diff options
Diffstat (limited to 'server/models/video/sql/shared/abstract-video-query-builder.ts')
-rw-r--r-- | server/models/video/sql/shared/abstract-video-query-builder.ts | 328 |
1 files changed, 328 insertions, 0 deletions
diff --git a/server/models/video/sql/shared/abstract-video-query-builder.ts b/server/models/video/sql/shared/abstract-video-query-builder.ts new file mode 100644 index 000000000..a6afb04e4 --- /dev/null +++ b/server/models/video/sql/shared/abstract-video-query-builder.ts | |||
@@ -0,0 +1,328 @@ | |||
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' | ||
6 | |||
7 | /** | ||
8 | * | ||
9 | * Abstract builder to create SQL query and fetch video models | ||
10 | * | ||
11 | */ | ||
12 | |||
13 | export class AbstractVideoQueryBuilder extends AbstractRunQuery { | ||
14 | protected attributes: { [key: string]: string } = {} | ||
15 | |||
16 | protected joins = '' | ||
17 | protected where: string | ||
18 | |||
19 | protected tables: VideoTableAttributes | ||
20 | |||
21 | constructor (protected readonly mode: 'list' | 'get') { | ||
22 | super() | ||
23 | |||
24 | this.tables = new VideoTableAttributes(this.mode) | ||
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 () { | ||
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"') | ||
39 | |||
40 | this.addJoin( | ||
41 | 'LEFT OUTER JOIN "server" AS "VideoChannel->Actor->Server" ON "VideoChannel->Actor"."serverId" = "VideoChannel->Actor->Server"."id"' | ||
42 | ) | ||
43 | |||
44 | this.addJoin( | ||
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 | |||
52 | ...this.buildAttributesObject('VideoChannel', this.tables.getChannelAttributes()), | ||
53 | ...this.buildActorInclude('VideoChannel->Actor'), | ||
54 | ...this.buildAvatarInclude('VideoChannel->Actor->Avatar'), | ||
55 | ...this.buildServerInclude('VideoChannel->Actor->Server') | ||
56 | } | ||
57 | } | ||
58 | |||
59 | protected includeAccounts () { | ||
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 | ) | ||
64 | |||
65 | this.addJoin( | ||
66 | 'LEFT OUTER JOIN "server" AS "VideoChannel->Account->Actor->Server" ' + | ||
67 | 'ON "VideoChannel->Account->Actor"."serverId" = "VideoChannel->Account->Actor->Server"."id"' | ||
68 | ) | ||
69 | |||
70 | this.addJoin( | ||
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 | |||
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') | ||
82 | } | ||
83 | } | ||
84 | |||
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 | |||
97 | protected includeThumbnails () { | ||
98 | this.addJoin('LEFT OUTER JOIN "thumbnail" AS "Thumbnails" ON "video"."id" = "Thumbnails"."videoId"') | ||
99 | |||
100 | this.attributes = { | ||
101 | ...this.attributes, | ||
102 | |||
103 | ...this.buildAttributesObject('Thumbnails', this.tables.getThumbnailAttributes()) | ||
104 | } | ||
105 | } | ||
106 | |||
107 | protected includeWebtorrentFiles () { | ||
108 | this.addJoin('LEFT JOIN "videoFile" AS "VideoFiles" ON "VideoFiles"."videoId" = "video"."id"') | ||
109 | |||
110 | this.attributes = { | ||
111 | ...this.attributes, | ||
112 | |||
113 | ...this.buildAttributesObject('VideoFiles', this.tables.getFileAttributes()) | ||
114 | } | ||
115 | } | ||
116 | |||
117 | protected includeStreamingPlaylistFiles () { | ||
118 | this.addJoin( | ||
119 | 'LEFT JOIN "videoStreamingPlaylist" AS "VideoStreamingPlaylists" ON "VideoStreamingPlaylists"."videoId" = "video"."id"' | ||
120 | ) | ||
121 | |||
122 | this.addJoin( | ||
123 | 'LEFT JOIN "videoFile" AS "VideoStreamingPlaylists->VideoFiles" ' + | ||
124 | 'ON "VideoStreamingPlaylists->VideoFiles"."videoStreamingPlaylistId" = "VideoStreamingPlaylists"."id"' | ||
125 | ) | ||
126 | |||
127 | this.attributes = { | ||
128 | ...this.attributes, | ||
129 | |||
130 | ...this.buildAttributesObject('VideoStreamingPlaylists', this.tables.getStreamingPlaylistAttributes()), | ||
131 | ...this.buildAttributesObject('VideoStreamingPlaylists->VideoFiles', this.tables.getFileAttributes()) | ||
132 | } | ||
133 | } | ||
134 | |||
135 | protected includeUserHistory (userId: number) { | ||
136 | this.addJoin( | ||
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 | |||
146 | ...this.buildAttributesObject('userVideoHistory', this.tables.getUserHistoryAttributes()) | ||
147 | } | ||
148 | } | ||
149 | |||
150 | protected includePlaylist (playlistId: number) { | ||
151 | this.addJoin( | ||
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 | |||
161 | ...this.buildAttributesObject('VideoPlaylistElement', this.tables.getPlaylistAttributes()) | ||
162 | } | ||
163 | } | ||
164 | |||
165 | protected includeTags () { | ||
166 | this.addJoin( | ||
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 | |||
176 | ...this.buildAttributesObject('Tags', this.tables.getTagAttributes()), | ||
177 | ...this.buildAttributesObject('Tags->VideoTagModel', this.tables.getVideoTagAttributes()) | ||
178 | } | ||
179 | } | ||
180 | |||
181 | protected includeBlacklisted () { | ||
182 | this.addJoin( | ||
183 | 'LEFT OUTER JOIN "videoBlacklist" AS "VideoBlacklist" ON "video"."id" = "VideoBlacklist"."videoId"' | ||
184 | ) | ||
185 | |||
186 | this.attributes = { | ||
187 | ...this.attributes, | ||
188 | |||
189 | ...this.buildAttributesObject('VideoBlacklist', this.tables.getBlacklistedAttributes()) | ||
190 | } | ||
191 | } | ||
192 | |||
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 | |||
219 | protected includeScheduleUpdate () { | ||
220 | this.addJoin( | ||
221 | 'LEFT OUTER JOIN "scheduleVideoUpdate" AS "ScheduleVideoUpdate" ON "video"."id" = "ScheduleVideoUpdate"."videoId"' | ||
222 | ) | ||
223 | |||
224 | this.attributes = { | ||
225 | ...this.attributes, | ||
226 | |||
227 | ...this.buildAttributesObject('ScheduleVideoUpdate', this.tables.getScheduleUpdateAttributes()) | ||
228 | } | ||
229 | } | ||
230 | |||
231 | protected includeLive () { | ||
232 | this.addJoin( | ||
233 | 'LEFT OUTER JOIN "videoLive" AS "VideoLive" ON "video"."id" = "VideoLive"."videoId"' | ||
234 | ) | ||
235 | |||
236 | this.attributes = { | ||
237 | ...this.attributes, | ||
238 | |||
239 | ...this.buildAttributesObject('VideoLive', this.tables.getLiveAttributes()) | ||
240 | } | ||
241 | } | ||
242 | |||
243 | protected includeTrackers () { | ||
244 | this.addJoin( | ||
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 | |||
254 | ...this.buildAttributesObject('Trackers', this.tables.getTrackerAttributes()), | ||
255 | ...this.buildAttributesObject('Trackers->VideoTrackerModel', this.tables.getVideoTrackerAttributes()) | ||
256 | } | ||
257 | } | ||
258 | |||
259 | protected includeWebTorrentRedundancies () { | ||
260 | this.addJoin( | ||
261 | 'LEFT OUTER JOIN "videoRedundancy" AS "VideoFiles->RedundancyVideos" ON ' + | ||
262 | '"VideoFiles"."id" = "VideoFiles->RedundancyVideos"."videoFileId"' | ||
263 | ) | ||
264 | |||
265 | this.attributes = { | ||
266 | ...this.attributes, | ||
267 | |||
268 | ...this.buildAttributesObject('VideoFiles->RedundancyVideos', this.tables.getRedundancyAttributes()) | ||
269 | } | ||
270 | } | ||
271 | |||
272 | protected includeStreamingPlaylistRedundancies () { | ||
273 | this.addJoin( | ||
274 | 'LEFT OUTER JOIN "videoRedundancy" AS "VideoStreamingPlaylists->RedundancyVideos" ' + | ||
275 | 'ON "VideoStreamingPlaylists"."id" = "VideoStreamingPlaylists->RedundancyVideos"."videoStreamingPlaylistId"' | ||
276 | ) | ||
277 | |||
278 | this.attributes = { | ||
279 | ...this.attributes, | ||
280 | |||
281 | ...this.buildAttributesObject('VideoStreamingPlaylists->RedundancyVideos', this.tables.getRedundancyAttributes()) | ||
282 | } | ||
283 | } | ||
284 | |||
285 | protected buildActorInclude (prefixKey: string) { | ||
286 | return this.buildAttributesObject(prefixKey, this.tables.getActorAttributes()) | ||
287 | } | ||
288 | |||
289 | protected buildAvatarInclude (prefixKey: string) { | ||
290 | return this.buildAttributesObject(prefixKey, this.tables.getAvatarAttributes()) | ||
291 | } | ||
292 | |||
293 | protected buildServerInclude (prefixKey: string) { | ||
294 | return this.buildAttributesObject(prefixKey, this.tables.getServerAttributes()) | ||
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 | } | ||
308 | |||
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)) { | ||
317 | this.where = 'WHERE "video".id = :videoId' | ||
318 | } else { | ||
319 | this.where = 'WHERE uuid = :videoId' | ||
320 | } | ||
321 | |||
322 | this.replacements.videoId = options.id | ||
323 | } | ||
324 | |||
325 | protected addJoin (join: string) { | ||
326 | this.joins += join + ' ' | ||
327 | } | ||
328 | } | ||