| Subject: | Fix round-trip of ON UPDATE in MySQL. |
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 |
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 |
CREATE TABLE `foo` (
`timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='record errors by workers';
| Subject: | roundtrip2.sql |
CREATE TABLE `foo` (
`timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='record errors by workers';