shared/server-commands/miscs/sql-command.ts

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