server/models/video/sql/video/shared/abstract-video-query-builder.ts

   1 import { Sequelize } from 'sequelize'
   2 import validator from 'validator'
   3 import { MUserAccountId } from '@server/types/models'
   4 import { ActorImageType } from '@shared/models'
   5 import { AbstractRunQuery } from '../../../../shared/abstract-run-query'
   6 import { createSafeIn } from '../../../../shared'
   7 import { VideoTableAttributes } from './video-table-attributes'
   8
   9 /**
  10  *
  11  * Abstract builder to create SQL query and fetch video models
  12  *
  13  */
  14
  15 export class AbstractVideoQueryBuilder extends AbstractRunQuery {
  16   protected attributes: { [key: string]: string } = {}
  17
  18   protected joins = ''
  19   protected where: string
  20
  21   protected tables: VideoTableAttributes
  22
  23   constructor (
  24     protected readonly sequelize: Sequelize,
  25     protected readonly mode: 'list' | 'get'
  26   ) {
  27     super(sequelize)
  28
  29     this.tables = new VideoTableAttributes(this.mode)
  30   }
  31
  32   protected buildSelect () {
  33     return 'SELECT ' + Object.keys(this.attributes).map(key => {
  34       const value = this.attributes[key]
  35       if (value) return `${key} AS ${value}`
  36
  37       return key
  38     }).join(', ')
  39   }
  40
  41   protected includeChannels () {
  42     this.addJoin('INNER JOIN "videoChannel" AS "VideoChannel" ON "video"."channelId" = "VideoChannel"."id"')
  43     this.addJoin('INNER JOIN "actor" AS "VideoChannel->Actor" ON "VideoChannel"."actorId" = "VideoChannel->Actor"."id"')
  44
  45     this.addJoin(
  46       'LEFT OUTER JOIN "server" AS "VideoChannel->Actor->Server" ON "VideoChannel->Actor"."serverId" = "VideoChannel->Actor->Server"."id"'
  47     )
  48
  49     this.addJoin(
  50       'LEFT OUTER JOIN "actorImage" AS "VideoChannel->Actor->Avatars" ' +
  51         'ON "VideoChannel->Actor"."id" = "VideoChannel->Actor->Avatars"."actorId" ' +
  52         `AND "VideoChannel->Actor->Avatars"."type" = ${ActorImageType.AVATAR}`
  53     )
  54
  55     this.attributes = {
  56       ...this.attributes,
  57
  58       ...this.buildAttributesObject('VideoChannel', this.tables.getChannelAttributes()),
  59       ...this.buildActorInclude('VideoChannel->Actor'),
  60       ...this.buildAvatarInclude('VideoChannel->Actor->Avatars'),
  61       ...this.buildServerInclude('VideoChannel->Actor->Server')
  62     }
  63   }
  64
  65   protected includeAccounts () {
  66     this.addJoin('INNER JOIN "account" AS "VideoChannel->Account" ON "VideoChannel"."accountId" = "VideoChannel->Account"."id"')
  67     this.addJoin(
  68       'INNER JOIN "actor" AS "VideoChannel->Account->Actor" ON "VideoChannel->Account"."actorId" = "VideoChannel->Account->Actor"."id"'
  69     )
  70
  71     this.addJoin(
  72       'LEFT OUTER JOIN "server" AS "VideoChannel->Account->Actor->Server" ' +
  73         'ON "VideoChannel->Account->Actor"."serverId" = "VideoChannel->Account->Actor->Server"."id"'
  74     )
  75
  76     this.addJoin(
  77       'LEFT OUTER JOIN "actorImage" AS "VideoChannel->Account->Actor->Avatars" ' +
  78         'ON "VideoChannel->Account"."actorId"= "VideoChannel->Account->Actor->Avatars"."actorId" ' +
  79         `AND "VideoChannel->Account->Actor->Avatars"."type" = ${ActorImageType.AVATAR}`
  80     )
  81
  82     this.attributes = {
  83       ...this.attributes,
  84
  85       ...this.buildAttributesObject('VideoChannel->Account', this.tables.getAccountAttributes()),
  86       ...this.buildActorInclude('VideoChannel->Account->Actor'),
  87       ...this.buildAvatarInclude('VideoChannel->Account->Actor->Avatars'),
  88       ...this.buildServerInclude('VideoChannel->Account->Actor->Server')
  89     }
  90   }
  91
  92   protected includeOwnerUser () {
  93     this.addJoin('INNER JOIN "videoChannel" AS "VideoChannel" ON "video"."channelId" = "VideoChannel"."id"')
  94     this.addJoin('INNER JOIN "account" AS "VideoChannel->Account" ON "VideoChannel"."accountId" = "VideoChannel->Account"."id"')
  95
  96     this.attributes = {
  97       ...this.attributes,
  98
  99       ...this.buildAttributesObject('VideoChannel', this.tables.getChannelAttributes()),
 100       ...this.buildAttributesObject('VideoChannel->Account', this.tables.getUserAccountAttributes())
 101     }
 102   }
 103
 104   protected includeThumbnails () {
 105     this.addJoin('LEFT OUTER JOIN "thumbnail" AS "Thumbnails" ON "video"."id" = "Thumbnails"."videoId"')
 106
 107     this.attributes = {
 108       ...this.attributes,
 109
 110       ...this.buildAttributesObject('Thumbnails', this.tables.getThumbnailAttributes())
 111     }
 112   }
 113
 114   protected includeWebtorrentFiles () {
 115     this.addJoin('LEFT JOIN "videoFile" AS "VideoFiles" ON "VideoFiles"."videoId" = "video"."id"')
 116
 117     this.attributes = {
 118       ...this.attributes,
 119
 120       ...this.buildAttributesObject('VideoFiles', this.tables.getFileAttributes())
 121     }
 122   }
 123
 124   protected includeStreamingPlaylistFiles () {
 125     this.addJoin(
 126       'LEFT JOIN "videoStreamingPlaylist" AS "VideoStreamingPlaylists" ON "VideoStreamingPlaylists"."videoId" = "video"."id"'
 127     )
 128
 129     this.addJoin(
 130       'LEFT JOIN "videoFile" AS "VideoStreamingPlaylists->VideoFiles" ' +
 131         'ON "VideoStreamingPlaylists->VideoFiles"."videoStreamingPlaylistId" = "VideoStreamingPlaylists"."id"'
 132     )
 133
 134     this.attributes = {
 135       ...this.attributes,
 136
 137       ...this.buildAttributesObject('VideoStreamingPlaylists', this.tables.getStreamingPlaylistAttributes()),
 138       ...this.buildAttributesObject('VideoStreamingPlaylists->VideoFiles', this.tables.getFileAttributes())
 139     }
 140   }
 141
 142   protected includeUserHistory (userId: number) {
 143     this.addJoin(
 144       'LEFT OUTER JOIN "userVideoHistory" ' +
 145         'ON "video"."id" = "userVideoHistory"."videoId" AND "userVideoHistory"."userId" = :userVideoHistoryId'
 146     )
 147
 148     this.replacements.userVideoHistoryId = userId
 149
 150     this.attributes = {
 151       ...this.attributes,
 152
 153       ...this.buildAttributesObject('userVideoHistory', this.tables.getUserHistoryAttributes())
 154     }
 155   }
 156
 157   protected includePlaylist (playlistId: number) {
 158     this.addJoin(
 159       'INNER JOIN "videoPlaylistElement" as "VideoPlaylistElement" ON "videoPlaylistElement"."videoId" = "video"."id" ' +
 160         'AND "VideoPlaylistElement"."videoPlaylistId" = :videoPlaylistId'
 161     )
 162
 163     this.replacements.videoPlaylistId = playlistId
 164
 165     this.attributes = {
 166       ...this.attributes,
 167
 168       ...this.buildAttributesObject('VideoPlaylistElement', this.tables.getPlaylistAttributes())
 169     }
 170   }
 171
 172   protected includeTags () {
 173     this.addJoin(
 174       'LEFT OUTER JOIN (' +
 175         '"videoTag" AS "Tags->VideoTagModel" INNER JOIN "tag" AS "Tags" ON "Tags"."id" = "Tags->VideoTagModel"."tagId"' +
 176       ') ' +
 177       'ON "video"."id" = "Tags->VideoTagModel"."videoId"'
 178     )
 179
 180     this.attributes = {
 181       ...this.attributes,
 182
 183       ...this.buildAttributesObject('Tags', this.tables.getTagAttributes()),
 184       ...this.buildAttributesObject('Tags->VideoTagModel', this.tables.getVideoTagAttributes())
 185     }
 186   }
 187
 188   protected includeBlacklisted () {
 189     this.addJoin(
 190       'LEFT OUTER JOIN "videoBlacklist" AS "VideoBlacklist" ON "video"."id" = "VideoBlacklist"."videoId"'
 191     )
 192
 193     this.attributes = {
 194       ...this.attributes,
 195
 196       ...this.buildAttributesObject('VideoBlacklist', this.tables.getBlacklistedAttributes())
 197     }
 198   }
 199
 200   protected includeBlockedOwnerAndServer (serverAccountId: number, user?: MUserAccountId) {
 201     const blockerIds = [ serverAccountId ]
 202     if (user) blockerIds.push(user.Account.id)
 203
 204     const inClause = createSafeIn(this.sequelize, blockerIds)
 205
 206     this.addJoin(
 207       'LEFT JOIN "accountBlocklist" AS "VideoChannel->Account->AccountBlocklist" ' +
 208         'ON "VideoChannel->Account"."id" = "VideoChannel->Account->AccountBlocklist"."targetAccountId" ' +
 209         'AND "VideoChannel->Account->AccountBlocklist"."accountId" IN (' + inClause + ')'
 210     )
 211
 212     this.addJoin(
 213       'LEFT JOIN "serverBlocklist" AS "VideoChannel->Account->Actor->Server->ServerBlocklist" ' +
 214         'ON "VideoChannel->Account->Actor->Server->ServerBlocklist"."targetServerId" = "VideoChannel->Account->Actor"."serverId" ' +
 215         'AND "VideoChannel->Account->Actor->Server->ServerBlocklist"."accountId" IN (' + inClause + ') '
 216     )
 217
 218     this.attributes = {
 219       ...this.attributes,
 220
 221       ...this.buildAttributesObject('VideoChannel->Account->AccountBlocklist', this.tables.getBlocklistAttributes()),
 222       ...this.buildAttributesObject('VideoChannel->Account->Actor->Server->ServerBlocklist', this.tables.getBlocklistAttributes())
 223     }
 224   }
 225
 226   protected includeScheduleUpdate () {
 227     this.addJoin(
 228       'LEFT OUTER JOIN "scheduleVideoUpdate" AS "ScheduleVideoUpdate" ON "video"."id" = "ScheduleVideoUpdate"."videoId"'
 229     )
 230
 231     this.attributes = {
 232       ...this.attributes,
 233
 234       ...this.buildAttributesObject('ScheduleVideoUpdate', this.tables.getScheduleUpdateAttributes())
 235     }
 236   }
 237
 238   protected includeLive () {
 239     this.addJoin(
 240       'LEFT OUTER JOIN "videoLive" AS "VideoLive" ON "video"."id" = "VideoLive"."videoId"'
 241     )
 242
 243     this.attributes = {
 244       ...this.attributes,
 245
 246       ...this.buildAttributesObject('VideoLive', this.tables.getLiveAttributes())
 247     }
 248   }
 249
 250   protected includeTrackers () {
 251     this.addJoin(
 252       'LEFT OUTER JOIN (' +
 253         '"videoTracker" AS "Trackers->VideoTrackerModel" ' +
 254           'INNER JOIN "tracker" AS "Trackers" ON "Trackers"."id" = "Trackers->VideoTrackerModel"."trackerId"' +
 255       ') ON "video"."id" = "Trackers->VideoTrackerModel"."videoId"'
 256     )
 257
 258     this.attributes = {
 259       ...this.attributes,
 260
 261       ...this.buildAttributesObject('Trackers', this.tables.getTrackerAttributes()),
 262       ...this.buildAttributesObject('Trackers->VideoTrackerModel', this.tables.getVideoTrackerAttributes())
 263     }
 264   }
 265
 266   protected includeWebTorrentRedundancies () {
 267     this.addJoin(
 268       'LEFT OUTER JOIN "videoRedundancy" AS "VideoFiles->RedundancyVideos" ON ' +
 269         '"VideoFiles"."id" = "VideoFiles->RedundancyVideos"."videoFileId"'
 270     )
 271
 272     this.attributes = {
 273       ...this.attributes,
 274
 275       ...this.buildAttributesObject('VideoFiles->RedundancyVideos', this.tables.getRedundancyAttributes())
 276     }
 277   }
 278
 279   protected includeStreamingPlaylistRedundancies () {
 280     this.addJoin(
 281       'LEFT OUTER JOIN "videoRedundancy" AS "VideoStreamingPlaylists->RedundancyVideos" ' +
 282         'ON "VideoStreamingPlaylists"."id" = "VideoStreamingPlaylists->RedundancyVideos"."videoStreamingPlaylistId"'
 283     )
 284
 285     this.attributes = {
 286       ...this.attributes,
 287
 288       ...this.buildAttributesObject('VideoStreamingPlaylists->RedundancyVideos', this.tables.getRedundancyAttributes())
 289     }
 290   }
 291
 292   protected buildActorInclude (prefixKey: string) {
 293     return this.buildAttributesObject(prefixKey, this.tables.getActorAttributes())
 294   }
 295
 296   protected buildAvatarInclude (prefixKey: string) {
 297     return this.buildAttributesObject(prefixKey, this.tables.getAvatarAttributes())
 298   }
 299
 300   protected buildServerInclude (prefixKey: string) {
 301     return this.buildAttributesObject(prefixKey, this.tables.getServerAttributes())
 302   }
 303
 304   protected buildAttributesObject (prefixKey: string, attributeKeys: string[]) {
 305     const result: { [id: string]: string } = {}
 306
 307     const prefixValue = prefixKey.replace(/->/g, '.')
 308
 309     for (const attribute of attributeKeys) {
 310       result[`"${prefixKey}"."${attribute}"`] = `"${prefixValue}.${attribute}"`
 311     }
 312
 313     return result
 314   }
 315
 316   protected whereId (options: { ids?: number[], id?: string | number, url?: string }) {
 317     if (options.ids) {
 318       this.where = `WHERE "video"."id" IN (${createSafeIn(this.sequelize, options.ids)})`
 319       return
 320     }
 321
 322     if (options.url) {
 323       this.where = 'WHERE "video"."url" = :videoUrl'
 324       this.replacements.videoUrl = options.url
 325       return
 326     }
 327
 328     if (validator.isInt('' + options.id)) {
 329       this.where = 'WHERE "video".id = :videoId'
 330     } else {
 331       this.where = 'WHERE uuid = :videoId'
 332     }
 333
 334     this.replacements.videoId = options.id
 335   }
 336
 337   protected addJoin (join: string) {
 338     this.joins += join + ' '
 339   }
 340 }