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 preferred 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
114 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 # Column types that do not support lenth attribute
152 my @no_length_attr = qw/
153 date time timestamp datetime year
157 sub preprocess_schema {
160 # extra->{mysql_table_type} used to be the type. It belongs in options, so
161 # move it if we find it. Return Engine type if found in extra or options
162 # Similarly for mysql_charset and mysql_collate
163 my $extra_to_options = sub {
164 my ($table, $extra_name, $opt_name) = @_;
166 my $extra = $table->extra;
168 my $extra_type = delete $extra->{$extra_name};
170 # Now just to find if there is already an Engine or Type option...
171 # and lets normalize it to ENGINE since:
173 # The ENGINE table option specifies the storage engine for the table.
174 # TYPE is a synonym, but ENGINE is the preferred option name.
177 # We have to use the hash directly here since otherwise there is no way
179 my $options = ( $table->{options} ||= []);
181 # If multiple option names, normalize to the first one
183 OPT_NAME: for ( @$opt_name[1..$#$opt_name] ) {
184 for my $idx ( 0..$#{$options} ) {
185 my ($key, $value) = %{ $options->[$idx] };
188 $options->[$idx] = { $opt_name->[0] => $value };
193 $opt_name = $opt_name->[0];
198 # This assumes that there isn't both a Type and an Engine option.
200 for my $idx ( 0..$#{$options} ) {
201 my ($key, $value) = %{ $options->[$idx] };
203 next unless uc $key eq $opt_name;
205 # make sure case is right on option name
206 delete $options->[$idx]{$key};
207 return $options->[$idx]{$opt_name} = $value || $extra_type;
212 push @$options, { $opt_name => $extra_type };
218 # Names are only specific to a given schema
219 local %used_names = ();
222 # Work out which tables need to be InnoDB to support foreign key
223 # constraints. We do this first as we need InnoDB at both ends.
225 foreach my $table ( $schema->get_tables ) {
227 $extra_to_options->($table, 'mysql_table_type', ['ENGINE', 'TYPE'] );
228 $extra_to_options->($table, 'mysql_charset', 'CHARACTER SET' );
229 $extra_to_options->($table, 'mysql_collate', 'COLLATE' );
231 foreach my $c ( $table->get_constraints ) {
232 next unless $c->type eq FOREIGN_KEY;
234 # Normalize constraint names here.
235 my $c_name = $c->name;
236 # Give the constraint a name if it doesn't have one, so it doens't feel
238 $c_name = $table->name . '_fk' unless length $c_name;
240 $c->name( next_unused_name($c_name) );
242 for my $meth (qw/table reference_table/) {
243 my $table = $schema->get_table($c->$meth) || next;
244 # This normalizes the types to ENGINE and returns the value if its there
245 next if $extra_to_options->($table, 'mysql_table_type', ['ENGINE', 'TYPE']);
246 $table->options( { 'ENGINE' => 'InnoDB' } );
248 } # foreach constraints
250 my %map = ( mysql_collate => 'collate', mysql_charset => 'character set');
251 foreach my $f ( $table->get_fields ) {
252 my $extra = $f->extra;
254 $extra->{$map{$_}} = delete $extra->{$_} if exists $extra->{$_};
258 if ( !$size[0] && $f->data_type =~ /char$/ ) {
267 my $translator = shift;
268 local $DEBUG = $translator->debug;
270 my $no_comments = $translator->no_comments;
271 my $add_drop_table = $translator->add_drop_table;
272 my $schema = $translator->schema;
273 my $show_warnings = $translator->show_warnings || 0;
274 my $producer_args = $translator->producer_args;
275 my $mysql_version = parse_mysql_version ($producer_args->{mysql_version}, 'perl') || 0;
276 my $max_id_length = $producer_args->{mysql_max_id_length} || $DEFAULT_MAX_ID_LENGTH;
278 my ($qt, $qf, $qc) = ('','', '');
279 $qt = '`' if $translator->quote_table_names;
280 $qf = '`' if $translator->quote_field_names;
282 debug("PKG: Beginning production\n");
285 $create .= header_comment unless ($no_comments);
286 # \todo Don't set if MySQL 3.x is set on command line
287 my @create = "SET foreign_key_checks=0";
289 preprocess_schema($schema);
296 for my $table ( $schema->get_tables ) {
297 # print $table->name, "\n";
298 push @table_defs, create_table($table,
299 { add_drop_table => $add_drop_table,
300 show_warnings => $show_warnings,
301 no_comments => $no_comments,
302 quote_table_names => $qt,
303 quote_field_names => $qf,
304 max_id_length => $max_id_length,
305 mysql_version => $mysql_version
309 if ($mysql_version >= 5.000001) {
310 for my $view ( $schema->get_views ) {
311 push @table_defs, create_view($view,
312 { add_replace_view => $add_drop_table,
313 show_warnings => $show_warnings,
314 no_comments => $no_comments,
315 quote_table_names => $qt,
316 quote_field_names => $qf,
317 max_id_length => $max_id_length,
318 mysql_version => $mysql_version
323 if ($mysql_version >= 5.000002) {
324 for my $trigger ( $schema->get_triggers ) {
325 push @table_defs, create_trigger($trigger,
326 { add_drop_trigger => $add_drop_table,
327 show_warnings => $show_warnings,
328 no_comments => $no_comments,
329 quote_table_names => $qt,
330 quote_field_names => $qf,
331 max_id_length => $max_id_length,
332 mysql_version => $mysql_version
338 # print "@table_defs\n";
339 push @table_defs, "SET foreign_key_checks=1";
341 return wantarray ? ($create ? $create : (), @create, @table_defs) : ($create . join('', map { $_ ? "$_;\n\n" : () } (@create, @table_defs)));
345 my ($trigger, $options) = @_;
346 my $qt = $options->{quote_table_names} || '';
347 my $qf = $options->{quote_field_names} || '';
349 my $trigger_name = $trigger->name;
350 debug("PKG: Looking at trigger '${trigger_name}'\n");
354 my $events = $trigger->database_events;
355 for my $event ( @$events ) {
356 my $name = $trigger_name;
360 warn "Multiple database events supplied for trigger '${trigger_name}', ",
361 "creating trigger '${name}' for the '${event}' event\n"
362 if $options->{show_warnings};
365 my $action = $trigger->action;
366 $action .= ";" unless $action =~ /;\s*\z/;
368 push @statements, "DROP TRIGGER IF EXISTS ${qt}${name}${qt}" if $options->{add_drop_trigger};
369 push @statements, sprintf(
370 "CREATE TRIGGER ${qt}%s${qt} %s %s ON ${qt}%s${qt}\n FOR EACH ROW BEGIN %s END",
371 $name, $trigger->perform_action_when, $event, $trigger->on_table, $action,
375 # Tack the comment onto the first statement
376 $statements[0] = "--\n-- Trigger ${qt}${trigger_name}${qt}\n--\n" . $statements[0] unless $options->{no_comments};
381 my ($view, $options) = @_;
382 my $qt = $options->{quote_table_names} || '';
383 my $qf = $options->{quote_field_names} || '';
385 my $view_name = $view->name;
386 debug("PKG: Looking at view '${view_name}'\n");
388 # Header. Should this look like what mysqldump produces?
390 $create .= "--\n-- View: ${qt}${view_name}${qt}\n--\n" unless $options->{no_comments};
392 $create .= ' OR REPLACE' if $options->{add_replace_view};
395 my $extra = $view->extra;
397 if( exists($extra->{mysql_algorithm}) && defined(my $algorithm = $extra->{mysql_algorithm}) ){
398 $create .= " ALGORITHM = ${algorithm}\n" if $algorithm =~ /(?:UNDEFINED|MERGE|TEMPTABLE)/i;
401 if( exists($extra->{mysql_definer}) && defined(my $user = $extra->{mysql_definer}) ){
402 $create .= " DEFINER = ${user}\n";
405 if( exists($extra->{mysql_security}) && defined(my $security = $extra->{mysql_security}) ){
406 $create .= " SQL SECURITY ${security}\n" if $security =~ /(?:DEFINER|INVOKER)/i;
410 $create .= " VIEW ${qt}${view_name}${qt}";
412 if( my @fields = $view->fields ){
413 my $list = join ', ', map { "${qf}${_}${qf}"} @fields;
414 $create .= " ( ${list} )";
416 if( my $sql = $view->sql ){
417 # do not wrap parenthesis around the selector, mysql doesn't like this
418 # http://bugs.mysql.com/bug.php?id=9198
419 $create .= " AS\n ${sql}\n";
427 my ($table, $options) = @_;
429 my $qt = $options->{quote_table_names} || '';
430 my $qf = $options->{quote_field_names} || '';
432 my $table_name = quote_table_name($table->name, $qt);
433 debug("PKG: Looking at table '$table_name'\n");
436 # Header. Should this look like what mysqldump produces?
440 $create .= "--\n-- Table: $table_name\n--\n" unless $options->{no_comments};
441 $drop = qq[DROP TABLE IF EXISTS $table_name] if $options->{add_drop_table};
442 $create .= "CREATE TABLE $table_name (\n";
448 for my $field ( $table->get_fields ) {
449 push @field_defs, create_field($field, $options);
457 for my $index ( $table->get_indices ) {
458 push @index_defs, create_index($index, $options);
459 $indexed_fields{ $_ } = 1 for $index->fields;
463 # Constraints -- need to handle more than just FK. -ky
466 my @constraints = $table->get_constraints;
467 for my $c ( @constraints ) {
468 my $constr = create_constraint($c, $options);
469 push @constraint_defs, $constr if($constr);
471 unless ( $indexed_fields{ ($c->fields())[0] } || $c->type ne FOREIGN_KEY ) {
472 push @index_defs, "INDEX ($qf" . ($c->fields())[0] . "$qf)";
473 $indexed_fields{ ($c->fields())[0] } = 1;
477 $create .= join(",\n", map { " $_" }
478 @field_defs, @index_defs, @constraint_defs
485 $create .= generate_table_options($table, $options) || '';
486 # $create .= ";\n\n";
488 return $drop ? ($drop,$create) : $create;
491 sub quote_table_name {
492 my ($table_name, $qt) = @_;
494 $table_name =~ s/\./$qt.$qt/g;
496 return "$qt$table_name$qt";
499 sub generate_table_options
501 my ($table, $options) = @_;
504 my $table_type_defined = 0;
505 my $qf = $options->{quote_field_names} ||= '';
506 my $charset = $table->extra('mysql_charset');
507 my $collate = $table->extra('mysql_collate');
509 for my $t1_option_ref ( $table->options ) {
510 my($key, $value) = %{$t1_option_ref};
511 $table_type_defined = 1
512 if uc $key eq 'ENGINE' or uc $key eq 'TYPE';
513 if (uc $key eq 'CHARACTER SET') {
516 } elsif (uc $key eq 'COLLATE') {
519 } elsif (uc $key eq 'UNION') {
520 $union = "($qf". join("$qf, $qf", @$value) ."$qf)";
523 $create .= " $key=$value";
526 my $mysql_table_type = $table->extra('mysql_table_type');
527 $create .= " ENGINE=$mysql_table_type"
528 if $mysql_table_type && !$table_type_defined;
529 my $comments = $table->comments;
531 $create .= " DEFAULT CHARACTER SET $charset" if $charset;
532 $create .= " COLLATE $collate" if $collate;
533 $create .= " UNION=$union" if $union;
534 $create .= qq[ comment='$comments'] if $comments;
540 my ($field, $options) = @_;
542 my $qf = $options->{quote_field_names} ||= '';
544 my $field_name = $field->name;
545 debug("PKG: Looking at field '$field_name'\n");
546 my $field_def = "$qf$field_name$qf";
549 my $data_type = $field->data_type;
550 my @size = $field->size;
551 my %extra = $field->extra;
552 my $list = $extra{'list'} || [];
553 # \todo deal with embedded quotes
554 my $commalist = join( ', ', map { qq['$_'] } @$list );
555 my $charset = $extra{'mysql_charset'};
556 my $collate = $extra{'mysql_collate'};
558 my $mysql_version = $options->{mysql_version} || 0;
560 # Oracle "number" type -- figure best MySQL type
562 if ( lc $data_type eq 'number' ) {
564 if ( scalar @size > 1 ) {
565 $data_type = 'double';
567 elsif ( $size[0] && $size[0] >= 12 ) {
568 $data_type = 'bigint';
570 elsif ( $size[0] && $size[0] <= 1 ) {
571 $data_type = 'tinyint';
578 # Convert a large Oracle varchar to "text"
579 # (not necessary as of 5.0.3 http://dev.mysql.com/doc/refman/5.0/en/char.html)
581 elsif ( $data_type =~ /char/i && $size[0] > 255 ) {
582 unless ($size[0] <= 65535 && $mysql_version >= 5.000003 ) {
587 elsif ( $data_type =~ /boolean/i ) {
588 if ($mysql_version >= 4) {
589 $data_type = 'boolean';
592 $commalist = "'0','1'";
595 elsif ( exists $translate{ lc $data_type } ) {
596 $data_type = $translate{ lc $data_type };
599 @size = () if $data_type =~ /(text|blob)/i;
601 if ( $data_type =~ /(double|float)/ && scalar @size == 1 ) {
605 $field_def .= " $data_type";
607 if ( lc($data_type) eq 'enum' || lc($data_type) eq 'set') {
608 $field_def .= '(' . $commalist . ')';
611 defined $size[0] && $size[0] > 0
613 ! grep lc($data_type) eq $_, @no_length_attr
615 $field_def .= '(' . join( ', ', @size ) . ')';
619 $field_def .= " CHARACTER SET $charset" if $charset;
620 $field_def .= " COLLATE $collate" if $collate;
623 for my $qual ( qw[ binary unsigned zerofill ] ) {
624 my $val = $extra{ $qual } || $extra{ uc $qual } or next;
625 $field_def .= " $qual";
627 for my $qual ( 'character set', 'collate', 'on update' ) {
628 my $val = $extra{ $qual } || $extra{ uc $qual } or next;
629 $field_def .= " $qual $val";
633 $field_def .= ' NOT NULL' unless $field->is_nullable;
636 SQL::Translator::Producer->_apply_default_value(
644 if ( my $comments = $field->comments ) {
645 $field_def .= qq[ comment '$comments'];
649 $field_def .= " auto_increment" if $field->is_auto_increment;
654 sub alter_create_index
656 my ($index, $options) = @_;
658 my $qt = $options->{quote_table_names} || '';
659 my $qf = $options->{quote_field_names} || '';
660 my $table_name = quote_table_name($index->table->name, $qt);
671 my ( $index, $options ) = @_;
673 my $qf = $options->{quote_field_names} || '';
678 lc $index->type eq 'normal' ? 'INDEX' : $index->type . ' INDEX',
680 ? $qf . truncate_id_uniquely(
682 $options->{max_id_length} || $DEFAULT_MAX_ID_LENGTH
685 '(' . $qf . join( "$qf, $qf", $index->fields ) . $qf . ')'
691 my ($index, $options) = @_;
693 my $qt = $options->{quote_table_names} || '';
694 my $qf = $options->{quote_field_names} || '';
695 my $table_name = quote_table_name($index->table->name, $qt);
702 $index->name || $index->fields
707 sub alter_drop_constraint
709 my ($c, $options) = @_;
711 my $qt = $options->{quote_table_names} || '';
712 my $qc = $options->{quote_field_names} || '';
713 my $table_name = quote_table_name($c->table->name, $qt);
715 my $out = sprintf('ALTER TABLE %s DROP %s %s',
717 $c->type eq FOREIGN_KEY ? $c->type : "INDEX",
718 $qc . $c->name . $qc );
723 sub alter_create_constraint
725 my ($index, $options) = @_;
727 my $qt = $options->{quote_table_names} || '';
728 my $table_name = quote_table_name($index->table->name, $qt);
733 create_constraint(@_) );
736 sub create_constraint
738 my ($c, $options) = @_;
740 my $qf = $options->{quote_field_names} || '';
741 my $qt = $options->{quote_table_names} || '';
742 my $leave_name = $options->{leave_name} || undef;
744 my $reference_table_name = quote_table_name($c->reference_table, $qt);
746 my @fields = $c->fields or next;
748 if ( $c->type eq PRIMARY_KEY ) {
749 return 'PRIMARY KEY (' . $qf . join("$qf, $qf", @fields). $qf . ')';
751 elsif ( $c->type eq UNIQUE ) {
754 (defined $c->name ? $qf.truncate_id_uniquely( $c->name, $options->{max_id_length} || $DEFAULT_MAX_ID_LENGTH ).$qf.' ' : '').
755 '(' . $qf . join("$qf, $qf", @fields). $qf . ')';
757 elsif ( $c->type eq FOREIGN_KEY ) {
759 # Make sure FK field is indexed or MySQL complains.
762 my $table = $c->table;
763 my $c_name = truncate_id_uniquely( $c->name, $options->{max_id_length} || $DEFAULT_MAX_ID_LENGTH );
773 $def .= ' ('.$qf . join( "$qf, $qf", @fields ) . $qf . ')';
775 $def .= ' REFERENCES ' . $reference_table_name;
777 my @rfields = map { $_ || () } $c->reference_fields;
778 unless ( @rfields ) {
779 my $rtable_name = $c->reference_table;
780 if ( my $ref_table = $table->schema->get_table( $rtable_name ) ) {
781 push @rfields, $ref_table->primary_key;
784 warn "Can't find reference table '$rtable_name' " .
785 "in schema\n" if $options->{show_warnings};
790 $def .= ' (' . $qf . join( "$qf, $qf", @rfields ) . $qf . ')';
793 warn "FK constraint on " . $table->name . '.' .
794 join('', @fields) . " has no reference fields\n"
795 if $options->{show_warnings};
798 if ( $c->match_type ) {
800 ( $c->match_type =~ /full/i ) ? 'FULL' : 'PARTIAL';
803 if ( $c->on_delete ) {
804 $def .= ' ON DELETE '. $c->on_delete;
807 if ( $c->on_update ) {
808 $def .= ' ON UPDATE '. $c->on_update;
818 my ($to_table, $options) = @_;
820 my $qt = $options->{quote_table_names} || '';
822 my $table_options = generate_table_options($to_table, $options) || '';
823 my $table_name = quote_table_name($to_table->name, $qt);
824 my $out = sprintf('ALTER TABLE %s%s',
831 sub rename_field { alter_field(@_) }
834 my ($from_field, $to_field, $options) = @_;
836 my $qf = $options->{quote_field_names} || '';
837 my $qt = $options->{quote_table_names} || '';
838 my $table_name = quote_table_name($to_field->table->name, $qt);
840 my $out = sprintf('ALTER TABLE %s CHANGE COLUMN %s %s',
842 $qf . $from_field->name . $qf,
843 create_field($to_field, $options));
850 my ($new_field, $options) = @_;
852 my $qt = $options->{quote_table_names} || '';
853 my $table_name = quote_table_name($new_field->table->name, $qt);
855 my $out = sprintf('ALTER TABLE %s ADD COLUMN %s',
857 create_field($new_field, $options));
865 my ($old_field, $options) = @_;
867 my $qf = $options->{quote_field_names} || '';
868 my $qt = $options->{quote_table_names} || '';
869 my $table_name = quote_table_name($old_field->table->name, $qt);
871 my $out = sprintf('ALTER TABLE %s DROP COLUMN %s',
873 $qf . $old_field->name . $qf);
879 sub batch_alter_table {
880 my ($table, $diff_hash, $options) = @_;
882 # InnoDB has an issue with dropping and re-adding a FK constraint under the
883 # name in a single alter statment, see: http://bugs.mysql.com/bug.php?id=13741
885 # We have to work round this.
888 my %fks_to_drop = map {
889 $_->type eq FOREIGN_KEY
892 } @{$diff_hash->{alter_drop_constraint} };
894 my %fks_to_create = map {
895 if ( $_->type eq FOREIGN_KEY) {
896 $fks_to_alter{$_->name} = $fks_to_drop{$_->name} if $fks_to_drop{$_->name};
899 } @{$diff_hash->{alter_create_constraint} };
902 if (scalar keys %fks_to_alter) {
903 $diff_hash->{alter_drop_constraint} = [
904 grep { !$fks_to_alter{$_->name} } @{ $diff_hash->{alter_drop_constraint} }
907 @drop_stmt = batch_alter_table($table, { alter_drop_constraint => [ values %fks_to_alter ] }, $options);
912 if (@{ $diff_hash->{$_} || [] }) {
913 my $meth = __PACKAGE__->can($_) or die __PACKAGE__ . " cant $_";
914 map { $meth->( (ref $_ eq 'ARRAY' ? @$_ : $_), $options ) } @{ $diff_hash->{$_} }
917 alter_drop_constraint
924 alter_create_constraint
928 my $qt = $options->{quote_table_names} || '';
930 # rename_table makes things a bit more complex
931 my $renamed_from = "";
932 $renamed_from = quote_table_name($diff_hash->{rename_table}[0][0]->name, $qt)
933 if $diff_hash->{rename_table} && @{$diff_hash->{rename_table}};
935 return unless @stmts;
936 # Just zero or one stmts. return now
937 return (@drop_stmt,@stmts) unless @stmts > 1;
939 # Now strip off the 'ALTER TABLE xyz' of all but the first one
941 my $table_name = quote_table_name($table->name, $qt);
943 my $re = $renamed_from
944 ? qr/^ALTER TABLE (?:\Q$table_name\E|\Q$renamed_from\E) /
945 : qr/^ALTER TABLE \Q$table_name\E /;
947 my $first = shift @stmts;
948 my ($alter_table) = $first =~ /($re)/;
950 my $padd = " " x length($alter_table);
952 return @drop_stmt, join( ",\n", $first, map { s/$re//; $padd . $_ } @stmts);
957 my ($table, $options) = @_;
959 my $qt = $options->{quote_table_names} || '';
961 # Drop (foreign key) constraints so table drops cleanly
962 my @sql = batch_alter_table($table, { alter_drop_constraint => [ grep { $_->type eq 'FOREIGN KEY' } $table->get_constraints ] }, $options);
964 my $table_name = quote_table_name($table, $qt);
965 return (@sql, "DROP TABLE $table");
970 my ($old_table, $new_table, $options) = @_;
972 my $qt = $options->{quote_table_names} || '';
973 my $old_table_name = quote_table_name($old_table, $qt);
974 my $new_table_name = quote_table_name($new_table, $qt);
976 return "ALTER TABLE $old_table_name RENAME TO $new_table_name";
979 sub next_unused_name {
980 my $name = shift || '';
981 if ( !defined($used_names{$name}) ) {
982 $used_names{$name} = $name;
987 while ( defined($used_names{$name . '_' . $i}) ) {
991 $used_names{$name} = $name;
1001 SQL::Translator, http://www.mysql.com/.
1005 darren chamberlain E<lt>darren@cpan.orgE<gt>,
1006 Ken Youens-Clark E<lt>kclark@cpan.orgE<gt>.