Merge branch 'develop' into merge/2024-02-03

# Conflicts:
#	locales/index.d.ts
#	packages/backend/src/core/SearchService.ts
#	packages/frontend/src/pages/search.note.vue
#	packages/misskey-js/src/autogen/types.ts
#	sharkey-locales/en-US.yml

4 files changed, 77 insertions, 51 deletions

diff --git a/packages/backend/migration/1739451520729-index_note_attachedFileTypes.js b/packages/backend/migration/1739451520729-index_note_attachedFileTypes.js
new file mode 100644
index 0000000000..351908a68c
--- /dev/null
+++ b/packages/backend/migration/1739451520729-index_note_attachedFileTypes.js
@@ -0,0 +1,12 @@
+// https://stackoverflow.com/a/4059785
+export class IndexNoteAttachedFileTypes1739451520729 {
+	name = 'IndexNoteAttachedFileTypes1739451520729'
+
+	async up(queryRunner) {
+		await queryRunner.query(`CREATE INDEX "IDX_NOTE_ATTACHED_FILE_TYPES" ON "note" USING GIN ("attachedFileTypes" array_ops)`);
+	}
+
+	async down(queryRunner) {
+		await queryRunner.query(`DROP INDEX "IDX_NOTE_ATTACHED_FILE_TYPES"`);
+	}
+}
diff --git a/packages/backend/src/core/SearchService.ts b/packages/backend/src/core/SearchService.ts
index 3ad2042d53..5794d27d4a 100644
--- a/packages/backend/src/core/SearchService.ts
+++ b/packages/backend/src/core/SearchService.ts
@@ -34,11 +34,65 @@ type Q =
 	{ op: 'or', qs: Q[] } |
 	{ op: 'not', q: Q };
 
+const fileTypes = {
+	image: [
+		'image/webp',
+		'image/png',
+		'image/jpeg',
+		'image/avif',
+		'image/apng',
+		'image/gif',
+	],
+	video: [
+		'video/mp4',
+		'video/webm',
+		'video/mpeg',
+		'video/x-m4v',
+	],
+	audio: [
+		'audio/mpeg',
+		'audio/flac',
+		'audio/wav',
+		'audio/aac',
+		'audio/webm',
+		'audio/opus',
+		'audio/ogg',
+		'audio/x-m4a',
+		'audio/mod',
+		'audio/s3m',
+		'audio/xm',
+		'audio/it',
+		'audio/x-mod',
+		'audio/x-s3m',
+		'audio/x-xm',
+		'audio/x-it',
+	],
+	// Keep in sync with frontend-shared/js/const.ts
+	module: [
+		'audio/mod',
+		'audio/x-mod',
+		'audio/s3m',
+		'audio/x-s3m',
+		'audio/xm',
+		'audio/x-xm',
+		'audio/it',
+		'audio/x-it',
+	],
+	flash: [
+		'application/x-shockwave-flash',
+		'application/vnd.adobe.flash.movie',
+	],
+};
+
+// Make sure to regenerate misskey-js and check search.note.vue after changing these
+export const fileTypeCategories = ['image', 'video', 'audio', 'module', 'flash'] as const;
+export type FileTypeCategory = typeof fileTypeCategories[number];
+
 export type SearchOpts = {
 	userId?: MiNote['userId'] | null;
 	channelId?: MiNote['channelId'] | null;
 	host?: string | null;
-	filetype?: string | null;
+	filetype?: FileTypeCategory | null;
 	order?: string | null;
 	disableMeili?: boolean | null;
 };
@@ -239,18 +293,7 @@ export class SearchService {
 		}
 
 		if (opts.filetype) {
-			/* this is very ugly, but the "correct" solution would
-				be `and exists (select 1 from
-				unnest(note."attachedFileTypes") x(t) where t like
-				:type)` and I can't find a way to get TypeORM to
-				generate that; this hack works because `~*` is
-				"regexp match, ignoring case" and the stringified
-				version of an array of varchars (which is what
-				`attachedFileTypes` is) looks like `{foo,bar}`, so
-				we're looking for opts.filetype as the first half of
-				a MIME type, either at start of the array (after the
-				`{`) or later (after a `,`) */
-			query.andWhere('note."attachedFileTypes"::varchar ~* :type', { type: `[{,]${opts.filetype}/` });
+			query.andWhere('note."attachedFileTypes" && :types', { types: fileTypes[opts.filetype] });
 		}
 
 		this.queryService.generateVisibilityQuery(query, me);
@@ -296,42 +339,8 @@ export class SearchService {
 		}
 
 		if (opts.filetype) {
-			if (opts.filetype === 'image') {
-				filter.qs.push({ op: 'or', qs: [
-					{ op: '=', k: 'attachedFileTypes', v: 'image/webp' },
-					{ op: '=', k: 'attachedFileTypes', v: 'image/png' },
-					{ op: '=', k: 'attachedFileTypes', v: 'image/jpeg' },
-					{ op: '=', k: 'attachedFileTypes', v: 'image/avif' },
-					{ op: '=', k: 'attachedFileTypes', v: 'image/apng' },
-					{ op: '=', k: 'attachedFileTypes', v: 'image/gif' },
-				] });
-			} else if (opts.filetype === 'video') {
-				filter.qs.push({ op: 'or', qs: [
-					{ op: '=', k: 'attachedFileTypes', v: 'video/mp4' },
-					{ op: '=', k: 'attachedFileTypes', v: 'video/webm' },
-					{ op: '=', k: 'attachedFileTypes', v: 'video/mpeg' },
-					{ op: '=', k: 'attachedFileTypes', v: 'video/x-m4v' },
-				] });
-			} else if (opts.filetype === 'audio') {
-				filter.qs.push({ op: 'or', qs: [
-					{ op: '=', k: 'attachedFileTypes', v: 'audio/mpeg' },
-					{ op: '=', k: 'attachedFileTypes', v: 'audio/flac' },
-					{ op: '=', k: 'attachedFileTypes', v: 'audio/wav' },
-					{ op: '=', k: 'attachedFileTypes', v: 'audio/aac' },
-					{ op: '=', k: 'attachedFileTypes', v: 'audio/webm' },
-					{ op: '=', k: 'attachedFileTypes', v: 'audio/opus' },
-					{ op: '=', k: 'attachedFileTypes', v: 'audio/ogg' },
-					{ op: '=', k: 'attachedFileTypes', v: 'audio/x-m4a' },
-					{ op: '=', k: 'attachedFileTypes', v: 'audio/mod' },
-					{ op: '=', k: 'attachedFileTypes', v: 'audio/s3m' },
-					{ op: '=', k: 'attachedFileTypes', v: 'audio/xm' },
-					{ op: '=', k: 'attachedFileTypes', v: 'audio/it' },
-					{ op: '=', k: 'attachedFileTypes', v: 'audio/x-mod' },
-					{ op: '=', k: 'attachedFileTypes', v: 'audio/x-s3m' },
-					{ op: '=', k: 'attachedFileTypes', v: 'audio/x-xm' },
-					{ op: '=', k: 'attachedFileTypes', v: 'audio/x-it' },
-				] });
-			}
+			const filters = fileTypes[opts.filetype].map(mime => ({ op: '=' as const, k: 'attachedFileTypes', v: mime }));
+			filter.qs.push({ op: 'or', qs: filters });
 		}
 
 		const res = await this.meilisearchNoteIndex.search(q, {
diff --git a/packages/backend/src/models/Note.ts b/packages/backend/src/models/Note.ts
index 408e023ff7..8b5265e8fe 100644
--- a/packages/backend/src/models/Note.ts
+++ b/packages/backend/src/models/Note.ts
@@ -143,6 +143,7 @@ export class MiNote {
 	})
 	public fileIds: MiDriveFile['id'][];
 
+	@Index('IDX_NOTE_ATTACHED_FILE_TYPES', { synchronize: false })
 	@Column('varchar', {
 		length: 256, array: true, default: '{}',
 	})
diff --git a/packages/backend/src/server/api/endpoints/notes/search.ts b/packages/backend/src/server/api/endpoints/notes/search.ts
index eca55cd085..f46f4d2adb 100644
--- a/packages/backend/src/server/api/endpoints/notes/search.ts
+++ b/packages/backend/src/server/api/endpoints/notes/search.ts
@@ -5,7 +5,7 @@
 
 import { Injectable } from '@nestjs/common';
 import { Endpoint } from '@/server/api/endpoint-base.js';
-import { SearchService } from '@/core/SearchService.js';
+import { fileTypeCategories, SearchService } from '@/core/SearchService.js';
 import { NoteEntityService } from '@/core/entities/NoteEntityService.js';
 import { RoleService } from '@/core/RoleService.js';
 import { ApiError } from '../../error.js';
@@ -52,7 +52,11 @@ export const paramDef = {
 			type: 'string',
 			description: 'The local host is represented with `.`.',
 		},
-		filetype: { type: 'string', nullable: true },
+		filetype: {
+			type: 'string',
+			nullable: true,
+			enum: fileTypeCategories,
+		},
 		userId: { type: 'string', format: 'misskey:id', nullable: true, default: null },
 		channelId: { type: 'string', format: 'misskey:id', nullable: true, default: null },
 		order: { type: 'string' },