From 59099a2b2a1d570334df3a0770450c8b4b1522c6 Mon Sep 17 00:00:00 2001 From: Hazelnoot Date: Sat, 24 May 2025 19:10:41 -0400 Subject: add foreign keys to note/user where instance is referenced --- packages/backend/src/models/Note.ts | 31 +++++++++++++++++++++++++++++++ 1 file changed, 31 insertions(+) (limited to 'packages/backend/src/models/Note.ts') diff --git a/packages/backend/src/models/Note.ts b/packages/backend/src/models/Note.ts index ee2098216d..fa5839b6ec 100644 --- a/packages/backend/src/models/Note.ts +++ b/packages/backend/src/models/Note.ts @@ -5,6 +5,7 @@ import { Entity, Index, JoinColumn, Column, PrimaryColumn, ManyToOne } from 'typeorm'; import { noteVisibilities } from '@/types.js'; +import { MiInstance } from '@/models/Instance.js'; import { id } from './util/id.js'; import { MiUser } from './User.js'; import { MiChannel } from './Channel.js'; @@ -222,6 +223,16 @@ export class MiNote { }) public userHost: string | null; + @ManyToOne(() => MiInstance, { + onDelete: 'CASCADE', + }) + @JoinColumn({ + name: 'userHost', + foreignKeyConstraintName: 'FK_note_userHost', + referencedColumnName: 'host', + }) + public userInstance: MiInstance | null; + @Column({ ...id(), nullable: true, @@ -235,6 +246,16 @@ export class MiNote { }) public replyUserHost: string | null; + @ManyToOne(() => MiInstance, { + onDelete: 'CASCADE', + }) + @JoinColumn({ + name: 'replyUserHost', + foreignKeyConstraintName: 'FK_note_replyUserHost', + referencedColumnName: 'host', + }) + public replyUserInstance: MiInstance | null; + @Column({ ...id(), nullable: true, @@ -247,6 +268,16 @@ export class MiNote { comment: '[Denormalized]', }) public renoteUserHost: string | null; + + @ManyToOne(() => MiInstance, { + onDelete: 'CASCADE', + }) + @JoinColumn({ + name: 'renoteUserHost', + foreignKeyConstraintName: 'FK_note_renoteUserHost', + referencedColumnName: 'host', + }) + public renoteUserInstance: MiInstance | null; //#endregion constructor(data: Partial) { -- cgit v1.2.3-freya From 9d45041bd8d8c59e7e555f0f3aed945394c7989c Mon Sep 17 00:00:00 2001 From: Hazelnoot Date: Tue, 3 Jun 2025 18:43:42 -0400 Subject: replace index on note.userHost --- .../1748990452958-replace_note-userHost_index.js | 22 ++++++++++++++++++++++ packages/backend/src/models/Note.ts | 2 +- 2 files changed, 23 insertions(+), 1 deletion(-) create mode 100644 packages/backend/migration/1748990452958-replace_note-userHost_index.js (limited to 'packages/backend/src/models/Note.ts') diff --git a/packages/backend/migration/1748990452958-replace_note-userHost_index.js b/packages/backend/migration/1748990452958-replace_note-userHost_index.js new file mode 100644 index 0000000000..55aadd8136 --- /dev/null +++ b/packages/backend/migration/1748990452958-replace_note-userHost_index.js @@ -0,0 +1,22 @@ +/* + * SPDX-FileCopyrightText: hazelnoot and other Sharkey contributors + * SPDX-License-Identifier: AGPL-3.0-only + */ + +export class ReplaceNoteUserHostIndex1748990452958 { + name = 'ReplaceNoteUserHostIndex1748990452958' + + async up(queryRunner) { + await queryRunner.query(`DROP INDEX "public"."IDX_7125a826ab192eb27e11d358a5"`); + await queryRunner.query(` + create index "IDX_note_userHost_id" + on "note" ("userHost", "id" desc) + nulls not distinct`); + await queryRunner.query(`comment on index "IDX_note_userHost_id" is 'User host with ID included'`); + } + + async down(queryRunner) { + await queryRunner.query(`drop index if exists "IDX_note_userHost_id"`); + await queryRunner.query(`CREATE INDEX "IDX_7125a826ab192eb27e11d358a5" ON "note" ("userHost") `); + } +} diff --git a/packages/backend/src/models/Note.ts b/packages/backend/src/models/Note.ts index fa5839b6ec..b9a6f576dc 100644 --- a/packages/backend/src/models/Note.ts +++ b/packages/backend/src/models/Note.ts @@ -12,6 +12,7 @@ import { MiChannel } from './Channel.js'; import type { MiDriveFile } from './DriveFile.js'; @Index('IDX_724b311e6f883751f261ebe378', ['userId', 'id']) +@Index('IDX_note_userHost_id', { synchronize: false }) // (userHost, id desc) @Entity('note') export class MiNote { @PrimaryColumn(id()) @@ -216,7 +217,6 @@ export class MiNote { public processErrors: string[] | null; //#region Denormalized fields - @Index() @Column('varchar', { length: 128, nullable: true, comment: '[Denormalized]', -- cgit v1.2.3-freya From 99e3d4506232f13e311afe1a5bd942b721b2cabe Mon Sep 17 00:00:00 2001 From: Hazelnoot Date: Tue, 3 Jun 2025 19:05:24 -0400 Subject: create IDX_note_for_timelines --- .../1748991828473-create-IDX_note_for_timelines.js | 19 +++++++++++++++++++ packages/backend/src/models/Note.ts | 1 + 2 files changed, 20 insertions(+) create mode 100644 packages/backend/migration/1748991828473-create-IDX_note_for_timelines.js (limited to 'packages/backend/src/models/Note.ts') diff --git a/packages/backend/migration/1748991828473-create-IDX_note_for_timelines.js b/packages/backend/migration/1748991828473-create-IDX_note_for_timelines.js new file mode 100644 index 0000000000..2ea7fe95d2 --- /dev/null +++ b/packages/backend/migration/1748991828473-create-IDX_note_for_timelines.js @@ -0,0 +1,19 @@ +/* + * SPDX-FileCopyrightText: hazelnoot and other Sharkey contributors + * SPDX-License-Identifier: AGPL-3.0-only + */ + +export class CreateIDXNoteForTimelines1748991828473 { + async up(queryRunner) { + await queryRunner.query(` + create index "IDX_note_for_timelines" + on "note" ("id" desc, "channelId", "visibility", "userHost") + include ("userId", "userHost", "replyId", "replyUserId", "replyUserHost", "renoteId", "renoteUserId", "renoteUserHost") + NULLS NOT DISTINCT`); + await queryRunner.query(`comment on index "IDX_note_for_timelines" is 'Covering index for timeline queries'`); + } + + async down(queryRunner) { + await queryRunner.query(`DROP INDEX "IDX_note_for_timelines"`); + } +} diff --git a/packages/backend/src/models/Note.ts b/packages/backend/src/models/Note.ts index b9a6f576dc..90b874f29a 100644 --- a/packages/backend/src/models/Note.ts +++ b/packages/backend/src/models/Note.ts @@ -13,6 +13,7 @@ import type { MiDriveFile } from './DriveFile.js'; @Index('IDX_724b311e6f883751f261ebe378', ['userId', 'id']) @Index('IDX_note_userHost_id', { synchronize: false }) // (userHost, id desc) +@Index('IDX_note_for_timelines', { synchronize: false }) // (id desc, channelId, visibility, userHost) @Entity('note') export class MiNote { @PrimaryColumn(id()) -- cgit v1.2.3-freya From 5f51f7878ea520956896337885789041647c9b25 Mon Sep 17 00:00:00 2001 From: Hazelnoot Date: Fri, 6 Jun 2025 13:03:25 -0400 Subject: create IDX_note_url over note.url --- .../migration/1749229288946-create-IDX_note_url.js | 16 ++++++++++++++++ packages/backend/src/models/Note.ts | 1 + 2 files changed, 17 insertions(+) create mode 100644 packages/backend/migration/1749229288946-create-IDX_note_url.js (limited to 'packages/backend/src/models/Note.ts') diff --git a/packages/backend/migration/1749229288946-create-IDX_note_url.js b/packages/backend/migration/1749229288946-create-IDX_note_url.js new file mode 100644 index 0000000000..4b2fc25cf7 --- /dev/null +++ b/packages/backend/migration/1749229288946-create-IDX_note_url.js @@ -0,0 +1,16 @@ +/* + * SPDX-FileCopyrightText: hazelnoot and other Sharkey contributors + * SPDX-License-Identifier: AGPL-3.0-only + */ + +export class CreateIDXNoteUrl1749229288946 { + name = 'CreateIDXNoteUrl1749229288946' + + async up(queryRunner) { + await queryRunner.query(`CREATE INDEX IF NOT EXISTS "IDX_note_url" ON "note" ("url") `); + } + + async down(queryRunner) { + await queryRunner.query(`DROP INDEX "public"."IDX_note_url"`); + } +} diff --git a/packages/backend/src/models/Note.ts b/packages/backend/src/models/Note.ts index 90b874f29a..bbe183cfbb 100644 --- a/packages/backend/src/models/Note.ts +++ b/packages/backend/src/models/Note.ts @@ -133,6 +133,7 @@ export class MiNote { }) public uri: string | null; + @Index('IDX_note_url') @Column('varchar', { length: 512, nullable: true, comment: 'The human readable url of a note. it will be null when the note is local.', -- cgit v1.2.3-freya