1 package SQL::Translator::Producer::MySQL;
3 # -------------------------------------------------------------------
5 # -------------------------------------------------------------------
6 # Copyright (C) 2002-2009 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[ $DEBUG %used_names ];
105 $DEBUG = 0 unless defined $DEBUG;
107 # Maximum length for most identifiers is 64, according to:
108 # http://dev.mysql.com/doc/refman/4.1/en/identifiers.html
109 # http://dev.mysql.com/doc/refman/5.0/en/identifiers.html
110 my $DEFAULT_MAX_ID_LENGTH = 64;
113 use SQL::Translator::Schema::Constants;
114 use SQL::Translator::Utils qw(debug header_comment truncate_id_uniquely parse_mysql_version);
117 # Use only lowercase for the keys (e.g. "long" and not "LONG")
123 varchar2 => 'varchar',
139 'long integer' => 'integer',
141 'datetime' => 'datetime',
150 sub preprocess_schema {
153 # extra->{mysql_table_type} used to be the type. It belongs in options, so
154 # move it if we find it. Return Engine type if found in extra or options
155 # Similarly for mysql_charset and mysql_collate
156 my $extra_to_options = sub {
157 my ($table, $extra_name, $opt_name) = @_;
159 my $extra = $table->extra;
161 my $extra_type = delete $extra->{$extra_name};
163 # Now just to find if there is already an Engine or Type option...
164 # and lets normalize it to ENGINE since:
166 # The ENGINE table option specifies the storage engine for the table.
167 # TYPE is a synonym, but ENGINE is the preferred option name.
170 # We have to use the hash directly here since otherwise there is no way
172 my $options = ( $table->{options} ||= []);
174 # If multiple option names, normalize to the first one
176 OPT_NAME: for ( @$opt_name[1..$#$opt_name] ) {
177 for my $idx ( 0..$#{$options} ) {
178 my ($key, $value) = %{ $options->[$idx] };
181 $options->[$idx] = { $opt_name->[0] => $value };
186 $opt_name = $opt_name->[0];
191 # This assumes that there isn't both a Type and an Engine option.
193 for my $idx ( 0..$#{$options} ) {
194 my ($key, $value) = %{ $options->[$idx] };
196 next unless uc $key eq $opt_name;
198 # make sure case is right on option name
199 delete $options->[$idx]{$key};
200 return $options->[$idx]{$opt_name} = $value || $extra_type;
205 push @$options, { $opt_name => $extra_type };
211 # Names are only specific to a given schema
212 local %used_names = ();
215 # Work out which tables need to be InnoDB to support foreign key
216 # constraints. We do this first as we need InnoDB at both ends.
218 foreach my $table ( $schema->get_tables ) {
220 $extra_to_options->($table, 'mysql_table_type', ['ENGINE', 'TYPE'] );
221 $extra_to_options->($table, 'mysql_charset', 'CHARACTER SET' );
222 $extra_to_options->($table, 'mysql_collate', 'COLLATE' );
224 foreach my $c ( $table->get_constraints ) {
225 next unless $c->type eq FOREIGN_KEY;
227 # Normalize constraint names here.
228 my $c_name = $c->name;
229 # Give the constraint a name if it doesn't have one, so it doens't feel
231 $c_name = $table->name . '_fk' unless length $c_name;
233 $c->name( next_unused_name($c_name) );
235 for my $meth (qw/table reference_table/) {
236 my $table = $schema->get_table($c->$meth) || next;
237 # This normalizes the types to ENGINE and returns the value if its there
238 next if $extra_to_options->($table, 'mysql_table_type', ['ENGINE', 'TYPE']);
239 $table->options( { 'ENGINE' => 'InnoDB' } );
241 } # foreach constraints
243 my %map = ( mysql_collate => 'collate', mysql_charset => 'character set');
244 foreach my $f ( $table->get_fields ) {
245 my $extra = $f->extra;
247 $extra->{$map{$_}} = delete $extra->{$_} if exists $extra->{$_};
251 if ( !$size[0] && $f->data_type =~ /char$/ ) {
260 my $translator = shift;
261 local $DEBUG = $translator->debug;
263 my $no_comments = $translator->no_comments;
264 my $add_drop_table = $translator->add_drop_table;
265 my $schema = $translator->schema;
266 my $show_warnings = $translator->show_warnings || 0;
267 my $producer_args = $translator->producer_args;
268 my $mysql_version = parse_mysql_version ($producer_args->{mysql_version}, 'perl') || 0;
269 my $max_id_length = $producer_args->{mysql_max_id_length} || $DEFAULT_MAX_ID_LENGTH;
271 my ($qt, $qf, $qc) = ('','', '');
272 $qt = '`' if $translator->quote_table_names;
273 $qf = '`' if $translator->quote_field_names;
275 debug("PKG: Beginning production\n");
278 $create .= header_comment unless ($no_comments);
279 # \todo Don't set if MySQL 3.x is set on command line
280 my @create = "SET foreign_key_checks=0";
282 preprocess_schema($schema);
289 for my $table ( $schema->get_tables ) {
290 # print $table->name, "\n";
291 push @table_defs, create_table($table,
292 { add_drop_table => $add_drop_table,
293 show_warnings => $show_warnings,
294 no_comments => $no_comments,
295 quote_table_names => $qt,
296 quote_field_names => $qf,
297 max_id_length => $max_id_length,
298 mysql_version => $mysql_version
302 if ($mysql_version >= 5.000001) {
303 for my $view ( $schema->get_views ) {
304 push @table_defs, create_view($view,
305 { add_replace_view => $add_drop_table,
306 show_warnings => $show_warnings,
307 no_comments => $no_comments,
308 quote_table_names => $qt,
309 quote_field_names => $qf,
310 max_id_length => $max_id_length,
311 mysql_version => $mysql_version
317 # print "@table_defs\n";
318 push @table_defs, "SET foreign_key_checks=1";
320 return wantarray ? ($create ? $create : (), @create, @table_defs) : ($create . join('', map { $_ ? "$_;\n\n" : () } (@create, @table_defs)));
324 my ($view, $options) = @_;
325 my $qt = $options->{quote_table_names} || '';
326 my $qf = $options->{quote_field_names} || '';
328 my $view_name = $view->name;
329 debug("PKG: Looking at view '${view_name}'\n");
331 # Header. Should this look like what mysqldump produces?
333 $create .= "--\n-- View: ${qt}${view_name}${qt}\n--\n" unless $options->{no_comments};
335 $create .= ' OR REPLACE' if $options->{add_replace_view};
338 my $extra = $view->extra;
340 if( exists($extra->{mysql_algorithm}) && defined(my $algorithm = $extra->{mysql_algorithm}) ){
341 $create .= " ALGORITHM = ${algorithm}\n" if $algorithm =~ /(?:UNDEFINED|MERGE|TEMPTABLE)/i;
344 if( exists($extra->{mysql_definer}) && defined(my $user = $extra->{mysql_definer}) ){
345 $create .= " DEFINER = ${user}\n";
348 if( exists($extra->{mysql_security}) && defined(my $security = $extra->{mysql_security}) ){
349 $create .= " SQL SECURITY ${security}\n" if $security =~ /(?:DEFINER|INVOKER)/i;
353 $create .= " VIEW ${qt}${view_name}${qt}";
355 if( my @fields = $view->fields ){
356 my $list = join ', ', map { "${qf}${_}${qf}"} @fields;
357 $create .= " ( ${list} )";
359 if( my $sql = $view->sql ){
360 $create .= " AS (\n ${sql}\n )";
368 my ($table, $options) = @_;
370 my $qt = $options->{quote_table_names} || '';
371 my $qf = $options->{quote_field_names} || '';
373 my $table_name = $table->name;
374 debug("PKG: Looking at table '$table_name'\n");
377 # Header. Should this look like what mysqldump produces?
381 $create .= "--\n-- Table: $qt$table_name$qt\n--\n" unless $options->{no_comments};
382 $drop = qq[DROP TABLE IF EXISTS $qt$table_name$qt] if $options->{add_drop_table};
383 $create .= "CREATE TABLE $qt$table_name$qt (\n";
389 for my $field ( $table->get_fields ) {
390 push @field_defs, create_field($field, $options);
398 for my $index ( $table->get_indices ) {
399 push @index_defs, create_index($index, $options);
400 $indexed_fields{ $_ } = 1 for $index->fields;
404 # Constraints -- need to handle more than just FK. -ky
407 my @constraints = $table->get_constraints;
408 for my $c ( @constraints ) {
409 my $constr = create_constraint($c, $options);
410 push @constraint_defs, $constr if($constr);
412 unless ( $indexed_fields{ ($c->fields())[0] } || $c->type ne FOREIGN_KEY ) {
413 push @index_defs, "INDEX ($qf" . ($c->fields())[0] . "$qf)";
414 $indexed_fields{ ($c->fields())[0] } = 1;
418 $create .= join(",\n", map { " $_" }
419 @field_defs, @index_defs, @constraint_defs
426 $create .= generate_table_options($table, $options) || '';
427 # $create .= ";\n\n";
429 return $drop ? ($drop,$create) : $create;
432 sub generate_table_options
434 my ($table, $options) = @_;
437 my $table_type_defined = 0;
438 my $qf = $options->{quote_field_names} ||= '';
439 my $charset = $table->extra('mysql_charset');
440 my $collate = $table->extra('mysql_collate');
442 for my $t1_option_ref ( $table->options ) {
443 my($key, $value) = %{$t1_option_ref};
444 $table_type_defined = 1
445 if uc $key eq 'ENGINE' or uc $key eq 'TYPE';
446 if (uc $key eq 'CHARACTER SET') {
449 } elsif (uc $key eq 'COLLATE') {
452 } elsif (uc $key eq 'UNION') {
453 $union = "($qf". join("$qf, $qf", @$value) ."$qf)";
456 $create .= " $key=$value";
459 my $mysql_table_type = $table->extra('mysql_table_type');
460 $create .= " ENGINE=$mysql_table_type"
461 if $mysql_table_type && !$table_type_defined;
462 my $comments = $table->comments;
464 $create .= " DEFAULT CHARACTER SET $charset" if $charset;
465 $create .= " COLLATE $collate" if $collate;
466 $create .= " UNION=$union" if $union;
467 $create .= qq[ comment='$comments'] if $comments;
473 my ($field, $options) = @_;
475 my $qf = $options->{quote_field_names} ||= '';
477 my $field_name = $field->name;
478 debug("PKG: Looking at field '$field_name'\n");
479 my $field_def = "$qf$field_name$qf";
482 my $data_type = $field->data_type;
483 my @size = $field->size;
484 my %extra = $field->extra;
485 my $list = $extra{'list'} || [];
486 # \todo deal with embedded quotes
487 my $commalist = join( ', ', map { qq['$_'] } @$list );
488 my $charset = $extra{'mysql_charset'};
489 my $collate = $extra{'mysql_collate'};
491 my $mysql_version = $options->{mysql_version} || 0;
493 # Oracle "number" type -- figure best MySQL type
495 if ( lc $data_type eq 'number' ) {
497 if ( scalar @size > 1 ) {
498 $data_type = 'double';
500 elsif ( $size[0] && $size[0] >= 12 ) {
501 $data_type = 'bigint';
503 elsif ( $size[0] && $size[0] <= 1 ) {
504 $data_type = 'tinyint';
511 # Convert a large Oracle varchar to "text"
512 # (not necessary as of 5.0.3 http://dev.mysql.com/doc/refman/5.0/en/char.html)
514 elsif ( $data_type =~ /char/i && $size[0] > 255 ) {
515 unless ($size[0] <= 65535 && $mysql_version >= 5.000003 ) {
520 elsif ( $data_type =~ /boolean/i ) {
521 if ($mysql_version >= 4) {
522 $data_type = 'boolean';
525 $commalist = "'0','1'";
528 elsif ( exists $translate{ lc $data_type } ) {
529 $data_type = $translate{ lc $data_type };
532 @size = () if $data_type =~ /(text|blob)/i;
534 if ( $data_type =~ /(double|float)/ && scalar @size == 1 ) {
538 $field_def .= " $data_type";
540 if ( lc($data_type) eq 'enum' || lc($data_type) eq 'set') {
541 $field_def .= '(' . $commalist . ')';
543 elsif ( defined $size[0] && $size[0] > 0 ) {
544 $field_def .= '(' . join( ', ', @size ) . ')';
548 $field_def .= " CHARACTER SET $charset" if $charset;
549 $field_def .= " COLLATE $collate" if $collate;
552 for my $qual ( qw[ binary unsigned zerofill ] ) {
553 my $val = $extra{ $qual } || $extra{ uc $qual } or next;
554 $field_def .= " $qual";
556 for my $qual ( 'character set', 'collate', 'on update' ) {
557 my $val = $extra{ $qual } || $extra{ uc $qual } or next;
558 $field_def .= " $qual $val";
562 $field_def .= ' NOT NULL' unless $field->is_nullable;
564 # Default? XXX Need better quoting!
565 my $default = $field->default_value;
566 if ( defined $default ) {
567 SQL::Translator::Producer->_apply_default_value(
576 if ( my $comments = $field->comments ) {
577 $field_def .= qq[ comment '$comments'];
581 $field_def .= " auto_increment" if $field->is_auto_increment;
586 sub alter_create_index
588 my ($index, $options) = @_;
590 my $qt = $options->{quote_table_names} || '';
591 my $qf = $options->{quote_field_names} || '';
595 $qt.$index->table->name.$qt,
603 my ($index, $options) = @_;
605 my $qf = $options->{quote_field_names} || '';
608 lc $index->type eq 'normal' ? 'INDEX' : $index->type . ' INDEX',
609 truncate_id_uniquely( $index->name, $options->{max_id_length} || $DEFAULT_MAX_ID_LENGTH ),
610 '(' . $qf . join( "$qf, $qf", $index->fields ) . $qf . ')'
617 my ($index, $options) = @_;
619 my $qt = $options->{quote_table_names} || '';
620 my $qf = $options->{quote_field_names} || '';
624 $qt.$index->table->name.$qt,
627 $index->name || $index->fields
632 sub alter_drop_constraint
634 my ($c, $options) = @_;
636 my $qt = $options->{quote_table_names} || '';
637 my $qc = $options->{quote_field_names} || '';
639 my $out = sprintf('ALTER TABLE %s DROP %s %s',
640 $qt . $c->table->name . $qt,
641 $c->type eq FOREIGN_KEY ? $c->type : "INDEX",
642 $qc . $c->name . $qc );
647 sub alter_create_constraint
649 my ($index, $options) = @_;
651 my $qt = $options->{quote_table_names} || '';
654 $qt.$index->table->name.$qt,
656 create_constraint(@_) );
659 sub create_constraint
661 my ($c, $options) = @_;
663 my $qf = $options->{quote_field_names} || '';
664 my $qt = $options->{quote_table_names} || '';
665 my $leave_name = $options->{leave_name} || undef;
667 my @fields = $c->fields or next;
669 if ( $c->type eq PRIMARY_KEY ) {
670 return 'PRIMARY KEY (' . $qf . join("$qf, $qf", @fields). $qf . ')';
672 elsif ( $c->type eq UNIQUE ) {
675 (defined $c->name ? $qf.truncate_id_uniquely( $c->name, $options->{max_id_length} || $DEFAULT_MAX_ID_LENGTH ).$qf.' ' : '').
676 '(' . $qf . join("$qf, $qf", @fields). $qf . ')';
678 elsif ( $c->type eq FOREIGN_KEY ) {
680 # Make sure FK field is indexed or MySQL complains.
683 my $table = $c->table;
684 my $c_name = truncate_id_uniquely( $c->name, $options->{max_id_length} || $DEFAULT_MAX_ID_LENGTH );
694 $def .= ' ('.$qf . join( "$qf, $qf", @fields ) . $qf . ')';
696 $def .= ' REFERENCES ' . $qt . $c->reference_table . $qt;
698 my @rfields = map { $_ || () } $c->reference_fields;
699 unless ( @rfields ) {
700 my $rtable_name = $c->reference_table;
701 if ( my $ref_table = $table->schema->get_table( $rtable_name ) ) {
702 push @rfields, $ref_table->primary_key;
705 warn "Can't find reference table '$rtable_name' " .
706 "in schema\n" if $options->{show_warnings};
711 $def .= ' (' . $qf . join( "$qf, $qf", @rfields ) . $qf . ')';
714 warn "FK constraint on " . $table->name . '.' .
715 join('', @fields) . " has no reference fields\n"
716 if $options->{show_warnings};
719 if ( $c->match_type ) {
721 ( $c->match_type =~ /full/i ) ? 'FULL' : 'PARTIAL';
724 if ( $c->on_delete ) {
725 $def .= ' ON DELETE '.join( ' ', $c->on_delete );
728 if ( $c->on_update ) {
729 $def .= ' ON UPDATE '.join( ' ', $c->on_update );
739 my ($to_table, $options) = @_;
741 my $qt = $options->{quote_table_names} || '';
743 my $table_options = generate_table_options($to_table, $options) || '';
744 my $out = sprintf('ALTER TABLE %s%s',
745 $qt . $to_table->name . $qt,
751 sub rename_field { alter_field(@_) }
754 my ($from_field, $to_field, $options) = @_;
756 my $qf = $options->{quote_field_names} || '';
757 my $qt = $options->{quote_table_names} || '';
759 my $out = sprintf('ALTER TABLE %s CHANGE COLUMN %s %s',
760 $qt . $to_field->table->name . $qt,
761 $qf . $from_field->name . $qf,
762 create_field($to_field, $options));
769 my ($new_field, $options) = @_;
771 my $qt = $options->{quote_table_names} || '';
773 my $out = sprintf('ALTER TABLE %s ADD COLUMN %s',
774 $qt . $new_field->table->name . $qt,
775 create_field($new_field, $options));
783 my ($old_field, $options) = @_;
785 my $qf = $options->{quote_field_names} || '';
786 my $qt = $options->{quote_table_names} || '';
788 my $out = sprintf('ALTER TABLE %s DROP COLUMN %s',
789 $qt . $old_field->table->name . $qt,
790 $qf . $old_field->name . $qf);
796 sub batch_alter_table {
797 my ($table, $diff_hash, $options) = @_;
799 # InnoDB has an issue with dropping and re-adding a FK constraint under the
800 # name in a single alter statment, see: http://bugs.mysql.com/bug.php?id=13741
802 # We have to work round this.
805 my %fks_to_drop = map {
806 $_->type eq FOREIGN_KEY
809 } @{$diff_hash->{alter_drop_constraint} };
811 my %fks_to_create = map {
812 if ( $_->type eq FOREIGN_KEY) {
813 $fks_to_alter{$_->name} = $fks_to_drop{$_->name} if $fks_to_drop{$_->name};
816 } @{$diff_hash->{alter_create_constraint} };
819 if (scalar keys %fks_to_alter) {
820 $diff_hash->{alter_drop_constraint} = [
821 grep { !$fks_to_alter{$_->name} } @{ $diff_hash->{alter_drop_constraint} }
824 @drop_stmt = batch_alter_table($table, { alter_drop_constraint => [ values %fks_to_alter ] }, $options);
829 if (@{ $diff_hash->{$_} || [] }) {
830 my $meth = __PACKAGE__->can($_) or die __PACKAGE__ . " cant $_";
831 map { $meth->( (ref $_ eq 'ARRAY' ? @$_ : $_), $options ) } @{ $diff_hash->{$_} }
834 alter_drop_constraint
841 alter_create_constraint
844 # rename_table makes things a bit more complex
845 my $renamed_from = "";
846 $renamed_from = $diff_hash->{rename_table}[0][0]->name
847 if $diff_hash->{rename_table} && @{$diff_hash->{rename_table}};
849 return unless @stmts;
850 # Just zero or one stmts. return now
851 return (@drop_stmt,@stmts) unless @stmts > 1;
853 # Now strip off the 'ALTER TABLE xyz' of all but the first one
855 my $qt = $options->{quote_table_names} || '';
856 my $table_name = $qt . $table->name . $qt;
859 my $re = $renamed_from
860 ? qr/^ALTER TABLE (?:\Q$table_name\E|\Q$qt$renamed_from$qt\E) /
861 : qr/^ALTER TABLE \Q$table_name\E /;
863 my $first = shift @stmts;
864 my ($alter_table) = $first =~ /($re)/;
866 my $padd = " " x length($alter_table);
868 return @drop_stmt, join( ",\n", $first, map { s/$re//; $padd . $_ } @stmts);
873 my ($table, $options) = @_;
875 my $qt = $options->{quote_table_names} || '';
877 # Drop (foreign key) constraints so table drops cleanly
878 my @sql = batch_alter_table($table, { alter_drop_constraint => [ grep { $_->type eq 'FOREIGN KEY' } $table->get_constraints ] }, $options);
880 return (@sql, "DROP TABLE $qt$table$qt");
881 # return join("\n", @sql, "DROP TABLE $qt$table$qt");
886 my ($old_table, $new_table, $options) = @_;
888 my $qt = $options->{quote_table_names} || '';
890 return "ALTER TABLE $qt$old_table$qt RENAME TO $qt$new_table$qt";
893 sub next_unused_name {
894 my $name = shift || '';
895 if ( !defined($used_names{$name}) ) {
896 $used_names{$name} = $name;
901 while ( defined($used_names{$name . '_' . $i}) ) {
905 $used_names{$name} = $name;
911 # -------------------------------------------------------------------
917 SQL::Translator, http://www.mysql.com/.
921 darren chamberlain E<lt>darren@cpan.orgE<gt>,
922 Ken Y. Clark E<lt>kclark@cpan.orgE<gt>.