From d9a2a03196275065c28f4a0b7d4d7bc9992d77a1 Mon Sep 17 00:00:00 2001 From: Chocobozzz Date: Thu, 18 Feb 2021 10:15:11 +0100 Subject: Don't guess remote tracker URL --- server/initializers/constants.ts | 2 +- server/initializers/database.ts | 4 + server/initializers/migrations/0590-trackers.ts | 44 ++++++++ server/initializers/migrations/0595-remote-url.ts | 130 ++++++++++++++++++++++ 4 files changed, 179 insertions(+), 1 deletion(-) create mode 100644 server/initializers/migrations/0590-trackers.ts create mode 100644 server/initializers/migrations/0595-remote-url.ts (limited to 'server/initializers') diff --git a/server/initializers/constants.ts b/server/initializers/constants.ts index c4c7ffdac..fbedc2164 100644 --- a/server/initializers/constants.ts +++ b/server/initializers/constants.ts @@ -24,7 +24,7 @@ import { CONFIG, registerConfigChangedHandler } from './config' // --------------------------------------------------------------------------- -const LAST_MIGRATION_VERSION = 585 +const LAST_MIGRATION_VERSION = 595 // --------------------------------------------------------------------------- diff --git a/server/initializers/database.ts b/server/initializers/database.ts index 61768234f..1f2b6d521 100644 --- a/server/initializers/database.ts +++ b/server/initializers/database.ts @@ -1,3 +1,5 @@ +import { TrackerModel } from '@server/models/server/tracker' +import { VideoTrackerModel } from '@server/models/server/video-tracker' import { QueryTypes, Transaction } from 'sequelize' import { Sequelize as SequelizeTypescript } from 'sequelize-typescript' import { isTestInstance } from '../helpers/core-utils' @@ -128,6 +130,8 @@ async function initDatabaseModels (silent: boolean) { VideoPlaylistModel, VideoPlaylistElementModel, ThumbnailModel, + TrackerModel, + VideoTrackerModel, PluginModel ]) 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 @@ +import * as Sequelize from 'sequelize' + +async function up (utils: { + transaction: Sequelize.Transaction + queryInterface: Sequelize.QueryInterface + sequelize: Sequelize.Sequelize + db: any +}): Promise { + { + const query = `CREATE TABLE IF NOT EXISTS "tracker" ( + "id" serial, + "url" varchar(255) NOT NULL, + "createdAt" timestamp WITH time zone NOT NULL, + "updatedAt" timestamp WITH time zone NOT NULL, + PRIMARY KEY ("id") + );` + + await utils.sequelize.query(query) + } + + { + const query = `CREATE TABLE IF NOT EXISTS "videoTracker" ( + "videoId" integer REFERENCES "video" ("id") ON DELETE CASCADE ON UPDATE CASCADE, + "trackerId" integer REFERENCES "tracker" ("id") ON DELETE CASCADE ON UPDATE CASCADE, + "createdAt" timestamp WITH time zone NOT NULL, + "updatedAt" timestamp WITH time zone NOT NULL, + UNIQUE ("videoId", "trackerId"), + PRIMARY KEY ("videoId", "trackerId") + );` + + await utils.sequelize.query(query) + } + + await utils.sequelize.query(`CREATE UNIQUE INDEX "tracker_url" ON "tracker" ("url")`) +} + +function down (options) { + throw new Error('Not implemented.') +} + +export { + up, + down +} 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 @@ +import * as Sequelize from 'sequelize' + +async function up (utils: { + transaction: Sequelize.Transaction + queryInterface: Sequelize.QueryInterface + sequelize: Sequelize.Sequelize + db: any +}): Promise { + + // 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 +} -- cgit v1.2.3