| Subject: | sqlite->mysql stringifies hashrefs in CREATE TRIGGER |
::Parser::SQLite returns a hashref for 'trigger_action':
https://metacpan.org/source/FREW/SQL-Translator-0.11018/lib/SQL/Translator/Parser/SQLite.pm#L519
::Producer::MySQL expects $action->trigger to be a string:
https://metacpan.org/source/FREW/SQL-Translator-0.11018/lib/SQL/Translator/Producer/MySQL.pm#L345
Giving output similar to the following when there's a multi-step trigger in SQLite:
CREATE TRIGGER `delete_movie` after delete ON `movie`
FOR EACH ROW BEGIN HASH(0x2fea3d0); END;
I've attached a demo/failing test.
It's not clear what the correct format of ->action() should be.
If you give some direction I'm happy to investigate:
* making ::Parser::SQLite return a string
* making ::Producer::MySQL handle strings and SQLite-esque hashrefs
similar to: https://metacpan.org/source/FREW/SQL-Translator-0.11018/lib/SQL/Translator/Producer/SQLite.pm#L344
* making ->action stringify automatically (this is slightly outside my experience and comfort zone)
| Subject: | 99.sqlite-to-mysql.t |
#!/usr/bin/env perl
use strict;
use warnings;
use SQL::Translator;
use Test::More;
# This is a hugely cut down schema source to illustrate the issue reported in
# RT#xxxxxx
# It's taken from a '.dump' of the XBMC sqlite schema
my $sqlite_in =<<EOSCHEMA;
CREATE TABLE movie (
idMovie integer PRIMARY KEY,
idFile integer,
c00 text,
c01 text,
-- and so on
c22 text,
c23 text,
idSet integer);
CREATE TABLE art(art_id INTEGER PRIMARY KEY, media_id INTEGER, media_type TEXT, TYPE TEXT, url TEXT);
CREATE TRIGGER delete_movie AFTER
DELETE ON movie
FOR EACH ROW BEGIN
DELETE
FROM art
WHERE media_id=old.idMovie
AND media_type='movie';
DELETE
FROM taglinks WHERE idMedia=old.idMovie
AND media_type='movie'; END;
EOSCHEMA
my $sqlt = SQL::Translator->new(
show_warnings => 1,
no_comments => 1,
from => "SQLite",
quote_table_names => 1,
quote_field_names => 1,
);
# make sure we report/choose a MySQL version that produces VIEWs
$sqlt->producer('MySQL', mysql_version => 5.5);
my $out = $sqlt->translate(\$sqlite_in)
or die "Translate error:".$sqlt->error;
ok $out ne "", "Produced something!";
# we should NOT see a CREATE TRIGGER with a hashref-address in it
unlike(
$out,
qr/FOR EACH ROW BEGIN HASH\(0x[0-9a-f]+\); END/
);
done_testing;