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