[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 => 8;

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, producer_options => { quote_table_names => 0 } } );
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 INDEX 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,
      producer_options => { quote_table_names => 0 },
    });

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 INDEX 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', { producer_options => { quote_table_names => 0 } } );
  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 INDEX 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

  # Test quoting works too.
  $out = SQL::Translator::Diff::schema_diff($s1, 'MySQL', $s2, 'MySQL', 
    { producer_options => { quote_table_names => '`' } }
  );
  eq_or_diff($out, <<'## END OF DIFF', "Quoting can be turned on");
-- 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
7467c458	16	plan tests => 8;
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
4d438549	36	# Test for differences
7467c458	37	my $out = SQL::Translator::Diff::schema_diff( $source_schema, 'MySQL', $target_schema, 'MySQL', { no_batch_alters => 1, producer_options => { quote_table_names => 0 } } );
4d438549	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;
74ca32ce	56	ALTER TABLE person DROP INDEX UC_age_name;
4d438549	57	ALTER TABLE person DROP INDEX u_name;
4d438549	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',
4d438549	78	{ ignore_index_names => 1,
7467c458	79	ignore_constraint_names => 1,
7467c458	80	producer_options => { quote_table_names => 0 },
4d438549	81	});
	82
	83	eq_or_diff($out, <<'## END OF DIFF', "Diff as expected");
	84	-- Convert schema 'create1.yml' to 'create2.yml':
	85
f9ed5d54	86	BEGIN;
4d438549	87
	88	SET foreign_key_checks=0;
	89
	90
	91	CREATE TABLE added (
	92	id integer(11)
	93	);
	94
	95
	96	SET foreign_key_checks=1;
	97
	98
	99	ALTER TABLE employee DROP COLUMN job_title;
46bf5655	100	ALTER TABLE old_name RENAME TO new_name,
46bf5655	101	ADD COLUMN new_field integer;
74ca32ce	102	ALTER TABLE person DROP INDEX UC_age_name,
4d438549	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),
	111	ENGINE=InnoDB;
	112	ALTER TABLE deleted DROP FOREIGN KEY fk_fake;
	113	DROP TABLE deleted;
	114
	115	COMMIT;
	116	## END OF DIFF
	117
	118
	119	# Test for sameness
	120	$out = SQL::Translator::Diff::schema_diff($source_schema, 'MySQL', $source_schema, 'MySQL' );
	121
	122	eq_or_diff($out, <<'## END OF DIFF', "No differences found");
	123	-- Convert schema 'create1.yml' to 'create1.yml':
	124
	125	-- No differences found
	126
	127	## END OF DIFF
	128
07d6e5f7	129	{
	130	my $t = SQL::Translator->new;
	131	$t->parser( 'MySQL' )
	132	or die $tr->error;
	133	my $out = $t->translate( catfile($Bin, qw/data mysql create.sql/ ) )
	134	or die $tr->error;
46bf5655	135
	136	# Lets remove the renamed table so we dont have to change the SQL or other tests
	137	$target_schema->drop_table('new_name');
07d6e5f7	138
	139	my $schema = $t->schema;
	140	unless ( $schema->name ) {
	141	$schema->name( 'create.sql' );
	142	}
	143
	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');
	148	$field->size(0);
7467c458	149	$out = SQL::Translator::Diff::schema_diff($schema, 'MySQL', $target_schema, 'MySQL', { producer_options => { quote_table_names => 0 } } );
07d6e5f7	150	eq_or_diff($out, <<'## END OF DIFF', "No differences found");
	151	-- Convert schema 'create.sql' to 'create2.yml':
	152
f9ed5d54	153	BEGIN;
07d6e5f7	154
	155	SET foreign_key_checks=0;
	156
	157
	158	CREATE TABLE added (
	159	id integer(11)
	160	);
	161
	162
	163	SET foreign_key_checks=1;
	164
	165
	166	ALTER TABLE employee DROP FOREIGN KEY FK5302D47D93FE702E,
	167	DROP COLUMN job_title,
1c680eb9	168	ADD CONSTRAINT FK5302D47D93FE702E_diff FOREIGN KEY (employee_id) REFERENCES person (person_id);
74ca32ce	169	ALTER TABLE person DROP INDEX UC_age_name,
07d6e5f7	170	DROP INDEX u_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),
	180	ENGINE=InnoDB;
	181	DROP TABLE deleted;
	182
	183	COMMIT;
	184	## END OF DIFF
	185	}
f9ed5d54	186
	187	# Test InnoDB stupidness. Have to drop constraints before re-adding them if
	188	# they are just alters.
	189
	190
	191	{
	192	my $s1 = SQL::Translator::Schema->new;
	193	my $s2 = SQL::Translator::Schema->new;
	194
	195	$s1->name('Schema 1');
	196	$s2->name('Schema 2');
	197
	198	my $t1 = $s1->add_table($target_schema->get_table('employee'));
	199	my $t2 = $s2->add_table(dclone($target_schema->get_table('employee')));
	200
	201
	202	my ($c) = grep { $_->name eq 'FK5302D47D93FE702E_diff' } $t2->get_constraints;
	203	$c->on_delete('CASCADE');
	204
	205	$t2->add_constraint(
	206	name => 'new_constraint',
	207	type => 'FOREIGN KEY',
	208	fields => ['employee_id'],
	209	reference_fields => ['fake'],
	210	reference_table => 'patty',
	211	);
	212
	213	$t2->add_field(
	214	name => 'new',
	215	data_type => 'int'
	216	);
	217
4104f82b	218	my $out = SQL::Translator::Diff::schema_diff($s1, 'MySQL', $s2, 'MySQL' );
f9ed5d54	219
	220	eq_or_diff($out, <<'## END OF DIFF', "Batch alter of constraints work for InnoDB");
	221	-- Convert schema 'Schema 1' to 'Schema 2':
	222
	223	BEGIN;
	224
	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);
	229
	230	COMMIT;
	231	## END OF DIFF
	232	}
4104f82b	233
	234	{
	235	# Test other things about renaming tables to - namely that renames
	236	# constraints are still formated right.
	237
	238	my $s1 = SQL::Translator::Schema->new;
	239	my $s2 = SQL::Translator::Schema->new;
	240
	241	$s1->name('Schema 3');
	242	$s2->name('Schema 4');
	243
	244	my $t1 = $s1->add_table(dclone($target_schema->get_table('employee')));
	245	my $t2 = dclone($target_schema->get_table('employee'));
	246	$t2->name('fnord');
	247	$t2->extra(renamed_from => 'employee');
	248	$s2->add_table($t2);
	249
	250
	251	$t1->add_constraint(
	252	name => 'bar_fk',
	253	type => 'FOREIGN KEY',
	254	fields => ['employee_id'],
	255	reference_fields => ['id'],
	256	reference_table => 'bar',
	257	);
	258	$t2->add_constraint(
	259	name => 'foo_fk',
	260	type => 'FOREIGN KEY',
	261	fields => ['employee_id'],
	262	reference_fields => ['id'],
	263	reference_table => 'foo',
	264	);
	265
	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':
	269
	270	BEGIN;
	271
	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);
	275
	276	COMMIT;
	277	## END OF DIFF
7467c458	278
	279	# Test quoting works too.
	280	$out = SQL::Translator::Diff::schema_diff($s1, 'MySQL', $s2, 'MySQL',
	281	{ producer_options => { quote_table_names => '`' } }
	282	);
	283	eq_or_diff($out, <<'## END OF DIFF', "Quoting can be turned on");
	284	-- Convert schema 'Schema 3' to 'Schema 4':
	285
	286	BEGIN;
	287
	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);
	291
	292	COMMIT;
	293	## END OF DIFF
4104f82b	294	}