aboutsummaryrefslogtreecommitdiffhomepage
path: root/server
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
parent41a676db3989fe3eca91301ac5f5aea30d98654a (diff)
downloadPeerTube-8b60488020883c66d3831eacd030893ab184268f.tar.gz
PeerTube-8b60488020883c66d3831eacd030893ab184268f.tar.zst
PeerTube-8b60488020883c66d3831eacd030893ab184268f.zip
Refractor user quota SQL queries
Diffstat (limited to 'server')
-rw-r--r--server/helpers/custom-validators/activitypub/misc.ts4
-rw-r--r--server/helpers/custom-validators/videos.ts3
-rw-r--r--server/models/account/user.ts73
-rw-r--r--server/models/video/video-channel.ts2
4 files changed, 37 insertions, 45 deletions
diff --git a/server/helpers/custom-validators/activitypub/misc.ts b/server/helpers/custom-validators/activitypub/misc.ts
index 2dac8e1ad..6c5c7abca 100644
--- a/server/helpers/custom-validators/activitypub/misc.ts
+++ b/server/helpers/custom-validators/activitypub/misc.ts
@@ -41,12 +41,10 @@ function setValidAttributedTo (obj: any) {
41 return true 41 return true
42 } 42 }
43 43
44 const newAttributesTo = obj.attributedTo.filter(a => { 44 obj.attributedTo = obj.attributedTo.filter(a => {
45 return (a.type === 'Group' || a.type === 'Person') && isActivityPubUrlValid(a.id) 45 return (a.type === 'Group' || a.type === 'Person') && isActivityPubUrlValid(a.id)
46 }) 46 })
47 47
48 obj.attributedTo = newAttributesTo
49
50 return true 48 return true
51} 49}
52 50
diff --git a/server/helpers/custom-validators/videos.ts b/server/helpers/custom-validators/videos.ts
index 5e6cfe217..4b1f6c069 100644
--- a/server/helpers/custom-validators/videos.ts
+++ b/server/helpers/custom-validators/videos.ts
@@ -6,7 +6,6 @@ import * as validator from 'validator'
6import { UserRight, VideoPrivacy, VideoRateType } from '../../../shared' 6import { UserRight, VideoPrivacy, VideoRateType } from '../../../shared'
7import { 7import {
8 CONSTRAINTS_FIELDS, 8 CONSTRAINTS_FIELDS,
9 VIDEO_ABUSE_STATES,
10 VIDEO_CATEGORIES, 9 VIDEO_CATEGORIES,
11 VIDEO_LICENCES, 10 VIDEO_LICENCES,
12 VIDEO_MIMETYPE_EXT, 11 VIDEO_MIMETYPE_EXT,
@@ -19,10 +18,8 @@ import { exists, isArray, isFileValid } from './misc'
19import { VideoChannelModel } from '../../models/video/video-channel' 18import { VideoChannelModel } from '../../models/video/video-channel'
20import { UserModel } from '../../models/account/user' 19import { UserModel } from '../../models/account/user'
21import * as magnetUtil from 'magnet-uri' 20import * as magnetUtil from 'magnet-uri'
22import { VideoAbuseModel } from '../../models/video/video-abuse'
23 21
24const VIDEOS_CONSTRAINTS_FIELDS = CONSTRAINTS_FIELDS.VIDEOS 22const VIDEOS_CONSTRAINTS_FIELDS = CONSTRAINTS_FIELDS.VIDEOS
25const VIDEO_ABUSES_CONSTRAINTS_FIELDS = CONSTRAINTS_FIELDS.VIDEO_ABUSES
26 23
27function isVideoCategoryValid (value: any) { 24function isVideoCategoryValid (value: any) {
28 return value === null || VIDEO_CATEGORIES[ value ] !== undefined 25 return value === null || VIDEO_CATEGORIES[ value ] !== undefined
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 {