use instance block columns instead of checking meta columns

4 files changed, 40 insertions, 44 deletions

diff --git a/packages/backend/src/core/FanoutTimelineEndpointService.ts b/packages/backend/src/core/FanoutTimelineEndpointService.ts
index af2723e99d..f9cf41e854 100644
--- a/packages/backend/src/core/FanoutTimelineEndpointService.ts
+++ b/packages/backend/src/core/FanoutTimelineEndpointService.ts
@@ -136,10 +136,10 @@ export class FanoutTimelineEndpointService {
 				const parentFilter = filter;
 				filter = (note) => {
 					if (!ps.ignoreAuthorFromInstanceBlock) {
-						if (this.utilityService.isBlockedHost(this.meta.blockedHosts, note.userHost)) return false;
+						if (note.userInstance?.isBlocked) return false;
 					}
-					if (note.userId !== note.renoteUserId && this.utilityService.isBlockedHost(this.meta.blockedHosts, note.renoteUserHost)) return false;
-					if (note.userId !== note.replyUserId && this.utilityService.isBlockedHost(this.meta.blockedHosts, note.replyUserHost)) return false;
+					if (note.userId !== note.renoteUserId && note.renoteUserInstance?.isBlocked) return false;
+					if (note.userId !== note.replyUserId && note.replyUserInstance?.isBlocked) return false;
 
 					return parentFilter(note);
 				};
@@ -194,7 +194,10 @@ export class FanoutTimelineEndpointService {
 			.leftJoinAndSelect('note.renote', 'renote')
 			.leftJoinAndSelect('reply.user', 'replyUser')
 			.leftJoinAndSelect('renote.user', 'renoteUser')
-			.leftJoinAndSelect('note.channel', 'channel');
+			.leftJoinAndSelect('note.channel', 'channel')
+			.leftJoinAndSelect('note.userInstance', 'userInstance')
+			.leftJoinAndSelect('note.replyUserInstance', 'replyUserInstance')
+			.leftJoinAndSelect('note.renoteUserInstance', 'renoteUserInstance');
 
 		const notes = (await query.getMany()).filter(noteFilter);
 
diff --git a/packages/backend/src/core/QueryService.ts b/packages/backend/src/core/QueryService.ts
index e87360b00d..1b00f41d20 100644
--- a/packages/backend/src/core/QueryService.ts
+++ b/packages/backend/src/core/QueryService.ts
@@ -255,34 +255,28 @@ export class QueryService {
 	}
 
 	@bindThis
-	public generateBlockedHostQueryForNote(q: SelectQueryBuilder<any>, excludeAuthor?: boolean): void {
-		let nonBlockedHostQuery: (part: string) => string;
-		if (this.meta.blockedHosts.length === 0) {
-			nonBlockedHostQuery = () => '1=1';
-		} else {
-			nonBlockedHostQuery = (match: string) => `('.' || ${match}) NOT ILIKE ALL(select '%.' || x from (select unnest("blockedHosts") as x from "meta") t)`;
-		}
+	public generateBlockedHostQueryForNote(q: SelectQueryBuilder<any>, excludeAuthor?: boolean, allowSilenced = true): void {
+		function checkFor(key: 'user' | 'replyUser' | 'renoteUser') {
+			q.leftJoin(`note.${key}Instance`, `${key}Instance`);
+			q.andWhere(new Brackets(qb => {
+				qb.orWhere(`note.${key}Id IS NULL`) // no corresponding user
+					.orWhere(`note.${key}Host IS NULL`) // local
+					.orWhere(`${key}Instance.isBlocked = false`); // not blocked
 
-		if (excludeAuthor) {
-			const instanceSuspension = (user: string) => new Brackets(qb => qb
-				.where(`note.${user}Id IS NULL`) // no corresponding user
-				.orWhere(`note.userId = note.${user}Id`)
-				.orWhere(`note.${user}Host IS NULL`) // local
-				.orWhere(nonBlockedHostQuery(`note.${user}Host`)));
+				if (!allowSilenced) {
+					qb.orWhere(`${key}Instance.isSilenced = false`); // not silenced
+				}
 
-			q
-				.andWhere(instanceSuspension('replyUser'))
-				.andWhere(instanceSuspension('renoteUser'));
-		} else {
-			const instanceSuspension = (user: string) => new Brackets(qb => qb
-				.where(`note.${user}Id IS NULL`) // no corresponding user
-				.orWhere(`note.${user}Host IS NULL`) // local
-				.orWhere(nonBlockedHostQuery(`note.${user}Host`)));
+				if (excludeAuthor) {
+					qb.orWhere(`note.userId = note.${key}Id`); // author
+				}
+			}));
+		}
 
-			q
-				.andWhere(instanceSuspension('user'))
-				.andWhere(instanceSuspension('replyUser'))
-				.andWhere(instanceSuspension('renoteUser'));
+		if (!excludeAuthor) {
+			checkFor('user');
 		}
+		checkFor('replyUser');
+		checkFor('renoteUser');
 	}
 }
diff --git a/packages/backend/src/core/chart/charts/federation.ts b/packages/backend/src/core/chart/charts/federation.ts
index bf702884ca..b6db6f5454 100644
--- a/packages/backend/src/core/chart/charts/federation.ts
+++ b/packages/backend/src/core/chart/charts/federation.ts
@@ -44,10 +44,6 @@ export default class FederationChart extends Chart<typeof schema> { // eslint-di
 	}
 
 	protected async tickMinor(): Promise<Partial<KVs<typeof schema>>> {
-		const suspendedInstancesQuery = this.instancesRepository.createQueryBuilder('instance')
-			.select('instance.host')
-			.where('instance.suspensionState != \'none\'');
-
 		const pubsubSubQuery = this.followingsRepository.createQueryBuilder('f')
 			.select('f.followerHost')
 			.where('f.followerHost IS NOT NULL');
@@ -64,22 +60,25 @@ export default class FederationChart extends Chart<typeof schema> { // eslint-di
 			this.followingsRepository.createQueryBuilder('following')
 				.select('COUNT(DISTINCT following.followeeHost)')
 				.where('following.followeeHost IS NOT NULL')
-				.andWhere(this.meta.blockedHosts.length === 0 ? '1=1' : '(\'.\' || following.followeeHost) NOT ILIKE ALL(select \'%.\' || x from (select unnest("blockedHosts") as x from "meta") t)')
-				.andWhere(`following.followeeHost NOT IN (${ suspendedInstancesQuery.getQuery() })`)
+				.innerJoin('following.followeeInstance', 'followeeInstance')
+				.andWhere('followeeInstance.suspensionState = \'none\'')
+				.andWhere('followeeInstance.isBlocked = false')
 				.getRawOne()
 				.then(x => parseInt(x.count, 10)),
 			this.followingsRepository.createQueryBuilder('following')
 				.select('COUNT(DISTINCT following.followerHost)')
 				.where('following.followerHost IS NOT NULL')
-				.andWhere(this.meta.blockedHosts.length === 0 ? '1=1' : '(\'.\' || following.followerHost) NOT ILIKE ALL(select \'%.\' || x from (select unnest("blockedHosts") as x from "meta") t)')
-				.andWhere(`following.followerHost NOT IN (${ suspendedInstancesQuery.getQuery() })`)
+				.innerJoin('following.followerInstance', 'followerInstance')
+				.andWhere('followerInstance.isBlocked = false')
+				.andWhere('followerInstance.suspensionState = \'none\'')
 				.getRawOne()
 				.then(x => parseInt(x.count, 10)),
 			this.followingsRepository.createQueryBuilder('following')
 				.select('COUNT(DISTINCT following.followeeHost)')
 				.where('following.followeeHost IS NOT NULL')
-				.andWhere(this.meta.blockedHosts.length === 0 ? '1=1' : '(\'.\' || following.followeeHost) NOT ILIKE ALL(select \'%.\' || x from (select unnest("blockedHosts") as x from "meta") t)')
-				.andWhere(`following.followeeHost NOT IN (${ suspendedInstancesQuery.getQuery() })`)
+				.innerJoin('following.followeeInstance', 'followeeInstance')
+				.andWhere('followeeInstance.isBlocked = false')
+				.andWhere('followeeInstance.suspensionState = \'none\'')
 				.andWhere(`following.followeeHost IN (${ pubsubSubQuery.getQuery() })`)
 				.setParameters(pubsubSubQuery.getParameters())
 				.getRawOne()
@@ -87,7 +86,7 @@ export default class FederationChart extends Chart<typeof schema> { // eslint-di
 			this.instancesRepository.createQueryBuilder('instance')
 				.select('COUNT(instance.id)')
 				.where(`instance.host IN (${ subInstancesQuery.getQuery() })`)
-				.andWhere(this.meta.blockedHosts.length === 0 ? '1=1' : '(\'.\' || instance.host) NOT ILIKE ALL(select \'%.\' || x from (select unnest("blockedHosts") as x from "meta") t)')
+				.andWhere('instance.isBlocked = false')
 				.andWhere('instance.suspensionState = \'none\'')
 				.andWhere('instance.isNotResponding = false')
 				.getRawOne()
@@ -95,7 +94,7 @@ export default class FederationChart extends Chart<typeof schema> { // eslint-di
 			this.instancesRepository.createQueryBuilder('instance')
 				.select('COUNT(instance.id)')
 				.where(`instance.host IN (${ pubInstancesQuery.getQuery() })`)
-				.andWhere(this.meta.blockedHosts.length === 0 ? '1=1' : '(\'.\' || instance.host) NOT ILIKE ALL(select \'%.\' || x from (select unnest("blockedHosts") as x from "meta") t)')
+				.andWhere('instance.isBlocked = false')
 				.andWhere('instance.suspensionState = \'none\'')
 				.andWhere('instance.isNotResponding = false')
 				.getRawOne()
diff --git a/packages/backend/src/core/entities/InstanceEntityService.ts b/packages/backend/src/core/entities/InstanceEntityService.ts
index fcc9bed3bd..332d2943a4 100644
--- a/packages/backend/src/core/entities/InstanceEntityService.ts
+++ b/packages/backend/src/core/entities/InstanceEntityService.ts
@@ -43,7 +43,7 @@ export class InstanceEntityService {
 			isNotResponding: instance.isNotResponding,
 			isSuspended: instance.suspensionState !== 'none',
 			suspensionState: instance.suspensionState,
-			isBlocked: this.utilityService.isBlockedHost(this.meta.blockedHosts, instance.host),
+			isBlocked: instance.isBlocked,
 			softwareName: instance.softwareName,
 			softwareVersion: instance.softwareVersion,
 			openRegistrations: instance.openRegistrations,
@@ -51,8 +51,8 @@ export class InstanceEntityService {
 			description: instance.description,
 			maintainerName: instance.maintainerName,
 			maintainerEmail: instance.maintainerEmail,
-			isSilenced: this.utilityService.isSilencedHost(this.meta.silencedHosts, instance.host),
-			isMediaSilenced: this.utilityService.isMediaSilencedHost(this.meta.mediaSilencedHosts, instance.host),
+			isSilenced: instance.isSilenced,
+			isMediaSilenced: instance.isMediaSilenced,
 			iconUrl: instance.iconUrl,
 			faviconUrl: instance.faviconUrl,
 			themeColor: instance.themeColor,