All Schema objects now have an extra attribute. Added parsing support (and

[dbsrgits/SQL-Translator.git] / lib / SQL / Translator / Producer / MySQL.pm

diff --git a/lib/SQL/Translator/Producer/MySQL.pm b/lib/SQL/Translator/Producer/MySQL.pm
index 85beb78..dd41858 100644 (file)
--- a/lib/SQL/Translator/Producer/MySQL.pm
+++ b/lib/SQL/Translator/Producer/MySQL.pm
@@ -1,7 +1,7 @@
 package SQL::Translator::Producer::MySQL;
 
 # -------------------------------------------------------------------
-# $Id: MySQL.pm,v 1.31 2004-02-09 23:02:15 kycl4rk Exp $
+# $Id: MySQL.pm,v 1.41 2004-09-20 20:48:19 kycl4rk Exp $
 # -------------------------------------------------------------------
 # Copyright (C) 2002-4 SQLFairy Authors
 #
@@ -44,7 +44,7 @@ for fields, etc.).
 
 use strict;
 use vars qw[ $VERSION $DEBUG ];
-$VERSION = sprintf "%d.%02d", q$Revision: 1.31 $ =~ /(\d+)\.(\d+)/;
+$VERSION = sprintf "%d.%02d", q$Revision: 1.41 $ =~ /(\d+)\.(\d+)/;
 $DEBUG   = 0 unless defined $DEBUG;
 
 use Data::Dumper;
@@ -70,6 +70,13 @@ my %translate  = (
     real       => 'double',
     comment    => 'text',
     bit        => 'tinyint',
+
+    #
+    # Access types
+    #
+    'long integer' => 'integer',
+    'text'         => 'text',
+    'datetime'     => 'datetime',
 );
 
 sub produce {
@@ -78,6 +85,7 @@ sub produce {
     my $no_comments    = $translator->no_comments;
     my $add_drop_table = $translator->add_drop_table;
     my $schema         = $translator->schema;
+    my $show_warnings  = $translator->show_warnings || 0;
 
     debug("PKG: Beginning production\n");
 
@@ -122,22 +130,40 @@ sub produce {
                 if ( scalar @size > 1 ) {
                     $data_type = 'double';
                 }
-                elsif ( $size[0] >= 12 ) {
+                elsif ( $size[0] && $size[0] >= 12 ) {
                     $data_type = 'bigint';
                 }
-                elsif ( $size[0] <= 1 ) {
+                elsif ( $size[0] && $size[0] <= 1 ) {
                     $data_type = 'tinyint';
                 }
                 else {
                     $data_type = 'int';
                 }
             }
+            #
+            # Convert a large Oracle varchar to "text"
+            #
+            elsif ( $data_type =~ /char/i && $size[0] > 255 ) {
+                $data_type = 'text';
+                @size      = ();
+            }
+            elsif ( $data_type =~ /char/i && ! $size[0] ) {
+                @size = (255);
+            }
+            elsif ( $data_type =~ /boolean/i ) {
+                $data_type = 'enum';
+                $commalist = "'0','1'";
+            }
             elsif ( exists $translate{ lc $data_type } ) {
                 $data_type = $translate{ lc $data_type };
             }
 
             @size = () if $data_type =~ /(text|blob)/i;
 
+            if ( $data_type =~ /(double|float)/ && scalar @size == 1 ) {
+                push @size, '0';
+            }
+
             $field_def .= " $data_type";
             
             if ( lc $data_type eq 'enum' ) {
@@ -175,19 +201,23 @@ sub produce {
         # Indices
         #
         my @index_defs;
+        my %indexed_fields;
         for my $index ( $table->get_indices ) {
             push @index_defs, join( ' ', 
                 lc $index->type eq 'normal' ? 'INDEX' : $index->type,
                 $index->name,
                 '(' . join( ', ', $index->fields ) . ')'
             );
+            $indexed_fields{ $_ } = 1 for $index->fields;
         }
 
         #
         # Constraints -- need to handle more than just FK. -ky
         #
         my @constraint_defs;
-        for my $c ( $table->get_constraints ) {
+        my $has_fk;
+        my @constraints = $table->get_constraints;
+        for my $c ( @constraints ) {
             my @fields = $c->fields or next;
 
             if ( $c->type eq PRIMARY_KEY ) {
@@ -199,6 +229,16 @@ sub produce {
                     'UNIQUE (' . join(', ', @fields). ')';
             }
             elsif ( $c->type eq FOREIGN_KEY ) {
+                $has_fk = 1;
+                
+                #
+                # Make sure FK field is indexed or MySQL complains.
+                #
+                unless ( $indexed_fields{ $fields[0] } ) {
+                    push @index_defs, "INDEX ($fields[0])";
+                    $indexed_fields{ $fields[0] } = 1;
+                }
+
                 my $def = join(' ', 
                     map { $_ || () } 'FOREIGN KEY', $c->name 
                 );
@@ -207,9 +247,26 @@ sub produce {
 
                 $def .= ' REFERENCES ' . $c->reference_table;
 
-                if ( my @rfields = $c->reference_fields ) {
+                my @rfields = map { $_ || () } $c->reference_fields;
+                unless ( @rfields ) {
+                    my $rtable_name = $c->reference_table;
+                    if ( my $ref_table = $schema->get_table( $rtable_name ) ) {
+                        push @rfields, $ref_table->primary_key;
+                    }
+                    else {
+                        warn "Can't find reference table '$rtable_name' " .
+                            "in schema\n" if $show_warnings;
+                    }
+                }
+
+                if ( @rfields ) {
                     $def .= ' (' . join( ', ', @rfields ) . ')';
                 }
+                else {
+                    warn "FK constraint on " . $table->name . '.' .
+                        join('', @fields) . " has no reference fields\n" 
+                        if $show_warnings;
+                }
 
                 if ( $c->match_type ) {
                     $def .= ' MATCH ' . 
@@ -236,11 +293,9 @@ sub produce {
         # Footer
         #
         $create .= "\n)";
-#        while ( 
-#            my ( $key, $val ) = each %{ $table->options }
-#        ) {
-#            $create .= " $key=$val" 
-#        }
+        if ( $has_fk ) {
+            $create .= " Type=InnoDB";
+        }
         $create .= ";\n\n";
     }