From 85c20aaeb90ef0e0f44c377e62c323fde275cdde Mon Sep 17 00:00:00 2001
From: Chocobozzz <me@florianbigard.com>
Date: Thu, 11 May 2023 16:16:27 +0200
Subject: Set actor preferred name case insensitive

---
 .../migrations/0770-actor-preferred-username.ts    | 44 ++++++++++++++++++++++
 1 file changed, 44 insertions(+)
 create mode 100644 server/initializers/migrations/0770-actor-preferred-username.ts

(limited to 'server/initializers/migrations')

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
+}
-- 
cgit v1.2.3