Giving a MYSQL Database a “version”

Question

I am writing a game that uses an embedded mySQL database for keeping track of scores and I am releasing an alpha soon.

I know there are going to be features added down the road which are going to require changes to the database structure.

Up to now, whenever I changed the database's schema, I just deleted what i had and had a new one generated.

But now that I'm releasing an alpha, I don't want to be so careless with my players' score data.

It feels like I would like to give the database a "version number", so when the game starts, I can check what version database it is, and if it's not the current version, automatically do whatever surgery on the database to give it the right schema.

My intuitive approach to doing this is adding a single row, single column table called "DB_VERSION" and then checking it each time on load. This way, if I change the database, I can increase the number in my code, and when I come across a database in the wild that has a lower number, I can upgrade the database structure automatically.

While this will work, intuitively something is telling me this is The Wrong Way.

Is there a built-in way to give a database a version ID? Is there some other approach to solving this problem that I haven't thought of? I'm sure this isn't a unique problem.

Thanks!

Here is the code I would like you to review:

When the database is first built:

statement.execute ( "CREATE TABLE DBVersion ( Version INT NOT NULL )" );

and then each time the program starts:

statement.execute ( "Select Version from DBVersion");
int databaseVersion = rs.getInt ( "Version" );

if( Database.version != databaseVersion ) {
   //Update database schema
}

Answer 1

An alternative would be checking for each of the changes you want to make — does table foo exist, does column bar exist in table baz, and so on. Which sounds cleaner, perhaps, but means a lot more i/o.

So the best way would be something like

int databaseVersion = getDatabaseVersion(); // because of course it's a function

try {
   if (databaseVersion < 2) {
      // Update database schema to version 2
      databaseVersion = 2;
   }

   if (databaseVersion < 3) {
      // Update database schema to version 3
      databaseVersion = 3;
   }

   // and so on...
}
finally {
   setDatabaseVersion(databaseVersion);
}

Don't forget to write the version to the database.

If you do not tie the database version number to your program version number (so databaseVersion <> programVersion, but of course a specific version of your program has a specific version of the database), but instead increment the version number after each DDL statement you execute, this, coupled with the version being written in the finally, ensures that if for whatever reason the update crashes halfway through, the next time you can continue from that point on.