From 696d83fd1377486dd03cc1bd02a21d9b6ddd9fcd Mon Sep 17 00:00:00 2001 From: Chocobozzz Date: Fri, 22 May 2020 17:06:26 +0200 Subject: Block comments from muted accounts/servers Add better control for users of comments displayed on their videos: * Do not forward comments from muted remote accounts/servers (muted by the current server or by the video owner) * Do not list threads and hide replies (with their children) of accounts/servers muted by the video owner * Hide from RSS comments of muted accounts/servers by video owners Use case: * Try to limit spam propagation in the federation * Add ability for users to automatically hide comments on their videos from undesirable accounts/servers (the comment section belongs to videomakers, so they choose what's posted there) --- server/models/account/account.ts | 26 +++++++++++- server/models/utils.ts | 5 +-- server/models/video/video-abuse.ts | 2 +- server/models/video/video-comment.ts | 76 ++++++++++++++++++++++++++---------- 4 files changed, 83 insertions(+), 26 deletions(-) (limited to 'server/models') diff --git a/server/models/account/account.ts b/server/models/account/account.ts index a0081f259..ad649837a 100644 --- a/server/models/account/account.ts +++ b/server/models/account/account.ts @@ -32,9 +32,10 @@ import { FindOptions, IncludeOptions, Op, Transaction, WhereOptions } from 'sequ import { AccountBlocklistModel } from './account-blocklist' import { ServerBlocklistModel } from '../server/server-blocklist' import { ActorFollowModel } from '../activitypub/actor-follow' -import { MAccountActor, MAccountAP, MAccountDefault, MAccountFormattable, MAccountSummaryFormattable } from '../../typings/models' +import { MAccountActor, MAccountAP, MAccountDefault, MAccountFormattable, MAccountSummaryFormattable, MAccount } from '../../typings/models' import * as Bluebird from 'bluebird' import { ModelCache } from '@server/models/model-cache' +import { VideoModel } from '../video/video' export enum ScopeNames { SUMMARY = 'SUMMARY' @@ -343,6 +344,29 @@ export class AccountModel extends Model { }) } + static loadAccountIdFromVideo (videoId: number): Bluebird { + const query = { + include: [ + { + attributes: [ 'id', 'accountId' ], + model: VideoChannelModel.unscoped(), + required: true, + include: [ + { + attributes: [ 'id', 'channelId' ], + model: VideoModel.unscoped(), + where: { + id: videoId + } + } + ] + } + ] + } + + return AccountModel.findOne(query) + } + static listLocalsForSitemap (sort: string): Bluebird { const query = { attributes: [ ], diff --git a/server/models/utils.ts b/server/models/utils.ts index b2573cd35..88c9b4adb 100644 --- a/server/models/utils.ts +++ b/server/models/utils.ts @@ -136,10 +136,7 @@ function createSimilarityAttribute (col: string, value: string) { ) } -function buildBlockedAccountSQL (serverAccountId: number, userAccountId?: number) { - const blockerIds = [ serverAccountId ] - if (userAccountId) blockerIds.push(userAccountId) - +function buildBlockedAccountSQL (blockerIds: number[]) { const blockerIdsString = blockerIds.join(', ') return 'SELECT "targetAccountId" AS "id" FROM "accountBlocklist" WHERE "accountId" IN (' + blockerIdsString + ')' + diff --git a/server/models/video/video-abuse.ts b/server/models/video/video-abuse.ts index 0844f702d..e0cf50b59 100644 --- a/server/models/video/video-abuse.ts +++ b/server/models/video/video-abuse.ts @@ -57,7 +57,7 @@ export enum ScopeNames { }) => { const where = { reporterAccountId: { - [Op.notIn]: literal('(' + buildBlockedAccountSQL(options.serverAccountId, options.userAccountId) + ')') + [Op.notIn]: literal('(' + buildBlockedAccountSQL([ options.serverAccountId, options.userAccountId ]) + ')') } } diff --git a/server/models/video/video-comment.ts b/server/models/video/video-comment.ts index dfeb1c4e7..ba09522cc 100644 --- a/server/models/video/video-comment.ts +++ b/server/models/video/video-comment.ts @@ -21,7 +21,8 @@ import { MCommentOwnerReplyVideoLight, MCommentOwnerVideo, MCommentOwnerVideoFeed, - MCommentOwnerVideoReply + MCommentOwnerVideoReply, + MVideoImmutable } from '../../typings/models/video' import { AccountModel } from '../account/account' import { ActorModel, unusedActorAttributesForAPI } from '../activitypub/actor' @@ -38,14 +39,14 @@ enum ScopeNames { } @Scopes(() => ({ - [ScopeNames.ATTRIBUTES_FOR_API]: (serverAccountId: number, userAccountId?: number) => { + [ScopeNames.ATTRIBUTES_FOR_API]: (blockerAccountIds: number[]) => { return { attributes: { include: [ [ Sequelize.literal( '(' + - 'WITH "blocklist" AS (' + buildBlockedAccountSQL(serverAccountId, userAccountId) + ')' + + 'WITH "blocklist" AS (' + buildBlockedAccountSQL(blockerAccountIds) + ')' + 'SELECT COUNT("replies"."id") - (' + 'SELECT COUNT("replies"."id") ' + 'FROM "videoComment" AS "replies" ' + @@ -276,16 +277,15 @@ export class VideoCommentModel extends Model { static async listThreadsForApi (parameters: { videoId: number + isVideoOwned: boolean start: number count: number sort: string user?: MUserAccountId }) { - const { videoId, start, count, sort, user } = parameters + const { videoId, isVideoOwned, start, count, sort, user } = parameters - const serverActor = await getServerActor() - const serverAccountId = serverActor.Account.id - const userAccountId = user ? user.Account.id : undefined + const blockerAccountIds = await VideoCommentModel.buildBlockerAccountIds({ videoId, user, isVideoOwned }) const query = { offset: start, @@ -304,7 +304,7 @@ export class VideoCommentModel extends Model { { accountId: { [Op.notIn]: Sequelize.literal( - '(' + buildBlockedAccountSQL(serverAccountId, userAccountId) + ')' + '(' + buildBlockedAccountSQL(blockerAccountIds) + ')' ) } }, @@ -320,7 +320,7 @@ export class VideoCommentModel extends Model { const scopes: (string | ScopeOptions)[] = [ ScopeNames.WITH_ACCOUNT_FOR_API, { - method: [ ScopeNames.ATTRIBUTES_FOR_API, serverAccountId, userAccountId ] + method: [ ScopeNames.ATTRIBUTES_FOR_API, blockerAccountIds ] } ] @@ -334,14 +334,13 @@ export class VideoCommentModel extends Model { static async listThreadCommentsForApi (parameters: { videoId: number + isVideoOwned: boolean threadId: number user?: MUserAccountId }) { - const { videoId, threadId, user } = parameters + const { videoId, threadId, user, isVideoOwned } = parameters - const serverActor = await getServerActor() - const serverAccountId = serverActor.Account.id - const userAccountId = user ? user.Account.id : undefined + const blockerAccountIds = await VideoCommentModel.buildBlockerAccountIds({ videoId, user, isVideoOwned }) const query = { order: [ [ 'createdAt', 'ASC' ], [ 'updatedAt', 'ASC' ] ] as Order, @@ -353,7 +352,7 @@ export class VideoCommentModel extends Model { ], accountId: { [Op.notIn]: Sequelize.literal( - '(' + buildBlockedAccountSQL(serverAccountId, userAccountId) + ')' + '(' + buildBlockedAccountSQL(blockerAccountIds) + ')' ) } } @@ -362,7 +361,7 @@ export class VideoCommentModel extends Model { const scopes: any[] = [ ScopeNames.WITH_ACCOUNT_FOR_API, { - method: [ ScopeNames.ATTRIBUTES_FOR_API, serverAccountId, userAccountId ] + method: [ ScopeNames.ATTRIBUTES_FOR_API, blockerAccountIds ] } ] @@ -399,13 +398,23 @@ export class VideoCommentModel extends Model { .findAll(query) } - static listAndCountByVideoId (videoId: number, start: number, count: number, t?: Transaction, order: 'ASC' | 'DESC' = 'ASC') { + static async listAndCountByVideoForAP (video: MVideoImmutable, start: number, count: number, t?: Transaction) { + const blockerAccountIds = await VideoCommentModel.buildBlockerAccountIds({ + videoId: video.id, + isVideoOwned: video.isOwned() + }) + const query = { - order: [ [ 'createdAt', order ] ] as Order, + order: [ [ 'createdAt', 'ASC' ] ] as Order, offset: start, limit: count, where: { - videoId + videoId: video.id, + accountId: { + [Op.notIn]: Sequelize.literal( + '(' + buildBlockedAccountSQL(blockerAccountIds) + ')' + ) + } }, transaction: t } @@ -424,7 +433,7 @@ export class VideoCommentModel extends Model { deletedAt: null, accountId: { [Op.notIn]: Sequelize.literal( - '(' + buildBlockedAccountSQL(serverActor.Account.id) + ')' + '(' + buildBlockedAccountSQL([ serverActor.Account.id, '"Video->VideoChannel"."accountId"' ]) + ')' ) } }, @@ -435,7 +444,14 @@ export class VideoCommentModel extends Model { required: true, where: { privacy: VideoPrivacy.PUBLIC - } + }, + include: [ + { + attributes: [ 'accountId' ], + model: VideoChannelModel.unscoped(), + required: true + } + ] } ] } @@ -650,4 +666,24 @@ export class VideoCommentModel extends Model { tag } } + + private static async buildBlockerAccountIds (options: { + videoId: number + isVideoOwned: boolean + user?: MUserAccountId + }) { + const { videoId, user, isVideoOwned } = options + + const serverActor = await getServerActor() + const blockerAccountIds = [ serverActor.Account.id ] + + if (user) blockerAccountIds.push(user.Account.id) + + if (isVideoOwned) { + const videoOwnerAccount = await AccountModel.loadAccountIdFromVideo(videoId) + blockerAccountIds.push(videoOwnerAccount.id) + } + + return blockerAccountIds + } } -- cgit v1.2.3