X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?a=blobdiff_plain;f=lib%2FSQL%2FTranslator%2FProducer%2FSQLite.pm;h=9cc92aff71c05b126aae1b800e2019b0c29ff3c1;hb=c0ec0e22d3f0e3852c00daac5ef5763010b410c3;hp=2e7aa6a3bb6321d2211dad63339dbf9581c98fde;hpb=44659089c28216f1984873bc4aa8641e2e0e3410;p=dbsrgits%2FSQL-Translator.git diff --git a/lib/SQL/Translator/Producer/SQLite.pm b/lib/SQL/Translator/Producer/SQLite.pm index 2e7aa6a..9cc92af 100644 --- a/lib/SQL/Translator/Producer/SQLite.pm +++ b/lib/SQL/Translator/Producer/SQLite.pm @@ -1,23 +1,5 @@ package SQL::Translator::Producer::SQLite; -# ------------------------------------------------------------------- -# Copyright (C) 2002-2009 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::SQLite - SQLite producer for SQL::Translator @@ -39,17 +21,29 @@ use strict; use warnings; use Data::Dumper; use SQL::Translator::Schema::Constants; -use SQL::Translator::Utils qw(debug header_comment parse_dbms_version); - -use vars qw[ $VERSION $DEBUG $WARN ]; +use SQL::Translator::Utils qw(debug header_comment parse_dbms_version batch_alter_table_statements); +use SQL::Translator::Generator::DDL::SQLite; -$VERSION = '1.59'; +our ( $DEBUG, $WARN ); +our $VERSION = '1.59'; $DEBUG = 0 unless defined $DEBUG; $WARN = 0 unless defined $WARN; our $max_id_length = 30; my %global_names; +# HIDEOUS TEMPORARY DEFAULT WITHOUT QUOTING! +our $NO_QUOTES = 1; +{ + + my ($quoting_generator, $nonquoting_generator); + sub _generator { + $NO_QUOTES + ? $nonquoting_generator ||= SQL::Translator::Generator::DDL::SQLite->new(quote_chars => []) + : $quoting_generator ||= SQL::Translator::Generator::DDL::SQLite->new + } +} + sub produce { my $translator = shift; local $DEBUG = $translator->debug; @@ -67,6 +61,10 @@ sub produce { %global_names = (); #reset + # only quote if quotes were requested for real + # 0E0 indicates "the default of true" was assumed + local $NO_QUOTES = 0 + if $translator->quote_identifiers and $translator->quote_identifiers ne '0E0'; my $head = (header_comment() . "\n") unless $no_comments; @@ -107,7 +105,6 @@ sub produce { } } -# ------------------------------------------------------------------- sub mk_name { my ($name, $scope, $critical) = @_; @@ -115,7 +112,7 @@ sub mk_name { if ( my $prev = $scope->{ $name } ) { my $name_orig = $name; $name .= sprintf( "%02d", ++$prev ); - substr($name, $max_id_length - 3) = "00" + substr($name, $max_id_length - 3) = "00" if length( $name ) > $max_id_length; warn "The name '$name_orig' has been changed to ", @@ -125,14 +122,16 @@ sub mk_name { } $scope->{ $name }++; - return $name; + return _generator()->quote($name); } sub create_view { my ($view, $options) = @_; my $add_drop_view = $options->{add_drop_view}; - my $view_name = $view->name; + my $view_name = _generator()->quote($view->name); + $global_names{$view->name} = 1; + debug("PKG: Looking at view '${view_name}'\n"); # Header. Should this look like what mysqldump produces? @@ -164,7 +163,9 @@ sub create_table { my ($table, $options) = @_; - my $table_name = $table->name; + my $table_name = _generator()->quote($table->name); + $global_names{$table->name} = 1; + my $no_comments = $options->{no_comments}; my $add_drop_table = $options->{add_drop_table}; my $sqlite_version = $options->{sqlite_version} || 0; @@ -213,18 +214,17 @@ sub create_table push @field_defs, create_field($field); } - if ( - scalar @pk_fields > 1 - || - ( @pk_fields && !grep /INTEGER PRIMARY KEY/, @field_defs ) + if ( + scalar @pk_fields > 1 + || + ( @pk_fields && !grep /INTEGER PRIMARY KEY/, @field_defs ) ) { - push @field_defs, 'PRIMARY KEY (' . join(', ', @pk_fields ) . ')'; + push @field_defs, 'PRIMARY KEY (' . join(', ', map _generator()->quote($_), @pk_fields ) . ')'; } # # Indices # - my $idx_name_default = 'A'; for my $index ( $table->get_indices ) { push @index_defs, create_index($index); } @@ -232,9 +232,14 @@ sub create_table # # Constraints # - my $c_name_default = 'A'; for my $c ( $table->get_constraints ) { - next unless $c->type eq UNIQUE; + if ($c->type eq "FOREIGN KEY") { + push @field_defs, create_foreignkey($c); + } + elsif ($c->type eq "CHECK") { + push @field_defs, create_check_constraint($c); + } + next unless $c->type eq UNIQUE; push @constraint_defs, create_constraint($c); } @@ -243,97 +248,60 @@ sub create_table return (@create, $create_table, @index_defs, @constraint_defs ); } -sub create_field -{ - my ($field, $options) = @_; - - my $field_name = $field->name; - debug("PKG: Looking at field '$field_name'\n"); - my $field_comments = $field->comments - ? "-- " . $field->comments . "\n " - : ''; - - my $field_def = $field_comments.$field_name; - - # data type and size - my $size = $field->size; - my $data_type = $field->data_type; - $data_type = 'varchar' if lc $data_type eq 'set'; - $data_type = 'blob' if lc $data_type eq 'bytea'; - - if ( lc $data_type =~ /(text|blob)/i ) { - $size = undef; - } - -# if ( $data_type =~ /timestamp/i ) { -# push @trigger_defs, -# "CREATE TRIGGER ts_${table_name} ". -# "after insert on $table_name\n". -# "begin\n". -# " update $table_name set $field_name=timestamp() ". -# "where id=new.id;\n". -# "end;\n" -# ; -# -# } +sub create_check_constraint { + my $c = shift; + my $check = ''; + $check .= 'CONSTRAINT ' . _generator->quote( $c->name ) . ' ' if $c->name; + $check .= 'CHECK(' . $c->expression . ')'; + return $check; +} - # - # SQLite is generally typeless, but newer versions will - # make a field autoincrement if it is declared as (and - # *only* as) INTEGER PRIMARY KEY - # - my $pk = $field->table->primary_key; - my @pk_fields = $pk ? $pk->fields : (); +sub create_foreignkey { + my $c = shift; - if ( - $field->is_primary_key && - scalar @pk_fields == 1 && - ( - $data_type =~ /int(eger)?$/i - || - ( $data_type =~ /^number?$/i && $size !~ /,/ ) - ) - ) { - $data_type = 'INTEGER PRIMARY KEY'; - $size = undef; -# $pk_set = 1; + my @fields = $c->fields; + my @rfields = map { $_ || () } $c->reference_fields; + unless ( @rfields ) { + my $rtable_name = $c->reference_table; + if ( my $ref_table = $c->schema->get_table( $rtable_name ) ) { + push @rfields, $ref_table->primary_key; + + die "FK constraint on " . $rtable_name . '.' . join('', @fields) . " has no reference fields\n" + unless @rfields; + } + else { + die "Can't find reference table '$rtable_name' in schema\n"; + } } - $field_def .= sprintf " %s%s", $data_type, - ( !$field->is_auto_increment && $size ) ? "($size)" : ''; - - # Null? - $field_def .= ' NOT NULL' unless $field->is_nullable; - - # Default? - SQL::Translator::Producer->_apply_default_value( - $field, - \$field_def, - [ - 'NULL' => \'NULL', - 'now()' => 'now()', - 'CURRENT_TIMESTAMP' => 'CURRENT_TIMESTAMP', - ], - ); + my $fk_sql = sprintf 'FOREIGN KEY (%s) REFERENCES %s(%s)', + join (', ', map { _generator()->quote($_) } @fields ), + _generator()->quote($c->reference_table), + join (', ', map { _generator()->quote($_) } @rfields ) + ; - return $field_def; + $fk_sql .= " ON DELETE " . $c->{on_delete} if $c->{on_delete}; + $fk_sql .= " ON UPDATE " . $c->{on_update} if $c->{on_update}; + return $fk_sql; } +sub create_field { return _generator()->field($_[0]) } + sub create_index { my ($index, $options) = @_; - my $name = $index->name; - $name = mk_name($name); + (my $index_table_name = $index->table->name) =~ s/^.+?\.//; # table name may not specify schema + my $name = mk_name($index->name || "${index_table_name}_idx"); - my $type = $index->type eq 'UNIQUE' ? "UNIQUE " : ''; + my $type = $index->type eq 'UNIQUE' ? "UNIQUE " : ''; # strip any field size qualifiers as SQLite doesn't like these - my @fields = map { s/\(\d+\)$//; $_ } $index->fields; - (my $index_table_name = $index->table->name) =~ s/^.+?\.//; # table name may not specify schema + my @fields = map { s/\(\d+\)$//; _generator()->quote($_) } $index->fields; + $index_table_name = _generator()->quote($index_table_name); warn "removing schema name from '" . $index->table->name . "' to make '$index_table_name'\n" if $WARN; - my $index_def = + my $index_def = "CREATE ${type}INDEX $name ON " . $index_table_name . ' (' . join( ', ', @fields ) . ')'; @@ -344,13 +312,13 @@ sub create_constraint { my ($c, $options) = @_; - my $name = $c->name; - $name = mk_name($name); - my @fields = $c->fields; (my $index_table_name = $c->table->name) =~ s/^.+?\.//; # table name may not specify schema + my $name = mk_name($c->name || "${index_table_name}_idx"); + my @fields = map _generator()->quote($_), $c->fields; + $index_table_name = _generator()->quote($index_table_name); warn "removing schema name from '" . $c->table->name . "' to make '$index_table_name'\n" if $WARN; - my $c_def = + my $c_def = "CREATE UNIQUE INDEX $name ON " . $index_table_name . ' (' . join( ', ', @fields ) . ')'; @@ -364,6 +332,8 @@ sub create_trigger { my @statements; my $trigger_name = $trigger->name; + $global_names{$trigger_name} = 1; + my $events = $trigger->database_events; for my $evt ( @$events ) { @@ -375,13 +345,16 @@ sub create_trigger { "creating trigger '$trig_name' for the '$evt' event.\n" if $WARN; } + $trig_name = _generator()->quote($trig_name); push @statements, "DROP TRIGGER IF EXISTS $trig_name" if $add_drop; $DB::single = 1; my $action = ""; if (not ref $trigger->action) { - $action .= "BEGIN " . $trigger->action . " END"; + $action = $trigger->action; + $action = "BEGIN " . $action . " END" + unless $action =~ /^ \s* BEGIN [\s\;] .*? [\s\;] END [\s\;]* $/six; } else { $action = $trigger->action->{for_each} . " " @@ -402,7 +375,7 @@ sub create_trigger { $trig_name, $trigger->perform_action_when, $evt, - $trigger->on_table, + _generator()->quote($trigger->on_table), $action ); } @@ -410,13 +383,13 @@ sub create_trigger { return @statements; } -sub alter_table { } # Noop +sub alter_table { () } # Noop sub add_field { my ($field) = @_; return sprintf("ALTER TABLE %s ADD COLUMN %s", - $field->table->name, create_field($field)) + _generator()->quote($field->table->name), create_field($field)) } sub alter_create_index { @@ -438,11 +411,11 @@ sub alter_drop_index { my ($constraint) = @_; return sprintf("DROP INDEX %s", - $constraint->name); + _generator()->quote($constraint->name)); } sub batch_alter_table { - my ($table, $diffs) = @_; + my ($table, $diffs, $options) = @_; # If we have any of the following # @@ -464,64 +437,105 @@ sub batch_alter_table { # Fun, eh? # # If we have rename_field we do similarly. + # + # We create the temporary table as a copy of the new table, copy all data + # to temp table, create new table and then copy as appropriate taking note + # of renamed fields. my $table_name = $table->name; - my $renaming = $diffs->{rename_table} && @{$diffs->{rename_table}}; if ( @{$diffs->{rename_field}} == 0 && @{$diffs->{alter_field}} == 0 && @{$diffs->{drop_field}} == 0 ) { -# return join("\n", map { - return map { - my $meth = __PACKAGE__->can($_) or die __PACKAGE__ . " cant $_"; - map { my $sql = $meth->(ref $_ eq 'ARRAY' ? @$_ : $_); $sql ? ("$sql") : () } @{ $diffs->{$_} } - - } grep { @{$diffs->{$_}} } - 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/; + return batch_alter_table_statements($diffs, $options); } - my @sql; - my $old_table = $renaming ? $diffs->{rename_table}[0][0] : $table; - + + # $table is the new table but we may need an old one + # TODO: this is NOT very well tested at the moment so add more tests + + my $old_table = $table; + + if ( $diffs->{rename_table} && @{$diffs->{rename_table}} ) { + $old_table = $diffs->{rename_table}[0][0]; + } + + my $temp_table_name = $table_name . '_temp_alter'; + + # CREATE TEMPORARY TABLE t1_backup(a,b); + + my %temp_table_fields; do { - local $table->{name} = $table_name . '_temp_alter'; - # We only want the table - dont care about indexes on tmp table + local $table->{name} = $temp_table_name; + # We only want the table - don't care about indexes on tmp table my ($table_sql) = create_table($table, {no_comments => 1, temporary_table => 1}); push @sql,$table_sql; + + %temp_table_fields = map { $_ => 1} $table->get_fields; }; - push @sql, "INSERT INTO @{[$table_name]}_temp_alter SELECT @{[ join(', ', $old_table->get_fields)]} FROM @{[$old_table]}", - "DROP TABLE @{[$old_table]}", - create_table($table, { no_comments => 1 }), - "INSERT INTO @{[$table_name]} SELECT @{[ join(', ', $old_table->get_fields)]} FROM @{[$table_name]}_temp_alter", - "DROP TABLE @{[$table_name]}_temp_alter"; + # record renamed fields for later + my %rename_field = map { $_->[1]->name => $_->[0]->name } @{$diffs->{rename_field}}; + + # drop added fields from %temp_table_fields + delete @temp_table_fields{@{$diffs->{add_field}}}; + + # INSERT INTO t1_backup SELECT a,b FROM t1; + + push @sql, sprintf( 'INSERT INTO %s( %s) SELECT %s FROM %s', + + _generator()->quote( $temp_table_name ), + + join( ', ', + map _generator()->quote($_), + grep { $temp_table_fields{$_} } $table->get_fields ), + + join( ', ', + map _generator()->quote($_), + map { $rename_field{$_} ? $rename_field{$_} : $_ } + grep { $temp_table_fields{$_} } $table->get_fields ), + + _generator()->quote( $old_table->name ) + ); + + # DROP TABLE t1; + + push @sql, sprintf('DROP TABLE %s', _generator()->quote($old_table->name)); + + # CREATE TABLE t1(a,b); + + push @sql, create_table($table, { no_comments => 1 }); + + # INSERT INTO t1 SELECT a,b FROM t1_backup; + + push @sql, sprintf('INSERT INTO %s SELECT %s FROM %s', + _generator()->quote($table_name), + join(', ', map _generator()->quote($_), $table->get_fields), + _generator()->quote($temp_table_name) + ); + + # DROP TABLE t1_backup; + + push @sql, sprintf('DROP TABLE %s', _generator()->quote($temp_table_name)); - return @sql; -# return join("", @sql, ""); + return wantarray ? @sql : join(";\n", @sql); } sub drop_table { my ($table) = @_; + $table = _generator()->quote($table); return "DROP TABLE $table"; } sub rename_table { my ($old_table, $new_table, $options) = @_; - my $qt = $options->{quote_table_names} || ''; + $old_table = _generator()->quote($old_table); + $new_table = _generator()->quote($new_table); - return "ALTER TABLE $qt$old_table$qt RENAME TO $qt$new_table$qt"; + return "ALTER TABLE $old_table RENAME TO $new_table"; }