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;
93 use base qw(SQL::Translator::Producer);
95 use SQL::Translator::Schema::Constants;
96 use SQL::Translator::Generator::DDL::MySQL;
97 use SQL::Translator::Utils qw(debug header_comment
98 truncate_id_uniquely parse_mysql_version
99 batch_alter_table_statements
100 normalize_quote_options
104 # Use only lowercase for the keys (e.g. "long" and not "LONG")
110 varchar2 => 'varchar',
126 'long integer' => 'integer',
128 'datetime' => 'datetime',
137 # Column types that do not support length attribute
139 my @no_length_attr = qw/
140 date time timestamp datetime year
144 sub preprocess_schema {
147 # extra->{mysql_table_type} used to be the type. It belongs in options, so
148 # move it if we find it. Return Engine type if found in extra or options
149 # Similarly for mysql_charset and mysql_collate
150 my $extra_to_options = sub {
151 my ($table, $extra_name, $opt_name) = @_;
153 my $extra = $table->extra;
155 my $extra_type = delete $extra->{$extra_name};
157 # Now just to find if there is already an Engine or Type option...
158 # and lets normalize it to ENGINE since:
160 # The ENGINE table option specifies the storage engine for the table.
161 # TYPE is a synonym, but ENGINE is the preferred option name.
164 my $options = $table->options;
166 # If multiple option names, normalize to the first one
168 OPT_NAME: for ( @$opt_name[1..$#$opt_name] ) {
169 for my $idx ( 0..$#{$options} ) {
170 my ($key, $value) = %{ $options->[$idx] };
173 $options->[$idx] = { $opt_name->[0] => $value };
178 $opt_name = $opt_name->[0];
183 # This assumes that there isn't both a Type and an Engine option.
185 for my $idx ( 0..$#{$options} ) {
186 my ($key, $value) = %{ $options->[$idx] };
188 next unless uc $key eq $opt_name;
190 # make sure case is right on option name
191 delete $options->[$idx]{$key};
192 return $options->[$idx]{$opt_name} = $value || $extra_type;
197 push @$options, { $opt_name => $extra_type };
203 # Names are only specific to a given schema
204 local %used_names = ();
207 # Work out which tables need to be InnoDB to support foreign key
208 # constraints. We do this first as we need InnoDB at both ends.
210 foreach my $table ( $schema->get_tables ) {
212 $extra_to_options->($table, 'mysql_table_type', ['ENGINE', 'TYPE'] );
213 $extra_to_options->($table, 'mysql_charset', 'CHARACTER SET' );
214 $extra_to_options->($table, 'mysql_collate', 'COLLATE' );
216 foreach my $c ( $table->get_constraints ) {
217 next unless $c->type eq FOREIGN_KEY;
219 # Normalize constraint names here.
220 my $c_name = $c->name;
221 # Give the constraint a name if it doesn't have one, so it doesn't feel
223 $c_name = $table->name . '_fk' unless length $c_name;
225 $c->name( next_unused_name($c_name) );
227 for my $meth (qw/table reference_table/) {
228 my $table = $schema->get_table($c->$meth) || next;
229 # This normalizes the types to ENGINE and returns the value if its there
230 next if $extra_to_options->($table, 'mysql_table_type', ['ENGINE', 'TYPE']);
231 $table->options( { 'ENGINE' => 'InnoDB' } );
233 } # foreach constraints
235 my %map = ( mysql_collate => 'collate', mysql_charset => 'character set');
236 foreach my $f ( $table->get_fields ) {
237 my $extra = $f->extra;
239 $extra->{$map{$_}} = delete $extra->{$_} if exists $extra->{$_};
243 if ( !$size[0] && $f->data_type =~ /char$/ ) {
252 my ($quoting_generator, $nonquoting_generator);
255 return $options->{generator} if exists $options->{generator};
257 return normalize_quote_options($options)
258 ? $quoting_generator ||= SQL::Translator::Generator::DDL::MySQL->new()
259 : $nonquoting_generator ||= SQL::Translator::Generator::DDL::MySQL->new(
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 $generator = _generator({ quote_identifiers => $translator->quote_identifiers });
279 debug("PKG: Beginning production\n");
282 $create .= header_comment unless ($no_comments);
283 # \todo Don't set if MySQL 3.x is set on command line
284 my @create = "SET foreign_key_checks=0";
286 preprocess_schema($schema);
293 for my $table ( $schema->get_tables ) {
294 # print $table->name, "\n";
295 push @table_defs, create_table($table,
296 { add_drop_table => $add_drop_table,
297 show_warnings => $show_warnings,
298 no_comments => $no_comments,
299 generator => $generator,
300 max_id_length => $max_id_length,
301 mysql_version => $mysql_version
305 if ($mysql_version >= 5.000001) {
306 for my $view ( $schema->get_views ) {
307 push @table_defs, create_view($view,
308 { add_replace_view => $add_drop_table,
309 show_warnings => $show_warnings,
310 no_comments => $no_comments,
311 generator => $generator,
312 max_id_length => $max_id_length,
313 mysql_version => $mysql_version
318 if ($mysql_version >= 5.000002) {
319 for my $trigger ( $schema->get_triggers ) {
320 push @table_defs, create_trigger($trigger,
321 { add_drop_trigger => $add_drop_table,
322 show_warnings => $show_warnings,
323 no_comments => $no_comments,
324 generator => $generator,
325 max_id_length => $max_id_length,
326 mysql_version => $mysql_version
332 # print "@table_defs\n";
333 push @table_defs, "SET foreign_key_checks=1";
335 return wantarray ? ($create ? $create : (), @create, @table_defs) : ($create . join('', map { $_ ? "$_;\n\n" : () } (@create, @table_defs)));
339 my ($trigger, $options) = @_;
340 my $generator = _generator($options);
342 my $trigger_name = $trigger->name;
343 debug("PKG: Looking at trigger '${trigger_name}'\n");
347 my $events = $trigger->database_events;
348 for my $event ( @$events ) {
349 my $name = $trigger_name;
353 warn "Multiple database events supplied for trigger '${trigger_name}', ",
354 "creating trigger '${name}' for the '${event}' event\n"
355 if $options->{show_warnings};
358 my $action = $trigger->action;
359 $action .= ";" unless $action =~ /;\s*\z/;
361 push @statements, "DROP TRIGGER IF EXISTS " . $generator->quote($name) if $options->{add_drop_trigger};
362 push @statements, sprintf(
363 "CREATE TRIGGER %s %s %s ON %s\n FOR EACH ROW BEGIN %s END",
364 $generator->quote($name), $trigger->perform_action_when, $event,
365 $generator->quote($trigger->on_table), $action,
369 # Tack the comment onto the first statement
370 $statements[0] = "--\n-- Trigger " . $generator->quote($trigger_name) . "\n--\n" . $statements[0] unless $options->{no_comments};
375 my ($view, $options) = @_;
376 my $generator = _generator($options);
378 my $view_name = $view->name;
379 my $view_name_qt = $generator->quote($view_name);
381 debug("PKG: Looking at view '${view_name}'\n");
383 # Header. Should this look like what mysqldump produces?
385 $create .= "--\n-- View: $view_name_qt\n--\n" unless $options->{no_comments};
387 $create .= ' OR REPLACE' if $options->{add_replace_view};
390 my $extra = $view->extra;
392 if( exists($extra->{mysql_algorithm}) && defined(my $algorithm = $extra->{mysql_algorithm}) ){
393 $create .= " ALGORITHM = ${algorithm}\n" if $algorithm =~ /(?:UNDEFINED|MERGE|TEMPTABLE)/i;
396 if( exists($extra->{mysql_definer}) && defined(my $user = $extra->{mysql_definer}) ){
397 $create .= " DEFINER = ${user}\n";
400 if( exists($extra->{mysql_security}) && defined(my $security = $extra->{mysql_security}) ){
401 $create .= " SQL SECURITY ${security}\n" if $security =~ /(?:DEFINER|INVOKER)/i;
405 $create .= " VIEW $view_name_qt";
407 if( my @fields = $view->fields ){
408 my $list = join ', ', map { $generator->quote($_) } @fields;
409 $create .= " ( ${list} )";
411 if( my $sql = $view->sql ){
412 # do not wrap parenthesis around the selector, mysql doesn't like this
413 # http://bugs.mysql.com/bug.php?id=9198
414 $create .= " AS\n ${sql}\n";
422 my ($table, $options) = @_;
423 my $generator = _generator($options);
425 my $table_name = $generator->quote($table->name);
426 debug("PKG: Looking at table '$table_name'\n");
429 # Header. Should this look like what mysqldump produces?
433 $create .= "--\n-- Table: $table_name\n--\n" unless $options->{no_comments};
434 $drop = qq[DROP TABLE IF EXISTS $table_name] if $options->{add_drop_table};
435 $create .= "CREATE TABLE $table_name (\n";
441 for my $field ( $table->get_fields ) {
442 push @field_defs, create_field($field, $options);
450 for my $index ( $table->get_indices ) {
451 push @index_defs, create_index($index, $options);
452 $indexed_fields{ $_ } = 1 for $index->fields;
456 # Constraints -- need to handle more than just FK. -ky
459 my @constraints = $table->get_constraints;
460 for my $c ( @constraints ) {
461 my $constr = create_constraint($c, $options);
462 push @constraint_defs, $constr if($constr);
464 unless ( $indexed_fields{ ($c->fields())[0] } || $c->type ne FOREIGN_KEY ) {
465 push @index_defs, "INDEX (" . $generator->quote(($c->fields())[0]) . ")";
466 $indexed_fields{ ($c->fields())[0] } = 1;
470 $create .= join(",\n", map { " $_" }
471 @field_defs, @index_defs, @constraint_defs
478 $create .= generate_table_options($table, $options) || '';
479 # $create .= ";\n\n";
481 return $drop ? ($drop,$create) : $create;
484 sub generate_table_options
486 my ($table, $options) = @_;
489 my $table_type_defined = 0;
490 my $generator = _generator($options);
491 my $charset = $table->extra('mysql_charset');
492 my $collate = $table->extra('mysql_collate');
494 for my $t1_option_ref ( $table->options ) {
495 my($key, $value) = %{$t1_option_ref};
496 $table_type_defined = 1
497 if uc $key eq 'ENGINE' or uc $key eq 'TYPE';
498 if (uc $key eq 'CHARACTER SET') {
501 } elsif (uc $key eq 'COLLATE') {
504 } elsif (uc $key eq 'UNION') {
505 $union = '(' . join(', ', map { $generator->quote($_) } @$value) . ')';
508 $create .= " $key=$value";
511 my $mysql_table_type = $table->extra('mysql_table_type');
512 $create .= " ENGINE=$mysql_table_type"
513 if $mysql_table_type && !$table_type_defined;
514 my $comments = $table->comments;
516 $create .= " DEFAULT CHARACTER SET $charset" if $charset;
517 $create .= " COLLATE $collate" if $collate;
518 $create .= " UNION=$union" if $union;
519 $create .= qq[ comment='$comments'] if $comments;
525 my ($field, $options) = @_;
527 my $generator = _generator($options);
529 my $field_name = $field->name;
530 debug("PKG: Looking at field '$field_name'\n");
531 my $field_def = $generator->quote($field_name);
534 my $data_type = $field->data_type;
535 my @size = $field->size;
536 my %extra = $field->extra;
537 my $list = $extra{'list'} || [];
538 my $commalist = join( ', ', map { __PACKAGE__->_quote_string($_) } @$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 __PACKAGE__->_apply_default_value(
638 if ( my $comments = $field->comments ) {
639 $comments = __PACKAGE__->_quote_string($comments);
640 $field_def .= qq[ comment $comments];
644 $field_def .= " auto_increment" if $field->is_auto_increment;
650 my ($self, $string) = @_;
652 $string =~ s/([\\'])/$1$1/g;
653 return qq{'$string'};
656 sub alter_create_index
658 my ($index, $options) = @_;
660 my $table_name = _generator($options)->quote($index->table->name);
671 my ( $index, $options ) = @_;
672 my $generator = _generator($options);
677 lc $index->type eq 'normal' ? 'INDEX' : $index->type . ' INDEX',
679 ? $generator->quote(truncate_id_uniquely(
681 $options->{max_id_length} || $DEFAULT_MAX_ID_LENGTH
684 '(' . join( ', ', map { $generator->quote($_) } $index->fields ) . ')'
690 my ($index, $options) = @_;
692 my $table_name = _generator($options)->quote($index->table->name);
699 $index->name || $index->fields
704 sub alter_drop_constraint
706 my ($c, $options) = @_;
708 my $generator = _generator($options);
709 my $table_name = $generator->quote($c->table->name);
711 my @out = ('ALTER','TABLE',$table_name,'DROP');
712 if($c->type eq PRIMARY_KEY) {
716 push @out, ($c->type eq FOREIGN_KEY ? $c->type : "INDEX"),
717 $generator->quote($c->name);
719 return join(' ',@out);
722 sub alter_create_constraint
724 my ($index, $options) = @_;
726 my $table_name = _generator($options)->quote($index->table->name);
731 create_constraint(@_) );
734 sub create_constraint
736 my ($c, $options) = @_;
738 my $generator = _generator($options);
739 my $leave_name = $options->{leave_name} || undef;
741 my $reference_table_name = $generator->quote($c->reference_table);
743 my @fields = $c->fields or return;
745 if ( $c->type eq PRIMARY_KEY ) {
746 return 'PRIMARY KEY (' . join(", ", map { $generator->quote($_) } @fields) . ')';
748 elsif ( $c->type eq UNIQUE ) {
749 return sprintf 'UNIQUE %s(%s)',
750 ((defined $c->name && $c->name)
752 truncate_id_uniquely( $c->name, $options->{max_id_length} || $DEFAULT_MAX_ID_LENGTH ),
756 ( join ', ', map { $generator->quote($_) } @fields ),
759 elsif ( $c->type eq FOREIGN_KEY ) {
761 # Make sure FK field is indexed or MySQL complains.
764 my $table = $c->table;
765 my $c_name = truncate_id_uniquely( $c->name, $options->{max_id_length} || $DEFAULT_MAX_ID_LENGTH );
769 ($c_name ? $generator->quote($c_name) : () ),
774 $def .= ' ('. join( ', ', map { $generator->quote($_) } @fields ) . ')';
776 $def .= ' REFERENCES ' . $reference_table_name;
778 my @rfields = map { $_ || () } $c->reference_fields;
779 unless ( @rfields ) {
780 my $rtable_name = $c->reference_table;
781 if ( my $ref_table = $table->schema->get_table( $rtable_name ) ) {
782 push @rfields, $ref_table->primary_key;
785 warn "Can't find reference table '$rtable_name' " .
786 "in schema\n" if $options->{show_warnings};
791 $def .= ' (' . join( ', ', map { $generator->quote($_) } @rfields ) . ')';
794 warn "FK constraint on " . $table->name . '.' .
795 join('', @fields) . " has no reference fields\n"
796 if $options->{show_warnings};
799 if ( $c->match_type ) {
801 ( $c->match_type =~ /full/i ) ? 'FULL' : 'PARTIAL';
804 if ( $c->on_delete ) {
805 $def .= ' ON DELETE '. $c->on_delete;
808 if ( $c->on_update ) {
809 $def .= ' ON UPDATE '. $c->on_update;
819 my ($to_table, $options) = @_;
821 my $table_options = generate_table_options($to_table, $options) || '';
822 my $table_name = _generator($options)->quote($to_table->name);
823 my $out = sprintf('ALTER TABLE %s%s',
830 sub rename_field { alter_field(@_) }
833 my ($from_field, $to_field, $options) = @_;
835 my $generator = _generator($options);
836 my $table_name = $generator->quote($to_field->table->name);
838 my $out = sprintf('ALTER TABLE %s CHANGE COLUMN %s %s',
840 $generator->quote($from_field->name),
841 create_field($to_field, $options));
848 my ($new_field, $options) = @_;
850 my $table_name = _generator($options)->quote($new_field->table->name);
852 my $out = sprintf('ALTER TABLE %s ADD COLUMN %s',
854 create_field($new_field, $options));
862 my ($old_field, $options) = @_;
864 my $generator = _generator($options);
865 my $table_name = $generator->quote($old_field->table->name);
867 my $out = sprintf('ALTER TABLE %s DROP COLUMN %s',
869 $generator->quote($old_field->name));
875 sub batch_alter_table {
876 my ($table, $diff_hash, $options) = @_;
878 # InnoDB has an issue with dropping and re-adding a FK constraint under the
879 # name in a single alter statement, see: http://bugs.mysql.com/bug.php?id=13741
881 # We have to work round this.
884 my %fks_to_drop = map {
885 $_->type eq FOREIGN_KEY
888 } @{$diff_hash->{alter_drop_constraint} };
890 my %fks_to_create = map {
891 if ( $_->type eq FOREIGN_KEY) {
892 $fks_to_alter{$_->name} = $fks_to_drop{$_->name} if $fks_to_drop{$_->name};
895 } @{$diff_hash->{alter_create_constraint} };
898 if (scalar keys %fks_to_alter) {
899 $diff_hash->{alter_drop_constraint} = [
900 grep { !$fks_to_alter{$_->name} } @{ $diff_hash->{alter_drop_constraint} }
903 @drop_stmt = batch_alter_table($table, { alter_drop_constraint => [ values %fks_to_alter ] }, $options);
907 my @stmts = batch_alter_table_statements($diff_hash, $options);
910 my $generator = _generator($options);
912 # rename_table makes things a bit more complex
913 my $renamed_from = "";
914 $renamed_from = $generator->quote($diff_hash->{rename_table}[0][0]->name)
915 if $diff_hash->{rename_table} && @{$diff_hash->{rename_table}};
917 return unless @stmts;
918 # Just zero or one stmts. return now
919 return (@drop_stmt,@stmts) unless @stmts > 1;
921 # Now strip off the 'ALTER TABLE xyz' of all but the first one
923 my $table_name = $generator->quote($table->name);
925 my $re = $renamed_from
926 ? qr/^ALTER TABLE (?:\Q$table_name\E|\Q$renamed_from\E) /
927 : qr/^ALTER TABLE \Q$table_name\E /;
929 my $first = shift @stmts;
930 my ($alter_table) = $first =~ /($re)/;
932 my $padd = " " x length($alter_table);
934 return @drop_stmt, join( ",\n", $first, map { s/$re//; $padd . $_ } @stmts);
939 my ($table, $options) = @_;
941 # Drop (foreign key) constraints so table drops cleanly
942 my @sql = batch_alter_table($table, { alter_drop_constraint => [ grep { $_->type eq 'FOREIGN KEY' } $table->get_constraints ] }, $options);
944 my $table_name = _generator($options)->quote($table);
945 return (@sql, "DROP TABLE $table");
950 my ($old_table, $new_table, $options) = @_;
952 my $generator = _generator($options);
953 my $old_table_name = $generator->quote($old_table);
954 my $new_table_name = $generator->quote($new_table);
956 return "ALTER TABLE $old_table_name RENAME TO $new_table_name";
959 sub next_unused_name {
960 my $name = shift || '';
961 if ( !defined($used_names{$name}) ) {
962 $used_names{$name} = $name;
967 while ( defined($used_names{$name . '_' . $i}) ) {
971 $used_names{$name} = $name;
981 SQL::Translator, http://www.mysql.com/.
985 darren chamberlain E<lt>darren@cpan.orgE<gt>,
986 Ken Youens-Clark E<lt>kclark@cpan.orgE<gt>.