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 
14 namespace dballe {
15 namespace sql {
16 
20 struct error_postgresql : public error_db
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 
35 namespace postgresql {
36 
37 int64_t encode_datetime(const Datetime& arg);
38 int64_t encode_int64_t(int64_t arg);
39 
41 template<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 
64 protected:
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 
152 struct 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 {
243 protected:
245  PGconn* db = nullptr;
246  std::unordered_set<std::string> prepared_names;
248  bool forked = false;
249 
250 protected:
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 
261 public:
263  PostgreSQLConnection(const PostgreSQLConnection&&) = delete;
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 
454  void discard_all_input_nothrow() noexcept;
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.
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.
std::unique_ptr< Transaction > transaction(bool readonly=false) override
Begin a transaction.
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
std::vector< uint8_t > get_bytea(unsigned row, unsigned col) const
Return a result value, transmitted in binary as an 8 bit integer.
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.
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.
const char * get_string(unsigned row, unsigned col) const
Return a result value, transmitted as a string.
Definition: postgresql.h:224
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