ニジカ投稿局 https://tv.nizika.tv
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
 
 
 
 
 

290 lines
7.5 KiB

  1. import { UserRegistration, UserRegistrationState, type UserRegistrationStateType } from '@peertube/peertube-models'
  2. import {
  3. isRegistrationModerationResponseValid,
  4. isRegistrationReasonValid,
  5. isRegistrationStateValid
  6. } from '@server/helpers/custom-validators/user-registration.js'
  7. import { isVideoChannelDisplayNameValid } from '@server/helpers/custom-validators/video-channels.js'
  8. import { cryptPassword } from '@server/helpers/peertube-crypto.js'
  9. import { USER_REGISTRATION_STATES } from '@server/initializers/constants.js'
  10. import { MRegistration, MRegistrationFormattable } from '@server/types/models/index.js'
  11. import { FindOptions, Op, QueryTypes, WhereOptions } from 'sequelize'
  12. import {
  13. AllowNull,
  14. BeforeCreate,
  15. BelongsTo,
  16. Column,
  17. CreatedAt,
  18. DataType,
  19. ForeignKey,
  20. Is,
  21. IsEmail, Table,
  22. UpdatedAt
  23. } from 'sequelize-typescript'
  24. import { isUserDisplayNameValid, isUserEmailVerifiedValid, isUserPasswordValid } from '../../helpers/custom-validators/users.js'
  25. import { SequelizeModel, getSort, parseAggregateResult, throwIfNotValid } from '../shared/index.js'
  26. import { UserModel } from './user.js'
  27. import { forceNumber } from '@peertube/peertube-core-utils'
  28. @Table({
  29. tableName: 'userRegistration',
  30. indexes: [
  31. {
  32. fields: [ 'username' ],
  33. unique: true
  34. },
  35. {
  36. fields: [ 'email' ],
  37. unique: true
  38. },
  39. {
  40. fields: [ 'channelHandle' ],
  41. unique: true
  42. },
  43. {
  44. fields: [ 'userId' ],
  45. unique: true
  46. }
  47. ]
  48. })
  49. export class UserRegistrationModel extends SequelizeModel<UserRegistrationModel> {
  50. @AllowNull(false)
  51. @Is('RegistrationState', value => throwIfNotValid(value, isRegistrationStateValid, 'state'))
  52. @Column
  53. state: UserRegistrationStateType
  54. @AllowNull(false)
  55. @Is('RegistrationReason', value => throwIfNotValid(value, isRegistrationReasonValid, 'registration reason'))
  56. @Column(DataType.TEXT)
  57. registrationReason: string
  58. @AllowNull(true)
  59. @Is('RegistrationModerationResponse', value => throwIfNotValid(value, isRegistrationModerationResponseValid, 'moderation response', true))
  60. @Column(DataType.TEXT)
  61. moderationResponse: string
  62. @AllowNull(true)
  63. @Is('RegistrationPassword', value => throwIfNotValid(value, isUserPasswordValid, 'registration password', true))
  64. @Column
  65. password: string
  66. @AllowNull(false)
  67. @Column
  68. username: string
  69. @AllowNull(false)
  70. @IsEmail
  71. @Column(DataType.STRING(400))
  72. email: string
  73. @AllowNull(true)
  74. @Is('RegistrationEmailVerified', value => throwIfNotValid(value, isUserEmailVerifiedValid, 'email verified boolean', true))
  75. @Column
  76. emailVerified: boolean
  77. @AllowNull(true)
  78. @Is('RegistrationAccountDisplayName', value => throwIfNotValid(value, isUserDisplayNameValid, 'account display name', true))
  79. @Column
  80. accountDisplayName: string
  81. @AllowNull(true)
  82. @Is('ChannelHandle', value => throwIfNotValid(value, isVideoChannelDisplayNameValid, 'channel handle', true))
  83. @Column
  84. channelHandle: string
  85. @AllowNull(true)
  86. @Is('ChannelDisplayName', value => throwIfNotValid(value, isVideoChannelDisplayNameValid, 'channel display name', true))
  87. @Column
  88. channelDisplayName: string
  89. @AllowNull(true)
  90. @Column
  91. processedAt: Date
  92. @CreatedAt
  93. createdAt: Date
  94. @UpdatedAt
  95. updatedAt: Date
  96. @ForeignKey(() => UserModel)
  97. @Column
  98. userId: number
  99. @BelongsTo(() => UserModel, {
  100. foreignKey: {
  101. allowNull: true
  102. },
  103. onDelete: 'SET NULL'
  104. })
  105. User: Awaited<UserModel>
  106. @BeforeCreate
  107. static async cryptPasswordIfNeeded (instance: UserRegistrationModel) {
  108. instance.password = await cryptPassword(instance.password)
  109. }
  110. static load (id: number): Promise<MRegistration> {
  111. return UserRegistrationModel.findByPk(id)
  112. }
  113. static loadByEmail (email: string): Promise<MRegistration> {
  114. const query = {
  115. where: { email }
  116. }
  117. return UserRegistrationModel.findOne(query)
  118. }
  119. static loadByEmailOrUsername (emailOrUsername: string): Promise<MRegistration> {
  120. const query = {
  121. where: {
  122. [Op.or]: [
  123. { email: emailOrUsername },
  124. { username: emailOrUsername }
  125. ]
  126. }
  127. }
  128. return UserRegistrationModel.findOne(query)
  129. }
  130. static loadByEmailOrHandle (options: {
  131. email: string
  132. username: string
  133. channelHandle?: string
  134. }): Promise<MRegistration> {
  135. const { email, username, channelHandle } = options
  136. let or: WhereOptions = [
  137. { email },
  138. { channelHandle: username },
  139. { username }
  140. ]
  141. if (channelHandle) {
  142. or = or.concat([
  143. { username: channelHandle },
  144. { channelHandle }
  145. ])
  146. }
  147. const query = {
  148. where: {
  149. [Op.or]: or
  150. }
  151. }
  152. return UserRegistrationModel.findOne(query)
  153. }
  154. // ---------------------------------------------------------------------------
  155. static listForApi (options: {
  156. start: number
  157. count: number
  158. sort: string
  159. search?: string
  160. }) {
  161. const { start, count, sort, search } = options
  162. const where: WhereOptions = {}
  163. if (search) {
  164. Object.assign(where, {
  165. [Op.or]: [
  166. {
  167. email: {
  168. [Op.iLike]: '%' + search + '%'
  169. }
  170. },
  171. {
  172. username: {
  173. [Op.iLike]: '%' + search + '%'
  174. }
  175. }
  176. ]
  177. })
  178. }
  179. const query: FindOptions = {
  180. offset: start,
  181. limit: count,
  182. order: getSort(sort),
  183. where,
  184. include: [
  185. {
  186. model: UserModel.unscoped(),
  187. required: false
  188. }
  189. ]
  190. }
  191. return Promise.all([
  192. UserRegistrationModel.count(query),
  193. UserRegistrationModel.findAll<MRegistrationFormattable>(query)
  194. ]).then(([ total, data ]) => ({ total, data }))
  195. }
  196. // ---------------------------------------------------------------------------
  197. static getStats () {
  198. const query = `SELECT ` +
  199. `AVG(EXTRACT(EPOCH FROM ("processedAt" - "createdAt") * 1000)) ` +
  200. `FILTER (WHERE "processedAt" IS NOT NULL AND "createdAt" > CURRENT_DATE - INTERVAL '3 months')` +
  201. `AS "avgResponseTime", ` +
  202. // "processedAt" has been introduced in PeerTube 6.1 so also check the abuse state to check processed abuses
  203. `COUNT(*) FILTER (WHERE "processedAt" IS NOT NULL OR "state" != ${UserRegistrationState.PENDING}) AS "processedRequests", ` +
  204. `COUNT(*) AS "totalRequests" ` +
  205. `FROM "userRegistration"`
  206. return UserRegistrationModel.sequelize.query<any>(query, {
  207. type: QueryTypes.SELECT,
  208. raw: true
  209. }).then(([ row ]) => {
  210. return {
  211. totalRegistrationRequests: parseAggregateResult(row.totalRequests),
  212. totalRegistrationRequestsProcessed: parseAggregateResult(row.processedRequests),
  213. averageRegistrationRequestResponseTimeMs: row?.avgResponseTime
  214. ? forceNumber(row.avgResponseTime)
  215. : null
  216. }
  217. })
  218. }
  219. // ---------------------------------------------------------------------------
  220. toFormattedJSON (this: MRegistrationFormattable): UserRegistration {
  221. return {
  222. id: this.id,
  223. state: {
  224. id: this.state,
  225. label: USER_REGISTRATION_STATES[this.state]
  226. },
  227. registrationReason: this.registrationReason,
  228. moderationResponse: this.moderationResponse,
  229. username: this.username,
  230. email: this.email,
  231. emailVerified: this.emailVerified,
  232. accountDisplayName: this.accountDisplayName,
  233. channelHandle: this.channelHandle,
  234. channelDisplayName: this.channelDisplayName,
  235. createdAt: this.createdAt,
  236. updatedAt: this.updatedAt,
  237. user: this.User
  238. ? { id: this.User.id }
  239. : null
  240. }
  241. }
  242. }