2

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
Improve this question
8
  • You would still need individual scripts available for a db administrator should the users of the app be connecting with MySQL accounts that do not have admin rights to the database.
    – GrandmasterB
    Jul 2, 2014 at 16:17
  • @GrandmasterB what do you mean by that? I don't quite follow the situation you're talking about
    – Daenyth
    Jul 2, 2014 at 16:18
  • You should have mentioned exactly your open source project, e.g. by giving a link to it!
    – Basile Starynkevitch
    Jul 2, 2014 at 16:34
  • I added a link.
    – Daenyth
    Jul 2, 2014 at 16:44
  • @Daenyth Often client applications in a multi-user setup do not connect to a remote MySQL server using a MySQL user name that has rights to change the schema.
    – GrandmasterB
    Jul 2, 2014 at 17:24

3 Answers 3

Reset to default
2

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.

Improve this answer
0

If you are doing the upgrade within the client application like that, your approach will work. I'd go so far as to say its unnecessary to do anything more than check the schema version in the database and apply the relevant SQL statements to update the schema. So I'd skip the step where you try to compare the schema to another scratch database.

Improve this answer
2
  • I didn't really imagine this to be something that the application would do, more the server administrator would run a script that would do this.
    – Daenyth
    Jul 2, 2014 at 17:35
  • You really need to clarify the question and describe how all the pieces go together, because the way it reads it sounds like your C++ program is doing the updates - "Managing database schema in a C++ app". If all you intended was for some SQL update scripts, that your client app is written in C++ isn't relevant.
    – GrandmasterB
    Jul 2, 2014 at 17:39
0

You might consider creating the tables as part of the installation.

Alternatively, your C++ program could issue CREATE TABLE IF NOT EXISTS Mysql statements. Then it would work when the tables exist (usual case) or when they don't exist.

And you could query the existence and the columns of older tables with e.g. SHOW TABLES & SHOW COLUMNS and/or SHOW CREATE TABLE -or use the Information Schema (then later ALTER TABLE if needed)

Improve this answer
1
  • How would you handle a case where alter table is needed to change a column or something else?
    – Daenyth
    Jul 2, 2014 at 16:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.