X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?a=blobdiff_plain;f=t%2F38-mysql-producer.t;h=b3c3bf88c378798711996f74e725632aa9fc3b05;hb=9d430e0976994de076ebb0602651e9e0eca332da;hp=a6ed999a728fe9176fcba15199246ee9b9143b48;hpb=1ded85136983878ef6745bc1b9fe2193499ba4d2;p=dbsrgits%2FSQL-Translator.git

diff --git a/t/38-mysql-producer.t b/t/38-mysql-producer.t
index a6ed999..b3c3bf8 100644
--- a/t/38-mysql-producer.t
+++ b/t/38-mysql-producer.t
@@ -1,4 +1,4 @@
-#!/usr/bin/perl -w 
+#!/usr/bin/perl -w
 # vim:filetype=perl
 
 #
@@ -19,7 +19,7 @@ use FindBin qw/$Bin/;
 #=============================================================================
 
 BEGIN {
-    maybe_plan(2,
+    maybe_plan(75,
         'YAML',
         'SQL::Translator::Producer::MySQL',
         'Test::Differences',
@@ -37,64 +37,809 @@ 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:
+          name: id
+          data_type: unsigned int
+          is_primary_key: 1
+          is_auto_increment: 1
+          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: 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
+          name: idx_unique_name
+
+    thing2:
+      name: some.thing2
+      extra:
+      order: 2
+      fields:
+        id:
+          name: id
+          data_type: int
+          is_primary_key: 0
+          order: 1
+          is_foreign_key: 1
+        foo:
+          name: foo
+          data_type: int
+          order: 2
+          is_not_null: 1
+        foo2:
+          name: foo2
+          data_type: int
+          order: 3
+          is_not_null: 1
+        bar_set:
+          name: bar_set
+          data_type: set
+          order: 4
+          is_not_null: 1
+          extra:
+            list:
+              - foo
+              - bar
+              - ba'z
+      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
+
+    thing3:
+      name: some.thing3
+      extra:
+      order: 3
+      fields:
+        id:
+          name: id
+          data_type: int
+          is_primary_key: 0
+          order: 1
+          is_foreign_key: 1
+        foo:
+          name: foo
+          data_type: int
+          order: 2
+          is_not_null: 1
+        foo2:
+          name: foo2
+          data_type: int
           order: 3
+          is_not_null: 1
+        bar_set:
+          name: bar_set
+          data_type: set
+          order: 4
+          is_not_null: 1
+          extra:
+            list:
+              - foo
+              - bar
+              - ba'z
+      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: some.thing2
+          type: FOREIGN_KEY
+          fields: foo
+          name: fk_thing
+        - reference_table: some.thing2
+          type: FOREIGN_KEY
+          fields: foo2
+          name: fk_thing
 EOSCHEMA
 
-my $mysql_out = <<EOSQL;
-SET foreign_key_checks=0;
+my @stmts = (
+"SET foreign_key_checks=0",
+
+"DROP TABLE IF EXISTS `thing`",
+"CREATE TABLE `thing` (
+  `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 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`),
+  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",
+
+"DROP TABLE IF EXISTS `some`.`thing3`",
+"CREATE TABLE `some`.`thing3` (
+  `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`),
+  INDEX (`foo2`),
+  PRIMARY KEY (`id`, `foo`),
+  CONSTRAINT `fk_thing_2` FOREIGN KEY (`foo`) REFERENCES `some`.`thing2` (`id`, `foo`),
+  CONSTRAINT `fk_thing_3` FOREIGN KEY (`foo2`) REFERENCES `some`.`thing2` (`id`, `foo`)
+) ENGINE=InnoDB",
+
+"SET foreign_key_checks=1",
+
+);
+
+my @stmts_no_drop = grep {$_ !~ /^DROP TABLE/} @stmts;
 
-CREATE TABLE thing (
-  name varchar(32),
-  swedish_name varchar(32) CHARACTER SET swe7,
-  description text CHARACTER SET utf8 COLLATE utf8_general_ci
-) DEFAULT CHARACTER SET latin1 COLLATE latin1_danish_ci;
+my $mysql_out = join(";\n\n", @stmts_no_drop) . ";\n\n";
 
-EOSQL
 
     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(\$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(\$yaml_in)
+      or die "Translat eerror:".$sqlt->error;
+    is_deeply \@out, \@stmts_no_drop, "Array output looks right with quoting";
+
+    $sqlt->quote_identifiers(0);
+
+    $out = $sqlt->translate(\$yaml_in)
+      or die "Translate error:".$sqlt->error;
+
+    @out = $sqlt->translate(\$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->quote_identifiers(1);
+    $sqlt->add_drop_table(1);
+
+    @out = $sqlt->translate(\$yaml_in)
+      or die "Translat eerror:".$sqlt->error;
+    $out = $sqlt->translate(\$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) NULL', '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) 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');
+
+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) 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(
+        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) 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) 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) 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) 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 NULL',
+    'VARCHAR(256) is substituted with TEXT for Mysql < 5.0.3'
+);
+is (
+    SQL::Translator::Producer::MySQL::create_field($varchars->{256}),
+    '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) 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 NULL',
+    'VARCHAR(65535) is substituted with TEXT for Mysql < 5.0.3'
+);
+is (
+    SQL::Translator::Producer::MySQL::create_field($varchars->{65535}),
+    '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 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 NULL',
+    'VARCHAR(65536) is substituted with TEXT for Mysql < 5.0.3'
+);
+is (
+    SQL::Translator::Producer::MySQL::create_field($varchars->{65536}),
+    'vch_65536 text NULL',
+    '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
 
-    my $out;
-    $yaml_in = do {
-        local $/ = undef;
-        open(FILE, "/home/grommit/src/sqlfairy/test.yml") or die "$!";
-        <FILE>
+  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 NULL", "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,
     };
-    $mysql_out = do {
-        local $/ = undef;
-        open(FILE, "/home/grommit/src/sqlfairy/test.sql") or die "$!";
-        <FILE>
+
+
+    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) 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');
+
+    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) 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(
+            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) 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) 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) 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) 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 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 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) 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 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 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 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 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 NULL',
+        '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 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');
+    }
 
-    $out = $sqlt->translate(\$yaml_in) or die "Translate error:".$sqlt->error;
-    ok $out ne ""                 ,"Produced something!";
-    eq_or_diff $out, $mysql_out   ,"Output looks right";
+    {
+        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');
+    }
 }