package SQL::Translator::Producer::MySQL;
-# -------------------------------------------------------------------
-# $Id: MySQL.pm,v 1.54 2007-11-10 03:36:43 mwz444 Exp $
-# -------------------------------------------------------------------
-# Copyright (C) 2002-4 SQLFairy Authors
-#
-# This program is free software; you can redistribute it and/or
-# modify it under the terms of the GNU General Public License as
-# published by the Free Software Foundation; version 2.
-#
-# This program is distributed in the hope that it will be useful, but
-# WITHOUT ANY WARRANTY; without even the implied warranty of
-# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
-# General Public License for more details.
-#
-# You should have received a copy of the GNU General Public License
-# along with this program; if not, write to the Free Software
-# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA
-# 02111-1307 USA
-# -------------------------------------------------------------------
-
=head1 NAME
SQL::Translator::Producer::MySQL - MySQL-specific producer for SQL::Translator
=head1 DESCRIPTION
This module will produce text output of the schema suitable for MySQL.
-There are still some issues to be worked out with syntax differences
+There are still some issues to be worked out with syntax differences
between MySQL versions 3 and 4 ("SET foreign_key_checks," character sets
for fields, etc.).
+=head1 ARGUMENTS
+
+This producer takes a single optional producer_arg C<mysql_version>, which
+provides the desired version for the target database. By default MySQL v3 is
+assumed, and statements pertaining to any features introduced in later versions
+(e.g. CREATE VIEW) are not produced.
+
+Valid version specifiers for C<mysql_version> are listed L<here|SQL::Translator::Utils/parse_mysql_version>
+
=head2 Table Types
Normally the tables will be created without any explicit table type given and
automatically set for tables involved in foreign key constraints if it is
not already set explicitly. See L<"Table Types">.
-Please note that the C<ENGINE> option is the prefered method of specifying
+Please note that the C<ENGINE> option is the preferred method of specifying
the MySQL storage engine to use, but this method still works for backwards
-compatability.
+compatibility.
=item B<table.mysql_charset>, B<table.mysql_collate>
-Set the tables default charater set and collation order.
+Set the tables default character set and collation order.
=item B<field.mysql_charset>, B<field.mysql_collate>
-Set the fields charater set and collation order.
+Set the fields character set and collation order.
=back
use strict;
use warnings;
-use vars qw[ $VERSION $DEBUG %used_names ];
-$VERSION = sprintf "%d.%02d", q$Revision: 1.54 $ =~ /(\d+)\.(\d+)/;
+our ( $DEBUG, %used_names );
+our $VERSION = '1.59';
$DEBUG = 0 unless defined $DEBUG;
+# Maximum length for most identifiers is 64, according to:
+# http://dev.mysql.com/doc/refman/4.1/en/identifiers.html
+# http://dev.mysql.com/doc/refman/5.0/en/identifiers.html
+my $DEFAULT_MAX_ID_LENGTH = 64;
+
+use base qw(SQL::Translator::Producer);
use Data::Dumper;
use SQL::Translator::Schema::Constants;
-use SQL::Translator::Utils qw(debug header_comment);
+use SQL::Translator::Generator::DDL::MySQL;
+use SQL::Translator::Utils qw(debug header_comment
+ truncate_id_uniquely parse_mysql_version
+ batch_alter_table_statements
+ normalize_quote_options
+);
#
# Use only lowercase for the keys (e.g. "long" and not "LONG")
'long integer' => 'integer',
'text' => 'text',
'datetime' => 'datetime',
+
+ #
+ # PostgreSQL types
+ #
+ bytea => 'BLOB',
);
+#
+# Column types that do not support length attribute
+#
+my @no_length_attr = qw/
+ date time timestamp datetime year
+ /;
+
sub preprocess_schema {
my ($schema) = @_;
# Now just to find if there is already an Engine or Type option...
# and lets normalize it to ENGINE since:
#
- # The ENGINE table option specifies the storage engine for the table.
+ # The ENGINE table option specifies the storage engine for the table.
# TYPE is a synonym, but ENGINE is the preferred option name.
#
- # We have to use the hash directly here since otherwise there is no way
- # to remove options.
- my $options = ( $table->{options} ||= []);
+ my $options = $table->options;
# If multiple option names, normalize to the first one
if (ref $opt_name) {
OPT_NAME: for ( @$opt_name[1..$#$opt_name] ) {
for my $idx ( 0..$#{$options} ) {
my ($key, $value) = %{ $options->[$idx] };
-
+
if (uc $key eq $_) {
$options->[$idx] = { $opt_name->[0] => $value };
last OPT_NAME;
my ($key, $value) = %{ $options->[$idx] };
next unless uc $key eq $opt_name;
-
+
# make sure case is right on option name
delete $options->[$idx]{$key};
return $options->[$idx]{$opt_name} = $value || $extra_type;
}
-
+
if ($extra_type) {
push @$options, { $opt_name => $extra_type };
return $extra_type;
# constraints. We do this first as we need InnoDB at both ends.
#
foreach my $table ( $schema->get_tables ) {
-
+
$extra_to_options->($table, 'mysql_table_type', ['ENGINE', 'TYPE'] );
$extra_to_options->($table, 'mysql_charset', 'CHARACTER SET' );
$extra_to_options->($table, 'mysql_collate', 'COLLATE' );
# Normalize constraint names here.
my $c_name = $c->name;
- # Give the constraint a name if it doesn't have one, so it doens't feel
+ # Give the constraint a name if it doesn't have one, so it doesn't feel
# left out
$c_name = $table->name . '_fk' unless length $c_name;
-
+
$c->name( next_unused_name($c_name) );
for my $meth (qw/table reference_table/) {
}
} # foreach constraints
+ my %map = ( mysql_collate => 'collate', mysql_charset => 'character set');
foreach my $f ( $table->get_fields ) {
+ my $extra = $f->extra;
+ for (keys %map) {
+ $extra->{$map{$_}} = delete $extra->{$_} if exists $extra->{$_};
+ }
+
my @size = $f->size;
if ( !$size[0] && $f->data_type =~ /char$/ ) {
$f->size( (255) );
}
}
+{
+ my ($quoting_generator, $nonquoting_generator);
+ sub _generator {
+ my $options = shift;
+ return $options->{generator} if exists $options->{generator};
+
+ return normalize_quote_options($options)
+ ? $quoting_generator ||= SQL::Translator::Generator::DDL::MySQL->new()
+ : $nonquoting_generator ||= SQL::Translator::Generator::DDL::MySQL->new(
+ quote_chars => [],
+ );
+ }
+}
+
sub produce {
my $translator = shift;
local $DEBUG = $translator->debug;
my $add_drop_table = $translator->add_drop_table;
my $schema = $translator->schema;
my $show_warnings = $translator->show_warnings || 0;
+ my $producer_args = $translator->producer_args;
+ my $mysql_version = parse_mysql_version ($producer_args->{mysql_version}, 'perl') || 0;
+ my $max_id_length = $producer_args->{mysql_max_id_length} || $DEFAULT_MAX_ID_LENGTH;
- my ($qt, $qf, $qc) = ('','', '');
- $qt = '`' if $translator->quote_table_names;
- $qf = '`' if $translator->quote_field_names;
+ my $generator = _generator({ quote_identifiers => $translator->quote_identifiers });
debug("PKG: Beginning production\n");
%used_names = ();
- my $create;
+ my $create = '';
$create .= header_comment unless ($no_comments);
# \todo Don't set if MySQL 3.x is set on command line
- $create .= "SET foreign_key_checks=0;\n\n";
+ my @create = "SET foreign_key_checks=0";
preprocess_schema($schema);
# Generate sql
#
my @table_defs =();
-
+
for my $table ( $schema->get_tables ) {
# print $table->name, "\n";
- push @table_defs, create_table($table,
+ push @table_defs, create_table($table,
{ add_drop_table => $add_drop_table,
show_warnings => $show_warnings,
no_comments => $no_comments,
- quote_table_names => $qt,
- quote_field_names => $qf
+ generator => $generator,
+ max_id_length => $max_id_length,
+ mysql_version => $mysql_version
});
}
+ if ($mysql_version >= 5.000001) {
+ for my $view ( $schema->get_views ) {
+ push @table_defs, create_view($view,
+ { add_replace_view => $add_drop_table,
+ show_warnings => $show_warnings,
+ no_comments => $no_comments,
+ generator => $generator,
+ max_id_length => $max_id_length,
+ mysql_version => $mysql_version
+ });
+ }
+ }
+
+ if ($mysql_version >= 5.000002) {
+ for my $trigger ( $schema->get_triggers ) {
+ push @table_defs, create_trigger($trigger,
+ { add_drop_trigger => $add_drop_table,
+ show_warnings => $show_warnings,
+ no_comments => $no_comments,
+ generator => $generator,
+ max_id_length => $max_id_length,
+ mysql_version => $mysql_version
+ });
+ }
+ }
+
+
# print "@table_defs\n";
- push @table_defs, "SET foreign_key_checks=1;\n\n";
+ push @table_defs, "SET foreign_key_checks=1";
+
+ return wantarray ? ($create ? $create : (), @create, @table_defs) : ($create . join('', map { $_ ? "$_;\n\n" : () } (@create, @table_defs)));
+}
+
+sub create_trigger {
+ my ($trigger, $options) = @_;
+ my $generator = _generator($options);
+
+ my $trigger_name = $trigger->name;
+ debug("PKG: Looking at trigger '${trigger_name}'\n");
+
+ my @statements;
+
+ my $events = $trigger->database_events;
+ for my $event ( @$events ) {
+ my $name = $trigger_name;
+ if (@$events > 1) {
+ $name .= "_$event";
+
+ warn "Multiple database events supplied for trigger '${trigger_name}', ",
+ "creating trigger '${name}' for the '${event}' event\n"
+ if $options->{show_warnings};
+ }
+
+ my $action = $trigger->action;
+ $action .= ";" unless $action =~ /;\s*\z/;
+
+ push @statements, "DROP TRIGGER IF EXISTS " . $generator->quote($name) if $options->{add_drop_trigger};
+ push @statements, sprintf(
+ "CREATE TRIGGER %s %s %s ON %s\n FOR EACH ROW BEGIN %s END",
+ $generator->quote($name), $trigger->perform_action_when, $event,
+ $generator->quote($trigger->on_table), $action,
+ );
+
+ }
+ # Tack the comment onto the first statement
+ $statements[0] = "--\n-- Trigger " . $generator->quote($trigger_name) . "\n--\n" . $statements[0] unless $options->{no_comments};
+ return @statements;
+}
- return wantarray ? ($create, @table_defs) : $create . join ('', @table_defs);
+sub create_view {
+ my ($view, $options) = @_;
+ my $generator = _generator($options);
+
+ my $view_name = $view->name;
+ my $view_name_qt = $generator->quote($view_name);
+
+ debug("PKG: Looking at view '${view_name}'\n");
+
+ # Header. Should this look like what mysqldump produces?
+ my $create = '';
+ $create .= "--\n-- View: $view_name_qt\n--\n" unless $options->{no_comments};
+ $create .= 'CREATE';
+ $create .= ' OR REPLACE' if $options->{add_replace_view};
+ $create .= "\n";
+
+ my $extra = $view->extra;
+ # ALGORITHM
+ if( exists($extra->{mysql_algorithm}) && defined(my $algorithm = $extra->{mysql_algorithm}) ){
+ $create .= " ALGORITHM = ${algorithm}\n" if $algorithm =~ /(?:UNDEFINED|MERGE|TEMPTABLE)/i;
+ }
+ # DEFINER
+ if( exists($extra->{mysql_definer}) && defined(my $user = $extra->{mysql_definer}) ){
+ $create .= " DEFINER = ${user}\n";
+ }
+ # SECURITY
+ if( exists($extra->{mysql_security}) && defined(my $security = $extra->{mysql_security}) ){
+ $create .= " SQL SECURITY ${security}\n" if $security =~ /(?:DEFINER|INVOKER)/i;
+ }
+
+ #Header, cont.
+ $create .= " VIEW $view_name_qt";
+
+ if( my @fields = $view->fields ){
+ my $list = join ', ', map { $generator->quote($_) } @fields;
+ $create .= " ( ${list} )";
+ }
+ if( my $sql = $view->sql ){
+ # do not wrap parenthesis around the selector, mysql doesn't like this
+ # http://bugs.mysql.com/bug.php?id=9198
+ $create .= " AS\n ${sql}\n";
+ }
+# $create .= "";
+ return $create;
}
sub create_table
{
my ($table, $options) = @_;
+ my $generator = _generator($options);
- my $qt = $options->{quote_table_names} || '';
- my $qf = $options->{quote_field_names} || '';
-
- my $table_name = $table->name;
+ my $table_name = $generator->quote($table->name);
debug("PKG: Looking at table '$table_name'\n");
#
#
my $create = '';
my $drop;
- $create .= "--\n-- Table: $qt$table_name$qt\n--\n" unless $options->{no_comments};
- $drop = qq[DROP TABLE IF EXISTS $qt$table_name$qt;\n] if $options->{add_drop_table};
- $create .= "CREATE TABLE $qt$table_name$qt (\n";
+ $create .= "--\n-- Table: $table_name\n--\n" unless $options->{no_comments};
+ $drop = qq[DROP TABLE IF EXISTS $table_name] if $options->{add_drop_table};
+ $create .= "CREATE TABLE $table_name (\n";
#
# Fields
for my $c ( @constraints ) {
my $constr = create_constraint($c, $options);
push @constraint_defs, $constr if($constr);
-
+
unless ( $indexed_fields{ ($c->fields())[0] } || $c->type ne FOREIGN_KEY ) {
- push @index_defs, "INDEX ($qf" . ($c->fields())[0] . "$qf)";
+ push @index_defs, "INDEX (" . $generator->quote(($c->fields())[0]) . ")";
$indexed_fields{ ($c->fields())[0] } = 1;
}
}
- $create .= join(",\n", map { " $_" }
+ $create .= join(",\n", map { " $_" }
@field_defs, @index_defs, @constraint_defs
);
# Footer
#
$create .= "\n)";
- $create .= generate_table_options($table) || '';
- $create .= ";\n\n";
+ $create .= generate_table_options($table, $options) || '';
+# $create .= ";\n\n";
return $drop ? ($drop,$create) : $create;
}
-sub generate_table_options
+sub generate_table_options
{
- my ($table) = @_;
+ my ($table, $options) = @_;
my $create;
my $table_type_defined = 0;
+ my $generator = _generator($options);
my $charset = $table->extra('mysql_charset');
my $collate = $table->extra('mysql_collate');
+ my $union = undef;
for my $t1_option_ref ( $table->options ) {
my($key, $value) = %{$t1_option_ref};
$table_type_defined = 1
} elsif (uc $key eq 'COLLATE') {
$collate = $value;
next;
+ } elsif (uc $key eq 'UNION') {
+ $union = '(' . join(', ', map { $generator->quote($_) } @$value) . ')';
+ next;
}
$create .= " $key=$value";
}
$create .= " DEFAULT CHARACTER SET $charset" if $charset;
$create .= " COLLATE $collate" if $collate;
+ $create .= " UNION=$union" if $union;
$create .= qq[ comment='$comments'] if $comments;
return $create;
}
{
my ($field, $options) = @_;
- my $qf = $options->{quote_field_names} ||= '';
+ my $generator = _generator($options);
my $field_name = $field->name;
debug("PKG: Looking at field '$field_name'\n");
- my $field_def = "$qf$field_name$qf";
+ my $field_def = $generator->quote($field_name);
# data type and size
my $data_type = $field->data_type;
my @size = $field->size;
my %extra = $field->extra;
my $list = $extra{'list'} || [];
- # \todo deal with embedded quotes
- my $commalist = join( ', ', map { qq['$_'] } @$list );
+ my $commalist = join( ', ', map { __PACKAGE__->_quote_string($_) } @$list );
my $charset = $extra{'mysql_charset'};
my $collate = $extra{'mysql_collate'};
+ my $mysql_version = $options->{mysql_version} || 0;
#
# Oracle "number" type -- figure best MySQL type
#
}
#
# Convert a large Oracle varchar to "text"
+ # (not necessary as of 5.0.3 http://dev.mysql.com/doc/refman/5.0/en/char.html)
#
elsif ( $data_type =~ /char/i && $size[0] > 255 ) {
- $data_type = 'text';
- @size = ();
+ unless ($size[0] <= 65535 && $mysql_version >= 5.000003 ) {
+ $data_type = 'text';
+ @size = ();
+ }
}
elsif ( $data_type =~ /boolean/i ) {
- $data_type = 'enum';
- $commalist = "'0','1'";
+ if ($mysql_version >= 4) {
+ $data_type = 'boolean';
+ } else {
+ $data_type = 'enum';
+ $commalist = "'0','1'";
+ }
}
elsif ( exists $translate{ lc $data_type } ) {
$data_type = $translate{ lc $data_type };
$field_def .= " $data_type";
- if ( lc $data_type eq 'enum' ) {
+ if ( lc($data_type) eq 'enum' || lc($data_type) eq 'set') {
$field_def .= '(' . $commalist . ')';
- }
- elsif ( defined $size[0] && $size[0] > 0 ) {
+ }
+ elsif (
+ defined $size[0] && $size[0] > 0
+ &&
+ ! grep lc($data_type) eq $_, @no_length_attr
+ ) {
$field_def .= '(' . join( ', ', @size ) . ')';
}
}
for my $qual ( 'character set', 'collate', 'on update' ) {
my $val = $extra{ $qual } || $extra{ uc $qual } or next;
- $field_def .= " $qual $val";
+ if ( ref $val ) {
+ $field_def .= " $qual ${$val}";
+ }
+ else {
+ $field_def .= " $qual $val";
+ }
}
# Null?
- $field_def .= ' NOT NULL' unless $field->is_nullable;
-
- # Default? XXX Need better quoting!
- my $default = $field->default_value;
- if ( defined $default ) {
- if ( uc $default eq 'NULL') {
- $field_def .= ' DEFAULT NULL';
- } else {
- $field_def .= " DEFAULT '$default'";
- }
+ if ( $field->is_nullable ) {
+ $field_def .= ' NULL';
}
+ else {
+ $field_def .= ' NOT NULL';
+ }
+
+ # Default?
+ __PACKAGE__->_apply_default_value(
+ $field,
+ \$field_def,
+ [
+ 'NULL' => \'NULL',
+ ],
+ );
if ( my $comments = $field->comments ) {
- $field_def .= qq[ comment '$comments'];
+ $comments = __PACKAGE__->_quote_string($comments);
+ $field_def .= qq[ comment $comments];
}
# auto_increment?
return $field_def;
}
+sub _quote_string {
+ my ($self, $string) = @_;
+
+ $string =~ s/([\\'])/$1$1/g;
+ return qq{'$string'};
+}
+
sub alter_create_index
{
my ($index, $options) = @_;
- my $qt = $options->{quote_table_names} || '';
- my $qf = $options->{quote_field_names} || '';
-
+ my $table_name = _generator($options)->quote($index->table->name);
return join( ' ',
'ALTER TABLE',
- $qt.$index->table->name.$qt,
+ $table_name,
'ADD',
create_index(@_)
);
sub create_index
{
- my ($index, $options) = @_;
-
- my $qf = $options->{quote_field_names} || '';
-
- return join( ' ',
- lc $index->type eq 'normal' ? 'INDEX' : $index->type . ' INDEX',
- $index->name,
- '(' . $qf . join( "$qf, $qf", $index->fields ) . $qf . ')'
- );
-
+ my ( $index, $options ) = @_;
+ my $generator = _generator($options);
+
+ return join(
+ ' ',
+ map { $_ || () }
+ lc $index->type eq 'normal' ? 'INDEX' : $index->type . ' INDEX',
+ $index->name
+ ? $generator->quote(truncate_id_uniquely(
+ $index->name,
+ $options->{max_id_length} || $DEFAULT_MAX_ID_LENGTH
+ ))
+ : '',
+ '(' . join( ', ', map { $generator->quote($_) } $index->fields ) . ')'
+ );
}
sub alter_drop_index
{
my ($index, $options) = @_;
- my $qt = $options->{quote_table_names} || '';
- my $qf = $options->{quote_field_names} || '';
+ my $table_name = _generator($options)->quote($index->table->name);
- return join( ' ',
+ return join( ' ',
'ALTER TABLE',
- $qt.$index->table->name.$qt,
+ $table_name,
'DROP',
'INDEX',
$index->name || $index->fields
{
my ($c, $options) = @_;
- my $qt = $options->{quote_table_names} || '';
- my $qc = $options->{quote_field_names} || '';
-
- my $out = sprintf('ALTER TABLE %s DROP %s %s',
- $qt . $c->table->name . $qt,
- $c->type eq FOREIGN_KEY ? $c->type : "INDEX",
- $qc . $c->name . $qc );
+ my $generator = _generator($options);
+ my $table_name = $generator->quote($c->table->name);
- return $out;
+ my @out = ('ALTER','TABLE',$table_name,'DROP');
+ if($c->type eq PRIMARY_KEY) {
+ push @out, $c->type;
+ }
+ else {
+ push @out, ($c->type eq FOREIGN_KEY ? $c->type : "INDEX"),
+ $generator->quote($c->name);
+ }
+ return join(' ',@out);
}
sub alter_create_constraint
{
my ($index, $options) = @_;
- my $qt = $options->{quote_table_names} || '';
+ my $table_name = _generator($options)->quote($index->table->name);
return join( ' ',
'ALTER TABLE',
- $qt.$index->table->name.$qt,
+ $table_name,
'ADD',
create_constraint(@_) );
}
{
my ($c, $options) = @_;
- my $qf = $options->{quote_field_names} || '';
- my $qt = $options->{quote_table_names} || '';
+ my $generator = _generator($options);
my $leave_name = $options->{leave_name} || undef;
- my @fields = $c->fields or next;
+ my $reference_table_name = $generator->quote($c->reference_table);
+
+ my @fields = $c->fields or return;
if ( $c->type eq PRIMARY_KEY ) {
- return 'PRIMARY KEY (' . $qf . join("$qf, $qf", @fields). $qf . ')';
+ return 'PRIMARY KEY (' . join(", ", map { $generator->quote($_) } @fields) . ')';
}
elsif ( $c->type eq UNIQUE ) {
- return
- 'UNIQUE '.
- (defined $c->name ? $qf.$c->name.$qf.' ' : '').
- '(' . $qf . join("$qf, $qf", @fields). $qf . ')';
+ return sprintf 'UNIQUE %s(%s)',
+ ((defined $c->name && $c->name)
+ ? $generator->quote(
+ truncate_id_uniquely( $c->name, $options->{max_id_length} || $DEFAULT_MAX_ID_LENGTH ),
+ ) . ' '
+ : ''
+ ),
+ ( join ', ', map { $generator->quote($_) } @fields ),
+ ;
}
elsif ( $c->type eq FOREIGN_KEY ) {
#
#
my $table = $c->table;
- my $c_name = $c->name;
+ my $c_name = truncate_id_uniquely( $c->name, $options->{max_id_length} || $DEFAULT_MAX_ID_LENGTH );
- my $def = join(' ',
- map { $_ || () }
- 'CONSTRAINT',
- $qf . $c_name . $qf,
+ my $def = join(' ',
+ 'CONSTRAINT',
+ ($c_name ? $generator->quote($c_name) : () ),
'FOREIGN KEY'
);
- $def .= ' ('.$qf . join( "$qf, $qf", @fields ) . $qf . ')';
+ $def .= ' ('. join( ', ', map { $generator->quote($_) } @fields ) . ')';
- $def .= ' REFERENCES ' . $qt . $c->reference_table . $qt;
+ $def .= ' REFERENCES ' . $reference_table_name;
my @rfields = map { $_ || () } $c->reference_fields;
unless ( @rfields ) {
}
if ( @rfields ) {
- $def .= ' (' . $qf . join( "$qf, $qf", @rfields ) . $qf . ')';
+ $def .= ' (' . join( ', ', map { $generator->quote($_) } @rfields ) . ')';
}
else {
warn "FK constraint on " . $table->name . '.' .
- join('', @fields) . " has no reference fields\n"
+ join('', @fields) . " has no reference fields\n"
if $options->{show_warnings};
}
if ( $c->match_type ) {
- $def .= ' MATCH ' .
+ $def .= ' MATCH ' .
( $c->match_type =~ /full/i ) ? 'FULL' : 'PARTIAL';
}
if ( $c->on_delete ) {
- $def .= ' ON DELETE '.join( ' ', $c->on_delete );
+ $def .= ' ON DELETE '. $c->on_delete;
}
if ( $c->on_update ) {
- $def .= ' ON UPDATE '.join( ' ', $c->on_update );
+ $def .= ' ON UPDATE '. $c->on_update;
}
return $def;
}
{
my ($to_table, $options) = @_;
- my $qt = $options->{quote_table_names} || '';
-
- my $table_options = generate_table_options($to_table) || '';
+ my $table_options = generate_table_options($to_table, $options) || '';
+ my $table_name = _generator($options)->quote($to_table->name);
my $out = sprintf('ALTER TABLE %s%s',
- $qt . $to_table->name . $qt,
+ $table_name,
$table_options);
return $out;
{
my ($from_field, $to_field, $options) = @_;
- my $qf = $options->{quote_field_names} || '';
- my $qt = $options->{quote_table_names} || '';
+ my $generator = _generator($options);
+ my $table_name = $generator->quote($to_field->table->name);
my $out = sprintf('ALTER TABLE %s CHANGE COLUMN %s %s',
- $qt . $to_field->table->name . $qt,
- $qf . $from_field->name . $qf,
+ $table_name,
+ $generator->quote($from_field->name),
create_field($to_field, $options));
return $out;
{
my ($new_field, $options) = @_;
- my $qt = $options->{quote_table_names} || '';
+ my $table_name = _generator($options)->quote($new_field->table->name);
my $out = sprintf('ALTER TABLE %s ADD COLUMN %s',
- $qt . $new_field->table->name . $qt,
+ $table_name,
create_field($new_field, $options));
return $out;
}
sub drop_field
-{
+{
my ($old_field, $options) = @_;
- my $qf = $options->{quote_field_names} || '';
- my $qt = $options->{quote_table_names} || '';
-
+ my $generator = _generator($options);
+ my $table_name = $generator->quote($old_field->table->name);
+
my $out = sprintf('ALTER TABLE %s DROP COLUMN %s',
- $qt . $old_field->table->name . $qt,
- $qf . $old_field->name . $qf);
+ $table_name,
+ $generator->quote($old_field->name));
return $out;
-
+
}
sub batch_alter_table {
my ($table, $diff_hash, $options) = @_;
- # InnoDB has an issue with dropping and re-adding a FK constraint under the
- # name in a single alter statment, see: http://bugs.mysql.com/bug.php?id=13741
+ # InnoDB has an issue with dropping and re-adding a FK constraint under the
+ # name in a single alter statement, see: http://bugs.mysql.com/bug.php?id=13741
#
# We have to work round this.
my %fks_to_alter;
my %fks_to_drop = map {
- $_->type eq FOREIGN_KEY
- ? ( $_->name => $_ )
+ $_->type eq FOREIGN_KEY
+ ? ( $_->name => $_ )
: ( )
} @{$diff_hash->{alter_drop_constraint} };
} else { ( ) }
} @{$diff_hash->{alter_create_constraint} };
- my $drop_stmt = '';
+ my @drop_stmt;
if (scalar keys %fks_to_alter) {
$diff_hash->{alter_drop_constraint} = [
grep { !$fks_to_alter{$_->name} } @{ $diff_hash->{alter_drop_constraint} }
];
- $drop_stmt = batch_alter_table($table, { alter_drop_constraint => [ values %fks_to_alter ] }, $options)
- . "\n";
+ @drop_stmt = batch_alter_table($table, { alter_drop_constraint => [ values %fks_to_alter ] }, $options);
}
- my @stmts = map {
- if (@{ $diff_hash->{$_} || [] }) {
- my $meth = __PACKAGE__->can($_) or die __PACKAGE__ . " cant $_";
- map { $meth->( (ref $_ eq 'ARRAY' ? @$_ : $_), $options ) } @{ $diff_hash->{$_} }
- } else { () }
- } qw/rename_table
- alter_drop_constraint
- alter_drop_index
- drop_field
- add_field
- alter_field
- rename_field
- alter_create_index
- alter_create_constraint
- alter_table/;
+ my @stmts = batch_alter_table_statements($diff_hash, $options);
+
+ #quote
+ my $generator = _generator($options);
# rename_table makes things a bit more complex
my $renamed_from = "";
- $renamed_from = $diff_hash->{rename_table}[0][0]->name
+ $renamed_from = $generator->quote($diff_hash->{rename_table}[0][0]->name)
if $diff_hash->{rename_table} && @{$diff_hash->{rename_table}};
return unless @stmts;
# Just zero or one stmts. return now
- return "$drop_stmt@stmts;" unless @stmts > 1;
+ return (@drop_stmt,@stmts) unless @stmts > 1;
# Now strip off the 'ALTER TABLE xyz' of all but the first one
- my $qt = $options->{quote_table_names} || '';
- my $table_name = $qt . $table->name . $qt;
+ my $table_name = $generator->quote($table->name);
-
- my $re = $renamed_from
- ? qr/^ALTER TABLE (?:\Q$table_name\E|\Q$qt$renamed_from$qt\E) /
+ my $re = $renamed_from
+ ? qr/^ALTER TABLE (?:\Q$table_name\E|\Q$renamed_from\E) /
: qr/^ALTER TABLE \Q$table_name\E /;
my $first = shift @stmts;
my $padd = " " x length($alter_table);
- return $drop_stmt . join( ",\n", $first, map { s/$re//; $padd . $_ } @stmts) . ';';
+ return @drop_stmt, join( ",\n", $first, map { s/$re//; $padd . $_ } @stmts);
}
sub drop_table {
my ($table, $options) = @_;
- my $qt = $options->{quote_table_names} || '';
-
# Drop (foreign key) constraints so table drops cleanly
my @sql = batch_alter_table($table, { alter_drop_constraint => [ grep { $_->type eq 'FOREIGN KEY' } $table->get_constraints ] }, $options);
- return join("\n", @sql, "DROP TABLE $qt$table$qt;");
+ my $table_name = _generator($options)->quote($table);
+ return (@sql, "DROP TABLE $table");
}
sub rename_table {
my ($old_table, $new_table, $options) = @_;
- my $qt = $options->{quote_table_names} || '';
+ my $generator = _generator($options);
+ my $old_table_name = $generator->quote($old_table);
+ my $new_table_name = $generator->quote($new_table);
- return "ALTER TABLE $qt$old_table$qt RENAME TO $qt$new_table$qt";
+ return "ALTER TABLE $old_table_name RENAME TO $new_table_name";
}
sub next_unused_name {
1;
-# -------------------------------------------------------------------
-
=pod
=head1 SEE ALSO
=head1 AUTHORS
darren chamberlain E<lt>darren@cpan.orgE<gt>,
-Ken Y. Clark E<lt>kclark@cpan.orgE<gt>.
+Ken Youens-Clark E<lt>kclark@cpan.orgE<gt>.
=cut
projects / dbsrgits/SQL-Translator.git / blobdiff