[dbsrgits/SQL-Translator.git] / t / 38-mysql-producer.t

#!/usr/bin/perl -w
# vim:filetype=perl

#
# Note that the bulk of the testing for the mysql producer is in
# 08postgres-to-mysql.t. This test is for additional stuff that can't be tested
# using an Oracle schema as source e.g. extra attributes.
#

use strict;
use Test::More;
use Test::Exception;
use Test::SQL::Translator qw(maybe_plan);

use Data::Dumper;
use FindBin qw/$Bin/;

# Testing 1,2,3,4...
#=============================================================================

BEGIN {
    maybe_plan(40,
        'YAML',
        'SQL::Translator::Producer::MySQL',
        'Test::Differences',
    )
}
use Test::Differences;
use SQL::Translator;

# Main test.
{
my $yaml_in = <<EOSCHEMA;
---
schema:
  tables:
    thing:
      name: thing
      extra:
        mysql_charset: latin1 
        mysql_collate: latin1_danish_ci 
      order: 1
      fields:
        id:
          name: id
          data_type: unsigned int
          is_primary_key: 1
          is_auto_increment: 1
          order: 0
        name:
          name: name
          data_type: varchar
          size:
            - 32
          order: 1
        swedish_name:
          name: swedish_name
          data_type: varchar
          size: 32
          extra:
            mysql_charset: swe7
          order: 2
        description:
          name: description
          data_type: text
          extra:
            mysql_charset: utf8
            mysql_collate: utf8_general_ci
          order: 3
      constraints:
        - type: UNIQUE
          fields:
            - name
          name: idx_unique_name

    thing2:
      name: some.thing2
      extra:
      order: 2
      fields:
        id:
          name: id
          data_type: int
          is_primary_key: 0
          order: 0
          is_foreign_key: 1
        foo:
          name: foo
          data_type: int
          order: 1
          is_not_null: 1
        foo2:
          name: foo2
          data_type: int
          order: 2
          is_not_null: 1
        bar_set:
          name: bar_set
          data_type: set
          order: 3
          is_not_null: 1
          extra:
            list:
              - foo
              - bar
              - baz
      indices:
        - type: NORMAL
          fields:
            - id
          name: index_1
        - type: NORMAL
          fields:
            - id
          name: really_long_name_bigger_than_64_chars_aaaaaaaaaaaaaaaaaaaaaaaaaaa
      constraints:
        - type: PRIMARY_KEY
          fields:
            - id
            - foo
        - reference_table: thing
          type: FOREIGN_KEY
          fields: foo
          name: fk_thing
        - reference_table: thing
          type: FOREIGN_KEY
          fields: foo2
          name: fk_thing

    thing3:
      name: some.thing3
      extra:
      order: 2
      fields:
        id:
          name: id
          data_type: int
          is_primary_key: 0
          order: 0
          is_foreign_key: 1
        foo:
          name: foo
          data_type: int
          order: 1
          is_not_null: 1
        foo2:
          name: foo2
          data_type: int
          order: 2
          is_not_null: 1
        bar_set:
          name: bar_set
          data_type: set
          order: 3
          is_not_null: 1
          extra:
            list:
              - foo
              - bar
              - baz
      indices:
        - type: NORMAL
          fields:
            - id
          name: index_1
        - type: NORMAL
          fields:
            - id
          name: really_long_name_bigger_than_64_chars_aaaaaaaaaaaaaaaaaaaaaaaaaaa
      constraints:
        - type: PRIMARY_KEY
          fields:
            - id
            - foo
        - reference_table: some.thing2
          type: FOREIGN_KEY
          fields: foo
          name: fk_thing
        - reference_table: some.thing2
          type: FOREIGN_KEY
          fields: foo2
          name: fk_thing
EOSCHEMA

my @stmts = (
"SET foreign_key_checks=0",

"DROP TABLE IF EXISTS `thing`",
"CREATE TABLE `thing` (
  `id` unsigned int auto_increment,
  `name` varchar(32),
  `swedish_name` varchar(32) character set swe7,
  `description` text character set utf8 collate utf8_general_ci,
  PRIMARY KEY (`id`),
  UNIQUE `idx_unique_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARACTER SET latin1 COLLATE latin1_danish_ci",

"DROP TABLE IF EXISTS `some`.`thing2`",
"CREATE TABLE `some`.`thing2` (
  `id` integer,
  `foo` integer,
  `foo2` integer,
  `bar_set` set('foo', 'bar', 'baz'),
  INDEX `index_1` (`id`),
  INDEX `really_long_name_bigger_than_64_chars_aaaaaaaaaaaaaaaaa_aed44c47` (`id`),
  INDEX (`foo`),
  INDEX (`foo2`),
  PRIMARY KEY (`id`, `foo`),
  CONSTRAINT `fk_thing` FOREIGN KEY (`foo`) REFERENCES `thing` (`id`),
  CONSTRAINT `fk_thing_1` FOREIGN KEY (`foo2`) REFERENCES `thing` (`id`)
) ENGINE=InnoDB",

"DROP TABLE IF EXISTS `some`.`thing3`",
"CREATE TABLE `some`.`thing3` (
  `id` integer,
  `foo` integer,
  `foo2` integer,
  `bar_set` set('foo', 'bar', 'baz'),
  INDEX `index_1` (`id`),
  INDEX `really_long_name_bigger_than_64_chars_aaaaaaaaaaaaaaaaa_aed44c47` (`id`),
  INDEX (`foo`),
  INDEX (`foo2`),
  PRIMARY KEY (`id`, `foo`),
  CONSTRAINT `fk_thing_2` FOREIGN KEY (`foo`) REFERENCES `some`.`thing2` (`id`, `foo`),
  CONSTRAINT `fk_thing_3` FOREIGN KEY (`foo2`) REFERENCES `some`.`thing2` (`id`, `foo`)
) ENGINE=InnoDB",

"SET foreign_key_checks=1",

);

my @stmts_no_drop = grep {$_ !~ /^DROP TABLE/} @stmts;

my $mysql_out = join(";\n\n", @stmts_no_drop) . ";\n\n";


    my $sqlt;
    $sqlt = SQL::Translator->new(
        show_warnings  => 1,
        no_comments    => 1,
#        debug          => 1,
        from           => "YAML",
        to             => "MySQL",
        quote_table_names => 1,
        quote_field_names => 1
    );

    my $out = $sqlt->translate(\$yaml_in)
    or die "Translate error:".$sqlt->error;
    ok $out ne "",                    "Produced something!";
    eq_or_diff $out, $mysql_out,      "Scalar output looks right with quoting";

    my @out = $sqlt->translate(\$yaml_in)
      or die "Translat eerror:".$sqlt->error;
    is_deeply \@out, \@stmts_no_drop, "Array output looks right with quoting";


    @{$sqlt}{qw/quote_table_names quote_field_names/} = (0,0);
    $out = $sqlt->translate(\$yaml_in)
      or die "Translate error:".$sqlt->error;

    @out = $sqlt->translate(\$yaml_in)
      or die "Translate error:".$sqlt->error;
    $mysql_out =~ s/`//g;
    my @unquoted_stmts = map { s/`//g; $_} @stmts_no_drop;
    eq_or_diff $out, $mysql_out,       "Output looks right without quoting";
    is_deeply \@out, \@unquoted_stmts, "Array output looks right without quoting";

    @{$sqlt}{qw/add_drop_table quote_field_names quote_table_names/} = (1,1,1);
    @out = $sqlt->translate(\$yaml_in)
      or die "Translat eerror:".$sqlt->error;
    $out = $sqlt->translate(\$yaml_in)
      or die "Translat eerror:".$sqlt->error;

    eq_or_diff $out, join(";\n\n", @stmts) . ";\n\n", "Output looks right with DROP TABLEs";
    is_deeply \@out, \@stmts,          "Array output looks right with DROP TABLEs";
}

###############################################################################
# New alter/add subs

my $table = SQL::Translator::Schema::Table->new( name => 'mytable');

my $field1 = SQL::Translator::Schema::Field->new( name => 'myfield',
                                                  table => $table,
                                                  data_type => 'VARCHAR',
                                                  size => 10,
                                                  default_value => undef,
                                                  is_auto_increment => 0,
                                                  is_nullable => 1,
                                                  is_foreign_key => 0,
                                                  is_unique => 0 );

my $field1_sql = SQL::Translator::Producer::MySQL::create_field($field1);

is($field1_sql, 'myfield VARCHAR(10)', 'Create field works');

my $field2 = SQL::Translator::Schema::Field->new( name      => 'myfield',
                                                  table => $table,
                                                  data_type => 'VARCHAR',
                                                  size      => 25,
                                                  default_value => undef,
                                                  is_auto_increment => 0,
                                                  is_nullable => 0,
                                                  is_foreign_key => 0,
                                                  is_unique => 0 );

my $alter_field = SQL::Translator::Producer::MySQL::alter_field($field1,
                                                                $field2);
is($alter_field, 'ALTER TABLE mytable CHANGE COLUMN myfield myfield VARCHAR(25) NOT NULL', 'Alter field works');

my $add_field = SQL::Translator::Producer::MySQL::add_field($field1);

is($add_field, 'ALTER TABLE mytable ADD COLUMN myfield VARCHAR(10)', 'Add field works');

my $drop_field = SQL::Translator::Producer::MySQL::drop_field($field2);
is($drop_field, 'ALTER TABLE mytable DROP COLUMN myfield', 'Drop field works');

my $field3 = SQL::Translator::Schema::Field->new( name      => 'myfield',
                                                  table => $table,
                                                  data_type => 'boolean',
                                                  is_nullable => 0,
                                                  is_foreign_key => 0,
                                                  is_unique => 0 );

my $field3_sql = SQL::Translator::Producer::MySQL::create_field($field3, { mysql_version => 4.1 });
is($field3_sql, 'myfield boolean NOT NULL', 'For Mysql >= 4, use boolean type');
$field3_sql = SQL::Translator::Producer::MySQL::create_field($field3, { mysql_version => 3.22 });
is($field3_sql, "myfield enum('0','1') NOT NULL", 'For Mysql < 4, use enum for boolean type');
$field3_sql = SQL::Translator::Producer::MySQL::create_field($field3,);
is($field3_sql, "myfield enum('0','1') NOT NULL", 'When no version specified, use enum for boolean type');

my $number_sizes = {
    '3, 2' => 'double',
    12 => 'bigint',
    1 => 'tinyint',
    4 => 'int',
};
for my $size (keys %$number_sizes) {
    my $expected = $number_sizes->{$size};
    my $number_field = SQL::Translator::Schema::Field->new( 
        name => "numberfield_$expected",
        table => $table,
        data_type => 'number',
        size => $size,
        is_nullable => 1,
        is_foreign_key => 0,
        is_unique => 0 
    );

    is(
        SQL::Translator::Producer::MySQL::create_field($number_field),
        "numberfield_$expected $expected($size)",
        "Use $expected for NUMBER types of size $size"
    );
}

my $varchars;
for my $size (qw/255 256 65535 65536/) {
    $varchars->{$size} = SQL::Translator::Schema::Field->new( 
        name => "vch_$size",
        table => $table,
        data_type => 'varchar',
        size => $size,
        is_nullable => 1,
    );
}


is (
    SQL::Translator::Producer::MySQL::create_field($varchars->{255}, { mysql_version => 5.000003 }),
    'vch_255 varchar(255)', 
    'VARCHAR(255) is not substituted with TEXT for Mysql >= 5.0.3'
);
is (
    SQL::Translator::Producer::MySQL::create_field($varchars->{255}, { mysql_version => 5.0 }),
    'vch_255 varchar(255)', 
    'VARCHAR(255) is not substituted with TEXT for Mysql < 5.0.3'
);
is (
    SQL::Translator::Producer::MySQL::create_field($varchars->{255}),
    'vch_255 varchar(255)', 
    'VARCHAR(255) is not substituted with TEXT when no version specified',
);


is (
    SQL::Translator::Producer::MySQL::create_field($varchars->{256}, { mysql_version => 5.000003 }),
    'vch_256 varchar(256)', 
    'VARCHAR(256) is not substituted with TEXT for Mysql >= 5.0.3'
);
is (
    SQL::Translator::Producer::MySQL::create_field($varchars->{256}, { mysql_version => 5.0 }),
    'vch_256 text', 
    'VARCHAR(256) is substituted with TEXT for Mysql < 5.0.3'
);
is (
    SQL::Translator::Producer::MySQL::create_field($varchars->{256}),
    'vch_256 text', 
    'VARCHAR(256) is substituted with TEXT when no version specified',
);


is (
    SQL::Translator::Producer::MySQL::create_field($varchars->{65535}, { mysql_version => 5.000003 }),
    'vch_65535 varchar(65535)', 
    'VARCHAR(65535) is not substituted with TEXT for Mysql >= 5.0.3'
);
is (
    SQL::Translator::Producer::MySQL::create_field($varchars->{65535}, { mysql_version => 5.0 }),
    'vch_65535 text', 
    'VARCHAR(65535) is substituted with TEXT for Mysql < 5.0.3'
);
is (
    SQL::Translator::Producer::MySQL::create_field($varchars->{65535}),
    'vch_65535 text', 
    'VARCHAR(65535) is substituted with TEXT when no version specified',
);


is (
    SQL::Translator::Producer::MySQL::create_field($varchars->{65536}, { mysql_version => 5.000003 }),
    'vch_65536 text', 
    'VARCHAR(65536) is substituted with TEXT for Mysql >= 5.0.3'
);
is (
    SQL::Translator::Producer::MySQL::create_field($varchars->{65536}, { mysql_version => 5.0 }),
    'vch_65536 text', 
    'VARCHAR(65536) is substituted with TEXT for Mysql < 5.0.3'
);
is (
    SQL::Translator::Producer::MySQL::create_field($varchars->{65536}),
    'vch_65536 text', 
    'VARCHAR(65536) is substituted with TEXT when no version specified',
);


{
  my $view1 = SQL::Translator::Schema::View->new( name => 'view_foo',
                                                  fields => [qw/id name/],
                                                  sql => 'SELECT id, name FROM thing',
                                                  extra => {
                                                    mysql_definer => 'CURRENT_USER',
                                                    mysql_algorithm => 'MERGE',
                                                    mysql_security => 'DEFINER',
                                                  });
  my $create_opts = { add_replace_view => 1, no_comments => 1 };
  my $view1_sql1 = SQL::Translator::Producer::MySQL::create_view($view1, $create_opts);

  my $view_sql_replace = <<'EOV';
CREATE OR REPLACE
   ALGORITHM = MERGE
   DEFINER = CURRENT_USER
   SQL SECURITY DEFINER
  VIEW view_foo ( id, name ) AS
    SELECT id, name FROM thing
EOV

  is($view1_sql1, $view_sql_replace, 'correct "CREATE OR REPLACE VIEW" SQL');


  my $view2 = SQL::Translator::Schema::View->new( name => 'view_foo',
                                                  fields => [qw/id name/],
                                                  sql => 'SELECT id, name FROM thing',);
  my $create2_opts = { add_replace_view => 0, no_comments => 1 };
  my $view1_sql2 = SQL::Translator::Producer::MySQL::create_view($view2, $create2_opts);
  my $view_sql_noreplace = <<'EOV';
CREATE
  VIEW view_foo ( id, name ) AS
    SELECT id, name FROM thing
EOV

  is($view1_sql2, $view_sql_noreplace, 'correct "CREATE VIEW" SQL');

  {
    my %extra = $view1->extra;
    is_deeply \%extra,
      {
        'mysql_algorithm' => 'MERGE',
        'mysql_definer'   => 'CURRENT_USER',
        'mysql_security'  => 'DEFINER'
      },
      'Extra attributes';
  }

  $view1->remove_extra(qw/mysql_definer mysql_security/);
  {
    my %extra = $view1->extra;
    is_deeply \%extra, { 'mysql_algorithm' => 'MERGE', }, 'Extra attributes after first reset_extra call';
  }

  $view1->remove_extra();
  {
    my %extra = $view1->extra;
    is_deeply \%extra, {}, 'Extra attributes completely removed';
  }
}

{

    # certain types do not support a size, see also:
    # http://dev.mysql.com/doc/refman/5.1/de/create-table.html
    for my $type (qw/date time timestamp datetime year/) {
        my $field = SQL::Translator::Schema::Field->new(
            name              => "my$type",
            table             => $table,
            data_type         => $type,
            size              => 10,
            default_value     => undef,
            is_auto_increment => 0,
            is_nullable       => 1,
            is_foreign_key    => 0,
            is_unique         => 0
        );
        my $sql = SQL::Translator::Producer::MySQL::create_field($field);
        is($sql, "my$type $type", "Skip length param for type $type");
    }
}
Commit	Line	Data
819fe9ef	1	#!/usr/bin/perl -w
1ded8513	2	# vim:filetype=perl
	3
	4	#
	5	# Note that the bulk of the testing for the mysql producer is in
	6	# 08postgres-to-mysql.t. This test is for additional stuff that can't be tested
	7	# using an Oracle schema as source e.g. extra attributes.
	8	#
	9
	10	use strict;
	11	use Test::More;
	12	use Test::Exception;
	13	use Test::SQL::Translator qw(maybe_plan);
	14
	15	use Data::Dumper;
	16	use FindBin qw/$Bin/;
	17
	18	# Testing 1,2,3,4...
	19	#=============================================================================
	20
	21	BEGIN {
de176728	22	maybe_plan(40,
1ded8513	23	'YAML',
	24	'SQL::Translator::Producer::MySQL',
	25	'Test::Differences',
	26	)
	27	}
	28	use Test::Differences;
	29	use SQL::Translator;
	30
	31	# Main test.
	32	{
	33	my $yaml_in = <<EOSCHEMA;
	34	---
	35	schema:
	36	tables:
	37	thing:
	38	name: thing
	39	extra:
	40	mysql_charset: latin1
	41	mysql_collate: latin1_danish_ci
	42	order: 1
	43	fields:
8c4efd11	44	id:
	45	name: id
	46	data_type: unsigned int
	47	is_primary_key: 1
	48	is_auto_increment: 1
	49	order: 0
1ded8513	50	name:
	51	name: name
	52	data_type: varchar
	53	size:
	54	- 32
	55	order: 1
	56	swedish_name:
	57	name: swedish_name
	58	data_type: varchar
	59	size: 32
	60	extra:
	61	mysql_charset: swe7
	62	order: 2
	63	description:
	64	name: description
	65	data_type: text
	66	extra:
	67	mysql_charset: utf8
	68	mysql_collate: utf8_general_ci
	69	order: 3
fe0f47d0	70	constraints:
	71	- type: UNIQUE
	72	fields:
	73	- name
	74	name: idx_unique_name
4d438549	75
8c4efd11	76	thing2:
8d693a85	77	name: some.thing2
8c4efd11	78	extra:
	79	order: 2
	80	fields:
	81	id:
	82	name: id
	83	data_type: int
	84	is_primary_key: 0
	85	order: 0
	86	is_foreign_key: 1
	87	foo:
	88	name: foo
	89	data_type: int
	90	order: 1
	91	is_not_null: 1
fb149f81	92	foo2:
	93	name: foo2
	94	data_type: int
	95	order: 2
	96	is_not_null: 1
7c1aae02	97	bar_set:
	98	name: bar_set
	99	data_type: set
	100	order: 3
	101	is_not_null: 1
	102	extra:
	103	list:
	104	- foo
	105	- bar
	106	- baz
4d438549	107	indices:
4d438549	108	- type: NORMAL
7c1aae02	109	fields:
4d438549	110	- id
	111	name: index_1
	112	- type: NORMAL
7c1aae02	113	fields:
4d438549	114	- id
f5405d47	115	name: really_long_name_bigger_than_64_chars_aaaaaaaaaaaaaaaaaaaaaaaaaaa
8c4efd11	116	constraints:
	117	- type: PRIMARY_KEY
	118	fields:
	119	- id
	120	- foo
	121	- reference_table: thing
	122	type: FOREIGN_KEY
	123	fields: foo
	124	name: fk_thing
fb149f81	125	- reference_table: thing
	126	type: FOREIGN_KEY
	127	fields: foo2
	128	name: fk_thing
8c4efd11	129
2c098ea5	130	thing3:
	131	name: some.thing3
	132	extra:
	133	order: 2
	134	fields:
	135	id:
	136	name: id
	137	data_type: int
	138	is_primary_key: 0
	139	order: 0
	140	is_foreign_key: 1
	141	foo:
	142	name: foo
	143	data_type: int
	144	order: 1
	145	is_not_null: 1
	146	foo2:
	147	name: foo2
	148	data_type: int
	149	order: 2
	150	is_not_null: 1
	151	bar_set:
	152	name: bar_set
	153	data_type: set
	154	order: 3
	155	is_not_null: 1
	156	extra:
	157	list:
	158	- foo
	159	- bar
	160	- baz
	161	indices:
	162	- type: NORMAL
	163	fields:
	164	- id
	165	name: index_1
	166	- type: NORMAL
	167	fields:
	168	- id
	169	name: really_long_name_bigger_than_64_chars_aaaaaaaaaaaaaaaaaaaaaaaaaaa
	170	constraints:
	171	- type: PRIMARY_KEY
	172	fields:
	173	- id
	174	- foo
	175	- reference_table: some.thing2
	176	type: FOREIGN_KEY
	177	fields: foo
	178	name: fk_thing
	179	- reference_table: some.thing2
	180	type: FOREIGN_KEY
	181	fields: foo2
	182	name: fk_thing
1ded8513	183	EOSCHEMA
1ded8513	184
f6af58ae	185	my @stmts = (
24d9fe69	186	"SET foreign_key_checks=0",
1ded8513	187
24d9fe69	188	"DROP TABLE IF EXISTS `thing`",
f6af58ae	189	"CREATE TABLE `thing` (
fe0f47d0	190	`id` unsigned int auto_increment,
fe0f47d0	191	`name` varchar(32),
f5405d47	192	`swedish_name` varchar(32) character set swe7,
f5405d47	193	`description` text character set utf8 collate utf8_general_ci,
fe0f47d0	194	PRIMARY KEY (`id`),
fe0f47d0	195	UNIQUE `idx_unique_name` (`name`)
24d9fe69	196	) ENGINE=InnoDB DEFAULT CHARACTER SET latin1 COLLATE latin1_danish_ci",
1ded8513	197
8d693a85	198	"DROP TABLE IF EXISTS `some`.`thing2`",
8d693a85	199	"CREATE TABLE `some`.`thing2` (
fe0f47d0	200	`id` integer,
fe0f47d0	201	`foo` integer,
fb149f81	202	`foo2` integer,
7c1aae02	203	`bar_set` set('foo', 'bar', 'baz'),
20476859	204	INDEX `index_1` (`id`),
20476859	205	INDEX `really_long_name_bigger_than_64_chars_aaaaaaaaaaaaaaaaa_aed44c47` (`id`),
fe0f47d0	206	INDEX (`foo`),
fb149f81	207	INDEX (`foo2`),
fe0f47d0	208	PRIMARY KEY (`id`, `foo`),
da5a1bae	209	CONSTRAINT `fk_thing` FOREIGN KEY (`foo`) REFERENCES `thing` (`id`),
da5a1bae	210	CONSTRAINT `fk_thing_1` FOREIGN KEY (`foo2`) REFERENCES `thing` (`id`)
24d9fe69	211	) ENGINE=InnoDB",
8c4efd11	212
2c098ea5	213	"DROP TABLE IF EXISTS `some`.`thing3`",
	214	"CREATE TABLE `some`.`thing3` (
	215	`id` integer,
	216	`foo` integer,
	217	`foo2` integer,
	218	`bar_set` set('foo', 'bar', 'baz'),
	219	INDEX `index_1` (`id`),
	220	INDEX `really_long_name_bigger_than_64_chars_aaaaaaaaaaaaaaaaa_aed44c47` (`id`),
	221	INDEX (`foo`),
	222	INDEX (`foo2`),
	223	PRIMARY KEY (`id`, `foo`),
	224	CONSTRAINT `fk_thing_2` FOREIGN KEY (`foo`) REFERENCES `some`.`thing2` (`id`, `foo`),
	225	CONSTRAINT `fk_thing_3` FOREIGN KEY (`foo2`) REFERENCES `some`.`thing2` (`id`, `foo`)
	226	) ENGINE=InnoDB",
	227
24d9fe69	228	"SET foreign_key_checks=1",
f6af58ae	229
	230	);
	231
	232	my @stmts_no_drop = grep {$_ !~ /^DROP TABLE/} @stmts;
	233
24d9fe69	234	my $mysql_out = join(";\n\n", @stmts_no_drop) . ";\n\n";
8c4efd11	235
1ded8513	236
	237	my $sqlt;
	238	$sqlt = SQL::Translator->new(
	239	show_warnings => 1,
	240	no_comments => 1,
8c4efd11	241	# debug => 1,
1ded8513	242	from => "YAML",
1ded8513	243	to => "MySQL",
fe0f47d0	244	quote_table_names => 1,
fe0f47d0	245	quote_field_names => 1
1ded8513	246	);
1ded8513	247
819fe9ef	248	my $out = $sqlt->translate(\$yaml_in)
819fe9ef	249	or die "Translate error:".$sqlt->error;
f6af58ae	250	ok $out ne "", "Produced something!";
	251	eq_or_diff $out, $mysql_out, "Scalar output looks right with quoting";
	252
	253	my @out = $sqlt->translate(\$yaml_in)
	254	or die "Translat eerror:".$sqlt->error;
	255	is_deeply \@out, \@stmts_no_drop, "Array output looks right with quoting";
fe0f47d0	256
fe0f47d0	257
f6af58ae	258	@{$sqlt}{qw/quote_table_names quote_field_names/} = (0,0);
fe0f47d0	259	$out = $sqlt->translate(\$yaml_in)
4d438549	260	or die "Translate error:".$sqlt->error;
f6af58ae	261
f6af58ae	262	@out = $sqlt->translate(\$yaml_in)
4d438549	263	or die "Translate error:".$sqlt->error;
fe0f47d0	264	$mysql_out =~ s/`//g;
f6af58ae	265	my @unquoted_stmts = map { s/`//g; $_} @stmts_no_drop;
	266	eq_or_diff $out, $mysql_out, "Output looks right without quoting";
	267	is_deeply \@out, \@unquoted_stmts, "Array output looks right without quoting";
	268
	269	@{$sqlt}{qw/add_drop_table quote_field_names quote_table_names/} = (1,1,1);
	270	@out = $sqlt->translate(\$yaml_in)
	271	or die "Translat eerror:".$sqlt->error;
	272	$out = $sqlt->translate(\$yaml_in)
	273	or die "Translat eerror:".$sqlt->error;
	274
24d9fe69	275	eq_or_diff $out, join(";\n\n", @stmts) . ";\n\n", "Output looks right with DROP TABLEs";
f6af58ae	276	is_deeply \@out, \@stmts, "Array output looks right with DROP TABLEs";
1ded8513	277	}
8db4bd9d	278
	279	###############################################################################
	280	# New alter/add subs
	281
	282	my $table = SQL::Translator::Schema::Table->new( name => 'mytable');
	283
	284	my $field1 = SQL::Translator::Schema::Field->new( name => 'myfield',
	285	table => $table,
	286	data_type => 'VARCHAR',
	287	size => 10,
	288	default_value => undef,
	289	is_auto_increment => 0,
	290	is_nullable => 1,
	291	is_foreign_key => 0,
	292	is_unique => 0 );
	293
	294	my $field1_sql = SQL::Translator::Producer::MySQL::create_field($field1);
	295
	296	is($field1_sql, 'myfield VARCHAR(10)', 'Create field works');
	297
	298	my $field2 = SQL::Translator::Schema::Field->new( name => 'myfield',
	299	table => $table,
	300	data_type => 'VARCHAR',
	301	size => 25,
	302	default_value => undef,
	303	is_auto_increment => 0,
	304	is_nullable => 0,
	305	is_foreign_key => 0,
	306	is_unique => 0 );
	307
	308	my $alter_field = SQL::Translator::Producer::MySQL::alter_field($field1,
	309	$field2);
	310	is($alter_field, 'ALTER TABLE mytable CHANGE COLUMN myfield myfield VARCHAR(25) NOT NULL', 'Alter field works');
	311
	312	my $add_field = SQL::Translator::Producer::MySQL::add_field($field1);
	313
	314	is($add_field, 'ALTER TABLE mytable ADD COLUMN myfield VARCHAR(10)', 'Add field works');
	315
	316	my $drop_field = SQL::Translator::Producer::MySQL::drop_field($field2);
	317	is($drop_field, 'ALTER TABLE mytable DROP COLUMN myfield', 'Drop field works');
ca1f9923	318
	319	my $field3 = SQL::Translator::Schema::Field->new( name => 'myfield',
	320	table => $table,
	321	data_type => 'boolean',
	322	is_nullable => 0,
	323	is_foreign_key => 0,
	324	is_unique => 0 );
	325
	326	my $field3_sql = SQL::Translator::Producer::MySQL::create_field($field3, { mysql_version => 4.1 });
	327	is($field3_sql, 'myfield boolean NOT NULL', 'For Mysql >= 4, use boolean type');
	328	$field3_sql = SQL::Translator::Producer::MySQL::create_field($field3, { mysql_version => 3.22 });
	329	is($field3_sql, "myfield enum('0','1') NOT NULL", 'For Mysql < 4, use enum for boolean type');
	330	$field3_sql = SQL::Translator::Producer::MySQL::create_field($field3,);
	331	is($field3_sql, "myfield enum('0','1') NOT NULL", 'When no version specified, use enum for boolean type');
d28afa66	332
5d666b31	333	my $number_sizes = {
	334	'3, 2' => 'double',
	335	12 => 'bigint',
	336	1 => 'tinyint',
	337	4 => 'int',
	338	};
	339	for my $size (keys %$number_sizes) {
	340	my $expected = $number_sizes->{$size};
	341	my $number_field = SQL::Translator::Schema::Field->new(
75b8fbe8	342	name => "numberfield_$expected",
5d666b31	343	table => $table,
	344	data_type => 'number',
	345	size => $size,
	346	is_nullable => 1,
	347	is_foreign_key => 0,
	348	is_unique => 0
	349	);
	350
	351	is(
	352	SQL::Translator::Producer::MySQL::create_field($number_field),
75b8fbe8	353	"numberfield_$expected $expected($size)",
5d666b31	354	"Use $expected for NUMBER types of size $size"
	355	);
	356	}
	357
	358	my $varchars;
	359	for my $size (qw/255 256 65535 65536/) {
	360	$varchars->{$size} = SQL::Translator::Schema::Field->new(
	361	name => "vch_$size",
	362	table => $table,
	363	data_type => 'varchar',
	364	size => $size,
	365	is_nullable => 1,
	366	);
	367	}
	368
	369
	370	is (
	371	SQL::Translator::Producer::MySQL::create_field($varchars->{255}, { mysql_version => 5.000003 }),
	372	'vch_255 varchar(255)',
	373	'VARCHAR(255) is not substituted with TEXT for Mysql >= 5.0.3'
	374	);
	375	is (
	376	SQL::Translator::Producer::MySQL::create_field($varchars->{255}, { mysql_version => 5.0 }),
	377	'vch_255 varchar(255)',
	378	'VARCHAR(255) is not substituted with TEXT for Mysql < 5.0.3'
	379	);
	380	is (
	381	SQL::Translator::Producer::MySQL::create_field($varchars->{255}),
	382	'vch_255 varchar(255)',
	383	'VARCHAR(255) is not substituted with TEXT when no version specified',
	384	);
	385
	386
	387	is (
	388	SQL::Translator::Producer::MySQL::create_field($varchars->{256}, { mysql_version => 5.000003 }),
	389	'vch_256 varchar(256)',
	390	'VARCHAR(256) is not substituted with TEXT for Mysql >= 5.0.3'
	391	);
	392	is (
	393	SQL::Translator::Producer::MySQL::create_field($varchars->{256}, { mysql_version => 5.0 }),
	394	'vch_256 text',
	395	'VARCHAR(256) is substituted with TEXT for Mysql < 5.0.3'
	396	);
	397	is (
	398	SQL::Translator::Producer::MySQL::create_field($varchars->{256}),
	399	'vch_256 text',
	400	'VARCHAR(256) is substituted with TEXT when no version specified',
	401	);
	402
	403
	404	is (
	405	SQL::Translator::Producer::MySQL::create_field($varchars->{65535}, { mysql_version => 5.000003 }),
	406	'vch_65535 varchar(65535)',
	407	'VARCHAR(65535) is not substituted with TEXT for Mysql >= 5.0.3'
	408	);
	409	is (
	410	SQL::Translator::Producer::MySQL::create_field($varchars->{65535}, { mysql_version => 5.0 }),
	411	'vch_65535 text',
	412	'VARCHAR(65535) is substituted with TEXT for Mysql < 5.0.3'
	413	);
	414	is (
	415	SQL::Translator::Producer::MySQL::create_field($varchars->{65535}),
	416	'vch_65535 text',
	417	'VARCHAR(65535) is substituted with TEXT when no version specified',
418	);
419
420
421	is (
422	SQL::Translator::Producer::MySQL::create_field($varchars->{65536}, { mysql_version => 5.000003 }),
423	'vch_65536 text',
424	'VARCHAR(65536) is substituted with TEXT for Mysql >= 5.0.3'
425	);
426	is (
427	SQL::Translator::Producer::MySQL::create_field($varchars->{65536}, { mysql_version => 5.0 }),
428	'vch_65536 text',
429	'VARCHAR(65536) is substituted with TEXT for Mysql < 5.0.3'
430	);
431	is (
432	SQL::Translator::Producer::MySQL::create_field($varchars->{65536}),
433	'vch_65536 text',
434	'VARCHAR(65536) is substituted with TEXT when no version specified',
435	);
436
437
d28afa66	438	{
	439	my $view1 = SQL::Translator::Schema::View->new( name => 'view_foo',
	440	fields => [qw/id name/],
	441	sql => 'SELECT id, name FROM thing',
	442	extra => {
	443	mysql_definer => 'CURRENT_USER',
	444	mysql_algorithm => 'MERGE',
	445	mysql_security => 'DEFINER',
	446	});
	447	my $create_opts = { add_replace_view => 1, no_comments => 1 };
	448	my $view1_sql1 = SQL::Translator::Producer::MySQL::create_view($view1, $create_opts);
	449
1c8ec56e	450	my $view_sql_replace = <<'EOV';
1c8ec56e	451	CREATE OR REPLACE
d28afa66	452	ALGORITHM = MERGE
	453	DEFINER = CURRENT_USER
	454	SQL SECURITY DEFINER
1c8ec56e	455	VIEW view_foo ( id, name ) AS
d28afa66	456	SELECT id, name FROM thing
1c8ec56e	457	EOV
1c8ec56e	458
d28afa66	459	is($view1_sql1, $view_sql_replace, 'correct "CREATE OR REPLACE VIEW" SQL');
	460
	461
	462	my $view2 = SQL::Translator::Schema::View->new( name => 'view_foo',
	463	fields => [qw/id name/],
	464	sql => 'SELECT id, name FROM thing',);
	465	my $create2_opts = { add_replace_view => 0, no_comments => 1 };
	466	my $view1_sql2 = SQL::Translator::Producer::MySQL::create_view($view2, $create2_opts);
1c8ec56e	467	my $view_sql_noreplace = <<'EOV';
	468	CREATE
	469	VIEW view_foo ( id, name ) AS
d28afa66	470	SELECT id, name FROM thing
1c8ec56e	471	EOV
1c8ec56e	472
d28afa66	473	is($view1_sql2, $view_sql_noreplace, 'correct "CREATE VIEW" SQL');
1c8ec56e	474
12018c09	475	{
	476	my %extra = $view1->extra;
	477	is_deeply \%extra,
	478	{
	479	'mysql_algorithm' => 'MERGE',
	480	'mysql_definer' => 'CURRENT_USER',
	481	'mysql_security' => 'DEFINER'
	482	},
	483	'Extra attributes';
	484	}
	485
	486	$view1->remove_extra(qw/mysql_definer mysql_security/);
	487	{
	488	my %extra = $view1->extra;
	489	is_deeply \%extra, { 'mysql_algorithm' => 'MERGE', }, 'Extra attributes after first reset_extra call';
	490	}
	491
	492	$view1->remove_extra();
	493	{
	494	my %extra = $view1->extra;
	495	is_deeply \%extra, {}, 'Extra attributes completely removed';
	496	}
d28afa66	497	}
de176728	498
	499	{
	500
	501	# certain types do not support a size, see also:
	502	# http://dev.mysql.com/doc/refman/5.1/de/create-table.html
	503	for my $type (qw/date time timestamp datetime year/) {
	504	my $field = SQL::Translator::Schema::Field->new(
	505	name => "my$type",
	506	table => $table,
	507	data_type => $type,
	508	size => 10,
	509	default_value => undef,
	510	is_auto_increment => 0,
	511	is_nullable => 1,
	512	is_foreign_key => 0,
	513	is_unique => 0
	514	);
	515	my $sql = SQL::Translator::Producer::MySQL::create_field($field);
	516	is($sql, "my$type $type", "Skip length param for type $type");
	517	}
	518	}