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( $source_schema, 'MySQL', $target_schema, 'MySQL', { no_batch_alters => 1, producer_options => { quote_table_names => 0 } } );
38 eq_or_diff($out, <<'## END OF DIFF', "Diff as expected");
39 -- Convert schema 'create1.yml' to 'create2.yml':
43 SET foreign_key_checks=0;
51 SET foreign_key_checks=1;
54 ALTER TABLE old_name RENAME TO new_name;
55 ALTER TABLE employee DROP FOREIGN KEY FK5302D47D93FE702E;
56 ALTER TABLE person DROP INDEX UC_age_name;
57 ALTER TABLE person DROP INDEX u_name;
58 ALTER TABLE employee DROP COLUMN job_title;
59 ALTER TABLE new_name ADD COLUMN new_field integer;
60 ALTER TABLE person ADD COLUMN is_rock_star tinyint(4) DEFAULT '1';
61 ALTER TABLE person CHANGE COLUMN person_id person_id integer(11) NOT NULL auto_increment;
62 ALTER TABLE person CHANGE COLUMN name name varchar(20) NOT NULL;
63 ALTER TABLE person CHANGE COLUMN age age integer(11) DEFAULT '18';
64 ALTER TABLE person CHANGE COLUMN iq iq integer(11) DEFAULT '0';
65 ALTER TABLE person CHANGE COLUMN description physical_description text;
66 ALTER TABLE person ADD UNIQUE INDEX unique_name (name);
67 ALTER TABLE employee ADD CONSTRAINT FK5302D47D93FE702E_diff FOREIGN KEY (employee_id) REFERENCES person (person_id);
68 ALTER TABLE person ADD UNIQUE UC_person_id (person_id);
69 ALTER TABLE person ADD UNIQUE UC_age_name (age, name);
70 ALTER TABLE person ENGINE=InnoDB;
71 ALTER TABLE deleted DROP FOREIGN KEY fk_fake;
77 $out = SQL::Translator::Diff::schema_diff($source_schema, 'MySQL', $target_schema, 'MySQL',
78 { ignore_index_names => 1,
79 ignore_constraint_names => 1,
80 producer_options => { quote_table_names => 0 },
83 eq_or_diff($out, <<'## END OF DIFF', "Diff as expected");
84 -- Convert schema 'create1.yml' to 'create2.yml':
88 SET foreign_key_checks=0;
96 SET foreign_key_checks=1;
99 ALTER TABLE employee DROP COLUMN job_title;
100 ALTER TABLE old_name RENAME TO new_name,
101 ADD COLUMN new_field integer;
102 ALTER TABLE person DROP INDEX UC_age_name,
103 ADD COLUMN is_rock_star tinyint(4) DEFAULT '1',
104 CHANGE COLUMN person_id person_id integer(11) NOT NULL auto_increment,
105 CHANGE COLUMN name name varchar(20) NOT NULL,
106 CHANGE COLUMN age age integer(11) DEFAULT '18',
107 CHANGE COLUMN iq iq integer(11) DEFAULT '0',
108 CHANGE COLUMN description physical_description text,
109 ADD UNIQUE UC_person_id (person_id),
110 ADD UNIQUE UC_age_name (age, name),
112 ALTER TABLE deleted DROP FOREIGN KEY fk_fake;
120 $out = SQL::Translator::Diff::schema_diff($source_schema, 'MySQL', $source_schema, 'MySQL' );
122 eq_or_diff($out, <<'## END OF DIFF', "No differences found");
123 -- Convert schema 'create1.yml' to 'create1.yml':
125 -- No differences found
130 my $t = SQL::Translator->new;
131 $t->parser( 'MySQL' )
133 my $out = $t->translate( catfile($Bin, qw/data mysql create.sql/ ) )
136 # Lets remove the renamed table so we dont have to change the SQL or other tests
137 $target_schema->drop_table('new_name');
139 my $schema = $t->schema;
140 unless ( $schema->name ) {
141 $schema->name( 'create.sql' );
144 # Now lets change the type of one of the 'integer' columns so that it
145 # matches what the mysql parser sees for '<col> interger'.
146 my $field = $target_schema->get_table('employee')->get_field('employee_id');
147 $field->data_type('integer');
149 $out = SQL::Translator::Diff::schema_diff($schema, 'MySQL', $target_schema, 'MySQL', { producer_options => { quote_table_names => 0 } } );
150 eq_or_diff($out, <<'## END OF DIFF', "No differences found");
151 -- Convert schema 'create.sql' to 'create2.yml':
155 SET foreign_key_checks=0;
163 SET foreign_key_checks=1;
166 ALTER TABLE employee DROP FOREIGN KEY FK5302D47D93FE702E,
167 DROP COLUMN job_title,
168 ADD CONSTRAINT FK5302D47D93FE702E_diff FOREIGN KEY (employee_id) REFERENCES person (person_id);
169 ALTER TABLE person DROP INDEX UC_age_name,
171 ADD COLUMN is_rock_star tinyint(4) DEFAULT '1',
172 CHANGE COLUMN person_id person_id integer(11) NOT NULL auto_increment,
173 CHANGE COLUMN name name varchar(20) NOT NULL,
174 CHANGE COLUMN age age integer(11) DEFAULT '18',
175 CHANGE COLUMN iq iq integer(11) DEFAULT '0',
176 CHANGE COLUMN description physical_description text,
177 ADD UNIQUE INDEX unique_name (name),
178 ADD UNIQUE UC_person_id (person_id),
179 ADD UNIQUE UC_age_name (age, name),
187 # Test InnoDB stupidness. Have to drop constraints before re-adding them if
188 # they are just alters.
192 my $s1 = SQL::Translator::Schema->new;
193 my $s2 = SQL::Translator::Schema->new;
195 $s1->name('Schema 1');
196 $s2->name('Schema 2');
198 my $t1 = $s1->add_table($target_schema->get_table('employee'));
199 my $t2 = $s2->add_table(dclone($target_schema->get_table('employee')));
202 my ($c) = grep { $_->name eq 'FK5302D47D93FE702E_diff' } $t2->get_constraints;
203 $c->on_delete('CASCADE');
206 name => 'new_constraint',
207 type => 'FOREIGN KEY',
208 fields => ['employee_id'],
209 reference_fields => ['fake'],
210 reference_table => 'patty',
218 my $out = SQL::Translator::Diff::schema_diff($s1, 'MySQL', $s2, 'MySQL' );
220 eq_or_diff($out, <<'## END OF DIFF', "Batch alter of constraints work for InnoDB");
221 -- Convert schema 'Schema 1' to 'Schema 2':
225 ALTER TABLE employee DROP FOREIGN KEY FK5302D47D93FE702E_diff;
226 ALTER TABLE employee ADD COLUMN new integer,
227 ADD CONSTRAINT FK5302D47D93FE702E_diff FOREIGN KEY (employee_id) REFERENCES person (person_id) ON DELETE CASCADE,
228 ADD CONSTRAINT new_constraint FOREIGN KEY (employee_id) REFERENCES patty (fake);
235 # Test other things about renaming tables to - namely that renames
236 # constraints are still formated right.
238 my $s1 = SQL::Translator::Schema->new;
239 my $s2 = SQL::Translator::Schema->new;
241 $s1->name('Schema 3');
242 $s2->name('Schema 4');
244 my $t1 = $s1->add_table(dclone($target_schema->get_table('employee')));
245 my $t2 = dclone($target_schema->get_table('employee'));
247 $t2->extra(renamed_from => 'employee');
253 type => 'FOREIGN KEY',
254 fields => ['employee_id'],
255 reference_fields => ['id'],
256 reference_table => 'bar',
260 type => 'FOREIGN KEY',
261 fields => ['employee_id'],
262 reference_fields => ['id'],
263 reference_table => 'foo',
266 my $out = SQL::Translator::Diff::schema_diff($s1, 'MySQL', $s2, 'MySQL' );
267 eq_or_diff($out, <<'## END OF DIFF', "Alter/drop constraints works with rename table");
268 -- Convert schema 'Schema 3' to 'Schema 4':
272 ALTER TABLE employee RENAME TO fnord,
273 DROP FOREIGN KEY bar_fk,
274 ADD CONSTRAINT foo_fk FOREIGN KEY (employee_id) REFERENCES foo (id);
279 # Test quoting works too.
280 $out = SQL::Translator::Diff::schema_diff($s1, 'MySQL', $s2, 'MySQL',
281 { producer_options => { quote_table_names => '`' } }
283 eq_or_diff($out, <<'## END OF DIFF', "Quoting can be turned on");
284 -- Convert schema 'Schema 3' to 'Schema 4':
288 ALTER TABLE `employee` RENAME TO `fnord`,
289 DROP FOREIGN KEY bar_fk,
290 ADD CONSTRAINT foo_fk FOREIGN KEY (employee_id) REFERENCES `foo` (id);