[dbsrgits/SQL-Translator.git] / t / 30sqlt-new-diff-mysql.t

#!/usr/bin/perl
# vim: set ft=perl:

use strict;
use warnings;
use SQL::Translator;

use File::Spec::Functions qw(catfile updir tmpdir);
use FindBin qw($Bin);
use Test::More;
use Test::Differences;
use Test::SQL::Translator qw(maybe_plan);
use SQL::Translator::Schema::Constants;
use Storable 'dclone';

plan tests => 7;

use_ok('SQL::Translator::Diff') or die "Cannot continue\n";

my $tr            = SQL::Translator->new;

my ( $source_schema, $target_schema, $parsed_sql_schema ) = map {
    my $t = SQL::Translator->new;
    $t->parser( 'YAML' )
      or die $tr->error;
    my $out = $t->translate( catfile($Bin, qw/data diff/, $_ ) )
      or die $tr->error;
    
    my $schema = $t->schema;
    unless ( $schema->name ) {
        $schema->name( $_ );
    }
    ($schema);
} (qw( create1.yml create2.yml ));

# Test for differences
my $out = SQL::Translator::Diff::schema_diff( $source_schema, 'MySQL', $target_schema, 'MySQL', { no_batch_alters => 1} );
eq_or_diff($out, <<'## END OF DIFF', "Diff as expected");
-- Convert schema 'create1.yml' to 'create2.yml':

BEGIN;

SET foreign_key_checks=0;


CREATE TABLE added (
  id integer(11)
);


SET foreign_key_checks=1;


ALTER TABLE old_name RENAME TO new_name;
ALTER TABLE employee DROP FOREIGN KEY FK5302D47D93FE702E;
ALTER TABLE person DROP UNIQUE UC_age_name;
ALTER TABLE person DROP INDEX u_name;
ALTER TABLE employee DROP COLUMN job_title;
ALTER TABLE new_name ADD COLUMN new_field integer;
ALTER TABLE person ADD COLUMN is_rock_star tinyint(4) DEFAULT '1';
ALTER TABLE person CHANGE COLUMN person_id person_id integer(11) NOT NULL auto_increment;
ALTER TABLE person CHANGE COLUMN name name varchar(20) NOT NULL;
ALTER TABLE person CHANGE COLUMN age age integer(11) DEFAULT '18';
ALTER TABLE person CHANGE COLUMN iq iq integer(11) DEFAULT '0';
ALTER TABLE person CHANGE COLUMN description physical_description text;
ALTER TABLE person ADD UNIQUE INDEX unique_name (name);
ALTER TABLE employee ADD CONSTRAINT FK5302D47D93FE702E_diff FOREIGN KEY (employee_id) REFERENCES person (person_id);
ALTER TABLE person ADD UNIQUE UC_person_id (person_id);
ALTER TABLE person ADD UNIQUE UC_age_name (age, name);
ALTER TABLE person ENGINE=InnoDB;
ALTER TABLE deleted DROP FOREIGN KEY fk_fake;
DROP TABLE deleted;

COMMIT;
## END OF DIFF

$out = SQL::Translator::Diff::schema_diff($source_schema, 'MySQL', $target_schema, 'MySQL',
    { ignore_index_names => 1,
      ignore_constraint_names => 1
    });

eq_or_diff($out, <<'## END OF DIFF', "Diff as expected");
-- Convert schema 'create1.yml' to 'create2.yml':

BEGIN;

SET foreign_key_checks=0;


CREATE TABLE added (
  id integer(11)
);


SET foreign_key_checks=1;


ALTER TABLE employee DROP COLUMN job_title;
ALTER TABLE old_name RENAME TO new_name,
                     ADD COLUMN new_field integer;
ALTER TABLE person DROP UNIQUE UC_age_name,
                   ADD COLUMN is_rock_star tinyint(4) DEFAULT '1',
                   CHANGE COLUMN person_id person_id integer(11) NOT NULL auto_increment,
                   CHANGE COLUMN name name varchar(20) NOT NULL,
                   CHANGE COLUMN age age integer(11) DEFAULT '18',
                   CHANGE COLUMN iq iq integer(11) DEFAULT '0',
                   CHANGE COLUMN description physical_description text,
                   ADD UNIQUE UC_person_id (person_id),
                   ADD UNIQUE UC_age_name (age, name),
                   ENGINE=InnoDB;
ALTER TABLE deleted DROP FOREIGN KEY fk_fake;
DROP TABLE deleted;

COMMIT;
## END OF DIFF


# Test for sameness
$out = SQL::Translator::Diff::schema_diff($source_schema, 'MySQL', $source_schema, 'MySQL' );

eq_or_diff($out, <<'## END OF DIFF', "No differences found");
-- Convert schema 'create1.yml' to 'create1.yml':

-- No differences found

## END OF DIFF

{
  my $t = SQL::Translator->new;
  $t->parser( 'MySQL' )
    or die $tr->error;
  my $out = $t->translate( catfile($Bin, qw/data mysql create.sql/ ) )
    or die $tr->error;

  # Lets remove the renamed table so we dont have to change the SQL or other tests
  $target_schema->drop_table('new_name');
  
  my $schema = $t->schema;
  unless ( $schema->name ) {
      $schema->name( 'create.sql' );
  }

  # Now lets change the type of one of the 'integer' columns so that it 
  # matches what the mysql parser sees for '<col> interger'.
  my $field = $target_schema->get_table('employee')->get_field('employee_id');
  $field->data_type('integer');
  $field->size(0);
  $out = SQL::Translator::Diff::schema_diff($schema, 'MySQL', $target_schema, 'MySQL' );
  eq_or_diff($out, <<'## END OF DIFF', "No differences found");
-- Convert schema 'create.sql' to 'create2.yml':

BEGIN;

SET foreign_key_checks=0;


CREATE TABLE added (
  id integer(11)
);


SET foreign_key_checks=1;


ALTER TABLE employee DROP FOREIGN KEY FK5302D47D93FE702E,
                     DROP COLUMN job_title,
                     ADD CONSTRAINT FK5302D47D93FE702E_diff FOREIGN KEY (employee_id) REFERENCES person (person_id);
ALTER TABLE person DROP UNIQUE UC_age_name,
                   DROP INDEX u_name,
                   ADD COLUMN is_rock_star tinyint(4) DEFAULT '1',
                   CHANGE COLUMN person_id person_id integer(11) NOT NULL auto_increment,
                   CHANGE COLUMN name name varchar(20) NOT NULL,
                   CHANGE COLUMN age age integer(11) DEFAULT '18',
                   CHANGE COLUMN iq iq integer(11) DEFAULT '0',
                   CHANGE COLUMN description physical_description text,
                   ADD UNIQUE INDEX unique_name (name),
                   ADD UNIQUE UC_person_id (person_id),
                   ADD UNIQUE UC_age_name (age, name),
                   ENGINE=InnoDB;
DROP TABLE deleted;

COMMIT;
## END OF DIFF
}

# Test InnoDB stupidness. Have to drop constraints before re-adding them if
# they are just alters.


{
  my $s1 = SQL::Translator::Schema->new;
  my $s2 = SQL::Translator::Schema->new;

  $s1->name('Schema 1');
  $s2->name('Schema 2');

  my $t1 = $s1->add_table($target_schema->get_table('employee'));
  my $t2 = $s2->add_table(dclone($target_schema->get_table('employee')));


  my ($c) = grep { $_->name eq 'FK5302D47D93FE702E_diff' } $t2->get_constraints;
  $c->on_delete('CASCADE');

  $t2->add_constraint(
    name => 'new_constraint',
    type => 'FOREIGN KEY',
    fields => ['employee_id'],
    reference_fields => ['fake'],
    reference_table => 'patty',
  );

  $t2->add_field(
    name => 'new',
    data_type => 'int'
  );

  my $out = SQL::Translator::Diff::schema_diff($s1, 'MySQL', $s2, 'MySQL' );

  eq_or_diff($out, <<'## END OF DIFF', "Batch alter of constraints work for InnoDB");
-- Convert schema 'Schema 1' to 'Schema 2':

BEGIN;

ALTER TABLE employee DROP FOREIGN KEY FK5302D47D93FE702E_diff;
ALTER TABLE employee ADD COLUMN new integer,
                     ADD CONSTRAINT FK5302D47D93FE702E_diff FOREIGN KEY (employee_id) REFERENCES person (person_id) ON DELETE CASCADE,
                     ADD CONSTRAINT new_constraint FOREIGN KEY (employee_id) REFERENCES patty (fake);

COMMIT;
## END OF DIFF
}

{
  # Test other things about renaming tables to - namely that renames 
  # constraints are still formated right.

  my $s1 = SQL::Translator::Schema->new;
  my $s2 = SQL::Translator::Schema->new;

  $s1->name('Schema 3');
  $s2->name('Schema 4');

  my $t1 = $s1->add_table(dclone($target_schema->get_table('employee')));
  my $t2 = dclone($target_schema->get_table('employee'));
  $t2->name('fnord');
  $t2->extra(renamed_from => 'employee');
  $s2->add_table($t2);


  $t1->add_constraint(
    name => 'bar_fk',
    type => 'FOREIGN KEY',
    fields => ['employee_id'],
    reference_fields => ['id'],
    reference_table => 'bar',
  );
  $t2->add_constraint(
    name => 'foo_fk',
    type => 'FOREIGN KEY',
    fields => ['employee_id'],
    reference_fields => ['id'],
    reference_table => 'foo',
  );

  my $out = SQL::Translator::Diff::schema_diff($s1, 'MySQL', $s2, 'MySQL' );
  eq_or_diff($out, <<'## END OF DIFF', "Alter/drop constraints works with rename table");
-- Convert schema 'Schema 3' to 'Schema 4':

BEGIN;

ALTER TABLE employee RENAME TO fnord,
                     DROP FOREIGN KEY bar_fk,
                     ADD CONSTRAINT foo_fk FOREIGN KEY (employee_id) REFERENCES foo (id);

COMMIT;
## END OF DIFF
}
Commit	Line	Data
4d438549	1	#!/usr/bin/perl
	2	# vim: set ft=perl:
	3
	4	use strict;
	5	use warnings;
	6	use SQL::Translator;
	7
	8	use File::Spec::Functions qw(catfile updir tmpdir);
	9	use FindBin qw($Bin);
	10	use Test::More;
	11	use Test::Differences;
	12	use Test::SQL::Translator qw(maybe_plan);
f9ed5d54	13	use SQL::Translator::Schema::Constants;
f9ed5d54	14	use Storable 'dclone';
4d438549	15
4104f82b	16	plan tests => 7;
4d438549	17
	18	use_ok('SQL::Translator::Diff') or die "Cannot continue\n";
	19
	20	my $tr = SQL::Translator->new;
	21
07d6e5f7	22	my ( $source_schema, $target_schema, $parsed_sql_schema ) = map {
4d438549	23	my $t = SQL::Translator->new;
	24	$t->parser( 'YAML' )
	25	or die $tr->error;
	26	my $out = $t->translate( catfile($Bin, qw/data diff/, $_ ) )
	27	or die $tr->error;
	28
	29	my $schema = $t->schema;
	30	unless ( $schema->name ) {
	31	$schema->name( $_ );
	32	}
	33	($schema);
07d6e5f7	34	} (qw( create1.yml create2.yml ));
4d438549	35
	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':
	40
f9ed5d54	41	BEGIN;
4d438549	42
	43	SET foreign_key_checks=0;
	44
	45
	46	CREATE TABLE added (
	47	id integer(11)
	48	);
	49
	50
	51	SET foreign_key_checks=1;
	52
	53
46bf5655	54	ALTER TABLE old_name RENAME TO new_name;
4d438549	55	ALTER TABLE employee DROP FOREIGN KEY FK5302D47D93FE702E;
	56	ALTER TABLE person DROP UNIQUE UC_age_name;
	57	ALTER TABLE person DROP INDEX u_name;
	58	ALTER TABLE employee DROP COLUMN job_title;
46bf5655	59	ALTER TABLE new_name ADD COLUMN new_field integer;
4d438549	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;
	72	DROP TABLE deleted;
	73
	74	COMMIT;
	75	## END OF DIFF
	76
4d438549	77	$out = SQL::Translator::Diff::schema_diff($source_schema, 'MySQL', $target_schema, 'MySQL',
	78	{ ignore_index_names => 1,
	79	ignore_constraint_names => 1
	80	});
	81
	82	eq_or_diff($out, <<'## END OF DIFF', "Diff as expected");
	83	-- Convert schema 'create1.yml' to 'create2.yml':
	84
f9ed5d54	85	BEGIN;
4d438549	86
	87	SET foreign_key_checks=0;
	88
	89
	90	CREATE TABLE added (
	91	id integer(11)
	92	);
	93
	94
	95	SET foreign_key_checks=1;
	96
	97
	98	ALTER TABLE employee DROP COLUMN job_title;
46bf5655	99	ALTER TABLE old_name RENAME TO new_name,
46bf5655	100	ADD COLUMN new_field integer;
4d438549	101	ALTER TABLE person DROP UNIQUE 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),
	110	ENGINE=InnoDB;
	111	ALTER TABLE deleted DROP FOREIGN KEY fk_fake;
	112	DROP TABLE deleted;
	113
	114	COMMIT;
	115	## END OF DIFF
	116
	117
	118	# Test for sameness
	119	$out = SQL::Translator::Diff::schema_diff($source_schema, 'MySQL', $source_schema, 'MySQL' );
	120
	121	eq_or_diff($out, <<'## END OF DIFF', "No differences found");
	122	-- Convert schema 'create1.yml' to 'create1.yml':
	123
	124	-- No differences found
	125
	126	## END OF DIFF
	127
07d6e5f7	128	{
	129	my $t = SQL::Translator->new;
	130	$t->parser( 'MySQL' )
	131	or die $tr->error;
	132	my $out = $t->translate( catfile($Bin, qw/data mysql create.sql/ ) )
	133	or die $tr->error;
46bf5655	134
	135	# Lets remove the renamed table so we dont have to change the SQL or other tests
	136	$target_schema->drop_table('new_name');
07d6e5f7	137
	138	my $schema = $t->schema;
	139	unless ( $schema->name ) {
	140	$schema->name( 'create.sql' );
	141	}
	142
	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');
	147	$field->size(0);
	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':
	151
f9ed5d54	152	BEGIN;
07d6e5f7	153
	154	SET foreign_key_checks=0;
	155
	156
	157	CREATE TABLE added (
	158	id integer(11)
	159	);
	160
	161
	162	SET foreign_key_checks=1;
	163
	164
	165	ALTER TABLE employee DROP FOREIGN KEY FK5302D47D93FE702E,
	166	DROP COLUMN job_title,
1c680eb9	167	ADD CONSTRAINT FK5302D47D93FE702E_diff FOREIGN KEY (employee_id) REFERENCES person (person_id);
07d6e5f7	168	ALTER TABLE person DROP UNIQUE UC_age_name,
	169	DROP INDEX u_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),
	179	ENGINE=InnoDB;
	180	DROP TABLE deleted;
	181
	182	COMMIT;
	183	## END OF DIFF
	184	}
f9ed5d54	185
	186	# Test InnoDB stupidness. Have to drop constraints before re-adding them if
	187	# they are just alters.
	188
	189
	190	{
	191	my $s1 = SQL::Translator::Schema->new;
	192	my $s2 = SQL::Translator::Schema->new;
	193
	194	$s1->name('Schema 1');
	195	$s2->name('Schema 2');
	196
	197	my $t1 = $s1->add_table($target_schema->get_table('employee'));
	198	my $t2 = $s2->add_table(dclone($target_schema->get_table('employee')));
	199
	200
	201	my ($c) = grep { $_->name eq 'FK5302D47D93FE702E_diff' } $t2->get_constraints;
	202	$c->on_delete('CASCADE');
	203
	204	$t2->add_constraint(
	205	name => 'new_constraint',
	206	type => 'FOREIGN KEY',
	207	fields => ['employee_id'],
	208	reference_fields => ['fake'],
	209	reference_table => 'patty',
	210	);
	211
	212	$t2->add_field(
	213	name => 'new',
	214	data_type => 'int'
	215	);
	216
4104f82b	217	my $out = SQL::Translator::Diff::schema_diff($s1, 'MySQL', $s2, 'MySQL' );
f9ed5d54	218
	219	eq_or_diff($out, <<'## END OF DIFF', "Batch alter of constraints work for InnoDB");
	220	-- Convert schema 'Schema 1' to 'Schema 2':
	221
	222	BEGIN;
	223
	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);
	228
	229	COMMIT;
	230	## END OF DIFF
	231	}
4104f82b	232
	233	{
	234	# Test other things about renaming tables to - namely that renames
	235	# constraints are still formated right.
	236
	237	my $s1 = SQL::Translator::Schema->new;
	238	my $s2 = SQL::Translator::Schema->new;
	239
	240	$s1->name('Schema 3');
	241	$s2->name('Schema 4');
	242
	243	my $t1 = $s1->add_table(dclone($target_schema->get_table('employee')));
	244	my $t2 = dclone($target_schema->get_table('employee'));
	245	$t2->name('fnord');
	246	$t2->extra(renamed_from => 'employee');
	247	$s2->add_table($t2);
	248
	249
	250	$t1->add_constraint(
	251	name => 'bar_fk',
	252	type => 'FOREIGN KEY',
	253	fields => ['employee_id'],
	254	reference_fields => ['id'],
	255	reference_table => 'bar',
	256	);
	257	$t2->add_constraint(
	258	name => 'foo_fk',
	259	type => 'FOREIGN KEY',
	260	fields => ['employee_id'],
	261	reference_fields => ['id'],
	262	reference_table => 'foo',
	263	);
	264
	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':
	268
	269	BEGIN;
	270
	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);
	274
	275	COMMIT;
	276	## END OF DIFF
	277	}