ROSE  0.11.98.0
DatabasePostgresql.h
1 // WARNING: Changes to this file must be contributed back to Sawyer or else they will
2 // be clobbered by the next update from Sawyer. The Sawyer repository is at
3 // https://github.com/matzke1/sawyer.
4 
5 
6 
7 
8 #ifndef Sawyer_DatabasePostgresql_H
9 #define Sawyer_DatabasePostgresql_H
10 
11 #if __cplusplus >= 201103L
12 
13 #include <Sawyer/Database.h>
14 
15 #include <boost/algorithm/string/predicate.hpp>
16 #include <boost/format.hpp>
17 #include <boost/lexical_cast.hpp>
18 #include <cctype>
19 #include <pqxx/pqxx>
20 
21 namespace Sawyer {
22 namespace Database {
23 
25 class Postgresql: public Connection {
26 public:
28  struct Locator {
29  std::string hostname;
30  std::string port;
31  std::string user;
32  std::string password;
33  std::string database;
34  };
35 
37  Postgresql() {}
38 
40  explicit Postgresql(const Locator &c) {
41  open(c);
42  }
43 
44  Postgresql& open(const Locator &c);
45 };
46 
47 // Only implementation details beyond this point -- no public APIs
48 namespace Detail {
49 
50 class PostgresqlStatement;
51 
53 // PostgreSQL connection details
55 
56 class PostgresqlConnection: public ConnectionBase {
57  friend class ::Sawyer::Database::Postgresql;
58  friend class ::Sawyer::Database::Detail::PostgresqlStatement;
59 
60  // pqxx::connection has deleted operator= and not defined swap, so in order to be able to close and re-open a connection
61  // we need to throw away the old connection and create a new one. Thus the use of pointers here.
62  std::unique_ptr<pqxx::connection> connection;
63  std::unique_ptr<pqxx::work> transaction;
64 
65 public:
66  ~PostgresqlConnection() {
67  close();
68  }
69 
70 private:
71  // See RFC 3986
72  std::string uriEscape(const std::string &s) {
73  std::string retval;
74  for (char ch: s) {
75  if (::isalnum(ch) || ::strchr("-_.~", ch)) {
76  retval += ch;
77  } else {
78  retval += (boost::format("%02X") % (unsigned)ch).str();
79  }
80  }
81  return retval;
82  }
83 
84  void open(const Postgresql::Locator &where) {
85  close();
86 
87  // Create the URI. See https://www.postgresql.org/docs/10/libpq-connect.html section 33.1.1.2
88  std::string uri = "postgresql://";
89  if (!where.user.empty() || !where.password.empty()) {
90  uri += uriEscape(where.user);
91  if (!where.password.empty())
92  uri += ":" + uriEscape(where.password);
93  uri += "@";
94  }
95  if (!where.hostname.empty())
96  uri += uriEscape(where.hostname);
97  if (!where.port.empty())
98  uri += ":" + uriEscape(where.port);
99  if (!where.database.empty())
100  uri += "/" + uriEscape(where.database);
101 
102  connection = std::unique_ptr<pqxx::connection>(new pqxx::connection(uri));
103  transaction = std::unique_ptr<pqxx::work>(new pqxx::work(*connection));
104  }
105 
106  void close() override {
107  if (connection && connection->is_open() && transaction)
108  transaction->commit();
109  transaction.reset();
110  connection.reset();
111  }
112 
113  std::string driverName() const override {
114  return "postgresql";
115  }
116 
117  Statement prepareStatement(const std::string &sql) override;
118 
119  size_t lastInsert() const override {
120  throw Exception("last inserted row ID not supported; suggestion: use UUIDs instead");
121  }
122 };
123 
125 // PostgreSQL statement details
127 
128 class PostgresqlStatement: public StatementBase {
129  friend class ::Sawyer::Database::Detail::PostgresqlConnection;
130 
131  std::string sql_; // SQL with "?" parameters
132  std::vector<std::string> pvalues_; // value for each "?" parameter
133  pqxx::result result_; // result of most recent query
134 
135 private:
136  PostgresqlStatement(const std::shared_ptr<ConnectionBase> &db, const std::string &sql)
137  : StatementBase(db) {
138  auto low = parseParameters(sql);
139  sql_ = low.first;
140  pvalues_.resize(low.second, "null");
141  }
142 
143  void unbindAllParams() override {
144  pvalues_.clear();
145  }
146 
147  void bindLow(size_t idx, int value) override {
148  pvalues_[idx] = boost::lexical_cast<std::string>(value);
149  }
150 
151  void bindLow(size_t idx, int64_t value) override {
152  pvalues_[idx] = boost::lexical_cast<std::string>(value);
153  }
154 
155  void bindLow(size_t idx, size_t value) override {
156  pvalues_[idx] = boost::lexical_cast<std::string>(value);
157  }
158 
159  void bindLow(size_t idx, double value) override {
160  pvalues_[idx] = boost::lexical_cast<std::string>(value);
161  }
162 
163  void bindLow(size_t idx, const std::string &value) override {
164 #if 0
165  // Commented out on 2021-09-29 but I didn't record the reason.
166  // The original version, or nearly so.
167  auto tx = std::dynamic_pointer_cast<PostgresqlConnection>(connection())->transaction.get();
168  pvalues_[idx] = "'" + tx->esc_raw(reinterpret_cast<const unsigned char*>(value.c_str()), value.size()) + "'";
169 #elif 0
170  // Commented out [Robb Matzke 2021-10-14] The "esc" function in behaves strangely: the characters are sign-extended to
171  // 4 bytes before escaping them to octal, which results in octal escapes like "\37777777742", which the sql parser then
172  // treates as "\377" followed by the characters "77777742" and then throws an exception "invalid byte sequence for
173  // encoding "UTF8": 0xff".
174  auto tx = std::dynamic_pointer_cast<PostgresqlConnection>(connection())->transaction.get();
175  pvalues_[idx] = "'" + tx->esc(value) + "'";
176 #else
177  // [Robb Matzke 2021-10-14] Implemented from PostgreSQL 14 official documentation
178  // https://www.postgresql.org/docs/current/sql-syntax-lexical.html
179  std::string s;
180  for (size_t i = 0; i < value.size(); ++i) {
181  // Postgresql's E'...' style quoting is called "C-Style Escapes" but according to the postfix documentation, not
182  // all C escape sequences are supported. For instance "\a" is not listed in the table and thus falls under the
183  // category "any other character following a backslash is taken literally." Same for "\v". So we'll use octal escapes
184  // for them. They're not common anyway, so doing so doesn't hamper readability much.
185  switch (value[i]) {
186  case '\b':
187  s += "\\b";
188  break;
189  case '\f':
190  s += "\\f";
191  break;
192  case '\n':
193  s += "\\n";
194  break;
195  case '\r':
196  s += "\\r";
197  break;
198  case '\t':
199  s += "\\t";
200  break;
201  case '\'':
202  s += "\\'";
203  break;
204  case '\\':
205  s += "\\\\";
206  break;
207  default:
208  if (::isgraph(value[i]) || ' ' == value[i]) {
209  s += value[i];
210  } else {
211  s += (boost::format("\\%03o") % (unsigned)(unsigned char)value[i]).str();
212  }
213  break;
214  }
215  }
216  pvalues_[idx] = (s != value ? "E'" : "'") + s + "'";
217 #endif
218  }
219 
220  void bindLow(size_t idx, const char *value) override {
221  auto tx = std::dynamic_pointer_cast<PostgresqlConnection>(connection())->transaction.get();
222  pvalues_[idx] = "'" + tx->esc(value) + "'";
223  }
224 
225  void bindLow(size_t idx, const std::vector<uint8_t> &value) override {
226  auto tx = std::dynamic_pointer_cast<PostgresqlConnection>(connection())->transaction.get();
227  auto data = static_cast<const unsigned char*>(value.data());
228  pvalues_[idx] = "'" + tx->esc_raw(data, value.size()) + "'";
229  }
230 
231  void bindLow(size_t idx, Nothing) override {
232  pvalues_[idx] = "null";
233  }
234 
235  Iterator beginLow() override {
236  // Expand the low-level "?" parameters into a new SQL string. The values are already escaped and quoted if necessary.
237  std::string sql;
238  size_t paramIdx = 0;
239  bool inQuote = false;
240  for (size_t i=0; i<sql_.size(); ++i) {
241  if ('\'' == sql_[i]) {
242  inQuote = !inQuote;
243  sql += sql_[i];
244  } else if ('?' == sql_[i] && !inQuote) {
245  ASSERT_require(paramIdx < pvalues_.size());
246  sql += pvalues_[paramIdx++];
247  } else {
248  sql += sql_[i];
249  }
250  }
251 
252  auto tx = std::dynamic_pointer_cast<PostgresqlConnection>(connection())->transaction.get();
253  result_ = tx->exec(sql);
254  if (result_.empty())
255  return Iterator();
256  return makeIterator();
257  }
258 
259  Iterator nextLow() override {
260  if (rowNumber() >= result_.size()) {
261  state(Statement::FINISHED);
262  return Iterator();
263  } else {
264  return makeIterator();
265  }
266  }
267 
268  size_t nColumns() const override {
269  return result_.columns();
270  }
271 
272  Sawyer::Optional<std::string> getString(size_t idx) override {
273  ASSERT_require(rowNumber() < result_.size());
274  ASSERT_require(idx < result_.columns());
275  if (result_[rowNumber()][boost::numeric_cast<int>(idx)].is_null()) {
276  return Nothing();
277  } else {
278  return unescapeRaw(result_[rowNumber()][boost::numeric_cast<int>(idx)].as<std::string>());
279  }
280  }
281 
282  static unsigned hex2int(char ch) {
283  if (::isdigit(ch))
284  return ch - '0';
285  if (ch >= 'a' && ch <= 'f')
286  return ch - 'a' + 10;
287  ASSERT_require(ch >= 'A' && ch <= 'F');
288  return ch - 'A' + 10;
289  }
290 
291  // Because pqxx::transaction_base::unesc_raw is not available yet
292  static std::string unescapeRaw(const std::string &s) {
293  if (boost::starts_with(s, "\\x") && s.size() % 2 == 0) {
294  std::string retval;
295  for (size_t i=2; i<s.size(); i+=2)
296  retval += static_cast<char>(hex2int(s[i])*16 + hex2int(s[i+1]));
297  return retval;
298  } else {
299  return s;
300  }
301  }
302 
303  Sawyer::Optional<std::vector<uint8_t>> getBlob(size_t idx) override {
304  ASSERT_require(rowNumber() < result_.size());
305  ASSERT_require(idx < result_.columns());
306  if (result_[rowNumber()][boost::numeric_cast<int>(idx)].is_null()) {
307  return Nothing();
308  } else {
309  std::string bytes = unescapeRaw(result_[rowNumber()][boost::numeric_cast<int>(idx)].as<std::string>());
310  auto data = reinterpret_cast<const uint8_t*>(bytes.c_str());
311  return std::vector<uint8_t>(data, data + bytes.size());
312  }
313  }
314 };
315 
317 // PostgresqlConnection implementations
319 
320 inline Statement
321 PostgresqlConnection::prepareStatement(const std::string &sql) {
322  auto detail = std::shared_ptr<PostgresqlStatement>(new PostgresqlStatement(shared_from_this(), sql));
323  return makeStatement(detail);
324 }
325 
326 } // namespace
327 
329 // Top-level Postgresql connection
331 
332 inline Postgresql&
333 Postgresql::open(const Postgresql::Locator &where) {
334  auto pimpl = std::shared_ptr<Detail::PostgresqlConnection>(new Detail::PostgresqlConnection);
335  pimpl->open(where);
336  this->pimpl(pimpl);
337  return *this;
338 }
339 
340 } // namespace
341 } // namespace
342 
343 #endif
344 #endif
Name space for the entire library.
Definition: FeasiblePath.h:773