utils.ts 4.9 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170
  1. import { Sequelize } from 'sequelize-typescript'
  2. import * as validator from 'validator'
  3. import { OrderItem } from 'sequelize'
  4. import { Col } from 'sequelize/types/lib/utils'
  5. type SortType = { sortModel: any, sortValue: string }
  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. let finalField: string | Col
  10. if (field.toLowerCase() === 'match') { // Search
  11. finalField = Sequelize.col('similarity')
  12. } else {
  13. finalField = field
  14. }
  15. return [ [ finalField, direction ], lastSort ]
  16. }
  17. function getVideoSort (value: string, lastSort: OrderItem = [ 'id', 'ASC' ]): OrderItem[] {
  18. const { direction, field } = buildDirectionAndField(value)
  19. if (field.toLowerCase() === 'trending') { // Sort by aggregation
  20. return [
  21. [ Sequelize.fn('COALESCE', Sequelize.fn('SUM', Sequelize.col('VideoViews.views')), '0'), direction ],
  22. [ Sequelize.col('VideoModel.views'), direction ],
  23. lastSort
  24. ]
  25. }
  26. let finalField: string | Col
  27. // Alias
  28. if (field.toLowerCase() === 'match') { // Search
  29. finalField = Sequelize.col('similarity')
  30. } else {
  31. finalField = field
  32. }
  33. const firstSort = typeof finalField === 'string'
  34. ? finalField.split('.').concat([ direction ]) as any // FIXME: sequelize typings
  35. : [ finalField, direction ]
  36. return [ firstSort, lastSort ]
  37. }
  38. function getSortOnModel (model: any, value: string, lastSort: OrderItem = [ 'id', 'ASC' ]): OrderItem[] {
  39. const [ firstSort ] = getSort(value)
  40. if (model) return [ [ model, firstSort[0], firstSort[1] ], lastSort ]
  41. return [ firstSort, lastSort ]
  42. }
  43. function isOutdated (model: { createdAt: Date, updatedAt: Date }, refreshInterval: number) {
  44. const now = Date.now()
  45. const createdAtTime = model.createdAt.getTime()
  46. const updatedAtTime = model.updatedAt.getTime()
  47. return (now - createdAtTime) > refreshInterval && (now - updatedAtTime) > refreshInterval
  48. }
  49. function throwIfNotValid (value: any, validator: (value: any) => boolean, fieldName = 'value', nullable = false) {
  50. if (nullable && (value === null || value === undefined)) return
  51. if (validator(value) === false) {
  52. throw new Error(`"${value}" is not a valid ${fieldName}.`)
  53. }
  54. }
  55. function buildTrigramSearchIndex (indexName: string, attribute: string) {
  56. return {
  57. name: indexName,
  58. fields: [ Sequelize.literal('lower(immutable_unaccent(' + attribute + '))') as any ],
  59. using: 'gin',
  60. operator: 'gin_trgm_ops'
  61. }
  62. }
  63. function createSimilarityAttribute (col: string, value: string) {
  64. return Sequelize.fn(
  65. 'similarity',
  66. searchTrigramNormalizeCol(col),
  67. searchTrigramNormalizeValue(value)
  68. )
  69. }
  70. function buildBlockedAccountSQL (serverAccountId: number, userAccountId?: number) {
  71. const blockerIds = [ serverAccountId ]
  72. if (userAccountId) blockerIds.push(userAccountId)
  73. const blockerIdsString = blockerIds.join(', ')
  74. return 'SELECT "targetAccountId" AS "id" FROM "accountBlocklist" WHERE "accountId" IN (' + blockerIdsString + ')' +
  75. ' UNION ALL ' +
  76. 'SELECT "account"."id" AS "id" FROM account INNER JOIN "actor" ON account."actorId" = actor.id ' +
  77. 'INNER JOIN "serverBlocklist" ON "actor"."serverId" = "serverBlocklist"."targetServerId" ' +
  78. 'WHERE "serverBlocklist"."accountId" IN (' + blockerIdsString + ')'
  79. }
  80. function buildServerIdsFollowedBy (actorId: any) {
  81. const actorIdNumber = parseInt(actorId + '', 10)
  82. return '(' +
  83. 'SELECT "actor"."serverId" FROM "actorFollow" ' +
  84. 'INNER JOIN "actor" ON actor.id = "actorFollow"."targetActorId" ' +
  85. 'WHERE "actorFollow"."actorId" = ' + actorIdNumber +
  86. ')'
  87. }
  88. function buildWhereIdOrUUID (id: number | string) {
  89. return validator.isInt('' + id) ? { id } : { uuid: id }
  90. }
  91. function parseAggregateResult (result: any) {
  92. if (!result) return 0
  93. const total = parseInt(result + '', 10)
  94. if (isNaN(total)) return 0
  95. return total
  96. }
  97. // ---------------------------------------------------------------------------
  98. export {
  99. buildBlockedAccountSQL,
  100. SortType,
  101. getSort,
  102. getVideoSort,
  103. getSortOnModel,
  104. createSimilarityAttribute,
  105. throwIfNotValid,
  106. buildServerIdsFollowedBy,
  107. buildTrigramSearchIndex,
  108. buildWhereIdOrUUID,
  109. isOutdated,
  110. parseAggregateResult
  111. }
  112. // ---------------------------------------------------------------------------
  113. function searchTrigramNormalizeValue (value: string) {
  114. return Sequelize.fn('lower', Sequelize.fn('immutable_unaccent', value))
  115. }
  116. function searchTrigramNormalizeCol (col: string) {
  117. return Sequelize.fn('lower', Sequelize.fn('immutable_unaccent', Sequelize.col(col)))
  118. }
  119. function buildDirectionAndField (value: string) {
  120. let field: string
  121. let direction: 'ASC' | 'DESC'
  122. if (value.substring(0, 1) === '-') {
  123. direction = 'DESC'
  124. field = value.substring(1)
  125. } else {
  126. direction = 'ASC'
  127. field = value
  128. }
  129. return { direction, field }
  130. }