server/models/account/user.ts

   1 import * as Sequelize from 'sequelize'
   2 import {
   3   AfterDelete,
   4   AfterUpdate,
   5   AllowNull,
   6   BeforeCreate,
   7   BeforeUpdate,
   8   Column,
   9   CreatedAt,
  10   DataType,
  11   Default,
  12   DefaultScope,
  13   HasMany,
  14   HasOne,
  15   Is,
  16   IsEmail,
  17   Model,
  18   Scopes,
  19   Table,
  20   UpdatedAt
  21 } from 'sequelize-typescript'
  22 import { hasUserRight, USER_ROLE_LABELS, UserRight } from '../../../shared'
  23 import { User, UserRole } from '../../../shared/models/users'
  24 import {
  25   isUserAutoPlayVideoValid,
  26   isUserBlockedReasonValid,
  27   isUserBlockedValid,
  28   isUserEmailVerifiedValid,
  29   isUserNSFWPolicyValid,
  30   isUserPasswordValid,
  31   isUserRoleValid,
  32   isUserUsernameValid,
  33   isUserVideoQuotaDailyValid,
  34   isUserVideoQuotaValid,
  35   isUserWebTorrentEnabledValid
  36 } from '../../helpers/custom-validators/users'
  37 import { comparePassword, cryptPassword } from '../../helpers/peertube-crypto'
  38 import { OAuthTokenModel } from '../oauth/oauth-token'
  39 import { getSort, throwIfNotValid } from '../utils'
  40 import { VideoChannelModel } from '../video/video-channel'
  41 import { AccountModel } from './account'
  42 import { NSFWPolicyType } from '../../../shared/models/videos/nsfw-policy.type'
  43 import { values } from 'lodash'
  44 import { NSFW_POLICY_TYPES } from '../../initializers'
  45 import { clearCacheByUserId } from '../../lib/oauth-model'
  46
  47 enum ScopeNames {
  48   WITH_VIDEO_CHANNEL = 'WITH_VIDEO_CHANNEL'
  49 }
  50
  51 @DefaultScope({
  52   include: [
  53     {
  54       model: () => AccountModel,
  55       required: true
  56     }
  57   ]
  58 })
  59 @Scopes({
  60   [ScopeNames.WITH_VIDEO_CHANNEL]: {
  61     include: [
  62       {
  63         model: () => AccountModel,
  64         required: true,
  65         include: [ () => VideoChannelModel ]
  66       }
  67     ]
  68   }
  69 })
  70 @Table({
  71   tableName: 'user',
  72   indexes: [
  73     {
  74       fields: [ 'username' ],
  75       unique: true
  76     },
  77     {
  78       fields: [ 'email' ],
  79       unique: true
  80     }
  81   ]
  82 })
  83 export class UserModel extends Model<UserModel> {
  84
  85   @AllowNull(false)
  86   @Is('UserPassword', value => throwIfNotValid(value, isUserPasswordValid, 'user password'))
  87   @Column
  88   password: string
  89
  90   @AllowNull(false)
  91   @Is('UserPassword', value => throwIfNotValid(value, isUserUsernameValid, 'user name'))
  92   @Column
  93   username: string
  94
  95   @AllowNull(false)
  96   @IsEmail
  97   @Column(DataType.STRING(400))
  98   email: string
  99
 100   @AllowNull(true)
 101   @Default(null)
 102   @Is('UserEmailVerified', value => throwIfNotValid(value, isUserEmailVerifiedValid, 'email verified boolean'))
 103   @Column
 104   emailVerified: boolean
 105
 106   @AllowNull(false)
 107   @Is('UserNSFWPolicy', value => throwIfNotValid(value, isUserNSFWPolicyValid, 'NSFW policy'))
 108   @Column(DataType.ENUM(values(NSFW_POLICY_TYPES)))
 109   nsfwPolicy: NSFWPolicyType
 110
 111   @AllowNull(false)
 112   @Default(true)
 113   @Is('UserWebTorrentEnabled', value => throwIfNotValid(value, isUserWebTorrentEnabledValid, 'WebTorrent enabled'))
 114   @Column
 115   webTorrentEnabled: boolean
 116
 117   @AllowNull(false)
 118   @Default(true)
 119   @Is('UserAutoPlayVideo', value => throwIfNotValid(value, isUserAutoPlayVideoValid, 'auto play video boolean'))
 120   @Column
 121   autoPlayVideo: boolean
 122
 123   @AllowNull(false)
 124   @Default(false)
 125   @Is('UserBlocked', value => throwIfNotValid(value, isUserBlockedValid, 'blocked boolean'))
 126   @Column
 127   blocked: boolean
 128
 129   @AllowNull(true)
 130   @Default(null)
 131   @Is('UserBlockedReason', value => throwIfNotValid(value, isUserBlockedReasonValid, 'blocked reason'))
 132   @Column
 133   blockedReason: string
 134
 135   @AllowNull(false)
 136   @Is('UserRole', value => throwIfNotValid(value, isUserRoleValid, 'role'))
 137   @Column
 138   role: number
 139
 140   @AllowNull(false)
 141   @Is('UserVideoQuota', value => throwIfNotValid(value, isUserVideoQuotaValid, 'video quota'))
 142   @Column(DataType.BIGINT)
 143   videoQuota: number
 144
 145   @AllowNull(false)
 146   @Is('UserVideoQuotaDaily', value => throwIfNotValid(value, isUserVideoQuotaDailyValid, 'video quota daily'))
 147   @Column(DataType.BIGINT)
 148   videoQuotaDaily: number
 149
 150   @CreatedAt
 151   createdAt: Date
 152
 153   @UpdatedAt
 154   updatedAt: Date
 155
 156   @HasOne(() => AccountModel, {
 157     foreignKey: 'userId',
 158     onDelete: 'cascade',
 159     hooks: true
 160   })
 161   Account: AccountModel
 162
 163   @HasMany(() => OAuthTokenModel, {
 164     foreignKey: 'userId',
 165     onDelete: 'cascade'
 166   })
 167   OAuthTokens: OAuthTokenModel[]
 168
 169   @BeforeCreate
 170   @BeforeUpdate
 171   static cryptPasswordIfNeeded (instance: UserModel) {
 172     if (instance.changed('password')) {
 173       return cryptPassword(instance.password)
 174         .then(hash => {
 175           instance.password = hash
 176           return undefined
 177         })
 178     }
 179   }
 180
 181   @AfterUpdate
 182   @AfterDelete
 183   static removeTokenCache (instance: UserModel) {
 184     return clearCacheByUserId(instance.id)
 185   }
 186
 187   static countTotal () {
 188     return this.count()
 189   }
 190
 191   static listForApi (start: number, count: number, sort: string, search?: string) {
 192     let where = undefined
 193     if (search) {
 194       where = {
 195         [Sequelize.Op.or]: [
 196           {
 197             email: {
 198               [Sequelize.Op.iLike]: '%' + search + '%'
 199             }
 200           },
 201           {
 202             username: {
 203               [ Sequelize.Op.iLike ]: '%' + search + '%'
 204             }
 205           }
 206         ]
 207       }
 208     }
 209
 210     const query = {
 211       attributes: {
 212         include: [
 213           [
 214             Sequelize.literal(
 215               '(' +
 216                 'SELECT COALESCE(SUM("size"), 0) ' +
 217                 'FROM (' +
 218                   'SELECT MAX("videoFile"."size") AS "size" FROM "videoFile" ' +
 219                   'INNER JOIN "video" ON "videoFile"."videoId" = "video"."id" ' +
 220                   'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" ' +
 221                   'INNER JOIN "account" ON "videoChannel"."accountId" = "account"."id" ' +
 222                   'WHERE "account"."userId" = "UserModel"."id" GROUP BY "video"."id"' +
 223                 ') t' +
 224               ')'
 225             ),
 226             'videoQuotaUsed'
 227           ] as any // FIXME: typings
 228         ]
 229       },
 230       offset: start,
 231       limit: count,
 232       order: getSort(sort),
 233       where
 234     }
 235
 236     return UserModel.findAndCountAll(query)
 237       .then(({ rows, count }) => {
 238         return {
 239           data: rows,
 240           total: count
 241         }
 242       })
 243   }
 244
 245   static listEmailsWithRight (right: UserRight) {
 246     const roles = Object.keys(USER_ROLE_LABELS)
 247       .map(k => parseInt(k, 10) as UserRole)
 248       .filter(role => hasUserRight(role, right))
 249
 250     const query = {
 251       attribute: [ 'email' ],
 252       where: {
 253         role: {
 254           [Sequelize.Op.in]: roles
 255         }
 256       }
 257     }
 258
 259     return UserModel.unscoped()
 260       .findAll(query)
 261       .then(u => u.map(u => u.email))
 262   }
 263
 264   static loadById (id: number) {
 265     return UserModel.findById(id)
 266   }
 267
 268   static loadByUsername (username: string) {
 269     const query = {
 270       where: {
 271         username
 272       }
 273     }
 274
 275     return UserModel.findOne(query)
 276   }
 277
 278   static loadByUsernameAndPopulateChannels (username: string) {
 279     const query = {
 280       where: {
 281         username
 282       }
 283     }
 284
 285     return UserModel.scope(ScopeNames.WITH_VIDEO_CHANNEL).findOne(query)
 286   }
 287
 288   static loadByEmail (email: string) {
 289     const query = {
 290       where: {
 291         email
 292       }
 293     }
 294
 295     return UserModel.findOne(query)
 296   }
 297
 298   static loadByUsernameOrEmail (username: string, email?: string) {
 299     if (!email) email = username
 300
 301     const query = {
 302       where: {
 303         [ Sequelize.Op.or ]: [ { username }, { email } ]
 304       }
 305     }
 306
 307     return UserModel.findOne(query)
 308   }
 309
 310   static getOriginalVideoFileTotalFromUser (user: UserModel) {
 311     // Don't use sequelize because we need to use a sub query
 312     const query = UserModel.generateUserQuotaBaseSQL()
 313
 314     return UserModel.getTotalRawQuery(query, user.id)
 315   }
 316
 317   // Returns cumulative size of all video files uploaded in the last 24 hours.
 318   static getOriginalVideoFileTotalDailyFromUser (user: UserModel) {
 319     // Don't use sequelize because we need to use a sub query
 320     const query = UserModel.generateUserQuotaBaseSQL('"video"."createdAt" > now() - interval \'24 hours\'')
 321
 322     return UserModel.getTotalRawQuery(query, user.id)
 323   }
 324
 325   static async getStats () {
 326     const totalUsers = await UserModel.count()
 327
 328     return {
 329       totalUsers
 330     }
 331   }
 332
 333   static autoComplete (search: string) {
 334     const query = {
 335       where: {
 336         username: {
 337           [ Sequelize.Op.like ]: `%${search}%`
 338         }
 339       },
 340       limit: 10
 341     }
 342
 343     return UserModel.findAll(query)
 344                     .then(u => u.map(u => u.username))
 345   }
 346
 347   hasRight (right: UserRight) {
 348     return hasUserRight(this.role, right)
 349   }
 350
 351   isPasswordMatch (password: string) {
 352     return comparePassword(password, this.password)
 353   }
 354
 355   toFormattedJSON (): User {
 356     const videoQuotaUsed = this.get('videoQuotaUsed')
 357     const videoQuotaUsedDaily = this.get('videoQuotaUsedDaily')
 358
 359     const json = {
 360       id: this.id,
 361       username: this.username,
 362       email: this.email,
 363       emailVerified: this.emailVerified,
 364       nsfwPolicy: this.nsfwPolicy,
 365       webTorrentEnabled: this.webTorrentEnabled,
 366       autoPlayVideo: this.autoPlayVideo,
 367       role: this.role,
 368       roleLabel: USER_ROLE_LABELS[ this.role ],
 369       videoQuota: this.videoQuota,
 370       videoQuotaDaily: this.videoQuotaDaily,
 371       createdAt: this.createdAt,
 372       blocked: this.blocked,
 373       blockedReason: this.blockedReason,
 374       account: this.Account.toFormattedJSON(),
 375       videoChannels: [],
 376       videoQuotaUsed: videoQuotaUsed !== undefined
 377             ? parseInt(videoQuotaUsed, 10)
 378             : undefined,
 379       videoQuotaUsedDaily: videoQuotaUsedDaily !== undefined
 380             ? parseInt(videoQuotaUsedDaily, 10)
 381             : undefined
 382     }
 383
 384     if (Array.isArray(this.Account.VideoChannels) === true) {
 385       json.videoChannels = this.Account.VideoChannels
 386         .map(c => c.toFormattedJSON())
 387         .sort((v1, v2) => {
 388           if (v1.createdAt < v2.createdAt) return -1
 389           if (v1.createdAt === v2.createdAt) return 0
 390
 391           return 1
 392         })
 393     }
 394
 395     return json
 396   }
 397
 398   async isAbleToUploadVideo (videoFile: { size: number }) {
 399     if (this.videoQuota === -1 && this.videoQuotaDaily === -1) return Promise.resolve(true)
 400
 401     const [ totalBytes, totalBytesDaily ] = await Promise.all([
 402       UserModel.getOriginalVideoFileTotalFromUser(this),
 403       UserModel.getOriginalVideoFileTotalDailyFromUser(this)
 404     ])
 405
 406     const uploadedTotal = videoFile.size + totalBytes
 407     const uploadedDaily = videoFile.size + totalBytesDaily
 408     if (this.videoQuotaDaily === -1) {
 409       return uploadedTotal < this.videoQuota
 410     }
 411     if (this.videoQuota === -1) {
 412       return uploadedDaily < this.videoQuotaDaily
 413     }
 414
 415     return (uploadedTotal < this.videoQuota) &&
 416         (uploadedDaily < this.videoQuotaDaily)
 417   }
 418
 419   private static generateUserQuotaBaseSQL (where?: string) {
 420     const andWhere = where ? 'AND ' + where : ''
 421
 422     return 'SELECT SUM("size") AS "total" ' +
 423       'FROM (' +
 424         'SELECT MAX("videoFile"."size") AS "size" FROM "videoFile" ' +
 425         'INNER JOIN "video" ON "videoFile"."videoId" = "video"."id" ' +
 426         'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" ' +
 427         'INNER JOIN "account" ON "videoChannel"."accountId" = "account"."id" ' +
 428         'WHERE "account"."userId" = $userId ' + andWhere +
 429         'GROUP BY "video"."id"' +
 430       ') t'
 431   }
 432
 433   private static getTotalRawQuery (query: string, userId: number) {
 434     const options = {
 435       bind: { userId },
 436       type: Sequelize.QueryTypes.SELECT
 437     }
 438
 439     return UserModel.sequelize.query(query, options)
 440                     .then(([ { total } ]) => {
 441                       if (total === null) return 0
 442
 443                       return parseInt(total, 10)
 444                     })
 445   }
 446 }