Skip to content

Commit bd935b3

Browse files
josecelanojosecelano
committed
fix: [#526] DB migration for SQLite. Backup secondary tables
The table `torrust_torrents` has 8 secondary tables that reference it with a foreign key: ```sql FOREIGN KEY("torrent_id") REFERENCES "torrust_torrents"("torrent_id") ON DELETE CASCADE, ``` The migration fixed in this commit creates a new table in order to alter it and drops the old one. However, when you drop the old table, SQLite uses an implicit DELETE query for the `torrust_torrents` table which triggers a DELETE ON CASCADE, deleting all the related records in secondary tables. The secondary tables are: - torrust_torrent_files - torrust_torrent_announce_urls - torrust_torrent_info - torrust_torrent_tracker_stats - torrust_torrent_tag_links - torrust_torrent_info_hashes - torrust_torrent_http_seeds - torrust_torrent_nodes These tables store the torrent file fiel together with the master `torrust_torrents`.
1 parent 0cc2663 commit bd935b3

File tree

1 file changed

+90
-7
lines changed

1 file changed

+90
-7
lines changed

‎migrations/sqlite3/20240305120015_torrust_add_independent_root_hash_field.sql‎

Lines changed: 90 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -1,6 +1,68 @@
1-
-- add field `root_hash` and make `pieces` nullable
1+
PRAGMA foreign_keys = off;
2+
3+
-- Step 1: backup secondary tables. They will be truncated because of the DELETE ON CASCADE
4+
CREATE TEMPORARY TABLE IF NOT EXISTS "torrust_torrent_files_backup" (
5+
"file_id" INTEGER NOT NULL,
6+
"torrent_id" INTEGER NOT NULL,
7+
"md5sum" TEXT DEFAULT NULL,
8+
"length" BIGINT NOT NULL,
9+
"path" TEXT DEFAULT NULL
10+
);
11+
INSERT INTO torrust_torrent_files_backup SELECT * FROM torrust_torrent_files;
12+
13+
CREATE TEMPORARY TABLE IF NOT EXISTS "torrust_torrent_announce_urls_backup" (
14+
"announce_url_id" INTEGER NOT NULL,
15+
"torrent_id" INTEGER NOT NULL,
16+
"tracker_url" TEXT NOT NULL
17+
);
18+
INSERT INTO torrust_torrent_announce_urls_backup SELECT * FROM torrust_torrent_announce_urls;
19+
20+
CREATE TEMPORARY TABLE IF NOT EXISTS "torrust_torrent_info_backup" (
21+
"torrent_id" INTEGER NOT NULL,
22+
"title" VARCHAR(256) NOT NULL UNIQUE,
23+
"description" TEXT DEFAULT NULL
24+
);
25+
INSERT INTO torrust_torrent_info_backup SELECT * FROM torrust_torrent_info;
26+
27+
CREATE TEMPORARY TABLE IF NOT EXISTS "torrust_torrent_tracker_stats_backup" (
28+
"torrent_id" INTEGER NOT NULL,
29+
"tracker_url" VARCHAR(256) NOT NULL,
30+
"seeders" INTEGER NOT NULL DEFAULT 0,
31+
"leechers" INTEGER NOT NULL DEFAULT 0
32+
);
33+
INSERT INTO torrust_torrent_tracker_stats_backup SELECT * FROM torrust_torrent_tracker_stats;
34+
35+
CREATE TEMPORARY TABLE IF NOT EXISTS "torrust_torrent_tag_links_backup" (
36+
"torrent_id" INTEGER NOT NULL,
37+
"tag_id" INTEGER NOT NULL
38+
);
39+
INSERT INTO torrust_torrent_tag_links_backup SELECT * FROM torrust_torrent_tag_links;
40+
41+
CREATE TEMPORARY TABLE IF NOT EXISTS "torrust_torrent_info_hashes_backup" (
42+
"info_hash" TEXT NOT NULL,
43+
"canonical_info_hash" TEXT NOT NULL,
44+
"original_is_known" BOOLEAN NOT NULL
45+
);
46+
INSERT INTO torrust_torrent_info_hashes_backup SELECT * FROM torrust_torrent_info_hashes;
47+
48+
CREATE TEMPORARY TABLE IF NOT EXISTS "torrust_torrent_http_seeds_backup" (
49+
"http_seed_id" INTEGER NOT NULL,
50+
"torrent_id" INTEGER NOT NULL,
51+
"seed_url" TEXT NOT NULL
52+
);
53+
INSERT INTO torrust_torrent_http_seeds_backup SELECT * FROM torrust_torrent_http_seeds;
54+
55+
CREATE TEMPORARY TABLE IF NOT EXISTS "torrust_torrent_nodes_backup" (
56+
"node_id" INTEGER NOT NULL,
57+
"torrent_id" INTEGER NOT NULL,
58+
"node_ip" TEXT NOT NULL,
59+
"node_port" INTEGER NOT NULL
60+
);
61+
INSERT INTO torrust_torrent_nodes_backup SELECT * FROM torrust_torrent_nodes;
62+
63+
-- Step 2: Add field `root_hash` and make `pieces` nullable
264
CREATE TABLE
3-
"torrust_torrents_new" (
65+
IF NOT EXISTS "torrust_torrents_new" (
466
"torrent_id" INTEGER NOT NULL,
567
"uploader_id" INTEGER NOT NULL,
668
"category_id" INTEGER,
@@ -23,7 +85,7 @@ CREATE TABLE
2385
PRIMARY KEY ("torrent_id" AUTOINCREMENT)
2486
);
2587

26-
-- Step 2: Copy data from the old table to the new table
88+
-- Step 3: Copy data from the old table to the new table
2789
INSERT INTO
2890
torrust_torrents_new (
2991
torrent_id,
@@ -69,9 +131,30 @@ SELECT
69131
FROM
70132
torrust_torrents;
71133

72-
-- Step 3: Drop the old table
134+
-- Step 4: Drop the old table
73135
DROP TABLE torrust_torrents;
74136

75-
-- Step 4: Rename the new table to the original name
76-
ALTER TABLE torrust_torrents_new
77-
RENAME TO torrust_torrents;
137+
-- Step 5: Rename the new table to the original name
138+
ALTER TABLE torrust_torrents_new RENAME TO torrust_torrents;
139+
140+
-- Step 6: Repopulate secondary tables from backup tables
141+
INSERT INTO torrust_torrent_files SELECT * FROM torrust_torrent_files_backup;
142+
INSERT INTO torrust_torrent_announce_urls SELECT * FROM torrust_torrent_announce_urls_backup;
143+
INSERT INTO torrust_torrent_info SELECT * FROM torrust_torrent_info_backup;
144+
INSERT INTO torrust_torrent_tracker_stats SELECT * FROM torrust_torrent_tracker_stats_backup;
145+
INSERT INTO torrust_torrent_tag_links SELECT * FROM torrust_torrent_tag_links_backup;
146+
INSERT INTO torrust_torrent_info_hashes SELECT * FROM torrust_torrent_info_hashes_backup;
147+
INSERT INTO torrust_torrent_http_seeds SELECT * FROM torrust_torrent_http_seeds_backup;
148+
INSERT INTO torrust_torrent_nodes SELECT * FROM torrust_torrent_nodes_backup;
149+
150+
-- Step 7: Drop temporary secondary table backups
151+
DROP TABLE torrust_torrent_files_backup;
152+
DROP TABLE torrust_torrent_announce_urls_backup;
153+
DROP TABLE torrust_torrent_info_backup;
154+
DROP TABLE torrust_torrent_tracker_stats_backup;
155+
DROP TABLE torrust_torrent_tag_links_backup;
156+
DROP TABLE torrust_torrent_info_hashes_backup;
157+
DROP TABLE torrust_torrent_http_seeds_backup;
158+
DROP TABLE torrust_torrent_nodes_backup;
159+
160+
PRAGMA foreign_keys = on;

0 commit comments

Comments
 (0)