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 => 9;
use_ok('SQL::Translator::Diff') or die "Cannot continue\n";
-my $tr = SQL::Translator->new;
+my $tr = SQL::Translator->new;
my ( $source_schema, $target_schema, $parsed_sql_schema ) = map {
my $t = SQL::Translator->new;
or die $tr->error;
my $out = $t->translate( catfile($Bin, qw/data diff/, $_ ) )
or die $tr->error;
-
+
my $schema = $t->schema;
unless ( $schema->name ) {
$schema->name( $_ );
} (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_args => { quote_identifiers => 0 }
+ }
+);
+
+ok( @out, 'Got a list' );
+
+my $out = join('', @out);
+
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)
+ id integer(11) NULL
);
-
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 person ADD COLUMN is_rock_star tinyint(4) DEFAULT '1';
+
+ALTER TABLE new_name ADD COLUMN new_field integer NULL;
+
+ALTER TABLE person ADD COLUMN is_rock_star tinyint(4) NULL 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 CHANGE COLUMN age age integer(11) NULL DEFAULT 18;
+
+ALTER TABLE person CHANGE COLUMN iq iq integer(11) NULL DEFAULT 0;
+
+ALTER TABLE person CHANGE COLUMN description physical_description text NULL;
+
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_args => { quote_identifiers => 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)
+ id integer(11) NULL
);
-
SET foreign_key_checks=1;
-
ALTER TABLE employee DROP COLUMN job_title;
-ALTER TABLE person DROP UNIQUE UC_age_name,
- ADD COLUMN is_rock_star tinyint(4) DEFAULT '1',
+
+ALTER TABLE old_name RENAME TO new_name,
+ ADD COLUMN new_field integer NULL;
+
+ALTER TABLE person DROP INDEX UC_age_name,
+ ADD COLUMN is_rock_star tinyint(4) NULL DEFAULT 1,
CHANGE COLUMN person_id person_id integer(11) NOT NULL auto_increment,
CHANGE COLUMN name name varchar(20) NOT NULL,
- CHANGE COLUMN age age integer(11) DEFAULT '18',
- CHANGE COLUMN iq iq integer(11) DEFAULT '0',
- CHANGE COLUMN description physical_description text,
+ CHANGE COLUMN age age integer(11) NULL DEFAULT 18,
+ CHANGE COLUMN iq iq integer(11) NULL DEFAULT 0,
+ CHANGE COLUMN description physical_description text NULL,
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
$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
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 ) {
$schema->name( 'create.sql' );
}
- # Now lets change the type of one of the 'integer' columns so that it
+ # Now lets change the type of one of the 'integer' columns so that it
# matches what the mysql parser sees for '<col> interger'.
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_args => { quote_identifiers => 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)
+ id integer(11) NULL
);
-
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',
+ ADD COLUMN is_rock_star tinyint(4) NULL DEFAULT 1,
+ ADD COLUMN value double(8, 2) NULL DEFAULT 0.00,
CHANGE COLUMN person_id person_id integer(11) NOT NULL auto_increment,
CHANGE COLUMN name name varchar(20) NOT NULL,
- CHANGE COLUMN age age integer(11) DEFAULT '18',
- CHANGE COLUMN iq iq integer(11) DEFAULT '0',
- CHANGE COLUMN description physical_description text,
+ CHANGE COLUMN age age integer(11) NULL DEFAULT 18,
+ CHANGE COLUMN iq iq integer(11) NULL DEFAULT 0,
+ CHANGE COLUMN description physical_description text NULL,
ADD UNIQUE INDEX unique_name (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 NULL,
+ 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_args => { quote_identifiers => 1 } }
+ );
+ 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
}
projects / dbsrgits/SQL-Translator.git / blobdiff