iOS Tips on Database updating?

[sanPietro98]

So my app has a sqlite3 database with several tables. Some stables contain "static" data (i.e., it is reference data that the user doesn't change). There are also "dynamic" tables that are affected by actions the user takes in the app. The dynamic tables refer to static tables (via foreign keys), hence my need for a relational DB.

So my question is this... When I want to publish version 2.0 of my app, I may want to change the schemas or the even data contained in the static tables, but I don't want to blow away the user's data in the dynamic tables. Therefore I don't want to just overwrite the existing DB with the new one that is in the App's bundle.

Does anyone have any "best practices" or experiences that they want to share on how to effectively upgrade a sqlite3 database file when you publish & deploy your app's next version?​

I have developed some code to copy over dynamic table data to the new upgraded DB, but it is rather cumbersome and complex. I'm also worried about maintainability as schemas evolve over time.

Thanks in advance.

 

[Pring]

I simply maintain a set of SQL 'INSERT' and 'ALTER' statements which run depending on which version path is being taken. I have a NSUserDefault stored with the current version, I use this to check which path is being executed when we update (ie 1.0.0 directly to 1.0.2 or 1.0.1 to 1.0.2).

So a big case statement that reads in a different set of SQL statements based on the upgrade path.

 

[PhoneyDeveloper]

I recommend that you keep the version number in the database file itself. Check the version number when you open the db and then update the db as required.

How you update the db will really depend on how it's set up. Since this process will only happen one timer per user I wouldn't worry about efficiency. Instead write it in the simplest way that works.

 

[sanPietro98]

I simply maintain a set of SQL 'INSERT' and 'ALTER' statements which run depending on which version path is being taken. I have a NSUserDefault stored with the current version, I use this to check which path is being executed when we update (ie 1.0.0 directly to 1.0.2 or 1.0.1 to 1.0.2).

So a big case statement that reads in a different set of SQL statements based on the upgrade path.

I had thought of this. My only concern was that it might be easy to not perfectly capture every modification to the DB's rows with one of these commands. It's more of a data management concern rather than a technical approach concern -- since I have a large data set.

Is there a way to get sqlite3 (or any tool) to create these statements by 'diff'ing the two DB instances?

 

[sanPietro98]

I recommend that you keep the version number in the database file itself. Check the version number when you open the db and then update the db as required.

This is basically the approach I have built. I was hoping for a more elegant solution, but I agree, since this is a one-time operation (per update) I'm not too concerned with efficiency.

As Pring stated, I'll probably need a large case statement. I'll want to compare the current version against the updated DB version. I don't (and can't) assume that every user is vigilant about keeping versions up-to-date and therefore the "update" process may need to skip a version or more.

Thanks.

 

[sanPietro98]

So my app has a sqlite3 database with several tables. Some stables contain "static" data (i.e., it is reference data that the user doesn't change). There are also "dynamic" tables that are affected by actions the user takes in the app. The dynamic tables refer to static tables (via foreign keys), hence my need for a relational DB.

(It's bad when you quote yourself)

I think I may have found an easier way to handle this for my particular situation. I discovered that sqlite3 has an "ATTACH" command that allows you to dynamically merge 2 database files into a virtual common DB. I could use this to keep the static data in one DB file and the user data in a second DB file. That would allow me to update static data without disturbing the other DB.

Granted, if schemas change, I'll still need to copy the older user data over, but this approach might mitigate some of the hassles when I just want to update the static database.

Has anyone used the ATTACH or DETACH commands in sqlite3? Any good or bad experiences with it?
Any thoughts?

 

[ghayenga]

(It's bad when you quote yourself)

I think I may have found an easier way to handle this for my particular situation. I discovered that sqlite3 has an "ATTACH" command that allows you to dynamically merge 2 database files into a virtual common DB. I could use this to keep the static data in one DB file and the user data in a second DB file. That would allow me to update static data without disturbing the other DB.

Granted, if schemas change, I'll still need to copy the older user data over, but this approach might mitigate some of the hassles when I just want to update the static database.

Has anyone used the ATTACH or DETACH commands in sqlite3? Any good or bad experiences with it?
Any thoughts?

I've used ATTACH and DETACH quite a bit and it seems to work just fine.

 

[sanPietro98]

I just did a quick prototype and I like this feature. I'm now able to keep my "static" database inside the App Bundle. Now I only keep the "user data" tables in the Documents directory. I think this is actually simplifying my deployment.

 

[yrvaken2]

I have been thinking on this for a while myself, thanks for the ideas!