#=============================================================================
BEGIN {
- maybe_plan(40,
+ maybe_plan(72,
'YAML',
'SQL::Translator::Producer::MySQL',
'Test::Differences',
###############################################################################
# New alter/add subs
+{
my $table = SQL::Translator::Schema::Table->new( name => 'mytable');
my $field1 = SQL::Translator::Schema::Field->new( name => 'myfield',
is($sql, "my$type $type", "Skip length param for type $type");
}
}
+
+} #non quoted test
+
+{
+ #Quoted test
+ my $table = SQL::Translator::Schema::Table->new( name => 'mydb.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 $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 $field3 = SQL::Translator::Schema::Field->new( name => 'myfield',
+ table => $table,
+ data_type => 'boolean',
+ is_nullable => 0,
+ is_foreign_key => 0,
+ is_unique => 0 );
+
+
+ my $qt = '`';
+ my $qf = '`';
+ my $options = {
+ quote_table_names => $qt,
+ 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');
+
+ my $drop_field = SQL::Translator::Producer::MySQL::drop_field($field2, $options);
+ is($drop_field, 'ALTER TABLE `mydb`.`mytable` DROP COLUMN `myfield`', 'Drop field works');
+
+ my $field3_sql = SQL::Translator::Producer::MySQL::create_field($field3, { mysql_version => 4.1, %$options });
+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, %$options });
+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,$options);
+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, $options),
+ "`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, %$options }),
+ '`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, %$options }),
+ '`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}, $options),
+ '`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, %$options }),
+ '`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, %$options }),
+ '`vch_256` text',
+ 'VARCHAR(256) is substituted with TEXT for Mysql < 5.0.3'
+ );
+ is (
+ SQL::Translator::Producer::MySQL::create_field($varchars->{256}, $options),
+ '`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, %$options }),
+ '`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, %$options }),
+ '`vch_65535` text',
+ 'VARCHAR(65535) is substituted with TEXT for Mysql < 5.0.3'
+ );
+ is (
+ SQL::Translator::Producer::MySQL::create_field($varchars->{65535}, $options),
+ '`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, %$options }),
+ '`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, %$options }),
+ '`vch_65536` text',
+ 'VARCHAR(65536) is substituted with TEXT for Mysql < 5.0.3'
+ );
+ is (
+ SQL::Translator::Producer::MySQL::create_field($varchars->{65536}, $options),
+ '`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 `my`.`thing`',
+ extra => {
+ mysql_definer => 'CURRENT_USER',
+ mysql_algorithm => 'MERGE',
+ mysql_security => 'DEFINER',
+ });
+ my $create_opts = { add_replace_view => 1, no_comments => 1, %$options };
+ 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 `my`.`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 `my`.`thing`',);
+ my $create2_opts = { add_replace_view => 0, no_comments => 1, %$options };
+ 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 `my`.`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, $options);
+ is($sql, "`my$type` $type", "Skip length param for type $type");
+ }
+ }
+}
projects / dbsrgits/SQL-Translator.git / blobdiff