8 use File::Spec::Functions qw(catfile updir tmpdir);
11 use Test::Differences;
12 use Test::SQL::Translator qw(maybe_plan);
16 use_ok('SQL::Translator::Diff') or die "Cannot continue\n";
18 my $tr = SQL::Translator->new;
20 my ( $source_schema, $target_schema, $parsed_sql_schema ) = map {
21 my $t = SQL::Translator->new;
24 my $out = $t->translate( catfile($Bin, qw/data diff/, $_ ) )
27 my $schema = $t->schema;
28 unless ( $schema->name ) {
32 } (qw( create1.yml create2.yml ));
34 # Test for differences
35 my $out = SQL::Translator::Diff::schema_diff( $source_schema, 'MySQL', $target_schema, 'MySQL', { no_batch_alters => 1} );
36 eq_or_diff($out, <<'## END OF DIFF', "Diff as expected");
37 -- Convert schema 'create1.yml' to 'create2.yml':
41 SET foreign_key_checks=0;
49 SET foreign_key_checks=1;
52 ALTER TABLE old_name RENAME TO new_name;
53 ALTER TABLE employee DROP FOREIGN KEY FK5302D47D93FE702E;
54 ALTER TABLE person DROP UNIQUE UC_age_name;
55 ALTER TABLE person DROP INDEX u_name;
56 ALTER TABLE employee DROP COLUMN job_title;
57 ALTER TABLE new_name ADD COLUMN new_field integer;
58 ALTER TABLE person ADD COLUMN is_rock_star tinyint(4) DEFAULT '1';
59 ALTER TABLE person CHANGE COLUMN person_id person_id integer(11) NOT NULL auto_increment;
60 ALTER TABLE person CHANGE COLUMN name name varchar(20) NOT NULL;
61 ALTER TABLE person CHANGE COLUMN age age integer(11) DEFAULT '18';
62 ALTER TABLE person CHANGE COLUMN iq iq integer(11) DEFAULT '0';
63 ALTER TABLE person CHANGE COLUMN description physical_description text;
64 ALTER TABLE person ADD UNIQUE INDEX unique_name (name);
65 ALTER TABLE employee ADD CONSTRAINT FK5302D47D93FE702E_diff FOREIGN KEY (employee_id) REFERENCES person (person_id);
66 ALTER TABLE person ADD UNIQUE UC_person_id (person_id);
67 ALTER TABLE person ADD UNIQUE UC_age_name (age, name);
68 ALTER TABLE person ENGINE=InnoDB;
69 ALTER TABLE deleted DROP FOREIGN KEY fk_fake;
75 $out = SQL::Translator::Diff::schema_diff($source_schema, 'MySQL', $target_schema, 'MySQL',
76 { ignore_index_names => 1,
77 ignore_constraint_names => 1
80 eq_or_diff($out, <<'## END OF DIFF', "Diff as expected");
81 -- Convert schema 'create1.yml' to 'create2.yml':
85 SET foreign_key_checks=0;
93 SET foreign_key_checks=1;
96 ALTER TABLE employee DROP COLUMN job_title;
97 ALTER TABLE old_name RENAME TO new_name,
98 ADD COLUMN new_field integer;
99 ALTER TABLE person DROP UNIQUE UC_age_name,
100 ADD COLUMN is_rock_star tinyint(4) DEFAULT '1',
101 CHANGE COLUMN person_id person_id integer(11) NOT NULL auto_increment,
102 CHANGE COLUMN name name varchar(20) NOT NULL,
103 CHANGE COLUMN age age integer(11) DEFAULT '18',
104 CHANGE COLUMN iq iq integer(11) DEFAULT '0',
105 CHANGE COLUMN description physical_description text,
106 ADD UNIQUE UC_person_id (person_id),
107 ADD UNIQUE UC_age_name (age, name),
109 ALTER TABLE deleted DROP FOREIGN KEY fk_fake;
117 $out = SQL::Translator::Diff::schema_diff($source_schema, 'MySQL', $source_schema, 'MySQL' );
119 eq_or_diff($out, <<'## END OF DIFF', "No differences found");
120 -- Convert schema 'create1.yml' to 'create1.yml':
122 -- No differences found
127 my $t = SQL::Translator->new;
128 $t->parser( 'MySQL' )
130 my $out = $t->translate( catfile($Bin, qw/data mysql create.sql/ ) )
133 # Lets remove the renamed table so we dont have to change the SQL or other tests
134 $target_schema->drop_table('new_name');
136 my $schema = $t->schema;
137 unless ( $schema->name ) {
138 $schema->name( 'create.sql' );
141 # Now lets change the type of one of the 'integer' columns so that it
142 # matches what the mysql parser sees for '<col> interger'.
143 my $field = $target_schema->get_table('employee')->get_field('employee_id');
144 $field->data_type('integer');
146 $out = SQL::Translator::Diff::schema_diff($schema, 'MySQL', $target_schema, 'MySQL' );
147 eq_or_diff($out, <<'## END OF DIFF', "No differences found");
148 -- Convert schema 'create.sql' to 'create2.yml':
152 SET foreign_key_checks=0;
160 SET foreign_key_checks=1;
163 ALTER TABLE employee DROP FOREIGN KEY FK5302D47D93FE702E,
164 DROP COLUMN job_title,
165 ADD CONSTRAINT FK5302D47D93FE702E_diff FOREIGN KEY (employee_id) REFERENCES person (person_id);
166 ALTER TABLE person DROP UNIQUE UC_age_name,
168 ADD COLUMN is_rock_star tinyint(4) DEFAULT '1',
169 CHANGE COLUMN person_id person_id integer(11) NOT NULL auto_increment,
170 CHANGE COLUMN name name varchar(20) NOT NULL,
171 CHANGE COLUMN age age integer(11) DEFAULT '18',
172 CHANGE COLUMN iq iq integer(11) DEFAULT '0',
173 CHANGE COLUMN description physical_description text,
174 ADD UNIQUE INDEX unique_name (name),
175 ADD UNIQUE UC_person_id (person_id),
176 ADD UNIQUE UC_age_name (age, name),