server/models/utils.ts

   1 import { Model, Sequelize } from 'sequelize-typescript'
   2 import * as validator from 'validator'
   3 import { Col } from 'sequelize/types/lib/utils'
   4 import { OrderItem, literal } from 'sequelize'
   5
   6 type SortType = { sortModel: any, 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 getVideoSort (value: string, lastSort: OrderItem = [ 'id', 'ASC' ]): OrderItem[] {
  26   const { direction, field } = buildDirectionAndField(value)
  27
  28   if (field.toLowerCase() === 'trending') { // Sort by aggregation
  29     return [
  30       [ Sequelize.fn('COALESCE', Sequelize.fn('SUM', Sequelize.col('VideoViews.views')), '0'), direction ],
  31
  32       [ Sequelize.col('VideoModel.views'), direction ],
  33
  34       lastSort
  35     ]
  36   }
  37
  38   let finalField: string | Col
  39
  40   // Alias
  41   if (field.toLowerCase() === 'match') { // Search
  42     finalField = Sequelize.col('similarity')
  43   } else {
  44     finalField = field
  45   }
  46
  47   const firstSort = typeof finalField === 'string'
  48     ? finalField.split('.').concat([ direction ]) as any // FIXME: sequelize typings
  49     : [ finalField, direction ]
  50
  51   return [ firstSort, lastSort ]
  52 }
  53
  54 function getSortOnModel (model: any, value: string, lastSort: OrderItem = [ 'id', 'ASC' ]): OrderItem[] {
  55   const [ firstSort ] = getSort(value)
  56
  57   if (model) return [ [ model, firstSort[0], firstSort[1] ], lastSort ]
  58   return [ firstSort, lastSort ]
  59 }
  60
  61 function isOutdated (model: { createdAt: Date, updatedAt: Date }, refreshInterval: number) {
  62   const now = Date.now()
  63   const createdAtTime = model.createdAt.getTime()
  64   const updatedAtTime = model.updatedAt.getTime()
  65
  66   return (now - createdAtTime) > refreshInterval && (now - updatedAtTime) > refreshInterval
  67 }
  68
  69 function throwIfNotValid (value: any, validator: (value: any) => boolean, fieldName = 'value', nullable = false) {
  70   if (nullable && (value === null || value === undefined)) return
  71
  72   if (validator(value) === false) {
  73     throw new Error(`"${value}" is not a valid ${fieldName}.`)
  74   }
  75 }
  76
  77 function buildTrigramSearchIndex (indexName: string, attribute: string) {
  78   return {
  79     name: indexName,
  80     fields: [ Sequelize.literal('lower(immutable_unaccent(' + attribute + '))') as any ],
  81     using: 'gin',
  82     operator: 'gin_trgm_ops'
  83   }
  84 }
  85
  86 function createSimilarityAttribute (col: string, value: string) {
  87   return Sequelize.fn(
  88     'similarity',
  89
  90     searchTrigramNormalizeCol(col),
  91
  92     searchTrigramNormalizeValue(value)
  93   )
  94 }
  95
  96 function buildBlockedAccountSQL (serverAccountId: number, userAccountId?: number) {
  97   const blockerIds = [ serverAccountId ]
  98   if (userAccountId) blockerIds.push(userAccountId)
  99
 100   const blockerIdsString = blockerIds.join(', ')
 101
 102   return 'SELECT "targetAccountId" AS "id" FROM "accountBlocklist" WHERE "accountId" IN (' + blockerIdsString + ')' +
 103     ' UNION ALL ' +
 104     'SELECT "account"."id" AS "id" FROM account INNER JOIN "actor" ON account."actorId" = actor.id ' +
 105     'INNER JOIN "serverBlocklist" ON "actor"."serverId" = "serverBlocklist"."targetServerId" ' +
 106     'WHERE "serverBlocklist"."accountId" IN (' + blockerIdsString + ')'
 107 }
 108
 109 function buildServerIdsFollowedBy (actorId: any) {
 110   const actorIdNumber = parseInt(actorId + '', 10)
 111
 112   return '(' +
 113     'SELECT "actor"."serverId" FROM "actorFollow" ' +
 114     'INNER JOIN "actor" ON actor.id = "actorFollow"."targetActorId" ' +
 115     'WHERE "actorFollow"."actorId" = ' + actorIdNumber +
 116   ')'
 117 }
 118
 119 function buildWhereIdOrUUID (id: number | string) {
 120   return validator.isInt('' + id) ? { id } : { uuid: id }
 121 }
 122
 123 function parseAggregateResult (result: any) {
 124   if (!result) return 0
 125
 126   const total = parseInt(result + '', 10)
 127   if (isNaN(total)) return 0
 128
 129   return total
 130 }
 131
 132 const createSafeIn = (model: typeof Model, stringArr: (string | number)[]) => {
 133   return stringArr.map(t => model.sequelize.escape('' + t))
 134                   .join(', ')
 135 }
 136
 137 function buildLocalAccountIdsIn () {
 138   return literal(
 139     '(SELECT "account"."id" FROM "account" INNER JOIN "actor" ON "actor"."id" = "account"."actorId" AND "actor"."serverId" IS NULL)'
 140   )
 141 }
 142
 143 function buildLocalActorIdsIn () {
 144   return literal(
 145     '(SELECT "actor"."id" FROM "actor" WHERE "actor"."serverId" IS NULL)'
 146   )
 147 }
 148
 149 // ---------------------------------------------------------------------------
 150
 151 export {
 152   buildBlockedAccountSQL,
 153   buildLocalActorIdsIn,
 154   SortType,
 155   buildLocalAccountIdsIn,
 156   getSort,
 157   getVideoSort,
 158   getSortOnModel,
 159   createSimilarityAttribute,
 160   throwIfNotValid,
 161   buildServerIdsFollowedBy,
 162   buildTrigramSearchIndex,
 163   buildWhereIdOrUUID,
 164   isOutdated,
 165   parseAggregateResult,
 166   createSafeIn
 167 }
 168
 169 // ---------------------------------------------------------------------------
 170
 171 function searchTrigramNormalizeValue (value: string) {
 172   return Sequelize.fn('lower', Sequelize.fn('immutable_unaccent', value))
 173 }
 174
 175 function searchTrigramNormalizeCol (col: string) {
 176   return Sequelize.fn('lower', Sequelize.fn('immutable_unaccent', Sequelize.col(col)))
 177 }
 178
 179 function buildDirectionAndField (value: string) {
 180   let field: string
 181   let direction: 'ASC' | 'DESC'
 182
 183   if (value.substring(0, 1) === '-') {
 184     direction = 'DESC'
 185     field = value.substring(1)
 186   } else {
 187     direction = 'ASC'
 188     field = value
 189   }
 190
 191   return { direction, field }
 192 }