Best practices for in-app database migration for Sqlite

by

I maintain an application that periodically needs to update a sqlite database and migrate old databases to the new schema and here’s what I do:

For tracking the database version, I use the built in user-version variable that sqlite provides (sqlite does nothing with this variable, you are free to use it however you please). It starts at 0, and you can get/set this variable with the following sqlite statements:

> PRAGMA user_version;  
> PRAGMA user_version = 1;

When the app starts, I check the current user-version, apply any changes that are needed to bring the schema up to date, and then update the user-version. I wrap the updates in a transaction so that if anything goes wrong, the changes aren’t committed.

For making schema changes, sqlite supports “ALTER TABLE” syntax for certain operations (renaming the table or adding a column). This is an easy way to update existing tables in-place. See the documentation here: http://www.sqlite.org/lang_altertable.html. For deleting columns or other changes that aren’t supported by the “ALTER TABLE” syntax, I create a new table, migrate date into it, drop the old table, and rename the new table to the original name.

More Related Contents:

  1. how to save the data in sqlite3 in iphone
  2. Use CoreData or SQLite on iPhone? [closed]
  3. SQLite Exception: SQLite Busy
  4. Where’s the best SQLite 3 tutorial for iPhone-SDK? [closed]
  5. How get a datetime column in SQLite with Objective C
  6. Add an SQLite database to an iPhone app
  7. Best Cocoa/Objective-C Wrapper Library for SQLite on iPhone [closed]
  8. Where would you place your SQLite database file in an iPhone app?
  9. how to get rowNum like column in sqlite IPHONE
  10. How to view the data in sqlite file running in iphone application? [closed]
  11. Synchronizing an SQLite client database with a MySQL server database
  12. Creating an SQLite3 database file through Objective-C
  13. Encrypting SQLite Database file in iPhone OS
  14. sqlite database update when app version changes on Appstore in iPhone
  15. SQLite database on PhoneGap
  16. Lock Unlock events iphone
  17. How to programmatically determine iPhone interface orientation?
  18. Is there a way to pause a CABasicAnimation?
  19. Comparing two NSDates and ignoring the time component
  20. Simultaneous gesture recognizers in Iphone SDK
  21. A NSFetchedResultsController with date as sectionNameKeyPath
  22. What does the Xcode 4.2 preference “Support Wirelessly Connected Devices” do?
  23. Do NSUserDefaults persist through an Update to an app in the Appstore?
  24. Disable Dictation button on the keyboard of iPhone 4S / new iPad
  25. Could not find a storyboard named ‘Main’ in bundle
  26. iOS: Use a boolean in NSUserDefaults
  27. UICollectionView animations (insert/delete items)
  28. Xcode 4 .xib Create iPad Version
  29. Annotate PDF within iPhone SDK
  30. Web site exhibits JavaScript error on iPad / iPhone under 3G but not under WiFi

Leave a Comment