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 ADD COLUMN value double(8, 2) NULL DEFAULT 0.00,
203 CHANGE COLUMN person_id person_id integer(11) NOT NULL auto_increment,
204 CHANGE COLUMN name name varchar(20) NOT NULL,
205 CHANGE COLUMN age age integer(11) NULL DEFAULT 18,
206 CHANGE COLUMN iq iq integer(11) NULL DEFAULT 0,
207 CHANGE COLUMN description physical_description text NULL,
208 ADD UNIQUE INDEX unique_name (name),
209 ADD UNIQUE UC_person_id (person_id),
210 ADD UNIQUE UC_age_name (age, name),
221 # Test InnoDB stupidness. Have to drop constraints before re-adding them if
222 # they are just alters.
226 my $s1 = SQL::Translator::Schema->new;
227 my $s2 = SQL::Translator::Schema->new;
229 $s1->name('Schema 1');
230 $s2->name('Schema 2');
232 my $t1 = $s1->add_table($target_schema->get_table('employee'));
233 my $t2 = $s2->add_table(dclone($target_schema->get_table('employee')));
236 my ($c) = grep { $_->name eq 'FK5302D47D93FE702E_diff' } $t2->get_constraints;
237 $c->on_delete('CASCADE');
240 name => 'new_constraint',
241 type => 'FOREIGN KEY',
242 fields => ['employee_id'],
243 reference_fields => ['fake'],
244 reference_table => 'patty',
252 my $out = SQL::Translator::Diff::schema_diff($s1, 'MySQL', $s2, 'MySQL' );
254 eq_or_diff($out, <<'## END OF DIFF', "Batch alter of constraints work for InnoDB");
255 -- Convert schema 'Schema 1' to 'Schema 2':;
259 ALTER TABLE employee DROP FOREIGN KEY FK5302D47D93FE702E_diff;
261 ALTER TABLE employee ADD COLUMN new integer NULL,
262 ADD CONSTRAINT FK5302D47D93FE702E_diff FOREIGN KEY (employee_id) REFERENCES person (person_id) ON DELETE CASCADE,
263 ADD CONSTRAINT new_constraint FOREIGN KEY (employee_id) REFERENCES patty (fake);
272 # Test other things about renaming tables to - namely that renames
273 # constraints are still formated right.
275 my $s1 = SQL::Translator::Schema->new;
276 my $s2 = SQL::Translator::Schema->new;
278 $s1->name('Schema 3');
279 $s2->name('Schema 4');
281 my $t1 = $s1->add_table(dclone($target_schema->get_table('employee')));
282 my $t2 = dclone($target_schema->get_table('employee'));
284 $t2->extra(renamed_from => 'employee');
290 type => 'FOREIGN KEY',
291 fields => ['employee_id'],
292 reference_fields => ['id'],
293 reference_table => 'bar',
297 type => 'FOREIGN KEY',
298 fields => ['employee_id'],
299 reference_fields => ['id'],
300 reference_table => 'foo',
303 my $out = SQL::Translator::Diff::schema_diff($s1, 'MySQL', $s2, 'MySQL' );
304 eq_or_diff($out, <<'## END OF DIFF', "Alter/drop constraints works with rename table");
305 -- Convert schema 'Schema 3' to 'Schema 4':;
309 ALTER TABLE employee RENAME TO fnord,
310 DROP FOREIGN KEY bar_fk,
311 ADD CONSTRAINT foo_fk FOREIGN KEY (employee_id) REFERENCES foo (id);
318 # Test quoting works too.
319 $out = SQL::Translator::Diff::schema_diff($s1, 'MySQL', $s2, 'MySQL',
320 { producer_args => { quote_table_names => '`' } }
322 eq_or_diff($out, <<'## END OF DIFF', "Quoting can be turned on");
323 -- Convert schema 'Schema 3' to 'Schema 4':;
327 ALTER TABLE `employee` RENAME TO `fnord`,
328 DROP FOREIGN KEY bar_fk,
329 ADD CONSTRAINT foo_fk FOREIGN KEY (employee_id) REFERENCES `foo` (id);