aboutsummaryrefslogtreecommitdiffhomepage
diff options
context:
space:
mode:
authorChocobozzz <me@florianbigard.com>2020-06-12 16:01:42 +0200
committerChocobozzz <me@florianbigard.com>2020-06-12 16:27:41 +0200
commit5a61ffbb7c72dd7ecfa16c7944dac45411c1bbe9 (patch)
tree9bc4788c0b54d7386d5533589c6cf52c347c0a48
parente74bda21d15300b4653392957e730c5e4eff2af3 (diff)
downloadPeerTube-5a61ffbb7c72dd7ecfa16c7944dac45411c1bbe9.tar.gz
PeerTube-5a61ffbb7c72dd7ecfa16c7944dac45411c1bbe9.tar.zst
PeerTube-5a61ffbb7c72dd7ecfa16c7944dac45411c1bbe9.zip
Optimize views per day in video channels
-rw-r--r--server/middlewares/validators/videos/video-channels.ts17
-rw-r--r--server/models/video/video-channel.ts26
-rw-r--r--server/tests/api/videos/video-channels.ts1
3 files changed, 22 insertions, 22 deletions
diff --git a/server/middlewares/validators/videos/video-channels.ts b/server/middlewares/validators/videos/video-channels.ts
index 882fb2b84..6604ae6a8 100644
--- a/server/middlewares/validators/videos/video-channels.ts
+++ b/server/middlewares/validators/videos/video-channels.ts
@@ -1,20 +1,20 @@
1import * as express from 'express' 1import * as express from 'express'
2import { body, param, query } from 'express-validator' 2import { body, param, query } from 'express-validator'
3import { VIDEO_CHANNELS } from '@server/initializers/constants'
4import { MChannelAccountDefault, MUser } from '@server/typings/models'
3import { UserRight } from '../../../../shared' 5import { UserRight } from '../../../../shared'
6import { isActorPreferredUsernameValid } from '../../../helpers/custom-validators/activitypub/actor'
7import { isBooleanValid, toBooleanOrNull } from '../../../helpers/custom-validators/misc'
4import { 8import {
5 isVideoChannelDescriptionValid, 9 isVideoChannelDescriptionValid,
6 isVideoChannelNameValid, 10 isVideoChannelNameValid,
7 isVideoChannelSupportValid 11 isVideoChannelSupportValid
8} from '../../../helpers/custom-validators/video-channels' 12} from '../../../helpers/custom-validators/video-channels'
9import { logger } from '../../../helpers/logger' 13import { logger } from '../../../helpers/logger'
14import { doesLocalVideoChannelNameExist, doesVideoChannelNameWithHostExist } from '../../../helpers/middlewares'
15import { ActorModel } from '../../../models/activitypub/actor'
10import { VideoChannelModel } from '../../../models/video/video-channel' 16import { VideoChannelModel } from '../../../models/video/video-channel'
11import { areValidationErrors } from '../utils' 17import { areValidationErrors } from '../utils'
12import { isActorPreferredUsernameValid } from '../../../helpers/custom-validators/activitypub/actor'
13import { ActorModel } from '../../../models/activitypub/actor'
14import { isBooleanValid } from '../../../helpers/custom-validators/misc'
15import { doesLocalVideoChannelNameExist, doesVideoChannelNameWithHostExist } from '../../../helpers/middlewares'
16import { MChannelAccountDefault, MUser } from '@server/typings/models'
17import { VIDEO_CHANNELS } from '@server/initializers/constants'
18 18
19const videoChannelsAddValidator = [ 19const videoChannelsAddValidator = [
20 body('name').custom(isActorPreferredUsernameValid).withMessage('Should have a valid channel name'), 20 body('name').custom(isActorPreferredUsernameValid).withMessage('Should have a valid channel name'),
@@ -129,7 +129,10 @@ const localVideoChannelValidator = [
129] 129]
130 130
131const videoChannelStatsValidator = [ 131const videoChannelStatsValidator = [
132 query('withStats').optional().isBoolean().withMessage('Should have a valid stats flag'), 132 query('withStats')
133 .optional()
134 .customSanitizer(toBooleanOrNull)
135 .custom(isBooleanValid).withMessage('Should have a valid stats flag'),
133 136
134 (req: express.Request, res: express.Response, next: express.NextFunction) => { 137 (req: express.Request, res: express.Response, next: express.NextFunction) => {
135 if (areValidationErrors(req, res)) return 138 if (areValidationErrors(req, res)) return
diff --git a/server/models/video/video-channel.ts b/server/models/video/video-channel.ts
index 642e129ff..b5bcbdc65 100644
--- a/server/models/video/video-channel.ts
+++ b/server/models/video/video-channel.ts
@@ -181,20 +181,16 @@ export type SummaryOptions = {
181 'days AS ( ' + 181 'days AS ( ' +
182 `SELECT generate_series(date_trunc('day', now()) - '${daysPrior} day'::interval, ` + 182 `SELECT generate_series(date_trunc('day', now()) - '${daysPrior} day'::interval, ` +
183 `date_trunc('day', now()), '1 day'::interval) AS day ` + 183 `date_trunc('day', now()), '1 day'::interval) AS day ` +
184 '), ' +
185 'views AS ( ' +
186 'SELECT v.* ' +
187 'FROM "videoView" AS v ' +
188 'INNER JOIN "video" ON "video"."id" = v."videoId" ' +
189 'WHERE "video"."channelId" = "VideoChannelModel"."id" ' +
190 ') ' + 184 ') ' +
191 'SELECT days.day AS day, ' + 185 'SELECT days.day AS day, COALESCE(SUM("videoView".views), 0) AS views ' +
192 'COALESCE(SUM(views.views), 0) AS views ' + 186 'FROM days ' +
193 'FROM days ' + 187 'LEFT JOIN (' +
194 `LEFT JOIN views ON date_trunc('day', "views"."startDate") = date_trunc('day', days.day) ` + 188 '"videoView" INNER JOIN "video" ON "videoView"."videoId" = "video"."id" ' +
195 'GROUP BY day ' + 189 'AND "video"."channelId" = "VideoChannelModel"."id"' +
196 'ORDER BY day ' + 190 `) ON date_trunc('day', "videoView"."startDate") = date_trunc('day', days.day) ` +
197 ') t' + 191 'GROUP BY day ' +
192 'ORDER BY day ' +
193 ') t' +
198 ')' 194 ')'
199 ), 195 ),
200 'viewsPerDay' 196 'viewsPerDay'
@@ -413,7 +409,7 @@ export class VideoChannelModel extends Model<VideoChannelModel> {
413 409
414 const scopes: string | ScopeOptions | (string | ScopeOptions)[] = [ ScopeNames.WITH_ACTOR ] 410 const scopes: string | ScopeOptions | (string | ScopeOptions)[] = [ ScopeNames.WITH_ACTOR ]
415 411
416 if (options.withStats) { 412 if (options.withStats === true) {
417 scopes.push({ 413 scopes.push({
418 method: [ ScopeNames.WITH_STATS, { daysPrior: 30 } as AvailableWithStatsOptions ] 414 method: [ ScopeNames.WITH_STATS, { daysPrior: 30 } as AvailableWithStatsOptions ]
419 }) 415 })
@@ -560,7 +556,7 @@ export class VideoChannelModel extends Model<VideoChannelModel> {
560 createdAt: this.createdAt, 556 createdAt: this.createdAt,
561 updatedAt: this.updatedAt, 557 updatedAt: this.updatedAt,
562 ownerAccount: undefined, 558 ownerAccount: undefined,
563 viewsPerDay: viewsPerDay !== undefined 559 viewsPerDay: viewsPerDay
564 ? viewsPerDay.split(',').map(v => { 560 ? viewsPerDay.split(',').map(v => {
565 const o = v.split('|') 561 const o = v.split('|')
566 return { 562 return {
diff --git a/server/tests/api/videos/video-channels.ts b/server/tests/api/videos/video-channels.ts
index 876a6ab66..89026aba7 100644
--- a/server/tests/api/videos/video-channels.ts
+++ b/server/tests/api/videos/video-channels.ts
@@ -366,6 +366,7 @@ describe('Test video channels', function () {
366 }) 366 })
367 367
368 it('Should report correct channel statistics', async function () { 368 it('Should report correct channel statistics', async function () {
369 this.timeout(10000)
369 370
370 { 371 {
371 const res = await getAccountVideoChannelsList({ 372 const res = await getAccountVideoChannelsList({