fix(yt/storage/migrate): Correct the two to three migration script

1 files changed, 22 insertions, 1 deletions

diff --git a/yt/src/storage/migrate/sql/3_Two_to_Three.sql b/yt/src/storage/migrate/sql/3_Two_to_Three.sql
index 445a9ec..b33f849 100644
--- a/yt/src/storage/migrate/sql/3_Two_to_Three.sql
+++ b/yt/src/storage/migrate/sql/3_Two_to_Three.sql
@@ -14,6 +14,15 @@
 -- 3. Drop old table
 -- 4. Rename new into old
 
+-- remove the original TRANSACTION
+COMMIT TRANSACTION;
+
+-- tweak config
+PRAGMA foreign_keys=OFF;
+
+-- start your own TRANSACTION
+BEGIN TRANSACTION;
+
 CREATE TABLE videos_new (
     cache_path                  TEXT    UNIQUE                       CHECK (CASE
                                                                               WHEN cache_path IS NOT NULL THEN status == 2
@@ -42,7 +51,7 @@ CREATE TABLE videos_new (
     watch_progress              INTEGER        NOT NULL DEFAULT 0    CHECK (watch_progress <= duration)
 ) STRICT;
 
-INSERT INTO videos SELECT
+INSERT INTO videos_new SELECT
     videos.cache_path,
     videos.description,
     videos.duration,
@@ -62,3 +71,15 @@ FROM videos, (SELECT NULL AS is_focused) AS dummy;
 DROP TABLE videos;
 
 ALTER TABLE videos_new RENAME TO videos;
+
+-- check foreign key constraint still upholding.
+PRAGMA foreign_key_check;
+
+-- commit your own TRANSACTION
+COMMIT TRANSACTION;
+
+-- rollback all config you setup before.
+PRAGMA foreign_keys=ON;
+
+-- start a new TRANSACTION to let migrator commit it.
+BEGIN TRANSACTION;