From 901bcf5c188ea79350fecd499ad76460b866617b Mon Sep 17 00:00:00 2001 From: Chocobozzz Date: Thu, 7 Apr 2022 10:53:35 +0200 Subject: Add ability to set start/end date to timeserie --- server/controllers/api/videos/stats.ts | 17 +++- server/lib/timeserie.ts | 55 ++++++++++++ .../middlewares/validators/videos/video-stats.ts | 41 ++++++++- server/models/view/local-video-viewer.ts | 39 ++++++--- server/tests/api/check-params/views.ts | 48 +++++++++++ .../tests/api/views/video-views-timeserie-stats.ts | 97 +++++++++++++++++++++- 6 files changed, 277 insertions(+), 20 deletions(-) create mode 100644 server/lib/timeserie.ts (limited to 'server') diff --git a/server/controllers/api/videos/stats.ts b/server/controllers/api/videos/stats.ts index 5f8513e9e..71452d9f0 100644 --- a/server/controllers/api/videos/stats.ts +++ b/server/controllers/api/videos/stats.ts @@ -1,6 +1,6 @@ import express from 'express' import { LocalVideoViewerModel } from '@server/models/view/local-video-viewer' -import { VideoStatsTimeserieMetric } from '@shared/models' +import { VideoStatsTimeserieMetric, VideoStatsTimeserieQuery } from '@shared/models' import { asyncMiddleware, authenticate, @@ -57,10 +57,23 @@ async function getTimeserieStats (req: express.Request, res: express.Response) { const video = res.locals.videoAll const metric = req.params.metric as VideoStatsTimeserieMetric + const query = req.query as VideoStatsTimeserieQuery + const stats = await LocalVideoViewerModel.getTimeserieStats({ video, - metric + metric, + startDate: query.startDate ?? buildOneMonthAgo().toISOString(), + endDate: query.endDate ?? new Date().toISOString() }) return res.json(stats) } + +function buildOneMonthAgo () { + const monthAgo = new Date() + monthAgo.setHours(0, 0, 0, 0) + + monthAgo.setDate(monthAgo.getDate() - 29) + + return monthAgo +} diff --git a/server/lib/timeserie.ts b/server/lib/timeserie.ts new file mode 100644 index 000000000..d8f700a2f --- /dev/null +++ b/server/lib/timeserie.ts @@ -0,0 +1,55 @@ +import { logger } from '@server/helpers/logger' +import { VideoStatsTimeserieGroupInterval } from '@shared/models' + +function buildGroupByAndBoundaries (startDateString: string, endDateString: string) { + const startDate = new Date(startDateString) + const endDate = new Date(endDateString) + + const groupByMatrix: { [ id in VideoStatsTimeserieGroupInterval ]: string } = { + one_day: '1 day', + one_hour: '1 hour', + ten_minutes: '10 minutes', + one_minute: '1 minute' + } + const groupInterval = buildGroupInterval(startDate, endDate) + + logger.debug('Found "%s" group interval.', groupInterval, { startDate, endDate }) + + // Remove parts of the date we don't need + if (groupInterval === 'one_day') { + startDate.setHours(0, 0, 0, 0) + } else if (groupInterval === 'one_hour') { + startDate.setMinutes(0, 0, 0) + } else { + startDate.setSeconds(0, 0) + } + + return { + groupInterval, + sqlInterval: groupByMatrix[groupInterval], + startDate, + endDate + } +} + +// --------------------------------------------------------------------------- + +export { + buildGroupByAndBoundaries +} + +// --------------------------------------------------------------------------- + +function buildGroupInterval (startDate: Date, endDate: Date): VideoStatsTimeserieGroupInterval { + const aDay = 86400 + const anHour = 3600 + const aMinute = 60 + + const diffSeconds = (endDate.getTime() - startDate.getTime()) / 1000 + + if (diffSeconds >= 6 * aDay) return 'one_day' + if (diffSeconds >= 6 * anHour) return 'one_hour' + if (diffSeconds >= 60 * aMinute) return 'ten_minutes' + + return 'one_minute' +} diff --git a/server/middlewares/validators/videos/video-stats.ts b/server/middlewares/validators/videos/video-stats.ts index 358b6b473..12509abde 100644 --- a/server/middlewares/validators/videos/video-stats.ts +++ b/server/middlewares/validators/videos/video-stats.ts @@ -1,7 +1,9 @@ import express from 'express' -import { param } from 'express-validator' +import { param, query } from 'express-validator' +import { isDateValid } from '@server/helpers/custom-validators/misc' import { isValidStatTimeserieMetric } from '@server/helpers/custom-validators/video-stats' -import { HttpStatusCode, UserRight } from '@shared/models' +import { STATS_TIMESERIE } from '@server/initializers/constants' +import { HttpStatusCode, UserRight, VideoStatsTimeserieQuery } from '@shared/models' import { logger } from '../../../helpers/logger' import { areValidationErrors, checkUserCanManageVideo, doesVideoExist, isValidVideoIdParam } from '../shared' @@ -45,12 +47,40 @@ const videoTimeserieStatsValidator = [ .custom(isValidStatTimeserieMetric) .withMessage('Should have a valid timeserie metric'), + query('startDate') + .optional() + .custom(isDateValid) + .withMessage('Should have a valid start date'), + + query('endDate') + .optional() + .custom(isDateValid) + .withMessage('Should have a valid end date'), + async (req: express.Request, res: express.Response, next: express.NextFunction) => { logger.debug('Checking videoTimeserieStatsValidator parameters', { parameters: req.body }) if (areValidationErrors(req, res)) return if (!await commonStatsCheck(req, res)) return + const query: VideoStatsTimeserieQuery = req.query + if ( + (query.startDate && !query.endDate) || + (!query.startDate && query.endDate) + ) { + return res.fail({ + status: HttpStatusCode.BAD_REQUEST_400, + message: 'Both start date and end date should be defined if one of them is specified' + }) + } + + if (query.startDate && getIntervalByDays(query.startDate, query.endDate) > STATS_TIMESERIE.MAX_DAYS) { + return res.fail({ + status: HttpStatusCode.BAD_REQUEST_400, + message: 'Star date and end date interval is too big' + }) + } + return next() } ] @@ -71,3 +101,10 @@ async function commonStatsCheck (req: express.Request, res: express.Response) { return true } + +function getIntervalByDays (startDateString: string, endDateString: string) { + const startDate = new Date(startDateString) + const endDate = new Date(endDateString) + + return (endDate.getTime() - startDate.getTime()) / 1000 / 86400 +} diff --git a/server/models/view/local-video-viewer.ts b/server/models/view/local-video-viewer.ts index 1491acb9e..ad2ad35ca 100644 --- a/server/models/view/local-video-viewer.ts +++ b/server/models/view/local-video-viewer.ts @@ -1,7 +1,7 @@ import { QueryTypes } from 'sequelize' import { AllowNull, BelongsTo, Column, CreatedAt, DataType, Default, ForeignKey, HasMany, IsUUID, Model, Table } from 'sequelize-typescript' -import { STATS_TIMESERIE } from '@server/initializers/constants' import { getActivityStreamDuration } from '@server/lib/activitypub/activity' +import { buildGroupByAndBoundaries } from '@server/lib/timeserie' import { MLocalVideoViewer, MLocalVideoViewerWithWatchSections, MVideo } from '@server/types/models' import { VideoStatsOverall, VideoStatsRetention, VideoStatsTimeserie, VideoStatsTimeserieMetric, WatchActionObject } from '@shared/models' import { AttributesOnly } from '@shared/typescript-utils' @@ -216,33 +216,48 @@ export class LocalVideoViewerModel extends Model { const { video, metric } = options + const { groupInterval, sqlInterval, startDate, endDate } = buildGroupByAndBoundaries(options.startDate, options.endDate) + const selectMetrics: { [ id in VideoStatsTimeserieMetric ]: string } = { viewers: 'COUNT("localVideoViewer"."id")', aggregateWatchTime: 'SUM("localVideoViewer"."watchTime")' } - const query = `WITH days AS ( ` + - `SELECT (current_date::timestamp - (serie || ' days')::interval)::timestamptz AS day - FROM generate_series(0, ${STATS_TIMESERIE.MAX_DAYS - 1}) serie` + - `) ` + - `SELECT days.day AS date, COALESCE(${selectMetrics[metric]}, 0) AS value ` + - `FROM days ` + - `LEFT JOIN "localVideoViewer" ON "localVideoViewer"."videoId" = :videoId ` + - `AND date_trunc('day', "localVideoViewer"."startDate") = date_trunc('day', days.day) ` + - `GROUP BY day ` + - `ORDER BY day ` + const query = `WITH "intervals" AS ( + SELECT + "time" AS "startDate", "time" + :sqlInterval::interval as "endDate" + FROM + generate_series(:startDate::timestamptz, :endDate::timestamptz, :sqlInterval::interval) serie("time") + ) + SELECT "intervals"."startDate" as "date", COALESCE(${selectMetrics[metric]}, 0) AS value + FROM + intervals + LEFT JOIN "localVideoViewer" ON "localVideoViewer"."videoId" = :videoId + AND "localVideoViewer"."startDate" >= "intervals"."startDate" AND "localVideoViewer"."startDate" <= "intervals"."endDate" + GROUP BY + "intervals"."startDate" + ORDER BY + "intervals"."startDate"` const queryOptions = { type: QueryTypes.SELECT as QueryTypes.SELECT, - replacements: { videoId: video.id } + replacements: { + startDate, + endDate, + sqlInterval, + videoId: video.id + } } const rows = await LocalVideoViewerModel.sequelize.query(query, queryOptions) return { + groupInterval, data: rows.map(r => ({ date: r.date, value: parseInt(r.value) diff --git a/server/tests/api/check-params/views.ts b/server/tests/api/check-params/views.ts index ca4752345..3dba2a42e 100644 --- a/server/tests/api/check-params/views.ts +++ b/server/tests/api/check-params/views.ts @@ -112,6 +112,54 @@ describe('Test videos views', function () { await servers[0].videoStats.getTimeserieStats({ videoId, metric: 'hello' as any, expectedStatus: HttpStatusCode.BAD_REQUEST_400 }) }) + it('Should fail with an invalid start date', async function () { + await servers[0].videoStats.getTimeserieStats({ + videoId, + metric: 'viewers', + startDate: 'fake' as any, + endDate: new Date(), + expectedStatus: HttpStatusCode.BAD_REQUEST_400 + }) + }) + + it('Should fail with an invalid end date', async function () { + await servers[0].videoStats.getTimeserieStats({ + videoId, + metric: 'viewers', + startDate: new Date(), + endDate: 'fake' as any, + expectedStatus: HttpStatusCode.BAD_REQUEST_400 + }) + }) + + it('Should fail if start date is specified but not end date', async function () { + await servers[0].videoStats.getTimeserieStats({ + videoId, + metric: 'viewers', + startDate: new Date(), + expectedStatus: HttpStatusCode.BAD_REQUEST_400 + }) + }) + + it('Should fail if end date is specified but not start date', async function () { + await servers[0].videoStats.getTimeserieStats({ + videoId, + metric: 'viewers', + endDate: new Date(), + expectedStatus: HttpStatusCode.BAD_REQUEST_400 + }) + }) + + it('Should fail with a too big interval', async function () { + await servers[0].videoStats.getTimeserieStats({ + videoId, + metric: 'viewers', + startDate: new Date('2021-04-07T08:31:57.126Z'), + endDate: new Date(), + expectedStatus: HttpStatusCode.BAD_REQUEST_400 + }) + }) + it('Should succeed with the correct parameters', async function () { await servers[0].videoStats.getTimeserieStats({ videoId, metric: 'viewers' }) }) diff --git a/server/tests/api/views/video-views-timeserie-stats.ts b/server/tests/api/views/video-views-timeserie-stats.ts index 858edeff7..4db76fe89 100644 --- a/server/tests/api/views/video-views-timeserie-stats.ts +++ b/server/tests/api/views/video-views-timeserie-stats.ts @@ -47,21 +47,31 @@ describe('Test views timeserie stats', function () { let liveVideoId: string let command: FfmpegCommand - function expectTimeserieData (result: VideoStatsTimeserie, lastValue: number) { + function expectTodayLastValue (result: VideoStatsTimeserie, lastValue: number) { const { data } = result - expect(data).to.have.lengthOf(30) const last = data[data.length - 1] - const today = new Date().getDate() expect(new Date(last.date).getDate()).to.equal(today) - expect(last.value).to.equal(lastValue) + } + + function expectTimeserieData (result: VideoStatsTimeserie, lastValue: number) { + const { data } = result + expect(data).to.have.lengthOf(30) + + expectTodayLastValue(result, lastValue) for (let i = 0; i < data.length - 2; i++) { expect(data[i].value).to.equal(0) } } + function expectInterval (result: VideoStatsTimeserie, intervalMs: number) { + const first = result.data[0] + const second = result.data[1] + expect(new Date(second.date).getTime() - new Date(first.date).getTime()).to.equal(intervalMs) + } + before(async function () { this.timeout(120000); @@ -98,6 +108,85 @@ describe('Test views timeserie stats', function () { } }) + it('Should use a custom start/end date', async function () { + const now = new Date() + const tenDaysAgo = new Date() + tenDaysAgo.setDate(tenDaysAgo.getDate() - 9) + + const result = await servers[0].videoStats.getTimeserieStats({ + videoId: vodVideoId, + metric: 'aggregateWatchTime', + startDate: tenDaysAgo, + endDate: now + }) + + expect(result.groupInterval).to.equal('one_day') + expect(result.data).to.have.lengthOf(10) + + const first = result.data[0] + expect(new Date(first.date).toLocaleDateString()).to.equal(tenDaysAgo.toLocaleDateString()) + + expectInterval(result, 24 * 3600 * 1000) + expectTodayLastValue(result, 9) + }) + + it('Should automatically group by hours', async function () { + const now = new Date() + const twoDaysAgo = new Date() + twoDaysAgo.setDate(twoDaysAgo.getDate() - 1) + + const result = await servers[0].videoStats.getTimeserieStats({ + videoId: vodVideoId, + metric: 'aggregateWatchTime', + startDate: twoDaysAgo, + endDate: now + }) + + expect(result.groupInterval).to.equal('one_hour') + expect(result.data).to.have.length.above(24).and.below(50) + + expectInterval(result, 3600 * 1000) + expectTodayLastValue(result, 9) + }) + + it('Should automatically group by ten minutes', async function () { + const now = new Date() + const twoHoursAgo = new Date() + twoHoursAgo.setHours(twoHoursAgo.getHours() - 1) + + const result = await servers[0].videoStats.getTimeserieStats({ + videoId: vodVideoId, + metric: 'aggregateWatchTime', + startDate: twoHoursAgo, + endDate: now + }) + + expect(result.groupInterval).to.equal('ten_minutes') + expect(result.data).to.have.length.above(6).and.below(18) + + expectInterval(result, 60 * 10 * 1000) + expectTodayLastValue(result, 9) + }) + + it('Should automatically group by one minute', async function () { + const now = new Date() + const thirtyAgo = new Date() + thirtyAgo.setMinutes(thirtyAgo.getMinutes() - 30) + + const result = await servers[0].videoStats.getTimeserieStats({ + videoId: vodVideoId, + metric: 'aggregateWatchTime', + startDate: thirtyAgo, + endDate: now + }) + + expect(result.groupInterval).to.equal('one_minute') + expect(result.data).to.have.length.above(20).and.below(40) + + expectInterval(result, 60 * 1000) + expectTodayLastValue(result, 9) + }) + after(async function () { await stopFfmpeg(command) }) -- cgit v1.2.3