feat: 新カスタム絵文字管理画面（β）の追加 (#13473)

* wip

* wip

* wip

* wip

* wip

* wip

* wip

* wip

* fix

* fix

* fix

* fix size

* fix register logs

* fix img autosize

* fix row selection

* support delete

* fix border rendering

* fix display:none

* tweak comments

* support choose pc file and drive file

* support directory drag-drop

* fix

* fix comment

* support context menu on data area

* fix autogen

* wip イベント整理

* イベントの整理

* refactor grid

* fix cell re-render bugs

* fix row remove

* fix comment

* fix validation

* fix utils

* list maximum

* add mimetype check

* fix

* fix number cell focus

* fix over 100 file drop

* remove log

* fix patchData

* fix performance

* fix

* support update and delete

* support remote import

* fix layout

* heightやめる

* fix performance

* add list v2 endpoint

* support pagination

* fix api call

* fix no clickable input text

* fix limit

* fix paging

* fix

* fix

* support search

* tweak logs

* tweak cell selection

* fix range select

* block delete

* add comment

* fix

* support import log

* fix dialog

* refactor

* add confirm dialog

* fix name

* fix autogen

* wip

* support image change and highlight row

* add columns

* wip

* support sort

* add role name

* add index to emoji

* refine context menu setting

* support role select

* remove unused buttons

* fix url

* fix MkRoleSelectDialog.vue

* add route

* refine remote page

* enter key search

* fix paste bugs

* fix copy/paste

* fix keyEvent

* fix copy/paste and delete

* fix comment

* fix MkRoleSelectDialog.vue and storybook scenario

* fix MkRoleSelectDialog.vue and storybook scenario

* add MkGrid.stories.impl.ts

* fix

* [wip] add custom-emojis-manager2.stories.impl.ts

* [wip] add custom-emojis-manager2.stories.impl.ts

* wip

* 課題はまだ残っているが、ひとまず完了

* fix validation and register roles

* fix upload

* optimize import

* patch from dev

* i18n

* revert excess fixes

* separate sort order component

* add SPDX

* revert excess fixes

* fix pre test

* fix bugs

* add type column

* fix types

* fix CHANGELOG.md

* fix lit

* lint

* tweak style

* refactor

* fix ci

* autogen

* Update types.ts

* CSS Module化

* fix log

* 縦スクロールを無効化

* MkStickyContainer化

* regenerate locales index.d.ts

* fix

* fix

* テスト

* ランダム値によるUI変更の抑制

* テスト

* tableタグやめる

* fix last-child css

* fix overflow css

* fix endpoint.ts

* tweak css

* 最新への追従とレイアウト微調整

* ソートキーの指定方法を他と合わせた

* fix focus

* fix layout

* v2エンドポイントのルールに対応

* 表示条件などを微調整

* fix MkDataCell.vue

* fix error code

* fix error

* add comment to MkModal.vue

* Update index.d.ts

* fix CHANGELOG.md

* fix color theme

* fix CHANGELOG.md

* fix CHANGELOG.md

* fix center

* fix: テーブルにフォーカスがあり、通常状態であるときはキーイベントの伝搬を止める

* fix: ロール選択用のダイアログにてコンディショナルロールを×ボタンで除外できなかったのを修正

* fix remote list folder

* sticky footers

* chore: fix ci error(just single line-break diff)

* fix loading

* fix like

* comma to space

* fix ci

* fix ci

* removed align-center

---------

Co-authored-by: osamu <46447427+sam-osamu@users.noreply.github.com>
Co-authored-by: syuilo <4439005+syuilo@users.noreply.github.com>
Co-authored-by: Sayamame-beans <61457993+Sayamame-beans@users.noreply.github.com>

1 files changed, 203 insertions, 21 deletions

diff --git a/packages/backend/src/core/CustomEmojiService.ts b/packages/backend/src/core/CustomEmojiService.ts
index 4566113449..da71a5de6f 100644
--- a/packages/backend/src/core/CustomEmojiService.ts
+++ b/packages/backend/src/core/CustomEmojiService.ts
@@ -4,24 +4,59 @@
  */
 
 import { Inject, Injectable, OnApplicationShutdown } from '@nestjs/common';
-import { In, IsNull } from 'typeorm';
 import * as Redis from 'ioredis';
-import { DI } from '@/di-symbols.js';
-import { IdService } from '@/core/IdService.js';
+import { In, IsNull } from 'typeorm';
 import { EmojiEntityService } from '@/core/entities/EmojiEntityService.js';
 import { GlobalEventService } from '@/core/GlobalEventService.js';
-import type { MiDriveFile } from '@/models/DriveFile.js';
-import type { MiEmoji } from '@/models/Emoji.js';
-import type { EmojisRepository, MiRole, MiUser } from '@/models/_.js';
+import { IdService } from '@/core/IdService.js';
+import { ModerationLogService } from '@/core/ModerationLogService.js';
+import { UtilityService } from '@/core/UtilityService.js';
 import { bindThis } from '@/decorators.js';
+import { DI } from '@/di-symbols.js';
 import { MemoryKVCache, RedisSingleCache } from '@/misc/cache.js';
-import { UtilityService } from '@/core/UtilityService.js';
-import { query } from '@/misc/prelude/url.js';
+import { sqlLikeEscape } from '@/misc/sql-like-escape.js';
+import type { EmojisRepository, MiRole, MiUser } from '@/models/_.js';
+import type { MiEmoji } from '@/models/Emoji.js';
 import type { Serialized } from '@/types.js';
-import { ModerationLogService } from '@/core/ModerationLogService.js';
 
 const parseEmojiStrRegexp = /^([-\w]+)(?:@([\w.-]+))?$/;
 
+export const fetchEmojisHostTypes = [
+	'local',
+	'remote',
+	'all',
+] as const;
+export type FetchEmojisHostTypes = typeof fetchEmojisHostTypes[number];
+export const fetchEmojisSortKeys = [
+	'+id',
+	'-id',
+	'+updatedAt',
+	'-updatedAt',
+	'+name',
+	'-name',
+	'+host',
+	'-host',
+	'+uri',
+	'-uri',
+	'+publicUrl',
+	'-publicUrl',
+	'+type',
+	'-type',
+	'+aliases',
+	'-aliases',
+	'+category',
+	'-category',
+	'+license',
+	'-license',
+	'+isSensitive',
+	'-isSensitive',
+	'+localOnly',
+	'-localOnly',
+	'+roleIdsThatCanBeUsedThisEmojiAsReaction',
+	'-roleIdsThatCanBeUsedThisEmojiAsReaction',
+] as const;
+export type FetchEmojisSortKeys = typeof fetchEmojisSortKeys[number];
+
 @Injectable()
 export class CustomEmojiService implements OnApplicationShutdown {
 	private emojisCache: MemoryKVCache<MiEmoji | null>;
@@ -30,10 +65,8 @@ export class CustomEmojiService implements OnApplicationShutdown {
 	constructor(
 		@Inject(DI.redis)
 		private redisClient: Redis.Redis,
-
 		@Inject(DI.emojisRepository)
 		private emojisRepository: EmojisRepository,
-
 		private utilityService: UtilityService,
 		private idService: IdService,
 		private emojiEntityService: EmojiEntityService,
@@ -58,7 +91,9 @@ export class CustomEmojiService implements OnApplicationShutdown {
 
 	@bindThis
 	public async add(data: {
-		driveFile: MiDriveFile;
+		originalUrl: string;
+		publicUrl: string;
+		fileType: string;
 		name: string;
 		category: string | null;
 		aliases: string[];
@@ -75,9 +110,9 @@ export class CustomEmojiService implements OnApplicationShutdown {
 			category: data.category,
 			host: data.host,
 			aliases: data.aliases,
-			originalUrl: data.driveFile.url,
-			publicUrl: data.driveFile.webpublicUrl ?? data.driveFile.url,
-			type: data.driveFile.webpublicType ?? data.driveFile.type,
+			originalUrl: data.originalUrl,
+			publicUrl: data.publicUrl,
+			type: data.fileType,
 			license: data.license,
 			isSensitive: data.isSensitive,
 			localOnly: data.localOnly,
@@ -105,8 +140,10 @@ export class CustomEmojiService implements OnApplicationShutdown {
 	@bindThis
 	public async update(data: (
 		{ id: MiEmoji['id'], name?: string; } | { name: string; id?: MiEmoji['id'], }
-	) & {
-		driveFile?: MiDriveFile;
+		) & {
+		originalUrl?: string;
+		publicUrl?: string;
+		fileType?: string;
 		category?: string | null;
 		aliases?: string[];
 		license?: string | null;
@@ -139,9 +176,9 @@ export class CustomEmojiService implements OnApplicationShutdown {
 			license: data.license,
 			isSensitive: data.isSensitive,
 			localOnly: data.localOnly,
-			originalUrl: data.driveFile != null ? data.driveFile.url : undefined,
-			publicUrl: data.driveFile != null ? (data.driveFile.webpublicUrl ?? data.driveFile.url) : undefined,
-			type: data.driveFile != null ? (data.driveFile.webpublicType ?? data.driveFile.type) : undefined,
+			originalUrl: data.originalUrl,
+			publicUrl: data.publicUrl,
+			type: data.fileType,
 			roleIdsThatCanBeUsedThisEmojiAsReaction: data.roleIdsThatCanBeUsedThisEmojiAsReaction ?? undefined,
 		});
 
@@ -308,7 +345,7 @@ export class CustomEmojiService implements OnApplicationShutdown {
 
 	@bindThis
 	private normalizeHost(src: string | undefined, noteUserHost: string | null): string | null {
-	// クエリに使うホスト
+		// クエリに使うホスト
 		let host = src === '.' ? null	// .はローカルホスト (ここがマッチするのはリアクションのみ)
 			: src === undefined ? noteUserHost	// ノートなどでホスト省略表記の場合はローカルホスト (ここがリアクションにマッチすることはない)
 			: this.utilityService.isSelfHost(src) ? null	// 自ホスト指定
@@ -415,6 +452,151 @@ export class CustomEmojiService implements OnApplicationShutdown {
 	}
 
 	@bindThis
+	public async fetchEmojis(
+		params?: {
+			query?: {
+				updatedAtFrom?: string;
+				updatedAtTo?: string;
+				name?: string;
+				host?: string;
+				uri?: string;
+				publicUrl?: string;
+				type?: string;
+				aliases?: string;
+				category?: string;
+				license?: string;
+				isSensitive?: boolean;
+				localOnly?: boolean;
+				hostType?: FetchEmojisHostTypes;
+				roleIds?: string[];
+			},
+			sinceId?: string;
+			untilId?: string;
+		},
+		opts?: {
+			limit?: number;
+			page?: number;
+			sortKeys?: FetchEmojisSortKeys[]
+		},
+	) {
+		function multipleWordsToQuery(words: string) {
+			return words.split(/\s/).filter(x => x.length > 0).map(x => `%${sqlLikeEscape(x)}%`);
+		}
+
+		const builder = this.emojisRepository.createQueryBuilder('emoji');
+		if (params?.query) {
+			const q = params.query;
+			if (q.updatedAtFrom) {
+				// noIndexScan
+				builder.andWhere('CAST(emoji.updatedAt AS DATE) >= :updateAtFrom', { updateAtFrom: q.updatedAtFrom });
+			}
+			if (q.updatedAtTo) {
+				// noIndexScan
+				builder.andWhere('CAST(emoji.updatedAt AS DATE) <= :updateAtTo', { updateAtTo: q.updatedAtTo });
+			}
+			if (q.name) {
+				builder.andWhere('emoji.name ~~ ANY(ARRAY[:...name])', { name: multipleWordsToQuery(q.name) });
+			}
+
+			switch (true) {
+				case q.hostType === 'local': {
+					builder.andWhere('emoji.host IS NULL');
+					break;
+				}
+				case q.hostType === 'remote': {
+					if (q.host) {
+						// noIndexScan
+						builder.andWhere('emoji.host ~~ ANY(ARRAY[:...host])', { host: multipleWordsToQuery(q.host) });
+					} else {
+						builder.andWhere('emoji.host IS NOT NULL');
+					}
+					break;
+				}
+			}
+
+			if (q.uri) {
+				// noIndexScan
+				builder.andWhere('emoji.uri ~~ ANY(ARRAY[:...uri])', { uri: multipleWordsToQuery(q.uri) });
+			}
+			if (q.publicUrl) {
+				// noIndexScan
+				builder.andWhere('emoji.publicUrl ~~ ANY(ARRAY[:...publicUrl])', { publicUrl: multipleWordsToQuery(q.publicUrl) });
+			}
+			if (q.type) {
+				// noIndexScan
+				builder.andWhere('emoji.type ~~ ANY(ARRAY[:...type])', { type: multipleWordsToQuery(q.type) });
+			}
+			if (q.aliases) {
+				// noIndexScan
+				const subQueryBuilder = builder.subQuery()
+					.select('COUNT(0)', 'count')
+					.from(
+						sq2 => sq2
+							.select('unnest(subEmoji.aliases)', 'alias')
+							.addSelect('subEmoji.id', 'id')
+							.from('emoji', 'subEmoji'),
+						'aliasTable',
+					)
+					.where('"emoji"."id" = "aliasTable"."id"')
+					.andWhere('"aliasTable"."alias" ~~ ANY(ARRAY[:...aliases])', { aliases: multipleWordsToQuery(q.aliases) });
+
+				builder.andWhere(`(${subQueryBuilder.getQuery()}) > 0`);
+			}
+			if (q.category) {
+				builder.andWhere('emoji.category ~~ ANY(ARRAY[:...category])', { category: multipleWordsToQuery(q.category) });
+			}
+			if (q.license) {
+				// noIndexScan
+				builder.andWhere('emoji.license ~~ ANY(ARRAY[:...license])', { license: multipleWordsToQuery(q.license) });
+			}
+			if (q.isSensitive != null) {
+				// noIndexScan
+				builder.andWhere('emoji.isSensitive = :isSensitive', { isSensitive: q.isSensitive });
+			}
+			if (q.localOnly != null) {
+				// noIndexScan
+				builder.andWhere('emoji.localOnly = :localOnly', { localOnly: q.localOnly });
+			}
+			if (q.roleIds && q.roleIds.length > 0) {
+				builder.andWhere('emoji.roleIdsThatCanBeUsedThisEmojiAsReaction && ARRAY[:...roleIds]::VARCHAR[]', { roleIds: q.roleIds });
+			}
+		}
+
+		if (params?.sinceId) {
+			builder.andWhere('emoji.id > :sinceId', { sinceId: params.sinceId });
+		}
+		if (params?.untilId) {
+			builder.andWhere('emoji.id < :untilId', { untilId: params.untilId });
+		}
+
+		if (opts?.sortKeys && opts.sortKeys.length > 0) {
+			for (const sortKey of opts.sortKeys) {
+				const direction = sortKey.startsWith('-') ? 'DESC' : 'ASC';
+				const key = sortKey.replace(/^[+-]/, '');
+				builder.addOrderBy(`emoji.${key}`, direction);
+			}
+		} else {
+			builder.addOrderBy('emoji.id', 'DESC');
+		}
+
+		const limit = opts?.limit ?? 10;
+		if (opts?.page) {
+			builder.skip((opts.page - 1) * limit);
+		}
+
+		builder.take(limit);
+
+		const [emojis, count] = await builder.getManyAndCount();
+
+		return {
+			emojis,
+			count: (count > limit ? emojis.length : count),
+			allCount: count,
+			allPages: Math.ceil(count / limit),
+		};
+	}
+
+	@bindThis
 	public dispose(): void {
 		this.emojisCache.dispose();
 	}