How use Vacuum on sqlite3?

[programonauta]

Hi,

I'm using sqlite3 in my WeMos WiFi&Bluetooth Battery board in SPIFFS, and everything work fine.

But I'd like to optimize the database size. So, I tried to run the command "VACUUM;" with sqlite3_exec function, but I received an error: "disk I/O error".

My code:

char *zErrMsg = 0;
int dbExec(sqlite3 *db, const char *sql) {
   Serial.println(sql);
   long start = micros();
   int rc = sqlite3_exec(db, sql, NULL, NULL, &zErrMsg);
   if (rc != SQLITE_OK) {
       Serial.printf("SQL error: %s\n", zErrMsg);
       sqlite3_free(zErrMsg);
   } else {
       Serial.printf("Operation done successfully\n");
   }
   Serial.print(F("Time taken:"));
   Serial.println(micros()-start);
   return rc;
}

bool deleteEvents(int qty)
{
  char strSQL[250];
  int rc;
  int lastRowId;
  if (dbOpen(dbName.c_str(), &db))
    return false;

  snprintf(strSQL, sizeof(strSQL), "Select rowid from event limit %d;", qty);

  rc = sqlite3_prepare_v2(db, strSQL, 1000, &dbRes, &dbTail);
  if (rc != SQLITE_OK) 
  {
#ifdef DEBUG    
    String resp = "Failed to fetch rowid: ";
    resp += sqlite3_errmsg(db);
    Serial.printf("deleteEvents - %s\n", resp.c_str());
#endif
    return -1;
  }

  lastRowId = 0;

  while (sqlite3_step(dbRes) == SQLITE_ROW) 
     lastRowId = sqlite3_column_int(dbRes, 0);

  snprintf(strSQL, sizeof(strSQL), "DELETE from event where RowId <= %d;", lastRowId);
  rc = dbExec(db, strSQL);
  if (rc != SQLITE_OK) {
#ifdef DEBUG    
    String resp = "Failed to delete data: ";
    resp += sqlite3_errmsg(db);
    Serial.printf("deleteEvents - %s\n", resp.c_str());
#endif
    sqlite3_close(db);
    return false;
  }

  rc = dbExec(db, "vacuum;");
  if (rc != SQLITE_OK) {
#ifdef DEBUG    
    String resp = "Failed to vacuum: ";
    resp += sqlite3_errmsg(db);
    Serial.printf("deleteEvents - %s\n", resp.c_str());
#endif
    sqlite3_close(db);
    return false;
  }

  sqlite3_close(db);
  return true;
}

So I tried to initialize #define SQLITE_DEFAULT_AUTOVACUUM 1 in the firsts lines of my sketch.

How could I reduce the size of my sqlite file on SPIFFS?

Thanks

[siara-cc]

Hi, I will check what could be the issue.. are you checking against the current clone of this repo or any particular release?

[programonauta]

Hi @siara-cc, I've installed through Arduino IDE and the "more information" button directs to this repository.

Anyway, it could be anything I'm doing wrong?

[siara-cc]

Hi, as of now unable to determine why it does not work. Auto-vacuum cannot be enabled by adding it on top of ino file.. you will have to change config_ext.h. Another option would be to insert remaining records to another DB when number of deleted records become significant. Or maintain datewise or monthwise db files and delete them.

[txf-]

Shouldn't #define SQLITE_OMIT_AUTOVACUUM 1 also be undefined in config_ext.h in order for auto-vacuum to work?

Also how memory intensive is a VACUUM operation? I tried to do it but I got an "out of memory" error, I only tested on VFAT though, it just seems to work better there (though probably because it isn't sharing data bandwidth with program code).

[siara-cc]

According to my understanding, Vacuum is different from Auto-vacuum and requires more resources. See https://sqliteviewer.com/blog/sqlite-vacuum-and-auto-vacuum-commands/.

Vacuum copies to a new database so might get into trouble with limitation of number of simulataneous files and memory, so I have disabled it.