None of this is necessary if you select a durable schema. Whenever you think you need to "add a field" - add a whole new table and use joins anywhere you need access to the new field.
You can't "delete" a field, but deleting a field usually means losing data anyway. You can't change the nature of a field without changing the domain that the data exists in. Keeping strict 1NF tables can really help avoid this. Your "update procedure" wouldn't be necessary at all. On Sun, 2005-01-30 at 11:44 +1300, 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?
