aboutsummaryrefslogtreecommitdiffhomepage
path: root/server/models/video/sql
diff options
context:
space:
mode:
authorChocobozzz <me@florianbigard.com>2023-01-05 15:31:51 +0100
committerChocobozzz <me@florianbigard.com>2023-01-09 09:29:02 +0100
commitcde3d90ded5debb24281a444eabb720b721e5600 (patch)
tree9ad93c2228b980863d06fea45e3c0b04003ede2a /server/models/video/sql
parent458685e0d039a0ad3fa4f26d99746f7d6d0b40e9 (diff)
downloadPeerTube-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.ts394
-rw-r--r--server/models/video/sql/comment/video-comment-table-attributes.ts78
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 @@
1import { Model, Sequelize, Transaction } from 'sequelize'
2import { AbstractRunQuery, ModelBuilder } from '@server/models/shared'
3import { createSafeIn, getCommentSort, parseRowCountResult } from '@server/models/utils'
4import { ActorImageType, VideoPrivacy } from '@shared/models'
5import { VideoCommentTableAttributes } from './video-comment-table-attributes'
6
7export 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
37export 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 @@
1export 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}