server/models/utils.ts

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