From cde3d90ded5debb24281a444eabb720b721e5600 Mon Sep 17 00:00:00 2001 From: Chocobozzz Date: Thu, 5 Jan 2023 15:31:51 +0100 Subject: Use raw sql for comments --- server/controllers/activitypub/client.ts | 2 +- server/controllers/api/videos/comment.ts | 7 +- server/lib/video-comment.ts | 33 +- server/models/shared/model-builder.ts | 27 +- server/models/utils.ts | 6 +- .../comment/video-comment-list-query-builder.ts | 394 ++++++++++++++++++ .../sql/comment/video-comment-table-attributes.ts | 78 ++++ server/models/video/video-comment.ts | 446 +++++---------------- server/tests/api/videos/video-comments.ts | 3 +- 9 files changed, 621 insertions(+), 375 deletions(-) create mode 100644 server/models/video/sql/comment/video-comment-list-query-builder.ts create mode 100644 server/models/video/sql/comment/video-comment-table-attributes.ts (limited to 'server') diff --git a/server/controllers/activitypub/client.ts b/server/controllers/activitypub/client.ts index 8e064fb5b..def320730 100644 --- a/server/controllers/activitypub/client.ts +++ b/server/controllers/activitypub/client.ts @@ -309,7 +309,7 @@ async function videoCommentsController (req: express.Request, res: express.Respo if (redirectIfNotOwned(video.url, res)) return const handler = async (start: number, count: number) => { - const result = await VideoCommentModel.listAndCountByVideoForAP(video, start, count) + const result = await VideoCommentModel.listAndCountByVideoForAP({ video, start, count }) return { total: result.total, diff --git a/server/controllers/api/videos/comment.ts b/server/controllers/api/videos/comment.ts index 44d64776c..70ca21500 100644 --- a/server/controllers/api/videos/comment.ts +++ b/server/controllers/api/videos/comment.ts @@ -1,4 +1,6 @@ +import { MCommentFormattable } from '@server/types/models' import express from 'express' + import { ResultList, ThreadsResultList, UserRight, VideoCommentCreate } from '../../../../shared/models' import { HttpStatusCode } from '../../../../shared/models/http/http-error-codes' import { VideoCommentThreads } from '../../../../shared/models/videos/comment/video-comment.model' @@ -109,7 +111,7 @@ async function listVideoThreads (req: express.Request, res: express.Response) { const video = res.locals.onlyVideo const user = res.locals.oauth ? res.locals.oauth.token.User : undefined - let resultList: ThreadsResultList + let resultList: ThreadsResultList if (video.commentsEnabled === true) { const apiOptions = await Hooks.wrapObject({ @@ -144,12 +146,11 @@ async function listVideoThreadComments (req: express.Request, res: express.Respo const video = res.locals.onlyVideo const user = res.locals.oauth ? res.locals.oauth.token.User : undefined - let resultList: ResultList + let resultList: ResultList if (video.commentsEnabled === true) { const apiOptions = await Hooks.wrapObject({ videoId: video.id, - isVideoOwned: video.isOwned(), threadId: res.locals.videoCommentThread.id, user }, 'filter:api.video-thread-comments.list.params') diff --git a/server/lib/video-comment.ts b/server/lib/video-comment.ts index 02f160fe8..6eb865f7f 100644 --- a/server/lib/video-comment.ts +++ b/server/lib/video-comment.ts @@ -1,30 +1,41 @@ +import express from 'express' import { cloneDeep } from 'lodash' import * as Sequelize from 'sequelize' -import express from 'express' import { logger } from '@server/helpers/logger' import { sequelizeTypescript } from '@server/initializers/database' import { ResultList } from '../../shared/models' import { VideoCommentThreadTree } from '../../shared/models/videos/comment/video-comment.model' import { VideoCommentModel } from '../models/video/video-comment' -import { MAccountDefault, MComment, MCommentOwnerVideo, MCommentOwnerVideoReply, MVideoFullLight } from '../types/models' +import { + MAccountDefault, + MComment, + MCommentFormattable, + MCommentOwnerVideo, + MCommentOwnerVideoReply, + MVideoFullLight +} from '../types/models' import { sendCreateVideoComment, sendDeleteVideoComment } from './activitypub/send' import { getLocalVideoCommentActivityPubUrl } from './activitypub/url' import { Hooks } from './plugins/hooks' -async function removeComment (videoCommentInstance: MCommentOwnerVideo, req: express.Request, res: express.Response) { - const videoCommentInstanceBefore = cloneDeep(videoCommentInstance) +async function removeComment (commentArg: MComment, req: express.Request, res: express.Response) { + let videoCommentInstanceBefore: MCommentOwnerVideo await sequelizeTypescript.transaction(async t => { - if (videoCommentInstance.isOwned() || videoCommentInstance.Video.isOwned()) { - await sendDeleteVideoComment(videoCommentInstance, t) + const comment = await VideoCommentModel.loadByUrlAndPopulateAccountAndVideo(commentArg.url, t) + + videoCommentInstanceBefore = cloneDeep(comment) + + if (comment.isOwned() || comment.Video.isOwned()) { + await sendDeleteVideoComment(comment, t) } - videoCommentInstance.markAsDeleted() + comment.markAsDeleted() - await videoCommentInstance.save({ transaction: t }) - }) + await comment.save({ transaction: t }) - logger.info('Video comment %d deleted.', videoCommentInstance.id) + logger.info('Video comment %d deleted.', comment.id) + }) Hooks.runAction('action:api.video-comment.deleted', { comment: videoCommentInstanceBefore, req, res }) } @@ -64,7 +75,7 @@ async function createVideoComment (obj: { return savedComment } -function buildFormattedCommentTree (resultList: ResultList): VideoCommentThreadTree { +function buildFormattedCommentTree (resultList: ResultList): VideoCommentThreadTree { // Comments are sorted by id ASC const comments = resultList.data diff --git a/server/models/shared/model-builder.ts b/server/models/shared/model-builder.ts index c015ca4f5..07f7c4038 100644 --- a/server/models/shared/model-builder.ts +++ b/server/models/shared/model-builder.ts @@ -1,7 +1,24 @@ import { isPlainObject } from 'lodash' -import { Model as SequelizeModel, Sequelize } from 'sequelize' +import { Model as SequelizeModel, ModelStatic, Sequelize } from 'sequelize' import { logger } from '@server/helpers/logger' +/** + * + * Build Sequelize models from sequelize raw query (that must use { nest: true } options) + * + * In order to sequelize to correctly build the JSON this class will ingest, + * the columns selected in the raw query should be in the following form: + * * All tables must be Pascal Cased (for example "VideoChannel") + * * Root table must end with `Model` (for example "VideoCommentModel") + * * Joined tables must contain the origin table name + '->JoinedTable'. For example: + * * "Actor" is joined to "Account": "Actor" table must be renamed "Account->Actor" + * * "Account->Actor" is joined to "Server": "Server" table must be renamed to "Account->Actor->Server" + * * Selected columns must be renamed to contain the JSON path: + * * "videoComment"."id": "VideoCommentModel"."id" + * * "Account"."Actor"."Server"."id": "Account.Actor.Server.id" + * * All tables must contain the row id + */ + export class ModelBuilder { private readonly modelRegistry = new Map() @@ -72,18 +89,18 @@ export class ModelBuilder { 'Cannot build model %s that does not exist', this.buildSequelizeModelName(modelName), { existing: this.sequelize.modelManager.all.map(m => m.name) } ) - return undefined + return { created: false, model: null } } - // FIXME: typings - const model = new (Model as any)(json) + const model = Model.build(json, { raw: true, isNewRecord: false }) + this.modelRegistry.set(registryKey, model) return { created: true, model } } private findModelBuilder (modelName: string) { - return this.sequelize.modelManager.getModel(this.buildSequelizeModelName(modelName)) + return this.sequelize.modelManager.getModel(this.buildSequelizeModelName(modelName)) as ModelStatic } private buildSequelizeModelName (modelName: string) { diff --git a/server/models/utils.ts b/server/models/utils.ts index 3476799ce..0b6ac8340 100644 --- a/server/models/utils.ts +++ b/server/models/utils.ts @@ -231,12 +231,12 @@ function parseRowCountResult (result: any) { return 0 } -function createSafeIn (sequelize: Sequelize, stringArr: (string | number)[]) { - return stringArr.map(t => { +function createSafeIn (sequelize: Sequelize, toEscape: (string | number)[], additionalUnescaped: string[] = []) { + return toEscape.map(t => { return t === null ? null : sequelize.escape('' + t) - }).join(', ') + }).concat(additionalUnescaped).join(', ') } function buildLocalAccountIdsIn () { diff --git a/server/models/video/sql/comment/video-comment-list-query-builder.ts b/server/models/video/sql/comment/video-comment-list-query-builder.ts new file mode 100644 index 000000000..f3f6910e1 --- /dev/null +++ b/server/models/video/sql/comment/video-comment-list-query-builder.ts @@ -0,0 +1,394 @@ +import { Model, Sequelize, Transaction } from 'sequelize' +import { AbstractRunQuery, ModelBuilder } from '@server/models/shared' +import { createSafeIn, getCommentSort, parseRowCountResult } from '@server/models/utils' +import { ActorImageType, VideoPrivacy } from '@shared/models' +import { VideoCommentTableAttributes } from './video-comment-table-attributes' + +export interface ListVideoCommentsOptions { + selectType: 'api' | 'feed' | 'comment-only' + + start?: number + count?: number + sort?: string + + videoId?: number + threadId?: number + accountId?: number + videoChannelId?: number + + blockerAccountIds?: number[] + + isThread?: boolean + notDeleted?: boolean + isLocal?: boolean + onLocalVideo?: boolean + onPublicVideo?: boolean + videoAccountOwnerId?: boolean + + search?: string + searchAccount?: string + searchVideo?: string + + includeReplyCounters?: boolean + + transaction?: Transaction +} + +export class VideoCommentListQueryBuilder extends AbstractRunQuery { + private readonly tableAttributes = new VideoCommentTableAttributes() + + private innerQuery: string + + private select = '' + private joins = '' + + private innerSelect = '' + private innerJoins = '' + private innerWhere = '' + + private readonly built = { + cte: false, + accountJoin: false, + videoJoin: false, + videoChannelJoin: false, + avatarJoin: false + } + + constructor ( + protected readonly sequelize: Sequelize, + private readonly options: ListVideoCommentsOptions + ) { + super(sequelize) + } + + async listComments () { + this.buildListQuery() + + const results = await this.runQuery({ nest: true, transaction: this.options.transaction }) + const modelBuilder = new ModelBuilder(this.sequelize) + + return modelBuilder.createModels(results, 'VideoComment') + } + + async countComments () { + this.buildCountQuery() + + const result = await this.runQuery({ transaction: this.options.transaction }) + + return parseRowCountResult(result) + } + + // --------------------------------------------------------------------------- + + private buildListQuery () { + this.buildInnerListQuery() + this.buildListSelect() + + this.query = `${this.select} ` + + `FROM (${this.innerQuery}) AS "VideoCommentModel" ` + + `${this.joins} ` + + `${this.getOrder()} ` + + `${this.getLimit()}` + } + + private buildInnerListQuery () { + this.buildWhere() + this.buildInnerListSelect() + + this.innerQuery = `${this.innerSelect} ` + + `FROM "videoComment" AS "VideoCommentModel" ` + + `${this.innerJoins} ` + + `${this.innerWhere} ` + + `${this.getOrder()} ` + + `${this.getInnerLimit()}` + } + + // --------------------------------------------------------------------------- + + private buildCountQuery () { + this.buildWhere() + + this.query = `SELECT COUNT(*) AS "total" ` + + `FROM "videoComment" AS "VideoCommentModel" ` + + `${this.innerJoins} ` + + `${this.innerWhere}` + } + + // --------------------------------------------------------------------------- + + private buildWhere () { + let where: string[] = [] + + if (this.options.videoId) { + this.replacements.videoId = this.options.videoId + + where.push('"VideoCommentModel"."videoId" = :videoId') + } + + if (this.options.threadId) { + this.replacements.threadId = this.options.threadId + + where.push('("VideoCommentModel"."id" = :threadId OR "VideoCommentModel"."originCommentId" = :threadId)') + } + + if (this.options.accountId) { + this.replacements.accountId = this.options.accountId + + where.push('"VideoCommentModel"."accountId" = :accountId') + } + + if (this.options.videoChannelId) { + this.buildVideoChannelJoin() + + this.replacements.videoChannelId = this.options.videoChannelId + + where.push('"Account->VideoChannel"."id" = :videoChannelId') + } + + if (this.options.blockerAccountIds) { + this.buildVideoChannelJoin() + + where = where.concat(this.getBlockWhere('VideoCommentModel', 'Video->VideoChannel')) + } + + if (this.options.isThread === true) { + where.push('"VideoCommentModel"."inReplyToCommentId" IS NULL') + } + + if (this.options.notDeleted === true) { + where.push('"VideoCommentModel"."deletedAt" IS NULL') + } + + if (this.options.isLocal === true) { + this.buildAccountJoin() + + where.push('"Account->Actor"."serverId" IS NULL') + } else if (this.options.isLocal === false) { + this.buildAccountJoin() + + where.push('"Account->Actor"."serverId" IS NOT NULL') + } + + if (this.options.onLocalVideo === true) { + this.buildVideoJoin() + + where.push('"Video"."remote" IS FALSE') + } else if (this.options.onLocalVideo === false) { + this.buildVideoJoin() + + where.push('"Video"."remote" IS TRUE') + } + + if (this.options.onPublicVideo === true) { + this.buildVideoJoin() + + where.push(`"Video"."privacy" = ${VideoPrivacy.PUBLIC}`) + } + + if (this.options.videoAccountOwnerId) { + this.buildVideoChannelJoin() + + this.replacements.videoAccountOwnerId = this.options.videoAccountOwnerId + + where.push(`"Video->VideoChannel"."accountId" = :videoAccountOwnerId`) + } + + if (this.options.search) { + this.buildVideoJoin() + this.buildAccountJoin() + + const escapedLikeSearch = this.sequelize.escape('%' + this.options.search + '%') + + where.push( + `(` + + `"VideoCommentModel"."text" ILIKE ${escapedLikeSearch} OR ` + + `"Account->Actor"."preferredUsername" ILIKE ${escapedLikeSearch} OR ` + + `"Account"."name" ILIKE ${escapedLikeSearch} OR ` + + `"Video"."name" ILIKE ${escapedLikeSearch} ` + + `)` + ) + } + + if (this.options.searchAccount) { + this.buildAccountJoin() + + const escapedLikeSearch = this.sequelize.escape('%' + this.options.searchAccount + '%') + + where.push( + `(` + + `"Account->Actor"."preferredUsername" ILIKE ${escapedLikeSearch} OR ` + + `"Account"."name" ILIKE ${escapedLikeSearch} ` + + `)` + ) + } + + if (this.options.searchVideo) { + this.buildVideoJoin() + + const escapedLikeSearch = this.sequelize.escape('%' + this.options.searchVideo + '%') + + where.push(`"Video"."name" ILIKE ${escapedLikeSearch}`) + } + + if (where.length !== 0) { + this.innerWhere = `WHERE ${where.join(' AND ')}` + } + } + + private buildAccountJoin () { + if (this.built.accountJoin) return + + this.innerJoins += ' LEFT JOIN "account" "Account" ON "Account"."id" = "VideoCommentModel"."accountId" ' + + 'LEFT JOIN "actor" "Account->Actor" ON "Account->Actor"."id" = "Account"."actorId" ' + + 'LEFT JOIN "server" "Account->Actor->Server" ON "Account->Actor"."serverId" = "Account->Actor->Server"."id" ' + + this.built.accountJoin = true + } + + private buildVideoJoin () { + if (this.built.videoJoin) return + + this.innerJoins += ' LEFT JOIN "video" "Video" ON "Video"."id" = "VideoCommentModel"."videoId" ' + + this.built.videoJoin = true + } + + private buildVideoChannelJoin () { + if (this.built.videoChannelJoin) return + + this.buildVideoJoin() + + this.innerJoins += ' LEFT JOIN "videoChannel" "Video->VideoChannel" ON "Video"."channelId" = "Video->VideoChannel"."id" ' + + this.built.videoChannelJoin = true + } + + private buildAvatarsJoin () { + if (this.options.selectType !== 'api' && this.options.selectType !== 'feed') return '' + if (this.built.avatarJoin) return + + this.joins += `LEFT JOIN "actorImage" "Account->Actor->Avatars" ` + + `ON "VideoCommentModel"."Account.Actor.id" = "Account->Actor->Avatars"."actorId" ` + + `AND "Account->Actor->Avatars"."type" = ${ActorImageType.AVATAR}` + + this.built.avatarJoin = true + } + + // --------------------------------------------------------------------------- + + private buildListSelect () { + const toSelect = [ '"VideoCommentModel".*' ] + + if (this.options.selectType === 'api' || this.options.selectType === 'feed') { + this.buildAvatarsJoin() + + toSelect.push(this.tableAttributes.getAvatarAttributes()) + } + + if (this.options.includeReplyCounters === true) { + toSelect.push(this.getTotalRepliesSelect()) + toSelect.push(this.getAuthorTotalRepliesSelect()) + } + + this.select = this.buildSelect(toSelect) + } + + private buildInnerListSelect () { + let toSelect = [ this.tableAttributes.getVideoCommentAttributes() ] + + if (this.options.selectType === 'api' || this.options.selectType === 'feed') { + this.buildAccountJoin() + this.buildVideoJoin() + + toSelect = toSelect.concat([ + this.tableAttributes.getVideoAttributes(), + this.tableAttributes.getAccountAttributes(), + this.tableAttributes.getActorAttributes(), + this.tableAttributes.getServerAttributes() + ]) + } + + this.innerSelect = this.buildSelect(toSelect) + } + + // --------------------------------------------------------------------------- + + private getBlockWhere (commentTableName: string, channelTableName: string) { + const where: string[] = [] + + const blockerIdsString = createSafeIn( + this.sequelize, + this.options.blockerAccountIds, + [ `"${channelTableName}"."accountId"` ] + ) + + where.push( + `NOT EXISTS (` + + `SELECT 1 FROM "accountBlocklist" ` + + `WHERE "targetAccountId" = "${commentTableName}"."accountId" ` + + `AND "accountId" IN (${blockerIdsString})` + + `)` + ) + + where.push( + `NOT EXISTS (` + + `SELECT 1 FROM "account" ` + + `INNER JOIN "actor" ON account."actorId" = actor.id ` + + `INNER JOIN "serverBlocklist" ON "actor"."serverId" = "serverBlocklist"."targetServerId" ` + + `WHERE "account"."id" = "${commentTableName}"."accountId" ` + + `AND "serverBlocklist"."accountId" IN (${blockerIdsString})` + + `)` + ) + + return where + } + + // --------------------------------------------------------------------------- + + private getTotalRepliesSelect () { + const blockWhereString = this.getBlockWhere('replies', 'videoChannel').join(' AND ') + + return `(` + + `SELECT COUNT("replies"."id") FROM "videoComment" AS "replies" ` + + `LEFT JOIN "video" ON "video"."id" = "replies"."videoId" ` + + `LEFT JOIN "videoChannel" ON "video"."channelId" = "videoChannel"."id" ` + + `WHERE "replies"."originCommentId" = "VideoCommentModel"."id" ` + + `AND "deletedAt" IS NULL ` + + `AND ${blockWhereString} ` + + `) AS "totalReplies"` + } + + private getAuthorTotalRepliesSelect () { + return `(` + + `SELECT COUNT("replies"."id") FROM "videoComment" AS "replies" ` + + `INNER JOIN "video" ON "video"."id" = "replies"."videoId" ` + + `INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" ` + + `WHERE "replies"."originCommentId" = "VideoCommentModel"."id" AND "replies"."accountId" = "videoChannel"."accountId"` + + `) AS "totalRepliesFromVideoAuthor"` + } + + private getOrder () { + if (!this.options.sort) return '' + + const orders = getCommentSort(this.options.sort) + + return 'ORDER BY ' + orders.map(o => `"${o[0]}" ${o[1]}`).join(', ') + } + + private getLimit () { + if (!this.options.count) return '' + + this.replacements.limit = this.options.count + + return `LIMIT :limit ` + } + + private getInnerLimit () { + if (!this.options.count) return '' + + this.replacements.limit = this.options.count + this.replacements.offset = this.options.start || 0 + + return `LIMIT :limit OFFSET :offset ` + } +} diff --git a/server/models/video/sql/comment/video-comment-table-attributes.ts b/server/models/video/sql/comment/video-comment-table-attributes.ts new file mode 100644 index 000000000..cae3c1683 --- /dev/null +++ b/server/models/video/sql/comment/video-comment-table-attributes.ts @@ -0,0 +1,78 @@ +export class VideoCommentTableAttributes { + + getVideoCommentAttributes () { + return [ + '"VideoCommentModel"."id"', + '"VideoCommentModel"."url"', + '"VideoCommentModel"."deletedAt"', + '"VideoCommentModel"."updatedAt"', + '"VideoCommentModel"."createdAt"', + '"VideoCommentModel"."text"', + '"VideoCommentModel"."originCommentId"', + '"VideoCommentModel"."inReplyToCommentId"', + '"VideoCommentModel"."videoId"', + '"VideoCommentModel"."accountId"' + ].join(', ') + } + + getAccountAttributes () { + return [ + `"Account"."id" AS "Account.id"`, + `"Account"."name" AS "Account.name"`, + `"Account"."description" AS "Account.description"`, + `"Account"."createdAt" AS "Account.createdAt"`, + `"Account"."updatedAt" AS "Account.updatedAt"`, + `"Account"."actorId" AS "Account.actorId"`, + `"Account"."userId" AS "Account.userId"`, + `"Account"."applicationId" AS "Account.applicationId"` + ].join(', ') + } + + getVideoAttributes () { + return [ + `"Video"."id" AS "Video.id"`, + `"Video"."uuid" AS "Video.uuid"`, + `"Video"."name" AS "Video.name"` + ].join(', ') + } + + getActorAttributes () { + return [ + `"Account->Actor"."id" AS "Account.Actor.id"`, + `"Account->Actor"."type" AS "Account.Actor.type"`, + `"Account->Actor"."preferredUsername" AS "Account.Actor.preferredUsername"`, + `"Account->Actor"."url" AS "Account.Actor.url"`, + `"Account->Actor"."followersCount" AS "Account.Actor.followersCount"`, + `"Account->Actor"."followingCount" AS "Account.Actor.followingCount"`, + `"Account->Actor"."remoteCreatedAt" AS "Account.Actor.remoteCreatedAt"`, + `"Account->Actor"."serverId" AS "Account.Actor.serverId"`, + `"Account->Actor"."createdAt" AS "Account.Actor.createdAt"`, + `"Account->Actor"."updatedAt" AS "Account.Actor.updatedAt"` + ].join(', ') + } + + getServerAttributes () { + return [ + `"Account->Actor->Server"."id" AS "Account.Actor.Server.id"`, + `"Account->Actor->Server"."host" AS "Account.Actor.Server.host"`, + `"Account->Actor->Server"."redundancyAllowed" AS "Account.Actor.Server.redundancyAllowed"`, + `"Account->Actor->Server"."createdAt" AS "Account.Actor.Server.createdAt"`, + `"Account->Actor->Server"."updatedAt" AS "Account.Actor.Server.updatedAt"` + ].join(', ') + } + + getAvatarAttributes () { + return [ + `"Account->Actor->Avatars"."id" AS "Account.Actor.Avatars.id"`, + `"Account->Actor->Avatars"."filename" AS "Account.Actor.Avatars.filename"`, + `"Account->Actor->Avatars"."height" AS "Account.Actor.Avatars.height"`, + `"Account->Actor->Avatars"."width" AS "Account.Actor.Avatars.width"`, + `"Account->Actor->Avatars"."fileUrl" AS "Account.Actor.Avatars.fileUrl"`, + `"Account->Actor->Avatars"."onDisk" AS "Account.Actor.Avatars.onDisk"`, + `"Account->Actor->Avatars"."type" AS "Account.Actor.Avatars.type"`, + `"Account->Actor->Avatars"."actorId" AS "Account.Actor.Avatars.actorId"`, + `"Account->Actor->Avatars"."createdAt" AS "Account.Actor.Avatars.createdAt"`, + `"Account->Actor->Avatars"."updatedAt" AS "Account.Actor.Avatars.updatedAt"` + ].join(', ') + } +} diff --git a/server/models/video/video-comment.ts b/server/models/video/video-comment.ts index af9614d30..fb9d15e55 100644 --- a/server/models/video/video-comment.ts +++ b/server/models/video/video-comment.ts @@ -1,4 +1,4 @@ -import { FindOptions, Op, Order, QueryTypes, ScopeOptions, Sequelize, Transaction, WhereOptions } from 'sequelize' +import { FindOptions, Op, Order, QueryTypes, Sequelize, Transaction } from 'sequelize' import { AllowNull, BelongsTo, @@ -13,11 +13,9 @@ import { Table, UpdatedAt } from 'sequelize-typescript' -import { exists } from '@server/helpers/custom-validators/misc' import { getServerActor } from '@server/models/application/application' import { MAccount, MAccountId, MUserAccountId } from '@server/types/models' -import { uniqify } from '@shared/core-utils' -import { VideoPrivacy } from '@shared/models' +import { pick, uniqify } from '@shared/core-utils' import { AttributesOnly } from '@shared/typescript-utils' import { ActivityTagObject, ActivityTombstoneObject } from '../../../shared/models/activitypub/objects/common-objects' import { VideoCommentObject } from '../../../shared/models/activitypub/objects/video-comment-object' @@ -41,61 +39,19 @@ import { } from '../../types/models/video' import { VideoCommentAbuseModel } from '../abuse/video-comment-abuse' import { AccountModel } from '../account/account' -import { ActorModel, unusedActorAttributesForAPI } from '../actor/actor' -import { - buildBlockedAccountSQL, - buildBlockedAccountSQLOptimized, - buildLocalAccountIdsIn, - getCommentSort, - searchAttribute, - throwIfNotValid -} from '../utils' +import { ActorModel } from '../actor/actor' +import { buildLocalAccountIdsIn, throwIfNotValid } from '../utils' +import { ListVideoCommentsOptions, VideoCommentListQueryBuilder } from './sql/comment/video-comment-list-query-builder' import { VideoModel } from './video' import { VideoChannelModel } from './video-channel' export enum ScopeNames { WITH_ACCOUNT = 'WITH_ACCOUNT', - WITH_ACCOUNT_FOR_API = 'WITH_ACCOUNT_FOR_API', WITH_IN_REPLY_TO = 'WITH_IN_REPLY_TO', - WITH_VIDEO = 'WITH_VIDEO', - ATTRIBUTES_FOR_API = 'ATTRIBUTES_FOR_API' + WITH_VIDEO = 'WITH_VIDEO' } @Scopes(() => ({ - [ScopeNames.ATTRIBUTES_FOR_API]: (blockerAccountIds: number[]) => { - return { - attributes: { - include: [ - [ - Sequelize.literal( - '(' + - 'WITH "blocklist" AS (' + buildBlockedAccountSQL(blockerAccountIds) + ')' + - 'SELECT COUNT("replies"."id") ' + - 'FROM "videoComment" AS "replies" ' + - 'WHERE "replies"."originCommentId" = "VideoCommentModel"."id" ' + - 'AND "deletedAt" IS NULL ' + - 'AND "accountId" NOT IN (SELECT "id" FROM "blocklist")' + - ')' - ), - 'totalReplies' - ], - [ - Sequelize.literal( - '(' + - 'SELECT COUNT("replies"."id") ' + - 'FROM "videoComment" AS "replies" ' + - 'INNER JOIN "video" ON "video"."id" = "replies"."videoId" ' + - 'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" ' + - 'WHERE "replies"."originCommentId" = "VideoCommentModel"."id" ' + - 'AND "replies"."accountId" = "videoChannel"."accountId"' + - ')' - ), - 'totalRepliesFromVideoAuthor' - ] - ] - } - } as FindOptions - }, [ScopeNames.WITH_ACCOUNT]: { include: [ { @@ -103,22 +59,6 @@ export enum ScopeNames { } ] }, - [ScopeNames.WITH_ACCOUNT_FOR_API]: { - include: [ - { - model: AccountModel.unscoped(), - include: [ - { - attributes: { - exclude: unusedActorAttributesForAPI - }, - model: ActorModel, // Default scope includes avatar and server - required: true - } - ] - } - ] - }, [ScopeNames.WITH_IN_REPLY_TO]: { include: [ { @@ -319,93 +259,19 @@ export class VideoCommentModel extends Model { - return { - offset: start, - limit: count, - order: getCommentSort(sort), - where, - include: [ - { - model: AccountModel.unscoped(), - required: true, - where: whereAccount, - include: [ - { - attributes: { - exclude: unusedActorAttributesForAPI - }, - model: forCount === true - ? ActorModel.unscoped() // Default scope includes avatar and server - : ActorModel, - required: true, - where: whereActor - } - ] - }, - { - model: VideoModel.unscoped(), - required: true, - where: whereVideo - } - ] - } + selectType: 'api', + notDeleted: true } return Promise.all([ - VideoCommentModel.count(getQuery(true)), - VideoCommentModel.findAll(getQuery(false)) - ]).then(([ total, data ]) => ({ total, data })) + new VideoCommentListQueryBuilder(VideoCommentModel.sequelize, queryOptions).listComments(), + new VideoCommentListQueryBuilder(VideoCommentModel.sequelize, queryOptions).countComments() + ]).then(([ rows, count ]) => { + return { total: count, data: rows } + }) } static async listThreadsForApi (parameters: { @@ -416,67 +282,40 @@ export class VideoCommentModel extends Model(), + new VideoCommentListQueryBuilder(VideoCommentModel.sequelize, countOptions).countComments(), + new VideoCommentListQueryBuilder(VideoCommentModel.sequelize, notDeletedCountOptions).countComments() ]).then(([ rows, count, totalNotDeletedComments ]) => { return { total: count, data: rows, totalNotDeletedComments } }) @@ -484,54 +323,29 @@ export class VideoCommentModel extends Model ({ total, data })) + new VideoCommentListQueryBuilder(VideoCommentModel.sequelize, queryOptions).listComments(), + new VideoCommentListQueryBuilder(VideoCommentModel.sequelize, queryOptions).countComments() + ]).then(([ rows, count ]) => { + return { total: count, data: rows } + }) } static listThreadParentComments (comment: MCommentId, t: Transaction, order: 'ASC' | 'DESC' = 'ASC'): Promise { @@ -559,31 +373,31 @@ export class VideoCommentModel extends Model(query) - ]).then(([ total, data ]) => ({ total, data })) + new VideoCommentListQueryBuilder(VideoCommentModel.sequelize, queryOptions).listComments(), + new VideoCommentListQueryBuilder(VideoCommentModel.sequelize, queryOptions).countComments() + ]).then(([ rows, count ]) => { + return { total: count, data: rows } + }) } static async listForFeed (parameters: { @@ -592,97 +406,36 @@ export class VideoCommentModel extends Model { - const serverActor = await getServerActor() - const { start, count, videoId, accountId, videoChannelId } = parameters + }) { + const blockerAccountIds = await VideoCommentModel.buildBlockerAccountIds({ user: null }) - const whereAnd: WhereOptions[] = buildBlockedAccountSQLOptimized( - '"VideoCommentModel"."accountId"', - [ serverActor.Account.id, '"Video->VideoChannel"."accountId"' ] - ) + const queryOptions: ListVideoCommentsOptions = { + ...pick(parameters, [ 'start', 'count', 'accountId', 'videoId', 'videoChannelId' ]), - if (accountId) { - whereAnd.push({ - accountId - }) - } + selectType: 'feed', - const accountWhere = { - [Op.and]: whereAnd - } - - const videoChannelWhere = videoChannelId ? { id: videoChannelId } : undefined + sort: '-createdAt', + onPublicVideo: true, + notDeleted: true, - const query = { - order: [ [ 'createdAt', 'DESC' ] ] as Order, - offset: start, - limit: count, - where: { - deletedAt: null, - accountId: accountWhere - }, - include: [ - { - attributes: [ 'name', 'uuid' ], - model: VideoModel.unscoped(), - required: true, - where: { - privacy: VideoPrivacy.PUBLIC - }, - include: [ - { - attributes: [ 'accountId' ], - model: VideoChannelModel.unscoped(), - required: true, - where: videoChannelWhere - } - ] - } - ] + blockerAccountIds } - if (videoId) query.where['videoId'] = videoId - - return VideoCommentModel - .scope([ ScopeNames.WITH_ACCOUNT ]) - .findAll(query) + return new VideoCommentListQueryBuilder(VideoCommentModel.sequelize, queryOptions).listComments() } static listForBulkDelete (ofAccount: MAccount, filter: { onVideosOfAccount?: MAccountId } = {}) { - const accountWhere = filter.onVideosOfAccount - ? { id: filter.onVideosOfAccount.id } - : {} + const queryOptions: ListVideoCommentsOptions = { + selectType: 'comment-only', - const query = { - limit: 1000, - where: { - deletedAt: null, - accountId: ofAccount.id - }, - include: [ - { - model: VideoModel, - required: true, - include: [ - { - model: VideoChannelModel, - required: true, - include: [ - { - model: AccountModel, - required: true, - where: accountWhere - } - ] - } - ] - } - ] + accountId: ofAccount.id, + videoAccountOwnerId: filter.onVideosOfAccount?.id, + + notDeleted: true, + count: 5000 } - return VideoCommentModel - .scope([ ScopeNames.WITH_ACCOUNT ]) - .findAll(query) + return new VideoCommentListQueryBuilder(VideoCommentModel.sequelize, queryOptions).listComments() } static async getStats () { @@ -750,9 +503,7 @@ export class VideoCommentModel extends Model { + const { user } = 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) - if (videoOwnerAccount) blockerAccountIds.push(videoOwnerAccount.id) - } - return blockerAccountIds } } diff --git a/server/tests/api/videos/video-comments.ts b/server/tests/api/videos/video-comments.ts index dc47f8a4a..5485b72ec 100644 --- a/server/tests/api/videos/video-comments.ts +++ b/server/tests/api/videos/video-comments.ts @@ -232,7 +232,8 @@ describe('Test video comments', function () { await command.addReply({ videoId, toCommentId: threadId2, text: text3 }) const tree = await command.getThread({ videoId: videoUUID, threadId: threadId2 }) - expect(tree.comment.totalReplies).to.equal(tree.comment.totalRepliesFromVideoAuthor + 1) + expect(tree.comment.totalRepliesFromVideoAuthor).to.equal(1) + expect(tree.comment.totalReplies).to.equal(2) }) }) -- cgit v1.2.3