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

2 files changed, 28 insertions, 2 deletions

diff --git a/scripts/mkdb.sh b/scripts/mkdb.sh
index 6bcebaf..a6b453c 100755
--- a/scripts/mkdb.sh
+++ b/scripts/mkdb.sh
@@ -18,7 +18,12 @@ db="$root/target/database.sqlx"
 
 fd . "$root/yt/src/storage/migrate/sql" | while read -r sql_file; do
     echo "Applying sql migration file: $(basename "$sql_file").."
-    sqlite3 "$db" <"$sql_file"
+    {
+        # NOTE(@bpeetz): The wrapping in a transaction is needed to simulate the rust code. <2025-05-07>
+        echo "BEGIN TRANSACTION;"
+        cat "$sql_file"
+        echo "COMMIT TRANSACTION;"
+    } | sqlite3 "$db"
 done
 
 # vim: ft=sh
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;