aboutsummaryrefslogtreecommitdiffhomepage
path: root/server/models/view
diff options
context:
space:
mode:
authorChocobozzz <me@florianbigard.com>2022-03-24 13:36:47 +0100
committerChocobozzz <chocobozzz@cpy.re>2022-04-15 09:49:35 +0200
commitb211106695bb82f6c32e53306081b5262c3d109d (patch)
treefa187de1c33b0956665f5362e29af6b0f6d8bb57 /server/models/view
parent69d48ee30c9d47cddf0c3c047dc99a99dcb6e894 (diff)
downloadPeerTube-b211106695bb82f6c32e53306081b5262c3d109d.tar.gz
PeerTube-b211106695bb82f6c32e53306081b5262c3d109d.tar.zst
PeerTube-b211106695bb82f6c32e53306081b5262c3d109d.zip
Support video views/viewers stats in server
* Add "currentTime" and "event" body params to view endpoint * Merge watching and view endpoints * Introduce WatchAction AP activity * Add tables to store viewer information of local videos * Add endpoints to fetch video views/viewers stats of local videos * Refactor views/viewers handlers * Support "views" and "viewers" counters for both VOD and live videos
Diffstat (limited to 'server/models/view')
-rw-r--r--server/models/view/local-video-viewer-watch-section.ts63
-rw-r--r--server/models/view/local-video-viewer.ts274
-rw-r--r--server/models/view/video-view.ts60
3 files changed, 397 insertions, 0 deletions
diff --git a/server/models/view/local-video-viewer-watch-section.ts b/server/models/view/local-video-viewer-watch-section.ts
new file mode 100644
index 000000000..e29bb7847
--- /dev/null
+++ b/server/models/view/local-video-viewer-watch-section.ts
@@ -0,0 +1,63 @@
1import { Transaction } from 'sequelize'
2import { AllowNull, BelongsTo, Column, CreatedAt, ForeignKey, Model, Table } from 'sequelize-typescript'
3import { MLocalVideoViewerWatchSection } from '@server/types/models'
4import { AttributesOnly } from '@shared/typescript-utils'
5import { LocalVideoViewerModel } from './local-video-viewer'
6
7@Table({
8 tableName: 'localVideoViewerWatchSection',
9 updatedAt: false,
10 indexes: [
11 {
12 fields: [ 'localVideoViewerId' ]
13 }
14 ]
15})
16export class LocalVideoViewerWatchSectionModel extends Model<Partial<AttributesOnly<LocalVideoViewerWatchSectionModel>>> {
17 @CreatedAt
18 createdAt: Date
19
20 @AllowNull(false)
21 @Column
22 watchStart: number
23
24 @AllowNull(false)
25 @Column
26 watchEnd: number
27
28 @ForeignKey(() => LocalVideoViewerModel)
29 @Column
30 localVideoViewerId: number
31
32 @BelongsTo(() => LocalVideoViewerModel, {
33 foreignKey: {
34 allowNull: false
35 },
36 onDelete: 'CASCADE'
37 })
38 LocalVideoViewer: LocalVideoViewerModel
39
40 static async bulkCreateSections (options: {
41 localVideoViewerId: number
42 watchSections: {
43 start: number
44 end: number
45 }[]
46 transaction?: Transaction
47 }) {
48 const { localVideoViewerId, watchSections, transaction } = options
49 const models: MLocalVideoViewerWatchSection[] = []
50
51 for (const section of watchSections) {
52 const model = await this.create({
53 watchStart: section.start,
54 watchEnd: section.end,
55 localVideoViewerId
56 }, { transaction })
57
58 models.push(model)
59 }
60
61 return models
62 }
63}
diff --git a/server/models/view/local-video-viewer.ts b/server/models/view/local-video-viewer.ts
new file mode 100644
index 000000000..6f8de53cd
--- /dev/null
+++ b/server/models/view/local-video-viewer.ts
@@ -0,0 +1,274 @@
1import { QueryTypes } from 'sequelize'
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'
5import { MLocalVideoViewer, MLocalVideoViewerWithWatchSections, MVideo } from '@server/types/models'
6import { VideoStatsOverall, VideoStatsRetention, VideoStatsTimeserie, VideoStatsTimeserieMetric, WatchActionObject } from '@shared/models'
7import { AttributesOnly } from '@shared/typescript-utils'
8import { VideoModel } from '../video/video'
9import { LocalVideoViewerWatchSectionModel } from './local-video-viewer-watch-section'
10
11@Table({
12 tableName: 'localVideoViewer',
13 updatedAt: false,
14 indexes: [
15 {
16 fields: [ 'videoId' ]
17 }
18 ]
19})
20export class LocalVideoViewerModel extends Model<Partial<AttributesOnly<LocalVideoViewerModel>>> {
21 @CreatedAt
22 createdAt: Date
23
24 @AllowNull(false)
25 @Column(DataType.DATE)
26 startDate: Date
27
28 @AllowNull(false)
29 @Column(DataType.DATE)
30 endDate: Date
31
32 @AllowNull(false)
33 @Column
34 watchTime: number
35
36 @AllowNull(true)
37 @Column
38 country: string
39
40 @AllowNull(false)
41 @Default(DataType.UUIDV4)
42 @IsUUID(4)
43 @Column(DataType.UUID)
44 uuid: string
45
46 @AllowNull(false)
47 @Column
48 url: string
49
50 @ForeignKey(() => VideoModel)
51 @Column
52 videoId: number
53
54 @BelongsTo(() => VideoModel, {
55 foreignKey: {
56 allowNull: false
57 },
58 onDelete: 'CASCADE'
59 })
60 Video: VideoModel
61
62 @HasMany(() => LocalVideoViewerWatchSectionModel, {
63 foreignKey: {
64 allowNull: false
65 },
66 onDelete: 'cascade'
67 })
68 WatchSections: LocalVideoViewerWatchSectionModel[]
69
70 static loadByUrl (url: string): Promise<MLocalVideoViewer> {
71 return this.findOne({
72 where: {
73 url
74 }
75 })
76 }
77
78 static loadFullById (id: number): Promise<MLocalVideoViewerWithWatchSections> {
79 return this.findOne({
80 include: [
81 {
82 model: VideoModel.unscoped(),
83 required: true
84 },
85 {
86 model: LocalVideoViewerWatchSectionModel.unscoped(),
87 required: true
88 }
89 ],
90 where: {
91 id
92 }
93 })
94 }
95
96 static async getOverallStats (video: MVideo): Promise<VideoStatsOverall> {
97 const options = {
98 type: QueryTypes.SELECT as QueryTypes.SELECT,
99 replacements: { videoId: video.id }
100 }
101
102 const watchTimeQuery = `SELECT ` +
103 `SUM("localVideoViewer"."watchTime") AS "totalWatchTime", ` +
104 `AVG("localVideoViewer"."watchTime") AS "averageWatchTime" ` +
105 `FROM "localVideoViewer" ` +
106 `INNER JOIN "video" ON "video"."id" = "localVideoViewer"."videoId" ` +
107 `WHERE "videoId" = :videoId`
108
109 const watchTimePromise = LocalVideoViewerModel.sequelize.query<any>(watchTimeQuery, options)
110
111 const watchPeakQuery = `WITH "watchPeakValues" AS (
112 SELECT "startDate" AS "dateBreakpoint", 1 AS "inc"
113 FROM "localVideoViewer"
114 WHERE "videoId" = :videoId
115 UNION ALL
116 SELECT "endDate" AS "dateBreakpoint", -1 AS "inc"
117 FROM "localVideoViewer"
118 WHERE "videoId" = :videoId
119 )
120 SELECT "dateBreakpoint", "concurrent"
121 FROM (
122 SELECT "dateBreakpoint", SUM(SUM("inc")) OVER (ORDER BY "dateBreakpoint") AS "concurrent"
123 FROM "watchPeakValues"
124 GROUP BY "dateBreakpoint"
125 ) tmp
126 ORDER BY "concurrent" DESC
127 FETCH FIRST 1 ROW ONLY`
128 const watchPeakPromise = LocalVideoViewerModel.sequelize.query<any>(watchPeakQuery, options)
129
130 const commentsQuery = `SELECT COUNT(*) AS comments FROM "videoComment" WHERE "videoId" = :videoId`
131 const commentsPromise = LocalVideoViewerModel.sequelize.query<any>(commentsQuery, options)
132
133 const countriesQuery = `SELECT country, COUNT(country) as viewers ` +
134 `FROM "localVideoViewer" ` +
135 `WHERE "videoId" = :videoId AND country IS NOT NULL ` +
136 `GROUP BY country ` +
137 `ORDER BY viewers DESC`
138 const countriesPromise = LocalVideoViewerModel.sequelize.query<any>(countriesQuery, options)
139
140 const [ rowsWatchTime, rowsWatchPeak, rowsComment, rowsCountries ] = await Promise.all([
141 watchTimePromise,
142 watchPeakPromise,
143 commentsPromise,
144 countriesPromise
145 ])
146
147 return {
148 totalWatchTime: rowsWatchTime.length !== 0
149 ? Math.round(rowsWatchTime[0].totalWatchTime) || 0
150 : 0,
151 averageWatchTime: rowsWatchTime.length !== 0
152 ? Math.round(rowsWatchTime[0].averageWatchTime) || 0
153 : 0,
154
155 viewersPeak: rowsWatchPeak.length !== 0
156 ? parseInt(rowsWatchPeak[0].concurrent) || 0
157 : 0,
158 viewersPeakDate: rowsWatchPeak.length !== 0
159 ? rowsWatchPeak[0].dateBreakpoint || null
160 : null,
161
162 views: video.views,
163 likes: video.likes,
164 dislikes: video.dislikes,
165
166 comments: rowsComment.length !== 0
167 ? parseInt(rowsComment[0].comments) || 0
168 : 0,
169
170 countries: rowsCountries.map(r => ({
171 isoCode: r.country,
172 viewers: r.viewers
173 }))
174 }
175 }
176
177 static async getRetentionStats (video: MVideo): Promise<VideoStatsRetention> {
178 const step = Math.max(Math.round(video.duration / 100), 1)
179
180 const query = `WITH "total" AS (SELECT COUNT(*) AS viewers FROM "localVideoViewer" WHERE "videoId" = :videoId) ` +
181 `SELECT serie AS "second", ` +
182 `(COUNT("localVideoViewer".id)::float / (SELECT GREATEST("total"."viewers", 1) FROM "total")) AS "retention" ` +
183 `FROM generate_series(0, ${video.duration}, ${step}) serie ` +
184 `LEFT JOIN "localVideoViewer" ON "localVideoViewer"."videoId" = :videoId ` +
185 `AND EXISTS (` +
186 `SELECT 1 FROM "localVideoViewerWatchSection" ` +
187 `WHERE "localVideoViewer"."id" = "localVideoViewerWatchSection"."localVideoViewerId" ` +
188 `AND serie >= "localVideoViewerWatchSection"."watchStart" ` +
189 `AND serie <= "localVideoViewerWatchSection"."watchEnd"` +
190 `)` +
191 `GROUP BY serie ` +
192 `ORDER BY serie ASC`
193
194 const queryOptions = {
195 type: QueryTypes.SELECT as QueryTypes.SELECT,
196 replacements: { videoId: video.id }
197 }
198
199 const rows = await LocalVideoViewerModel.sequelize.query<any>(query, queryOptions)
200
201 return {
202 data: rows.map(r => ({
203 second: r.second,
204 retentionPercent: parseFloat(r.retention) * 100
205 }))
206 }
207 }
208
209 static async getTimeserieStats (options: {
210 video: MVideo
211 metric: VideoStatsTimeserieMetric
212 }): Promise<VideoStatsTimeserie> {
213 const { video, metric } = options
214
215 const selectMetrics: { [ id in VideoStatsTimeserieMetric ]: string } = {
216 viewers: 'COUNT("localVideoViewer"."id")',
217 aggregateWatchTime: 'SUM("localVideoViewer"."watchTime")'
218 }
219
220 const query = `WITH days AS ( ` +
221 `SELECT (current_date::timestamp - (serie || ' days')::interval)::timestamptz AS day
222 FROM generate_series(0, ${STATS_TIMESERIE.MAX_DAYS - 1}) serie` +
223 `) ` +
224 `SELECT days.day AS date, COALESCE(${selectMetrics[metric]}, 0) AS value ` +
225 `FROM days ` +
226 `LEFT JOIN "localVideoViewer" ON "localVideoViewer"."videoId" = :videoId ` +
227 `AND date_trunc('day', "localVideoViewer"."startDate") = date_trunc('day', days.day) ` +
228 `GROUP BY day ` +
229 `ORDER BY day `
230
231 const queryOptions = {
232 type: QueryTypes.SELECT as QueryTypes.SELECT,
233 replacements: { videoId: video.id }
234 }
235
236 const rows = await LocalVideoViewerModel.sequelize.query<any>(query, queryOptions)
237
238 return {
239 data: rows.map(r => ({
240 date: r.date,
241 value: parseInt(r.value)
242 }))
243 }
244 }
245
246 toActivityPubObject (this: MLocalVideoViewerWithWatchSections): WatchActionObject {
247 const location = this.country
248 ? {
249 location: {
250 addressCountry: this.country
251 }
252 }
253 : {}
254
255 return {
256 id: this.url,
257 type: 'WatchAction',
258 duration: getActivityStreamDuration(this.watchTime),
259 startTime: this.startDate.toISOString(),
260 endTime: this.endDate.toISOString(),
261
262 object: this.Video.url,
263 uuid: this.uuid,
264 actionStatus: 'CompletedActionStatus',
265
266 watchSections: this.WatchSections.map(w => ({
267 startTimestamp: w.watchStart,
268 endTimestamp: w.watchEnd
269 })),
270
271 ...location
272 }
273 }
274}
diff --git a/server/models/view/video-view.ts b/server/models/view/video-view.ts
new file mode 100644
index 000000000..df462e631
--- /dev/null
+++ b/server/models/view/video-view.ts
@@ -0,0 +1,60 @@
1import { literal, Op } from 'sequelize'
2import { AllowNull, BelongsTo, Column, CreatedAt, DataType, ForeignKey, Model, Table } from 'sequelize-typescript'
3import { AttributesOnly } from '@shared/typescript-utils'
4import { VideoModel } from '../video/video'
5
6@Table({
7 tableName: 'videoView',
8 updatedAt: false,
9 indexes: [
10 {
11 fields: [ 'videoId' ]
12 },
13 {
14 fields: [ 'startDate' ]
15 }
16 ]
17})
18export class VideoViewModel extends Model<Partial<AttributesOnly<VideoViewModel>>> {
19 @CreatedAt
20 createdAt: Date
21
22 @AllowNull(false)
23 @Column(DataType.DATE)
24 startDate: Date
25
26 @AllowNull(false)
27 @Column(DataType.DATE)
28 endDate: Date
29
30 @AllowNull(false)
31 @Column
32 views: number
33
34 @ForeignKey(() => VideoModel)
35 @Column
36 videoId: number
37
38 @BelongsTo(() => VideoModel, {
39 foreignKey: {
40 allowNull: false
41 },
42 onDelete: 'CASCADE'
43 })
44 Video: VideoModel
45
46 static removeOldRemoteViewsHistory (beforeDate: string) {
47 const query = {
48 where: {
49 startDate: {
50 [Op.lt]: beforeDate
51 },
52 videoId: {
53 [Op.in]: literal('(SELECT "id" FROM "video" WHERE "remote" IS TRUE)')
54 }
55 }
56 }
57
58 return VideoViewModel.destroy(query)
59 }
60}