From 811cef146c841ef8530bc812c05dfee77e0f2998 Mon Sep 17 00:00:00 2001 From: Chocobozzz Date: Tue, 7 Jul 2020 17:18:26 +0200 Subject: Use raw sql for abuses --- server/models/abuse/abuse-query-builder.ts | 154 ++++++++++++++++++++++ server/models/abuse/abuse.ts | 198 +++++++++-------------------- server/models/video/video-query-builder.ts | 4 +- 3 files changed, 219 insertions(+), 137 deletions(-) create mode 100644 server/models/abuse/abuse-query-builder.ts (limited to 'server/models') 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 @@ + +import { exists } from '@server/helpers/custom-validators/misc' +import { AbuseFilter, AbuseState, AbuseVideoIs } from '@shared/models' +import { buildBlockedAccountSQL, buildDirectionAndField } from '../utils' + +export type BuildAbusesQueryOptions = { + start: number + count: number + sort: string + + // 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 +} + +function buildAbuseListQuery (options: BuildAbusesQueryOptions, type: 'count' | 'id') { + const whereAnd: string[] = [] + const replacements: any = {} + + const joins = [ + 'LEFT JOIN "videoAbuse" ON "videoAbuse"."abuseId" = "abuse"."id"', + 'LEFT JOIN "video" ON "videoAbuse"."videoId" = "video"."id"', + 'LEFT JOIN "videoBlacklist" ON "videoBlacklist"."videoId" = "video"."id"', + 'LEFT JOIN "videoChannel" ON "video"."channelId" = "videoChannel"."id"', + 'LEFT JOIN "account" "reporterAccount" ON "reporterAccount"."id" = "abuse"."reporterAccountId"', + 'LEFT JOIN "account" "flaggedAccount" ON "flaggedAccount"."id" = "abuse"."reporterAccountId"', + 'LEFT JOIN "commentAbuse" ON "commentAbuse"."abuseId" = "abuse"."id"', + 'LEFT JOIN "videoComment" ON "commentAbuse"."videoCommentId" = "videoComment"."id"' + ] + + whereAnd.push('"abuse"."reporterAccountId" NOT IN (' + buildBlockedAccountSQL([ options.serverAccountId, options.userAccountId ]) + ')') + + if (options.search) { + const searchWhereOr = [ + '"video"."name" ILIKE :search', + '"videoChannel"."name" ILIKE :search', + `"videoAbuse"."deletedVideo"->>'name' ILIKE :search`, + `"videoAbuse"."deletedVideo"->'channel'->>'displayName' ILIKE :search`, + '"reporterAccount"."name" ILIKE :search', + '"flaggedAccount"."name" ILIKE :search' + ] + + replacements.search = `%${options.search}%` + whereAnd.push('(' + searchWhereOr.join(' OR ') + ')') + } + + if (options.searchVideo) { + whereAnd.push('"video"."name" ILIKE :searchVideo') + replacements.searchVideo = `%${options.searchVideo}%` + } + + if (options.searchVideoChannel) { + whereAnd.push('"videoChannel"."name" ILIKE :searchVideoChannel') + replacements.searchVideoChannel = `%${options.searchVideoChannel}%` + } + + if (options.id) { + whereAnd.push('"abuse"."id" = :id') + replacements.id = options.id + } + + if (options.state) { + whereAnd.push('"abuse"."state" = :state') + replacements.state = options.state + } + + if (options.videoIs === 'deleted') { + whereAnd.push('"videoAbuse"."deletedVideo" IS NOT NULL') + } else if (options.videoIs === 'blacklisted') { + whereAnd.push('"videoBlacklist"."id" IS NOT NULL') + } + + if (options.predefinedReasonId) { + whereAnd.push(':predefinedReasonId = ANY("abuse"."predefinedReasons")') + replacements.predefinedReasonId = options.predefinedReasonId + } + + if (options.filter === 'video') { + whereAnd.push('"videoAbuse"."id" IS NOT NULL') + } else if (options.filter === 'comment') { + whereAnd.push('"commentAbuse"."id" IS NOT NULL') + } else if (options.filter === 'account') { + whereAnd.push('"videoAbuse"."id" IS NULL AND "commentAbuse"."id" IS NULL') + } + + if (options.searchReporter) { + whereAnd.push('"reporterAccount"."name" ILIKE :searchReporter') + replacements.searchReporter = `%${options.searchReporter}%` + } + + if (options.searchReportee) { + whereAnd.push('"flaggedAccount"."name" ILIKE :searchReportee') + replacements.searchReportee = `%${options.searchReportee}%` + } + + const prefix = type === 'count' + ? 'SELECT COUNT("abuse"."id") AS "total"' + : 'SELECT "abuse"."id" ' + + let suffix = '' + if (type !== 'count') { + + if (options.sort) { + const order = buildAbuseOrder(options.sort) + suffix += `${order} ` + } + + if (exists(options.count)) { + const count = parseInt(options.count + '', 10) + suffix += `LIMIT ${count} ` + } + + if (exists(options.start)) { + const start = parseInt(options.start + '', 10) + suffix += `OFFSET ${start} ` + } + } + + const where = whereAnd.length !== 0 + ? `WHERE ${whereAnd.join(' AND ')}` + : '' + + return { + query: `${prefix} FROM "abuse" ${joins.join(' ')} ${where} ${suffix}`, + replacements + } +} + +function buildAbuseOrder (value: string) { + const { direction, field } = buildDirectionAndField(value) + + return `ORDER BY "abuse"."${field}" ${direction}` +} + +export { + buildAbuseListQuery +} 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 @@ import * as Bluebird from 'bluebird' import { invert } from 'lodash' -import { literal, Op, WhereOptions } from 'sequelize' +import { literal, Op, QueryTypes, WhereOptions } from 'sequelize' import { AllowNull, BelongsTo, @@ -32,12 +32,13 @@ import { import { ABUSE_STATES, CONSTRAINTS_FIELDS } from '../../initializers/constants' import { MAbuse, MAbuseAP, MAbuseFormattable, MUserAccountId } from '../../types/models' import { AccountModel, ScopeNames as AccountScopeNames, SummaryOptions as AccountSummaryOptions } from '../account/account' -import { buildBlockedAccountSQL, getSort, searchAttribute, throwIfNotValid } from '../utils' +import { getSort, throwIfNotValid } from '../utils' import { ThumbnailModel } from '../video/thumbnail' import { VideoModel } from '../video/video' import { VideoBlacklistModel } from '../video/video-blacklist' import { ScopeNames as VideoChannelScopeNames, SummaryOptions as ChannelSummaryOptions, VideoChannelModel } from '../video/video-channel' import { VideoCommentModel } from '../video/video-comment' +import { buildAbuseListQuery, BuildAbusesQueryOptions } from './abuse-query-builder' import { VideoAbuseModel } from './video-abuse' import { VideoCommentAbuseModel } from './video-comment-abuse' @@ -46,100 +47,7 @@ export enum ScopeNames { } @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 whereAnd: WhereOptions[] = [] - - whereAnd.push({ - reporterAccountId: { - [Op.notIn]: literal('(' + buildBlockedAccountSQL([ options.serverAccountId, options.userAccountId ]) + ')') - } - }) - - if (options.search) { - const escapedSearch = AbuseModel.sequelize.escape('%' + options.search + '%') - - whereAnd.push({ - [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) whereAnd.push({ id: options.id }) - if (options.state) whereAnd.push({ state: options.state }) - - if (options.videoIs === 'deleted') { - whereAnd.push({ - '$VideoAbuse.deletedVideo$': { - [Op.not]: null - } - }) - } - - if (options.predefinedReasonId) { - whereAnd.push({ - predefinedReasons: { - [Op.contains]: [ options.predefinedReasonId ] - } - }) - } - - if (options.filter === 'account') { - whereAnd.push({ - videoId: null, - commentId: null - }) - } - - const onlyBlacklisted = options.videoIs === 'blacklisted' - const videoRequired = !!(onlyBlacklisted || options.searchVideo || options.searchVideoChannel) - + [ScopeNames.FOR_API]: () => { return { attributes: { include: [ @@ -193,10 +101,13 @@ export enum ScopeNames { }, include: [ { - model: AccountModel.scope(AccountScopeNames.SUMMARY), - as: 'ReporterAccount', - required: !!options.searchReporter, - where: searchAttribute(options.searchReporter, 'name') + model: AccountModel.scope({ + method: [ + AccountScopeNames.SUMMARY, + { actorRequired: false } as AccountSummaryOptions + ] + }), + as: 'ReporterAccount' }, { model: AccountModel.scope({ @@ -205,17 +116,13 @@ export enum ScopeNames { { actorRequired: false } as AccountSummaryOptions ] }), - as: 'FlaggedAccount', - required: !!options.searchReportee, - where: searchAttribute(options.searchReportee, 'name') + as: 'FlaggedAccount' }, { model: VideoCommentAbuseModel.unscoped(), - required: options.filter === 'comment', include: [ { model: VideoCommentModel.unscoped(), - required: false, include: [ { model: VideoModel.unscoped(), @@ -227,13 +134,10 @@ export enum ScopeNames { }, { model: VideoAbuseModel.unscoped(), - required: options.filter === 'video' || !!options.videoIs || videoRequired, include: [ { attributes: [ 'id', 'uuid', 'name', 'nsfw' ], model: VideoModel.unscoped(), - required: videoRequired, - where: searchAttribute(options.searchVideo, 'name'), include: [ { attributes: [ 'filename', 'fileUrl' ], @@ -246,23 +150,18 @@ export enum ScopeNames { { withAccount: false, actorRequired: false } as ChannelSummaryOptions ] }), - - where: searchAttribute(options.searchVideoChannel, 'name'), - required: !!options.searchVideoChannel + required: false }, { attributes: [ 'id', 'reason', 'unfederated' ], - model: VideoBlacklistModel, - required: onlyBlacklisted + required: false, + model: VideoBlacklistModel } ] } ] } - ], - where: { - [Op.and]: whereAnd - } + ] } } })) @@ -386,7 +285,7 @@ export class AbuseModel extends Model { return AbuseModel.findOne(query) } - static listForApi (parameters: { + static async listForApi (parameters: { start: number count: number sort: string @@ -428,15 +327,10 @@ export class AbuseModel extends Model { 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, @@ -451,14 +345,12 @@ export class AbuseModel extends Model { 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 { @@ -573,6 +465,42 @@ export class AbuseModel extends Model { } } + 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' } diff --git a/server/models/video/video-query-builder.ts b/server/models/video/video-query-builder.ts index 984b0e6af..466890364 100644 --- a/server/models/video/video-query-builder.ts +++ b/server/models/video/video-query-builder.ts @@ -327,7 +327,7 @@ function buildListQuery (model: typeof Model, options: BuildVideosQueryOptions) attributes.push('COALESCE("video"."originallyPublishedAt", "video"."publishedAt") AS "publishedAtForOrder"') } - order = buildOrder(model, options.sort) + order = buildOrder(options.sort) suffix += `${order} ` } @@ -357,7 +357,7 @@ function buildListQuery (model: typeof Model, options: BuildVideosQueryOptions) return { query, replacements, order } } -function buildOrder (model: typeof Model, value: string) { +function buildOrder (value: string) { const { direction, field } = buildDirectionAndField(value) if (field.match(/^[a-zA-Z."]+$/) === null) throw new Error('Invalid sort column ' + field) -- cgit v1.2.3