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
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
|
/* eslint-disable no-unused-vars */
// Copyright (C) 2020 The Qt Company Ltd.
// SPDX-License-Identifier: LicenseRef-Qt-Commercial OR LGPL-3.0-only OR GPL-2.0-only OR GPL-3.0-only
exports.id = "postgreSQLClient";
const { Pool } = require("pg");
const jsonSql = require("json-sql")();
const safeJsonStringify = require("safe-json-stringify");
let config = require("./postgreSQLconfig.json");
const Logger = require("./logger");
const logger = new Logger();
jsonSql.configure({ namedValues: false });
jsonSql.setDialect("postgresql");
// Use DATABASE_URL environment variable if set. (Heroku environments)
// Otherwise, continue to use the config file.
if (process.env.DATABASE_URL)
config = { connectionString: process.env.DATABASE_URL, ssl: { rejectUnauthorized: false } }
logger.log(
`Connecting to PostgreSQL database with config: ${safeJsonStringify(config)}`,
"debug", "DATABASE"
);
const pool = new Pool(config);
exports.pool = pool;
pool.on("error", (err) => {
// This should be non-critical. The database will clean up idle clients.
logger.log(`An idle database client has experienced an error: ${err.stack}`, "error");
});
// Create our tables if they don't exist
pool.query(`CREATE TABLE IF NOT EXISTS processing_queue
(
uuid UUID PRIMARY KEY,
changeid TEXT,
state TEXT,
revision TEXT,
rawjson TEXT,
cherrypick_results_json TEXT,
pick_count_remaining INTEGER,
listener_cache TEXT
)
`);
pool.query(`CREATE TABLE IF NOT EXISTS retry_queue
(
uuid UUID PRIMARY KEY,
retryaction TEXT,
args TEXT
)
`);
// Exported functions
exports.end = end;
function end(callback) {
// Clean up the retry_queue. Restoring processes on next restart
// will retry any action that was in-process.
logger.log("cleaning up retry table entries...", undefined, "DATABASE");
pool.query(`DELETE FROM retry_queue`, (err, data) => {
logger.log(`Cleanup result: ${!err}`, undefined, "DATABASE");
logger.log("Waiting for PostgreSQL connections to close...", undefined, "DATABASE");
pool.end(() => {
logger.log("Database client pool has ended", undefined, "DATABASE");
callback(true);
});
});
}
exports.insert = insert;
function insert(table, columns, values, callback, processNextQueuedUpdate) {
let valuecount_string = "";
for (let i = 0; i < columns.length; i++) {
// Form a string like "$1,$2,$3,$4,$5,$6" based on count of columns.
valuecount_string += `$${i+1}${i < columns.length-1 ? ',' : ''}`; // No trailing comma.
}
const query = {
name: `insert-row-${table}`,
text: `INSERT INTO ${table}(${columns}) VALUES(${valuecount_string})`,
values: values
};
logger.log(`Running query: ${safeJsonStringify(query)}`, "silly", "DATABASE");
pool.query(query, function (err, data) {
if (err)
logger.log(`Database error: ${err.message}\n${Error().stack}`, "error", "DATABASE");
if (callback)
callback(!err, err || data);
if (processNextQueuedUpdate)
processNextQueuedUpdate(undefined, values.at(0), undefined, undefined, true);
});
}
exports.query = query;
function query(table, fields, keyName, keyValue, operator, callback, processNextQueuedUpdate) {
// null and explicit false are valid values, but other empty values are not.
const hasKeyValue = keyValue !== undefined && keyValue !== "";
const query = {
name: `query-${keyName}-${fields}`,
text: `SELECT ${fields || "*"} FROM ${table} WHERE ${keyName} ${
hasKeyValue ? operator + " $1" : operator
}`,
values: hasKeyValue ? [keyValue] : undefined
};
logger.log(`Running query: ${safeJsonStringify(query)}`, "silly", "DATABASE");
pool.query(query, (err, data) => {
if (err)
logger.log(`Database error: ${err}\nQuery: ${safeJsonStringify(query)}\n${Error().stack}`, "error", "DATABASE");
if (callback)
callback(!err, err || data.rows);
// If the queuing function was passed, call it with the unlock parameter.
// This will process the next item in queue or globally unlock the status
// update lockout.
if (processNextQueuedUpdate)
processNextQueuedUpdate(undefined, keyValue, undefined, undefined, true);
});
}
exports.update = update;
function update(table, keyName, keyValue, changes, callback, processNextQueuedUpdate) {
let sql = jsonSql.build({
type: "update", table: table,
condition: { [keyName]: keyValue },
modifier: { ...changes }
});
logger.log(`Running query: ${safeJsonStringify(sql)}`, "silly", "DATABASE");
pool.query(sql.query, sql.values, function (err, result) {
if (err) {
logger.log(
`Database error: ${err}\nQuery: ${safeJsonStringify(sql)}\n${Error().stack}`,
"error", "DATABASE"
);
}
if (callback)
callback(!err, err || result);
// If the queuing function was passed, call it with the unlock parameter.
// This will process the next item in queue or globally unlock the status
// update lockout.
if (processNextQueuedUpdate)
processNextQueuedUpdate(undefined, keyValue, undefined, undefined, true);
});
}
// Decrement a numeric key and return the new count.
exports.decrement = decrement;
function decrement(table, uuid, keyName, callback) {
let querystring = `UPDATE ${table} SET ${keyName} = ${keyName} - 1 WHERE uuid = '${uuid}' and ${
keyName} > 0`;
logger.log(`Running query: ${querystring}`, "silly", "DATABASE");
pool.query(querystring, function (err) {
if (err) {
logger.log(`Database error: ${err}\n${Error().stack}`, "error", uuid);
callback(false, err);
} else {
query(table, ["pick_count_remaining"], "uuid", uuid, "=", callback);
}
});
}
exports.deleteDBEntry = deleteDBEntry;
function deleteDBEntry(table, keyName, keyValue, callback) {
let sql = jsonSql.build({
type: "remove", table: table,
condition: { [keyName]: keyValue }
});
logger.log(`Running query: ${safeJsonStringify(sql)}`, "silly", "DATABASE");
pool.query(sql.query, sql.values, function (err) {
if (err) {
logger.log(
`An error occurred while running a query: ${safeJsonStringify(sql)}`,
"error", "DATABASE"
);
}
if (callback)
callback(!err, err || this);
});
}
|
