8 use File::Spec::Functions qw(catfile updir tmpdir);
11 use Test::Differences;
12 use Test::SQL::Translator qw(maybe_plan);
13 use SQL::Translator::Schema::Constants;
14 use Storable 'dclone';
18 use_ok('SQL::Translator::Diff') or die "Cannot continue\n";
20 my $tr = SQL::Translator->new;
22 my ( $source_schema, $target_schema, $parsed_sql_schema ) = map {
23 my $t = SQL::Translator->new;
26 my $out = $t->translate( catfile($Bin, qw/data diff/, $_ ) )
29 my $schema = $t->schema;
30 unless ( $schema->name ) {
34 } (qw( create1.yml create2.yml ));
36 # Test for differences
37 my @out = SQL::Translator::Diff::schema_diff(
38 $source_schema, 'MySQL',
39 $target_schema, 'MySQL',
42 producer_args => { quote_table_names => 0 }
46 ok( @out, 'Got a list' );
48 my $out = join('', @out);
50 eq_or_diff($out, <<'## END OF DIFF', "Diff as expected");
51 -- Convert schema 'create1.yml' to 'create2.yml':;
55 SET foreign_key_checks=0;
61 SET foreign_key_checks=1;
63 ALTER TABLE old_name RENAME TO new_name;
65 ALTER TABLE employee DROP FOREIGN KEY FK5302D47D93FE702E;
67 ALTER TABLE person DROP INDEX UC_age_name;
69 ALTER TABLE person DROP INDEX u_name;
71 ALTER TABLE employee DROP COLUMN job_title;
73 ALTER TABLE new_name ADD COLUMN new_field integer NULL;
75 ALTER TABLE person ADD COLUMN is_rock_star tinyint(4) NULL DEFAULT 1;
77 ALTER TABLE person CHANGE COLUMN person_id person_id integer(11) NOT NULL auto_increment;
79 ALTER TABLE person CHANGE COLUMN name name varchar(20) NOT NULL;
81 ALTER TABLE person CHANGE COLUMN age age integer(11) NULL DEFAULT 18;
83 ALTER TABLE person CHANGE COLUMN iq iq integer(11) NULL DEFAULT 0;
85 ALTER TABLE person CHANGE COLUMN description physical_description text NULL;
87 ALTER TABLE person ADD UNIQUE INDEX unique_name (name);
89 ALTER TABLE employee ADD CONSTRAINT FK5302D47D93FE702E_diff FOREIGN KEY (employee_id) REFERENCES person (person_id);
91 ALTER TABLE person ADD UNIQUE UC_person_id (person_id);
93 ALTER TABLE person ADD UNIQUE UC_age_name (age, name);
95 ALTER TABLE person ENGINE=InnoDB;
97 ALTER TABLE deleted DROP FOREIGN KEY fk_fake;
106 $out = SQL::Translator::Diff::schema_diff($source_schema, 'MySQL', $target_schema, 'MySQL',
107 { ignore_index_names => 1,
108 ignore_constraint_names => 1,
109 producer_args => { quote_table_names => 0 },
112 eq_or_diff($out, <<'## END OF DIFF', "Diff as expected");
113 -- Convert schema 'create1.yml' to 'create2.yml':;
117 SET foreign_key_checks=0;
123 SET foreign_key_checks=1;
125 ALTER TABLE employee DROP COLUMN job_title;
127 ALTER TABLE old_name RENAME TO new_name,
128 ADD COLUMN new_field integer NULL;
130 ALTER TABLE person DROP INDEX UC_age_name,
131 ADD COLUMN is_rock_star tinyint(4) NULL DEFAULT 1,
132 CHANGE COLUMN person_id person_id integer(11) NOT NULL auto_increment,
133 CHANGE COLUMN name name varchar(20) NOT NULL,
134 CHANGE COLUMN age age integer(11) NULL DEFAULT 18,
135 CHANGE COLUMN iq iq integer(11) NULL DEFAULT 0,
136 CHANGE COLUMN description physical_description text NULL,
137 ADD UNIQUE UC_person_id (person_id),
138 ADD UNIQUE UC_age_name (age, name),
141 ALTER TABLE deleted DROP FOREIGN KEY fk_fake;
152 $out = SQL::Translator::Diff::schema_diff($source_schema, 'MySQL', $source_schema, 'MySQL' );
154 eq_or_diff($out, <<'## END OF DIFF', "No differences found");
155 -- Convert schema 'create1.yml' to 'create1.yml':;
157 -- No differences found;
162 my $t = SQL::Translator->new;
163 $t->parser( 'MySQL' )
165 my $out = $t->translate( catfile($Bin, qw/data mysql create.sql/ ) )
168 # Lets remove the renamed table so we dont have to change the SQL or other tests
169 $target_schema->drop_table('new_name');
171 my $schema = $t->schema;
172 unless ( $schema->name ) {
173 $schema->name( 'create.sql' );
176 # Now lets change the type of one of the 'integer' columns so that it
177 # matches what the mysql parser sees for '<col> interger'.
178 my $field = $target_schema->get_table('employee')->get_field('employee_id');
179 $field->data_type('integer');
181 $out = SQL::Translator::Diff::schema_diff($schema, 'MySQL', $target_schema, 'MySQL', { producer_args => { quote_table_names => 0 } } );
182 eq_or_diff($out, <<'## END OF DIFF', "No differences found");
183 -- Convert schema 'create.sql' to 'create2.yml':;
187 SET foreign_key_checks=0;
193 SET foreign_key_checks=1;
195 ALTER TABLE employee DROP FOREIGN KEY FK5302D47D93FE702E,
196 DROP COLUMN job_title,
197 ADD CONSTRAINT FK5302D47D93FE702E_diff FOREIGN KEY (employee_id) REFERENCES person (person_id);
199 ALTER TABLE person DROP INDEX UC_age_name,
201 ADD COLUMN is_rock_star tinyint(4) NULL DEFAULT 1,
202 CHANGE COLUMN person_id person_id integer(11) NOT NULL auto_increment,
203 CHANGE COLUMN name name varchar(20) NOT NULL,
204 CHANGE COLUMN age age integer(11) NULL DEFAULT 18,
205 CHANGE COLUMN iq iq integer(11) NULL DEFAULT 0,
206 CHANGE COLUMN description physical_description text NULL,
207 ADD UNIQUE INDEX unique_name (name),
208 ADD UNIQUE UC_person_id (person_id),
209 ADD UNIQUE UC_age_name (age, name),
220 # Test InnoDB stupidness. Have to drop constraints before re-adding them if
221 # they are just alters.
225 my $s1 = SQL::Translator::Schema->new;
226 my $s2 = SQL::Translator::Schema->new;
228 $s1->name('Schema 1');
229 $s2->name('Schema 2');
231 my $t1 = $s1->add_table($target_schema->get_table('employee'));
232 my $t2 = $s2->add_table(dclone($target_schema->get_table('employee')));
235 my ($c) = grep { $_->name eq 'FK5302D47D93FE702E_diff' } $t2->get_constraints;
236 $c->on_delete('CASCADE');
239 name => 'new_constraint',
240 type => 'FOREIGN KEY',
241 fields => ['employee_id'],
242 reference_fields => ['fake'],
243 reference_table => 'patty',
251 my $out = SQL::Translator::Diff::schema_diff($s1, 'MySQL', $s2, 'MySQL' );
253 eq_or_diff($out, <<'## END OF DIFF', "Batch alter of constraints work for InnoDB");
254 -- Convert schema 'Schema 1' to 'Schema 2':;
258 ALTER TABLE employee DROP FOREIGN KEY FK5302D47D93FE702E_diff;
260 ALTER TABLE employee ADD COLUMN new integer NULL,
261 ADD CONSTRAINT FK5302D47D93FE702E_diff FOREIGN KEY (employee_id) REFERENCES person (person_id) ON DELETE CASCADE,
262 ADD CONSTRAINT new_constraint FOREIGN KEY (employee_id) REFERENCES patty (fake);
271 # Test other things about renaming tables to - namely that renames
272 # constraints are still formated right.
274 my $s1 = SQL::Translator::Schema->new;
275 my $s2 = SQL::Translator::Schema->new;
277 $s1->name('Schema 3');
278 $s2->name('Schema 4');
280 my $t1 = $s1->add_table(dclone($target_schema->get_table('employee')));
281 my $t2 = dclone($target_schema->get_table('employee'));
283 $t2->extra(renamed_from => 'employee');
289 type => 'FOREIGN KEY',
290 fields => ['employee_id'],
291 reference_fields => ['id'],
292 reference_table => 'bar',
296 type => 'FOREIGN KEY',
297 fields => ['employee_id'],
298 reference_fields => ['id'],
299 reference_table => 'foo',
302 my $out = SQL::Translator::Diff::schema_diff($s1, 'MySQL', $s2, 'MySQL' );
303 eq_or_diff($out, <<'## END OF DIFF', "Alter/drop constraints works with rename table");
304 -- Convert schema 'Schema 3' to 'Schema 4':;
308 ALTER TABLE employee RENAME TO fnord,
309 DROP FOREIGN KEY bar_fk,
310 ADD CONSTRAINT foo_fk FOREIGN KEY (employee_id) REFERENCES foo (id);
317 # Test quoting works too.
318 $out = SQL::Translator::Diff::schema_diff($s1, 'MySQL', $s2, 'MySQL',
319 { producer_args => { quote_table_names => '`' } }
321 eq_or_diff($out, <<'## END OF DIFF', "Quoting can be turned on");
322 -- Convert schema 'Schema 3' to 'Schema 4':;
326 ALTER TABLE `employee` RENAME TO `fnord`,
327 DROP FOREIGN KEY bar_fk,
328 ADD CONSTRAINT foo_fk FOREIGN KEY (employee_id) REFERENCES `foo` (id);