aboutsummaryrefslogtreecommitdiffhomepage
path: root/server
diff options
context:
space:
mode:
authorChocobozzz <me@florianbigard.com>2022-04-07 10:53:35 +0200
committerChocobozzz <chocobozzz@cpy.re>2022-04-15 09:49:35 +0200
commit901bcf5c188ea79350fecd499ad76460b866617b (patch)
tree1e79f26cc3f2b952371d31bfa9b94a2b150be38a /server
parentac907dc7c158056e9b6a5cb58acd27df5c7c2670 (diff)
downloadPeerTube-901bcf5c188ea79350fecd499ad76460b866617b.tar.gz
PeerTube-901bcf5c188ea79350fecd499ad76460b866617b.tar.zst
PeerTube-901bcf5c188ea79350fecd499ad76460b866617b.zip
Add ability to set start/end date to timeserie
Diffstat (limited to 'server')
-rw-r--r--server/controllers/api/videos/stats.ts17
-rw-r--r--server/lib/timeserie.ts55
-rw-r--r--server/middlewares/validators/videos/video-stats.ts41
-rw-r--r--server/models/view/local-video-viewer.ts39
-rw-r--r--server/tests/api/check-params/views.ts48
-rw-r--r--server/tests/api/views/video-views-timeserie-stats.ts97
6 files changed, 277 insertions, 20 deletions
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 @@
1import express from 'express' 1import express from 'express'
2import { LocalVideoViewerModel } from '@server/models/view/local-video-viewer' 2import { LocalVideoViewerModel } from '@server/models/view/local-video-viewer'
3import { VideoStatsTimeserieMetric } from '@shared/models' 3import { VideoStatsTimeserieMetric, VideoStatsTimeserieQuery } from '@shared/models'
4import { 4import {
5 asyncMiddleware, 5 asyncMiddleware,
6 authenticate, 6 authenticate,
@@ -57,10 +57,23 @@ async function getTimeserieStats (req: express.Request, res: express.Response) {
57 const video = res.locals.videoAll 57 const video = res.locals.videoAll
58 const metric = req.params.metric as VideoStatsTimeserieMetric 58 const metric = req.params.metric as VideoStatsTimeserieMetric
59 59
60 const query = req.query as VideoStatsTimeserieQuery
61
60 const stats = await LocalVideoViewerModel.getTimeserieStats({ 62 const stats = await LocalVideoViewerModel.getTimeserieStats({
61 video, 63 video,
62 metric 64 metric,
65 startDate: query.startDate ?? buildOneMonthAgo().toISOString(),
66 endDate: query.endDate ?? new Date().toISOString()
63 }) 67 })
64 68
65 return res.json(stats) 69 return res.json(stats)
66} 70}
71
72function buildOneMonthAgo () {
73 const monthAgo = new Date()
74 monthAgo.setHours(0, 0, 0, 0)
75
76 monthAgo.setDate(monthAgo.getDate() - 29)
77
78 return monthAgo
79}
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 @@
1import { logger } from '@server/helpers/logger'
2import { VideoStatsTimeserieGroupInterval } from '@shared/models'
3
4function buildGroupByAndBoundaries (startDateString: string, endDateString: string) {
5 const startDate = new Date(startDateString)
6 const endDate = new Date(endDateString)
7
8 const groupByMatrix: { [ id in VideoStatsTimeserieGroupInterval ]: string } = {
9 one_day: '1 day',
10 one_hour: '1 hour',
11 ten_minutes: '10 minutes',
12 one_minute: '1 minute'
13 }
14 const groupInterval = buildGroupInterval(startDate, endDate)
15
16 logger.debug('Found "%s" group interval.', groupInterval, { startDate, endDate })
17
18 // Remove parts of the date we don't need
19 if (groupInterval === 'one_day') {
20 startDate.setHours(0, 0, 0, 0)
21 } else if (groupInterval === 'one_hour') {
22 startDate.setMinutes(0, 0, 0)
23 } else {
24 startDate.setSeconds(0, 0)
25 }
26
27 return {
28 groupInterval,
29 sqlInterval: groupByMatrix[groupInterval],
30 startDate,
31 endDate
32 }
33}
34
35// ---------------------------------------------------------------------------
36
37export {
38 buildGroupByAndBoundaries
39}
40
41// ---------------------------------------------------------------------------
42
43function buildGroupInterval (startDate: Date, endDate: Date): VideoStatsTimeserieGroupInterval {
44 const aDay = 86400
45 const anHour = 3600
46 const aMinute = 60
47
48 const diffSeconds = (endDate.getTime() - startDate.getTime()) / 1000
49
50 if (diffSeconds >= 6 * aDay) return 'one_day'
51 if (diffSeconds >= 6 * anHour) return 'one_hour'
52 if (diffSeconds >= 60 * aMinute) return 'ten_minutes'
53
54 return 'one_minute'
55}
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 @@
1import express from 'express' 1import express from 'express'
2import { param } from 'express-validator' 2import { param, query } from 'express-validator'
3import { isDateValid } from '@server/helpers/custom-validators/misc'
3import { isValidStatTimeserieMetric } from '@server/helpers/custom-validators/video-stats' 4import { isValidStatTimeserieMetric } from '@server/helpers/custom-validators/video-stats'
4import { HttpStatusCode, UserRight } from '@shared/models' 5import { STATS_TIMESERIE } from '@server/initializers/constants'
6import { HttpStatusCode, UserRight, VideoStatsTimeserieQuery } from '@shared/models'
5import { logger } from '../../../helpers/logger' 7import { logger } from '../../../helpers/logger'
6import { areValidationErrors, checkUserCanManageVideo, doesVideoExist, isValidVideoIdParam } from '../shared' 8import { areValidationErrors, checkUserCanManageVideo, doesVideoExist, isValidVideoIdParam } from '../shared'
7 9
@@ -45,12 +47,40 @@ const videoTimeserieStatsValidator = [
45 .custom(isValidStatTimeserieMetric) 47 .custom(isValidStatTimeserieMetric)
46 .withMessage('Should have a valid timeserie metric'), 48 .withMessage('Should have a valid timeserie metric'),
47 49
50 query('startDate')
51 .optional()
52 .custom(isDateValid)
53 .withMessage('Should have a valid start date'),
54
55 query('endDate')
56 .optional()
57 .custom(isDateValid)
58 .withMessage('Should have a valid end date'),
59
48 async (req: express.Request, res: express.Response, next: express.NextFunction) => { 60 async (req: express.Request, res: express.Response, next: express.NextFunction) => {
49 logger.debug('Checking videoTimeserieStatsValidator parameters', { parameters: req.body }) 61 logger.debug('Checking videoTimeserieStatsValidator parameters', { parameters: req.body })
50 62
51 if (areValidationErrors(req, res)) return 63 if (areValidationErrors(req, res)) return
52 if (!await commonStatsCheck(req, res)) return 64 if (!await commonStatsCheck(req, res)) return
53 65
66 const query: VideoStatsTimeserieQuery = req.query
67 if (
68 (query.startDate && !query.endDate) ||
69 (!query.startDate && query.endDate)
70 ) {
71 return res.fail({
72 status: HttpStatusCode.BAD_REQUEST_400,
73 message: 'Both start date and end date should be defined if one of them is specified'
74 })
75 }
76
77 if (query.startDate && getIntervalByDays(query.startDate, query.endDate) > STATS_TIMESERIE.MAX_DAYS) {
78 return res.fail({
79 status: HttpStatusCode.BAD_REQUEST_400,
80 message: 'Star date and end date interval is too big'
81 })
82 }
83
54 return next() 84 return next()
55 } 85 }
56] 86]
@@ -71,3 +101,10 @@ async function commonStatsCheck (req: express.Request, res: express.Response) {
71 101
72 return true 102 return true
73} 103}
104
105function getIntervalByDays (startDateString: string, endDateString: string) {
106 const startDate = new Date(startDateString)
107 const endDate = new Date(endDateString)
108
109 return (endDate.getTime() - startDate.getTime()) / 1000 / 86400
110}
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 @@
1import { QueryTypes } from 'sequelize' 1import { QueryTypes } from 'sequelize'
2import { AllowNull, BelongsTo, Column, CreatedAt, DataType, Default, ForeignKey, HasMany, IsUUID, Model, Table } from 'sequelize-typescript' 2import { AllowNull, BelongsTo, Column, CreatedAt, DataType, Default, ForeignKey, HasMany, IsUUID, Model, Table } from 'sequelize-typescript'
3import { STATS_TIMESERIE } from '@server/initializers/constants'
4import { getActivityStreamDuration } from '@server/lib/activitypub/activity' 3import { getActivityStreamDuration } from '@server/lib/activitypub/activity'
4import { buildGroupByAndBoundaries } from '@server/lib/timeserie'
5import { MLocalVideoViewer, MLocalVideoViewerWithWatchSections, MVideo } from '@server/types/models' 5import { MLocalVideoViewer, MLocalVideoViewerWithWatchSections, MVideo } from '@server/types/models'
6import { VideoStatsOverall, VideoStatsRetention, VideoStatsTimeserie, VideoStatsTimeserieMetric, WatchActionObject } from '@shared/models' 6import { VideoStatsOverall, VideoStatsRetention, VideoStatsTimeserie, VideoStatsTimeserieMetric, WatchActionObject } from '@shared/models'
7import { AttributesOnly } from '@shared/typescript-utils' 7import { AttributesOnly } from '@shared/typescript-utils'
@@ -216,33 +216,48 @@ export class LocalVideoViewerModel extends Model<Partial<AttributesOnly<LocalVid
216 static async getTimeserieStats (options: { 216 static async getTimeserieStats (options: {
217 video: MVideo 217 video: MVideo
218 metric: VideoStatsTimeserieMetric 218 metric: VideoStatsTimeserieMetric
219 startDate: string
220 endDate: string
219 }): Promise<VideoStatsTimeserie> { 221 }): Promise<VideoStatsTimeserie> {
220 const { video, metric } = options 222 const { video, metric } = options
221 223
224 const { groupInterval, sqlInterval, startDate, endDate } = buildGroupByAndBoundaries(options.startDate, options.endDate)
225
222 const selectMetrics: { [ id in VideoStatsTimeserieMetric ]: string } = { 226 const selectMetrics: { [ id in VideoStatsTimeserieMetric ]: string } = {
223 viewers: 'COUNT("localVideoViewer"."id")', 227 viewers: 'COUNT("localVideoViewer"."id")',
224 aggregateWatchTime: 'SUM("localVideoViewer"."watchTime")' 228 aggregateWatchTime: 'SUM("localVideoViewer"."watchTime")'
225 } 229 }
226 230
227 const query = `WITH days AS ( ` + 231 const query = `WITH "intervals" AS (
228 `SELECT (current_date::timestamp - (serie || ' days')::interval)::timestamptz AS day 232 SELECT
229 FROM generate_series(0, ${STATS_TIMESERIE.MAX_DAYS - 1}) serie` + 233 "time" AS "startDate", "time" + :sqlInterval::interval as "endDate"
230 `) ` + 234 FROM
231 `SELECT days.day AS date, COALESCE(${selectMetrics[metric]}, 0) AS value ` + 235 generate_series(:startDate::timestamptz, :endDate::timestamptz, :sqlInterval::interval) serie("time")
232 `FROM days ` + 236 )
233 `LEFT JOIN "localVideoViewer" ON "localVideoViewer"."videoId" = :videoId ` + 237 SELECT "intervals"."startDate" as "date", COALESCE(${selectMetrics[metric]}, 0) AS value
234 `AND date_trunc('day', "localVideoViewer"."startDate") = date_trunc('day', days.day) ` + 238 FROM
235 `GROUP BY day ` + 239 intervals
236 `ORDER BY day ` 240 LEFT JOIN "localVideoViewer" ON "localVideoViewer"."videoId" = :videoId
241 AND "localVideoViewer"."startDate" >= "intervals"."startDate" AND "localVideoViewer"."startDate" <= "intervals"."endDate"
242 GROUP BY
243 "intervals"."startDate"
244 ORDER BY
245 "intervals"."startDate"`
237 246
238 const queryOptions = { 247 const queryOptions = {
239 type: QueryTypes.SELECT as QueryTypes.SELECT, 248 type: QueryTypes.SELECT as QueryTypes.SELECT,
240 replacements: { videoId: video.id } 249 replacements: {
250 startDate,
251 endDate,
252 sqlInterval,
253 videoId: video.id
254 }
241 } 255 }
242 256
243 const rows = await LocalVideoViewerModel.sequelize.query<any>(query, queryOptions) 257 const rows = await LocalVideoViewerModel.sequelize.query<any>(query, queryOptions)
244 258
245 return { 259 return {
260 groupInterval,
246 data: rows.map(r => ({ 261 data: rows.map(r => ({
247 date: r.date, 262 date: r.date,
248 value: parseInt(r.value) 263 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 () {
112 await servers[0].videoStats.getTimeserieStats({ videoId, metric: 'hello' as any, expectedStatus: HttpStatusCode.BAD_REQUEST_400 }) 112 await servers[0].videoStats.getTimeserieStats({ videoId, metric: 'hello' as any, expectedStatus: HttpStatusCode.BAD_REQUEST_400 })
113 }) 113 })
114 114
115 it('Should fail with an invalid start date', async function () {
116 await servers[0].videoStats.getTimeserieStats({
117 videoId,
118 metric: 'viewers',
119 startDate: 'fake' as any,
120 endDate: new Date(),
121 expectedStatus: HttpStatusCode.BAD_REQUEST_400
122 })
123 })
124
125 it('Should fail with an invalid end date', async function () {
126 await servers[0].videoStats.getTimeserieStats({
127 videoId,
128 metric: 'viewers',
129 startDate: new Date(),
130 endDate: 'fake' as any,
131 expectedStatus: HttpStatusCode.BAD_REQUEST_400
132 })
133 })
134
135 it('Should fail if start date is specified but not end date', async function () {
136 await servers[0].videoStats.getTimeserieStats({
137 videoId,
138 metric: 'viewers',
139 startDate: new Date(),
140 expectedStatus: HttpStatusCode.BAD_REQUEST_400
141 })
142 })
143
144 it('Should fail if end date is specified but not start date', async function () {
145 await servers[0].videoStats.getTimeserieStats({
146 videoId,
147 metric: 'viewers',
148 endDate: new Date(),
149 expectedStatus: HttpStatusCode.BAD_REQUEST_400
150 })
151 })
152
153 it('Should fail with a too big interval', async function () {
154 await servers[0].videoStats.getTimeserieStats({
155 videoId,
156 metric: 'viewers',
157 startDate: new Date('2021-04-07T08:31:57.126Z'),
158 endDate: new Date(),
159 expectedStatus: HttpStatusCode.BAD_REQUEST_400
160 })
161 })
162
115 it('Should succeed with the correct parameters', async function () { 163 it('Should succeed with the correct parameters', async function () {
116 await servers[0].videoStats.getTimeserieStats({ videoId, metric: 'viewers' }) 164 await servers[0].videoStats.getTimeserieStats({ videoId, metric: 'viewers' })
117 }) 165 })
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 () {
47 let liveVideoId: string 47 let liveVideoId: string
48 let command: FfmpegCommand 48 let command: FfmpegCommand
49 49
50 function expectTimeserieData (result: VideoStatsTimeserie, lastValue: number) { 50 function expectTodayLastValue (result: VideoStatsTimeserie, lastValue: number) {
51 const { data } = result 51 const { data } = result
52 expect(data).to.have.lengthOf(30)
53 52
54 const last = data[data.length - 1] 53 const last = data[data.length - 1]
55
56 const today = new Date().getDate() 54 const today = new Date().getDate()
57 expect(new Date(last.date).getDate()).to.equal(today) 55 expect(new Date(last.date).getDate()).to.equal(today)
58 expect(last.value).to.equal(lastValue) 56 }
57
58 function expectTimeserieData (result: VideoStatsTimeserie, lastValue: number) {
59 const { data } = result
60 expect(data).to.have.lengthOf(30)
61
62 expectTodayLastValue(result, lastValue)
59 63
60 for (let i = 0; i < data.length - 2; i++) { 64 for (let i = 0; i < data.length - 2; i++) {
61 expect(data[i].value).to.equal(0) 65 expect(data[i].value).to.equal(0)
62 } 66 }
63 } 67 }
64 68
69 function expectInterval (result: VideoStatsTimeserie, intervalMs: number) {
70 const first = result.data[0]
71 const second = result.data[1]
72 expect(new Date(second.date).getTime() - new Date(first.date).getTime()).to.equal(intervalMs)
73 }
74
65 before(async function () { 75 before(async function () {
66 this.timeout(120000); 76 this.timeout(120000);
67 77
@@ -98,6 +108,85 @@ describe('Test views timeserie stats', function () {
98 } 108 }
99 }) 109 })
100 110
111 it('Should use a custom start/end date', async function () {
112 const now = new Date()
113 const tenDaysAgo = new Date()
114 tenDaysAgo.setDate(tenDaysAgo.getDate() - 9)
115
116 const result = await servers[0].videoStats.getTimeserieStats({
117 videoId: vodVideoId,
118 metric: 'aggregateWatchTime',
119 startDate: tenDaysAgo,
120 endDate: now
121 })
122
123 expect(result.groupInterval).to.equal('one_day')
124 expect(result.data).to.have.lengthOf(10)
125
126 const first = result.data[0]
127 expect(new Date(first.date).toLocaleDateString()).to.equal(tenDaysAgo.toLocaleDateString())
128
129 expectInterval(result, 24 * 3600 * 1000)
130 expectTodayLastValue(result, 9)
131 })
132
133 it('Should automatically group by hours', async function () {
134 const now = new Date()
135 const twoDaysAgo = new Date()
136 twoDaysAgo.setDate(twoDaysAgo.getDate() - 1)
137
138 const result = await servers[0].videoStats.getTimeserieStats({
139 videoId: vodVideoId,
140 metric: 'aggregateWatchTime',
141 startDate: twoDaysAgo,
142 endDate: now
143 })
144
145 expect(result.groupInterval).to.equal('one_hour')
146 expect(result.data).to.have.length.above(24).and.below(50)
147
148 expectInterval(result, 3600 * 1000)
149 expectTodayLastValue(result, 9)
150 })
151
152 it('Should automatically group by ten minutes', async function () {
153 const now = new Date()
154 const twoHoursAgo = new Date()
155 twoHoursAgo.setHours(twoHoursAgo.getHours() - 1)
156
157 const result = await servers[0].videoStats.getTimeserieStats({
158 videoId: vodVideoId,
159 metric: 'aggregateWatchTime',
160 startDate: twoHoursAgo,
161 endDate: now
162 })
163
164 expect(result.groupInterval).to.equal('ten_minutes')
165 expect(result.data).to.have.length.above(6).and.below(18)
166
167 expectInterval(result, 60 * 10 * 1000)
168 expectTodayLastValue(result, 9)
169 })
170
171 it('Should automatically group by one minute', async function () {
172 const now = new Date()
173 const thirtyAgo = new Date()
174 thirtyAgo.setMinutes(thirtyAgo.getMinutes() - 30)
175
176 const result = await servers[0].videoStats.getTimeserieStats({
177 videoId: vodVideoId,
178 metric: 'aggregateWatchTime',
179 startDate: thirtyAgo,
180 endDate: now
181 })
182
183 expect(result.groupInterval).to.equal('one_minute')
184 expect(result.data).to.have.length.above(20).and.below(40)
185
186 expectInterval(result, 60 * 1000)
187 expectTodayLastValue(result, 9)
188 })
189
101 after(async function () { 190 after(async function () {
102 await stopFfmpeg(command) 191 await stopFfmpeg(command)
103 }) 192 })