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