aboutsummaryrefslogtreecommitdiffhomepage
path: root/server/models/abuse
diff options
context:
space:
mode:
authorChocobozzz <me@florianbigard.com>2023-07-31 14:34:36 +0200
committerChocobozzz <me@florianbigard.com>2023-08-11 15:02:33 +0200
commit3a4992633ee62d5edfbb484d9c6bcb3cf158489d (patch)
treee4510b39bdac9c318fdb4b47018d08f15368b8f0 /server/models/abuse
parent04d1da5621d25d59bd5fa1543b725c497bf5d9a8 (diff)
downloadPeerTube-3a4992633ee62d5edfbb484d9c6bcb3cf158489d.tar.gz
PeerTube-3a4992633ee62d5edfbb484d9c6bcb3cf158489d.tar.zst
PeerTube-3a4992633ee62d5edfbb484d9c6bcb3cf158489d.zip
Migrate server to ESM
Sorry for the very big commit that may lead to git log issues and merge conflicts, but it's a major step forward: * Server can be faster at startup because imports() are async and we can easily lazy import big modules * Angular doesn't seem to support ES import (with .js extension), so we had to correctly organize peertube into a monorepo: * Use yarn workspace feature * Use typescript reference projects for dependencies * Shared projects have been moved into "packages", each one is now a node module (with a dedicated package.json/tsconfig.json) * server/tools have been moved into apps/ and is now a dedicated app bundled and published on NPM so users don't have to build peertube cli tools manually * server/tests have been moved into packages/ so we don't compile them every time we want to run the server * Use isolatedModule option: * Had to move from const enum to const (https://www.typescriptlang.org/docs/handbook/enums.html#objects-vs-enums) * Had to explictely specify "type" imports when used in decorators * Prefer tsx (that uses esbuild under the hood) instead of ts-node to load typescript files (tests with mocha or scripts): * To reduce test complexity as esbuild doesn't support decorator metadata, we only test server files that do not import server models * We still build tests files into js files for a faster CI * Remove unmaintained peertube CLI import script * Removed some barrels to speed up execution (less imports)
Diffstat (limited to 'server/models/abuse')
-rw-r--r--server/models/abuse/abuse-message.ts114
-rw-r--r--server/models/abuse/abuse.ts624
-rw-r--r--server/models/abuse/sql/abuse-query-builder.ts167
-rw-r--r--server/models/abuse/video-abuse.ts64
-rw-r--r--server/models/abuse/video-comment-abuse.ts48
5 files changed, 0 insertions, 1017 deletions
diff --git a/server/models/abuse/abuse-message.ts b/server/models/abuse/abuse-message.ts
deleted file mode 100644
index 14a5bffa2..000000000
--- a/server/models/abuse/abuse-message.ts
+++ /dev/null
@@ -1,114 +0,0 @@
1import { FindOptions } from 'sequelize'
2import { AllowNull, BelongsTo, Column, CreatedAt, DataType, ForeignKey, Is, Model, Table, UpdatedAt } from 'sequelize-typescript'
3import { isAbuseMessageValid } from '@server/helpers/custom-validators/abuses'
4import { MAbuseMessage, MAbuseMessageFormattable } from '@server/types/models'
5import { AbuseMessage } from '@shared/models'
6import { AttributesOnly } from '@shared/typescript-utils'
7import { AccountModel, ScopeNames as AccountScopeNames } from '../account/account'
8import { getSort, throwIfNotValid } from '../shared'
9import { AbuseModel } from './abuse'
10
11@Table({
12 tableName: 'abuseMessage',
13 indexes: [
14 {
15 fields: [ 'abuseId' ]
16 },
17 {
18 fields: [ 'accountId' ]
19 }
20 ]
21})
22export class AbuseMessageModel extends Model<Partial<AttributesOnly<AbuseMessageModel>>> {
23
24 @AllowNull(false)
25 @Is('AbuseMessage', value => throwIfNotValid(value, isAbuseMessageValid, 'message'))
26 @Column(DataType.TEXT)
27 message: string
28
29 @AllowNull(false)
30 @Column
31 byModerator: boolean
32
33 @CreatedAt
34 createdAt: Date
35
36 @UpdatedAt
37 updatedAt: Date
38
39 @ForeignKey(() => AccountModel)
40 @Column
41 accountId: number
42
43 @BelongsTo(() => AccountModel, {
44 foreignKey: {
45 name: 'accountId',
46 allowNull: true
47 },
48 onDelete: 'set null'
49 })
50 Account: AccountModel
51
52 @ForeignKey(() => AbuseModel)
53 @Column
54 abuseId: number
55
56 @BelongsTo(() => AbuseModel, {
57 foreignKey: {
58 name: 'abuseId',
59 allowNull: false
60 },
61 onDelete: 'cascade'
62 })
63 Abuse: AbuseModel
64
65 static listForApi (abuseId: number) {
66 const getQuery = (forCount: boolean) => {
67 const query: FindOptions = {
68 where: { abuseId },
69 order: getSort('createdAt')
70 }
71
72 if (forCount !== true) {
73 query.include = [
74 {
75 model: AccountModel.scope(AccountScopeNames.SUMMARY),
76 required: false
77 }
78 ]
79 }
80
81 return query
82 }
83
84 return Promise.all([
85 AbuseMessageModel.count(getQuery(true)),
86 AbuseMessageModel.findAll(getQuery(false))
87 ]).then(([ total, data ]) => ({ total, data }))
88 }
89
90 static loadByIdAndAbuseId (messageId: number, abuseId: number): Promise<MAbuseMessage> {
91 return AbuseMessageModel.findOne({
92 where: {
93 id: messageId,
94 abuseId
95 }
96 })
97 }
98
99 toFormattedJSON (this: MAbuseMessageFormattable): AbuseMessage {
100 const account = this.Account
101 ? this.Account.toFormattedSummaryJSON()
102 : null
103
104 return {
105 id: this.id,
106 createdAt: this.createdAt,
107
108 byModerator: this.byModerator,
109 message: this.message,
110
111 account
112 }
113 }
114}
diff --git a/server/models/abuse/abuse.ts b/server/models/abuse/abuse.ts
deleted file mode 100644
index 4ce40bf2f..000000000
--- a/server/models/abuse/abuse.ts
+++ /dev/null
@@ -1,624 +0,0 @@
1import { invert } from 'lodash'
2import { literal, Op, QueryTypes } from 'sequelize'
3import {
4 AllowNull,
5 BelongsTo,
6 Column,
7 CreatedAt,
8 DataType,
9 Default,
10 ForeignKey,
11 HasOne,
12 Is,
13 Model,
14 Scopes,
15 Table,
16 UpdatedAt
17} from 'sequelize-typescript'
18import { isAbuseModerationCommentValid, isAbuseReasonValid, isAbuseStateValid } from '@server/helpers/custom-validators/abuses'
19import { abusePredefinedReasonsMap } from '@shared/core-utils'
20import {
21 AbuseFilter,
22 AbuseObject,
23 AbusePredefinedReasons,
24 AbusePredefinedReasonsString,
25 AbuseState,
26 AbuseVideoIs,
27 AdminAbuse,
28 AdminVideoAbuse,
29 AdminVideoCommentAbuse,
30 UserAbuse,
31 UserVideoAbuse
32} from '@shared/models'
33import { AttributesOnly } from '@shared/typescript-utils'
34import { ABUSE_STATES, CONSTRAINTS_FIELDS } from '../../initializers/constants'
35import { MAbuseAdminFormattable, MAbuseAP, MAbuseFull, MAbuseReporter, MAbuseUserFormattable, MUserAccountId } from '../../types/models'
36import { AccountModel, ScopeNames as AccountScopeNames, SummaryOptions as AccountSummaryOptions } from '../account/account'
37import { getSort, throwIfNotValid } from '../shared'
38import { ThumbnailModel } from '../video/thumbnail'
39import { ScopeNames as VideoScopeNames, VideoModel } from '../video/video'
40import { VideoBlacklistModel } from '../video/video-blacklist'
41import { ScopeNames as VideoChannelScopeNames, SummaryOptions as ChannelSummaryOptions, VideoChannelModel } from '../video/video-channel'
42import { ScopeNames as CommentScopeNames, VideoCommentModel } from '../video/video-comment'
43import { buildAbuseListQuery, BuildAbusesQueryOptions } from './sql/abuse-query-builder'
44import { VideoAbuseModel } from './video-abuse'
45import { VideoCommentAbuseModel } from './video-comment-abuse'
46
47export enum ScopeNames {
48 FOR_API = 'FOR_API'
49}
50
51@Scopes(() => ({
52 [ScopeNames.FOR_API]: () => {
53 return {
54 attributes: {
55 include: [
56 [
57 literal(
58 '(' +
59 'SELECT count(*) ' +
60 'FROM "abuseMessage" ' +
61 'WHERE "abuseId" = "AbuseModel"."id"' +
62 ')'
63 ),
64 'countMessages'
65 ],
66 [
67 // we don't care about this count for deleted videos, so there are not included
68 literal(
69 '(' +
70 'SELECT count(*) ' +
71 'FROM "videoAbuse" ' +
72 'WHERE "videoId" = "VideoAbuse"."videoId" AND "videoId" IS NOT NULL' +
73 ')'
74 ),
75 'countReportsForVideo'
76 ],
77 [
78 // we don't care about this count for deleted videos, so there are not included
79 literal(
80 '(' +
81 'SELECT t.nth ' +
82 'FROM ( ' +
83 'SELECT id, ' +
84 'row_number() OVER (PARTITION BY "videoId" ORDER BY "createdAt") AS nth ' +
85 'FROM "videoAbuse" ' +
86 ') t ' +
87 'WHERE t.id = "VideoAbuse".id AND t.id IS NOT NULL' +
88 ')'
89 ),
90 'nthReportForVideo'
91 ],
92 [
93 literal(
94 '(' +
95 'SELECT count("abuse"."id") ' +
96 'FROM "abuse" ' +
97 'WHERE "abuse"."reporterAccountId" = "AbuseModel"."reporterAccountId"' +
98 ')'
99 ),
100 'countReportsForReporter'
101 ],
102 [
103 literal(
104 '(' +
105 'SELECT count("abuse"."id") ' +
106 'FROM "abuse" ' +
107 'WHERE "abuse"."flaggedAccountId" = "AbuseModel"."flaggedAccountId"' +
108 ')'
109 ),
110 'countReportsForReportee'
111 ]
112 ]
113 },
114 include: [
115 {
116 model: AccountModel.scope({
117 method: [
118 AccountScopeNames.SUMMARY,
119 { actorRequired: false } as AccountSummaryOptions
120 ]
121 }),
122 as: 'ReporterAccount'
123 },
124 {
125 model: AccountModel.scope({
126 method: [
127 AccountScopeNames.SUMMARY,
128 { actorRequired: false } as AccountSummaryOptions
129 ]
130 }),
131 as: 'FlaggedAccount'
132 },
133 {
134 model: VideoCommentAbuseModel.unscoped(),
135 include: [
136 {
137 model: VideoCommentModel.unscoped(),
138 include: [
139 {
140 model: VideoModel.unscoped(),
141 attributes: [ 'name', 'id', 'uuid' ]
142 }
143 ]
144 }
145 ]
146 },
147 {
148 model: VideoAbuseModel.unscoped(),
149 include: [
150 {
151 attributes: [ 'id', 'uuid', 'name', 'nsfw' ],
152 model: VideoModel.unscoped(),
153 include: [
154 {
155 attributes: [ 'filename', 'fileUrl', 'type' ],
156 model: ThumbnailModel
157 },
158 {
159 model: VideoChannelModel.scope({
160 method: [
161 VideoChannelScopeNames.SUMMARY,
162 { withAccount: false, actorRequired: false } as ChannelSummaryOptions
163 ]
164 }),
165 required: false
166 },
167 {
168 attributes: [ 'id', 'reason', 'unfederated' ],
169 required: false,
170 model: VideoBlacklistModel
171 }
172 ]
173 }
174 ]
175 }
176 ]
177 }
178 }
179}))
180@Table({
181 tableName: 'abuse',
182 indexes: [
183 {
184 fields: [ 'reporterAccountId' ]
185 },
186 {
187 fields: [ 'flaggedAccountId' ]
188 }
189 ]
190})
191export class AbuseModel extends Model<Partial<AttributesOnly<AbuseModel>>> {
192
193 @AllowNull(false)
194 @Default(null)
195 @Is('AbuseReason', value => throwIfNotValid(value, isAbuseReasonValid, 'reason'))
196 @Column(DataType.STRING(CONSTRAINTS_FIELDS.ABUSES.REASON.max))
197 reason: string
198
199 @AllowNull(false)
200 @Default(null)
201 @Is('AbuseState', value => throwIfNotValid(value, isAbuseStateValid, 'state'))
202 @Column
203 state: AbuseState
204
205 @AllowNull(true)
206 @Default(null)
207 @Is('AbuseModerationComment', value => throwIfNotValid(value, isAbuseModerationCommentValid, 'moderationComment', true))
208 @Column(DataType.STRING(CONSTRAINTS_FIELDS.ABUSES.MODERATION_COMMENT.max))
209 moderationComment: string
210
211 @AllowNull(true)
212 @Default(null)
213 @Column(DataType.ARRAY(DataType.INTEGER))
214 predefinedReasons: AbusePredefinedReasons[]
215
216 @CreatedAt
217 createdAt: Date
218
219 @UpdatedAt
220 updatedAt: Date
221
222 @ForeignKey(() => AccountModel)
223 @Column
224 reporterAccountId: number
225
226 @BelongsTo(() => AccountModel, {
227 foreignKey: {
228 name: 'reporterAccountId',
229 allowNull: true
230 },
231 as: 'ReporterAccount',
232 onDelete: 'set null'
233 })
234 ReporterAccount: AccountModel
235
236 @ForeignKey(() => AccountModel)
237 @Column
238 flaggedAccountId: number
239
240 @BelongsTo(() => AccountModel, {
241 foreignKey: {
242 name: 'flaggedAccountId',
243 allowNull: true
244 },
245 as: 'FlaggedAccount',
246 onDelete: 'set null'
247 })
248 FlaggedAccount: AccountModel
249
250 @HasOne(() => VideoCommentAbuseModel, {
251 foreignKey: {
252 name: 'abuseId',
253 allowNull: false
254 },
255 onDelete: 'cascade'
256 })
257 VideoCommentAbuse: VideoCommentAbuseModel
258
259 @HasOne(() => VideoAbuseModel, {
260 foreignKey: {
261 name: 'abuseId',
262 allowNull: false
263 },
264 onDelete: 'cascade'
265 })
266 VideoAbuse: VideoAbuseModel
267
268 static loadByIdWithReporter (id: number): Promise<MAbuseReporter> {
269 const query = {
270 where: {
271 id
272 },
273 include: [
274 {
275 model: AccountModel,
276 as: 'ReporterAccount'
277 }
278 ]
279 }
280
281 return AbuseModel.findOne(query)
282 }
283
284 static loadFull (id: number): Promise<MAbuseFull> {
285 const query = {
286 where: {
287 id
288 },
289 include: [
290 {
291 model: AccountModel.scope(AccountScopeNames.SUMMARY),
292 required: false,
293 as: 'ReporterAccount'
294 },
295 {
296 model: AccountModel.scope(AccountScopeNames.SUMMARY),
297 as: 'FlaggedAccount'
298 },
299 {
300 model: VideoAbuseModel,
301 required: false,
302 include: [
303 {
304 model: VideoModel.scope([ VideoScopeNames.WITH_ACCOUNT_DETAILS ])
305 }
306 ]
307 },
308 {
309 model: VideoCommentAbuseModel,
310 required: false,
311 include: [
312 {
313 model: VideoCommentModel.scope([
314 CommentScopeNames.WITH_ACCOUNT
315 ]),
316 include: [
317 {
318 model: VideoModel
319 }
320 ]
321 }
322 ]
323 }
324 ]
325 }
326
327 return AbuseModel.findOne(query)
328 }
329
330 static async listForAdminApi (parameters: {
331 start: number
332 count: number
333 sort: string
334
335 filter?: AbuseFilter
336
337 serverAccountId: number
338 user?: MUserAccountId
339
340 id?: number
341 predefinedReason?: AbusePredefinedReasonsString
342 state?: AbuseState
343 videoIs?: AbuseVideoIs
344
345 search?: string
346 searchReporter?: string
347 searchReportee?: string
348 searchVideo?: string
349 searchVideoChannel?: string
350 }) {
351 const {
352 start,
353 count,
354 sort,
355 search,
356 user,
357 serverAccountId,
358 state,
359 videoIs,
360 predefinedReason,
361 searchReportee,
362 searchVideo,
363 filter,
364 searchVideoChannel,
365 searchReporter,
366 id
367 } = parameters
368
369 const userAccountId = user ? user.Account.id : undefined
370 const predefinedReasonId = predefinedReason ? abusePredefinedReasonsMap[predefinedReason] : undefined
371
372 const queryOptions: BuildAbusesQueryOptions = {
373 start,
374 count,
375 sort,
376 id,
377 filter,
378 predefinedReasonId,
379 search,
380 state,
381 videoIs,
382 searchReportee,
383 searchVideo,
384 searchVideoChannel,
385 searchReporter,
386 serverAccountId,
387 userAccountId
388 }
389
390 const [ total, data ] = await Promise.all([
391 AbuseModel.internalCountForApi(queryOptions),
392 AbuseModel.internalListForApi(queryOptions)
393 ])
394
395 return { total, data }
396 }
397
398 static async listForUserApi (parameters: {
399 user: MUserAccountId
400
401 start: number
402 count: number
403 sort: string
404
405 id?: number
406 search?: string
407 state?: AbuseState
408 }) {
409 const {
410 start,
411 count,
412 sort,
413 search,
414 user,
415 state,
416 id
417 } = parameters
418
419 const queryOptions: BuildAbusesQueryOptions = {
420 start,
421 count,
422 sort,
423 id,
424 search,
425 state,
426 reporterAccountId: user.Account.id
427 }
428
429 const [ total, data ] = await Promise.all([
430 AbuseModel.internalCountForApi(queryOptions),
431 AbuseModel.internalListForApi(queryOptions)
432 ])
433
434 return { total, data }
435 }
436
437 buildBaseVideoCommentAbuse (this: MAbuseUserFormattable) {
438 // Associated video comment could have been destroyed if the video has been deleted
439 if (!this.VideoCommentAbuse?.VideoComment) return null
440
441 const entity = this.VideoCommentAbuse.VideoComment
442
443 return {
444 id: entity.id,
445 threadId: entity.getThreadId(),
446
447 text: entity.text ?? '',
448
449 deleted: entity.isDeleted(),
450
451 video: {
452 id: entity.Video.id,
453 name: entity.Video.name,
454 uuid: entity.Video.uuid
455 }
456 }
457 }
458
459 buildBaseVideoAbuse (this: MAbuseUserFormattable): UserVideoAbuse {
460 if (!this.VideoAbuse) return null
461
462 const abuseModel = this.VideoAbuse
463 const entity = abuseModel.Video || abuseModel.deletedVideo
464
465 return {
466 id: entity.id,
467 uuid: entity.uuid,
468 name: entity.name,
469 nsfw: entity.nsfw,
470
471 startAt: abuseModel.startAt,
472 endAt: abuseModel.endAt,
473
474 deleted: !abuseModel.Video,
475 blacklisted: abuseModel.Video?.isBlacklisted() || false,
476 thumbnailPath: abuseModel.Video?.getMiniatureStaticPath(),
477
478 channel: abuseModel.Video?.VideoChannel.toFormattedJSON() || abuseModel.deletedVideo?.channel
479 }
480 }
481
482 buildBaseAbuse (this: MAbuseUserFormattable, countMessages: number): UserAbuse {
483 const predefinedReasons = AbuseModel.getPredefinedReasonsStrings(this.predefinedReasons)
484
485 return {
486 id: this.id,
487 reason: this.reason,
488 predefinedReasons,
489
490 flaggedAccount: this.FlaggedAccount
491 ? this.FlaggedAccount.toFormattedJSON()
492 : null,
493
494 state: {
495 id: this.state,
496 label: AbuseModel.getStateLabel(this.state)
497 },
498
499 countMessages,
500
501 createdAt: this.createdAt,
502 updatedAt: this.updatedAt
503 }
504 }
505
506 toFormattedAdminJSON (this: MAbuseAdminFormattable): AdminAbuse {
507 const countReportsForVideo = this.get('countReportsForVideo') as number
508 const nthReportForVideo = this.get('nthReportForVideo') as number
509
510 const countReportsForReporter = this.get('countReportsForReporter') as number
511 const countReportsForReportee = this.get('countReportsForReportee') as number
512
513 const countMessages = this.get('countMessages') as number
514
515 const baseVideo = this.buildBaseVideoAbuse()
516 const video: AdminVideoAbuse = baseVideo
517 ? Object.assign(baseVideo, {
518 countReports: countReportsForVideo,
519 nthReport: nthReportForVideo
520 })
521 : null
522
523 const comment: AdminVideoCommentAbuse = this.buildBaseVideoCommentAbuse()
524
525 const abuse = this.buildBaseAbuse(countMessages || 0)
526
527 return Object.assign(abuse, {
528 video,
529 comment,
530
531 moderationComment: this.moderationComment,
532
533 reporterAccount: this.ReporterAccount
534 ? this.ReporterAccount.toFormattedJSON()
535 : null,
536
537 countReportsForReporter: (countReportsForReporter || 0),
538 countReportsForReportee: (countReportsForReportee || 0)
539 })
540 }
541
542 toFormattedUserJSON (this: MAbuseUserFormattable): UserAbuse {
543 const countMessages = this.get('countMessages') as number
544
545 const video = this.buildBaseVideoAbuse()
546 const comment = this.buildBaseVideoCommentAbuse()
547 const abuse = this.buildBaseAbuse(countMessages || 0)
548
549 return Object.assign(abuse, {
550 video,
551 comment
552 })
553 }
554
555 toActivityPubObject (this: MAbuseAP): AbuseObject {
556 const predefinedReasons = AbuseModel.getPredefinedReasonsStrings(this.predefinedReasons)
557
558 const object = this.VideoAbuse?.Video?.url || this.VideoCommentAbuse?.VideoComment?.url || this.FlaggedAccount.Actor.url
559
560 const startAt = this.VideoAbuse?.startAt
561 const endAt = this.VideoAbuse?.endAt
562
563 return {
564 type: 'Flag' as 'Flag',
565 content: this.reason,
566 mediaType: 'text/markdown',
567 object,
568 tag: predefinedReasons.map(r => ({
569 type: 'Hashtag' as 'Hashtag',
570 name: r
571 })),
572 startAt,
573 endAt
574 }
575 }
576
577 private static async internalCountForApi (parameters: BuildAbusesQueryOptions) {
578 const { query, replacements } = buildAbuseListQuery(parameters, 'count')
579 const options = {
580 type: QueryTypes.SELECT as QueryTypes.SELECT,
581 replacements
582 }
583
584 const [ { total } ] = await AbuseModel.sequelize.query<{ total: string }>(query, options)
585 if (total === null) return 0
586
587 return parseInt(total, 10)
588 }
589
590 private static async internalListForApi (parameters: BuildAbusesQueryOptions) {
591 const { query, replacements } = buildAbuseListQuery(parameters, 'id')
592 const options = {
593 type: QueryTypes.SELECT as QueryTypes.SELECT,
594 replacements
595 }
596
597 const rows = await AbuseModel.sequelize.query<{ id: string }>(query, options)
598 const ids = rows.map(r => r.id)
599
600 if (ids.length === 0) return []
601
602 return AbuseModel.scope(ScopeNames.FOR_API)
603 .findAll({
604 order: getSort(parameters.sort),
605 where: {
606 id: {
607 [Op.in]: ids
608 }
609 }
610 })
611 }
612
613 private static getStateLabel (id: number) {
614 return ABUSE_STATES[id] || 'Unknown'
615 }
616
617 private static getPredefinedReasonsStrings (predefinedReasons: AbusePredefinedReasons[]): AbusePredefinedReasonsString[] {
618 const invertedPredefinedReasons = invert(abusePredefinedReasonsMap)
619
620 return (predefinedReasons || [])
621 .map(r => invertedPredefinedReasons[r] as AbusePredefinedReasonsString)
622 .filter(v => !!v)
623 }
624}
diff --git a/server/models/abuse/sql/abuse-query-builder.ts b/server/models/abuse/sql/abuse-query-builder.ts
deleted file mode 100644
index 282d4541a..000000000
--- a/server/models/abuse/sql/abuse-query-builder.ts
+++ /dev/null
@@ -1,167 +0,0 @@
1
2import { exists } from '@server/helpers/custom-validators/misc'
3import { forceNumber } from '@shared/core-utils'
4import { AbuseFilter, AbuseState, AbuseVideoIs } from '@shared/models'
5import { buildBlockedAccountSQL, buildSortDirectionAndField } from '../../shared'
6
7export type BuildAbusesQueryOptions = {
8 start: number
9 count: number
10 sort: string
11
12 // search
13 search?: string
14 searchReporter?: string
15 searchReportee?: string
16
17 // video related
18 searchVideo?: string
19 searchVideoChannel?: string
20 videoIs?: AbuseVideoIs
21
22 // filters
23 id?: number
24 predefinedReasonId?: number
25 filter?: AbuseFilter
26
27 state?: AbuseState
28
29 // accountIds
30 serverAccountId?: number
31 userAccountId?: number
32
33 reporterAccountId?: number
34}
35
36function buildAbuseListQuery (options: BuildAbusesQueryOptions, type: 'count' | 'id') {
37 const whereAnd: string[] = []
38 const replacements: any = {}
39
40 const joins = [
41 'LEFT JOIN "videoAbuse" ON "videoAbuse"."abuseId" = "abuse"."id"',
42 'LEFT JOIN "video" ON "videoAbuse"."videoId" = "video"."id"',
43 'LEFT JOIN "videoBlacklist" ON "videoBlacklist"."videoId" = "video"."id"',
44 'LEFT JOIN "videoChannel" ON "video"."channelId" = "videoChannel"."id"',
45 'LEFT JOIN "account" "reporterAccount" ON "reporterAccount"."id" = "abuse"."reporterAccountId"',
46 'LEFT JOIN "account" "flaggedAccount" ON "flaggedAccount"."id" = "abuse"."flaggedAccountId"',
47 'LEFT JOIN "commentAbuse" ON "commentAbuse"."abuseId" = "abuse"."id"',
48 'LEFT JOIN "videoComment" ON "commentAbuse"."videoCommentId" = "videoComment"."id"'
49 ]
50
51 if (options.serverAccountId || options.userAccountId) {
52 whereAnd.push(
53 '"abuse"."reporterAccountId" IS NULL OR ' +
54 '"abuse"."reporterAccountId" NOT IN (' + buildBlockedAccountSQL([ options.serverAccountId, options.userAccountId ]) + ')'
55 )
56 }
57
58 if (options.reporterAccountId) {
59 whereAnd.push('"abuse"."reporterAccountId" = :reporterAccountId')
60 replacements.reporterAccountId = options.reporterAccountId
61 }
62
63 if (options.search) {
64 const searchWhereOr = [
65 '"video"."name" ILIKE :search',
66 '"videoChannel"."name" ILIKE :search',
67 `"videoAbuse"."deletedVideo"->>'name' ILIKE :search`,
68 `"videoAbuse"."deletedVideo"->'channel'->>'displayName' ILIKE :search`,
69 '"reporterAccount"."name" ILIKE :search',
70 '"flaggedAccount"."name" ILIKE :search'
71 ]
72
73 replacements.search = `%${options.search}%`
74 whereAnd.push('(' + searchWhereOr.join(' OR ') + ')')
75 }
76
77 if (options.searchVideo) {
78 whereAnd.push('"video"."name" ILIKE :searchVideo')
79 replacements.searchVideo = `%${options.searchVideo}%`
80 }
81
82 if (options.searchVideoChannel) {
83 whereAnd.push('"videoChannel"."name" ILIKE :searchVideoChannel')
84 replacements.searchVideoChannel = `%${options.searchVideoChannel}%`
85 }
86
87 if (options.id) {
88 whereAnd.push('"abuse"."id" = :id')
89 replacements.id = options.id
90 }
91
92 if (options.state) {
93 whereAnd.push('"abuse"."state" = :state')
94 replacements.state = options.state
95 }
96
97 if (options.videoIs === 'deleted') {
98 whereAnd.push('"videoAbuse"."deletedVideo" IS NOT NULL')
99 } else if (options.videoIs === 'blacklisted') {
100 whereAnd.push('"videoBlacklist"."id" IS NOT NULL')
101 }
102
103 if (options.predefinedReasonId) {
104 whereAnd.push(':predefinedReasonId = ANY("abuse"."predefinedReasons")')
105 replacements.predefinedReasonId = options.predefinedReasonId
106 }
107
108 if (options.filter === 'video') {
109 whereAnd.push('"videoAbuse"."id" IS NOT NULL')
110 } else if (options.filter === 'comment') {
111 whereAnd.push('"commentAbuse"."id" IS NOT NULL')
112 } else if (options.filter === 'account') {
113 whereAnd.push('"videoAbuse"."id" IS NULL AND "commentAbuse"."id" IS NULL')
114 }
115
116 if (options.searchReporter) {
117 whereAnd.push('"reporterAccount"."name" ILIKE :searchReporter')
118 replacements.searchReporter = `%${options.searchReporter}%`
119 }
120
121 if (options.searchReportee) {
122 whereAnd.push('"flaggedAccount"."name" ILIKE :searchReportee')
123 replacements.searchReportee = `%${options.searchReportee}%`
124 }
125
126 const prefix = type === 'count'
127 ? 'SELECT COUNT("abuse"."id") AS "total"'
128 : 'SELECT "abuse"."id" '
129
130 let suffix = ''
131 if (type !== 'count') {
132
133 if (options.sort) {
134 const order = buildAbuseOrder(options.sort)
135 suffix += `${order} `
136 }
137
138 if (exists(options.count)) {
139 const count = forceNumber(options.count)
140 suffix += `LIMIT ${count} `
141 }
142
143 if (exists(options.start)) {
144 const start = forceNumber(options.start)
145 suffix += `OFFSET ${start} `
146 }
147 }
148
149 const where = whereAnd.length !== 0
150 ? `WHERE ${whereAnd.join(' AND ')}`
151 : ''
152
153 return {
154 query: `${prefix} FROM "abuse" ${joins.join(' ')} ${where} ${suffix}`,
155 replacements
156 }
157}
158
159function buildAbuseOrder (value: string) {
160 const { direction, field } = buildSortDirectionAndField(value)
161
162 return `ORDER BY "abuse"."${field}" ${direction}`
163}
164
165export {
166 buildAbuseListQuery
167}
diff --git a/server/models/abuse/video-abuse.ts b/server/models/abuse/video-abuse.ts
deleted file mode 100644
index 773a9ebba..000000000
--- a/server/models/abuse/video-abuse.ts
+++ /dev/null
@@ -1,64 +0,0 @@
1import { AllowNull, BelongsTo, Column, CreatedAt, DataType, Default, ForeignKey, Model, Table, UpdatedAt } from 'sequelize-typescript'
2import { AttributesOnly } from '@shared/typescript-utils'
3import { VideoDetails } from '@shared/models'
4import { VideoModel } from '../video/video'
5import { AbuseModel } from './abuse'
6
7@Table({
8 tableName: 'videoAbuse',
9 indexes: [
10 {
11 fields: [ 'abuseId' ]
12 },
13 {
14 fields: [ 'videoId' ]
15 }
16 ]
17})
18export class VideoAbuseModel extends Model<Partial<AttributesOnly<VideoAbuseModel>>> {
19
20 @CreatedAt
21 createdAt: Date
22
23 @UpdatedAt
24 updatedAt: Date
25
26 @AllowNull(true)
27 @Default(null)
28 @Column
29 startAt: number
30
31 @AllowNull(true)
32 @Default(null)
33 @Column
34 endAt: number
35
36 @AllowNull(true)
37 @Default(null)
38 @Column(DataType.JSONB)
39 deletedVideo: VideoDetails
40
41 @ForeignKey(() => AbuseModel)
42 @Column
43 abuseId: number
44
45 @BelongsTo(() => AbuseModel, {
46 foreignKey: {
47 allowNull: false
48 },
49 onDelete: 'cascade'
50 })
51 Abuse: AbuseModel
52
53 @ForeignKey(() => VideoModel)
54 @Column
55 videoId: number
56
57 @BelongsTo(() => VideoModel, {
58 foreignKey: {
59 allowNull: true
60 },
61 onDelete: 'set null'
62 })
63 Video: VideoModel
64}
diff --git a/server/models/abuse/video-comment-abuse.ts b/server/models/abuse/video-comment-abuse.ts
deleted file mode 100644
index 337aaaa58..000000000
--- a/server/models/abuse/video-comment-abuse.ts
+++ /dev/null
@@ -1,48 +0,0 @@
1import { BelongsTo, Column, CreatedAt, ForeignKey, Model, Table, UpdatedAt } from 'sequelize-typescript'
2import { AttributesOnly } from '@shared/typescript-utils'
3import { VideoCommentModel } from '../video/video-comment'
4import { AbuseModel } from './abuse'
5
6@Table({
7 tableName: 'commentAbuse',
8 indexes: [
9 {
10 fields: [ 'abuseId' ]
11 },
12 {
13 fields: [ 'videoCommentId' ]
14 }
15 ]
16})
17export class VideoCommentAbuseModel extends Model<Partial<AttributesOnly<VideoCommentAbuseModel>>> {
18
19 @CreatedAt
20 createdAt: Date
21
22 @UpdatedAt
23 updatedAt: Date
24
25 @ForeignKey(() => AbuseModel)
26 @Column
27 abuseId: number
28
29 @BelongsTo(() => AbuseModel, {
30 foreignKey: {
31 allowNull: false
32 },
33 onDelete: 'cascade'
34 })
35 Abuse: AbuseModel
36
37 @ForeignKey(() => VideoCommentModel)
38 @Column
39 videoCommentId: number
40
41 @BelongsTo(() => VideoCommentModel, {
42 foreignKey: {
43 allowNull: true
44 },
45 onDelete: 'set null'
46 })
47 VideoComment: VideoCommentModel
48}
generated by cgit v1.2.3 (git 2.25.1) at 2025-01-23 19:36:12 +0000