diff options
author | Chocobozzz <me@florianbigard.com> | 2021-07-09 15:37:43 +0200 |
---|---|---|
committer | Chocobozzz <me@florianbigard.com> | 2021-07-20 15:27:18 +0200 |
commit | 9293139fde7091e9badcafa9b570b83cea9a10ad (patch) | |
tree | 9728412f02af4aa59d2c016db47624251e8f6be6 /shared/extra-utils | |
parent | 078f17e6d90376050f43ce639e88e11869b49ee7 (diff) | |
download | PeerTube-9293139fde7091e9badcafa9b570b83cea9a10ad.tar.gz PeerTube-9293139fde7091e9badcafa9b570b83cea9a10ad.tar.zst PeerTube-9293139fde7091e9badcafa9b570b83cea9a10ad.zip |
Introduce sql command
Diffstat (limited to 'shared/extra-utils')
-rw-r--r-- | shared/extra-utils/miscs/index.ts | 2 | ||||
-rw-r--r-- | shared/extra-utils/miscs/sql-command.ts | 142 | ||||
-rw-r--r-- | shared/extra-utils/miscs/sql.ts | 161 | ||||
-rw-r--r-- | shared/extra-utils/miscs/stubs.ts | 7 | ||||
-rw-r--r-- | shared/extra-utils/server/servers.ts | 10 | ||||
-rw-r--r-- | shared/extra-utils/shared/abstract-command.ts | 2 |
6 files changed, 152 insertions, 172 deletions
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 @@ | |||
1 | export * from './miscs' | 1 | export * from './miscs' |
2 | export * from './sql' | 2 | export * from './sql-command' |
3 | export * from './stubs' | 3 | 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 @@ | |||
1 | import { QueryTypes, Sequelize } from 'sequelize' | ||
2 | import { AbstractCommand } from '../shared' | ||
3 | |||
4 | export class SQLCommand extends AbstractCommand { | ||
5 | private sequelize: Sequelize | ||
6 | |||
7 | deleteAll (table: string) { | ||
8 | const seq = this.getSequelize() | ||
9 | |||
10 | const options = { type: QueryTypes.DELETE } | ||
11 | |||
12 | return seq.query(`DELETE FROM "${table}"`, options) | ||
13 | } | ||
14 | |||
15 | async getCount (table: string) { | ||
16 | const seq = this.getSequelize() | ||
17 | |||
18 | const options = { type: QueryTypes.SELECT as QueryTypes.SELECT } | ||
19 | |||
20 | const [ { total } ] = await seq.query<{ total: string }>(`SELECT COUNT(*) as total FROM "${table}"`, options) | ||
21 | if (total === null) return 0 | ||
22 | |||
23 | return parseInt(total, 10) | ||
24 | } | ||
25 | |||
26 | setActorField (to: string, field: string, value: string) { | ||
27 | const seq = this.getSequelize() | ||
28 | |||
29 | const options = { type: QueryTypes.UPDATE } | ||
30 | |||
31 | return seq.query(`UPDATE actor SET "${field}" = '${value}' WHERE url = '${to}'`, options) | ||
32 | } | ||
33 | |||
34 | setVideoField (uuid: string, field: string, value: string) { | ||
35 | const seq = this.getSequelize() | ||
36 | |||
37 | const options = { type: QueryTypes.UPDATE } | ||
38 | |||
39 | return seq.query(`UPDATE video SET "${field}" = '${value}' WHERE uuid = '${uuid}'`, options) | ||
40 | } | ||
41 | |||
42 | setPlaylistField (uuid: string, field: string, value: string) { | ||
43 | const seq = this.getSequelize() | ||
44 | |||
45 | const options = { type: QueryTypes.UPDATE } | ||
46 | |||
47 | return seq.query(`UPDATE "videoPlaylist" SET "${field}" = '${value}' WHERE uuid = '${uuid}'`, options) | ||
48 | } | ||
49 | |||
50 | async countVideoViewsOf (uuid: string) { | ||
51 | const seq = this.getSequelize() | ||
52 | |||
53 | // tslint:disable | ||
54 | const query = 'SELECT SUM("videoView"."views") AS "total" FROM "videoView" ' + | ||
55 | `INNER JOIN "video" ON "video"."id" = "videoView"."videoId" WHERE "video"."uuid" = '${uuid}'` | ||
56 | |||
57 | const options = { type: QueryTypes.SELECT as QueryTypes.SELECT } | ||
58 | const [ { total } ] = await seq.query<{ total: number }>(query, options) | ||
59 | |||
60 | if (!total) return 0 | ||
61 | |||
62 | return parseInt(total + '', 10) | ||
63 | } | ||
64 | |||
65 | getActorImage (filename: string) { | ||
66 | return this.selectQuery(`SELECT * FROM "actorImage" WHERE filename = '${filename}'`) | ||
67 | .then(rows => rows[0]) | ||
68 | } | ||
69 | |||
70 | selectQuery (query: string) { | ||
71 | const seq = this.getSequelize() | ||
72 | const options = { type: QueryTypes.SELECT as QueryTypes.SELECT } | ||
73 | |||
74 | return seq.query<any>(query, options) | ||
75 | } | ||
76 | |||
77 | updateQuery (query: string) { | ||
78 | const seq = this.getSequelize() | ||
79 | const options = { type: QueryTypes.UPDATE as QueryTypes.UPDATE } | ||
80 | |||
81 | return seq.query(query, options) | ||
82 | } | ||
83 | |||
84 | setPluginField (pluginName: string, field: string, value: string) { | ||
85 | const seq = this.getSequelize() | ||
86 | |||
87 | const options = { type: QueryTypes.UPDATE } | ||
88 | |||
89 | return seq.query(`UPDATE "plugin" SET "${field}" = '${value}' WHERE "name" = '${pluginName}'`, options) | ||
90 | } | ||
91 | |||
92 | setPluginVersion (pluginName: string, newVersion: string) { | ||
93 | return this.setPluginField(pluginName, 'version', newVersion) | ||
94 | } | ||
95 | |||
96 | setPluginLatestVersion (pluginName: string, newVersion: string) { | ||
97 | return this.setPluginField(pluginName, 'latestVersion', newVersion) | ||
98 | } | ||
99 | |||
100 | setActorFollowScores (newScore: number) { | ||
101 | const seq = this.getSequelize() | ||
102 | |||
103 | const options = { type: QueryTypes.UPDATE } | ||
104 | |||
105 | return seq.query(`UPDATE "actorFollow" SET "score" = ${newScore}`, options) | ||
106 | } | ||
107 | |||
108 | setTokenField (accessToken: string, field: string, value: string) { | ||
109 | const seq = this.getSequelize() | ||
110 | |||
111 | const options = { type: QueryTypes.UPDATE } | ||
112 | |||
113 | return seq.query(`UPDATE "oAuthToken" SET "${field}" = '${value}' WHERE "accessToken" = '${accessToken}'`, options) | ||
114 | } | ||
115 | |||
116 | async cleanup () { | ||
117 | if (!this.sequelize) return | ||
118 | |||
119 | await this.sequelize.close() | ||
120 | this.sequelize = undefined | ||
121 | } | ||
122 | |||
123 | private getSequelize () { | ||
124 | if (this.sequelize) return this.sequelize | ||
125 | |||
126 | const dbname = 'peertube_test' + this.server.internalServerNumber | ||
127 | const username = 'peertube' | ||
128 | const password = 'peertube' | ||
129 | const host = 'localhost' | ||
130 | const port = 5432 | ||
131 | |||
132 | this.sequelize = new Sequelize(dbname, username, password, { | ||
133 | dialect: 'postgres', | ||
134 | host, | ||
135 | port, | ||
136 | logging: false | ||
137 | }) | ||
138 | |||
139 | return this.sequelize | ||
140 | } | ||
141 | |||
142 | } | ||
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 @@ | |||
1 | import { QueryTypes, Sequelize } from 'sequelize' | ||
2 | import { ServerInfo } from '../server/servers' | ||
3 | |||
4 | const sequelizes: { [ id: number ]: Sequelize } = {} | ||
5 | |||
6 | function getSequelize (internalServerNumber: number) { | ||
7 | if (sequelizes[internalServerNumber]) return sequelizes[internalServerNumber] | ||
8 | |||
9 | const dbname = 'peertube_test' + internalServerNumber | ||
10 | const username = 'peertube' | ||
11 | const password = 'peertube' | ||
12 | const host = 'localhost' | ||
13 | const port = 5432 | ||
14 | |||
15 | const seq = new Sequelize(dbname, username, password, { | ||
16 | dialect: 'postgres', | ||
17 | host, | ||
18 | port, | ||
19 | logging: false | ||
20 | }) | ||
21 | |||
22 | sequelizes[internalServerNumber] = seq | ||
23 | |||
24 | return seq | ||
25 | } | ||
26 | |||
27 | function deleteAll (internalServerNumber: number, table: string) { | ||
28 | const seq = getSequelize(internalServerNumber) | ||
29 | |||
30 | const options = { type: QueryTypes.DELETE } | ||
31 | |||
32 | return seq.query(`DELETE FROM "${table}"`, options) | ||
33 | } | ||
34 | |||
35 | async function getCount (internalServerNumber: number, table: string) { | ||
36 | const seq = getSequelize(internalServerNumber) | ||
37 | |||
38 | const options = { type: QueryTypes.SELECT as QueryTypes.SELECT } | ||
39 | |||
40 | const [ { total } ] = await seq.query<{ total: string }>(`SELECT COUNT(*) as total FROM "${table}"`, options) | ||
41 | if (total === null) return 0 | ||
42 | |||
43 | return parseInt(total, 10) | ||
44 | } | ||
45 | |||
46 | function setActorField (internalServerNumber: number, to: string, field: string, value: string) { | ||
47 | const seq = getSequelize(internalServerNumber) | ||
48 | |||
49 | const options = { type: QueryTypes.UPDATE } | ||
50 | |||
51 | return seq.query(`UPDATE actor SET "${field}" = '${value}' WHERE url = '${to}'`, options) | ||
52 | } | ||
53 | |||
54 | function setVideoField (internalServerNumber: number, uuid: string, field: string, value: string) { | ||
55 | const seq = getSequelize(internalServerNumber) | ||
56 | |||
57 | const options = { type: QueryTypes.UPDATE } | ||
58 | |||
59 | return seq.query(`UPDATE video SET "${field}" = '${value}' WHERE uuid = '${uuid}'`, options) | ||
60 | } | ||
61 | |||
62 | function setPlaylistField (internalServerNumber: number, uuid: string, field: string, value: string) { | ||
63 | const seq = getSequelize(internalServerNumber) | ||
64 | |||
65 | const options = { type: QueryTypes.UPDATE } | ||
66 | |||
67 | return seq.query(`UPDATE "videoPlaylist" SET "${field}" = '${value}' WHERE uuid = '${uuid}'`, options) | ||
68 | } | ||
69 | |||
70 | async function countVideoViewsOf (internalServerNumber: number, uuid: string) { | ||
71 | const seq = getSequelize(internalServerNumber) | ||
72 | |||
73 | // tslint:disable | ||
74 | const query = 'SELECT SUM("videoView"."views") AS "total" FROM "videoView" ' + | ||
75 | `INNER JOIN "video" ON "video"."id" = "videoView"."videoId" WHERE "video"."uuid" = '${uuid}'` | ||
76 | |||
77 | const options = { type: QueryTypes.SELECT as QueryTypes.SELECT } | ||
78 | const [ { total } ] = await seq.query<{ total: number }>(query, options) | ||
79 | |||
80 | if (!total) return 0 | ||
81 | |||
82 | return parseInt(total + '', 10) | ||
83 | } | ||
84 | |||
85 | function getActorImage (internalServerNumber: number, filename: string) { | ||
86 | return selectQuery(internalServerNumber, `SELECT * FROM "actorImage" WHERE filename = '${filename}'`) | ||
87 | .then(rows => rows[0]) | ||
88 | } | ||
89 | |||
90 | function selectQuery (internalServerNumber: number, query: string) { | ||
91 | const seq = getSequelize(internalServerNumber) | ||
92 | const options = { type: QueryTypes.SELECT as QueryTypes.SELECT } | ||
93 | |||
94 | return seq.query<any>(query, options) | ||
95 | } | ||
96 | |||
97 | function updateQuery (internalServerNumber: number, query: string) { | ||
98 | const seq = getSequelize(internalServerNumber) | ||
99 | const options = { type: QueryTypes.UPDATE as QueryTypes.UPDATE } | ||
100 | |||
101 | return seq.query(query, options) | ||
102 | } | ||
103 | |||
104 | async function closeAllSequelize (servers: ServerInfo[]) { | ||
105 | for (const server of servers) { | ||
106 | if (sequelizes[server.internalServerNumber]) { | ||
107 | await sequelizes[server.internalServerNumber].close() | ||
108 | // eslint-disable-next-line | ||
109 | delete sequelizes[server.internalServerNumber] | ||
110 | } | ||
111 | } | ||
112 | } | ||
113 | |||
114 | function setPluginField (internalServerNumber: number, pluginName: string, field: string, value: string) { | ||
115 | const seq = getSequelize(internalServerNumber) | ||
116 | |||
117 | const options = { type: QueryTypes.UPDATE } | ||
118 | |||
119 | return seq.query(`UPDATE "plugin" SET "${field}" = '${value}' WHERE "name" = '${pluginName}'`, options) | ||
120 | } | ||
121 | |||
122 | function setPluginVersion (internalServerNumber: number, pluginName: string, newVersion: string) { | ||
123 | return setPluginField(internalServerNumber, pluginName, 'version', newVersion) | ||
124 | } | ||
125 | |||
126 | function setPluginLatestVersion (internalServerNumber: number, pluginName: string, newVersion: string) { | ||
127 | return setPluginField(internalServerNumber, pluginName, 'latestVersion', newVersion) | ||
128 | } | ||
129 | |||
130 | function setActorFollowScores (internalServerNumber: number, newScore: number) { | ||
131 | const seq = getSequelize(internalServerNumber) | ||
132 | |||
133 | const options = { type: QueryTypes.UPDATE } | ||
134 | |||
135 | return seq.query(`UPDATE "actorFollow" SET "score" = ${newScore}`, options) | ||
136 | } | ||
137 | |||
138 | function setTokenField (internalServerNumber: number, accessToken: string, field: string, value: string) { | ||
139 | const seq = getSequelize(internalServerNumber) | ||
140 | |||
141 | const options = { type: QueryTypes.UPDATE } | ||
142 | |||
143 | return seq.query(`UPDATE "oAuthToken" SET "${field}" = '${value}' WHERE "accessToken" = '${accessToken}'`, options) | ||
144 | } | ||
145 | |||
146 | export { | ||
147 | setVideoField, | ||
148 | setPlaylistField, | ||
149 | setActorField, | ||
150 | countVideoViewsOf, | ||
151 | setPluginVersion, | ||
152 | setPluginLatestVersion, | ||
153 | selectQuery, | ||
154 | getActorImage, | ||
155 | deleteAll, | ||
156 | setTokenField, | ||
157 | updateQuery, | ||
158 | setActorFollowScores, | ||
159 | closeAllSequelize, | ||
160 | getCount | ||
161 | } | ||
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 { | |||
2 | return { } | 2 | return { } |
3 | } | 3 | } |
4 | 4 | ||
5 | function buildResponseStub (): any { | ||
6 | return { | ||
7 | locals: {} | ||
8 | } | ||
9 | } | ||
10 | |||
11 | export { | 5 | export { |
12 | buildResponseStub, | ||
13 | buildRequestStub | 6 | buildRequestStub |
14 | } | 7 | } |
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 { | |||
30 | ServicesCommand, | 30 | ServicesCommand, |
31 | StreamingPlaylistsCommand | 31 | StreamingPlaylistsCommand |
32 | } from '../videos' | 32 | } from '../videos' |
33 | import { SQLCommand } from '../miscs' | ||
33 | import { CommentsCommand } from '../videos/comments-command' | 34 | import { CommentsCommand } from '../videos/comments-command' |
34 | import { ConfigCommand } from './config-command' | 35 | import { ConfigCommand } from './config-command' |
35 | import { ContactFormCommand } from './contact-form-command' | 36 | import { ContactFormCommand } from './contact-form-command' |
@@ -123,6 +124,7 @@ interface ServerInfo { | |||
123 | streamingPlaylistsCommand?: StreamingPlaylistsCommand | 124 | streamingPlaylistsCommand?: StreamingPlaylistsCommand |
124 | channelsCommand?: ChannelsCommand | 125 | channelsCommand?: ChannelsCommand |
125 | commentsCommand?: CommentsCommand | 126 | commentsCommand?: CommentsCommand |
127 | sqlCommand?: SQLCommand | ||
126 | } | 128 | } |
127 | 129 | ||
128 | function parallelTests () { | 130 | function parallelTests () { |
@@ -367,6 +369,7 @@ function assignCommands (server: ServerInfo) { | |||
367 | server.streamingPlaylistsCommand = new StreamingPlaylistsCommand(server) | 369 | server.streamingPlaylistsCommand = new StreamingPlaylistsCommand(server) |
368 | server.channelsCommand = new ChannelsCommand(server) | 370 | server.channelsCommand = new ChannelsCommand(server) |
369 | server.commentsCommand = new CommentsCommand(server) | 371 | server.commentsCommand = new CommentsCommand(server) |
372 | server.sqlCommand = new SQLCommand(server) | ||
370 | } | 373 | } |
371 | 374 | ||
372 | async function reRunServer (server: ServerInfo, configOverride?: any) { | 375 | async function reRunServer (server: ServerInfo, configOverride?: any) { |
@@ -398,17 +401,20 @@ async function checkDirectoryIsEmpty (server: ServerInfo, directory: string, exc | |||
398 | expect(filtered).to.have.lengthOf(0) | 401 | expect(filtered).to.have.lengthOf(0) |
399 | } | 402 | } |
400 | 403 | ||
401 | function killallServers (servers: ServerInfo[]) { | 404 | async function killallServers (servers: ServerInfo[]) { |
402 | for (const server of servers) { | 405 | for (const server of servers) { |
403 | if (!server.app) continue | 406 | if (!server.app) continue |
404 | 407 | ||
408 | await server.sqlCommand.cleanup() | ||
409 | |||
405 | process.kill(-server.app.pid) | 410 | process.kill(-server.app.pid) |
411 | |||
406 | server.app = null | 412 | server.app = null |
407 | } | 413 | } |
408 | } | 414 | } |
409 | 415 | ||
410 | async function cleanupTests (servers: ServerInfo[]) { | 416 | async function cleanupTests (servers: ServerInfo[]) { |
411 | killallServers(servers) | 417 | await killallServers(servers) |
412 | 418 | ||
413 | if (isGithubCI()) { | 419 | if (isGithubCI()) { |
414 | await ensureDir('artifacts') | 420 | 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 @@ | |||
1 | import { isAbsolute, join } from 'path' | 1 | import { isAbsolute, join } from 'path' |
2 | import { HttpStatusCode } from '@shared/core-utils' | 2 | import { HttpStatusCode } from '@shared/core-utils' |
3 | import { root } from '../miscs' | 3 | import { root } from '../miscs/miscs' |
4 | import { | 4 | import { |
5 | makeDeleteRequest, | 5 | makeDeleteRequest, |
6 | makeGetRequest, | 6 | makeGetRequest, |