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