From f816da921f6cf735a988f5ff0bb0e9d0e8e4f8ca Mon Sep 17 00:00:00 2001 From: Chocobozzz Date: Mon, 26 Apr 2021 14:27:14 +0200 Subject: Add migration to remove duplicated keys Due to old/corrupted data, it can be problematic to restore a backup --- server/initializers/migrations/0640-unique-keys.ts | 39 ++++++++++++++++++++++ 1 file changed, 39 insertions(+) create mode 100644 server/initializers/migrations/0640-unique-keys.ts (limited to 'server/initializers/migrations') diff --git a/server/initializers/migrations/0640-unique-keys.ts b/server/initializers/migrations/0640-unique-keys.ts new file mode 100644 index 000000000..b082accc2 --- /dev/null +++ b/server/initializers/migrations/0640-unique-keys.ts @@ -0,0 +1,39 @@ +import * as Sequelize from 'sequelize' + +async function up (utils: { + transaction: Sequelize.Transaction + queryInterface: Sequelize.QueryInterface + sequelize: Sequelize.Sequelize + db: any +}): Promise { + { + await utils.sequelize.query( + 'DELETE FROM "actor" v1 USING (SELECT MIN(id) as id, "preferredUsername", "serverId" FROM "actor" ' + + 'GROUP BY "preferredUsername", "serverId" HAVING COUNT(*) > 1 AND "serverId" IS NOT NULL) v2 ' + + 'WHERE v1."preferredUsername" = v2."preferredUsername" AND v1."serverId" = v2."serverId" AND v1.id <> v2.id' + ) + } + + { + await utils.sequelize.query( + 'DELETE FROM "actor" v1 USING (SELECT MIN(id) as id, "url" FROM "actor" GROUP BY "url" HAVING COUNT(*) > 1) v2 ' + + 'WHERE v1."url" = v2."url" AND v1.id <> v2.id' + ) + } + + { + await utils.sequelize.query( + 'DELETE FROM "tag" v1 USING (SELECT MIN(id) as id, "name" FROM "tag" GROUP BY "name" HAVING COUNT(*) > 1) v2 ' + + 'WHERE v1."name" = v2."name" AND v1.id <> v2.id' + ) + } +} + +function down (options) { + throw new Error('Not implemented.') +} + +export { + up, + down +} -- cgit v1.2.3