X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?a=blobdiff_plain;f=t%2F38-mysql-producer.t;h=0660697e42ff3658f7f7975f213390a64efc5673;hb=b307a0db18f6d0a82ecfc4c0ab71ca62353c69bb;hp=ce5bbdde3e1c72bfc06910372108e39d2591a135;hpb=ca1f9923d3585f8a2eba5f556616d9a2713d108e;p=dbsrgits%2FSQL-Translator.git

diff --git a/t/38-mysql-producer.t b/t/38-mysql-producer.t
index ce5bbdd..0660697 100644
--- a/t/38-mysql-producer.t
+++ b/t/38-mysql-producer.t
@@ -19,7 +19,7 @@ use FindBin qw/$Bin/;
 #=============================================================================
 
 BEGIN {
-    maybe_plan(14,
+    maybe_plan(40,
         'YAML',
         'SQL::Translator::Producer::MySQL',
         'Test::Differences',
@@ -74,7 +74,7 @@ schema:
           name: idx_unique_name
 
     thing2:
-      name: thing2
+      name: some.thing2
       extra:
       order: 2
       fields:
@@ -94,15 +94,25 @@ schema:
           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: 
+          fields:
             - id
           name: index_1
         - type: NORMAL
-          fields: 
+          fields:
             - id
-          name: index_2
+          name: really_long_name_bigger_than_64_chars_aaaaaaaaaaaaaaaaaaaaaaaaaaa
       constraints:
         - type: PRIMARY_KEY
           fields:
@@ -120,39 +130,40 @@ schema:
 EOSCHEMA
 
 my @stmts = (
-"SET foreign_key_checks=0;\n\n",
+"SET foreign_key_checks=0",
 
-"DROP TABLE IF EXISTS `thing`;\n",
+"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,
+  `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;\n\n",
+) ENGINE=InnoDB DEFAULT CHARACTER SET latin1 COLLATE latin1_danish_ci",
 
-"DROP TABLE IF EXISTS `thing2`;\n",
-"CREATE TABLE `thing2` (
+"DROP TABLE IF EXISTS `some`.`thing2`",
+"CREATE TABLE `some`.`thing2` (
   `id` integer,
   `foo` integer,
   `foo2` integer,
-  INDEX index_1 (`id`),
-  INDEX index_2 (`id`),
+  `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;\n\n",
+) ENGINE=InnoDB",
 
-"SET foreign_key_checks=1;\n\n"
+"SET foreign_key_checks=1",
 
 );
 
 my @stmts_no_drop = grep {$_ !~ /^DROP TABLE/} @stmts;
 
-my $mysql_out = join("", @stmts_no_drop);
+my $mysql_out = join(";\n\n", @stmts_no_drop) . ";\n\n";
 
 
     my $sqlt;
@@ -193,7 +204,7 @@ my $mysql_out = join("", @stmts_no_drop);
     $out = $sqlt->translate(\$yaml_in)
       or die "Translat eerror:".$sqlt->error;
 
-    eq_or_diff $out, join("", @stmts), "Output looks right with DROP TABLEs";
+    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";
 }
 
@@ -250,3 +261,190 @@ $field3_sql = SQL::Translator::Producer::MySQL::create_field($field3, { mysql_ve
 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 = <<'EOV';
+CREATE OR REPLACE
+   ALGORITHM = MERGE
+   DEFINER = CURRENT_USER
+   SQL SECURITY DEFINER
+  VIEW view_foo ( id, name ) AS
+    SELECT id, name FROM 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 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 = <<'EOV';
+CREATE
+  VIEW view_foo ( id, name ) AS
+    SELECT id, name FROM 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);
+        is($sql, "my$type $type", "Skip length param for type $type");
+    }
+}