aboutsummaryrefslogtreecommitdiffhomepage
path: root/server/models/abuse
diff options
context:
space:
mode:
authorChocobozzz <me@florianbigard.com>2020-07-07 17:18:26 +0200
committerChocobozzz <chocobozzz@cpy.re>2020-07-10 14:02:41 +0200
commit811cef146c841ef8530bc812c05dfee77e0f2998 (patch)
tree25643c58352f0452433e25c8bc3ed4aa752b019d /server/models/abuse
parent4f32032fed8587ea97d45e235b167e8958efd81f (diff)
downloadPeerTube-811cef146c841ef8530bc812c05dfee77e0f2998.tar.gz
PeerTube-811cef146c841ef8530bc812c05dfee77e0f2998.tar.zst
PeerTube-811cef146c841ef8530bc812c05dfee77e0f2998.zip
Use raw sql for abuses
Diffstat (limited to 'server/models/abuse')
-rw-r--r--server/models/abuse/abuse-query-builder.ts154
-rw-r--r--server/models/abuse/abuse.ts198
2 files changed, 217 insertions, 135 deletions
diff --git a/server/models/abuse/abuse-query-builder.ts b/server/models/abuse/abuse-query-builder.ts
new file mode 100644
index 000000000..5fddcf3c4
--- /dev/null
+++ b/server/models/abuse/abuse-query-builder.ts
@@ -0,0 +1,154 @@
1
2import { exists } from '@server/helpers/custom-validators/misc'
3import { AbuseFilter, AbuseState, AbuseVideoIs } from '@shared/models'
4import { buildBlockedAccountSQL, buildDirectionAndField } from '../utils'
5
6export type BuildAbusesQueryOptions = {
7 start: number
8 count: number
9 sort: string
10
11 // search
12 search?: string
13 searchReporter?: string
14 searchReportee?: string
15
16 // video releated
17 searchVideo?: string
18 searchVideoChannel?: string
19 videoIs?: AbuseVideoIs
20
21 // filters
22 id?: number
23 predefinedReasonId?: number
24 filter?: AbuseFilter
25
26 state?: AbuseState
27
28 // accountIds
29 serverAccountId: number
30 userAccountId: number
31}
32
33function buildAbuseListQuery (options: BuildAbusesQueryOptions, type: 'count' | 'id') {
34 const whereAnd: string[] = []
35 const replacements: any = {}
36
37 const joins = [
38 'LEFT JOIN "videoAbuse" ON "videoAbuse"."abuseId" = "abuse"."id"',
39 'LEFT JOIN "video" ON "videoAbuse"."videoId" = "video"."id"',
40 'LEFT JOIN "videoBlacklist" ON "videoBlacklist"."videoId" = "video"."id"',
41 'LEFT JOIN "videoChannel" ON "video"."channelId" = "videoChannel"."id"',
42 'LEFT JOIN "account" "reporterAccount" ON "reporterAccount"."id" = "abuse"."reporterAccountId"',
43 'LEFT JOIN "account" "flaggedAccount" ON "flaggedAccount"."id" = "abuse"."reporterAccountId"',
44 'LEFT JOIN "commentAbuse" ON "commentAbuse"."abuseId" = "abuse"."id"',
45 'LEFT JOIN "videoComment" ON "commentAbuse"."videoCommentId" = "videoComment"."id"'
46 ]
47
48 whereAnd.push('"abuse"."reporterAccountId" NOT IN (' + buildBlockedAccountSQL([ options.serverAccountId, options.userAccountId ]) + ')')
49
50 if (options.search) {
51 const searchWhereOr = [
52 '"video"."name" ILIKE :search',
53 '"videoChannel"."name" ILIKE :search',
54 `"videoAbuse"."deletedVideo"->>'name' ILIKE :search`,
55 `"videoAbuse"."deletedVideo"->'channel'->>'displayName' ILIKE :search`,
56 '"reporterAccount"."name" ILIKE :search',
57 '"flaggedAccount"."name" ILIKE :search'
58 ]
59
60 replacements.search = `%${options.search}%`
61 whereAnd.push('(' + searchWhereOr.join(' OR ') + ')')
62 }
63
64 if (options.searchVideo) {
65 whereAnd.push('"video"."name" ILIKE :searchVideo')
66 replacements.searchVideo = `%${options.searchVideo}%`
67 }
68
69 if (options.searchVideoChannel) {
70 whereAnd.push('"videoChannel"."name" ILIKE :searchVideoChannel')
71 replacements.searchVideoChannel = `%${options.searchVideoChannel}%`
72 }
73
74 if (options.id) {
75 whereAnd.push('"abuse"."id" = :id')
76 replacements.id = options.id
77 }
78
79 if (options.state) {
80 whereAnd.push('"abuse"."state" = :state')
81 replacements.state = options.state
82 }
83
84 if (options.videoIs === 'deleted') {
85 whereAnd.push('"videoAbuse"."deletedVideo" IS NOT NULL')
86 } else if (options.videoIs === 'blacklisted') {
87 whereAnd.push('"videoBlacklist"."id" IS NOT NULL')
88 }
89
90 if (options.predefinedReasonId) {
91 whereAnd.push(':predefinedReasonId = ANY("abuse"."predefinedReasons")')
92 replacements.predefinedReasonId = options.predefinedReasonId
93 }
94
95 if (options.filter === 'video') {
96 whereAnd.push('"videoAbuse"."id" IS NOT NULL')
97 } else if (options.filter === 'comment') {
98 whereAnd.push('"commentAbuse"."id" IS NOT NULL')
99 } else if (options.filter === 'account') {
100 whereAnd.push('"videoAbuse"."id" IS NULL AND "commentAbuse"."id" IS NULL')
101 }
102
103 if (options.searchReporter) {
104 whereAnd.push('"reporterAccount"."name" ILIKE :searchReporter')
105 replacements.searchReporter = `%${options.searchReporter}%`
106 }
107
108 if (options.searchReportee) {
109 whereAnd.push('"flaggedAccount"."name" ILIKE :searchReportee')
110 replacements.searchReportee = `%${options.searchReportee}%`
111 }
112
113 const prefix = type === 'count'
114 ? 'SELECT COUNT("abuse"."id") AS "total"'
115 : 'SELECT "abuse"."id" '
116
117 let suffix = ''
118 if (type !== 'count') {
119
120 if (options.sort) {
121 const order = buildAbuseOrder(options.sort)
122 suffix += `${order} `
123 }
124
125 if (exists(options.count)) {
126 const count = parseInt(options.count + '', 10)
127 suffix += `LIMIT ${count} `
128 }
129
130 if (exists(options.start)) {
131 const start = parseInt(options.start + '', 10)
132 suffix += `OFFSET ${start} `
133 }
134 }
135
136 const where = whereAnd.length !== 0
137 ? `WHERE ${whereAnd.join(' AND ')}`
138 : ''
139
140 return {
141 query: `${prefix} FROM "abuse" ${joins.join(' ')} ${where} ${suffix}`,
142 replacements
143 }
144}
145
146function buildAbuseOrder (value: string) {
147 const { direction, field } = buildDirectionAndField(value)
148
149 return `ORDER BY "abuse"."${field}" ${direction}`
150}
151
152export {
153 buildAbuseListQuery
154}
diff --git a/server/models/abuse/abuse.ts b/server/models/abuse/abuse.ts
index 9c17c4d51..28ecf8253 100644
--- a/server/models/abuse/abuse.ts
+++ b/server/models/abuse/abuse.ts
@@ -1,6 +1,6 @@
1import * as Bluebird from 'bluebird' 1import * as Bluebird from 'bluebird'
2import { invert } from 'lodash' 2import { invert } from 'lodash'
3import { literal, Op, WhereOptions } from 'sequelize' 3import { literal, Op, QueryTypes, WhereOptions } from 'sequelize'
4import { 4import {
5 AllowNull, 5 AllowNull,
6 BelongsTo, 6 BelongsTo,
@@ -32,12 +32,13 @@ import {
32import { ABUSE_STATES, CONSTRAINTS_FIELDS } from '../../initializers/constants' 32import { ABUSE_STATES, CONSTRAINTS_FIELDS } from '../../initializers/constants'
33import { MAbuse, MAbuseAP, MAbuseFormattable, MUserAccountId } from '../../types/models' 33import { MAbuse, MAbuseAP, MAbuseFormattable, MUserAccountId } from '../../types/models'
34import { AccountModel, ScopeNames as AccountScopeNames, SummaryOptions as AccountSummaryOptions } from '../account/account' 34import { AccountModel, ScopeNames as AccountScopeNames, SummaryOptions as AccountSummaryOptions } from '../account/account'
35import { buildBlockedAccountSQL, getSort, searchAttribute, throwIfNotValid } from '../utils' 35import { getSort, throwIfNotValid } from '../utils'
36import { ThumbnailModel } from '../video/thumbnail' 36import { ThumbnailModel } from '../video/thumbnail'
37import { VideoModel } from '../video/video' 37import { VideoModel } from '../video/video'
38import { VideoBlacklistModel } from '../video/video-blacklist' 38import { VideoBlacklistModel } from '../video/video-blacklist'
39import { ScopeNames as VideoChannelScopeNames, SummaryOptions as ChannelSummaryOptions, VideoChannelModel } from '../video/video-channel' 39import { ScopeNames as VideoChannelScopeNames, SummaryOptions as ChannelSummaryOptions, VideoChannelModel } from '../video/video-channel'
40import { VideoCommentModel } from '../video/video-comment' 40import { VideoCommentModel } from '../video/video-comment'
41import { buildAbuseListQuery, BuildAbusesQueryOptions } from './abuse-query-builder'
41import { VideoAbuseModel } from './video-abuse' 42import { VideoAbuseModel } from './video-abuse'
42import { VideoCommentAbuseModel } from './video-comment-abuse' 43import { VideoCommentAbuseModel } from './video-comment-abuse'
43 44
@@ -46,100 +47,7 @@ export enum ScopeNames {
46} 47}
47 48
48@Scopes(() => ({ 49@Scopes(() => ({
49 [ScopeNames.FOR_API]: (options: { 50 [ScopeNames.FOR_API]: () => {
50 // search
51 search?: string
52 searchReporter?: string
53 searchReportee?: string
54
55 // video releated
56 searchVideo?: string
57 searchVideoChannel?: string
58 videoIs?: AbuseVideoIs
59
60 // filters
61 id?: number
62 predefinedReasonId?: number
63 filter?: AbuseFilter
64
65 state?: AbuseState
66
67 // accountIds
68 serverAccountId: number
69 userAccountId: number
70 }) => {
71 const whereAnd: WhereOptions[] = []
72
73 whereAnd.push({
74 reporterAccountId: {
75 [Op.notIn]: literal('(' + buildBlockedAccountSQL([ options.serverAccountId, options.userAccountId ]) + ')')
76 }
77 })
78
79 if (options.search) {
80 const escapedSearch = AbuseModel.sequelize.escape('%' + options.search + '%')
81
82 whereAnd.push({
83 [Op.or]: [
84 {
85 [Op.and]: [
86 { '$VideoAbuse.videoId$': { [Op.not]: null } },
87 searchAttribute(options.search, '$VideoAbuse.Video.name$')
88 ]
89 },
90 {
91 [Op.and]: [
92 { '$VideoAbuse.videoId$': { [Op.not]: null } },
93 searchAttribute(options.search, '$VideoAbuse.Video.VideoChannel.name$')
94 ]
95 },
96 {
97 [Op.and]: [
98 { '$VideoAbuse.deletedVideo$': { [Op.not]: null } },
99 literal(`"VideoAbuse"."deletedVideo"->>'name' ILIKE ${escapedSearch}`)
100 ]
101 },
102 {
103 [Op.and]: [
104 { '$VideoAbuse.deletedVideo$': { [Op.not]: null } },
105 literal(`"VideoAbuse"."deletedVideo"->'channel'->>'displayName' ILIKE ${escapedSearch}`)
106 ]
107 },
108 searchAttribute(options.search, '$ReporterAccount.name$'),
109 searchAttribute(options.search, '$FlaggedAccount.name$')
110 ]
111 })
112 }
113
114 if (options.id) whereAnd.push({ id: options.id })
115 if (options.state) whereAnd.push({ state: options.state })
116
117 if (options.videoIs === 'deleted') {
118 whereAnd.push({
119 '$VideoAbuse.deletedVideo$': {
120 [Op.not]: null
121 }
122 })
123 }
124
125 if (options.predefinedReasonId) {
126 whereAnd.push({
127 predefinedReasons: {
128 [Op.contains]: [ options.predefinedReasonId ]
129 }
130 })
131 }
132
133 if (options.filter === 'account') {
134 whereAnd.push({
135 videoId: null,
136 commentId: null
137 })
138 }
139
140 const onlyBlacklisted = options.videoIs === 'blacklisted'
141 const videoRequired = !!(onlyBlacklisted || options.searchVideo || options.searchVideoChannel)
142
143 return { 51 return {
144 attributes: { 52 attributes: {
145 include: [ 53 include: [
@@ -193,10 +101,13 @@ export enum ScopeNames {
193 }, 101 },
194 include: [ 102 include: [
195 { 103 {
196 model: AccountModel.scope(AccountScopeNames.SUMMARY), 104 model: AccountModel.scope({
197 as: 'ReporterAccount', 105 method: [
198 required: !!options.searchReporter, 106 AccountScopeNames.SUMMARY,
199 where: searchAttribute(options.searchReporter, 'name') 107 { actorRequired: false } as AccountSummaryOptions
108 ]
109 }),
110 as: 'ReporterAccount'
200 }, 111 },
201 { 112 {
202 model: AccountModel.scope({ 113 model: AccountModel.scope({
@@ -205,17 +116,13 @@ export enum ScopeNames {
205 { actorRequired: false } as AccountSummaryOptions 116 { actorRequired: false } as AccountSummaryOptions
206 ] 117 ]
207 }), 118 }),
208 as: 'FlaggedAccount', 119 as: 'FlaggedAccount'
209 required: !!options.searchReportee,
210 where: searchAttribute(options.searchReportee, 'name')
211 }, 120 },
212 { 121 {
213 model: VideoCommentAbuseModel.unscoped(), 122 model: VideoCommentAbuseModel.unscoped(),
214 required: options.filter === 'comment',
215 include: [ 123 include: [
216 { 124 {
217 model: VideoCommentModel.unscoped(), 125 model: VideoCommentModel.unscoped(),
218 required: false,
219 include: [ 126 include: [
220 { 127 {
221 model: VideoModel.unscoped(), 128 model: VideoModel.unscoped(),
@@ -227,13 +134,10 @@ export enum ScopeNames {
227 }, 134 },
228 { 135 {
229 model: VideoAbuseModel.unscoped(), 136 model: VideoAbuseModel.unscoped(),
230 required: options.filter === 'video' || !!options.videoIs || videoRequired,
231 include: [ 137 include: [
232 { 138 {
233 attributes: [ 'id', 'uuid', 'name', 'nsfw' ], 139 attributes: [ 'id', 'uuid', 'name', 'nsfw' ],
234 model: VideoModel.unscoped(), 140 model: VideoModel.unscoped(),
235 required: videoRequired,
236 where: searchAttribute(options.searchVideo, 'name'),
237 include: [ 141 include: [
238 { 142 {
239 attributes: [ 'filename', 'fileUrl' ], 143 attributes: [ 'filename', 'fileUrl' ],
@@ -246,23 +150,18 @@ export enum ScopeNames {
246 { withAccount: false, actorRequired: false } as ChannelSummaryOptions 150 { withAccount: false, actorRequired: false } as ChannelSummaryOptions
247 ] 151 ]
248 }), 152 }),
249 153 required: false
250 where: searchAttribute(options.searchVideoChannel, 'name'),
251 required: !!options.searchVideoChannel
252 }, 154 },
253 { 155 {
254 attributes: [ 'id', 'reason', 'unfederated' ], 156 attributes: [ 'id', 'reason', 'unfederated' ],
255 model: VideoBlacklistModel, 157 required: false,
256 required: onlyBlacklisted 158 model: VideoBlacklistModel
257 } 159 }
258 ] 160 ]
259 } 161 }
260 ] 162 ]
261 } 163 }
262 ], 164 ]
263 where: {
264 [Op.and]: whereAnd
265 }
266 } 165 }
267 } 166 }
268})) 167}))
@@ -386,7 +285,7 @@ export class AbuseModel extends Model<AbuseModel> {
386 return AbuseModel.findOne(query) 285 return AbuseModel.findOne(query)
387 } 286 }
388 287
389 static listForApi (parameters: { 288 static async listForApi (parameters: {
390 start: number 289 start: number
391 count: number 290 count: number
392 sort: string 291 sort: string
@@ -428,15 +327,10 @@ export class AbuseModel extends Model<AbuseModel> {
428 const userAccountId = user ? user.Account.id : undefined 327 const userAccountId = user ? user.Account.id : undefined
429 const predefinedReasonId = predefinedReason ? abusePredefinedReasonsMap[predefinedReason] : undefined 328 const predefinedReasonId = predefinedReason ? abusePredefinedReasonsMap[predefinedReason] : undefined
430 329
431 const query = { 330 const queryOptions: BuildAbusesQueryOptions = {
432 offset: start, 331 start,
433 limit: count, 332 count,
434 order: getSort(sort), 333 sort,
435 col: 'AbuseModel.id',
436 distinct: true
437 }
438
439 const filters = {
440 id, 334 id,
441 filter, 335 filter,
442 predefinedReasonId, 336 predefinedReasonId,
@@ -451,14 +345,12 @@ export class AbuseModel extends Model<AbuseModel> {
451 userAccountId 345 userAccountId
452 } 346 }
453 347
454 return AbuseModel 348 const [ total, data ] = await Promise.all([
455 .scope([ 349 AbuseModel.internalCountForApi(queryOptions),
456 { method: [ ScopeNames.FOR_API, filters ] } 350 AbuseModel.internalListForApi(queryOptions)
457 ]) 351 ])
458 .findAndCountAll(query) 352
459 .then(({ rows, count }) => { 353 return { total, data }
460 return { total: count, data: rows }
461 })
462 } 354 }
463 355
464 toFormattedJSON (this: MAbuseFormattable): Abuse { 356 toFormattedJSON (this: MAbuseFormattable): Abuse {
@@ -573,6 +465,42 @@ export class AbuseModel extends Model<AbuseModel> {
573 } 465 }
574 } 466 }
575 467
468 private static async internalCountForApi (parameters: BuildAbusesQueryOptions) {
469 const { query, replacements } = buildAbuseListQuery(parameters, 'count')
470 const options = {
471 type: QueryTypes.SELECT as QueryTypes.SELECT,
472 replacements
473 }
474
475 const [ { total } ] = await AbuseModel.sequelize.query<{ total: string }>(query, options)
476 if (total === null) return 0
477
478 return parseInt(total, 10)
479 }
480
481 private static async internalListForApi (parameters: BuildAbusesQueryOptions) {
482 const { query, replacements } = buildAbuseListQuery(parameters, 'id')
483 const options = {
484 type: QueryTypes.SELECT as QueryTypes.SELECT,
485 replacements
486 }
487
488 const rows = await AbuseModel.sequelize.query<{ id: string }>(query, options)
489 const ids = rows.map(r => r.id)
490
491 if (ids.length === 0) return []
492
493 return AbuseModel.scope(ScopeNames.FOR_API)
494 .findAll({
495 order: getSort(parameters.sort),
496 where: {
497 id: {
498 [Op.in]: ids
499 }
500 }
501 })
502 }
503
576 private static getStateLabel (id: number) { 504 private static getStateLabel (id: number) {
577 return ABUSE_STATES[id] || 'Unknown' 505 return ABUSE_STATES[id] || 'Unknown'
578 } 506 }