libdballe 9.6
postgresql.h
Go to the documentation of this file.
1
4#ifndef DBALLE_SQL_POSTGRESQL_H
5#define DBALLE_SQL_POSTGRESQL_H
6
7#include <dballe/sql/sql.h>
8#include <libpq-fe.h>
9#include <arpa/inet.h>
10#include <vector>
11#include <functional>
12#include <unordered_set>
13
14namespace dballe {
15namespace sql {
16
21{
22 std::string msg;
23
24 error_postgresql(PGconn* db, const std::string& msg);
25 error_postgresql(PGresult* db, const std::string& msg);
26 error_postgresql(const std::string& dbmsg, const std::string& msg);
27 ~error_postgresql() throw () {}
28
29 const char* what() const noexcept override { return msg.c_str(); }
30
31 static void throwf(PGconn* db, const char* fmt, ...) WREPORT_THROWF_ATTRS(2, 3);
32 static void throwf(PGresult* db, const char* fmt, ...) WREPORT_THROWF_ATTRS(2, 3);
33};
34
35namespace postgresql {
36
37int64_t encode_datetime(const Datetime& arg);
38int64_t encode_int64_t(int64_t arg);
39
41template<typename... ARGS> struct Params
42{
43 static const int count = sizeof...(ARGS);
44 const char* args[sizeof...(ARGS)];
45 int lengths[sizeof...(ARGS)];
46 int formats[sizeof...(ARGS)];
47 void* local[sizeof...(ARGS)];
48
49 Params(const ARGS&... args)
50 {
51 _add(0, args...);
52 }
53 ~Params()
54 {
55 for (auto& i: local)
56 free(i);
57 }
58
59 Params(const Params&) = delete;
60 Params(const Params&&) = delete;
61 Params& operator=(const Params&) = delete;
62 Params& operator=(const Params&&) = delete;
63
64protected:
66 void _add(unsigned pos)
67 {
68 }
69
71 template<typename... REST>
72 void _add(unsigned pos, std::nullptr_t arg, const REST&... rest)
73 {
74 local[pos] = nullptr;
75 args[pos] = nullptr;
76 lengths[pos] = 0;
77 formats[pos] = 0;
78 _add(pos + 1, rest...);
79 }
80
82 template<typename... REST>
83 void _add(unsigned pos, int32_t arg, const REST&... rest)
84 {
85 local[pos] = malloc(sizeof(int32_t));
86 *(int32_t*)local[pos] = (int32_t)htonl((uint32_t)arg);
87 args[pos] = (const char*)local[pos];
88 lengths[pos] = sizeof(int32_t);
89 formats[pos] = 1;
90 _add(pos + 1, rest...);
91 }
92
94 template<typename... REST>
95 void _add(unsigned pos, uint64_t arg, const REST&... rest)
96 {
97 local[pos] = malloc(sizeof(int64_t));
98 *(int64_t*)local[pos] = encode_int64_t(arg);
99 args[pos] = (const char*)local[pos];
100 lengths[pos] = sizeof(int64_t);
101 formats[pos] = 1;
102 _add(pos + 1, rest...);
103 }
104
106 template<typename... REST>
107 void _add(unsigned pos, const char* arg, const REST&... rest)
108 {
109 local[pos] = nullptr;
110 args[pos] = arg;
111 lengths[pos] = 0;
112 formats[pos] = 0;
113 _add(pos + 1, rest...);
114 }
115
117 template<typename... REST>
118 void _add(unsigned pos, const std::string& arg, const REST&... rest)
119 {
120 local[pos] = nullptr;
121 args[pos] = arg.data();
122 lengths[pos] = arg.size();
123 formats[pos] = 0;
124 _add(pos + 1, rest...);
125 }
126
128 template<typename... REST>
129 void _add(unsigned pos, const std::vector<uint8_t>& arg, const REST&... rest)
130 {
131 local[pos] = nullptr;
132 args[pos] = (const char*)arg.data();
133 lengths[pos] = arg.size();
134 formats[pos] = 1;
135 _add(pos + 1, rest...);
136 }
137
139 template<typename... REST>
140 void _add(unsigned pos, const Datetime& arg, const REST&... rest)
141 {
142 local[pos] = malloc(sizeof(int64_t));
143 *(int64_t*)local[pos] = encode_datetime(arg);
144 args[pos] = (const char*)local[pos];
145 lengths[pos] = sizeof(int64_t);
146 formats[pos] = 1;
147 _add(pos + 1, rest...);
148 }
149};
150
152struct Result
153{
154 PGresult* res;
155
156 Result() : res(nullptr) {}
157 Result(PGresult* res) : res(res) {}
158 ~Result() { PQclear(res); }
159
161 Result(Result&& o) : res(o.res) { o.res = nullptr; }
162 Result& operator=(Result&& o)
163 {
164 if (this == &o) return *this;
165 PQclear(res);
166 res = o.res;
167 o.res = nullptr;
168 return *this;
169 }
170
171 operator bool() const { return res != nullptr; }
172 operator PGresult*() { return res; }
173 operator const PGresult*() const { return res; }
174
176 void expect_no_data(const std::string& query);
177
179 void expect_result(const std::string& query);
180
182 void expect_one_row(const std::string& query);
183
185 void expect_success(const std::string& query);
186
188 unsigned rowcount() const { return PQntuples(res); }
189
191 bool is_null(unsigned row, unsigned col) const
192 {
193 return PQgetisnull(res, row, col);
194 }
195
197 bool get_bool(unsigned row, unsigned col) const
198 {
199 char* val = PQgetvalue(res, row, col);
200 return *val;
201 }
202
204 uint16_t get_int2(unsigned row, unsigned col) const
205 {
206 char* val = PQgetvalue(res, row, col);
207 return ntohs(*(uint16_t*)val);
208 }
209
211 uint32_t get_int4(unsigned row, unsigned col) const
212 {
213 char* val = PQgetvalue(res, row, col);
214 return ntohl(*(uint32_t*)val);
215 }
216
218 uint64_t get_int8(unsigned row, unsigned col) const;
219
221 std::vector<uint8_t> get_bytea(unsigned row, unsigned col) const;
222
224 const char* get_string(unsigned row, unsigned col) const
225 {
226 return PQgetvalue(res, row, col);
227 }
228
230 Datetime get_timestamp(unsigned row, unsigned col) const;
231
232 // Prevent copy
233 Result(const Result&) = delete;
234 Result& operator=(const Result&) = delete;
235};
236
237}
238
239
242{
243protected:
245 PGconn* db = nullptr;
246 std::unordered_set<std::string> prepared_names;
248 bool forked = false;
249
250protected:
251 void init_after_connect();
252
254
255 void fork_prepare() override;
256 void fork_parent() override;
257 void fork_child() override;
258
259 void check_connection();
260
261public:
265
266 PostgreSQLConnection& operator=(const PostgreSQLConnection&) = delete;
267
268 static std::shared_ptr<PostgreSQLConnection> create();
269
270 operator PGconn*() { return db; }
271
278 void open_url(const std::string& connection_string);
279 void open_test();
280
281 std::unique_ptr<Transaction> transaction(bool readonly=false) override;
282
284 void prepare(const std::string& name, const std::string& query);
285
286 postgresql::Result exec_unchecked(const char* query)
287 {
288 check_connection();
289 auto res = PQexecParams(db, query, 0, nullptr, nullptr, nullptr, nullptr, 1);
290 if (!res)
291 throw error_postgresql(db, std::string("cannot execute query ") + query);
292 return res;
293 }
294
295 postgresql::Result exec_unchecked(const std::string& query)
296 {
297 check_connection();
298 auto res = PQexecParams(db, query.c_str(), 0, nullptr, nullptr, nullptr, nullptr, 1);
299 if (!res)
300 throw error_postgresql(db, "cannot execute query " + query);
301 return res;
302 }
303
304 template<typename STRING>
305 void exec_no_data(STRING query)
306 {
307 postgresql::Result res(exec_unchecked(query));
308 res.expect_no_data(query);
309 }
310
311 template<typename STRING>
312 postgresql::Result exec(STRING query)
313 {
314 postgresql::Result res(exec_unchecked(query));
315 res.expect_result(query);
316 return res;
317 }
318
319 template<typename STRING>
320 postgresql::Result exec_one_row(STRING query)
321 {
322 postgresql::Result res(exec_unchecked(query));
323 res.expect_one_row(query);
324 return res;
325 }
326
327 template<typename ...ARGS>
328 postgresql::Result exec_unchecked(const char* query, ARGS... args)
329 {
330 check_connection();
331 postgresql::Params<ARGS...> params(args...);
332 auto res = PQexecParams(db, query, params.count, nullptr, params.args, params.lengths, params.formats, 1);
333 if (!res)
334 throw error_postgresql(db, std::string("cannot execute query ") + query);
335 return res;
336 }
337
338 template<typename ...ARGS>
339 postgresql::Result exec_unchecked(const std::string& query, ARGS... args)
340 {
341 check_connection();
342 postgresql::Params<ARGS...> params(args...);
343 auto res = PQexecParams(db, query.c_str(), params.count, nullptr, params.args, params.lengths, params.formats, 1);
344 if (!res)
345 throw error_postgresql(db, "cannot execute query " + query);
346 return res;
347 }
348
349 template<typename STRING, typename ...ARGS>
350 void exec_no_data(STRING query, ARGS... args)
351 {
352 postgresql::Result res(exec_unchecked(query, args...));
353 res.expect_no_data(query);
354 }
355
356 template<typename STRING, typename ...ARGS>
357 postgresql::Result exec(STRING query, ARGS... args)
358 {
359 postgresql::Result res(exec_unchecked(query, args...));
360 res.expect_result(query);
361 return res;
362 }
363
364 template<typename STRING, typename ...ARGS>
365 postgresql::Result exec_one_row(STRING query, ARGS... args)
366 {
367 postgresql::Result res(exec_unchecked(query, args...));
368 res.expect_one_row(query);
369 return res;
370 }
371
372 postgresql::Result exec_prepared_unchecked(const char* name)
373 {
374 check_connection();
375 auto res = PQexecPrepared(db, name, 0, nullptr, nullptr, nullptr, 1);
376 if (!res)
377 throw error_postgresql(db, std::string("cannot execute prepared query ") + name);
378 return res;
379 }
380
381 postgresql::Result exec_prepared_unchecked(const std::string& name)
382 {
383 check_connection();
384 auto res = PQexecPrepared(db, name.c_str(), 0, nullptr, nullptr, nullptr, 1);
385 if (!res)
386 throw error_postgresql(db, "cannot execute prepared query " + name);
387 return res;
388 }
389
390 template<typename STRING>
391 void exec_prepared_no_data(STRING name)
392 {
393 postgresql::Result res(exec_prepared_unchecked(name));
394 res.expect_no_data(name);
395 }
396
397 template<typename STRING>
398 postgresql::Result exec_prepared(STRING name)
399 {
400 postgresql::Result res(exec_prepared_unchecked(name));
401 res.expect_result(name);
402 return res;
403 }
404
405 template<typename STRING>
406 postgresql::Result exec_prepared_one_row(STRING name)
407 {
408 postgresql::Result res(exec_prepared_unchecked(name));
409 res.expect_one_row(name);
410 return res;
411 }
412
413 template<typename ...ARGS>
414 postgresql::Result exec_prepared_unchecked(const char* name, ARGS... args)
415 {
416 postgresql::Params<ARGS...> params(args...);
417 return PQexecPrepared(db, name, params.count, params.args, params.lengths, params.formats, 1);
418 }
419
420 template<typename ...ARGS>
421 postgresql::Result exec_prepared_unchecked(const std::string& name, ARGS... args)
422 {
423 postgresql::Params<ARGS...> params(args...);
424 return PQexecPrepared(db, name.c_str(), params.count, params.args, params.lengths, params.formats, 1);
425 }
426
427 template<typename STRING, typename ...ARGS>
428 void exec_prepared_no_data(STRING name, ARGS... args)
429 {
430 postgresql::Result res(exec_prepared_unchecked(name, args...));
431 res.expect_no_data(name);
432 }
433
434 template<typename STRING, typename ...ARGS>
435 postgresql::Result exec_prepared(STRING name, ARGS... args)
436 {
437 postgresql::Result res(exec_prepared_unchecked(name, args...));
438 res.expect_result(name);
439 return res;
440 }
441
442 template<typename STRING, typename ...ARGS>
443 postgresql::Result exec_prepared_one_row(STRING name, ARGS... args)
444 {
445 postgresql::Result res(exec_prepared_unchecked(name, args...));
446 res.expect_one_row(name);
447 return res;
448 }
449
452
455
456 bool has_table(const std::string& name) override;
457 std::string get_setting(const std::string& key) override;
458 void set_setting(const std::string& key, const std::string& value) override;
459 void drop_settings() override;
460 void execute(const std::string& query) override;
461 void explain(const std::string& query, FILE* out) override;
462
466 void drop_table_if_exists(const char* name);
467
469 int changes();
470
472 void pqexec(const std::string& query);
473
480 void pqexec_nothrow(const std::string& query) noexcept;
481
483 void run_single_row_mode(const std::string& query_desc, std::function<void(const postgresql::Result&)> dest);
484
486 void append_escaped(Querybuf& qb, const char* str);
487
489 void append_escaped(Querybuf& qb, const std::string& str);
490
492 void append_escaped(Querybuf& qb, const std::vector<uint8_t>& buf);
493};
494
495}
496}
497#endif
498
Definition: sql.h:53
Database connection.
Definition: postgresql.h:242
void drop_table_if_exists(const char *name)
Delete a table in the database if it exists, otherwise do nothing.
void prepare(const std::string &name, const std::string &query)
Precompile a query.
void append_escaped(Querybuf &qb, const char *str)
Escape the string as a literal value and append it to qb.
void run_single_row_mode(const std::string &query_desc, std::function< void(const postgresql::Result &)> dest)
Retrieve query results in single row mode.
void pqexec_nothrow(const std::string &query) noexcept
Wrap PQexec but do not throw an exception in case of errors.
void pqexec(const std::string &query)
Wrap PQexec.
std::unique_ptr< Transaction > transaction(bool readonly=false) override
Begin a transaction.
void set_setting(const std::string &key, const std::string &value) override
Set a value in the settings table.
int changes()
Count the number of rows modified by the last query that was run.
bool forked
Marker to catch attempts to reuse connections in forked processes.
Definition: postgresql.h:248
PGconn * db
Database connection.
Definition: postgresql.h:245
void open_url(const std::string &connection_string)
Connect to PostgreSQL using a connection URI.
void cancel_running_query_nothrow() noexcept
Send a cancellation command to the server.
void discard_all_input_nothrow() noexcept
Discard all input from an asynchronous request.
void explain(const std::string &query, FILE *out) override
Format and print the EXPLAIN output for the query to the given file.
void drop_settings() override
Drop the settings table.
bool has_table(const std::string &name) override
Check if the database contains a table.
void execute(const std::string &query) override
Execute a query without reading its results.
std::string get_setting(const std::string &key) override
Get a value from the settings table.
#define WREPORT_THROWF_ATTRS(a, b)
Common infrastructure for talking with SQL databases.
Date and time.
Definition: types.h:165
Error in case of failed database operations.
Definition: error.h:22
String buffer for composing database queries.
Definition: querybuf.h:16
Report an PostgreSQL error.
Definition: postgresql.h:21
Argument list for PQexecParams built at compile time.
Definition: postgresql.h:42
void _add(unsigned pos, const std::string &arg, const REST &... rest)
Fill in the argument structures.
Definition: postgresql.h:118
void _add(unsigned pos, const char *arg, const REST &... rest)
Fill in the argument structures.
Definition: postgresql.h:107
void _add(unsigned pos, int32_t arg, const REST &... rest)
Fill in the argument structures.
Definition: postgresql.h:83
void _add(unsigned pos)
Terminating condition for compile-time arg expansion.
Definition: postgresql.h:66
void _add(unsigned pos, const std::vector< uint8_t > &arg, const REST &... rest)
Fill in the argument structures.
Definition: postgresql.h:129
void _add(unsigned pos, std::nullptr_t arg, const REST &... rest)
Fill in the argument structures.
Definition: postgresql.h:72
void _add(unsigned pos, uint64_t arg, const REST &... rest)
Fill in the argument structures.
Definition: postgresql.h:95
void _add(unsigned pos, const Datetime &arg, const REST &... rest)
Fill in the argument structures.
Definition: postgresql.h:140
Wrap a PGresult, taking care of its memory management.
Definition: postgresql.h:153
const char * get_string(unsigned row, unsigned col) const
Return a result value, transmitted as a string.
Definition: postgresql.h:224
uint32_t get_int4(unsigned row, unsigned col) const
Return a result value, transmitted in binary as a 4 bit integer.
Definition: postgresql.h:211
bool get_bool(unsigned row, unsigned col) const
Return a result value, transmitted in binary as a byte (?)
Definition: postgresql.h:197
uint64_t get_int8(unsigned row, unsigned col) const
Return a result value, transmitted in binary as an 8 bit integer.
void expect_success(const std::string &query)
Check that the result was successful.
uint16_t get_int2(unsigned row, unsigned col) const
Return a result value, transmitted in binary as a 2 bit integer.
Definition: postgresql.h:204
Result(Result &&o)
Implement move.
Definition: postgresql.h:161
void expect_no_data(const std::string &query)
Check that the result successfully returned no data.
std::vector< uint8_t > get_bytea(unsigned row, unsigned col) const
Return a result value, transmitted in binary as an 8 bit integer.
void expect_result(const std::string &query)
Check that the result successfully returned some (possibly empty) data.
void expect_one_row(const std::string &query)
Check that the result successfully returned one row of data.
Datetime get_timestamp(unsigned row, unsigned col) const
Return a result value, transmitted as a timestamp without timezone.
unsigned rowcount() const
Get the number of rows in the result.
Definition: postgresql.h:188
bool is_null(unsigned row, unsigned col) const
Check if a result value is null.
Definition: postgresql.h:191