server/models/utils.ts

   1 import { literal, Op, OrderItem, Sequelize } from 'sequelize'
   2 import { Col } from 'sequelize/types/lib/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 = typeof finalField === 'string'
  78     ? finalField.split('.').concat([ direction ]) as any // FIXME: sequelize typings
  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 any[] // FIXME: typings
  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       [Op.iLike]: `%${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 }