diff --git a/src/database/entities/Channel.ts b/src/database/entities/Channel.ts
index c0c02f7e6..c11ae098c 100644
--- a/src/database/entities/Channel.ts
+++ b/src/database/entities/Channel.ts
@@ -17,7 +17,7 @@
*/
import { HTTPError } from "lambert-server/HTTPError";
-import { Column, Entity, JoinColumn, ManyToOne, OneToMany, RelationId } from "typeorm";
+import { Column, Entity, JoinColumn, ManyToOne, OneToMany, RelationId, Index } from "typeorm";
import { DmChannelDTO } from "../../util/dtos";
import { ChannelCreateEvent, ChannelRecipientRemoveEvent, ThreadCreateEvent, ThreadMembersUpdateEvent } from "../../util/interfaces";
import { InvisibleCharacters, Snowflake, emitEvent, getPermission, Permissions, Config, DiscordApiErrors } from "@spacebar/util/util";
@@ -70,6 +70,7 @@ export class Channel extends BaseClass {
@Column({ nullable: true })
@RelationId((channel: Channel) => channel.guild)
+ @Index("IDX_channel_guild_id", { where: "guild_id IS NOT NULL" })
guild_id?: string;
@JoinColumn({ name: "guild_id", foreignKeyConstraintName: "FK_channel_guild_id" })
diff --git a/src/database/entities/Emoji.ts b/src/database/entities/Emoji.ts
index f0a8ae5da..2578645f5 100644
--- a/src/database/entities/Emoji.ts
+++ b/src/database/entities/Emoji.ts
@@ -16,7 +16,7 @@
along with this program. If not, see <https://www.gnu.org/licenses/>.
*/
-import { Column, Entity, JoinColumn, ManyToOne, RelationId } from "typeorm";
+import { Column, Entity, JoinColumn, ManyToOne, RelationId, Index } from "typeorm";
import { BaseClass } from "./BaseClass";
import { Application } from "./Application";
import { Guild } from "./Guild";
@@ -33,6 +33,7 @@ export class Emoji extends BaseClass {
available: boolean; // whether this emoji can be used, may be false due to various reasons
@Column({ nullable: true })
+ @Index("IDX_emoji_guild_id", { where: "guild_id IS NOT NULL" })
guild_id: string | null;
@JoinColumn({ name: "guild_id", foreignKeyConstraintName: "FK_emoji_guild_id" })
diff --git a/src/database/entities/Role.ts b/src/database/entities/Role.ts
index 5e6a2eeaf..5c70938f1 100644
--- a/src/database/entities/Role.ts
+++ b/src/database/entities/Role.ts
@@ -16,7 +16,7 @@
along with this program. If not, see <https://www.gnu.org/licenses/>.
*/
-import { Column, Entity, JoinColumn, ManyToOne, RelationId } from "typeorm";
+import { Column, Entity, JoinColumn, ManyToOne, RelationId, Index } from "typeorm";
import { BaseClass } from "./BaseClass";
import { Guild } from "./Guild";
@@ -28,6 +28,7 @@ import { RoleColors } from "@spacebar/schemas";
export class Role extends BaseClass {
@Column()
@RelationId((role: Role) => role.guild)
+ @Index("IDX_role_guild_id")
guild_id: string;
@JoinColumn({ name: "guild_id", foreignKeyConstraintName: "FK_role_guild_id" })
diff --git a/src/database/entities/Sticker.ts b/src/database/entities/Sticker.ts
index 98bf5bfee..de46d9e8c 100644
--- a/src/database/entities/Sticker.ts
+++ b/src/database/entities/Sticker.ts
@@ -16,7 +16,7 @@
along with this program. If not, see <https://www.gnu.org/licenses/>.
*/
-import { Column, Entity, JoinColumn, ManyToOne, RelationId } from "typeorm";
+import { Column, Entity, JoinColumn, ManyToOne, RelationId, Index } from "typeorm";
import { BaseClass } from "./BaseClass";
import { Guild } from "./Guild";
import { User } from "./User";
@@ -50,6 +50,7 @@ export class Sticker extends BaseClass {
pack: import("./StickerPack").StickerPack;
@Column({ nullable: true })
+ @Index("IDX_sticker_guild_id", { where: "guild_id IS NOT NULL" })
guild_id?: string;
@JoinColumn({ name: "guild_id", foreignKeyConstraintName: "FK_sticker_guild_id" })
diff --git a/src/database/migration/postgres/1793720801192-RoleStickerEmojiGuildIndexes.ts b/src/database/migration/postgres/1793720801192-RoleStickerEmojiGuildIndexes.ts
new file mode 100644
index 000000000..82e101fff
--- /dev/null
+++ b/src/database/migration/postgres/1793720801192-RoleStickerEmojiGuildIndexes.ts
@@ -0,0 +1,25 @@
+import { MigrationInterface, QueryRunner } from "typeorm";
+
+export class RoleStickerEmojiGuildIndexes1793720801192 implements MigrationInterface {
+ name = "RoleStickerEmojiGuildIndexes1793720801192";
+
+ public async up(queryRunner: QueryRunner): Promise<void> {
+ await queryRunner.query(`ALTER TABLE "roles" DROP CONSTRAINT "FK_role_guild_id"`);
+ await queryRunner.query(`ALTER TABLE "roles" ALTER COLUMN "guild_id" SET NOT NULL`);
+ await queryRunner.query(`CREATE INDEX "IDX_role_guild_id" ON "roles" ("guild_id") `);
+ await queryRunner.query(`CREATE INDEX "IDX_sticker_guild_id" ON "stickers" ("guild_id") WHERE guild_id IS NOT NULL`);
+ await queryRunner.query(`CREATE INDEX "IDX_emoji_guild_id" ON "emojis" ("guild_id") WHERE guild_id IS NOT NULL`);
+ await queryRunner.query(`CREATE INDEX "IDX_channel_guild_id" ON "channels" ("guild_id") WHERE guild_id IS NOT NULL`);
+ await queryRunner.query(`ALTER TABLE "roles" ADD CONSTRAINT "FK_role_guild_id" FOREIGN KEY ("guild_id") REFERENCES "guilds"("id") ON DELETE CASCADE ON UPDATE NO ACTION`);
+ }
+
+ public async down(queryRunner: QueryRunner): Promise<void> {
+ await queryRunner.query(`ALTER TABLE "roles" DROP CONSTRAINT "FK_role_guild_id"`);
+ await queryRunner.query(`DROP INDEX "public"."IDX_channel_guild_id"`);
+ await queryRunner.query(`DROP INDEX "public"."IDX_emoji_guild_id"`);
+ await queryRunner.query(`DROP INDEX "public"."IDX_sticker_guild_id"`);
+ await queryRunner.query(`DROP INDEX "public"."IDX_role_guild_id"`);
+ await queryRunner.query(`ALTER TABLE "roles" ALTER COLUMN "guild_id" DROP NOT NULL`);
+ await queryRunner.query(`ALTER TABLE "roles" ADD CONSTRAINT "FK_role_guild_id" FOREIGN KEY ("guild_id") REFERENCES "guilds"("id") ON DELETE CASCADE ON UPDATE NO ACTION`);
+ }
+}
|
