server/models/shared/sql.ts

   1 import { literal, Model, ModelStatic } from 'sequelize'
   2 import { forceNumber } from '@shared/core-utils'
   3 import { AttributesOnly } from '@shared/typescript-utils'
   4
   5 function buildLocalAccountIdsIn () {
   6   return literal(
   7     '(SELECT "account"."id" FROM "account" INNER JOIN "actor" ON "actor"."id" = "account"."actorId" AND "actor"."serverId" IS NULL)'
   8   )
   9 }
  10
  11 function buildLocalActorIdsIn () {
  12   return literal(
  13     '(SELECT "actor"."id" FROM "actor" WHERE "actor"."serverId" IS NULL)'
  14   )
  15 }
  16
  17 function buildBlockedAccountSQL (blockerIds: number[]) {
  18   const blockerIdsString = blockerIds.join(', ')
  19
  20   return 'SELECT "targetAccountId" AS "id" FROM "accountBlocklist" WHERE "accountId" IN (' + blockerIdsString + ')' +
  21     ' UNION ' +
  22     'SELECT "account"."id" AS "id" FROM account INNER JOIN "actor" ON account."actorId" = actor.id ' +
  23     'INNER JOIN "serverBlocklist" ON "actor"."serverId" = "serverBlocklist"."targetServerId" ' +
  24     'WHERE "serverBlocklist"."accountId" IN (' + blockerIdsString + ')'
  25 }
  26
  27 function buildServerIdsFollowedBy (actorId: any) {
  28   const actorIdNumber = forceNumber(actorId)
  29
  30   return '(' +
  31     'SELECT "actor"."serverId" FROM "actorFollow" ' +
  32     'INNER JOIN "actor" ON actor.id = "actorFollow"."targetActorId" ' +
  33     'WHERE "actorFollow"."actorId" = ' + actorIdNumber +
  34     ')'
  35 }
  36
  37 function buildSQLAttributes<M extends Model> (options: {
  38   model: ModelStatic<M>
  39   tableName: string
  40
  41   excludeAttributes?: Exclude<keyof AttributesOnly<M>, symbol>[]
  42   aliasPrefix?: string
  43 }) {
  44   const { model, tableName, aliasPrefix, excludeAttributes } = options
  45
  46   const attributes = Object.keys(model.getAttributes()) as Exclude<keyof AttributesOnly<M>, symbol>[]
  47
  48   return attributes
  49     .filter(a => {
  50       if (!excludeAttributes) return true
  51       if (excludeAttributes.includes(a)) return false
  52
  53       return true
  54     })
  55     .map(a => {
  56       return `"${tableName}"."${a}" AS "${aliasPrefix || ''}${a}"`
  57     })
  58 }
  59
  60 // ---------------------------------------------------------------------------
  61
  62 export {
  63   buildSQLAttributes,
  64   buildBlockedAccountSQL,
  65   buildServerIdsFollowedBy,
  66   buildLocalAccountIdsIn,
  67   buildLocalActorIdsIn
  68 }