Phonegap Sqlite Vacuum

Question

We have an offline capable tablet app using VSNomad (through Phonegap) with Sqlite local database. One thing I've noticed is when we delete all data from tables and drop tables (doing an "app reset") on an iPad it doesn't reflect that space has been opened up.

I've come across "VACUUM" command for Sqlite however I am unsure how/if this can be used with our implementation. When I tried to run it I get an error saying cannot run within a transaction.

Here's examples of how we're implementing http://docs.phonegap.com/en/2.7.0/cordova_storage_storage.md.html#Storage

        app.shared.db().transaction(function (tx) {
            tx.executeSql('VACUUM', [], function (tx, results) {
                alert('done');
            }, function (tx, error) {
                alert('error');
                alert(error.message);
            });
        });

Is it possible to run a vacuum like this?

michel.iamit · Answer 1 · 2013-09-13T06:23:23.297

I implemented your code and I get:

E/SQLiteQuery(18724): exception: not an error; query: VACUUM

Searching a bit further I found out that the vacuum sql command can not be given while connected to the database, so cannot be run from the database open and transaction from within javascript nor phonegap. Can only be given from command line.

But there must be another way... (looking in to that).

For now I have a few suggestions for database management:

Make sure you can delete the app and start with a fresh database (see below)
Prevent growing of the database: empty table and not drop table all the time (transaction.executeSql('DELETE FROM tablename;');) Note: don't use the '*' mark
Populate your database if it's the first run, see: http://mebefreelancer.wordpress.com/2012/08/14/phonegap-if-database-exists-do-something-else-populate-database/
Try to set up your app in a way the database is being migrated in a nice way: see enter link description here (a django /south kind of way for migrations)

Phonegap Sqlite Vacuum

1 Answers1