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(/'$/, '"')
}
}
|
