Use raw SQL for most of video queries

5 files changed, 65 insertions, 28 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 65df8d914..d959cb5d0 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
@@ -80,6 +80,18 @@ export class AbstractVideosModelQueryBuilder extends AbstractVideosQueryBuilder
     }
   }
 
+  protected includeOwnerUser () {
+    this.addJoin('INNER JOIN "videoChannel" AS "VideoChannel" ON "video"."channelId" = "VideoChannel"."id"')
+    this.addJoin('INNER JOIN "account" AS "VideoChannel->Account" ON "VideoChannel"."accountId" = "VideoChannel->Account"."id"')
+
+    this.attributes = {
+      ...this.attributes,
+
+      ...this.buildAttributesObject('VideoChannel', this.tables.getChannelAttributes()),
+      ...this.buildAttributesObject('VideoChannel->Account', this.tables.getUserAccountAttributes())
+    }
+  }
+
   protected includeThumbnails () {
     this.addJoin('LEFT OUTER JOIN "thumbnail" AS "Thumbnails" ON "video"."id" = "Thumbnails"."videoId"')
 
@@ -269,14 +281,20 @@ export class AbstractVideosModelQueryBuilder extends AbstractVideosQueryBuilder
     return result
   }
 
-  protected whereId (id: string | number) {
-    if (validator.isInt('' + id)) {
+  protected whereId (options: { id?: string | number, url?: string }) {
+    if (options.url) {
+      this.where = 'WHERE "video"."url" = :videoUrl'
+      this.replacements.videoUrl = options.url
+      return
+    }
+
+    if (validator.isInt('' + options.id)) {
       this.where = 'WHERE "video".id = :videoId'
     } else {
       this.where = 'WHERE uuid = :videoId'
     }
 
-    this.replacements.videoId = id
+    this.replacements.videoId = options.id
   }
 
   protected addJoin (join: string) {
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 7e67fa34f..10699317a 100644
--- a/server/models/video/sql/shared/abstract-videos-query-builder.ts
+++ b/server/models/video/sql/shared/abstract-videos-query-builder.ts
@@ -13,16 +13,17 @@ export class AbstractVideosQueryBuilder {
   protected query: string
   protected replacements: any = {}
 
-  protected runQuery (transaction?: Transaction) {
+  protected runQuery (options: { transaction?: Transaction, logging?: boolean } = {}) {
     logger.debug('Running videos query.', { query: this.query, replacements: this.replacements })
 
-    const options = {
-      transaction,
+    const queryOptions = {
+      transaction: options.transaction,
+      logging: options.logging,
       replacements: this.replacements,
       type: QueryTypes.SELECT as QueryTypes.SELECT,
       next: false
     }
 
-    return this.sequelize.query<any>(this.query, options)
+    return this.sequelize.query<any>(this.query, queryOptions)
   }
 }
diff --git a/server/models/video/sql/shared/video-file-query-builder.ts b/server/models/video/sql/shared/video-file-query-builder.ts
index 7d822f8fa..a62fa64f8 100644
--- a/server/models/video/sql/shared/video-file-query-builder.ts
+++ b/server/models/video/sql/shared/video-file-query-builder.ts
@@ -18,13 +18,13 @@ export class VideoFileQueryBuilder extends AbstractVideosModelQueryBuilder {
   queryWebTorrentVideos (options: BuildVideoGetQueryOptions) {
     this.buildWebtorrentFilesQuery(options)
 
-    return this.runQuery(options.transaction)
+    return this.runQuery(options)
   }
 
   queryStreamingPlaylistVideos (options: BuildVideoGetQueryOptions) {
     this.buildVideoStreamingPlaylistFilesQuery(options)
 
-    return this.runQuery(options.transaction)
+    return this.runQuery(options)
   }
 
   private buildWebtorrentFilesQuery (options: BuildVideoGetQueryOptions) {
@@ -34,11 +34,11 @@ export class VideoFileQueryBuilder extends AbstractVideosModelQueryBuilder {
 
     this.includeWebtorrentFiles(true)
 
-    if (options.forGetAPI === true) {
+    if (this.shouldIncludeRedundancies(options)) {
       this.includeWebTorrentRedundancies()
     }
 
-    this.whereId(options.id)
+    this.whereId(options)
 
     this.query = this.buildQuery()
   }
@@ -50,11 +50,11 @@ export class VideoFileQueryBuilder extends AbstractVideosModelQueryBuilder {
 
     this.includeStreamingPlaylistFiles(true)
 
-    if (options.forGetAPI === true) {
+    if (this.shouldIncludeRedundancies(options)) {
       this.includeStreamingPlaylistRedundancies()
     }
 
-    this.whereId(options.id)
+    this.whereId(options)
 
     this.query = this.buildQuery()
   }
@@ -62,4 +62,8 @@ export class VideoFileQueryBuilder extends AbstractVideosModelQueryBuilder {
   private buildQuery () {
     return `${this.buildSelect()} FROM "video" ${this.joins} ${this.where}`
   }
+
+  private shouldIncludeRedundancies (options: BuildVideoGetQueryOptions) {
+    return options.type === 'api'
+  }
 }
diff --git a/server/models/video/sql/shared/video-model-builder.ts b/server/models/video/sql/shared/video-model-builder.ts
index 2a60dab04..467a9378a 100644
--- a/server/models/video/sql/shared/video-model-builder.ts
+++ b/server/models/video/sql/shared/video-model-builder.ts
@@ -1,5 +1,4 @@
 
-import { logger } from '@server/helpers/logger'
 import { AccountModel } from '@server/models/account/account'
 import { ActorModel } from '@server/models/actor/actor'
 import { ActorImageModel } from '@server/models/actor/actor-image'
@@ -56,7 +55,7 @@ export class VideoModelBuilder {
     this.reinit()
 
     for (const row of rows) {
-      this.buildVideo(row)
+      this.buildVideoAndAccount(row)
 
       const videoModel = this.videosMemo[row.id]
 
@@ -131,22 +130,10 @@ export class VideoModelBuilder {
     }
   }
 
-  private buildVideo (row: SQLRow) {
+  private buildVideoAndAccount (row: SQLRow) {
     if (this.videosMemo[row.id]) return
 
-    // Build Channel
-    const channelModel = new VideoChannelModel(this.grab(row, this.tables.getChannelAttributes(), 'VideoChannel'), this.buildOpts)
-    channelModel.Actor = this.buildActor(row, 'VideoChannel')
-
-    const accountModel = new AccountModel(this.grab(row, this.tables.getAccountAttributes(), 'VideoChannel.Account'), this.buildOpts)
-    accountModel.Actor = this.buildActor(row, 'VideoChannel.Account')
-
-    channelModel.Account = accountModel
-
     const videoModel = new VideoModel(this.grab(row, this.tables.getVideoAttributes(), ''), this.buildOpts)
-    videoModel.VideoChannel = channelModel
-
-    this.videosMemo[row.id] = videoModel
 
     videoModel.UserVideoHistories = []
     videoModel.Thumbnails = []
@@ -155,10 +142,29 @@ export class VideoModelBuilder {
     videoModel.Tags = []
     videoModel.Trackers = []
 
+    this.buildAccount(row, videoModel)
+
+    this.videosMemo[row.id] = videoModel
+
     // Keep rows order
     this.videos.push(videoModel)
   }
 
+  private buildAccount (row: SQLRow, videoModel: VideoModel) {
+    const id = row['VideoChannel.Account.id']
+    if (!id) return
+
+    const channelModel = new VideoChannelModel(this.grab(row, this.tables.getChannelAttributes(), 'VideoChannel'), this.buildOpts)
+    channelModel.Actor = this.buildActor(row, 'VideoChannel')
+
+    const accountModel = new AccountModel(this.grab(row, this.tables.getAccountAttributes(), 'VideoChannel.Account'), this.buildOpts)
+    accountModel.Actor = this.buildActor(row, 'VideoChannel.Account')
+
+    channelModel.Account = accountModel
+
+    videoModel.VideoChannel = channelModel
+  }
+
   private buildActor (row: SQLRow, prefix: string) {
     const actorPrefix = `${prefix}.Actor`
     const avatarPrefix = `${actorPrefix}.Avatar`
diff --git a/server/models/video/sql/shared/video-tables.ts b/server/models/video/sql/shared/video-tables.ts
index fddf1210c..52929fa5e 100644
--- a/server/models/video/sql/shared/video-tables.ts
+++ b/server/models/video/sql/shared/video-tables.ts
@@ -10,6 +10,10 @@ export class VideoTables {
 
   }
 
+  getChannelAttributesForUser () {
+    return [ 'id', 'accountId' ]
+  }
+
   getChannelAttributes () {
     let attributeKeys = [
       'id',
@@ -29,6 +33,10 @@ export class VideoTables {
     return attributeKeys
   }
 
+  getUserAccountAttributes () {
+    return [ 'id', 'userId' ]
+  }
+
   getAccountAttributes () {
     let attributeKeys = [ 'id', 'name', 'actorId' ]