Use legacy_alter_table ON in sqlite recreate_table

Use "PRAGMA legacy_alter_table = ON" with sqlite >= 3.26 when
using "ALTER TABLE RENAME TO migration_tmp" to maintain legacy
behavior.

As of sqlite version 3.26, when a table is renamed using
"ALTER TABLE RENAME TO", REFERENCES clauses that refer to the
table will be updated. To maintain legacy (3.24 and earlier)
behavior, "PRAGMA legacy_alter_table" can be set to true and
"PRAGMA foreign_keys" can be set to false. [1]

[1] https://www.sqlite.org/src/info/ae9638e9c0ad0c36

Thanks to "László Böszörményi (GCS)" <gcs@debian.org> for
providing the code for this patch, which has since been
slightly modified.

Change-Id: I539988ab2ad6df6c8f423ecec15364ad8fcc7267
Closes-Bug: 1807262

1 files changed, 9 insertions, 0 deletions

diff --git a/migrate/changeset/databases/sqlite.py b/migrate/changeset/databases/sqlite.py
index 92d42f2..d44b3b3 100644
--- a/migrate/changeset/databases/sqlite.py
+++ b/migrate/changeset/databases/sqlite.py
@@ -96,8 +96,17 @@ class SQLiteHelper(SQLiteCommon):
             if omit_constraints is None or cons.name not in omit_constraints
         ])
 
+        # Use "PRAGMA legacy_alter_table = ON" with sqlite >= 3.26 when
+        # using "ALTER TABLE RENAME TO migration_tmp" to maintain legacy
+        # behavior. See: https://www.sqlite.org/src/info/ae9638e9c0ad0c36
+        if self.connection.engine.dialect.server_version_info >= (3, 26):
+            self.append('PRAGMA legacy_alter_table = ON')
+            self.execute()
         self.append('ALTER TABLE %s RENAME TO migration_tmp' % table_name)
         self.execute()
+        if self.connection.engine.dialect.server_version_info >= (3, 26):
+            self.append('PRAGMA legacy_alter_table = OFF')
+            self.execute()
 
         insertion_string = self._modify_table(table, column, delta)