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