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

package SQL::Translator::Producer::MySQL;

# -------------------------------------------------------------------
# $Id: MySQL.pm,v 1.49 2006-07-23 14:03:52 schiffbruechige Exp $
# -------------------------------------------------------------------
# Copyright (C) 2002-4 SQLFairy Authors
#
# This program is free software; you can redistribute it and/or
# modify it under the terms of the GNU General Public License as
# published by the Free Software Foundation; version 2.
#
# This program is distributed in the hope that it will be useful, but
# WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
# General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA
# 02111-1307  USA
# -------------------------------------------------------------------

=head1 NAME

SQL::Translator::Producer::MySQL - MySQL-specific producer for SQL::Translator

=head1 SYNOPSIS

Use via SQL::Translator:

  use SQL::Translator;

  my $t = SQL::Translator->new( parser => '...', producer => 'MySQL', '...' );
  $t->translate;

=head1 DESCRIPTION

This module will produce text output of the schema suitable for MySQL.
There are still some issues to be worked out with syntax differences 
between MySQL versions 3 and 4 ("SET foreign_key_checks," character sets
for fields, etc.).

=head2 Table Types

Normally the tables will be created without any explicit table type given and
so will use the MySQL default.

Any tables involved in foreign key constraints automatically get a table type
of InnoDB, unless this is overridden by setting the C<mysql_table_type> extra
attribute explicitly on the table.

=head2 Extra attributes.

The producer recognises the following extra attributes on the Schema objects.

=over 4

=item field.list

Set the list of allowed values for Enum fields.

=item field.binary field.unsigned field.zerofill

Set the MySQL field options of the same name.

=item table.mysql_table_type

Set the type of the table e.g. 'InnoDB', 'MyISAM'. This will be
automatically set for tables involved in foreign key constraints if it is
not already set explicitly. See L<"Table Types">.

=item mysql_character_set

MySql-4.1+. Set the tables character set.
Run SHOW CHARACTER SET to see list.

=item mysql_collate

MySql-4.1+. Set the tables colation order.

=item table.mysql_charset table.mysql_collate

Set the tables default charater set and collation order.

=item field.mysql_charset field.mysql_collate

Set the fields charater set and collation order.

=back

=cut

use strict;
use warnings;
use vars qw[ $VERSION $DEBUG ];
$VERSION = sprintf "%d.%02d", q$Revision: 1.49 $ =~ /(\d+)\.(\d+)/;
$DEBUG   = 0 unless defined $DEBUG;

use Data::Dumper;
use SQL::Translator::Schema::Constants;
use SQL::Translator::Utils qw(debug header_comment);

#
# Use only lowercase for the keys (e.g. "long" and not "LONG")
#
my %translate  = (
    #
    # Oracle types
    #
    varchar2   => 'varchar',
    long       => 'text',
    clob       => 'longtext',

    #
    # Sybase types
    #
    int        => 'integer',
    money      => 'float',
    real       => 'double',
    comment    => 'text',
    bit        => 'tinyint',

    #
    # Access types
    #
    'long integer' => 'integer',
    'text'         => 'text',
    'datetime'     => 'datetime',
);

sub produce {
    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;
    my $show_warnings  = $translator->show_warnings || 0;

    debug("PKG: Beginning production\n");

    my $create; 
    $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";

    #
    # Work out which tables need to be InnoDB to support foreign key
    # constraints. We do this first as we need InnoDB at both ends.
    #
    foreach ( map { $_->get_constraints } $schema->get_tables ) {
        next unless $_->type eq FOREIGN_KEY;
        foreach my $meth (qw/table reference_table/) {
            my $table = $schema->get_table($_->$meth) || next;
            next if $table->extra('mysql_table_type');
            $table->extra( 'mysql_table_type' => 'InnoDB');
        }
    }

    #
    # Generate sql
    #
    my @table_defs =();
    for my $table ( $schema->get_tables ) {
#        print $table->name, "\n";
        push @table_defs, create_table($table, 
                                       { add_drop_table => $add_drop_table,
                                         show_warnings  => $show_warnings,
                                         no_comments    => $no_comments
                                         });
    }

#    print "@table_defs\n";

    return wantarray ? ($create, @table_defs, 'SET foreign_key_checks=1;') : $create . join ('', @table_defs, "SET foreign_key_checks=1;\n\n");
}

sub create_table
{
    my ($table, $options) = @_;

    my $table_name = $table->name;
    debug("PKG: Looking at table '$table_name'\n");

    #
    # Header.  Should this look like what mysqldump produces?
    #
    my $create = '';
    $create .= "--\n-- Table: $table_name\n--\n" unless $options->{no_comments};
    $create .= qq[DROP TABLE IF EXISTS $table_name;\n] if $options->{add_drop_table};
    $create .= "CREATE TABLE $table_name (\n";

    #
    # Fields
    #
    my @field_defs;
    for my $field ( $table->get_fields ) {
        push @field_defs, create_field($field);
    }

    #
    # Indices
    #
    my @index_defs;
    my %indexed_fields;
    for my $index ( $table->get_indices ) {
        push @index_defs, create_index($index);
        $indexed_fields{ $_ } = 1 for $index->fields;
    }

    #
    # Constraints -- need to handle more than just FK. -ky
    #
    my @constraint_defs;
    my @constraints = $table->get_constraints;
    for my $c ( @constraints ) {
        my $constr = create_constraint($c, $options);
        push @constraint_defs, $constr if($constr);
        
        unless ( $indexed_fields{ ($c->fields())[0] } ) {
            push @index_defs, "INDEX (" . ($c->fields())[0] . ")";
            $indexed_fields{ ($c->fields())[0] } = 1;
        }
    }

    $create .= join(",\n", map { "  $_" } 
                    @field_defs, @index_defs, @constraint_defs
                    );

    #
    # Footer
    #
    $create .= "\n)";
    my $table_type_defined = 0;
    for my $t1_option_ref ( $table->options ) {
        my($key, $value) = %{$t1_option_ref};
        $table_type_defined = 1
            if uc $key eq 'ENGINE' or uc $key eq 'TYPE';
        $create .= " $key=$value";
    }
    my $mysql_table_type = $table->extra('mysql_table_type');
    #my $charset          = $table->extra('mysql_character_set');
    #my $collate          = $table->extra('mysql_collate');
    #$create .= " Type=$mysql_table_type" if $mysql_table_type;
    #$create .= " DEFAULT CHARACTER SET $charset" if $charset;
    #$create .= " COLLATE $collate" if $collate;
    $create .= " Type=$mysql_table_type"
        if $mysql_table_type && !$table_type_defined;
    my $charset          = $table->extra('mysql_charset');
    my $collate          = $table->extra('mysql_collate');
    my $comments         = $table->comments;

    $create .= " DEFAULT CHARACTER SET $charset" if $charset;
    $create .= " COLLATE $collate" if $collate;
    $create .= qq[ comment='$comments'] if $comments;
    $create .= ";\n\n";

    return $create;
}

sub create_field
{
    my ($field) = @_;

    my $field_name = $field->name;
    debug("PKG: Looking at field '$field_name'\n");
    my $field_def = $field_name;

    # data type and size
    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 );
    my $charset = $extra{'mysql_charset'};
    my $collate = $extra{'mysql_collate'};

    #
    # 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] && $size[0] >= 12 ) {
            $data_type = 'bigint';
        }
        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' ) {
        $field_def .= '(' . $commalist . ')';
    } 
    elsif ( defined $size[0] && $size[0] > 0 ) {
        $field_def .= '(' . join( ', ', @size ) . ')';
    }

    # char sets
    $field_def .= " CHARACTER SET $charset" if $charset;
    $field_def .= " COLLATE $collate" if $collate;

    # MySQL qualifiers
    for my $qual ( qw[ binary unsigned zerofill ] ) {
        my $val = $extra{ $qual } || $extra{ uc $qual } or next;
        $field_def .= " $qual";
    }
    for my $qual ( 'character set', 'collate', 'on update' ) {
        my $val = $extra{ $qual } || $extra{ uc $qual } or next;
        $field_def .= " $qual $val";
    }

    # Null?
    $field_def .= ' NOT NULL' unless $field->is_nullable;

    # Default?  XXX Need better quoting!
    my $default = $field->default_value;
    if ( defined $default ) {
        if ( uc $default eq 'NULL') {
            $field_def .= ' DEFAULT NULL';
        } else {
            $field_def .= " DEFAULT '$default'";
        }
    }

    if ( my $comments = $field->comments ) {
        $field_def .= qq[ comment '$comments'];
    }

    # auto_increment?
    $field_def .= " auto_increment" if $field->is_auto_increment;

    return $field_def;
}

sub create_index
{
    my ($index) = @_;

    return join( ' ', 
                 lc $index->type eq 'normal' ? 'INDEX' : $index->type,
                 $index->name,
                 '(' . join( ', ', $index->fields ) . ')'
                 );

}

sub create_constraint
{
    my ($c, $options) = @_;

    my @fields = $c->fields or next;

    if ( $c->type eq PRIMARY_KEY ) {
        return 'PRIMARY KEY (' . join(', ', @fields). ')';
    }
    elsif ( $c->type eq UNIQUE ) {
        return
        'UNIQUE '.
            (defined $c->name ? $c->name.' ' : '').
            '(' . join(', ', @fields). ')';
    }
    elsif ( $c->type eq FOREIGN_KEY ) {
        #
        # Make sure FK field is indexed or MySQL complains.
        #

        my $def = join(' ', 
                       map { $_ || () } 'CONSTRAINT', $c->table . '_' . $c->name, 'FOREIGN KEY'
                       );

        $def .= ' (' . join( ', ', @fields ) . ')';

        $def .= ' REFERENCES ' . $c->reference_table;

        my @rfields = map { $_ || () } $c->reference_fields;
        unless ( @rfields ) {
            my $rtable_name = $c->reference_table;
            if ( my $ref_table = $c->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 $options->{show_warnings};
            }
        }

        if ( @rfields ) {
            $def .= ' (' . join( ', ', @rfields ) . ')';
        }
        else {
            warn "FK constraint on " . $c->table->name . '.' .
                join('', @fields) . " has no reference fields\n" 
                if $options->{show_warnings};
        }

        if ( $c->match_type ) {
            $def .= ' MATCH ' . 
                ( $c->match_type =~ /full/i ) ? 'FULL' : 'PARTIAL';
        }

        if ( $c->on_delete ) {
            $def .= ' ON DELETE '.join( ' ', $c->on_delete );
        }

        if ( $c->on_update ) {
            $def .= ' ON UPDATE '.join( ' ', $c->on_update );
        }
        return $def;
    }

    return undef;
}

sub alter_field
{
    my ($from_field, $to_field) = @_;

    my $out = sprintf('ALTER TABLE %s CHANGE COLUMN %s %s',
                      $to_field->table->name,
                      $to_field->name,
                      create_field($to_field));

    return $out;
}

sub add_field
{
    my ($new_field) = @_;

    my $out = sprintf('ALTER TABLE %s ADD COLUMN %s',
                      $new_field->table->name,
                      create_field($new_field));

    return $out;

}

sub drop_field
{ 
    my ($old_field) = @_;

    my $out = sprintf('ALTER TABLE %s DROP COLUMN %s',
                      $old_field->table->name,
                      $old_field->name);

    return $out;
    
}

1;

# -------------------------------------------------------------------

=pod

=head1 SEE ALSO

SQL::Translator, http://www.mysql.com/.

=head1 AUTHORS

darren chamberlain E<lt>darren@cpan.orgE<gt>,
Ken Y. Clark E<lt>kclark@cpan.orgE<gt>.

=cut
Commit	Line	Data
9398955f	1	package SQL::Translator::Producer::MySQL;
9398955f	2
49e1eb70	3	# -------------------------------------------------------------------
bfb5a568	4	# $Id: MySQL.pm,v 1.49 2006-07-23 14:03:52 schiffbruechige Exp $
49e1eb70	5	# -------------------------------------------------------------------
977651a5	6	# Copyright (C) 2002-4 SQLFairy Authors
9398955f	7	#
	8	# This program is free software; you can redistribute it and/or
	9	# modify it under the terms of the GNU General Public License as
	10	# published by the Free Software Foundation; version 2.
	11	#
	12	# This program is distributed in the hope that it will be useful, but
	13	# WITHOUT ANY WARRANTY; without even the implied warranty of
	14	# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
	15	# General Public License for more details.
	16	#
	17	# You should have received a copy of the GNU General Public License
	18	# along with this program; if not, write to the Free Software
	19	# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA
	20	# 02111-1307 USA
	21	# -------------------------------------------------------------------
	22
c855a748	23	=head1 NAME
	24
	25	SQL::Translator::Producer::MySQL - MySQL-specific producer for SQL::Translator
	26
	27	=head1 SYNOPSIS
	28
	29	Use via SQL::Translator:
	30
	31	use SQL::Translator;
	32
	33	my $t = SQL::Translator->new( parser => '...', producer => 'MySQL', '...' );
	34	$t->translate;
	35
	36	=head1 DESCRIPTION
	37
	38	This module will produce text output of the schema suitable for MySQL.
	39	There are still some issues to be worked out with syntax differences
	40	between MySQL versions 3 and 4 ("SET foreign_key_checks," character sets
	41	for fields, etc.).
	42
5a0c7b43	43	=head2 Table Types
	44
	45	Normally the tables will be created without any explicit table type given and
	46	so will use the MySQL default.
	47
	48	Any tables involved in foreign key constraints automatically get a table type
	49	of InnoDB, unless this is overridden by setting the C<mysql_table_type> extra
	50	attribute explicitly on the table.
	51
	52	=head2 Extra attributes.
	53
	54	The producer recognises the following extra attributes on the Schema objects.
	55
	56	=over 4
	57
	58	=item field.list
	59
	60	Set the list of allowed values for Enum fields.
	61
	62	=item field.binary field.unsigned field.zerofill
	63
	64	Set the MySQL field options of the same name.
	65
1ded8513	66	=item table.mysql_table_type
5a0c7b43	67
	68	Set the type of the table e.g. 'InnoDB', 'MyISAM'. This will be
	69	automatically set for tables involved in foreign key constraints if it is
	70	not already set explicitly. See L<"Table Types">.
	71
4257646e	72	=item mysql_character_set
	73
	74	MySql-4.1+. Set the tables character set.
	75	Run SHOW CHARACTER SET to see list.
	76
	77	=item mysql_collate
	78
	79	MySql-4.1+. Set the tables colation order.
	80
1ded8513	81	=item table.mysql_charset table.mysql_collate
	82
	83	Set the tables default charater set and collation order.
	84
	85	=item field.mysql_charset field.mysql_collate
	86
	87	Set the fields charater set and collation order.
	88
5a0c7b43	89	=back
5a0c7b43	90
c855a748	91	=cut
c855a748	92
9398955f	93	use strict;
cd0ea0fd	94	use warnings;
d529894e	95	use vars qw[ $VERSION $DEBUG ];
bfb5a568	96	$VERSION = sprintf "%d.%02d", q$Revision: 1.49 $ =~ /(\d+)\.(\d+)/;
5636ed00	97	$DEBUG = 0 unless defined $DEBUG;
9398955f	98
9398955f	99	use Data::Dumper;
1c14e9f1	100	use SQL::Translator::Schema::Constants;
5ee19df8	101	use SQL::Translator::Utils qw(debug header_comment);
9398955f	102
d2344c83	103	#
	104	# Use only lowercase for the keys (e.g. "long" and not "LONG")
	105	#
2620fc1c	106	my %translate = (
	107	#
	108	# Oracle types
	109	#
	110	varchar2 => 'varchar',
	111	long => 'text',
d2344c83	112	clob => 'longtext',
2620fc1c	113
	114	#
	115	# Sybase types
	116	#
	117	int => 'integer',
	118	money => 'float',
	119	real => 'double',
	120	comment => 'text',
	121	bit => 'tinyint',
87779799	122
	123	#
	124	# Access types
	125	#
	126	'long integer' => 'integer',
	127	'text' => 'text',
	128	'datetime' => 'datetime',
2620fc1c	129	);
2620fc1c	130
9398955f	131	sub produce {
a1d94525	132	my $translator = shift;
	133	local $DEBUG = $translator->debug;
	134	my $no_comments = $translator->no_comments;
	135	my $add_drop_table = $translator->add_drop_table;
	136	my $schema = $translator->schema;
2bc23e82	137	my $show_warnings = $translator->show_warnings \|\| 0;
d529894e	138
1a24938d	139	debug("PKG: Beginning production\n");
d529894e	140
d529894e	141	my $create;
5ee19df8	142	$create .= header_comment unless ($no_comments);
0823773d	143	# \todo Don't set if MySQL 3.x is set on command line
da147d03	144	$create .= "SET foreign_key_checks=0;\n\n";
9398955f	145
5a0c7b43	146	#
	147	# Work out which tables need to be InnoDB to support foreign key
	148	# constraints. We do this first as we need InnoDB at both ends.
	149	#
	150	foreach ( map { $_->get_constraints } $schema->get_tables ) {
cd0ea0fd	151	next unless $_->type eq FOREIGN_KEY;
5a0c7b43	152	foreach my $meth (qw/table reference_table/) {
	153	my $table = $schema->get_table($_->$meth) \|\| next;
	154	next if $table->extra('mysql_table_type');
	155	$table->extra( 'mysql_table_type' => 'InnoDB');
	156	}
	157	}
	158
	159	#
	160	# Generate sql
	161	#
cd0ea0fd	162	my @table_defs =();
1c14e9f1	163	for my $table ( $schema->get_tables ) {
cd0ea0fd	164	# print $table->name, "\n";
0013ee25	165	push @table_defs, create_table($table,
	166	{ add_drop_table => $add_drop_table,
	167	show_warnings => $show_warnings,
	168	no_comments => $no_comments
	169	});
	170	}
9398955f	171
cd0ea0fd	172	# print "@table_defs\n";
cd0ea0fd	173
bfb5a568	174	return wantarray ? ($create, @table_defs, 'SET foreign_key_checks=1;') : $create . join ('', @table_defs, "SET foreign_key_checks=1;\n\n");
0013ee25	175	}
9398955f	176
0013ee25	177	sub create_table
	178	{
	179	my ($table, $options) = @_;
2620fc1c	180
0013ee25	181	my $table_name = $table->name;
0013ee25	182	debug("PKG: Looking at table '$table_name'\n");
6d3f6379	183
0013ee25	184	#
	185	# Header. Should this look like what mysqldump produces?
	186	#
cd0ea0fd	187	my $create = '';
cd0ea0fd	188	$create .= "--\n-- Table: $table_name\n--\n" unless $options->{no_comments};
0013ee25	189	$create .= qq[DROP TABLE IF EXISTS $table_name;\n] if $options->{add_drop_table};
0013ee25	190	$create .= "CREATE TABLE $table_name (\n";
2b695517	191
0013ee25	192	#
	193	# Fields
	194	#
	195	my @field_defs;
	196	for my $field ( $table->get_fields ) {
	197	push @field_defs, create_field($field);
	198	}
1ded8513	199
0013ee25	200	#
	201	# Indices
	202	#
	203	my @index_defs;
	204	my %indexed_fields;
	205	for my $index ( $table->get_indices ) {
	206	push @index_defs, create_index($index);
	207	$indexed_fields{ $_ } = 1 for $index->fields;
	208	}
d529894e	209
0013ee25	210	#
	211	# Constraints -- need to handle more than just FK. -ky
	212	#
	213	my @constraint_defs;
	214	my @constraints = $table->get_constraints;
	215	for my $c ( @constraints ) {
cd0ea0fd	216	my $constr = create_constraint($c, $options);
cd0ea0fd	217	push @constraint_defs, $constr if($constr);
0013ee25	218
	219	unless ( $indexed_fields{ ($c->fields())[0] } ) {
	220	push @index_defs, "INDEX (" . ($c->fields())[0] . ")";
	221	$indexed_fields{ ($c->fields())[0] } = 1;
	222	}
	223	}
1ded8513	224
0013ee25	225	$create .= join(",\n", map { " $_" }
	226	@field_defs, @index_defs, @constraint_defs
	227	);
9398955f	228
0013ee25	229	#
	230	# Footer
	231	#
	232	$create .= "\n)";
	233	my $table_type_defined = 0;
	234	for my $t1_option_ref ( $table->options ) {
	235	my($key, $value) = %{$t1_option_ref};
	236	$table_type_defined = 1
	237	if uc $key eq 'ENGINE' or uc $key eq 'TYPE';
	238	$create .= " $key=$value";
	239	}
	240	my $mysql_table_type = $table->extra('mysql_table_type');
	241	#my $charset = $table->extra('mysql_character_set');
	242	#my $collate = $table->extra('mysql_collate');
	243	#$create .= " Type=$mysql_table_type" if $mysql_table_type;
	244	#$create .= " DEFAULT CHARACTER SET $charset" if $charset;
	245	#$create .= " COLLATE $collate" if $collate;
	246	$create .= " Type=$mysql_table_type"
	247	if $mysql_table_type && !$table_type_defined;
	248	my $charset = $table->extra('mysql_charset');
	249	my $collate = $table->extra('mysql_collate');
	250	my $comments = $table->comments;
	251
	252	$create .= " DEFAULT CHARACTER SET $charset" if $charset;
	253	$create .= " COLLATE $collate" if $collate;
	254	$create .= qq[ comment='$comments'] if $comments;
	255	$create .= ";\n\n";
9398955f	256
0013ee25	257	return $create;
0013ee25	258	}
f8b6e804	259
0013ee25	260	sub create_field
	261	{
	262	my ($field) = @_;
9398955f	263
0013ee25	264	my $field_name = $field->name;
	265	debug("PKG: Looking at field '$field_name'\n");
	266	my $field_def = $field_name;
	267
	268	# data type and size
	269	my $data_type = $field->data_type;
	270	my @size = $field->size;
	271	my %extra = $field->extra;
	272	my $list = $extra{'list'} \|\| [];
	273	# \todo deal with embedded quotes
	274	my $commalist = join( ', ', map { qq['$_'] } @$list );
	275	my $charset = $extra{'mysql_charset'};
	276	my $collate = $extra{'mysql_collate'};
	277
	278	#
	279	# Oracle "number" type -- figure best MySQL type
	280	#
	281	if ( lc $data_type eq 'number' ) {
	282	# not an integer
	283	if ( scalar @size > 1 ) {
	284	$data_type = 'double';
d529894e	285	}
0013ee25	286	elsif ( $size[0] && $size[0] >= 12 ) {
	287	$data_type = 'bigint';
	288	}
	289	elsif ( $size[0] && $size[0] <= 1 ) {
	290	$data_type = 'tinyint';
	291	}
	292	else {
	293	$data_type = 'int';
	294	}
	295	}
	296	#
	297	# Convert a large Oracle varchar to "text"
	298	#
	299	elsif ( $data_type =~ /char/i && $size[0] > 255 ) {
	300	$data_type = 'text';
	301	@size = ();
	302	}
	303	elsif ( $data_type =~ /char/i && ! $size[0] ) {
	304	@size = (255);
	305	}
	306	elsif ( $data_type =~ /boolean/i ) {
	307	$data_type = 'enum';
	308	$commalist = "'0','1'";
	309	}
	310	elsif ( exists $translate{ lc $data_type } ) {
	311	$data_type = $translate{ lc $data_type };
	312	}
	313
	314	@size = () if $data_type =~ /(text\|blob)/i;
	315
	316	if ( $data_type =~ /(double\|float)/ && scalar @size == 1 ) {
	317	push @size, '0';
	318	}
d529894e	319
0013ee25	320	$field_def .= " $data_type";
	321
	322	if ( lc $data_type eq 'enum' ) {
	323	$field_def .= '(' . $commalist . ')';
	324	}
	325	elsif ( defined $size[0] && $size[0] > 0 ) {
	326	$field_def .= '(' . join( ', ', @size ) . ')';
	327	}
	328
	329	# char sets
	330	$field_def .= " CHARACTER SET $charset" if $charset;
	331	$field_def .= " COLLATE $collate" if $collate;
	332
	333	# MySQL qualifiers
	334	for my $qual ( qw[ binary unsigned zerofill ] ) {
	335	my $val = $extra{ $qual } \|\| $extra{ uc $qual } or next;
	336	$field_def .= " $qual";
	337	}
	338	for my $qual ( 'character set', 'collate', 'on update' ) {
	339	my $val = $extra{ $qual } \|\| $extra{ uc $qual } or next;
	340	$field_def .= " $qual $val";
	341	}
	342
	343	# Null?
	344	$field_def .= ' NOT NULL' unless $field->is_nullable;
	345
	346	# Default? XXX Need better quoting!
	347	my $default = $field->default_value;
	348	if ( defined $default ) {
	349	if ( uc $default eq 'NULL') {
	350	$field_def .= ' DEFAULT NULL';
	351	} else {
	352	$field_def .= " DEFAULT '$default'";
	353	}
	354	}
	355
	356	if ( my $comments = $field->comments ) {
	357	$field_def .= qq[ comment '$comments'];
	358	}
	359
	360	# auto_increment?
	361	$field_def .= " auto_increment" if $field->is_auto_increment;
	362
	363	return $field_def;
	364	}
	365
	366	sub create_index
	367	{
	368	my ($index) = @_;
	369
	370	return join( ' ',
	371	lc $index->type eq 'normal' ? 'INDEX' : $index->type,
	372	$index->name,
	373	'(' . join( ', ', $index->fields ) . ')'
	374	);
	375
	376	}
	377
	378	sub create_constraint
	379	{
	380	my ($c, $options) = @_;
	381
	382	my @fields = $c->fields or next;
	383
384	if ( $c->type eq PRIMARY_KEY ) {
385	return 'PRIMARY KEY (' . join(', ', @fields). ')';
386	}
387	elsif ( $c->type eq UNIQUE ) {
388	return
389	'UNIQUE '.
390	(defined $c->name ? $c->name.' ' : '').
391	'(' . join(', ', @fields). ')';
392	}
393	elsif ( $c->type eq FOREIGN_KEY ) {
d529894e	394	#
0013ee25	395	# Make sure FK field is indexed or MySQL complains.
5e56da9a	396	#
0013ee25	397
0013ee25	398	my $def = join(' ',
cd0ea0fd	399	map { $_ \|\| () } 'CONSTRAINT', $c->table . '_' . $c->name, 'FOREIGN KEY'
0013ee25	400	);
	401
	402	$def .= ' (' . join( ', ', @fields ) . ')';
	403
	404	$def .= ' REFERENCES ' . $c->reference_table;
	405
	406	my @rfields = map { $_ \|\| () } $c->reference_fields;
	407	unless ( @rfields ) {
	408	my $rtable_name = $c->reference_table;
	409	if ( my $ref_table = $c->table->schema->get_table( $rtable_name ) ) {
	410	push @rfields, $ref_table->primary_key;
1c14e9f1	411	}
0013ee25	412	else {
	413	warn "Can't find reference table '$rtable_name' " .
	414	"in schema\n" if $options->{show_warnings};
5e56da9a	415	}
	416	}
	417
0013ee25	418	if ( @rfields ) {
	419	$def .= ' (' . join( ', ', @rfields ) . ')';
	420	}
	421	else {
	422	warn "FK constraint on " . $c->table->name . '.' .
	423	join('', @fields) . " has no reference fields\n"
	424	if $options->{show_warnings};
	425	}
5e56da9a	426
0013ee25	427	if ( $c->match_type ) {
	428	$def .= ' MATCH ' .
	429	( $c->match_type =~ /full/i ) ? 'FULL' : 'PARTIAL';
	430	}
	431
	432	if ( $c->on_delete ) {
	433	$def .= ' ON DELETE '.join( ' ', $c->on_delete );
	434	}
	435
	436	if ( $c->on_update ) {
	437	$def .= ' ON UPDATE '.join( ' ', $c->on_update );
	438	}
cd0ea0fd	439	return $def;
9398955f	440	}
9398955f	441
cd0ea0fd	442	return undef;
0013ee25	443	}
	444
	445	sub alter_field
	446	{
	447	my ($from_field, $to_field) = @_;
	448
	449	my $out = sprintf('ALTER TABLE %s CHANGE COLUMN %s %s',
	450	$to_field->table->name,
	451	$to_field->name,
	452	create_field($to_field));
	453
	454	return $out;
	455	}
	456
	457	sub add_field
	458	{
	459	my ($new_field) = @_;
	460
	461	my $out = sprintf('ALTER TABLE %s ADD COLUMN %s',
	462	$new_field->table->name,
	463	create_field($new_field));
	464
	465	return $out;
	466
	467	}
	468
	469	sub drop_field
	470	{
	471	my ($old_field) = @_;
	472
	473	my $out = sprintf('ALTER TABLE %s DROP COLUMN %s',
	474	$old_field->table->name,
	475	$old_field->name);
	476
	477	return $out;
	478
9398955f	479	}
9398955f	480
9398955f	481	1;
9398955f	482
c855a748	483	# -------------------------------------------------------------------
9398955f	484
c855a748	485	=pod
	486
	487	=head1 SEE ALSO
	488
	489	SQL::Translator, http://www.mysql.com/.
9398955f	490
2d6979da	491	=head1 AUTHORS
9398955f	492
758ab1cd	493	darren chamberlain E<lt>darren@cpan.orgE<gt>,
c855a748	494	Ken Y. Clark E<lt>kclark@cpan.orgE<gt>.
	495
	496	=cut