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 -- shared/extra-utils/server/servers.ts | 10 +- shared/extra-utils/shared/abstract-command.ts | 2 +- 6 files changed, 152 insertions(+), 172 deletions(-) create mode 100644 shared/extra-utils/miscs/sql-command.ts delete mode 100644 shared/extra-utils/miscs/sql.ts (limited to 'shared') 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 } diff --git a/shared/extra-utils/server/servers.ts b/shared/extra-utils/server/servers.ts index 41b48a8ee..bd3be8373 100644 --- a/shared/extra-utils/server/servers.ts +++ b/shared/extra-utils/server/servers.ts @@ -30,6 +30,7 @@ import { ServicesCommand, StreamingPlaylistsCommand } from '../videos' +import { SQLCommand } from '../miscs' import { CommentsCommand } from '../videos/comments-command' import { ConfigCommand } from './config-command' import { ContactFormCommand } from './contact-form-command' @@ -123,6 +124,7 @@ interface ServerInfo { streamingPlaylistsCommand?: StreamingPlaylistsCommand channelsCommand?: ChannelsCommand commentsCommand?: CommentsCommand + sqlCommand?: SQLCommand } function parallelTests () { @@ -367,6 +369,7 @@ function assignCommands (server: ServerInfo) { server.streamingPlaylistsCommand = new StreamingPlaylistsCommand(server) server.channelsCommand = new ChannelsCommand(server) server.commentsCommand = new CommentsCommand(server) + server.sqlCommand = new SQLCommand(server) } async function reRunServer (server: ServerInfo, configOverride?: any) { @@ -398,17 +401,20 @@ async function checkDirectoryIsEmpty (server: ServerInfo, directory: string, exc expect(filtered).to.have.lengthOf(0) } -function killallServers (servers: ServerInfo[]) { +async function killallServers (servers: ServerInfo[]) { for (const server of servers) { if (!server.app) continue + await server.sqlCommand.cleanup() + process.kill(-server.app.pid) + server.app = null } } async function cleanupTests (servers: ServerInfo[]) { - killallServers(servers) + await killallServers(servers) if (isGithubCI()) { await ensureDir('artifacts') diff --git a/shared/extra-utils/shared/abstract-command.ts b/shared/extra-utils/shared/abstract-command.ts index 6a9ab1348..200db90d4 100644 --- a/shared/extra-utils/shared/abstract-command.ts +++ b/shared/extra-utils/shared/abstract-command.ts @@ -1,6 +1,6 @@ import { isAbsolute, join } from 'path' import { HttpStatusCode } from '@shared/core-utils' -import { root } from '../miscs' +import { root } from '../miscs/miscs' import { makeDeleteRequest, makeGetRequest, -- cgit v1.2.3