refactor: noteテーブルのインデックス整理と配列カラムへのクエリでインデックスを使うように (#12993)

* Optimize note model index

* enhance(backend): ANY()をやめる (MisskeyIO#239)

* add small e2e test drive endpoint

---------

Co-authored-by: まっちゃとーにゅ <17376330+u1-liquid@users.noreply.github.com>

9 files changed, 188 insertions, 27 deletions

diff --git a/packages/backend/migration/1705222772858-optimize-note-index-for-array-column.js b/packages/backend/migration/1705222772858-optimize-note-index-for-array-column.js
new file mode 100644
index 0000000000..571bd8e8f3
--- /dev/null
+++ b/packages/backend/migration/1705222772858-optimize-note-index-for-array-column.js
@@ -0,0 +1,24 @@
+/*
+ * SPDX-FileCopyrightText: syuilo and other misskey contributors
+ * SPDX-License-Identifier: AGPL-3.0-only
+ */
+
+export class OptimizeNoteIndexForArrayColumns1705222772858 {
+    name = 'OptimizeNoteIndexForArrayColumns1705222772858'
+
+    async up(queryRunner) {
+        await queryRunner.query(`DROP INDEX "public"."IDX_796a8c03959361f97dc2be1d5c"`);
+        await queryRunner.query(`DROP INDEX "public"."IDX_54ebcb6d27222913b908d56fd8"`);
+        await queryRunner.query(`DROP INDEX "public"."IDX_88937d94d7443d9a99a76fa5c0"`);
+        await queryRunner.query(`DROP INDEX "public"."IDX_51c063b6a133a9cb87145450f5"`);
+        await queryRunner.query(`CREATE INDEX "IDX_NOTE_FILE_IDS" ON "note" using gin ("fileIds")`)
+    }
+
+    async down(queryRunner) {
+        await queryRunner.query(`DROP INDEX "IDX_NOTE_FILE_IDS"`)
+        await queryRunner.query(`CREATE INDEX "IDX_51c063b6a133a9cb87145450f5" ON "note" ("fileIds") `);
+        await queryRunner.query(`CREATE INDEX "IDX_88937d94d7443d9a99a76fa5c0" ON "note" ("tags") `);
+        await queryRunner.query(`CREATE INDEX "IDX_54ebcb6d27222913b908d56fd8" ON "note" ("mentions") `);
+        await queryRunner.query(`CREATE INDEX "IDX_796a8c03959361f97dc2be1d5c" ON "note" ("visibleUserIds") `);
+    }
+}
diff --git a/packages/backend/src/core/QueryService.ts b/packages/backend/src/core/QueryService.ts
index f006ed4944..13d8a67597 100644
--- a/packages/backend/src/core/QueryService.ts
+++ b/packages/backend/src/core/QueryService.ts
@@ -212,8 +212,8 @@ export class QueryService {
 				// または 自分自身
 					.orWhere('note.userId = :meId')
 				// または 自分宛て
-					.orWhere(':meId = ANY(note.visibleUserIds)')
-					.orWhere(':meId = ANY(note.mentions)')
+					.orWhere(':meIdAsList <@ note.visibleUserIds')
+					.orWhere(':meIdAsList <@ note.mentions')
 					.orWhere(new Brackets(qb => {
 						qb
 						// または フォロワー宛ての投稿であり、
@@ -228,7 +228,7 @@ export class QueryService {
 					}));
 			}));
 
-			q.setParameters({ meId: me.id });
+			q.setParameters({ meId: me.id, meIdAsList: [me.id] });
 		}
 	}
 
diff --git a/packages/backend/src/models/Note.ts b/packages/backend/src/models/Note.ts
index a4358b9ba6..dee2560b7c 100644
--- a/packages/backend/src/models/Note.ts
+++ b/packages/backend/src/models/Note.ts
@@ -11,9 +11,6 @@ import { MiChannel } from './Channel.js';
 import type { MiDriveFile } from './DriveFile.js';
 
 @Entity('note')
-@Index('IDX_NOTE_TAGS', { synchronize: false })
-@Index('IDX_NOTE_MENTIONS', { synchronize: false })
-@Index('IDX_NOTE_VISIBLE_USER_IDS', { synchronize: false })
 export class MiNote {
 	@PrimaryColumn(id())
 	public id: string;
@@ -133,7 +130,7 @@ export class MiNote {
 	})
 	public url: string | null;
 
-	@Index()
+	@Index('IDX_NOTE_FILE_IDS', { synchronize: false })
 	@Column({
 		...id(),
 		array: true, default: '{}',
@@ -145,14 +142,14 @@ export class MiNote {
 	})
 	public attachedFileTypes: string[];
 
-	@Index()
+	@Index('IDX_NOTE_VISIBLE_USER_IDS', { synchronize: false })
 	@Column({
 		...id(),
 		array: true, default: '{}',
 	})
 	public visibleUserIds: MiUser['id'][];
 
-	@Index()
+	@Index('IDX_NOTE_MENTIONS', { synchronize: false })
 	@Column({
 		...id(),
 		array: true, default: '{}',
@@ -174,7 +171,7 @@ export class MiNote {
 	})
 	public emojis: string[];
 
-	@Index()
+	@Index('IDX_NOTE_TAGS', { synchronize: false })
 	@Column('varchar', {
 		length: 128, array: true, default: '{}',
 	})
diff --git a/packages/backend/src/server/api/endpoints/drive/files/attached-notes.ts b/packages/backend/src/server/api/endpoints/drive/files/attached-notes.ts
index 14a13b09c9..7a0b8b4417 100644
--- a/packages/backend/src/server/api/endpoints/drive/files/attached-notes.ts
+++ b/packages/backend/src/server/api/endpoints/drive/files/attached-notes.ts
@@ -74,7 +74,7 @@ export default class extends Endpoint<typeof meta, typeof paramDef> { // eslint-
 			}
 
 			const query = this.queryService.makePaginationQuery(this.notesRepository.createQueryBuilder('note'), ps.sinceId, ps.untilId);
-			query.andWhere(':file = ANY(note.fileIds)', { file: file.id });
+			query.andWhere(':file <@ note.fileIds', { file: [file.id] });
 
 			const notes = await query.limit(ps.limit).getMany();
 
diff --git a/packages/backend/src/server/api/endpoints/hashtags/users.ts b/packages/backend/src/server/api/endpoints/hashtags/users.ts
index 50aea79943..8302d2380f 100644
--- a/packages/backend/src/server/api/endpoints/hashtags/users.ts
+++ b/packages/backend/src/server/api/endpoints/hashtags/users.ts
@@ -6,6 +6,7 @@
 import { Inject, Injectable } from '@nestjs/common';
 import { Endpoint } from '@/server/api/endpoint-base.js';
 import type { UsersRepository } from '@/models/_.js';
+import { safeForSql } from "@/misc/safe-for-sql.js";
 import { normalizeForSearch } from '@/misc/normalize-for-search.js';
 import { UserEntityService } from '@/core/entities/UserEntityService.js';
 import { DI } from '@/di-symbols.js';
@@ -47,8 +48,9 @@ export default class extends Endpoint<typeof meta, typeof paramDef> { // eslint-
 		private userEntityService: UserEntityService,
 	) {
 		super(meta, paramDef, async (ps, me) => {
+			if (!safeForSql(normalizeForSearch(ps.tag))) throw new Error('Injection');
 			const query = this.usersRepository.createQueryBuilder('user')
-				.where(':tag = ANY(user.tags)', { tag: normalizeForSearch(ps.tag) })
+				.where(':tag <@ user.tags', { tag: [normalizeForSearch(ps.tag)] })
 				.andWhere('user.isSuspended = FALSE');
 
 			const recent = new Date(Date.now() - (1000 * 60 * 60 * 24 * 5));
diff --git a/packages/backend/src/server/api/endpoints/notes/mentions.ts b/packages/backend/src/server/api/endpoints/notes/mentions.ts
index 2317f8f7b2..323c6c946b 100644
--- a/packages/backend/src/server/api/endpoints/notes/mentions.ts
+++ b/packages/backend/src/server/api/endpoints/notes/mentions.ts
@@ -61,9 +61,9 @@ export default class extends Endpoint<typeof meta, typeof paramDef> { // eslint-
 
 			const query = this.queryService.makePaginationQuery(this.notesRepository.createQueryBuilder('note'), ps.sinceId, ps.untilId)
 				.andWhere(new Brackets(qb => {
-					qb
-						.where(`'{"${me.id}"}' <@ note.mentions`)
-						.orWhere(`'{"${me.id}"}' <@ note.visibleUserIds`);
+					qb // このmeIdAsListパラメータはqueryServiceのgenerateVisibilityQueryでセットされる
+						.where(':meIdAsList <@ note.mentions')
+						.orWhere(':meIdAsList <@ note.visibleUserIds');
 				}))
 				// Avoid scanning primary key index
 				.orderBy('CONCAT(note.id)', 'DESC')
diff --git a/packages/backend/src/server/api/endpoints/notes/search-by-tag.ts b/packages/backend/src/server/api/endpoints/notes/search-by-tag.ts
index b00f5207d8..0d7aca6621 100644
--- a/packages/backend/src/server/api/endpoints/notes/search-by-tag.ts
+++ b/packages/backend/src/server/api/endpoints/notes/search-by-tag.ts
@@ -87,14 +87,14 @@ export default class extends Endpoint<typeof meta, typeof paramDef> { // eslint-
 			try {
 				if (ps.tag) {
 					if (!safeForSql(normalizeForSearch(ps.tag))) throw new Error('Injection');
-					query.andWhere(`'{"${normalizeForSearch(ps.tag)}"}' <@ note.tags`);
+					query.andWhere(':tag <@ note.tags', { tag: [normalizeForSearch(ps.tag)] });
 				} else {
 					query.andWhere(new Brackets(qb => {
 						for (const tags of ps.query!) {
 							qb.orWhere(new Brackets(qb => {
 								for (const tag of tags) {
 									if (!safeForSql(normalizeForSearch(tag))) throw new Error('Injection');
-									qb.andWhere(`'{"${normalizeForSearch(tag)}"}' <@ note.tags`);
+									qb.andWhere(':tag <@ note.tags', { tag: [normalizeForSearch(tag)] });
 								}
 							}));
 						}
diff --git a/packages/backend/test/e2e/drive.ts b/packages/backend/test/e2e/drive.ts
new file mode 100644
index 0000000000..3a84961fc7
--- /dev/null
+++ b/packages/backend/test/e2e/drive.ts
@@ -0,0 +1,95 @@
+/*
+ * SPDX-FileCopyrightText: syuilo and other misskey contributors
+ * SPDX-License-Identifier: AGPL-3.0-only
+ */
+
+process.env.NODE_ENV = 'test';
+
+import * as assert from 'assert';
+import { MiNote } from '@/models/Note.js';
+import { api, initTestDb, makeStreamCatcher, post, signup, uploadFile } from '../utils.js';
+import type * as misskey from 'misskey-js';
+import type{ Repository } from 'typeorm'
+import type { Packed } from '@/misc/json-schema.js';
+
+
+describe('Drive', () => {
+	let Notes: Repository<MiNote>;
+
+	let alice: misskey.entities.SignupResponse;
+	let bob: misskey.entities.SignupResponse;
+
+	beforeAll(async () => {
+		const connection = await initTestDb(true);
+		Notes = connection.getRepository(MiNote);
+		alice = await signup({ username: 'alice' });
+		bob = await signup({ username: 'bob' });
+	}, 1000 * 60 * 2);
+
+	test('ファイルURLからアップロードできる', async () => {
+		// utils.js uploadUrl の処理だがAPIレスポンスも見るためここで同様の処理を書いている
+
+		const marker = Math.random().toString();
+
+		const url = 'https://raw.githubusercontent.com/misskey-dev/misskey/develop/packages/backend/test/resources/Lenna.jpg'
+
+		const catcher = makeStreamCatcher(
+			alice,
+			'main',
+			(msg) => msg.type === 'urlUploadFinished' && msg.body.marker === marker,
+			(msg) => msg.body.file as Packed<'DriveFile'>,
+			10 * 1000);
+
+		const res = await api('drive/files/upload-from-url', {
+			url,
+			marker,
+			force: true,
+		}, alice);
+
+		const file = await catcher;
+
+		assert.strictEqual(res.status, 204);
+		assert.strictEqual(file.name, 'Lenna.jpg');
+		assert.strictEqual(file.type, 'image/jpeg');
+	})
+
+	test('ローカルからアップロードできる', async () => {
+		// APIレスポンスを直接使用するので utils.js uploadFile が通過することで成功とする
+
+		const res = await uploadFile(alice, { path: 'Lenna.jpg', name: 'テスト画像' });
+
+		assert.strictEqual(res.body?.name, 'テスト画像.jpg');
+		assert.strictEqual(res.body?.type, 'image/jpeg');
+	})
+
+	test('添付ノート一覧を取得できる', async () => {
+		const ids = (await Promise.all([uploadFile(alice), uploadFile(alice), uploadFile(alice)])).map(elm => elm.body!.id)
+
+		const note0 = await post(alice, { fileIds: [ids[0]] });
+		const note1 = await post(alice, { fileIds: [ids[0], ids[1]] });
+
+		const attached0 = await api('drive/files/attached-notes', { fileId: ids[0] }, alice);
+		assert.strictEqual(attached0.body.length, 2);
+		assert.strictEqual(attached0.body[0].id, note1.id)
+		assert.strictEqual(attached0.body[1].id, note0.id)
+
+		const attached1 = await api('drive/files/attached-notes', { fileId: ids[1] }, alice);
+		assert.strictEqual(attached1.body.length, 1);
+		assert.strictEqual(attached1.body[0].id, note1.id)
+
+		const attached2 = await api('drive/files/attached-notes', { fileId: ids[2] }, alice);
+		assert.strictEqual(attached2.body.length, 0)
+	})
+
+	test('添付ノート一覧は他の人から見えない', async () => {
+		const file = await uploadFile(alice);
+
+		await post(alice, { fileIds: [file.body!.id] });
+
+		const res = await api('drive/files/attached-notes', { fileId: file.body!.id }, bob);
+		assert.strictEqual(res.status, 400);
+		assert.strictEqual('error' in res.body, true);
+
+	})
+});
+
diff --git a/packages/backend/test/utils.ts b/packages/backend/test/utils.ts
index 2b232a0a5d..a41002cc8c 100644
--- a/packages/backend/test/utils.ts
+++ b/packages/backend/test/utils.ts
@@ -16,6 +16,7 @@ import { DEFAULT_POLICIES } from '@/core/RoleService.js';
 import { entities } from '../src/postgres.js';
 import { loadConfig } from '../src/config.js';
 import type * as misskey from 'misskey-js';
+import { Packed } from '@/misc/json-schema.js';
 
 export { server as startServer, jobQueue as startJobQueue } from '@/boot/common.js';
 
@@ -114,6 +115,20 @@ export function randomString(chars = 'abcdefghijklmnopqrstuvwxyz0123456789', len
 	return randomString;
 }
 
+/**
+ * @brief プロミスにタイムアウト追加
+ * @param p 待ち対象プロミス
+ * @param timeout 待機ミリ秒
+ */
+function timeoutPromise<T>(p: Promise<T>, timeout: number): Promise<T> {
+	return Promise.race([
+		p,
+		new Promise((reject) =>{
+			setTimeout(() => { reject(new Error('timed out')); }, timeout)
+		}) as never
+	]);
+}
+
 export const signup = async (params?: Partial<misskey.Endpoints['signup']['req']>): Promise<NonNullable<misskey.Endpoints['signup']['res']>> => {
 	const q = Object.assign({
 		username: randomString(),
@@ -320,17 +335,16 @@ export const uploadFile = async (user?: UserToken, { path, name, blob }: UploadO
 	};
 };
 
-export const uploadUrl = async (user: UserToken, url: string) => {
-	let resolve: unknown;
-	const file = new Promise(ok => resolve = ok);
+export const uploadUrl = async (user: UserToken, url: string): Promise<Packed<'DriveFile'>> => {
 	const marker = Math.random().toString();
 
-	const ws = await connectStream(user, 'main', (msg) => {
-		if (msg.type === 'urlUploadFinished' && msg.body.marker === marker) {
-			ws.close();
-			resolve(msg.body.file);
-		}
-	});
+	const catcher = makeStreamCatcher(
+		user,
+		'main',
+		(msg) => msg.type === 'urlUploadFinished' && msg.body.marker === marker,
+		(msg) => msg.body.file as Packed<'DriveFile'>,
+		60 * 1000
+	);
 
 	await api('drive/files/upload-from-url', {
 		url,
@@ -338,7 +352,7 @@ export const uploadUrl = async (user: UserToken, url: string) => {
 		force: true,
 	}, user);
 
-	return file;
+	return catcher;
 };
 
 export function connectStream(user: UserToken, channel: string, listener: (message: Record<string, any>) => any, params?: any): Promise<WebSocket> {
@@ -410,6 +424,35 @@ export const waitFire = async (user: UserToken, channel: string, trgr: () => any
 	});
 };
 
+/**
+ * @brief WebSocketストリームから特定条件の通知を拾うプロミスを生成
+ * @param user ユーザー認証情報
+ * @param channel チャンネル
+ * @param cond 条件
+ * @param extractor 取り出し処理
+ * @param timeout ミリ秒タイムアウト
+ * @returns 時間内に正常に処理できた場合に通知からextractorを通した値を得る
+ */
+export function makeStreamCatcher<T>(
+		user: UserToken,
+		channel: string,
+		cond: (message: Record<string, any>) => boolean,
+		extractor: (message: Record<string, any>) => T,
+		timeout = 60 * 1000): Promise<T> {
+	let ws: WebSocket
+	const p = new Promise<T>(async (resolve) => {
+		ws = await connectStream(user, channel, (msg) => {
+			if (cond(msg)) {
+				resolve(extractor(msg))
+			}
+		});
+	}).finally(() => {
+		ws?.close();
+	});
+
+	return timeoutPromise(p, timeout);
+}
+
 export type SimpleGetResponse = {
 	status: number,
 	body: any | JSDOM | null,