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 This producer takes a single optional producer_arg C<mysql_version>, which
46 provides the desired version for the target database. By default MySQL v3 is
47 assumed, and statements pertaining to any features introduced in later versions
48 (e.g. CREATE VIEW) are not produced.
50 Valid version specifiers for C<mysql_parser_version> are listed L<here|SQL::Translator::Utils/parse_mysql_version>
54 Normally the tables will be created without any explicit table type given and
55 so will use the MySQL default.
57 Any tables involved in foreign key constraints automatically get a table type
58 of InnoDB, unless this is overridden by setting the C<mysql_table_type> extra
59 attribute explicitly on the table.
61 =head2 Extra attributes.
63 The producer recognises the following extra attributes on the Schema objects.
69 Set the list of allowed values for Enum fields.
71 =item B<field.binary>, B<field.unsigned>, B<field.zerofill>
73 Set the MySQL field options of the same name.
75 =item B<field.renamed_from>, B<table.renamed_from>
77 Use when producing diffs to indicate that the current table/field has been
78 renamed from the old name as given in the attribute value.
80 =item B<table.mysql_table_type>
82 Set the type of the table e.g. 'InnoDB', 'MyISAM'. This will be
83 automatically set for tables involved in foreign key constraints if it is
84 not already set explicitly. See L<"Table Types">.
86 Please note that the C<ENGINE> option is the prefered method of specifying
87 the MySQL storage engine to use, but this method still works for backwards
90 =item B<table.mysql_charset>, B<table.mysql_collate>
92 Set the tables default charater set and collation order.
94 =item B<field.mysql_charset>, B<field.mysql_collate>
96 Set the fields charater set and collation order.
104 use vars qw[ $VERSION $DEBUG %used_names ];
105 $VERSION = sprintf "%d.%02d", q$Revision: 1.54 $ =~ /(\d+)\.(\d+)/;
106 $DEBUG = 0 unless defined $DEBUG;
108 # Maximum length for most identifiers is 64, according to:
109 # http://dev.mysql.com/doc/refman/4.1/en/identifiers.html
110 # http://dev.mysql.com/doc/refman/5.0/en/identifiers.html
111 my $DEFAULT_MAX_ID_LENGTH = 64;
114 use SQL::Translator::Schema::Constants;
115 use SQL::Translator::Utils qw(debug header_comment truncate_id_uniquely parse_mysql_version);
118 # Use only lowercase for the keys (e.g. "long" and not "LONG")
124 varchar2 => 'varchar',
140 'long integer' => 'integer',
142 'datetime' => 'datetime',
146 sub preprocess_schema {
149 # extra->{mysql_table_type} used to be the type. It belongs in options, so
150 # move it if we find it. Return Engine type if found in extra or options
151 # Similarly for mysql_charset and mysql_collate
152 my $extra_to_options = sub {
153 my ($table, $extra_name, $opt_name) = @_;
155 my $extra = $table->extra;
157 my $extra_type = delete $extra->{$extra_name};
159 # Now just to find if there is already an Engine or Type option...
160 # and lets normalize it to ENGINE since:
162 # The ENGINE table option specifies the storage engine for the table.
163 # TYPE is a synonym, but ENGINE is the preferred option name.
166 # We have to use the hash directly here since otherwise there is no way
168 my $options = ( $table->{options} ||= []);
170 # If multiple option names, normalize to the first one
172 OPT_NAME: for ( @$opt_name[1..$#$opt_name] ) {
173 for my $idx ( 0..$#{$options} ) {
174 my ($key, $value) = %{ $options->[$idx] };
177 $options->[$idx] = { $opt_name->[0] => $value };
182 $opt_name = $opt_name->[0];
187 # This assumes that there isn't both a Type and an Engine option.
189 for my $idx ( 0..$#{$options} ) {
190 my ($key, $value) = %{ $options->[$idx] };
192 next unless uc $key eq $opt_name;
194 # make sure case is right on option name
195 delete $options->[$idx]{$key};
196 return $options->[$idx]{$opt_name} = $value || $extra_type;
201 push @$options, { $opt_name => $extra_type };
207 # Names are only specific to a given schema
208 local %used_names = ();
211 # Work out which tables need to be InnoDB to support foreign key
212 # constraints. We do this first as we need InnoDB at both ends.
214 foreach my $table ( $schema->get_tables ) {
216 $extra_to_options->($table, 'mysql_table_type', ['ENGINE', 'TYPE'] );
217 $extra_to_options->($table, 'mysql_charset', 'CHARACTER SET' );
218 $extra_to_options->($table, 'mysql_collate', 'COLLATE' );
220 foreach my $c ( $table->get_constraints ) {
221 next unless $c->type eq FOREIGN_KEY;
223 # Normalize constraint names here.
224 my $c_name = $c->name;
225 # Give the constraint a name if it doesn't have one, so it doens't feel
227 $c_name = $table->name . '_fk' unless length $c_name;
229 $c->name( next_unused_name($c_name) );
231 for my $meth (qw/table reference_table/) {
232 my $table = $schema->get_table($c->$meth) || next;
233 # This normalizes the types to ENGINE and returns the value if its there
234 next if $extra_to_options->($table, 'mysql_table_type', ['ENGINE', 'TYPE']);
235 $table->options( { 'ENGINE' => 'InnoDB' } );
237 } # foreach constraints
239 my %map = ( mysql_collate => 'collate', mysql_charset => 'character set');
240 foreach my $f ( $table->get_fields ) {
241 my $extra = $f->extra;
243 $extra->{$map{$_}} = delete $extra->{$_} if exists $extra->{$_};
247 if ( !$size[0] && $f->data_type =~ /char$/ ) {
256 my $translator = shift;
257 local $DEBUG = $translator->debug;
259 my $no_comments = $translator->no_comments;
260 my $add_drop_table = $translator->add_drop_table;
261 my $schema = $translator->schema;
262 my $show_warnings = $translator->show_warnings || 0;
263 my $producer_args = $translator->producer_args;
264 my $mysql_version = parse_mysql_version ($producer_args->{mysql_version}, 'perl') || 0;
265 my $max_id_length = $producer_args->{mysql_max_id_length} || $DEFAULT_MAX_ID_LENGTH;
267 my ($qt, $qf, $qc) = ('','', '');
268 $qt = '`' if $translator->quote_table_names;
269 $qf = '`' if $translator->quote_field_names;
271 debug("PKG: Beginning production\n");
274 $create .= header_comment unless ($no_comments);
275 # \todo Don't set if MySQL 3.x is set on command line
276 my @create = "SET foreign_key_checks=0";
278 preprocess_schema($schema);
285 for my $table ( $schema->get_tables ) {
286 # print $table->name, "\n";
287 push @table_defs, create_table($table,
288 { add_drop_table => $add_drop_table,
289 show_warnings => $show_warnings,
290 no_comments => $no_comments,
291 quote_table_names => $qt,
292 quote_field_names => $qf,
293 max_id_length => $max_id_length,
294 mysql_version => $mysql_version
298 if ($mysql_version >= 5.000001) {
299 for my $view ( $schema->get_views ) {
300 push @table_defs, create_view($view,
301 { add_replace_view => $add_drop_table,
302 show_warnings => $show_warnings,
303 no_comments => $no_comments,
304 quote_table_names => $qt,
305 quote_field_names => $qf,
306 max_id_length => $max_id_length,
307 mysql_version => $mysql_version
313 # print "@table_defs\n";
314 push @table_defs, "SET foreign_key_checks=1";
316 return wantarray ? ($create ? $create : (), @create, @table_defs) : ($create . join('', map { $_ ? "$_;\n\n" : () } (@create, @table_defs)));
320 my ($view, $options) = @_;
321 my $qt = $options->{quote_table_names} || '';
322 my $qf = $options->{quote_field_names} || '';
324 my $view_name = $view->name;
325 debug("PKG: Looking at view '${view_name}'\n");
327 # Header. Should this look like what mysqldump produces?
329 $create .= "--\n-- View: ${qt}${view_name}${qt}\n--\n" unless $options->{no_comments};
331 $create .= ' OR REPLACE' if $options->{add_replace_view};
334 my $extra = $view->extra;
336 if( exists($extra->{mysql_algorithm}) && defined(my $algorithm = $extra->{mysql_algorithm}) ){
337 $create .= " ALGORITHM = ${algorithm}\n" if $algorithm =~ /(?:UNDEFINED|MERGE|TEMPTABLE)/i;
340 if( exists($extra->{mysql_definer}) && defined(my $user = $extra->{mysql_definer}) ){
341 $create .= " DEFINER = ${user}\n";
344 if( exists($extra->{mysql_security}) && defined(my $security = $extra->{mysql_security}) ){
345 $create .= " SQL SECURITY ${security}\n" if $security =~ /(?:DEFINER|INVOKER)/i;
349 $create .= " VIEW ${qt}${view_name}${qt}";
351 if( my @fields = $view->fields ){
352 my $list = join ', ', map { "${qf}${_}${qf}"} @fields;
353 $create .= " ( ${list} )";
355 if( my $sql = $view->sql ){
356 $create .= " AS (\n ${sql}\n )";
364 my ($table, $options) = @_;
366 my $qt = $options->{quote_table_names} || '';
367 my $qf = $options->{quote_field_names} || '';
369 my $table_name = $table->name;
370 debug("PKG: Looking at table '$table_name'\n");
373 # Header. Should this look like what mysqldump produces?
377 $create .= "--\n-- Table: $qt$table_name$qt\n--\n" unless $options->{no_comments};
378 $drop = qq[DROP TABLE IF EXISTS $qt$table_name$qt] if $options->{add_drop_table};
379 $create .= "CREATE TABLE $qt$table_name$qt (\n";
385 for my $field ( $table->get_fields ) {
386 push @field_defs, create_field($field, $options);
394 for my $index ( $table->get_indices ) {
395 push @index_defs, create_index($index, $options);
396 $indexed_fields{ $_ } = 1 for $index->fields;
400 # Constraints -- need to handle more than just FK. -ky
403 my @constraints = $table->get_constraints;
404 for my $c ( @constraints ) {
405 my $constr = create_constraint($c, $options);
406 push @constraint_defs, $constr if($constr);
408 unless ( $indexed_fields{ ($c->fields())[0] } || $c->type ne FOREIGN_KEY ) {
409 push @index_defs, "INDEX ($qf" . ($c->fields())[0] . "$qf)";
410 $indexed_fields{ ($c->fields())[0] } = 1;
414 $create .= join(",\n", map { " $_" }
415 @field_defs, @index_defs, @constraint_defs
422 $create .= generate_table_options($table, $options) || '';
423 # $create .= ";\n\n";
425 return $drop ? ($drop,$create) : $create;
428 sub generate_table_options
430 my ($table, $options) = @_;
433 my $table_type_defined = 0;
434 my $qf = $options->{quote_field_names} ||= '';
435 my $charset = $table->extra('mysql_charset');
436 my $collate = $table->extra('mysql_collate');
438 for my $t1_option_ref ( $table->options ) {
439 my($key, $value) = %{$t1_option_ref};
440 $table_type_defined = 1
441 if uc $key eq 'ENGINE' or uc $key eq 'TYPE';
442 if (uc $key eq 'CHARACTER SET') {
445 } elsif (uc $key eq 'COLLATE') {
448 } elsif (uc $key eq 'UNION') {
449 $union = "($qf". join("$qf, $qf", @$value) ."$qf)";
452 $create .= " $key=$value";
455 my $mysql_table_type = $table->extra('mysql_table_type');
456 $create .= " ENGINE=$mysql_table_type"
457 if $mysql_table_type && !$table_type_defined;
458 my $comments = $table->comments;
460 $create .= " DEFAULT CHARACTER SET $charset" if $charset;
461 $create .= " COLLATE $collate" if $collate;
462 $create .= " UNION=$union" if $union;
463 $create .= qq[ comment='$comments'] if $comments;
469 my ($field, $options) = @_;
471 my $qf = $options->{quote_field_names} ||= '';
473 my $field_name = $field->name;
474 debug("PKG: Looking at field '$field_name'\n");
475 my $field_def = "$qf$field_name$qf";
478 my $data_type = $field->data_type;
479 my @size = $field->size;
480 my %extra = $field->extra;
481 my $list = $extra{'list'} || [];
482 # \todo deal with embedded quotes
483 my $commalist = join( ', ', map { qq['$_'] } @$list );
484 my $charset = $extra{'mysql_charset'};
485 my $collate = $extra{'mysql_collate'};
487 my $mysql_version = $options->{mysql_version} || 0;
489 # Oracle "number" type -- figure best MySQL type
491 if ( lc $data_type eq 'number' ) {
493 if ( scalar @size > 1 ) {
494 $data_type = 'double';
496 elsif ( $size[0] && $size[0] >= 12 ) {
497 $data_type = 'bigint';
499 elsif ( $size[0] && $size[0] <= 1 ) {
500 $data_type = 'tinyint';
507 # Convert a large Oracle varchar to "text"
508 # (not necessary as of 5.0.3 http://dev.mysql.com/doc/refman/5.0/en/char.html)
510 elsif ( $data_type =~ /char/i && $size[0] > 255 ) {
511 unless ($size[0] <= 65535 && $mysql_version >= 5.000003 ) {
516 elsif ( $data_type =~ /boolean/i ) {
517 if ($mysql_version >= 4) {
518 $data_type = 'boolean';
521 $commalist = "'0','1'";
524 elsif ( exists $translate{ lc $data_type } ) {
525 $data_type = $translate{ lc $data_type };
528 @size = () if $data_type =~ /(text|blob)/i;
530 if ( $data_type =~ /(double|float)/ && scalar @size == 1 ) {
534 $field_def .= " $data_type";
536 if ( lc($data_type) eq 'enum' || lc($data_type) eq 'set') {
537 $field_def .= '(' . $commalist . ')';
539 elsif ( defined $size[0] && $size[0] > 0 ) {
540 $field_def .= '(' . join( ', ', @size ) . ')';
544 $field_def .= " CHARACTER SET $charset" if $charset;
545 $field_def .= " COLLATE $collate" if $collate;
548 for my $qual ( qw[ binary unsigned zerofill ] ) {
549 my $val = $extra{ $qual } || $extra{ uc $qual } or next;
550 $field_def .= " $qual";
552 for my $qual ( 'character set', 'collate', 'on update' ) {
553 my $val = $extra{ $qual } || $extra{ uc $qual } or next;
554 $field_def .= " $qual $val";
558 $field_def .= ' NOT NULL' unless $field->is_nullable;
560 # Default? XXX Need better quoting!
561 my $default = $field->default_value;
562 if ( defined $default ) {
563 SQL::Translator::Producer->_apply_default_value(
575 if ( my $comments = $field->comments ) {
576 $field_def .= qq[ comment '$comments'];
580 $field_def .= " auto_increment" if $field->is_auto_increment;
585 sub alter_create_index
587 my ($index, $options) = @_;
589 my $qt = $options->{quote_table_names} || '';
590 my $qf = $options->{quote_field_names} || '';
594 $qt.$index->table->name.$qt,
602 my ($index, $options) = @_;
604 my $qf = $options->{quote_field_names} || '';
607 lc $index->type eq 'normal' ? 'INDEX' : $index->type . ' INDEX',
608 truncate_id_uniquely( $index->name, $options->{max_id_length} || $DEFAULT_MAX_ID_LENGTH ),
609 '(' . $qf . join( "$qf, $qf", $index->fields ) . $qf . ')'
616 my ($index, $options) = @_;
618 my $qt = $options->{quote_table_names} || '';
619 my $qf = $options->{quote_field_names} || '';
623 $qt.$index->table->name.$qt,
626 $index->name || $index->fields
631 sub alter_drop_constraint
633 my ($c, $options) = @_;
635 my $qt = $options->{quote_table_names} || '';
636 my $qc = $options->{quote_field_names} || '';
638 my $out = sprintf('ALTER TABLE %s DROP %s %s',
639 $qt . $c->table->name . $qt,
640 $c->type eq FOREIGN_KEY ? $c->type : "INDEX",
641 $qc . $c->name . $qc );
646 sub alter_create_constraint
648 my ($index, $options) = @_;
650 my $qt = $options->{quote_table_names} || '';
653 $qt.$index->table->name.$qt,
655 create_constraint(@_) );
658 sub create_constraint
660 my ($c, $options) = @_;
662 my $qf = $options->{quote_field_names} || '';
663 my $qt = $options->{quote_table_names} || '';
664 my $leave_name = $options->{leave_name} || undef;
666 my @fields = $c->fields or next;
668 if ( $c->type eq PRIMARY_KEY ) {
669 return 'PRIMARY KEY (' . $qf . join("$qf, $qf", @fields). $qf . ')';
671 elsif ( $c->type eq UNIQUE ) {
674 (defined $c->name ? $qf.truncate_id_uniquely( $c->name, $options->{max_id_length} || $DEFAULT_MAX_ID_LENGTH ).$qf.' ' : '').
675 '(' . $qf . join("$qf, $qf", @fields). $qf . ')';
677 elsif ( $c->type eq FOREIGN_KEY ) {
679 # Make sure FK field is indexed or MySQL complains.
682 my $table = $c->table;
683 my $c_name = truncate_id_uniquely( $c->name, $options->{max_id_length} || $DEFAULT_MAX_ID_LENGTH );
693 $def .= ' ('.$qf . join( "$qf, $qf", @fields ) . $qf . ')';
695 $def .= ' REFERENCES ' . $qt . $c->reference_table . $qt;
697 my @rfields = map { $_ || () } $c->reference_fields;
698 unless ( @rfields ) {
699 my $rtable_name = $c->reference_table;
700 if ( my $ref_table = $table->schema->get_table( $rtable_name ) ) {
701 push @rfields, $ref_table->primary_key;
704 warn "Can't find reference table '$rtable_name' " .
705 "in schema\n" if $options->{show_warnings};
710 $def .= ' (' . $qf . join( "$qf, $qf", @rfields ) . $qf . ')';
713 warn "FK constraint on " . $table->name . '.' .
714 join('', @fields) . " has no reference fields\n"
715 if $options->{show_warnings};
718 if ( $c->match_type ) {
720 ( $c->match_type =~ /full/i ) ? 'FULL' : 'PARTIAL';
723 if ( $c->on_delete ) {
724 $def .= ' ON DELETE '.join( ' ', $c->on_delete );
727 if ( $c->on_update ) {
728 $def .= ' ON UPDATE '.join( ' ', $c->on_update );
738 my ($to_table, $options) = @_;
740 my $qt = $options->{quote_table_names} || '';
742 my $table_options = generate_table_options($to_table, $options) || '';
743 my $out = sprintf('ALTER TABLE %s%s',
744 $qt . $to_table->name . $qt,
750 sub rename_field { alter_field(@_) }
753 my ($from_field, $to_field, $options) = @_;
755 my $qf = $options->{quote_field_names} || '';
756 my $qt = $options->{quote_table_names} || '';
758 my $out = sprintf('ALTER TABLE %s CHANGE COLUMN %s %s',
759 $qt . $to_field->table->name . $qt,
760 $qf . $from_field->name . $qf,
761 create_field($to_field, $options));
768 my ($new_field, $options) = @_;
770 my $qt = $options->{quote_table_names} || '';
772 my $out = sprintf('ALTER TABLE %s ADD COLUMN %s',
773 $qt . $new_field->table->name . $qt,
774 create_field($new_field, $options));
782 my ($old_field, $options) = @_;
784 my $qf = $options->{quote_field_names} || '';
785 my $qt = $options->{quote_table_names} || '';
787 my $out = sprintf('ALTER TABLE %s DROP COLUMN %s',
788 $qt . $old_field->table->name . $qt,
789 $qf . $old_field->name . $qf);
795 sub batch_alter_table {
796 my ($table, $diff_hash, $options) = @_;
798 # InnoDB has an issue with dropping and re-adding a FK constraint under the
799 # name in a single alter statment, see: http://bugs.mysql.com/bug.php?id=13741
801 # We have to work round this.
804 my %fks_to_drop = map {
805 $_->type eq FOREIGN_KEY
808 } @{$diff_hash->{alter_drop_constraint} };
810 my %fks_to_create = map {
811 if ( $_->type eq FOREIGN_KEY) {
812 $fks_to_alter{$_->name} = $fks_to_drop{$_->name} if $fks_to_drop{$_->name};
815 } @{$diff_hash->{alter_create_constraint} };
818 if (scalar keys %fks_to_alter) {
819 $diff_hash->{alter_drop_constraint} = [
820 grep { !$fks_to_alter{$_->name} } @{ $diff_hash->{alter_drop_constraint} }
823 @drop_stmt = batch_alter_table($table, { alter_drop_constraint => [ values %fks_to_alter ] }, $options);
828 if (@{ $diff_hash->{$_} || [] }) {
829 my $meth = __PACKAGE__->can($_) or die __PACKAGE__ . " cant $_";
830 map { $meth->( (ref $_ eq 'ARRAY' ? @$_ : $_), $options ) } @{ $diff_hash->{$_} }
833 alter_drop_constraint
840 alter_create_constraint
843 # rename_table makes things a bit more complex
844 my $renamed_from = "";
845 $renamed_from = $diff_hash->{rename_table}[0][0]->name
846 if $diff_hash->{rename_table} && @{$diff_hash->{rename_table}};
848 return unless @stmts;
849 # Just zero or one stmts. return now
850 return (@drop_stmt,@stmts) unless @stmts > 1;
852 # Now strip off the 'ALTER TABLE xyz' of all but the first one
854 my $qt = $options->{quote_table_names} || '';
855 my $table_name = $qt . $table->name . $qt;
858 my $re = $renamed_from
859 ? qr/^ALTER TABLE (?:\Q$table_name\E|\Q$qt$renamed_from$qt\E) /
860 : qr/^ALTER TABLE \Q$table_name\E /;
862 my $first = shift @stmts;
863 my ($alter_table) = $first =~ /($re)/;
865 my $padd = " " x length($alter_table);
867 return @drop_stmt, join( ",\n", $first, map { s/$re//; $padd . $_ } @stmts);
872 my ($table, $options) = @_;
874 my $qt = $options->{quote_table_names} || '';
876 # Drop (foreign key) constraints so table drops cleanly
877 my @sql = batch_alter_table($table, { alter_drop_constraint => [ grep { $_->type eq 'FOREIGN KEY' } $table->get_constraints ] }, $options);
879 return (@sql, "DROP TABLE $qt$table$qt");
880 # return join("\n", @sql, "DROP TABLE $qt$table$qt");
885 my ($old_table, $new_table, $options) = @_;
887 my $qt = $options->{quote_table_names} || '';
889 return "ALTER TABLE $qt$old_table$qt RENAME TO $qt$new_table$qt";
892 sub next_unused_name {
893 my $name = shift || '';
894 if ( !defined($used_names{$name}) ) {
895 $used_names{$name} = $name;
900 while ( defined($used_names{$name . '_' . $i}) ) {
904 $used_names{$name} = $name;
910 # -------------------------------------------------------------------
916 SQL::Translator, http://www.mysql.com/.
920 darren chamberlain E<lt>darren@cpan.orgE<gt>,
921 Ken Y. Clark E<lt>kclark@cpan.orgE<gt>.