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