server/models/utils.ts

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