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} );
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
82 eq_or_diff($out, <<'## END OF DIFF', "Diff as expected");
83 -- Convert schema 'create1.yml' to 'create2.yml':
87 SET foreign_key_checks=0;
95 SET foreign_key_checks=1;
98 ALTER TABLE employee DROP COLUMN job_title;
99 ALTER TABLE old_name RENAME TO new_name,
100 ADD COLUMN new_field integer;
101 ALTER TABLE person DROP INDEX UC_age_name,
102 ADD COLUMN is_rock_star tinyint(4) DEFAULT '1',
103 CHANGE COLUMN person_id person_id integer(11) NOT NULL auto_increment,
104 CHANGE COLUMN name name varchar(20) NOT NULL,
105 CHANGE COLUMN age age integer(11) DEFAULT '18',
106 CHANGE COLUMN iq iq integer(11) DEFAULT '0',
107 CHANGE COLUMN description physical_description text,
108 ADD UNIQUE UC_person_id (person_id),
109 ADD UNIQUE UC_age_name (age, name),
111 ALTER TABLE deleted DROP FOREIGN KEY fk_fake;
119 $out = SQL::Translator::Diff::schema_diff($source_schema, 'MySQL', $source_schema, 'MySQL' );
121 eq_or_diff($out, <<'## END OF DIFF', "No differences found");
122 -- Convert schema 'create1.yml' to 'create1.yml':
124 -- No differences found
129 my $t = SQL::Translator->new;
130 $t->parser( 'MySQL' )
132 my $out = $t->translate( catfile($Bin, qw/data mysql create.sql/ ) )
135 # Lets remove the renamed table so we dont have to change the SQL or other tests
136 $target_schema->drop_table('new_name');
138 my $schema = $t->schema;
139 unless ( $schema->name ) {
140 $schema->name( 'create.sql' );
143 # Now lets change the type of one of the 'integer' columns so that it
144 # matches what the mysql parser sees for '<col> interger'.
145 my $field = $target_schema->get_table('employee')->get_field('employee_id');
146 $field->data_type('integer');
148 $out = SQL::Translator::Diff::schema_diff($schema, 'MySQL', $target_schema, 'MySQL' );
149 eq_or_diff($out, <<'## END OF DIFF', "No differences found");
150 -- Convert schema 'create.sql' to 'create2.yml':
154 SET foreign_key_checks=0;
162 SET foreign_key_checks=1;
165 ALTER TABLE employee DROP FOREIGN KEY FK5302D47D93FE702E,
166 DROP COLUMN job_title,
167 ADD CONSTRAINT FK5302D47D93FE702E_diff FOREIGN KEY (employee_id) REFERENCES person (person_id);
168 ALTER TABLE person DROP INDEX UC_age_name,
170 ADD COLUMN is_rock_star tinyint(4) DEFAULT '1',
171 CHANGE COLUMN person_id person_id integer(11) NOT NULL auto_increment,
172 CHANGE COLUMN name name varchar(20) NOT NULL,
173 CHANGE COLUMN age age integer(11) DEFAULT '18',
174 CHANGE COLUMN iq iq integer(11) DEFAULT '0',
175 CHANGE COLUMN description physical_description text,
176 ADD UNIQUE INDEX unique_name (name),
177 ADD UNIQUE UC_person_id (person_id),
178 ADD UNIQUE UC_age_name (age, name),
186 # Test InnoDB stupidness. Have to drop constraints before re-adding them if
187 # they are just alters.
191 my $s1 = SQL::Translator::Schema->new;
192 my $s2 = SQL::Translator::Schema->new;
194 $s1->name('Schema 1');
195 $s2->name('Schema 2');
197 my $t1 = $s1->add_table($target_schema->get_table('employee'));
198 my $t2 = $s2->add_table(dclone($target_schema->get_table('employee')));
201 my ($c) = grep { $_->name eq 'FK5302D47D93FE702E_diff' } $t2->get_constraints;
202 $c->on_delete('CASCADE');
205 name => 'new_constraint',
206 type => 'FOREIGN KEY',
207 fields => ['employee_id'],
208 reference_fields => ['fake'],
209 reference_table => 'patty',
217 my $out = SQL::Translator::Diff::schema_diff($s1, 'MySQL', $s2, 'MySQL' );
219 eq_or_diff($out, <<'## END OF DIFF', "Batch alter of constraints work for InnoDB");
220 -- Convert schema 'Schema 1' to 'Schema 2':
224 ALTER TABLE employee DROP FOREIGN KEY FK5302D47D93FE702E_diff;
225 ALTER TABLE employee ADD COLUMN new integer,
226 ADD CONSTRAINT FK5302D47D93FE702E_diff FOREIGN KEY (employee_id) REFERENCES person (person_id) ON DELETE CASCADE,
227 ADD CONSTRAINT new_constraint FOREIGN KEY (employee_id) REFERENCES patty (fake);
234 # Test other things about renaming tables to - namely that renames
235 # constraints are still formated right.
237 my $s1 = SQL::Translator::Schema->new;
238 my $s2 = SQL::Translator::Schema->new;
240 $s1->name('Schema 3');
241 $s2->name('Schema 4');
243 my $t1 = $s1->add_table(dclone($target_schema->get_table('employee')));
244 my $t2 = dclone($target_schema->get_table('employee'));
246 $t2->extra(renamed_from => 'employee');
252 type => 'FOREIGN KEY',
253 fields => ['employee_id'],
254 reference_fields => ['id'],
255 reference_table => 'bar',
259 type => 'FOREIGN KEY',
260 fields => ['employee_id'],
261 reference_fields => ['id'],
262 reference_table => 'foo',
265 my $out = SQL::Translator::Diff::schema_diff($s1, 'MySQL', $s2, 'MySQL' );
266 eq_or_diff($out, <<'## END OF DIFF', "Alter/drop constraints works with rename table");
267 -- Convert schema 'Schema 3' to 'Schema 4':
271 ALTER TABLE employee RENAME TO fnord,
272 DROP FOREIGN KEY bar_fk,
273 ADD CONSTRAINT foo_fk FOREIGN KEY (employee_id) REFERENCES foo (id);