Set actor preferred name case insensitive

8 files changed, 95 insertions, 46 deletions

diff --git a/server/controllers/api/users/my-subscriptions.ts b/server/controllers/api/users/my-subscriptions.ts
index 6ba8ba597..6e2aa3711 100644
--- a/server/controllers/api/users/my-subscriptions.ts
+++ b/server/controllers/api/users/my-subscriptions.ts
@@ -99,7 +99,7 @@ async function areSubscriptionsExist (req: express.Request, res: express.Respons
     const obj = results.find(r => {
       const server = r.ActorFollowing.Server
 
-      return r.ActorFollowing.preferredUsername === sanitizedHandle.name &&
+      return r.ActorFollowing.preferredUsername.toLowerCase() === sanitizedHandle.name.toLowerCase() &&
         (
           (!server && !sanitizedHandle.host) ||
           (server.host === sanitizedHandle.host)
diff --git a/server/initializers/constants.ts b/server/initializers/constants.ts
index adf24b73f..1dfc9fb27 100644
--- a/server/initializers/constants.ts
+++ b/server/initializers/constants.ts
@@ -27,7 +27,7 @@ import { CONFIG, registerConfigChangedHandler } from './config'
 
 // ---------------------------------------------------------------------------
 
-const LAST_MIGRATION_VERSION = 765
+const LAST_MIGRATION_VERSION = 770
 
 // ---------------------------------------------------------------------------
 
diff --git a/server/initializers/migrations/0770-actor-preferred-username.ts b/server/initializers/migrations/0770-actor-preferred-username.ts
new file mode 100644
index 000000000..217813f7f
--- /dev/null
+++ b/server/initializers/migrations/0770-actor-preferred-username.ts
@@ -0,0 +1,44 @@
+import * as Sequelize from 'sequelize'
+
+async function up (utils: {
+  transaction: Sequelize.Transaction
+  queryInterface: Sequelize.QueryInterface
+  sequelize: Sequelize.Sequelize
+  db: any
+}): Promise<void> {
+  const { transaction } = utils
+
+  await utils.sequelize.query('drop index if exists "actor_preferred_username"', { transaction })
+  await utils.sequelize.query('drop index if exists "actor_preferred_username_server_id"', { transaction })
+
+  await utils.sequelize.query(
+    'DELETE FROM "actor" v1 USING (' +
+      'SELECT MIN(id) as id, lower("preferredUsername") AS "lowerPreferredUsername", "serverId" ' +
+      'FROM "actor" ' +
+      'GROUP BY "lowerPreferredUsername", "serverId" HAVING COUNT(*) > 1 AND "serverId" IS NOT NULL' +
+    ') v2 ' +
+    'WHERE lower(v1."preferredUsername") = v2."lowerPreferredUsername" AND v1."serverId" = v2."serverId" AND v1.id <> v2.id',
+    { transaction }
+  )
+
+  await utils.sequelize.query(
+    'DELETE FROM "actor" v1 USING (' +
+      'SELECT MIN(id) as id, lower("preferredUsername") AS "lowerPreferredUsername", "serverId" ' +
+      'FROM "actor" ' +
+      'GROUP BY "lowerPreferredUsername", "serverId" HAVING COUNT(*) > 1 AND "serverId" IS NULL' +
+    ') v2 ' +
+    'WHERE lower(v1."preferredUsername") = v2."lowerPreferredUsername" AND v1."serverId" IS NULL AND v1.id <> v2.id',
+    { transaction }
+  )
+}
+
+async function down (utils: {
+  queryInterface: Sequelize.QueryInterface
+  transaction: Sequelize.Transaction
+}) {
+}
+
+export {
+  up,
+  down
+}
diff --git a/server/models/account/account-video-rate.ts b/server/models/account/account-video-rate.ts
index 9e7ef4394..18ff07d53 100644
--- a/server/models/account/account-video-rate.ts
+++ b/server/models/account/account-video-rate.ts
@@ -189,8 +189,10 @@ export class AccountVideoRateModel extends Model<Partial<AttributesOnly<AccountV
               model: ActorModel.unscoped(),
               required: true,
               where: {
-                preferredUsername: accountName,
-                serverId: null
+                [Op.and]: [
+                  ActorModel.wherePreferredUsername(accountName),
+                  { serverId: null }
+                ]
               }
             }
           ]
diff --git a/server/models/account/account.ts b/server/models/account/account.ts
index 5bf29f45a..ec4e8d946 100644
--- a/server/models/account/account.ts
+++ b/server/models/account/account.ts
@@ -37,8 +37,8 @@ import { ActorImageModel } from '../actor/actor-image'
 import { ApplicationModel } from '../application/application'
 import { ServerModel } from '../server/server'
 import { ServerBlocklistModel } from '../server/server-blocklist'
-import { UserModel } from '../user/user'
 import { buildSQLAttributes, getSort, throwIfNotValid } from '../shared'
+import { UserModel } from '../user/user'
 import { VideoModel } from '../video/video'
 import { VideoChannelModel } from '../video/video-channel'
 import { VideoCommentModel } from '../video/video-comment'
@@ -296,9 +296,7 @@ export class AccountModel extends Model<Partial<AttributesOnly<AccountModel>>> {
           {
             model: ActorModel,
             required: true,
-            where: {
-              preferredUsername: name
-            }
+            where: ActorModel.wherePreferredUsername(name)
           }
         ]
       }
@@ -321,9 +319,7 @@ export class AccountModel extends Model<Partial<AttributesOnly<AccountModel>>> {
         {
           model: ActorModel,
           required: true,
-          where: {
-            preferredUsername: name
-          },
+          where: ActorModel.wherePreferredUsername(name),
           include: [
             {
               model: ServerModel,
diff --git a/server/models/actor/actor-follow.ts b/server/models/actor/actor-follow.ts
index 32e5d78b0..0f199d208 100644
--- a/server/models/actor/actor-follow.ts
+++ b/server/models/actor/actor-follow.ts
@@ -37,8 +37,8 @@ import { logger } from '../../helpers/logger'
 import { ACTOR_FOLLOW_SCORE, CONSTRAINTS_FIELDS, FOLLOW_STATES, SERVER_ACTOR_NAME, SORTABLE_COLUMNS } from '../../initializers/constants'
 import { AccountModel } from '../account/account'
 import { ServerModel } from '../server/server'
-import { doesExist } from '../shared/query'
 import { buildSQLAttributes, createSafeIn, getSort, searchAttribute, throwIfNotValid } from '../shared'
+import { doesExist } from '../shared/query'
 import { VideoChannelModel } from '../video/video-channel'
 import { ActorModel, unusedActorAttributesForAPI } from './actor'
 import { InstanceListFollowersQueryBuilder, ListFollowersOptions } from './sql/instance-list-followers-query-builder'
@@ -265,9 +265,7 @@ export class ActorFollowModel extends Model<Partial<AttributesOnly<ActorFollowMo
       model: ActorModel,
       required: true,
       as: 'ActorFollowing',
-      where: {
-        preferredUsername: targetName
-      },
+      where: ActorModel.wherePreferredUsername(targetName),
       include: [
         {
           model: VideoChannelModel.unscoped(),
@@ -313,24 +311,16 @@ export class ActorFollowModel extends Model<Partial<AttributesOnly<ActorFollowMo
         if (t.host) {
           return {
             [Op.and]: [
-              {
-                $preferredUsername$: t.name
-              },
-              {
-                $host$: t.host
-              }
+              ActorModel.wherePreferredUsername(t.name, '$preferredUsername$'),
+              { $host$: t.host }
             ]
           }
         }
 
         return {
           [Op.and]: [
-            {
-              $preferredUsername$: t.name
-            },
-            {
-              $serverId$: null
-            }
+            ActorModel.wherePreferredUsername(t.name, '$preferredUsername$'),
+            { $serverId$: null }
           ]
         }
       })
diff --git a/server/models/actor/actor.ts b/server/models/actor/actor.ts
index 1432e8757..1524e0533 100644
--- a/server/models/actor/actor.ts
+++ b/server/models/actor/actor.ts
@@ -1,4 +1,4 @@
-import { literal, Op, QueryTypes, Transaction } from 'sequelize'
+import { col, fn, literal, Op, QueryTypes, Transaction, where } from 'sequelize'
 import {
   AllowNull,
   BelongsTo,
@@ -130,7 +130,8 @@ export const unusedActorAttributesForAPI: (keyof AttributesOnly<ActorModel>)[] =
       unique: true
     },
     {
-      fields: [ 'preferredUsername', 'serverId' ],
+      fields: [ fn('lower', col('preferredUsername')), 'serverId' ],
+      name: 'actor_preferred_username_lower_server_id',
       unique: true,
       where: {
         serverId: {
@@ -139,7 +140,8 @@ export const unusedActorAttributesForAPI: (keyof AttributesOnly<ActorModel>)[] =
       }
     },
     {
-      fields: [ 'preferredUsername' ],
+      fields: [ fn('lower', col('preferredUsername')) ],
+      name: 'actor_preferred_username_lower',
       unique: true,
       where: {
         serverId: null
@@ -327,6 +329,12 @@ export class ActorModel extends Model<Partial<AttributesOnly<ActorModel>>> {
 
   // ---------------------------------------------------------------------------
 
+  static wherePreferredUsername (preferredUsername: string, colName = 'preferredUsername') {
+    return where(fn('lower', col(colName)), preferredUsername.toLowerCase())
+  }
+
+  // ---------------------------------------------------------------------------
+
   static async load (id: number): Promise<MActor> {
     const actorServer = await getServerActor()
     if (id === actorServer.id) return actorServer
@@ -372,8 +380,12 @@ export class ActorModel extends Model<Partial<AttributesOnly<ActorModel>>> {
     const fun = () => {
       const query = {
         where: {
-          preferredUsername,
-          serverId: null
+          [Op.and]: [
+            this.wherePreferredUsername(preferredUsername),
+            {
+              serverId: null
+            }
+          ]
         },
         transaction
       }
@@ -395,8 +407,12 @@ export class ActorModel extends Model<Partial<AttributesOnly<ActorModel>>> {
       const query = {
         attributes: [ 'url' ],
         where: {
-          preferredUsername,
-          serverId: null
+          [Op.and]: [
+            this.wherePreferredUsername(preferredUsername),
+            {
+              serverId: null
+            }
+          ]
         },
         transaction
       }
@@ -405,7 +421,7 @@ export class ActorModel extends Model<Partial<AttributesOnly<ActorModel>>> {
     }
 
     return ModelCache.Instance.doCache({
-      cacheType: 'local-actor-name',
+      cacheType: 'local-actor-url',
       key: preferredUsername,
       // The server actor never change, so we can easily cache it
       whitelist: () => preferredUsername === SERVER_ACTOR_NAME,
@@ -415,9 +431,7 @@ export class ActorModel extends Model<Partial<AttributesOnly<ActorModel>>> {
 
   static loadByNameAndHost (preferredUsername: string, host: string): Promise<MActorFull> {
     const query = {
-      where: {
-        preferredUsername
-      },
+      where: this.wherePreferredUsername(preferredUsername),
       include: [
         {
           model: ServerModel,
diff --git a/server/models/video/video-channel.ts b/server/models/video/video-channel.ts
index 67fccab68..306bc6ade 100644
--- a/server/models/video/video-channel.ts
+++ b/server/models/video/video-channel.ts
@@ -130,13 +130,16 @@ export type SummaryOptions = {
       for (const handle of options.handles || []) {
         const [ preferredUsername, host ] = handle.split('@')
 
+        const sanitizedPreferredUsername = VideoChannelModel.sequelize.escape(preferredUsername.toLowerCase())
+        const sanitizedHost = VideoChannelModel.sequelize.escape(host)
+
         if (!host || host === WEBSERVER.HOST) {
-          or.push(`("preferredUsername" = ${VideoChannelModel.sequelize.escape(preferredUsername)} AND "serverId" IS NULL)`)
+          or.push(`(LOWER("preferredUsername") = ${sanitizedPreferredUsername} AND "serverId" IS NULL)`)
         } else {
           or.push(
             `(` +
-              `"preferredUsername" = ${VideoChannelModel.sequelize.escape(preferredUsername)} ` +
-              `AND "host" = ${VideoChannelModel.sequelize.escape(host)}` +
+              `LOWER("preferredUsername") = ${sanitizedPreferredUsername} ` +
+              `AND "host" = ${sanitizedHost}` +
             `)`
           )
         }
@@ -698,8 +701,10 @@ export class VideoChannelModel extends Model<Partial<AttributesOnly<VideoChannel
           model: ActorModel,
           required: true,
           where: {
-            preferredUsername: name,
-            serverId: null
+            [Op.and]: [
+              ActorModel.wherePreferredUsername(name),
+              { serverId: null }
+            ]
           },
           include: [
             {
@@ -723,9 +728,7 @@ export class VideoChannelModel extends Model<Partial<AttributesOnly<VideoChannel
         {
           model: ActorModel,
           required: true,
-          where: {
-            preferredUsername: name
-          },
+          where: ActorModel.wherePreferredUsername(name),
           include: [
             {
               model: ServerModel,