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_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 # Column types that do not support lenth attribute
151 my @no_length_attr = qw/
152 date time timestamp datetime year
156 sub preprocess_schema {
159 # extra->{mysql_table_type} used to be the type. It belongs in options, so
160 # move it if we find it. Return Engine type if found in extra or options
161 # Similarly for mysql_charset and mysql_collate
162 my $extra_to_options = sub {
163 my ($table, $extra_name, $opt_name) = @_;
165 my $extra = $table->extra;
167 my $extra_type = delete $extra->{$extra_name};
169 # Now just to find if there is already an Engine or Type option...
170 # and lets normalize it to ENGINE since:
172 # The ENGINE table option specifies the storage engine for the table.
173 # TYPE is a synonym, but ENGINE is the preferred option name.
176 # We have to use the hash directly here since otherwise there is no way
178 my $options = ( $table->{options} ||= []);
180 # If multiple option names, normalize to the first one
182 OPT_NAME: for ( @$opt_name[1..$#$opt_name] ) {
183 for my $idx ( 0..$#{$options} ) {
184 my ($key, $value) = %{ $options->[$idx] };
187 $options->[$idx] = { $opt_name->[0] => $value };
192 $opt_name = $opt_name->[0];
197 # This assumes that there isn't both a Type and an Engine option.
199 for my $idx ( 0..$#{$options} ) {
200 my ($key, $value) = %{ $options->[$idx] };
202 next unless uc $key eq $opt_name;
204 # make sure case is right on option name
205 delete $options->[$idx]{$key};
206 return $options->[$idx]{$opt_name} = $value || $extra_type;
211 push @$options, { $opt_name => $extra_type };
217 # Names are only specific to a given schema
218 local %used_names = ();
221 # Work out which tables need to be InnoDB to support foreign key
222 # constraints. We do this first as we need InnoDB at both ends.
224 foreach my $table ( $schema->get_tables ) {
226 $extra_to_options->($table, 'mysql_table_type', ['ENGINE', 'TYPE'] );
227 $extra_to_options->($table, 'mysql_charset', 'CHARACTER SET' );
228 $extra_to_options->($table, 'mysql_collate', 'COLLATE' );
230 foreach my $c ( $table->get_constraints ) {
231 next unless $c->type eq FOREIGN_KEY;
233 # Normalize constraint names here.
234 my $c_name = $c->name;
235 # Give the constraint a name if it doesn't have one, so it doens't feel
237 $c_name = $table->name . '_fk' unless length $c_name;
239 $c->name( next_unused_name($c_name) );
241 for my $meth (qw/table reference_table/) {
242 my $table = $schema->get_table($c->$meth) || next;
243 # This normalizes the types to ENGINE and returns the value if its there
244 next if $extra_to_options->($table, 'mysql_table_type', ['ENGINE', 'TYPE']);
245 $table->options( { 'ENGINE' => 'InnoDB' } );
247 } # foreach constraints
249 my %map = ( mysql_collate => 'collate', mysql_charset => 'character set');
250 foreach my $f ( $table->get_fields ) {
251 my $extra = $f->extra;
253 $extra->{$map{$_}} = delete $extra->{$_} if exists $extra->{$_};
257 if ( !$size[0] && $f->data_type =~ /char$/ ) {
266 my $translator = shift;
267 local $DEBUG = $translator->debug;
269 my $no_comments = $translator->no_comments;
270 my $add_drop_table = $translator->add_drop_table;
271 my $schema = $translator->schema;
272 my $show_warnings = $translator->show_warnings || 0;
273 my $producer_args = $translator->producer_args;
274 my $mysql_version = parse_mysql_version ($producer_args->{mysql_version}, 'perl') || 0;
275 my $max_id_length = $producer_args->{mysql_max_id_length} || $DEFAULT_MAX_ID_LENGTH;
277 my ($qt, $qf, $qc) = ('','', '');
278 $qt = '`' if $translator->quote_table_names;
279 $qf = '`' if $translator->quote_field_names;
281 debug("PKG: Beginning production\n");
284 $create .= header_comment unless ($no_comments);
285 # \todo Don't set if MySQL 3.x is set on command line
286 my @create = "SET foreign_key_checks=0";
288 preprocess_schema($schema);
295 for my $table ( $schema->get_tables ) {
296 # print $table->name, "\n";
297 push @table_defs, create_table($table,
298 { add_drop_table => $add_drop_table,
299 show_warnings => $show_warnings,
300 no_comments => $no_comments,
301 quote_table_names => $qt,
302 quote_field_names => $qf,
303 max_id_length => $max_id_length,
304 mysql_version => $mysql_version
308 if ($mysql_version >= 5.000001) {
309 for my $view ( $schema->get_views ) {
310 push @table_defs, create_view($view,
311 { add_replace_view => $add_drop_table,
312 show_warnings => $show_warnings,
313 no_comments => $no_comments,
314 quote_table_names => $qt,
315 quote_field_names => $qf,
316 max_id_length => $max_id_length,
317 mysql_version => $mysql_version
323 # print "@table_defs\n";
324 push @table_defs, "SET foreign_key_checks=1";
326 return wantarray ? ($create ? $create : (), @create, @table_defs) : ($create . join('', map { $_ ? "$_;\n\n" : () } (@create, @table_defs)));
330 my ($view, $options) = @_;
331 my $qt = $options->{quote_table_names} || '';
332 my $qf = $options->{quote_field_names} || '';
334 my $view_name = $view->name;
335 debug("PKG: Looking at view '${view_name}'\n");
337 # Header. Should this look like what mysqldump produces?
339 $create .= "--\n-- View: ${qt}${view_name}${qt}\n--\n" unless $options->{no_comments};
341 $create .= ' OR REPLACE' if $options->{add_replace_view};
344 my $extra = $view->extra;
346 if( exists($extra->{mysql_algorithm}) && defined(my $algorithm = $extra->{mysql_algorithm}) ){
347 $create .= " ALGORITHM = ${algorithm}\n" if $algorithm =~ /(?:UNDEFINED|MERGE|TEMPTABLE)/i;
350 if( exists($extra->{mysql_definer}) && defined(my $user = $extra->{mysql_definer}) ){
351 $create .= " DEFINER = ${user}\n";
354 if( exists($extra->{mysql_security}) && defined(my $security = $extra->{mysql_security}) ){
355 $create .= " SQL SECURITY ${security}\n" if $security =~ /(?:DEFINER|INVOKER)/i;
359 $create .= " VIEW ${qt}${view_name}${qt}";
361 if( my @fields = $view->fields ){
362 my $list = join ', ', map { "${qf}${_}${qf}"} @fields;
363 $create .= " ( ${list} )";
365 if( my $sql = $view->sql ){
366 $create .= " AS (\n ${sql}\n )";
374 my ($table, $options) = @_;
376 my $qt = $options->{quote_table_names} || '';
377 my $qf = $options->{quote_field_names} || '';
379 my $table_name = quote_table_name($table->name, $qt);
380 debug("PKG: Looking at table '$table_name'\n");
383 # Header. Should this look like what mysqldump produces?
387 $create .= "--\n-- Table: $table_name\n--\n" unless $options->{no_comments};
388 $drop = qq[DROP TABLE IF EXISTS $table_name] if $options->{add_drop_table};
389 $create .= "CREATE TABLE $table_name (\n";
395 for my $field ( $table->get_fields ) {
396 push @field_defs, create_field($field, $options);
404 for my $index ( $table->get_indices ) {
405 push @index_defs, create_index($index, $options);
406 $indexed_fields{ $_ } = 1 for $index->fields;
410 # Constraints -- need to handle more than just FK. -ky
413 my @constraints = $table->get_constraints;
414 for my $c ( @constraints ) {
415 my $constr = create_constraint($c, $options);
416 push @constraint_defs, $constr if($constr);
418 unless ( $indexed_fields{ ($c->fields())[0] } || $c->type ne FOREIGN_KEY ) {
419 push @index_defs, "INDEX ($qf" . ($c->fields())[0] . "$qf)";
420 $indexed_fields{ ($c->fields())[0] } = 1;
424 $create .= join(",\n", map { " $_" }
425 @field_defs, @index_defs, @constraint_defs
432 $create .= generate_table_options($table, $options) || '';
433 # $create .= ";\n\n";
435 return $drop ? ($drop,$create) : $create;
438 sub quote_table_name {
439 my ($table_name, $qt) = @_;
441 $table_name =~ s/\./$qt.$qt/g;
443 return "$qt$table_name$qt";
446 sub generate_table_options
448 my ($table, $options) = @_;
451 my $table_type_defined = 0;
452 my $qf = $options->{quote_field_names} ||= '';
453 my $charset = $table->extra('mysql_charset');
454 my $collate = $table->extra('mysql_collate');
456 for my $t1_option_ref ( $table->options ) {
457 my($key, $value) = %{$t1_option_ref};
458 $table_type_defined = 1
459 if uc $key eq 'ENGINE' or uc $key eq 'TYPE';
460 if (uc $key eq 'CHARACTER SET') {
463 } elsif (uc $key eq 'COLLATE') {
466 } elsif (uc $key eq 'UNION') {
467 $union = "($qf". join("$qf, $qf", @$value) ."$qf)";
470 $create .= " $key=$value";
473 my $mysql_table_type = $table->extra('mysql_table_type');
474 $create .= " ENGINE=$mysql_table_type"
475 if $mysql_table_type && !$table_type_defined;
476 my $comments = $table->comments;
478 $create .= " DEFAULT CHARACTER SET $charset" if $charset;
479 $create .= " COLLATE $collate" if $collate;
480 $create .= " UNION=$union" if $union;
481 $create .= qq[ comment='$comments'] if $comments;
487 my ($field, $options) = @_;
489 my $qf = $options->{quote_field_names} ||= '';
491 my $field_name = $field->name;
492 debug("PKG: Looking at field '$field_name'\n");
493 my $field_def = "$qf$field_name$qf";
496 my $data_type = $field->data_type;
497 my @size = $field->size;
498 my %extra = $field->extra;
499 my $list = $extra{'list'} || [];
500 # \todo deal with embedded quotes
501 my $commalist = join( ', ', map { qq['$_'] } @$list );
502 my $charset = $extra{'mysql_charset'};
503 my $collate = $extra{'mysql_collate'};
505 my $mysql_version = $options->{mysql_version} || 0;
507 # Oracle "number" type -- figure best MySQL type
509 if ( lc $data_type eq 'number' ) {
511 if ( scalar @size > 1 ) {
512 $data_type = 'double';
514 elsif ( $size[0] && $size[0] >= 12 ) {
515 $data_type = 'bigint';
517 elsif ( $size[0] && $size[0] <= 1 ) {
518 $data_type = 'tinyint';
525 # Convert a large Oracle varchar to "text"
526 # (not necessary as of 5.0.3 http://dev.mysql.com/doc/refman/5.0/en/char.html)
528 elsif ( $data_type =~ /char/i && $size[0] > 255 ) {
529 unless ($size[0] <= 65535 && $mysql_version >= 5.000003 ) {
534 elsif ( $data_type =~ /boolean/i ) {
535 if ($mysql_version >= 4) {
536 $data_type = 'boolean';
539 $commalist = "'0','1'";
542 elsif ( exists $translate{ lc $data_type } ) {
543 $data_type = $translate{ lc $data_type };
546 @size = () if $data_type =~ /(text|blob)/i;
548 if ( $data_type =~ /(double|float)/ && scalar @size == 1 ) {
552 $field_def .= " $data_type";
554 if ( lc($data_type) eq 'enum' || lc($data_type) eq 'set') {
555 $field_def .= '(' . $commalist . ')';
557 elsif ( defined $size[0] && $size[0] > 0 && ! grep $data_type eq $_, @no_length_attr ) {
558 $field_def .= '(' . join( ', ', @size ) . ')';
562 $field_def .= " CHARACTER SET $charset" if $charset;
563 $field_def .= " COLLATE $collate" if $collate;
566 for my $qual ( qw[ binary unsigned zerofill ] ) {
567 my $val = $extra{ $qual } || $extra{ uc $qual } or next;
568 $field_def .= " $qual";
570 for my $qual ( 'character set', 'collate', 'on update' ) {
571 my $val = $extra{ $qual } || $extra{ uc $qual } or next;
572 $field_def .= " $qual $val";
576 $field_def .= ' NOT NULL' unless $field->is_nullable;
578 # Default? XXX Need better quoting!
579 my $default = $field->default_value;
580 if ( defined $default ) {
581 SQL::Translator::Producer->_apply_default_value(
590 if ( my $comments = $field->comments ) {
591 $field_def .= qq[ comment '$comments'];
595 $field_def .= " auto_increment" if $field->is_auto_increment;
600 sub alter_create_index
602 my ($index, $options) = @_;
604 my $qt = $options->{quote_table_names} || '';
605 my $qf = $options->{quote_field_names} || '';
609 $qt.$index->table->name.$qt,
617 my ($index, $options) = @_;
619 my $qf = $options->{quote_field_names} || '';
622 lc $index->type eq 'normal'
624 : $index->type . ' INDEX'
627 ? (truncate_id_uniquely( $index->name, $options->{max_id_length} || $DEFAULT_MAX_ID_LENGTH ) )
630 '(' . $qf . join( "$qf, $qf", $index->fields ) . $qf . ')'
637 my ($index, $options) = @_;
639 my $qt = $options->{quote_table_names} || '';
640 my $qf = $options->{quote_field_names} || '';
644 $qt.$index->table->name.$qt,
647 $index->name || $index->fields
652 sub alter_drop_constraint
654 my ($c, $options) = @_;
656 my $qt = $options->{quote_table_names} || '';
657 my $qc = $options->{quote_field_names} || '';
659 my $out = sprintf('ALTER TABLE %s DROP %s %s',
660 $qt . $c->table->name . $qt,
661 $c->type eq FOREIGN_KEY ? $c->type : "INDEX",
662 $qc . $c->name . $qc );
667 sub alter_create_constraint
669 my ($index, $options) = @_;
671 my $qt = $options->{quote_table_names} || '';
674 $qt.$index->table->name.$qt,
676 create_constraint(@_) );
679 sub create_constraint
681 my ($c, $options) = @_;
683 my $qf = $options->{quote_field_names} || '';
684 my $qt = $options->{quote_table_names} || '';
685 my $leave_name = $options->{leave_name} || undef;
687 my @fields = $c->fields or next;
689 if ( $c->type eq PRIMARY_KEY ) {
690 return 'PRIMARY KEY (' . $qf . join("$qf, $qf", @fields). $qf . ')';
692 elsif ( $c->type eq UNIQUE ) {
695 (defined $c->name ? $qf.truncate_id_uniquely( $c->name, $options->{max_id_length} || $DEFAULT_MAX_ID_LENGTH ).$qf.' ' : '').
696 '(' . $qf . join("$qf, $qf", @fields). $qf . ')';
698 elsif ( $c->type eq FOREIGN_KEY ) {
700 # Make sure FK field is indexed or MySQL complains.
703 my $table = $c->table;
704 my $c_name = truncate_id_uniquely( $c->name, $options->{max_id_length} || $DEFAULT_MAX_ID_LENGTH );
714 $def .= ' ('.$qf . join( "$qf, $qf", @fields ) . $qf . ')';
716 $def .= ' REFERENCES ' . $qt . $c->reference_table . $qt;
718 my @rfields = map { $_ || () } $c->reference_fields;
719 unless ( @rfields ) {
720 my $rtable_name = $c->reference_table;
721 if ( my $ref_table = $table->schema->get_table( $rtable_name ) ) {
722 push @rfields, $ref_table->primary_key;
725 warn "Can't find reference table '$rtable_name' " .
726 "in schema\n" if $options->{show_warnings};
731 $def .= ' (' . $qf . join( "$qf, $qf", @rfields ) . $qf . ')';
734 warn "FK constraint on " . $table->name . '.' .
735 join('', @fields) . " has no reference fields\n"
736 if $options->{show_warnings};
739 if ( $c->match_type ) {
741 ( $c->match_type =~ /full/i ) ? 'FULL' : 'PARTIAL';
744 if ( $c->on_delete ) {
745 $def .= ' ON DELETE '.join( ' ', $c->on_delete );
748 if ( $c->on_update ) {
749 $def .= ' ON UPDATE '.join( ' ', $c->on_update );
759 my ($to_table, $options) = @_;
761 my $qt = $options->{quote_table_names} || '';
763 my $table_options = generate_table_options($to_table, $options) || '';
764 my $out = sprintf('ALTER TABLE %s%s',
765 $qt . $to_table->name . $qt,
771 sub rename_field { alter_field(@_) }
774 my ($from_field, $to_field, $options) = @_;
776 my $qf = $options->{quote_field_names} || '';
777 my $qt = $options->{quote_table_names} || '';
779 my $out = sprintf('ALTER TABLE %s CHANGE COLUMN %s %s',
780 $qt . $to_field->table->name . $qt,
781 $qf . $from_field->name . $qf,
782 create_field($to_field, $options));
789 my ($new_field, $options) = @_;
791 my $qt = $options->{quote_table_names} || '';
793 my $out = sprintf('ALTER TABLE %s ADD COLUMN %s',
794 $qt . $new_field->table->name . $qt,
795 create_field($new_field, $options));
803 my ($old_field, $options) = @_;
805 my $qf = $options->{quote_field_names} || '';
806 my $qt = $options->{quote_table_names} || '';
808 my $out = sprintf('ALTER TABLE %s DROP COLUMN %s',
809 $qt . $old_field->table->name . $qt,
810 $qf . $old_field->name . $qf);
816 sub batch_alter_table {
817 my ($table, $diff_hash, $options) = @_;
819 # InnoDB has an issue with dropping and re-adding a FK constraint under the
820 # name in a single alter statment, see: http://bugs.mysql.com/bug.php?id=13741
822 # We have to work round this.
825 my %fks_to_drop = map {
826 $_->type eq FOREIGN_KEY
829 } @{$diff_hash->{alter_drop_constraint} };
831 my %fks_to_create = map {
832 if ( $_->type eq FOREIGN_KEY) {
833 $fks_to_alter{$_->name} = $fks_to_drop{$_->name} if $fks_to_drop{$_->name};
836 } @{$diff_hash->{alter_create_constraint} };
839 if (scalar keys %fks_to_alter) {
840 $diff_hash->{alter_drop_constraint} = [
841 grep { !$fks_to_alter{$_->name} } @{ $diff_hash->{alter_drop_constraint} }
844 @drop_stmt = batch_alter_table($table, { alter_drop_constraint => [ values %fks_to_alter ] }, $options);
849 if (@{ $diff_hash->{$_} || [] }) {
850 my $meth = __PACKAGE__->can($_) or die __PACKAGE__ . " cant $_";
851 map { $meth->( (ref $_ eq 'ARRAY' ? @$_ : $_), $options ) } @{ $diff_hash->{$_} }
854 alter_drop_constraint
861 alter_create_constraint
864 # rename_table makes things a bit more complex
865 my $renamed_from = "";
866 $renamed_from = $diff_hash->{rename_table}[0][0]->name
867 if $diff_hash->{rename_table} && @{$diff_hash->{rename_table}};
869 return unless @stmts;
870 # Just zero or one stmts. return now
871 return (@drop_stmt,@stmts) unless @stmts > 1;
873 # Now strip off the 'ALTER TABLE xyz' of all but the first one
875 my $qt = $options->{quote_table_names} || '';
876 my $table_name = $qt . $table->name . $qt;
879 my $re = $renamed_from
880 ? qr/^ALTER TABLE (?:\Q$table_name\E|\Q$qt$renamed_from$qt\E) /
881 : qr/^ALTER TABLE \Q$table_name\E /;
883 my $first = shift @stmts;
884 my ($alter_table) = $first =~ /($re)/;
886 my $padd = " " x length($alter_table);
888 return @drop_stmt, join( ",\n", $first, map { s/$re//; $padd . $_ } @stmts);
893 my ($table, $options) = @_;
895 my $qt = $options->{quote_table_names} || '';
897 # Drop (foreign key) constraints so table drops cleanly
898 my @sql = batch_alter_table($table, { alter_drop_constraint => [ grep { $_->type eq 'FOREIGN KEY' } $table->get_constraints ] }, $options);
900 return (@sql, "DROP TABLE $qt$table$qt");
901 # return join("\n", @sql, "DROP TABLE $qt$table$qt");
906 my ($old_table, $new_table, $options) = @_;
908 my $qt = $options->{quote_table_names} || '';
910 return "ALTER TABLE $qt$old_table$qt RENAME TO $qt$new_table$qt";
913 sub next_unused_name {
914 my $name = shift || '';
915 if ( !defined($used_names{$name}) ) {
916 $used_names{$name} = $name;
921 while ( defined($used_names{$name . '_' . $i}) ) {
925 $used_names{$name} = $name;
931 # -------------------------------------------------------------------
937 SQL::Translator, http://www.mysql.com/.
941 darren chamberlain E<lt>darren@cpan.orgE<gt>,
942 Ken Y. Clark E<lt>kclark@cpan.orgE<gt>.