5 files changed, 991 insertions, 0 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
new file mode 100644
index 000000000..0d7e64574
--- /dev/null
+++ b/server/models/video/sql/shared/abstract-videos-model-query-builder.ts
@@ -0,0 +1,300 @@
+import validator from 'validator'
+import { AbstractVideosQueryBuilder } from './abstract-videos-query-builder'
+import { VideoTables } from './video-tables'
+
+/**
+ *
+ * Abstract builder to create SQL query and fetch video models
+ *
+ */
+
+export class AbstractVideosModelQueryBuilder extends AbstractVideosQueryBuilder {
+  protected attributes: { [key: string]: string } = {}
+
+  protected joins = ''
+  protected where: string
+
+  protected tables: VideoTables
+
+  constructor (protected readonly mode: 'list' | 'get') {
+    super()
+
+    this.tables = new VideoTables(this.mode)
+  }
+
+  protected buildSelect () {
+    return 'SELECT ' + Object.keys(this.attributes).map(key => {
+      const value = this.attributes[key]
+      if (value) return `${key} AS ${value}`
+
+      return key
+    }).join(', ')
+  }
+
+  protected includeChannels () {
+    this.addJoin('INNER JOIN "videoChannel" AS "VideoChannel" ON "video"."channelId" = "VideoChannel"."id"')
+    this.addJoin('INNER JOIN "actor" AS "VideoChannel->Actor" ON "VideoChannel"."actorId" = "VideoChannel->Actor"."id"')
+
+    this.addJoin(
+      'LEFT OUTER JOIN "server" AS "VideoChannel->Actor->Server" ON "VideoChannel->Actor"."serverId" = "VideoChannel->Actor->Server"."id"'
+    )
+
+    this.addJoin(
+      'LEFT OUTER JOIN "actorImage" AS "VideoChannel->Actor->Avatar" ' +
+        'ON "VideoChannel->Actor"."avatarId" = "VideoChannel->Actor->Avatar"."id"'
+    )
+
+    this.attributes = {
+      ...this.attributes,
+
+      ...this.buildAttributesObject('VideoChannel', this.tables.getChannelAttributes()),
+      ...this.buildActorInclude('VideoChannel->Actor'),
+      ...this.buildAvatarInclude('VideoChannel->Actor->Avatar'),
+      ...this.buildServerInclude('VideoChannel->Actor->Server')
+    }
+  }
+
+  protected includeAccounts () {
+    this.addJoin('INNER JOIN "account" AS "VideoChannel->Account" ON "VideoChannel"."accountId" = "VideoChannel->Account"."id"')
+    this.addJoin(
+      'INNER JOIN "actor" AS "VideoChannel->Account->Actor" ON "VideoChannel->Account"."actorId" = "VideoChannel->Account->Actor"."id"'
+    )
+
+    this.addJoin(
+      'LEFT OUTER JOIN "server" AS "VideoChannel->Account->Actor->Server" ' +
+        'ON "VideoChannel->Account->Actor"."serverId" = "VideoChannel->Account->Actor->Server"."id"'
+    )
+
+    this.addJoin(
+      'LEFT OUTER JOIN "actorImage" AS "VideoChannel->Account->Actor->Avatar" ' +
+        'ON "VideoChannel->Account->Actor"."avatarId" = "VideoChannel->Account->Actor->Avatar"."id"'
+    )
+
+    this.attributes = {
+      ...this.attributes,
+
+      ...this.buildAttributesObject('VideoChannel->Account', this.tables.getAccountAttributes()),
+      ...this.buildActorInclude('VideoChannel->Account->Actor'),
+      ...this.buildAvatarInclude('VideoChannel->Account->Actor->Avatar'),
+      ...this.buildServerInclude('VideoChannel->Account->Actor->Server')
+    }
+  }
+
+  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"')
+
+    this.attributes = {
+      ...this.attributes,
+
+      ...this.buildAttributesObject('Thumbnails', this.tables.getThumbnailAttributes())
+    }
+  }
+
+  protected includeWebtorrentFiles () {
+    this.addJoin('LEFT JOIN "videoFile" AS "VideoFiles" ON "VideoFiles"."videoId" = "video"."id"')
+
+    this.attributes = {
+      ...this.attributes,
+
+      ...this.buildAttributesObject('VideoFiles', this.tables.getFileAttributes())
+    }
+  }
+
+  protected includeStreamingPlaylistFiles () {
+    this.addJoin(
+      'LEFT JOIN "videoStreamingPlaylist" AS "VideoStreamingPlaylists" ON "VideoStreamingPlaylists"."videoId" = "video"."id"'
+    )
+
+    this.addJoin(
+      'LEFT JOIN "videoFile" AS "VideoStreamingPlaylists->VideoFiles" ' +
+        'ON "VideoStreamingPlaylists->VideoFiles"."videoStreamingPlaylistId" = "VideoStreamingPlaylists"."id"'
+    )
+
+    this.attributes = {
+      ...this.attributes,
+
+      ...this.buildAttributesObject('VideoStreamingPlaylists', this.tables.getStreamingPlaylistAttributes()),
+      ...this.buildAttributesObject('VideoStreamingPlaylists->VideoFiles', this.tables.getFileAttributes())
+    }
+  }
+
+  protected includeUserHistory (userId: number) {
+    this.addJoin(
+      'LEFT OUTER JOIN "userVideoHistory" ' +
+        'ON "video"."id" = "userVideoHistory"."videoId" AND "userVideoHistory"."userId" = :userVideoHistoryId'
+    )
+
+    this.replacements.userVideoHistoryId = userId
+
+    this.attributes = {
+      ...this.attributes,
+
+      ...this.buildAttributesObject('userVideoHistory', this.tables.getUserHistoryAttributes())
+    }
+  }
+
+  protected includePlaylist (playlistId: number) {
+    this.addJoin(
+      'INNER JOIN "videoPlaylistElement" as "VideoPlaylistElement" ON "videoPlaylistElement"."videoId" = "video"."id" ' +
+        'AND "VideoPlaylistElement"."videoPlaylistId" = :videoPlaylistId'
+    )
+
+    this.replacements.videoPlaylistId = playlistId
+
+    this.attributes = {
+      ...this.attributes,
+
+      ...this.buildAttributesObject('VideoPlaylistElement', this.tables.getPlaylistAttributes())
+    }
+  }
+
+  protected includeTags () {
+    this.addJoin(
+      'LEFT OUTER JOIN (' +
+        '"videoTag" AS "Tags->VideoTagModel" INNER JOIN "tag" AS "Tags" ON "Tags"."id" = "Tags->VideoTagModel"."tagId"' +
+      ') ' +
+      'ON "video"."id" = "Tags->VideoTagModel"."videoId"'
+    )
+
+    this.attributes = {
+      ...this.attributes,
+
+      ...this.buildAttributesObject('Tags', this.tables.getTagAttributes()),
+      ...this.buildAttributesObject('Tags->VideoTagModel', this.tables.getVideoTagAttributes())
+    }
+  }
+
+  protected includeBlacklisted () {
+    this.addJoin(
+      'LEFT OUTER JOIN "videoBlacklist" AS "VideoBlacklist" ON "video"."id" = "VideoBlacklist"."videoId"'
+    )
+
+    this.attributes = {
+      ...this.attributes,
+
+      ...this.buildAttributesObject('VideoBlacklist', this.tables.getBlacklistedAttributes())
+    }
+  }
+
+  protected includeScheduleUpdate () {
+    this.addJoin(
+      'LEFT OUTER JOIN "scheduleVideoUpdate" AS "ScheduleVideoUpdate" ON "video"."id" = "ScheduleVideoUpdate"."videoId"'
+    )
+
+    this.attributes = {
+      ...this.attributes,
+
+      ...this.buildAttributesObject('ScheduleVideoUpdate', this.tables.getScheduleUpdateAttributes())
+    }
+  }
+
+  protected includeLive () {
+    this.addJoin(
+      'LEFT OUTER JOIN "videoLive" AS "VideoLive" ON "video"."id" = "VideoLive"."videoId"'
+    )
+
+    this.attributes = {
+      ...this.attributes,
+
+      ...this.buildAttributesObject('VideoLive', this.tables.getLiveAttributes())
+    }
+  }
+
+  protected includeTrackers () {
+    this.addJoin(
+      'LEFT OUTER JOIN (' +
+        '"videoTracker" AS "Trackers->VideoTrackerModel" ' +
+          'INNER JOIN "tracker" AS "Trackers" ON "Trackers"."id" = "Trackers->VideoTrackerModel"."trackerId"' +
+      ') ON "video"."id" = "Trackers->VideoTrackerModel"."videoId"'
+    )
+
+    this.attributes = {
+      ...this.attributes,
+
+      ...this.buildAttributesObject('Trackers', this.tables.getTrackerAttributes()),
+      ...this.buildAttributesObject('Trackers->VideoTrackerModel', this.tables.getVideoTrackerAttributes())
+    }
+  }
+
+  protected includeWebTorrentRedundancies () {
+    this.addJoin(
+      'LEFT OUTER JOIN "videoRedundancy" AS "VideoFiles->RedundancyVideos" ON ' +
+        '"VideoFiles"."id" = "VideoFiles->RedundancyVideos"."videoFileId"'
+    )
+
+    this.attributes = {
+      ...this.attributes,
+
+      ...this.buildAttributesObject('VideoFiles->RedundancyVideos', this.tables.getRedundancyAttributes())
+    }
+  }
+
+  protected includeStreamingPlaylistRedundancies () {
+    this.addJoin(
+      'LEFT OUTER JOIN "videoRedundancy" AS "VideoStreamingPlaylists->RedundancyVideos" ' +
+        'ON "VideoStreamingPlaylists"."id" = "VideoStreamingPlaylists->RedundancyVideos"."videoStreamingPlaylistId"'
+    )
+
+    this.attributes = {
+      ...this.attributes,
+
+      ...this.buildAttributesObject('VideoStreamingPlaylists->RedundancyVideos', this.tables.getRedundancyAttributes())
+    }
+  }
+
+  protected buildActorInclude (prefixKey: string) {
+    return this.buildAttributesObject(prefixKey, this.tables.getActorAttributes())
+  }
+
+  protected buildAvatarInclude (prefixKey: string) {
+    return this.buildAttributesObject(prefixKey, this.tables.getAvatarAttributes())
+  }
+
+  protected buildServerInclude (prefixKey: string) {
+    return this.buildAttributesObject(prefixKey, this.tables.getServerAttributes())
+  }
+
+  protected buildAttributesObject (prefixKey: string, attributeKeys: string[]) {
+    const result: { [id: string]: string} = {}
+
+    const prefixValue = prefixKey.replace(/->/g, '.')
+
+    for (const attribute of attributeKeys) {
+      result[`"${prefixKey}"."${attribute}"`] = `"${prefixValue}.${attribute}"`
+    }
+
+    return result
+  }
+
+  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 = options.id
+  }
+
+  protected addJoin (join: string) {
+    this.joins += join + ' '
+  }
+}
diff --git a/server/models/video/sql/shared/abstract-videos-query-builder.ts b/server/models/video/sql/shared/abstract-videos-query-builder.ts
new file mode 100644
index 000000000..09776bcb0
--- /dev/null
+++ b/server/models/video/sql/shared/abstract-videos-query-builder.ts
@@ -0,0 +1,26 @@
+import { QueryTypes, Sequelize, Transaction } from 'sequelize'
+
+/**
+ *
+ * Abstact builder to run video SQL queries
+ *
+ */
+
+export class AbstractVideosQueryBuilder {
+  protected sequelize: Sequelize
+
+  protected query: string
+  protected replacements: any = {}
+
+  protected runQuery (options: { transaction?: Transaction, logging?: boolean } = {}) {
+    const queryOptions = {
+      transaction: options.transaction,
+      logging: options.logging,
+      replacements: this.replacements,
+      type: QueryTypes.SELECT as QueryTypes.SELECT,
+      nest: false
+    }
+
+    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
new file mode 100644
index 000000000..6b15c3b69
--- /dev/null
+++ b/server/models/video/sql/shared/video-file-query-builder.ts
@@ -0,0 +1,69 @@
+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 }
+
+  constructor (protected readonly sequelize: Sequelize) {
+    super('get')
+  }
+
+  queryWebTorrentVideos (options: BuildVideoGetQueryOptions) {
+    this.buildWebtorrentFilesQuery(options)
+
+    return this.runQuery(options)
+  }
+
+  queryStreamingPlaylistVideos (options: BuildVideoGetQueryOptions) {
+    this.buildVideoStreamingPlaylistFilesQuery(options)
+
+    return this.runQuery(options)
+  }
+
+  private buildWebtorrentFilesQuery (options: BuildVideoGetQueryOptions) {
+    this.attributes = {
+      '"video"."id"': ''
+    }
+
+    this.includeWebtorrentFiles()
+
+    if (this.shouldIncludeRedundancies(options)) {
+      this.includeWebTorrentRedundancies()
+    }
+
+    this.whereId(options)
+
+    this.query = this.buildQuery()
+  }
+
+  private buildVideoStreamingPlaylistFilesQuery (options: BuildVideoGetQueryOptions) {
+    this.attributes = {
+      '"video"."id"': ''
+    }
+
+    this.includeStreamingPlaylistFiles()
+
+    if (this.shouldIncludeRedundancies(options)) {
+      this.includeStreamingPlaylistRedundancies()
+    }
+
+    this.whereId(options)
+
+    this.query = this.buildQuery()
+  }
+
+  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
new file mode 100644
index 000000000..e7e2aa1ca
--- /dev/null
+++ b/server/models/video/sql/shared/video-model-builder.ts
@@ -0,0 +1,333 @@
+
+import { AccountModel } from '@server/models/account/account'
+import { ActorModel } from '@server/models/actor/actor'
+import { ActorImageModel } from '@server/models/actor/actor-image'
+import { VideoRedundancyModel } from '@server/models/redundancy/video-redundancy'
+import { ServerModel } from '@server/models/server/server'
+import { TrackerModel } from '@server/models/server/tracker'
+import { UserVideoHistoryModel } from '@server/models/user/user-video-history'
+import { ScheduleVideoUpdateModel } from '../../schedule-video-update'
+import { TagModel } from '../../tag'
+import { ThumbnailModel } from '../../thumbnail'
+import { VideoModel } from '../../video'
+import { VideoBlacklistModel } from '../../video-blacklist'
+import { VideoChannelModel } from '../../video-channel'
+import { VideoFileModel } from '../../video-file'
+import { VideoLiveModel } from '../../video-live'
+import { VideoStreamingPlaylistModel } from '../../video-streaming-playlist'
+import { VideoTables } from './video-tables'
+
+type SQLRow = { [id: string]: string | number }
+
+/**
+ *
+ * Build video models from SQL rows
+ *
+ */
+
+export class VideoModelBuilder {
+  private videosMemo: { [ id: number ]: VideoModel }
+  private videoStreamingPlaylistMemo: { [ id: number ]: VideoStreamingPlaylistModel }
+  private videoFileMemo: { [ id: number ]: VideoFileModel }
+
+  private thumbnailsDone: Set<any>
+  private historyDone: Set<any>
+  private blacklistDone: Set<any>
+  private liveDone: Set<any>
+  private redundancyDone: Set<any>
+  private scheduleVideoUpdateDone: Set<any>
+
+  private trackersDone: Set<string>
+  private tagsDone: Set<string>
+
+  private videos: VideoModel[]
+
+  private readonly buildOpts = { raw: true, isNewRecord: false }
+
+  constructor (
+    readonly mode: 'get' | 'list',
+    readonly tables: VideoTables
+  ) {
+
+  }
+
+  buildVideosFromRows (rows: SQLRow[], rowsWebTorrentFiles?: SQLRow[], rowsStreamingPlaylist?: SQLRow[]) {
+    this.reinit()
+
+    for (const row of rows) {
+      this.buildVideoAndAccount(row)
+
+      const videoModel = this.videosMemo[row.id]
+
+      this.setUserHistory(row, videoModel)
+      this.addThumbnail(row, videoModel)
+
+      if (!rowsWebTorrentFiles) {
+        this.addWebTorrentFile(row, videoModel)
+      }
+
+      if (!rowsStreamingPlaylist) {
+        this.addStreamingPlaylist(row, videoModel)
+        this.addStreamingPlaylistFile(row)
+      }
+
+      if (this.mode === 'get') {
+        this.addTag(row, videoModel)
+        this.addTracker(row, videoModel)
+        this.setBlacklisted(row, videoModel)
+        this.setScheduleVideoUpdate(row, videoModel)
+        this.setLive(row, videoModel)
+      }
+    }
+
+    this.grabSeparateWebTorrentFiles(rowsWebTorrentFiles)
+    this.grabSeparateStreamingPlaylistFiles(rowsStreamingPlaylist)
+
+    return this.videos
+  }
+
+  private reinit () {
+    this.videosMemo = {}
+    this.videoStreamingPlaylistMemo = {}
+    this.videoFileMemo = {}
+
+    this.thumbnailsDone = new Set<number>()
+    this.historyDone = new Set<number>()
+    this.blacklistDone = new Set<number>()
+    this.liveDone = new Set<number>()
+    this.redundancyDone = new Set<number>()
+    this.scheduleVideoUpdateDone = new Set<number>()
+
+    this.trackersDone = new Set<string>()
+    this.tagsDone = new Set<string>()
+
+    this.videos = []
+  }
+
+  private grabSeparateWebTorrentFiles (rowsWebTorrentFiles?: SQLRow[]) {
+    if (!rowsWebTorrentFiles) return
+
+    for (const row of rowsWebTorrentFiles) {
+      const id = row['VideoFiles.id']
+      if (!id) continue
+
+      const videoModel = this.videosMemo[row.id]
+      this.addWebTorrentFile(row, videoModel)
+      this.addRedundancy(row, 'VideoFiles', this.videoFileMemo[id])
+    }
+  }
+
+  private grabSeparateStreamingPlaylistFiles (rowsStreamingPlaylist?: SQLRow[]) {
+    if (!rowsStreamingPlaylist) return
+
+    for (const row of rowsStreamingPlaylist || []) {
+      const id = row['VideoStreamingPlaylists.id']
+      if (!id) continue
+
+      const videoModel = this.videosMemo[row.id]
+
+      this.addStreamingPlaylist(row, videoModel)
+      this.addStreamingPlaylistFile(row)
+      this.addRedundancy(row, 'VideoStreamingPlaylists', this.videoStreamingPlaylistMemo[id])
+    }
+  }
+
+  private buildVideoAndAccount (row: SQLRow) {
+    if (this.videosMemo[row.id]) return
+
+    const videoModel = new VideoModel(this.grab(row, this.tables.getVideoAttributes(), ''), this.buildOpts)
+
+    videoModel.UserVideoHistories = []
+    videoModel.Thumbnails = []
+    videoModel.VideoFiles = []
+    videoModel.VideoStreamingPlaylists = []
+    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`
+    const serverPrefix = `${actorPrefix}.Server`
+
+    const avatarModel = row[`${avatarPrefix}.id`] !== null
+      ? new ActorImageModel(this.grab(row, this.tables.getAvatarAttributes(), avatarPrefix), this.buildOpts)
+      : null
+
+    const serverModel = row[`${serverPrefix}.id`] !== null
+      ? new ServerModel(this.grab(row, this.tables.getServerAttributes(), serverPrefix), this.buildOpts)
+      : null
+
+    const actorModel = new ActorModel(this.grab(row, this.tables.getActorAttributes(), actorPrefix), this.buildOpts)
+    actorModel.Avatar = avatarModel
+    actorModel.Server = serverModel
+
+    return actorModel
+  }
+
+  private setUserHistory (row: SQLRow, videoModel: VideoModel) {
+    const id = row['userVideoHistory.id']
+    if (!id || this.historyDone.has(id)) return
+
+    const attributes = this.grab(row, this.tables.getUserHistoryAttributes(), 'userVideoHistory')
+    const historyModel = new UserVideoHistoryModel(attributes, this.buildOpts)
+    videoModel.UserVideoHistories.push(historyModel)
+
+    this.historyDone.add(id)
+  }
+
+  private addThumbnail (row: SQLRow, videoModel: VideoModel) {
+    const id = row['Thumbnails.id']
+    if (!id || this.thumbnailsDone.has(id)) return
+
+    const attributes = this.grab(row, this.tables.getThumbnailAttributes(), 'Thumbnails')
+    const thumbnailModel = new ThumbnailModel(attributes, this.buildOpts)
+    videoModel.Thumbnails.push(thumbnailModel)
+
+    this.thumbnailsDone.add(id)
+  }
+
+  private addWebTorrentFile (row: SQLRow, videoModel: VideoModel) {
+    const id = row['VideoFiles.id']
+    if (!id || this.videoFileMemo[id]) return
+
+    const attributes = this.grab(row, this.tables.getFileAttributes(), 'VideoFiles')
+    const videoFileModel = new VideoFileModel(attributes, this.buildOpts)
+    videoModel.VideoFiles.push(videoFileModel)
+
+    this.videoFileMemo[id] = videoFileModel
+  }
+
+  private addStreamingPlaylist (row: SQLRow, videoModel: VideoModel) {
+    const id = row['VideoStreamingPlaylists.id']
+    if (!id || this.videoStreamingPlaylistMemo[id]) return
+
+    const attributes = this.grab(row, this.tables.getStreamingPlaylistAttributes(), 'VideoStreamingPlaylists')
+    const streamingPlaylist = new VideoStreamingPlaylistModel(attributes, this.buildOpts)
+    streamingPlaylist.VideoFiles = []
+
+    videoModel.VideoStreamingPlaylists.push(streamingPlaylist)
+
+    this.videoStreamingPlaylistMemo[id] = streamingPlaylist
+  }
+
+  private addStreamingPlaylistFile (row: SQLRow) {
+    const id = row['VideoStreamingPlaylists.VideoFiles.id']
+    if (!id || this.videoFileMemo[id]) return
+
+    const streamingPlaylist = this.videoStreamingPlaylistMemo[row['VideoStreamingPlaylists.id']]
+
+    const attributes = this.grab(row, this.tables.getFileAttributes(), 'VideoStreamingPlaylists.VideoFiles')
+    const videoFileModel = new VideoFileModel(attributes, this.buildOpts)
+    streamingPlaylist.VideoFiles.push(videoFileModel)
+
+    this.videoFileMemo[id] = videoFileModel
+  }
+
+  private addRedundancy (row: SQLRow, prefix: string, to: VideoFileModel | VideoStreamingPlaylistModel) {
+    if (!to.RedundancyVideos) to.RedundancyVideos = []
+
+    const redundancyPrefix = `${prefix}.RedundancyVideos`
+    const id = row[`${redundancyPrefix}.id`]
+
+    if (!id || this.redundancyDone.has(id)) return
+
+    const attributes = this.grab(row, this.tables.getRedundancyAttributes(), redundancyPrefix)
+    const redundancyModel = new VideoRedundancyModel(attributes, this.buildOpts)
+    to.RedundancyVideos.push(redundancyModel)
+
+    this.redundancyDone.add(id)
+  }
+
+  private addTag (row: SQLRow, videoModel: VideoModel) {
+    if (!row['Tags.name']) return
+
+    const key = `${row['Tags.VideoTagModel.videoId']}-${row['Tags.VideoTagModel.tagId']}`
+    if (this.tagsDone.has(key)) return
+
+    const attributes = this.grab(row, this.tables.getTagAttributes(), 'Tags')
+    const tagModel = new TagModel(attributes, this.buildOpts)
+    videoModel.Tags.push(tagModel)
+
+    this.tagsDone.add(key)
+  }
+
+  private addTracker (row: SQLRow, videoModel: VideoModel) {
+    if (!row['Trackers.id']) return
+
+    const key = `${row['Trackers.VideoTrackerModel.videoId']}-${row['Trackers.VideoTrackerModel.trackerId']}`
+    if (this.trackersDone.has(key)) return
+
+    const attributes = this.grab(row, this.tables.getTrackerAttributes(), 'Trackers')
+    const trackerModel = new TrackerModel(attributes, this.buildOpts)
+    videoModel.Trackers.push(trackerModel)
+
+    this.trackersDone.add(key)
+  }
+
+  private setBlacklisted (row: SQLRow, videoModel: VideoModel) {
+    const id = row['VideoBlacklist.id']
+    if (!id || this.blacklistDone.has(id)) return
+
+    const attributes = this.grab(row, this.tables.getBlacklistedAttributes(), 'VideoBlacklist')
+    videoModel.VideoBlacklist = new VideoBlacklistModel(attributes, this.buildOpts)
+
+    this.blacklistDone.add(id)
+  }
+
+  private setScheduleVideoUpdate (row: SQLRow, videoModel: VideoModel) {
+    const id = row['ScheduleVideoUpdate.id']
+    if (!id || this.scheduleVideoUpdateDone.has(id)) return
+
+    const attributes = this.grab(row, this.tables.getScheduleUpdateAttributes(), 'ScheduleVideoUpdate')
+    videoModel.ScheduleVideoUpdate = new ScheduleVideoUpdateModel(attributes, this.buildOpts)
+
+    this.scheduleVideoUpdateDone.add(id)
+  }
+
+  private setLive (row: SQLRow, videoModel: VideoModel) {
+    const id = row['VideoLive.id']
+    if (!id || this.liveDone.has(id)) return
+
+    const attributes = this.grab(row, this.tables.getLiveAttributes(), 'VideoLive')
+    videoModel.VideoLive = new VideoLiveModel(attributes, this.buildOpts)
+
+    this.liveDone.add(id)
+  }
+
+  private grab (row: SQLRow, attributes: string[], prefix: string) {
+    const result: { [ id: string ]: string | number } = {}
+
+    for (const a of attributes) {
+      const key = prefix
+        ? prefix + '.' + a
+        : a
+
+      result[a] = row[key]
+    }
+
+    return result
+  }
+}
diff --git a/server/models/video/sql/shared/video-tables.ts b/server/models/video/sql/shared/video-tables.ts
new file mode 100644
index 000000000..abdd22188
--- /dev/null
+++ b/server/models/video/sql/shared/video-tables.ts
@@ -0,0 +1,263 @@
+
+/**
+ *
+ * Class to build video attributes/join names we want to fetch from the database
+ *
+ */
+export class VideoTables {
+
+  constructor (readonly mode: 'get' | 'list') {
+
+  }
+
+  getChannelAttributesForUser () {
+    return [ 'id', 'accountId' ]
+  }
+
+  getChannelAttributes () {
+    let attributeKeys = [
+      'id',
+      'name',
+      'description',
+      'actorId'
+    ]
+
+    if (this.mode === 'get') {
+      attributeKeys = attributeKeys.concat([
+        'support',
+        'createdAt',
+        'updatedAt'
+      ])
+    }
+
+    return attributeKeys
+  }
+
+  getUserAccountAttributes () {
+    return [ 'id', 'userId' ]
+  }
+
+  getAccountAttributes () {
+    let attributeKeys = [ 'id', 'name', 'actorId' ]
+
+    if (this.mode === 'get') {
+      attributeKeys = attributeKeys.concat([
+        'description',
+        'userId',
+        'createdAt',
+        'updatedAt'
+      ])
+    }
+
+    return attributeKeys
+  }
+
+  getThumbnailAttributes () {
+    let attributeKeys = [ 'id', 'type', 'filename' ]
+
+    if (this.mode === 'get') {
+      attributeKeys = attributeKeys.concat([
+        'height',
+        'width',
+        'fileUrl',
+        'automaticallyGenerated',
+        'videoId',
+        'videoPlaylistId',
+        'createdAt',
+        'updatedAt'
+      ])
+    }
+
+    return attributeKeys
+  }
+
+  getFileAttributes () {
+    return [
+      'id',
+      'createdAt',
+      'updatedAt',
+      'resolution',
+      'size',
+      'extname',
+      'filename',
+      'fileUrl',
+      'torrentFilename',
+      'torrentUrl',
+      'infoHash',
+      'fps',
+      'metadataUrl',
+      'videoStreamingPlaylistId',
+      'videoId'
+    ]
+  }
+
+  getStreamingPlaylistAttributes () {
+    let playlistKeys = [ 'id', 'playlistUrl', 'type' ]
+
+    if (this.mode === 'get') {
+      playlistKeys = playlistKeys.concat([
+        'p2pMediaLoaderInfohashes',
+        'p2pMediaLoaderPeerVersion',
+        'segmentsSha256Url',
+        'videoId',
+        'createdAt',
+        'updatedAt'
+      ])
+    }
+
+    return playlistKeys
+  }
+
+  getUserHistoryAttributes () {
+    return [ 'id', 'currentTime' ]
+  }
+
+  getPlaylistAttributes () {
+    return [
+      'createdAt',
+      'updatedAt',
+      'url',
+      'position',
+      'startTimestamp',
+      'stopTimestamp',
+      'videoPlaylistId'
+    ]
+  }
+
+  getTagAttributes () {
+    return [ 'id', 'name' ]
+  }
+
+  getVideoTagAttributes () {
+    return [ 'videoId', 'tagId', 'createdAt', 'updatedAt' ]
+  }
+
+  getBlacklistedAttributes () {
+    return [ 'id', 'reason', 'unfederated' ]
+  }
+
+  getScheduleUpdateAttributes () {
+    return [
+      'id',
+      'updateAt',
+      'privacy',
+      'videoId',
+      'createdAt',
+      'updatedAt'
+    ]
+  }
+
+  getLiveAttributes () {
+    return [
+      'id',
+      'streamKey',
+      'saveReplay',
+      'permanentLive',
+      'videoId',
+      'createdAt',
+      'updatedAt'
+    ]
+  }
+
+  getTrackerAttributes () {
+    return [ 'id', 'url' ]
+  }
+
+  getVideoTrackerAttributes () {
+    return [
+      'videoId',
+      'trackerId',
+      'createdAt',
+      'updatedAt'
+    ]
+  }
+
+  getRedundancyAttributes () {
+    return [ 'id', 'fileUrl' ]
+  }
+
+  getActorAttributes () {
+    let attributeKeys = [
+      'id',
+      'preferredUsername',
+      'url',
+      'serverId',
+      'avatarId'
+    ]
+
+    if (this.mode === 'get') {
+      attributeKeys = attributeKeys.concat([
+        'type',
+        'followersCount',
+        'followingCount',
+        'inboxUrl',
+        'outboxUrl',
+        'sharedInboxUrl',
+        'followersUrl',
+        'followingUrl',
+        'remoteCreatedAt',
+        'createdAt',
+        'updatedAt'
+      ])
+    }
+
+    return attributeKeys
+  }
+
+  getAvatarAttributes () {
+    let attributeKeys = [
+      'id',
+      'filename',
+      'type',
+      'fileUrl',
+      'onDisk',
+      'createdAt',
+      'updatedAt'
+    ]
+
+    if (this.mode === 'get') {
+      attributeKeys = attributeKeys.concat([
+        'height',
+        'width',
+        'type'
+      ])
+    }
+
+    return attributeKeys
+  }
+
+  getServerAttributes () {
+    return [ 'id', 'host' ]
+  }
+
+  getVideoAttributes () {
+    return [
+      'id',
+      'uuid',
+      'name',
+      'category',
+      'licence',
+      'language',
+      'privacy',
+      'nsfw',
+      'description',
+      'support',
+      'duration',
+      'views',
+      'likes',
+      'dislikes',
+      'remote',
+      'isLive',
+      'url',
+      'commentsEnabled',
+      'downloadEnabled',
+      'waitTranscoding',
+      'state',
+      'publishedAt',
+      'originallyPublishedAt',
+      'channelId',
+      'createdAt',
+      'updatedAt'
+    ]
+  }
+}