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   // TODO: when Typescript supports Object.fromEntries, replace with the Object method
 235   function fromEntries<T> (entries: [keyof T, T[keyof T]][]): T {
 236     return entries.reduce(
 237       (acc, [ key, value ]) => ({ ...acc, [key]: value }),
 238       {} as T
 239     )
 240   }
 241
 242   const objectMap = (obj, fn) => fromEntries(
 243     Object.entries(obj).map(
 244       ([ k, v ], i) => [ k, fn(v, k, i) ]
 245     )
 246   )
 247
 248   return {
 249     // search is the querystring minus defined filters
 250     search: tokens.filter(e => !Object.values(prefixes).some(p => {
 251       if (typeof p === "string") {
 252         return e.startsWith(p)
 253       } else {
 254         return e.startsWith(p.prefix)
 255       }
 256     })).join(' '),
 257     // filters defined in prefixes are added under their own name
 258     ...objectMap(prefixes, p => {
 259       if (typeof p === "string") {
 260         return tokens.filter(e => e.startsWith(p)).map(e => e.slice(p.length)) // we keep the matched item, and remove its prefix
 261       } else {
 262         const _tokens = tokens.filter(e => e.startsWith(p.prefix)).map(e => e.slice(p.prefix.length)).map(p.handler)
 263         // multiple is false by default, meaning we usually just keep the first occurence of a given prefix
 264         if (!p.multiple && _tokens.length > 0) {
 265           return _tokens[0]
 266         } else if (!p.multiple) {
 267           return ''
 268         }
 269         return _tokens
 270       }
 271     })
 272   }
 273 }
 274
 275 // ---------------------------------------------------------------------------
 276
 277 export {
 278   DeepOmit,
 279   buildBlockedAccountSQL,
 280   buildLocalActorIdsIn,
 281   SortType,
 282   buildLocalAccountIdsIn,
 283   getSort,
 284   getCommentSort,
 285   getVideoSort,
 286   getBlacklistSort,
 287   createSimilarityAttribute,
 288   throwIfNotValid,
 289   buildServerIdsFollowedBy,
 290   buildTrigramSearchIndex,
 291   buildWhereIdOrUUID,
 292   isOutdated,
 293   parseAggregateResult,
 294   getFollowsSort,
 295   buildDirectionAndField,
 296   createSafeIn,
 297   searchAttribute,
 298   parseQueryStringFilter
 299 }
 300
 301 // ---------------------------------------------------------------------------
 302
 303 function searchTrigramNormalizeValue (value: string) {
 304   return Sequelize.fn('lower', Sequelize.fn('immutable_unaccent', value))
 305 }
 306
 307 function searchTrigramNormalizeCol (col: string) {
 308   return Sequelize.fn('lower', Sequelize.fn('immutable_unaccent', Sequelize.col(col)))
 309 }