Work round MySQL/InnoDB bug http://bugs.mysql.com/bug.php?id=13741

[dbsrgits/SQL-Translator.git] / t / 30sqlt-new-diff-mysql.t

diff --git a/t/30sqlt-new-diff-mysql.t b/t/30sqlt-new-diff-mysql.t
index a4cca87..867931a 100644 (file)
--- a/t/30sqlt-new-diff-mysql.t
+++ b/t/30sqlt-new-diff-mysql.t
@@ -10,8 +10,10 @@ use FindBin qw($Bin);
 use Test::More;
 use Test::Differences;
 use Test::SQL::Translator qw(maybe_plan);
+use SQL::Translator::Schema::Constants;
+use Storable 'dclone';
 
-plan tests => 5;
+plan tests => 6;
 
 use_ok('SQL::Translator::Diff') or die "Cannot continue\n";
 
@@ -36,7 +38,7 @@ my $out = SQL::Translator::Diff::schema_diff( $source_schema, 'MySQL', $target_s
 eq_or_diff($out, <<'## END OF DIFF', "Diff as expected");
 -- Convert schema 'create1.yml' to 'create2.yml':
 
-BEGIN TRANSACTION;
+BEGIN;
 
 SET foreign_key_checks=0;
 
@@ -80,7 +82,7 @@ $out = SQL::Translator::Diff::schema_diff($source_schema, 'MySQL', $target_schem
 eq_or_diff($out, <<'## END OF DIFF', "Diff as expected");
 -- Convert schema 'create1.yml' to 'create2.yml':
 
-BEGIN TRANSACTION;
+BEGIN;
 
 SET foreign_key_checks=0;
 
@@ -147,7 +149,7 @@ eq_or_diff($out, <<'## END OF DIFF', "No differences found");
   eq_or_diff($out, <<'## END OF DIFF', "No differences found");
 -- Convert schema 'create.sql' to 'create2.yml':
 
-BEGIN TRANSACTION;
+BEGIN;
 
 SET foreign_key_checks=0;
 
@@ -180,3 +182,50 @@ DROP TABLE deleted;
 COMMIT;
 ## END OF DIFF
 }
+
+# Test InnoDB stupidness. Have to drop constraints before re-adding them if
+# they are just alters.
+
+
+{
+  my $s1 = SQL::Translator::Schema->new;
+  my $s2 = SQL::Translator::Schema->new;
+
+  $s1->name('Schema 1');
+  $s2->name('Schema 2');
+
+  my $t1 = $s1->add_table($target_schema->get_table('employee'));
+  my $t2 = $s2->add_table(dclone($target_schema->get_table('employee')));
+
+
+  my ($c) = grep { $_->name eq 'FK5302D47D93FE702E_diff' } $t2->get_constraints;
+  $c->on_delete('CASCADE');
+
+  $t2->add_constraint(
+    name => 'new_constraint',
+    type => 'FOREIGN KEY',
+    fields => ['employee_id'],
+    reference_fields => ['fake'],
+    reference_table => 'patty',
+  );
+
+  $t2->add_field(
+    name => 'new',
+    data_type => 'int'
+  );
+
+  $out = SQL::Translator::Diff::schema_diff($s1, 'MySQL', $s2, 'MySQL' );
+
+  eq_or_diff($out, <<'## END OF DIFF', "Batch alter of constraints work for InnoDB");
+-- Convert schema 'Schema 1' to 'Schema 2':
+
+BEGIN;
+
+ALTER TABLE employee DROP FOREIGN KEY FK5302D47D93FE702E_diff;
+ALTER TABLE employee ADD COLUMN new integer,
+                     ADD CONSTRAINT FK5302D47D93FE702E_diff FOREIGN KEY (employee_id) REFERENCES person (person_id) ON DELETE CASCADE,
+                     ADD CONSTRAINT new_constraint FOREIGN KEY (employee_id) REFERENCES patty (fake);
+
+COMMIT;
+## END OF DIFF
+}