aboutsummaryrefslogtreecommitdiffhomepage
path: root/shared/server-commands/miscs/sql-command.ts
blob: 35cc2253f7d09ae2cff85637d9f13212fee29a16 (plain) (blame)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
import { QueryTypes, Sequelize } from 'sequelize'
import { forceNumber } from '@shared/core-utils'
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 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<{ fileUrl: string }>(`SELECT "fileUrl" FROM "videoFile" WHERE id = :fileId`, { fileId })
      .then(rows => rows[0].fileUrl)
  }

  setActorField (to: string, field: string, value: string) {
    return this.updateQuery(`UPDATE actor SET ${this.escapeColumnName(field)} = :value WHERE url = :to`, { value, to })
  }

  setVideoField (uuid: string, field: string, value: string) {
    return this.updateQuery(`UPDATE video SET ${this.escapeColumnName(field)} = :value WHERE uuid = :uuid`, { value, uuid })
  }

  setPlaylistField (uuid: string, field: string, value: string) {
    return this.updateQuery(`UPDATE "videoPlaylist" SET ${this.escapeColumnName(field)} = :value WHERE uuid = :uuid`, { value, uuid })
  }

  async countVideoViewsOf (uuid: string) {
    const query = 'SELECT SUM("videoView"."views") AS "total" FROM "videoView" ' +
      `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<{ width: number, height: number }>(`SELECT * FROM "actorImage" WHERE filename = :filename`, { filename })
      .then(rows => rows[0])
  }

  // ---------------------------------------------------------------------------

  setPluginVersion (pluginName: string, newVersion: string) {
    return this.setPluginField(pluginName, 'version', newVersion)
  }

  setPluginLatestVersion (pluginName: string, newVersion: string) {
    return this.setPluginField(pluginName, 'latestVersion', newVersion)
  }

  setPluginField (pluginName: string, field: string, value: string) {
    return this.updateQuery(
      `UPDATE "plugin" SET ${this.escapeColumnName(field)} = :value WHERE "name" = :pluginName`,
      { pluginName, value }
    )
  }

  // ---------------------------------------------------------------------------

  selectQuery <T extends object> (query: string, replacements: { [id: string]: string | number } = {}) {
    const seq = this.getSequelize()
    const options = {
      type: QueryTypes.SELECT as QueryTypes.SELECT,
      replacements
    }

    return seq.query<T>(query, options)
  }

  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)
  }

  // ---------------------------------------------------------------------------

  async getPlaylistInfohash (playlistId: number) {
    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
  }

  // ---------------------------------------------------------------------------

  setActorFollowScores (newScore: number) {
    return this.updateQuery(`UPDATE "actorFollow" SET "score" = :newScore`, { newScore })
  }

  setTokenField (accessToken: string, field: string, value: string) {
    return this.updateQuery(
      `UPDATE "oAuthToken" SET ${this.escapeColumnName(field)} = :value WHERE "accessToken" = :accessToken`,
      { value, accessToken }
    )
  }

  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 = '127.0.0.1'
    const port = 5432

    this.sequelize = new Sequelize(dbname, username, password, {
      dialect: 'postgres',
      host,
      port,
      logging: false
    })

    return this.sequelize
  }

  private escapeColumnName (columnName: string) {
    return this.getSequelize().escape(columnName)
      .replace(/^'/, '"')
      .replace(/'$/, '"')
  }
}