diff options
| -rwxr-xr-x | scripts/mkdb.sh | 7 | ||||
| -rw-r--r-- | yt/src/storage/migrate/sql/3_Two_to_Three.sql | 23 |
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; |