ROSE  0.11.2.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  auto tx = std::dynamic_pointer_cast<PostgresqlConnection>(connection())->transaction.get();
165  pvalues_[idx] = "'" + tx->esc_raw(reinterpret_cast<const unsigned char*>(value.c_str()), value.size()) + "'";
166  }
167 
168  void bindLow(size_t idx, const char *value) override {
169  auto tx = std::dynamic_pointer_cast<PostgresqlConnection>(connection())->transaction.get();
170  pvalues_[idx] = "'" + tx->esc(value) + "'";
171  }
172 
173  void bindLow(size_t idx, const std::vector<uint8_t> &value) override {
174  auto tx = std::dynamic_pointer_cast<PostgresqlConnection>(connection())->transaction.get();
175  auto data = static_cast<const unsigned char*>(value.data());
176  pvalues_[idx] = "'" + tx->esc_raw(data, value.size()) + "'";
177  }
178 
179  void bindLow(size_t idx, Nothing) override {
180  pvalues_[idx] = "null";
181  }
182 
183  Iterator beginLow() override {
184  // Expand the low-level "?" parameters into a new SQL string. The values are already escaped and quoted if necessary.
185  std::string sql;
186  size_t paramIdx = 0;
187  bool inQuote = false;
188  for (size_t i=0; i<sql_.size(); ++i) {
189  if ('\'' == sql_[i]) {
190  inQuote = !inQuote;
191  sql += sql_[i];
192  } else if ('?' == sql_[i] && !inQuote) {
193  ASSERT_require(paramIdx < pvalues_.size());
194  sql += pvalues_[paramIdx++];
195  } else {
196  sql += sql_[i];
197  }
198  }
199 
200  auto tx = std::dynamic_pointer_cast<PostgresqlConnection>(connection())->transaction.get();
201  result_ = tx->exec(sql);
202  if (result_.empty())
203  return Iterator();
204  return makeIterator();
205  }
206 
207  Iterator nextLow() override {
208  if (rowNumber() >= result_.size()) {
209  state(Statement::FINISHED);
210  return Iterator();
211  } else {
212  return makeIterator();
213  }
214  }
215 
216  size_t nColumns() const override {
217  return result_.columns();
218  }
219 
220  Sawyer::Optional<std::string> getString(size_t idx) override {
221  ASSERT_require(rowNumber() < result_.size());
222  ASSERT_require(idx < result_.columns());
223  if (result_[rowNumber()][boost::numeric_cast<int>(idx)].is_null()) {
224  return Nothing();
225  } else {
226  return unescapeRaw(result_[rowNumber()][boost::numeric_cast<int>(idx)].as<std::string>());
227  }
228  }
229 
230  static unsigned hex2int(char ch) {
231  if (::isdigit(ch))
232  return ch - '0';
233  if (ch >= 'a' && ch <= 'f')
234  return ch - 'a' + 10;
235  ASSERT_require(ch >= 'A' && ch <= 'F');
236  return ch - 'A' + 10;
237  }
238 
239  // Because pqxx::transaction_base::unesc_raw is not available yet
240  static std::string unescapeRaw(const std::string &s) {
241  if (boost::starts_with(s, "\\x") && s.size() % 2 == 0) {
242  std::string retval;
243  for (size_t i=2; i<s.size(); i+=2)
244  retval += static_cast<char>(hex2int(s[i])*16 + hex2int(s[i+1]));
245  return retval;
246  } else {
247  return s;
248  }
249  }
250 
251  Sawyer::Optional<std::vector<uint8_t>> getBlob(size_t idx) override {
252  ASSERT_require(rowNumber() < result_.size());
253  ASSERT_require(idx < result_.columns());
254  if (result_[rowNumber()][boost::numeric_cast<int>(idx)].is_null()) {
255  return Nothing();
256  } else {
257  std::string bytes = unescapeRaw(result_[rowNumber()][boost::numeric_cast<int>(idx)].as<std::string>());
258  auto data = reinterpret_cast<const uint8_t*>(bytes.c_str());
259  return std::vector<uint8_t>(data, data + bytes.size());
260  }
261  }
262 };
263 
265 // PostgresqlConnection implementations
267 
268 inline Statement
269 PostgresqlConnection::prepareStatement(const std::string &sql) {
270  auto detail = std::shared_ptr<PostgresqlStatement>(new PostgresqlStatement(shared_from_this(), sql));
271  return makeStatement(detail);
272 }
273 
274 } // namespace
275 
277 // Top-level Postgresql connection
279 
280 inline Postgresql&
281 Postgresql::open(const Postgresql::Locator &where) {
282  auto pimpl = std::shared_ptr<Detail::PostgresqlConnection>(new Detail::PostgresqlConnection);
283  pimpl->open(where);
284  this->pimpl(pimpl);
285  return *this;
286 }
287 
288 } // namespace
289 } // namespace
290 
291 #endif
292 #endif
Name space for the entire library.