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