1 package SQL::Translator::Producer::MySQL;
3 # -------------------------------------------------------------------
4 # $Id: MySQL.pm,v 1.54 2007-11-10 03:36:43 mwz444 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.
95 use vars qw[ $VERSION $DEBUG %used_names ];
96 $VERSION = sprintf "%d.%02d", q$Revision: 1.54 $ =~ /(\d+)\.(\d+)/;
97 $DEBUG = 0 unless defined $DEBUG;
100 use SQL::Translator::Schema::Constants;
101 use SQL::Translator::Utils qw(debug header_comment);
104 # Use only lowercase for the keys (e.g. "long" and not "LONG")
110 varchar2 => 'varchar',
126 'long integer' => 'integer',
128 'datetime' => 'datetime',
132 my $translator = shift;
133 local $DEBUG = $translator->debug;
135 my $no_comments = $translator->no_comments;
136 my $add_drop_table = $translator->add_drop_table;
137 my $schema = $translator->schema;
138 my $show_warnings = $translator->show_warnings || 0;
140 my ($qt, $qf) = ('','');
141 $qt = '`' if $translator->quote_table_names;
142 $qf = '`' if $translator->quote_field_names;
144 debug("PKG: Beginning production\n");
147 $create .= header_comment unless ($no_comments);
148 # \todo Don't set if MySQL 3.x is set on command line
149 $create .= "SET foreign_key_checks=0;\n\n";
152 # Work out which tables need to be InnoDB to support foreign key
153 # constraints. We do this first as we need InnoDB at both ends.
155 foreach ( map { $_->get_constraints } $schema->get_tables ) {
156 next unless $_->type eq FOREIGN_KEY;
157 foreach my $meth (qw/table reference_table/) {
158 my $table = $schema->get_table($_->$meth) || next;
159 next if $table->extra('mysql_table_type');
160 $table->extra( 'mysql_table_type' => 'InnoDB');
168 for my $table ( $schema->get_tables ) {
169 # print $table->name, "\n";
170 push @table_defs, create_table($table,
171 { add_drop_table => $add_drop_table,
172 show_warnings => $show_warnings,
173 no_comments => $no_comments,
174 quote_table_names => $qt,
175 quote_field_names => $qf
179 # print "@table_defs\n";
180 push @table_defs, "SET foreign_key_checks=1;\n\n";
182 return wantarray ? ($create, @table_defs) : $create . join ('', @table_defs);
187 my ($table, $options) = @_;
189 my $qt = $options->{quote_table_names} || '';
190 my $qf = $options->{quote_field_names} || '';
192 my $table_name = $table->name;
193 debug("PKG: Looking at table '$table_name'\n");
196 # Header. Should this look like what mysqldump produces?
200 $create .= "--\n-- Table: $qt$table_name$qt\n--\n" unless $options->{no_comments};
201 $drop = qq[DROP TABLE IF EXISTS $qt$table_name$qt;\n] if $options->{add_drop_table};
202 $create .= "CREATE TABLE $qt$table_name$qt (\n";
208 for my $field ( $table->get_fields ) {
209 push @field_defs, create_field($field, $options);
217 for my $index ( $table->get_indices ) {
218 push @index_defs, create_index($index, $options);
219 $indexed_fields{ $_ } = 1 for $index->fields;
223 # Constraints -- need to handle more than just FK. -ky
226 my @constraints = $table->get_constraints;
227 for my $c ( @constraints ) {
228 my $constr = create_constraint($c, $options);
229 push @constraint_defs, $constr if($constr);
231 unless ( $indexed_fields{ ($c->fields())[0] } || $c->type ne FOREIGN_KEY ) {
232 push @index_defs, "INDEX ($qf" . ($c->fields())[0] . "$qf)";
233 $indexed_fields{ ($c->fields())[0] } = 1;
237 $create .= join(",\n", map { " $_" }
238 @field_defs, @index_defs, @constraint_defs
245 $create .= generate_table_options($table) || '';
248 return $drop ? ($drop,$create) : $create;
251 sub generate_table_options
256 my $table_type_defined = 0;
257 for my $t1_option_ref ( $table->options ) {
258 my($key, $value) = %{$t1_option_ref};
259 $table_type_defined = 1
260 if uc $key eq 'ENGINE' or uc $key eq 'TYPE';
261 $create .= " $key=$value";
263 my $mysql_table_type = $table->extra('mysql_table_type');
264 #my $charset = $table->extra('mysql_character_set');
265 #my $collate = $table->extra('mysql_collate');
266 #$create .= " Type=$mysql_table_type" if $mysql_table_type;
267 #$create .= " DEFAULT CHARACTER SET $charset" if $charset;
268 #$create .= " COLLATE $collate" if $collate;
269 $create .= " Type=$mysql_table_type"
270 if $mysql_table_type && !$table_type_defined;
271 my $charset = $table->extra('mysql_charset');
272 my $collate = $table->extra('mysql_collate');
273 my $comments = $table->comments;
275 $create .= " DEFAULT CHARACTER SET $charset" if $charset;
276 $create .= " COLLATE $collate" if $collate;
277 $create .= qq[ comment='$comments'] if $comments;
283 my ($field, $options) = @_;
285 my $qf = $options->{quote_field_names} ||= '';
287 my $field_name = $field->name;
288 debug("PKG: Looking at field '$field_name'\n");
289 my $field_def = "$qf$field_name$qf";
292 my $data_type = $field->data_type;
293 my @size = $field->size;
294 my %extra = $field->extra;
295 my $list = $extra{'list'} || [];
296 # \todo deal with embedded quotes
297 my $commalist = join( ', ', map { qq['$_'] } @$list );
298 my $charset = $extra{'mysql_charset'};
299 my $collate = $extra{'mysql_collate'};
302 # Oracle "number" type -- figure best MySQL type
304 if ( lc $data_type eq 'number' ) {
306 if ( scalar @size > 1 ) {
307 $data_type = 'double';
309 elsif ( $size[0] && $size[0] >= 12 ) {
310 $data_type = 'bigint';
312 elsif ( $size[0] && $size[0] <= 1 ) {
313 $data_type = 'tinyint';
320 # Convert a large Oracle varchar to "text"
322 elsif ( $data_type =~ /char/i && $size[0] > 255 ) {
326 elsif ( $data_type =~ /char/i && ! $size[0] ) {
329 elsif ( $data_type =~ /boolean/i ) {
331 $commalist = "'0','1'";
333 elsif ( exists $translate{ lc $data_type } ) {
334 $data_type = $translate{ lc $data_type };
337 @size = () if $data_type =~ /(text|blob)/i;
339 if ( $data_type =~ /(double|float)/ && scalar @size == 1 ) {
343 $field_def .= " $data_type";
345 if ( lc $data_type eq 'enum' ) {
346 $field_def .= '(' . $commalist . ')';
348 elsif ( defined $size[0] && $size[0] > 0 ) {
349 $field_def .= '(' . join( ', ', @size ) . ')';
353 $field_def .= " CHARACTER SET $charset" if $charset;
354 $field_def .= " COLLATE $collate" if $collate;
357 for my $qual ( qw[ binary unsigned zerofill ] ) {
358 my $val = $extra{ $qual } || $extra{ uc $qual } or next;
359 $field_def .= " $qual";
361 for my $qual ( 'character set', 'collate', 'on update' ) {
362 my $val = $extra{ $qual } || $extra{ uc $qual } or next;
363 $field_def .= " $qual $val";
367 $field_def .= ' NOT NULL' unless $field->is_nullable;
369 # Default? XXX Need better quoting!
370 my $default = $field->default_value;
371 if ( defined $default ) {
372 if ( uc $default eq 'NULL') {
373 $field_def .= ' DEFAULT NULL';
375 $field_def .= " DEFAULT '$default'";
379 if ( my $comments = $field->comments ) {
380 $field_def .= qq[ comment '$comments'];
384 $field_def .= " auto_increment" if $field->is_auto_increment;
389 sub alter_create_index
391 my ($index, $options) = @_;
393 my $qt = $options->{quote_table_names} || '';
394 my $qf = $options->{quote_field_names} || '';
398 $qt.$index->table->name.$qt,
406 my ($index, $options) = @_;
408 my $qf = $options->{quote_field_names} || '';
411 lc $index->type eq 'normal' ? 'INDEX' : $index->type . ' INDEX',
413 '(' . $qf . join( "$qf, $qf", $index->fields ) . $qf . ')'
420 my ($index, $options) = @_;
422 my $qt = $options->{quote_table_names} || '';
423 my $qf = $options->{quote_field_names} || '';
427 $qt.$index->table->name.$qt,
430 $index->name || $index->fields
435 sub alter_drop_constraint
437 my ($c, $options) = @_;
439 my $qt = $options->{quote_table_names} || '';
440 my $qc = $options->{quote_constraint_names} || '';
442 my $out = sprintf('ALTER TABLE %s DROP %s %s',
445 $qc . $c->name . $qc );
450 sub alter_create_constraint
452 my ($index, $options) = @_;
454 my $qt = $options->{quote_table_names} || '';
457 $qt.$index->table->name.$qt,
459 create_constraint(@_) );
462 sub create_constraint
464 my ($c, $options) = @_;
466 my $qf = $options->{quote_field_names} || '';
467 my $qt = $options->{quote_table_names} || '';
468 my $leave_name = $options->{leave_name} || undef;
469 my $counter = ($options->{fk_name_counter} ||= {});
471 my @fields = $c->fields or next;
473 if ( $c->type eq PRIMARY_KEY ) {
474 return 'PRIMARY KEY (' . $qf . join("$qf, $qf", @fields). $qf . ')';
476 elsif ( $c->type eq UNIQUE ) {
479 (defined $c->name ? $qf.$c->name.$qf.' ' : '').
480 '(' . $qf . join("$qf, $qf", @fields). $qf . ')';
482 elsif ( $c->type eq FOREIGN_KEY ) {
484 # Make sure FK field is indexed or MySQL complains.
487 my $table = $c->table;
488 my $c_name = $c->name;
490 # Give the constraint a name if it doesn't have one, so it doens't feel
493 $c_name = $table->name . '_fk';
496 $counter->{$table} ||= {};
500 $qt . join('_', next_unused_name($c_name)
506 $def .= ' ('.$qf . join( "$qf, $qf", @fields ) . $qf . ')';
508 $def .= ' REFERENCES ' . $qt . $c->reference_table . $qt;
510 my @rfields = map { $_ || () } $c->reference_fields;
511 unless ( @rfields ) {
512 my $rtable_name = $c->reference_table;
513 if ( my $ref_table = $table->schema->get_table( $rtable_name ) ) {
514 push @rfields, $ref_table->primary_key;
517 warn "Can't find reference table '$rtable_name' " .
518 "in schema\n" if $options->{show_warnings};
523 $def .= ' (' . $qf . join( "$qf, $qf", @rfields ) . $qf . ')';
526 warn "FK constraint on " . $table->name . '.' .
527 join('', @fields) . " has no reference fields\n"
528 if $options->{show_warnings};
531 if ( $c->match_type ) {
533 ( $c->match_type =~ /full/i ) ? 'FULL' : 'PARTIAL';
536 if ( $c->on_delete ) {
537 $def .= ' ON DELETE '.join( ' ', $c->on_delete );
540 if ( $c->on_update ) {
541 $def .= ' ON UPDATE '.join( ' ', $c->on_update );
551 my ($to_table, $options) = @_;
553 my $qt = $options->{quote_table_name} || '';
555 my $table_options = generate_table_options($to_table) || '';
556 my $out = sprintf('ALTER TABLE %s%s',
557 $qt . $to_table->name . $qt,
565 my ($from_field, $to_field, $options) = @_;
567 my $qf = $options->{quote_field_name} || '';
568 my $qt = $options->{quote_table_name} || '';
570 my $out = sprintf('ALTER TABLE %s CHANGE COLUMN %s %s',
571 $qt . $to_field->table->name . $qt,
572 $qf . $to_field->name . $qf,
573 create_field($to_field, $options));
580 my ($new_field, $options) = @_;
582 my $qt = $options->{quote_table_name} || '';
584 my $out = sprintf('ALTER TABLE %s ADD COLUMN %s',
585 $qt . $new_field->table->name . $qt,
586 create_field($new_field, $options));
594 my ($old_field, $options) = @_;
596 my $qf = $options->{quote_field_name} || '';
597 my $qt = $options->{quote_table_name} || '';
599 my $out = sprintf('ALTER TABLE %s DROP COLUMN %s',
600 $qt . $old_field->table->name . $qt,
601 $qf . $old_field->name . $qf);
607 sub next_unused_name {
608 my $name = shift || '';
609 if ( !defined($used_names{$name}) ) {
610 $used_names{$name} = $name;
615 while ( defined($used_names{$name . '_' . $i}) ) {
619 $used_names{$name} = $name;
625 # -------------------------------------------------------------------
631 SQL::Translator, http://www.mysql.com/.
635 darren chamberlain E<lt>darren@cpan.orgE<gt>,
636 Ken Y. Clark E<lt>kclark@cpan.orgE<gt>.