[sqlite] Handling database updates in my program updates

[Murray Moffatt]

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?