hphp/util/sqlite-wrapper.cpp

   1 /*
   2    +----------------------------------------------------------------------+
   3    | HipHop for PHP                                                       |
   4    +----------------------------------------------------------------------+
   5    | Copyright (c) 2010-present Facebook, Inc. (http://www.facebook.com)  |
   6    +----------------------------------------------------------------------+
   7    | This source file is subject to version 3.01 of the PHP license,      |
   8    | that is bundled with this package in the file LICENSE, and is        |
   9    | available through the world-wide-web at the following url:           |
  10    | http://www.php.net/license/3_01.txt                                  |
  11    | If you did not receive a copy of the PHP license and are unable to   |
  12    | obtain it through the world-wide-web, please send a note to          |
  13    | license@php.net so we can mail you a copy immediately.               |
  14    +----------------------------------------------------------------------+
  15 */
  16
  17 #include <sqlite3.h>
  18
  19 #include "hphp/util/assertions.h"
  20 #include "hphp/util/sqlite-wrapper.h"
  21
  22 namespace HPHP {
  23
  24 //==============================================================================
  25 // SQLite.
  26
  27 SQLite::SQLite(SQLite&& old) noexcept
  28     : m_dbc{old.m_dbc},
  29       m_beginStmt{std::move(old.m_beginStmt)},
  30       m_rollbackStmt{std::move(old.m_rollbackStmt)},
  31       m_commitStmt{std::move(old.m_commitStmt)} {
  32   old.m_dbc = nullptr;
  33
  34   // No outstanding transactions
  35   assertx(old.m_txDepth == 0);
  36   assertx(old.m_rollback == false);
  37 }
  38
  39 SQLite& SQLite::operator=(SQLite&& old) noexcept {
  40   if (this == &old) {
  41     return *this;
  42   }
  43
  44   // Outstanding transactions would be invalidated; make sure none
  45   // exist
  46   assertx(m_txDepth == 0);
  47   assertx(m_rollback == false);
  48   assertx(old.m_txDepth == 0);
  49   assertx(old.m_rollback == false);
  50
  51   sqlite3_close_v2(m_dbc);
  52   m_dbc = old.m_dbc;
  53   old.m_dbc = nullptr;
  54
  55   m_beginStmt = std::move(old.m_beginStmt);
  56   m_rollbackStmt = std::move(old.m_rollbackStmt);
  57   m_commitStmt = std::move(old.m_commitStmt);
  58
  59   return *this;
  60 }
  61
  62 SQLite::~SQLite() {
  63   sqlite3_close_v2(m_dbc);
  64   m_dbc = nullptr;
  65 }
  66
  67 SQLite SQLite::connect(const folly::StringPiece path) {
  68   sqlite3* dbc = nullptr;
  69   int rc = sqlite3_open_v2(
  70       path.data(), &dbc,
  71       SQLITE_OPEN_NOMUTEX | SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE,
  72       nullptr);
  73   if (rc) {
  74     throw SQLiteExc{rc, ""};
  75   }
  76   return SQLite{dbc};
  77 }
  78
  79 /**
  80  * Compile the given SQL query into a statement object which can run and rerun
  81  * the query.
  82  */
  83 SQLiteStmt SQLite::prepare(const folly::StringPiece sql) {
  84   return {*this, sql};
  85 }
  86
  87 SQLiteTxn SQLite::begin() { return SQLiteTxn{*this}; }
  88
  89 void SQLite::setBusyTimeout(int ms) noexcept {
  90   sqlite3_busy_timeout(m_dbc, ms);
  91 }
  92
  93 std::string SQLite::errMsg() const noexcept { return {sqlite3_errmsg(m_dbc)}; }
  94
  95 SQLite::SQLite(sqlite3* dbc)
  96     : m_dbc{dbc},
  97       m_beginStmt{*this, "BEGIN"},
  98       m_rollbackStmt{*this, "ROLLBACK"},
  99       m_commitStmt{*this, "COMMIT"} {
 100   setBusyTimeout(60'000);
 101   SQLiteStmt foreignKeysStmt{*this, "PRAGMA foreign_keys = ON"};
 102   foreignKeysStmt.query().step();
 103   SQLiteStmt walModeStmt{*this, "PRAGMA journal_mode = WAL"};
 104   try {
 105     walModeStmt.query().step();
 106   } catch (SQLiteExc& e) {
 107     switch (e.m_code) {
 108       case SQLITE_BUSY:
 109         // This happens if multiple connections attempt to set WAL mode at the
 110         // same time. We only need one connection to succeed.
 111         break;
 112       default:
 113         throw;
 114     }
 115   }
 116 }
 117
 118 void SQLite::txPush() {
 119   if (m_txDepth == 0) {
 120     SQLiteQuery query{m_beginStmt};
 121     query.step();
 122   }
 123   m_txDepth++;
 124 }
 125
 126 void SQLite::txPop() {
 127   // We mix the concept of rollback with a normal commit so that if we try to
 128   // rollback an inner transaction we eventually end up rolling back the outer
 129   // transaction instead (Sqlite doesn't support rolling back partial
 130   // transactions).
 131   assertx(m_txDepth > 0);
 132   if (m_txDepth > 1) {
 133     m_txDepth--;
 134     return;
 135   }
 136   if (!m_rollback) {
 137     SQLiteQuery query{m_commitStmt};
 138     query.step();
 139   } else {
 140     // We're in the outermost transaction - so clear the rollback flag.
 141     m_rollback = false;
 142     SQLiteQuery query{m_rollbackStmt};
 143     try {
 144       query.step();
 145     } catch (const SQLiteExc& ex) {
 146       /*
 147        * Having a rollback fail is actually a normal, expected case,
 148        * so just swallow this.
 149        *
 150        * In particular, according to the docs, if we got an I/O error
 151        * while doing a commit, the rollback will often fail with "no
 152        * transaction in progress", because the commit will have
 153        * automatically been rolled back.  Recommended practice is
 154        * still to execute a rollback statement and ignore the error.
 155        */
 156     }
 157   }
 158   // Decrement depth after query execution, in case an exception occurs during
 159   // commit.  This allows for subsequent rollback of the failed commit.
 160   m_txDepth--;
 161 }
 162
 163 void SQLite::rollback() noexcept {
 164   m_rollback = true;
 165   // NOTE: A try/catch isn't necessary - txPop() handles rollback as a nothrow.
 166   txPop();
 167 }
 168
 169 void SQLite::commit() { txPop(); }
 170
 171 //==============================================================================
 172 // SQLiteTxn.
 173
 174 SQLiteTxn::SQLiteTxn(SQLite& db) : m_db{&db} { m_db->txPush(); }
 175
 176 SQLiteTxn::SQLiteTxn(SQLiteTxn&& old) noexcept
 177     : m_db{old.m_db}, m_pending{old.m_pending} {
 178   old.m_db = nullptr;
 179   old.m_pending = false;
 180 }
 181
 182 SQLiteTxn& SQLiteTxn::operator=(SQLiteTxn&& old) noexcept {
 183   if (this == &old) {
 184     return *this;
 185   }
 186   assertx(!m_pending);
 187   m_db = old.m_db;
 188   old.m_db = nullptr;
 189   m_pending = old.m_pending;
 190   old.m_pending = false;
 191   return *this;
 192 }
 193
 194 SQLiteTxn::~SQLiteTxn() {
 195   if (m_pending) {
 196     assertx(m_db != nullptr);
 197     m_db->rollback();
 198   }
 199 }
 200
 201 SQLiteQuery SQLiteTxn::query(SQLiteStmt& stmt) noexcept { return stmt.query(); }
 202
 203 void SQLiteTxn::exec(folly::StringPiece sql) {
 204   assertx(m_db != nullptr);
 205   SQLiteStmt stmt{m_db->prepare(sql)};
 206   SQLiteQuery query{stmt.query()};
 207   query.step();
 208 }
 209
 210 void SQLiteTxn::commit() {
 211   assertx(m_db != nullptr);
 212   m_db->commit();
 213   m_pending = false;
 214 }
 215
 216 //==============================================================================
 217 // SQLiteStmt.
 218
 219 SQLiteStmt::SQLiteStmt(SQLiteStmt&& old) noexcept
 220     : m_stmt{old.m_stmt}, m_queryExists{old.m_queryExists} {
 221   assertx(!old.m_queryExists);
 222   old.m_stmt = nullptr;
 223 }
 224
 225 SQLiteStmt& SQLiteStmt::operator=(SQLiteStmt&& old) noexcept {
 226   if (this == &old) {
 227     return *this;
 228   }
 229
 230   // Outstanding queries would be invalidated; make sure none exist
 231   assertx(!m_queryExists);
 232   assertx(!old.m_queryExists);
 233
 234   sqlite3_finalize(m_stmt);
 235   m_stmt = old.m_stmt;
 236   old.m_stmt = nullptr;
 237
 238   return *this;
 239 }
 240
 241 SQLiteStmt::~SQLiteStmt() {
 242   assertx(!m_queryExists);
 243   sqlite3_finalize(m_stmt);
 244   m_stmt = nullptr;
 245 }
 246
 247 folly::StringPiece SQLiteStmt::sql() const noexcept {
 248 #if SQLITE_VERSION_NUMBER >= 3014000
 249   return folly::StringPiece{sqlite3_expanded_sql(m_stmt)};
 250 #else
 251   return folly::StringPiece{sqlite3_sql(m_stmt)};
 252 #endif
 253 }
 254
 255 SQLiteStmt::SQLiteStmt(SQLite& db, folly::StringPiece sql) {
 256   int rc =
 257       sqlite3_prepare_v2(db.m_dbc, sql.data(), sql.size(), &m_stmt, nullptr);
 258   if (rc) {
 259     throw SQLiteExc{rc, ""};
 260   }
 261   assertx(m_stmt != nullptr);
 262 }
 263
 264 SQLiteQuery SQLiteStmt::query() noexcept {
 265   assertx(!m_queryExists);
 266   m_queryExists = true;
 267   return SQLiteQuery{*this};
 268 }
 269
 270 void SQLiteStmt::reset() noexcept {
 271   m_queryExists = false;
 272   sqlite3_reset(m_stmt);
 273   sqlite3_clear_bindings(m_stmt);
 274 }
 275
 276 //==============================================================================
 277 // SQLiteQuery.
 278
 279 SQLiteQuery::SQLiteQuery(SQLiteQuery&& old) noexcept
 280     : m_stmt{old.m_stmt}, m_row{old.m_row}, m_done{old.m_done} {
 281   old.m_stmt = nullptr;
 282 }
 283
 284 SQLiteQuery& SQLiteQuery::operator=(SQLiteQuery&& old) noexcept {
 285   if (this == &old) {
 286     return *this;
 287   }
 288
 289   if (m_stmt != nullptr) {
 290     m_stmt->reset();
 291   }
 292   m_stmt = old.m_stmt;
 293   m_row = old.m_row;
 294   m_done = old.m_done;
 295
 296   old.m_stmt = nullptr;
 297   return *this;
 298 }
 299
 300 SQLiteQuery::~SQLiteQuery() {
 301   if (m_stmt != nullptr) {
 302     m_stmt->reset();
 303   }
 304 }
 305
 306 folly::StringPiece SQLiteQuery::sql() const noexcept {
 307   assertx(m_stmt != nullptr);
 308   return m_stmt->sql();
 309 }
 310
 311 void SQLiteQuery::step() {
 312   assertx(m_stmt != nullptr);
 313   int rc = sqlite3_step(m_stmt->m_stmt);
 314   switch (rc) {
 315     case SQLITE_DONE:
 316       m_row = false;
 317       m_done = true;
 318       break;
 319     case SQLITE_ROW:
 320       m_row = true;
 321       m_done = false;
 322       break;
 323     default:
 324       throw SQLiteExc{rc, sql().str()};
 325   }
 326 }
 327
 328 void SQLiteQuery::bindBlob(const char* paramName, const void* blob, int size,
 329                            bool isStatic /* = false */) noexcept {
 330   assertx(m_stmt != nullptr);
 331   sqlite3_stmt* stmt = m_stmt->m_stmt;
 332   int UNUSED rc = sqlite3_bind_blob(
 333       stmt, sqlite3_bind_parameter_index(stmt, paramName), blob, size,
 334       isStatic ? SQLITE_STATIC : SQLITE_TRANSIENT);
 335   assertx(rc == SQLITE_OK);
 336 }
 337
 338 void SQLiteQuery::bindText(const char* paramName, const char* text, int size,
 339                            bool isStatic /* = false */) noexcept {
 340   assertx(m_stmt != nullptr);
 341   assertx(size >= 0);
 342   sqlite3_stmt* stmt = m_stmt->m_stmt;
 343   int UNUSED rc = sqlite3_bind_text(
 344       stmt, sqlite3_bind_parameter_index(stmt, paramName), text, int(size),
 345       isStatic ? SQLITE_STATIC : SQLITE_TRANSIENT);
 346   assertx(rc == SQLITE_OK);
 347 }
 348
 349 void SQLiteQuery::bindString(const char* paramName,
 350                              const folly::StringPiece s) noexcept {
 351   bindText(paramName, s.data(), s.size(), true);
 352 }
 353
 354 void SQLiteQuery::bindDouble(const char* paramName, double val) noexcept {
 355   assertx(m_stmt != nullptr);
 356   sqlite3_stmt* stmt = m_stmt->m_stmt;
 357   int UNUSED rc = sqlite3_bind_double(
 358       stmt, sqlite3_bind_parameter_index(stmt, paramName), val);
 359   assertx(rc == SQLITE_OK);
 360 }
 361
 362 void SQLiteQuery::bindInt(const char* paramName, int val) noexcept {
 363   assertx(m_stmt != nullptr);
 364   sqlite3_stmt* stmt = m_stmt->m_stmt;
 365   int UNUSED rc = sqlite3_bind_int(
 366       stmt, sqlite3_bind_parameter_index(stmt, paramName), val);
 367   assertx(rc == SQLITE_OK);
 368 }
 369
 370 void SQLiteQuery::bindBool(const char* paramName, bool b) noexcept {
 371   bindInt(paramName, int(b));
 372 }
 373
 374 void SQLiteQuery::bindInt64(const char* paramName, int64_t val) noexcept {
 375   assertx(m_stmt != nullptr);
 376   sqlite3_stmt* stmt = m_stmt->m_stmt;
 377   int UNUSED rc = sqlite3_bind_int64(
 378       stmt, sqlite3_bind_parameter_index(stmt, paramName), val);
 379   assertx(rc == SQLITE_OK);
 380 }
 381
 382 void SQLiteQuery::bindNull(const char* paramName) noexcept {
 383   assertx(m_stmt != nullptr);
 384   sqlite3_stmt* stmt = m_stmt->m_stmt;
 385   int UNUSED rc =
 386       sqlite3_bind_null(stmt, sqlite3_bind_parameter_index(stmt, paramName));
 387   assertx(rc == SQLITE_OK);
 388 }
 389
 390 // Get the column value as the named type. If the value cannot be converted
 391 // into the named type then an error is thrown.
 392 bool SQLiteQuery::getBool(int iCol) { return static_cast<bool>(getInt(iCol)); }
 393
 394 int SQLiteQuery::getInt(int iCol) {
 395   assertx(m_stmt != nullptr);
 396   return sqlite3_column_int(m_stmt->m_stmt, iCol);
 397 }
 398
 399 int64_t SQLiteQuery::getInt64(int iCol) {
 400   assertx(m_stmt != nullptr);
 401   return sqlite3_column_int64(m_stmt->m_stmt, iCol);
 402 }
 403
 404 double SQLiteQuery::getDouble(int iCol) {
 405   assertx(m_stmt != nullptr);
 406   return sqlite3_column_double(m_stmt->m_stmt, iCol);
 407 }
 408
 409 void SQLiteQuery::getBlob(int iCol, const void*& blob, size_t& size) {
 410   assertx(m_stmt != nullptr);
 411   sqlite3_stmt* stmt = m_stmt->m_stmt;
 412   blob = sqlite3_column_blob(stmt, iCol);
 413   size = sqlite3_column_bytes(stmt, iCol);
 414 }
 415
 416 const folly::StringPiece SQLiteQuery::getString(int iCol) {
 417   assertx(m_stmt != nullptr);
 418   sqlite3_stmt* stmt = m_stmt->m_stmt;
 419   const char* text =
 420       reinterpret_cast<const char*>(sqlite3_column_text(stmt, iCol));
 421   int size = sqlite3_column_bytes(stmt, iCol);
 422   assertx(size >= 0);
 423   return {text, static_cast<size_t>(size)};
 424 }
 425
 426 SQLiteQuery::SQLiteQuery(SQLiteStmt& stmt) : m_stmt{&stmt} {}
 427
 428 //==============================================================================
 429 // SQLiteExc.
 430
 431 SQLiteExc::SQLiteExc(int code, std::string sql)
 432     : std::runtime_error{sql.empty()
 433                              ? folly::to<std::string>(
 434                                    "SQLiteExc [", code,
 435                                    "]: ", sqlite3_errstr(code))
 436                              : folly::to<std::string>(
 437                                    "SQLiteExc [", code, "] while running ", sql,
 438                                    ": ", sqlite3_errstr(code))},
 439       m_code{code} {}
 440
 441 }  // namespace HPHP