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'
9 * Abstract builder to create SQL query and fetch video models
13 export class AbstractVideoQueryBuilder extends AbstractRunQuery {
14 protected attributes: { [key: string]: string } = {}
17 protected where: string
19 protected tables: VideoTableAttributes
21 constructor (protected readonly mode: 'list' | 'get') {
24 this.tables = new VideoTableAttributes(this.mode)
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}`
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"')
41 'LEFT OUTER JOIN "server" AS "VideoChannel->Actor->Server" ON "VideoChannel->Actor"."serverId" = "VideoChannel->Actor->Server"."id"'
45 'LEFT OUTER JOIN "actorImage" AS "VideoChannel->Actor->Avatar" ' +
46 'ON "VideoChannel->Actor"."avatarId" = "VideoChannel->Actor->Avatar"."id"'
52 ...this.buildAttributesObject('VideoChannel', this.tables.getChannelAttributes()),
53 ...this.buildActorInclude('VideoChannel->Actor'),
54 ...this.buildAvatarInclude('VideoChannel->Actor->Avatar'),
55 ...this.buildServerInclude('VideoChannel->Actor->Server')
59 protected includeAccounts () {
60 this.addJoin('INNER JOIN "account" AS "VideoChannel->Account" ON "VideoChannel"."accountId" = "VideoChannel->Account"."id"')
62 'INNER JOIN "actor" AS "VideoChannel->Account->Actor" ON "VideoChannel->Account"."actorId" = "VideoChannel->Account->Actor"."id"'
66 'LEFT OUTER JOIN "server" AS "VideoChannel->Account->Actor->Server" ' +
67 'ON "VideoChannel->Account->Actor"."serverId" = "VideoChannel->Account->Actor->Server"."id"'
71 'LEFT OUTER JOIN "actorImage" AS "VideoChannel->Account->Actor->Avatar" ' +
72 'ON "VideoChannel->Account->Actor"."avatarId" = "VideoChannel->Account->Actor->Avatar"."id"'
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')
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"')
92 ...this.buildAttributesObject('VideoChannel', this.tables.getChannelAttributes()),
93 ...this.buildAttributesObject('VideoChannel->Account', this.tables.getUserAccountAttributes())
97 protected includeThumbnails () {
98 this.addJoin('LEFT OUTER JOIN "thumbnail" AS "Thumbnails" ON "video"."id" = "Thumbnails"."videoId"')
103 ...this.buildAttributesObject('Thumbnails', this.tables.getThumbnailAttributes())
107 protected includeWebtorrentFiles () {
108 this.addJoin('LEFT JOIN "videoFile" AS "VideoFiles" ON "VideoFiles"."videoId" = "video"."id"')
113 ...this.buildAttributesObject('VideoFiles', this.tables.getFileAttributes())
117 protected includeStreamingPlaylistFiles () {
119 'LEFT JOIN "videoStreamingPlaylist" AS "VideoStreamingPlaylists" ON "VideoStreamingPlaylists"."videoId" = "video"."id"'
123 'LEFT JOIN "videoFile" AS "VideoStreamingPlaylists->VideoFiles" ' +
124 'ON "VideoStreamingPlaylists->VideoFiles"."videoStreamingPlaylistId" = "VideoStreamingPlaylists"."id"'
130 ...this.buildAttributesObject('VideoStreamingPlaylists', this.tables.getStreamingPlaylistAttributes()),
131 ...this.buildAttributesObject('VideoStreamingPlaylists->VideoFiles', this.tables.getFileAttributes())
135 protected includeUserHistory (userId: number) {
137 'LEFT OUTER JOIN "userVideoHistory" ' +
138 'ON "video"."id" = "userVideoHistory"."videoId" AND "userVideoHistory"."userId" = :userVideoHistoryId'
141 this.replacements.userVideoHistoryId = userId
146 ...this.buildAttributesObject('userVideoHistory', this.tables.getUserHistoryAttributes())
150 protected includePlaylist (playlistId: number) {
152 'INNER JOIN "videoPlaylistElement" as "VideoPlaylistElement" ON "videoPlaylistElement"."videoId" = "video"."id" ' +
153 'AND "VideoPlaylistElement"."videoPlaylistId" = :videoPlaylistId'
156 this.replacements.videoPlaylistId = playlistId
161 ...this.buildAttributesObject('VideoPlaylistElement', this.tables.getPlaylistAttributes())
165 protected includeTags () {
167 'LEFT OUTER JOIN (' +
168 '"videoTag" AS "Tags->VideoTagModel" INNER JOIN "tag" AS "Tags" ON "Tags"."id" = "Tags->VideoTagModel"."tagId"' +
170 'ON "video"."id" = "Tags->VideoTagModel"."videoId"'
176 ...this.buildAttributesObject('Tags', this.tables.getTagAttributes()),
177 ...this.buildAttributesObject('Tags->VideoTagModel', this.tables.getVideoTagAttributes())
181 protected includeBlacklisted () {
183 'LEFT OUTER JOIN "videoBlacklist" AS "VideoBlacklist" ON "video"."id" = "VideoBlacklist"."videoId"'
189 ...this.buildAttributesObject('VideoBlacklist', this.tables.getBlacklistedAttributes())
193 protected includeBlockedOwnerAndServer (serverAccountId: number, user?: MUserAccountId) {
194 const blockerIds = [ serverAccountId ]
195 if (user) blockerIds.push(user.Account.id)
197 const inClause = createSafeIn(this.sequelize, blockerIds)
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 + ')'
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 + ') '
214 ...this.buildAttributesObject('VideoChannel->Account->AccountBlocklist', this.tables.getBlocklistAttributes()),
215 ...this.buildAttributesObject('VideoChannel->Account->Actor->Server->ServerBlocklist', this.tables.getBlocklistAttributes())
219 protected includeScheduleUpdate () {
221 'LEFT OUTER JOIN "scheduleVideoUpdate" AS "ScheduleVideoUpdate" ON "video"."id" = "ScheduleVideoUpdate"."videoId"'
227 ...this.buildAttributesObject('ScheduleVideoUpdate', this.tables.getScheduleUpdateAttributes())
231 protected includeLive () {
233 'LEFT OUTER JOIN "videoLive" AS "VideoLive" ON "video"."id" = "VideoLive"."videoId"'
239 ...this.buildAttributesObject('VideoLive', this.tables.getLiveAttributes())
243 protected includeTrackers () {
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"'
254 ...this.buildAttributesObject('Trackers', this.tables.getTrackerAttributes()),
255 ...this.buildAttributesObject('Trackers->VideoTrackerModel', this.tables.getVideoTrackerAttributes())
259 protected includeWebTorrentRedundancies () {
261 'LEFT OUTER JOIN "videoRedundancy" AS "VideoFiles->RedundancyVideos" ON ' +
262 '"VideoFiles"."id" = "VideoFiles->RedundancyVideos"."videoFileId"'
268 ...this.buildAttributesObject('VideoFiles->RedundancyVideos', this.tables.getRedundancyAttributes())
272 protected includeStreamingPlaylistRedundancies () {
274 'LEFT OUTER JOIN "videoRedundancy" AS "VideoStreamingPlaylists->RedundancyVideos" ' +
275 'ON "VideoStreamingPlaylists"."id" = "VideoStreamingPlaylists->RedundancyVideos"."videoStreamingPlaylistId"'
281 ...this.buildAttributesObject('VideoStreamingPlaylists->RedundancyVideos', this.tables.getRedundancyAttributes())
285 protected buildActorInclude (prefixKey: string) {
286 return this.buildAttributesObject(prefixKey, this.tables.getActorAttributes())
289 protected buildAvatarInclude (prefixKey: string) {
290 return this.buildAttributesObject(prefixKey, this.tables.getAvatarAttributes())
293 protected buildServerInclude (prefixKey: string) {
294 return this.buildAttributesObject(prefixKey, this.tables.getServerAttributes())
297 protected buildAttributesObject (prefixKey: string, attributeKeys: string[]) {
298 const result: { [id: string]: string} = {}
300 const prefixValue = prefixKey.replace(/->/g, '.')
302 for (const attribute of attributeKeys) {
303 result[`"${prefixKey}"."${attribute}"`] = `"${prefixValue}.${attribute}"`
309 protected whereId (options: { id?: string | number, url?: string }) {
311 this.where = 'WHERE "video"."url" = :videoUrl'
312 this.replacements.videoUrl = options.url
316 if (validator.isInt('' + options.id)) {
317 this.where = 'WHERE "video".id = :videoId'
319 this.where = 'WHERE uuid = :videoId'
322 this.replacements.videoId = options.id
325 protected addJoin (join: string) {
326 this.joins += join + ' '