From 74d249bc1346c7cfaac7ee49bebbebcf2a01f82a Mon Sep 17 00:00:00 2001 From: Chocobozzz Date: Fri, 26 Feb 2021 16:26:27 +0100 Subject: Add ability to cleanup remote AP interactions --- server/models/account/account-video-rate.ts | 30 ++++++++++++++--------------- server/models/video/video-comment.ts | 14 +++++++++++++- server/models/video/video-share.ts | 13 ++++++++++++- server/models/video/video.ts | 20 ++++++++++++++++++- 4 files changed, 58 insertions(+), 19 deletions(-) (limited to 'server/models') diff --git a/server/models/account/account-video-rate.ts b/server/models/account/account-video-rate.ts index d9c529491..801f76bba 100644 --- a/server/models/account/account-video-rate.ts +++ b/server/models/account/account-video-rate.ts @@ -146,10 +146,22 @@ export class AccountVideoRateModel extends Model { return AccountVideoRateModel.findAndCountAll(query) } + static listRemoteRateUrlsOfLocalVideos () { + const query = `SELECT "accountVideoRate".url FROM "accountVideoRate" ` + + `INNER JOIN account ON account.id = "accountVideoRate"."accountId" ` + + `INNER JOIN actor ON actor.id = account."actorId" AND actor."serverId" IS NOT NULL ` + + `INNER JOIN video ON video.id = "accountVideoRate"."videoId" AND video.remote IS FALSE` + + return AccountVideoRateModel.sequelize.query<{ url: string }>(query, { + type: QueryTypes.SELECT, + raw: true + }).then(rows => rows.map(r => r.url)) + } + static loadLocalAndPopulateVideo ( rateType: VideoRateType, accountName: string, - videoId: number | string, + videoId: number, t?: Transaction ): Promise { const options: FindOptions = { @@ -241,21 +253,7 @@ export class AccountVideoRateModel extends Model { await AccountVideoRateModel.destroy(query) - const field = type === 'like' - ? 'likes' - : 'dislikes' - - const rawQuery = `UPDATE "video" SET "${field}" = ` + - '(' + - 'SELECT COUNT(id) FROM "accountVideoRate" WHERE "accountVideoRate"."videoId" = "video"."id" AND type = :rateType' + - ') ' + - 'WHERE "video"."id" = :videoId' - - return AccountVideoRateModel.sequelize.query(rawQuery, { - transaction: t, - replacements: { videoId, rateType: type }, - type: QueryTypes.UPDATE - }) + return VideoModel.updateRatesOf(videoId, type, t) }) } diff --git a/server/models/video/video-comment.ts b/server/models/video/video-comment.ts index dc7556d44..151c2bc81 100644 --- a/server/models/video/video-comment.ts +++ b/server/models/video/video-comment.ts @@ -1,5 +1,5 @@ import { uniq } from 'lodash' -import { FindAndCountOptions, FindOptions, Op, Order, ScopeOptions, Sequelize, Transaction, WhereOptions } from 'sequelize' +import { FindAndCountOptions, FindOptions, Op, Order, QueryTypes, ScopeOptions, Sequelize, Transaction, WhereOptions } from 'sequelize' import { AllowNull, BelongsTo, @@ -696,6 +696,18 @@ export class VideoCommentModel extends Model { } } + static listRemoteCommentUrlsOfLocalVideos () { + const query = `SELECT "videoComment".url FROM "videoComment" ` + + `INNER JOIN account ON account.id = "videoComment"."accountId" ` + + `INNER JOIN actor ON actor.id = "account"."actorId" AND actor."serverId" IS NOT NULL ` + + `INNER JOIN video ON video.id = "videoComment"."videoId" AND video.remote IS FALSE` + + return VideoCommentModel.sequelize.query<{ url: string }>(query, { + type: QueryTypes.SELECT, + raw: true + }).then(rows => rows.map(r => r.url)) + } + static cleanOldCommentsOf (videoId: number, beforeUpdatedAt: Date) { const query = { where: { diff --git a/server/models/video/video-share.ts b/server/models/video/video-share.ts index b7f5f3fa3..5059c1fa6 100644 --- a/server/models/video/video-share.ts +++ b/server/models/video/video-share.ts @@ -1,4 +1,4 @@ -import { literal, Op, Transaction } from 'sequelize' +import { literal, Op, QueryTypes, Transaction } from 'sequelize' import { AllowNull, BelongsTo, Column, CreatedAt, DataType, ForeignKey, Is, Model, Scopes, Table, UpdatedAt } from 'sequelize-typescript' import { isActivityPubUrlValid } from '../../helpers/custom-validators/activitypub/misc' import { CONSTRAINTS_FIELDS } from '../../initializers/constants' @@ -185,6 +185,17 @@ export class VideoShareModel extends Model { return VideoShareModel.findAndCountAll(query) } + static listRemoteShareUrlsOfLocalVideos () { + const query = `SELECT "videoShare".url FROM "videoShare" ` + + `INNER JOIN actor ON actor.id = "videoShare"."actorId" AND actor."serverId" IS NOT NULL ` + + `INNER JOIN video ON video.id = "videoShare"."videoId" AND video.remote IS FALSE` + + return VideoShareModel.sequelize.query<{ url: string }>(query, { + type: QueryTypes.SELECT, + raw: true + }).then(rows => rows.map(r => r.url)) + } + static cleanOldSharesOf (videoId: number, beforeUpdatedAt: Date) { const query = { where: { diff --git a/server/models/video/video.ts b/server/models/video/video.ts index 8894843e0..b4c7da655 100644 --- a/server/models/video/video.ts +++ b/server/models/video/video.ts @@ -34,7 +34,7 @@ import { ModelCache } from '@server/models/model-cache' import { VideoFile } from '@shared/models/videos/video-file.model' import { ResultList, UserRight, VideoPrivacy, VideoState } from '../../../shared' import { VideoObject } from '../../../shared/models/activitypub/objects' -import { Video, VideoDetails } from '../../../shared/models/videos' +import { Video, VideoDetails, VideoRateType } from '../../../shared/models/videos' import { ThumbnailType } from '../../../shared/models/videos/thumbnail.type' import { VideoFilter } from '../../../shared/models/videos/video-query.type' import { VideoStreamingPlaylistType } from '../../../shared/models/videos/video-streaming-playlist.type' @@ -1509,6 +1509,24 @@ export class VideoModel extends Model { }) } + static updateRatesOf (videoId: number, type: VideoRateType, t: Transaction) { + const field = type === 'like' + ? 'likes' + : 'dislikes' + + const rawQuery = `UPDATE "video" SET "${field}" = ` + + '(' + + 'SELECT COUNT(id) FROM "accountVideoRate" WHERE "accountVideoRate"."videoId" = "video"."id" AND type = :rateType' + + ') ' + + 'WHERE "video"."id" = :videoId' + + return AccountVideoRateModel.sequelize.query(rawQuery, { + transaction: t, + replacements: { videoId, rateType: type }, + type: QueryTypes.UPDATE + }) + } + static checkVideoHasInstanceFollow (videoId: number, followerActorId: number) { // Instances only share videos const query = 'SELECT 1 FROM "videoShare" ' + -- cgit v1.2.3