From 9293139fde7091e9badcafa9b570b83cea9a10ad Mon Sep 17 00:00:00 2001 From: Chocobozzz Date: Fri, 9 Jul 2021 15:37:43 +0200 Subject: Introduce sql command --- shared/extra-utils/miscs/index.ts | 2 +- shared/extra-utils/miscs/sql-command.ts | 142 ++++++++++++++++++++++++++++ shared/extra-utils/miscs/sql.ts | 161 -------------------------------- shared/extra-utils/miscs/stubs.ts | 7 -- 4 files changed, 143 insertions(+), 169 deletions(-) create mode 100644 shared/extra-utils/miscs/sql-command.ts delete mode 100644 shared/extra-utils/miscs/sql.ts (limited to 'shared/extra-utils/miscs') diff --git a/shared/extra-utils/miscs/index.ts b/shared/extra-utils/miscs/index.ts index ccacd4c79..7e236c329 100644 --- a/shared/extra-utils/miscs/index.ts +++ b/shared/extra-utils/miscs/index.ts @@ -1,3 +1,3 @@ export * from './miscs' -export * from './sql' +export * from './sql-command' export * from './stubs' diff --git a/shared/extra-utils/miscs/sql-command.ts b/shared/extra-utils/miscs/sql-command.ts new file mode 100644 index 000000000..2a3e9e607 --- /dev/null +++ b/shared/extra-utils/miscs/sql-command.ts @@ -0,0 +1,142 @@ +import { QueryTypes, Sequelize } from 'sequelize' +import { AbstractCommand } from '../shared' + +export class SQLCommand extends AbstractCommand { + private sequelize: Sequelize + + deleteAll (table: string) { + const seq = this.getSequelize() + + const options = { type: QueryTypes.DELETE } + + return seq.query(`DELETE FROM "${table}"`, options) + } + + async getCount (table: string) { + const seq = this.getSequelize() + + const options = { type: QueryTypes.SELECT as QueryTypes.SELECT } + + const [ { total } ] = await seq.query<{ total: string }>(`SELECT COUNT(*) as total FROM "${table}"`, options) + if (total === null) return 0 + + return parseInt(total, 10) + } + + setActorField (to: string, field: string, value: string) { + const seq = this.getSequelize() + + const options = { type: QueryTypes.UPDATE } + + return seq.query(`UPDATE actor SET "${field}" = '${value}' WHERE url = '${to}'`, options) + } + + setVideoField (uuid: string, field: string, value: string) { + const seq = this.getSequelize() + + const options = { type: QueryTypes.UPDATE } + + return seq.query(`UPDATE video SET "${field}" = '${value}' WHERE uuid = '${uuid}'`, options) + } + + setPlaylistField (uuid: string, field: string, value: string) { + const seq = this.getSequelize() + + const options = { type: QueryTypes.UPDATE } + + return seq.query(`UPDATE "videoPlaylist" SET "${field}" = '${value}' WHERE uuid = '${uuid}'`, options) + } + + async countVideoViewsOf (uuid: string) { + const seq = this.getSequelize() + + // 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: QueryTypes.SELECT as QueryTypes.SELECT } + const [ { total } ] = await seq.query<{ total: number }>(query, options) + + if (!total) return 0 + + return parseInt(total + '', 10) + } + + getActorImage (filename: string) { + return this.selectQuery(`SELECT * FROM "actorImage" WHERE filename = '${filename}'`) + .then(rows => rows[0]) + } + + selectQuery (query: string) { + const seq = this.getSequelize() + const options = { type: QueryTypes.SELECT as QueryTypes.SELECT } + + return seq.query(query, options) + } + + updateQuery (query: string) { + const seq = this.getSequelize() + const options = { type: QueryTypes.UPDATE as QueryTypes.UPDATE } + + return seq.query(query, options) + } + + setPluginField (pluginName: string, field: string, value: string) { + const seq = this.getSequelize() + + const options = { type: QueryTypes.UPDATE } + + return seq.query(`UPDATE "plugin" SET "${field}" = '${value}' WHERE "name" = '${pluginName}'`, options) + } + + setPluginVersion (pluginName: string, newVersion: string) { + return this.setPluginField(pluginName, 'version', newVersion) + } + + setPluginLatestVersion (pluginName: string, newVersion: string) { + return this.setPluginField(pluginName, 'latestVersion', newVersion) + } + + setActorFollowScores (newScore: number) { + const seq = this.getSequelize() + + const options = { type: QueryTypes.UPDATE } + + return seq.query(`UPDATE "actorFollow" SET "score" = ${newScore}`, options) + } + + setTokenField (accessToken: string, field: string, value: string) { + const seq = this.getSequelize() + + const options = { type: QueryTypes.UPDATE } + + return seq.query(`UPDATE "oAuthToken" SET "${field}" = '${value}' WHERE "accessToken" = '${accessToken}'`, options) + } + + async cleanup () { + if (!this.sequelize) return + + await this.sequelize.close() + this.sequelize = undefined + } + + private getSequelize () { + if (this.sequelize) return this.sequelize + + const dbname = 'peertube_test' + this.server.internalServerNumber + const username = 'peertube' + const password = 'peertube' + const host = 'localhost' + const port = 5432 + + this.sequelize = new Sequelize(dbname, username, password, { + dialect: 'postgres', + host, + port, + logging: false + }) + + return this.sequelize + } + +} diff --git a/shared/extra-utils/miscs/sql.ts b/shared/extra-utils/miscs/sql.ts deleted file mode 100644 index 65a0aa5fe..000000000 --- a/shared/extra-utils/miscs/sql.ts +++ /dev/null @@ -1,161 +0,0 @@ -import { QueryTypes, Sequelize } from 'sequelize' -import { ServerInfo } from '../server/servers' - -const sequelizes: { [ id: number ]: Sequelize } = {} - -function getSequelize (internalServerNumber: number) { - if (sequelizes[internalServerNumber]) return sequelizes[internalServerNumber] - - const dbname = 'peertube_test' + internalServerNumber - const username = 'peertube' - const password = 'peertube' - const host = 'localhost' - const port = 5432 - - const seq = new Sequelize(dbname, username, password, { - dialect: 'postgres', - host, - port, - logging: false - }) - - sequelizes[internalServerNumber] = seq - - return seq -} - -function deleteAll (internalServerNumber: number, table: string) { - const seq = getSequelize(internalServerNumber) - - const options = { type: QueryTypes.DELETE } - - return seq.query(`DELETE FROM "${table}"`, options) -} - -async function getCount (internalServerNumber: number, table: string) { - const seq = getSequelize(internalServerNumber) - - const options = { type: QueryTypes.SELECT as QueryTypes.SELECT } - - const [ { total } ] = await seq.query<{ total: string }>(`SELECT COUNT(*) as total FROM "${table}"`, options) - if (total === null) return 0 - - return parseInt(total, 10) -} - -function setActorField (internalServerNumber: number, to: string, field: string, value: string) { - const seq = getSequelize(internalServerNumber) - - const options = { type: QueryTypes.UPDATE } - - return seq.query(`UPDATE actor SET "${field}" = '${value}' WHERE url = '${to}'`, options) -} - -function setVideoField (internalServerNumber: number, uuid: string, field: string, value: string) { - const seq = getSequelize(internalServerNumber) - - const options = { type: QueryTypes.UPDATE } - - return seq.query(`UPDATE video SET "${field}" = '${value}' WHERE uuid = '${uuid}'`, options) -} - -function setPlaylistField (internalServerNumber: number, uuid: string, field: string, value: string) { - const seq = getSequelize(internalServerNumber) - - const options = { type: QueryTypes.UPDATE } - - return seq.query(`UPDATE "videoPlaylist" SET "${field}" = '${value}' WHERE uuid = '${uuid}'`, options) -} - -async function countVideoViewsOf (internalServerNumber: number, uuid: string) { - const seq = getSequelize(internalServerNumber) - - // 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: QueryTypes.SELECT as QueryTypes.SELECT } - const [ { total } ] = await seq.query<{ total: number }>(query, options) - - if (!total) return 0 - - return parseInt(total + '', 10) -} - -function getActorImage (internalServerNumber: number, filename: string) { - return selectQuery(internalServerNumber, `SELECT * FROM "actorImage" WHERE filename = '${filename}'`) - .then(rows => rows[0]) -} - -function selectQuery (internalServerNumber: number, query: string) { - const seq = getSequelize(internalServerNumber) - const options = { type: QueryTypes.SELECT as QueryTypes.SELECT } - - return seq.query(query, options) -} - -function updateQuery (internalServerNumber: number, query: string) { - const seq = getSequelize(internalServerNumber) - const options = { type: QueryTypes.UPDATE as QueryTypes.UPDATE } - - return seq.query(query, options) -} - -async function closeAllSequelize (servers: ServerInfo[]) { - for (const server of servers) { - if (sequelizes[server.internalServerNumber]) { - await sequelizes[server.internalServerNumber].close() - // eslint-disable-next-line - delete sequelizes[server.internalServerNumber] - } - } -} - -function setPluginField (internalServerNumber: number, pluginName: string, field: string, value: string) { - const seq = getSequelize(internalServerNumber) - - const options = { type: QueryTypes.UPDATE } - - return seq.query(`UPDATE "plugin" SET "${field}" = '${value}' WHERE "name" = '${pluginName}'`, options) -} - -function setPluginVersion (internalServerNumber: number, pluginName: string, newVersion: string) { - return setPluginField(internalServerNumber, pluginName, 'version', newVersion) -} - -function setPluginLatestVersion (internalServerNumber: number, pluginName: string, newVersion: string) { - return setPluginField(internalServerNumber, pluginName, 'latestVersion', newVersion) -} - -function setActorFollowScores (internalServerNumber: number, newScore: number) { - const seq = getSequelize(internalServerNumber) - - const options = { type: QueryTypes.UPDATE } - - return seq.query(`UPDATE "actorFollow" SET "score" = ${newScore}`, options) -} - -function setTokenField (internalServerNumber: number, accessToken: string, field: string, value: string) { - const seq = getSequelize(internalServerNumber) - - const options = { type: QueryTypes.UPDATE } - - return seq.query(`UPDATE "oAuthToken" SET "${field}" = '${value}' WHERE "accessToken" = '${accessToken}'`, options) -} - -export { - setVideoField, - setPlaylistField, - setActorField, - countVideoViewsOf, - setPluginVersion, - setPluginLatestVersion, - selectQuery, - getActorImage, - deleteAll, - setTokenField, - updateQuery, - setActorFollowScores, - closeAllSequelize, - getCount -} diff --git a/shared/extra-utils/miscs/stubs.ts b/shared/extra-utils/miscs/stubs.ts index d1eb0e3b2..940e4bf29 100644 --- a/shared/extra-utils/miscs/stubs.ts +++ b/shared/extra-utils/miscs/stubs.ts @@ -2,13 +2,6 @@ function buildRequestStub (): any { return { } } -function buildResponseStub (): any { - return { - locals: {} - } -} - export { - buildResponseStub, buildRequestStub } -- cgit v1.2.3