aboutsummaryrefslogtreecommitdiffhomepage
path: root/server/models
diff options
context:
space:
mode:
authorChocobozzz <me@florianbigard.com>2018-08-28 18:29:29 +0200
committerChocobozzz <me@florianbigard.com>2018-08-28 18:29:48 +0200
commit8b60488020883c66d3831eacd030893ab184268f (patch)
tree5b1a98b1796cd2709f5497e2459fe94867befb6b /server/models
parent41a676db3989fe3eca91301ac5f5aea30d98654a (diff)
downloadPeerTube-8b60488020883c66d3831eacd030893ab184268f.tar.gz
PeerTube-8b60488020883c66d3831eacd030893ab184268f.tar.zst
PeerTube-8b60488020883c66d3831eacd030893ab184268f.zip
Refractor user quota SQL queries
Diffstat (limited to 'server/models')
-rw-r--r--server/models/account/user.ts73
-rw-r--r--server/models/video/video-channel.ts2
2 files changed, 36 insertions, 39 deletions
diff --git a/server/models/account/user.ts b/server/models/account/user.ts
index a88ec244f..bae683b12 100644
--- a/server/models/account/user.ts
+++ b/server/models/account/user.ts
@@ -172,8 +172,8 @@ export class UserModel extends Model<UserModel> {
172 [ 172 [
173 Sequelize.literal( 173 Sequelize.literal(
174 '(' + 174 '(' +
175 'SELECT COALESCE(SUM("size"), 0) FROM ' + 175 'SELECT COALESCE(SUM("size"), 0) ' +
176 '(' + 176 'FROM (' +
177 'SELECT MAX("videoFile"."size") AS "size" FROM "videoFile" ' + 177 'SELECT MAX("videoFile"."size") AS "size" FROM "videoFile" ' +
178 'INNER JOIN "video" ON "videoFile"."videoId" = "video"."id" ' + 178 'INNER JOIN "video" ON "videoFile"."videoId" = "video"."id" ' +
179 'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" ' + 179 'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" ' +
@@ -267,48 +267,17 @@ export class UserModel extends Model<UserModel> {
267 267
268 static getOriginalVideoFileTotalFromUser (user: UserModel) { 268 static getOriginalVideoFileTotalFromUser (user: UserModel) {
269 // Don't use sequelize because we need to use a sub query 269 // Don't use sequelize because we need to use a sub query
270 const query = 'SELECT SUM("size") AS "total" FROM ' + 270 const query = UserModel.generateUserQuotaBaseSQL()
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" = $userId ' +
276 'GROUP BY "video"."id") t'
277 271
278 const options = { 272 return UserModel.getTotalRawQuery(query, user.id)
279 bind: { userId: user.id },
280 type: Sequelize.QueryTypes.SELECT
281 }
282 return UserModel.sequelize.query(query, options)
283 .then(([ { total } ]) => {
284 if (total === null) return 0
285
286 return parseInt(total, 10)
287 })
288 } 273 }
289 274
290 // Returns comulative size of all video files uploaded in the last 24 hours. 275 // Returns cumulative size of all video files uploaded in the last 24 hours.
291 static getOriginalVideoFileTotalDailyFromUser (user: UserModel) { 276 static getOriginalVideoFileTotalDailyFromUser (user: UserModel) {
292 // Don't use sequelize because we need to use a sub query 277 // Don't use sequelize because we need to use a sub query
293 const query = 'SELECT SUM("size") AS "total" FROM ' + 278 const query = UserModel.generateUserQuotaBaseSQL('"video"."createdAt" > now() - interval \'24 hours\'')
294 '(SELECT MAX("videoFile"."size") AS "size" FROM "videoFile" ' +
295 'INNER JOIN "video" ON "videoFile"."videoId" = "video"."id" ' +
296 'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" ' +
297 'INNER JOIN "account" ON "videoChannel"."accountId" = "account"."id" ' +
298 'WHERE "account"."userId" = $userId ' +
299 'AND "video"."createdAt" > now() - interval \'24 hours\'' +
300 'GROUP BY "video"."id") t'
301
302 const options = {
303 bind: { userId: user.id },
304 type: Sequelize.QueryTypes.SELECT
305 }
306 return UserModel.sequelize.query(query, options)
307 .then(([ { total } ]) => {
308 if (total === null) return 0
309 279
310 return parseInt(total, 10) 280 return UserModel.getTotalRawQuery(query, user.id)
311 })
312 } 281 }
313 282
314 static async getStats () { 283 static async getStats () {
@@ -388,4 +357,32 @@ export class UserModel extends Model<UserModel> {
388 return (uploadedTotal < this.videoQuota) && 357 return (uploadedTotal < this.videoQuota) &&
389 (uploadedDaily < this.videoQuotaDaily) 358 (uploadedDaily < this.videoQuotaDaily)
390 } 359 }
360
361 private static generateUserQuotaBaseSQL (where?: string) {
362 const andWhere = where ? 'AND ' + where : ''
363
364 return 'SELECT SUM("size") AS "total" ' +
365 'FROM (' +
366 'SELECT MAX("videoFile"."size") AS "size" FROM "videoFile" ' +
367 'INNER JOIN "video" ON "videoFile"."videoId" = "video"."id" ' +
368 'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" ' +
369 'INNER JOIN "account" ON "videoChannel"."accountId" = "account"."id" ' +
370 'WHERE "account"."userId" = $userId ' + andWhere +
371 'GROUP BY "video"."id"' +
372 ') t'
373 }
374
375 private static getTotalRawQuery (query: string, userId: number) {
376 const options = {
377 bind: { userId },
378 type: Sequelize.QueryTypes.SELECT
379 }
380
381 return UserModel.sequelize.query(query, options)
382 .then(([ { total } ]) => {
383 if (total === null) return 0
384
385 return parseInt(total, 10)
386 })
387 }
391} 388}
diff --git a/server/models/video/video-channel.ts b/server/models/video/video-channel.ts
index e70e52515..475530daf 100644
--- a/server/models/video/video-channel.ts
+++ b/server/models/video/video-channel.ts
@@ -71,7 +71,7 @@ type AvailableForListOptions = {
71 const inQueryInstanceFollow = '(' + 71 const inQueryInstanceFollow = '(' +
72 'SELECT "actor"."serverId" FROM "actorFollow" ' + 72 'SELECT "actor"."serverId" FROM "actorFollow" ' +
73 'INNER JOIN "actor" ON actor.id= "actorFollow"."targetActorId" ' + 73 'INNER JOIN "actor" ON actor.id= "actorFollow"."targetActorId" ' +
74 'WHERE "actor"."id" = ' + actorIdNumber + 74 'WHERE "actorFollow"."actorId" = ' + actorIdNumber +
75 ')' 75 ')'
76 76
77 return { 77 return {