Re: [sqlite] Handling database updates in my program updates

[Darren Duncan]

If you are looking for longetivity of your program, as I am doing 
with mine, I would keep and continue to use a Control table such as 
you speak of.  However, this Control table should not contain the 
database version number, but rather the version number of your own 
application program.  That way, if the database schema that your 
program uses gets updated over time to support new program features, 
or you fix bugs in your program that caused data to be stored 
incorrectly in the older versions, you will have the best chance of 
importing the data despite schema changes and have the best chance at 
correcting the data errors, because you can predict what kind of 
errors there would be based on the old program version the data says 
it was used with.  You could also store the SQlite version in the 
table if you want, but that would be mainly useful to recover from 
errors that older SQlite may have introduced, that were later 
detected.  Otherwise, I assume that SQLite itself is smart enough to 
recognize when SQLite files are or are not compatible.  You don't 
necessarily have to worry about this. -- Darren Duncan

At 11:44 AM +1300 1/30/05, 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?