X-Git-Url: https://git.immae.eu/?a=blobdiff_plain;f=shared%2Fserver-commands%2Fmiscs%2Fsql-command.ts;h=35cc2253f7d09ae2cff85637d9f13212fee29a16;hb=2fe978744e5b74eb824e4d79c1bb9b840169f125;hp=dbc441abab1e075114f2ccc431b82ac82036e06f;hpb=2732eeff9e6994582293b5aaa0cb158b7e272e9e;p=github%2FChocobozzz%2FPeerTube.git

diff --git a/shared/server-commands/miscs/sql-command.ts b/shared/server-commands/miscs/sql-command.ts
index dbc441aba..35cc2253f 100644
--- a/shared/server-commands/miscs/sql-command.ts
+++ b/shared/server-commands/miscs/sql-command.ts
@@ -13,109 +13,103 @@ 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<any>(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 }
-
-    return seq.query(query, options)
+  setPluginLatestVersion (pluginName: string, newVersion: string) {
+    return this.setPluginField(pluginName, 'latestVersion', newVersion)
   }
 
   setPluginField (pluginName: string, field: string, value: string) {
-    const seq = this.getSequelize()
+    return this.updateQuery(
+      `UPDATE "plugin" SET ${this.escapeColumnName(field)} = :value WHERE "name" = :pluginName`,
+      { pluginName, value }
+    )
+  }
 
-    const options = { type: QueryTypes.UPDATE }
+  // ---------------------------------------------------------------------------
 
-    return seq.query(`UPDATE "plugin" SET "${field}" = '${value}' WHERE "name" = '${pluginName}'`, options)
-  }
+  selectQuery <T extends object> (query: string, replacements: { [id: string]: string | number } = {}) {
+    const seq = this.getSequelize()
+    const options = {
+      type: QueryTypes.SELECT as QueryTypes.SELECT,
+      replacements
+    }
 
-  setPluginVersion (pluginName: string, newVersion: string) {
-    return this.setPluginField(pluginName, 'version', newVersion)
+    return seq.query<T>(query, options)
   }
 
-  setPluginLatestVersion (pluginName: string, newVersion: string) {
-    return this.setPluginField(pluginName, 'latestVersion', newVersion)
+  updateQuery (query: string, replacements: { [id: string]: string | number } = {}) {
+    const seq = this.getSequelize()
+    const options = { type: QueryTypes.UPDATE as QueryTypes.UPDATE, replacements }
+
+    return seq.query(query, options)
   }
 
-  setActorFollowScores (newScore: number) {
-    const seq = this.getSequelize()
+  // ---------------------------------------------------------------------------
 
-    const options = { type: QueryTypes.UPDATE }
+  async getPlaylistInfohash (playlistId: number) {
+    const query = 'SELECT "p2pMediaLoaderInfohashes" FROM "videoStreamingPlaylist" WHERE id = :playlistId'
 
-    return seq.query(`UPDATE "actorFollow" SET "score" = ${newScore}`, options)
+    const result = await this.selectQuery<{ p2pMediaLoaderInfohashes: string }>(query, { playlistId })
+    if (!result || result.length === 0) return []
+
+    return result[0].p2pMediaLoaderInfohashes
   }
 
-  setTokenField (accessToken: string, field: string, value: string) {
-    const seq = this.getSequelize()
+  // ---------------------------------------------------------------------------
 
-    const options = { type: QueryTypes.UPDATE }
+  setActorFollowScores (newScore: number) {
+    return this.updateQuery(`UPDATE "actorFollow" SET "score" = :newScore`, { newScore })
+  }
 
-    return seq.query(`UPDATE "oAuthToken" SET "${field}" = '${value}' WHERE "accessToken" = '${accessToken}'`, options)
+  setTokenField (accessToken: string, field: string, value: string) {
+    return this.updateQuery(
+      `UPDATE "oAuthToken" SET ${this.escapeColumnName(field)} = :value WHERE "accessToken" = :accessToken`,
+      { value, accessToken }
+    )
   }
 
   async cleanup () {
@@ -144,4 +138,9 @@ export class SQLCommand extends AbstractCommand {
     return this.sequelize
   }
 
+  private escapeColumnName (columnName: string) {
+    return this.getSequelize().escape(columnName)
+      .replace(/^'/, '"')
+      .replace(/'$/, '"')
+  }
 }