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