1 package SQL::Translator::Producer::MySQL;
3 # -------------------------------------------------------------------
4 # $Id: MySQL.pm,v 1.46 2005-12-16 05:49:37 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 table.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">.
72 =item mysql_character_set
74 MySql-4.1+. Set the tables character set.
75 Run SHOW CHARACTER SET to see list.
79 MySql-4.1+. Set the tables colation order.
81 =item table.mysql_charset table.mysql_collate
83 Set the tables default charater set and collation order.
85 =item field.mysql_charset field.mysql_collate
87 Set the fields charater set and collation order.
94 use vars qw[ $VERSION $DEBUG ];
95 $VERSION = sprintf "%d.%02d", q$Revision: 1.46 $ =~ /(\d+)\.(\d+)/;
96 $DEBUG = 0 unless defined $DEBUG;
99 use SQL::Translator::Schema::Constants;
100 use SQL::Translator::Utils qw(debug header_comment);
103 # Use only lowercase for the keys (e.g. "long" and not "LONG")
109 varchar2 => 'varchar',
125 'long integer' => 'integer',
127 'datetime' => 'datetime',
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;
136 my $show_warnings = $translator->show_warnings || 0;
138 debug("PKG: Beginning production\n");
141 $create .= header_comment unless ($no_comments);
142 # \todo Don't set if MySQL 3.x is set on command line
143 $create .= "SET foreign_key_checks=0;\n\n";
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.
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');
160 for my $table ( $schema->get_tables ) {
161 my $table_name = $table->name;
162 debug("PKG: Looking at table '$table_name'\n");
165 # Header. Should this look like what mysqldump produces?
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";
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;
181 my $data_type = $field->data_type;
182 my @size = $field->size;
183 my %extra = $field->extra;
184 my $list = $extra{'list'} || [];
185 # \todo deal with embedded quotes
186 my $commalist = join( ', ', map { qq['$_'] } @$list );
187 my $charset = $extra{'mysql_charset'};
188 my $collate = $extra{'mysql_collate'};
191 # Oracle "number" type -- figure best MySQL type
193 if ( lc $data_type eq 'number' ) {
195 if ( scalar @size > 1 ) {
196 $data_type = 'double';
198 elsif ( $size[0] && $size[0] >= 12 ) {
199 $data_type = 'bigint';
201 elsif ( $size[0] && $size[0] <= 1 ) {
202 $data_type = 'tinyint';
209 # Convert a large Oracle varchar to "text"
211 elsif ( $data_type =~ /char/i && $size[0] > 255 ) {
215 elsif ( $data_type =~ /char/i && ! $size[0] ) {
218 elsif ( $data_type =~ /boolean/i ) {
220 $commalist = "'0','1'";
222 elsif ( exists $translate{ lc $data_type } ) {
223 $data_type = $translate{ lc $data_type };
226 @size = () if $data_type =~ /(text|blob)/i;
228 if ( $data_type =~ /(double|float)/ && scalar @size == 1 ) {
232 $field_def .= " $data_type";
234 if ( lc $data_type eq 'enum' ) {
235 $field_def .= '(' . $commalist . ')';
237 elsif ( defined $size[0] && $size[0] > 0 ) {
238 $field_def .= '(' . join( ', ', @size ) . ')';
242 $field_def .= " CHARACTER SET $charset" if $charset;
243 $field_def .= " COLLATE $collate" if $collate;
246 for my $qual ( qw[ binary unsigned zerofill ] ) {
247 my $val = $extra{ $qual } || $extra{ uc $qual } or next;
248 $field_def .= " $qual";
250 for my $qual ( 'character set', 'collate', 'on update' ) {
251 my $val = $extra{ $qual } || $extra{ uc $qual } or next;
252 $field_def .= " $qual $val";
256 $field_def .= ' NOT NULL' unless $field->is_nullable;
258 # Default? XXX Need better quoting!
259 my $default = $field->default_value;
260 if ( defined $default ) {
261 if ( uc $default eq 'NULL') {
262 $field_def .= ' DEFAULT NULL';
264 $field_def .= " DEFAULT '$default'";
268 if ( my $comments = $field->comments ) {
269 $field_def .= qq[ comment '$comments'];
273 $field_def .= " auto_increment" if $field->is_auto_increment;
274 push @field_defs, $field_def;
282 for my $index ( $table->get_indices ) {
283 push @index_defs, join( ' ',
284 lc $index->type eq 'normal' ? 'INDEX' : $index->type,
286 '(' . join( ', ', $index->fields ) . ')'
288 $indexed_fields{ $_ } = 1 for $index->fields;
292 # Constraints -- need to handle more than just FK. -ky
295 my @constraints = $table->get_constraints;
296 for my $c ( @constraints ) {
297 my @fields = $c->fields or next;
299 if ( $c->type eq PRIMARY_KEY ) {
300 push @constraint_defs,
301 'PRIMARY KEY (' . join(', ', @fields). ')';
303 elsif ( $c->type eq UNIQUE ) {
304 push @constraint_defs,
306 (defined $c->name ? $c->name.' ' : '').
307 '(' . join(', ', @fields). ')';
309 elsif ( $c->type eq FOREIGN_KEY ) {
311 # Make sure FK field is indexed or MySQL complains.
313 unless ( $indexed_fields{ $fields[0] } ) {
314 push @index_defs, "INDEX ($fields[0])";
315 $indexed_fields{ $fields[0] } = 1;
319 map { $_ || () } 'CONSTRAINT', $c->name, 'FOREIGN KEY'
322 $def .= ' (' . join( ', ', @fields ) . ')';
324 $def .= ' REFERENCES ' . $c->reference_table;
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;
333 warn "Can't find reference table '$rtable_name' " .
334 "in schema\n" if $show_warnings;
339 $def .= ' (' . join( ', ', @rfields ) . ')';
342 warn "FK constraint on " . $table->name . '.' .
343 join('', @fields) . " has no reference fields\n"
347 if ( $c->match_type ) {
349 ( $c->match_type =~ /full/i ) ? 'FULL' : 'PARTIAL';
352 if ( $c->on_delete ) {
353 $def .= ' ON DELETE '.join( ' ', $c->on_delete );
356 if ( $c->on_update ) {
357 $def .= ' ON UPDATE '.join( ' ', $c->on_update );
360 push @constraint_defs, $def;
364 $create .= join(",\n", map { " $_" }
365 @field_defs, @index_defs, @constraint_defs
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";
379 my $mysql_table_type = $table->extra('mysql_table_type');
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;
385 $create .= " Type=$mysql_table_type"
386 if $mysql_table_type && !$table_type_defined;
387 my $charset = $table->extra('mysql_charset');
388 my $collate = $table->extra('mysql_collate');
389 my $comments = $table->comments;
391 $create .= " DEFAULT CHARACTER SET $charset" if $charset;
392 $create .= " COLLATE $collate" if $collate;
393 $create .= qq[ comment='$comments'] if $comments;
402 # -------------------------------------------------------------------
408 SQL::Translator, http://www.mysql.com/.
412 darren chamberlain E<lt>darren@cpan.orgE<gt>,
413 Ken Y. Clark E<lt>kclark@cpan.orgE<gt>.