src/database-sqlite3.cpp

   1 /*
   2 Minetest
   3 Copyright (C) 2013 celeron55, Perttu Ahola <celeron55@gmail.com>
   4
   5 This program is free software; you can redistribute it and/or modify
   6 it under the terms of the GNU Lesser General Public License as published by
   7 the Free Software Foundation; either version 2.1 of the License, or
   8 (at your option) any later version.
   9
  10 This program is distributed in the hope that it will be useful,
  11 but WITHOUT ANY WARRANTY; without even the implied warranty of
  12 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  13 GNU Lesser General Public License for more details.
  14
  15 You should have received a copy of the GNU Lesser General Public License along
  16 with this program; if not, write to the Free Software Foundation, Inc.,
  17 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
  18 */
  19
  20 /*
  21 SQLite format specification:
  22         blocks:
  23                 (PK) INT id
  24                 BLOB data
  25 */
  26
  27
  28 #include "database-sqlite3.h"
  29
  30 #include "log.h"
  31 #include "filesys.h"
  32 #include "exceptions.h"
  33 #include "settings.h"
  34 #include "porting.h"
  35 #include "util/string.h"
  36
  37 #include <cassert>
  38
  39 // When to print messages when the database is being held locked by another process
  40 // Note: I've seen occasional delays of over 250ms while running minetestmapper.
  41 #define BUSY_INFO_TRESHOLD      100     // Print first informational message after 100ms.
  42 #define BUSY_WARNING_TRESHOLD   250     // Print warning message after 250ms. Lag is increased.
  43 #define BUSY_ERROR_TRESHOLD     1000    // Print error message after 1000ms. Significant lag.
  44 #define BUSY_FATAL_TRESHOLD     3000    // Allow SQLITE_BUSY to be returned, which will cause a minetest crash.
  45 #define BUSY_ERROR_INTERVAL     10000   // Safety net: report again every 10 seconds
  46
  47
  48 #define SQLRES(s, r) \
  49         if ((s) != (r)) { \
  50                 throw FileNotGoodException(std::string(\
  51                                         "SQLite3 database error (" \
  52                                         __FILE__ ":" TOSTRING(__LINE__) \
  53                                         "): ") +\
  54                                 sqlite3_errmsg(m_database)); \
  55         }
  56 #define SQLOK(s) SQLRES(s, SQLITE_OK)
  57
  58 #define PREPARE_STATEMENT(name, query) \
  59         SQLOK(sqlite3_prepare_v2(m_database, query, -1, &m_stmt_##name, NULL))
  60
  61 #define FINALIZE_STATEMENT(statement) \
  62         if (sqlite3_finalize(statement) != SQLITE_OK) { \
  63                 throw FileNotGoodException(std::string( \
  64                         "SQLite3: Failed to finalize " #statement ": ") + \
  65                          sqlite3_errmsg(m_database)); \
  66         }
  67
  68 int Database_SQLite3::busyHandler(void *data, int count)
  69 {
  70         s64 &first_time = reinterpret_cast<s64 *>(data)[0];
  71         s64 &prev_time = reinterpret_cast<s64 *>(data)[1];
  72         s64 cur_time = getTimeMs();
  73
  74         if (count == 0) {
  75                 first_time = cur_time;
  76                 prev_time = first_time;
  77         } else {
  78                 while (cur_time < prev_time)
  79                         cur_time += s64(1)<<32;
  80         }
  81
  82         if (cur_time - first_time < BUSY_INFO_TRESHOLD) {
  83                 ; // do nothing
  84         } else if (cur_time - first_time >= BUSY_INFO_TRESHOLD &&
  85                         prev_time - first_time < BUSY_INFO_TRESHOLD) {
  86                 infostream << "SQLite3 database has been locked for "
  87                         << cur_time - first_time << " ms." << std::endl;
  88         } else if (cur_time - first_time >= BUSY_WARNING_TRESHOLD &&
  89                         prev_time - first_time < BUSY_WARNING_TRESHOLD) {
  90                 warningstream << "Sqlite3 database has been locked for "
  91                         << cur_time - first_time << " ms." << std::endl;
  92         } else if (cur_time - first_time >= BUSY_ERROR_TRESHOLD &&
  93                         prev_time - first_time < BUSY_ERROR_TRESHOLD) {
  94                 errorstream << "SQLite3 database has been locked for "
  95                         << cur_time - first_time << " ms; this causes lag." << std::endl;
  96         } else if (cur_time - first_time >= BUSY_FATAL_TRESHOLD &&
  97                         prev_time - first_time < BUSY_FATAL_TRESHOLD) {
  98                 errorstream << "Sqlite3 database has been locked for "
  99                         << cur_time - first_time << " ms - giving up!" << std::endl;
 100         } else if ((cur_time - first_time) / BUSY_ERROR_INTERVAL !=
 101                         (prev_time - first_time) / BUSY_ERROR_INTERVAL) {
 102                 // Safety net: keep reporting every BUSY_ERROR_INTERVAL
 103                 errorstream << "SQLite3 database has been locked for "
 104                         << (cur_time - first_time) / 1000 << " seconds!" << std::endl;
 105         }
 106
 107         prev_time = cur_time;
 108
 109         // Make sqlite transaction fail if delay exceeds BUSY_FATAL_TRESHOLD
 110         return cur_time - first_time < BUSY_FATAL_TRESHOLD;
 111 }
 112
 113
 114 Database_SQLite3::Database_SQLite3(const std::string &savedir) :
 115         m_initialized(false),
 116         m_savedir(savedir),
 117         m_database(NULL),
 118         m_stmt_read(NULL),
 119         m_stmt_write(NULL),
 120         m_stmt_list(NULL),
 121         m_stmt_delete(NULL),
 122         m_stmt_begin(NULL),
 123         m_stmt_end(NULL)
 124 {
 125 }
 126
 127 void Database_SQLite3::beginSave() {
 128         verifyDatabase();
 129         SQLRES(sqlite3_step(m_stmt_begin), SQLITE_DONE);
 130         sqlite3_reset(m_stmt_begin);
 131 }
 132
 133 void Database_SQLite3::endSave() {
 134         verifyDatabase();
 135         SQLRES(sqlite3_step(m_stmt_end), SQLITE_DONE);
 136         sqlite3_reset(m_stmt_end);
 137 }
 138
 139 void Database_SQLite3::openDatabase()
 140 {
 141         if (m_database) return;
 142
 143         std::string dbp = m_savedir + DIR_DELIM + "map.sqlite";
 144
 145         // Open the database connection
 146
 147         if (!fs::CreateAllDirs(m_savedir)) {
 148                 infostream << "Database_SQLite3: Failed to create directory \""
 149                         << m_savedir << "\"" << std::endl;
 150                 throw FileNotGoodException("Failed to create database "
 151                                 "save directory");
 152         }
 153
 154         bool needs_create = !fs::PathExists(dbp);
 155
 156         if (sqlite3_open_v2(dbp.c_str(), &m_database,
 157                         SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE,
 158                         NULL) != SQLITE_OK) {
 159                 errorstream << "SQLite3 database failed to open: "
 160                         << sqlite3_errmsg(m_database) << std::endl;
 161                 throw FileNotGoodException("Cannot open database file");
 162         }
 163
 164         if (sqlite3_busy_handler(m_database, sqlite3BusyHandler, busy_handler_data) != SQLITE_OK) {
 165                 errorstream << "SQLite3 database failed to set busy handler: "
 166                         << sqlite3_errmsg(m_database) << std::endl;
 167                 throw FileNotGoodException("Failed to set busy handler for sqlite connection");
 168         }
 169
 170         if (needs_create) {
 171                 createDatabase();
 172         }
 173
 174         std::string query_str = std::string("PRAGMA synchronous = ")
 175                          + itos(g_settings->getU16("sqlite_synchronous"));
 176         SQLOK(sqlite3_exec(m_database, query_str.c_str(), NULL, NULL, NULL));
 177 }
 178
 179 void Database_SQLite3::verifyDatabase()
 180 {
 181         if (m_initialized) return;
 182
 183         openDatabase();
 184
 185         PREPARE_STATEMENT(begin, "BEGIN");
 186         PREPARE_STATEMENT(end, "COMMIT");
 187         PREPARE_STATEMENT(read, "SELECT `data` FROM `blocks` WHERE `pos` = ? LIMIT 1");
 188 #ifdef __ANDROID__
 189         PREPARE_STATEMENT(write,  "INSERT INTO `blocks` (`pos`, `data`) VALUES (?, ?)");
 190 #else
 191         PREPARE_STATEMENT(write, "REPLACE INTO `blocks` (`pos`, `data`) VALUES (?, ?)");
 192 #endif
 193         PREPARE_STATEMENT(delete, "DELETE FROM `blocks` WHERE `pos` = ?");
 194         PREPARE_STATEMENT(list, "SELECT `pos` FROM `blocks`");
 195
 196         m_initialized = true;
 197
 198         verbosestream << "ServerMap: SQLite3 database opened." << std::endl;
 199 }
 200
 201 inline void Database_SQLite3::bindPos(sqlite3_stmt *stmt, const v3s16 &pos, int index)
 202 {
 203         SQLOK(sqlite3_bind_int64(stmt, index, getBlockAsInteger(pos)));
 204 }
 205
 206 bool Database_SQLite3::deleteBlock(const v3s16 &pos)
 207 {
 208         verifyDatabase();
 209
 210         bindPos(m_stmt_delete, pos);
 211
 212         bool good = sqlite3_step(m_stmt_delete) == SQLITE_DONE;
 213         sqlite3_reset(m_stmt_delete);
 214
 215         if (!good) {
 216                 warningstream << "deleteBlock: Block failed to delete "
 217                         << PP(pos) << ": " << sqlite3_errmsg(m_database) << std::endl;
 218         }
 219         return good;
 220 }
 221
 222 bool Database_SQLite3::saveBlock(const v3s16 &pos, const std::string &data)
 223 {
 224         verifyDatabase();
 225
 226 #ifdef __ANDROID__
 227         /**
 228          * Note: For some unknown reason SQLite3 fails to REPLACE blocks on Android,
 229          * deleting them and then inserting works.
 230          */
 231         bindPos(m_stmt_read, pos);
 232
 233         if (sqlite3_step(m_stmt_read) == SQLITE_ROW) {
 234                 deleteBlock(pos);
 235         }
 236         sqlite3_reset(m_stmt_read);
 237 #endif
 238
 239         bindPos(m_stmt_write, pos);
 240         SQLOK(sqlite3_bind_blob(m_stmt_write, 2, data.data(), data.size(), NULL));
 241
 242         SQLRES(sqlite3_step(m_stmt_write), SQLITE_DONE)
 243         sqlite3_reset(m_stmt_write);
 244
 245         return true;
 246 }
 247
 248 std::string Database_SQLite3::loadBlock(const v3s16 &pos)
 249 {
 250         verifyDatabase();
 251
 252         bindPos(m_stmt_read, pos);
 253
 254         if (sqlite3_step(m_stmt_read) != SQLITE_ROW) {
 255                 sqlite3_reset(m_stmt_read);
 256                 return "";
 257         }
 258         const char *data = (const char *) sqlite3_column_blob(m_stmt_read, 0);
 259         size_t len = sqlite3_column_bytes(m_stmt_read, 0);
 260
 261         std::string s;
 262         if (data)
 263                 s = std::string(data, len);
 264
 265         sqlite3_step(m_stmt_read);
 266         // We should never get more than 1 row, so ok to reset
 267         sqlite3_reset(m_stmt_read);
 268
 269         return s;
 270 }
 271
 272 void Database_SQLite3::createDatabase()
 273 {
 274         assert(m_database); // Pre-condition
 275         SQLOK(sqlite3_exec(m_database,
 276                 "CREATE TABLE IF NOT EXISTS `blocks` (\n"
 277                 "       `pos` INT PRIMARY KEY,\n"
 278                 "       `data` BLOB\n"
 279                 ");\n",
 280                 NULL, NULL, NULL));
 281 }
 282
 283 void Database_SQLite3::listAllLoadableBlocks(std::vector<v3s16> &dst)
 284 {
 285         verifyDatabase();
 286
 287         while (sqlite3_step(m_stmt_list) == SQLITE_ROW) {
 288                 dst.push_back(getIntegerAsBlock(sqlite3_column_int64(m_stmt_list, 0)));
 289         }
 290         sqlite3_reset(m_stmt_list);
 291 }
 292
 293 Database_SQLite3::~Database_SQLite3()
 294 {
 295         FINALIZE_STATEMENT(m_stmt_read)
 296         FINALIZE_STATEMENT(m_stmt_write)
 297         FINALIZE_STATEMENT(m_stmt_list)
 298         FINALIZE_STATEMENT(m_stmt_begin)
 299         FINALIZE_STATEMENT(m_stmt_end)
 300         FINALIZE_STATEMENT(m_stmt_delete)
 301
 302         if (sqlite3_close(m_database) != SQLITE_OK) {
 303                 errorstream << "Database_SQLite3::~Database_SQLite3(): "
 304                                 << "Failed to close database: "
 305                                 << sqlite3_errmsg(m_database) << std::endl;
 306         }
 307 }
 308