How to compress (or cleanup) Sqlite database after delete?

Question

Level 1

35 points

How to compress (or cleanup) Sqlite database after delete?

Is there a way to compress (or cleanup) a Sqlite database after a large number of rows have been deleted?

For example, if I have a database file around 1Mb and delete all the rows the database file remains at 1Mb (despite being empty). If I then open this file in Sqlite Administrator and click on "CleanUp" the size goes down to 4kb.

Is this functionality available from inside my app?

Thanks

iMac, Mac OS X (10.5.4), N/A

Posted on Aug 13, 2008 12:15 AM

Reply

Answer 1

Aug 13, 2008 9:15 AM in response to RJShearman

You can use the VACUUM command of SQLite. You just have to execute the SQL command: "VACUUM" like any other command.

See http://www.sqlite.org/lang_vacuum.html for more details.

Reply

Answer 2

RJShearman Author

Level 1

35 points

Aug 24, 2008 10:58 AM in response to buddy0815

I'm finally getting some time to look at this but not doing too well.

Running the following code gives the below error message:


NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *documentsDirectory = [paths objectAtIndex:0];
NSString *path = [documentsDirectory stringByAppendingPathComponent:@"myDB.sql"];
int success;
if (sqlite3_open([path UTF8String], &database) == SQLITE_OK) {
 // My first delete statement
 // My second delete statement
 // My third delete statement
....
 // VACUUM STATEMENT
 const char *sql4 = "VACUUM";
 sqlite3_stmt *statement4;
 if (sqlite3preparev2(database, sql4, -1, &statement4, NULL) != SQLITE_OK) {
 NSAssert1(0, @"Error: failed to prepare vacuum with message '%s'.", sqlite3_errmsg(database));
 }
 success = sqlite3_step(statement4);
 sqlite3_reset(statement4);
 if (success != SQLITE_DONE) {
 NSAssert1(0, @"Error: failed to execute vacuum with message '%s'.", sqlite3_errmsg(database));
 }
 sqlite3_finalize(statement4);
 sqlite3_close(database);
}

error:


Error: failed to execute vacuum with message 'unable to open database file'.

I know the database file is open ok because the rows are being deleted fine. The problem is just cleaning up the database file with the VACUUM command.

Has anyone successfully run this statement on the iPhone?

Reply