Skip Menu |
 

This queue is for tickets about the SQL-Translator CPAN distribution.

Report information
The Basics
Id: 90827
Status: open
Priority: 0/
Queue: SQL-Translator

People
Owner: Nobody in particular
Requestors: DLAMBLEY [...] cpan.org
Cc:
AdminCc:

Bug Information
Severity: Normal
Broken in:
  • 0.11018
  • 0.11016
Fixed in: (no value)



Subject: Fix round-trip of ON UPDATE in MySQL.
Download (untitled) / with headers
text/plain 897b
The current version of SQL::Translator is unable to successfully round trip an ON UPDATE when generating MySQL ALTER TABLE statements. It is working in 0.11010 as packaged by Ubuntu 12.04. I have attached a patch against git HEAD which fixes the problem. The attached files demonstrate the problem, sqlt-diff roundtrip.sql=MySQL roundtrip2.sql=MySQL This code is experimental, currently the new code only supports MySQL or SQLite diffing. To add support for other databases, please patch the relevant SQL::Translator::Producer:: module. If you need compatibility with the old sqlt-diff, please use sqlt-diff-old, and look into helping us make this one work for you -- Convert schema 'roundtrip.sql' to 'roundtrip2.sql':; BEGIN; ALTER TABLE foo CHANGE COLUMN timestamp timestamp timestamp on update SCALAR(0x220d580) NOT NULL DEFAULT CURRENT_TIMESTAMP; COMMIT; Best regards, Dave Lambley
Subject: roundtrip.patch
Download roundtrip.patch
text/x-diff 1.1k
commit 17445a92700895356ec50a0b12745afd8944cd3f Author: Dave Lambley <dave@adzuna.com> Date: Tue Nov 26 11:51:08 2013 +0000 Correct round-trip of ON UPDATE. ALTER TABLE output failed to dereference scalar reference. diff --git a/t/mysql-on-update-roundtrip.t b/t/mysql-on-update-roundtrip.t new file mode 100644 index 0000000..f8f956c --- /dev/null +++ b/t/mysql-on-update-roundtrip.t @@ -0,0 +1,27 @@ +use strict; +use warnings; + +use Test::More; + +use SQL::Translator; +use SQL::Translator::Producer::MySQL; + +my $sqlt = SQL::Translator->new; +isa_ok($sqlt, "SQL::Translator"); +$sqlt->parser("MySQL"); +$sqlt->data(\q{ + CREATE TABLE `foo` ( + `bar` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, + ); +}); +ok $sqlt->translate(); + +my $field = $sqlt->schema()->get_table("foo")->get_field("bar"); +isa_ok($field, "SQL::Translator::Schema::Field"); + +my $sql = SQL::Translator::Producer::MySQL::create_field($field); +# bad SQL looks like, +# bar timestamp on update SCALAR(0x234d9f8) NOT NULL DEFAULT CURRENT_TIMESTAMP +is($sql, "bar timestamp on update CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP"); + +done_testing();
Subject: roundtrip.sql
Download roundtrip.sql
text/x-sql 154b
CREATE TABLE `foo` ( `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='record errors by workers';
Subject: roundtrip2.sql
Download roundtrip2.sql
text/x-sql 182b
CREATE TABLE `foo` ( `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='record errors by workers';
I omitted the actual fix from the patch. Please find a corrected patch attached.
Subject: roundtrip.patch
Download roundtrip.patch
text/x-diff 1.6k
commit cf000360f20e6767bbc2399cb70b6f1e8e3bb98d Author: Dave Lambley <dave@adzuna.com> Date: Tue Nov 26 12:19:13 2013 +0000 Correct round-trip of ON UPDATE. ALTER TABLE output failed to dereference scalar reference. diff --git a/lib/SQL/Translator/Producer/MySQL.pm b/lib/SQL/Translator/Producer/MySQL.pm index 356f4b6..cd1e102 100644 --- a/lib/SQL/Translator/Producer/MySQL.pm +++ b/lib/SQL/Translator/Producer/MySQL.pm @@ -606,6 +606,9 @@ sub create_field } for my $qual ( 'character set', 'collate', 'on update' ) { my $val = $extra{ $qual } || $extra{ uc $qual } or next; + if (ref($val) eq 'SCALAR') { + $val = ${$val}; + } $field_def .= " $qual $val"; } diff --git a/t/mysql-on-update-roundtrip.t b/t/mysql-on-update-roundtrip.t new file mode 100644 index 0000000..f8f956c --- /dev/null +++ b/t/mysql-on-update-roundtrip.t @@ -0,0 +1,27 @@ +use strict; +use warnings; + +use Test::More; + +use SQL::Translator; +use SQL::Translator::Producer::MySQL; + +my $sqlt = SQL::Translator->new; +isa_ok($sqlt, "SQL::Translator"); +$sqlt->parser("MySQL"); +$sqlt->data(\q{ + CREATE TABLE `foo` ( + `bar` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, + ); +}); +ok $sqlt->translate(); + +my $field = $sqlt->schema()->get_table("foo")->get_field("bar"); +isa_ok($field, "SQL::Translator::Schema::Field"); + +my $sql = SQL::Translator::Producer::MySQL::create_field($field); +# bad SQL looks like, +# bar timestamp on update SCALAR(0x234d9f8) NOT NULL DEFAULT CURRENT_TIMESTAMP +is($sql, "bar timestamp on update CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP"); + +done_testing();
Download (untitled) / with headers
text/plain 269b
The SQL previously generated is not parsed by MySQL. "bar timestamp on update CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP" should be bar timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP" I am not sure where in the code to fix this!
Download (untitled) / with headers
text/plain 548b
On 2013-11-26 14:52:41, DLAMBLEY wrote: Show quoted text
> The SQL previously generated is not parsed by MySQL.
MySQL (at least version 5.5) parses both these just fine. Are you referring to SQL::Translator::Parser::MySQL? Show quoted text
> "bar timestamp on update CURRENT_TIMESTAMP NOT NULL DEFAULT > CURRENT_TIMESTAMP" > > should be > > bar timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE > CURRENT_TIMESTAMP" > > I am not sure where in the code to fix this!
That would be in lib/SQL/Translator/Parser/MySQL.pm, which uses Parse::RecDescent to do the actual work.
Download (untitled) / with headers
text/plain 347b
On Wed Sep 10 12:45:56 2014, ilmari wrote: Show quoted text
> On 2013-11-26 14:52:41, DLAMBLEY wrote:
> > The SQL previously generated is not parsed by MySQL.
> > MySQL (at least version 5.5) parses both these just fine. Are you > referring to SQL::Translator::Parser::MySQL?
I'd have been using MySQL 5.1 (and still am for some things, but that is my problem.)


This service is sponsored and maintained by Best Practical Solutions and runs on Perl.org infrastructure.

Please report any issues with rt.cpan.org to rt-cpan-admin@bestpractical.com.