I have an open source project (Cockatrice) that uses mysql for the server component. I'm planning some changes to the schema, but right now the only management I have is a script that runs the proper CREATE TABLE statements.
Before I start rolling out changes I'd like to get a schema management solution in place. The app is written in C++ with Qt, and I haven't been able to find anything like rails migrations or play evolutions for C++. I could just use those, but it would introduce a completely unrelated dependency that I'm reluctant to use for this.
I've come up with an approach and I'd like some feedback on whether it makes sense or has any drawbacks.
Add a new table to manage the schema, with a version column.
New migrations would get added with a version up, down, and full creation script (eg 2.up.sql, 2.down.sql, and 2.full.sql). The up script would contain the sql to perform the upgrade, the down script would reverse it, and the full would create a database from scratch at that version.
I was thinking I'd write a script runnable by the administrator that would do something like the following:
- Read current version from db
- scan for new patches
- lock whole db
- snapshot db minus volatile tables
- start transaction
- apply each sql file in order to bring it to the latest version
- create a scratch db and apply the full create sql
- compare updated schema against the scratch db
- if schema doesn't match, error out and abort transaction
- if it matches, drop the scratch schema
- unlock
Best Answer
y primary thought on DB management is not to bundle it into the application, but to bundle it into the installer. Admittedly all the project I work on have an ops team (of some sort or other) to do the install so maybe I'm a little blinkered here.
Nevertheless the steps are sensible. I tend to create an upgrade script for each version upgrade rather than many small upgrade steps, but Rails seems to do well with a numbered set of upgrade scripts for each feature.
Given the sql is self-contained, I still think an app to run it is unnecessary - a batch file will happily open the DB and run the sql in. We do this in SQLServer command line tools all the time, MySQL runs on the command line, using the source command to run a sql file directly.