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

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

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