Add ability to cleanup remote AP interactions

3 files changed, 44 insertions, 3 deletions

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" ' +