[dbsrgits/SQL-Translator-2.0-ish.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

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


    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(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 "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(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 "Translat eerror:".$sqlt->error;
    $out = $sqlt->translate(data => $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 = "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::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 = "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::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");
    }
}

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