server/models/utils.ts

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