aboutsummaryrefslogtreecommitdiffhomepage
path: root/server/models/abuse
diff options
context:
space:
mode:
authorChocobozzz <me@florianbigard.com>2020-07-01 16:05:30 +0200
committerChocobozzz <chocobozzz@cpy.re>2020-07-10 14:02:41 +0200
commitd95d15598847c7f020aa056e7e6e0c02d2bbf732 (patch)
treea8a593f1269688caf9e5f99559996f346290fec5 /server/models/abuse
parent72493e44e9b455a04c4f093ed6c6ffa300b98d8b (diff)
downloadPeerTube-d95d15598847c7f020aa056e7e6e0c02d2bbf732.tar.gz
PeerTube-d95d15598847c7f020aa056e7e6e0c02d2bbf732.tar.zst
PeerTube-d95d15598847c7f020aa056e7e6e0c02d2bbf732.zip
Use 3 tables to represent abuses
Diffstat (limited to 'server/models/abuse')
-rw-r--r--server/models/abuse/abuse.ts538
-rw-r--r--server/models/abuse/video-abuse.ts63
-rw-r--r--server/models/abuse/video-comment-abuse.ts53
3 files changed, 654 insertions, 0 deletions
diff --git a/server/models/abuse/abuse.ts b/server/models/abuse/abuse.ts
new file mode 100644
index 000000000..4f99f9c9b
--- /dev/null
+++ b/server/models/abuse/abuse.ts
@@ -0,0 +1,538 @@
1import * as Bluebird from 'bluebird'
2import { invert } from 'lodash'
3import { literal, Op, WhereOptions } from 'sequelize'
4import {
5 AllowNull,
6 BelongsTo,
7 Column,
8 CreatedAt,
9 DataType,
10 Default,
11 ForeignKey,
12 HasOne,
13 Is,
14 Model,
15 Scopes,
16 Table,
17 UpdatedAt
18} from 'sequelize-typescript'
19import { isAbuseModerationCommentValid, isAbuseReasonValid, isAbuseStateValid } from '@server/helpers/custom-validators/abuses'
20import {
21 Abuse,
22 AbuseObject,
23 AbusePredefinedReasons,
24 abusePredefinedReasonsMap,
25 AbusePredefinedReasonsString,
26 AbuseState,
27 AbuseVideoIs,
28 VideoAbuse
29} from '@shared/models'
30import { AbuseFilter } from '@shared/models/moderation/abuse/abuse-filter'
31import { CONSTRAINTS_FIELDS, ABUSE_STATES } from '../../initializers/constants'
32import { MAbuse, MAbuseAP, MAbuseFormattable, MUserAccountId } from '../../types/models'
33import { AccountModel, ScopeNames as AccountScopeNames } from '../account/account'
34import { buildBlockedAccountSQL, getSort, searchAttribute, throwIfNotValid } from '../utils'
35import { ThumbnailModel } from '../video/thumbnail'
36import { VideoModel } from '../video/video'
37import { VideoBlacklistModel } from '../video/video-blacklist'
38import { ScopeNames as VideoChannelScopeNames, SummaryOptions, VideoChannelModel } from '../video/video-channel'
39import { VideoAbuseModel } from './video-abuse'
40import { VideoCommentAbuseModel } from './video-comment-abuse'
41
42export enum ScopeNames {
43 FOR_API = 'FOR_API'
44}
45
46@Scopes(() => ({
47 [ScopeNames.FOR_API]: (options: {
48 // search
49 search?: string
50 searchReporter?: string
51 searchReportee?: string
52
53 // video releated
54 searchVideo?: string
55 searchVideoChannel?: string
56 videoIs?: AbuseVideoIs
57
58 // filters
59 id?: number
60 predefinedReasonId?: number
61 filter?: AbuseFilter
62
63 state?: AbuseState
64
65 // accountIds
66 serverAccountId: number
67 userAccountId: number
68 }) => {
69 const onlyBlacklisted = options.videoIs === 'blacklisted'
70 const videoRequired = !!(onlyBlacklisted || options.searchVideo || options.searchVideoChannel)
71
72 const where = {
73 reporterAccountId: {
74 [Op.notIn]: literal('(' + buildBlockedAccountSQL([ options.serverAccountId, options.userAccountId ]) + ')')
75 }
76 }
77
78 if (options.search) {
79 const escapedSearch = AbuseModel.sequelize.escape('%' + options.search + '%')
80
81 Object.assign(where, {
82 [Op.or]: [
83 {
84 [Op.and]: [
85 { '$VideoAbuse.videoId$': { [Op.not]: null } },
86 searchAttribute(options.search, '$VideoAbuse.Video.name$')
87 ]
88 },
89 {
90 [Op.and]: [
91 { '$VideoAbuse.videoId$': { [Op.not]: null } },
92 searchAttribute(options.search, '$VideoAbuse.Video.VideoChannel.name$')
93 ]
94 },
95 {
96 [Op.and]: [
97 { '$VideoAbuse.deletedVideo$': { [Op.not]: null } },
98 literal(`"VideoAbuse"."deletedVideo"->>'name' ILIKE ${escapedSearch}`)
99 ]
100 },
101 {
102 [Op.and]: [
103 { '$VideoAbuse.deletedVideo$': { [Op.not]: null } },
104 literal(`"VideoAbuse"."deletedVideo"->'channel'->>'displayName' ILIKE ${escapedSearch}`)
105 ]
106 },
107 searchAttribute(options.search, '$ReporterAccount.name$'),
108 searchAttribute(options.search, '$FlaggedAccount.name$')
109 ]
110 })
111 }
112
113 if (options.id) Object.assign(where, { id: options.id })
114 if (options.state) Object.assign(where, { state: options.state })
115
116 if (options.videoIs === 'deleted') {
117 Object.assign(where, {
118 '$VideoAbuse.deletedVideo$': {
119 [Op.not]: null
120 }
121 })
122 }
123
124 if (options.predefinedReasonId) {
125 Object.assign(where, {
126 predefinedReasons: {
127 [Op.contains]: [ options.predefinedReasonId ]
128 }
129 })
130 }
131
132 return {
133 attributes: {
134 include: [
135 [
136 // we don't care about this count for deleted videos, so there are not included
137 literal(
138 '(' +
139 'SELECT count(*) ' +
140 'FROM "videoAbuse" ' +
141 'WHERE "videoId" = "VideoAbuse"."videoId" ' +
142 ')'
143 ),
144 'countReportsForVideo'
145 ],
146 [
147 // we don't care about this count for deleted videos, so there are not included
148 literal(
149 '(' +
150 'SELECT t.nth ' +
151 'FROM ( ' +
152 'SELECT id, ' +
153 'row_number() OVER (PARTITION BY "videoId" ORDER BY "createdAt") AS nth ' +
154 'FROM "videoAbuse" ' +
155 ') t ' +
156 'WHERE t.id = "VideoAbuse".id' +
157 ')'
158 ),
159 'nthReportForVideo'
160 ],
161 [
162 literal(
163 '(' +
164 'SELECT count("videoAbuse"."id") ' +
165 'FROM "videoAbuse" ' +
166 'INNER JOIN "video" ON "video"."id" = "videoAbuse"."videoId" ' +
167 'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" ' +
168 'INNER JOIN "account" ON "videoChannel"."accountId" = "account"."id" ' +
169 'WHERE "account"."id" = "AbuseModel"."reporterAccountId" ' +
170 ')'
171 ),
172 'countReportsForReporter__video'
173 ],
174 [
175 literal(
176 '(' +
177 'SELECT count(DISTINCT "videoAbuse"."id") ' +
178 'FROM "videoAbuse" ' +
179 `WHERE CAST("deletedVideo"->'channel'->'ownerAccount'->>'id' AS INTEGER) = "AbuseModel"."reporterAccountId" ` +
180 ')'
181 ),
182 'countReportsForReporter__deletedVideo'
183 ],
184 [
185 literal(
186 '(' +
187 'SELECT count(DISTINCT "videoAbuse"."id") ' +
188 'FROM "videoAbuse" ' +
189 'INNER JOIN "video" ON "video"."id" = "videoAbuse"."videoId" ' +
190 'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" ' +
191 'INNER JOIN "account" ON ' +
192 '"videoChannel"."accountId" = "VideoAbuse->Video->VideoChannel"."accountId" ' +
193 `OR "videoChannel"."accountId" = CAST("VideoAbuse"."deletedVideo"->'channel'->'ownerAccount'->>'id' AS INTEGER) ` +
194 ')'
195 ),
196 'countReportsForReportee__video'
197 ],
198 [
199 literal(
200 '(' +
201 'SELECT count(DISTINCT "videoAbuse"."id") ' +
202 'FROM "videoAbuse" ' +
203 `WHERE CAST("deletedVideo"->'channel'->'ownerAccount'->>'id' AS INTEGER) = "VideoAbuse->Video->VideoChannel"."accountId" ` +
204 `OR CAST("deletedVideo"->'channel'->'ownerAccount'->>'id' AS INTEGER) = ` +
205 `CAST("VideoAbuse"."deletedVideo"->'channel'->'ownerAccount'->>'id' AS INTEGER) ` +
206 ')'
207 ),
208 'countReportsForReportee__deletedVideo'
209 ]
210 ]
211 },
212 include: [
213 {
214 model: AccountModel.scope(AccountScopeNames.SUMMARY),
215 as: 'ReporterAccount',
216 required: true,
217 where: searchAttribute(options.searchReporter, 'name')
218 },
219 {
220 model: AccountModel.scope(AccountScopeNames.SUMMARY),
221 as: 'FlaggedAccount',
222 required: true,
223 where: searchAttribute(options.searchReportee, 'name')
224 },
225 {
226 model: VideoAbuseModel,
227 required: options.filter === 'video' || !!options.videoIs || videoRequired,
228 include: [
229 {
230 model: VideoModel,
231 required: videoRequired,
232 where: searchAttribute(options.searchVideo, 'name'),
233 include: [
234 {
235 model: ThumbnailModel
236 },
237 {
238 model: VideoChannelModel.scope({ method: [ VideoChannelScopeNames.SUMMARY, { withAccount: false } as SummaryOptions ] }),
239 where: searchAttribute(options.searchVideoChannel, 'name'),
240 required: true,
241 include: [
242 {
243 model: AccountModel.scope(AccountScopeNames.SUMMARY),
244 required: true,
245 where: searchAttribute(options.searchReportee, 'name')
246 }
247 ]
248 },
249 {
250 attributes: [ 'id', 'reason', 'unfederated' ],
251 model: VideoBlacklistModel,
252 required: onlyBlacklisted
253 }
254 ]
255 }
256 ]
257 }
258 ],
259 where
260 }
261 }
262}))
263@Table({
264 tableName: 'abuse',
265 indexes: [
266 {
267 fields: [ 'reporterAccountId' ]
268 },
269 {
270 fields: [ 'flaggedAccountId' ]
271 }
272 ]
273})
274export class AbuseModel extends Model<AbuseModel> {
275
276 @AllowNull(false)
277 @Default(null)
278 @Is('VideoAbuseReason', value => throwIfNotValid(value, isAbuseReasonValid, 'reason'))
279 @Column(DataType.STRING(CONSTRAINTS_FIELDS.ABUSES.REASON.max))
280 reason: string
281
282 @AllowNull(false)
283 @Default(null)
284 @Is('VideoAbuseState', value => throwIfNotValid(value, isAbuseStateValid, 'state'))
285 @Column
286 state: AbuseState
287
288 @AllowNull(true)
289 @Default(null)
290 @Is('VideoAbuseModerationComment', value => throwIfNotValid(value, isAbuseModerationCommentValid, 'moderationComment', true))
291 @Column(DataType.STRING(CONSTRAINTS_FIELDS.ABUSES.MODERATION_COMMENT.max))
292 moderationComment: string
293
294 @AllowNull(true)
295 @Default(null)
296 @Column(DataType.ARRAY(DataType.INTEGER))
297 predefinedReasons: AbusePredefinedReasons[]
298
299 @CreatedAt
300 createdAt: Date
301
302 @UpdatedAt
303 updatedAt: Date
304
305 @ForeignKey(() => AccountModel)
306 @Column
307 reporterAccountId: number
308
309 @BelongsTo(() => AccountModel, {
310 foreignKey: {
311 name: 'reporterAccountId',
312 allowNull: true
313 },
314 as: 'ReporterAccount',
315 onDelete: 'set null'
316 })
317 ReporterAccount: AccountModel
318
319 @ForeignKey(() => AccountModel)
320 @Column
321 flaggedAccountId: number
322
323 @BelongsTo(() => AccountModel, {
324 foreignKey: {
325 name: 'flaggedAccountId',
326 allowNull: true
327 },
328 as: 'FlaggedAccount',
329 onDelete: 'set null'
330 })
331 FlaggedAccount: AccountModel
332
333 @HasOne(() => VideoCommentAbuseModel, {
334 foreignKey: {
335 name: 'abuseId',
336 allowNull: false
337 },
338 onDelete: 'cascade'
339 })
340 VideoCommentAbuse: VideoCommentAbuseModel
341
342 @HasOne(() => VideoAbuseModel, {
343 foreignKey: {
344 name: 'abuseId',
345 allowNull: false
346 },
347 onDelete: 'cascade'
348 })
349 VideoAbuse: VideoAbuseModel
350
351 static loadByIdAndVideoId (id: number, videoId?: number, uuid?: string): Bluebird<MAbuse> {
352 const videoWhere: WhereOptions = {}
353
354 if (videoId) videoWhere.videoId = videoId
355 if (uuid) videoWhere.deletedVideo = { uuid }
356
357 const query = {
358 include: [
359 {
360 model: VideoAbuseModel,
361 required: true,
362 where: videoWhere
363 }
364 ],
365 where: {
366 id
367 }
368 }
369 return AbuseModel.findOne(query)
370 }
371
372 static listForApi (parameters: {
373 start: number
374 count: number
375 sort: string
376
377 filter?: AbuseFilter
378
379 serverAccountId: number
380 user?: MUserAccountId
381
382 id?: number
383 predefinedReason?: AbusePredefinedReasonsString
384 state?: AbuseState
385 videoIs?: AbuseVideoIs
386
387 search?: string
388 searchReporter?: string
389 searchReportee?: string
390 searchVideo?: string
391 searchVideoChannel?: string
392 }) {
393 const {
394 start,
395 count,
396 sort,
397 search,
398 user,
399 serverAccountId,
400 state,
401 videoIs,
402 predefinedReason,
403 searchReportee,
404 searchVideo,
405 filter,
406 searchVideoChannel,
407 searchReporter,
408 id
409 } = parameters
410
411 const userAccountId = user ? user.Account.id : undefined
412 const predefinedReasonId = predefinedReason ? abusePredefinedReasonsMap[predefinedReason] : undefined
413
414 const query = {
415 offset: start,
416 limit: count,
417 order: getSort(sort),
418 col: 'AbuseModel.id',
419 distinct: true
420 }
421
422 const filters = {
423 id,
424 filter,
425 predefinedReasonId,
426 search,
427 state,
428 videoIs,
429 searchReportee,
430 searchVideo,
431 searchVideoChannel,
432 searchReporter,
433 serverAccountId,
434 userAccountId
435 }
436
437 return AbuseModel
438 .scope([
439 { method: [ ScopeNames.FOR_API, filters ] }
440 ])
441 .findAndCountAll(query)
442 .then(({ rows, count }) => {
443 return { total: count, data: rows }
444 })
445 }
446
447 toFormattedJSON (this: MAbuseFormattable): Abuse {
448 const predefinedReasons = AbuseModel.getPredefinedReasonsStrings(this.predefinedReasons)
449 const countReportsForVideo = this.get('countReportsForVideo') as number
450 const nthReportForVideo = this.get('nthReportForVideo') as number
451 const countReportsForReporterVideo = this.get('countReportsForReporter__video') as number
452 const countReportsForReporterDeletedVideo = this.get('countReportsForReporter__deletedVideo') as number
453 const countReportsForReporteeVideo = this.get('countReportsForReportee__video') as number
454 const countReportsForReporteeDeletedVideo = this.get('countReportsForReportee__deletedVideo') as number
455
456 let video: VideoAbuse
457
458 if (this.VideoAbuse) {
459 const abuseModel = this.VideoAbuse
460 const entity = abuseModel.Video || abuseModel.deletedVideo
461
462 video = {
463 id: entity.id,
464 uuid: entity.uuid,
465 name: entity.name,
466 nsfw: entity.nsfw,
467
468 startAt: abuseModel.startAt,
469 endAt: abuseModel.endAt,
470
471 deleted: !abuseModel.Video,
472 blacklisted: abuseModel.Video?.isBlacklisted() || false,
473 thumbnailPath: abuseModel.Video?.getMiniatureStaticPath(),
474 channel: abuseModel.Video?.VideoChannel.toFormattedJSON() || abuseModel.deletedVideo?.channel
475 }
476 }
477
478 return {
479 id: this.id,
480 reason: this.reason,
481 predefinedReasons,
482
483 reporterAccount: this.ReporterAccount.toFormattedJSON(),
484
485 state: {
486 id: this.state,
487 label: AbuseModel.getStateLabel(this.state)
488 },
489
490 moderationComment: this.moderationComment,
491
492 video,
493 comment: null,
494
495 createdAt: this.createdAt,
496 updatedAt: this.updatedAt,
497 count: countReportsForVideo || 0,
498 nth: nthReportForVideo || 0,
499 countReportsForReporter: (countReportsForReporterVideo || 0) + (countReportsForReporterDeletedVideo || 0),
500 countReportsForReportee: (countReportsForReporteeVideo || 0) + (countReportsForReporteeDeletedVideo || 0),
501
502 // FIXME: deprecated in 2.3, remove this
503 startAt: null,
504 endAt: null
505 }
506 }
507
508 toActivityPubObject (this: MAbuseAP): AbuseObject {
509 const predefinedReasons = AbuseModel.getPredefinedReasonsStrings(this.predefinedReasons)
510
511 const object = this.VideoAbuse?.Video?.url || this.VideoCommentAbuse?.VideoComment?.url || this.FlaggedAccount.Actor.url
512
513 const startAt = this.VideoAbuse?.startAt
514 const endAt = this.VideoAbuse?.endAt
515
516 return {
517 type: 'Flag' as 'Flag',
518 content: this.reason,
519 object,
520 tag: predefinedReasons.map(r => ({
521 type: 'Hashtag' as 'Hashtag',
522 name: r
523 })),
524 startAt,
525 endAt
526 }
527 }
528
529 private static getStateLabel (id: number) {
530 return ABUSE_STATES[id] || 'Unknown'
531 }
532
533 private static getPredefinedReasonsStrings (predefinedReasons: AbusePredefinedReasons[]): AbusePredefinedReasonsString[] {
534 return (predefinedReasons || [])
535 .filter(r => r in AbusePredefinedReasons)
536 .map(r => invert(abusePredefinedReasonsMap)[r] as AbusePredefinedReasonsString)
537 }
538}
diff --git a/server/models/abuse/video-abuse.ts b/server/models/abuse/video-abuse.ts
new file mode 100644
index 000000000..d92bcf19f
--- /dev/null
+++ b/server/models/abuse/video-abuse.ts
@@ -0,0 +1,63 @@
1import { AllowNull, BelongsTo, Column, CreatedAt, DataType, Default, ForeignKey, Model, Table, UpdatedAt } from 'sequelize-typescript'
2import { VideoDetails } from '@shared/models'
3import { VideoModel } from '../video/video'
4import { AbuseModel } from './abuse'
5
6@Table({
7 tableName: 'videoAbuse',
8 indexes: [
9 {
10 fields: [ 'abuseId' ]
11 },
12 {
13 fields: [ 'videoId' ]
14 }
15 ]
16})
17export class VideoAbuseModel extends Model<VideoAbuseModel> {
18
19 @CreatedAt
20 createdAt: Date
21
22 @UpdatedAt
23 updatedAt: Date
24
25 @AllowNull(true)
26 @Default(null)
27 @Column
28 startAt: number
29
30 @AllowNull(true)
31 @Default(null)
32 @Column
33 endAt: number
34
35 @AllowNull(true)
36 @Default(null)
37 @Column(DataType.JSONB)
38 deletedVideo: VideoDetails
39
40 @ForeignKey(() => AbuseModel)
41 @Column
42 abuseId: number
43
44 @BelongsTo(() => AbuseModel, {
45 foreignKey: {
46 allowNull: false
47 },
48 onDelete: 'cascade'
49 })
50 Abuse: AbuseModel
51
52 @ForeignKey(() => VideoModel)
53 @Column
54 videoId: number
55
56 @BelongsTo(() => VideoModel, {
57 foreignKey: {
58 allowNull: true
59 },
60 onDelete: 'set null'
61 })
62 Video: VideoModel
63}
diff --git a/server/models/abuse/video-comment-abuse.ts b/server/models/abuse/video-comment-abuse.ts
new file mode 100644
index 000000000..b4cc2762e
--- /dev/null
+++ b/server/models/abuse/video-comment-abuse.ts
@@ -0,0 +1,53 @@
1import { AllowNull, BelongsTo, Column, CreatedAt, DataType, Default, ForeignKey, Model, Table, UpdatedAt } from 'sequelize-typescript'
2import { VideoComment } from '@shared/models'
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<VideoCommentAbuseModel> {
18
19 @CreatedAt
20 createdAt: Date
21
22 @UpdatedAt
23 updatedAt: Date
24
25 @AllowNull(true)
26 @Default(null)
27 @Column(DataType.JSONB)
28 deletedComment: VideoComment
29
30 @ForeignKey(() => AbuseModel)
31 @Column
32 abuseId: number
33
34 @BelongsTo(() => AbuseModel, {
35 foreignKey: {
36 allowNull: false
37 },
38 onDelete: 'cascade'
39 })
40 Abuse: AbuseModel
41
42 @ForeignKey(() => VideoCommentModel)
43 @Column
44 videoCommentId: number
45
46 @BelongsTo(() => VideoCommentModel, {
47 foreignKey: {
48 allowNull: true
49 },
50 onDelete: 'set null'
51 })
52 VideoComment: VideoCommentModel
53}