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
