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

diff --git a/AUTHORS b/AUTHORS
index ec6fc89..c29a0b1 100644 (file)
--- a/AUTHORS
+++ b/AUTHORS
@@ -27,6 +27,7 @@ The following people have contributed to the SQLFairy project:
 -   Jason Williams <smdwilliams@users.sourceforge.net>
 -   Johan Viklund <viklund@cpan.org>
 -   John Goulah <jgoulah@cpan.org>
+-   John Napiorkowski <jjnapiork@cpan.org>
 -   Jonathan Yu <frequency@cpan.org>
 -   Ken Youens-Clark <kclark@cpan.org>
 -   Kevin McClellan <kdmcclel@gmail.com>

diff --git a/Changes b/Changes
index 1929da7..2710192 100644 (file)
--- a/Changes
+++ b/Changes
@@ -3,6 +3,7 @@
 * ON DELETE/UPDATE actions for SQLite (patch from Lukas Thiemeier)
   closes RT#70734, RT#71283, RT#70378
 * Proper quoting support in SQLite
+* Fix data preservation on SQLite diffs involving adding/dropping columns
 * Support for triggers in PostgreSQL producer and parser
 * Correct Data Type in SQLT::Parser::DBI::PostgreSQL (patch from Andrew Pam)
 * Fix index issue in SQLT::Parser::DBI::PostgreSQL

diff --git a/lib/SQL/Translator/Producer/SQLite.pm b/lib/SQL/Translator/Producer/SQLite.pm
index a4a7fa4..5d80337 100644 (file)
--- a/lib/SQL/Translator/Producer/SQLite.pm
+++ b/lib/SQL/Translator/Producer/SQLite.pm
@@ -441,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, "");
 }

diff --git a/t/30sqlt-new-diff-sqlite.t b/t/30sqlt-new-diff-sqlite.t
index d77e7b7..926162a 100644 (file)
--- a/t/30sqlt-new-diff-sqlite.t
+++ b/t/30sqlt-new-diff-sqlite.t
@@ -102,7 +102,7 @@ CREATE TEMPORARY TABLE "employee_temp_alter" (
   FOREIGN KEY ("employee_id") REFERENCES "person"("person_id")
 );
 
-INSERT INTO "employee_temp_alter" SELECT "position", "employee_id" FROM "employee";
+INSERT INTO "employee_temp_alter"( "position", "employee_id") SELECT "position", "employee_id" FROM "employee";
 
 DROP TABLE "employee";
 
@@ -131,7 +131,7 @@ CREATE TEMPORARY TABLE "person_temp_alter" (
   "physical_description" text
 );
 
-INSERT INTO "person_temp_alter" SELECT "person_id", "name", "age", "weight", "iq", "is_rock_star", "physical_description" FROM "person";
+INSERT INTO "person_temp_alter"( "person_id", "name", "age", "weight", "iq", "is_rock_star", "physical_description") SELECT "person_id", "name", "age", "weight", "iq", "is_rock_star", "physical_description" FROM "person";
 
 DROP TABLE "person";
 

diff --git a/t/72-sqlite-add-drop-fields.t b/t/72-sqlite-add-drop-fields.t
new file mode 100644 (file)
index 0000000..bdc3f93
--- /dev/null
+++ b/t/72-sqlite-add-drop-fields.t
@@ -0,0 +1,69 @@
+#!/usr/bin/env perl
+
+use strict;
+use warnings;
+
+use Test::More tests => 4;
+use Test::Differences;
+use SQL::Translator;
+use SQL::Translator::Parser::SQLite;
+use SQL::Translator::Diff;
+
+
+ok my $version1 = SQL::Translator->new(from=>'SQLite')
+  ->translate(\<<SQL);
+CREATE TABLE "Foo" (
+  "foo" INTEGER PRIMARY KEY NOT NULL,
+  "bar" VARCHAR(10) NOT NULL,
+  "biff" VARCHAR(10)
+);
+SQL
+
+ok my $version2 = SQL::Translator->new(from=>'SQLite')
+  ->translate(\<<SQL);
+CREATE TABLE "Foo" (
+  "foo" INTEGER PRIMARY KEY NOT NULL,
+  "bar" VARCHAR(10) NOT NULL,
+  "baz" VARCHAR(10),
+  "doomed" VARCHAR(10)
+);
+SQL
+
+ok my $upgrade_sql = SQL::Translator::Diff->new({
+  output_db     => 'SQLite',
+  source_schema => $version1,
+  target_schema => $version2,
+})->compute_differences->produce_diff_sql;
+
+eq_or_diff($upgrade_sql, <<'## END OF DIFF', "Diff as expected");
+-- Convert schema '' to '':;
+
+BEGIN;
+
+CREATE TEMPORARY TABLE "Foo_temp_alter" (
+  "foo" INTEGER PRIMARY KEY NOT NULL,
+  "bar" VARCHAR(10) NOT NULL,
+  "baz" VARCHAR(10),
+  "doomed" VARCHAR(10)
+);
+
+INSERT INTO "Foo_temp_alter"( "foo", "bar") SELECT "foo", "bar" FROM "Foo";
+
+DROP TABLE "Foo";
+
+CREATE TABLE "Foo" (
+  "foo" INTEGER PRIMARY KEY NOT NULL,
+  "bar" VARCHAR(10) NOT NULL,
+  "baz" VARCHAR(10),
+  "doomed" VARCHAR(10)
+);
+
+INSERT INTO "Foo" SELECT "foo", "bar", "baz", "doomed" FROM "Foo_temp_alter";
+
+DROP TABLE "Foo_temp_alter";
+
+
+COMMIT;
+
+## END OF DIFF
+