2 files changed, 45 insertions, 1 deletions

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
+}