From 0251197e249cc03b65805ed6805da72bf4573529 Mon Sep 17 00:00:00 2001 From: Rigel Kent Date: Mon, 20 Apr 2020 11:20:12 +0200 Subject: Factorize rest-table and fix/simplify SQL --- server/helpers/middlewares/video-abuses.ts | 2 +- server/initializers/migrations/0490-abuse-video.ts | 4 +- server/models/account/account-blocklist.ts | 4 +- server/models/server/server-blocklist.ts | 2 - server/models/utils.ts | 8 ++-- server/models/video/video-abuse.ts | 43 ++++++++-------------- server/models/video/video-blacklist.ts | 2 +- server/models/video/video.ts | 6 +-- 8 files changed, 27 insertions(+), 44 deletions(-) (limited to 'server') diff --git a/server/helpers/middlewares/video-abuses.ts b/server/helpers/middlewares/video-abuses.ts index 7553a5eb3..97a5724b6 100644 --- a/server/helpers/middlewares/video-abuses.ts +++ b/server/helpers/middlewares/video-abuses.ts @@ -7,7 +7,7 @@ async function doesVideoAbuseExist (abuseIdArg: number | string, videoUUID: stri let videoAbuse = await VideoAbuseModel.loadByIdAndVideoId(abuseId, null, videoUUID) if (!videoAbuse) { - const userId = res.locals.oauth ? res.locals.oauth.token.User.id : undefined + const userId = res.locals.oauth?.token.User.id const video = await fetchVideo(videoUUID, 'all', userId) if (video) videoAbuse = await VideoAbuseModel.loadByIdAndVideoId(abuseId, video.id) diff --git a/server/initializers/migrations/0490-abuse-video.ts b/server/initializers/migrations/0490-abuse-video.ts index 26333feb5..610307aa4 100644 --- a/server/initializers/migrations/0490-abuse-video.ts +++ b/server/initializers/migrations/0490-abuse-video.ts @@ -11,10 +11,8 @@ async function up (utils: { allowNull: true } await utils.queryInterface.addColumn('videoAbuse', 'deletedVideo', deletedVideo) - await utils.sequelize.query(`ALTER TABLE "videoAbsue" ALTER COLUMN "videoId" DROP NOT NULL;`) + await utils.sequelize.query(`ALTER TABLE "videoAbuse" ALTER COLUMN "videoId" DROP NOT NULL;`) await utils.sequelize.query(`ALTER TABLE "videoAbuse" DROP CONSTRAINT IF EXISTS "videoAbuse_videoId_fkey";`) - await utils.sequelize.query(`ALTER TABLE "videoAbuse" ADD CONSTRAINT "videoAbuse_videoId_fkey" - FOREIGN KEY ("videoId") REFERENCES video(id) ON UPDATE CASCADE ON DELETE SET NULL;`) } diff --git a/server/models/account/account-blocklist.ts b/server/models/account/account-blocklist.ts index fe2d5d010..d8a7ce4b4 100644 --- a/server/models/account/account-blocklist.ts +++ b/server/models/account/account-blocklist.ts @@ -133,8 +133,8 @@ export class AccountBlocklistModel extends Model { if (search) { Object.assign(where, { [Op.or]: [ - { ...searchAttribute(search, '$BlockedAccount.name$') }, - { ...searchAttribute(search, '$BlockedAccount.Actor.url$') } + searchAttribute(search, '$BlockedAccount.name$'), + searchAttribute(search, '$BlockedAccount.Actor.url$') ] }) } diff --git a/server/models/server/server-blocklist.ts b/server/models/server/server-blocklist.ts index 764203d2c..892024c04 100644 --- a/server/models/server/server-blocklist.ts +++ b/server/models/server/server-blocklist.ts @@ -139,8 +139,6 @@ export class ServerBlocklistModel extends Model { } } - console.log(search) - return ServerBlocklistModel .scope([ ScopeNames.WITH_ACCOUNT, ScopeNames.WITH_SERVER ]) .findAndCountAll(query) diff --git a/server/models/utils.ts b/server/models/utils.ts index 7137419a2..bdf2291f0 100644 --- a/server/models/utils.ts +++ b/server/models/utils.ts @@ -208,13 +208,15 @@ function buildDirectionAndField (value: string) { } function searchAttribute (sourceField, targetField) { - return sourceField - ? { + if (sourceField) { + return { [targetField]: { [Op.iLike]: `%${sourceField}%` } } - : {} + } else { + return {} + } } // --------------------------------------------------------------------------- diff --git a/server/models/video/video-abuse.ts b/server/models/video/video-abuse.ts index e8c3bd823..71b519cd9 100644 --- a/server/models/video/video-abuse.ts +++ b/server/models/video/video-abuse.ts @@ -15,7 +15,7 @@ import { VideoAbuseState, VideoDetails } from '../../../shared' import { CONSTRAINTS_FIELDS, VIDEO_ABUSE_STATES } from '../../initializers/constants' import { MUserAccountId, MVideoAbuse, MVideoAbuseFormattable, MVideoAbuseVideo } from '../../typings/models' import * as Bluebird from 'bluebird' -import { literal, Op } from 'sequelize' +import { literal, Op, Sequelize } from 'sequelize' import { ThumbnailModel } from './thumbnail' import { VideoBlacklistModel } from './video-blacklist' import { ScopeNames as VideoChannelScopeNames, SummaryOptions, VideoChannelModel } from './video-channel' @@ -31,7 +31,7 @@ export enum ScopeNames { searchVideo?: string searchVideoChannel?: string serverAccountId: number - userAccountId: any + userAccountId: number }) => { let where = { reporterAccountId: { @@ -45,28 +45,28 @@ export enum ScopeNames { { [Op.and]: [ { videoId: { [Op.not]: null } }, - { '$Video.name$': { [Op.iLike]: `%${options.search}%` } } + searchAttribute(options.search, '$Video.name$') ] }, { [Op.and]: [ { videoId: { [Op.not]: null } }, - { '$Video.VideoChannel.name$': { [Op.iLike]: `%${options.search}%` } } + searchAttribute(options.search, '$Video.VideoChannel.name$') ] }, { [Op.and]: [ { deletedVideo: { [Op.not]: null } }, - { deletedVideo: { name: { [Op.iLike]: `%${options.search}%` } } } + { deletedVideo: searchAttribute(options.search, 'name') } ] }, { [Op.and]: [ { deletedVideo: { [Op.not]: null } }, - { deletedVideo: { channel: { displayName: { [Op.iLike]: `%${options.search}%` } } } } + { deletedVideo: { channel: searchAttribute(options.search, 'displayName') } } ] }, - { '$Account.name$': { [Op.iLike]: `%${options.search}%` } } + searchAttribute(options.search, '$Account.name$') ] }) } @@ -77,13 +77,9 @@ export enum ScopeNames { [ literal( '(' + - 'SELECT t.count ' + - 'FROM ( ' + - 'SELECT id, ' + - 'count(id) OVER (PARTITION BY "videoId") ' + - 'FROM "videoAbuse" ' + - ') t ' + - 'WHERE t.id = "VideoAbuseModel".id ' + + 'SELECT count(*) ' + + 'FROM "videoAbuse" ' + + 'WHERE "videoId" = "VideoAbuseModel"."videoId" ' + ')' ), 'countReportsForVideo' @@ -118,20 +114,11 @@ export enum ScopeNames { [ literal( '(' + - 'WITH ' + - 'ids AS ( ' + - 'SELECT "account"."id" ' + - 'FROM "account" ' + - 'INNER JOIN "videoChannel" ON "videoChannel"."accountId" = "account"."id" ' + - 'INNER JOIN "video" ON "video"."channelId" = "videoChannel"."id" ' + - 'WHERE "video"."id" = "VideoAbuseModel"."videoId" ' + - ') ' + - 'SELECT count("videoAbuse"."id") ' + + 'SELECT count(DISTINCT "videoAbuse"."id") ' + 'FROM "videoAbuse" ' + 'INNER JOIN "video" ON "video"."id" = "videoAbuse"."videoId" ' + 'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" ' + - 'INNER JOIN "account" ON "videoChannel"."accountId" = "account"."id" ' + - 'INNER JOIN ids ON "account"."id" = ids.id ' + + 'INNER JOIN "account" ON "videoChannel"."accountId" = "Video->VideoChannel"."accountId" ' + ')' ), 'countReportsForReportee' @@ -142,19 +129,19 @@ export enum ScopeNames { { model: AccountModel, required: true, - where: { ...searchAttribute(options.searchReporter, 'name') } + where: searchAttribute(options.searchReporter, 'name') }, { model: VideoModel, required: false, - where: { ...searchAttribute(options.searchVideo, 'name') }, + where: searchAttribute(options.searchVideo, 'name'), include: [ { model: ThumbnailModel }, { model: VideoChannelModel.scope({ method: [ VideoChannelScopeNames.SUMMARY, { withAccount: true } as SummaryOptions ] }), - where: { ...searchAttribute(options.searchVideoChannel, 'name') } + where: searchAttribute(options.searchVideoChannel, 'name') }, { attributes: [ 'id', 'reason', 'unfederated' ], diff --git a/server/models/video/video-blacklist.ts b/server/models/video/video-blacklist.ts index 680eba471..8cbfe362e 100644 --- a/server/models/video/video-blacklist.ts +++ b/server/models/video/video-blacklist.ts @@ -78,7 +78,7 @@ export class VideoBlacklistModel extends Model { { model: VideoModel, required: true, - where: { ...searchAttribute(search, 'name') }, + where: searchAttribute(search, 'name'), include: [ { model: VideoChannelModel.scope({ method: [ VideoChannelScopeNames.SUMMARY, { withAccount: true } as SummaryOptions ] }), diff --git a/server/models/video/video.ts b/server/models/video/video.ts index f32216e90..ccb9d64ca 100644 --- a/server/models/video/video.ts +++ b/server/models/video/video.ts @@ -813,10 +813,8 @@ export class VideoModel extends Model { const details = instance.toFormattedDetailsJSON() for (const abuse of instance.VideoAbuses) { - tasks.push((_ => { - abuse.deletedVideo = details - return abuse.save({ transaction: options.transaction }) - })()) + abuse.deletedVideo = details + tasks.push(abuse.save({ transaction: options.transaction })) } Promise.all(tasks) -- cgit v1.2.3