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
}