#=============================================================================
BEGIN {
- maybe_plan(72,
+ maybe_plan(75,
'YAML',
'SQL::Translator::Producer::MySQL',
'Test::Differences',
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:
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:
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:
"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`),
"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`),
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;
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)
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,
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');
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"
);
}
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',
);
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");
}
}
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');
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"
);
}
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',
);
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');
+ }
+}
projects / dbsrgits/SQL-Translator.git / blobdiff