diff options
author | Chocobozzz <me@florianbigard.com> | 2018-08-28 18:29:29 +0200 |
---|---|---|
committer | Chocobozzz <me@florianbigard.com> | 2018-08-28 18:29:48 +0200 |
commit | 8b60488020883c66d3831eacd030893ab184268f (patch) | |
tree | 5b1a98b1796cd2709f5497e2459fe94867befb6b | |
parent | 41a676db3989fe3eca91301ac5f5aea30d98654a (diff) | |
download | PeerTube-8b60488020883c66d3831eacd030893ab184268f.tar.gz PeerTube-8b60488020883c66d3831eacd030893ab184268f.tar.zst PeerTube-8b60488020883c66d3831eacd030893ab184268f.zip |
Refractor user quota SQL queries
-rw-r--r-- | server/helpers/custom-validators/activitypub/misc.ts | 4 | ||||
-rw-r--r-- | server/helpers/custom-validators/videos.ts | 3 | ||||
-rw-r--r-- | server/models/account/user.ts | 73 | ||||
-rw-r--r-- | server/models/video/video-channel.ts | 2 |
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' | |||
6 | import { UserRight, VideoPrivacy, VideoRateType } from '../../../shared' | 6 | import { UserRight, VideoPrivacy, VideoRateType } from '../../../shared' |
7 | import { | 7 | import { |
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' | |||
19 | import { VideoChannelModel } from '../../models/video/video-channel' | 18 | import { VideoChannelModel } from '../../models/video/video-channel' |
20 | import { UserModel } from '../../models/account/user' | 19 | import { UserModel } from '../../models/account/user' |
21 | import * as magnetUtil from 'magnet-uri' | 20 | import * as magnetUtil from 'magnet-uri' |
22 | import { VideoAbuseModel } from '../../models/video/video-abuse' | ||
23 | 21 | ||
24 | const VIDEOS_CONSTRAINTS_FIELDS = CONSTRAINTS_FIELDS.VIDEOS | 22 | const VIDEOS_CONSTRAINTS_FIELDS = CONSTRAINTS_FIELDS.VIDEOS |
25 | const VIDEO_ABUSES_CONSTRAINTS_FIELDS = CONSTRAINTS_FIELDS.VIDEO_ABUSES | ||
26 | 23 | ||
27 | function isVideoCategoryValid (value: any) { | 24 | function 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 { |