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

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

# -------------------------------------------------------------------
# $Id: MySQL.pm,v 1.46 2005-12-16 05:49:37 grommit 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 vars qw[ $VERSION $DEBUG ];
$VERSION = sprintf "%d.%02d", q$Revision: 1.46 $ =~ /(\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 ) {
        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
    #
    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_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
        #
        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 $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;
            push @field_defs, $field_def;
		}

        #
        # 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;
        my @constraints = $table->get_constraints;
        for my $c ( @constraints ) {
            my @fields = $c->fields or next;

            if ( $c->type eq PRIMARY_KEY ) {
                push @constraint_defs,
                    'PRIMARY KEY (' . join(', ', @fields). ')';
            }
            elsif ( $c->type eq UNIQUE ) {
                push @constraint_defs,
                    'UNIQUE '.
                    (defined $c->name ? $c->name.' ' : '').
                    '(' . join(', ', @fields). ')';
            }
            elsif ( $c->type eq FOREIGN_KEY ) {
                #
                # 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 { $_ || () } 'CONSTRAINT', $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 = $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 ' . 
                        ( $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 );
                }

                push @constraint_defs, $def;
            }
        }

        $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;
}

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	# -------------------------------------------------------------------
4257646e	4	# $Id: MySQL.pm,v 1.46 2005-12-16 05:49:37 grommit 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;
d529894e	94	use vars qw[ $VERSION $DEBUG ];
4257646e	95	$VERSION = sprintf "%d.%02d", q$Revision: 1.46 $ =~ /(\d+)\.(\d+)/;
5636ed00	96	$DEBUG = 0 unless defined $DEBUG;
9398955f	97
9398955f	98	use Data::Dumper;
1c14e9f1	99	use SQL::Translator::Schema::Constants;
5ee19df8	100	use SQL::Translator::Utils qw(debug header_comment);
9398955f	101
d2344c83	102	#
	103	# Use only lowercase for the keys (e.g. "long" and not "LONG")
	104	#
2620fc1c	105	my %translate = (
	106	#
	107	# Oracle types
	108	#
	109	varchar2 => 'varchar',
	110	long => 'text',
d2344c83	111	clob => 'longtext',
2620fc1c	112
	113	#
	114	# Sybase types
	115	#
	116	int => 'integer',
	117	money => 'float',
	118	real => 'double',
	119	comment => 'text',
	120	bit => 'tinyint',
87779799	121
	122	#
	123	# Access types
	124	#
	125	'long integer' => 'integer',
	126	'text' => 'text',
	127	'datetime' => 'datetime',
2620fc1c	128	);
2620fc1c	129
9398955f	130	sub produce {
a1d94525	131	my $translator = shift;
	132	local $DEBUG = $translator->debug;
	133	my $no_comments = $translator->no_comments;
	134	my $add_drop_table = $translator->add_drop_table;
	135	my $schema = $translator->schema;
2bc23e82	136	my $show_warnings = $translator->show_warnings \|\| 0;
d529894e	137
1a24938d	138	debug("PKG: Beginning production\n");
d529894e	139
d529894e	140	my $create;
5ee19df8	141	$create .= header_comment unless ($no_comments);
0823773d	142	# \todo Don't set if MySQL 3.x is set on command line
da147d03	143	$create .= "SET foreign_key_checks=0;\n\n";
9398955f	144
5a0c7b43	145	#
	146	# Work out which tables need to be InnoDB to support foreign key
	147	# constraints. We do this first as we need InnoDB at both ends.
	148	#
	149	foreach ( map { $_->get_constraints } $schema->get_tables ) {
	150	foreach my $meth (qw/table reference_table/) {
	151	my $table = $schema->get_table($_->$meth) \|\| next;
	152	next if $table->extra('mysql_table_type');
	153	$table->extra( 'mysql_table_type' => 'InnoDB');
	154	}
	155	}
	156
	157	#
	158	# Generate sql
	159	#
1c14e9f1	160	for my $table ( $schema->get_tables ) {
	161	my $table_name = $table->name;
	162	debug("PKG: Looking at table '$table_name'\n");
9398955f	163
d529894e	164	#
9398955f	165	# Header. Should this look like what mysqldump produces?
d529894e	166	#
1c14e9f1	167	$create .= "--\n-- Table: $table_name\n--\n" unless $no_comments;
	168	$create .= qq[DROP TABLE IF EXISTS $table_name;\n] if $add_drop_table;
	169	$create .= "CREATE TABLE $table_name (\n";
9398955f	170
d529894e	171	#
9398955f	172	# Fields
d529894e	173	#
1c14e9f1	174	my @field_defs;
	175	for my $field ( $table->get_fields ) {
	176	my $field_name = $field->name;
	177	debug("PKG: Looking at field '$field_name'\n");
	178	my $field_def = $field_name;
9398955f	179
9398955f	180	# data type and size
1c14e9f1	181	my $data_type = $field->data_type;
1c14e9f1	182	my @size = $field->size;
35ed60b5	183	my %extra = $field->extra;
35ed60b5	184	my $list = $extra{'list'} \|\| [];
0823773d	185	# \todo deal with embedded quotes
4524cf01	186	my $commalist = join( ', ', map { qq['$_'] } @$list );
1ded8513	187	my $charset = $extra{'mysql_charset'};
1ded8513	188	my $collate = $extra{'mysql_collate'};
2620fc1c	189
1c14e9f1	190	#
	191	# Oracle "number" type -- figure best MySQL type
	192	#
	193	if ( lc $data_type eq 'number' ) {
aa0a4c42	194	# not an integer
	195	if ( scalar @size > 1 ) {
	196	$data_type = 'double';
	197	}
be42bce8	198	elsif ( $size[0] && $size[0] >= 12 ) {
aa0a4c42	199	$data_type = 'bigint';
aa0a4c42	200	}
be42bce8	201	elsif ( $size[0] && $size[0] <= 1 ) {
aa0a4c42	202	$data_type = 'tinyint';
	203	}
	204	else {
	205	$data_type = 'int';
2620fc1c	206	}
2620fc1c	207	}
49758c5d	208	#
	209	# Convert a large Oracle varchar to "text"
	210	#
2b695517	211	elsif ( $data_type =~ /char/i && $size[0] > 255 ) {
49758c5d	212	$data_type = 'text';
	213	@size = ();
	214	}
4167a803	215	elsif ( $data_type =~ /char/i && ! $size[0] ) {
	216	@size = (255);
	217	}
2b695517	218	elsif ( $data_type =~ /boolean/i ) {
	219	$data_type = 'enum';
	220	$commalist = "'0','1'";
	221	}
d2344c83	222	elsif ( exists $translate{ lc $data_type } ) {
d2344c83	223	$data_type = $translate{ lc $data_type };
2620fc1c	224	}
2620fc1c	225
472b787e	226	@size = () if $data_type =~ /(text\|blob)/i;
6d3f6379	227
2b695517	228	if ( $data_type =~ /(double\|float)/ && scalar @size == 1 ) {
	229	push @size, '0';
	230	}
	231
1c14e9f1	232	$field_def .= " $data_type";
1ded8513	233
35ed60b5	234	if ( lc $data_type eq 'enum' ) {
35ed60b5	235	$field_def .= '(' . $commalist . ')';
6d3f6379	236	}
6d3f6379	237	elsif ( defined $size[0] && $size[0] > 0 ) {
1c14e9f1	238	$field_def .= '(' . join( ', ', @size ) . ')';
1c14e9f1	239	}
d529894e	240
1ded8513	241	# char sets
	242	$field_def .= " CHARACTER SET $charset" if $charset;
	243	$field_def .= " COLLATE $collate" if $collate;
	244
d529894e	245	# MySQL qualifiers
d529894e	246	for my $qual ( qw[ binary unsigned zerofill ] ) {
a16247ae	247	my $val = $extra{ $qual } \|\| $extra{ uc $qual } or next;
0823773d	248	$field_def .= " $qual";
d529894e	249	}
a16247ae	250	for my $qual ( 'character set', 'collate', 'on update' ) {
	251	my $val = $extra{ $qual } \|\| $extra{ uc $qual } or next;
	252	$field_def .= " $qual $val";
	253	}
9398955f	254
9398955f	255	# Null?
1c14e9f1	256	$field_def .= ' NOT NULL' unless $field->is_nullable;
9398955f	257
9398955f	258	# Default? XXX Need better quoting!
1c14e9f1	259	my $default = $field->default_value;
d529894e	260	if ( defined $default ) {
d529894e	261	if ( uc $default eq 'NULL') {
1c14e9f1	262	$field_def .= ' DEFAULT NULL';
9398955f	263	} else {
1c14e9f1	264	$field_def .= " DEFAULT '$default'";
9398955f	265	}
	266	}
	267
f8b6e804	268	if ( my $comments = $field->comments ) {
	269	$field_def .= qq[ comment '$comments'];
	270	}
	271
9398955f	272	# auto_increment?
1c14e9f1	273	$field_def .= " auto_increment" if $field->is_auto_increment;
1c14e9f1	274	push @field_defs, $field_def;
56120730	275	}
9398955f	276
d529894e	277	#
	278	# Indices
	279	#
1c14e9f1	280	my @index_defs;
bc60004d	281	my %indexed_fields;
1c14e9f1	282	for my $index ( $table->get_indices ) {
1c14e9f1	283	push @index_defs, join( ' ',
35ed60b5	284	lc $index->type eq 'normal' ? 'INDEX' : $index->type,
1c14e9f1	285	$index->name,
	286	'(' . join( ', ', $index->fields ) . ')'
	287	);
bc60004d	288	$indexed_fields{ $_ } = 1 for $index->fields;
d529894e	289	}
	290
	291	#
5e56da9a	292	# Constraints -- need to handle more than just FK. -ky
5e56da9a	293	#
2620fc1c	294	my @constraint_defs;
2bc23e82	295	my @constraints = $table->get_constraints;
2bc23e82	296	for my $c ( @constraints ) {
1c14e9f1	297	my @fields = $c->fields or next;
5e56da9a	298
1c14e9f1	299	if ( $c->type eq PRIMARY_KEY ) {
	300	push @constraint_defs,
	301	'PRIMARY KEY (' . join(', ', @fields). ')';
	302	}
	303	elsif ( $c->type eq UNIQUE ) {
	304	push @constraint_defs,
a16247ae	305	'UNIQUE '.
	306	(defined $c->name ? $c->name.' ' : '').
	307	'(' . join(', ', @fields). ')';
1c14e9f1	308	}
1c14e9f1	309	elsif ( $c->type eq FOREIGN_KEY ) {
bc60004d	310	#
	311	# Make sure FK field is indexed or MySQL complains.
	312	#
	313	unless ( $indexed_fields{ $fields[0] } ) {
	314	push @index_defs, "INDEX ($fields[0])";
2bc23e82	315	$indexed_fields{ $fields[0] } = 1;
bc60004d	316	}
bc60004d	317
1c14e9f1	318	my $def = join(' ',
a16247ae	319	map { $_ \|\| () } 'CONSTRAINT', $c->name, 'FOREIGN KEY'
1c14e9f1	320	);
	321
	322	$def .= ' (' . join( ', ', @fields ) . ')';
	323
	324	$def .= ' REFERENCES ' . $c->reference_table;
	325
2bc23e82	326	my @rfields = map { $_ \|\| () } $c->reference_fields;
	327	unless ( @rfields ) {
	328	my $rtable_name = $c->reference_table;
	329	if ( my $ref_table = $schema->get_table( $rtable_name ) ) {
	330	push @rfields, $ref_table->primary_key;
	331	}
	332	else {
	333	warn "Can't find reference table '$rtable_name' " .
	334	"in schema\n" if $show_warnings;
	335	}
	336	}
	337
	338	if ( @rfields ) {
1c14e9f1	339	$def .= ' (' . join( ', ', @rfields ) . ')';
5e56da9a	340	}
2bc23e82	341	else {
	342	warn "FK constraint on " . $table->name . '.' .
	343	join('', @fields) . " has no reference fields\n"
	344	if $show_warnings;
	345	}
5e56da9a	346
1c14e9f1	347	if ( $c->match_type ) {
5e56da9a	348	$def .= ' MATCH ' .
1c14e9f1	349	( $c->match_type =~ /full/i ) ? 'FULL' : 'PARTIAL';
5e56da9a	350	}
5e56da9a	351
1c14e9f1	352	if ( $c->on_delete ) {
1c14e9f1	353	$def .= ' ON DELETE '.join( ' ', $c->on_delete );
586809da	354	}
586809da	355
1c14e9f1	356	if ( $c->on_update ) {
1c14e9f1	357	$def .= ' ON UPDATE '.join( ' ', $c->on_update );
586809da	358	}
5e56da9a	359
2620fc1c	360	push @constraint_defs, $def;
5e56da9a	361	}
	362	}
	363
1c14e9f1	364	$create .= join(",\n", map { " $_" }
	365	@field_defs, @index_defs, @constraint_defs
	366	);
5e56da9a	367
5e56da9a	368	#
9398955f	369	# Footer
d529894e	370	#
c45c546e	371	$create .= "\n)";
a16247ae	372	my $table_type_defined = 0;
	373	for my $t1_option_ref ( $table->options ) {
	374	my($key, $value) = %{$t1_option_ref};
	375	$table_type_defined = 1
	376	if uc $key eq 'ENGINE' or uc $key eq 'TYPE';
	377	$create .= " $key=$value";
	378	}
5a0c7b43	379	my $mysql_table_type = $table->extra('mysql_table_type');
4257646e	380	#my $charset = $table->extra('mysql_character_set');
	381	#my $collate = $table->extra('mysql_collate');
	382	#$create .= " Type=$mysql_table_type" if $mysql_table_type;
	383	#$create .= " DEFAULT CHARACTER SET $charset" if $charset;
	384	#$create .= " COLLATE $collate" if $collate;
a16247ae	385	$create .= " Type=$mysql_table_type"
a16247ae	386	if $mysql_table_type && !$table_type_defined;
1ded8513	387	my $charset = $table->extra('mysql_charset');
1ded8513	388	my $collate = $table->extra('mysql_collate');
f8b6e804	389	my $comments = $table->comments;
f8b6e804	390
1ded8513	391	$create .= " DEFAULT CHARACTER SET $charset" if $charset;
1ded8513	392	$create .= " COLLATE $collate" if $collate;
f8b6e804	393	$create .= qq[ comment='$comments'] if $comments;
9398955f	394	$create .= ";\n\n";
	395	}
	396
9398955f	397	return $create;
	398	}
	399
9398955f	400	1;
9398955f	401
c855a748	402	# -------------------------------------------------------------------
9398955f	403
c855a748	404	=pod
	405
	406	=head1 SEE ALSO
	407
	408	SQL::Translator, http://www.mysql.com/.
9398955f	409
2d6979da	410	=head1 AUTHORS
9398955f	411
758ab1cd	412	darren chamberlain E<lt>darren@cpan.orgE<gt>,
c855a748	413	Ken Y. Clark E<lt>kclark@cpan.orgE<gt>.
	414
	415	=cut