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