From 25691c99307997b80075c817a801fe3049cd8987 Mon Sep 17 00:00:00 2001 From: Chocobozzz Date: Tue, 3 Jan 2023 14:23:42 +0100 Subject: Prefer using sequelize replacements even for tests --- server/tests/api/activitypub/cleaner.ts | 8 +-- server/tests/api/live/live-fast-restream.ts | 2 +- shared/server-commands/miscs/sql-command.ts | 102 ++++++++++++---------------- 3 files changed, 49 insertions(+), 63 deletions(-) diff --git a/server/tests/api/activitypub/cleaner.ts b/server/tests/api/activitypub/cleaner.ts index eb6779123..1c1495022 100644 --- a/server/tests/api/activitypub/cleaner.ts +++ b/server/tests/api/activitypub/cleaner.ts @@ -148,7 +148,7 @@ describe('Test AP cleaner', function () { it('Should destroy server 3 internal shares and correctly clean them', async function () { this.timeout(20000) - const preCount = await servers[0].sql.getCount('videoShare') + const preCount = await servers[0].sql.getVideoShareCount() expect(preCount).to.equal(6) await servers[2].sql.deleteAll('videoShare') @@ -156,7 +156,7 @@ describe('Test AP cleaner', function () { await waitJobs(servers) // Still 6 because we don't have remote shares on local videos - const postCount = await servers[0].sql.getCount('videoShare') + const postCount = await servers[0].sql.getVideoShareCount() expect(postCount).to.equal(6) }) @@ -185,7 +185,7 @@ describe('Test AP cleaner', function () { async function check (like: string, ofServerUrl: string, urlSuffix: string, remote: 'true' | 'false') { const query = `SELECT "videoId", "accountVideoRate".url FROM "accountVideoRate" ` + `INNER JOIN video ON "accountVideoRate"."videoId" = video.id AND remote IS ${remote} WHERE "accountVideoRate"."url" LIKE '${like}'` - const res = await servers[0].sql.selectQuery(query) + const res = await servers[0].sql.selectQuery<{ url: string }>(query) for (const rate of res) { const matcher = new RegExp(`^${ofServerUrl}/accounts/root/dislikes/\\d+${urlSuffix}$`) @@ -231,7 +231,7 @@ describe('Test AP cleaner', function () { const query = `SELECT "videoId", "videoComment".url, uuid as "videoUUID" FROM "videoComment" ` + `INNER JOIN video ON "videoComment"."videoId" = video.id AND remote IS ${remote} WHERE "videoComment"."url" LIKE '${like}'` - const res = await servers[0].sql.selectQuery(query) + const res = await servers[0].sql.selectQuery<{ url: string, videoUUID: string }>(query) for (const comment of res) { const matcher = new RegExp(`${ofServerUrl}/videos/watch/${comment.videoUUID}/comments/\\d+${urlSuffix}`) diff --git a/server/tests/api/live/live-fast-restream.ts b/server/tests/api/live/live-fast-restream.ts index c0bb8d529..63a69e591 100644 --- a/server/tests/api/live/live-fast-restream.ts +++ b/server/tests/api/live/live-fast-restream.ts @@ -129,7 +129,7 @@ describe('Fast restream in live', function () { await server.config.enableLive({ allowReplay: true, transcoding: true, resolutions: 'min' }) }) - it('Should correctly fast reastream in a permanent live with and without save replay', async function () { + it('Should correctly fast restream in a permanent live with and without save replay', async function () { this.timeout(480000) // A test can take a long time, so prefer to run them in parallel diff --git a/shared/server-commands/miscs/sql-command.ts b/shared/server-commands/miscs/sql-command.ts index 823fc9e38..35cc2253f 100644 --- a/shared/server-commands/miscs/sql-command.ts +++ b/shared/server-commands/miscs/sql-command.ts @@ -13,101 +13,87 @@ export class SQLCommand extends AbstractCommand { 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) + async getVideoShareCount () { + const [ { total } ] = await this.selectQuery<{ total: string }>(`SELECT COUNT(*) as total FROM "videoShare"`) if (total === null) return 0 return parseInt(total, 10) } async getInternalFileUrl (fileId: number) { - return this.selectQuery(`SELECT "fileUrl" FROM "videoFile" WHERE id = ${fileId}`) - .then(rows => rows[0].fileUrl as string) + return this.selectQuery<{ fileUrl: string }>(`SELECT "fileUrl" FROM "videoFile" WHERE id = :fileId`, { fileId }) + .then(rows => rows[0].fileUrl) } 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) + return this.updateQuery(`UPDATE actor SET ${this.escapeColumnName(field)} = :value WHERE url = :to`, { value, to }) } 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) + return this.updateQuery(`UPDATE video SET ${this.escapeColumnName(field)} = :value WHERE uuid = :uuid`, { value, uuid }) } 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) + return this.updateQuery(`UPDATE "videoPlaylist" SET ${this.escapeColumnName(field)} = :value WHERE uuid = :uuid`, { value, uuid }) } async countVideoViewsOf (uuid: string) { - const seq = this.getSequelize() - 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) + `INNER JOIN "video" ON "video"."id" = "videoView"."videoId" WHERE "video"."uuid" = :uuid` + const [ { total } ] = await this.selectQuery<{ total: number }>(query, { uuid }) if (!total) return 0 return forceNumber(total) } getActorImage (filename: string) { - return this.selectQuery(`SELECT * FROM "actorImage" WHERE filename = '${filename}'`) + return this.selectQuery<{ width: number, height: number }>(`SELECT * FROM "actorImage" WHERE filename = :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) + setPluginVersion (pluginName: string, newVersion: string) { + return this.setPluginField(pluginName, 'version', newVersion) } - updateQuery (query: string) { - const seq = this.getSequelize() - const options = { type: QueryTypes.UPDATE as QueryTypes.UPDATE } + setPluginLatestVersion (pluginName: string, newVersion: string) { + return this.setPluginField(pluginName, 'latestVersion', newVersion) + } - return seq.query(query, options) + setPluginField (pluginName: string, field: string, value: string) { + return this.updateQuery( + `UPDATE "plugin" SET ${this.escapeColumnName(field)} = :value WHERE "name" = :pluginName`, + { pluginName, value } + ) } // --------------------------------------------------------------------------- - setPluginField (pluginName: string, field: string, value: string) { + selectQuery (query: string, replacements: { [id: string]: string | number } = {}) { const seq = this.getSequelize() + const options = { + type: QueryTypes.SELECT as QueryTypes.SELECT, + replacements + } - const options = { type: QueryTypes.UPDATE } - - return seq.query(`UPDATE "plugin" SET "${field}" = '${value}' WHERE "name" = '${pluginName}'`, options) + return seq.query(query, options) } - setPluginVersion (pluginName: string, newVersion: string) { - return this.setPluginField(pluginName, 'version', newVersion) - } + updateQuery (query: string, replacements: { [id: string]: string | number } = {}) { + const seq = this.getSequelize() + const options = { type: QueryTypes.UPDATE as QueryTypes.UPDATE, replacements } - setPluginLatestVersion (pluginName: string, newVersion: string) { - return this.setPluginField(pluginName, 'latestVersion', newVersion) + return seq.query(query, options) } // --------------------------------------------------------------------------- async getPlaylistInfohash (playlistId: number) { - const result = await this.selectQuery('SELECT "p2pMediaLoaderInfohashes" FROM "videoStreamingPlaylist" WHERE id = ' + playlistId) + const query = 'SELECT "p2pMediaLoaderInfohashes" FROM "videoStreamingPlaylist" WHERE id = :playlistId' + + const result = await this.selectQuery<{ p2pMediaLoaderInfohashes: string }>(query, { playlistId }) if (!result || result.length === 0) return [] return result[0].p2pMediaLoaderInfohashes @@ -116,19 +102,14 @@ export class SQLCommand extends AbstractCommand { // --------------------------------------------------------------------------- setActorFollowScores (newScore: number) { - const seq = this.getSequelize() - - const options = { type: QueryTypes.UPDATE } - - return seq.query(`UPDATE "actorFollow" SET "score" = ${newScore}`, options) + return this.updateQuery(`UPDATE "actorFollow" SET "score" = :newScore`, { newScore }) } 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) + return this.updateQuery( + `UPDATE "oAuthToken" SET ${this.escapeColumnName(field)} = :value WHERE "accessToken" = :accessToken`, + { value, accessToken } + ) } async cleanup () { @@ -157,4 +138,9 @@ export class SQLCommand extends AbstractCommand { return this.sequelize } + private escapeColumnName (columnName: string) { + return this.getSequelize().escape(columnName) + .replace(/^'/, '"') + .replace(/'$/, '"') + } } -- cgit v1.2.3