1 package SQL::Translator::Producer::MySQL;
3 # -------------------------------------------------------------------
4 # $Id: MySQL.pm,v 1.42 2005-01-13 11:50:23 grommit Exp $
5 # -------------------------------------------------------------------
6 # Copyright (C) 2002-4 SQLFairy Authors
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.
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.
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
21 # -------------------------------------------------------------------
25 SQL::Translator::Producer::MySQL - MySQL-specific producer for SQL::Translator
29 Use via SQL::Translator:
33 my $t = SQL::Translator->new( parser => '...', producer => 'MySQL', '...' );
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
45 Normally the tables will be created without any explicit table type given and
46 so will use the MySQL default.
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.
52 =head2 Extra attributes.
54 The producer recognises the following extra attributes on the Schema objects.
60 Set the list of allowed values for Enum fields.
62 =item field.binary field.unsigned field.zerofill
64 Set the MySQL field options of the same name.
66 =item mysql_table_type
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">.
77 use vars qw[ $VERSION $DEBUG ];
78 $VERSION = sprintf "%d.%02d", q$Revision: 1.42 $ =~ /(\d+)\.(\d+)/;
79 $DEBUG = 0 unless defined $DEBUG;
82 use SQL::Translator::Schema::Constants;
83 use SQL::Translator::Utils qw(debug header_comment);
86 # Use only lowercase for the keys (e.g. "long" and not "LONG")
92 varchar2 => 'varchar',
108 'long integer' => 'integer',
110 'datetime' => 'datetime',
114 my $translator = shift;
115 local $DEBUG = $translator->debug;
116 my $no_comments = $translator->no_comments;
117 my $add_drop_table = $translator->add_drop_table;
118 my $schema = $translator->schema;
119 my $show_warnings = $translator->show_warnings || 0;
121 debug("PKG: Beginning production\n");
124 $create .= header_comment unless ($no_comments);
125 # \todo Don't set if MySQL 3.x is set on command line
126 $create .= "SET foreign_key_checks=0;\n\n";
129 # Work out which tables need to be InnoDB to support foreign key
130 # constraints. We do this first as we need InnoDB at both ends.
132 foreach ( map { $_->get_constraints } $schema->get_tables ) {
133 foreach my $meth (qw/table reference_table/) {
134 my $table = $schema->get_table($_->$meth) || next;
135 next if $table->extra('mysql_table_type');
136 $table->extra( 'mysql_table_type' => 'InnoDB');
143 for my $table ( $schema->get_tables ) {
144 my $table_name = $table->name;
145 debug("PKG: Looking at table '$table_name'\n");
148 # Header. Should this look like what mysqldump produces?
150 $create .= "--\n-- Table: $table_name\n--\n" unless $no_comments;
151 $create .= qq[DROP TABLE IF EXISTS $table_name;\n] if $add_drop_table;
152 $create .= "CREATE TABLE $table_name (\n";
158 for my $field ( $table->get_fields ) {
159 my $field_name = $field->name;
160 debug("PKG: Looking at field '$field_name'\n");
161 my $field_def = $field_name;
164 my $data_type = $field->data_type;
165 my @size = $field->size;
166 my %extra = $field->extra;
167 my $list = $extra{'list'} || [];
168 # \todo deal with embedded quotes
169 my $commalist = join( ', ', map { qq['$_'] } @$list );
172 # Oracle "number" type -- figure best MySQL type
174 if ( lc $data_type eq 'number' ) {
176 if ( scalar @size > 1 ) {
177 $data_type = 'double';
179 elsif ( $size[0] && $size[0] >= 12 ) {
180 $data_type = 'bigint';
182 elsif ( $size[0] && $size[0] <= 1 ) {
183 $data_type = 'tinyint';
190 # Convert a large Oracle varchar to "text"
192 elsif ( $data_type =~ /char/i && $size[0] > 255 ) {
196 elsif ( $data_type =~ /char/i && ! $size[0] ) {
199 elsif ( $data_type =~ /boolean/i ) {
201 $commalist = "'0','1'";
203 elsif ( exists $translate{ lc $data_type } ) {
204 $data_type = $translate{ lc $data_type };
207 @size = () if $data_type =~ /(text|blob)/i;
209 if ( $data_type =~ /(double|float)/ && scalar @size == 1 ) {
213 $field_def .= " $data_type";
215 if ( lc $data_type eq 'enum' ) {
216 $field_def .= '(' . $commalist . ')';
218 elsif ( defined $size[0] && $size[0] > 0 ) {
219 $field_def .= '(' . join( ', ', @size ) . ')';
223 for my $qual ( qw[ binary unsigned zerofill ] ) {
224 my $val = $extra{ $qual || uc $qual } or next;
225 $field_def .= " $qual";
229 $field_def .= ' NOT NULL' unless $field->is_nullable;
231 # Default? XXX Need better quoting!
232 my $default = $field->default_value;
233 if ( defined $default ) {
234 if ( uc $default eq 'NULL') {
235 $field_def .= ' DEFAULT NULL';
237 $field_def .= " DEFAULT '$default'";
242 $field_def .= " auto_increment" if $field->is_auto_increment;
243 push @field_defs, $field_def;
251 for my $index ( $table->get_indices ) {
252 push @index_defs, join( ' ',
253 lc $index->type eq 'normal' ? 'INDEX' : $index->type,
255 '(' . join( ', ', $index->fields ) . ')'
257 $indexed_fields{ $_ } = 1 for $index->fields;
261 # Constraints -- need to handle more than just FK. -ky
264 my @constraints = $table->get_constraints;
265 for my $c ( @constraints ) {
266 my @fields = $c->fields or next;
268 if ( $c->type eq PRIMARY_KEY ) {
269 push @constraint_defs,
270 'PRIMARY KEY (' . join(', ', @fields). ')';
272 elsif ( $c->type eq UNIQUE ) {
273 push @constraint_defs,
274 'UNIQUE (' . join(', ', @fields). ')';
276 elsif ( $c->type eq FOREIGN_KEY ) {
278 # Make sure FK field is indexed or MySQL complains.
280 unless ( $indexed_fields{ $fields[0] } ) {
281 push @index_defs, "INDEX ($fields[0])";
282 $indexed_fields{ $fields[0] } = 1;
286 map { $_ || () } 'FOREIGN KEY', $c->name
289 $def .= ' (' . join( ', ', @fields ) . ')';
291 $def .= ' REFERENCES ' . $c->reference_table;
293 my @rfields = map { $_ || () } $c->reference_fields;
294 unless ( @rfields ) {
295 my $rtable_name = $c->reference_table;
296 if ( my $ref_table = $schema->get_table( $rtable_name ) ) {
297 push @rfields, $ref_table->primary_key;
300 warn "Can't find reference table '$rtable_name' " .
301 "in schema\n" if $show_warnings;
306 $def .= ' (' . join( ', ', @rfields ) . ')';
309 warn "FK constraint on " . $table->name . '.' .
310 join('', @fields) . " has no reference fields\n"
314 if ( $c->match_type ) {
316 ( $c->match_type =~ /full/i ) ? 'FULL' : 'PARTIAL';
319 if ( $c->on_delete ) {
320 $def .= ' ON DELETE '.join( ' ', $c->on_delete );
323 if ( $c->on_update ) {
324 $def .= ' ON UPDATE '.join( ' ', $c->on_update );
327 push @constraint_defs, $def;
331 $create .= join(",\n", map { " $_" }
332 @field_defs, @index_defs, @constraint_defs
339 my $mysql_table_type = $table->extra('mysql_table_type');
340 $create .= " Type=$mysql_table_type" if $mysql_table_type;
349 # -------------------------------------------------------------------
355 SQL::Translator, http://www.mysql.com/.
359 darren chamberlain E<lt>darren@cpan.orgE<gt>,
360 Ken Y. Clark E<lt>kclark@cpan.orgE<gt>.