Use separate queries for video files

8 files changed, 231 insertions, 56 deletions

diff --git a/server/models/video/sql/shared/abstract-videos-model-query-builder.ts b/server/models/video/sql/shared/abstract-videos-model-query-builder.ts
index bdf926cbe..8ed207eea 100644
--- a/server/models/video/sql/shared/abstract-videos-model-query-builder.ts
+++ b/server/models/video/sql/shared/abstract-videos-model-query-builder.ts
@@ -1,19 +1,24 @@
+import validator from 'validator'
 import { AbstractVideosQueryBuilder } from './abstract-videos-query-builder'
 import { VideoAttributes } from './video-attributes'
-import { VideoModelBuilder } from './video-model-builder'
+
+/**
+ *
+ * Abstract builder to create SQL query and fetch video models
+ *
+ */
 
 export class AbstractVideosModelQueryBuilder extends AbstractVideosQueryBuilder {
   protected attributes: { [key: string]: string } = {}
   protected joins: string[] = []
+  protected where: string
 
   protected videoAttributes: VideoAttributes
-  protected videoModelBuilder: VideoModelBuilder
 
-  constructor (private readonly mode: 'list' | 'get') {
+  constructor (protected readonly mode: 'list' | 'get') {
     super()
 
     this.videoAttributes = new VideoAttributes(this.mode)
-    this.videoModelBuilder = new VideoModelBuilder(this.mode, this.videoAttributes)
   }
 
   protected buildSelect () {
@@ -78,21 +83,30 @@ export class AbstractVideosModelQueryBuilder extends AbstractVideosQueryBuilder
     }
   }
 
-  protected includeFiles () {
-    this.joins.push(
-      'LEFT JOIN "videoFile" AS "VideoFiles" ON "VideoFiles"."videoId" = "video"."id"',
+  protected includeWebtorrentFiles (required: boolean) {
+    const joinType = required ? 'INNER' : 'LEFT'
+    this.joins.push(joinType + ' JOIN "videoFile" AS "VideoFiles" ON "VideoFiles"."videoId" = "video"."id"')
+
+    this.attributes = {
+      ...this.attributes,
+
+      ...this.buildAttributesObject('VideoFiles', this.videoAttributes.getFileAttributes())
+    }
+  }
+
+  protected includeStreamingPlaylistFiles (required: boolean) {
+    const joinType = required ? 'INNER' : 'LEFT'
 
-      'LEFT JOIN "videoStreamingPlaylist" AS "VideoStreamingPlaylists" ON "VideoStreamingPlaylists"."videoId" = "video"."id"',
+    this.joins.push(
+      joinType + ' JOIN "videoStreamingPlaylist" AS "VideoStreamingPlaylists" ON "VideoStreamingPlaylists"."videoId" = "video"."id"',
 
-      'LEFT JOIN "videoFile" AS "VideoStreamingPlaylists->VideoFiles" ' +
+      joinType + ' JOIN "videoFile" AS "VideoStreamingPlaylists->VideoFiles" ' +
         'ON "VideoStreamingPlaylists->VideoFiles"."videoStreamingPlaylistId" = "VideoStreamingPlaylists"."id"'
     )
 
     this.attributes = {
       ...this.attributes,
 
-      ...this.buildAttributesObject('VideoFiles', this.videoAttributes.getFileAttributes()),
-
       ...this.buildAttributesObject('VideoStreamingPlaylists', this.videoAttributes.getStreamingPlaylistAttributes()),
       ...this.buildAttributesObject('VideoStreamingPlaylists->VideoFiles', this.videoAttributes.getFileAttributes())
     }
@@ -196,11 +210,8 @@ export class AbstractVideosModelQueryBuilder extends AbstractVideosQueryBuilder
     }
   }
 
-  protected includeRedundancies () {
+  protected includeWebTorrentRedundancies () {
     this.joins.push(
-      'LEFT OUTER JOIN "videoRedundancy" AS "VideoStreamingPlaylists->RedundancyVideos" ' +
-        'ON "VideoStreamingPlaylists"."id" = "VideoStreamingPlaylists->RedundancyVideos"."videoStreamingPlaylistId"',
-
       'LEFT OUTER JOIN "videoRedundancy" AS "VideoFiles->RedundancyVideos" ON ' +
         '"VideoFiles"."id" = "VideoFiles->RedundancyVideos"."videoFileId"'
     )
@@ -208,7 +219,19 @@ export class AbstractVideosModelQueryBuilder extends AbstractVideosQueryBuilder
     this.attributes = {
       ...this.attributes,
 
-      ...this.buildAttributesObject('VideoFiles->RedundancyVideos', this.videoAttributes.getRedundancyAttributes()),
+      ...this.buildAttributesObject('VideoFiles->RedundancyVideos', this.videoAttributes.getRedundancyAttributes())
+    }
+  }
+
+  protected includeStreamingPlaylistRedundancies () {
+    this.joins.push(
+      'LEFT OUTER JOIN "videoRedundancy" AS "VideoStreamingPlaylists->RedundancyVideos" ' +
+        'ON "VideoStreamingPlaylists"."id" = "VideoStreamingPlaylists->RedundancyVideos"."videoStreamingPlaylistId"'
+    )
+
+    this.attributes = {
+      ...this.attributes,
+
       ...this.buildAttributesObject('VideoStreamingPlaylists->RedundancyVideos', this.videoAttributes.getRedundancyAttributes())
     }
   }
@@ -236,4 +259,14 @@ export class AbstractVideosModelQueryBuilder extends AbstractVideosQueryBuilder
 
     return result
   }
+
+  protected whereId (id: string | number) {
+    if (validator.isInt('' + id)) {
+      this.where = 'WHERE "video".id = :videoId'
+    } else {
+      this.where = 'WHERE uuid = :videoId'
+    }
+
+    this.replacements.videoId = id
+  }
 }
diff --git a/server/models/video/sql/shared/abstract-videos-query-builder.ts b/server/models/video/sql/shared/abstract-videos-query-builder.ts
index 01694e691..c1bbeb71e 100644
--- a/server/models/video/sql/shared/abstract-videos-query-builder.ts
+++ b/server/models/video/sql/shared/abstract-videos-query-builder.ts
@@ -1,6 +1,12 @@
 import { QueryTypes, Sequelize, Transaction } from 'sequelize'
 import { logger } from '@server/helpers/logger'
 
+/**
+ *
+ * Abstact builder to run video SQL queries
+ *
+ */
+
 export class AbstractVideosQueryBuilder {
   protected sequelize: Sequelize
 
diff --git a/server/models/video/sql/shared/video-attributes.ts b/server/models/video/sql/shared/video-attributes.ts
index 1a1650dc7..e21b33c73 100644
--- a/server/models/video/sql/shared/video-attributes.ts
+++ b/server/models/video/sql/shared/video-attributes.ts
@@ -1,3 +1,9 @@
+
+/**
+ *
+ * Class to build video attributes we want to fetch from the database
+ *
+ */
 export class VideoAttributes {
 
   constructor (readonly mode: 'get' | 'list') {
diff --git a/server/models/video/sql/shared/video-file-query-builder.ts b/server/models/video/sql/shared/video-file-query-builder.ts
new file mode 100644
index 000000000..29b11a298
--- /dev/null
+++ b/server/models/video/sql/shared/video-file-query-builder.ts
@@ -0,0 +1,66 @@
+import { Sequelize } from 'sequelize'
+import { BuildVideoGetQueryOptions } from '../video-model-get-query-builder'
+import { AbstractVideosModelQueryBuilder } from './abstract-videos-model-query-builder'
+
+/**
+ *
+ * Fetch files (webtorrent and streaming playlist) according to a video
+ *
+ */
+
+export class VideoFileQueryBuilder extends AbstractVideosModelQueryBuilder {
+  protected attributes: { [key: string]: string }
+  protected joins: string[] = []
+
+  constructor (protected readonly sequelize: Sequelize) {
+    super('get')
+  }
+
+  queryWebTorrentVideos (options: BuildVideoGetQueryOptions) {
+    this.buildWebtorrentFilesQuery(options)
+
+    return this.runQuery(options.transaction, true)
+  }
+
+  queryStreamingPlaylistVideos (options: BuildVideoGetQueryOptions) {
+    this.buildVideoStreamingPlaylistFilesQuery(options)
+
+    return this.runQuery(options.transaction, true)
+  }
+
+  private buildWebtorrentFilesQuery (options: BuildVideoGetQueryOptions) {
+    this.attributes = {
+      '"video"."id"': ''
+    }
+
+    this.includeWebtorrentFiles(true)
+
+    if (options.forGetAPI === true) {
+      this.includeWebTorrentRedundancies()
+    }
+
+    this.whereId(options.id)
+
+    this.query = this.buildQuery()
+  }
+
+  private buildVideoStreamingPlaylistFilesQuery (options: BuildVideoGetQueryOptions) {
+    this.attributes = {
+      '"video"."id"': ''
+    }
+
+    this.includeStreamingPlaylistFiles(true)
+
+    if (options.forGetAPI === true) {
+      this.includeStreamingPlaylistRedundancies()
+    }
+
+    this.whereId(options.id)
+
+    this.query = this.buildQuery()
+  }
+
+  private buildQuery () {
+    return `${this.buildSelect()} FROM "video" ${this.joins.join(' ')} ${this.where}`
+  }
+}
diff --git a/server/models/video/sql/shared/video-model-builder.ts b/server/models/video/sql/shared/video-model-builder.ts
index 9719f6d2e..627ea6443 100644
--- a/server/models/video/sql/shared/video-model-builder.ts
+++ b/server/models/video/sql/shared/video-model-builder.ts
@@ -17,6 +17,12 @@ import { VideoLiveModel } from '../../video-live'
 import { VideoStreamingPlaylistModel } from '../../video-streaming-playlist'
 import { VideoAttributes } from './video-attributes'
 
+/**
+ *
+ * Build video models from SQL rows
+ *
+ */
+
 export class VideoModelBuilder {
   private videosMemo: { [ id: number ]: VideoModel }
   private videoStreamingPlaylistMemo: { [ id: number ]: VideoStreamingPlaylistModel }
@@ -43,7 +49,7 @@ export class VideoModelBuilder {
 
   }
 
-  buildVideosFromRows (rows: any[]) {
+  buildVideosFromRows (rows: any[], rowsWebtorrentFiles?: any[], rowsStreamingPlaylist?: any[]) {
     this.reinit()
 
     for (const row of rows) {
@@ -53,10 +59,15 @@ export class VideoModelBuilder {
 
       this.setUserHistory(row, videoModel)
       this.addThumbnail(row, videoModel)
-      this.addWebTorrentFile(row, videoModel)
 
-      this.addStreamingPlaylist(row, videoModel)
-      this.addStreamingPlaylistFile(row)
+      if (!rowsWebtorrentFiles) {
+        this.addWebTorrentFile(row, videoModel)
+      }
+
+      if (!rowsStreamingPlaylist) {
+        this.addStreamingPlaylist(row, videoModel)
+        this.addStreamingPlaylistFile(row)
+      }
 
       if (this.mode === 'get') {
         this.addTag(row, videoModel)
@@ -65,16 +76,30 @@ export class VideoModelBuilder {
         this.setScheduleVideoUpdate(row, videoModel)
         this.setLive(row, videoModel)
 
-        if (row.VideoFiles.id) {
+        if (!rowsWebtorrentFiles && row.VideoFiles.id) {
           this.addRedundancy(row.VideoFiles.RedundancyVideos, this.videoFileMemo[row.VideoFiles.id])
         }
 
-        if (row.VideoStreamingPlaylists.id) {
+        if (!rowsStreamingPlaylist && row.VideoStreamingPlaylists.id) {
           this.addRedundancy(row.VideoStreamingPlaylists.RedundancyVideos, this.videoStreamingPlaylistMemo[row.VideoStreamingPlaylists.id])
         }
       }
     }
 
+    for (const row of rowsWebtorrentFiles || []) {
+      const videoModel = this.videosMemo[row.id]
+      this.addWebTorrentFile(row, videoModel)
+      this.addRedundancy(row.VideoFiles.RedundancyVideos, this.videoFileMemo[row.VideoFiles.id])
+    }
+
+    for (const row of rowsStreamingPlaylist || []) {
+      const videoModel = this.videosMemo[row.id]
+
+      this.addStreamingPlaylist(row, videoModel)
+      this.addStreamingPlaylistFile(row)
+      this.addRedundancy(row.VideoStreamingPlaylists.RedundancyVideos, this.videoStreamingPlaylistMemo[row.VideoStreamingPlaylists.id])
+    }
+
     return this.videos
   }
 
diff --git a/server/models/video/sql/video-model-get-query-builder.ts b/server/models/video/sql/video-model-get-query-builder.ts
index 0a3723e63..1a921d802 100644
--- a/server/models/video/sql/video-model-get-query-builder.ts
+++ b/server/models/video/sql/video-model-get-query-builder.ts
@@ -1,6 +1,14 @@
 import { Sequelize, Transaction } from 'sequelize'
-import validator from 'validator'
 import { AbstractVideosModelQueryBuilder } from './shared/abstract-videos-model-query-builder'
+import { VideoAttributes } from './shared/video-attributes'
+import { VideoFileQueryBuilder } from './shared/video-file-query-builder'
+import { VideoModelBuilder } from './shared/video-model-builder'
+
+/**
+ *
+ * Build a GET SQL query, fetch rows and create the video model
+ *
+ */
 
 export type BuildVideoGetQueryOptions = {
   id: number | string
@@ -9,31 +17,57 @@ export type BuildVideoGetQueryOptions = {
   forGetAPI?: boolean
 }
 
-export class VideosModelGetQueryBuilder extends AbstractVideosModelQueryBuilder {
+export class VideosModelGetQueryBuilder {
+  videoQueryBuilder: VideosModelGetQuerySubBuilder
+  webtorrentFilesQueryBuilder: VideoFileQueryBuilder
+  streamingPlaylistFilesQueryBuilder: VideoFileQueryBuilder
+
+  private readonly videoModelBuilder: VideoModelBuilder
+
+  constructor (protected readonly sequelize: Sequelize) {
+    this.videoQueryBuilder = new VideosModelGetQuerySubBuilder(sequelize)
+    this.webtorrentFilesQueryBuilder = new VideoFileQueryBuilder(sequelize)
+    this.streamingPlaylistFilesQueryBuilder = new VideoFileQueryBuilder(sequelize)
+
+    this.videoModelBuilder = new VideoModelBuilder('get', new VideoAttributes('get'))
+  }
+
+  async queryVideos (options: BuildVideoGetQueryOptions) {
+    const [ videoRows, webtorrentFilesRows, streamingPlaylistFilesRows ] = await Promise.all([
+      this.videoQueryBuilder.queryVideos(options),
+      this.webtorrentFilesQueryBuilder.queryWebTorrentVideos(options),
+      this.streamingPlaylistFilesQueryBuilder.queryStreamingPlaylistVideos(options)
+    ])
+
+    const videos = this.videoModelBuilder.buildVideosFromRows(videoRows, webtorrentFilesRows, streamingPlaylistFilesRows)
+
+    if (videos.length > 1) {
+      throw new Error('Video results is more than ')
+    }
+
+    if (videos.length === 0) return null
+    return videos[0]
+  }
+}
+
+export class VideosModelGetQuerySubBuilder extends AbstractVideosModelQueryBuilder {
   protected attributes: { [key: string]: string }
   protected joins: string[] = []
-  protected where: string
+
+  protected webtorrentFilesQuery: string
+  protected streamingPlaylistFilesQuery: string
 
   constructor (protected readonly sequelize: Sequelize) {
     super('get')
   }
 
   queryVideos (options: BuildVideoGetQueryOptions) {
-    this.buildGetQuery(options)
-
-    return this.runQuery(options.transaction, true).then(rows => {
-      const videos = this.videoModelBuilder.buildVideosFromRows(rows)
-
-      if (videos.length > 1) {
-        throw new Error('Video results is more than ')
-      }
+    this.buildMainGetQuery(options)
 
-      if (videos.length === 0) return null
-      return videos[0]
-    })
+    return this.runQuery(options.transaction, true)
   }
 
-  private buildGetQuery (options: BuildVideoGetQueryOptions) {
+  private buildMainGetQuery (options: BuildVideoGetQueryOptions) {
     this.attributes = {
       '"video".*': ''
     }
@@ -45,8 +79,6 @@ export class VideosModelGetQueryBuilder extends AbstractVideosModelQueryBuilder
 
     this.includeThumbnails()
 
-    this.includeFiles()
-
     this.includeBlacklisted()
 
     this.includeScheduleUpdate()
@@ -59,28 +91,17 @@ export class VideosModelGetQueryBuilder extends AbstractVideosModelQueryBuilder
 
     if (options.forGetAPI === true) {
       this.includeTrackers()
-      this.includeRedundancies()
     }
 
     this.whereId(options.id)
 
-    const select = this.buildSelect()
-    const order = this.buildOrder()
-
-    this.query = `${select} FROM "video" ${this.joins.join(' ')} ${this.where} ${order}`
+    this.query = this.buildQuery()
   }
 
-  private whereId (id: string | number) {
-    if (validator.isInt('' + id)) {
-      this.where = 'WHERE "video".id = :videoId'
-    } else {
-      this.where = 'WHERE uuid = :videoId'
-    }
-
-    this.replacements.videoId = id
-  }
+  private buildQuery () {
+    const order = 'ORDER BY "Tags"."name" ASC'
+    const from = `SELECT * FROM "video" ${this.where} LIMIT 1`
 
-  private buildOrder () {
-    return 'ORDER BY "Tags"."name" ASC'
+    return `${this.buildSelect()} FROM (${from}) AS "video" ${this.joins.join(' ')} ${this.where} ${order}`
   }
 }
diff --git a/server/models/video/sql/videos-id-list-query-builder.ts b/server/models/video/sql/videos-id-list-query-builder.ts
index 6e0d97d9e..30b251f0f 100644
--- a/server/models/video/sql/videos-id-list-query-builder.ts
+++ b/server/models/video/sql/videos-id-list-query-builder.ts
@@ -6,6 +6,12 @@ import { MUserAccountId, MUserId } from '@server/types/models'
 import { VideoFilter, VideoPrivacy, VideoState } from '@shared/models'
 import { AbstractVideosQueryBuilder } from './shared/abstract-videos-query-builder'
 
+/**
+ *
+ * Build videos list SQL query to fetch rows
+ *
+ */
+
 export type BuildVideosListQueryOptions = {
   attributes?: string[]
 
diff --git a/server/models/video/sql/videos-model-list-query-builder.ts b/server/models/video/sql/videos-model-list-query-builder.ts
index 38b9c91d0..acb76d80a 100644
--- a/server/models/video/sql/videos-model-list-query-builder.ts
+++ b/server/models/video/sql/videos-model-list-query-builder.ts
@@ -1,7 +1,14 @@
 import { Sequelize } from 'sequelize'
 import { AbstractVideosModelQueryBuilder } from './shared/abstract-videos-model-query-builder'
+import { VideoModelBuilder } from './shared/video-model-builder'
 import { BuildVideosListQueryOptions, VideosIdListQueryBuilder } from './videos-id-list-query-builder'
 
+/**
+ *
+ * Build videos list SQL query and create video models
+ *
+ */
+
 export class VideosModelListQueryBuilder extends AbstractVideosModelQueryBuilder {
   protected attributes: { [key: string]: string }
   protected joins: string[] = []
@@ -9,8 +16,12 @@ export class VideosModelListQueryBuilder extends AbstractVideosModelQueryBuilder
   private innerQuery: string
   private innerSort: string
 
+  private readonly videoModelBuilder: VideoModelBuilder
+
   constructor (protected readonly sequelize: Sequelize) {
     super('list')
+
+    this.videoModelBuilder = new VideoModelBuilder(this.mode, this.videoAttributes)
   }
 
   queryVideos (options: BuildVideosListQueryOptions) {
@@ -41,7 +52,8 @@ export class VideosModelListQueryBuilder extends AbstractVideosModelQueryBuilder
     this.includeThumbnails()
 
     if (options.withFiles) {
-      this.includeFiles()
+      this.includeWebtorrentFiles(false)
+      this.includeStreamingPlaylistFiles(false)
     }
 
     if (options.user) {