pragma auto_vacuum = 1; VACUUM;

[domschmidt]

auto vacuum cannot be enabled on platform cordova-windows.

step 1:
execute

const vacuumPragmaStatement = 'pragma auto_vacuum = 1; VACUUM;';
dbConnection.executeSql(vacuumPragmaStatement).then((vacuumResult) => {
   this.logger.debug('SQL Queries Finished:', vacuumPragmaStatement, vacuumResult);
   observer.next(dbConnection);
   observer.complete();
}).catch((error) => {
   this.logger.error('Unable to set vacuum PRAGMA', error);
   observer.error(error);
});

step 2:
open path

C:\Users\<useracc>\AppData\Local\Packages\<package>\LocalState

step 3:
inspect DB using https://sqlitebrowser.org/

expected result:

• auto vacuum is set to FULL

actual result:

• auto vacuum is set to NONE

this is a major problem. How can somebody use this adapter, if the sql file grows endlessly?

Do you have any advice?

If I apply the changes manually via sqlitebrowser, then the changes are applied properly (both via query and via UI).

Thanks :-)

[domschmidt]

Sorry, my fault.

It works properly if I separate the pragma statement from the the vacuum one.

Maybe you could note this specific case in the documentation, because the pragma auto_vacuum is different to other pragmas. auto_vacuum needs an additional vacuum statement.

To change auto-vacuum modes, first use the auto_vacuum pragma to set the new desired mode, then invoke the VACUUM command to reorganize the entire database file. To change from "full" or "incremental" back to "none" always requires running VACUUM even on an empty database.

source: https://www.sqlite.org/pragma.html

this is the final solution:

 // db not set up initially. we must correct the auto_vacuum setting
const pragmaStmt = `PRAGMA auto_vacuum = '1';`;
dbConnection.executeSql(pragmaStmt, []).then((pragmaResult) => {
   this.logger.debug('SQL Queries Finished:', pragmaStmt, pragmaResult);

   const vacuumStatement = `VACUUM;`;
   dbConnection.executeSql(vacuumStatement, []).then((vacuumResult) => {
      this.logger.debug('SQL Queries Finished:', vacuumStatement, vacuumResult);
      observer.next(dbConnection);
      observer.complete();
   }).catch((error) => {
      this.logger.error('Unable to do vacuum', error);
      observer.error(error);
   });

}).catch((error) => {
   this.logger.error('Unable to do vacuum', error);
   observer.error(error);
});

best

[brodycj]

Thanks @domschmidt for reporting, just reopened and transferred to the help repo.

Yes I would like to get this documented someday.

FYI I am working on a new design, as discussed in brodycj/ask-me-anything#3.