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

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