libdballe  9.11
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 
451  void cancel_running_query_nothrow() noexcept;
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 
void open_url(const std::string &connection_string)
Connect to PostgreSQL using a connection URI.
Database connection.
Definition: postgresql.h:241
Result(Result &&o)
Implement move.
Definition: postgresql.h:161
void set_setting(const std::string &key, const std::string &value) override
Set a value in the settings table.
Argument list for PQexecParams built at compile time.
Definition: postgresql.h:41
void _add(unsigned pos, const char *arg, const REST &... rest)
Fill in the argument structures.
Definition: postgresql.h:107
void _add(unsigned pos, uint64_t arg, const REST &... rest)
Fill in the argument structures.
Definition: postgresql.h:95
void run_single_row_mode(const std::string &query_desc, std::function< void(const postgresql::Result &)> dest)
Retrieve query results in single row mode.
bool forked
Marker to catch attempts to reuse connections in forked processes.
Definition: postgresql.h:248
int changes()
Count the number of rows modified by the last query that was run.
void cancel_running_query_nothrow() noexcept
Send a cancellation command to the server.
bool has_table(const std::string &name) override
Check if the database contains a table.
unsigned rowcount() const
Get the number of rows in the result.
Definition: postgresql.h:188
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
Definition: utils.h:31
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
Definition: cmdline.h:18
void prepare(const std::string &name, const std::string &query)
Precompile a query.
bool is_null(unsigned row, unsigned col) const
Check if a result value is null.
Definition: postgresql.h:191
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
void pqexec(const std::string &query)
Wrap PQexec.
Datetime get_timestamp(unsigned row, unsigned col) const
Return a result value, transmitted as a timestamp without timezone.
Definition: sql.h:52
void pqexec_nothrow(const std::string &query) noexcept
Wrap PQexec but do not throw an exception in case of errors.
void expect_one_row(const std::string &query)
Check that the result successfully returned one row of data.
Wrap a PGresult, taking care of its memory management.
Definition: postgresql.h:152
bool get_bool(unsigned row, unsigned col) const
Return a result value, transmitted in binary as a byte (?)
Definition: postgresql.h:197
void _add(unsigned pos, int32_t arg, const REST &... rest)
Fill in the argument structures.
Definition: postgresql.h:83
void append_escaped(Querybuf &qb, const char *str)
Escape the string as a literal value and append it to qb.
void expect_result(const std::string &query)
Check that the result successfully returned some (possibly empty) data.
void expect_no_data(const std::string &query)
Check that the result successfully returned no data.
void drop_table_if_exists(const char *name)
Delete a table in the database if it exists, otherwise do nothing.
Report an PostgreSQL error.
Definition: postgresql.h:20
Common infrastructure for talking with SQL databases.
Error in case of failed database operations.
Definition: error.h:21
void _add(unsigned pos, const std::string &arg, const REST &... rest)
Fill in the argument structures.
Definition: postgresql.h:118
PGconn * db
Database connection.
Definition: postgresql.h:245
void discard_all_input_nothrow() noexcept
Discard all input from an asynchronous request.
void _add(unsigned pos, const Datetime &arg, const REST &... rest)
Fill in the argument structures.
Definition: postgresql.h:140
void explain(const std::string &query, FILE *out) override
Format and print the EXPLAIN output for the query to the given file.
Date and time.
Definition: types.h:164
std::unique_ptr< Transaction > transaction(bool readonly=false) override
Begin a transaction.
void drop_settings() override
Drop the settings table.
#define WREPORT_THROWF_ATTRS(a, b)
void _add(unsigned pos, std::nullptr_t arg, const REST &... rest)
Fill in the argument structures.
Definition: postgresql.h:72
const char * get_string(unsigned row, unsigned col) const
Return a result value, transmitted as a string.
Definition: postgresql.h:224
uint64_t get_int8(unsigned row, unsigned col) const
Return a result value, transmitted in binary as an 8 bit integer.
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.
void expect_success(const std::string &query)
Check that the result was successful.