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