fix for when we are adding /dropping columns in sqlite and need to roundtrip via...

[dbsrgits/SQL-Translator.git] / lib / SQL / Translator / Producer / SQLite.pm

diff --git a/lib/SQL/Translator/Producer/SQLite.pm b/lib/SQL/Translator/Producer/SQLite.pm
index f3e3620..5d80337 100644 (file)
--- a/lib/SQL/Translator/Producer/SQLite.pm
+++ b/lib/SQL/Translator/Producer/SQLite.pm
@@ -22,8 +22,9 @@ 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::Generator::Utils;
+my $util = SQL::Translator::Generator::Utils->new( quote_chars => q(") );
+use SQL::Translator::Generator::DDL::SQLite;
 
 our ( $DEBUG, $WARN );
 our $VERSION = '1.59';
@@ -32,6 +33,7 @@ $WARN = 0 unless defined $WARN;
 
 our $max_id_length    = 30;
 my %global_names;
+my $future = SQL::Translator::Generator::DDL::SQLite->new();
 
 sub produce {
     my $translator     = shift;
@@ -235,92 +237,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)";
+    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;
 
-    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;
-    }
-
-#             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 { $util->quote($_) } @fields ),
+        $util->quote($c->reference_table),
+        join (', ', map { $util->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 $future->field($_[0]) }
+
 sub create_index
 {
     my ($index, $options) = @_;
@@ -496,23 +441,28 @@ sub batch_alter_table {
        alter_table/;
   }
 
-
   my @sql;
   my $old_table = $renaming ? $diffs->{rename_table}[0][0] : $table;
 
+  if(@{$diffs->{drop_field}}) {
+    $old_table =$diffs->{drop_field}[0]->table;
+  }
+
+  my %temp_table_fields;
   do {
     local $table->{name} = $table_name . '_temp_alter';
     # We only want the table - dont 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)]}",
+  push @sql, "INSERT INTO @{[$util->quote($table_name.'_temp_alter')]}( @{[ join(', ', map $util->quote($_), grep { $temp_table_fields{$_} } $old_table->get_fields)]}) SELECT @{[ join(', ', map $util->quote($_), grep { $temp_table_fields{$_} } $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')]}",
+             "INSERT INTO @{[$util->quote($table_name)]} SELECT @{[ join(', ', map $util->quote($_), $table->get_fields)]} FROM @{[$util->quote($table_name.'_temp_alter')]}",
              "DROP TABLE @{[$util->quote($table_name.'_temp_alter')]}";
-
   return @sql;
 #  return join("", @sql, "");
 }