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 ) = 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_identifiers => 0 }
46 ok( @out, 'Got a list' );
48 my $out = join('', @out);
50 eq_or_diff($out, <<'## END OF DIFF', "Diff as expected", { context => 1 });
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 CONSTRAINT 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_identifiers => 0 },
112 eq_or_diff($out, <<'## END OF DIFF', "Diff as expected", { context => 1 });
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 CONSTRAINT 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", { context => 1 });
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_identifiers => 0 } } );
182 eq_or_diff($out, <<'## END OF DIFF', "No differences found", { context => 1 });
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 CONSTRAINT demo_constraint,
197 DROP COLUMN job_title,
198 ADD CONSTRAINT FK5302D47D93FE702E_diff FOREIGN KEY (employee_id) REFERENCES person (person_id);
200 ALTER TABLE person DROP CONSTRAINT UC_age_name,
202 ADD COLUMN is_rock_star tinyint(4) NULL DEFAULT 1,
203 ADD COLUMN value double(8, 2) NULL DEFAULT 0.00,
204 CHANGE COLUMN person_id person_id integer(11) NOT NULL auto_increment,
205 CHANGE COLUMN name name varchar(20) NOT NULL,
206 CHANGE COLUMN age age integer(11) NULL DEFAULT 18,
207 CHANGE COLUMN iq iq integer(11) NULL DEFAULT 0,
208 CHANGE COLUMN description physical_description text NULL,
209 ADD UNIQUE INDEX unique_name (name),
210 ADD UNIQUE UC_person_id (person_id),
211 ADD UNIQUE UC_age_name (age, name),
222 # Test InnoDB stupidness. Have to drop constraints before re-adding them if
223 # they are just alters.
227 my $s1 = SQL::Translator::Schema->new;
228 my $s2 = SQL::Translator::Schema->new;
230 $s1->name('Schema 1');
231 $s2->name('Schema 2');
233 my $t1 = $s1->add_table($target_schema->get_table('employee'));
234 my $t2 = $s2->add_table(dclone($target_schema->get_table('employee')));
237 my ($c) = grep { $_->name eq 'FK5302D47D93FE702E_diff' } $t2->get_constraints;
238 $c->on_delete('CASCADE');
241 name => 'new_constraint',
242 type => 'FOREIGN KEY',
243 fields => ['employee_id'],
244 reference_fields => ['fake'],
245 reference_table => 'patty',
253 my $out = SQL::Translator::Diff::schema_diff($s1, 'MySQL', $s2, 'MySQL' );
255 eq_or_diff($out, <<'## END OF DIFF', "Batch alter of constraints work for InnoDB", { context => 1 });
256 -- Convert schema 'Schema 1' to 'Schema 2':;
260 ALTER TABLE employee DROP FOREIGN KEY FK5302D47D93FE702E_diff;
262 ALTER TABLE employee ADD COLUMN new integer NULL,
263 ADD CONSTRAINT FK5302D47D93FE702E_diff FOREIGN KEY (employee_id) REFERENCES person (person_id) ON DELETE CASCADE,
264 ADD CONSTRAINT new_constraint FOREIGN KEY (employee_id) REFERENCES patty (fake);
273 # Test other things about renaming tables to - namely that renames
274 # constraints are still formated right.
276 my $s1 = SQL::Translator::Schema->new;
277 my $s2 = SQL::Translator::Schema->new;
279 $s1->name('Schema 3');
280 $s2->name('Schema 4');
282 my $t1 = $s1->add_table(dclone($target_schema->get_table('employee')));
283 $s1->add_table(dclone($source_schema->get_table('deleted')));
284 my $t2 = dclone($target_schema->get_table('employee'));
286 $t2->extra(renamed_from => 'employee');
292 type => 'FOREIGN KEY',
293 fields => ['employee_id'],
294 reference_fields => ['id'],
295 reference_table => 'bar',
299 type => 'FOREIGN KEY',
300 fields => ['employee_id'],
301 reference_fields => ['id'],
302 reference_table => 'foo',
305 my $out = SQL::Translator::Diff::schema_diff($s1, 'MySQL', $s2, 'MySQL' );
306 eq_or_diff($out, <<'## END OF DIFF', "Alter/drop constraints works with rename table", { context => 1 });
307 -- Convert schema 'Schema 3' to 'Schema 4':;
311 ALTER TABLE employee RENAME TO fnord,
312 DROP FOREIGN KEY bar_fk,
313 ADD CONSTRAINT foo_fk FOREIGN KEY (employee_id) REFERENCES foo (id);
315 ALTER TABLE deleted DROP FOREIGN KEY fk_fake;
324 # Test quoting works too.
325 $out = SQL::Translator::Diff::schema_diff($s1, 'MySQL', $s2, 'MySQL',
326 { producer_args => { quote_identifiers => 1 } }
328 eq_or_diff($out, <<'## END OF DIFF', "Quoting can be turned on", { context => 1 });
329 -- Convert schema 'Schema 3' to 'Schema 4':;
333 ALTER TABLE `employee` RENAME TO `fnord`,
334 DROP FOREIGN KEY `bar_fk`,
335 ADD CONSTRAINT `foo_fk` FOREIGN KEY (`employee_id`) REFERENCES `foo` (`id`);
337 ALTER TABLE `deleted` DROP FOREIGN KEY `fk_fake`;
339 DROP TABLE `deleted`;