3

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
Daenyth
  • 8,127
  • 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 02 '14 at 16:17
  • @GrandmasterB what do you mean by that? I don't quite follow the situation you're talking about – Daenyth Jul 02 '14 at 16:18
  • You should have mentioned exactly your open source project, e.g. by giving a link to it! – Basile Starynkevitch Jul 02 '14 at 16:34
  • I added a link. – Daenyth Jul 02 '14 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 02 '14 at 17:24
  • Users don't connect to the sql server directly, only the server. – Daenyth Jul 02 '14 at 17:28
  • Does your C++ program connect to the MySQL server or not? If so, then its using a MySQL user account, correct? Are you sure that MySQL server account will always have admin rights? – GrandmasterB Jul 02 '14 at 17:31
  • I see what you mean now. The application's account shouldn't have that level of permission, I'd expect these to use a different mysql account for maintenance tasks like this. – Daenyth Jul 02 '14 at 17:33

3 Answers3

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.

gbjbaanb
  • 48,585
  • 6
  • 103
  • 173
1

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)

Basile Starynkevitch
  • 32,652
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.

GrandmasterB
  • 39,224
  • 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 02 '14 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 02 '14 at 17:39