Generating ALTER TABLE code for MySQL

blogical has asked for the wisdom of the Perl Monks concerning the following question:

Not necessarily perl, but I'm currently headed towards a perlish solution...

I have two databases, one for development and another for production. They do not share the same data. When I make structural changes in development, I need to apply them to production when I update.

As I am unaware of any native MySQL function combination that addresses this specific need I've begun to think about a perlsih solution.

My current solution is to dump the table structure creation sql, apply some alterations to translate CREATE statements into ALTER / CHANGE statements, and apply that as a patch. This has a few limitations- it doesn't drop leftover columns or keys, and it doesn't create missing columns. While this seems to suit my purposes, I thought I would ask around and see if anyone has seen a similar / better solution.

Edit
Here's what I'm using at the moment. YMMV.

#Step 1. Dump/export table creation sql
#     Note table and field names enclosed in backquotes
#Step 2. Run this filter on it to create alter patch
#Step 3. Manually add commands to drop keys, drop and create table / f
+ield to top of patch.
#     Note: created tables and fields needn't be accurate, they just n
+eed to exist
#Step 4. Run this patch on the database

while (<>) {
    s/^\s*CREATE TABLE (`\w+`) \(/ALTER TABLE $1\n/;
    s/^\s*(`\w+`)/  CHANGE $1 $1/;
    s/^\s*PRIMARY KEY/  DROP PRIMARY KEY, ADD PRIMARY KEY/;
    s/^\s*UNIQUE KEY (`\w+`)/  DROP INDEX $1, ADD UNIQUE $1/;
    s/^\s*KEY (`\w+`)/  DROP INDEX $1, ADD INDEX $1/;
    s/^\)//;
    s/ENGINE=(\w+)/ ENGINE=$1,\n/;
    s/DEFAULT CHARSET=(\w+)/ DEFAULT CHARSET=$1/;
    s/^\s+(.+?),?\s*$/  $1,\n/;
    print;
}
[download]

b.logical

"One is enough. If you are acquainted with the principle, what do you care for the myriad instances and applications?"
- Henry David Thoreau, Walden

Comment on Generating ALTER TABLE code for MySQL Download Code

Replies are listed 'Best First'.
Re: Generating ALTER TABLE code for MySQL by liz (Monsignor) on Oct 21, 2006 at 09:28 UTC
Version 5.0 of MySQL has: `CREATE TABLE new_tbl LIKE orig_tbl;` [download] Use LIKE to create an empty table based on the definition of another table, including any column attributes and indexes defined in the original table (see http://dev.mysql.com/doc/refman/5.0/en/create-table.html ). Maybe something like that, in combination with a SELECT from the table into the new table, and some table renames, would also be an option? Liz	[reply] [d/l]
Re: Generating ALTER TABLE code for MySQL by adrianh (Chancellor) on Oct 21, 2006 at 11:05 UTC
I have two databases, one for development and another for production. They do not share the same data. When I make structural changes in development, I need to apply them to production when I update. You might want to look at mysqldiff and SQLFairy.	[reply]
Re^2: Generating ALTER TABLE code for MySQL by soonix (Chancellor) on May 28, 2018 at 09:03 UTC
Link rot: twelve years later, SQLFairy still has the old URL, however, mysqldiff seems to live now here (and on github).	[reply]
Re: Generating ALTER TABLE code for MySQL by jdtoronto (Prior) on Oct 21, 2006 at 12:13 UTC
Some of this functionality is also available prior to 5.0 using the CREATE .. SELECT syntax. `CREATE TABLE new_tbl SELECT * FROM orig_tbl; MySQL creates new columns for all elements in the SELECT. For example: mysql> CREATE TABLE test (a INT NOT NULL AUTO_INCREMENT, -> PRIMARY KEY (a), KEY(b)) -> TYPE=MyISAM SELECT b,c FROM test2;` [download] I know it works in 4.1 and I think it works in 4.0, but you would need to check. jdtoronto	[reply] [d/l]
Re: Generating ALTER TABLE code for MySQL by Cabrion (Friar) on Oct 21, 2006 at 12:49 UTC
You might checkout the SQL::Translator module. The included sqlt script can essentially "diff" two database schemas. Not the fastest tool in the world, but it's impressive.	[reply]
Re: Generating ALTER TABLE code for MySQL by badaiaqrandista (Pilgrim) on Oct 22, 2006 at 09:02 UTC
This is how I do it: Create a table called version in each database, containing only one integer field named version. This table will only ever hold one row containing its version. Create an sql directory in the source directory tree. This table holds sql files with names like 0.sql, 1.sql, 2.sql, etc. All those files contain valid sql statements to create table, drop table, create column, updates, etc. The file names must be sequentially created. The 0.sql contains the initial schema (usually the output of mysqldump without the data). The other *.sql files usually contain various database modification statements. Create a script that checks the latest version of the database and applies each sql files with the number higher than the version. Update the version table after applying each sql file. I actually have the script but it still contains my company's specific code in it. If you don't mind playing around with it to make it work in your situation, I probably can send it to you upon request. This is basically a primitive database versioning system. I recently added a hook functionality into it, which will be automatically called before or after applying an sql file. If I have time, I'd like to submit it to CPAN. But that's quite a big 'if' considering my current workload. Hope that helps. -cheepy-	[reply]