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 includeThumbnails () {
  84     this.addJoin('LEFT OUTER JOIN "thumbnail" AS "Thumbnails" ON "video"."id" = "Thumbnails"."videoId"')
  85
  86     this.attributes = {
  87       ...this.attributes,
  88
  89       ...this.buildAttributesObject('Thumbnails', this.tables.getThumbnailAttributes())
  90     }
  91   }
  92
  93   protected includeWebtorrentFiles (required: boolean) {
  94     const joinType = required ? 'INNER' : 'LEFT'
  95     this.addJoin(joinType + ' JOIN "videoFile" AS "VideoFiles" ON "VideoFiles"."videoId" = "video"."id"')
  96
  97     this.attributes = {
  98       ...this.attributes,
  99
 100       ...this.buildAttributesObject('VideoFiles', this.tables.getFileAttributes())
 101     }
 102   }
 103
 104   protected includeStreamingPlaylistFiles (required: boolean) {
 105     const joinType = required ? 'INNER' : 'LEFT'
 106
 107     this.addJoin(
 108       joinType + ' JOIN "videoStreamingPlaylist" AS "VideoStreamingPlaylists" ON "VideoStreamingPlaylists"."videoId" = "video"."id"'
 109     )
 110
 111     this.addJoin(
 112       joinType + ' JOIN "videoFile" AS "VideoStreamingPlaylists->VideoFiles" ' +
 113         'ON "VideoStreamingPlaylists->VideoFiles"."videoStreamingPlaylistId" = "VideoStreamingPlaylists"."id"'
 114     )
 115
 116     this.attributes = {
 117       ...this.attributes,
 118
 119       ...this.buildAttributesObject('VideoStreamingPlaylists', this.tables.getStreamingPlaylistAttributes()),
 120       ...this.buildAttributesObject('VideoStreamingPlaylists->VideoFiles', this.tables.getFileAttributes())
 121     }
 122   }
 123
 124   protected includeUserHistory (userId: number) {
 125     this.addJoin(
 126       'LEFT OUTER JOIN "userVideoHistory" ' +
 127         'ON "video"."id" = "userVideoHistory"."videoId" AND "userVideoHistory"."userId" = :userVideoHistoryId'
 128     )
 129
 130     this.replacements.userVideoHistoryId = userId
 131
 132     this.attributes = {
 133       ...this.attributes,
 134
 135       ...this.buildAttributesObject('userVideoHistory', this.tables.getUserHistoryAttributes())
 136     }
 137   }
 138
 139   protected includePlaylist (playlistId: number) {
 140     this.addJoin(
 141       'INNER JOIN "videoPlaylistElement" as "VideoPlaylistElement" ON "videoPlaylistElement"."videoId" = "video"."id" ' +
 142         'AND "VideoPlaylistElement"."videoPlaylistId" = :videoPlaylistId'
 143     )
 144
 145     this.replacements.videoPlaylistId = playlistId
 146
 147     this.attributes = {
 148       ...this.attributes,
 149
 150       ...this.buildAttributesObject('VideoPlaylistElement', this.tables.getPlaylistAttributes())
 151     }
 152   }
 153
 154   protected includeTags () {
 155     this.addJoin(
 156       'LEFT OUTER JOIN (' +
 157         '"videoTag" AS "Tags->VideoTagModel" INNER JOIN "tag" AS "Tags" ON "Tags"."id" = "Tags->VideoTagModel"."tagId"' +
 158       ') ' +
 159       'ON "video"."id" = "Tags->VideoTagModel"."videoId"'
 160     )
 161
 162     this.attributes = {
 163       ...this.attributes,
 164
 165       ...this.buildAttributesObject('Tags', this.tables.getTagAttributes()),
 166       ...this.buildAttributesObject('Tags->VideoTagModel', this.tables.getVideoTagAttributes())
 167     }
 168   }
 169
 170   protected includeBlacklisted () {
 171     this.addJoin(
 172       'LEFT OUTER JOIN "videoBlacklist" AS "VideoBlacklist" ON "video"."id" = "VideoBlacklist"."videoId"'
 173     )
 174
 175     this.attributes = {
 176       ...this.attributes,
 177
 178       ...this.buildAttributesObject('VideoBlacklist', this.tables.getBlacklistedAttributes())
 179     }
 180   }
 181
 182   protected includeScheduleUpdate () {
 183     this.addJoin(
 184       'LEFT OUTER JOIN "scheduleVideoUpdate" AS "ScheduleVideoUpdate" ON "video"."id" = "ScheduleVideoUpdate"."videoId"'
 185     )
 186
 187     this.attributes = {
 188       ...this.attributes,
 189
 190       ...this.buildAttributesObject('ScheduleVideoUpdate', this.tables.getScheduleUpdateAttributes())
 191     }
 192   }
 193
 194   protected includeLive () {
 195     this.addJoin(
 196       'LEFT OUTER JOIN "videoLive" AS "VideoLive" ON "video"."id" = "VideoLive"."videoId"'
 197     )
 198
 199     this.attributes = {
 200       ...this.attributes,
 201
 202       ...this.buildAttributesObject('VideoLive', this.tables.getLiveAttributes())
 203     }
 204   }
 205
 206   protected includeTrackers () {
 207     this.addJoin(
 208       'LEFT OUTER JOIN (' +
 209         '"videoTracker" AS "Trackers->VideoTrackerModel" ' +
 210           'INNER JOIN "tracker" AS "Trackers" ON "Trackers"."id" = "Trackers->VideoTrackerModel"."trackerId"' +
 211       ') ON "video"."id" = "Trackers->VideoTrackerModel"."videoId"'
 212     )
 213
 214     this.attributes = {
 215       ...this.attributes,
 216
 217       ...this.buildAttributesObject('Trackers', this.tables.getTrackerAttributes()),
 218       ...this.buildAttributesObject('Trackers->VideoTrackerModel', this.tables.getVideoTrackerAttributes())
 219     }
 220   }
 221
 222   protected includeWebTorrentRedundancies () {
 223     this.addJoin(
 224       'LEFT OUTER JOIN "videoRedundancy" AS "VideoFiles->RedundancyVideos" ON ' +
 225         '"VideoFiles"."id" = "VideoFiles->RedundancyVideos"."videoFileId"'
 226     )
 227
 228     this.attributes = {
 229       ...this.attributes,
 230
 231       ...this.buildAttributesObject('VideoFiles->RedundancyVideos', this.tables.getRedundancyAttributes())
 232     }
 233   }
 234
 235   protected includeStreamingPlaylistRedundancies () {
 236     this.addJoin(
 237       'LEFT OUTER JOIN "videoRedundancy" AS "VideoStreamingPlaylists->RedundancyVideos" ' +
 238         'ON "VideoStreamingPlaylists"."id" = "VideoStreamingPlaylists->RedundancyVideos"."videoStreamingPlaylistId"'
 239     )
 240
 241     this.attributes = {
 242       ...this.attributes,
 243
 244       ...this.buildAttributesObject('VideoStreamingPlaylists->RedundancyVideos', this.tables.getRedundancyAttributes())
 245     }
 246   }
 247
 248   protected buildActorInclude (prefixKey: string) {
 249     return this.buildAttributesObject(prefixKey, this.tables.getActorAttributes())
 250   }
 251
 252   protected buildAvatarInclude (prefixKey: string) {
 253     return this.buildAttributesObject(prefixKey, this.tables.getAvatarAttributes())
 254   }
 255
 256   protected buildServerInclude (prefixKey: string) {
 257     return this.buildAttributesObject(prefixKey, this.tables.getServerAttributes())
 258   }
 259
 260   protected buildAttributesObject (prefixKey: string, attributeKeys: string[]) {
 261     const result: { [id: string]: string} = {}
 262
 263     const prefixValue = prefixKey.replace(/->/g, '.')
 264
 265     for (const attribute of attributeKeys) {
 266       result[`"${prefixKey}"."${attribute}"`] = `"${prefixValue}.${attribute}"`
 267     }
 268
 269     return result
 270   }
 271
 272   protected whereId (id: string | number) {
 273     if (validator.isInt('' + id)) {
 274       this.where = 'WHERE "video".id = :videoId'
 275     } else {
 276       this.where = 'WHERE uuid = :videoId'
 277     }
 278
 279     this.replacements.videoId = id
 280   }
 281
 282   protected addJoin (join: string) {
 283     this.joins += join + ' '
 284   }
 285 }