diff options
Diffstat (limited to 'server/models/video/sql')
4 files changed, 433 insertions, 4 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..3960f6b13 --- /dev/null +++ b/server/models/video/sql/comment/video-comment-list-query-builder.ts | |||
@@ -0,0 +1,385 @@ | |||
1 | import { Model, Sequelize, Transaction } from 'sequelize' | ||
2 | import { AbstractRunQuery, ModelBuilder } from '@server/models/shared' | ||
3 | import { ActorImageType, VideoPrivacy } from '@shared/models' | ||
4 | import { createSafeIn, getCommentSort, parseRowCountResult } from '../../../shared' | ||
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 | } | ||
92 | |||
93 | private buildInnerListQuery () { | ||
94 | this.buildWhere() | ||
95 | this.buildInnerListSelect() | ||
96 | |||
97 | this.innerQuery = `${this.innerSelect} ` + | ||
98 | `FROM "videoComment" AS "VideoCommentModel" ` + | ||
99 | `${this.innerJoins} ` + | ||
100 | `${this.innerWhere} ` + | ||
101 | `${this.getOrder()} ` + | ||
102 | `${this.getInnerLimit()}` | ||
103 | } | ||
104 | |||
105 | // --------------------------------------------------------------------------- | ||
106 | |||
107 | private buildCountQuery () { | ||
108 | this.buildWhere() | ||
109 | |||
110 | this.query = `SELECT COUNT(*) AS "total" ` + | ||
111 | `FROM "videoComment" AS "VideoCommentModel" ` + | ||
112 | `${this.innerJoins} ` + | ||
113 | `${this.innerWhere}` | ||
114 | } | ||
115 | |||
116 | // --------------------------------------------------------------------------- | ||
117 | |||
118 | private buildWhere () { | ||
119 | let where: string[] = [] | ||
120 | |||
121 | if (this.options.videoId) { | ||
122 | this.replacements.videoId = this.options.videoId | ||
123 | |||
124 | where.push('"VideoCommentModel"."videoId" = :videoId') | ||
125 | } | ||
126 | |||
127 | if (this.options.threadId) { | ||
128 | this.replacements.threadId = this.options.threadId | ||
129 | |||
130 | where.push('("VideoCommentModel"."id" = :threadId OR "VideoCommentModel"."originCommentId" = :threadId)') | ||
131 | } | ||
132 | |||
133 | if (this.options.accountId) { | ||
134 | this.replacements.accountId = this.options.accountId | ||
135 | |||
136 | where.push('"VideoCommentModel"."accountId" = :accountId') | ||
137 | } | ||
138 | |||
139 | if (this.options.videoChannelId) { | ||
140 | this.buildVideoChannelJoin() | ||
141 | |||
142 | this.replacements.videoChannelId = this.options.videoChannelId | ||
143 | |||
144 | where.push('"Account->VideoChannel"."id" = :videoChannelId') | ||
145 | } | ||
146 | |||
147 | if (this.options.blockerAccountIds) { | ||
148 | this.buildVideoChannelJoin() | ||
149 | |||
150 | where = where.concat(this.getBlockWhere('VideoCommentModel', 'Video->VideoChannel')) | ||
151 | } | ||
152 | |||
153 | if (this.options.isThread === true) { | ||
154 | where.push('"VideoCommentModel"."inReplyToCommentId" IS NULL') | ||
155 | } | ||
156 | |||
157 | if (this.options.notDeleted === true) { | ||
158 | where.push('"VideoCommentModel"."deletedAt" IS NULL') | ||
159 | } | ||
160 | |||
161 | if (this.options.isLocal === true) { | ||
162 | this.buildAccountJoin() | ||
163 | |||
164 | where.push('"Account->Actor"."serverId" IS NULL') | ||
165 | } else if (this.options.isLocal === false) { | ||
166 | this.buildAccountJoin() | ||
167 | |||
168 | where.push('"Account->Actor"."serverId" IS NOT NULL') | ||
169 | } | ||
170 | |||
171 | if (this.options.onLocalVideo === true) { | ||
172 | this.buildVideoJoin() | ||
173 | |||
174 | where.push('"Video"."remote" IS FALSE') | ||
175 | } else if (this.options.onLocalVideo === false) { | ||
176 | this.buildVideoJoin() | ||
177 | |||
178 | where.push('"Video"."remote" IS TRUE') | ||
179 | } | ||
180 | |||
181 | if (this.options.onPublicVideo === true) { | ||
182 | this.buildVideoJoin() | ||
183 | |||
184 | where.push(`"Video"."privacy" = ${VideoPrivacy.PUBLIC}`) | ||
185 | } | ||
186 | |||
187 | if (this.options.videoAccountOwnerId) { | ||
188 | this.buildVideoChannelJoin() | ||
189 | |||
190 | this.replacements.videoAccountOwnerId = this.options.videoAccountOwnerId | ||
191 | |||
192 | where.push(`"Video->VideoChannel"."accountId" = :videoAccountOwnerId`) | ||
193 | } | ||
194 | |||
195 | if (this.options.search) { | ||
196 | this.buildVideoJoin() | ||
197 | this.buildAccountJoin() | ||
198 | |||
199 | const escapedLikeSearch = this.sequelize.escape('%' + this.options.search + '%') | ||
200 | |||
201 | where.push( | ||
202 | `(` + | ||
203 | `"VideoCommentModel"."text" ILIKE ${escapedLikeSearch} OR ` + | ||
204 | `"Account->Actor"."preferredUsername" ILIKE ${escapedLikeSearch} OR ` + | ||
205 | `"Account"."name" ILIKE ${escapedLikeSearch} OR ` + | ||
206 | `"Video"."name" ILIKE ${escapedLikeSearch} ` + | ||
207 | `)` | ||
208 | ) | ||
209 | } | ||
210 | |||
211 | if (this.options.searchAccount) { | ||
212 | this.buildAccountJoin() | ||
213 | |||
214 | const escapedLikeSearch = this.sequelize.escape('%' + this.options.searchAccount + '%') | ||
215 | |||
216 | where.push( | ||
217 | `(` + | ||
218 | `"Account->Actor"."preferredUsername" ILIKE ${escapedLikeSearch} OR ` + | ||
219 | `"Account"."name" ILIKE ${escapedLikeSearch} ` + | ||
220 | `)` | ||
221 | ) | ||
222 | } | ||
223 | |||
224 | if (this.options.searchVideo) { | ||
225 | this.buildVideoJoin() | ||
226 | |||
227 | const escapedLikeSearch = this.sequelize.escape('%' + this.options.searchVideo + '%') | ||
228 | |||
229 | where.push(`"Video"."name" ILIKE ${escapedLikeSearch}`) | ||
230 | } | ||
231 | |||
232 | if (where.length !== 0) { | ||
233 | this.innerWhere = `WHERE ${where.join(' AND ')}` | ||
234 | } | ||
235 | } | ||
236 | |||
237 | private buildAccountJoin () { | ||
238 | if (this.built.accountJoin) return | ||
239 | |||
240 | this.innerJoins += ' LEFT JOIN "account" "Account" ON "Account"."id" = "VideoCommentModel"."accountId" ' + | ||
241 | 'LEFT JOIN "actor" "Account->Actor" ON "Account->Actor"."id" = "Account"."actorId" ' + | ||
242 | 'LEFT JOIN "server" "Account->Actor->Server" ON "Account->Actor"."serverId" = "Account->Actor->Server"."id" ' | ||
243 | |||
244 | this.built.accountJoin = true | ||
245 | } | ||
246 | |||
247 | private buildVideoJoin () { | ||
248 | if (this.built.videoJoin) return | ||
249 | |||
250 | this.innerJoins += ' LEFT JOIN "video" "Video" ON "Video"."id" = "VideoCommentModel"."videoId" ' | ||
251 | |||
252 | this.built.videoJoin = true | ||
253 | } | ||
254 | |||
255 | private buildVideoChannelJoin () { | ||
256 | if (this.built.videoChannelJoin) return | ||
257 | |||
258 | this.buildVideoJoin() | ||
259 | |||
260 | this.innerJoins += ' LEFT JOIN "videoChannel" "Video->VideoChannel" ON "Video"."channelId" = "Video->VideoChannel"."id" ' | ||
261 | |||
262 | this.built.videoChannelJoin = true | ||
263 | } | ||
264 | |||
265 | private buildAvatarsJoin () { | ||
266 | if (this.options.selectType !== 'api' && this.options.selectType !== 'feed') return '' | ||
267 | if (this.built.avatarJoin) return | ||
268 | |||
269 | this.joins += `LEFT JOIN "actorImage" "Account->Actor->Avatars" ` + | ||
270 | `ON "VideoCommentModel"."Account.Actor.id" = "Account->Actor->Avatars"."actorId" ` + | ||
271 | `AND "Account->Actor->Avatars"."type" = ${ActorImageType.AVATAR}` | ||
272 | |||
273 | this.built.avatarJoin = true | ||
274 | } | ||
275 | |||
276 | // --------------------------------------------------------------------------- | ||
277 | |||
278 | private buildListSelect () { | ||
279 | const toSelect = [ '"VideoCommentModel".*' ] | ||
280 | |||
281 | if (this.options.selectType === 'api' || this.options.selectType === 'feed') { | ||
282 | this.buildAvatarsJoin() | ||
283 | |||
284 | toSelect.push(this.tableAttributes.getAvatarAttributes()) | ||
285 | } | ||
286 | |||
287 | if (this.options.includeReplyCounters === true) { | ||
288 | toSelect.push(this.getTotalRepliesSelect()) | ||
289 | toSelect.push(this.getAuthorTotalRepliesSelect()) | ||
290 | } | ||
291 | |||
292 | this.select = this.buildSelect(toSelect) | ||
293 | } | ||
294 | |||
295 | private buildInnerListSelect () { | ||
296 | let toSelect = [ this.tableAttributes.getVideoCommentAttributes() ] | ||
297 | |||
298 | if (this.options.selectType === 'api' || this.options.selectType === 'feed') { | ||
299 | this.buildAccountJoin() | ||
300 | this.buildVideoJoin() | ||
301 | |||
302 | toSelect = toSelect.concat([ | ||
303 | this.tableAttributes.getVideoAttributes(), | ||
304 | this.tableAttributes.getAccountAttributes(), | ||
305 | this.tableAttributes.getActorAttributes(), | ||
306 | this.tableAttributes.getServerAttributes() | ||
307 | ]) | ||
308 | } | ||
309 | |||
310 | this.innerSelect = this.buildSelect(toSelect) | ||
311 | } | ||
312 | |||
313 | // --------------------------------------------------------------------------- | ||
314 | |||
315 | private getBlockWhere (commentTableName: string, channelTableName: string) { | ||
316 | const where: string[] = [] | ||
317 | |||
318 | const blockerIdsString = createSafeIn( | ||
319 | this.sequelize, | ||
320 | this.options.blockerAccountIds, | ||
321 | [ `"${channelTableName}"."accountId"` ] | ||
322 | ) | ||
323 | |||
324 | where.push( | ||
325 | `NOT EXISTS (` + | ||
326 | `SELECT 1 FROM "accountBlocklist" ` + | ||
327 | `WHERE "targetAccountId" = "${commentTableName}"."accountId" ` + | ||
328 | `AND "accountId" IN (${blockerIdsString})` + | ||
329 | `)` | ||
330 | ) | ||
331 | |||
332 | where.push( | ||
333 | `NOT EXISTS (` + | ||
334 | `SELECT 1 FROM "account" ` + | ||
335 | `INNER JOIN "actor" ON account."actorId" = actor.id ` + | ||
336 | `INNER JOIN "serverBlocklist" ON "actor"."serverId" = "serverBlocklist"."targetServerId" ` + | ||
337 | `WHERE "account"."id" = "${commentTableName}"."accountId" ` + | ||
338 | `AND "serverBlocklist"."accountId" IN (${blockerIdsString})` + | ||
339 | `)` | ||
340 | ) | ||
341 | |||
342 | return where | ||
343 | } | ||
344 | |||
345 | // --------------------------------------------------------------------------- | ||
346 | |||
347 | private getTotalRepliesSelect () { | ||
348 | const blockWhereString = this.getBlockWhere('replies', 'videoChannel').join(' AND ') | ||
349 | |||
350 | return `(` + | ||
351 | `SELECT COUNT("replies"."id") FROM "videoComment" AS "replies" ` + | ||
352 | `LEFT JOIN "video" ON "video"."id" = "replies"."videoId" ` + | ||
353 | `LEFT JOIN "videoChannel" ON "video"."channelId" = "videoChannel"."id" ` + | ||
354 | `WHERE "replies"."originCommentId" = "VideoCommentModel"."id" ` + | ||
355 | `AND "deletedAt" IS NULL ` + | ||
356 | `AND ${blockWhereString} ` + | ||
357 | `) AS "totalReplies"` | ||
358 | } | ||
359 | |||
360 | private getAuthorTotalRepliesSelect () { | ||
361 | return `(` + | ||
362 | `SELECT COUNT("replies"."id") FROM "videoComment" AS "replies" ` + | ||
363 | `INNER JOIN "video" ON "video"."id" = "replies"."videoId" ` + | ||
364 | `INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" ` + | ||
365 | `WHERE "replies"."originCommentId" = "VideoCommentModel"."id" AND "replies"."accountId" = "videoChannel"."accountId"` + | ||
366 | `) AS "totalRepliesFromVideoAuthor"` | ||
367 | } | ||
368 | |||
369 | private getOrder () { | ||
370 | if (!this.options.sort) return '' | ||
371 | |||
372 | const orders = getCommentSort(this.options.sort) | ||
373 | |||
374 | return 'ORDER BY ' + orders.map(o => `"${o[0]}" ${o[1]}`).join(', ') | ||
375 | } | ||
376 | |||
377 | private getInnerLimit () { | ||
378 | if (!this.options.count) return '' | ||
379 | |||
380 | this.replacements.limit = this.options.count | ||
381 | this.replacements.offset = this.options.start || 0 | ||
382 | |||
383 | return `LIMIT :limit OFFSET :offset ` | ||
384 | } | ||
385 | } | ||
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..87f8750c1 --- /dev/null +++ b/server/models/video/sql/comment/video-comment-table-attributes.ts | |||
@@ -0,0 +1,43 @@ | |||
1 | import { Memoize } from '@server/helpers/memoize' | ||
2 | import { AccountModel } from '@server/models/account/account' | ||
3 | import { ActorModel } from '@server/models/actor/actor' | ||
4 | import { ActorImageModel } from '@server/models/actor/actor-image' | ||
5 | import { ServerModel } from '@server/models/server/server' | ||
6 | import { VideoCommentModel } from '../../video-comment' | ||
7 | |||
8 | export class VideoCommentTableAttributes { | ||
9 | |||
10 | @Memoize() | ||
11 | getVideoCommentAttributes () { | ||
12 | return VideoCommentModel.getSQLAttributes('VideoCommentModel').join(', ') | ||
13 | } | ||
14 | |||
15 | @Memoize() | ||
16 | getAccountAttributes () { | ||
17 | return AccountModel.getSQLAttributes('Account', 'Account.').join(', ') | ||
18 | } | ||
19 | |||
20 | @Memoize() | ||
21 | getVideoAttributes () { | ||
22 | return [ | ||
23 | `"Video"."id" AS "Video.id"`, | ||
24 | `"Video"."uuid" AS "Video.uuid"`, | ||
25 | `"Video"."name" AS "Video.name"` | ||
26 | ].join(', ') | ||
27 | } | ||
28 | |||
29 | @Memoize() | ||
30 | getActorAttributes () { | ||
31 | return ActorModel.getSQLAPIAttributes('Account->Actor', `Account.Actor.`).join(', ') | ||
32 | } | ||
33 | |||
34 | @Memoize() | ||
35 | getServerAttributes () { | ||
36 | return ServerModel.getSQLAttributes('Account->Actor->Server', `Account.Actor.Server.`).join(', ') | ||
37 | } | ||
38 | |||
39 | @Memoize() | ||
40 | getAvatarAttributes () { | ||
41 | return ActorImageModel.getSQLAttributes('Account->Actor->Avatars', 'Account.Actor.Avatars.').join(', ') | ||
42 | } | ||
43 | } | ||
diff --git a/server/models/video/sql/video/shared/abstract-video-query-builder.ts b/server/models/video/sql/video/shared/abstract-video-query-builder.ts index f0ce69501..cbd57ad8c 100644 --- a/server/models/video/sql/video/shared/abstract-video-query-builder.ts +++ b/server/models/video/sql/video/shared/abstract-video-query-builder.ts | |||
@@ -1,9 +1,9 @@ | |||
1 | import { Sequelize } from 'sequelize' | 1 | import { Sequelize } from 'sequelize' |
2 | import validator from 'validator' | 2 | import validator from 'validator' |
3 | import { createSafeIn } from '@server/models/utils' | ||
4 | import { MUserAccountId } from '@server/types/models' | 3 | import { MUserAccountId } from '@server/types/models' |
5 | import { ActorImageType } from '@shared/models' | 4 | import { ActorImageType } from '@shared/models' |
6 | import { AbstractRunQuery } from '../../../../shared/abstract-run-query' | 5 | import { AbstractRunQuery } from '../../../../shared/abstract-run-query' |
6 | import { createSafeIn } from '../../../../shared' | ||
7 | import { VideoTableAttributes } from './video-table-attributes' | 7 | import { VideoTableAttributes } from './video-table-attributes' |
8 | 8 | ||
9 | /** | 9 | /** |
diff --git a/server/models/video/sql/video/videos-id-list-query-builder.ts b/server/models/video/sql/video/videos-id-list-query-builder.ts index 7c864bf27..62f1855c7 100644 --- a/server/models/video/sql/video/videos-id-list-query-builder.ts +++ b/server/models/video/sql/video/videos-id-list-query-builder.ts | |||
@@ -2,11 +2,12 @@ import { Sequelize, Transaction } from 'sequelize' | |||
2 | import validator from 'validator' | 2 | import validator from 'validator' |
3 | import { exists } from '@server/helpers/custom-validators/misc' | 3 | import { exists } from '@server/helpers/custom-validators/misc' |
4 | import { WEBSERVER } from '@server/initializers/constants' | 4 | import { WEBSERVER } from '@server/initializers/constants' |
5 | import { buildDirectionAndField, createSafeIn, parseRowCountResult } from '@server/models/utils' | 5 | import { buildSortDirectionAndField } from '@server/models/shared' |
6 | import { MUserAccountId, MUserId } from '@server/types/models' | 6 | import { MUserAccountId, MUserId } from '@server/types/models' |
7 | import { forceNumber } from '@shared/core-utils' | ||
7 | import { VideoInclude, VideoPrivacy, VideoState } from '@shared/models' | 8 | import { VideoInclude, VideoPrivacy, VideoState } from '@shared/models' |
9 | import { createSafeIn, parseRowCountResult } from '../../../shared' | ||
8 | import { AbstractRunQuery } from '../../../shared/abstract-run-query' | 10 | import { AbstractRunQuery } from '../../../shared/abstract-run-query' |
9 | import { forceNumber } from '@shared/core-utils' | ||
10 | 11 | ||
11 | /** | 12 | /** |
12 | * | 13 | * |
@@ -665,7 +666,7 @@ export class VideosIdListQueryBuilder extends AbstractRunQuery { | |||
665 | } | 666 | } |
666 | 667 | ||
667 | private buildOrder (value: string) { | 668 | private buildOrder (value: string) { |
668 | const { direction, field } = buildDirectionAndField(value) | 669 | const { direction, field } = buildSortDirectionAndField(value) |
669 | if (field.match(/^[a-zA-Z."]+$/) === null) throw new Error('Invalid sort column ' + field) | 670 | if (field.match(/^[a-zA-Z."]+$/) === null) throw new Error('Invalid sort column ' + field) |
670 | 671 | ||
671 | if (field.toLowerCase() === 'random') return 'ORDER BY RANDOM()' | 672 | if (field.toLowerCase() === 'random') return 'ORDER BY RANDOM()' |