There are two issues at play here:
(1) Schema updates driven by new versions of your application program. The method you described is basically a sound method, though I might suggest a couple of modifications. You raised the question of a user skipping versions. This is easily addressed by having a file format updater which applies updates sequentially. If the file needs to be updated from version 1 to version 3, apply the 1->2 update then apply the 2->3 update, etc. I wrote code like this a few months ago, and chose to keep the schema version as a date string in "yyyy-mm-dd" format, so I didn't have to think about version numbering and the updates were always applied in the right order. ;-)
(2) Database file format updates driven by new versions of SQLite. From my reading of this list over the past few months, I'm led to believe these are relatively rare. Most version updates come for free (3.0.7 to 3.0.8, for instance, didn't invalidate any databases; files generated by one could be read & written by the other). A few updates (2.x -> 3.x) do break compatibility altogether, and you may have to include both versions of the SQLite library to maintain the upgrade path. (In this case, I gather this is a new application, so you won't have any need for 2.x, but you may need to keep 3.x should a later version 4.x be unable to read 3.x files.) Of course, nobody forces you to upgrade to the latest and greatest version, so if a break in database file format occurs, you may want to consider staying behind. Plenty of people are still using 2.x. YMMV.
Hope this helps...
-Eric
Murray Moffatt wrote:
I'm creating a shareware program using Visual Basic and SQLite. I'm using the SQLiteDB wrapper.
I want to plan how I am going to handle updates to my program that involve modifing the database, and thus I'd like some advice as I'm sure others have faced this problem before. I need to be able to send out an update to existing users which keeps all of their existing data in tact. I imagine that most of the updates will involve adding new fields.
At the moment my plan is to have a Control table in the database that includes a field that holds the version number of the database. Then when my program starts up it can check this version against its own version and if the database version is lower then it can tell the user that they need to upgrade their database and automatically run an Update program that will be included with all update releases.
The Update program will have an empty copy of the latest database (i.e. tables all set up but no actual data), and will import all the records from the old database into the new one. Then rename the old database (thus keeping a copy in case something goes wrong) and copy the new one in its place.
One problem I thought of is what happens if a user skips an update and thus is upgrading from, say, version 1 to version 3. Rather than create a convoluted system where that Update program can convert from any version to any other, I would like to make the Update program as generic as possible, i.e. it reads the old database and matches the fields in it with the fields in the new database and copies that data. In this way it won't expect to see certain fields in certain versions, instead it just sees a Name field in the old database and copies the data found in that field into the Name field in the new database. Obviously all new fields will be empty.
Does this sound like a logical way of handling database updates? Does anyone have any suggestions for a better method, or possible pitfalls in my idea?
One assumption I'm making is that if I upgrade the verson of SQLite that is used by my program then I assume that newer versions will always be able to read databases created by older versions. Is this correct?
