Keeping database schema up to date with new versions of application.

[metalla_nz]

Hi all,

Im developing a small app that stores information in a database.

When I first released, I tried to make sure my DB was future-proofed against any changes.

However i'm about to release a new version that requires a change to the DB schema.

Currently my terrible way of handling this is my DB Manager class will try to create the table in the constructor, if the Create commands come back with an exception (table already exists) catch it and move on.

This has been working fine, now I need to make a change to the schema without losing all my users data. For now i'm putting more SQL statements in the DB Manager to save data, drop table, create table and put it back in. Which will work, but is not good I assume, and will only work for this version, any new changes will need something different.

How does everyone else handle this? Adding tables/columns if not present removing columns etc without losing data and seamlessly to the user?

Any tips will be appreciated.

[jatin24]

Well in any database driven application, i think any changes to the database will cause a lot of trouble in the working of the application. Esp if you are playing around with columns of tables.
While adding/removing columns, you will have to make appropriate changes in the queries/stored procedures as well, else you will get lots of errors.

If you want to remove a column, i dont think theres anyway to do that without loosing the data. Unless you retrieve all the values of that column in your application somewhere, and then store it elsewhere (a file or maybe in the database itself).

[metalla_nz]

Removing columns isnt really an issue. I would assume I will only be adding columns.

This is my updater class ive just thrown together. Any opinions now would be appreciated...

This one works through a new method for each schema change, and the changes are handled incrementally.

public static class DBUpdater
    {
        static SQLiteClient dbConn;
        private static Logging log = Logging.getLogger();

        public static void Update(string DBFileName)
        {
            if (!System.IO.File.Exists(DBFileName))
                CreateDB(DBFileName);
            dbConn = new SQLiteClient(DBFileName);
            string dbVersion = "0.1", currentVersion = "0.4";
            try
            {
                dbVersion = dbConn.Execute("SELECT version FROM Settings").Rows[0].fields[0];
            }
            catch { }

            if(dbVersion == currentVersion)
                log.Append("Database already up to date");
            else
                doUpdates(dbVersion);
            dbConn.Close();
        }

        private static void doUpdates(string dbVersion)
        {
            switch (dbVersion)
            {
                case "0.1":
                    Update04();
                    goto case "0.4";
                case "0.4":
                    //Current Version
                    goto case "0.5";
                case "0.5":
                    //Future Version
                    break;
            }
        }


        private static void CreateDB(string dbFilename)
        {
            //This should always be the latest schema
            dbConn = new SQLiteClient(dbFilename);
            dbConn.Execute("CREATE TABLE Videos (id integer PRIMARY KEY AUTOINCREMENT, artistID integer, " + 
                "path char(300), title char(100), playCount integer, rating integer, year integer, thumb char(300))");
            dbConn.Execute("CREATE TABLE Artists (id integer PRIMARY KEY AUTOINCREMENT, artistName char(100), " +
                "artistBio char(700), artistImage char(300))");
            dbConn.Execute("CREATE TABLE Settings (ffmpeg char(300), thumbTime char(3), pluginName char(50), " +
                "extensions char(150), version char(10))");
            dbConn.Execute("CREATE TABLE Folders (id integer PRIMARY KEY AUTOINCREMENT, folder char(300), " + 
                "expression char(100))");
            dbConn.Execute("INSERT INTO Settings VALUES('c:\\ffmpeg.exe', '5', 'Music Videos', " + 
               "'.avi.mpg.mkv.wmv.divx.ts.ogm.vob.mp4.m4v.mpeg.m2v', '0.4')");
            log.Append("Database created");
            dbConn.Close();
        }

        private static void Update04()
        {
            string ffmpeg, time, name;
            ffmpeg = dbConn.Execute("SELECT ffmpeg FROM Settings").Rows[0].fields[0];
            time = dbConn.Execute("SELECT thumbTime FROM Settings").Rows[0].fields[0];
            name = dbConn.Execute("SELECT pluginName FROM Settings").Rows[0].fields[0];
            dbConn.Execute("DROP TABLE Settings");
            dbConn.Execute("CREATE TABLE Settings (ffmpeg char(300), thumbTime char(3), pluginName char(50), " + 
               "extensions char(150), version char(10))");
            dbConn.Execute("INSERT INTO Settings VALUES('" + ffmpeg + "', '" + time + "', '" + name + "', " + 
               "'.avi.mpg.mkv.wmv.divx.ts.ogm.vob.mp4.m4v.mpeg.m2v', '0.4')");
            log.Append("Database updated for V0.4");
        }
    }

[sknake]

I do the same thing in a number of my applications and there is no "right" or "wrong" answer in my opinion, but the way I chose doesn't seem to be popular to internet folks.

What I do is have an "Install Wizard" run as a separate application and it sets up a handful of the tables. One of those tables contain the software version in a "SystemReg" table. When the software connects it checks for the "SystemReg" table to make sure they're in the right database (and it hard-excludes 'tempdb' and 'master' databases). If they are in the right database I check for an "Upgrade" table. If the table does not exist then I create it.

Create Table Upgrade
(
  UpgradeId int PRIMARY KEY,
  UpgradeCode varchar(max),
  Ran bit
)

The software has queries embedded in the application with an upgrade ID. I insert all of the upgrades that do not exist based on upgrade id. Once they are all inserted I Select * From Upgrade Where Ran Is Null or Ran = 0 . From that point I start executing the queries in order.

If an upgrade fails to run I halt running upgrades and show the error message for the user take action.

Almost everyone disagrees with using this method because it requires the application to connect to the SQL Database with dbo access however I ship my application with SQL Server and it is intended that the application connect with DBO so it can maintain its own database, so to those people I simply say "oh well."

I have looked at 50 different ways to do this and in my opinion shipping a separate schema deployment application sucks. I do not like the project type, I don't like how it works, and I don't like the inability to 'version' the database accurately. I have used this method for over 5 years without a hitch.

That is my two cents on the matter.

[edit]
I thought of more:

In the "SystemReg" table I store two versions -- the current application version, and the minimum application version allowed to connect to the database.

Lets say:
MinimumRev = 1.0.0.0
CurrentRev = 1.0.0.5

This means the database has been updated to the schema of the 1.0.0.5 version but 1.0.0.0 is still allowed to connect because not enough functionality or schema changes have been implemented that will break previous revisions.
[/edit]

[metalla_nz]

Thanks (again :P) for your assistance Scott, as always a very useful and informative reply.

[Garr]

You could probably implement a stored procedure with SQL SECURITY DEFINER to work around the permissions issue of the application. You would need to update the stored procedure as part of the install process, but then all other schema changes would be done when the application runs the stored procedure at startup.