1 import { QueryTypes, Sequelize } from 'sequelize'
2 import { forceNumber } from '@shared/core-utils'
3 import { AbstractCommand } from '../shared'
5 export class SQLCommand extends AbstractCommand {
6 private sequelize: Sequelize
8 deleteAll (table: string) {
9 const seq = this.getSequelize()
11 const options = { type: QueryTypes.DELETE }
13 return seq.query(`DELETE FROM "${table}"`, options)
16 async getVideoShareCount () {
17 const [ { total } ] = await this.selectQuery<{ total: string }>(`SELECT COUNT(*) as total FROM "videoShare"`)
18 if (total === null) return 0
20 return parseInt(total, 10)
23 async getInternalFileUrl (fileId: number) {
24 return this.selectQuery<{ fileUrl: string }>(`SELECT "fileUrl" FROM "videoFile" WHERE id = :fileId`, { fileId })
25 .then(rows => rows[0].fileUrl)
28 setActorField (to: string, field: string, value: string) {
29 return this.updateQuery(`UPDATE actor SET ${this.escapeColumnName(field)} = :value WHERE url = :to`, { value, to })
32 setVideoField (uuid: string, field: string, value: string) {
33 return this.updateQuery(`UPDATE video SET ${this.escapeColumnName(field)} = :value WHERE uuid = :uuid`, { value, uuid })
36 setPlaylistField (uuid: string, field: string, value: string) {
37 return this.updateQuery(`UPDATE "videoPlaylist" SET ${this.escapeColumnName(field)} = :value WHERE uuid = :uuid`, { value, uuid })
40 async countVideoViewsOf (uuid: string) {
41 const query = 'SELECT SUM("videoView"."views") AS "total" FROM "videoView" ' +
42 `INNER JOIN "video" ON "video"."id" = "videoView"."videoId" WHERE "video"."uuid" = :uuid`
44 const [ { total } ] = await this.selectQuery<{ total: number }>(query, { uuid })
47 return forceNumber(total)
50 getActorImage (filename: string) {
51 return this.selectQuery<{ width: number, height: number }>(`SELECT * FROM "actorImage" WHERE filename = :filename`, { filename })
52 .then(rows => rows[0])
55 // ---------------------------------------------------------------------------
57 setPluginVersion (pluginName: string, newVersion: string) {
58 return this.setPluginField(pluginName, 'version', newVersion)
61 setPluginLatestVersion (pluginName: string, newVersion: string) {
62 return this.setPluginField(pluginName, 'latestVersion', newVersion)
65 setPluginField (pluginName: string, field: string, value: string) {
66 return this.updateQuery(
67 `UPDATE "plugin" SET ${this.escapeColumnName(field)} = :value WHERE "name" = :pluginName`,
72 // ---------------------------------------------------------------------------
74 selectQuery <T extends object> (query: string, replacements: { [id: string]: string | number } = {}) {
75 const seq = this.getSequelize()
77 type: QueryTypes.SELECT as QueryTypes.SELECT,
81 return seq.query<T>(query, options)
84 updateQuery (query: string, replacements: { [id: string]: string | number } = {}) {
85 const seq = this.getSequelize()
86 const options = { type: QueryTypes.UPDATE as QueryTypes.UPDATE, replacements }
88 return seq.query(query, options)
91 // ---------------------------------------------------------------------------
93 async getPlaylistInfohash (playlistId: number) {
94 const query = 'SELECT "p2pMediaLoaderInfohashes" FROM "videoStreamingPlaylist" WHERE id = :playlistId'
96 const result = await this.selectQuery<{ p2pMediaLoaderInfohashes: string }>(query, { playlistId })
97 if (!result || result.length === 0) return []
99 return result[0].p2pMediaLoaderInfohashes
102 // ---------------------------------------------------------------------------
104 setActorFollowScores (newScore: number) {
105 return this.updateQuery(`UPDATE "actorFollow" SET "score" = :newScore`, { newScore })
108 setTokenField (accessToken: string, field: string, value: string) {
109 return this.updateQuery(
110 `UPDATE "oAuthToken" SET ${this.escapeColumnName(field)} = :value WHERE "accessToken" = :accessToken`,
111 { value, accessToken }
116 if (!this.sequelize) return
118 await this.sequelize.close()
119 this.sequelize = undefined
122 private getSequelize () {
123 if (this.sequelize) return this.sequelize
125 const dbname = 'peertube_test' + this.server.internalServerNumber
126 const username = 'peertube'
127 const password = 'peertube'
128 const host = '127.0.0.1'
131 this.sequelize = new Sequelize(dbname, username, password, {
138 return this.sequelize
141 private escapeColumnName (columnName: string) {
142 return this.getSequelize().escape(columnName)