aboutsummaryrefslogtreecommitdiffhomepage
path: root/shared/server-commands
diff options
context:
space:
mode:
Diffstat (limited to 'shared/server-commands')
-rw-r--r--shared/server-commands/miscs/sql-command.ts102
1 files changed, 44 insertions, 58 deletions
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 {
13 return seq.query(`DELETE FROM "${table}"`, options) 13 return seq.query(`DELETE FROM "${table}"`, options)
14 } 14 }
15 15
16 async getCount (table: string) { 16 async getVideoShareCount () {
17 const seq = this.getSequelize() 17 const [ { total } ] = await this.selectQuery<{ total: string }>(`SELECT COUNT(*) as total FROM "videoShare"`)
18
19 const options = { type: QueryTypes.SELECT as QueryTypes.SELECT }
20
21 const [ { total } ] = await seq.query<{ total: string }>(`SELECT COUNT(*) as total FROM "${table}"`, options)
22 if (total === null) return 0 18 if (total === null) return 0
23 19
24 return parseInt(total, 10) 20 return parseInt(total, 10)
25 } 21 }
26 22
27 async getInternalFileUrl (fileId: number) { 23 async getInternalFileUrl (fileId: number) {
28 return this.selectQuery(`SELECT "fileUrl" FROM "videoFile" WHERE id = ${fileId}`) 24 return this.selectQuery<{ fileUrl: string }>(`SELECT "fileUrl" FROM "videoFile" WHERE id = :fileId`, { fileId })
29 .then(rows => rows[0].fileUrl as string) 25 .then(rows => rows[0].fileUrl)
30 } 26 }
31 27
32 setActorField (to: string, field: string, value: string) { 28 setActorField (to: string, field: string, value: string) {
33 const seq = this.getSequelize() 29 return this.updateQuery(`UPDATE actor SET ${this.escapeColumnName(field)} = :value WHERE url = :to`, { value, to })
34
35 const options = { type: QueryTypes.UPDATE }
36
37 return seq.query(`UPDATE actor SET "${field}" = '${value}' WHERE url = '${to}'`, options)
38 } 30 }
39 31
40 setVideoField (uuid: string, field: string, value: string) { 32 setVideoField (uuid: string, field: string, value: string) {
41 const seq = this.getSequelize() 33 return this.updateQuery(`UPDATE video SET ${this.escapeColumnName(field)} = :value WHERE uuid = :uuid`, { value, uuid })
42
43 const options = { type: QueryTypes.UPDATE }
44
45 return seq.query(`UPDATE video SET "${field}" = '${value}' WHERE uuid = '${uuid}'`, options)
46 } 34 }
47 35
48 setPlaylistField (uuid: string, field: string, value: string) { 36 setPlaylistField (uuid: string, field: string, value: string) {
49 const seq = this.getSequelize() 37 return this.updateQuery(`UPDATE "videoPlaylist" SET ${this.escapeColumnName(field)} = :value WHERE uuid = :uuid`, { value, uuid })
50
51 const options = { type: QueryTypes.UPDATE }
52
53 return seq.query(`UPDATE "videoPlaylist" SET "${field}" = '${value}' WHERE uuid = '${uuid}'`, options)
54 } 38 }
55 39
56 async countVideoViewsOf (uuid: string) { 40 async countVideoViewsOf (uuid: string) {
57 const seq = this.getSequelize()
58
59 const query = 'SELECT SUM("videoView"."views") AS "total" FROM "videoView" ' + 41 const query = 'SELECT SUM("videoView"."views") AS "total" FROM "videoView" ' +
60 `INNER JOIN "video" ON "video"."id" = "videoView"."videoId" WHERE "video"."uuid" = '${uuid}'` 42 `INNER JOIN "video" ON "video"."id" = "videoView"."videoId" WHERE "video"."uuid" = :uuid`
61
62 const options = { type: QueryTypes.SELECT as QueryTypes.SELECT }
63 const [ { total } ] = await seq.query<{ total: number }>(query, options)
64 43
44 const [ { total } ] = await this.selectQuery<{ total: number }>(query, { uuid })
65 if (!total) return 0 45 if (!total) return 0
66 46
67 return forceNumber(total) 47 return forceNumber(total)
68 } 48 }
69 49
70 getActorImage (filename: string) { 50 getActorImage (filename: string) {
71 return this.selectQuery(`SELECT * FROM "actorImage" WHERE filename = '${filename}'`) 51 return this.selectQuery<{ width: number, height: number }>(`SELECT * FROM "actorImage" WHERE filename = :filename`, { filename })
72 .then(rows => rows[0]) 52 .then(rows => rows[0])
73 } 53 }
74 54
75 selectQuery (query: string) { 55 // ---------------------------------------------------------------------------
76 const seq = this.getSequelize()
77 const options = { type: QueryTypes.SELECT as QueryTypes.SELECT }
78 56
79 return seq.query<any>(query, options) 57 setPluginVersion (pluginName: string, newVersion: string) {
58 return this.setPluginField(pluginName, 'version', newVersion)
80 } 59 }
81 60
82 updateQuery (query: string) { 61 setPluginLatestVersion (pluginName: string, newVersion: string) {
83 const seq = this.getSequelize() 62 return this.setPluginField(pluginName, 'latestVersion', newVersion)
84 const options = { type: QueryTypes.UPDATE as QueryTypes.UPDATE } 63 }
85 64
86 return seq.query(query, options) 65 setPluginField (pluginName: string, field: string, value: string) {
66 return this.updateQuery(
67 `UPDATE "plugin" SET ${this.escapeColumnName(field)} = :value WHERE "name" = :pluginName`,
68 { pluginName, value }
69 )
87 } 70 }
88 71
89 // --------------------------------------------------------------------------- 72 // ---------------------------------------------------------------------------
90 73
91 setPluginField (pluginName: string, field: string, value: string) { 74 selectQuery <T extends object> (query: string, replacements: { [id: string]: string | number } = {}) {
92 const seq = this.getSequelize() 75 const seq = this.getSequelize()
76 const options = {
77 type: QueryTypes.SELECT as QueryTypes.SELECT,
78 replacements
79 }
93 80
94 const options = { type: QueryTypes.UPDATE } 81 return seq.query<T>(query, options)
95
96 return seq.query(`UPDATE "plugin" SET "${field}" = '${value}' WHERE "name" = '${pluginName}'`, options)
97 } 82 }
98 83
99 setPluginVersion (pluginName: string, newVersion: string) { 84 updateQuery (query: string, replacements: { [id: string]: string | number } = {}) {
100 return this.setPluginField(pluginName, 'version', newVersion) 85 const seq = this.getSequelize()
101 } 86 const options = { type: QueryTypes.UPDATE as QueryTypes.UPDATE, replacements }
102 87
103 setPluginLatestVersion (pluginName: string, newVersion: string) { 88 return seq.query(query, options)
104 return this.setPluginField(pluginName, 'latestVersion', newVersion)
105 } 89 }
106 90
107 // --------------------------------------------------------------------------- 91 // ---------------------------------------------------------------------------
108 92
109 async getPlaylistInfohash (playlistId: number) { 93 async getPlaylistInfohash (playlistId: number) {
110 const result = await this.selectQuery('SELECT "p2pMediaLoaderInfohashes" FROM "videoStreamingPlaylist" WHERE id = ' + playlistId) 94 const query = 'SELECT "p2pMediaLoaderInfohashes" FROM "videoStreamingPlaylist" WHERE id = :playlistId'
95
96 const result = await this.selectQuery<{ p2pMediaLoaderInfohashes: string }>(query, { playlistId })
111 if (!result || result.length === 0) return [] 97 if (!result || result.length === 0) return []
112 98
113 return result[0].p2pMediaLoaderInfohashes 99 return result[0].p2pMediaLoaderInfohashes
@@ -116,19 +102,14 @@ export class SQLCommand extends AbstractCommand {
116 // --------------------------------------------------------------------------- 102 // ---------------------------------------------------------------------------
117 103
118 setActorFollowScores (newScore: number) { 104 setActorFollowScores (newScore: number) {
119 const seq = this.getSequelize() 105 return this.updateQuery(`UPDATE "actorFollow" SET "score" = :newScore`, { newScore })
120
121 const options = { type: QueryTypes.UPDATE }
122
123 return seq.query(`UPDATE "actorFollow" SET "score" = ${newScore}`, options)
124 } 106 }
125 107
126 setTokenField (accessToken: string, field: string, value: string) { 108 setTokenField (accessToken: string, field: string, value: string) {
127 const seq = this.getSequelize() 109 return this.updateQuery(
128 110 `UPDATE "oAuthToken" SET ${this.escapeColumnName(field)} = :value WHERE "accessToken" = :accessToken`,
129 const options = { type: QueryTypes.UPDATE } 111 { value, accessToken }
130 112 )
131 return seq.query(`UPDATE "oAuthToken" SET "${field}" = '${value}' WHERE "accessToken" = '${accessToken}'`, options)
132 } 113 }
133 114
134 async cleanup () { 115 async cleanup () {
@@ -157,4 +138,9 @@ export class SQLCommand extends AbstractCommand {
157 return this.sequelize 138 return this.sequelize
158 } 139 }
159 140
141 private escapeColumnName (columnName: string) {
142 return this.getSequelize().escape(columnName)
143 .replace(/^'/, '"')
144 .replace(/'$/, '"')
145 }
160} 146}