1 package SQL::Translator::Producer::MySQL;
5 SQL::Translator::Producer::MySQL - MySQL-specific producer for SQL::Translator
9 Use via SQL::Translator:
13 my $t = SQL::Translator->new( parser => '...', producer => 'MySQL', '...' );
18 This module will produce text output of the schema suitable for MySQL.
19 There are still some issues to be worked out with syntax differences
20 between MySQL versions 3 and 4 ("SET foreign_key_checks," character sets
25 This producer takes a single optional producer_arg C<mysql_version>, which
26 provides the desired version for the target database. By default MySQL v3 is
27 assumed, and statements pertaining to any features introduced in later versions
28 (e.g. CREATE VIEW) are not produced.
30 Valid version specifiers for C<mysql_version> are listed L<here|SQL::Translator::Utils/parse_mysql_version>
34 Normally the tables will be created without any explicit table type given and
35 so will use the MySQL default.
37 Any tables involved in foreign key constraints automatically get a table type
38 of InnoDB, unless this is overridden by setting the C<mysql_table_type> extra
39 attribute explicitly on the table.
41 =head2 Extra attributes.
43 The producer recognises the following extra attributes on the Schema objects.
49 Set the list of allowed values for Enum fields.
51 =item B<field.binary>, B<field.unsigned>, B<field.zerofill>
53 Set the MySQL field options of the same name.
55 =item B<field.renamed_from>, B<table.renamed_from>
57 Use when producing diffs to indicate that the current table/field has been
58 renamed from the old name as given in the attribute value.
60 =item B<table.mysql_table_type>
62 Set the type of the table e.g. 'InnoDB', 'MyISAM'. This will be
63 automatically set for tables involved in foreign key constraints if it is
64 not already set explicitly. See L<"Table Types">.
66 Please note that the C<ENGINE> option is the preferred method of specifying
67 the MySQL storage engine to use, but this method still works for backwards
70 =item B<table.mysql_charset>, B<table.mysql_collate>
72 Set the tables default character set and collation order.
74 =item B<field.mysql_charset>, B<field.mysql_collate>
76 Set the fields character set and collation order.
84 our ( $DEBUG, %used_names );
85 our $VERSION = '1.59';
86 $DEBUG = 0 unless defined $DEBUG;
88 # Maximum length for most identifiers is 64, according to:
89 # http://dev.mysql.com/doc/refman/4.1/en/identifiers.html
90 # http://dev.mysql.com/doc/refman/5.0/en/identifiers.html
91 my $DEFAULT_MAX_ID_LENGTH = 64;
94 use SQL::Translator::Schema::Constants;
95 use SQL::Translator::Generator::DDL::MySQL;
96 use SQL::Translator::Utils qw(debug header_comment
97 truncate_id_uniquely parse_mysql_version
98 batch_alter_table_statements
99 normalize_quote_options
103 # Use only lowercase for the keys (e.g. "long" and not "LONG")
109 varchar2 => 'varchar',
125 'long integer' => 'integer',
127 'datetime' => 'datetime',
136 # Column types that do not support length attribute
138 my @no_length_attr = qw/
139 date time timestamp datetime year
143 sub preprocess_schema {
146 # extra->{mysql_table_type} used to be the type. It belongs in options, so
147 # move it if we find it. Return Engine type if found in extra or options
148 # Similarly for mysql_charset and mysql_collate
149 my $extra_to_options = sub {
150 my ($table, $extra_name, $opt_name) = @_;
152 my $extra = $table->extra;
154 my $extra_type = delete $extra->{$extra_name};
156 # Now just to find if there is already an Engine or Type option...
157 # and lets normalize it to ENGINE since:
159 # The ENGINE table option specifies the storage engine for the table.
160 # TYPE is a synonym, but ENGINE is the preferred option name.
163 my $options = $table->options;
165 # If multiple option names, normalize to the first one
167 OPT_NAME: for ( @$opt_name[1..$#$opt_name] ) {
168 for my $idx ( 0..$#{$options} ) {
169 my ($key, $value) = %{ $options->[$idx] };
172 $options->[$idx] = { $opt_name->[0] => $value };
177 $opt_name = $opt_name->[0];
182 # This assumes that there isn't both a Type and an Engine option.
184 for my $idx ( 0..$#{$options} ) {
185 my ($key, $value) = %{ $options->[$idx] };
187 next unless uc $key eq $opt_name;
189 # make sure case is right on option name
190 delete $options->[$idx]{$key};
191 return $options->[$idx]{$opt_name} = $value || $extra_type;
196 push @$options, { $opt_name => $extra_type };
202 # Names are only specific to a given schema
203 local %used_names = ();
206 # Work out which tables need to be InnoDB to support foreign key
207 # constraints. We do this first as we need InnoDB at both ends.
209 foreach my $table ( $schema->get_tables ) {
211 $extra_to_options->($table, 'mysql_table_type', ['ENGINE', 'TYPE'] );
212 $extra_to_options->($table, 'mysql_charset', 'CHARACTER SET' );
213 $extra_to_options->($table, 'mysql_collate', 'COLLATE' );
215 foreach my $c ( $table->get_constraints ) {
216 next unless $c->type eq FOREIGN_KEY;
218 # Normalize constraint names here.
219 my $c_name = $c->name;
220 # Give the constraint a name if it doesn't have one, so it doesn't feel
222 $c_name = $table->name . '_fk' unless length $c_name;
224 $c->name( next_unused_name($c_name) );
226 for my $meth (qw/table reference_table/) {
227 my $table = $schema->get_table($c->$meth) || next;
228 # This normalizes the types to ENGINE and returns the value if its there
229 next if $extra_to_options->($table, 'mysql_table_type', ['ENGINE', 'TYPE']);
230 $table->options( { 'ENGINE' => 'InnoDB' } );
232 } # foreach constraints
234 my %map = ( mysql_collate => 'collate', mysql_charset => 'character set');
235 foreach my $f ( $table->get_fields ) {
236 my $extra = $f->extra;
238 $extra->{$map{$_}} = delete $extra->{$_} if exists $extra->{$_};
242 if ( !$size[0] && $f->data_type =~ /char$/ ) {
251 my ($quoting_generator, $nonquoting_generator);
254 return $options->{generator} if exists $options->{generator};
256 return normalize_quote_options($options)
257 ? $quoting_generator ||= SQL::Translator::Generator::DDL::MySQL->new()
258 : $nonquoting_generator ||= SQL::Translator::Generator::DDL::MySQL->new(
265 my $translator = shift;
266 local $DEBUG = $translator->debug;
268 my $no_comments = $translator->no_comments;
269 my $add_drop_table = $translator->add_drop_table;
270 my $schema = $translator->schema;
271 my $show_warnings = $translator->show_warnings || 0;
272 my $producer_args = $translator->producer_args;
273 my $mysql_version = parse_mysql_version ($producer_args->{mysql_version}, 'perl') || 0;
274 my $max_id_length = $producer_args->{mysql_max_id_length} || $DEFAULT_MAX_ID_LENGTH;
276 my $generator = _generator({ quote_identifiers => $translator->quote_identifiers });
278 debug("PKG: Beginning production\n");
281 $create .= header_comment unless ($no_comments);
282 # \todo Don't set if MySQL 3.x is set on command line
283 my @create = "SET foreign_key_checks=0";
285 preprocess_schema($schema);
292 for my $table ( $schema->get_tables ) {
293 # print $table->name, "\n";
294 push @table_defs, create_table($table,
295 { add_drop_table => $add_drop_table,
296 show_warnings => $show_warnings,
297 no_comments => $no_comments,
298 generator => $generator,
299 max_id_length => $max_id_length,
300 mysql_version => $mysql_version
304 if ($mysql_version >= 5.000001) {
305 for my $view ( $schema->get_views ) {
306 push @table_defs, create_view($view,
307 { add_replace_view => $add_drop_table,
308 show_warnings => $show_warnings,
309 no_comments => $no_comments,
310 generator => $generator,
311 max_id_length => $max_id_length,
312 mysql_version => $mysql_version
317 if ($mysql_version >= 5.000002) {
318 for my $trigger ( $schema->get_triggers ) {
319 push @table_defs, create_trigger($trigger,
320 { add_drop_trigger => $add_drop_table,
321 show_warnings => $show_warnings,
322 no_comments => $no_comments,
323 generator => $generator,
324 max_id_length => $max_id_length,
325 mysql_version => $mysql_version
331 # print "@table_defs\n";
332 push @table_defs, "SET foreign_key_checks=1";
334 return wantarray ? ($create ? $create : (), @create, @table_defs) : ($create . join('', map { $_ ? "$_;\n\n" : () } (@create, @table_defs)));
338 my ($trigger, $options) = @_;
339 my $generator = _generator($options);
341 my $trigger_name = $trigger->name;
342 debug("PKG: Looking at trigger '${trigger_name}'\n");
346 my $events = $trigger->database_events;
347 for my $event ( @$events ) {
348 my $name = $trigger_name;
352 warn "Multiple database events supplied for trigger '${trigger_name}', ",
353 "creating trigger '${name}' for the '${event}' event\n"
354 if $options->{show_warnings};
357 my $action = $trigger->action;
358 $action .= ";" unless $action =~ /;\s*\z/;
360 push @statements, "DROP TRIGGER IF EXISTS " . $generator->quote($name) if $options->{add_drop_trigger};
361 push @statements, sprintf(
362 "CREATE TRIGGER %s %s %s ON %s\n FOR EACH ROW BEGIN %s END",
363 $generator->quote($name), $trigger->perform_action_when, $event,
364 $generator->quote($trigger->on_table), $action,
368 # Tack the comment onto the first statement
369 $statements[0] = "--\n-- Trigger " . $generator->quote($trigger_name) . "\n--\n" . $statements[0] unless $options->{no_comments};
374 my ($view, $options) = @_;
375 my $generator = _generator($options);
377 my $view_name = $view->name;
378 my $view_name_qt = $generator->quote($view_name);
380 debug("PKG: Looking at view '${view_name}'\n");
382 # Header. Should this look like what mysqldump produces?
384 $create .= "--\n-- View: $view_name_qt\n--\n" unless $options->{no_comments};
386 $create .= ' OR REPLACE' if $options->{add_replace_view};
389 my $extra = $view->extra;
391 if( exists($extra->{mysql_algorithm}) && defined(my $algorithm = $extra->{mysql_algorithm}) ){
392 $create .= " ALGORITHM = ${algorithm}\n" if $algorithm =~ /(?:UNDEFINED|MERGE|TEMPTABLE)/i;
395 if( exists($extra->{mysql_definer}) && defined(my $user = $extra->{mysql_definer}) ){
396 $create .= " DEFINER = ${user}\n";
399 if( exists($extra->{mysql_security}) && defined(my $security = $extra->{mysql_security}) ){
400 $create .= " SQL SECURITY ${security}\n" if $security =~ /(?:DEFINER|INVOKER)/i;
404 $create .= " VIEW $view_name_qt";
406 if( my @fields = $view->fields ){
407 my $list = join ', ', map { $generator->quote($_) } @fields;
408 $create .= " ( ${list} )";
410 if( my $sql = $view->sql ){
411 # do not wrap parenthesis around the selector, mysql doesn't like this
412 # http://bugs.mysql.com/bug.php?id=9198
413 $create .= " AS\n ${sql}\n";
421 my ($table, $options) = @_;
422 my $generator = _generator($options);
424 my $table_name = $generator->quote($table->name);
425 debug("PKG: Looking at table '$table_name'\n");
428 # Header. Should this look like what mysqldump produces?
432 $create .= "--\n-- Table: $table_name\n--\n" unless $options->{no_comments};
433 $drop = qq[DROP TABLE IF EXISTS $table_name] if $options->{add_drop_table};
434 $create .= "CREATE TABLE $table_name (\n";
440 for my $field ( $table->get_fields ) {
441 push @field_defs, create_field($field, $options);
449 for my $index ( $table->get_indices ) {
450 push @index_defs, create_index($index, $options);
451 $indexed_fields{ $_ } = 1 for $index->fields;
455 # Constraints -- need to handle more than just FK. -ky
458 my @constraints = $table->get_constraints;
459 for my $c ( @constraints ) {
460 my $constr = create_constraint($c, $options);
461 push @constraint_defs, $constr if($constr);
463 unless ( $indexed_fields{ ($c->fields())[0] } || $c->type ne FOREIGN_KEY ) {
464 push @index_defs, "INDEX (" . $generator->quote(($c->fields())[0]) . ")";
465 $indexed_fields{ ($c->fields())[0] } = 1;
469 $create .= join(",\n", map { " $_" }
470 @field_defs, @index_defs, @constraint_defs
477 $create .= generate_table_options($table, $options) || '';
478 # $create .= ";\n\n";
480 return $drop ? ($drop,$create) : $create;
483 sub generate_table_options
485 my ($table, $options) = @_;
488 my $table_type_defined = 0;
489 my $generator = _generator($options);
490 my $charset = $table->extra('mysql_charset');
491 my $collate = $table->extra('mysql_collate');
493 for my $t1_option_ref ( $table->options ) {
494 my($key, $value) = %{$t1_option_ref};
495 $table_type_defined = 1
496 if uc $key eq 'ENGINE' or uc $key eq 'TYPE';
497 if (uc $key eq 'CHARACTER SET') {
500 } elsif (uc $key eq 'COLLATE') {
503 } elsif (uc $key eq 'UNION') {
504 $union = '(' . join(', ', map { $generator->quote($_) } @$value) . ')';
507 $create .= " $key=$value";
510 my $mysql_table_type = $table->extra('mysql_table_type');
511 $create .= " ENGINE=$mysql_table_type"
512 if $mysql_table_type && !$table_type_defined;
513 my $comments = $table->comments;
515 $create .= " DEFAULT CHARACTER SET $charset" if $charset;
516 $create .= " COLLATE $collate" if $collate;
517 $create .= " UNION=$union" if $union;
518 $create .= qq[ comment='$comments'] if $comments;
524 my ($field, $options) = @_;
526 my $generator = _generator($options);
528 my $field_name = $field->name;
529 debug("PKG: Looking at field '$field_name'\n");
530 my $field_def = $generator->quote($field_name);
533 my $data_type = $field->data_type;
534 my @size = $field->size;
535 my %extra = $field->extra;
536 my $list = $extra{'list'} || [];
537 # \todo deal with embedded quotes
538 my $commalist = join( ', ', map { qq['$_'] } @$list );
539 my $charset = $extra{'mysql_charset'};
540 my $collate = $extra{'mysql_collate'};
542 my $mysql_version = $options->{mysql_version} || 0;
544 # Oracle "number" type -- figure best MySQL type
546 if ( lc $data_type eq 'number' ) {
548 if ( scalar @size > 1 ) {
549 $data_type = 'double';
551 elsif ( $size[0] && $size[0] >= 12 ) {
552 $data_type = 'bigint';
554 elsif ( $size[0] && $size[0] <= 1 ) {
555 $data_type = 'tinyint';
562 # Convert a large Oracle varchar to "text"
563 # (not necessary as of 5.0.3 http://dev.mysql.com/doc/refman/5.0/en/char.html)
565 elsif ( $data_type =~ /char/i && $size[0] > 255 ) {
566 unless ($size[0] <= 65535 && $mysql_version >= 5.000003 ) {
571 elsif ( $data_type =~ /boolean/i ) {
572 if ($mysql_version >= 4) {
573 $data_type = 'boolean';
576 $commalist = "'0','1'";
579 elsif ( exists $translate{ lc $data_type } ) {
580 $data_type = $translate{ lc $data_type };
583 @size = () if $data_type =~ /(text|blob)/i;
585 if ( $data_type =~ /(double|float)/ && scalar @size == 1 ) {
589 $field_def .= " $data_type";
591 if ( lc($data_type) eq 'enum' || lc($data_type) eq 'set') {
592 $field_def .= '(' . $commalist . ')';
595 defined $size[0] && $size[0] > 0
597 ! grep lc($data_type) eq $_, @no_length_attr
599 $field_def .= '(' . join( ', ', @size ) . ')';
603 $field_def .= " CHARACTER SET $charset" if $charset;
604 $field_def .= " COLLATE $collate" if $collate;
607 for my $qual ( qw[ binary unsigned zerofill ] ) {
608 my $val = $extra{ $qual } || $extra{ uc $qual } or next;
609 $field_def .= " $qual";
611 for my $qual ( 'character set', 'collate', 'on update' ) {
612 my $val = $extra{ $qual } || $extra{ uc $qual } or next;
614 $field_def .= " $qual ${$val}";
617 $field_def .= " $qual $val";
622 if ( $field->is_nullable ) {
623 $field_def .= ' NULL';
626 $field_def .= ' NOT NULL';
630 SQL::Translator::Producer->_apply_default_value(
638 if ( my $comments = $field->comments ) {
639 $field_def .= qq[ comment '$comments'];
643 $field_def .= " auto_increment" if $field->is_auto_increment;
648 sub alter_create_index
650 my ($index, $options) = @_;
652 my $table_name = _generator($options)->quote($index->table->name);
663 my ( $index, $options ) = @_;
664 my $generator = _generator($options);
669 lc $index->type eq 'normal' ? 'INDEX' : $index->type . ' INDEX',
671 ? $generator->quote(truncate_id_uniquely(
673 $options->{max_id_length} || $DEFAULT_MAX_ID_LENGTH
676 '(' . join( ', ', map { $generator->quote($_) } $index->fields ) . ')'
682 my ($index, $options) = @_;
684 my $table_name = _generator($options)->quote($index->table->name);
691 $index->name || $index->fields
696 sub alter_drop_constraint
698 my ($c, $options) = @_;
700 my $generator = _generator($options);
701 my $table_name = $generator->quote($c->table->name);
703 my @out = ('ALTER','TABLE',$table_name,'DROP');
704 if($c->type eq PRIMARY_KEY) {
708 push @out, ($c->type eq FOREIGN_KEY ? $c->type : "INDEX"),
709 $generator->quote($c->name);
711 return join(' ',@out);
714 sub alter_create_constraint
716 my ($index, $options) = @_;
718 my $table_name = _generator($options)->quote($index->table->name);
723 create_constraint(@_) );
726 sub create_constraint
728 my ($c, $options) = @_;
730 my $generator = _generator($options);
731 my $leave_name = $options->{leave_name} || undef;
733 my $reference_table_name = $generator->quote($c->reference_table);
735 my @fields = $c->fields or return;
737 if ( $c->type eq PRIMARY_KEY ) {
738 return 'PRIMARY KEY (' . join(", ", map { $generator->quote($_) } @fields) . ')';
740 elsif ( $c->type eq UNIQUE ) {
741 return sprintf 'UNIQUE %s(%s)',
742 ((defined $c->name && $c->name)
744 truncate_id_uniquely( $c->name, $options->{max_id_length} || $DEFAULT_MAX_ID_LENGTH ),
748 ( join ', ', map { $generator->quote($_) } @fields ),
751 elsif ( $c->type eq FOREIGN_KEY ) {
753 # Make sure FK field is indexed or MySQL complains.
756 my $table = $c->table;
757 my $c_name = truncate_id_uniquely( $c->name, $options->{max_id_length} || $DEFAULT_MAX_ID_LENGTH );
761 ($c_name ? $generator->quote($c_name) : () ),
766 $def .= ' ('. join( ', ', map { $generator->quote($_) } @fields ) . ')';
768 $def .= ' REFERENCES ' . $reference_table_name;
770 my @rfields = map { $_ || () } $c->reference_fields;
771 unless ( @rfields ) {
772 my $rtable_name = $c->reference_table;
773 if ( my $ref_table = $table->schema->get_table( $rtable_name ) ) {
774 push @rfields, $ref_table->primary_key;
777 warn "Can't find reference table '$rtable_name' " .
778 "in schema\n" if $options->{show_warnings};
783 $def .= ' (' . join( ', ', map { $generator->quote($_) } @rfields ) . ')';
786 warn "FK constraint on " . $table->name . '.' .
787 join('', @fields) . " has no reference fields\n"
788 if $options->{show_warnings};
791 if ( $c->match_type ) {
793 ( $c->match_type =~ /full/i ) ? 'FULL' : 'PARTIAL';
796 if ( $c->on_delete ) {
797 $def .= ' ON DELETE '. $c->on_delete;
800 if ( $c->on_update ) {
801 $def .= ' ON UPDATE '. $c->on_update;
811 my ($to_table, $options) = @_;
813 my $table_options = generate_table_options($to_table, $options) || '';
814 my $table_name = _generator($options)->quote($to_table->name);
815 my $out = sprintf('ALTER TABLE %s%s',
822 sub rename_field { alter_field(@_) }
825 my ($from_field, $to_field, $options) = @_;
827 my $generator = _generator($options);
828 my $table_name = $generator->quote($to_field->table->name);
830 my $out = sprintf('ALTER TABLE %s CHANGE COLUMN %s %s',
832 $generator->quote($from_field->name),
833 create_field($to_field, $options));
840 my ($new_field, $options) = @_;
842 my $table_name = _generator($options)->quote($new_field->table->name);
844 my $out = sprintf('ALTER TABLE %s ADD COLUMN %s',
846 create_field($new_field, $options));
854 my ($old_field, $options) = @_;
856 my $generator = _generator($options);
857 my $table_name = $generator->quote($old_field->table->name);
859 my $out = sprintf('ALTER TABLE %s DROP COLUMN %s',
861 $generator->quote($old_field->name));
867 sub batch_alter_table {
868 my ($table, $diff_hash, $options) = @_;
870 # InnoDB has an issue with dropping and re-adding a FK constraint under the
871 # name in a single alter statement, see: http://bugs.mysql.com/bug.php?id=13741
873 # We have to work round this.
876 my %fks_to_drop = map {
877 $_->type eq FOREIGN_KEY
880 } @{$diff_hash->{alter_drop_constraint} };
882 my %fks_to_create = map {
883 if ( $_->type eq FOREIGN_KEY) {
884 $fks_to_alter{$_->name} = $fks_to_drop{$_->name} if $fks_to_drop{$_->name};
887 } @{$diff_hash->{alter_create_constraint} };
890 if (scalar keys %fks_to_alter) {
891 $diff_hash->{alter_drop_constraint} = [
892 grep { !$fks_to_alter{$_->name} } @{ $diff_hash->{alter_drop_constraint} }
895 @drop_stmt = batch_alter_table($table, { alter_drop_constraint => [ values %fks_to_alter ] }, $options);
899 my @stmts = batch_alter_table_statements($diff_hash, $options);
902 my $generator = _generator($options);
904 # rename_table makes things a bit more complex
905 my $renamed_from = "";
906 $renamed_from = $generator->quote($diff_hash->{rename_table}[0][0]->name)
907 if $diff_hash->{rename_table} && @{$diff_hash->{rename_table}};
909 return unless @stmts;
910 # Just zero or one stmts. return now
911 return (@drop_stmt,@stmts) unless @stmts > 1;
913 # Now strip off the 'ALTER TABLE xyz' of all but the first one
915 my $table_name = $generator->quote($table->name);
917 my $re = $renamed_from
918 ? qr/^ALTER TABLE (?:\Q$table_name\E|\Q$renamed_from\E) /
919 : qr/^ALTER TABLE \Q$table_name\E /;
921 my $first = shift @stmts;
922 my ($alter_table) = $first =~ /($re)/;
924 my $padd = " " x length($alter_table);
926 return @drop_stmt, join( ",\n", $first, map { s/$re//; $padd . $_ } @stmts);
931 my ($table, $options) = @_;
933 # Drop (foreign key) constraints so table drops cleanly
934 my @sql = batch_alter_table($table, { alter_drop_constraint => [ grep { $_->type eq 'FOREIGN KEY' } $table->get_constraints ] }, $options);
936 my $table_name = _generator($options)->quote($table);
937 return (@sql, "DROP TABLE $table");
942 my ($old_table, $new_table, $options) = @_;
944 my $generator = _generator($options);
945 my $old_table_name = $generator->quote($old_table);
946 my $new_table_name = $generator->quote($new_table);
948 return "ALTER TABLE $old_table_name RENAME TO $new_table_name";
951 sub next_unused_name {
952 my $name = shift || '';
953 if ( !defined($used_names{$name}) ) {
954 $used_names{$name} = $name;
959 while ( defined($used_names{$name . '_' . $i}) ) {
963 $used_names{$name} = $name;
973 SQL::Translator, http://www.mysql.com/.
977 darren chamberlain E<lt>darren@cpan.orgE<gt>,
978 Ken Youens-Clark E<lt>kclark@cpan.orgE<gt>.