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 = $table->name;
373 debug("PKG: Looking at table '$table_name'\n");
376 # Header. Should this look like what mysqldump produces?
380 $create .= "--\n-- Table: $qt$table_name$qt\n--\n" unless $options->{no_comments};
381 $drop = qq[DROP TABLE IF EXISTS $qt$table_name$qt] if $options->{add_drop_table};
382 $create .= "CREATE TABLE $qt$table_name$qt (\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 generate_table_options
433 my ($table, $options) = @_;
436 my $table_type_defined = 0;
437 my $qf = $options->{quote_field_names} ||= '';
438 my $charset = $table->extra('mysql_charset');
439 my $collate = $table->extra('mysql_collate');
441 for my $t1_option_ref ( $table->options ) {
442 my($key, $value) = %{$t1_option_ref};
443 $table_type_defined = 1
444 if uc $key eq 'ENGINE' or uc $key eq 'TYPE';
445 if (uc $key eq 'CHARACTER SET') {
448 } elsif (uc $key eq 'COLLATE') {
451 } elsif (uc $key eq 'UNION') {
452 $union = "($qf". join("$qf, $qf", @$value) ."$qf)";
455 $create .= " $key=$value";
458 my $mysql_table_type = $table->extra('mysql_table_type');
459 $create .= " ENGINE=$mysql_table_type"
460 if $mysql_table_type && !$table_type_defined;
461 my $comments = $table->comments;
463 $create .= " DEFAULT CHARACTER SET $charset" if $charset;
464 $create .= " COLLATE $collate" if $collate;
465 $create .= " UNION=$union" if $union;
466 $create .= qq[ comment='$comments'] if $comments;
472 my ($field, $options) = @_;
474 my $qf = $options->{quote_field_names} ||= '';
476 my $field_name = $field->name;
477 debug("PKG: Looking at field '$field_name'\n");
478 my $field_def = "$qf$field_name$qf";
481 my $data_type = $field->data_type;
482 my @size = $field->size;
483 my %extra = $field->extra;
484 my $list = $extra{'list'} || [];
485 # \todo deal with embedded quotes
486 my $commalist = join( ', ', map { qq['$_'] } @$list );
487 my $charset = $extra{'mysql_charset'};
488 my $collate = $extra{'mysql_collate'};
490 my $mysql_version = $options->{mysql_version} || 0;
492 # Oracle "number" type -- figure best MySQL type
494 if ( lc $data_type eq 'number' ) {
496 if ( scalar @size > 1 ) {
497 $data_type = 'double';
499 elsif ( $size[0] && $size[0] >= 12 ) {
500 $data_type = 'bigint';
502 elsif ( $size[0] && $size[0] <= 1 ) {
503 $data_type = 'tinyint';
510 # Convert a large Oracle varchar to "text"
511 # (not necessary as of 5.0.3 http://dev.mysql.com/doc/refman/5.0/en/char.html)
513 elsif ( $data_type =~ /char/i && $size[0] > 255 ) {
514 unless ($size[0] <= 65535 && $mysql_version >= 5.000003 ) {
519 elsif ( $data_type =~ /boolean/i ) {
520 if ($mysql_version >= 4) {
521 $data_type = 'boolean';
524 $commalist = "'0','1'";
527 elsif ( exists $translate{ lc $data_type } ) {
528 $data_type = $translate{ lc $data_type };
531 @size = () if $data_type =~ /(text|blob)/i;
533 if ( $data_type =~ /(double|float)/ && scalar @size == 1 ) {
537 $field_def .= " $data_type";
539 if ( lc($data_type) eq 'enum' || lc($data_type) eq 'set') {
540 $field_def .= '(' . $commalist . ')';
542 elsif ( defined $size[0] && $size[0] > 0 ) {
543 $field_def .= '(' . join( ', ', @size ) . ')';
547 $field_def .= " CHARACTER SET $charset" if $charset;
548 $field_def .= " COLLATE $collate" if $collate;
551 for my $qual ( qw[ binary unsigned zerofill ] ) {
552 my $val = $extra{ $qual } || $extra{ uc $qual } or next;
553 $field_def .= " $qual";
555 for my $qual ( 'character set', 'collate', 'on update' ) {
556 my $val = $extra{ $qual } || $extra{ uc $qual } or next;
557 $field_def .= " $qual $val";
561 $field_def .= ' NOT NULL' unless $field->is_nullable;
563 # Default? XXX Need better quoting!
564 my $default = $field->default_value;
565 if ( defined $default ) {
566 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>.