1 package SQL::Translator::Producer::MySQL;
3 # -------------------------------------------------------------------
4 # Copyright (C) 2002-2009 SQLFairy Authors
6 # This program is free software; you can redistribute it and/or
7 # modify it under the terms of the GNU General Public License as
8 # published by the Free Software Foundation; version 2.
10 # This program is distributed in the hope that it will be useful, but
11 # WITHOUT ANY WARRANTY; without even the implied warranty of
12 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
13 # General Public License for more details.
15 # You should have received a copy of the GNU General Public License
16 # along with this program; if not, write to the Free Software
17 # Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA
19 # -------------------------------------------------------------------
23 SQL::Translator::Producer::MySQL - MySQL-specific producer for SQL::Translator
27 Use via SQL::Translator:
31 my $t = SQL::Translator->new( parser => '...', producer => 'MySQL', '...' );
36 This module will produce text output of the schema suitable for MySQL.
37 There are still some issues to be worked out with syntax differences
38 between MySQL versions 3 and 4 ("SET foreign_key_checks," character sets
43 This producer takes a single optional producer_arg C<mysql_version>, which
44 provides the desired version for the target database. By default MySQL v3 is
45 assumed, and statements pertaining to any features introduced in later versions
46 (e.g. CREATE VIEW) are not produced.
48 Valid version specifiers for C<mysql_parser_version> are listed L<here|SQL::Translator::Utils/parse_mysql_version>
52 Normally the tables will be created without any explicit table type given and
53 so will use the MySQL default.
55 Any tables involved in foreign key constraints automatically get a table type
56 of InnoDB, unless this is overridden by setting the C<mysql_table_type> extra
57 attribute explicitly on the table.
59 =head2 Extra attributes.
61 The producer recognises the following extra attributes on the Schema objects.
67 Set the list of allowed values for Enum fields.
69 =item B<field.binary>, B<field.unsigned>, B<field.zerofill>
71 Set the MySQL field options of the same name.
73 =item B<field.renamed_from>, B<table.renamed_from>
75 Use when producing diffs to indicate that the current table/field has been
76 renamed from the old name as given in the attribute value.
78 =item B<table.mysql_table_type>
80 Set the type of the table e.g. 'InnoDB', 'MyISAM'. This will be
81 automatically set for tables involved in foreign key constraints if it is
82 not already set explicitly. See L<"Table Types">.
84 Please note that the C<ENGINE> option is the prefered method of specifying
85 the MySQL storage engine to use, but this method still works for backwards
88 =item B<table.mysql_charset>, B<table.mysql_collate>
90 Set the tables default charater set and collation order.
92 =item B<field.mysql_charset>, B<field.mysql_collate>
94 Set the fields charater set and collation order.
102 use vars qw[ $VERSION $DEBUG %used_names ];
104 $DEBUG = 0 unless defined $DEBUG;
106 # Maximum length for most identifiers is 64, according to:
107 # http://dev.mysql.com/doc/refman/4.1/en/identifiers.html
108 # http://dev.mysql.com/doc/refman/5.0/en/identifiers.html
109 my $DEFAULT_MAX_ID_LENGTH = 64;
112 use SQL::Translator::Schema::Constants;
113 use SQL::Translator::Utils qw(debug header_comment truncate_id_uniquely parse_mysql_version);
116 # Use only lowercase for the keys (e.g. "long" and not "LONG")
122 varchar2 => 'varchar',
138 'long integer' => 'integer',
140 'datetime' => 'datetime',
149 sub preprocess_schema {
152 # extra->{mysql_table_type} used to be the type. It belongs in options, so
153 # move it if we find it. Return Engine type if found in extra or options
154 # Similarly for mysql_charset and mysql_collate
155 my $extra_to_options = sub {
156 my ($table, $extra_name, $opt_name) = @_;
158 my $extra = $table->extra;
160 my $extra_type = delete $extra->{$extra_name};
162 # Now just to find if there is already an Engine or Type option...
163 # and lets normalize it to ENGINE since:
165 # The ENGINE table option specifies the storage engine for the table.
166 # TYPE is a synonym, but ENGINE is the preferred option name.
169 # We have to use the hash directly here since otherwise there is no way
171 my $options = ( $table->{options} ||= []);
173 # If multiple option names, normalize to the first one
175 OPT_NAME: for ( @$opt_name[1..$#$opt_name] ) {
176 for my $idx ( 0..$#{$options} ) {
177 my ($key, $value) = %{ $options->[$idx] };
180 $options->[$idx] = { $opt_name->[0] => $value };
185 $opt_name = $opt_name->[0];
190 # This assumes that there isn't both a Type and an Engine option.
192 for my $idx ( 0..$#{$options} ) {
193 my ($key, $value) = %{ $options->[$idx] };
195 next unless uc $key eq $opt_name;
197 # make sure case is right on option name
198 delete $options->[$idx]{$key};
199 return $options->[$idx]{$opt_name} = $value || $extra_type;
204 push @$options, { $opt_name => $extra_type };
210 # Names are only specific to a given schema
211 local %used_names = ();
214 # Work out which tables need to be InnoDB to support foreign key
215 # constraints. We do this first as we need InnoDB at both ends.
217 foreach my $table ( $schema->get_tables ) {
219 $extra_to_options->($table, 'mysql_table_type', ['ENGINE', 'TYPE'] );
220 $extra_to_options->($table, 'mysql_charset', 'CHARACTER SET' );
221 $extra_to_options->($table, 'mysql_collate', 'COLLATE' );
223 foreach my $c ( $table->get_constraints ) {
224 next unless $c->type eq FOREIGN_KEY;
226 # Normalize constraint names here.
227 my $c_name = $c->name;
228 # Give the constraint a name if it doesn't have one, so it doens't feel
230 $c_name = $table->name . '_fk' unless length $c_name;
232 $c->name( next_unused_name($c_name) );
234 for my $meth (qw/table reference_table/) {
235 my $table = $schema->get_table($c->$meth) || next;
236 # This normalizes the types to ENGINE and returns the value if its there
237 next if $extra_to_options->($table, 'mysql_table_type', ['ENGINE', 'TYPE']);
238 $table->options( { 'ENGINE' => 'InnoDB' } );
240 } # foreach constraints
242 my %map = ( mysql_collate => 'collate', mysql_charset => 'character set');
243 foreach my $f ( $table->get_fields ) {
244 my $extra = $f->extra;
246 $extra->{$map{$_}} = delete $extra->{$_} if exists $extra->{$_};
250 if ( !$size[0] && $f->data_type =~ /char$/ ) {
259 my $translator = shift;
260 local $DEBUG = $translator->debug;
262 my $no_comments = $translator->no_comments;
263 my $add_drop_table = $translator->add_drop_table;
264 my $schema = $translator->schema;
265 my $show_warnings = $translator->show_warnings || 0;
266 my $producer_args = $translator->producer_args;
267 my $mysql_version = parse_mysql_version ($producer_args->{mysql_version}, 'perl') || 0;
268 my $max_id_length = $producer_args->{mysql_max_id_length} || $DEFAULT_MAX_ID_LENGTH;
270 my ($qt, $qf, $qc) = ('','', '');
271 $qt = '`' if $translator->quote_table_names;
272 $qf = '`' if $translator->quote_field_names;
274 debug("PKG: Beginning production\n");
277 $create .= header_comment unless ($no_comments);
278 # \todo Don't set if MySQL 3.x is set on command line
279 my @create = "SET foreign_key_checks=0";
281 preprocess_schema($schema);
288 for my $table ( $schema->get_tables ) {
289 # print $table->name, "\n";
290 push @table_defs, create_table($table,
291 { add_drop_table => $add_drop_table,
292 show_warnings => $show_warnings,
293 no_comments => $no_comments,
294 quote_table_names => $qt,
295 quote_field_names => $qf,
296 max_id_length => $max_id_length,
297 mysql_version => $mysql_version
301 if ($mysql_version >= 5.000001) {
302 for my $view ( $schema->get_views ) {
303 push @table_defs, create_view($view,
304 { add_replace_view => $add_drop_table,
305 show_warnings => $show_warnings,
306 no_comments => $no_comments,
307 quote_table_names => $qt,
308 quote_field_names => $qf,
309 max_id_length => $max_id_length,
310 mysql_version => $mysql_version
316 # print "@table_defs\n";
317 push @table_defs, "SET foreign_key_checks=1";
319 return wantarray ? ($create ? $create : (), @create, @table_defs) : ($create . join('', map { $_ ? "$_;\n\n" : () } (@create, @table_defs)));
323 my ($view, $options) = @_;
324 my $qt = $options->{quote_table_names} || '';
325 my $qf = $options->{quote_field_names} || '';
327 my $view_name = $view->name;
328 debug("PKG: Looking at view '${view_name}'\n");
330 # Header. Should this look like what mysqldump produces?
332 $create .= "--\n-- View: ${qt}${view_name}${qt}\n--\n" unless $options->{no_comments};
334 $create .= ' OR REPLACE' if $options->{add_replace_view};
337 my $extra = $view->extra;
339 if( exists($extra->{mysql_algorithm}) && defined(my $algorithm = $extra->{mysql_algorithm}) ){
340 $create .= " ALGORITHM = ${algorithm}\n" if $algorithm =~ /(?:UNDEFINED|MERGE|TEMPTABLE)/i;
343 if( exists($extra->{mysql_definer}) && defined(my $user = $extra->{mysql_definer}) ){
344 $create .= " DEFINER = ${user}\n";
347 if( exists($extra->{mysql_security}) && defined(my $security = $extra->{mysql_security}) ){
348 $create .= " SQL SECURITY ${security}\n" if $security =~ /(?:DEFINER|INVOKER)/i;
352 $create .= " VIEW ${qt}${view_name}${qt}";
354 if( my @fields = $view->fields ){
355 my $list = join ', ', map { "${qf}${_}${qf}"} @fields;
356 $create .= " ( ${list} )";
358 if( my $sql = $view->sql ){
359 $create .= " AS (\n ${sql}\n )";
367 my ($table, $options) = @_;
369 my $qt = $options->{quote_table_names} || '';
370 my $qf = $options->{quote_field_names} || '';
372 my $table_name = quote_table_name($table->name, $qt);
373 debug("PKG: Looking at table '$table_name'\n");
376 # Header. Should this look like what mysqldump produces?
380 $create .= "--\n-- Table: $table_name\n--\n" unless $options->{no_comments};
381 $drop = qq[DROP TABLE IF EXISTS $table_name] if $options->{add_drop_table};
382 $create .= "CREATE TABLE $table_name (\n";
388 for my $field ( $table->get_fields ) {
389 push @field_defs, create_field($field, $options);
397 for my $index ( $table->get_indices ) {
398 push @index_defs, create_index($index, $options);
399 $indexed_fields{ $_ } = 1 for $index->fields;
403 # Constraints -- need to handle more than just FK. -ky
406 my @constraints = $table->get_constraints;
407 for my $c ( @constraints ) {
408 my $constr = create_constraint($c, $options);
409 push @constraint_defs, $constr if($constr);
411 unless ( $indexed_fields{ ($c->fields())[0] } || $c->type ne FOREIGN_KEY ) {
412 push @index_defs, "INDEX ($qf" . ($c->fields())[0] . "$qf)";
413 $indexed_fields{ ($c->fields())[0] } = 1;
417 $create .= join(",\n", map { " $_" }
418 @field_defs, @index_defs, @constraint_defs
425 $create .= generate_table_options($table, $options) || '';
426 # $create .= ";\n\n";
428 return $drop ? ($drop,$create) : $create;
431 sub quote_table_name {
432 my ($table_name, $qt) = @_;
434 $table_name =~ s/\./$qt.$qt/g;
436 return "$qt$table_name$qt";
439 sub generate_table_options
441 my ($table, $options) = @_;
444 my $table_type_defined = 0;
445 my $qf = $options->{quote_field_names} ||= '';
446 my $charset = $table->extra('mysql_charset');
447 my $collate = $table->extra('mysql_collate');
449 for my $t1_option_ref ( $table->options ) {
450 my($key, $value) = %{$t1_option_ref};
451 $table_type_defined = 1
452 if uc $key eq 'ENGINE' or uc $key eq 'TYPE';
453 if (uc $key eq 'CHARACTER SET') {
456 } elsif (uc $key eq 'COLLATE') {
459 } elsif (uc $key eq 'UNION') {
460 $union = "($qf". join("$qf, $qf", @$value) ."$qf)";
463 $create .= " $key=$value";
466 my $mysql_table_type = $table->extra('mysql_table_type');
467 $create .= " ENGINE=$mysql_table_type"
468 if $mysql_table_type && !$table_type_defined;
469 my $comments = $table->comments;
471 $create .= " DEFAULT CHARACTER SET $charset" if $charset;
472 $create .= " COLLATE $collate" if $collate;
473 $create .= " UNION=$union" if $union;
474 $create .= qq[ comment='$comments'] if $comments;
480 my ($field, $options) = @_;
482 my $qf = $options->{quote_field_names} ||= '';
484 my $field_name = $field->name;
485 debug("PKG: Looking at field '$field_name'\n");
486 my $field_def = "$qf$field_name$qf";
489 my $data_type = $field->data_type;
490 my @size = $field->size;
491 my %extra = $field->extra;
492 my $list = $extra{'list'} || [];
493 # \todo deal with embedded quotes
494 my $commalist = join( ', ', map { qq['$_'] } @$list );
495 my $charset = $extra{'mysql_charset'};
496 my $collate = $extra{'mysql_collate'};
498 my $mysql_version = $options->{mysql_version} || 0;
500 # Oracle "number" type -- figure best MySQL type
502 if ( lc $data_type eq 'number' ) {
504 if ( scalar @size > 1 ) {
505 $data_type = 'double';
507 elsif ( $size[0] && $size[0] >= 12 ) {
508 $data_type = 'bigint';
510 elsif ( $size[0] && $size[0] <= 1 ) {
511 $data_type = 'tinyint';
518 # Convert a large Oracle varchar to "text"
519 # (not necessary as of 5.0.3 http://dev.mysql.com/doc/refman/5.0/en/char.html)
521 elsif ( $data_type =~ /char/i && $size[0] > 255 ) {
522 unless ($size[0] <= 65535 && $mysql_version >= 5.000003 ) {
527 elsif ( $data_type =~ /boolean/i ) {
528 if ($mysql_version >= 4) {
529 $data_type = 'boolean';
532 $commalist = "'0','1'";
535 elsif ( exists $translate{ lc $data_type } ) {
536 $data_type = $translate{ lc $data_type };
539 @size = () if $data_type =~ /(text|blob)/i;
541 if ( $data_type =~ /(double|float)/ && scalar @size == 1 ) {
545 $field_def .= " $data_type";
547 if ( lc($data_type) eq 'enum' || lc($data_type) eq 'set') {
548 $field_def .= '(' . $commalist . ')';
550 elsif ( defined $size[0] && $size[0] > 0 ) {
551 $field_def .= '(' . join( ', ', @size ) . ')';
555 $field_def .= " CHARACTER SET $charset" if $charset;
556 $field_def .= " COLLATE $collate" if $collate;
559 for my $qual ( qw[ binary unsigned zerofill ] ) {
560 my $val = $extra{ $qual } || $extra{ uc $qual } or next;
561 $field_def .= " $qual";
563 for my $qual ( 'character set', 'collate', 'on update' ) {
564 my $val = $extra{ $qual } || $extra{ uc $qual } or next;
565 $field_def .= " $qual $val";
569 $field_def .= ' NOT NULL' unless $field->is_nullable;
571 # Default? XXX Need better quoting!
572 my $default = $field->default_value;
573 if ( defined $default ) {
574 SQL::Translator::Producer->_apply_default_value(
583 if ( my $comments = $field->comments ) {
584 $field_def .= qq[ comment '$comments'];
588 $field_def .= " auto_increment" if $field->is_auto_increment;
593 sub alter_create_index
595 my ($index, $options) = @_;
597 my $qt = $options->{quote_table_names} || '';
598 my $qf = $options->{quote_field_names} || '';
602 $qt.$index->table->name.$qt,
610 my ($index, $options) = @_;
612 my $qf = $options->{quote_field_names} || '';
615 lc $index->type eq 'normal' ? 'INDEX' : $index->type . ' INDEX',
616 truncate_id_uniquely( $index->name, $options->{max_id_length} || $DEFAULT_MAX_ID_LENGTH ),
617 '(' . $qf . join( "$qf, $qf", $index->fields ) . $qf . ')'
624 my ($index, $options) = @_;
626 my $qt = $options->{quote_table_names} || '';
627 my $qf = $options->{quote_field_names} || '';
631 $qt.$index->table->name.$qt,
634 $index->name || $index->fields
639 sub alter_drop_constraint
641 my ($c, $options) = @_;
643 my $qt = $options->{quote_table_names} || '';
644 my $qc = $options->{quote_field_names} || '';
646 my $out = sprintf('ALTER TABLE %s DROP %s %s',
647 $qt . $c->table->name . $qt,
648 $c->type eq FOREIGN_KEY ? $c->type : "INDEX",
649 $qc . $c->name . $qc );
654 sub alter_create_constraint
656 my ($index, $options) = @_;
658 my $qt = $options->{quote_table_names} || '';
661 $qt.$index->table->name.$qt,
663 create_constraint(@_) );
666 sub create_constraint
668 my ($c, $options) = @_;
670 my $qf = $options->{quote_field_names} || '';
671 my $qt = $options->{quote_table_names} || '';
672 my $leave_name = $options->{leave_name} || undef;
674 my @fields = $c->fields or next;
676 if ( $c->type eq PRIMARY_KEY ) {
677 return 'PRIMARY KEY (' . $qf . join("$qf, $qf", @fields). $qf . ')';
679 elsif ( $c->type eq UNIQUE ) {
682 (defined $c->name ? $qf.truncate_id_uniquely( $c->name, $options->{max_id_length} || $DEFAULT_MAX_ID_LENGTH ).$qf.' ' : '').
683 '(' . $qf . join("$qf, $qf", @fields). $qf . ')';
685 elsif ( $c->type eq FOREIGN_KEY ) {
687 # Make sure FK field is indexed or MySQL complains.
690 my $table = $c->table;
691 my $c_name = truncate_id_uniquely( $c->name, $options->{max_id_length} || $DEFAULT_MAX_ID_LENGTH );
701 $def .= ' ('.$qf . join( "$qf, $qf", @fields ) . $qf . ')';
703 $def .= ' REFERENCES ' . $qt . $c->reference_table . $qt;
705 my @rfields = map { $_ || () } $c->reference_fields;
706 unless ( @rfields ) {
707 my $rtable_name = $c->reference_table;
708 if ( my $ref_table = $table->schema->get_table( $rtable_name ) ) {
709 push @rfields, $ref_table->primary_key;
712 warn "Can't find reference table '$rtable_name' " .
713 "in schema\n" if $options->{show_warnings};
718 $def .= ' (' . $qf . join( "$qf, $qf", @rfields ) . $qf . ')';
721 warn "FK constraint on " . $table->name . '.' .
722 join('', @fields) . " has no reference fields\n"
723 if $options->{show_warnings};
726 if ( $c->match_type ) {
728 ( $c->match_type =~ /full/i ) ? 'FULL' : 'PARTIAL';
731 if ( $c->on_delete ) {
732 $def .= ' ON DELETE '.join( ' ', $c->on_delete );
735 if ( $c->on_update ) {
736 $def .= ' ON UPDATE '.join( ' ', $c->on_update );
746 my ($to_table, $options) = @_;
748 my $qt = $options->{quote_table_names} || '';
750 my $table_options = generate_table_options($to_table, $options) || '';
751 my $out = sprintf('ALTER TABLE %s%s',
752 $qt . $to_table->name . $qt,
758 sub rename_field { alter_field(@_) }
761 my ($from_field, $to_field, $options) = @_;
763 my $qf = $options->{quote_field_names} || '';
764 my $qt = $options->{quote_table_names} || '';
766 my $out = sprintf('ALTER TABLE %s CHANGE COLUMN %s %s',
767 $qt . $to_field->table->name . $qt,
768 $qf . $from_field->name . $qf,
769 create_field($to_field, $options));
776 my ($new_field, $options) = @_;
778 my $qt = $options->{quote_table_names} || '';
780 my $out = sprintf('ALTER TABLE %s ADD COLUMN %s',
781 $qt . $new_field->table->name . $qt,
782 create_field($new_field, $options));
790 my ($old_field, $options) = @_;
792 my $qf = $options->{quote_field_names} || '';
793 my $qt = $options->{quote_table_names} || '';
795 my $out = sprintf('ALTER TABLE %s DROP COLUMN %s',
796 $qt . $old_field->table->name . $qt,
797 $qf . $old_field->name . $qf);
803 sub batch_alter_table {
804 my ($table, $diff_hash, $options) = @_;
806 # InnoDB has an issue with dropping and re-adding a FK constraint under the
807 # name in a single alter statment, see: http://bugs.mysql.com/bug.php?id=13741
809 # We have to work round this.
812 my %fks_to_drop = map {
813 $_->type eq FOREIGN_KEY
816 } @{$diff_hash->{alter_drop_constraint} };
818 my %fks_to_create = map {
819 if ( $_->type eq FOREIGN_KEY) {
820 $fks_to_alter{$_->name} = $fks_to_drop{$_->name} if $fks_to_drop{$_->name};
823 } @{$diff_hash->{alter_create_constraint} };
826 if (scalar keys %fks_to_alter) {
827 $diff_hash->{alter_drop_constraint} = [
828 grep { !$fks_to_alter{$_->name} } @{ $diff_hash->{alter_drop_constraint} }
831 @drop_stmt = batch_alter_table($table, { alter_drop_constraint => [ values %fks_to_alter ] }, $options);
836 if (@{ $diff_hash->{$_} || [] }) {
837 my $meth = __PACKAGE__->can($_) or die __PACKAGE__ . " cant $_";
838 map { $meth->( (ref $_ eq 'ARRAY' ? @$_ : $_), $options ) } @{ $diff_hash->{$_} }
841 alter_drop_constraint
848 alter_create_constraint
851 # rename_table makes things a bit more complex
852 my $renamed_from = "";
853 $renamed_from = $diff_hash->{rename_table}[0][0]->name
854 if $diff_hash->{rename_table} && @{$diff_hash->{rename_table}};
856 return unless @stmts;
857 # Just zero or one stmts. return now
858 return (@drop_stmt,@stmts) unless @stmts > 1;
860 # Now strip off the 'ALTER TABLE xyz' of all but the first one
862 my $qt = $options->{quote_table_names} || '';
863 my $table_name = $qt . $table->name . $qt;
866 my $re = $renamed_from
867 ? qr/^ALTER TABLE (?:\Q$table_name\E|\Q$qt$renamed_from$qt\E) /
868 : qr/^ALTER TABLE \Q$table_name\E /;
870 my $first = shift @stmts;
871 my ($alter_table) = $first =~ /($re)/;
873 my $padd = " " x length($alter_table);
875 return @drop_stmt, join( ",\n", $first, map { s/$re//; $padd . $_ } @stmts);
880 my ($table, $options) = @_;
882 my $qt = $options->{quote_table_names} || '';
884 # Drop (foreign key) constraints so table drops cleanly
885 my @sql = batch_alter_table($table, { alter_drop_constraint => [ grep { $_->type eq 'FOREIGN KEY' } $table->get_constraints ] }, $options);
887 return (@sql, "DROP TABLE $qt$table$qt");
888 # return join("\n", @sql, "DROP TABLE $qt$table$qt");
893 my ($old_table, $new_table, $options) = @_;
895 my $qt = $options->{quote_table_names} || '';
897 return "ALTER TABLE $qt$old_table$qt RENAME TO $qt$new_table$qt";
900 sub next_unused_name {
901 my $name = shift || '';
902 if ( !defined($used_names{$name}) ) {
903 $used_names{$name} = $name;
908 while ( defined($used_names{$name . '_' . $i}) ) {
912 $used_names{$name} = $name;
918 # -------------------------------------------------------------------
924 SQL::Translator, http://www.mysql.com/.
928 darren chamberlain E<lt>darren@cpan.orgE<gt>,
929 Ken Y. Clark E<lt>kclark@cpan.orgE<gt>.