X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?a=blobdiff_plain;f=lib%2FSQL%2FTranslator%2FProducer%2FMySQL.pm;h=251781cf6ef6ec81c7dfecf2bbe564107cf57479;hb=4524cf01f6ee980d3eca760ffb3012c698d11d11;hp=b3124f73070c67f1347bedc47fd774271d3ca075;hpb=023c402625f90e592733c59310c998a4089cd03f;p=dbsrgits%2FSQL-Translator.git diff --git a/lib/SQL/Translator/Producer/MySQL.pm b/lib/SQL/Translator/Producer/MySQL.pm index b3124f7..251781c 100644 --- 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.11 2003-04-10 03:09:47 kycl4rk Exp $ +# $Id: MySQL.pm,v 1.26 2003-08-18 15:43:14 kycl4rk Exp $ # ------------------------------------------------------------------- # Copyright (C) 2003 Ken Y. Clark , # darren chamberlain , @@ -24,164 +24,197 @@ package SQL::Translator::Producer::MySQL; use strict; use vars qw[ $VERSION $DEBUG ]; -$VERSION = sprintf "%d.%02d", q$Revision: 1.11 $ =~ /(\d+)\.(\d+)/; +$VERSION = sprintf "%d.%02d", q$Revision: 1.26 $ =~ /(\d+)\.(\d+)/; $DEBUG = 0 unless defined $DEBUG; use Data::Dumper; -use SQL::Translator::Utils qw(debug); +use SQL::Translator::Schema::Constants; +use SQL::Translator::Utils qw(debug header_comment); + +my %translate = ( + # + # Oracle types + # + varchar2 => 'varchar', + long => 'text', + CLOB => 'longtext', + + # + # Sybase types + # + int => 'integer', + money => 'float', + real => 'double', + comment => 'text', + bit => 'tinyint', +); sub produce { - my ($translator, $data) = @_; - local $DEBUG = $translator->debug; - my $no_comments = $translator->no_comments; - my $add_drop_table = $translator->add_drop_table; + my $translator = shift; + local $DEBUG = $translator->debug; + my $no_comments = $translator->no_comments; + my $add_drop_table = $translator->add_drop_table; + my $schema = $translator->schema; debug("PKG: Beginning production\n"); my $create; - unless ( $no_comments ) { - $create .= sprintf "--\n-- Created by %s\n-- Created on %s\n--\n\n", - __PACKAGE__, scalar localtime; - } + $create .= header_comment unless ($no_comments); + # \todo Don't set if MySQL 3.x is set on command line + $create .= "SET foreign_key_checks=0;\n\n"; - for my $table ( keys %{ $data } ) { - debug("PKG: Looking at table '$table'\n"); - my $table_data = $data->{$table}; - my @fields = sort { - $table_data->{'fields'}->{$a}->{'order'} - <=> - $table_data->{'fields'}->{$b}->{'order'} - } keys %{$table_data->{'fields'}}; + for my $table ( $schema->get_tables ) { + my $table_name = $table->name; + debug("PKG: Looking at table '$table_name'\n"); # # Header. Should this look like what mysqldump produces? # - $create .= "--\n-- Table: $table\n--\n" unless $no_comments; - $create .= qq[DROP TABLE $table;\n] if $add_drop_table; - $create .= "CREATE TABLE $table ("; + $create .= "--\n-- Table: $table_name\n--\n" unless $no_comments; + $create .= qq[DROP TABLE IF EXISTS $table_name;\n] if $add_drop_table; + $create .= "CREATE TABLE $table_name (\n"; # # Fields # - for (my $i = 0; $i <= $#fields; $i++) { - my $field = $fields[$i]; - debug("PKG: Looking at field '$field'\n"); - my $field_data = $table_data->{'fields'}->{$field}; - my @fdata = ("", $field); - $create .= "\n"; + my @field_defs; + for my $field ( $table->get_fields ) { + my $field_name = $field->name; + debug("PKG: Looking at field '$field_name'\n"); + my $field_def = $field_name; # data type and size - my $attr = uc $field_data->{'data_type'} eq 'SET' ? 'list' : 'size'; - my @values = @{ $field_data->{ $attr } || [] }; - push @fdata, sprintf "%s%s", - $field_data->{'data_type'}, - ( @values ) - ? '('.join(', ', @values).')' - : ''; + my $data_type = $field->data_type; + my @size = $field->size; + my %extra = $field->extra; + my $list = $extra{'list'} || []; + # \todo deal with embedded quotes + my $commalist = join( ', ', map { qq['$_'] } @$list ); + + # + # Oracle "number" type -- figure best MySQL type + # + if ( lc $data_type eq 'number' ) { + # not an integer + if ( scalar @size > 1 ) { + $data_type = 'double'; + } + elsif ( $size[0] >= 12 ) { + $data_type = 'bigint'; + } + elsif ( $size[0] <= 1 ) { + $data_type = 'tinyint'; + } + else { + $data_type = 'int'; + } + } + elsif ( exists $translate{ $data_type } ) { + $data_type = $translate{ $data_type }; + } + + $field_def .= " $data_type"; + + if ( lc $data_type eq 'enum' ) { + $field_def .= '(' . $commalist . ')'; + } elsif ( defined $size[0] && $size[0] > 0 ) { + $field_def .= '(' . join( ', ', @size ) . ')'; + } # MySQL qualifiers for my $qual ( qw[ binary unsigned zerofill ] ) { - push @fdata, $qual - if $field_data->{ $qual } || - $field_data->{ uc $qual }; + my $val = $extra{ $qual || uc $qual } or next; + $field_def .= " $qual"; } # Null? - push @fdata, "NOT NULL" unless $field_data->{'null'}; + $field_def .= ' NOT NULL' unless $field->is_nullable; # Default? XXX Need better quoting! - my $default = $field_data->{'default'}; + my $default = $field->default_value; if ( defined $default ) { if ( uc $default eq 'NULL') { - push @fdata, "DEFAULT NULL"; + $field_def .= ' DEFAULT NULL'; } else { - push @fdata, "DEFAULT '$default'"; + $field_def .= " DEFAULT '$default'"; } } # auto_increment? - push @fdata, "auto_increment" if $field_data->{'is_auto_inc'}; - - # primary key? - # This is taken care of in the indices, could be duplicated here - # push @fdata, "PRIMARY KEY" if $field_data->{'is_primary_key'}; - - - $create .= (join " ", '', @fdata); - $create .= "," unless ($i == $#fields); - } + $field_def .= " auto_increment" if $field->is_auto_increment; + push @field_defs, $field_def; + } # # Indices # - my @index_creates; - my @indices = @{ $table_data->{'indices'} || [] }; - for (my $i = 0; $i <= $#indices; $i++) { - my $key = $indices[$i]; - my ($name, $type, $fields) = @{ $key }{ qw[ name type fields ] }; - $name ||= ''; - my $index_type = - $type eq 'primary_key' ? 'PRIMARY KEY' : - $type eq 'unique' ? 'UNIQUE KEY' : 'KEY'; - push @index_creates, - " $index_type $name (" . join( ', ', @$fields ) . ')'; - } - - if ( @index_creates ) { - $create .= join(",\n", '', @index_creates); + my @index_defs; + for my $index ( $table->get_indices ) { + push @index_defs, join( ' ', + lc $index->type eq 'normal' ? 'INDEX' : $index->type, + $index->name, + '(' . join( ', ', $index->fields ) . ')' + ); } # # Constraints -- need to handle more than just FK. -ky # - my @constraints; - for my $constraint ( @{ $table_data->{'constraints'} } ) { - my $name = $constraint->{'name'} || ''; - my $type = $constraint->{'type'}; - my $fields = $constraint->{'fields'}; - my $ref_table = $constraint->{'reference_table'}; - my $ref_fields = $constraint->{'reference_fields'}; - my $match_type = $constraint->{'match_type'} || ''; - my $on_delete = $constraint->{'on_delete_do'}; - my $on_update = $constraint->{'on_update_do'}; - - if ( $type eq 'foreign_key' ) { - my $def = join(' ', map { $_ || () } ' FOREIGN KEY', $name ); - if ( @$fields ) { - $def .= ' (' . join( ', ', @$fields ) . ')'; - } - $def .= " REFERENCES $ref_table"; + my @constraint_defs; + for my $c ( $table->get_constraints ) { + my @fields = $c->fields or next; - if ( @{ $ref_fields || [] } ) { - $def .= ' (' . join( ', ', @$ref_fields ) . ')'; + if ( $c->type eq PRIMARY_KEY ) { + push @constraint_defs, + 'PRIMARY KEY (' . join(', ', @fields). ')'; + } + elsif ( $c->type eq UNIQUE ) { + push @constraint_defs, + 'UNIQUE (' . join(', ', @fields). ')'; + } + elsif ( $c->type eq FOREIGN_KEY ) { + my $def = join(' ', + map { $_ || () } 'FOREIGN KEY', $c->name + ); + + $def .= ' (' . join( ', ', @fields ) . ')'; + + $def .= ' REFERENCES ' . $c->reference_table; + + if ( my @rfields = $c->reference_fields ) { + $def .= ' (' . join( ', ', @rfields ) . ')'; } - if ( $match_type ) { + if ( $c->match_type ) { $def .= ' MATCH ' . - ( $match_type =~ /full/i ) ? 'FULL' : 'PARTIAL'; + ( $c->match_type =~ /full/i ) ? 'FULL' : 'PARTIAL'; } - if ( @{ $on_delete || [] } ) { - $def .= ' ON DELETE '.join(' ', @$on_delete); + if ( $c->on_delete ) { + $def .= ' ON DELETE '.join( ' ', $c->on_delete ); } - if ( @{ $on_update || [] } ) { - $def .= ' ON UPDATE '.join(' ', @$on_update); + if ( $c->on_update ) { + $def .= ' ON UPDATE '.join( ' ', $c->on_update ); } - push @constraints, $def; + push @constraint_defs, $def; } } - $create .= join(",\n", '', @constraints) if @constraints; + $create .= join(",\n", map { " $_" } + @field_defs, @index_defs, @constraint_defs + ); # # Footer # $create .= "\n)"; - while ( my ( $key, $val ) = each %{ $table_data->{'table_options'} } ) { - $create .= " $key=$val" - } +# while ( +# my ( $key, $val ) = each %{ $table->options } +# ) { +# $create .= " $key=$val" +# } $create .= ";\n\n"; } @@ -195,7 +228,7 @@ __END__ SQL::Translator::Producer::MySQL - MySQL-specific producer for SQL::Translator -=head1 AUTHOR +=head1 AUTHORS darren chamberlain Edarren@cpan.orgE, Ken Y. Clark Ekclark@cpan.orgE