Add ability to delete old remote views

15 files changed, 237 insertions, 26 deletions

diff --git a/config/default.yaml b/config/default.yaml
index d45d84b90..70b10299d 100644
--- a/config/default.yaml
+++ b/config/default.yaml
@@ -118,6 +118,16 @@ history:
     # Other values could be '6 months' or '30 days' etc (PeerTube will periodically delete old entries from database)
     max_age: -1
 
+views:
+  videos:
+    # PeerTube creates a database entry every hour for each video to track views over a period of time
+    # This is used in particular by the Trending page
+    # PeerTube could remove old remote video views if you want to reduce your database size (video view counter will not be altered)
+    # -1 means no cleanup
+    # Other values could be '6 months' or '30 days' etc (PeerTube will periodically delete old entries from database)
+    remote:
+      max_age: -1
+
 cache:
   previews:
     size: 500 # Max number of previews you want to cache
diff --git a/config/production.yaml.example b/config/production.yaml.example
index b813a65e9..06baaf7d4 100644
--- a/config/production.yaml.example
+++ b/config/production.yaml.example
@@ -119,6 +119,17 @@ history:
     # Other values could be '6 months' or '30 days' etc (PeerTube will periodically delete old entries from database)
     max_age: -1
 
+views:
+  videos:
+    # PeerTube creates a database entry every hour for each video to track views over a period of time
+    # This is used in particular by the Trending page
+    # PeerTube could remove old remote video views if you want to reduce your database size (video view counter will not be altered)
+    # -1 means no cleanup
+    # Other values could be '6 months' or '30 days' etc (PeerTube will periodically delete old entries from database)
+    remote:
+      max_age: -1
+
+
 ###############################################################################
 #
 # From this point, all the following keys can be overridden by the web interface
diff --git a/scripts/parse-log.ts b/scripts/parse-log.ts
index fe87db009..83ad45b72 100755
--- a/scripts/parse-log.ts
+++ b/scripts/parse-log.ts
@@ -5,7 +5,7 @@ import { createInterface } from 'readline'
 import * as winston from 'winston'
 import { labelFormatter } from '../server/helpers/logger'
 import { CONFIG } from '../server/initializers/config'
-import { mtimeSortFilesDesc } from '../shared/utils/logs/logs'
+import { mtimeSortFilesDesc } from '../shared/core-utils/logs/logs'
 
 program
   .option('-l, --level [level]', 'Level log (debug/info/warn/error)')
diff --git a/server.ts b/server.ts
index f4f0c4d68..aa4382ee7 100644
--- a/server.ts
+++ b/server.ts
@@ -101,6 +101,7 @@ import {
 import { advertiseDoNotTrack } from './server/middlewares/dnt'
 import { Redis } from './server/lib/redis'
 import { ActorFollowScheduler } from './server/lib/schedulers/actor-follow-scheduler'
+import { RemoveOldViewsScheduler } from './server/lib/schedulers/remove-old-views-scheduler'
 import { RemoveOldJobsScheduler } from './server/lib/schedulers/remove-old-jobs-scheduler'
 import { UpdateVideosScheduler } from './server/lib/schedulers/update-videos-scheduler'
 import { YoutubeDlUpdateScheduler } from './server/lib/schedulers/youtube-dl-update-scheduler'
@@ -242,6 +243,7 @@ async function startApplication () {
   YoutubeDlUpdateScheduler.Instance.enable()
   VideosRedundancyScheduler.Instance.enable()
   RemoveOldHistoryScheduler.Instance.enable()
+  RemoveOldViewsScheduler.Instance.enable()
 
   // Redis initialization
   Redis.Instance.init()
diff --git a/server/controllers/api/server/logs.ts b/server/controllers/api/server/logs.ts
index 03941cca7..e9d1f2efd 100644
--- a/server/controllers/api/server/logs.ts
+++ b/server/controllers/api/server/logs.ts
@@ -1,7 +1,7 @@
 import * as express from 'express'
 import { UserRight } from '../../../../shared/models/users'
 import { asyncMiddleware, authenticate, ensureUserHasRight } from '../../../middlewares'
-import { mtimeSortFilesDesc } from '../../../../shared/utils/logs/logs'
+import { mtimeSortFilesDesc } from '../../../../shared/core-utils/logs/logs'
 import { readdir, readFile } from 'fs-extra'
 import { MAX_LOGS_OUTPUT_CHARACTERS } from '../../../initializers/constants'
 import { join } from 'path'
diff --git a/server/initializers/checker-before-init.ts b/server/initializers/checker-before-init.ts
index 6b43debfb..223ef8078 100644
--- a/server/initializers/checker-before-init.ts
+++ b/server/initializers/checker-before-init.ts
@@ -26,7 +26,8 @@ function checkMissedConfig () {
     'instance.is_nsfw', 'instance.default_nsfw_policy', 'instance.robots', 'instance.securitytxt',
     'services.twitter.username', 'services.twitter.whitelisted',
     'followers.instance.enabled', 'followers.instance.manual_approval',
-    'tracker.enabled', 'tracker.private', 'tracker.reject_too_many_announces'
+    'tracker.enabled', 'tracker.private', 'tracker.reject_too_many_announces',
+    'history.videos.max_age', 'views.videos.remote.max_age'
   ]
   const requiredAlternatives = [
     [ // set
diff --git a/server/initializers/config.ts b/server/initializers/config.ts
index 1f374dea9..baf502305 100644
--- a/server/initializers/config.ts
+++ b/server/initializers/config.ts
@@ -99,6 +99,13 @@ const CONFIG = {
       MAX_AGE: parseDurationToMs(config.get('history.videos.max_age'))
     }
   },
+  VIEWS: {
+    VIDEOS: {
+      REMOTE: {
+        MAX_AGE: parseDurationToMs(config.get('views.videos.remote.max_age'))
+      }
+    }
+  },
   ADMIN: {
     get EMAIL () { return config.get<string>('admin.email') }
   },
diff --git a/server/initializers/constants.ts b/server/initializers/constants.ts
index f008cd291..8f6ef1a81 100644
--- a/server/initializers/constants.ts
+++ b/server/initializers/constants.ts
@@ -163,6 +163,7 @@ const SCHEDULER_INTERVALS_MS = {
   removeOldJobs: 60000 * 60, // 1 hour
   updateVideos: 60000, // 1 minute
   youtubeDLUpdate: 60000 * 60 * 24, // 1 day
+  removeOldViews: 60000 * 60 * 24, // 1 day
   removeOldHistory: 60000 * 60 * 24 // 1 day
 }
 
@@ -592,6 +593,7 @@ if (isTestInstance() === true) {
   SCHEDULER_INTERVALS_MS.actorFollowScores = 1000
   SCHEDULER_INTERVALS_MS.removeOldJobs = 10000
   SCHEDULER_INTERVALS_MS.removeOldHistory = 5000
+  SCHEDULER_INTERVALS_MS.removeOldViews = 5000
   SCHEDULER_INTERVALS_MS.updateVideos = 5000
   REPEAT_JOBS[ 'videos-views' ] = { every: 5000 }
 
diff --git a/server/lib/schedulers/remove-old-views-scheduler.ts b/server/lib/schedulers/remove-old-views-scheduler.ts
new file mode 100644
index 000000000..39fbb9163
--- /dev/null
+++ b/server/lib/schedulers/remove-old-views-scheduler.ts
@@ -0,0 +1,33 @@
+import { logger } from '../../helpers/logger'
+import { AbstractScheduler } from './abstract-scheduler'
+import { SCHEDULER_INTERVALS_MS } from '../../initializers/constants'
+import { UserVideoHistoryModel } from '../../models/account/user-video-history'
+import { CONFIG } from '../../initializers/config'
+import { isTestInstance } from '../../helpers/core-utils'
+import { VideoViewModel } from '../../models/video/video-views'
+
+export class RemoveOldViewsScheduler extends AbstractScheduler {
+
+  private static instance: AbstractScheduler
+
+  protected schedulerIntervalMs = SCHEDULER_INTERVALS_MS.removeOldViews
+
+  private constructor () {
+    super()
+  }
+
+  protected internalExecute () {
+    if (CONFIG.VIEWS.VIDEOS.REMOTE.MAX_AGE === -1) return
+
+    logger.info('Removing old videos views.')
+
+    const now = new Date()
+    const beforeDate = new Date(now.getTime() - CONFIG.VIEWS.VIDEOS.REMOTE.MAX_AGE).toISOString()
+
+    return VideoViewModel.removeOldRemoteViewsHistory(beforeDate)
+  }
+
+  static get Instance () {
+    return this.instance || (this.instance = new this())
+  }
+}
diff --git a/server/models/video/video-views.ts b/server/models/video/video-views.ts
index fde5f7056..6071e8c22 100644
--- a/server/models/video/video-views.ts
+++ b/server/models/video/video-views.ts
@@ -41,4 +41,18 @@ export class VideoViewModel extends Model<VideoViewModel> {
   })
   Video: VideoModel
 
+  static removeOldRemoteViewsHistory (beforeDate: string) {
+    const query = {
+      where: {
+        startDate: {
+          [Sequelize.Op.lt]: beforeDate
+        },
+        videoId: {
+          [Sequelize.Op.in]: Sequelize.literal('(SELECT "id" FROM "video" WHERE "remote" IS TRUE)')
+        }
+      }
+    }
+
+    return VideoViewModel.destroy(query)
+  }
 }
diff --git a/server/tests/api/videos/index.ts b/server/tests/api/videos/index.ts
index 4be12ad15..93e1f3e98 100644
--- a/server/tests/api/videos/index.ts
+++ b/server/tests/api/videos/index.ts
@@ -18,3 +18,4 @@ import './video-transcoder'
 import './videos-filter'
 import './videos-history'
 import './videos-overview'
+import './videos-views-cleaner'
diff --git a/server/tests/api/videos/videos-views-cleaner.ts b/server/tests/api/videos/videos-views-cleaner.ts
new file mode 100644
index 000000000..9f268c8e6
--- /dev/null
+++ b/server/tests/api/videos/videos-views-cleaner.ts
@@ -0,0 +1,113 @@
+/* tslint:disable:no-unused-expression */
+
+import * as chai from 'chai'
+import 'mocha'
+import {
+  flushAndRunMultipleServers,
+  flushTests,
+  killallServers,
+  reRunServer,
+  runServer,
+  ServerInfo,
+  setAccessTokensToServers,
+  uploadVideo, uploadVideoAndGetId, viewVideo, wait, countVideoViewsOf, doubleFollow, waitJobs
+} from '../../../../shared/utils'
+import { getVideosOverview } from '../../../../shared/utils/overviews/overviews'
+import { VideosOverview } from '../../../../shared/models/overviews'
+import { listMyVideosHistory } from '../../../../shared/utils/videos/video-history'
+
+const expect = chai.expect
+
+describe('Test video views cleaner', function () {
+  let servers: ServerInfo[]
+
+  let videoIdServer1: string
+  let videoIdServer2: string
+
+  before(async function () {
+    this.timeout(50000)
+
+    await flushTests()
+
+    servers = await flushAndRunMultipleServers(2)
+    await setAccessTokensToServers(servers)
+
+    await doubleFollow(servers[0], servers[1])
+
+    videoIdServer1 = (await uploadVideoAndGetId({ server: servers[0], videoName: 'video server 1' })).uuid
+    videoIdServer2 = (await uploadVideoAndGetId({ server: servers[1], videoName: 'video server 2' })).uuid
+
+    await waitJobs(servers)
+
+    await viewVideo(servers[0].url, videoIdServer1)
+    await viewVideo(servers[1].url, videoIdServer1)
+    await viewVideo(servers[0].url, videoIdServer2)
+    await viewVideo(servers[1].url, videoIdServer2)
+
+    await waitJobs(servers)
+  })
+
+  it('Should not clean old video views', async function () {
+    this.timeout(50000)
+
+    killallServers([ servers[0] ])
+
+    await reRunServer(servers[0], { views: { videos: { remote: { max_age: '10 days' } } } })
+
+    await wait(6000)
+
+    // Should still have views
+
+    {
+      for (const server of servers) {
+        const total = await countVideoViewsOf(server.serverNumber, videoIdServer1)
+        expect(total).to.equal(2)
+      }
+    }
+
+    {
+      for (const server of servers) {
+        const total = await countVideoViewsOf(server.serverNumber, videoIdServer2)
+        expect(total).to.equal(2)
+      }
+    }
+  })
+
+  it('Should clean old video views', async function () {
+    this.timeout(50000)
+
+    this.timeout(50000)
+
+    killallServers([ servers[0] ])
+
+    await reRunServer(servers[0], { views: { videos: { remote: { max_age: '5 seconds' } } } })
+
+    await wait(6000)
+
+    // Should still have views
+
+    {
+      for (const server of servers) {
+        const total = await countVideoViewsOf(server.serverNumber, videoIdServer1)
+        expect(total).to.equal(2)
+      }
+    }
+
+    {
+      const totalServer1 = await countVideoViewsOf(servers[0].serverNumber, videoIdServer2)
+      expect(totalServer1).to.equal(0)
+
+      const totalServer2 = await countVideoViewsOf(servers[1].serverNumber, videoIdServer2)
+      expect(totalServer2).to.equal(2)
+    }
+  })
+
+  after(async function () {
+    killallServers(servers)
+
+    // Keep the logs if the test failed
+    if (this['ok']) {
+      await flushTests()
+    }
+  })
+})
diff --git a/shared/core-utils/logs/logs.ts b/shared/core-utils/logs/logs.ts
new file mode 100644
index 000000000..d0996cf55
--- /dev/null
+++ b/shared/core-utils/logs/logs.ts
@@ -0,0 +1,25 @@
+import { stat } from 'fs-extra'
+
+async function mtimeSortFilesDesc (files: string[], basePath: string) {
+  const promises = []
+  const out: { file: string, mtime: number }[] = []
+
+  for (const file of files) {
+    const p = stat(basePath + '/' + file)
+      .then(stats => {
+        if (stats.isFile()) out.push({ file, mtime: stats.mtime.getTime() })
+      })
+
+    promises.push(p)
+  }
+
+  await Promise.all(promises)
+
+  out.sort((a, b) => b.mtime - a.mtime)
+
+  return out
+}
+
+export {
+  mtimeSortFilesDesc
+}
diff --git a/shared/utils/logs/logs.ts b/shared/utils/logs/logs.ts
index 21adace82..cbb1afb93 100644
--- a/shared/utils/logs/logs.ts
+++ b/shared/utils/logs/logs.ts
@@ -1,28 +1,6 @@
-// Thanks: https://stackoverflow.com/a/37014317
-import { stat } from 'fs-extra'
 import { makeGetRequest } from '../requests/requests'
 import { LogLevel } from '../../models/server/log-level.type'
 
-async function mtimeSortFilesDesc (files: string[], basePath: string) {
-  const promises = []
-  const out: { file: string, mtime: number }[] = []
-
-  for (const file of files) {
-    const p = stat(basePath + '/' + file)
-      .then(stats => {
-        if (stats.isFile()) out.push({ file, mtime: stats.mtime.getTime() })
-      })
-
-    promises.push(p)
-  }
-
-  await Promise.all(promises)
-
-  out.sort((a, b) => b.mtime - a.mtime)
-
-  return out
-}
-
 function getLogs (url: string, accessToken: string, startDate: Date, endDate?: Date, level?: LogLevel) {
   const path = '/api/v1/server/logs'
 
@@ -36,6 +14,5 @@ function getLogs (url: string, accessToken: string, startDate: Date, endDate?: D
 }
 
 export {
-  mtimeSortFilesDesc,
   getLogs
 }
diff --git a/shared/utils/miscs/sql.ts b/shared/utils/miscs/sql.ts
index 1ce3d801a..b281471ce 100644
--- a/shared/utils/miscs/sql.ts
+++ b/shared/utils/miscs/sql.ts
@@ -48,6 +48,20 @@ function setPlaylistField (serverNumber: number, uuid: string, field: string, va
   return seq.query(`UPDATE "videoPlaylist" SET "${field}" = '${value}' WHERE uuid = '${uuid}'`, options)
 }
 
+async function countVideoViewsOf (serverNumber: number, uuid: string) {
+  const seq = getSequelize(serverNumber)
+
+  // tslint:disable
+  const query = `SELECT SUM("videoView"."views") AS "total" FROM "videoView" INNER JOIN "video" ON "video"."id" = "videoView"."videoId" WHERE "video"."uuid" = '${uuid}'`
+
+  const options = { type: Sequelize.QueryTypes.SELECT }
+  const [ { total } ] = await seq.query(query, options)
+
+  if (!total) return 0
+
+  return parseInt(total, 10)
+}
+
 async function closeAllSequelize (servers: any[]) {
   for (let i = 1; i <= servers.length; i++) {
     if (sequelizes[ i ]) {
@@ -61,5 +75,6 @@ export {
   setVideoField,
   setPlaylistField,
   setActorField,
+  countVideoViewsOf,
   closeAllSequelize
 }