merge: Merge upstream 2025.2.0 (!886)

View MR for information: https://activitypub.software/TransFem-org/Sharkey/-/merge_requests/886

Approved-by: Marie <github@yuugi.dev>
Approved-by: Amber Null <puppygirlhornyposting@gmail.com>

1 files changed, 209 insertions, 25 deletions

diff --git a/packages/backend/src/core/CustomEmojiService.ts b/packages/backend/src/core/CustomEmojiService.ts
index cc33fb5c0b..165d58d1b2 100644
--- a/packages/backend/src/core/CustomEmojiService.ts
+++ b/packages/backend/src/core/CustomEmojiService.ts
@@ -4,19 +4,18 @@
  */
 
 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 { DriveFilesRepository, EmojisRepository, MiRole, MiUser } from '@/models/_.js';
+import { IdService } from '@/core/IdService.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 { DriveFilesRepository, 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';
 import type { Config } from '@/config.js';
@@ -24,6 +23,42 @@ import { DriveService } from './DriveService.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>;
@@ -32,16 +67,12 @@ export class CustomEmojiService implements OnApplicationShutdown {
 	constructor(
 		@Inject(DI.redis)
 		private redisClient: Redis.Redis,
-
 		@Inject(DI.config)
 		private config: Config,
-
 		@Inject(DI.emojisRepository)
 		private emojisRepository: EmojisRepository,
-
 		@Inject(DI.driveFilesRepository)
 		private driveFilesRepository: DriveFilesRepository,
-
 		private utilityService: UtilityService,
 		private idService: IdService,
 		private emojiEntityService: EmojiEntityService,
@@ -67,7 +98,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[];
@@ -84,9 +117,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,
@@ -115,7 +148,9 @@ export class CustomEmojiService implements OnApplicationShutdown {
 	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;
@@ -148,18 +183,22 @@ 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,
 		});
 
 		this.localEmojisCache.refresh();
 
-		if (data.driveFile != null) {
-			const file = await this.driveFilesRepository.findOneBy({ url: emoji.originalUrl, userHost: emoji.host ? emoji.host : IsNull() });
-			if (file && file.id !== data.driveFile.id) {
-				await this.driveService.deleteFile(file, false, moderator ? moderator : undefined);
+		// If we're changing the file, then we need to delete the old one
+		if (data.originalUrl != null && data.originalUrl !== emoji.originalUrl) {
+			const oldFile = await this.driveFilesRepository.findOneBy({ url: emoji.originalUrl, userHost: emoji.host ? emoji.host : IsNull() });
+			const newFile = await this.driveFilesRepository.findOneBy({ url: data.originalUrl, userHost: emoji.host ? emoji.host : IsNull() });
+
+			// But DON'T delete if this is the same file reference, otherwise we'll break the emoji!
+			if (oldFile && newFile && oldFile.id !== newFile.id) {
+				await this.driveService.deleteFile(oldFile, false, moderator ? moderator : undefined);
 			}
 		}
 
@@ -336,7 +375,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	// 自ホスト指定
@@ -445,6 +484,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();
 	}