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