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

#!/usr/bin/perl -w
#
# 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 Data::Dumper;
use FindBin qw/$Bin/;

use Test::Differences;
use SQL::Translator;

my $sqlt;

{
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

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",

"SET foreign_key_checks=1",

);

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

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


    $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(data => $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(data => $yaml_in)
      or die "Translate error:".$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(data => $yaml_in)
      or die "Translate error:".$sqlt->error;

    @out = $sqlt->translate(data => $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(data => $yaml_in)
      or die "Translate error:".$sqlt->error;
    $out = $sqlt->translate(data => $yaml_in)
      or die "Translate error:".$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";
}

my $producer = $sqlt->_producer;
my $parser = $sqlt->_parser;

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

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

my $field1 = SQL::Translator::Object::Column->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 = $producer->create_field($field1);

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

my $field2 = SQL::Translator::Object::Column->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 = $producer->alter_field($field1,
                                                                $field2);
is($alter_field, 'ALTER TABLE mytable CHANGE COLUMN myfield myfield VARCHAR(25) NOT NULL', 'Alter field works');

my $add_field = $producer->add_field($field1);

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

my $drop_field = $producer->drop_field($field2);
is($drop_field, 'ALTER TABLE mytable DROP COLUMN myfield', 'Drop field works');

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

my $field3_sql = $producer->create_field($field3, { mysql_version => 4.1 });
is($field3_sql, 'myfield boolean NOT NULL', 'For Mysql >= 4, use boolean type');
$field3_sql = $producer->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 = $producer->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::Object::Column->new( 
        name => "numberfield_$expected",
        table => $table,
        data_type => 'number',
        size => $size,
        is_nullable => 1,
        is_foreign_key => 0,
        is_unique => 0 
    );

    is(
        $producer->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::Object::Column->new( 
        name => "vch_$size",
        table => $table,
        data_type => 'varchar',
        size => $size,
        is_nullable => 1,
    );
}


is (
    $producer->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 (
    $producer->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 (
    $producer->create_field($varchars->{255}),
    'vch_255 varchar(255)', 
    'VARCHAR(255) is not substituted with TEXT when no version specified',
);


is (
    $producer->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 (
    $producer->create_field($varchars->{256}, { mysql_version => 5.0 }),
    'vch_256 text', 
    'VARCHAR(256) is substituted with TEXT for Mysql < 5.0.3'
);
is (
    $producer->create_field($varchars->{256}),
    'vch_256 text', 
    'VARCHAR(256) is substituted with TEXT when no version specified',
);


is (
    $producer->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 (
    $producer->create_field($varchars->{65535}, { mysql_version => 5.0 }),
    'vch_65535 text', 
    'VARCHAR(65535) is substituted with TEXT for Mysql < 5.0.3'
);
is (
    $producer->create_field($varchars->{65535}),
    'vch_65535 text', 
    'VARCHAR(65535) is substituted with TEXT when no version specified',
);


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


{
  my $view1 = SQL::Translator::Object::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 = $producer->create_view($view1, $create_opts);

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


  my $view2 = SQL::Translator::Object::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 = $producer->create_view($view2, $create2_opts);
  my $view_sql_noreplace = "CREATE
  VIEW view_foo ( id, name ) AS (
    SELECT id, name FROM thing
  )";
  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::Object::Column->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 = $producer->create_field($field);
        is($sql, "my$type $type", "Skip length param for type $type");
    }
}

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