X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?a=blobdiff_plain;f=t%2F30sqlt-new-diff-mysql.t;h=5a9997e968b60ecc4a6457073701c03f0de4fd08;hb=d4924f25d6cc897fa44e9bd9f0123308f63abc11;hp=eca0a2dc364037e224dca80a15553bd8ed6277ea;hpb=07d6e5f7a00c14f44c4f2cab58bbb0a4dbe65567;p=dbsrgits%2FSQL-Translator.git

diff --git a/t/30sqlt-new-diff-mysql.t b/t/30sqlt-new-diff-mysql.t
index eca0a2d..5a9997e 100644
--- 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 => 8;
 
 use_ok('SQL::Translator::Diff') or die "Cannot continue\n";
 
@@ -32,67 +34,88 @@ my ( $source_schema, $target_schema, $parsed_sql_schema ) = map {
 } (qw( create1.yml create2.yml ));
 
 # Test for differences
-my $out = SQL::Translator::Diff::schema_diff( $source_schema, 'MySQL', $target_schema, 'MySQL', { no_batch_alters => 1} );
+my $out = SQL::Translator::Diff::schema_diff( $source_schema, 'MySQL', $target_schema, 'MySQL', { no_batch_alters => 1, producer_options => { quote_table_names => 0 } } );
 eq_or_diff($out, <<'## END OF DIFF', "Diff as expected");
--- Convert schema 'create1.yml' to 'create2.yml':
+-- Convert schema 'create1.yml' to 'create2.yml':;
 
-BEGIN TRANSACTION;
+BEGIN;
 
 SET foreign_key_checks=0;
 
-
 CREATE TABLE added (
   id integer(11)
 );
 
-
 SET foreign_key_checks=1;
 
+ALTER TABLE old_name RENAME TO new_name;
 
 ALTER TABLE employee DROP FOREIGN KEY FK5302D47D93FE702E;
-ALTER TABLE person DROP UNIQUE UC_age_name;
+
+ALTER TABLE person DROP INDEX UC_age_name;
+
 ALTER TABLE person DROP INDEX u_name;
+
 ALTER TABLE employee DROP COLUMN job_title;
+
+ALTER TABLE new_name ADD COLUMN new_field integer;
+
 ALTER TABLE person ADD COLUMN is_rock_star tinyint(4) DEFAULT '1';
+
 ALTER TABLE person CHANGE COLUMN person_id person_id integer(11) NOT NULL auto_increment;
+
 ALTER TABLE person CHANGE COLUMN name name varchar(20) NOT NULL;
+
 ALTER TABLE person CHANGE COLUMN age age integer(11) DEFAULT '18';
+
 ALTER TABLE person CHANGE COLUMN iq iq integer(11) DEFAULT '0';
+
 ALTER TABLE person CHANGE COLUMN description physical_description text;
+
 ALTER TABLE person ADD UNIQUE INDEX unique_name (name);
+
 ALTER TABLE employee ADD CONSTRAINT FK5302D47D93FE702E_diff FOREIGN KEY (employee_id) REFERENCES person (person_id);
+
 ALTER TABLE person ADD UNIQUE UC_person_id (person_id);
+
 ALTER TABLE person ADD UNIQUE UC_age_name (age, name);
+
 ALTER TABLE person ENGINE=InnoDB;
+
 ALTER TABLE deleted DROP FOREIGN KEY fk_fake;
+
 DROP TABLE deleted;
 
+
 COMMIT;
+
 ## END OF DIFF
 
 $out = SQL::Translator::Diff::schema_diff($source_schema, 'MySQL', $target_schema, 'MySQL',
     { ignore_index_names => 1,
-      ignore_constraint_names => 1
+      ignore_constraint_names => 1,
+      producer_options => { quote_table_names => 0 },
     });
 
 eq_or_diff($out, <<'## END OF DIFF', "Diff as expected");
--- Convert schema 'create1.yml' to 'create2.yml':
+-- Convert schema 'create1.yml' to 'create2.yml':;
 
-BEGIN TRANSACTION;
+BEGIN;
 
 SET foreign_key_checks=0;
 
-
 CREATE TABLE added (
   id integer(11)
 );
 
-
 SET foreign_key_checks=1;
 
-
 ALTER TABLE employee DROP COLUMN job_title;
-ALTER TABLE person DROP UNIQUE UC_age_name,
+
+ALTER TABLE old_name RENAME TO new_name,
+                     ADD COLUMN new_field integer;
+
+ALTER TABLE person DROP INDEX UC_age_name,
                    ADD COLUMN is_rock_star tinyint(4) DEFAULT '1',
                    CHANGE COLUMN person_id person_id integer(11) NOT NULL auto_increment,
                    CHANGE COLUMN name name varchar(20) NOT NULL,
@@ -102,10 +125,14 @@ ALTER TABLE person DROP UNIQUE UC_age_name,
                    ADD UNIQUE UC_person_id (person_id),
                    ADD UNIQUE UC_age_name (age, name),
                    ENGINE=InnoDB;
+
 ALTER TABLE deleted DROP FOREIGN KEY fk_fake;
+
 DROP TABLE deleted;
 
+
 COMMIT;
+
 ## END OF DIFF
 
 
@@ -113,9 +140,9 @@ COMMIT;
 $out = SQL::Translator::Diff::schema_diff($source_schema, 'MySQL', $source_schema, 'MySQL' );
 
 eq_or_diff($out, <<'## END OF DIFF', "No differences found");
--- Convert schema 'create1.yml' to 'create1.yml':
+-- Convert schema 'create1.yml' to 'create1.yml':;
 
--- No differences found
+-- No differences found;
 
 ## END OF DIFF
 
@@ -125,6 +152,9 @@ eq_or_diff($out, <<'## END OF DIFF', "No differences found");
     or die $tr->error;
   my $out = $t->translate( catfile($Bin, qw/data mysql create.sql/ ) )
     or die $tr->error;
+
+  # Lets remove the renamed table so we dont have to change the SQL or other tests
+  $target_schema->drop_table('new_name');
   
   my $schema = $t->schema;
   unless ( $schema->name ) {
@@ -136,27 +166,25 @@ eq_or_diff($out, <<'## END OF DIFF', "No differences found");
   my $field = $target_schema->get_table('employee')->get_field('employee_id');
   $field->data_type('integer');
   $field->size(0);
-  $out = SQL::Translator::Diff::schema_diff($schema, 'MySQL', $target_schema, 'MySQL' );
+  $out = SQL::Translator::Diff::schema_diff($schema, 'MySQL', $target_schema, 'MySQL', { producer_options => { quote_table_names => 0 } } );
   eq_or_diff($out, <<'## END OF DIFF', "No differences found");
--- Convert schema 'create.sql' to 'create2.yml':
+-- Convert schema 'create.sql' to 'create2.yml':;
 
-BEGIN TRANSACTION;
+BEGIN;
 
 SET foreign_key_checks=0;
 
-
 CREATE TABLE added (
   id integer(11)
 );
 
-
 SET foreign_key_checks=1;
 
-
 ALTER TABLE employee DROP FOREIGN KEY FK5302D47D93FE702E,
                      DROP COLUMN job_title,
-                     ADD CONSTRAINT FK5302D47D93FE702E_diff_1 FOREIGN KEY (employee_id) REFERENCES person (person_id);
-ALTER TABLE person DROP UNIQUE UC_age_name,
+                     ADD CONSTRAINT FK5302D47D93FE702E_diff FOREIGN KEY (employee_id) REFERENCES person (person_id);
+
+ALTER TABLE person DROP INDEX UC_age_name,
                    DROP INDEX u_name,
                    ADD COLUMN is_rock_star tinyint(4) DEFAULT '1',
                    CHANGE COLUMN person_id person_id integer(11) NOT NULL auto_increment,
@@ -168,8 +196,127 @@ ALTER TABLE person DROP UNIQUE UC_age_name,
                    ADD UNIQUE UC_person_id (person_id),
                    ADD UNIQUE UC_age_name (age, name),
                    ENGINE=InnoDB;
+
 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'
+  );
+
+  my $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
+}
+
+{
+  # Test other things about renaming tables to - namely that renames 
+  # constraints are still formated right.
+
+  my $s1 = SQL::Translator::Schema->new;
+  my $s2 = SQL::Translator::Schema->new;
+
+  $s1->name('Schema 3');
+  $s2->name('Schema 4');
+
+  my $t1 = $s1->add_table(dclone($target_schema->get_table('employee')));
+  my $t2 = dclone($target_schema->get_table('employee'));
+  $t2->name('fnord');
+  $t2->extra(renamed_from => 'employee');
+  $s2->add_table($t2);
+
+
+  $t1->add_constraint(
+    name => 'bar_fk',
+    type => 'FOREIGN KEY',
+    fields => ['employee_id'],
+    reference_fields => ['id'],
+    reference_table => 'bar',
+  );
+  $t2->add_constraint(
+    name => 'foo_fk',
+    type => 'FOREIGN KEY',
+    fields => ['employee_id'],
+    reference_fields => ['id'],
+    reference_table => 'foo',
+  );
+
+  my $out = SQL::Translator::Diff::schema_diff($s1, 'MySQL', $s2, 'MySQL' );
+  eq_or_diff($out, <<'## END OF DIFF', "Alter/drop constraints works with rename table");
+-- Convert schema 'Schema 3' to 'Schema 4':;
+
+BEGIN;
+
+ALTER TABLE employee RENAME TO fnord,
+                     DROP FOREIGN KEY bar_fk,
+                     ADD CONSTRAINT foo_fk FOREIGN KEY (employee_id) REFERENCES foo (id);
+
+
+COMMIT;
+
+## END OF DIFF
+
+  # Test quoting works too.
+  $out = SQL::Translator::Diff::schema_diff($s1, 'MySQL', $s2, 'MySQL', 
+    { producer_options => { quote_table_names => '`' } }
+  );
+  eq_or_diff($out, <<'## END OF DIFF', "Quoting can be turned on");
+-- Convert schema 'Schema 3' to 'Schema 4':;
+
+BEGIN;
+
+ALTER TABLE `employee` RENAME TO `fnord`,
+                       DROP FOREIGN KEY bar_fk,
+                       ADD CONSTRAINT foo_fk FOREIGN KEY (employee_id) REFERENCES `foo` (id);
+
+
+COMMIT;
+
 ## END OF DIFF
 }