aboutsummaryrefslogtreecommitdiffhomepage
path: root/server/models/account
diff options
context:
space:
mode:
Diffstat (limited to 'server/models/account')
-rw-r--r--server/models/account/user.ts92
1 files changed, 60 insertions, 32 deletions
diff --git a/server/models/account/user.ts b/server/models/account/user.ts
index dec99d90a..da40bf290 100644
--- a/server/models/account/user.ts
+++ b/server/models/account/user.ts
@@ -1,4 +1,4 @@
1import { FindOptions, literal, Op, QueryTypes, where, fn, col, WhereOptions } from 'sequelize' 1import { col, FindOptions, fn, literal, Op, QueryTypes, where, WhereOptions } from 'sequelize'
2import { 2import {
3 AfterDestroy, 3 AfterDestroy,
4 AfterUpdate, 4 AfterUpdate,
@@ -19,7 +19,7 @@ import {
19 Table, 19 Table,
20 UpdatedAt 20 UpdatedAt
21} from 'sequelize-typescript' 21} from 'sequelize-typescript'
22import { hasUserRight, MyUser, USER_ROLE_LABELS, UserRight, VideoPlaylistType, VideoPrivacy, VideoAbuseState } from '../../../shared' 22import { hasUserRight, MyUser, USER_ROLE_LABELS, UserRight, VideoAbuseState, VideoPlaylistType, VideoPrivacy } from '../../../shared'
23import { User, UserRole } from '../../../shared/models/users' 23import { User, UserRole } from '../../../shared/models/users'
24import { 24import {
25 isNoInstanceConfigWarningModal, 25 isNoInstanceConfigWarningModal,
@@ -70,22 +70,6 @@ import {
70 MVideoFullLight 70 MVideoFullLight
71} from '@server/typings/models' 71} from '@server/typings/models'
72 72
73const literalVideoQuotaUsed: any = [
74 literal(
75 '(' +
76 'SELECT COALESCE(SUM("size"), 0) ' +
77 'FROM (' +
78 'SELECT MAX("videoFile"."size") AS "size" FROM "videoFile" ' +
79 'INNER JOIN "video" ON "videoFile"."videoId" = "video"."id" ' +
80 'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" ' +
81 'INNER JOIN "account" ON "videoChannel"."accountId" = "account"."id" ' +
82 'WHERE "account"."userId" = "UserModel"."id" GROUP BY "video"."id"' +
83 ') t' +
84 ')'
85 ),
86 'videoQuotaUsed'
87]
88
89enum ScopeNames { 73enum ScopeNames {
90 FOR_ME_API = 'FOR_ME_API', 74 FOR_ME_API = 'FOR_ME_API',
91 WITH_VIDEOCHANNELS = 'WITH_VIDEOCHANNELS', 75 WITH_VIDEOCHANNELS = 'WITH_VIDEOCHANNELS',
@@ -156,7 +140,17 @@ enum ScopeNames {
156 [ScopeNames.WITH_STATS]: { 140 [ScopeNames.WITH_STATS]: {
157 attributes: { 141 attributes: {
158 include: [ 142 include: [
159 literalVideoQuotaUsed, 143 [
144 literal(
145 '(' +
146 UserModel.generateUserQuotaBaseSQL({
147 withSelect: false,
148 whereUserId: '"UserModel"."id"'
149 }) +
150 ')'
151 ),
152 'videoQuotaUsed'
153 ],
160 [ 154 [
161 literal( 155 literal(
162 '(' + 156 '(' +
@@ -430,7 +424,19 @@ export class UserModel extends Model<UserModel> {
430 424
431 const query: FindOptions = { 425 const query: FindOptions = {
432 attributes: { 426 attributes: {
433 include: [ literalVideoQuotaUsed ] 427 include: [
428 [
429 literal(
430 '(' +
431 UserModel.generateUserQuotaBaseSQL({
432 withSelect: false,
433 whereUserId: '"UserModel"."id"'
434 }) +
435 ')'
436 ),
437 'videoQuotaUsed'
438 ] as any // FIXME: typings
439 ]
434 }, 440 },
435 offset: start, 441 offset: start,
436 limit: count, 442 limit: count,
@@ -659,7 +665,10 @@ export class UserModel extends Model<UserModel> {
659 665
660 static getOriginalVideoFileTotalFromUser (user: MUserId) { 666 static getOriginalVideoFileTotalFromUser (user: MUserId) {
661 // Don't use sequelize because we need to use a sub query 667 // Don't use sequelize because we need to use a sub query
662 const query = UserModel.generateUserQuotaBaseSQL() 668 const query = UserModel.generateUserQuotaBaseSQL({
669 withSelect: true,
670 whereUserId: '$userId'
671 })
663 672
664 return UserModel.getTotalRawQuery(query, user.id) 673 return UserModel.getTotalRawQuery(query, user.id)
665 } 674 }
@@ -667,7 +676,11 @@ export class UserModel extends Model<UserModel> {
667 // Returns cumulative size of all video files uploaded in the last 24 hours. 676 // Returns cumulative size of all video files uploaded in the last 24 hours.
668 static getOriginalVideoFileTotalDailyFromUser (user: MUserId) { 677 static getOriginalVideoFileTotalDailyFromUser (user: MUserId) {
669 // Don't use sequelize because we need to use a sub query 678 // Don't use sequelize because we need to use a sub query
670 const query = UserModel.generateUserQuotaBaseSQL('"video"."createdAt" > now() - interval \'24 hours\'') 679 const query = UserModel.generateUserQuotaBaseSQL({
680 withSelect: true,
681 whereUserId: '$userId',
682 where: '"video"."createdAt" > now() - interval \'24 hours\''
683 })
671 684
672 return UserModel.getTotalRawQuery(query, user.id) 685 return UserModel.getTotalRawQuery(query, user.id)
673 } 686 }
@@ -835,18 +848,33 @@ export class UserModel extends Model<UserModel> {
835 return uploadedTotal < this.videoQuota && uploadedDaily < this.videoQuotaDaily 848 return uploadedTotal < this.videoQuota && uploadedDaily < this.videoQuotaDaily
836 } 849 }
837 850
838 private static generateUserQuotaBaseSQL (where?: string) { 851 private static generateUserQuotaBaseSQL (options: {
839 const andWhere = where ? 'AND ' + where : '' 852 whereUserId: '$userId' | '"UserModel"."id"'
853 withSelect: boolean
854 where?: string
855 }) {
856 const andWhere = options.where
857 ? 'AND ' + options.where
858 : ''
840 859
841 return 'SELECT SUM("size") AS "total" ' + 860 const videoChannelJoin = 'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" ' +
842 'FROM (' +
843 'SELECT MAX("videoFile"."size") AS "size" FROM "videoFile" ' +
844 'INNER JOIN "video" ON "videoFile"."videoId" = "video"."id" ' +
845 'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" ' +
846 'INNER JOIN "account" ON "videoChannel"."accountId" = "account"."id" ' + 861 'INNER JOIN "account" ON "videoChannel"."accountId" = "account"."id" ' +
847 'WHERE "account"."userId" = $userId ' + andWhere + 862 `WHERE "account"."userId" = ${options.whereUserId} ${andWhere}`
848 'GROUP BY "video"."id"' + 863
849 ') t' 864 const webtorrentFiles = 'SELECT "videoFile"."size" AS "size", "video"."id" AS "videoId" FROM "videoFile" ' +
865 'INNER JOIN "video" ON "videoFile"."videoId" = "video"."id" ' +
866 videoChannelJoin
867
868 const hlsFiles = 'SELECT "videoFile"."size" AS "size", "video"."id" AS "videoId" FROM "videoFile" ' +
869 'INNER JOIN "videoStreamingPlaylist" ON "videoFile"."videoStreamingPlaylistId" = "videoStreamingPlaylist".id ' +
870 'INNER JOIN "video" ON "videoStreamingPlaylist"."videoId" = "video"."id" ' +
871 videoChannelJoin
872
873 return 'SELECT COALESCE(SUM("size"), 0) AS "total" ' +
874 'FROM (' +
875 `SELECT MAX("t1"."size") AS "size" FROM (${webtorrentFiles} UNION ${hlsFiles}) t1 ` +
876 'GROUP BY "t1"."videoId"' +
877 ') t2'
850 } 878 }
851 879
852 private static getTotalRawQuery (query: string, userId: number) { 880 private static getTotalRawQuery (query: string, userId: number) {