(This advent entry isn't strictly related to Catalyst, but a lot of people use DBIx::Class and SQL::Translator to produce SQL for their DBs, so its still relevant to a lot of you.)

Over the past month and a bit I've been spending some of my precious few spare moments working on improving the difference support in SQL::Translator. This started out as the fun job of splitting out the code so that it wasn't just one big long monolithic function. But you don't care about any of that - you only care what shiny new things it supports.

Producing diffs for MySQL will by default now produce one SQL statement for all the changes to a table, rather than one per changed column. Before you would get SQL like this:

 ALTER TABLE user CHANGE COLUMN gender gender ENUM('male', 'female');
 ALTER TABLE user CHANGE COLUMN handedness handedness ENUM('right', 'left', 'both');
 ALTER TABLE user CHANGE COLUMN profile_type profile_type ENUM('normal', 'top_100') NOT NULL DEFAULT 'normal';

which, while it works, has the downside of being slow for large tables as MySQL would have to copy the table three times to alter each field. So to get round this problem the MySQL producer will now produce

 ALTER TABLE user CHANGE COLUMN gender gender ENUM('male', 'female'),
                  CHANGE COLUMN handedness handedness ENUM('right', 'left', 'both'),
                  CHANGE COLUMN profile_type profile_type ENUM('normal', 'top_100') NOT NULL DEFAULT 'normal';

Much better. And faster too.

This only works if the current version comes from a source that can hold metadata (i.e. pretty much anything but an SQL file.) For example, if you had a column in one of you DBIx::Class result sources called foo and you wanted to rename it to bar, you would do the following:

 __PACKAGE__->add_columns(
   bar => {
     data_type => 'INTEGER',
     extra => { renamed_from => 'foo' }
   }
 )

This would produce SQL like the following:

 ALTER TABLE my_table CHANGE COLUMN foo bar INTEGER;

SQLite doesn't support much in the way of ALTER statements. If you want to do anything other than add a field to the end, you have to create a temporary table, copy data across, replace old table etc. This is tedious to do manually. Now you dont have to, as producing diffs for SQLite can now produce output like this:

 BEGIN TRANSACTION;

 CREATE TEMPORARY TABLE employee_temp_alter (
   position varchar(50) NOT NULL,
   employee_id int(11) NOT NULL,
   PRIMARY KEY (position, employee_id)
 );
 INSERT INTO employee_temp_alter SELECT position, employee_id FROM employee;
 DROP TABLE employee;
 CREATE TABLE employee (
   position varchar(50) NOT NULL,
   employee_id int(11) NOT NULL,
   PRIMARY KEY (position, employee_id)
 );
 INSERT INTO employee SELECT position, employee_id FROM employee_temp_alter;
 DROP TABLE employee_temp_alter;

 COMMIT;

Of course its not fast, but then SQLite isn't in some cases. Since we go through the process of creating a temporary table anyway, SQLite also supports renaming columns.

Previously when using DBIx::Class to produce diff SQL files, it generate the SQL file for the current version, then it would parse this file again. SQL::Translator uses Parse::RecDecent to parse the SQL files, and it is not particularly fast. Once I finish ironing out the last few bugs in it, it will convert the DBIx::Class::Schema to a SQL::Translator::Schema object once, then diff against this. Efficency++

Its not quite finished yet, but you can get it from http://sqlfairy.svn.sourceforge.net/svnroot/sqlfairy/branches/diff-refactor/sqlfairy/ and http://dev.catalyst.perl.org/repos/bast/DBIx-Class/0.08/branches/versioned_enhancements/

If you use a database other than MySQL or SQLite, these features might not work. You can help out by adding the methods needed, in patch form, for your Producer of choice.

Ash Berlin <ash@cpan.org>.