server/models/account/user.ts

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