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, Op } from 'sequelize'
   5
   6 type Primitive = string | Function | number | boolean | Symbol | undefined | null
   7 type DeepOmitHelper<T, K extends keyof T> = {
   8   [P in K]: // extra level of indirection needed to trigger homomorhic behavior
   9   T[P] extends infer TP // distribute over unions
  10     ? TP extends Primitive
  11       ? TP // leave primitives and functions alone
  12       : TP extends any[]
  13         ? DeepOmitArray<TP, K> // Array special handling
  14         : DeepOmit<TP, K>
  15     : never
  16 }
  17 type DeepOmit<T, K> = T extends Primitive ? T : DeepOmitHelper<T, Exclude<keyof T, K>>
  18
  19 type DeepOmitArray<T extends any[], K> = {
  20   [P in keyof T]: DeepOmit<T[P], K>
  21 }
  22
  23 type SortType = { sortModel: string, sortValue: string }
  24
  25 // Translate for example "-name" to [ [ 'name', 'DESC' ], [ 'id', 'ASC' ] ]
  26 function getSort (value: string, lastSort: OrderItem = [ 'id', 'ASC' ]): OrderItem[] {
  27   const { direction, field } = buildDirectionAndField(value)
  28
  29   let finalField: string | Col
  30
  31   if (field.toLowerCase() === 'match') { // Search
  32     finalField = Sequelize.col('similarity')
  33   } else if (field === 'videoQuotaUsed') { // Users list
  34     finalField = Sequelize.col('videoQuotaUsed')
  35   } else {
  36     finalField = field
  37   }
  38
  39   return [ [ finalField, direction ], lastSort ]
  40 }
  41
  42 function getCommentSort (value: string, lastSort: OrderItem = [ 'id', 'ASC' ]): OrderItem[] {
  43   const { direction, field } = buildDirectionAndField(value)
  44
  45   if (field === 'totalReplies') {
  46     return [
  47       [ Sequelize.literal('"totalReplies"'), direction ],
  48       lastSort
  49     ]
  50   }
  51
  52   return getSort(value, lastSort)
  53 }
  54
  55 function getVideoSort (value: string, lastSort: OrderItem = [ 'id', 'ASC' ]): OrderItem[] {
  56   const { direction, field } = buildDirectionAndField(value)
  57
  58   if (field.toLowerCase() === 'trending') { // Sort by aggregation
  59     return [
  60       [ Sequelize.fn('COALESCE', Sequelize.fn('SUM', Sequelize.col('VideoViews.views')), '0'), direction ],
  61
  62       [ Sequelize.col('VideoModel.views'), direction ],
  63
  64       lastSort
  65     ]
  66   }
  67
  68   let finalField: string | Col
  69
  70   // Alias
  71   if (field.toLowerCase() === 'match') { // Search
  72     finalField = Sequelize.col('similarity')
  73   } else {
  74     finalField = field
  75   }
  76
  77   const firstSort = typeof finalField === 'string'
  78     ? finalField.split('.').concat([ direction ]) as any // FIXME: sequelize typings
  79     : [ finalField, direction ]
  80
  81   return [ firstSort, lastSort ]
  82 }
  83
  84 function getBlacklistSort (model: any, value: string, lastSort: OrderItem = [ 'id', 'ASC' ]): OrderItem[] {
  85   const [ firstSort ] = getSort(value)
  86
  87   if (model) return [ [ literal(`"${model}.${firstSort[0]}" ${firstSort[1]}`) ], lastSort ] as any[] // FIXME: typings
  88   return [ firstSort, lastSort ]
  89 }
  90
  91 function getFollowsSort (value: string, lastSort: OrderItem = [ 'id', 'ASC' ]): OrderItem[] {
  92   const { direction, field } = buildDirectionAndField(value)
  93
  94   if (field === 'redundancyAllowed') {
  95     return [
  96       [ 'ActorFollowing', 'Server', 'redundancyAllowed', direction ],
  97       lastSort
  98     ]
  99   }
 100
 101   return getSort(value, lastSort)
 102 }
 103
 104 function isOutdated (model: { createdAt: Date, updatedAt: Date }, refreshInterval: number) {
 105   const now = Date.now()
 106   const createdAtTime = model.createdAt.getTime()
 107   const updatedAtTime = model.updatedAt.getTime()
 108
 109   return (now - createdAtTime) > refreshInterval && (now - updatedAtTime) > refreshInterval
 110 }
 111
 112 function throwIfNotValid (value: any, validator: (value: any) => boolean, fieldName = 'value', nullable = false) {
 113   if (nullable && (value === null || value === undefined)) return
 114
 115   if (validator(value) === false) {
 116     throw new Error(`"${value}" is not a valid ${fieldName}.`)
 117   }
 118 }
 119
 120 function buildTrigramSearchIndex (indexName: string, attribute: string) {
 121   return {
 122     name: indexName,
 123     fields: [ Sequelize.literal('lower(immutable_unaccent(' + attribute + '))') as any ],
 124     using: 'gin',
 125     operator: 'gin_trgm_ops'
 126   }
 127 }
 128
 129 function createSimilarityAttribute (col: string, value: string) {
 130   return Sequelize.fn(
 131     'similarity',
 132
 133     searchTrigramNormalizeCol(col),
 134
 135     searchTrigramNormalizeValue(value)
 136   )
 137 }
 138
 139 function buildBlockedAccountSQL (serverAccountId: number, userAccountId?: number) {
 140   const blockerIds = [ serverAccountId ]
 141   if (userAccountId) blockerIds.push(userAccountId)
 142
 143   const blockerIdsString = blockerIds.join(', ')
 144
 145   return 'SELECT "targetAccountId" AS "id" FROM "accountBlocklist" WHERE "accountId" IN (' + blockerIdsString + ')' +
 146     ' UNION ALL ' +
 147     'SELECT "account"."id" AS "id" FROM account INNER JOIN "actor" ON account."actorId" = actor.id ' +
 148     'INNER JOIN "serverBlocklist" ON "actor"."serverId" = "serverBlocklist"."targetServerId" ' +
 149     'WHERE "serverBlocklist"."accountId" IN (' + blockerIdsString + ')'
 150 }
 151
 152 function buildServerIdsFollowedBy (actorId: any) {
 153   const actorIdNumber = parseInt(actorId + '', 10)
 154
 155   return '(' +
 156     'SELECT "actor"."serverId" FROM "actorFollow" ' +
 157     'INNER JOIN "actor" ON actor.id = "actorFollow"."targetActorId" ' +
 158     'WHERE "actorFollow"."actorId" = ' + actorIdNumber +
 159     ')'
 160 }
 161
 162 function buildWhereIdOrUUID (id: number | string) {
 163   return validator.isInt('' + id) ? { id } : { uuid: id }
 164 }
 165
 166 function parseAggregateResult (result: any) {
 167   if (!result) return 0
 168
 169   const total = parseInt(result + '', 10)
 170   if (isNaN(total)) return 0
 171
 172   return total
 173 }
 174
 175 const createSafeIn = (model: typeof Model, stringArr: (string | number)[]) => {
 176   return stringArr.map(t => {
 177     return t === null
 178       ? null
 179       : model.sequelize.escape('' + t)
 180   }).join(', ')
 181 }
 182
 183 function buildLocalAccountIdsIn () {
 184   return literal(
 185     '(SELECT "account"."id" FROM "account" INNER JOIN "actor" ON "actor"."id" = "account"."actorId" AND "actor"."serverId" IS NULL)'
 186   )
 187 }
 188
 189 function buildLocalActorIdsIn () {
 190   return literal(
 191     '(SELECT "actor"."id" FROM "actor" WHERE "actor"."serverId" IS NULL)'
 192   )
 193 }
 194
 195 function buildDirectionAndField (value: string) {
 196   let field: string
 197   let direction: 'ASC' | 'DESC'
 198
 199   if (value.substring(0, 1) === '-') {
 200     direction = 'DESC'
 201     field = value.substring(1)
 202   } else {
 203     direction = 'ASC'
 204     field = value
 205   }
 206
 207   return { direction, field }
 208 }
 209
 210 function searchAttribute (sourceField, targetField) {
 211   if (sourceField) {
 212     return {
 213       [targetField]: {
 214         [Op.iLike]: `%${sourceField}%`
 215       }
 216     }
 217   } else {
 218     return {}
 219   }
 220 }
 221
 222 interface QueryStringFilterPrefixes {
 223   [key: string]: string | { prefix: string, handler: Function, multiple?: boolean }
 224 }
 225
 226 function parseQueryStringFilter (q: string, prefixes: QueryStringFilterPrefixes): {
 227   search: string
 228   [key: string]: string | number | string[] | number[]
 229 } {
 230   const tokens = q // tokenize only if we have a querystring
 231     ? [].concat.apply([], q.split('"').map((v, i) => i % 2 ? v : v.split(' '))).filter(Boolean) // split by space unless using double quotes
 232     : []
 233
 234   const objectMap = (obj, fn) => Object.fromEntries(
 235     Object.entries(obj).map(
 236       ([ k, v ], i) => [ k, fn(v, k, i) ]
 237     )
 238   )
 239
 240   return {
 241     // search is the querystring minus defined filters
 242     search: tokens.filter(e => !Object.values(prefixes).some(p => {
 243       if (typeof p === 'string') {
 244         return e.startsWith(p)
 245       } else {
 246         return e.startsWith(p.prefix)
 247       }
 248     })).join(' '),
 249     // filters defined in prefixes are added under their own name
 250     ...objectMap(prefixes, p => {
 251       if (typeof p === 'string') {
 252         return tokens.filter(e => e.startsWith(p)).map(e => e.slice(p.length)) // we keep the matched item, and remove its prefix
 253       } else {
 254         const _tokens = tokens.filter(e => e.startsWith(p.prefix)).map(e => e.slice(p.prefix.length)).map(p.handler)
 255         // multiple is false by default, meaning we usually just keep the first occurence of a given prefix
 256         if (!p.multiple && _tokens.length > 0) {
 257           return _tokens[0]
 258         } else if (!p.multiple) {
 259           return ''
 260         }
 261         return _tokens
 262       }
 263     })
 264   }
 265 }
 266
 267 // ---------------------------------------------------------------------------
 268
 269 export {
 270   DeepOmit,
 271   buildBlockedAccountSQL,
 272   buildLocalActorIdsIn,
 273   SortType,
 274   buildLocalAccountIdsIn,
 275   getSort,
 276   getCommentSort,
 277   getVideoSort,
 278   getBlacklistSort,
 279   createSimilarityAttribute,
 280   throwIfNotValid,
 281   buildServerIdsFollowedBy,
 282   buildTrigramSearchIndex,
 283   buildWhereIdOrUUID,
 284   isOutdated,
 285   parseAggregateResult,
 286   getFollowsSort,
 287   buildDirectionAndField,
 288   createSafeIn,
 289   searchAttribute,
 290   parseQueryStringFilter
 291 }
 292
 293 // ---------------------------------------------------------------------------
 294
 295 function searchTrigramNormalizeValue (value: string) {
 296   return Sequelize.fn('lower', Sequelize.fn('immutable_unaccent', value))
 297 }
 298
 299 function searchTrigramNormalizeCol (col: string) {
 300   return Sequelize.fn('lower', Sequelize.fn('immutable_unaccent', Sequelize.col(col)))
 301 }