server/models/utils.ts

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