1

I'm currently trying to reduce the size of my database by deleting records from my sqlite database in my iOS application but the size will stay the same (size of all the files associated with the database, i.e. the .sqlite file, the .sqlite-shm file and the .sqlite-wal file). I have read that this is the expected behaviour (Ref: change sqlite file size after "DELETE FROM table"). This link suggests to use the sqlite "vacuum" tool and this should clean up the unused memory fragment. However, by doing this, this allocates a lot of memory in the rollback on disk memory (more specifically, the .sqlite-wal file). So this doesn't help since the total size of the files associated with the database didn't decrease.

I was wondering if anybody has any idea what to do in that case? Any help is appreciated.

Thanks

JB

Community
  • 1
  • 1
JB Yung
  • 71
  • 7

1 Answers1

0

You can disable the use of Write ahead logging (sqlite-wal) in initialisation of your persistent store coordinator (typically in appdelegate) (Check stack overflow or google). This will mean that any changes made to the SQL db are reflected on disk on your device immediately as you appear to be expecting.

It might look something like this:

 if (![_persistentStoreCoordinator addPersistentStoreWithType:NSSQLiteStoreType
                                               configuration:nil
                                                         URL:storeURL
                                                     options:@{NSMigratePersistentStoresAutomaticallyOption:@YES, NSInferMappingModelAutomaticallyOption:@YES, NSSQLitePragmasOption : @{@"journal_mode" : @"DELETE"}}
                                                       error:&error])
David van Dugteren
  • 3,879
  • 9
  • 33
  • 48
  • I'm not using a persistent store coordinator. The app is using the FMDB library (which is essentially just a wrapper on top of the native sqlite3 libraries) and the library is using the Write ahead logging support and there's no way to turn it off. Thoughts? – JB Yung Jun 05 '15 at 12:38
  • There should be an option to turn off the feature in config, initialisation or elsewhere... – David van Dugteren Jun 09 '15 at 05:42
  • FMDB doesn't expose this. It doesn't allow the client to turn off journaling – JB Yung Jun 19 '15 at 13:27
  • Does sqlite allows turning off journalling ? – Nilesh Agrawal Mar 04 '16 at 03:10