Automatically remove bad followings

5 files changed, 66 insertions, 60 deletions

diff --git a/server/models/account/account-video-rate.ts b/server/models/account/account-video-rate.ts
index d5c214ecb..4bd8114cf 100644
--- a/server/models/account/account-video-rate.ts
+++ b/server/models/account/account-video-rate.ts
@@ -6,7 +6,7 @@ import { CONSTRAINTS_FIELDS, VIDEO_RATE_TYPES } from '../../initializers/constan
 import { VideoModel } from '../video/video'
 import { AccountModel } from './account'
 import { ActorModel } from '../activitypub/actor'
-import { getSort, throwIfNotValid } from '../utils'
+import { buildLocalAccountIdsIn, getSort, throwIfNotValid } from '../utils'
 import { isActivityPubUrlValid } from '../../helpers/custom-validators/activitypub/misc'
 import { AccountVideoRate } from '../../../shared'
 import { ScopeNames as VideoChannelScopeNames, SummaryOptions, VideoChannelModel } from '../video/video-channel'
@@ -219,25 +219,11 @@ export class AccountVideoRateModel extends Model<AccountVideoRateModel> {
             [Op.lt]: beforeUpdatedAt
           },
           videoId,
-          type
-        },
-        include: [
-          {
-            model: AccountModel.unscoped(),
-            required: true,
-            include: [
-              {
-                model: ActorModel.unscoped(),
-                required: true,
-                where: {
-                  serverId: {
-                    [Op.ne]: null
-                  }
-                }
-              }
-            ]
+          type,
+          accountId: {
+            [Op.notIn]: buildLocalAccountIdsIn()
           }
-        ],
+        },
         transaction: t
       }
 
diff --git a/server/models/activitypub/actor-follow.ts b/server/models/activitypub/actor-follow.ts
index 3039b90c7..99a5fd117 100644
--- a/server/models/activitypub/actor-follow.ts
+++ b/server/models/activitypub/actor-follow.ts
@@ -23,7 +23,7 @@ import { logger } from '../../helpers/logger'
 import { getServerActor } from '../../helpers/utils'
 import { ACTOR_FOLLOW_SCORE, FOLLOW_STATES } from '../../initializers/constants'
 import { ServerModel } from '../server/server'
-import { getSort } from '../utils'
+import { createSafeIn, getSort } from '../utils'
 import { ActorModel, unusedActorAttributesForAPI } from './actor'
 import { VideoChannelModel } from '../video/video-channel'
 import { AccountModel } from '../account/account'
@@ -464,7 +464,7 @@ export class ActorFollowModel extends Model<ActorFollowModel> {
     }
   }
 
-  static updateFollowScore (inboxUrl: string, value: number, t?: Transaction) {
+  static updateScore (inboxUrl: string, value: number, t?: Transaction) {
     const query = `UPDATE "actorFollow" SET "score" = LEAST("score" + ${value}, ${ACTOR_FOLLOW_SCORE.MAX}) ` +
       'WHERE id IN (' +
         'SELECT "actorFollow"."id" FROM "actorFollow" ' +
@@ -480,6 +480,28 @@ export class ActorFollowModel extends Model<ActorFollowModel> {
     return ActorFollowModel.sequelize.query(query, options)
   }
 
+  static async updateScoreByFollowingServers (serverIds: number[], value: number, t?: Transaction) {
+    if (serverIds.length === 0) return
+
+    const me = await getServerActor()
+    const serverIdsString = createSafeIn(ActorFollowModel, serverIds)
+
+    const query = `UPDATE "actorFollow" SET "score" = "score" + ${value} ` +
+      'WHERE id IN (' +
+        'SELECT "actorFollow"."id" FROM "actorFollow" ' +
+        'INNER JOIN "actor" ON "actor"."id" = "actorFollow"."targetActorId" ' +
+        `WHERE "actorFollow"."actorId" = ${me.Account.actorId} ` + // I'm the follower
+        `AND "actor"."serverId" IN (${serverIdsString})` + // Criteria on followings
+      ')'
+
+    const options = {
+      type: QueryTypes.BULKUPDATE,
+      transaction: t
+    }
+
+    return ActorFollowModel.sequelize.query(query, options)
+  }
+
   private static async createListAcceptedFollowForApiQuery (
     type: 'followers' | 'following',
     actorIds: number[],
diff --git a/server/models/utils.ts b/server/models/utils.ts
index 30de91e1d..24890f961 100644
--- a/server/models/utils.ts
+++ b/server/models/utils.ts
@@ -1,7 +1,7 @@
 import { Model, Sequelize } from 'sequelize-typescript'
 import * as validator from 'validator'
 import { Col } from 'sequelize/types/lib/utils'
-import { OrderItem } from 'sequelize/types'
+import { OrderItem, literal } from 'sequelize'
 
 type SortType = { sortModel: any, sortValue: string }
 
@@ -129,16 +129,30 @@ function parseAggregateResult (result: any) {
   return total
 }
 
-const createSafeIn = (model: typeof Model, stringArr: string[]) => {
-  return stringArr.map(t => model.sequelize.escape(t))
+const createSafeIn = (model: typeof Model, stringArr: (string | number)[]) => {
+  return stringArr.map(t => model.sequelize.escape('' + t))
                   .join(', ')
 }
 
+function buildLocalAccountIdsIn () {
+  return literal(
+    '(SELECT "account"."id" FROM "account" INNER JOIN "actor" ON "actor"."id" = "account"."actorId" AND "actor"."serverId" IS NULL)'
+  )
+}
+
+function buildLocalActorIdsIn () {
+  return literal(
+    '(SELECT "actor"."id" FROM "actor" WHERE "actor"."serverId" IS NULL)'
+  )
+}
+
 // ---------------------------------------------------------------------------
 
 export {
   buildBlockedAccountSQL,
+  buildLocalActorIdsIn,
   SortType,
+  buildLocalAccountIdsIn,
   getSort,
   getVideoSort,
   getSortOnModel,
diff --git a/server/models/video/video-comment.ts b/server/models/video/video-comment.ts
index 28e5818cd..6eda32f05 100644
--- a/server/models/video/video-comment.ts
+++ b/server/models/video/video-comment.ts
@@ -22,7 +22,7 @@ import { AccountModel } from '../account/account'
 import { ActorModel } from '../activitypub/actor'
 import { AvatarModel } from '../avatar/avatar'
 import { ServerModel } from '../server/server'
-import { buildBlockedAccountSQL, getSort, throwIfNotValid } from '../utils'
+import { buildBlockedAccountSQL, buildLocalAccountIdsIn, getSort, throwIfNotValid } from '../utils'
 import { VideoModel } from './video'
 import { VideoChannelModel } from './video-channel'
 import { getServerActor } from '../../helpers/utils'
@@ -30,7 +30,7 @@ import { UserModel } from '../account/user'
 import { actorNameAlphabet } from '../../helpers/custom-validators/activitypub/actor'
 import { regexpCapture } from '../../helpers/regexp'
 import { uniq } from 'lodash'
-import { FindOptions, Op, Order, ScopeOptions, Sequelize, Transaction } from 'sequelize'
+import { FindOptions, literal, Op, Order, ScopeOptions, Sequelize, Transaction } from 'sequelize'
 
 enum ScopeNames {
   WITH_ACCOUNT = 'WITH_ACCOUNT',
@@ -281,16 +281,22 @@ export class VideoCommentModel extends Model<VideoCommentModel> {
     return VideoCommentModel.scope([ ScopeNames.WITH_ACCOUNT ]).findOne(query)
   }
 
-  static loadByUrlAndPopulateReplyAndVideo (url: string, t?: Transaction) {
+  static loadByUrlAndPopulateReplyAndVideoUrlAndAccount (url: string, t?: Transaction) {
     const query: FindOptions = {
       where: {
         url
-      }
+      },
+      include: [
+        {
+          attributes: [ 'id', 'url' ],
+          model: VideoModel.unscoped()
+        }
+      ]
     }
 
     if (t !== undefined) query.transaction = t
 
-    return VideoCommentModel.scope([ ScopeNames.WITH_IN_REPLY_TO, ScopeNames.WITH_VIDEO ]).findOne(query)
+    return VideoCommentModel.scope([ ScopeNames.WITH_IN_REPLY_TO, ScopeNames.WITH_ACCOUNT ]).findOne(query)
   }
 
   static async listThreadsForApi (parameters: {
@@ -471,25 +477,11 @@ export class VideoCommentModel extends Model<VideoCommentModel> {
         updatedAt: {
           [Op.lt]: beforeUpdatedAt
         },
-        videoId
-      },
-      include: [
-        {
-          required: true,
-          model: AccountModel.unscoped(),
-          include: [
-            {
-              required: true,
-              model: ActorModel.unscoped(),
-              where: {
-                serverId: {
-                  [Op.ne]: null
-                }
-              }
-            }
-          ]
+        videoId,
+        accountId: {
+          [Op.notIn]: buildLocalAccountIdsIn()
         }
-      ]
+      }
     }
 
     return VideoCommentModel.destroy(query)
diff --git a/server/models/video/video-share.ts b/server/models/video/video-share.ts
index 3bab3c027..d8ed64557 100644
--- a/server/models/video/video-share.ts
+++ b/server/models/video/video-share.ts
@@ -4,7 +4,7 @@ import { isActivityPubUrlValid } from '../../helpers/custom-validators/activityp
 import { CONSTRAINTS_FIELDS } from '../../initializers/constants'
 import { AccountModel } from '../account/account'
 import { ActorModel } from '../activitypub/actor'
-import { throwIfNotValid } from '../utils'
+import { buildLocalActorIdsIn, throwIfNotValid } from '../utils'
 import { VideoModel } from './video'
 import { VideoChannelModel } from './video-channel'
 import { Op, Transaction } from 'sequelize'
@@ -207,19 +207,11 @@ export class VideoShareModel extends Model<VideoShareModel> {
         updatedAt: {
           [Op.lt]: beforeUpdatedAt
         },
-        videoId
-      },
-      include: [
-        {
-          model: ActorModel.unscoped(),
-          required: true,
-          where: {
-            serverId: {
-              [ Op.ne ]: null
-            }
-          }
+        videoId,
+        actorId: {
+          [Op.notIn]: buildLocalActorIdsIn()
         }
-      ]
+      }
     }
 
     return VideoShareModel.destroy(query)