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

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