On 12 Feb 2009, at 13:48, Jess Robinson wrote:
>
> 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
I did some work on this recently, so check what's in SVN
-ash
------------------------------------------------------------------------------
Open Source Business Conference (OSBC), March 24-25, 2009, San Francisco, CA
-OSBC tackles the biggest issue in open source: Open Sourcing the Enterprise
-Strategies to boost innovation and cut costs with open source participation
-Receive a $600 discount off the registration fee with the source code: SFAD
http://p.sf.net/sfu/XcvMzF8H
--
sqlfairy-developers mailing list
sqlfairy-developers@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlfairy-developers
