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

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.

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!

Sounds like these scripts would be static. Could you include all of the scripts (version x.1 to x.2, and x.2 to x.3, ..etc) and run the specific scripts that the user needs?

What I've done within my application is to keep a database version value in the database.

My application has a required Database version.

Here is a piece of my Pascal-Oracle code. Hope it will give you a good idea.

const
  ApplicationsDBVersion = 26 ;
.....
.....
if CurrentDBVersion = ApplicationsDBVersion then
  Exit ;
if CurrentDBVersion < 0 then // just in a case that the database is out of sync.
  Exit;
updtScript := tStringList.Create ;
if CurrentDBVersion < 1 then
  Upgrade2Version1 ;
if CurrentDBVersion < 2 then
  Upgrade2Version2 ;
if CurrentDBVersion < 3 then
  upgrade2Version3 ;
.....
.....
.....
procedure Upgrade2Version3 ;
begin
  UpdateParameter(-3) ; // set that database is in inconsitent state
  AddField('tableX','ColX','CHAR(1)') ; // I've plenty of such routines (AddRef, AlterField, DropField,AddTable etc...
  AddField('tableX','ColY','char(1) constraint CKC_checkConstraint check (ColY is null or (Coly in (''E'',''H'')))') ;
  AddField('TableY','Colz','NUMBER(3)') ;
  UpdateParameter(3); // set that database is in consistent state ( no fail in scripts )
  runScript(3) ; // actually do the job...
end;
...
procedure UpdateParameter (_dbVersion : Integer) ;
begin
  if CurrentDBVersion = 0 then
    updtScript.Add('Insert into parametre (parametre,sira_no,deger) values ('+QuotedStr(cPRM_VeriTabaniSurumu)+',1,''1'')')
  else
    updtScript.Add('update parametre set deger = '+IntToStr(_dbVersion) + ' where parametre = '+QuotedStr(cPRM_VeriTabaniSurumu));
end ;

The best I can think of is sharing with you my script, that does exactly this: takes a list of column definitions and alters database table respectively. It can add, drop, alter (even rename) columns and change primary keys. Unfortunately it's PHP so re-coding would be necessary, but maybe you can find general idea useful.

I've used this script successfully for months now to upgrade various installations of my CMS.

Function accepts (as a second argument) an array of arrays, where each of the latter contains at position:

0 - Column name
1 - MySql column type (ex. "int" or "varchar(30)").
2 - whether columns is nullable (true for allow null, false for forbid)
3 - The default value for column (ie. "0").
4 - true, when column is part of primary key
5 - old name of a column (thus column of name in 5., if exists, is going to be renamed to column of name in 0.)

First parameter is table name, and third is whether the function should drop columns that exist in database table, but were skipped in provided array.

Sorry for the disgusting contract, but this function was never meant to be a part of public interface. :-)

Here goes the CreateOrUpdateTable function body (references explained afterwards):

function CreateOrUpdateTable($tablename, array $columns, $allowdropcolumn = false)
{       
    foreach($columns as &$column)
    {
        if ((!isset($column[0])) || (!preg_match('/^[a-zA-Z0-9_\-]+$/', $column[0])))
            $column[0] = 'TableColumn' . array_search($column, $columns);
        if ((!isset($column[1])) || (!preg_match('/^(int|date|datetime|decimal\([0-9]+,[0-9]+\)|varchar\([0-9]+\)|char\([0-9]+\)|text|tinyint)$/', $column[1])))
            $column[1] = 'int';
        if ((!isset($column[2])) || (!is_bool($column[2])))
            $column[2] = ALLOW_NULL;
        if ((!isset($column[3])) || (!is_string($column[3])))
            $column[3] = (($column[2] == ALLOW_NULL || $column[1] === 'text') ? 'NULL' : ($column[1] == 'int' ? "'0'" : ($column[1] == 'tinyint' ? "'0'" : ($column[1] == 'decimal' ? "'0.00'" : ($column[1] == 'date' ? "'1900-01-01'" : ($column[1] == 'datetime' ? "'1900-01-01 00:00:00'" : "''"))))));
        else
            $column[3] = "'" . Uti::Sql($column[3]) . "'";
        if ((!isset($column[4])) || (!is_bool($column[4])))
            $column[4] = false;
    }
    unset($column);

    if (!$this->TableExists($tablename))
    {
        $statements = array();
        foreach ($columns as $column)
        {
            $statement = $this->ColumnCreationStatement($column);
            if ($statement !== '')
                $statements[] = $statement;
        }

        $this->Query("create table " . $tablename . "(" . implode(',', $statements) . ") ENGINE=InnoDB DEFAULT CHARSET=latin2");
    }
    else
    {
        $this->Select("show columns in " . $tablename);
        $existing = $this->AllRows(null, 'Field');

        $oldkeys = array(); $newkeys = array();         
        foreach ($existing as $e)
            if ($e['Key'] === 'PRI')
                $oldkeys[] = $e['Field'];

        sort($oldkeys);
        $oldkeys = implode(',', $oldkeys);

        $lastcolumn = ''; // not 'FIRST' as we can extend existing table here providing only extending columns

        foreach ($columns as $column)
        {
            if ($column[4])
                $newkeys[] = $column[0];

            $newtype = $column[1] . ($column[1] === 'int' ? '(11)' : ($column[1] === 'tinyint' ? '(4)' : ''));
            $newnull = ($column[2] === ALLOW_NULL ? 'YES' : 'NO');
            $newdefault = $column[3];                   

            if (isset($existing[$column[0]]))
            {
                $oldtype = $existing[$column[0]]['Type'];
                $oldnull = $existing[$column[0]]['Null'];
                $olddefault = isset($existing[$column[0]]['Default']) ? "'" . Uti::Sql($existing[$column[0]]['Default']) . "'" : "NULL";

                if (($oldtype != $newtype) || ($oldnull != $newnull) || ($olddefault != $newdefault))
                {
                    $this->SaveToLog("Altering table [" . $tablename . "], column [" . $column[0] . "], changing: type [" .
                        $oldtype . "] => [" . $newtype . "] nullability [" . $oldnull . "] => [" . $newnull . "] default [" . $olddefault . "] => [" . $newdefault . "]", true);
                    $statement = $this->ColumnCreationStatement($column, false);
                    if ($statement !== '')
                        $this->Query("alter table " . $tablename . " change " . $column[0] . " " . $statement);
                }

                unset($existing[$column[0]]);
            }
            else if (isset($column[5]) && (Uti::AnyExists(array_keys($existing), $column[5]) !== false))
            {
                $oldcolumn = Uti::AnyExists(array_keys($existing), $column[5]);

                $this->SaveToLog("Altering table [" . $tablename . "], column [" . $column[0] . "], renaming: name [" . $oldcolumn . "] => [" . $column[0] . "] " .
                    " type [" . $newtype . "] nullability [" . $newnull . "] default [" . $newdefault . "]", true);

                $statement = $this->ColumnCreationStatement($column, false);
                if ($statement !== '')
                    $this->Query("alter table " . $tablename . " change " . $oldcolumn . " " . $statement);

                unset($existing[$oldcolumn]);
            }
            else
            {
                $this->SaveToLog("Altering table [" . $tablename . "], column [" . $column[0] . "], adding: name [" . $column[0] . "] " .
                    " type [" . $newtype . "] nullability [" . $newnull . "] default [" . $newdefault . "]", true);

                $statement = $this->ColumnCreationStatement($column, false);
                if ($statement !== '')
                    $this->Query("alter table " . $tablename . " add " . $statement . " " . $lastcolumn);                   
            }

            $lastcolumn = 'AFTER ' . $column[0];
        }

        if ($allowdropcolumn)
        {
            foreach ($existing as $e)
            {
                $this->SaveToLog("Altering table [" . $tablename . "], column [" . $e['Field'] . "], dropping", true);

                $this->Query("alter table " . $tablename . " drop " . $e['Field']);
            }
        }

        sort($newkeys);
        $newkeys = implode(',',$newkeys);

        if ($oldkeys != $newkeys)
        {
            $this->SaveToLog("Altering table [" . $tablename . "], changing keys [" . $oldkeys . "] => [" . $newkeys . "]", true);

            if ($oldkeys !== '')
                $this->Query("alter table " . $tablename . " drop primary key");
            if ($newkeys !== '')    
                $this->Query("alter table " . $tablename . " add primary key (" . $newkeys . ")");
        }
    }
}

Following external functions require explanation:

ColumnCreationStatement provides alter/create table fragment:

private function ColumnCreationStatement(array $columninfo, $includekey = true)
{
    $r = '';

    if ((count($columninfo) > 0) && (preg_match('/^[a-zA-Z0-9_\-]+$/', $columninfo[0])))
    {
        $r .= $columninfo[0];
        if ((count($columninfo) > 1) && (preg_match('/^(int|date|datetime|decimal\([0-9]+,[0-9]+\)|varchar\([0-9]+\)|char\([0-9]+\)|text|tinyint)$/', $columninfo[1])))
            $r .= ' ' . $columninfo[1];
        else
            $r .= ' int';
        if ((count($columninfo) > 2) && is_bool($columninfo[2]))
            $r .= ($columninfo[2] === NOT_NULL ? ' not null' : ' null');
        if ((count($columninfo) > 3) && is_string($columninfo[3]) && ($columninfo[3] !== '') && ($columninfo[1] !== 'text'))
            $r .= " default " . $columninfo[3];
        if ((count($columninfo) > 4) && is_bool($columninfo[4]) && $includekey)
            $r .= ($columninfo[4] === true ? ', primary key(' . $columninfo[0] . ')' : '');
    }

    return $r;
}

TableExists simply verifies whether table is available in database (using show tables like).

Query executes MySql statement (and yes: returns no result ;])

Select and AllRows are shortcuts for returning rows as hashtables collection.

SaveToLog is - I guess - obvious. :-)

And Uti::AnyExists looks like this:

public static function AnyExists($haystack, $needles, $separator = ';')
{
    if (!is_array($needles))
        $needles = explode($separator, $needles);

    foreach ($needles as $needle)
    {
        if (array_search($needle, $haystack) !== false)
            return $needle;
    }

    return false;
}

I hope it all helps. In case of any questions please feel free to ask in comments. :-)