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 namespace {
  28
  29 std::string_view journalModeName(
  30   SQLite::JournalMode mode) noexcept {
  31   switch (mode) {
  32     case SQLite::JournalMode::DELETE:
  33       return "DELETE";
  34     case SQLite::JournalMode::TRUNCATE:
  35       return "TRUNCATE";
  36     case SQLite::JournalMode::PERSIST:
  37       return "PERSIST";
  38     case SQLite::JournalMode::MEMORY:
  39       return "MEMORY";
  40     case SQLite::JournalMode::WAL:
  41       return "WAL";
  42     case SQLite::JournalMode::OFF:
  43       return "OFF";
  44   }
  45   not_reached();
  46 }
  47
  48 } // namespace
  49
  50 SQLite::SQLite(SQLite&& old) noexcept
  51     : m_dbc{old.m_dbc},
  52       m_beginStmt{std::move(old.m_beginStmt)},
  53       m_rollbackStmt{std::move(old.m_rollbackStmt)},
  54       m_commitStmt{std::move(old.m_commitStmt)} {
  55   old.m_dbc = nullptr;
  56
  57   // No outstanding transactions
  58   assertx(old.m_txDepth == 0);
  59   assertx(old.m_rollback == false);
  60 }
  61
  62 SQLite& SQLite::operator=(SQLite&& old) noexcept {
  63   if (this == &old) {
  64     return *this;
  65   }
  66
  67   // Outstanding transactions would be invalidated; make sure none
  68   // exist
  69   assertx(m_txDepth == 0);
  70   assertx(m_rollback == false);
  71   assertx(old.m_txDepth == 0);
  72   assertx(old.m_rollback == false);
  73
  74   sqlite3_close_v2(m_dbc);
  75   m_dbc = old.m_dbc;
  76   old.m_dbc = nullptr;
  77
  78   m_beginStmt = std::move(old.m_beginStmt);
  79   m_rollbackStmt = std::move(old.m_rollbackStmt);
  80   m_commitStmt = std::move(old.m_commitStmt);
  81
  82   return *this;
  83 }
  84
  85 SQLite::~SQLite() {
  86   sqlite3_close_v2(m_dbc);
  87   m_dbc = nullptr;
  88 }
  89
  90 void SQLite::analyze() {
  91   int rc = sqlite3_exec(m_dbc, "ANALYZE", nullptr, nullptr, nullptr);
  92   if (rc != SQLITE_OK) {
  93     throw SQLiteExc{rc, "ANALYZE"};
  94   }
  95 }
  96
  97 SQLite SQLite::connect(const std::string& path, OpenMode mode) {
  98   return connect(path.c_str(), mode);
  99 }
 100
 101 SQLite SQLite::connect(const char* path, OpenMode mode) {
 102
 103   int flags = [&] {
 104     switch (mode) {
 105       case OpenMode::ReadOnly:
 106         return SQLITE_OPEN_READONLY;
 107       case OpenMode::ReadWrite:
 108         return SQLITE_OPEN_READWRITE;
 109       case OpenMode::ReadWriteCreate:
 110         return SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE;
 111     }
 112     not_reached();
 113   }();
 114
 115   sqlite3* dbc = nullptr;
 116   int rc = sqlite3_open_v2(
 117       path, &dbc,
 118       SQLITE_OPEN_NOMUTEX | flags,
 119       nullptr);
 120   if (rc) {
 121     throw SQLiteExc{rc, ""};
 122   }
 123   return SQLite{dbc};
 124 }
 125
 126 /**
 127  * Compile the given SQL query into a statement object which can run and rerun
 128  * the query.
 129  */
 130 SQLiteStmt SQLite::prepare(const std::string_view sql) {
 131   return {*this, sql};
 132 }
 133
 134 SQLiteTxn SQLite::begin() { return SQLiteTxn{*this}; }
 135
 136 void SQLite::setBusyTimeout(int ms) noexcept {
 137   sqlite3_busy_timeout(m_dbc, ms);
 138 }
 139
 140 void SQLite::setJournalMode(JournalMode mode) {
 141   SQLiteStmt journalModeStmt{
 142     *this, folly::sformat("PRAGMA journal_mode = {}", journalModeName(mode))};
 143   journalModeStmt.query().step();
 144 }
 145
 146 void SQLite::setSynchronousLevel(SynchronousLevel lvl) {
 147   SQLiteStmt stmt{
 148     *this,
 149     folly::sformat("PRAGMA synchronous = {}", static_cast<int>(lvl))};
 150   stmt.query().step();
 151 }
 152
 153 bool SQLite::isReadOnly() const {
 154   return isReadOnly("main");
 155 }
 156
 157 bool SQLite::isReadOnly(const std::string& dbName) const {
 158   return isReadOnly(dbName.c_str());
 159 }
 160
 161 bool SQLite::isReadOnly(const char* dbName) const {
 162   return bool(sqlite3_db_readonly(m_dbc, dbName));
 163 }
 164
 165 std::string SQLite::errMsg() const noexcept { return {sqlite3_errmsg(m_dbc)}; }
 166
 167 SQLite::SQLite(sqlite3* dbc)
 168     : m_dbc{dbc},
 169       m_beginStmt{*this, "BEGIN"},
 170       m_rollbackStmt{*this, "ROLLBACK"},
 171       m_commitStmt{*this, "COMMIT"} {
 172   setBusyTimeout(60'000);
 173   SQLiteStmt foreignKeysStmt{*this, "PRAGMA foreign_keys = ON"};
 174   foreignKeysStmt.query().step();
 175 }
 176
 177 void SQLite::txPush() {
 178   if (m_txDepth == 0) {
 179     SQLiteQuery query{m_beginStmt};
 180     query.step();
 181   }
 182   m_txDepth++;
 183 }
 184
 185 void SQLite::txPop() {
 186   // We mix the concept of rollback with a normal commit so that if we try to
 187   // rollback an inner transaction we eventually end up rolling back the outer
 188   // transaction instead (Sqlite doesn't support rolling back partial
 189   // transactions).
 190   assertx(m_txDepth > 0);
 191   if (m_txDepth > 1) {
 192     m_txDepth--;
 193     return;
 194   }
 195   if (!m_rollback) {
 196     SQLiteQuery query{m_commitStmt};
 197     query.step();
 198   } else {
 199     // We're in the outermost transaction - so clear the rollback flag.
 200     m_rollback = false;
 201     SQLiteQuery query{m_rollbackStmt};
 202     try {
 203       query.step();
 204     } catch (const SQLiteExc& ) {
 205       /*
 206        * Having a rollback fail is actually a normal, expected case,
 207        * so just swallow this.
 208        *
 209        * In particular, according to the docs, if we got an I/O error
 210        * while doing a commit, the rollback will often fail with "no
 211        * transaction in progress", because the commit will have
 212        * automatically been rolled back.  Recommended practice is
 213        * still to execute a rollback statement and ignore the error.
 214        */
 215     }
 216   }
 217   // Decrement depth after query execution, in case an exception occurs during
 218   // commit.  This allows for subsequent rollback of the failed commit.
 219   m_txDepth--;
 220 }
 221
 222 void SQLite::rollback() noexcept {
 223   m_rollback = true;
 224   // NOTE: A try/catch isn't necessary - txPop() handles rollback as a nothrow.
 225   txPop();
 226 }
 227
 228 void SQLite::commit() { txPop(); }
 229
 230 //==============================================================================
 231 // SQLiteTxn.
 232
 233 SQLiteTxn::SQLiteTxn(SQLite& db) : m_db{&db} { m_db->txPush(); }
 234
 235 SQLiteTxn::SQLiteTxn(SQLiteTxn&& old) noexcept
 236     : m_db{old.m_db}, m_pending{old.m_pending} {
 237   old.m_db = nullptr;
 238   old.m_pending = false;
 239 }
 240
 241 SQLiteTxn& SQLiteTxn::operator=(SQLiteTxn&& old) noexcept {
 242   if (this == &old) {
 243     return *this;
 244   }
 245   assertx(!m_pending);
 246   m_db = old.m_db;
 247   old.m_db = nullptr;
 248   m_pending = old.m_pending;
 249   old.m_pending = false;
 250   return *this;
 251 }
 252
 253 SQLiteTxn::~SQLiteTxn() {
 254   if (m_pending) {
 255     assertx(m_db != nullptr);
 256     m_db->rollback();
 257   }
 258 }
 259
 260 SQLiteQuery SQLiteTxn::query(SQLiteStmt& stmt) noexcept { return stmt.query(); }
 261
 262 void SQLiteTxn::exec(std::string_view sql) {
 263   assertx(m_db != nullptr);
 264   SQLiteStmt stmt{m_db->prepare(sql)};
 265   SQLiteQuery query{stmt.query()};
 266   query.step();
 267 }
 268
 269 void SQLiteTxn::commit() {
 270   assertx(m_db != nullptr);
 271   m_db->commit();
 272   m_pending = false;
 273 }
 274
 275 //==============================================================================
 276 // SQLiteStmt.
 277
 278 SQLiteStmt::SQLiteStmt(SQLiteStmt&& old) noexcept
 279     : m_stmt{old.m_stmt}, m_queryExists{old.m_queryExists} {
 280   assertx(!old.m_queryExists);
 281   old.m_stmt = nullptr;
 282 }
 283
 284 SQLiteStmt& SQLiteStmt::operator=(SQLiteStmt&& old) noexcept {
 285   if (this == &old) {
 286     return *this;
 287   }
 288
 289   // Outstanding queries would be invalidated; make sure none exist
 290   assertx(!m_queryExists);
 291   assertx(!old.m_queryExists);
 292
 293   sqlite3_finalize(m_stmt);
 294   m_stmt = old.m_stmt;
 295   old.m_stmt = nullptr;
 296
 297   return *this;
 298 }
 299
 300 SQLiteStmt::~SQLiteStmt() {
 301   assertx(!m_queryExists);
 302   sqlite3_finalize(m_stmt);
 303   m_stmt = nullptr;
 304 }
 305
 306 std::string_view SQLiteStmt::sql() const noexcept {
 307 #if SQLITE_VERSION_NUMBER >= 3014000
 308   return std::string_view{sqlite3_expanded_sql(m_stmt)};
 309 #else
 310   return std::string_view{sqlite3_sql(m_stmt)};
 311 #endif
 312 }
 313
 314 SQLiteStmt::SQLiteStmt(SQLite& db, const std::string_view sql) {
 315   int rc =
 316       sqlite3_prepare_v2(db.m_dbc, sql.data(), sql.size(), &m_stmt, nullptr);
 317   if (rc) {
 318     throw SQLiteExc{rc, std::string{sql}};
 319   }
 320   assertx(m_stmt != nullptr);
 321 }
 322
 323 SQLiteQuery SQLiteStmt::query() noexcept {
 324   assertx(!m_queryExists);
 325   assertx(m_stmt != nullptr);
 326   m_queryExists = true;
 327   return SQLiteQuery{*this};
 328 }
 329
 330 void SQLiteStmt::reset() noexcept {
 331   m_queryExists = false;
 332   sqlite3_reset(m_stmt);
 333   sqlite3_clear_bindings(m_stmt);
 334 }
 335
 336 //==============================================================================
 337 // SQLiteQuery.
 338
 339 SQLiteQuery::SQLiteQuery(SQLiteQuery&& old) noexcept
 340     : m_stmt{old.m_stmt}, m_row{old.m_row}, m_done{old.m_done} {
 341   old.m_stmt = nullptr;
 342 }
 343
 344 SQLiteQuery& SQLiteQuery::operator=(SQLiteQuery&& old) noexcept {
 345   if (this == &old) {
 346     return *this;
 347   }
 348
 349   if (m_stmt != nullptr) {
 350     m_stmt->reset();
 351   }
 352   m_stmt = old.m_stmt;
 353   m_row = old.m_row;
 354   m_done = old.m_done;
 355
 356   old.m_stmt = nullptr;
 357   return *this;
 358 }
 359
 360 SQLiteQuery::~SQLiteQuery() {
 361   if (m_stmt != nullptr) {
 362     m_stmt->reset();
 363   }
 364 }
 365
 366 std::string_view SQLiteQuery::sql() const noexcept {
 367   assertx(m_stmt != nullptr);
 368   return m_stmt->sql();
 369 }
 370
 371 void SQLiteQuery::step() {
 372   assertx(m_stmt != nullptr);
 373   int rc = sqlite3_step(m_stmt->m_stmt);
 374   switch (rc) {
 375     case SQLITE_DONE:
 376       m_row = false;
 377       m_done = true;
 378       break;
 379     case SQLITE_ROW:
 380       m_row = true;
 381       m_done = false;
 382       break;
 383     default:
 384       throw SQLiteExc{rc, std::string{sql()}};
 385   }
 386 }
 387
 388 void SQLiteQuery::bindBlob(const char* paramName, const void* blob, int size,
 389                            bool isStatic /* = false */) noexcept {
 390   assertx(m_stmt != nullptr);
 391   sqlite3_stmt* stmt = m_stmt->m_stmt;
 392   int UNUSED rc = sqlite3_bind_blob(
 393       stmt, sqlite3_bind_parameter_index(stmt, paramName), blob, size,
 394       isStatic ? SQLITE_STATIC : SQLITE_TRANSIENT);
 395   assertx(rc == SQLITE_OK);
 396 }
 397
 398 void SQLiteQuery::bindText(const char* paramName, const char* text, int size,
 399                            bool isStatic /* = false */) noexcept {
 400   assertx(m_stmt != nullptr);
 401   assertx(size >= 0);
 402   sqlite3_stmt* stmt = m_stmt->m_stmt;
 403   int UNUSED rc = sqlite3_bind_text(
 404       stmt, sqlite3_bind_parameter_index(stmt, paramName), text, int(size),
 405       isStatic ? SQLITE_STATIC : SQLITE_TRANSIENT);
 406   assertx(rc == SQLITE_OK);
 407 }
 408
 409 void SQLiteQuery::bindString(const char* paramName,
 410                              const std::string_view s) noexcept {
 411   bindText(paramName, s.data(), s.size(), true);
 412 }
 413
 414 void SQLiteQuery::bindDouble(const char* paramName, double val) noexcept {
 415   assertx(m_stmt != nullptr);
 416   sqlite3_stmt* stmt = m_stmt->m_stmt;
 417   int UNUSED rc = sqlite3_bind_double(
 418       stmt, sqlite3_bind_parameter_index(stmt, paramName), val);
 419   assertx(rc == SQLITE_OK);
 420 }
 421
 422 void SQLiteQuery::bindInt(const char* paramName, int val) noexcept {
 423   assertx(m_stmt != nullptr);
 424   sqlite3_stmt* stmt = m_stmt->m_stmt;
 425   int UNUSED rc = sqlite3_bind_int(
 426       stmt, sqlite3_bind_parameter_index(stmt, paramName), val);
 427   assertx(rc == SQLITE_OK);
 428 }
 429
 430 void SQLiteQuery::bindBool(const char* paramName, bool b) noexcept {
 431   bindInt(paramName, int(b));
 432 }
 433
 434 void SQLiteQuery::bindInt64(const char* paramName, int64_t val) noexcept {
 435   assertx(m_stmt != nullptr);
 436   sqlite3_stmt* stmt = m_stmt->m_stmt;
 437   int UNUSED rc = sqlite3_bind_int64(
 438       stmt, sqlite3_bind_parameter_index(stmt, paramName), val);
 439   assertx(rc == SQLITE_OK);
 440 }
 441
 442 void SQLiteQuery::bindNull(const char* paramName) noexcept {
 443   assertx(m_stmt != nullptr);
 444   sqlite3_stmt* stmt = m_stmt->m_stmt;
 445   int UNUSED rc =
 446       sqlite3_bind_null(stmt, sqlite3_bind_parameter_index(stmt, paramName));
 447   assertx(rc == SQLITE_OK);
 448 }
 449
 450 // Get the column value as the named type. If the value cannot be converted
 451 // into the named type then an error is thrown.
 452 bool SQLiteQuery::getBool(int iCol) { return static_cast<bool>(getInt(iCol)); }
 453
 454 int SQLiteQuery::getInt(int iCol) {
 455   assertx(m_stmt != nullptr);
 456   return sqlite3_column_int(m_stmt->m_stmt, iCol);
 457 }
 458
 459 int64_t SQLiteQuery::getInt64(int iCol) {
 460   assertx(m_stmt != nullptr);
 461   return sqlite3_column_int64(m_stmt->m_stmt, iCol);
 462 }
 463
 464 double SQLiteQuery::getDouble(int iCol) {
 465   assertx(m_stmt != nullptr);
 466   return sqlite3_column_double(m_stmt->m_stmt, iCol);
 467 }
 468
 469 void SQLiteQuery::getBlob(int iCol, const void*& blob, size_t& size) {
 470   assertx(m_stmt != nullptr);
 471   sqlite3_stmt* stmt = m_stmt->m_stmt;
 472   blob = sqlite3_column_blob(stmt, iCol);
 473   size = sqlite3_column_bytes(stmt, iCol);
 474 }
 475
 476 const std::string_view SQLiteQuery::getString(int iCol) {
 477   assertx(m_stmt != nullptr);
 478   sqlite3_stmt* stmt = m_stmt->m_stmt;
 479   const char* text =
 480       reinterpret_cast<const char*>(sqlite3_column_text(stmt, iCol));
 481   int size = sqlite3_column_bytes(stmt, iCol);
 482   assertx(size >= 0);
 483   return {text, static_cast<size_t>(size)};
 484 }
 485
 486 Optional<const std::string_view> SQLiteQuery::getNullableString(
 487     int iCol) {
 488   assertx(m_stmt != nullptr);
 489   sqlite3_stmt* stmt = m_stmt->m_stmt;
 490   if (sqlite3_column_type(stmt, iCol) == SQLITE_NULL) {
 491     return {};
 492   }
 493   return {getString(iCol)};
 494 }
 495
 496 std::string_view SQLite::openModeName(SQLite::OpenMode mode) noexcept {
 497   switch(mode) {
 498     case SQLite::OpenMode::ReadOnly:
 499       return "READ";
 500     case SQLite::OpenMode::ReadWrite:
 501       return "READ/WRITE";
 502     case SQLite::OpenMode::ReadWriteCreate:
 503       return "READ/WRITE/CREATE";
 504   }
 505   not_reached();
 506 }
 507
 508 SQLiteQuery::SQLiteQuery(SQLiteStmt& stmt) : m_stmt{&stmt} {}
 509
 510 //==============================================================================
 511 // SQLiteExc.
 512
 513 SQLiteExc::SQLiteExc(int code, std::string sql)
 514     : std::runtime_error{sql.empty()
 515                              ? folly::to<std::string>(
 516                                    "SQLiteExc [", code,
 517                                    "]: ", sqlite3_errstr(code))
 518                              : folly::to<std::string>(
 519                                    "SQLiteExc [", code, "] while running ", sql,
 520                                    ": ", sqlite3_errstr(code))},
 521       m_code{code} {}
 522
 523 }  // namespace HPHP