server/models/utils.ts

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