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.0) {
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(
572 if ( my $comments = $field->comments ) {
573 $field_def .= qq[ comment '$comments'];
577 $field_def .= " auto_increment" if $field->is_auto_increment;
582 sub alter_create_index
584 my ($index, $options) = @_;
586 my $qt = $options->{quote_table_names} || '';
587 my $qf = $options->{quote_field_names} || '';
591 $qt.$index->table->name.$qt,
599 my ($index, $options) = @_;
601 my $qf = $options->{quote_field_names} || '';
604 lc $index->type eq 'normal' ? 'INDEX' : $index->type . ' INDEX',
605 truncate_id_uniquely( $index->name, $options->{max_id_length} || $DEFAULT_MAX_ID_LENGTH ),
606 '(' . $qf . join( "$qf, $qf", $index->fields ) . $qf . ')'
613 my ($index, $options) = @_;
615 my $qt = $options->{quote_table_names} || '';
616 my $qf = $options->{quote_field_names} || '';
620 $qt.$index->table->name.$qt,
623 $index->name || $index->fields
628 sub alter_drop_constraint
630 my ($c, $options) = @_;
632 my $qt = $options->{quote_table_names} || '';
633 my $qc = $options->{quote_field_names} || '';
635 my $out = sprintf('ALTER TABLE %s DROP %s %s',
636 $qt . $c->table->name . $qt,
637 $c->type eq FOREIGN_KEY ? $c->type : "INDEX",
638 $qc . $c->name . $qc );
643 sub alter_create_constraint
645 my ($index, $options) = @_;
647 my $qt = $options->{quote_table_names} || '';
650 $qt.$index->table->name.$qt,
652 create_constraint(@_) );
655 sub create_constraint
657 my ($c, $options) = @_;
659 my $qf = $options->{quote_field_names} || '';
660 my $qt = $options->{quote_table_names} || '';
661 my $leave_name = $options->{leave_name} || undef;
663 my @fields = $c->fields or next;
665 if ( $c->type eq PRIMARY_KEY ) {
666 return 'PRIMARY KEY (' . $qf . join("$qf, $qf", @fields). $qf . ')';
668 elsif ( $c->type eq UNIQUE ) {
671 (defined $c->name ? $qf.truncate_id_uniquely( $c->name, $options->{max_id_length} || $DEFAULT_MAX_ID_LENGTH ).$qf.' ' : '').
672 '(' . $qf . join("$qf, $qf", @fields). $qf . ')';
674 elsif ( $c->type eq FOREIGN_KEY ) {
676 # Make sure FK field is indexed or MySQL complains.
679 my $table = $c->table;
680 my $c_name = truncate_id_uniquely( $c->name, $options->{max_id_length} || $DEFAULT_MAX_ID_LENGTH );
690 $def .= ' ('.$qf . join( "$qf, $qf", @fields ) . $qf . ')';
692 $def .= ' REFERENCES ' . $qt . $c->reference_table . $qt;
694 my @rfields = map { $_ || () } $c->reference_fields;
695 unless ( @rfields ) {
696 my $rtable_name = $c->reference_table;
697 if ( my $ref_table = $table->schema->get_table( $rtable_name ) ) {
698 push @rfields, $ref_table->primary_key;
701 warn "Can't find reference table '$rtable_name' " .
702 "in schema\n" if $options->{show_warnings};
707 $def .= ' (' . $qf . join( "$qf, $qf", @rfields ) . $qf . ')';
710 warn "FK constraint on " . $table->name . '.' .
711 join('', @fields) . " has no reference fields\n"
712 if $options->{show_warnings};
715 if ( $c->match_type ) {
717 ( $c->match_type =~ /full/i ) ? 'FULL' : 'PARTIAL';
720 if ( $c->on_delete ) {
721 $def .= ' ON DELETE '.join( ' ', $c->on_delete );
724 if ( $c->on_update ) {
725 $def .= ' ON UPDATE '.join( ' ', $c->on_update );
735 my ($to_table, $options) = @_;
737 my $qt = $options->{quote_table_names} || '';
739 my $table_options = generate_table_options($to_table, $options) || '';
740 my $out = sprintf('ALTER TABLE %s%s',
741 $qt . $to_table->name . $qt,
747 sub rename_field { alter_field(@_) }
750 my ($from_field, $to_field, $options) = @_;
752 my $qf = $options->{quote_field_names} || '';
753 my $qt = $options->{quote_table_names} || '';
755 my $out = sprintf('ALTER TABLE %s CHANGE COLUMN %s %s',
756 $qt . $to_field->table->name . $qt,
757 $qf . $from_field->name . $qf,
758 create_field($to_field, $options));
765 my ($new_field, $options) = @_;
767 my $qt = $options->{quote_table_names} || '';
769 my $out = sprintf('ALTER TABLE %s ADD COLUMN %s',
770 $qt . $new_field->table->name . $qt,
771 create_field($new_field, $options));
779 my ($old_field, $options) = @_;
781 my $qf = $options->{quote_field_names} || '';
782 my $qt = $options->{quote_table_names} || '';
784 my $out = sprintf('ALTER TABLE %s DROP COLUMN %s',
785 $qt . $old_field->table->name . $qt,
786 $qf . $old_field->name . $qf);
792 sub batch_alter_table {
793 my ($table, $diff_hash, $options) = @_;
795 # InnoDB has an issue with dropping and re-adding a FK constraint under the
796 # name in a single alter statment, see: http://bugs.mysql.com/bug.php?id=13741
798 # We have to work round this.
801 my %fks_to_drop = map {
802 $_->type eq FOREIGN_KEY
805 } @{$diff_hash->{alter_drop_constraint} };
807 my %fks_to_create = map {
808 if ( $_->type eq FOREIGN_KEY) {
809 $fks_to_alter{$_->name} = $fks_to_drop{$_->name} if $fks_to_drop{$_->name};
812 } @{$diff_hash->{alter_create_constraint} };
815 if (scalar keys %fks_to_alter) {
816 $diff_hash->{alter_drop_constraint} = [
817 grep { !$fks_to_alter{$_->name} } @{ $diff_hash->{alter_drop_constraint} }
820 @drop_stmt = batch_alter_table($table, { alter_drop_constraint => [ values %fks_to_alter ] }, $options);
825 if (@{ $diff_hash->{$_} || [] }) {
826 my $meth = __PACKAGE__->can($_) or die __PACKAGE__ . " cant $_";
827 map { $meth->( (ref $_ eq 'ARRAY' ? @$_ : $_), $options ) } @{ $diff_hash->{$_} }
830 alter_drop_constraint
837 alter_create_constraint
840 # rename_table makes things a bit more complex
841 my $renamed_from = "";
842 $renamed_from = $diff_hash->{rename_table}[0][0]->name
843 if $diff_hash->{rename_table} && @{$diff_hash->{rename_table}};
845 return unless @stmts;
846 # Just zero or one stmts. return now
847 return (@drop_stmt,@stmts) unless @stmts > 1;
849 # Now strip off the 'ALTER TABLE xyz' of all but the first one
851 my $qt = $options->{quote_table_names} || '';
852 my $table_name = $qt . $table->name . $qt;
855 my $re = $renamed_from
856 ? qr/^ALTER TABLE (?:\Q$table_name\E|\Q$qt$renamed_from$qt\E) /
857 : qr/^ALTER TABLE \Q$table_name\E /;
859 my $first = shift @stmts;
860 my ($alter_table) = $first =~ /($re)/;
862 my $padd = " " x length($alter_table);
864 return @drop_stmt, join( ",\n", $first, map { s/$re//; $padd . $_ } @stmts);
869 my ($table, $options) = @_;
871 my $qt = $options->{quote_table_names} || '';
873 # Drop (foreign key) constraints so table drops cleanly
874 my @sql = batch_alter_table($table, { alter_drop_constraint => [ grep { $_->type eq 'FOREIGN KEY' } $table->get_constraints ] }, $options);
876 return (@sql, "DROP TABLE $qt$table$qt");
877 # return join("\n", @sql, "DROP TABLE $qt$table$qt");
882 my ($old_table, $new_table, $options) = @_;
884 my $qt = $options->{quote_table_names} || '';
886 return "ALTER TABLE $qt$old_table$qt RENAME TO $qt$new_table$qt";
889 sub next_unused_name {
890 my $name = shift || '';
891 if ( !defined($used_names{$name}) ) {
892 $used_names{$name} = $name;
897 while ( defined($used_names{$name . '_' . $i}) ) {
901 $used_names{$name} = $name;
907 # -------------------------------------------------------------------
913 SQL::Translator, http://www.mysql.com/.
917 darren chamberlain E<lt>darren@cpan.orgE<gt>,
918 Ken Y. Clark E<lt>kclark@cpan.orgE<gt>.