Don't guess remote tracker URL

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 @@
+import * as Sequelize from 'sequelize'
+
+async function up (utils: {
+  transaction: Sequelize.Transaction
+  queryInterface: Sequelize.QueryInterface
+  sequelize: Sequelize.Sequelize
+  db: any
+}): Promise<void> {
+  {
+    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<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
+}