How to automatically create a (My)Sql ALTER script within an installer?

Question

In an software installer I need to automatically create a (My)SQL ALTER script just given a running database at unknown state (for example data-structure version x.5) and a couple of full DB (My)SQL CREATE scripts (say version x.1 to x.9).

First i need to find the currently running (or if possible closest version, probably some installations had some earlier update errors, but this feature is secondary). I then want to create an ALTER script to fix possible errors for the running version.

Afterwards i'd like to automatically create an ALTER script to the newest version (x.9) and apply this script. Once more compare both versions and repeat until version is up to date.

I cannot use a GUI-Application since this will have to run blind within an installer. The target platform will be Windows XP/7. Installments will have a count lower then 300 for a very long time (Update-Software for highly specialized industry software). So my question is:

Are there any good (My)SQL compare/diff/script-generation libraries for use with C++/NSIS/Some-Other-Installer-Frameworks?

Thanks for your support!

Answer 1

I am thinking on the same topic since a long time but haven't found a decent way of doing it. I will share what I do, with the hope that it will be helpful.

My current approach is to apply a list of SQL queries that is designed in such a way that is suitable for any previous schema version of the database. If the command have already been applied, then it will just fail (e.g. adding field or adding index).

This approach limits the ways one can change the DB schema and is also prone to errors - e.g. if by mistake there are queries to extend an ENUM(a,b) field to ENUM(a,b,c) and then to ENUM(a,b,c,d) then existing records with value d will get corrupted if you run the script again. This is easily fixed if there is only one query for the latest format.

I have also added schema versioning at a later point and currently use a simple but easy to manage format of the update file - one query per line ending with ; and extra lines delimiting schema versions:

-- version 105

With this upgrade code can be greatly simplified and unified in a single function that will handle all version transitions. The function have only to process queries after the --version <current version> line. After reaching a -- version line the function updates the schema version inside the database.

Also this format allows for manual processing with the mysql -f mydb < myfile command. In this case the version lines just get ignored as comments and all commands for all changes will be tried on the current schema - this can be used to fix errors (by errors I assume that you mean older schema than expected). There is also a similar trick for updating code of stored procedures:

drop procedure if exists procname;
delimiter //
create procedure procname ...
//
delimiter ;

In your question you ask about DB schema diff/patch - this can be generalised only in the case of adding new fields/indexes/etc. but cannot automatically process renamed fields or deleted fields. There is no way for an automated process to know that field a in table1 should be renamed to b by just looking at existing schema and new schema and to keep existing data (I assume that existing data must be kept intact).

So to summarize - there is no automated way of generating DB schema update script in the general case.

Answer 2

There are two approaches one can take in this problem.

1. Alter scripts are meant to affect the schema of the database without caring for data.

2. Alter scripts are meant to affect the schema while keeping the data.

In the first approach this is easily done by dropping the current database and generating a new one. But I'm sure this is not what you want and the data is an important part of your equation.

In the second approach, before anything, you need to know that this can not be done regardless of what DBMS you are going to deal with since SQL is not as standard as it sounds. Having some specific DBMS in mind, a general approach to this problem could be creating an up-to-date version of your schema in the DBMS and comparing it with your current version of it. Here is a list of tools you might find useful for MySQL.

Things you can do in this approach:

• Check and see if a table is removed.

• Check and see if a table is new.

• Check and see if a field is removed.

• Check and see if a field is new.

• Check and see if a table's properties are altered.

• Check and see if a field's properties are altered.

Things you can not do in this approach:

• Check and see if a table is renamed.

• Check and see if a field is renamed.

In other words, renamed entities will result in a DROP statement and a CREATE one which will lead to loosing your data. This is a logical problem of this approach and it's not possible to overcome it. The only way to see if a table or a field is renamed, is to look into the list of alter commands and look for appropriate ones (in case you have a list of alter statement and not just the final schema). And implementing this is hassle of its own.

There is one another IMPORTANT problem with this approach as well; since we are taking the closest path to our aimed schema, we might miss some important steps in the process. Namely, think of the scripts that you might have executed which affected the data of your database but not its schema. Such statements can not be extracted using any diff tool since you've got no reference for your data (unless you actually have which I don't think is your case). In this case your only choice is to apply a list of scripts one by one in the same order as it should be applied. And having such a list is only possible only if you have a versioning mechanism or a human should come up with the list through analyzing. I can hardly think of a tool to help you in this case (in case you don't have a versions for your databases). At least I don't know any!