server/models/utils.ts

   1 import { Model, Sequelize } from 'sequelize-typescript'
   2 import * as validator from 'validator'
   3 import { Col } from 'sequelize/types/lib/utils'
   4 import { literal, OrderItem } from 'sequelize'
   5
   6 type SortType = { sortModel: string, sortValue: string }
   7
   8 // Translate for example "-name" to [ [ 'name', 'DESC' ], [ 'id', 'ASC' ] ]
   9 function getSort (value: string, lastSort: OrderItem = [ 'id', 'ASC' ]): OrderItem[] {
  10   const { direction, field } = buildDirectionAndField(value)
  11
  12   let finalField: string | Col
  13
  14   if (field.toLowerCase() === 'match') { // Search
  15     finalField = Sequelize.col('similarity')
  16   } else if (field === 'videoQuotaUsed') { // Users list
  17     finalField = Sequelize.col('videoQuotaUsed')
  18   } else {
  19     finalField = field
  20   }
  21
  22   return [ [ finalField, direction ], lastSort ]
  23 }
  24
  25 function getVideoSort (value: string, lastSort: OrderItem = [ 'id', 'ASC' ]): OrderItem[] {
  26   const { direction, field } = buildDirectionAndField(value)
  27
  28   if (field.toLowerCase() === 'trending') { // Sort by aggregation
  29     return [
  30       [ Sequelize.fn('COALESCE', Sequelize.fn('SUM', Sequelize.col('VideoViews.views')), '0'), direction ],
  31
  32       [ Sequelize.col('VideoModel.views'), direction ],
  33
  34       lastSort
  35     ]
  36   }
  37
  38   let finalField: string | Col
  39
  40   // Alias
  41   if (field.toLowerCase() === 'match') { // Search
  42     finalField = Sequelize.col('similarity')
  43   } else {
  44     finalField = field
  45   }
  46
  47   const firstSort = typeof finalField === 'string'
  48     ? finalField.split('.').concat([ direction ]) as any // FIXME: sequelize typings
  49     : [ finalField, direction ]
  50
  51   return [ firstSort, lastSort ]
  52 }
  53
  54 function getBlacklistSort (model: any, value: string, lastSort: OrderItem = [ 'id', 'ASC' ]): OrderItem[] {
  55   const [ firstSort ] = getSort(value)
  56
  57   if (model) return [ [ literal(`"${model}.${firstSort[ 0 ]}" ${firstSort[ 1 ]}`) ], lastSort ] as any[] // FIXME: typings
  58   return [ firstSort, lastSort ]
  59 }
  60
  61 function getFollowsSort (value: string, lastSort: OrderItem = [ 'id', 'ASC' ]): OrderItem[] {
  62   const { direction, field } = buildDirectionAndField(value)
  63
  64   if (field === 'redundancyAllowed') {
  65     return [
  66       [ 'ActorFollowing', 'Server', 'redundancyAllowed', direction ],
  67       lastSort
  68     ]
  69   }
  70
  71   return getSort(value, lastSort)
  72 }
  73
  74 function isOutdated (model: { createdAt: Date, updatedAt: Date }, refreshInterval: number) {
  75   const now = Date.now()
  76   const createdAtTime = model.createdAt.getTime()
  77   const updatedAtTime = model.updatedAt.getTime()
  78
  79   return (now - createdAtTime) > refreshInterval && (now - updatedAtTime) > refreshInterval
  80 }
  81
  82 function throwIfNotValid (value: any, validator: (value: any) => boolean, fieldName = 'value', nullable = false) {
  83   if (nullable && (value === null || value === undefined)) return
  84
  85   if (validator(value) === false) {
  86     throw new Error(`"${value}" is not a valid ${fieldName}.`)
  87   }
  88 }
  89
  90 function buildTrigramSearchIndex (indexName: string, attribute: string) {
  91   return {
  92     name: indexName,
  93     fields: [ Sequelize.literal('lower(immutable_unaccent(' + attribute + '))') as any ],
  94     using: 'gin',
  95     operator: 'gin_trgm_ops'
  96   }
  97 }
  98
  99 function createSimilarityAttribute (col: string, value: string) {
 100   return Sequelize.fn(
 101     'similarity',
 102
 103     searchTrigramNormalizeCol(col),
 104
 105     searchTrigramNormalizeValue(value)
 106   )
 107 }
 108
 109 function buildBlockedAccountSQL (serverAccountId: number, userAccountId?: number) {
 110   const blockerIds = [ serverAccountId ]
 111   if (userAccountId) blockerIds.push(userAccountId)
 112
 113   const blockerIdsString = blockerIds.join(', ')
 114
 115   return 'SELECT "targetAccountId" AS "id" FROM "accountBlocklist" WHERE "accountId" IN (' + blockerIdsString + ')' +
 116     ' UNION ALL ' +
 117     'SELECT "account"."id" AS "id" FROM account INNER JOIN "actor" ON account."actorId" = actor.id ' +
 118     'INNER JOIN "serverBlocklist" ON "actor"."serverId" = "serverBlocklist"."targetServerId" ' +
 119     'WHERE "serverBlocklist"."accountId" IN (' + blockerIdsString + ')'
 120 }
 121
 122 function buildServerIdsFollowedBy (actorId: any) {
 123   const actorIdNumber = parseInt(actorId + '', 10)
 124
 125   return '(' +
 126     'SELECT "actor"."serverId" FROM "actorFollow" ' +
 127     'INNER JOIN "actor" ON actor.id = "actorFollow"."targetActorId" ' +
 128     'WHERE "actorFollow"."actorId" = ' + actorIdNumber +
 129   ')'
 130 }
 131
 132 function buildWhereIdOrUUID (id: number | string) {
 133   return validator.isInt('' + id) ? { id } : { uuid: id }
 134 }
 135
 136 function parseAggregateResult (result: any) {
 137   if (!result) return 0
 138
 139   const total = parseInt(result + '', 10)
 140   if (isNaN(total)) return 0
 141
 142   return total
 143 }
 144
 145 const createSafeIn = (model: typeof Model, stringArr: (string | number)[]) => {
 146   return stringArr.map(t => model.sequelize.escape('' + t))
 147                   .join(', ')
 148 }
 149
 150 function buildLocalAccountIdsIn () {
 151   return literal(
 152     '(SELECT "account"."id" FROM "account" INNER JOIN "actor" ON "actor"."id" = "account"."actorId" AND "actor"."serverId" IS NULL)'
 153   )
 154 }
 155
 156 function buildLocalActorIdsIn () {
 157   return literal(
 158     '(SELECT "actor"."id" FROM "actor" WHERE "actor"."serverId" IS NULL)'
 159   )
 160 }
 161
 162 // ---------------------------------------------------------------------------
 163
 164 export {
 165   buildBlockedAccountSQL,
 166   buildLocalActorIdsIn,
 167   SortType,
 168   buildLocalAccountIdsIn,
 169   getSort,
 170   getVideoSort,
 171   getBlacklistSort,
 172   createSimilarityAttribute,
 173   throwIfNotValid,
 174   buildServerIdsFollowedBy,
 175   buildTrigramSearchIndex,
 176   buildWhereIdOrUUID,
 177   isOutdated,
 178   parseAggregateResult,
 179   getFollowsSort,
 180   createSafeIn
 181 }
 182
 183 // ---------------------------------------------------------------------------
 184
 185 function searchTrigramNormalizeValue (value: string) {
 186   return Sequelize.fn('lower', Sequelize.fn('immutable_unaccent', value))
 187 }
 188
 189 function searchTrigramNormalizeCol (col: string) {
 190   return Sequelize.fn('lower', Sequelize.fn('immutable_unaccent', Sequelize.col(col)))
 191 }
 192
 193 function buildDirectionAndField (value: string) {
 194   let field: string
 195   let direction: 'ASC' | 'DESC'
 196
 197   if (value.substring(0, 1) === '-') {
 198     direction = 'DESC'
 199     field = value.substring(1)
 200   } else {
 201     direction = 'ASC'
 202     field = value
 203   }
 204
 205   return { direction, field }
 206 }