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_options => { 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;
75 ALTER TABLE person ADD COLUMN is_rock_star tinyint(4) 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) DEFAULT '18';
83 ALTER TABLE person CHANGE COLUMN iq iq integer(11) DEFAULT '0';
85 ALTER TABLE person CHANGE COLUMN description physical_description text;
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_options => { 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;
130 ALTER TABLE person DROP INDEX UC_age_name,
131 ADD COLUMN is_rock_star tinyint(4) 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) DEFAULT '18',
135 CHANGE COLUMN iq iq integer(11) DEFAULT '0',
136 CHANGE COLUMN description physical_description text,
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_options => { 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) 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) DEFAULT '18',
205 CHANGE COLUMN iq iq integer(11) DEFAULT '0',
206 CHANGE COLUMN description physical_description text,
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,
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_options => { 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);