1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
|
import * as Sequelize from 'sequelize'
async function up (utils: {
transaction: Sequelize.Transaction
queryInterface: Sequelize.QueryInterface
sequelize: Sequelize.Sequelize
db: any
}): Promise<void> {
// Torrent and file URLs
{
const fromQueryWebtorrent = `SELECT 'https://' || server.host AS "serverUrl", '/static/webseed/' AS "filePath", "videoFile".id ` +
`FROM video ` +
`INNER JOIN "videoChannel" ON "videoChannel".id = video."channelId" ` +
`INNER JOIN actor ON actor.id = "videoChannel"."actorId" ` +
`INNER JOIN server ON server.id = actor."serverId" ` +
`INNER JOIN "videoFile" ON "videoFile"."videoId" = video.id ` +
`WHERE video.remote IS TRUE`
const fromQueryHLS = `SELECT 'https://' || server.host AS "serverUrl", ` +
`'/static/streaming-playlists/hls/' || video.uuid || '/' AS "filePath", "videoFile".id ` +
`FROM video ` +
`INNER JOIN "videoChannel" ON "videoChannel".id = video."channelId" ` +
`INNER JOIN actor ON actor.id = "videoChannel"."actorId" ` +
`INNER JOIN server ON server.id = actor."serverId" ` +
`INNER JOIN "videoStreamingPlaylist" ON "videoStreamingPlaylist"."videoId" = video.id ` +
`INNER JOIN "videoFile" ON "videoFile"."videoStreamingPlaylistId" = "videoStreamingPlaylist".id ` +
`WHERE video.remote IS TRUE`
for (const fromQuery of [ fromQueryWebtorrent, fromQueryHLS ]) {
const query = `UPDATE "videoFile" ` +
`SET "torrentUrl" = t."serverUrl" || '/static/torrents/' || "videoFile"."torrentFilename", ` +
`"fileUrl" = t."serverUrl" || t."filePath" || "videoFile"."filename" ` +
`FROM (${fromQuery}) AS t WHERE t.id = "videoFile"."id" AND "videoFile"."fileUrl" IS NULL`
await utils.sequelize.query(query)
}
}
// Caption URLs
{
const fromQuery = `SELECT 'https://' || server.host AS "serverUrl", "video".uuid, "videoCaption".id ` +
`FROM video ` +
`INNER JOIN "videoChannel" ON "videoChannel".id = video."channelId" ` +
`INNER JOIN actor ON actor.id = "videoChannel"."actorId" ` +
`INNER JOIN server ON server.id = actor."serverId" ` +
`INNER JOIN "videoCaption" ON "videoCaption"."videoId" = video.id ` +
`WHERE video.remote IS TRUE`
const query = `UPDATE "videoCaption" ` +
`SET "fileUrl" = t."serverUrl" || '/lazy-static/video-captions/' || t.uuid || '-' || "videoCaption"."language" || '.vtt' ` +
`FROM (${fromQuery}) AS t WHERE t.id = "videoCaption"."id" AND "videoCaption"."fileUrl" IS NULL`
await utils.sequelize.query(query)
}
// Thumbnail URLs
{
const fromQuery = `SELECT 'https://' || server.host AS "serverUrl", "video".uuid, "thumbnail".id ` +
`FROM video ` +
`INNER JOIN "videoChannel" ON "videoChannel".id = video."channelId" ` +
`INNER JOIN actor ON actor.id = "videoChannel"."actorId" ` +
`INNER JOIN server ON server.id = actor."serverId" ` +
`INNER JOIN "thumbnail" ON "thumbnail"."videoId" = video.id ` +
`WHERE video.remote IS TRUE`
// Thumbnails
{
const query = `UPDATE "thumbnail" ` +
`SET "fileUrl" = t."serverUrl" || '/static/thumbnails/' || t.uuid || '.jpg' ` +
`FROM (${fromQuery}) AS t WHERE t.id = "thumbnail"."id" AND "thumbnail"."fileUrl" IS NULL AND thumbnail.type = 1`
await utils.sequelize.query(query)
}
{
// Previews
const query = `UPDATE "thumbnail" ` +
`SET "fileUrl" = t."serverUrl" || '/lazy-static/previews/' || t.uuid || '.jpg' ` +
`FROM (${fromQuery}) AS t WHERE t.id = "thumbnail"."id" AND "thumbnail"."fileUrl" IS NULL AND thumbnail.type = 2`
await utils.sequelize.query(query)
}
}
// Trackers
{
const trackerUrls = [
`'https://' || server.host || '/tracker/announce'`,
`'wss://' || server.host || '/tracker/socket'`
]
for (const trackerUrl of trackerUrls) {
{
const query = `INSERT INTO "tracker" ("url", "createdAt", "updatedAt") ` +
`SELECT ${trackerUrl} AS "url", NOW(), NOW() ` +
`FROM video ` +
`INNER JOIN "videoChannel" ON "videoChannel".id = video."channelId" ` +
`INNER JOIN actor ON actor.id = "videoChannel"."actorId" ` +
`INNER JOIN server ON server.id = actor."serverId" ` +
`WHERE video.remote IS TRUE ` +
`ON CONFLICT DO NOTHING`
await utils.sequelize.query(query)
}
{
const query = `INSERT INTO "videoTracker" ("videoId", "trackerId", "createdAt", "updatedAt") ` +
`SELECT video.id, (SELECT tracker.id FROM tracker WHERE url = ${trackerUrl}) AS "trackerId", NOW(), NOW()` +
`FROM video ` +
`INNER JOIN "videoChannel" ON "videoChannel".id = video."channelId" ` +
`INNER JOIN actor ON actor.id = "videoChannel"."actorId" ` +
`INNER JOIN server ON server.id = actor."serverId" ` +
`WHERE video.remote IS TRUE`
await utils.sequelize.query(query)
}
}
}
}
function down (options) {
throw new Error('Not implemented.')
}
export {
up,
down
}
|
