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 B<field.binary>, B<field.unsigned>, B<field.zerofill>
64 Set the MySQL field options of the same name.
66 =item B<field.renamed_from>, B<table.renamed_from>
68 Use when producing diffs to indicate that the current table/field has been
69 renamed from the old name as given in the attribute value.
71 =item B<table.mysql_table_type>
73 Set the type of the table e.g. 'InnoDB', 'MyISAM'. This will be
74 automatically set for tables involved in foreign key constraints if it is
75 not already set explicitly. See L<"Table Types">.
77 Please note that the C<ENGINE> option is the prefered method of specifying
78 the MySQL storage engine to use, but this method still works for backwards
81 =item B<table.mysql_charset>, B<table.mysql_collate>
83 Set the tables default charater set and collation order.
85 =item B<field.mysql_charset>, B<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;
99 # Maximum length for most identifiers is 64, according to:
100 # http://dev.mysql.com/doc/refman/4.1/en/identifiers.html
101 # http://dev.mysql.com/doc/refman/5.0/en/identifiers.html
102 my $DEFAULT_MAX_ID_LENGTH = 64;
105 use SQL::Translator::Schema::Constants;
106 use SQL::Translator::Utils qw(debug header_comment truncate_id_uniquely);
109 # Use only lowercase for the keys (e.g. "long" and not "LONG")
115 varchar2 => 'varchar',
131 'long integer' => 'integer',
133 'datetime' => 'datetime',
137 sub preprocess_schema {
140 # extra->{mysql_table_type} used to be the type. It belongs in options, so
141 # move it if we find it. Return Engine type if found in extra or options
142 # Similarly for mysql_charset and mysql_collate
143 my $extra_to_options = sub {
144 my ($table, $extra_name, $opt_name) = @_;
146 my $extra = $table->extra;
148 my $extra_type = delete $extra->{$extra_name};
150 # Now just to find if there is already an Engine or Type option...
151 # and lets normalize it to ENGINE since:
153 # The ENGINE table option specifies the storage engine for the table.
154 # TYPE is a synonym, but ENGINE is the preferred option name.
157 # We have to use the hash directly here since otherwise there is no way
159 my $options = ( $table->{options} ||= []);
161 # If multiple option names, normalize to the first one
163 OPT_NAME: for ( @$opt_name[1..$#$opt_name] ) {
164 for my $idx ( 0..$#{$options} ) {
165 my ($key, $value) = %{ $options->[$idx] };
168 $options->[$idx] = { $opt_name->[0] => $value };
173 $opt_name = $opt_name->[0];
178 # This assumes that there isn't both a Type and an Engine option.
180 for my $idx ( 0..$#{$options} ) {
181 my ($key, $value) = %{ $options->[$idx] };
183 next unless uc $key eq $opt_name;
185 # make sure case is right on option name
186 delete $options->[$idx]{$key};
187 return $options->[$idx]{$opt_name} = $value || $extra_type;
192 push @$options, { $opt_name => $extra_type };
198 # Names are only specific to a given schema
199 local %used_names = ();
202 # Work out which tables need to be InnoDB to support foreign key
203 # constraints. We do this first as we need InnoDB at both ends.
205 foreach my $table ( $schema->get_tables ) {
207 $extra_to_options->($table, 'mysql_table_type', ['ENGINE', 'TYPE'] );
208 $extra_to_options->($table, 'mysql_charset', 'CHARACTER SET' );
209 $extra_to_options->($table, 'mysql_collate', 'COLLATE' );
211 foreach my $c ( $table->get_constraints ) {
212 next unless $c->type eq FOREIGN_KEY;
214 # Normalize constraint names here.
215 my $c_name = $c->name;
216 # Give the constraint a name if it doesn't have one, so it doens't feel
218 $c_name = $table->name . '_fk' unless length $c_name;
220 $c->name( next_unused_name($c_name) );
222 for my $meth (qw/table reference_table/) {
223 my $table = $schema->get_table($c->$meth) || next;
224 # This normalizes the types to ENGINE and returns the value if its there
225 next if $extra_to_options->($table, 'mysql_table_type', ['ENGINE', 'TYPE']);
226 $table->options( { 'ENGINE' => 'InnoDB' } );
228 } # foreach constraints
230 my %map = ( mysql_collate => 'collate', mysql_charset => 'character set');
231 foreach my $f ( $table->get_fields ) {
232 my $extra = $f->extra;
234 $extra->{$map{$_}} = delete $extra->{$_} if exists $extra->{$_};
238 if ( !$size[0] && $f->data_type =~ /char$/ ) {
247 my $translator = shift;
248 local $DEBUG = $translator->debug;
250 my $no_comments = $translator->no_comments;
251 my $add_drop_table = $translator->add_drop_table;
252 my $schema = $translator->schema;
253 my $show_warnings = $translator->show_warnings || 0;
254 my $producer_args = $translator->producer_args;
255 my $mysql_version = $producer_args->{mysql_version} || 0;
256 my $max_id_length = $producer_args->{mysql_max_id_length} || $DEFAULT_MAX_ID_LENGTH;
258 my ($qt, $qf, $qc) = ('','', '');
259 $qt = '`' if $translator->quote_table_names;
260 $qf = '`' if $translator->quote_field_names;
262 debug("PKG: Beginning production\n");
265 $create .= header_comment unless ($no_comments);
266 # \todo Don't set if MySQL 3.x is set on command line
267 $create .= "SET foreign_key_checks=0;\n\n";
269 preprocess_schema($schema);
276 for my $table ( $schema->get_tables ) {
277 # print $table->name, "\n";
278 push @table_defs, create_table($table,
279 { add_drop_table => $add_drop_table,
280 show_warnings => $show_warnings,
281 no_comments => $no_comments,
282 quote_table_names => $qt,
283 quote_field_names => $qf,
284 max_id_length => $max_id_length,
285 mysql_version => $mysql_version
289 if ($mysql_version > 5.0) {
290 for my $view ( $schema->get_views ) {
291 push @table_defs, create_view($view,
292 { add_replace_view => $add_drop_table,
293 show_warnings => $show_warnings,
294 no_comments => $no_comments,
295 quote_table_names => $qt,
296 quote_field_names => $qf,
297 max_id_length => $max_id_length,
298 mysql_version => $mysql_version
304 # print "@table_defs\n";
305 push @table_defs, "SET foreign_key_checks=1;\n\n";
307 return wantarray ? ($create, @table_defs) : $create . join ('', @table_defs);
311 my ($view, $options) = @_;
312 my $qt = $options->{quote_table_names} || '';
313 my $qf = $options->{quote_field_names} || '';
315 my $view_name = $view->name;
316 debug("PKG: Looking at view '${view_name}'\n");
318 # Header. Should this look like what mysqldump produces?
320 $create .= "--\n-- View: ${qt}${view_name}${qt}\n--\n" unless $options->{no_comments};
322 $create .= ' OR REPLACE' if $options->{add_replace_view};
325 my $extra = $view->extra;
327 if( exists($extra->{mysql_algorithm}) && defined(my $algorithm = $extra->{mysql_algorithm}) ){
328 $create .= " ALGORITHM = ${algorithm}\n" if $algorithm =~ /(?:UNDEFINED|MERGE|TEMPTABLE)/i;
331 if( exists($extra->{mysql_definer}) && defined(my $user = $extra->{mysql_definer}) ){
332 $create .= " DEFINER = ${user}\n";
335 if( exists($extra->{mysql_security}) && defined(my $security = $extra->{mysql_security}) ){
336 $create .= " SQL SECURITY ${security}\n" if $security =~ /(?:DEFINER|INVOKER)/i;
340 $create .= " VIEW ${qt}${view_name}${qt}";
342 if( my @fields = $view->fields ){
343 my $list = join ', ', map { "${qf}${_}${qf}"} @fields;
344 $create .= " ( ${list} )";
346 if( my $sql = $view->sql ){
347 $create .= " AS (\n ${sql}\n )";
355 my ($table, $options) = @_;
357 my $qt = $options->{quote_table_names} || '';
358 my $qf = $options->{quote_field_names} || '';
360 my $table_name = $table->name;
361 debug("PKG: Looking at table '$table_name'\n");
364 # Header. Should this look like what mysqldump produces?
368 $create .= "--\n-- Table: $qt$table_name$qt\n--\n" unless $options->{no_comments};
369 $drop = qq[DROP TABLE IF EXISTS $qt$table_name$qt;\n] if $options->{add_drop_table};
370 $create .= "CREATE TABLE $qt$table_name$qt (\n";
376 for my $field ( $table->get_fields ) {
377 push @field_defs, create_field($field, $options);
385 for my $index ( $table->get_indices ) {
386 push @index_defs, create_index($index, $options);
387 $indexed_fields{ $_ } = 1 for $index->fields;
391 # Constraints -- need to handle more than just FK. -ky
394 my @constraints = $table->get_constraints;
395 for my $c ( @constraints ) {
396 my $constr = create_constraint($c, $options);
397 push @constraint_defs, $constr if($constr);
399 unless ( $indexed_fields{ ($c->fields())[0] } || $c->type ne FOREIGN_KEY ) {
400 push @index_defs, "INDEX ($qf" . ($c->fields())[0] . "$qf)";
401 $indexed_fields{ ($c->fields())[0] } = 1;
405 $create .= join(",\n", map { " $_" }
406 @field_defs, @index_defs, @constraint_defs
413 $create .= generate_table_options($table) || '';
416 return $drop ? ($drop,$create) : $create;
419 sub generate_table_options
424 my $table_type_defined = 0;
425 my $charset = $table->extra('mysql_charset');
426 my $collate = $table->extra('mysql_collate');
427 for my $t1_option_ref ( $table->options ) {
428 my($key, $value) = %{$t1_option_ref};
429 $table_type_defined = 1
430 if uc $key eq 'ENGINE' or uc $key eq 'TYPE';
431 if (uc $key eq 'CHARACTER SET') {
434 } elsif (uc $key eq 'COLLATE') {
438 $create .= " $key=$value";
441 my $mysql_table_type = $table->extra('mysql_table_type');
442 $create .= " ENGINE=$mysql_table_type"
443 if $mysql_table_type && !$table_type_defined;
444 my $comments = $table->comments;
446 $create .= " DEFAULT CHARACTER SET $charset" if $charset;
447 $create .= " COLLATE $collate" if $collate;
448 $create .= qq[ comment='$comments'] if $comments;
454 my ($field, $options) = @_;
456 my $qf = $options->{quote_field_names} ||= '';
458 my $field_name = $field->name;
459 debug("PKG: Looking at field '$field_name'\n");
460 my $field_def = "$qf$field_name$qf";
463 my $data_type = $field->data_type;
464 my @size = $field->size;
465 my %extra = $field->extra;
466 my $list = $extra{'list'} || [];
467 # \todo deal with embedded quotes
468 my $commalist = join( ', ', map { qq['$_'] } @$list );
469 my $charset = $extra{'mysql_charset'};
470 my $collate = $extra{'mysql_collate'};
473 # Oracle "number" type -- figure best MySQL type
475 if ( lc $data_type eq 'number' ) {
477 if ( scalar @size > 1 ) {
478 $data_type = 'double';
480 elsif ( $size[0] && $size[0] >= 12 ) {
481 $data_type = 'bigint';
483 elsif ( $size[0] && $size[0] <= 1 ) {
484 $data_type = 'tinyint';
491 # Convert a large Oracle varchar to "text"
493 elsif ( $data_type =~ /char/i && $size[0] > 255 ) {
497 elsif ( $data_type =~ /boolean/i ) {
498 my $mysql_version = $options->{mysql_version} || 0;
499 if ($mysql_version >= 4) {
500 $data_type = 'boolean';
503 $commalist = "'0','1'";
506 elsif ( exists $translate{ lc $data_type } ) {
507 $data_type = $translate{ lc $data_type };
510 @size = () if $data_type =~ /(text|blob)/i;
512 if ( $data_type =~ /(double|float)/ && scalar @size == 1 ) {
516 $field_def .= " $data_type";
518 if ( lc($data_type) eq 'enum' || lc($data_type) eq 'set') {
519 $field_def .= '(' . $commalist . ')';
521 elsif ( defined $size[0] && $size[0] > 0 ) {
522 $field_def .= '(' . join( ', ', @size ) . ')';
526 $field_def .= " CHARACTER SET $charset" if $charset;
527 $field_def .= " COLLATE $collate" if $collate;
530 for my $qual ( qw[ binary unsigned zerofill ] ) {
531 my $val = $extra{ $qual } || $extra{ uc $qual } or next;
532 $field_def .= " $qual";
534 for my $qual ( 'character set', 'collate', 'on update' ) {
535 my $val = $extra{ $qual } || $extra{ uc $qual } or next;
536 $field_def .= " $qual $val";
540 $field_def .= ' NOT NULL' unless $field->is_nullable;
542 # Default? XXX Need better quoting!
543 my $default = $field->default_value;
544 if ( defined $default ) {
545 if ( uc $default eq 'NULL') {
546 $field_def .= ' DEFAULT NULL';
548 $field_def .= " DEFAULT '$default'";
552 if ( my $comments = $field->comments ) {
553 $field_def .= qq[ comment '$comments'];
557 $field_def .= " auto_increment" if $field->is_auto_increment;
562 sub alter_create_index
564 my ($index, $options) = @_;
566 my $qt = $options->{quote_table_names} || '';
567 my $qf = $options->{quote_field_names} || '';
571 $qt.$index->table->name.$qt,
579 my ($index, $options) = @_;
581 my $qf = $options->{quote_field_names} || '';
584 lc $index->type eq 'normal' ? 'INDEX' : $index->type . ' INDEX',
585 truncate_id_uniquely( $index->name, $options->{max_id_length} || $DEFAULT_MAX_ID_LENGTH ),
586 '(' . $qf . join( "$qf, $qf", $index->fields ) . $qf . ')'
593 my ($index, $options) = @_;
595 my $qt = $options->{quote_table_names} || '';
596 my $qf = $options->{quote_field_names} || '';
600 $qt.$index->table->name.$qt,
603 $index->name || $index->fields
608 sub alter_drop_constraint
610 my ($c, $options) = @_;
612 my $qt = $options->{quote_table_names} || '';
613 my $qc = $options->{quote_field_names} || '';
615 my $out = sprintf('ALTER TABLE %s DROP %s %s',
616 $qt . $c->table->name . $qt,
617 $c->type eq FOREIGN_KEY ? $c->type : "INDEX",
618 $qc . $c->name . $qc );
623 sub alter_create_constraint
625 my ($index, $options) = @_;
627 my $qt = $options->{quote_table_names} || '';
630 $qt.$index->table->name.$qt,
632 create_constraint(@_) );
635 sub create_constraint
637 my ($c, $options) = @_;
639 my $qf = $options->{quote_field_names} || '';
640 my $qt = $options->{quote_table_names} || '';
641 my $leave_name = $options->{leave_name} || undef;
643 my @fields = $c->fields or next;
645 if ( $c->type eq PRIMARY_KEY ) {
646 return 'PRIMARY KEY (' . $qf . join("$qf, $qf", @fields). $qf . ')';
648 elsif ( $c->type eq UNIQUE ) {
651 (defined $c->name ? $qf.truncate_id_uniquely( $c->name, $options->{max_id_length} || $DEFAULT_MAX_ID_LENGTH ).$qf.' ' : '').
652 '(' . $qf . join("$qf, $qf", @fields). $qf . ')';
654 elsif ( $c->type eq FOREIGN_KEY ) {
656 # Make sure FK field is indexed or MySQL complains.
659 my $table = $c->table;
660 my $c_name = truncate_id_uniquely( $c->name, $options->{max_id_length} || $DEFAULT_MAX_ID_LENGTH );
670 $def .= ' ('.$qf . join( "$qf, $qf", @fields ) . $qf . ')';
672 $def .= ' REFERENCES ' . $qt . $c->reference_table . $qt;
674 my @rfields = map { $_ || () } $c->reference_fields;
675 unless ( @rfields ) {
676 my $rtable_name = $c->reference_table;
677 if ( my $ref_table = $table->schema->get_table( $rtable_name ) ) {
678 push @rfields, $ref_table->primary_key;
681 warn "Can't find reference table '$rtable_name' " .
682 "in schema\n" if $options->{show_warnings};
687 $def .= ' (' . $qf . join( "$qf, $qf", @rfields ) . $qf . ')';
690 warn "FK constraint on " . $table->name . '.' .
691 join('', @fields) . " has no reference fields\n"
692 if $options->{show_warnings};
695 if ( $c->match_type ) {
697 ( $c->match_type =~ /full/i ) ? 'FULL' : 'PARTIAL';
700 if ( $c->on_delete ) {
701 $def .= ' ON DELETE '.join( ' ', $c->on_delete );
704 if ( $c->on_update ) {
705 $def .= ' ON UPDATE '.join( ' ', $c->on_update );
715 my ($to_table, $options) = @_;
717 my $qt = $options->{quote_table_names} || '';
719 my $table_options = generate_table_options($to_table) || '';
720 my $out = sprintf('ALTER TABLE %s%s',
721 $qt . $to_table->name . $qt,
727 sub rename_field { alter_field(@_) }
730 my ($from_field, $to_field, $options) = @_;
732 my $qf = $options->{quote_field_names} || '';
733 my $qt = $options->{quote_table_names} || '';
735 my $out = sprintf('ALTER TABLE %s CHANGE COLUMN %s %s',
736 $qt . $to_field->table->name . $qt,
737 $qf . $from_field->name . $qf,
738 create_field($to_field, $options));
745 my ($new_field, $options) = @_;
747 my $qt = $options->{quote_table_names} || '';
749 my $out = sprintf('ALTER TABLE %s ADD COLUMN %s',
750 $qt . $new_field->table->name . $qt,
751 create_field($new_field, $options));
759 my ($old_field, $options) = @_;
761 my $qf = $options->{quote_field_names} || '';
762 my $qt = $options->{quote_table_names} || '';
764 my $out = sprintf('ALTER TABLE %s DROP COLUMN %s',
765 $qt . $old_field->table->name . $qt,
766 $qf . $old_field->name . $qf);
772 sub batch_alter_table {
773 my ($table, $diff_hash, $options) = @_;
775 # InnoDB has an issue with dropping and re-adding a FK constraint under the
776 # name in a single alter statment, see: http://bugs.mysql.com/bug.php?id=13741
778 # We have to work round this.
781 my %fks_to_drop = map {
782 $_->type eq FOREIGN_KEY
785 } @{$diff_hash->{alter_drop_constraint} };
787 my %fks_to_create = map {
788 if ( $_->type eq FOREIGN_KEY) {
789 $fks_to_alter{$_->name} = $fks_to_drop{$_->name} if $fks_to_drop{$_->name};
792 } @{$diff_hash->{alter_create_constraint} };
795 if (scalar keys %fks_to_alter) {
796 $diff_hash->{alter_drop_constraint} = [
797 grep { !$fks_to_alter{$_->name} } @{ $diff_hash->{alter_drop_constraint} }
800 $drop_stmt = batch_alter_table($table, { alter_drop_constraint => [ values %fks_to_alter ] }, $options)
806 if (@{ $diff_hash->{$_} || [] }) {
807 my $meth = __PACKAGE__->can($_) or die __PACKAGE__ . " cant $_";
808 map { $meth->( (ref $_ eq 'ARRAY' ? @$_ : $_), $options ) } @{ $diff_hash->{$_} }
811 alter_drop_constraint
818 alter_create_constraint
821 # rename_table makes things a bit more complex
822 my $renamed_from = "";
823 $renamed_from = $diff_hash->{rename_table}[0][0]->name
824 if $diff_hash->{rename_table} && @{$diff_hash->{rename_table}};
826 return unless @stmts;
827 # Just zero or one stmts. return now
828 return "$drop_stmt@stmts;" unless @stmts > 1;
830 # Now strip off the 'ALTER TABLE xyz' of all but the first one
832 my $qt = $options->{quote_table_names} || '';
833 my $table_name = $qt . $table->name . $qt;
836 my $re = $renamed_from
837 ? qr/^ALTER TABLE (?:\Q$table_name\E|\Q$qt$renamed_from$qt\E) /
838 : qr/^ALTER TABLE \Q$table_name\E /;
840 my $first = shift @stmts;
841 my ($alter_table) = $first =~ /($re)/;
843 my $padd = " " x length($alter_table);
845 return $drop_stmt . join( ",\n", $first, map { s/$re//; $padd . $_ } @stmts) . ';';
850 my ($table, $options) = @_;
852 my $qt = $options->{quote_table_names} || '';
854 # Drop (foreign key) constraints so table drops cleanly
855 my @sql = batch_alter_table($table, { alter_drop_constraint => [ grep { $_->type eq 'FOREIGN KEY' } $table->get_constraints ] }, $options);
857 return join("\n", @sql, "DROP TABLE $qt$table$qt;");
862 my ($old_table, $new_table, $options) = @_;
864 my $qt = $options->{quote_table_names} || '';
866 return "ALTER TABLE $qt$old_table$qt RENAME TO $qt$new_table$qt";
869 sub next_unused_name {
870 my $name = shift || '';
871 if ( !defined($used_names{$name}) ) {
872 $used_names{$name} = $name;
877 while ( defined($used_names{$name . '_' . $i}) ) {
881 $used_names{$name} = $name;
887 # -------------------------------------------------------------------
893 SQL::Translator, http://www.mysql.com/.
897 darren chamberlain E<lt>darren@cpan.orgE<gt>,
898 Ken Y. Clark E<lt>kclark@cpan.orgE<gt>.