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 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 // ---------------------------------------------------------------------------
 211
 212 export {
 213   DeepOmit,
 214   buildBlockedAccountSQL,
 215   buildLocalActorIdsIn,
 216   SortType,
 217   buildLocalAccountIdsIn,
 218   getSort,
 219   getCommentSort,
 220   getVideoSort,
 221   getBlacklistSort,
 222   createSimilarityAttribute,
 223   throwIfNotValid,
 224   buildServerIdsFollowedBy,
 225   buildTrigramSearchIndex,
 226   buildWhereIdOrUUID,
 227   isOutdated,
 228   parseAggregateResult,
 229   getFollowsSort,
 230   buildDirectionAndField,
 231   createSafeIn
 232 }
 233
 234 // ---------------------------------------------------------------------------
 235
 236 function searchTrigramNormalizeValue (value: string) {
 237   return Sequelize.fn('lower', Sequelize.fn('immutable_unaccent', value))
 238 }
 239
 240 function searchTrigramNormalizeCol (col: string) {
 241   return Sequelize.fn('lower', Sequelize.fn('immutable_unaccent', Sequelize.col(col)))
 242 }