server/models/utils.ts

   1 import { literal, Op, OrderItem } from 'sequelize'
   2 import { Model, Sequelize } from 'sequelize-typescript'
   3 import { Col } from 'sequelize/types/lib/utils'
   4 import validator from 'validator'
   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 getPlaylistSort (value: string, lastSort: OrderItem = [ 'id', 'ASC' ]): OrderItem[] {
  26   const { direction, field } = buildDirectionAndField(value)
  27
  28   if (field.toLowerCase() === 'name') {
  29     return [ [ 'displayName', direction ], lastSort ]
  30   }
  31
  32   return getSort(value, lastSort)
  33 }
  34
  35 function getCommentSort (value: string, lastSort: OrderItem = [ 'id', 'ASC' ]): OrderItem[] {
  36   const { direction, field } = buildDirectionAndField(value)
  37
  38   if (field === 'totalReplies') {
  39     return [
  40       [ Sequelize.literal('"totalReplies"'), direction ],
  41       lastSort
  42     ]
  43   }
  44
  45   return getSort(value, lastSort)
  46 }
  47
  48 function getVideoSort (value: string, lastSort: OrderItem = [ 'id', 'ASC' ]): OrderItem[] {
  49   const { direction, field } = buildDirectionAndField(value)
  50
  51   if (field.toLowerCase() === 'trending') { // Sort by aggregation
  52     return [
  53       [ Sequelize.fn('COALESCE', Sequelize.fn('SUM', Sequelize.col('VideoViews.views')), '0'), direction ],
  54
  55       [ Sequelize.col('VideoModel.views'), direction ],
  56
  57       lastSort
  58     ]
  59   }
  60
  61   let finalField: string | Col
  62
  63   // Alias
  64   if (field.toLowerCase() === 'match') { // Search
  65     finalField = Sequelize.col('similarity')
  66   } else {
  67     finalField = field
  68   }
  69
  70   const firstSort = typeof finalField === 'string'
  71     ? finalField.split('.').concat([ direction ]) as any // FIXME: sequelize typings
  72     : [ finalField, direction ]
  73
  74   return [ firstSort, lastSort ]
  75 }
  76
  77 function getBlacklistSort (model: any, value: string, lastSort: OrderItem = [ 'id', 'ASC' ]): OrderItem[] {
  78   const [ firstSort ] = getSort(value)
  79
  80   if (model) return [ [ literal(`"${model}.${firstSort[0]}" ${firstSort[1]}`) ], lastSort ] as any[] // FIXME: typings
  81   return [ firstSort, lastSort ]
  82 }
  83
  84 function getFollowsSort (value: string, lastSort: OrderItem = [ 'id', 'ASC' ]): OrderItem[] {
  85   const { direction, field } = buildDirectionAndField(value)
  86
  87   if (field === 'redundancyAllowed') {
  88     return [
  89       [ 'ActorFollowing', 'Server', 'redundancyAllowed', direction ],
  90       lastSort
  91     ]
  92   }
  93
  94   return getSort(value, lastSort)
  95 }
  96
  97 function isOutdated (model: { createdAt: Date, updatedAt: Date }, refreshInterval: number) {
  98   const now = Date.now()
  99   const createdAtTime = model.createdAt.getTime()
 100   const updatedAtTime = model.updatedAt.getTime()
 101
 102   return (now - createdAtTime) > refreshInterval && (now - updatedAtTime) > refreshInterval
 103 }
 104
 105 function throwIfNotValid (value: any, validator: (value: any) => boolean, fieldName = 'value', nullable = false) {
 106   if (nullable && (value === null || value === undefined)) return
 107
 108   if (validator(value) === false) {
 109     throw new Error(`"${value}" is not a valid ${fieldName}.`)
 110   }
 111 }
 112
 113 function buildTrigramSearchIndex (indexName: string, attribute: string) {
 114   return {
 115     name: indexName,
 116     fields: [ Sequelize.literal('lower(immutable_unaccent(' + attribute + '))') as any ],
 117     using: 'gin',
 118     operator: 'gin_trgm_ops'
 119   }
 120 }
 121
 122 function createSimilarityAttribute (col: string, value: string) {
 123   return Sequelize.fn(
 124     'similarity',
 125
 126     searchTrigramNormalizeCol(col),
 127
 128     searchTrigramNormalizeValue(value)
 129   )
 130 }
 131
 132 function buildBlockedAccountSQL (blockerIds: number[]) {
 133   const blockerIdsString = blockerIds.join(', ')
 134
 135   return 'SELECT "targetAccountId" AS "id" FROM "accountBlocklist" WHERE "accountId" IN (' + blockerIdsString + ')' +
 136     ' UNION ALL ' +
 137     'SELECT "account"."id" AS "id" FROM account INNER JOIN "actor" ON account."actorId" = actor.id ' +
 138     'INNER JOIN "serverBlocklist" ON "actor"."serverId" = "serverBlocklist"."targetServerId" ' +
 139     'WHERE "serverBlocklist"."accountId" IN (' + blockerIdsString + ')'
 140 }
 141
 142 function buildBlockedAccountSQLOptimized (columnNameJoin: string, blockerIds: number[]) {
 143   const blockerIdsString = blockerIds.join(', ')
 144
 145   return [
 146     literal(
 147       `NOT EXISTS (` +
 148       `  SELECT 1 FROM "accountBlocklist" ` +
 149       `  WHERE "targetAccountId" = ${columnNameJoin} ` +
 150       `  AND "accountId" IN (${blockerIdsString})` +
 151       `)`
 152     ),
 153
 154     literal(
 155       `NOT EXISTS (` +
 156       `  SELECT 1 FROM "account" ` +
 157       `  INNER JOIN "actor" ON account."actorId" = actor.id ` +
 158       `  INNER JOIN "serverBlocklist" ON "actor"."serverId" = "serverBlocklist"."targetServerId" ` +
 159       `  WHERE "account"."id" = ${columnNameJoin} ` +
 160       `  AND "serverBlocklist"."accountId" IN (${blockerIdsString})` +
 161       `)`
 162     )
 163   ]
 164 }
 165
 166 function buildServerIdsFollowedBy (actorId: any) {
 167   const actorIdNumber = parseInt(actorId + '', 10)
 168
 169   return '(' +
 170     'SELECT "actor"."serverId" FROM "actorFollow" ' +
 171     'INNER JOIN "actor" ON actor.id = "actorFollow"."targetActorId" ' +
 172     'WHERE "actorFollow"."actorId" = ' + actorIdNumber +
 173     ')'
 174 }
 175
 176 function buildWhereIdOrUUID (id: number | string) {
 177   return validator.isInt('' + id) ? { id } : { uuid: id }
 178 }
 179
 180 function parseAggregateResult (result: any) {
 181   if (!result) return 0
 182
 183   const total = parseInt(result + '', 10)
 184   if (isNaN(total)) return 0
 185
 186   return total
 187 }
 188
 189 const createSafeIn = (model: typeof Model, stringArr: (string | number)[]) => {
 190   return stringArr.map(t => {
 191     return t === null
 192       ? null
 193       : model.sequelize.escape('' + t)
 194   }).join(', ')
 195 }
 196
 197 function buildLocalAccountIdsIn () {
 198   return literal(
 199     '(SELECT "account"."id" FROM "account" INNER JOIN "actor" ON "actor"."id" = "account"."actorId" AND "actor"."serverId" IS NULL)'
 200   )
 201 }
 202
 203 function buildLocalActorIdsIn () {
 204   return literal(
 205     '(SELECT "actor"."id" FROM "actor" WHERE "actor"."serverId" IS NULL)'
 206   )
 207 }
 208
 209 function buildDirectionAndField (value: string) {
 210   let field: string
 211   let direction: 'ASC' | 'DESC'
 212
 213   if (value.substring(0, 1) === '-') {
 214     direction = 'DESC'
 215     field = value.substring(1)
 216   } else {
 217     direction = 'ASC'
 218     field = value
 219   }
 220
 221   return { direction, field }
 222 }
 223
 224 function searchAttribute (sourceField?: string, targetField?: string) {
 225   if (!sourceField) return {}
 226
 227   return {
 228     [targetField]: {
 229       [Op.iLike]: `%${sourceField}%`
 230     }
 231   }
 232 }
 233
 234 // ---------------------------------------------------------------------------
 235
 236 export {
 237   buildBlockedAccountSQL,
 238   buildBlockedAccountSQLOptimized,
 239   buildLocalActorIdsIn,
 240   getPlaylistSort,
 241   SortType,
 242   buildLocalAccountIdsIn,
 243   getSort,
 244   getCommentSort,
 245   getVideoSort,
 246   getBlacklistSort,
 247   createSimilarityAttribute,
 248   throwIfNotValid,
 249   buildServerIdsFollowedBy,
 250   buildTrigramSearchIndex,
 251   buildWhereIdOrUUID,
 252   isOutdated,
 253   parseAggregateResult,
 254   getFollowsSort,
 255   buildDirectionAndField,
 256   createSafeIn,
 257   searchAttribute
 258 }
 259
 260 // ---------------------------------------------------------------------------
 261
 262 function searchTrigramNormalizeValue (value: string) {
 263   return Sequelize.fn('lower', Sequelize.fn('immutable_unaccent', value))
 264 }
 265
 266 function searchTrigramNormalizeCol (col: string) {
 267   return Sequelize.fn('lower', Sequelize.fn('immutable_unaccent', Sequelize.col(col)))
 268 }