Hi Peter!
On Mon, 15 Dec 2008, Peter Leonard wrote:
> 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.
Thanks!
What you've discovered is, that the SQLite producer doesn't support the
full syntax of the latest SQLite releases. Ditto the MySQL producer, I'm
not sure it parses triggers into actual trigger objects either.
> 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.
That would be great.
By the sounds of it, you want to modify SQL::Translator::Producer::SQLite.
You'll see there is a $sqlite_version in the current one. So please find
out which version of sqlite these new features appeared in, and only have
the parser output them if the given version matches or is above that
value.
As for the trigger/ ON UPDATE, you'll need to modify the Producer::MySQL
to create those properly as SQL::Translator::Schema::Constraint objects .
See Parser::Oracle for an example of using $table->add_constraint with
"on_update", it looks like the MySQL parser does create constraints, just
not with on update at all.
I hope that gets you started.
Jess
------------------------------------------------------------------------------
Create and Deploy Rich Internet Apps outside the browser with Adobe(R)AIR(TM)
software. With Adobe AIR, Ajax developers can use existing skills and code to
build responsive, highly engaging applications that combine the power of local
resources and data with the reach of the web. Download the Adobe AIR SDK and
Ajax docs to start building applications today-http://p.sf.net/sfu/adobe-com
--
sqlfairy-developers mailing list
sqlfairy-developers@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlfairy-developers
