import * as Bluebird from 'bluebird'
import { invert } from 'lodash'
-import { literal, Op, WhereOptions } from 'sequelize'
+import { literal, Op, QueryTypes } from 'sequelize'
import {
AllowNull,
BelongsTo,
UpdatedAt
} from 'sequelize-typescript'
import { isAbuseModerationCommentValid, isAbuseReasonValid, isAbuseStateValid } from '@server/helpers/custom-validators/abuses'
+import { abusePredefinedReasonsMap } from '@shared/core-utils/abuse'
import {
- Abuse,
+ AbuseFilter,
AbuseObject,
AbusePredefinedReasons,
- abusePredefinedReasonsMap,
AbusePredefinedReasonsString,
AbuseState,
AbuseVideoIs,
- VideoAbuse
+ AdminAbuse,
+ AdminVideoAbuse,
+ AdminVideoCommentAbuse,
+ UserAbuse,
+ UserVideoAbuse
} from '@shared/models'
-import { AbuseFilter } from '@shared/models/moderation/abuse/abuse-filter'
-import { CONSTRAINTS_FIELDS, ABUSE_STATES } from '../../initializers/constants'
-import { MAbuse, MAbuseAP, MAbuseFormattable, MUserAccountId } from '../../types/models'
-import { AccountModel, ScopeNames as AccountScopeNames } from '../account/account'
-import { buildBlockedAccountSQL, getSort, searchAttribute, throwIfNotValid } from '../utils'
+import { ABUSE_STATES, CONSTRAINTS_FIELDS } from '../../initializers/constants'
+import { MAbuseAdminFormattable, MAbuseAP, MAbuseFull, MAbuseReporter, MAbuseUserFormattable, MUserAccountId } from '../../types/models'
+import { AccountModel, ScopeNames as AccountScopeNames, SummaryOptions as AccountSummaryOptions } from '../account/account'
+import { getSort, throwIfNotValid } from '../utils'
import { ThumbnailModel } from '../video/thumbnail'
-import { VideoModel } from '../video/video'
+import { ScopeNames as VideoScopeNames, VideoModel } from '../video/video'
import { VideoBlacklistModel } from '../video/video-blacklist'
-import { ScopeNames as VideoChannelScopeNames, SummaryOptions, VideoChannelModel } from '../video/video-channel'
+import { ScopeNames as VideoChannelScopeNames, SummaryOptions as ChannelSummaryOptions, VideoChannelModel } from '../video/video-channel'
+import { ScopeNames as CommentScopeNames, VideoCommentModel } from '../video/video-comment'
+import { buildAbuseListQuery, BuildAbusesQueryOptions } from './abuse-query-builder'
import { VideoAbuseModel } from './video-abuse'
import { VideoCommentAbuseModel } from './video-comment-abuse'
}
@Scopes(() => ({
- [ScopeNames.FOR_API]: (options: {
- // search
- search?: string
- searchReporter?: string
- searchReportee?: string
-
- // video releated
- searchVideo?: string
- searchVideoChannel?: string
- videoIs?: AbuseVideoIs
-
- // filters
- id?: number
- predefinedReasonId?: number
- filter?: AbuseFilter
-
- state?: AbuseState
-
- // accountIds
- serverAccountId: number
- userAccountId: number
- }) => {
- const onlyBlacklisted = options.videoIs === 'blacklisted'
- const videoRequired = !!(onlyBlacklisted || options.searchVideo || options.searchVideoChannel)
-
- const where = {
- reporterAccountId: {
- [Op.notIn]: literal('(' + buildBlockedAccountSQL([ options.serverAccountId, options.userAccountId ]) + ')')
- }
- }
-
- if (options.search) {
- const escapedSearch = AbuseModel.sequelize.escape('%' + options.search + '%')
-
- Object.assign(where, {
- [Op.or]: [
- {
- [Op.and]: [
- { '$VideoAbuse.videoId$': { [Op.not]: null } },
- searchAttribute(options.search, '$VideoAbuse.Video.name$')
- ]
- },
- {
- [Op.and]: [
- { '$VideoAbuse.videoId$': { [Op.not]: null } },
- searchAttribute(options.search, '$VideoAbuse.Video.VideoChannel.name$')
- ]
- },
- {
- [Op.and]: [
- { '$VideoAbuse.deletedVideo$': { [Op.not]: null } },
- literal(`"VideoAbuse"."deletedVideo"->>'name' ILIKE ${escapedSearch}`)
- ]
- },
- {
- [Op.and]: [
- { '$VideoAbuse.deletedVideo$': { [Op.not]: null } },
- literal(`"VideoAbuse"."deletedVideo"->'channel'->>'displayName' ILIKE ${escapedSearch}`)
- ]
- },
- searchAttribute(options.search, '$ReporterAccount.name$'),
- searchAttribute(options.search, '$FlaggedAccount.name$')
- ]
- })
- }
-
- if (options.id) Object.assign(where, { id: options.id })
- if (options.state) Object.assign(where, { state: options.state })
-
- if (options.videoIs === 'deleted') {
- Object.assign(where, {
- '$VideoAbuse.deletedVideo$': {
- [Op.not]: null
- }
- })
- }
-
- if (options.predefinedReasonId) {
- Object.assign(where, {
- predefinedReasons: {
- [Op.contains]: [ options.predefinedReasonId ]
- }
- })
- }
-
+ [ScopeNames.FOR_API]: () => {
return {
attributes: {
include: [
+ [
+ literal(
+ '(' +
+ 'SELECT count(*) ' +
+ 'FROM "abuseMessage" ' +
+ 'WHERE "abuseId" = "AbuseModel"."id"' +
+ ')'
+ ),
+ 'countMessages'
+ ],
[
// we don't care about this count for deleted videos, so there are not included
literal(
'(' +
'SELECT count(*) ' +
'FROM "videoAbuse" ' +
- 'WHERE "videoId" = "VideoAbuse"."videoId" ' +
+ 'WHERE "videoId" = "VideoAbuse"."videoId" AND "videoId" IS NOT NULL' +
')'
),
'countReportsForVideo'
'row_number() OVER (PARTITION BY "videoId" ORDER BY "createdAt") AS nth ' +
'FROM "videoAbuse" ' +
') t ' +
- 'WHERE t.id = "VideoAbuse".id' +
+ 'WHERE t.id = "VideoAbuse".id AND t.id IS NOT NULL' +
')'
),
'nthReportForVideo'
[
literal(
'(' +
- 'SELECT count("videoAbuse"."id") ' +
- 'FROM "videoAbuse" ' +
- 'INNER JOIN "video" ON "video"."id" = "videoAbuse"."videoId" ' +
- 'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" ' +
- 'INNER JOIN "account" ON "videoChannel"."accountId" = "account"."id" ' +
- 'WHERE "account"."id" = "AbuseModel"."reporterAccountId" ' +
+ 'SELECT count("abuse"."id") ' +
+ 'FROM "abuse" ' +
+ 'WHERE "abuse"."reporterAccountId" = "AbuseModel"."reporterAccountId"' +
')'
),
- 'countReportsForReporter__video'
+ 'countReportsForReporter'
],
[
literal(
'(' +
- 'SELECT count(DISTINCT "videoAbuse"."id") ' +
- 'FROM "videoAbuse" ' +
- `WHERE CAST("deletedVideo"->'channel'->'ownerAccount'->>'id' AS INTEGER) = "AbuseModel"."reporterAccountId" ` +
+ 'SELECT count("abuse"."id") ' +
+ 'FROM "abuse" ' +
+ 'WHERE "abuse"."flaggedAccountId" = "AbuseModel"."flaggedAccountId"' +
')'
),
- 'countReportsForReporter__deletedVideo'
- ],
- [
- literal(
- '(' +
- 'SELECT count(DISTINCT "videoAbuse"."id") ' +
- 'FROM "videoAbuse" ' +
- 'INNER JOIN "video" ON "video"."id" = "videoAbuse"."videoId" ' +
- 'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" ' +
- 'INNER JOIN "account" ON ' +
- '"videoChannel"."accountId" = "VideoAbuse->Video->VideoChannel"."accountId" ' +
- `OR "videoChannel"."accountId" = CAST("VideoAbuse"."deletedVideo"->'channel'->'ownerAccount'->>'id' AS INTEGER) ` +
- ')'
- ),
- 'countReportsForReportee__video'
- ],
- [
- literal(
- '(' +
- 'SELECT count(DISTINCT "videoAbuse"."id") ' +
- 'FROM "videoAbuse" ' +
- `WHERE CAST("deletedVideo"->'channel'->'ownerAccount'->>'id' AS INTEGER) = "VideoAbuse->Video->VideoChannel"."accountId" ` +
- `OR CAST("deletedVideo"->'channel'->'ownerAccount'->>'id' AS INTEGER) = ` +
- `CAST("VideoAbuse"."deletedVideo"->'channel'->'ownerAccount'->>'id' AS INTEGER) ` +
- ')'
- ),
- 'countReportsForReportee__deletedVideo'
+ 'countReportsForReportee'
]
]
},
include: [
{
- model: AccountModel.scope(AccountScopeNames.SUMMARY),
- as: 'ReporterAccount',
- required: true,
- where: searchAttribute(options.searchReporter, 'name')
+ model: AccountModel.scope({
+ method: [
+ AccountScopeNames.SUMMARY,
+ { actorRequired: false } as AccountSummaryOptions
+ ]
+ }),
+ as: 'ReporterAccount'
},
{
- model: AccountModel.scope(AccountScopeNames.SUMMARY),
- as: 'FlaggedAccount',
- required: true,
- where: searchAttribute(options.searchReportee, 'name')
+ model: AccountModel.scope({
+ method: [
+ AccountScopeNames.SUMMARY,
+ { actorRequired: false } as AccountSummaryOptions
+ ]
+ }),
+ as: 'FlaggedAccount'
},
{
- model: VideoAbuseModel,
- required: options.filter === 'video' || !!options.videoIs || videoRequired,
+ model: VideoCommentAbuseModel.unscoped(),
+ include: [
+ {
+ model: VideoCommentModel.unscoped(),
+ include: [
+ {
+ model: VideoModel.unscoped(),
+ attributes: [ 'name', 'id', 'uuid' ]
+ }
+ ]
+ }
+ ]
+ },
+ {
+ model: VideoAbuseModel.unscoped(),
include: [
{
- model: VideoModel,
- required: videoRequired,
- where: searchAttribute(options.searchVideo, 'name'),
+ attributes: [ 'id', 'uuid', 'name', 'nsfw' ],
+ model: VideoModel.unscoped(),
include: [
{
+ attributes: [ 'filename', 'fileUrl', 'type' ],
model: ThumbnailModel
},
{
- model: VideoChannelModel.scope({ method: [ VideoChannelScopeNames.SUMMARY, { withAccount: false } as SummaryOptions ] }),
- where: searchAttribute(options.searchVideoChannel, 'name'),
- required: true,
- include: [
- {
- model: AccountModel.scope(AccountScopeNames.SUMMARY),
- required: true,
- where: searchAttribute(options.searchReportee, 'name')
- }
- ]
+ model: VideoChannelModel.scope({
+ method: [
+ VideoChannelScopeNames.SUMMARY,
+ { withAccount: false, actorRequired: false } as ChannelSummaryOptions
+ ]
+ }),
+ required: false
},
{
attributes: [ 'id', 'reason', 'unfederated' ],
- model: VideoBlacklistModel,
- required: onlyBlacklisted
+ required: false,
+ model: VideoBlacklistModel
}
]
}
]
}
- ],
- where
+ ]
}
}
}))
@AllowNull(false)
@Default(null)
- @Is('VideoAbuseReason', value => throwIfNotValid(value, isAbuseReasonValid, 'reason'))
+ @Is('AbuseReason', value => throwIfNotValid(value, isAbuseReasonValid, 'reason'))
@Column(DataType.STRING(CONSTRAINTS_FIELDS.ABUSES.REASON.max))
reason: string
@AllowNull(false)
@Default(null)
- @Is('VideoAbuseState', value => throwIfNotValid(value, isAbuseStateValid, 'state'))
+ @Is('AbuseState', value => throwIfNotValid(value, isAbuseStateValid, 'state'))
@Column
state: AbuseState
@AllowNull(true)
@Default(null)
- @Is('VideoAbuseModerationComment', value => throwIfNotValid(value, isAbuseModerationCommentValid, 'moderationComment', true))
+ @Is('AbuseModerationComment', value => throwIfNotValid(value, isAbuseModerationCommentValid, 'moderationComment', true))
@Column(DataType.STRING(CONSTRAINTS_FIELDS.ABUSES.MODERATION_COMMENT.max))
moderationComment: string
})
VideoAbuse: VideoAbuseModel
- static loadByIdAndVideoId (id: number, videoId?: number, uuid?: string): Bluebird<MAbuse> {
- const videoWhere: WhereOptions = {}
+ static loadByIdWithReporter (id: number): Bluebird<MAbuseReporter> {
+ const query = {
+ where: {
+ id
+ },
+ include: [
+ {
+ model: AccountModel,
+ as: 'ReporterAccount'
+ }
+ ]
+ }
- if (videoId) videoWhere.videoId = videoId
- if (uuid) videoWhere.deletedVideo = { uuid }
+ return AbuseModel.findOne(query)
+ }
+ static loadFull (id: number): Bluebird<MAbuseFull> {
const query = {
+ where: {
+ id
+ },
include: [
+ {
+ model: AccountModel.scope(AccountScopeNames.SUMMARY),
+ required: false,
+ as: 'ReporterAccount'
+ },
+ {
+ model: AccountModel.scope(AccountScopeNames.SUMMARY),
+ as: 'FlaggedAccount'
+ },
{
model: VideoAbuseModel,
- required: true,
- where: videoWhere
+ required: false,
+ include: [
+ {
+ model: VideoModel.scope([ VideoScopeNames.WITH_ACCOUNT_DETAILS ])
+ }
+ ]
+ },
+ {
+ model: VideoCommentAbuseModel,
+ required: false,
+ include: [
+ {
+ model: VideoCommentModel.scope([
+ CommentScopeNames.WITH_ACCOUNT
+ ]),
+ include: [
+ {
+ model: VideoModel
+ }
+ ]
+ }
+ ]
}
- ],
- where: {
- id
- }
+ ]
}
+
return AbuseModel.findOne(query)
}
- static listForApi (parameters: {
+ static async listForAdminApi (parameters: {
start: number
count: number
sort: string
const userAccountId = user ? user.Account.id : undefined
const predefinedReasonId = predefinedReason ? abusePredefinedReasonsMap[predefinedReason] : undefined
- const query = {
- offset: start,
- limit: count,
- order: getSort(sort),
- col: 'AbuseModel.id',
- distinct: true
- }
-
- const filters = {
+ const queryOptions: BuildAbusesQueryOptions = {
+ start,
+ count,
+ sort,
id,
filter,
predefinedReasonId,
userAccountId
}
- return AbuseModel
- .scope([
- { method: [ ScopeNames.FOR_API, filters ] }
- ])
- .findAndCountAll(query)
- .then(({ rows, count }) => {
- return { total: count, data: rows }
- })
+ const [ total, data ] = await Promise.all([
+ AbuseModel.internalCountForApi(queryOptions),
+ AbuseModel.internalListForApi(queryOptions)
+ ])
+
+ return { total, data }
}
- toFormattedJSON (this: MAbuseFormattable): Abuse {
- const predefinedReasons = AbuseModel.getPredefinedReasonsStrings(this.predefinedReasons)
- const countReportsForVideo = this.get('countReportsForVideo') as number
- const nthReportForVideo = this.get('nthReportForVideo') as number
- const countReportsForReporterVideo = this.get('countReportsForReporter__video') as number
- const countReportsForReporterDeletedVideo = this.get('countReportsForReporter__deletedVideo') as number
- const countReportsForReporteeVideo = this.get('countReportsForReportee__video') as number
- const countReportsForReporteeDeletedVideo = this.get('countReportsForReportee__deletedVideo') as number
-
- let video: VideoAbuse
-
- if (this.VideoAbuse) {
- const abuseModel = this.VideoAbuse
- const entity = abuseModel.Video || abuseModel.deletedVideo
-
- video = {
- id: entity.id,
- uuid: entity.uuid,
- name: entity.name,
- nsfw: entity.nsfw,
-
- startAt: abuseModel.startAt,
- endAt: abuseModel.endAt,
-
- deleted: !abuseModel.Video,
- blacklisted: abuseModel.Video?.isBlacklisted() || false,
- thumbnailPath: abuseModel.Video?.getMiniatureStaticPath(),
- channel: abuseModel.Video?.VideoChannel.toFormattedJSON() || abuseModel.deletedVideo?.channel
+ static async listForUserApi (parameters: {
+ user: MUserAccountId
+
+ start: number
+ count: number
+ sort: string
+
+ id?: number
+ search?: string
+ state?: AbuseState
+ }) {
+ const {
+ start,
+ count,
+ sort,
+ search,
+ user,
+ state,
+ id
+ } = parameters
+
+ const queryOptions: BuildAbusesQueryOptions = {
+ start,
+ count,
+ sort,
+ id,
+ search,
+ state,
+ reporterAccountId: user.Account.id
+ }
+
+ const [ total, data ] = await Promise.all([
+ AbuseModel.internalCountForApi(queryOptions),
+ AbuseModel.internalListForApi(queryOptions)
+ ])
+
+ return { total, data }
+ }
+
+ buildBaseVideoCommentAbuse (this: MAbuseUserFormattable) {
+ if (!this.VideoCommentAbuse) return null
+
+ const abuseModel = this.VideoCommentAbuse
+ const entity = abuseModel.VideoComment
+
+ return {
+ id: entity.id,
+ threadId: entity.getThreadId(),
+
+ text: entity.text ?? '',
+
+ deleted: entity.isDeleted(),
+
+ video: {
+ id: entity.Video.id,
+ name: entity.Video.name,
+ uuid: entity.Video.uuid
}
}
+ }
+
+ buildBaseVideoAbuse (this: MAbuseUserFormattable): UserVideoAbuse {
+ if (!this.VideoAbuse) return null
+
+ const abuseModel = this.VideoAbuse
+ const entity = abuseModel.Video || abuseModel.deletedVideo
+
+ return {
+ id: entity.id,
+ uuid: entity.uuid,
+ name: entity.name,
+ nsfw: entity.nsfw,
+
+ startAt: abuseModel.startAt,
+ endAt: abuseModel.endAt,
+
+ deleted: !abuseModel.Video,
+ blacklisted: abuseModel.Video?.isBlacklisted() || false,
+ thumbnailPath: abuseModel.Video?.getMiniatureStaticPath(),
+
+ channel: abuseModel.Video?.VideoChannel.toFormattedJSON() || abuseModel.deletedVideo?.channel
+ }
+ }
+
+ buildBaseAbuse (this: MAbuseUserFormattable, countMessages: number): UserAbuse {
+ const predefinedReasons = AbuseModel.getPredefinedReasonsStrings(this.predefinedReasons)
return {
id: this.id,
reason: this.reason,
predefinedReasons,
- reporterAccount: this.ReporterAccount.toFormattedJSON(),
+ flaggedAccount: this.FlaggedAccount
+ ? this.FlaggedAccount.toFormattedJSON()
+ : null,
state: {
id: this.state,
label: AbuseModel.getStateLabel(this.state)
},
- moderationComment: this.moderationComment,
-
- video,
- comment: null,
+ countMessages,
createdAt: this.createdAt,
- updatedAt: this.updatedAt,
- count: countReportsForVideo || 0,
- nth: nthReportForVideo || 0,
- countReportsForReporter: (countReportsForReporterVideo || 0) + (countReportsForReporterDeletedVideo || 0),
- countReportsForReportee: (countReportsForReporteeVideo || 0) + (countReportsForReporteeDeletedVideo || 0),
-
- // FIXME: deprecated in 2.3, remove this
- startAt: null,
- endAt: null
+ updatedAt: this.updatedAt
}
}
+ toFormattedAdminJSON (this: MAbuseAdminFormattable): AdminAbuse {
+ const countReportsForVideo = this.get('countReportsForVideo') as number
+ const nthReportForVideo = this.get('nthReportForVideo') as number
+
+ const countReportsForReporter = this.get('countReportsForReporter') as number
+ const countReportsForReportee = this.get('countReportsForReportee') as number
+
+ const countMessages = this.get('countMessages') as number
+
+ const baseVideo = this.buildBaseVideoAbuse()
+ const video: AdminVideoAbuse = baseVideo
+ ? Object.assign(baseVideo, {
+ countReports: countReportsForVideo,
+ nthReport: nthReportForVideo
+ })
+ : null
+
+ const comment: AdminVideoCommentAbuse = this.buildBaseVideoCommentAbuse()
+
+ const abuse = this.buildBaseAbuse(countMessages || 0)
+
+ return Object.assign(abuse, {
+ video,
+ comment,
+
+ moderationComment: this.moderationComment,
+
+ reporterAccount: this.ReporterAccount
+ ? this.ReporterAccount.toFormattedJSON()
+ : null,
+
+ countReportsForReporter: (countReportsForReporter || 0),
+ countReportsForReportee: (countReportsForReportee || 0)
+ })
+ }
+
+ toFormattedUserJSON (this: MAbuseUserFormattable): UserAbuse {
+ const countMessages = this.get('countMessages') as number
+
+ const video = this.buildBaseVideoAbuse()
+ const comment = this.buildBaseVideoCommentAbuse()
+ const abuse = this.buildBaseAbuse(countMessages || 0)
+
+ return Object.assign(abuse, {
+ video,
+ comment
+ })
+ }
+
toActivityPubObject (this: MAbuseAP): AbuseObject {
const predefinedReasons = AbuseModel.getPredefinedReasonsStrings(this.predefinedReasons)
}
}
+ private static async internalCountForApi (parameters: BuildAbusesQueryOptions) {
+ const { query, replacements } = buildAbuseListQuery(parameters, 'count')
+ const options = {
+ type: QueryTypes.SELECT as QueryTypes.SELECT,
+ replacements
+ }
+
+ const [ { total } ] = await AbuseModel.sequelize.query<{ total: string }>(query, options)
+ if (total === null) return 0
+
+ return parseInt(total, 10)
+ }
+
+ private static async internalListForApi (parameters: BuildAbusesQueryOptions) {
+ const { query, replacements } = buildAbuseListQuery(parameters, 'id')
+ const options = {
+ type: QueryTypes.SELECT as QueryTypes.SELECT,
+ replacements
+ }
+
+ const rows = await AbuseModel.sequelize.query<{ id: string }>(query, options)
+ const ids = rows.map(r => r.id)
+
+ if (ids.length === 0) return []
+
+ return AbuseModel.scope(ScopeNames.FOR_API)
+ .findAll({
+ order: getSort(parameters.sort),
+ where: {
+ id: {
+ [Op.in]: ids
+ }
+ }
+ })
+ }
+
private static getStateLabel (id: number) {
return ABUSE_STATES[id] || 'Unknown'
}
private static getPredefinedReasonsStrings (predefinedReasons: AbusePredefinedReasons[]): AbusePredefinedReasonsString[] {
+ const invertedPredefinedReasons = invert(abusePredefinedReasonsMap)
+
return (predefinedReasons || [])
- .filter(r => r in AbusePredefinedReasons)
- .map(r => invert(abusePredefinedReasonsMap)[r] as AbusePredefinedReasonsString)
+ .map(r => invertedPredefinedReasons[r] as AbusePredefinedReasonsString)
+ .filter(v => !!v)
}
}
projects / github / Chocobozzz / PeerTube.git / blobdiff