=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
+=head1 ARGUMENTS
-This producer takes a single optional producer_arg C<mysql_version>, which
+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>
+Valid version specifiers for C<mysql_version> are listed L<here|SQL::Translator::Utils/parse_mysql_version>
=head2 Table Types
use Data::Dumper;
use SQL::Translator::Schema::Constants;
-use SQL::Translator::Utils qw(debug header_comment
+use SQL::Translator::Utils qw(debug header_comment
truncate_id_uniquely parse_mysql_version);
#
# 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
+ # We have to use the hash directly here since otherwise there is no way
# to remove options.
my $options = ( $table->{options} ||= []);
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' );
# Give the constraint a name if it doesn't have one, so it doens'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/) {
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
my @create = "SET foreign_key_checks=0";
# 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,
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)";
$indexed_fields{ ($c->fields())[0] } = 1;
}
}
- $create .= join(",\n", map { " $_" }
+ $create .= join(",\n", map { " $_" }
@field_defs, @index_defs, @constraint_defs
);
return "$qt$table_name$qt";
}
-sub generate_table_options
+sub generate_table_options
{
my ($table, $options) = @_;
my $create;
if ( lc($data_type) eq 'enum' || lc($data_type) eq 'set') {
$field_def .= '(' . $commalist . ')';
}
- elsif (
- defined $size[0] && $size[0] > 0
- &&
- ! grep lc($data_type) eq $_, @no_length_attr
+ elsif (
+ defined $size[0] && $size[0] > 0
+ &&
+ ! grep lc($data_type) eq $_, @no_length_attr
) {
$field_def .= '(' . join( ', ', @size ) . ')';
}
my $qt = $options->{quote_table_names} || '';
my $qf = $options->{quote_field_names} || '';
-
+ my $table_name = quote_table_name($index->table->name, $qt);
return join( ' ',
'ALTER TABLE',
- $qt.$index->table->name.$qt,
+ $table_name,
'ADD',
create_index(@_)
);
my $qt = $options->{quote_table_names} || '';
my $qf = $options->{quote_field_names} || '';
+ my $table_name = quote_table_name($index->table->name, $qt);
- return join( ' ',
+ return join( ' ',
'ALTER TABLE',
- $qt.$index->table->name.$qt,
+ $table_name,
'DROP',
'INDEX',
$index->name || $index->fields
my $qt = $options->{quote_table_names} || '';
my $qc = $options->{quote_field_names} || '';
+ my $table_name = quote_table_name($c->table->name, $qt);
my $out = sprintf('ALTER TABLE %s DROP %s %s',
- $qt . $c->table->name . $qt,
+ $table_name,
$c->type eq FOREIGN_KEY ? $c->type : "INDEX",
$qc . $c->name . $qc );
my ($index, $options) = @_;
my $qt = $options->{quote_table_names} || '';
+ my $table_name = quote_table_name($index->table->name, $qt);
return join( ' ',
'ALTER TABLE',
- $qt.$index->table->name.$qt,
+ $table_name,
'ADD',
create_constraint(@_) );
}
my $qt = $options->{quote_table_names} || '';
my $leave_name = $options->{leave_name} || undef;
+ my $reference_table_name = quote_table_name($c->reference_table, $qt);
+
my @fields = $c->fields or next;
if ( $c->type eq PRIMARY_KEY ) {
}
elsif ( $c->type eq UNIQUE ) {
return
- 'UNIQUE '.
+ 'UNIQUE '.
(defined $c->name ? $qf.truncate_id_uniquely( $c->name, $options->{max_id_length} || $DEFAULT_MAX_ID_LENGTH ).$qf.' ' : '').
'(' . $qf . join("$qf, $qf", @fields). $qf . ')';
}
my $table = $c->table;
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(' ',
+ map { $_ || () }
+ 'CONSTRAINT',
+ $qf . $c_name . $qf,
'FOREIGN KEY'
);
$def .= ' ('.$qf . join( "$qf, $qf", @fields ) . $qf . ')';
- $def .= ' REFERENCES ' . $qt . $c->reference_table . $qt;
+ $def .= ' REFERENCES ' . $reference_table_name;
my @rfields = map { $_ || () } $c->reference_fields;
unless ( @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 $qt = $options->{quote_table_names} || '';
my $table_options = generate_table_options($to_table, $options) || '';
+ my $table_name = quote_table_name($to_table->name, $qt);
my $out = sprintf('ALTER TABLE %s%s',
- $qt . $to_table->name . $qt,
+ $table_name,
$table_options);
return $out;
my $qf = $options->{quote_field_names} || '';
my $qt = $options->{quote_table_names} || '';
+ my $table_name = quote_table_name($to_field->table->name, $qt);
my $out = sprintf('ALTER TABLE %s CHANGE COLUMN %s %s',
- $qt . $to_field->table->name . $qt,
+ $table_name,
$qf . $from_field->name . $qf,
create_field($to_field, $options));
my ($new_field, $options) = @_;
my $qt = $options->{quote_table_names} || '';
+ my $table_name = quote_table_name($new_field->table->name, $qt);
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 $table_name = quote_table_name($old_field->table->name, $qt);
+
my $out = sprintf('ALTER TABLE %s DROP COLUMN %s',
- $qt . $old_field->table->name . $qt,
+ $table_name,
$qf . $old_field->name . $qf);
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
+ # 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
#
# 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} };
alter_create_constraint
alter_table/;
+ #quote
+ my $qt = $options->{quote_table_names} || '';
+
# rename_table makes things a bit more complex
my $renamed_from = "";
- $renamed_from = $diff_hash->{rename_table}[0][0]->name
+ $renamed_from = quote_table_name($diff_hash->{rename_table}[0][0]->name, $qt)
if $diff_hash->{rename_table} && @{$diff_hash->{rename_table}};
return unless @stmts;
# 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 = quote_table_name($table->name, $qt);
- 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;
# 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 (@sql, "DROP TABLE $qt$table$qt");
-# return join("\n", @sql, "DROP TABLE $qt$table$qt");
+ my $table_name = quote_table_name($table, $qt);
+ return (@sql, "DROP TABLE $table");
}
my ($old_table, $new_table, $options) = @_;
my $qt = $options->{quote_table_names} || '';
+ my $old_table_name = quote_table_name($old_table, $qt);
+ my $new_table_name = quote_table_name($new_table, $qt);
- 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 {
projects / dbsrgits/SQL-Translator.git / blobdiff