server/models/utils.ts

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