X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?a=blobdiff_plain;f=lib%2FSQL%2FTranslator%2FProducer%2FSQLite.pm;h=aefd038ceb1f68df436a3202b346df435d375065;hb=c9c8f3e19f6af2ce0fe5cacd55b87b1d88c6e5eb;hp=f3e36201a59901908e62ff56bc1c4f0aff6b0bd0;hpb=d8cf2279bd5e25db979d6251c5648d69fc5a298d;p=dbsrgits%2FSQL-Translator.git diff --git a/lib/SQL/Translator/Producer/SQLite.pm b/lib/SQL/Translator/Producer/SQLite.pm index f3e3620..aefd038 100644 --- a/lib/SQL/Translator/Producer/SQLite.pm +++ b/lib/SQL/Translator/Producer/SQLite.pm @@ -21,9 +21,8 @@ use strict; use warnings; use Data::Dumper; use SQL::Translator::Schema::Constants; -use SQL::Translator::Utils qw(debug header_comment parse_dbms_version); -use SQL::Translator::ProducerUtils; -my $util = SQL::Translator::ProducerUtils->new( quote_chars => q(') ); +use SQL::Translator::Utils qw(debug header_comment parse_dbms_version batch_alter_table_statements); +use SQL::Translator::Generator::DDL::SQLite; our ( $DEBUG, $WARN ); our $VERSION = '1.59'; @@ -33,6 +32,18 @@ $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; @@ -50,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,14 +122,14 @@ sub mk_name { } $scope->{ $name }++; - return $util->quote($name); + return _generator()->quote($name); } sub create_view { my ($view, $options) = @_; my $add_drop_view = $options->{add_drop_view}; - my $view_name = $util->quote($view->name); + my $view_name = _generator()->quote($view->name); $global_names{$view->name} = 1; debug("PKG: Looking at view '${view_name}'\n"); @@ -148,7 +163,7 @@ sub create_table { my ($table, $options) = @_; - my $table_name = $util->quote($table->name); + my $table_name = _generator()->quote($table->name); $global_names{$table->name} = 1; my $no_comments = $options->{no_comments}; @@ -204,7 +219,7 @@ sub create_table || ( @pk_fields && !grep /INTEGER PRIMARY KEY/, @field_defs ) ) { - push @field_defs, 'PRIMARY KEY (' . join(', ', map $util->quote($_), @pk_fields ) . ')'; + push @field_defs, 'PRIMARY KEY (' . join(', ', map _generator()->quote($_), @pk_fields ) . ')'; } # @@ -235,92 +250,35 @@ sub create_table sub create_foreignkey { my $c = shift; - my $field = $util->quote($c->{fields}[0]); - my $table = $c->{reference_table} ? $util->quote($c->{reference_table}) : ''; - my $ref = $c->{reference_fields}[0] ? $util->quote($c->{reference_fields}[0]) : ''; - my $fk_sql = "FOREIGN KEY($field) REFERENCES "; - $fk_sql .= "$table($ref)"; - - return $fk_sql; -} - -sub create_field -{ - my ($field, $options) = @_; - - my $field_name = $util->quote($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; - } + 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; -# 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" -# ; -# -# } - - # - # 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 : (); - - 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; + 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) = @_; @@ -331,9 +289,9 @@ sub create_index my $type = $index->type eq 'UNIQUE' ? "UNIQUE " : ''; # strip any field size qualifiers as SQLite doesn't like these - my @fields = map { s/\(\d+\)$//; $util->quote($_) } $index->fields; + my @fields = map { s/\(\d+\)$//; _generator()->quote($_) } $index->fields; (my $index_table_name = $index->table->name) =~ s/^.+?\.//; # table name may not specify schema - $index_table_name = $util->quote($index_table_name); + $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 = "CREATE ${type}INDEX $name ON " . $index_table_name . @@ -348,9 +306,9 @@ sub create_constraint my $name = $c->name; $name = mk_name($name); - my @fields = map $util->quote($_), $c->fields; + my @fields = map _generator()->quote($_), $c->fields; (my $index_table_name = $c->table->name) =~ s/^.+?\.//; # table name may not specify schema - $index_table_name = $util->quote($index_table_name); + $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 = @@ -380,7 +338,7 @@ sub create_trigger { "creating trigger '$trig_name' for the '$evt' event.\n" if $WARN; } - $trig_name = $util->quote($trig_name); + $trig_name = _generator()->quote($trig_name); push @statements, "DROP TRIGGER IF EXISTS $trig_name" if $add_drop; @@ -408,7 +366,7 @@ sub create_trigger { $trig_name, $trigger->perform_action_when, $evt, - $util->quote($trigger->on_table), + _generator()->quote($trigger->on_table), $action ); } @@ -422,7 +380,7 @@ sub add_field { my ($field) = @_; return sprintf("ALTER TABLE %s ADD COLUMN %s", - $util->quote($field->table->name), create_field($field)) + _generator()->quote($field->table->name), create_field($field)) } sub alter_create_index { @@ -444,11 +402,11 @@ sub alter_drop_index { my ($constraint) = @_; return sprintf("DROP INDEX %s", - $util->quote($constraint->name)); + _generator()->quote($constraint->name)); } sub batch_alter_table { - my ($table, $diffs) = @_; + my ($table, $diffs, $options) = @_; # If we have any of the following # @@ -470,64 +428,103 @@ 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 @{[$util->quote($table_name.'_temp_alter')]} SELECT @{[ join(', ', map $util->quote($_), $old_table->get_fields)]} FROM @{[$util->quote($old_table)]}", - "DROP TABLE @{[$util->quote($old_table)]}", - create_table($table, { no_comments => 1 }), - "INSERT INTO @{[$util->quote($table_name)]} SELECT @{[ join(', ', map $util->quote($_), $old_table->get_fields)]} FROM @{[$util->quote($table_name.'_temp_alter')]}", - "DROP TABLE @{[$util->quote($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 = $util->quote($table); + $table = _generator()->quote($table); return "DROP TABLE $table"; } sub rename_table { my ($old_table, $new_table, $options) = @_; - $old_table = $util->quote($old_table); - $new_table = $util->quote($new_table); + $old_table = _generator()->quote($old_table); + $new_table = _generator()->quote($new_table); return "ALTER TABLE $old_table RENAME TO $new_table";