X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?a=blobdiff_plain;f=t%2F38-mysql-producer.t;h=b3c3bf88c378798711996f74e725632aa9fc3b05;hb=dd7dcde649077c79ecb908bcb472f2039cc0436b;hp=2305f4f5a998e31b7c2b14894f60ab44953e4371;hpb=ab15e426881522ca7db31ca58a36a25826c3686e;p=dbsrgits%2FSQL-Translator.git diff --git a/t/38-mysql-producer.t b/t/38-mysql-producer.t index 2305f4f..b3c3bf8 100644 --- a/t/38-mysql-producer.t +++ b/t/38-mysql-producer.t @@ -19,7 +19,7 @@ use FindBin qw/$Bin/; #============================================================================= BEGIN { - maybe_plan(72, + maybe_plan(75, 'YAML', 'SQL::Translator::Producer::MySQL', 'Test::Differences', @@ -37,8 +37,8 @@ schema: thing: name: thing extra: - mysql_charset: latin1 - mysql_collate: latin1_danish_ci + mysql_charset: latin1 + mysql_collate: latin1_danish_ci order: 1 fields: id: @@ -46,27 +46,41 @@ schema: data_type: unsigned int is_primary_key: 1 is_auto_increment: 1 - order: 0 + order: 1 name: name: name data_type: varchar size: - 32 - order: 1 + order: 2 swedish_name: name: swedish_name data_type: varchar size: 32 extra: mysql_charset: swe7 - order: 2 + order: 3 description: name: description data_type: text extra: mysql_charset: utf8 mysql_collate: utf8_general_ci - order: 3 + order: 4 + timestamp: + data_type: timestamp + default_value: !!perl/ref + =: CURRENT_TIMESTAMP + extra: + on update: !!perl/ref + =: CURRENT_TIMESTAMP + is_nullable: 1 + is_primary_key: 0 + is_unique: 0 + name: timestamp + order: 5 + size: + - 0 constraints: - type: UNIQUE fields: @@ -82,28 +96,28 @@ schema: name: id data_type: int is_primary_key: 0 - order: 0 + order: 1 is_foreign_key: 1 foo: name: foo data_type: int - order: 1 + order: 2 is_not_null: 1 foo2: name: foo2 data_type: int - order: 2 + order: 3 is_not_null: 1 bar_set: name: bar_set data_type: set - order: 3 + order: 4 is_not_null: 1 extra: list: - foo - bar - - baz + - ba'z indices: - type: NORMAL fields: @@ -130,34 +144,34 @@ schema: thing3: name: some.thing3 extra: - order: 2 + order: 3 fields: id: name: id data_type: int is_primary_key: 0 - order: 0 + order: 1 is_foreign_key: 1 foo: name: foo data_type: int - order: 1 + order: 2 is_not_null: 1 foo2: name: foo2 data_type: int - order: 2 + order: 3 is_not_null: 1 bar_set: name: bar_set data_type: set - order: 3 + order: 4 is_not_null: 1 extra: list: - foo - bar - - baz + - ba'z indices: - type: NORMAL fields: @@ -187,20 +201,21 @@ my @stmts = ( "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, + `id` unsigned int NOT NULL auto_increment, + `name` varchar(32) NULL, + `swedish_name` varchar(32) character set swe7 NULL, + `description` text character set utf8 collate utf8_general_ci NULL, + `timestamp` timestamp on update CURRENT_TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP, 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'), + `id` integer NOT NULL, + `foo` integer NOT NULL, + `foo2` integer NULL, + `bar_set` set('foo', 'bar', 'ba''z') NULL, INDEX `index_1` (`id`), INDEX `really_long_name_bigger_than_64_chars_aaaaaaaaaaaaaaaaa_aed44c47` (`id`), INDEX (`foo`), @@ -212,10 +227,10 @@ my @stmts = ( "DROP TABLE IF EXISTS `some`.`thing3`", "CREATE TABLE `some`.`thing3` ( - `id` integer, - `foo` integer, - `foo2` integer, - `bar_set` set('foo', 'bar', 'baz'), + `id` integer NOT NULL, + `foo` integer NOT NULL, + `foo2` integer NULL, + `bar_set` set('foo', 'bar', 'ba''z') NULL, INDEX `index_1` (`id`), INDEX `really_long_name_bigger_than_64_chars_aaaaaaaaaaaaaaaaa_aed44c47` (`id`), INDEX (`foo`), @@ -254,8 +269,8 @@ my $mysql_out = join(";\n\n", @stmts_no_drop) . ";\n\n"; or die "Translat eerror:".$sqlt->error; is_deeply \@out, \@stmts_no_drop, "Array output looks right with quoting"; + $sqlt->quote_identifiers(0); - @{$sqlt}{qw/quote_table_names quote_field_names/} = (0,0); $out = $sqlt->translate(\$yaml_in) or die "Translate error:".$sqlt->error; @@ -266,7 +281,9 @@ my $mysql_out = join(";\n\n", @stmts_no_drop) . ";\n\n"; 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); + $sqlt->quote_identifiers(1); + $sqlt->add_drop_table(1); + @out = $sqlt->translate(\$yaml_in) or die "Translat eerror:".$sqlt->error; $out = $sqlt->translate(\$yaml_in) @@ -294,7 +311,7 @@ my $field1 = SQL::Translator::Schema::Field->new( name => 'myfield', my $field1_sql = SQL::Translator::Producer::MySQL::create_field($field1); -is($field1_sql, 'myfield VARCHAR(10)', 'Create field works'); +is($field1_sql, 'myfield VARCHAR(10) NULL', 'Create field works'); my $field2 = SQL::Translator::Schema::Field->new( name => 'myfield', table => $table, @@ -312,7 +329,7 @@ is($alter_field, 'ALTER TABLE mytable CHANGE COLUMN myfield myfield VARCHAR(25) my $add_field = SQL::Translator::Producer::MySQL::add_field($field1); -is($add_field, 'ALTER TABLE mytable ADD COLUMN myfield VARCHAR(10)', 'Add field works'); +is($add_field, 'ALTER TABLE mytable ADD COLUMN myfield VARCHAR(10) NULL', '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'); @@ -339,26 +356,26 @@ my $number_sizes = { }; for my $size (keys %$number_sizes) { my $expected = $number_sizes->{$size}; - my $number_field = SQL::Translator::Schema::Field->new( + 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_unique => 0 ); is( SQL::Translator::Producer::MySQL::create_field($number_field), - "numberfield_$expected $expected($size)", + "numberfield_$expected $expected($size) NULL", "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( + $varchars->{$size} = SQL::Translator::Schema::Field->new( name => "vch_$size", table => $table, data_type => 'varchar', @@ -370,68 +387,68 @@ for my $size (qw/255 256 65535 65536/) { is ( SQL::Translator::Producer::MySQL::create_field($varchars->{255}, { mysql_version => 5.000003 }), - 'vch_255 varchar(255)', + 'vch_255 varchar(255) NULL', '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)', + 'vch_255 varchar(255) NULL', '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)', + 'vch_255 varchar(255) NULL', '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)', + 'vch_256 varchar(256) NULL', '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', + 'vch_256 text NULL', 'VARCHAR(256) is substituted with TEXT for Mysql < 5.0.3' ); is ( SQL::Translator::Producer::MySQL::create_field($varchars->{256}), - 'vch_256 text', + 'vch_256 text NULL', '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)', + 'vch_65535 varchar(65535) NULL', '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', + 'vch_65535 text NULL', 'VARCHAR(65535) is substituted with TEXT for Mysql < 5.0.3' ); is ( SQL::Translator::Producer::MySQL::create_field($varchars->{65535}), - 'vch_65535 text', + 'vch_65535 text NULL', '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', + 'vch_65536 text NULL', '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', + 'vch_65536 text NULL', 'VARCHAR(65536) is substituted with TEXT for Mysql < 5.0.3' ); is ( SQL::Translator::Producer::MySQL::create_field($varchars->{65536}), - 'vch_65536 text', + 'vch_65536 text NULL', 'VARCHAR(65536) is substituted with TEXT when no version specified', ); @@ -514,7 +531,7 @@ EOV is_unique => 0 ); my $sql = SQL::Translator::Producer::MySQL::create_field($field); - is($sql, "my$type $type", "Skip length param for type $type"); + is($sql, "my$type $type NULL", "Skip length param for type $type"); } } @@ -560,13 +577,13 @@ EOV quote_field_names => $qf, }; - + my $alter_field = SQL::Translator::Producer::MySQL::alter_field($field1, $field2, $options); is($alter_field, 'ALTER TABLE `mydb`.`mytable` CHANGE COLUMN `myfield` `myfield` VARCHAR(25) NOT NULL', 'Alter field works'); my $add_field = SQL::Translator::Producer::MySQL::add_field($field1, $options); - is($add_field, 'ALTER TABLE `mydb`.`mytable` ADD COLUMN `myfield` VARCHAR(10)', 'Add field works'); + is($add_field, 'ALTER TABLE `mydb`.`mytable` ADD COLUMN `myfield` VARCHAR(10) NULL', 'Add field works'); my $drop_field = SQL::Translator::Producer::MySQL::drop_field($field2, $options); is($drop_field, 'ALTER TABLE `mydb`.`mytable` DROP COLUMN `myfield`', 'Drop field works'); @@ -586,26 +603,26 @@ is($field3_sql, "`myfield` enum('0','1') NOT NULL", 'When no version specified, }; for my $size (keys %$number_sizes) { my $expected = $number_sizes->{$size}; - my $number_field = SQL::Translator::Schema::Field->new( + 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_unique => 0 ); is( SQL::Translator::Producer::MySQL::create_field($number_field, $options), - "`numberfield_$expected` $expected($size)", + "`numberfield_$expected` $expected($size) NULL", "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( + $varchars->{$size} = SQL::Translator::Schema::Field->new( name => "vch_$size", table => $table, data_type => 'varchar', @@ -617,68 +634,68 @@ is($field3_sql, "`myfield` enum('0','1') NOT NULL", 'When no version specified, is ( SQL::Translator::Producer::MySQL::create_field($varchars->{255}, { mysql_version => 5.000003, %$options }), - '`vch_255` varchar(255)', + '`vch_255` varchar(255) NULL', '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, %$options }), - '`vch_255` varchar(255)', + '`vch_255` varchar(255) NULL', 'VARCHAR(255) is not substituted with TEXT for Mysql < 5.0.3' ); is ( SQL::Translator::Producer::MySQL::create_field($varchars->{255}, $options), - '`vch_255` varchar(255)', + '`vch_255` varchar(255) NULL', 'VARCHAR(255) is not substituted with TEXT when no version specified', ); is ( SQL::Translator::Producer::MySQL::create_field($varchars->{256}, { mysql_version => 5.000003, %$options }), - '`vch_256` varchar(256)', + '`vch_256` varchar(256) NULL', '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, %$options }), - '`vch_256` text', + '`vch_256` text NULL', 'VARCHAR(256) is substituted with TEXT for Mysql < 5.0.3' ); is ( SQL::Translator::Producer::MySQL::create_field($varchars->{256}, $options), - '`vch_256` text', + '`vch_256` text NULL', 'VARCHAR(256) is substituted with TEXT when no version specified', ); is ( SQL::Translator::Producer::MySQL::create_field($varchars->{65535}, { mysql_version => 5.000003, %$options }), - '`vch_65535` varchar(65535)', + '`vch_65535` varchar(65535) NULL', '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, %$options }), - '`vch_65535` text', + '`vch_65535` text NULL', 'VARCHAR(65535) is substituted with TEXT for Mysql < 5.0.3' ); is ( SQL::Translator::Producer::MySQL::create_field($varchars->{65535}, $options), - '`vch_65535` text', + '`vch_65535` text NULL', 'VARCHAR(65535) is substituted with TEXT when no version specified', ); is ( SQL::Translator::Producer::MySQL::create_field($varchars->{65536}, { mysql_version => 5.000003, %$options }), - '`vch_65536` text', + '`vch_65536` text NULL', 'VARCHAR(65536) is substituted with TEXT for Mysql >= 5.0.3' ); is ( SQL::Translator::Producer::MySQL::create_field($varchars->{65536}, { mysql_version => 5.0, %$options }), - '`vch_65536` text', + '`vch_65536` text NULL', 'VARCHAR(65536) is substituted with TEXT for Mysql < 5.0.3' ); is ( SQL::Translator::Producer::MySQL::create_field($varchars->{65536}, $options), - '`vch_65536` text', + '`vch_65536` text NULL', 'VARCHAR(65536) is substituted with TEXT when no version specified', ); @@ -760,7 +777,69 @@ EOV is_unique => 0 ); my $sql = SQL::Translator::Producer::MySQL::create_field($field, $options); - is($sql, "`my$type` $type", "Skip length param for type $type"); + is($sql, "`my$type` $type NULL", "Skip length param for type $type"); } } } + +{ # test for rt62250 + my $table = SQL::Translator::Schema::Table->new(name => 'table'); + $table->add_field( + SQL::Translator::Schema::Field->new( name => 'mypk', + table => $table, + data_type => 'INT', + size => 10, + default_value => undef, + is_auto_increment => 1, + is_nullable => 0, + is_foreign_key => 0, + is_unique => 1 )); + + my $constraint = $table->add_constraint(fields => ['mypk'], type => 'PRIMARY_KEY'); + my $options = {quote_table_names => '`'}; + is(SQL::Translator::Producer::MySQL::alter_drop_constraint($constraint,$options), + 'ALTER TABLE `table` DROP PRIMARY KEY','valid drop primary key'); +} + +{ + my $schema = SQL::Translator::Schema->new(); + my $table = $schema->add_table( name => 'foo', fields => ['bar'] ); + + { + my $trigger = $schema->add_trigger( + name => 'mytrigger', + perform_action_when => 'before', + database_events => 'update', + on_table => 'foo', + fields => ['bar'], + action => 'BEGIN baz(); END' + ); + my ($def) = SQL::Translator::Producer::MySQL::create_trigger($trigger); + my $expected + = "--\n" + . "-- Trigger mytrigger\n" + . "--\n" + . "CREATE TRIGGER mytrigger before update ON foo\n" + . " FOR EACH ROW BEGIN baz(); END"; + is($def, $expected, 'trigger created'); + } + + { + my $trigger = $schema->add_trigger( + name => 'mytrigger2', + perform_action_when => 'after', + database_events => ['insert'], + on_table => 'foo', + fields => ['bar'], + action => 'baz()' + ); + my ($def) = SQL::Translator::Producer::MySQL::create_trigger($trigger); + my $expected + = "--\n" + . "-- Trigger mytrigger2\n" + . "--\n" + . "CREATE TRIGGER mytrigger2 after insert ON foo\n" + . " FOR EACH ROW BEGIN baz(); END"; + is($def, $expected, 'trigger created'); + } +}