diff options
| author | dakkar <dakkar@thenautilus.net> | 2025-05-02 15:26:15 +0100 |
|---|---|---|
| committer | dakkar <dakkar@thenautilus.net> | 2025-05-02 15:57:56 +0100 |
| commit | 31d93c80525ef450f528287c50ed22270892c432 (patch) | |
| tree | 60b68311047061999dc2eeba77583b302e1005d8 /packages/backend/src/core/chart | |
| parent | enforce maxFileSize for remote users (diff) | |
| download | sharkey-31d93c80525ef450f528287c50ed22270892c432.tar.gz sharkey-31d93c80525ef450f528287c50ed22270892c432.tar.bz2 sharkey-31d93c80525ef450f528287c50ed22270892c432.zip | |
nicer SQL checks for blocked hosts
instead of passing a (possibly gigantic) array from JS, we get
PostgreSQL to look at the value in the `meta` table directly
tested the `federation/instances` endpoint, and the `QueryService`
methods; I have not tested the charts
Diffstat (limited to 'packages/backend/src/core/chart')
| -rw-r--r-- | packages/backend/src/core/chart/charts/federation.ts | 10 |
1 files changed, 5 insertions, 5 deletions
diff --git a/packages/backend/src/core/chart/charts/federation.ts b/packages/backend/src/core/chart/charts/federation.ts index c9b43cc66d..bf702884ca 100644 --- a/packages/backend/src/core/chart/charts/federation.ts +++ b/packages/backend/src/core/chart/charts/federation.ts @@ -64,21 +64,21 @@ 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(ARRAY[:...blocked])', { blocked: this.meta.blockedHosts.flatMap(x => [x, `%.${x}`]) }) + .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() })`) .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(ARRAY[:...blocked])', { blocked: this.meta.blockedHosts.flatMap(x => [x, `%.${x}`]) }) + .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() })`) .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(ARRAY[:...blocked])', { blocked: this.meta.blockedHosts.flatMap(x => [x, `%.${x}`]) }) + .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() })`) .andWhere(`following.followeeHost IN (${ pubsubSubQuery.getQuery() })`) .setParameters(pubsubSubQuery.getParameters()) @@ -87,7 +87,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(ARRAY[:...blocked])', { blocked: this.meta.blockedHosts.flatMap(x => [x, `%.${x}`]) }) + .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.suspensionState = \'none\'') .andWhere('instance.isNotResponding = false') .getRawOne() @@ -95,7 +95,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(ARRAY[:...blocked])', { blocked: this.meta.blockedHosts.flatMap(x => [x, `%.${x}`]) }) + .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.suspensionState = \'none\'') .andWhere('instance.isNotResponding = false') .getRawOne() |