server/models/account/user.ts

   1 import { FindOptions, literal, Op, QueryTypes } 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   isUserAdminFlagsValid,
  26   isUserAutoPlayVideoValid,
  27   isUserBlockedReasonValid,
  28   isUserBlockedValid,
  29   isUserEmailVerifiedValid,
  30   isUserNSFWPolicyValid,
  31   isUserPasswordValid,
  32   isUserRoleValid,
  33   isUserUsernameValid,
  34   isUserVideoQuotaDailyValid,
  35   isUserVideoQuotaValid,
  36   isUserVideosHistoryEnabledValid,
  37   isUserWebTorrentEnabledValid
  38 } from '../../helpers/custom-validators/users'
  39 import { comparePassword, cryptPassword } from '../../helpers/peertube-crypto'
  40 import { OAuthTokenModel } from '../oauth/oauth-token'
  41 import { getSort, throwIfNotValid } from '../utils'
  42 import { VideoChannelModel } from '../video/video-channel'
  43 import { AccountModel } from './account'
  44 import { NSFWPolicyType } from '../../../shared/models/videos/nsfw-policy.type'
  45 import { values } from 'lodash'
  46 import { NSFW_POLICY_TYPES } from '../../initializers/constants'
  47 import { clearCacheByUserId } from '../../lib/oauth-model'
  48 import { UserNotificationSettingModel } from './user-notification-setting'
  49 import { VideoModel } from '../video/video'
  50 import { ActorModel } from '../activitypub/actor'
  51 import { ActorFollowModel } from '../activitypub/actor-follow'
  52 import { VideoImportModel } from '../video/video-import'
  53 import { UserAdminFlag } from '../../../shared/models/users/user-flag.model'
  54
  55 enum ScopeNames {
  56   WITH_VIDEO_CHANNEL = 'WITH_VIDEO_CHANNEL'
  57 }
  58
  59 @DefaultScope(() => ({
  60   include: [
  61     {
  62       model: AccountModel,
  63       required: true
  64     },
  65     {
  66       model: UserNotificationSettingModel,
  67       required: true
  68     }
  69   ]
  70 }))
  71 @Scopes(() => ({
  72   [ScopeNames.WITH_VIDEO_CHANNEL]: {
  73     include: [
  74       {
  75         model: AccountModel,
  76         required: true,
  77         include: [ VideoChannelModel ]
  78       },
  79       {
  80         model: UserNotificationSettingModel,
  81         required: true
  82       }
  83     ]
  84   }
  85 }))
  86 @Table({
  87   tableName: 'user',
  88   indexes: [
  89     {
  90       fields: [ 'username' ],
  91       unique: true
  92     },
  93     {
  94       fields: [ 'email' ],
  95       unique: true
  96     }
  97   ]
  98 })
  99 export class UserModel extends Model<UserModel> {
 100
 101   @AllowNull(false)
 102   @Is('UserPassword', value => throwIfNotValid(value, isUserPasswordValid, 'user password'))
 103   @Column
 104   password: string
 105
 106   @AllowNull(false)
 107   @Is('UserPassword', value => throwIfNotValid(value, isUserUsernameValid, 'user name'))
 108   @Column
 109   username: string
 110
 111   @AllowNull(false)
 112   @IsEmail
 113   @Column(DataType.STRING(400))
 114   email: string
 115
 116   @AllowNull(true)
 117   @IsEmail
 118   @Column(DataType.STRING(400))
 119   pendingEmail: string
 120
 121   @AllowNull(true)
 122   @Default(null)
 123   @Is('UserEmailVerified', value => throwIfNotValid(value, isUserEmailVerifiedValid, 'email verified boolean', true))
 124   @Column
 125   emailVerified: boolean
 126
 127   @AllowNull(false)
 128   @Is('UserNSFWPolicy', value => throwIfNotValid(value, isUserNSFWPolicyValid, 'NSFW policy'))
 129   @Column(DataType.ENUM(...values(NSFW_POLICY_TYPES)))
 130   nsfwPolicy: NSFWPolicyType
 131
 132   @AllowNull(false)
 133   @Default(true)
 134   @Is('UserWebTorrentEnabled', value => throwIfNotValid(value, isUserWebTorrentEnabledValid, 'WebTorrent enabled'))
 135   @Column
 136   webTorrentEnabled: boolean
 137
 138   @AllowNull(false)
 139   @Default(true)
 140   @Is('UserVideosHistoryEnabled', value => throwIfNotValid(value, isUserVideosHistoryEnabledValid, 'Videos history enabled'))
 141   @Column
 142   videosHistoryEnabled: boolean
 143
 144   @AllowNull(false)
 145   @Default(true)
 146   @Is('UserAutoPlayVideo', value => throwIfNotValid(value, isUserAutoPlayVideoValid, 'auto play video boolean'))
 147   @Column
 148   autoPlayVideo: boolean
 149
 150   @AllowNull(false)
 151   @Default(UserAdminFlag.NONE)
 152   @Is('UserAdminFlags', value => throwIfNotValid(value, isUserAdminFlagsValid, 'user admin flags'))
 153   @Column
 154   adminFlags?: UserAdminFlag
 155
 156   @AllowNull(false)
 157   @Default(false)
 158   @Is('UserBlocked', value => throwIfNotValid(value, isUserBlockedValid, 'blocked boolean'))
 159   @Column
 160   blocked: boolean
 161
 162   @AllowNull(true)
 163   @Default(null)
 164   @Is('UserBlockedReason', value => throwIfNotValid(value, isUserBlockedReasonValid, 'blocked reason', true))
 165   @Column
 166   blockedReason: string
 167
 168   @AllowNull(false)
 169   @Is('UserRole', value => throwIfNotValid(value, isUserRoleValid, 'role'))
 170   @Column
 171   role: number
 172
 173   @AllowNull(false)
 174   @Is('UserVideoQuota', value => throwIfNotValid(value, isUserVideoQuotaValid, 'video quota'))
 175   @Column(DataType.BIGINT)
 176   videoQuota: number
 177
 178   @AllowNull(false)
 179   @Is('UserVideoQuotaDaily', value => throwIfNotValid(value, isUserVideoQuotaDailyValid, 'video quota daily'))
 180   @Column(DataType.BIGINT)
 181   videoQuotaDaily: number
 182
 183   @CreatedAt
 184   createdAt: Date
 185
 186   @UpdatedAt
 187   updatedAt: Date
 188
 189   @HasOne(() => AccountModel, {
 190     foreignKey: 'userId',
 191     onDelete: 'cascade',
 192     hooks: true
 193   })
 194   Account: AccountModel
 195
 196   @HasOne(() => UserNotificationSettingModel, {
 197     foreignKey: 'userId',
 198     onDelete: 'cascade',
 199     hooks: true
 200   })
 201   NotificationSetting: UserNotificationSettingModel
 202
 203   @HasMany(() => VideoImportModel, {
 204     foreignKey: 'userId',
 205     onDelete: 'cascade'
 206   })
 207   VideoImports: VideoImportModel[]
 208
 209   @HasMany(() => OAuthTokenModel, {
 210     foreignKey: 'userId',
 211     onDelete: 'cascade'
 212   })
 213   OAuthTokens: OAuthTokenModel[]
 214
 215   @BeforeCreate
 216   @BeforeUpdate
 217   static cryptPasswordIfNeeded (instance: UserModel) {
 218     if (instance.changed('password')) {
 219       return cryptPassword(instance.password)
 220         .then(hash => {
 221           instance.password = hash
 222           return undefined
 223         })
 224     }
 225   }
 226
 227   @AfterUpdate
 228   @AfterDestroy
 229   static removeTokenCache (instance: UserModel) {
 230     return clearCacheByUserId(instance.id)
 231   }
 232
 233   static countTotal () {
 234     return this.count()
 235   }
 236
 237   static listForApi (start: number, count: number, sort: string, search?: string) {
 238     let where = undefined
 239     if (search) {
 240       where = {
 241         [Op.or]: [
 242           {
 243             email: {
 244               [Op.iLike]: '%' + search + '%'
 245             }
 246           },
 247           {
 248             username: {
 249               [ Op.iLike ]: '%' + search + '%'
 250             }
 251           }
 252         ]
 253       }
 254     }
 255
 256     const query: FindOptions = {
 257       attributes: {
 258         include: [
 259           [
 260             literal(
 261               '(' +
 262                 'SELECT COALESCE(SUM("size"), 0) ' +
 263                 'FROM (' +
 264                   'SELECT MAX("videoFile"."size") AS "size" FROM "videoFile" ' +
 265                   'INNER JOIN "video" ON "videoFile"."videoId" = "video"."id" ' +
 266                   'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" ' +
 267                   'INNER JOIN "account" ON "videoChannel"."accountId" = "account"."id" ' +
 268                   'WHERE "account"."userId" = "UserModel"."id" GROUP BY "video"."id"' +
 269                 ') t' +
 270               ')'
 271             ),
 272             'videoQuotaUsed'
 273           ]
 274         ]
 275       },
 276       offset: start,
 277       limit: count,
 278       order: getSort(sort),
 279       where
 280     }
 281
 282     return UserModel.findAndCountAll(query)
 283       .then(({ rows, count }) => {
 284         return {
 285           data: rows,
 286           total: count
 287         }
 288       })
 289   }
 290
 291   static listWithRight (right: UserRight) {
 292     const roles = Object.keys(USER_ROLE_LABELS)
 293       .map(k => parseInt(k, 10) as UserRole)
 294       .filter(role => hasUserRight(role, right))
 295
 296     const query = {
 297       where: {
 298         role: {
 299           [Op.in]: roles
 300         }
 301       }
 302     }
 303
 304     return UserModel.findAll(query)
 305   }
 306
 307   static listUserSubscribersOf (actorId: number) {
 308     const query = {
 309       include: [
 310         {
 311           model: UserNotificationSettingModel.unscoped(),
 312           required: true
 313         },
 314         {
 315           attributes: [ 'userId' ],
 316           model: AccountModel.unscoped(),
 317           required: true,
 318           include: [
 319             {
 320               attributes: [ ],
 321               model: ActorModel.unscoped(),
 322               required: true,
 323               where: {
 324                 serverId: null
 325               },
 326               include: [
 327                 {
 328                   attributes: [ ],
 329                   as: 'ActorFollowings',
 330                   model: ActorFollowModel.unscoped(),
 331                   required: true,
 332                   where: {
 333                     targetActorId: actorId
 334                   }
 335                 }
 336               ]
 337             }
 338           ]
 339         }
 340       ]
 341     }
 342
 343     return UserModel.unscoped().findAll(query)
 344   }
 345
 346   static listByUsernames (usernames: string[]) {
 347     const query = {
 348       where: {
 349         username: usernames
 350       }
 351     }
 352
 353     return UserModel.findAll(query)
 354   }
 355
 356   static loadById (id: number) {
 357     return UserModel.findByPk(id)
 358   }
 359
 360   static loadByUsername (username: string) {
 361     const query = {
 362       where: {
 363         username
 364       }
 365     }
 366
 367     return UserModel.findOne(query)
 368   }
 369
 370   static loadByUsernameAndPopulateChannels (username: string) {
 371     const query = {
 372       where: {
 373         username
 374       }
 375     }
 376
 377     return UserModel.scope(ScopeNames.WITH_VIDEO_CHANNEL).findOne(query)
 378   }
 379
 380   static loadByEmail (email: string) {
 381     const query = {
 382       where: {
 383         email
 384       }
 385     }
 386
 387     return UserModel.findOne(query)
 388   }
 389
 390   static loadByUsernameOrEmail (username: string, email?: string) {
 391     if (!email) email = username
 392
 393     const query = {
 394       where: {
 395         [ Op.or ]: [ { username }, { email } ]
 396       }
 397     }
 398
 399     return UserModel.findOne(query)
 400   }
 401
 402   static loadByVideoId (videoId: number) {
 403     const query = {
 404       include: [
 405         {
 406           required: true,
 407           attributes: [ 'id' ],
 408           model: AccountModel.unscoped(),
 409           include: [
 410             {
 411               required: true,
 412               attributes: [ 'id' ],
 413               model: VideoChannelModel.unscoped(),
 414               include: [
 415                 {
 416                   required: true,
 417                   attributes: [ 'id' ],
 418                   model: VideoModel.unscoped(),
 419                   where: {
 420                     id: videoId
 421                   }
 422                 }
 423               ]
 424             }
 425           ]
 426         }
 427       ]
 428     }
 429
 430     return UserModel.findOne(query)
 431   }
 432
 433   static loadByVideoImportId (videoImportId: number) {
 434     const query = {
 435       include: [
 436         {
 437           required: true,
 438           attributes: [ 'id' ],
 439           model: VideoImportModel.unscoped(),
 440           where: {
 441             id: videoImportId
 442           }
 443         }
 444       ]
 445     }
 446
 447     return UserModel.findOne(query)
 448   }
 449
 450   static loadByChannelActorId (videoChannelActorId: number) {
 451     const query = {
 452       include: [
 453         {
 454           required: true,
 455           attributes: [ 'id' ],
 456           model: AccountModel.unscoped(),
 457           include: [
 458             {
 459               required: true,
 460               attributes: [ 'id' ],
 461               model: VideoChannelModel.unscoped(),
 462               where: {
 463                 actorId: videoChannelActorId
 464               }
 465             }
 466           ]
 467         }
 468       ]
 469     }
 470
 471     return UserModel.findOne(query)
 472   }
 473
 474   static loadByAccountActorId (accountActorId: number) {
 475     const query = {
 476       include: [
 477         {
 478           required: true,
 479           attributes: [ 'id' ],
 480           model: AccountModel.unscoped(),
 481           where: {
 482             actorId: accountActorId
 483           }
 484         }
 485       ]
 486     }
 487
 488     return UserModel.findOne(query)
 489   }
 490
 491   static getOriginalVideoFileTotalFromUser (user: UserModel) {
 492     // Don't use sequelize because we need to use a sub query
 493     const query = UserModel.generateUserQuotaBaseSQL()
 494
 495     return UserModel.getTotalRawQuery(query, user.id)
 496   }
 497
 498   // Returns cumulative size of all video files uploaded in the last 24 hours.
 499   static getOriginalVideoFileTotalDailyFromUser (user: UserModel) {
 500     // Don't use sequelize because we need to use a sub query
 501     const query = UserModel.generateUserQuotaBaseSQL('"video"."createdAt" > now() - interval \'24 hours\'')
 502
 503     return UserModel.getTotalRawQuery(query, user.id)
 504   }
 505
 506   static async getStats () {
 507     const totalUsers = await UserModel.count()
 508
 509     return {
 510       totalUsers
 511     }
 512   }
 513
 514   static autoComplete (search: string) {
 515     const query = {
 516       where: {
 517         username: {
 518           [ Op.like ]: `%${search}%`
 519         }
 520       },
 521       limit: 10
 522     }
 523
 524     return UserModel.findAll(query)
 525                     .then(u => u.map(u => u.username))
 526   }
 527
 528   hasRight (right: UserRight) {
 529     return hasUserRight(this.role, right)
 530   }
 531
 532   hasAdminFlag (flag: UserAdminFlag) {
 533     return this.adminFlags & flag
 534   }
 535
 536   isPasswordMatch (password: string) {
 537     return comparePassword(password, this.password)
 538   }
 539
 540   toFormattedJSON (parameters: { withAdminFlags?: boolean } = {}): User {
 541     const videoQuotaUsed = this.get('videoQuotaUsed')
 542     const videoQuotaUsedDaily = this.get('videoQuotaUsedDaily')
 543
 544     const json = {
 545       id: this.id,
 546       username: this.username,
 547       email: this.email,
 548       pendingEmail: this.pendingEmail,
 549       emailVerified: this.emailVerified,
 550       nsfwPolicy: this.nsfwPolicy,
 551       webTorrentEnabled: this.webTorrentEnabled,
 552       videosHistoryEnabled: this.videosHistoryEnabled,
 553       autoPlayVideo: this.autoPlayVideo,
 554       role: this.role,
 555       roleLabel: USER_ROLE_LABELS[ this.role ],
 556       videoQuota: this.videoQuota,
 557       videoQuotaDaily: this.videoQuotaDaily,
 558       createdAt: this.createdAt,
 559       blocked: this.blocked,
 560       blockedReason: this.blockedReason,
 561       account: this.Account.toFormattedJSON(),
 562       notificationSettings: this.NotificationSetting ? this.NotificationSetting.toFormattedJSON() : undefined,
 563       videoChannels: [],
 564       videoQuotaUsed: videoQuotaUsed !== undefined
 565             ? parseInt(videoQuotaUsed + '', 10)
 566             : undefined,
 567       videoQuotaUsedDaily: videoQuotaUsedDaily !== undefined
 568             ? parseInt(videoQuotaUsedDaily + '', 10)
 569             : undefined
 570     }
 571
 572     if (parameters.withAdminFlags) {
 573       Object.assign(json, { adminFlags: this.adminFlags })
 574     }
 575
 576     if (Array.isArray(this.Account.VideoChannels) === true) {
 577       json.videoChannels = this.Account.VideoChannels
 578         .map(c => c.toFormattedJSON())
 579         .sort((v1, v2) => {
 580           if (v1.createdAt < v2.createdAt) return -1
 581           if (v1.createdAt === v2.createdAt) return 0
 582
 583           return 1
 584         })
 585     }
 586
 587     return json
 588   }
 589
 590   async isAbleToUploadVideo (videoFile: { size: number }) {
 591     if (this.videoQuota === -1 && this.videoQuotaDaily === -1) return Promise.resolve(true)
 592
 593     const [ totalBytes, totalBytesDaily ] = await Promise.all([
 594       UserModel.getOriginalVideoFileTotalFromUser(this),
 595       UserModel.getOriginalVideoFileTotalDailyFromUser(this)
 596     ])
 597
 598     const uploadedTotal = videoFile.size + totalBytes
 599     const uploadedDaily = videoFile.size + totalBytesDaily
 600
 601     if (this.videoQuotaDaily === -1) return uploadedTotal < this.videoQuota
 602     if (this.videoQuota === -1) return uploadedDaily < this.videoQuotaDaily
 603
 604     return uploadedTotal < this.videoQuota && uploadedDaily < this.videoQuotaDaily
 605   }
 606
 607   private static generateUserQuotaBaseSQL (where?: string) {
 608     const andWhere = where ? 'AND ' + where : ''
 609
 610     return 'SELECT SUM("size") AS "total" ' +
 611       'FROM (' +
 612         'SELECT MAX("videoFile"."size") AS "size" FROM "videoFile" ' +
 613         'INNER JOIN "video" ON "videoFile"."videoId" = "video"."id" ' +
 614         'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" ' +
 615         'INNER JOIN "account" ON "videoChannel"."accountId" = "account"."id" ' +
 616         'WHERE "account"."userId" = $userId ' + andWhere +
 617         'GROUP BY "video"."id"' +
 618       ') t'
 619   }
 620
 621   private static getTotalRawQuery (query: string, userId: number) {
 622     const options = {
 623       bind: { userId },
 624       type: QueryTypes.SELECT as QueryTypes.SELECT
 625     }
 626
 627     return UserModel.sequelize.query<{ total: string }>(query, options)
 628                     .then(([ { total } ]) => {
 629                       if (total === null) return 0
 630
 631                       return parseInt(total, 10)
 632                     })
 633   }
 634 }