aboutsummaryrefslogtreecommitdiffhomepage
path: root/server/initializers/migrations
diff options
context:
space:
mode:
authorChocobozzz <me@florianbigard.com>2021-02-18 10:15:11 +0100
committerChocobozzz <chocobozzz@cpy.re>2021-02-18 13:38:09 +0100
commitd9a2a03196275065c28f4a0b7d4d7bc9992d77a1 (patch)
tree14579db95cd07506bf3d8e5c0af3ef1630e8700c /server/initializers/migrations
parent2451916e45420fedf556913ce121f3964c4b57d6 (diff)
downloadPeerTube-d9a2a03196275065c28f4a0b7d4d7bc9992d77a1.tar.gz
PeerTube-d9a2a03196275065c28f4a0b7d4d7bc9992d77a1.tar.zst
PeerTube-d9a2a03196275065c28f4a0b7d4d7bc9992d77a1.zip
Don't guess remote tracker URL
Diffstat (limited to 'server/initializers/migrations')
-rw-r--r--server/initializers/migrations/0590-trackers.ts44
-rw-r--r--server/initializers/migrations/0595-remote-url.ts130
2 files changed, 174 insertions, 0 deletions
diff --git a/server/initializers/migrations/0590-trackers.ts b/server/initializers/migrations/0590-trackers.ts
new file mode 100644
index 000000000..47b9022a3
--- /dev/null
+++ b/server/initializers/migrations/0590-trackers.ts
@@ -0,0 +1,44 @@
1import * as Sequelize from 'sequelize'
2
3async function up (utils: {
4 transaction: Sequelize.Transaction
5 queryInterface: Sequelize.QueryInterface
6 sequelize: Sequelize.Sequelize
7 db: any
8}): Promise<void> {
9 {
10 const query = `CREATE TABLE IF NOT EXISTS "tracker" (
11 "id" serial,
12 "url" varchar(255) NOT NULL,
13 "createdAt" timestamp WITH time zone NOT NULL,
14 "updatedAt" timestamp WITH time zone NOT NULL,
15 PRIMARY KEY ("id")
16 );`
17
18 await utils.sequelize.query(query)
19 }
20
21 {
22 const query = `CREATE TABLE IF NOT EXISTS "videoTracker" (
23 "videoId" integer REFERENCES "video" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
24 "trackerId" integer REFERENCES "tracker" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
25 "createdAt" timestamp WITH time zone NOT NULL,
26 "updatedAt" timestamp WITH time zone NOT NULL,
27 UNIQUE ("videoId", "trackerId"),
28 PRIMARY KEY ("videoId", "trackerId")
29 );`
30
31 await utils.sequelize.query(query)
32 }
33
34 await utils.sequelize.query(`CREATE UNIQUE INDEX "tracker_url" ON "tracker" ("url")`)
35}
36
37function down (options) {
38 throw new Error('Not implemented.')
39}
40
41export {
42 up,
43 down
44}
diff --git a/server/initializers/migrations/0595-remote-url.ts b/server/initializers/migrations/0595-remote-url.ts
new file mode 100644
index 000000000..85b367555
--- /dev/null
+++ b/server/initializers/migrations/0595-remote-url.ts
@@ -0,0 +1,130 @@
1import * as Sequelize from 'sequelize'
2
3async function up (utils: {
4 transaction: Sequelize.Transaction
5 queryInterface: Sequelize.QueryInterface
6 sequelize: Sequelize.Sequelize
7 db: any
8}): Promise<void> {
9
10 // Torrent and file URLs
11 {
12 const fromQueryWebtorrent = `SELECT 'https://' || server.host AS "serverUrl", '/static/webseed/' AS "filePath", "videoFile".id ` +
13 `FROM video ` +
14 `INNER JOIN "videoChannel" ON "videoChannel".id = video."channelId" ` +
15 `INNER JOIN actor ON actor.id = "videoChannel"."actorId" ` +
16 `INNER JOIN server ON server.id = actor."serverId" ` +
17 `INNER JOIN "videoFile" ON "videoFile"."videoId" = video.id ` +
18 `WHERE video.remote IS TRUE`
19
20 const fromQueryHLS = `SELECT 'https://' || server.host AS "serverUrl", ` +
21 `'/static/streaming-playlists/hls/' || video.uuid || '/' AS "filePath", "videoFile".id ` +
22 `FROM video ` +
23 `INNER JOIN "videoChannel" ON "videoChannel".id = video."channelId" ` +
24 `INNER JOIN actor ON actor.id = "videoChannel"."actorId" ` +
25 `INNER JOIN server ON server.id = actor."serverId" ` +
26 `INNER JOIN "videoStreamingPlaylist" ON "videoStreamingPlaylist"."videoId" = video.id ` +
27 `INNER JOIN "videoFile" ON "videoFile"."videoStreamingPlaylistId" = "videoStreamingPlaylist".id ` +
28 `WHERE video.remote IS TRUE`
29
30 for (const fromQuery of [ fromQueryWebtorrent, fromQueryHLS ]) {
31 const query = `UPDATE "videoFile" ` +
32 `SET "torrentUrl" = t."serverUrl" || '/static/torrents/' || "videoFile"."torrentFilename", ` +
33 `"fileUrl" = t."serverUrl" || t."filePath" || "videoFile"."filename" ` +
34 `FROM (${fromQuery}) AS t WHERE t.id = "videoFile"."id" AND "videoFile"."fileUrl" IS NULL`
35
36 await utils.sequelize.query(query)
37 }
38 }
39
40 // Caption URLs
41 {
42 const fromQuery = `SELECT 'https://' || server.host AS "serverUrl", "video".uuid, "videoCaption".id ` +
43 `FROM video ` +
44 `INNER JOIN "videoChannel" ON "videoChannel".id = video."channelId" ` +
45 `INNER JOIN actor ON actor.id = "videoChannel"."actorId" ` +
46 `INNER JOIN server ON server.id = actor."serverId" ` +
47 `INNER JOIN "videoCaption" ON "videoCaption"."videoId" = video.id ` +
48 `WHERE video.remote IS TRUE`
49
50 const query = `UPDATE "videoCaption" ` +
51 `SET "fileUrl" = t."serverUrl" || '/lazy-static/video-captions/' || t.uuid || '-' || "videoCaption"."language" || '.vtt' ` +
52 `FROM (${fromQuery}) AS t WHERE t.id = "videoCaption"."id" AND "videoCaption"."fileUrl" IS NULL`
53
54 await utils.sequelize.query(query)
55 }
56
57 // Thumbnail URLs
58 {
59 const fromQuery = `SELECT 'https://' || server.host AS "serverUrl", "video".uuid, "thumbnail".id ` +
60 `FROM video ` +
61 `INNER JOIN "videoChannel" ON "videoChannel".id = video."channelId" ` +
62 `INNER JOIN actor ON actor.id = "videoChannel"."actorId" ` +
63 `INNER JOIN server ON server.id = actor."serverId" ` +
64 `INNER JOIN "thumbnail" ON "thumbnail"."videoId" = video.id ` +
65 `WHERE video.remote IS TRUE`
66
67 // Thumbnails
68 {
69 const query = `UPDATE "thumbnail" ` +
70 `SET "fileUrl" = t."serverUrl" || '/static/thumbnails/' || t.uuid || '.jpg' ` +
71 `FROM (${fromQuery}) AS t WHERE t.id = "thumbnail"."id" AND "thumbnail"."fileUrl" IS NULL AND thumbnail.type = 1`
72
73 await utils.sequelize.query(query)
74 }
75
76 {
77 // Previews
78 const query = `UPDATE "thumbnail" ` +
79 `SET "fileUrl" = t."serverUrl" || '/lazy-static/previews/' || t.uuid || '.jpg' ` +
80 `FROM (${fromQuery}) AS t WHERE t.id = "thumbnail"."id" AND "thumbnail"."fileUrl" IS NULL AND thumbnail.type = 2`
81
82 await utils.sequelize.query(query)
83 }
84 }
85
86 // Trackers
87 {
88 const trackerUrls = [
89 `'https://' || server.host || '/tracker/announce'`,
90 `'wss://' || server.host || '/tracker/socket'`
91 ]
92
93 for (const trackerUrl of trackerUrls) {
94 {
95 const query = `INSERT INTO "tracker" ("url", "createdAt", "updatedAt") ` +
96 `SELECT ${trackerUrl} AS "url", NOW(), NOW() ` +
97 `FROM video ` +
98 `INNER JOIN "videoChannel" ON "videoChannel".id = video."channelId" ` +
99 `INNER JOIN actor ON actor.id = "videoChannel"."actorId" ` +
100 `INNER JOIN server ON server.id = actor."serverId" ` +
101 `WHERE video.remote IS TRUE ` +
102 `ON CONFLICT DO NOTHING`
103
104 await utils.sequelize.query(query)
105 }
106
107 {
108 const query = `INSERT INTO "videoTracker" ("videoId", "trackerId", "createdAt", "updatedAt") ` +
109 `SELECT video.id, (SELECT tracker.id FROM tracker WHERE url = ${trackerUrl}) AS "trackerId", NOW(), NOW()` +
110 `FROM video ` +
111 `INNER JOIN "videoChannel" ON "videoChannel".id = video."channelId" ` +
112 `INNER JOIN actor ON actor.id = "videoChannel"."actorId" ` +
113 `INNER JOIN server ON server.id = actor."serverId" ` +
114 `WHERE video.remote IS TRUE`
115
116 await utils.sequelize.query(query)
117 }
118 }
119 }
120
121}
122
123function down (options) {
124 throw new Error('Not implemented.')
125}
126
127export {
128 up,
129 down
130}