1 import validator from 'validator'
2 import { AbstractVideosQueryBuilder } from './abstract-videos-query-builder'
3 import { VideoTables } from './video-tables'
7 * Abstract builder to create SQL query and fetch video models
11 export class AbstractVideosModelQueryBuilder extends AbstractVideosQueryBuilder {
12 protected attributes: { [key: string]: string } = {}
15 protected where: string
17 protected tables: VideoTables
19 constructor (protected readonly mode: 'list' | 'get') {
22 this.tables = new VideoTables(this.mode)
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}`
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"')
39 'LEFT OUTER JOIN "server" AS "VideoChannel->Actor->Server" ON "VideoChannel->Actor"."serverId" = "VideoChannel->Actor->Server"."id"'
43 'LEFT OUTER JOIN "actorImage" AS "VideoChannel->Actor->Avatar" ' +
44 'ON "VideoChannel->Actor"."avatarId" = "VideoChannel->Actor->Avatar"."id"'
50 ...this.buildAttributesObject('VideoChannel', this.tables.getChannelAttributes()),
51 ...this.buildActorInclude('VideoChannel->Actor'),
52 ...this.buildAvatarInclude('VideoChannel->Actor->Avatar'),
53 ...this.buildServerInclude('VideoChannel->Actor->Server')
57 protected includeAccounts () {
58 this.addJoin('INNER JOIN "account" AS "VideoChannel->Account" ON "VideoChannel"."accountId" = "VideoChannel->Account"."id"')
60 'INNER JOIN "actor" AS "VideoChannel->Account->Actor" ON "VideoChannel->Account"."actorId" = "VideoChannel->Account->Actor"."id"'
64 'LEFT OUTER JOIN "server" AS "VideoChannel->Account->Actor->Server" ' +
65 'ON "VideoChannel->Account->Actor"."serverId" = "VideoChannel->Account->Actor->Server"."id"'
69 'LEFT OUTER JOIN "actorImage" AS "VideoChannel->Account->Actor->Avatar" ' +
70 'ON "VideoChannel->Account->Actor"."avatarId" = "VideoChannel->Account->Actor->Avatar"."id"'
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')
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"')
90 ...this.buildAttributesObject('VideoChannel', this.tables.getChannelAttributes()),
91 ...this.buildAttributesObject('VideoChannel->Account', this.tables.getUserAccountAttributes())
95 protected includeThumbnails () {
96 this.addJoin('LEFT OUTER JOIN "thumbnail" AS "Thumbnails" ON "video"."id" = "Thumbnails"."videoId"')
101 ...this.buildAttributesObject('Thumbnails', this.tables.getThumbnailAttributes())
105 protected includeWebtorrentFiles () {
106 this.addJoin('LEFT JOIN "videoFile" AS "VideoFiles" ON "VideoFiles"."videoId" = "video"."id"')
111 ...this.buildAttributesObject('VideoFiles', this.tables.getFileAttributes())
115 protected includeStreamingPlaylistFiles () {
117 'LEFT JOIN "videoStreamingPlaylist" AS "VideoStreamingPlaylists" ON "VideoStreamingPlaylists"."videoId" = "video"."id"'
121 'LEFT JOIN "videoFile" AS "VideoStreamingPlaylists->VideoFiles" ' +
122 'ON "VideoStreamingPlaylists->VideoFiles"."videoStreamingPlaylistId" = "VideoStreamingPlaylists"."id"'
128 ...this.buildAttributesObject('VideoStreamingPlaylists', this.tables.getStreamingPlaylistAttributes()),
129 ...this.buildAttributesObject('VideoStreamingPlaylists->VideoFiles', this.tables.getFileAttributes())
133 protected includeUserHistory (userId: number) {
135 'LEFT OUTER JOIN "userVideoHistory" ' +
136 'ON "video"."id" = "userVideoHistory"."videoId" AND "userVideoHistory"."userId" = :userVideoHistoryId'
139 this.replacements.userVideoHistoryId = userId
144 ...this.buildAttributesObject('userVideoHistory', this.tables.getUserHistoryAttributes())
148 protected includePlaylist (playlistId: number) {
150 'INNER JOIN "videoPlaylistElement" as "VideoPlaylistElement" ON "videoPlaylistElement"."videoId" = "video"."id" ' +
151 'AND "VideoPlaylistElement"."videoPlaylistId" = :videoPlaylistId'
154 this.replacements.videoPlaylistId = playlistId
159 ...this.buildAttributesObject('VideoPlaylistElement', this.tables.getPlaylistAttributes())
163 protected includeTags () {
165 'LEFT OUTER JOIN (' +
166 '"videoTag" AS "Tags->VideoTagModel" INNER JOIN "tag" AS "Tags" ON "Tags"."id" = "Tags->VideoTagModel"."tagId"' +
168 'ON "video"."id" = "Tags->VideoTagModel"."videoId"'
174 ...this.buildAttributesObject('Tags', this.tables.getTagAttributes()),
175 ...this.buildAttributesObject('Tags->VideoTagModel', this.tables.getVideoTagAttributes())
179 protected includeBlacklisted () {
181 'LEFT OUTER JOIN "videoBlacklist" AS "VideoBlacklist" ON "video"."id" = "VideoBlacklist"."videoId"'
187 ...this.buildAttributesObject('VideoBlacklist', this.tables.getBlacklistedAttributes())
191 protected includeScheduleUpdate () {
193 'LEFT OUTER JOIN "scheduleVideoUpdate" AS "ScheduleVideoUpdate" ON "video"."id" = "ScheduleVideoUpdate"."videoId"'
199 ...this.buildAttributesObject('ScheduleVideoUpdate', this.tables.getScheduleUpdateAttributes())
203 protected includeLive () {
205 'LEFT OUTER JOIN "videoLive" AS "VideoLive" ON "video"."id" = "VideoLive"."videoId"'
211 ...this.buildAttributesObject('VideoLive', this.tables.getLiveAttributes())
215 protected includeTrackers () {
217 'LEFT OUTER JOIN (' +
218 '"videoTracker" AS "Trackers->VideoTrackerModel" ' +
219 'INNER JOIN "tracker" AS "Trackers" ON "Trackers"."id" = "Trackers->VideoTrackerModel"."trackerId"' +
220 ') ON "video"."id" = "Trackers->VideoTrackerModel"."videoId"'
226 ...this.buildAttributesObject('Trackers', this.tables.getTrackerAttributes()),
227 ...this.buildAttributesObject('Trackers->VideoTrackerModel', this.tables.getVideoTrackerAttributes())
231 protected includeWebTorrentRedundancies () {
233 'LEFT OUTER JOIN "videoRedundancy" AS "VideoFiles->RedundancyVideos" ON ' +
234 '"VideoFiles"."id" = "VideoFiles->RedundancyVideos"."videoFileId"'
240 ...this.buildAttributesObject('VideoFiles->RedundancyVideos', this.tables.getRedundancyAttributes())
244 protected includeStreamingPlaylistRedundancies () {
246 'LEFT OUTER JOIN "videoRedundancy" AS "VideoStreamingPlaylists->RedundancyVideos" ' +
247 'ON "VideoStreamingPlaylists"."id" = "VideoStreamingPlaylists->RedundancyVideos"."videoStreamingPlaylistId"'
253 ...this.buildAttributesObject('VideoStreamingPlaylists->RedundancyVideos', this.tables.getRedundancyAttributes())
257 protected buildActorInclude (prefixKey: string) {
258 return this.buildAttributesObject(prefixKey, this.tables.getActorAttributes())
261 protected buildAvatarInclude (prefixKey: string) {
262 return this.buildAttributesObject(prefixKey, this.tables.getAvatarAttributes())
265 protected buildServerInclude (prefixKey: string) {
266 return this.buildAttributesObject(prefixKey, this.tables.getServerAttributes())
269 protected buildAttributesObject (prefixKey: string, attributeKeys: string[]) {
270 const result: { [id: string]: string} = {}
272 const prefixValue = prefixKey.replace(/->/g, '.')
274 for (const attribute of attributeKeys) {
275 result[`"${prefixKey}"."${attribute}"`] = `"${prefixValue}.${attribute}"`
281 protected whereId (options: { id?: string | number, url?: string }) {
283 this.where = 'WHERE "video"."url" = :videoUrl'
284 this.replacements.videoUrl = options.url
288 if (validator.isInt('' + options.id)) {
289 this.where = 'WHERE "video".id = :videoId'
291 this.where = 'WHERE uuid = :videoId'
294 this.replacements.videoId = options.id
297 protected addJoin (join: string) {
298 this.joins += join + ' '