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 (blockerIds: number[]) {
 140   const blockerIdsString = blockerIds.join(', ')
 141
 142   return 'SELECT "targetAccountId" AS "id" FROM "accountBlocklist" WHERE "accountId" IN (' + blockerIdsString + ')' +
 143     ' UNION ALL ' +
 144     'SELECT "account"."id" AS "id" FROM account INNER JOIN "actor" ON account."actorId" = actor.id ' +
 145     'INNER JOIN "serverBlocklist" ON "actor"."serverId" = "serverBlocklist"."targetServerId" ' +
 146     'WHERE "serverBlocklist"."accountId" IN (' + blockerIdsString + ')'
 147 }
 148
 149 function buildServerIdsFollowedBy (actorId: any) {
 150   const actorIdNumber = parseInt(actorId + '', 10)
 151
 152   return '(' +
 153     'SELECT "actor"."serverId" FROM "actorFollow" ' +
 154     'INNER JOIN "actor" ON actor.id = "actorFollow"."targetActorId" ' +
 155     'WHERE "actorFollow"."actorId" = ' + actorIdNumber +
 156     ')'
 157 }
 158
 159 function buildWhereIdOrUUID (id: number | string) {
 160   return validator.isInt('' + id) ? { id } : { uuid: id }
 161 }
 162
 163 function parseAggregateResult (result: any) {
 164   if (!result) return 0
 165
 166   const total = parseInt(result + '', 10)
 167   if (isNaN(total)) return 0
 168
 169   return total
 170 }
 171
 172 const createSafeIn = (model: typeof Model, stringArr: (string | number)[]) => {
 173   return stringArr.map(t => {
 174     return t === null
 175       ? null
 176       : model.sequelize.escape('' + t)
 177   }).join(', ')
 178 }
 179
 180 function buildLocalAccountIdsIn () {
 181   return literal(
 182     '(SELECT "account"."id" FROM "account" INNER JOIN "actor" ON "actor"."id" = "account"."actorId" AND "actor"."serverId" IS NULL)'
 183   )
 184 }
 185
 186 function buildLocalActorIdsIn () {
 187   return literal(
 188     '(SELECT "actor"."id" FROM "actor" WHERE "actor"."serverId" IS NULL)'
 189   )
 190 }
 191
 192 function buildDirectionAndField (value: string) {
 193   let field: string
 194   let direction: 'ASC' | 'DESC'
 195
 196   if (value.substring(0, 1) === '-') {
 197     direction = 'DESC'
 198     field = value.substring(1)
 199   } else {
 200     direction = 'ASC'
 201     field = value
 202   }
 203
 204   return { direction, field }
 205 }
 206
 207 function searchAttribute (sourceField?: string, targetField?: string) {
 208   if (!sourceField) return {}
 209
 210   return {
 211     [targetField]: {
 212       [Op.iLike]: `%${sourceField}%`
 213     }
 214   }
 215 }
 216
 217 // ---------------------------------------------------------------------------
 218
 219 export {
 220   DeepOmit,
 221   buildBlockedAccountSQL,
 222   buildLocalActorIdsIn,
 223   SortType,
 224   buildLocalAccountIdsIn,
 225   getSort,
 226   getCommentSort,
 227   getVideoSort,
 228   getBlacklistSort,
 229   createSimilarityAttribute,
 230   throwIfNotValid,
 231   buildServerIdsFollowedBy,
 232   buildTrigramSearchIndex,
 233   buildWhereIdOrUUID,
 234   isOutdated,
 235   parseAggregateResult,
 236   getFollowsSort,
 237   buildDirectionAndField,
 238   createSafeIn,
 239   searchAttribute
 240 }
 241
 242 // ---------------------------------------------------------------------------
 243
 244 function searchTrigramNormalizeValue (value: string) {
 245   return Sequelize.fn('lower', Sequelize.fn('immutable_unaccent', value))
 246 }
 247
 248 function searchTrigramNormalizeCol (col: string) {
 249   return Sequelize.fn('lower', Sequelize.fn('immutable_unaccent', Sequelize.col(col)))
 250 }