[mythtv] handling database structural changes

[Kirby Vandivort]

It's possible that this has been discussed before, but I didn't find
anything, so here goes..

On a project that I'm working on, we regularly release new code to
users, and periodically, the code requires database structural changes.
To handle this, I've done the following:

1) added a 'database version' field to a settings table in the database.
2) upon startup, the code calls a method that checks to see whether or
   not the database needs to be updated, and, if so, does it.

This insures that we have everyone at the latest version of the database
and it all happens automagically.

The basic code layout is as follows.  :

The settings table has a row that looks something like this:
----------------------------------
| DBVersion     |             3  |
----------------------------------


Then, there is code that looks like the following, and gets called
at program startup (this is pseudocodish, but you get the idea):
// ------------------------------------------------
const int currentVersion = 3;

// get the DBVersion from the DB

if (currentVersion == DBversion)
   return;

if (DBversion < 2)
{
    // perform database upgrade from version 1 to 2
}

if (DBversion < 3)
{
    // perform database upgrade from version 2 to 3
}

// update the database setting table to store the most recent version #


// ------------------------------------------------



When the database structure needs to change, 3 things occur:

1) the initial seed database file (like mc.sql) gets updated
2) the const int gets rev'd
3) another if is added to the bottom of the list to handle the
   update.




Any interest in having something like this for Myth?  If so, I can
start working on the code.  

Kirby
-- 

Kirby Vandivort                      Theoretical and Computational Biophysics 
Email: kvandivo at ks.uiuc.edu          3051 Beckman Institute
http://www.ks.uiuc.edu/~kvandivo/    University of Illinois
Phone: (217) 244-5711                405 N. Mathews Ave
Fax  : (217) 244-6078                Urbana, IL  61801, USA