server/initializers/migrations/0770-actor-preferred-username.ts

   1 import * as Sequelize from 'sequelize'
   2
   3 async function up (utils: {
   4   transaction: Sequelize.Transaction
   5   queryInterface: Sequelize.QueryInterface
   6   sequelize: Sequelize.Sequelize
   7   db: any
   8 }): Promise<void> {
   9   const { transaction } = utils
  10
  11   await utils.sequelize.query('drop index if exists "actor_preferred_username"', { transaction })
  12   await utils.sequelize.query('drop index if exists "actor_preferred_username_server_id"', { transaction })
  13
  14   await utils.sequelize.query(
  15     'DELETE FROM "actor" v1 USING (' +
  16       'SELECT MIN(id) as id, lower("preferredUsername") AS "lowerPreferredUsername", "serverId" ' +
  17       'FROM "actor" ' +
  18       'GROUP BY "lowerPreferredUsername", "serverId" HAVING COUNT(*) > 1 AND "serverId" IS NOT NULL' +
  19     ') v2 ' +
  20     'WHERE lower(v1."preferredUsername") = v2."lowerPreferredUsername" AND v1."serverId" = v2."serverId" AND v1.id <> v2.id',
  21     { transaction }
  22   )
  23
  24   await utils.sequelize.query(
  25     'DELETE FROM "actor" v1 USING (' +
  26       'SELECT MIN(id) as id, lower("preferredUsername") AS "lowerPreferredUsername", "serverId" ' +
  27       'FROM "actor" ' +
  28       'GROUP BY "lowerPreferredUsername", "serverId" HAVING COUNT(*) > 1 AND "serverId" IS NULL' +
  29     ') v2 ' +
  30     'WHERE lower(v1."preferredUsername") = v2."lowerPreferredUsername" AND v1."serverId" IS NULL AND v1.id <> v2.id',
  31     { transaction }
  32   )
  33 }
  34
  35 async function down (utils: {
  36   queryInterface: Sequelize.QueryInterface
  37   transaction: Sequelize.Transaction
  38 }) {
  39 }
  40
  41 export {
  42   up,
  43   down
  44 }