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>
use Data::Dumper;
use SQL::Translator::Schema::Constants;
-use SQL::Translator::Utils qw(debug header_comment truncate_id_uniquely parse_mysql_version);
+use SQL::Translator::Utils qw(debug header_comment
+ truncate_id_uniquely parse_mysql_version);
#
# Use only lowercase for the keys (e.g. "long" and not "LONG")
bytea => 'BLOB',
);
+#
+# Column types that do not support lenth attribute
+#
+my @no_length_attr = qw/
+ date time timestamp datetime year
+ /;
+
sub preprocess_schema {
my ($schema) = @_;
}
}
+ 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,
+ quote_table_names => $qt,
+ quote_field_names => $qf,
+ max_id_length => $max_id_length,
+ mysql_version => $mysql_version
+ });
+ }
+ }
+
# print "@table_defs\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 $qt = $options->{quote_table_names} || '';
+ my $qf = $options->{quote_field_names} || '';
+
+ 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 ${qt}${name}${qt}" if $options->{add_drop_trigger};
+ push @statements, sprintf(
+ "CREATE TRIGGER ${qt}%s${qt} %s %s ON ${qt}%s${qt}\n FOR EACH ROW BEGIN %s END",
+ $name, $trigger->perform_action_when, $event, $trigger->on_table, $action,
+ );
+
+ }
+ # Tack the comment onto the first statement
+ $statements[0] = "--\n-- Trigger ${qt}${trigger_name}${qt}\n--\n" . $statements[0] unless $options->{no_comments};
+ return @statements;
+}
+
sub create_view {
my ($view, $options) = @_;
my $qt = $options->{quote_table_names} || '';
$create .= " ( ${list} )";
}
if( my $sql = $view->sql ){
- $create .= " AS (\n ${sql}\n )";
+ # 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;
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 ) . ')';
}
# Null?
$field_def .= ' NOT NULL' unless $field->is_nullable;
- # Default? XXX Need better quoting!
- my $default = $field->default_value;
- if ( defined $default ) {
- SQL::Translator::Producer->_apply_default_value(
- \$field_def,
- $default,
- [
- 'NULL' => \'NULL',
- ],
- );
- }
+ # Default?
+ SQL::Translator::Producer->_apply_default_value(
+ $field,
+ \$field_def,
+ [
+ 'NULL' => \'NULL',
+ ],
+ );
if ( my $comments = $field->comments ) {
$field_def .= qq[ comment '$comments'];
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(@_)
);
sub create_index
{
- my ($index, $options) = @_;
+ my ( $index, $options ) = @_;
my $qf = $options->{quote_field_names} || '';
- return join( ' ',
- lc $index->type eq 'normal' ? 'INDEX' : $index->type . ' INDEX',
- truncate_id_uniquely( $index->name, $options->{max_id_length} || $DEFAULT_MAX_ID_LENGTH ),
- '(' . $qf . join( "$qf, $qf", $index->fields ) . $qf . ')'
- );
-
+ return join(
+ ' ',
+ map { $_ || () }
+ lc $index->type eq 'normal' ? 'INDEX' : $index->type . ' INDEX',
+ $index->name
+ ? $qf . truncate_id_uniquely(
+ $index->name,
+ $options->{max_id_length} || $DEFAULT_MAX_ID_LENGTH
+ ) . $qf
+ : '',
+ '(' . $qf . join( "$qf, $qf", $index->fields ) . $qf . ')'
+ );
}
sub alter_drop_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( ' ',
'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 ) {
$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 ) {
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;
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;
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) /
+ ? 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 {
=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