libdballe  7.29
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 
13 namespace dballe {
14 namespace sql {
15 
19 struct error_postgresql : public error_db
20 {
21  std::string msg;
22 
23  error_postgresql(PGconn* db, const std::string& msg);
24  error_postgresql(PGresult* db, const std::string& msg);
25  error_postgresql(const std::string& dbmsg, const std::string& msg);
26  ~error_postgresql() throw () {}
27 
28  const char* what() const noexcept override { return msg.c_str(); }
29 
30  static void throwf(PGconn* db, const char* fmt, ...) WREPORT_THROWF_ATTRS(2, 3);
31  static void throwf(PGresult* db, const char* fmt, ...) WREPORT_THROWF_ATTRS(2, 3);
32 };
33 
34 namespace postgresql {
35 
36 int64_t encode_datetime(const Datetime& arg);
37 int64_t encode_int64_t(int64_t arg);
38 
40 template<typename... ARGS> struct Params
41 {
42  static const int count = sizeof...(ARGS);
43  const char* args[sizeof...(ARGS)];
44  int lengths[sizeof...(ARGS)];
45  int formats[sizeof...(ARGS)];
46  void* local[sizeof...(ARGS)];
47 
48  Params(const ARGS&... args)
49  {
50  _add(0, args...);
51  }
52  ~Params()
53  {
54  for (auto& i: local)
55  free(i);
56  }
57 
58  Params(const Params&) = delete;
59  Params(const Params&&) = delete;
60  Params& operator=(const Params&) = delete;
61  Params& operator=(const Params&&) = delete;
62 
63 protected:
65  void _add(unsigned pos)
66  {
67  }
68 
70  template<typename... REST>
71  void _add(unsigned pos, std::nullptr_t arg, const REST&... rest)
72  {
73  local[pos] = nullptr;
74  args[pos] = nullptr;
75  lengths[pos] = 0;
76  formats[pos] = 0;
77  _add(pos + 1, rest...);
78  }
79 
81  template<typename... REST>
82  void _add(unsigned pos, int32_t arg, const REST&... rest)
83  {
84  local[pos] = malloc(sizeof(int32_t));
85  *(int32_t*)local[pos] = (int32_t)htonl((uint32_t)arg);
86  args[pos] = (const char*)local[pos];
87  lengths[pos] = sizeof(int32_t);
88  formats[pos] = 1;
89  _add(pos + 1, rest...);
90  }
91 
93  template<typename... REST>
94  void _add(unsigned pos, uint64_t arg, const REST&... rest)
95  {
96  local[pos] = malloc(sizeof(int64_t));
97  *(int64_t*)local[pos] = encode_int64_t(arg);
98  args[pos] = (const char*)local[pos];
99  lengths[pos] = sizeof(int64_t);
100  formats[pos] = 1;
101  _add(pos + 1, rest...);
102  }
103 
105  template<typename... REST>
106  void _add(unsigned pos, const char* arg, const REST&... rest)
107  {
108  local[pos] = nullptr;
109  args[pos] = arg;
110  lengths[pos] = 0;
111  formats[pos] = 0;
112  _add(pos + 1, rest...);
113  }
114 
116  template<typename... REST>
117  void _add(unsigned pos, const std::string& arg, const REST&... rest)
118  {
119  local[pos] = nullptr;
120  args[pos] = arg.data();
121  lengths[pos] = arg.size();
122  formats[pos] = 0;
123  _add(pos + 1, rest...);
124  }
125 
127  template<typename... REST>
128  void _add(unsigned pos, const std::vector<uint8_t>& arg, const REST&... rest)
129  {
130  local[pos] = nullptr;
131  args[pos] = (const char*)arg.data();
132  lengths[pos] = arg.size();
133  formats[pos] = 1;
134  _add(pos + 1, rest...);
135  }
136 
138  template<typename... REST>
139  void _add(unsigned pos, const Datetime& arg, const REST&... rest)
140  {
141  local[pos] = malloc(sizeof(int64_t));
142  *(int64_t*)local[pos] = encode_datetime(arg);
143  args[pos] = (const char*)local[pos];
144  lengths[pos] = sizeof(int64_t);
145  formats[pos] = 1;
146  _add(pos + 1, rest...);
147  }
148 };
149 
151 struct Result
152 {
153  PGresult* res;
154 
155  Result() : res(nullptr) {}
156  Result(PGresult* res) : res(res) {}
157  ~Result() { PQclear(res); }
158 
160  Result(Result&& o) : res(o.res) { o.res = nullptr; }
161  Result& operator=(Result&& o)
162  {
163  if (this == &o) return *this;
164  PQclear(res);
165  res = o.res;
166  o.res = nullptr;
167  return *this;
168  }
169 
170  operator bool() const { return res != nullptr; }
171  operator PGresult*() { return res; }
172  operator const PGresult*() const { return res; }
173 
175  void expect_no_data(const std::string& query);
176 
178  void expect_result(const std::string& query);
179 
181  void expect_one_row(const std::string& query);
182 
184  void expect_success(const std::string& query);
185 
187  unsigned rowcount() const { return PQntuples(res); }
188 
190  bool is_null(unsigned row, unsigned col) const
191  {
192  return PQgetisnull(res, row, col);
193  }
194 
196  bool get_bool(unsigned row, unsigned col) const
197  {
198  char* val = PQgetvalue(res, row, col);
199  return *val;
200  }
201 
203  uint16_t get_int2(unsigned row, unsigned col) const
204  {
205  char* val = PQgetvalue(res, row, col);
206  return ntohs(*(uint16_t*)val);
207  }
208 
210  uint32_t get_int4(unsigned row, unsigned col) const
211  {
212  char* val = PQgetvalue(res, row, col);
213  return ntohl(*(uint32_t*)val);
214  }
215 
217  uint64_t get_int8(unsigned row, unsigned col) const;
218 
220  std::vector<uint8_t> get_bytea(unsigned row, unsigned col) const;
221 
223  const char* get_string(unsigned row, unsigned col) const
224  {
225  return PQgetvalue(res, row, col);
226  }
227 
229  Datetime get_timestamp(unsigned row, unsigned col) const;
230 
231  // Prevent copy
232  Result(const Result&) = delete;
233  Result& operator=(const Result&) = delete;
234 };
235 
236 }
237 
238 
241 {
242 protected:
244  PGconn* db = nullptr;
245 
246 protected:
247  void init_after_connect();
248 
249 public:
252  PostgreSQLConnection(const PostgreSQLConnection&&) = delete;
254 
255  PostgreSQLConnection& operator=(const PostgreSQLConnection&) = delete;
256 
257  operator PGconn*() { return db; }
258 
265  void open_url(const std::string& connection_string);
266  void open_test();
267 
268  std::unique_ptr<Transaction> transaction() override;
269 
271  void prepare(const std::string& name, const std::string& query);
272 
273  postgresql::Result exec_unchecked(const char* query)
274  {
275  if (profile) ++profile_query_count;
276  return PQexecParams(db, query, 0, nullptr, nullptr, nullptr, nullptr, 1);
277  }
278 
279  postgresql::Result exec_unchecked(const std::string& query)
280  {
281  if (profile) ++profile_query_count;
282  return PQexecParams(db, query.c_str(), 0, nullptr, nullptr, nullptr, nullptr, 1);
283  }
284 
285  template<typename STRING>
286  void exec_no_data(STRING query)
287  {
288  postgresql::Result res(exec_unchecked(query));
289  res.expect_no_data(query);
290  }
291 
292  template<typename STRING>
293  postgresql::Result exec(STRING query)
294  {
295  postgresql::Result res(exec_unchecked(query));
296  res.expect_result(query);
297  return res;
298  }
299 
300  template<typename STRING>
301  postgresql::Result exec_one_row(STRING query)
302  {
303  postgresql::Result res(exec_unchecked(query));
304  res.expect_one_row(query);
305  return res;
306  }
307 
308  template<typename ...ARGS>
309  postgresql::Result exec_unchecked(const char* query, ARGS... args)
310  {
311  postgresql::Params<ARGS...> params(args...);
312  if (profile) ++profile_query_count;
313  return PQexecParams(db, query, params.count, nullptr, params.args, params.lengths, params.formats, 1);
314  }
315 
316  template<typename ...ARGS>
317  postgresql::Result exec_unchecked(const std::string& query, ARGS... args)
318  {
319  postgresql::Params<ARGS...> params(args...);
320  if (profile) ++profile_query_count;
321  return PQexecParams(db, query.c_str(), params.count, nullptr, params.args, params.lengths, params.formats, 1);
322  }
323 
324  template<typename STRING, typename ...ARGS>
325  void exec_no_data(STRING query, ARGS... args)
326  {
327  postgresql::Result res(exec_unchecked(query, args...));
328  res.expect_no_data(query);
329  }
330 
331  template<typename STRING, typename ...ARGS>
332  postgresql::Result exec(STRING query, ARGS... args)
333  {
334  postgresql::Result res(exec_unchecked(query, args...));
335  res.expect_result(query);
336  return res;
337  }
338 
339  template<typename STRING, typename ...ARGS>
340  postgresql::Result exec_one_row(STRING query, ARGS... args)
341  {
342  postgresql::Result res(exec_unchecked(query, args...));
343  res.expect_one_row(query);
344  return res;
345  }
346 
347  postgresql::Result exec_prepared_unchecked(const char* name)
348  {
349  if (profile) ++profile_query_count;
350  return PQexecPrepared(db, name, 0, nullptr, nullptr, nullptr, 1);
351  }
352 
353  postgresql::Result exec_prepared_unchecked(const std::string& name)
354  {
355  if (profile) ++profile_query_count;
356  return PQexecPrepared(db, name.c_str(), 0, nullptr, nullptr, nullptr, 1);
357  }
358 
359  template<typename STRING>
360  void exec_prepared_no_data(STRING name)
361  {
362  postgresql::Result res(exec_prepared_unchecked(name));
363  res.expect_no_data(name);
364  }
365 
366  template<typename STRING>
367  postgresql::Result exec_prepared(STRING name)
368  {
369  postgresql::Result res(exec_prepared_unchecked(name));
370  res.expect_result(name);
371  return res;
372  }
373 
374  template<typename STRING>
375  postgresql::Result exec_prepared_one_row(STRING name)
376  {
377  postgresql::Result res(exec_prepared_unchecked(name));
378  res.expect_one_row(name);
379  return res;
380  }
381 
382  template<typename ...ARGS>
383  postgresql::Result exec_prepared_unchecked(const char* name, ARGS... args)
384  {
385  postgresql::Params<ARGS...> params(args...);
386  if (profile) ++profile_query_count;
387  return PQexecPrepared(db, name, params.count, params.args, params.lengths, params.formats, 1);
388  }
389 
390  template<typename ...ARGS>
391  postgresql::Result exec_prepared_unchecked(const std::string& name, ARGS... args)
392  {
393  postgresql::Params<ARGS...> params(args...);
394  if (profile) ++profile_query_count;
395  return PQexecPrepared(db, name.c_str(), params.count, params.args, params.lengths, params.formats, 1);
396  }
397 
398  template<typename STRING, typename ...ARGS>
399  void exec_prepared_no_data(STRING name, ARGS... args)
400  {
401  postgresql::Result res(exec_prepared_unchecked(name, args...));
402  res.expect_no_data(name);
403  }
404 
405  template<typename STRING, typename ...ARGS>
406  postgresql::Result exec_prepared(STRING name, ARGS... args)
407  {
408  postgresql::Result res(exec_prepared_unchecked(name, args...));
409  res.expect_result(name);
410  return res;
411  }
412 
413  template<typename STRING, typename ...ARGS>
414  postgresql::Result exec_prepared_one_row(STRING name, ARGS... args)
415  {
416  postgresql::Result res(exec_prepared_unchecked(name, args...));
417  res.expect_one_row(name);
418  return res;
419  }
420 
422  void cancel_running_query_nothrow() noexcept;
423 
425  void discard_all_input_nothrow() noexcept;
426 
427  bool has_table(const std::string& name) override;
428  std::string get_setting(const std::string& key) override;
429  void set_setting(const std::string& key, const std::string& value) override;
430  void drop_settings() override;
431  void execute(const std::string& query) override;
432  void explain(const std::string& query, FILE* out) override;
433 
437  void drop_table_if_exists(const char* name);
438 
440  int changes();
441 
443  void pqexec(const std::string& query);
444 
451  void pqexec_nothrow(const std::string& query) noexcept;
452 
454  void run_single_row_mode(const std::string& query_desc, std::function<void(const postgresql::Result&)> dest);
455 
457  void append_escaped(Querybuf& qb, const char* str);
458 
460  void append_escaped(Querybuf& qb, const std::string& str);
461 
463  void append_escaped(Querybuf& qb, const std::vector<uint8_t>& buf);
464 };
465 
466 }
467 }
468 #endif
469 
void open_url(const std::string &connection_string)
Connect to PostgreSQL using a connection URI.
Database connection.
Definition: postgresql.h:240
Result(Result &&o)
Implement move.
Definition: postgresql.h:160
void _add(unsigned pos, const char *arg, const REST &...rest)
Fill in the argument structures.
Definition: postgresql.h:106
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:40
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 is_null(unsigned row, unsigned col) const
Check if a result value is null.
Definition: postgresql.h:190
std::unique_ptr< Transaction > transaction() override
Begin a transaction.
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.
void _add(unsigned pos)
Terminating condition for compile-time arg expansion.
Definition: postgresql.h:65
std::vector< uint8_t > get_bytea(unsigned row, unsigned col) const
Return a result value, transmitted in binary as an 8 bit integer.
uint64_t get_int8(unsigned row, unsigned col) const
Return a result value, transmitted in binary as an 8 bit integer.
void _add(unsigned pos, const Datetime &arg, const REST &...rest)
Fill in the argument structures.
Definition: postgresql.h:139
void prepare(const std::string &name, const std::string &query)
Precompile a query.
uint16_t get_int2(unsigned row, unsigned col) const
Return a result value, transmitted in binary as a 2 bit integer.
Definition: postgresql.h:203
void pqexec(const std::string &query)
Wrap PQexec.
void _add(unsigned pos, const std::vector< uint8_t > &arg, const REST &...rest)
Fill in the argument structures.
Definition: postgresql.h:128
Datetime get_timestamp(unsigned row, unsigned col) const
Return a result value, transmitted as a timestamp without timezone.
Definition: sql.h:59
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:151
void append_escaped(Querybuf &qb, const char *str)
Escape the string as a literal value and append it to qb.
bool get_bool(unsigned row, unsigned col) const
Return a result value, transmitted in binary as a byte (?)
Definition: postgresql.h:196
uint32_t get_int4(unsigned row, unsigned col) const
Return a result value, transmitted in binary as a 4 bit integer.
Definition: postgresql.h:210
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:19
Common infrastructure for talking with SQL databases.
Error in case of failed database operations.
Definition: error.h:21
unsigned rowcount() const
Get the number of rows in the result.
Definition: postgresql.h:187
PGconn * db
Database connection.
Definition: postgresql.h:244
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.
Date and time.
Definition: types.h:158
void _add(unsigned pos, std::nullptr_t arg, const REST &...rest)
Fill in the argument structures.
Definition: postgresql.h:71
void _add(unsigned pos, uint64_t arg, const REST &...rest)
Fill in the argument structures.
Definition: postgresql.h:94
void drop_settings() override
Drop the settings table.
#define WREPORT_THROWF_ATTRS(a, b)
const char * get_string(unsigned row, unsigned col) const
Return a result value, transmitted as a string.
Definition: postgresql.h:223
void execute(const std::string &query) override
Execute a query without reading its results.
void _add(unsigned pos, const std::string &arg, const REST &...rest)
Fill in the argument structures.
Definition: postgresql.h:117
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.
void _add(unsigned pos, int32_t arg, const REST &...rest)
Fill in the argument structures.
Definition: postgresql.h:82