[sqlfairy-developers] Questions on MySQL -> SQLite translation

[Peter Leonard]

Hello,

I've been working with SQL::Translator to convert a MySQL schema to 
SQLite, and I've hit a couple of snags that I was hoping for some 
feedback/assistance on:

The following table from MySQL demonstrates the 3 issues I'm dealing with:

   CREATE TABLE `demo` (
     `demo_id`       int(11) NOT NULL auto_increment,
     `exist`         enum('Yes','No') NOT NULL default 'Yes',
     `time_created`  timestamp NOT NULL default '0000-00-00 00:00:00',
     `time_modified` timestamp NOT NULL default CURRENT_TIMESTAMP on
   update CURRENT_TIMESTAMP,
     PRIMARY KEY  (`demo_id`),
     KEY `demo_id` (`demo_id`),
   ) ENGINE=MyISAM DEFAULT CHARSET=latin1;


SQL::Translator turns this into the following:

   BEGIN TRANSACTION;

   CREATE TABLE demo (
     demo_id       INTEGER PRIMARY KEY NOT NULL,
     exist         enum(3) NOT NULL DEFAULT 'Yes',
     time_created  timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
     time_modified timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
   );

   CREATE INDEX demo_id_demo ON demo (demo_id);

   COMMIT;


The three issues are as follows:

1) Column "demo_id": SQLite has a somewhat bizarre way of handling 
autoincrements - default behavior here, as SQL::Translator outputs, 
might re-use row_id's under some circumstances.  The desired definition 
for the column would be:

   demo_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL

This is further explained on the SQLite website here: 
http://www.sqlite.org/autoinc.html

2) The ON UPDATE trigger to the column "time_modified" is lost 
entirely.  Looking at SQL::Translator::Parser::MySQL, I see that it's 
getting parsed and set in $field->extra(), but it's getting lost 
somewhere between parsing and output.

The desired trigger would have the syntax of:

   CREATE TRIGGER demo_time_modified AFTER UPDATE ON demo FOR EACH ROW
     BEGIN
       UPDATE demo SET time_modified=CURRENT_TIMESTAMP;
     END;

3) Enum handling (Column "exist").  Enum handling in SQLite is 
definitely primitive, in that the lookup table and the trigger must be 
created manually by the SQLite user.  This is a lower priority, but 
getting it working would be nice.

I'm happy to kick this around and try to send relevant patches, but the 
SQL::Translator package is pretty large, and some guidance would be a 
huge help before I get too deep into the weeds.

Thank you,

Peter



------------------------------------------------------------------------------
SF.Net email is Sponsored by MIX09, March 18-20, 2009 in Las Vegas, Nevada.
The future of the web can't happen without you.  Join us at MIX09 to help
pave the way to the Next Web now. Learn more and register at
http://ad.doubleclick.net/clk;208669438;13503038;i?http://2009.visitmix.com/-- 
sqlfairy-developers mailing list
sqlfairy-developers@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlfairy-developers