1 package SQL::Translator::Producer::MySQL;
3 # -------------------------------------------------------------------
4 # $Id: MySQL.pm,v 1.45 2005-06-27 20:41:13 duality72 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 table.mysql_charset table.mysql_collate
74 Set the tables default charater set and collation order.
76 =item field.mysql_charset field.mysql_collate
78 Set the fields charater set and collation order.
85 use vars qw[ $VERSION $DEBUG ];
86 $VERSION = sprintf "%d.%02d", q$Revision: 1.45 $ =~ /(\d+)\.(\d+)/;
87 $DEBUG = 0 unless defined $DEBUG;
90 use SQL::Translator::Schema::Constants;
91 use SQL::Translator::Utils qw(debug header_comment);
94 # Use only lowercase for the keys (e.g. "long" and not "LONG")
100 varchar2 => 'varchar',
116 'long integer' => 'integer',
118 'datetime' => 'datetime',
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;
127 my $show_warnings = $translator->show_warnings || 0;
129 debug("PKG: Beginning production\n");
132 $create .= header_comment unless ($no_comments);
133 # \todo Don't set if MySQL 3.x is set on command line
134 $create .= "SET foreign_key_checks=0;\n\n";
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.
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');
151 for my $table ( $schema->get_tables ) {
152 my $table_name = $table->name;
153 debug("PKG: Looking at table '$table_name'\n");
156 # Header. Should this look like what mysqldump produces?
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";
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;
172 my $data_type = $field->data_type;
173 my @size = $field->size;
174 my %extra = $field->extra;
175 my $list = $extra{'list'} || [];
176 # \todo deal with embedded quotes
177 my $commalist = join( ', ', map { qq['$_'] } @$list );
178 my $charset = $extra{'mysql_charset'};
179 my $collate = $extra{'mysql_collate'};
182 # Oracle "number" type -- figure best MySQL type
184 if ( lc $data_type eq 'number' ) {
186 if ( scalar @size > 1 ) {
187 $data_type = 'double';
189 elsif ( $size[0] && $size[0] >= 12 ) {
190 $data_type = 'bigint';
192 elsif ( $size[0] && $size[0] <= 1 ) {
193 $data_type = 'tinyint';
200 # Convert a large Oracle varchar to "text"
202 elsif ( $data_type =~ /char/i && $size[0] > 255 ) {
206 elsif ( $data_type =~ /char/i && ! $size[0] ) {
209 elsif ( $data_type =~ /boolean/i ) {
211 $commalist = "'0','1'";
213 elsif ( exists $translate{ lc $data_type } ) {
214 $data_type = $translate{ lc $data_type };
217 @size = () if $data_type =~ /(text|blob)/i;
219 if ( $data_type =~ /(double|float)/ && scalar @size == 1 ) {
223 $field_def .= " $data_type";
225 if ( lc $data_type eq 'enum' ) {
226 $field_def .= '(' . $commalist . ')';
228 elsif ( defined $size[0] && $size[0] > 0 ) {
229 $field_def .= '(' . join( ', ', @size ) . ')';
233 $field_def .= " CHARACTER SET $charset" if $charset;
234 $field_def .= " COLLATE $collate" if $collate;
237 for my $qual ( qw[ binary unsigned zerofill ] ) {
238 my $val = $extra{ $qual } || $extra{ uc $qual } or next;
239 $field_def .= " $qual";
241 for my $qual ( 'character set', 'collate', 'on update' ) {
242 my $val = $extra{ $qual } || $extra{ uc $qual } or next;
243 $field_def .= " $qual $val";
247 $field_def .= ' NOT NULL' unless $field->is_nullable;
249 # Default? XXX Need better quoting!
250 my $default = $field->default_value;
251 if ( defined $default ) {
252 if ( uc $default eq 'NULL') {
253 $field_def .= ' DEFAULT NULL';
255 $field_def .= " DEFAULT '$default'";
259 if ( my $comments = $field->comments ) {
260 $field_def .= qq[ comment '$comments'];
264 $field_def .= " auto_increment" if $field->is_auto_increment;
265 push @field_defs, $field_def;
273 for my $index ( $table->get_indices ) {
274 push @index_defs, join( ' ',
275 lc $index->type eq 'normal' ? 'INDEX' : $index->type,
277 '(' . join( ', ', $index->fields ) . ')'
279 $indexed_fields{ $_ } = 1 for $index->fields;
283 # Constraints -- need to handle more than just FK. -ky
286 my @constraints = $table->get_constraints;
287 for my $c ( @constraints ) {
288 my @fields = $c->fields or next;
290 if ( $c->type eq PRIMARY_KEY ) {
291 push @constraint_defs,
292 'PRIMARY KEY (' . join(', ', @fields). ')';
294 elsif ( $c->type eq UNIQUE ) {
295 push @constraint_defs,
297 (defined $c->name ? $c->name.' ' : '').
298 '(' . join(', ', @fields). ')';
300 elsif ( $c->type eq FOREIGN_KEY ) {
302 # Make sure FK field is indexed or MySQL complains.
304 unless ( $indexed_fields{ $fields[0] } ) {
305 push @index_defs, "INDEX ($fields[0])";
306 $indexed_fields{ $fields[0] } = 1;
310 map { $_ || () } 'CONSTRAINT', $c->name, 'FOREIGN KEY'
313 $def .= ' (' . join( ', ', @fields ) . ')';
315 $def .= ' REFERENCES ' . $c->reference_table;
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;
324 warn "Can't find reference table '$rtable_name' " .
325 "in schema\n" if $show_warnings;
330 $def .= ' (' . join( ', ', @rfields ) . ')';
333 warn "FK constraint on " . $table->name . '.' .
334 join('', @fields) . " has no reference fields\n"
338 if ( $c->match_type ) {
340 ( $c->match_type =~ /full/i ) ? 'FULL' : 'PARTIAL';
343 if ( $c->on_delete ) {
344 $def .= ' ON DELETE '.join( ' ', $c->on_delete );
347 if ( $c->on_update ) {
348 $def .= ' ON UPDATE '.join( ' ', $c->on_update );
351 push @constraint_defs, $def;
355 $create .= join(",\n", map { " $_" }
356 @field_defs, @index_defs, @constraint_defs
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";
370 my $mysql_table_type = $table->extra('mysql_table_type');
371 $create .= " Type=$mysql_table_type"
372 if $mysql_table_type && !$table_type_defined;
373 my $charset = $table->extra('mysql_charset');
374 my $collate = $table->extra('mysql_collate');
375 my $comments = $table->comments;
377 $create .= " DEFAULT CHARACTER SET $charset" if $charset;
378 $create .= " COLLATE $collate" if $collate;
379 $create .= qq[ comment='$comments'] if $comments;
388 # -------------------------------------------------------------------
394 SQL::Translator, http://www.mysql.com/.
398 darren chamberlain E<lt>darren@cpan.orgE<gt>,
399 Ken Y. Clark E<lt>kclark@cpan.orgE<gt>.