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 => model.sequelize.escape('' + t))
 160                   .join(', ')
 161 }
 162
 163 function buildLocalAccountIdsIn () {
 164   return literal(
 165     '(SELECT "account"."id" FROM "account" INNER JOIN "actor" ON "actor"."id" = "account"."actorId" AND "actor"."serverId" IS NULL)'
 166   )
 167 }
 168
 169 function buildLocalActorIdsIn () {
 170   return literal(
 171     '(SELECT "actor"."id" FROM "actor" WHERE "actor"."serverId" IS NULL)'
 172   )
 173 }
 174
 175 // ---------------------------------------------------------------------------
 176
 177 export {
 178   buildBlockedAccountSQL,
 179   buildLocalActorIdsIn,
 180   SortType,
 181   buildLocalAccountIdsIn,
 182   getSort,
 183   getCommentSort,
 184   getVideoSort,
 185   getBlacklistSort,
 186   createSimilarityAttribute,
 187   throwIfNotValid,
 188   buildServerIdsFollowedBy,
 189   buildTrigramSearchIndex,
 190   buildWhereIdOrUUID,
 191   isOutdated,
 192   parseAggregateResult,
 193   getFollowsSort,
 194   createSafeIn
 195 }
 196
 197 // ---------------------------------------------------------------------------
 198
 199 function searchTrigramNormalizeValue (value: string) {
 200   return Sequelize.fn('lower', Sequelize.fn('immutable_unaccent', value))
 201 }
 202
 203 function searchTrigramNormalizeCol (col: string) {
 204   return Sequelize.fn('lower', Sequelize.fn('immutable_unaccent', Sequelize.col(col)))
 205 }
 206
 207 function buildDirectionAndField (value: string) {
 208   let field: string
 209   let direction: 'ASC' | 'DESC'
 210
 211   if (value.substring(0, 1) === '-') {
 212     direction = 'DESC'
 213     field = value.substring(1)
 214   } else {
 215     direction = 'ASC'
 216     field = value
 217   }
 218
 219   return { direction, field }
 220 }