Tuesday, August 19, 2014

Simple Database Migration-Class using sqlite and fmdb

Checkout the project at github https://github.com/flodev/DbMigration.
Then execute "git submodule init" and "git submodule update" to fetch fmdb.
The submodule is at tag 2.3 so it should be stable.

I'd like to present you how I will do database migrations on iOS devices.
The app is running an sqlite db containing a settings table with a db_version entry.
The migration class has defined updates for different database versions. They are presented in a dictionary with versions as keys and "blocks"/closures as values.
For example in the first version of the app normally the base scheme with tables which will be created.
In the second version the app needs an additional table. And in version 2.1 a column has changed and a new column will be inserted.

Then it goes like this.

first time the app starts, version 1.0 of the db scheme:
- db scheme will be created and queries for first version are executed

user is updating the app and starting it
- migration script is executing queries for version 2.0

and so on.

The script is working like that:
There is a Db class that has a version constant.
Now AppDelegate starts the DbMigration script. The script compares the constant of Db-Class with the version found in the settings-table in the database. But wait, first start, there is no database nor a settings table. Thus DbMigration script decides all queries needs to get executed. At the end it inserts the Db-class-constant into the settings table of the database.
After an update the Db-class-constant might have changed and differ from the version in the settings table. Then all the update statements in the migration script that have version greater than the db version will run.

No comments:

Post a Comment