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