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