server/tests/shared/sql-command.ts

   1 import { QueryTypes, Sequelize } from 'sequelize'
   2 import { forceNumber } from '@shared/core-utils'
   3 import { PeerTubeServer } from '@shared/server-commands'
   4
   5 export class SQLCommand {
   6   private sequelize: Sequelize
   7
   8   constructor (private readonly server: PeerTubeServer) {
   9
  10   }
  11
  12   deleteAll (table: string) {
  13     const seq = this.getSequelize()
  14
  15     const options = { type: QueryTypes.DELETE }
  16
  17     return seq.query(`DELETE FROM "${table}"`, options)
  18   }
  19
  20   async getVideoShareCount () {
  21     const [ { total } ] = await this.selectQuery<{ total: string }>(`SELECT COUNT(*) as total FROM "videoShare"`)
  22     if (total === null) return 0
  23
  24     return parseInt(total, 10)
  25   }
  26
  27   async getInternalFileUrl (fileId: number) {
  28     return this.selectQuery<{ fileUrl: string }>(`SELECT "fileUrl" FROM "videoFile" WHERE id = :fileId`, { fileId })
  29       .then(rows => rows[0].fileUrl)
  30   }
  31
  32   setActorField (to: string, field: string, value: string) {
  33     return this.updateQuery(`UPDATE actor SET ${this.escapeColumnName(field)} = :value WHERE url = :to`, { value, to })
  34   }
  35
  36   setVideoField (uuid: string, field: string, value: string) {
  37     return this.updateQuery(`UPDATE video SET ${this.escapeColumnName(field)} = :value WHERE uuid = :uuid`, { value, uuid })
  38   }
  39
  40   setPlaylistField (uuid: string, field: string, value: string) {
  41     return this.updateQuery(`UPDATE "videoPlaylist" SET ${this.escapeColumnName(field)} = :value WHERE uuid = :uuid`, { value, uuid })
  42   }
  43
  44   async countVideoViewsOf (uuid: string) {
  45     const query = 'SELECT SUM("videoView"."views") AS "total" FROM "videoView" ' +
  46       `INNER JOIN "video" ON "video"."id" = "videoView"."videoId" WHERE "video"."uuid" = :uuid`
  47
  48     const [ { total } ] = await this.selectQuery<{ total: number }>(query, { uuid })
  49     if (!total) return 0
  50
  51     return forceNumber(total)
  52   }
  53
  54   getActorImage (filename: string) {
  55     return this.selectQuery<{ width: number, height: number }>(`SELECT * FROM "actorImage" WHERE filename = :filename`, { filename })
  56       .then(rows => rows[0])
  57   }
  58
  59   // ---------------------------------------------------------------------------
  60
  61   setPluginVersion (pluginName: string, newVersion: string) {
  62     return this.setPluginField(pluginName, 'version', newVersion)
  63   }
  64
  65   setPluginLatestVersion (pluginName: string, newVersion: string) {
  66     return this.setPluginField(pluginName, 'latestVersion', newVersion)
  67   }
  68
  69   setPluginField (pluginName: string, field: string, value: string) {
  70     return this.updateQuery(
  71       `UPDATE "plugin" SET ${this.escapeColumnName(field)} = :value WHERE "name" = :pluginName`,
  72       { pluginName, value }
  73     )
  74   }
  75
  76   // ---------------------------------------------------------------------------
  77
  78   selectQuery <T extends object> (query: string, replacements: { [id: string]: string | number } = {}) {
  79     const seq = this.getSequelize()
  80     const options = {
  81       type: QueryTypes.SELECT as QueryTypes.SELECT,
  82       replacements
  83     }
  84
  85     return seq.query<T>(query, options)
  86   }
  87
  88   updateQuery (query: string, replacements: { [id: string]: string | number } = {}) {
  89     const seq = this.getSequelize()
  90     const options = { type: QueryTypes.UPDATE as QueryTypes.UPDATE, replacements }
  91
  92     return seq.query(query, options)
  93   }
  94
  95   // ---------------------------------------------------------------------------
  96
  97   async getPlaylistInfohash (playlistId: number) {
  98     const query = 'SELECT "p2pMediaLoaderInfohashes" FROM "videoStreamingPlaylist" WHERE id = :playlistId'
  99
 100     const result = await this.selectQuery<{ p2pMediaLoaderInfohashes: string }>(query, { playlistId })
 101     if (!result || result.length === 0) return []
 102
 103     return result[0].p2pMediaLoaderInfohashes
 104   }
 105
 106   // ---------------------------------------------------------------------------
 107
 108   setActorFollowScores (newScore: number) {
 109     return this.updateQuery(`UPDATE "actorFollow" SET "score" = :newScore`, { newScore })
 110   }
 111
 112   setTokenField (accessToken: string, field: string, value: string) {
 113     return this.updateQuery(
 114       `UPDATE "oAuthToken" SET ${this.escapeColumnName(field)} = :value WHERE "accessToken" = :accessToken`,
 115       { value, accessToken }
 116     )
 117   }
 118
 119   async cleanup () {
 120     if (!this.sequelize) return
 121
 122     await this.sequelize.close()
 123     this.sequelize = undefined
 124   }
 125
 126   private getSequelize () {
 127     if (this.sequelize) return this.sequelize
 128
 129     const dbname = 'peertube_test' + this.server.internalServerNumber
 130     const username = 'peertube'
 131     const password = 'peertube'
 132     const host = '127.0.0.1'
 133     const port = 5432
 134
 135     this.sequelize = new Sequelize(dbname, username, password, {
 136       dialect: 'postgres',
 137       host,
 138       port,
 139       logging: false
 140     })
 141
 142     return this.sequelize
 143   }
 144
 145   private escapeColumnName (columnName: string) {
 146     return this.getSequelize().escape(columnName)
 147       .replace(/^'/, '"')
 148       .replace(/'$/, '"')
 149   }
 150 }