X-Git-Url: https://git.immae.eu/?a=blobdiff_plain;f=server%2Fmodels%2Faccount%2Fuser.ts;h=de193131a27815d8ee31168528beae93eeaa7575;hb=8491293b02ed2ec53eb0fa128161ea0b08d3def9;hp=38c6d474aa2e6916ce7170bcf28a58512a214666;hpb=6aa541481390980f9c85d2e66514ba0e6ce77a35;p=github%2FChocobozzz%2FPeerTube.git

diff --git a/server/models/account/user.ts b/server/models/account/user.ts
index 38c6d474a..de193131a 100644
--- a/server/models/account/user.ts
+++ b/server/models/account/user.ts
@@ -1,4 +1,4 @@
-import { FindOptions, literal, Op, QueryTypes } from 'sequelize'
+import { col, FindOptions, fn, literal, Op, QueryTypes, where, WhereOptions } from 'sequelize'
 import {
   AfterDestroy,
   AfterUpdate,
@@ -19,13 +19,15 @@ import {
   Table,
   UpdatedAt
 } from 'sequelize-typescript'
-import { hasUserRight, USER_ROLE_LABELS, UserRight } from '../../../shared'
+import { hasUserRight, MyUser, USER_ROLE_LABELS, UserRight, VideoAbuseState, VideoPlaylistType, VideoPrivacy } from '../../../shared'
 import { User, UserRole } from '../../../shared/models/users'
 import {
   isNoInstanceConfigWarningModal,
+  isNoWelcomeModal,
   isUserAdminFlagsValid,
-  isUserAutoPlayVideoValid,
+  isUserAutoPlayNextVideoPlaylistValid,
   isUserAutoPlayNextVideoValid,
+  isUserAutoPlayVideoValid,
   isUserBlockedReasonValid,
   isUserBlockedValid,
   isUserEmailVerifiedValid,
@@ -37,17 +39,17 @@ import {
   isUserVideoQuotaDailyValid,
   isUserVideoQuotaValid,
   isUserVideosHistoryEnabledValid,
-  isUserWebTorrentEnabledValid,
-  isNoWelcomeModal
+  isUserWebTorrentEnabledValid
 } from '../../helpers/custom-validators/users'
 import { comparePassword, cryptPassword } from '../../helpers/peertube-crypto'
 import { OAuthTokenModel } from '../oauth/oauth-token'
 import { getSort, throwIfNotValid } from '../utils'
 import { VideoChannelModel } from '../video/video-channel'
+import { VideoPlaylistModel } from '../video/video-playlist'
 import { AccountModel } from './account'
 import { NSFWPolicyType } from '../../../shared/models/videos/nsfw-policy.type'
 import { values } from 'lodash'
-import { DEFAULT_THEME_NAME, DEFAULT_USER_THEME_NAME, NSFW_POLICY_TYPES } from '../../initializers/constants'
+import { DEFAULT_USER_THEME_NAME, NSFW_POLICY_TYPES } from '../../initializers/constants'
 import { clearCacheByUserId } from '../../lib/oauth-model'
 import { UserNotificationSettingModel } from './user-notification-setting'
 import { VideoModel } from '../video/video'
@@ -59,15 +61,19 @@ import { isThemeNameValid } from '../../helpers/custom-validators/plugins'
 import { getThemeOrDefault } from '../../lib/plugins/theme-utils'
 import * as Bluebird from 'bluebird'
 import {
+  MMyUserFormattable,
   MUserDefault,
   MUserFormattable,
   MUserId,
   MUserNotifSettingChannelDefault,
-  MUserWithNotificationSetting
-} from '@server/typings/models'
+  MUserWithNotificationSetting,
+  MVideoFullLight
+} from '@server/types/models'
 
 enum ScopeNames {
-  WITH_VIDEO_CHANNEL = 'WITH_VIDEO_CHANNEL'
+  FOR_ME_API = 'FOR_ME_API',
+  WITH_VIDEOCHANNELS = 'WITH_VIDEOCHANNELS',
+  WITH_STATS = 'WITH_STATS'
 }
 
 @DefaultScope(() => ({
@@ -83,18 +89,121 @@ enum ScopeNames {
   ]
 }))
 @Scopes(() => ({
-  [ScopeNames.WITH_VIDEO_CHANNEL]: {
+  [ScopeNames.FOR_ME_API]: {
     include: [
       {
         model: AccountModel,
-        required: true,
-        include: [ VideoChannelModel ]
+        include: [
+          {
+            model: VideoChannelModel
+          },
+          {
+            attributes: [ 'id', 'name', 'type' ],
+            model: VideoPlaylistModel.unscoped(),
+            required: true,
+            where: {
+              type: {
+                [Op.ne]: VideoPlaylistType.REGULAR
+              }
+            }
+          }
+        ]
       },
       {
         model: UserNotificationSettingModel,
         required: true
       }
     ]
+  },
+  [ScopeNames.WITH_VIDEOCHANNELS]: {
+    include: [
+      {
+        model: AccountModel,
+        include: [
+          {
+            model: VideoChannelModel
+          },
+          {
+            attributes: [ 'id', 'name', 'type' ],
+            model: VideoPlaylistModel.unscoped(),
+            required: true,
+            where: {
+              type: {
+                [Op.ne]: VideoPlaylistType.REGULAR
+              }
+            }
+          }
+        ]
+      }
+    ]
+  },
+  [ScopeNames.WITH_STATS]: {
+    attributes: {
+      include: [
+        [
+          literal(
+            '(' +
+              UserModel.generateUserQuotaBaseSQL({
+                withSelect: false,
+                whereUserId: '"UserModel"."id"'
+              }) +
+            ')'
+          ),
+          'videoQuotaUsed'
+        ],
+        [
+          literal(
+            '(' +
+              'SELECT COUNT("video"."id") ' +
+              'FROM "video" ' +
+              'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" ' +
+              'INNER JOIN "account" ON "account"."id" = "videoChannel"."accountId" ' +
+              'WHERE "account"."userId" = "UserModel"."id"' +
+            ')'
+          ),
+          'videosCount'
+        ],
+        [
+          literal(
+            '(' +
+              `SELECT concat_ws(':', "abuses", "acceptedAbuses") ` +
+              'FROM (' +
+                'SELECT COUNT("videoAbuse"."id") AS "abuses", ' +
+                       `COUNT("videoAbuse"."id") FILTER (WHERE "videoAbuse"."state" = ${VideoAbuseState.ACCEPTED}) AS "acceptedAbuses" ` +
+                'FROM "videoAbuse" ' +
+                'INNER JOIN "video" ON "videoAbuse"."videoId" = "video"."id" ' +
+                'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" ' +
+                'INNER JOIN "account" ON "account"."id" = "videoChannel"."accountId" ' +
+                'WHERE "account"."userId" = "UserModel"."id"' +
+              ') t' +
+            ')'
+          ),
+          'videoAbusesCount'
+        ],
+        [
+          literal(
+            '(' +
+              'SELECT COUNT("videoAbuse"."id") ' +
+              'FROM "videoAbuse" ' +
+              'INNER JOIN "account" ON "account"."id" = "videoAbuse"."reporterAccountId" ' +
+              'WHERE "account"."userId" = "UserModel"."id"' +
+            ')'
+          ),
+          'videoAbusesCreatedCount'
+        ],
+        [
+          literal(
+            '(' +
+              'SELECT COUNT("videoComment"."id") ' +
+              'FROM "videoComment" ' +
+              'INNER JOIN "account" ON "account"."id" = "videoComment"."accountId" ' +
+              'WHERE "account"."userId" = "UserModel"."id"' +
+            ')'
+          ),
+          'videoCommentsCount'
+        ]
+      ]
+    }
   }
 }))
 @Table({
@@ -112,13 +221,13 @@ enum ScopeNames {
 })
 export class UserModel extends Model<UserModel> {
 
-  @AllowNull(false)
-  @Is('UserPassword', value => throwIfNotValid(value, isUserPasswordValid, 'user password'))
+  @AllowNull(true)
+  @Is('UserPassword', value => throwIfNotValid(value, isUserPasswordValid, 'user password', true))
   @Column
   password: string
 
   @AllowNull(false)
-  @Is('UserPassword', value => throwIfNotValid(value, isUserUsernameValid, 'user name'))
+  @Is('UserUsername', value => throwIfNotValid(value, isUserUsernameValid, 'user name'))
   @Column
   username: string
 
@@ -167,6 +276,15 @@ export class UserModel extends Model<UserModel> {
   @Column
   autoPlayNextVideo: boolean
 
+  @AllowNull(false)
+  @Default(true)
+  @Is(
+    'UserAutoPlayNextVideoPlaylist',
+    value => throwIfNotValid(value, isUserAutoPlayNextVideoPlaylistValid, 'auto play next video for playlists boolean')
+  )
+  @Column
+  autoPlayNextVideoPlaylist: boolean
+
   @AllowNull(true)
   @Default(null)
   @Is('UserVideoLanguages', value => throwIfNotValid(value, isUserVideoLanguages, 'video languages'))
@@ -207,7 +325,7 @@ export class UserModel extends Model<UserModel> {
   videoQuotaDaily: number
 
   @AllowNull(false)
-  @Default(DEFAULT_THEME_NAME)
+  @Default(DEFAULT_USER_THEME_NAME)
   @Is('UserTheme', value => throwIfNotValid(value, isThemeNameValid, 'theme'))
   @Column
   theme: string
@@ -230,6 +348,16 @@ export class UserModel extends Model<UserModel> {
   @Column
   noWelcomeModal: boolean
 
+  @AllowNull(true)
+  @Default(null)
+  @Column
+  pluginAuth: string
+
+  @AllowNull(true)
+  @Default(null)
+  @Column
+  lastLoginDate: Date
+
   @CreatedAt
   createdAt: Date
 
@@ -265,7 +393,7 @@ export class UserModel extends Model<UserModel> {
   @BeforeCreate
   @BeforeUpdate
   static cryptPasswordIfNeeded (instance: UserModel) {
-    if (instance.changed('password')) {
+    if (instance.changed('password') && instance.password) {
       return cryptPassword(instance.password)
         .then(hash => {
           instance.password = hash
@@ -284,10 +412,18 @@ export class UserModel extends Model<UserModel> {
     return this.count()
   }
 
-  static listForApi (start: number, count: number, sort: string, search?: string) {
-    let where = undefined
+  static listForApi (parameters: {
+    start: number
+    count: number
+    sort: string
+    search?: string
+    blocked?: boolean
+  }) {
+    const { start, count, sort, search, blocked } = parameters
+    const where: WhereOptions = {}
+
     if (search) {
-      where = {
+      Object.assign(where, {
         [Op.or]: [
           {
             email: {
@@ -296,11 +432,17 @@ export class UserModel extends Model<UserModel> {
           },
           {
             username: {
-              [ Op.iLike ]: '%' + search + '%'
+              [Op.iLike]: '%' + search + '%'
             }
           }
         ]
-      }
+      })
+    }
+
+    if (blocked !== undefined) {
+      Object.assign(where, {
+        blocked: blocked
+      })
     }
 
     const query: FindOptions = {
@@ -309,18 +451,14 @@ export class UserModel extends Model<UserModel> {
           [
             literal(
               '(' +
-                'SELECT COALESCE(SUM("size"), 0) ' +
-                'FROM (' +
-                  'SELECT MAX("videoFile"."size") AS "size" FROM "videoFile" ' +
-                  'INNER JOIN "video" ON "videoFile"."videoId" = "video"."id" ' +
-                  'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" ' +
-                  'INNER JOIN "account" ON "videoChannel"."accountId" = "account"."id" ' +
-                  'WHERE "account"."userId" = "UserModel"."id" GROUP BY "video"."id"' +
-                ') t' +
+                UserModel.generateUserQuotaBaseSQL({
+                  withSelect: false,
+                  whereUserId: '"UserModel"."id"'
+                }) +
               ')'
             ),
             'videoQuotaUsed'
-          ]
+          ] as any // FIXME: typings
         ]
       },
       offset: start,
@@ -330,18 +468,18 @@ export class UserModel extends Model<UserModel> {
     }
 
     return UserModel.findAndCountAll(query)
-      .then(({ rows, count }) => {
-        return {
-          data: rows,
-          total: count
-        }
-      })
+                    .then(({ rows, count }) => {
+                      return {
+                        data: rows,
+                        total: count
+                      }
+                    })
   }
 
   static listWithRight (right: UserRight): Bluebird<MUserDefault[]> {
     const roles = Object.keys(USER_ROLE_LABELS)
-      .map(k => parseInt(k, 10) as UserRole)
-      .filter(role => hasUserRight(role, right))
+                        .map(k => parseInt(k, 10) as UserRole)
+                        .filter(role => hasUserRight(role, right))
 
     const query = {
       where: {
@@ -367,7 +505,7 @@ export class UserModel extends Model<UserModel> {
           required: true,
           include: [
             {
-              attributes: [ ],
+              attributes: [],
               model: ActorModel.unscoped(),
               required: true,
               where: {
@@ -375,7 +513,7 @@ export class UserModel extends Model<UserModel> {
               },
               include: [
                 {
-                  attributes: [ ],
+                  attributes: [],
                   as: 'ActorFollowings',
                   model: ActorFollowModel.unscoped(),
                   required: true,
@@ -403,28 +541,34 @@ export class UserModel extends Model<UserModel> {
     return UserModel.findAll(query)
   }
 
-  static loadById (id: number): Bluebird<MUserDefault> {
-    return UserModel.findByPk(id)
+  static loadById (id: number, withStats = false): Bluebird<MUserDefault> {
+    const scopes = [
+      ScopeNames.WITH_VIDEOCHANNELS
+    ]
+
+    if (withStats) scopes.push(ScopeNames.WITH_STATS)
+
+    return UserModel.scope(scopes).findByPk(id)
   }
 
   static loadByUsername (username: string): Bluebird<MUserDefault> {
     const query = {
       where: {
-        username: { [ Op.iLike ]: username }
+        username: { [Op.iLike]: username }
       }
     }
 
     return UserModel.findOne(query)
   }
 
-  static loadByUsernameAndPopulateChannels (username: string): Bluebird<MUserNotifSettingChannelDefault> {
+  static loadForMeAPI (username: string): Bluebird<MUserNotifSettingChannelDefault> {
     const query = {
       where: {
-        username: { [ Op.iLike ]: username }
+        username: { [Op.iLike]: username }
       }
     }
 
-    return UserModel.scope(ScopeNames.WITH_VIDEO_CHANNEL).findOne(query)
+    return UserModel.scope(ScopeNames.FOR_ME_API).findOne(query)
   }
 
   static loadByEmail (email: string): Bluebird<MUserDefault> {
@@ -442,7 +586,11 @@ export class UserModel extends Model<UserModel> {
 
     const query = {
       where: {
-        [ Op.or ]: [ { username: { [ Op.iLike ]: username } }, { email } ]
+        [Op.or]: [
+          where(fn('lower', col('username')), fn('lower', username)),
+
+          { email }
+        ]
       }
     }
 
@@ -540,7 +688,10 @@ export class UserModel extends Model<UserModel> {
 
   static getOriginalVideoFileTotalFromUser (user: MUserId) {
     // Don't use sequelize because we need to use a sub query
-    const query = UserModel.generateUserQuotaBaseSQL()
+    const query = UserModel.generateUserQuotaBaseSQL({
+      withSelect: true,
+      whereUserId: '$userId'
+    })
 
     return UserModel.getTotalRawQuery(query, user.id)
   }
@@ -548,16 +699,38 @@ export class UserModel extends Model<UserModel> {
   // Returns cumulative size of all video files uploaded in the last 24 hours.
   static getOriginalVideoFileTotalDailyFromUser (user: MUserId) {
     // Don't use sequelize because we need to use a sub query
-    const query = UserModel.generateUserQuotaBaseSQL('"video"."createdAt" > now() - interval \'24 hours\'')
+    const query = UserModel.generateUserQuotaBaseSQL({
+      withSelect: true,
+      whereUserId: '$userId',
+      where: '"video"."createdAt" > now() - interval \'24 hours\''
+    })
 
     return UserModel.getTotalRawQuery(query, user.id)
   }
 
   static async getStats () {
+    function getActiveUsers (days: number) {
+      const query = {
+        where: {
+          [Op.and]: [
+            literal(`"lastLoginDate" > NOW() - INTERVAL '${days}d'`)
+          ]
+        }
+      }
+
+      return UserModel.count(query)
+    }
+
     const totalUsers = await UserModel.count()
+    const totalDailyActiveUsers = await getActiveUsers(1)
+    const totalWeeklyActiveUsers = await getActiveUsers(7)
+    const totalMonthlyActiveUsers = await getActiveUsers(30)
 
     return {
-      totalUsers
+      totalUsers,
+      totalDailyActiveUsers,
+      totalWeeklyActiveUsers,
+      totalMonthlyActiveUsers
     }
   }
 
@@ -565,7 +738,7 @@ export class UserModel extends Model<UserModel> {
     const query = {
       where: {
         username: {
-          [ Op.like ]: `%${search}%`
+          [Op.like]: `%${search}%`
         }
       },
       limit: 10
@@ -575,6 +748,22 @@ export class UserModel extends Model<UserModel> {
                     .then(u => u.map(u => u.username))
   }
 
+  canGetVideo (video: MVideoFullLight) {
+    const videoUserId = video.VideoChannel.Account.userId
+
+    if (video.isBlacklisted()) {
+      return videoUserId === this.id || this.hasRight(UserRight.MANAGE_VIDEO_BLACKLIST)
+    }
+
+    if (video.privacy === VideoPrivacy.PRIVATE) {
+      return video.VideoChannel && videoUserId === this.id || this.hasRight(UserRight.MANAGE_VIDEO_BLACKLIST)
+    }
+
+    if (video.privacy === VideoPrivacy.INTERNAL) return true
+
+    return false
+  }
+
   hasRight (right: UserRight) {
     return hasUserRight(this.role, right)
   }
@@ -590,6 +779,10 @@ export class UserModel extends Model<UserModel> {
   toFormattedJSON (this: MUserFormattable, parameters: { withAdminFlags?: boolean } = {}): User {
     const videoQuotaUsed = this.get('videoQuotaUsed')
     const videoQuotaUsedDaily = this.get('videoQuotaUsedDaily')
+    const videosCount = this.get('videosCount')
+    const [ videoAbusesCount, videoAbusesAcceptedCount ] = (this.get('videoAbusesCount') as string || ':').split(':')
+    const videoAbusesCreatedCount = this.get('videoAbusesCreatedCount')
+    const videoCommentsCount = this.get('videoCommentsCount')
 
     const json: User = {
       id: this.id,
@@ -605,10 +798,11 @@ export class UserModel extends Model<UserModel> {
       videosHistoryEnabled: this.videosHistoryEnabled,
       autoPlayVideo: this.autoPlayVideo,
       autoPlayNextVideo: this.autoPlayNextVideo,
+      autoPlayNextVideoPlaylist: this.autoPlayNextVideoPlaylist,
       videoLanguages: this.videoLanguages,
 
       role: this.role,
-      roleLabel: USER_ROLE_LABELS[ this.role ],
+      roleLabel: USER_ROLE_LABELS[this.role],
 
       videoQuota: this.videoQuota,
       videoQuotaDaily: this.videoQuotaDaily,
@@ -618,6 +812,21 @@ export class UserModel extends Model<UserModel> {
       videoQuotaUsedDaily: videoQuotaUsedDaily !== undefined
         ? parseInt(videoQuotaUsedDaily + '', 10)
         : undefined,
+      videosCount: videosCount !== undefined
+        ? parseInt(videosCount + '', 10)
+        : undefined,
+      videoAbusesCount: videoAbusesCount
+        ? parseInt(videoAbusesCount, 10)
+        : undefined,
+      videoAbusesAcceptedCount: videoAbusesAcceptedCount
+        ? parseInt(videoAbusesAcceptedCount, 10)
+        : undefined,
+      videoAbusesCreatedCount: videoAbusesCreatedCount !== undefined
+        ? parseInt(videoAbusesCreatedCount + '', 10)
+        : undefined,
+      videoCommentsCount: videoCommentsCount !== undefined
+        ? parseInt(videoCommentsCount + '', 10)
+        : undefined,
 
       noInstanceConfigWarningModal: this.noInstanceConfigWarningModal,
       noWelcomeModal: this.noWelcomeModal,
@@ -633,7 +842,11 @@ export class UserModel extends Model<UserModel> {
 
       videoChannels: [],
 
-      createdAt: this.createdAt
+      createdAt: this.createdAt,
+
+      pluginAuth: this.pluginAuth,
+
+      lastLoginDate: this.lastLoginDate
     }
 
     if (parameters.withAdminFlags) {
@@ -642,18 +855,27 @@ export class UserModel extends Model<UserModel> {
 
     if (Array.isArray(this.Account.VideoChannels) === true) {
       json.videoChannels = this.Account.VideoChannels
-        .map(c => c.toFormattedJSON())
-        .sort((v1, v2) => {
-          if (v1.createdAt < v2.createdAt) return -1
-          if (v1.createdAt === v2.createdAt) return 0
+                               .map(c => c.toFormattedJSON())
+                               .sort((v1, v2) => {
+                                 if (v1.createdAt < v2.createdAt) return -1
+                                 if (v1.createdAt === v2.createdAt) return 0
 
-          return 1
-        })
+                                 return 1
+                               })
     }
 
     return json
   }
 
+  toMeFormattedJSON (this: MMyUserFormattable): MyUser {
+    const formatted = this.toFormattedJSON()
+
+    const specialPlaylists = this.Account.VideoPlaylists
+                                 .map(p => ({ id: p.id, name: p.name, type: p.type }))
+
+    return Object.assign(formatted, { specialPlaylists })
+  }
+
   async isAbleToUploadVideo (videoFile: { size: number }) {
     if (this.videoQuota === -1 && this.videoQuotaDaily === -1) return Promise.resolve(true)
 
@@ -671,18 +893,33 @@ export class UserModel extends Model<UserModel> {
     return uploadedTotal < this.videoQuota && uploadedDaily < this.videoQuotaDaily
   }
 
-  private static generateUserQuotaBaseSQL (where?: string) {
-    const andWhere = where ? 'AND ' + where : ''
-
-    return 'SELECT SUM("size") AS "total" ' +
+  private static generateUserQuotaBaseSQL (options: {
+    whereUserId: '$userId' | '"UserModel"."id"'
+    withSelect: boolean
+    where?: string
+  }) {
+    const andWhere = options.where
+      ? 'AND ' + options.where
+      : ''
+
+    const videoChannelJoin = 'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" ' +
+      'INNER JOIN "account" ON "videoChannel"."accountId" = "account"."id" ' +
+      `WHERE "account"."userId" = ${options.whereUserId} ${andWhere}`
+
+    const webtorrentFiles = 'SELECT "videoFile"."size" AS "size", "video"."id" AS "videoId" FROM "videoFile" ' +
+      'INNER JOIN "video" ON "videoFile"."videoId" = "video"."id" ' +
+      videoChannelJoin
+
+    const hlsFiles = 'SELECT "videoFile"."size" AS "size", "video"."id" AS "videoId" FROM "videoFile" ' +
+      'INNER JOIN "videoStreamingPlaylist" ON "videoFile"."videoStreamingPlaylistId" = "videoStreamingPlaylist".id ' +
+      'INNER JOIN "video" ON "videoStreamingPlaylist"."videoId" = "video"."id" ' +
+      videoChannelJoin
+
+    return 'SELECT COALESCE(SUM("size"), 0) AS "total" ' +
       'FROM (' +
-        'SELECT MAX("videoFile"."size") AS "size" FROM "videoFile" ' +
-        'INNER JOIN "video" ON "videoFile"."videoId" = "video"."id" ' +
-        'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" ' +
-        'INNER JOIN "account" ON "videoChannel"."accountId" = "account"."id" ' +
-        'WHERE "account"."userId" = $userId ' + andWhere +
-        'GROUP BY "video"."id"' +
-      ') t'
+        `SELECT MAX("t1"."size") AS "size" FROM (${webtorrentFiles} UNION ${hlsFiles}) t1 ` +
+        'GROUP BY "t1"."videoId"' +
+      ') t2'
   }
 
   private static getTotalRawQuery (query: string, userId: number) {