From 7ad9b9846c44d198a736183fb186c2039f5236b5 Mon Sep 17 00:00:00 2001 From: Chocobozzz Date: Fri, 12 Oct 2018 15:26:04 +0200 Subject: Add ability for users to block an account/instance on server side --- server/models/account/account-blocklist.ts | 111 ++++++++++++++++++++++++++ server/models/server/server-blocklist.ts | 121 +++++++++++++++++++++++++++++ server/models/server/server.ts | 6 ++ server/models/utils.ts | 18 +++++ server/models/video/video-comment.ts | 95 +++++++++++++++++----- server/models/video/video.ts | 40 +++++++--- 6 files changed, 360 insertions(+), 31 deletions(-) create mode 100644 server/models/account/account-blocklist.ts create mode 100644 server/models/server/server-blocklist.ts (limited to 'server/models') diff --git a/server/models/account/account-blocklist.ts b/server/models/account/account-blocklist.ts new file mode 100644 index 000000000..bacd122e8 --- /dev/null +++ b/server/models/account/account-blocklist.ts @@ -0,0 +1,111 @@ +import { BelongsTo, Column, CreatedAt, ForeignKey, Model, Scopes, Table, UpdatedAt } from 'sequelize-typescript' +import { AccountModel } from './account' +import { getSort } from '../utils' +import { AccountBlock } from '../../../shared/models/blocklist' + +enum ScopeNames { + WITH_ACCOUNTS = 'WITH_ACCOUNTS' +} + +@Scopes({ + [ScopeNames.WITH_ACCOUNTS]: { + include: [ + { + model: () => AccountModel, + required: true, + as: 'ByAccount' + }, + { + model: () => AccountModel, + required: true, + as: 'AccountBlocked' + } + ] + } +}) + +@Table({ + tableName: 'accountBlocklist', + indexes: [ + { + fields: [ 'accountId', 'targetAccountId' ], + unique: true + }, + { + fields: [ 'targetAccountId' ] + } + ] +}) +export class AccountBlocklistModel extends Model { + + @CreatedAt + createdAt: Date + + @UpdatedAt + updatedAt: Date + + @ForeignKey(() => AccountModel) + @Column + accountId: number + + @BelongsTo(() => AccountModel, { + foreignKey: { + name: 'accountId', + allowNull: false + }, + as: 'ByAccount', + onDelete: 'CASCADE' + }) + ByAccount: AccountModel + + @ForeignKey(() => AccountModel) + @Column + targetAccountId: number + + @BelongsTo(() => AccountModel, { + foreignKey: { + name: 'targetAccountId', + allowNull: false + }, + as: 'AccountBlocked', + onDelete: 'CASCADE' + }) + AccountBlocked: AccountModel + + static loadByAccountAndTarget (accountId: number, targetAccountId: number) { + const query = { + where: { + accountId, + targetAccountId + } + } + + return AccountBlocklistModel.findOne(query) + } + + static listForApi (accountId: number, start: number, count: number, sort: string) { + const query = { + offset: start, + limit: count, + order: getSort(sort), + where: { + accountId + } + } + + return AccountBlocklistModel + .scope([ ScopeNames.WITH_ACCOUNTS ]) + .findAndCountAll(query) + .then(({ rows, count }) => { + return { total: count, data: rows } + }) + } + + toFormattedJSON (): AccountBlock { + return { + byAccount: this.ByAccount.toFormattedJSON(), + accountBlocked: this.AccountBlocked.toFormattedJSON(), + createdAt: this.createdAt + } + } +} diff --git a/server/models/server/server-blocklist.ts b/server/models/server/server-blocklist.ts new file mode 100644 index 000000000..705ed2c6b --- /dev/null +++ b/server/models/server/server-blocklist.ts @@ -0,0 +1,121 @@ +import { BelongsTo, Column, CreatedAt, ForeignKey, Model, Scopes, Table, UpdatedAt } from 'sequelize-typescript' +import { AccountModel } from '../account/account' +import { ServerModel } from './server' +import { ServerBlock } from '../../../shared/models/blocklist' +import { getSort } from '../utils' + +enum ScopeNames { + WITH_ACCOUNT = 'WITH_ACCOUNT', + WITH_SERVER = 'WITH_SERVER' +} + +@Scopes({ + [ScopeNames.WITH_ACCOUNT]: { + include: [ + { + model: () => AccountModel, + required: true + } + ] + }, + [ScopeNames.WITH_SERVER]: { + include: [ + { + model: () => ServerModel, + required: true + } + ] + } +}) + +@Table({ + tableName: 'serverBlocklist', + indexes: [ + { + fields: [ 'accountId', 'targetServerId' ], + unique: true + }, + { + fields: [ 'targetServerId' ] + } + ] +}) +export class ServerBlocklistModel extends Model { + + @CreatedAt + createdAt: Date + + @UpdatedAt + updatedAt: Date + + @ForeignKey(() => AccountModel) + @Column + accountId: number + + @BelongsTo(() => AccountModel, { + foreignKey: { + name: 'accountId', + allowNull: false + }, + onDelete: 'CASCADE' + }) + ByAccount: AccountModel + + @ForeignKey(() => ServerModel) + @Column + targetServerId: number + + @BelongsTo(() => ServerModel, { + foreignKey: { + name: 'targetServerId', + allowNull: false + }, + onDelete: 'CASCADE' + }) + ServerBlocked: ServerModel + + static loadByAccountAndHost (accountId: number, host: string) { + const query = { + where: { + accountId + }, + include: [ + { + model: ServerModel, + where: { + host + }, + required: true + } + ] + } + + return ServerBlocklistModel.findOne(query) + } + + static listForApi (accountId: number, start: number, count: number, sort: string) { + const query = { + offset: start, + limit: count, + order: getSort(sort), + where: { + accountId + } + } + + return ServerBlocklistModel + .scope([ ScopeNames.WITH_ACCOUNT, ScopeNames.WITH_SERVER ]) + .findAndCountAll(query) + .then(({ rows, count }) => { + return { total: count, data: rows } + }) + } + + toFormattedJSON (): ServerBlock { + return { + byAccount: this.ByAccount.toFormattedJSON(), + serverBlocked: this.ServerBlocked.toFormattedJSON(), + createdAt: this.createdAt + } + } +} diff --git a/server/models/server/server.ts b/server/models/server/server.ts index ca3b24d51..300d70938 100644 --- a/server/models/server/server.ts +++ b/server/models/server/server.ts @@ -49,4 +49,10 @@ export class ServerModel extends Model { return ServerModel.findOne(query) } + + toFormattedJSON () { + return { + host: this.host + } + } } diff --git a/server/models/utils.ts b/server/models/utils.ts index e0bf091ad..50c865e75 100644 --- a/server/models/utils.ts +++ b/server/models/utils.ts @@ -64,9 +64,27 @@ function createSimilarityAttribute (col: string, value: string) { ) } +function buildBlockedAccountSQL (serverAccountId: number, userAccountId?: number) { + const blockerIds = [ serverAccountId ] + if (userAccountId) blockerIds.push(userAccountId) + + const blockerIdsString = blockerIds.join(', ') + + const query = 'SELECT "targetAccountId" AS "id" FROM "accountBlocklist" WHERE "accountId" IN (' + blockerIdsString + ')' + + ' UNION ALL ' + + // 'SELECT "accountId" FROM "accountBlocklist" WHERE "targetAccountId" = user.account.id + // UNION ALL + 'SELECT "account"."id" AS "id" FROM account INNER JOIN "actor" ON account."actorId" = actor.id ' + + 'INNER JOIN "serverBlocklist" ON "actor"."serverId" = "serverBlocklist"."targetServerId" ' + + 'WHERE "serverBlocklist"."accountId" IN (' + blockerIdsString + ')' + + return query +} + // --------------------------------------------------------------------------- export { + buildBlockedAccountSQL, SortType, getSort, getVideoSort, diff --git a/server/models/video/video-comment.ts b/server/models/video/video-comment.ts index f84c1880c..08c6b3ff0 100644 --- a/server/models/video/video-comment.ts +++ b/server/models/video/video-comment.ts @@ -1,6 +1,17 @@ import * as Sequelize from 'sequelize' import { - AllowNull, BeforeDestroy, BelongsTo, Column, CreatedAt, DataType, ForeignKey, IFindOptions, Is, Model, Scopes, Table, + AllowNull, + BeforeDestroy, + BelongsTo, + Column, + CreatedAt, + DataType, + ForeignKey, + IFindOptions, + Is, + Model, + Scopes, + Table, UpdatedAt } from 'sequelize-typescript' import { ActivityTagObject } from '../../../shared/models/activitypub/objects/common-objects' @@ -13,9 +24,11 @@ import { AccountModel } from '../account/account' import { ActorModel } from '../activitypub/actor' import { AvatarModel } from '../avatar/avatar' import { ServerModel } from '../server/server' -import { getSort, throwIfNotValid } from '../utils' +import { buildBlockedAccountSQL, getSort, throwIfNotValid } from '../utils' import { VideoModel } from './video' import { VideoChannelModel } from './video-channel' +import { getServerActor } from '../../helpers/utils' +import { UserModel } from '../account/user' enum ScopeNames { WITH_ACCOUNT = 'WITH_ACCOUNT', @@ -25,18 +38,29 @@ enum ScopeNames { } @Scopes({ - [ScopeNames.ATTRIBUTES_FOR_API]: { - attributes: { - include: [ - [ - Sequelize.literal( - '(SELECT COUNT("replies"."id") ' + - 'FROM "videoComment" AS "replies" ' + - 'WHERE "replies"."originCommentId" = "VideoCommentModel"."id")' - ), - 'totalReplies' + [ScopeNames.ATTRIBUTES_FOR_API]: (serverAccountId: number, userAccountId?: number) => { + return { + attributes: { + include: [ + [ + Sequelize.literal( + '(' + + 'WITH "blocklist" AS (' + buildBlockedAccountSQL(serverAccountId, userAccountId) + ')' + + 'SELECT COUNT("replies"."id") - (' + + 'SELECT COUNT("replies"."id") ' + + 'FROM "videoComment" AS "replies" ' + + 'WHERE "replies"."originCommentId" = "VideoCommentModel"."id" ' + + 'AND "accountId" IN (SELECT "id" FROM "blocklist")' + + ')' + + 'FROM "videoComment" AS "replies" ' + + 'WHERE "replies"."originCommentId" = "VideoCommentModel"."id" ' + + 'AND "accountId" NOT IN (SELECT "id" FROM "blocklist")' + + ')' + ), + 'totalReplies' + ] ] - ] + } } }, [ScopeNames.WITH_ACCOUNT]: { @@ -267,26 +291,47 @@ export class VideoCommentModel extends Model { return VideoCommentModel.scope([ ScopeNames.WITH_IN_REPLY_TO, ScopeNames.WITH_VIDEO ]).findOne(query) } - static listThreadsForApi (videoId: number, start: number, count: number, sort: string) { + static async listThreadsForApi (videoId: number, start: number, count: number, sort: string, user?: UserModel) { + const serverActor = await getServerActor() + const serverAccountId = serverActor.Account.id + const userAccountId = user.Account.id + const query = { offset: start, limit: count, order: getSort(sort), where: { videoId, - inReplyToCommentId: null + inReplyToCommentId: null, + accountId: { + [Sequelize.Op.notIn]: Sequelize.literal( + '(' + buildBlockedAccountSQL(serverAccountId, userAccountId) + ')' + ) + } } } + // FIXME: typings + const scopes: any[] = [ + ScopeNames.WITH_ACCOUNT, + { + method: [ ScopeNames.ATTRIBUTES_FOR_API, serverAccountId, userAccountId ] + } + ] + return VideoCommentModel - .scope([ ScopeNames.WITH_ACCOUNT, ScopeNames.ATTRIBUTES_FOR_API ]) + .scope(scopes) .findAndCountAll(query) .then(({ rows, count }) => { return { total: count, data: rows } }) } - static listThreadCommentsForApi (videoId: number, threadId: number) { + static async listThreadCommentsForApi (videoId: number, threadId: number, user?: UserModel) { + const serverActor = await getServerActor() + const serverAccountId = serverActor.Account.id + const userAccountId = user.Account.id + const query = { order: [ [ 'createdAt', 'ASC' ], [ 'updatedAt', 'ASC' ] ], where: { @@ -294,12 +339,24 @@ export class VideoCommentModel extends Model { [ Sequelize.Op.or ]: [ { id: threadId }, { originCommentId: threadId } - ] + ], + accountId: { + [Sequelize.Op.notIn]: Sequelize.literal( + '(' + buildBlockedAccountSQL(serverAccountId, userAccountId) + ')' + ) + } } } + const scopes: any[] = [ + ScopeNames.WITH_ACCOUNT, + { + method: [ ScopeNames.ATTRIBUTES_FOR_API, serverAccountId, userAccountId ] + } + ] + return VideoCommentModel - .scope([ ScopeNames.WITH_ACCOUNT, ScopeNames.ATTRIBUTES_FOR_API ]) + .scope(scopes) .findAndCountAll(query) .then(({ rows, count }) => { return { total: count, data: rows } diff --git a/server/models/video/video.ts b/server/models/video/video.ts index 4f3f75613..eab99cba7 100644 --- a/server/models/video/video.ts +++ b/server/models/video/video.ts @@ -27,7 +27,7 @@ import { Table, UpdatedAt } from 'sequelize-typescript' -import { VideoPrivacy, VideoState } from '../../../shared' +import { UserRight, VideoPrivacy, VideoState } from '../../../shared' import { VideoTorrentObject } from '../../../shared/models/activitypub/objects' import { Video, VideoDetails, VideoFile } from '../../../shared/models/videos' import { VideoFilter } from '../../../shared/models/videos/video-query.type' @@ -70,7 +70,7 @@ import { AccountVideoRateModel } from '../account/account-video-rate' import { ActorModel } from '../activitypub/actor' import { AvatarModel } from '../avatar/avatar' import { ServerModel } from '../server/server' -import { buildTrigramSearchIndex, createSimilarityAttribute, getVideoSort, throwIfNotValid } from '../utils' +import { buildBlockedAccountSQL, buildTrigramSearchIndex, createSimilarityAttribute, getVideoSort, throwIfNotValid } from '../utils' import { TagModel } from './tag' import { VideoAbuseModel } from './video-abuse' import { VideoChannelModel } from './video-channel' @@ -93,6 +93,7 @@ import { } from './video-format-utils' import * as validator from 'validator' import { UserVideoHistoryModel } from '../account/user-video-history' +import { UserModel } from '../account/user' // FIXME: Define indexes here because there is an issue with TS and Sequelize.literal when called directly in the annotation const indexes: Sequelize.DefineIndexesOptions[] = [ @@ -138,6 +139,7 @@ type ForAPIOptions = { } type AvailableForListIDsOptions = { + serverAccountId: number actorId: number includeLocalVideos: boolean filter?: VideoFilter @@ -151,6 +153,7 @@ type AvailableForListIDsOptions = { accountId?: number videoChannelId?: number trendingDays?: number + user?: UserModel } @Scopes({ @@ -235,6 +238,15 @@ type AvailableForListIDsOptions = { ) } ] + }, + channelId: { + [ Sequelize.Op.notIn ]: Sequelize.literal( + '(' + + 'SELECT id FROM "videoChannel" WHERE "accountId" IN (' + + buildBlockedAccountSQL(options.serverAccountId, options.user ? options.user.Account.id : undefined) + + ')' + + ')' + ) } }, include: [] @@ -975,10 +987,10 @@ export class VideoModel extends Model { videoChannelId?: number, actorId?: number trendingDays?: number, - userId?: number + user?: UserModel }, countVideos = true) { - if (options.filter && options.filter === 'all-local' && !options.userId) { - throw new Error('Try to filter all-local but no userId is provided') + if (options.filter && options.filter === 'all-local' && !options.user.hasRight(UserRight.SEE_ALL_VIDEOS)) { + throw new Error('Try to filter all-local but no user has not the see all videos right') } const query: IFindOptions = { @@ -994,11 +1006,14 @@ export class VideoModel extends Model { query.group = 'VideoModel.id' } + const serverActor = await getServerActor() + // actorId === null has a meaning, so just check undefined - const actorId = options.actorId !== undefined ? options.actorId : (await getServerActor()).id + const actorId = options.actorId !== undefined ? options.actorId : serverActor.id const queryOptions = { actorId, + serverAccountId: serverActor.Account.id, nsfw: options.nsfw, categoryOneOf: options.categoryOneOf, licenceOneOf: options.licenceOneOf, @@ -1010,7 +1025,7 @@ export class VideoModel extends Model { accountId: options.accountId, videoChannelId: options.videoChannelId, includeLocalVideos: options.includeLocalVideos, - userId: options.userId, + user: options.user, trendingDays } @@ -1033,7 +1048,7 @@ export class VideoModel extends Model { tagsAllOf?: string[] durationMin?: number // seconds durationMax?: number // seconds - userId?: number, + user?: UserModel, filter?: VideoFilter }) { const whereAnd = [] @@ -1104,6 +1119,7 @@ export class VideoModel extends Model { const serverActor = await getServerActor() const queryOptions = { actorId: serverActor.id, + serverAccountId: serverActor.Account.id, includeLocalVideos: options.includeLocalVideos, nsfw: options.nsfw, categoryOneOf: options.categoryOneOf, @@ -1111,7 +1127,7 @@ export class VideoModel extends Model { languageOneOf: options.languageOneOf, tagsOneOf: options.tagsOneOf, tagsAllOf: options.tagsAllOf, - userId: options.userId, + user: options.user, filter: options.filter } @@ -1287,7 +1303,7 @@ export class VideoModel extends Model { private static async getAvailableForApi ( query: IFindOptions, - options: AvailableForListIDsOptions & { userId?: number}, + options: AvailableForListIDsOptions, countVideos = true ) { const idsScope = { @@ -1320,8 +1336,8 @@ export class VideoModel extends Model { } ] - if (options.userId) { - apiScope.push({ method: [ ScopeNames.WITH_USER_HISTORY, options.userId ] }) + if (options.user) { + apiScope.push({ method: [ ScopeNames.WITH_USER_HISTORY, options.user.id ] }) } const secondQuery = { -- cgit v1.2.3