From 9293139fde7091e9badcafa9b570b83cea9a10ad Mon Sep 17 00:00:00 2001
From: Chocobozzz <me@florianbigard.com>
Date: Fri, 9 Jul 2021 15:37:43 +0200
Subject: Introduce sql command
---
shared/extra-utils/miscs/sql-command.ts | 142 ++++++++++++++++++++++++++++++++
1 file changed, 142 insertions(+)
create mode 100644 shared/extra-utils/miscs/sql-command.ts
(limited to 'shared/extra-utils/miscs/sql-command.ts')
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<any>(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
+ }
+
+}
--
cgit v1.2.3
From 6c5065a011b099618681a37bd77eaa7bd3db752e Mon Sep 17 00:00:00 2001
From: Chocobozzz <me@florianbigard.com>
Date: Tue, 13 Jul 2021 09:43:59 +0200
Subject: Introduce server commands
---
shared/extra-utils/miscs/sql-command.ts | 2 +-
1 file changed, 1 insertion(+), 1 deletion(-)
(limited to 'shared/extra-utils/miscs/sql-command.ts')
diff --git a/shared/extra-utils/miscs/sql-command.ts b/shared/extra-utils/miscs/sql-command.ts
index 2a3e9e607..80c8cd271 100644
--- a/shared/extra-utils/miscs/sql-command.ts
+++ b/shared/extra-utils/miscs/sql-command.ts
@@ -1,5 +1,5 @@
import { QueryTypes, Sequelize } from 'sequelize'
-import { AbstractCommand } from '../shared'
+import { AbstractCommand } from '../shared/abstract-command'
export class SQLCommand extends AbstractCommand {
private sequelize: Sequelize
--
cgit v1.2.3