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