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 => 9;

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

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

my ($source_schema, $target_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,
    sqlt_args       => { quote_identifiers => 0 }
  }
);

ok(@out, 'Got a list');

my $out = join('', @out);

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

BEGIN;

SET foreign_key_checks=0;

CREATE TABLE added (
  id integer(11) NULL
);

SET foreign_key_checks=1;

ALTER TABLE old_name RENAME TO new_name;

ALTER TABLE employee DROP FOREIGN KEY FK5302D47D93FE702E;

ALTER TABLE person DROP CONSTRAINT 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 NULL;

ALTER TABLE person ADD COLUMN is_rock_star tinyint(4) NULL 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) NULL DEFAULT 18;

ALTER TABLE person CHANGE COLUMN iq iq integer(11) NULL DEFAULT 0;

ALTER TABLE person CHANGE COLUMN description physical_description text NULL;

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,
    sqlt_args               => { quote_identifiers => 0 },
  }
);

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

BEGIN;

SET foreign_key_checks=0;

CREATE TABLE added (
  id integer(11) NULL
);

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 NULL;

ALTER TABLE person DROP CONSTRAINT UC_age_name,
                   ADD COLUMN is_rock_star tinyint(4) NULL 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) NULL DEFAULT 18,
                   CHANGE COLUMN iq iq integer(11) NULL DEFAULT 0,
                   CHANGE COLUMN description physical_description text NULL,
                   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", { context => 1 });
-- 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', { sqlt_args => { quote_identifiers => 0 } });
  eq_or_diff($out, <<'## END OF DIFF', "No differences found", { context => 1 });
-- Convert schema 'create.sql' to 'create2.yml':;

BEGIN;

SET foreign_key_checks=0;

CREATE TABLE added (
  id integer(11) NULL
);

SET foreign_key_checks=1;

ALTER TABLE employee DROP FOREIGN KEY FK5302D47D93FE702E,
                     DROP CONSTRAINT demo_constraint,
                     DROP COLUMN job_title,
                     ADD CONSTRAINT FK5302D47D93FE702E_diff FOREIGN KEY (employee_id) REFERENCES person (person_id);

ALTER TABLE person DROP CONSTRAINT UC_age_name,
                   DROP INDEX u_name,
                   ADD COLUMN is_rock_star tinyint(4) NULL DEFAULT 1,
                   ADD COLUMN value double(8, 2) NULL DEFAULT 0.00,
                   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) NULL DEFAULT 18,
                   CHANGE COLUMN iq iq integer(11) NULL DEFAULT 0,
                   CHANGE COLUMN description physical_description text NULL,
                   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", { context => 1 });
-- Convert schema 'Schema 1' to 'Schema 2':;

BEGIN;

ALTER TABLE employee DROP FOREIGN KEY FK5302D47D93FE702E_diff;

ALTER TABLE employee ADD COLUMN new integer NULL,
                     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')));
  $s1->add_table(dclone($source_schema->get_table('deleted')));
  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", { context => 1 });
-- 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);

ALTER TABLE deleted DROP FOREIGN KEY fk_fake;

DROP TABLE deleted;


COMMIT;

## END OF DIFF

  # Test quoting works too.
  $out = SQL::Translator::Diff::schema_diff($s1, 'MySQL', $s2, 'MySQL', { sqlt_args => { quote_identifiers => 1 } });
  eq_or_diff($out, <<'## END OF DIFF', "Quoting can be turned on", { context => 1 });
-- 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`);

ALTER TABLE `deleted` DROP FOREIGN KEY `fk_fake`;

DROP TABLE `deleted`;


COMMIT;

## END OF DIFF
}