diff options
author | Chocobozzz <me@florianbigard.com> | 2023-01-05 15:31:51 +0100 |
---|---|---|
committer | Chocobozzz <me@florianbigard.com> | 2023-01-09 09:29:02 +0100 |
commit | cde3d90ded5debb24281a444eabb720b721e5600 (patch) | |
tree | 9ad93c2228b980863d06fea45e3c0b04003ede2a /server/models/video/sql | |
parent | 458685e0d039a0ad3fa4f26d99746f7d6d0b40e9 (diff) | |
download | PeerTube-cde3d90ded5debb24281a444eabb720b721e5600.tar.gz PeerTube-cde3d90ded5debb24281a444eabb720b721e5600.tar.zst PeerTube-cde3d90ded5debb24281a444eabb720b721e5600.zip |
Use raw sql for comments
Diffstat (limited to 'server/models/video/sql')
-rw-r--r-- | server/models/video/sql/comment/video-comment-list-query-builder.ts | 394 | ||||
-rw-r--r-- | server/models/video/sql/comment/video-comment-table-attributes.ts | 78 |
2 files changed, 472 insertions, 0 deletions
diff --git a/server/models/video/sql/comment/video-comment-list-query-builder.ts b/server/models/video/sql/comment/video-comment-list-query-builder.ts new file mode 100644 index 000000000..f3f6910e1 --- /dev/null +++ b/server/models/video/sql/comment/video-comment-list-query-builder.ts | |||
@@ -0,0 +1,394 @@ | |||
1 | import { Model, Sequelize, Transaction } from 'sequelize' | ||
2 | import { AbstractRunQuery, ModelBuilder } from '@server/models/shared' | ||
3 | import { createSafeIn, getCommentSort, parseRowCountResult } from '@server/models/utils' | ||
4 | import { ActorImageType, VideoPrivacy } from '@shared/models' | ||
5 | import { VideoCommentTableAttributes } from './video-comment-table-attributes' | ||
6 | |||
7 | export interface ListVideoCommentsOptions { | ||
8 | selectType: 'api' | 'feed' | 'comment-only' | ||
9 | |||
10 | start?: number | ||
11 | count?: number | ||
12 | sort?: string | ||
13 | |||
14 | videoId?: number | ||
15 | threadId?: number | ||
16 | accountId?: number | ||
17 | videoChannelId?: number | ||
18 | |||
19 | blockerAccountIds?: number[] | ||
20 | |||
21 | isThread?: boolean | ||
22 | notDeleted?: boolean | ||
23 | isLocal?: boolean | ||
24 | onLocalVideo?: boolean | ||
25 | onPublicVideo?: boolean | ||
26 | videoAccountOwnerId?: boolean | ||
27 | |||
28 | search?: string | ||
29 | searchAccount?: string | ||
30 | searchVideo?: string | ||
31 | |||
32 | includeReplyCounters?: boolean | ||
33 | |||
34 | transaction?: Transaction | ||
35 | } | ||
36 | |||
37 | export class VideoCommentListQueryBuilder extends AbstractRunQuery { | ||
38 | private readonly tableAttributes = new VideoCommentTableAttributes() | ||
39 | |||
40 | private innerQuery: string | ||
41 | |||
42 | private select = '' | ||
43 | private joins = '' | ||
44 | |||
45 | private innerSelect = '' | ||
46 | private innerJoins = '' | ||
47 | private innerWhere = '' | ||
48 | |||
49 | private readonly built = { | ||
50 | cte: false, | ||
51 | accountJoin: false, | ||
52 | videoJoin: false, | ||
53 | videoChannelJoin: false, | ||
54 | avatarJoin: false | ||
55 | } | ||
56 | |||
57 | constructor ( | ||
58 | protected readonly sequelize: Sequelize, | ||
59 | private readonly options: ListVideoCommentsOptions | ||
60 | ) { | ||
61 | super(sequelize) | ||
62 | } | ||
63 | |||
64 | async listComments <T extends Model> () { | ||
65 | this.buildListQuery() | ||
66 | |||
67 | const results = await this.runQuery({ nest: true, transaction: this.options.transaction }) | ||
68 | const modelBuilder = new ModelBuilder<T>(this.sequelize) | ||
69 | |||
70 | return modelBuilder.createModels(results, 'VideoComment') | ||
71 | } | ||
72 | |||
73 | async countComments () { | ||
74 | this.buildCountQuery() | ||
75 | |||
76 | const result = await this.runQuery({ transaction: this.options.transaction }) | ||
77 | |||
78 | return parseRowCountResult(result) | ||
79 | } | ||
80 | |||
81 | // --------------------------------------------------------------------------- | ||
82 | |||
83 | private buildListQuery () { | ||
84 | this.buildInnerListQuery() | ||
85 | this.buildListSelect() | ||
86 | |||
87 | this.query = `${this.select} ` + | ||
88 | `FROM (${this.innerQuery}) AS "VideoCommentModel" ` + | ||
89 | `${this.joins} ` + | ||
90 | `${this.getOrder()} ` + | ||
91 | `${this.getLimit()}` | ||
92 | } | ||
93 | |||
94 | private buildInnerListQuery () { | ||
95 | this.buildWhere() | ||
96 | this.buildInnerListSelect() | ||
97 | |||
98 | this.innerQuery = `${this.innerSelect} ` + | ||
99 | `FROM "videoComment" AS "VideoCommentModel" ` + | ||
100 | `${this.innerJoins} ` + | ||
101 | `${this.innerWhere} ` + | ||
102 | `${this.getOrder()} ` + | ||
103 | `${this.getInnerLimit()}` | ||
104 | } | ||
105 | |||
106 | // --------------------------------------------------------------------------- | ||
107 | |||
108 | private buildCountQuery () { | ||
109 | this.buildWhere() | ||
110 | |||
111 | this.query = `SELECT COUNT(*) AS "total" ` + | ||
112 | `FROM "videoComment" AS "VideoCommentModel" ` + | ||
113 | `${this.innerJoins} ` + | ||
114 | `${this.innerWhere}` | ||
115 | } | ||
116 | |||
117 | // --------------------------------------------------------------------------- | ||
118 | |||
119 | private buildWhere () { | ||
120 | let where: string[] = [] | ||
121 | |||
122 | if (this.options.videoId) { | ||
123 | this.replacements.videoId = this.options.videoId | ||
124 | |||
125 | where.push('"VideoCommentModel"."videoId" = :videoId') | ||
126 | } | ||
127 | |||
128 | if (this.options.threadId) { | ||
129 | this.replacements.threadId = this.options.threadId | ||
130 | |||
131 | where.push('("VideoCommentModel"."id" = :threadId OR "VideoCommentModel"."originCommentId" = :threadId)') | ||
132 | } | ||
133 | |||
134 | if (this.options.accountId) { | ||
135 | this.replacements.accountId = this.options.accountId | ||
136 | |||
137 | where.push('"VideoCommentModel"."accountId" = :accountId') | ||
138 | } | ||
139 | |||
140 | if (this.options.videoChannelId) { | ||
141 | this.buildVideoChannelJoin() | ||
142 | |||
143 | this.replacements.videoChannelId = this.options.videoChannelId | ||
144 | |||
145 | where.push('"Account->VideoChannel"."id" = :videoChannelId') | ||
146 | } | ||
147 | |||
148 | if (this.options.blockerAccountIds) { | ||
149 | this.buildVideoChannelJoin() | ||
150 | |||
151 | where = where.concat(this.getBlockWhere('VideoCommentModel', 'Video->VideoChannel')) | ||
152 | } | ||
153 | |||
154 | if (this.options.isThread === true) { | ||
155 | where.push('"VideoCommentModel"."inReplyToCommentId" IS NULL') | ||
156 | } | ||
157 | |||
158 | if (this.options.notDeleted === true) { | ||
159 | where.push('"VideoCommentModel"."deletedAt" IS NULL') | ||
160 | } | ||
161 | |||
162 | if (this.options.isLocal === true) { | ||
163 | this.buildAccountJoin() | ||
164 | |||
165 | where.push('"Account->Actor"."serverId" IS NULL') | ||
166 | } else if (this.options.isLocal === false) { | ||
167 | this.buildAccountJoin() | ||
168 | |||
169 | where.push('"Account->Actor"."serverId" IS NOT NULL') | ||
170 | } | ||
171 | |||
172 | if (this.options.onLocalVideo === true) { | ||
173 | this.buildVideoJoin() | ||
174 | |||
175 | where.push('"Video"."remote" IS FALSE') | ||
176 | } else if (this.options.onLocalVideo === false) { | ||
177 | this.buildVideoJoin() | ||
178 | |||
179 | where.push('"Video"."remote" IS TRUE') | ||
180 | } | ||
181 | |||
182 | if (this.options.onPublicVideo === true) { | ||
183 | this.buildVideoJoin() | ||
184 | |||
185 | where.push(`"Video"."privacy" = ${VideoPrivacy.PUBLIC}`) | ||
186 | } | ||
187 | |||
188 | if (this.options.videoAccountOwnerId) { | ||
189 | this.buildVideoChannelJoin() | ||
190 | |||
191 | this.replacements.videoAccountOwnerId = this.options.videoAccountOwnerId | ||
192 | |||
193 | where.push(`"Video->VideoChannel"."accountId" = :videoAccountOwnerId`) | ||
194 | } | ||
195 | |||
196 | if (this.options.search) { | ||
197 | this.buildVideoJoin() | ||
198 | this.buildAccountJoin() | ||
199 | |||
200 | const escapedLikeSearch = this.sequelize.escape('%' + this.options.search + '%') | ||
201 | |||
202 | where.push( | ||
203 | `(` + | ||
204 | `"VideoCommentModel"."text" ILIKE ${escapedLikeSearch} OR ` + | ||
205 | `"Account->Actor"."preferredUsername" ILIKE ${escapedLikeSearch} OR ` + | ||
206 | `"Account"."name" ILIKE ${escapedLikeSearch} OR ` + | ||
207 | `"Video"."name" ILIKE ${escapedLikeSearch} ` + | ||
208 | `)` | ||
209 | ) | ||
210 | } | ||
211 | |||
212 | if (this.options.searchAccount) { | ||
213 | this.buildAccountJoin() | ||
214 | |||
215 | const escapedLikeSearch = this.sequelize.escape('%' + this.options.searchAccount + '%') | ||
216 | |||
217 | where.push( | ||
218 | `(` + | ||
219 | `"Account->Actor"."preferredUsername" ILIKE ${escapedLikeSearch} OR ` + | ||
220 | `"Account"."name" ILIKE ${escapedLikeSearch} ` + | ||
221 | `)` | ||
222 | ) | ||
223 | } | ||
224 | |||
225 | if (this.options.searchVideo) { | ||
226 | this.buildVideoJoin() | ||
227 | |||
228 | const escapedLikeSearch = this.sequelize.escape('%' + this.options.searchVideo + '%') | ||
229 | |||
230 | where.push(`"Video"."name" ILIKE ${escapedLikeSearch}`) | ||
231 | } | ||
232 | |||
233 | if (where.length !== 0) { | ||
234 | this.innerWhere = `WHERE ${where.join(' AND ')}` | ||
235 | } | ||
236 | } | ||
237 | |||
238 | private buildAccountJoin () { | ||
239 | if (this.built.accountJoin) return | ||
240 | |||
241 | this.innerJoins += ' LEFT JOIN "account" "Account" ON "Account"."id" = "VideoCommentModel"."accountId" ' + | ||
242 | 'LEFT JOIN "actor" "Account->Actor" ON "Account->Actor"."id" = "Account"."actorId" ' + | ||
243 | 'LEFT JOIN "server" "Account->Actor->Server" ON "Account->Actor"."serverId" = "Account->Actor->Server"."id" ' | ||
244 | |||
245 | this.built.accountJoin = true | ||
246 | } | ||
247 | |||
248 | private buildVideoJoin () { | ||
249 | if (this.built.videoJoin) return | ||
250 | |||
251 | this.innerJoins += ' LEFT JOIN "video" "Video" ON "Video"."id" = "VideoCommentModel"."videoId" ' | ||
252 | |||
253 | this.built.videoJoin = true | ||
254 | } | ||
255 | |||
256 | private buildVideoChannelJoin () { | ||
257 | if (this.built.videoChannelJoin) return | ||
258 | |||
259 | this.buildVideoJoin() | ||
260 | |||
261 | this.innerJoins += ' LEFT JOIN "videoChannel" "Video->VideoChannel" ON "Video"."channelId" = "Video->VideoChannel"."id" ' | ||
262 | |||
263 | this.built.videoChannelJoin = true | ||
264 | } | ||
265 | |||
266 | private buildAvatarsJoin () { | ||
267 | if (this.options.selectType !== 'api' && this.options.selectType !== 'feed') return '' | ||
268 | if (this.built.avatarJoin) return | ||
269 | |||
270 | this.joins += `LEFT JOIN "actorImage" "Account->Actor->Avatars" ` + | ||
271 | `ON "VideoCommentModel"."Account.Actor.id" = "Account->Actor->Avatars"."actorId" ` + | ||
272 | `AND "Account->Actor->Avatars"."type" = ${ActorImageType.AVATAR}` | ||
273 | |||
274 | this.built.avatarJoin = true | ||
275 | } | ||
276 | |||
277 | // --------------------------------------------------------------------------- | ||
278 | |||
279 | private buildListSelect () { | ||
280 | const toSelect = [ '"VideoCommentModel".*' ] | ||
281 | |||
282 | if (this.options.selectType === 'api' || this.options.selectType === 'feed') { | ||
283 | this.buildAvatarsJoin() | ||
284 | |||
285 | toSelect.push(this.tableAttributes.getAvatarAttributes()) | ||
286 | } | ||
287 | |||
288 | if (this.options.includeReplyCounters === true) { | ||
289 | toSelect.push(this.getTotalRepliesSelect()) | ||
290 | toSelect.push(this.getAuthorTotalRepliesSelect()) | ||
291 | } | ||
292 | |||
293 | this.select = this.buildSelect(toSelect) | ||
294 | } | ||
295 | |||
296 | private buildInnerListSelect () { | ||
297 | let toSelect = [ this.tableAttributes.getVideoCommentAttributes() ] | ||
298 | |||
299 | if (this.options.selectType === 'api' || this.options.selectType === 'feed') { | ||
300 | this.buildAccountJoin() | ||
301 | this.buildVideoJoin() | ||
302 | |||
303 | toSelect = toSelect.concat([ | ||
304 | this.tableAttributes.getVideoAttributes(), | ||
305 | this.tableAttributes.getAccountAttributes(), | ||
306 | this.tableAttributes.getActorAttributes(), | ||
307 | this.tableAttributes.getServerAttributes() | ||
308 | ]) | ||
309 | } | ||
310 | |||
311 | this.innerSelect = this.buildSelect(toSelect) | ||
312 | } | ||
313 | |||
314 | // --------------------------------------------------------------------------- | ||
315 | |||
316 | private getBlockWhere (commentTableName: string, channelTableName: string) { | ||
317 | const where: string[] = [] | ||
318 | |||
319 | const blockerIdsString = createSafeIn( | ||
320 | this.sequelize, | ||
321 | this.options.blockerAccountIds, | ||
322 | [ `"${channelTableName}"."accountId"` ] | ||
323 | ) | ||
324 | |||
325 | where.push( | ||
326 | `NOT EXISTS (` + | ||
327 | `SELECT 1 FROM "accountBlocklist" ` + | ||
328 | `WHERE "targetAccountId" = "${commentTableName}"."accountId" ` + | ||
329 | `AND "accountId" IN (${blockerIdsString})` + | ||
330 | `)` | ||
331 | ) | ||
332 | |||
333 | where.push( | ||
334 | `NOT EXISTS (` + | ||
335 | `SELECT 1 FROM "account" ` + | ||
336 | `INNER JOIN "actor" ON account."actorId" = actor.id ` + | ||
337 | `INNER JOIN "serverBlocklist" ON "actor"."serverId" = "serverBlocklist"."targetServerId" ` + | ||
338 | `WHERE "account"."id" = "${commentTableName}"."accountId" ` + | ||
339 | `AND "serverBlocklist"."accountId" IN (${blockerIdsString})` + | ||
340 | `)` | ||
341 | ) | ||
342 | |||
343 | return where | ||
344 | } | ||
345 | |||
346 | // --------------------------------------------------------------------------- | ||
347 | |||
348 | private getTotalRepliesSelect () { | ||
349 | const blockWhereString = this.getBlockWhere('replies', 'videoChannel').join(' AND ') | ||
350 | |||
351 | return `(` + | ||
352 | `SELECT COUNT("replies"."id") FROM "videoComment" AS "replies" ` + | ||
353 | `LEFT JOIN "video" ON "video"."id" = "replies"."videoId" ` + | ||
354 | `LEFT JOIN "videoChannel" ON "video"."channelId" = "videoChannel"."id" ` + | ||
355 | `WHERE "replies"."originCommentId" = "VideoCommentModel"."id" ` + | ||
356 | `AND "deletedAt" IS NULL ` + | ||
357 | `AND ${blockWhereString} ` + | ||
358 | `) AS "totalReplies"` | ||
359 | } | ||
360 | |||
361 | private getAuthorTotalRepliesSelect () { | ||
362 | return `(` + | ||
363 | `SELECT COUNT("replies"."id") FROM "videoComment" AS "replies" ` + | ||
364 | `INNER JOIN "video" ON "video"."id" = "replies"."videoId" ` + | ||
365 | `INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" ` + | ||
366 | `WHERE "replies"."originCommentId" = "VideoCommentModel"."id" AND "replies"."accountId" = "videoChannel"."accountId"` + | ||
367 | `) AS "totalRepliesFromVideoAuthor"` | ||
368 | } | ||
369 | |||
370 | private getOrder () { | ||
371 | if (!this.options.sort) return '' | ||
372 | |||
373 | const orders = getCommentSort(this.options.sort) | ||
374 | |||
375 | return 'ORDER BY ' + orders.map(o => `"${o[0]}" ${o[1]}`).join(', ') | ||
376 | } | ||
377 | |||
378 | private getLimit () { | ||
379 | if (!this.options.count) return '' | ||
380 | |||
381 | this.replacements.limit = this.options.count | ||
382 | |||
383 | return `LIMIT :limit ` | ||
384 | } | ||
385 | |||
386 | private getInnerLimit () { | ||
387 | if (!this.options.count) return '' | ||
388 | |||
389 | this.replacements.limit = this.options.count | ||
390 | this.replacements.offset = this.options.start || 0 | ||
391 | |||
392 | return `LIMIT :limit OFFSET :offset ` | ||
393 | } | ||
394 | } | ||
diff --git a/server/models/video/sql/comment/video-comment-table-attributes.ts b/server/models/video/sql/comment/video-comment-table-attributes.ts new file mode 100644 index 000000000..cae3c1683 --- /dev/null +++ b/server/models/video/sql/comment/video-comment-table-attributes.ts | |||
@@ -0,0 +1,78 @@ | |||
1 | export class VideoCommentTableAttributes { | ||
2 | |||
3 | getVideoCommentAttributes () { | ||
4 | return [ | ||
5 | '"VideoCommentModel"."id"', | ||
6 | '"VideoCommentModel"."url"', | ||
7 | '"VideoCommentModel"."deletedAt"', | ||
8 | '"VideoCommentModel"."updatedAt"', | ||
9 | '"VideoCommentModel"."createdAt"', | ||
10 | '"VideoCommentModel"."text"', | ||
11 | '"VideoCommentModel"."originCommentId"', | ||
12 | '"VideoCommentModel"."inReplyToCommentId"', | ||
13 | '"VideoCommentModel"."videoId"', | ||
14 | '"VideoCommentModel"."accountId"' | ||
15 | ].join(', ') | ||
16 | } | ||
17 | |||
18 | getAccountAttributes () { | ||
19 | return [ | ||
20 | `"Account"."id" AS "Account.id"`, | ||
21 | `"Account"."name" AS "Account.name"`, | ||
22 | `"Account"."description" AS "Account.description"`, | ||
23 | `"Account"."createdAt" AS "Account.createdAt"`, | ||
24 | `"Account"."updatedAt" AS "Account.updatedAt"`, | ||
25 | `"Account"."actorId" AS "Account.actorId"`, | ||
26 | `"Account"."userId" AS "Account.userId"`, | ||
27 | `"Account"."applicationId" AS "Account.applicationId"` | ||
28 | ].join(', ') | ||
29 | } | ||
30 | |||
31 | getVideoAttributes () { | ||
32 | return [ | ||
33 | `"Video"."id" AS "Video.id"`, | ||
34 | `"Video"."uuid" AS "Video.uuid"`, | ||
35 | `"Video"."name" AS "Video.name"` | ||
36 | ].join(', ') | ||
37 | } | ||
38 | |||
39 | getActorAttributes () { | ||
40 | return [ | ||
41 | `"Account->Actor"."id" AS "Account.Actor.id"`, | ||
42 | `"Account->Actor"."type" AS "Account.Actor.type"`, | ||
43 | `"Account->Actor"."preferredUsername" AS "Account.Actor.preferredUsername"`, | ||
44 | `"Account->Actor"."url" AS "Account.Actor.url"`, | ||
45 | `"Account->Actor"."followersCount" AS "Account.Actor.followersCount"`, | ||
46 | `"Account->Actor"."followingCount" AS "Account.Actor.followingCount"`, | ||
47 | `"Account->Actor"."remoteCreatedAt" AS "Account.Actor.remoteCreatedAt"`, | ||
48 | `"Account->Actor"."serverId" AS "Account.Actor.serverId"`, | ||
49 | `"Account->Actor"."createdAt" AS "Account.Actor.createdAt"`, | ||
50 | `"Account->Actor"."updatedAt" AS "Account.Actor.updatedAt"` | ||
51 | ].join(', ') | ||
52 | } | ||
53 | |||
54 | getServerAttributes () { | ||
55 | return [ | ||
56 | `"Account->Actor->Server"."id" AS "Account.Actor.Server.id"`, | ||
57 | `"Account->Actor->Server"."host" AS "Account.Actor.Server.host"`, | ||
58 | `"Account->Actor->Server"."redundancyAllowed" AS "Account.Actor.Server.redundancyAllowed"`, | ||
59 | `"Account->Actor->Server"."createdAt" AS "Account.Actor.Server.createdAt"`, | ||
60 | `"Account->Actor->Server"."updatedAt" AS "Account.Actor.Server.updatedAt"` | ||
61 | ].join(', ') | ||
62 | } | ||
63 | |||
64 | getAvatarAttributes () { | ||
65 | return [ | ||
66 | `"Account->Actor->Avatars"."id" AS "Account.Actor.Avatars.id"`, | ||
67 | `"Account->Actor->Avatars"."filename" AS "Account.Actor.Avatars.filename"`, | ||
68 | `"Account->Actor->Avatars"."height" AS "Account.Actor.Avatars.height"`, | ||
69 | `"Account->Actor->Avatars"."width" AS "Account.Actor.Avatars.width"`, | ||
70 | `"Account->Actor->Avatars"."fileUrl" AS "Account.Actor.Avatars.fileUrl"`, | ||
71 | `"Account->Actor->Avatars"."onDisk" AS "Account.Actor.Avatars.onDisk"`, | ||
72 | `"Account->Actor->Avatars"."type" AS "Account.Actor.Avatars.type"`, | ||
73 | `"Account->Actor->Avatars"."actorId" AS "Account.Actor.Avatars.actorId"`, | ||
74 | `"Account->Actor->Avatars"."createdAt" AS "Account.Actor.Avatars.createdAt"`, | ||
75 | `"Account->Actor->Avatars"."updatedAt" AS "Account.Actor.Avatars.updatedAt"` | ||
76 | ].join(', ') | ||
77 | } | ||
78 | } | ||