src/database/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 #include "remoteplayer.h"
  37 #include "server/player_sao.h"
  38
  39 #include <cassert>
  40
  41 // When to print messages when the database is being held locked by another process
  42 // Note: I've seen occasional delays of over 250ms while running minetestmapper.
  43 #define BUSY_INFO_TRESHOLD      100     // Print first informational message after 100ms.
  44 #define BUSY_WARNING_TRESHOLD   250     // Print warning message after 250ms. Lag is increased.
  45 #define BUSY_ERROR_TRESHOLD     1000    // Print error message after 1000ms. Significant lag.
  46 #define BUSY_FATAL_TRESHOLD     3000    // Allow SQLITE_BUSY to be returned, which will cause a minetest crash.
  47 #define BUSY_ERROR_INTERVAL     10000   // Safety net: report again every 10 seconds
  48
  49
  50 #define SQLRES(s, r, m) \
  51         if ((s) != (r)) { \
  52                 throw DatabaseException(std::string(m) + ": " +\
  53                                 sqlite3_errmsg(m_database)); \
  54         }
  55 #define SQLOK(s, m) SQLRES(s, SQLITE_OK, m)
  56
  57 #define PREPARE_STATEMENT(name, query) \
  58         SQLOK(sqlite3_prepare_v2(m_database, query, -1, &m_stmt_##name, NULL),\
  59                 "Failed to prepare query '" query "'")
  60
  61 #define SQLOK_ERRSTREAM(s, m)                           \
  62         if ((s) != SQLITE_OK) {                             \
  63                 errorstream << (m) << ": "                      \
  64                         << sqlite3_errmsg(m_database) << std::endl; \
  65         }
  66
  67 #define FINALIZE_STATEMENT(statement) SQLOK_ERRSTREAM(sqlite3_finalize(statement), \
  68         "Failed to finalize " #statement)
  69
  70 int Database_SQLite3::busyHandler(void *data, int count)
  71 {
  72         s64 &first_time = reinterpret_cast<s64 *>(data)[0];
  73         s64 &prev_time = reinterpret_cast<s64 *>(data)[1];
  74         s64 cur_time = porting::getTimeMs();
  75
  76         if (count == 0) {
  77                 first_time = cur_time;
  78                 prev_time = first_time;
  79         } else {
  80                 while (cur_time < prev_time)
  81                         cur_time += s64(1)<<32;
  82         }
  83
  84         if (cur_time - first_time < BUSY_INFO_TRESHOLD) {
  85                 ; // do nothing
  86         } else if (cur_time - first_time >= BUSY_INFO_TRESHOLD &&
  87                         prev_time - first_time < BUSY_INFO_TRESHOLD) {
  88                 infostream << "SQLite3 database has been locked for "
  89                         << cur_time - first_time << " ms." << std::endl;
  90         } else if (cur_time - first_time >= BUSY_WARNING_TRESHOLD &&
  91                         prev_time - first_time < BUSY_WARNING_TRESHOLD) {
  92                 warningstream << "SQLite3 database has been locked for "
  93                         << cur_time - first_time << " ms." << std::endl;
  94         } else if (cur_time - first_time >= BUSY_ERROR_TRESHOLD &&
  95                         prev_time - first_time < BUSY_ERROR_TRESHOLD) {
  96                 errorstream << "SQLite3 database has been locked for "
  97                         << cur_time - first_time << " ms; this causes lag." << std::endl;
  98         } else if (cur_time - first_time >= BUSY_FATAL_TRESHOLD &&
  99                         prev_time - first_time < BUSY_FATAL_TRESHOLD) {
 100                 errorstream << "SQLite3 database has been locked for "
 101                         << cur_time - first_time << " ms - giving up!" << std::endl;
 102         } else if ((cur_time - first_time) / BUSY_ERROR_INTERVAL !=
 103                         (prev_time - first_time) / BUSY_ERROR_INTERVAL) {
 104                 // Safety net: keep reporting every BUSY_ERROR_INTERVAL
 105                 errorstream << "SQLite3 database has been locked for "
 106                         << (cur_time - first_time) / 1000 << " seconds!" << std::endl;
 107         }
 108
 109         prev_time = cur_time;
 110
 111         // Make sqlite transaction fail if delay exceeds BUSY_FATAL_TRESHOLD
 112         return cur_time - first_time < BUSY_FATAL_TRESHOLD;
 113 }
 114
 115
 116 Database_SQLite3::Database_SQLite3(const std::string &savedir, const std::string &dbname) :
 117         m_savedir(savedir),
 118         m_dbname(dbname)
 119 {
 120 }
 121
 122 void Database_SQLite3::beginSave()
 123 {
 124         verifyDatabase();
 125         SQLRES(sqlite3_step(m_stmt_begin), SQLITE_DONE,
 126                 "Failed to start SQLite3 transaction");
 127         sqlite3_reset(m_stmt_begin);
 128 }
 129
 130 void Database_SQLite3::endSave()
 131 {
 132         verifyDatabase();
 133         SQLRES(sqlite3_step(m_stmt_end), SQLITE_DONE,
 134                 "Failed to commit SQLite3 transaction");
 135         sqlite3_reset(m_stmt_end);
 136 }
 137
 138 void Database_SQLite3::openDatabase()
 139 {
 140         if (m_database) return;
 141
 142         std::string dbp = m_savedir + DIR_DELIM + m_dbname + ".sqlite";
 143
 144         // Open the database connection
 145
 146         if (!fs::CreateAllDirs(m_savedir)) {
 147                 infostream << "Database_SQLite3: Failed to create directory \""
 148                         << m_savedir << "\"" << std::endl;
 149                 throw FileNotGoodException("Failed to create database "
 150                                 "save directory");
 151         }
 152
 153         bool needs_create = !fs::PathExists(dbp);
 154
 155         SQLOK(sqlite3_open_v2(dbp.c_str(), &m_database,
 156                         SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL),
 157                 std::string("Failed to open SQLite3 database file ") + dbp);
 158
 159         SQLOK(sqlite3_busy_handler(m_database, Database_SQLite3::busyHandler,
 160                 m_busy_handler_data), "Failed to set SQLite3 busy handler");
 161
 162         if (needs_create) {
 163                 createDatabase();
 164         }
 165
 166         std::string query_str = std::string("PRAGMA synchronous = ")
 167                          + itos(g_settings->getU16("sqlite_synchronous"));
 168         SQLOK(sqlite3_exec(m_database, query_str.c_str(), NULL, NULL, NULL),
 169                 "Failed to modify sqlite3 synchronous mode");
 170         SQLOK(sqlite3_exec(m_database, "PRAGMA foreign_keys = ON", NULL, NULL, NULL),
 171                 "Failed to enable sqlite3 foreign key support");
 172 }
 173
 174 void Database_SQLite3::verifyDatabase()
 175 {
 176         if (m_initialized) return;
 177
 178         openDatabase();
 179
 180         PREPARE_STATEMENT(begin, "BEGIN;");
 181         PREPARE_STATEMENT(end, "COMMIT;");
 182
 183         initStatements();
 184
 185         m_initialized = true;
 186 }
 187
 188 Database_SQLite3::~Database_SQLite3()
 189 {
 190         FINALIZE_STATEMENT(m_stmt_begin)
 191         FINALIZE_STATEMENT(m_stmt_end)
 192
 193         SQLOK_ERRSTREAM(sqlite3_close(m_database), "Failed to close database");
 194 }
 195
 196 /*
 197  * Map database
 198  */
 199
 200 MapDatabaseSQLite3::MapDatabaseSQLite3(const std::string &savedir):
 201         Database_SQLite3(savedir, "map"),
 202         MapDatabase()
 203 {
 204 }
 205
 206 MapDatabaseSQLite3::~MapDatabaseSQLite3()
 207 {
 208         FINALIZE_STATEMENT(m_stmt_read)
 209         FINALIZE_STATEMENT(m_stmt_write)
 210         FINALIZE_STATEMENT(m_stmt_list)
 211         FINALIZE_STATEMENT(m_stmt_delete)
 212 }
 213
 214
 215 void MapDatabaseSQLite3::createDatabase()
 216 {
 217         assert(m_database); // Pre-condition
 218
 219         SQLOK(sqlite3_exec(m_database,
 220                 "CREATE TABLE IF NOT EXISTS `blocks` (\n"
 221                         "       `pos` INT PRIMARY KEY,\n"
 222                         "       `data` BLOB\n"
 223                         ");\n",
 224                 NULL, NULL, NULL),
 225                 "Failed to create database table");
 226 }
 227
 228 void MapDatabaseSQLite3::initStatements()
 229 {
 230         PREPARE_STATEMENT(read, "SELECT `data` FROM `blocks` WHERE `pos` = ? LIMIT 1");
 231 #ifdef __ANDROID__
 232         PREPARE_STATEMENT(write,  "INSERT INTO `blocks` (`pos`, `data`) VALUES (?, ?)");
 233 #else
 234         PREPARE_STATEMENT(write, "REPLACE INTO `blocks` (`pos`, `data`) VALUES (?, ?)");
 235 #endif
 236         PREPARE_STATEMENT(delete, "DELETE FROM `blocks` WHERE `pos` = ?");
 237         PREPARE_STATEMENT(list, "SELECT `pos` FROM `blocks`");
 238
 239         verbosestream << "ServerMap: SQLite3 database opened." << std::endl;
 240 }
 241
 242 inline void MapDatabaseSQLite3::bindPos(sqlite3_stmt *stmt, const v3s16 &pos, int index)
 243 {
 244         SQLOK(sqlite3_bind_int64(stmt, index, getBlockAsInteger(pos)),
 245                 "Internal error: failed to bind query at " __FILE__ ":" TOSTRING(__LINE__));
 246 }
 247
 248 bool MapDatabaseSQLite3::deleteBlock(const v3s16 &pos)
 249 {
 250         verifyDatabase();
 251
 252         bindPos(m_stmt_delete, pos);
 253
 254         bool good = sqlite3_step(m_stmt_delete) == SQLITE_DONE;
 255         sqlite3_reset(m_stmt_delete);
 256
 257         if (!good) {
 258                 warningstream << "deleteBlock: Block failed to delete "
 259                         << PP(pos) << ": " << sqlite3_errmsg(m_database) << std::endl;
 260         }
 261         return good;
 262 }
 263
 264 bool MapDatabaseSQLite3::saveBlock(const v3s16 &pos, const std::string &data)
 265 {
 266         verifyDatabase();
 267
 268 #ifdef __ANDROID__
 269         /**
 270          * Note: For some unknown reason SQLite3 fails to REPLACE blocks on Android,
 271          * deleting them and then inserting works.
 272          */
 273         bindPos(m_stmt_read, pos);
 274
 275         if (sqlite3_step(m_stmt_read) == SQLITE_ROW) {
 276                 deleteBlock(pos);
 277         }
 278         sqlite3_reset(m_stmt_read);
 279 #endif
 280
 281         bindPos(m_stmt_write, pos);
 282         SQLOK(sqlite3_bind_blob(m_stmt_write, 2, data.data(), data.size(), NULL),
 283                 "Internal error: failed to bind query at " __FILE__ ":" TOSTRING(__LINE__));
 284
 285         SQLRES(sqlite3_step(m_stmt_write), SQLITE_DONE, "Failed to save block")
 286         sqlite3_reset(m_stmt_write);
 287
 288         return true;
 289 }
 290
 291 void MapDatabaseSQLite3::loadBlock(const v3s16 &pos, std::string *block)
 292 {
 293         verifyDatabase();
 294
 295         bindPos(m_stmt_read, pos);
 296
 297         if (sqlite3_step(m_stmt_read) != SQLITE_ROW) {
 298                 sqlite3_reset(m_stmt_read);
 299                 return;
 300         }
 301
 302         const char *data = (const char *) sqlite3_column_blob(m_stmt_read, 0);
 303         size_t len = sqlite3_column_bytes(m_stmt_read, 0);
 304
 305         if (data)
 306                 block->assign(data, len);
 307         else
 308                 block->clear();
 309
 310         sqlite3_step(m_stmt_read);
 311         // We should never get more than 1 row, so ok to reset
 312         sqlite3_reset(m_stmt_read);
 313 }
 314
 315 void MapDatabaseSQLite3::listAllLoadableBlocks(std::vector<v3s16> &dst)
 316 {
 317         verifyDatabase();
 318
 319         while (sqlite3_step(m_stmt_list) == SQLITE_ROW)
 320                 dst.push_back(getIntegerAsBlock(sqlite3_column_int64(m_stmt_list, 0)));
 321
 322         sqlite3_reset(m_stmt_list);
 323 }
 324
 325 /*
 326  * Player Database
 327  */
 328
 329 PlayerDatabaseSQLite3::PlayerDatabaseSQLite3(const std::string &savedir):
 330         Database_SQLite3(savedir, "players"),
 331         PlayerDatabase()
 332 {
 333 }
 334
 335 PlayerDatabaseSQLite3::~PlayerDatabaseSQLite3()
 336 {
 337         FINALIZE_STATEMENT(m_stmt_player_load)
 338         FINALIZE_STATEMENT(m_stmt_player_add)
 339         FINALIZE_STATEMENT(m_stmt_player_update)
 340         FINALIZE_STATEMENT(m_stmt_player_remove)
 341         FINALIZE_STATEMENT(m_stmt_player_list)
 342         FINALIZE_STATEMENT(m_stmt_player_add_inventory)
 343         FINALIZE_STATEMENT(m_stmt_player_add_inventory_items)
 344         FINALIZE_STATEMENT(m_stmt_player_remove_inventory)
 345         FINALIZE_STATEMENT(m_stmt_player_remove_inventory_items)
 346         FINALIZE_STATEMENT(m_stmt_player_load_inventory)
 347         FINALIZE_STATEMENT(m_stmt_player_load_inventory_items)
 348         FINALIZE_STATEMENT(m_stmt_player_metadata_load)
 349         FINALIZE_STATEMENT(m_stmt_player_metadata_add)
 350         FINALIZE_STATEMENT(m_stmt_player_metadata_remove)
 351 };
 352
 353
 354 void PlayerDatabaseSQLite3::createDatabase()
 355 {
 356         assert(m_database); // Pre-condition
 357
 358         SQLOK(sqlite3_exec(m_database,
 359                 "CREATE TABLE IF NOT EXISTS `player` ("
 360                         "`name` VARCHAR(50) NOT NULL,"
 361                         "`pitch` NUMERIC(11, 4) NOT NULL,"
 362                         "`yaw` NUMERIC(11, 4) NOT NULL,"
 363                         "`posX` NUMERIC(11, 4) NOT NULL,"
 364                         "`posY` NUMERIC(11, 4) NOT NULL,"
 365                         "`posZ` NUMERIC(11, 4) NOT NULL,"
 366                         "`hp` INT NOT NULL,"
 367                         "`breath` INT NOT NULL,"
 368                         "`creation_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,"
 369                         "`modification_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,"
 370                         "PRIMARY KEY (`name`));",
 371                 NULL, NULL, NULL),
 372                 "Failed to create player table");
 373
 374         SQLOK(sqlite3_exec(m_database,
 375                 "CREATE TABLE IF NOT EXISTS `player_metadata` ("
 376                         "    `player` VARCHAR(50) NOT NULL,"
 377                         "    `metadata` VARCHAR(256) NOT NULL,"
 378                         "    `value` TEXT,"
 379                         "    PRIMARY KEY(`player`, `metadata`),"
 380                         "    FOREIGN KEY (`player`) REFERENCES player (`name`) ON DELETE CASCADE );",
 381                 NULL, NULL, NULL),
 382                 "Failed to create player metadata table");
 383
 384         SQLOK(sqlite3_exec(m_database,
 385                 "CREATE TABLE IF NOT EXISTS `player_inventories` ("
 386                         "   `player` VARCHAR(50) NOT NULL,"
 387                         "       `inv_id` INT NOT NULL,"
 388                         "       `inv_width` INT NOT NULL,"
 389                         "       `inv_name` TEXT NOT NULL DEFAULT '',"
 390                         "       `inv_size` INT NOT NULL,"
 391                         "       PRIMARY KEY(player, inv_id),"
 392                         "   FOREIGN KEY (`player`) REFERENCES player (`name`) ON DELETE CASCADE );",
 393                 NULL, NULL, NULL),
 394                 "Failed to create player inventory table");
 395
 396         SQLOK(sqlite3_exec(m_database,
 397                 "CREATE TABLE `player_inventory_items` ("
 398                         "   `player` VARCHAR(50) NOT NULL,"
 399                         "       `inv_id` INT NOT NULL,"
 400                         "       `slot_id` INT NOT NULL,"
 401                         "       `item` TEXT NOT NULL DEFAULT '',"
 402                         "       PRIMARY KEY(player, inv_id, slot_id),"
 403                         "   FOREIGN KEY (`player`) REFERENCES player (`name`) ON DELETE CASCADE );",
 404                 NULL, NULL, NULL),
 405                 "Failed to create player inventory items table");
 406 }
 407
 408 void PlayerDatabaseSQLite3::initStatements()
 409 {
 410         PREPARE_STATEMENT(player_load, "SELECT `pitch`, `yaw`, `posX`, `posY`, `posZ`, `hp`, "
 411                 "`breath`"
 412                 "FROM `player` WHERE `name` = ?")
 413         PREPARE_STATEMENT(player_add, "INSERT INTO `player` (`name`, `pitch`, `yaw`, `posX`, "
 414                 "`posY`, `posZ`, `hp`, `breath`) VALUES (?, ?, ?, ?, ?, ?, ?, ?)")
 415         PREPARE_STATEMENT(player_update, "UPDATE `player` SET `pitch` = ?, `yaw` = ?, "
 416                         "`posX` = ?, `posY` = ?, `posZ` = ?, `hp` = ?, `breath` = ?, "
 417                         "`modification_date` = CURRENT_TIMESTAMP WHERE `name` = ?")
 418         PREPARE_STATEMENT(player_remove, "DELETE FROM `player` WHERE `name` = ?")
 419         PREPARE_STATEMENT(player_list, "SELECT `name` FROM `player`")
 420
 421         PREPARE_STATEMENT(player_add_inventory, "INSERT INTO `player_inventories` "
 422                 "(`player`, `inv_id`, `inv_width`, `inv_name`, `inv_size`) VALUES (?, ?, ?, ?, ?)")
 423         PREPARE_STATEMENT(player_add_inventory_items, "INSERT INTO `player_inventory_items` "
 424                 "(`player`, `inv_id`, `slot_id`, `item`) VALUES (?, ?, ?, ?)")
 425         PREPARE_STATEMENT(player_remove_inventory, "DELETE FROM `player_inventories` "
 426                 "WHERE `player` = ?")
 427         PREPARE_STATEMENT(player_remove_inventory_items, "DELETE FROM `player_inventory_items` "
 428                 "WHERE `player` = ?")
 429         PREPARE_STATEMENT(player_load_inventory, "SELECT `inv_id`, `inv_width`, `inv_name`, "
 430                 "`inv_size` FROM `player_inventories` WHERE `player` = ? ORDER BY inv_id")
 431         PREPARE_STATEMENT(player_load_inventory_items, "SELECT `slot_id`, `item` "
 432                 "FROM `player_inventory_items` WHERE `player` = ? AND `inv_id` = ?")
 433
 434         PREPARE_STATEMENT(player_metadata_load, "SELECT `metadata`, `value` FROM "
 435                 "`player_metadata` WHERE `player` = ?")
 436         PREPARE_STATEMENT(player_metadata_add, "INSERT INTO `player_metadata` "
 437                 "(`player`, `metadata`, `value`) VALUES (?, ?, ?)")
 438         PREPARE_STATEMENT(player_metadata_remove, "DELETE FROM `player_metadata` "
 439                 "WHERE `player` = ?")
 440         verbosestream << "ServerEnvironment: SQLite3 database opened (players)." << std::endl;
 441 }
 442
 443 bool PlayerDatabaseSQLite3::playerDataExists(const std::string &name)
 444 {
 445         verifyDatabase();
 446         str_to_sqlite(m_stmt_player_load, 1, name);
 447         bool res = (sqlite3_step(m_stmt_player_load) == SQLITE_ROW);
 448         sqlite3_reset(m_stmt_player_load);
 449         return res;
 450 }
 451
 452 void PlayerDatabaseSQLite3::savePlayer(RemotePlayer *player)
 453 {
 454         PlayerSAO* sao = player->getPlayerSAO();
 455         sanity_check(sao);
 456
 457         const v3f &pos = sao->getBasePosition();
 458         // Begin save in brace is mandatory
 459         if (!playerDataExists(player->getName())) {
 460                 beginSave();
 461                 str_to_sqlite(m_stmt_player_add, 1, player->getName());
 462                 double_to_sqlite(m_stmt_player_add, 2, sao->getLookPitch());
 463                 double_to_sqlite(m_stmt_player_add, 3, sao->getRotation().Y);
 464                 double_to_sqlite(m_stmt_player_add, 4, pos.X);
 465                 double_to_sqlite(m_stmt_player_add, 5, pos.Y);
 466                 double_to_sqlite(m_stmt_player_add, 6, pos.Z);
 467                 int64_to_sqlite(m_stmt_player_add, 7, sao->getHP());
 468                 int64_to_sqlite(m_stmt_player_add, 8, sao->getBreath());
 469
 470                 sqlite3_vrfy(sqlite3_step(m_stmt_player_add), SQLITE_DONE);
 471                 sqlite3_reset(m_stmt_player_add);
 472         } else {
 473                 beginSave();
 474                 double_to_sqlite(m_stmt_player_update, 1, sao->getLookPitch());
 475                 double_to_sqlite(m_stmt_player_update, 2, sao->getRotation().Y);
 476                 double_to_sqlite(m_stmt_player_update, 3, pos.X);
 477                 double_to_sqlite(m_stmt_player_update, 4, pos.Y);
 478                 double_to_sqlite(m_stmt_player_update, 5, pos.Z);
 479                 int64_to_sqlite(m_stmt_player_update, 6, sao->getHP());
 480                 int64_to_sqlite(m_stmt_player_update, 7, sao->getBreath());
 481                 str_to_sqlite(m_stmt_player_update, 8, player->getName());
 482
 483                 sqlite3_vrfy(sqlite3_step(m_stmt_player_update), SQLITE_DONE);
 484                 sqlite3_reset(m_stmt_player_update);
 485         }
 486
 487         // Write player inventories
 488         str_to_sqlite(m_stmt_player_remove_inventory, 1, player->getName());
 489         sqlite3_vrfy(sqlite3_step(m_stmt_player_remove_inventory), SQLITE_DONE);
 490         sqlite3_reset(m_stmt_player_remove_inventory);
 491
 492         str_to_sqlite(m_stmt_player_remove_inventory_items, 1, player->getName());
 493         sqlite3_vrfy(sqlite3_step(m_stmt_player_remove_inventory_items), SQLITE_DONE);
 494         sqlite3_reset(m_stmt_player_remove_inventory_items);
 495
 496         std::vector<const InventoryList*> inventory_lists = sao->getInventory()->getLists();
 497         std::ostringstream oss;
 498         for (u16 i = 0; i < inventory_lists.size(); i++) {
 499                 const InventoryList* list = inventory_lists[i];
 500
 501                 str_to_sqlite(m_stmt_player_add_inventory, 1, player->getName());
 502                 int_to_sqlite(m_stmt_player_add_inventory, 2, i);
 503                 int_to_sqlite(m_stmt_player_add_inventory, 3, list->getWidth());
 504                 str_to_sqlite(m_stmt_player_add_inventory, 4, list->getName());
 505                 int_to_sqlite(m_stmt_player_add_inventory, 5, list->getSize());
 506                 sqlite3_vrfy(sqlite3_step(m_stmt_player_add_inventory), SQLITE_DONE);
 507                 sqlite3_reset(m_stmt_player_add_inventory);
 508
 509                 for (u32 j = 0; j < list->getSize(); j++) {
 510                         oss.str("");
 511                         oss.clear();
 512                         list->getItem(j).serialize(oss);
 513                         std::string itemStr = oss.str();
 514
 515                         str_to_sqlite(m_stmt_player_add_inventory_items, 1, player->getName());
 516                         int_to_sqlite(m_stmt_player_add_inventory_items, 2, i);
 517                         int_to_sqlite(m_stmt_player_add_inventory_items, 3, j);
 518                         str_to_sqlite(m_stmt_player_add_inventory_items, 4, itemStr);
 519                         sqlite3_vrfy(sqlite3_step(m_stmt_player_add_inventory_items), SQLITE_DONE);
 520                         sqlite3_reset(m_stmt_player_add_inventory_items);
 521                 }
 522         }
 523
 524         str_to_sqlite(m_stmt_player_metadata_remove, 1, player->getName());
 525         sqlite3_vrfy(sqlite3_step(m_stmt_player_metadata_remove), SQLITE_DONE);
 526         sqlite3_reset(m_stmt_player_metadata_remove);
 527
 528         const StringMap &attrs = sao->getMeta().getStrings();
 529         for (const auto &attr : attrs) {
 530                 str_to_sqlite(m_stmt_player_metadata_add, 1, player->getName());
 531                 str_to_sqlite(m_stmt_player_metadata_add, 2, attr.first);
 532                 str_to_sqlite(m_stmt_player_metadata_add, 3, attr.second);
 533                 sqlite3_vrfy(sqlite3_step(m_stmt_player_metadata_add), SQLITE_DONE);
 534                 sqlite3_reset(m_stmt_player_metadata_add);
 535         }
 536
 537         endSave();
 538
 539         player->onSuccessfulSave();
 540 }
 541
 542 bool PlayerDatabaseSQLite3::loadPlayer(RemotePlayer *player, PlayerSAO *sao)
 543 {
 544         verifyDatabase();
 545
 546         str_to_sqlite(m_stmt_player_load, 1, player->getName());
 547         if (sqlite3_step(m_stmt_player_load) != SQLITE_ROW) {
 548                 sqlite3_reset(m_stmt_player_load);
 549                 return false;
 550         }
 551         sao->setLookPitch(sqlite_to_float(m_stmt_player_load, 0));
 552         sao->setPlayerYaw(sqlite_to_float(m_stmt_player_load, 1));
 553         sao->setBasePosition(sqlite_to_v3f(m_stmt_player_load, 2));
 554         sao->setHPRaw((u16) MYMIN(sqlite_to_int(m_stmt_player_load, 5), U16_MAX));
 555         sao->setBreath((u16) MYMIN(sqlite_to_int(m_stmt_player_load, 6), U16_MAX), false);
 556         sqlite3_reset(m_stmt_player_load);
 557
 558         // Load inventory
 559         str_to_sqlite(m_stmt_player_load_inventory, 1, player->getName());
 560         while (sqlite3_step(m_stmt_player_load_inventory) == SQLITE_ROW) {
 561                 InventoryList *invList = player->inventory.addList(
 562                         sqlite_to_string(m_stmt_player_load_inventory, 2),
 563                         sqlite_to_uint(m_stmt_player_load_inventory, 3));
 564                 invList->setWidth(sqlite_to_uint(m_stmt_player_load_inventory, 1));
 565
 566                 u32 invId = sqlite_to_uint(m_stmt_player_load_inventory, 0);
 567
 568                 str_to_sqlite(m_stmt_player_load_inventory_items, 1, player->getName());
 569                 int_to_sqlite(m_stmt_player_load_inventory_items, 2, invId);
 570                 while (sqlite3_step(m_stmt_player_load_inventory_items) == SQLITE_ROW) {
 571                         const std::string itemStr = sqlite_to_string(m_stmt_player_load_inventory_items, 1);
 572                         if (itemStr.length() > 0) {
 573                                 ItemStack stack;
 574                                 stack.deSerialize(itemStr);
 575                                 invList->changeItem(sqlite_to_uint(m_stmt_player_load_inventory_items, 0), stack);
 576                         }
 577                 }
 578                 sqlite3_reset(m_stmt_player_load_inventory_items);
 579         }
 580
 581         sqlite3_reset(m_stmt_player_load_inventory);
 582
 583         str_to_sqlite(m_stmt_player_metadata_load, 1, sao->getPlayer()->getName());
 584         while (sqlite3_step(m_stmt_player_metadata_load) == SQLITE_ROW) {
 585                 std::string attr = sqlite_to_string(m_stmt_player_metadata_load, 0);
 586                 std::string value = sqlite_to_string(m_stmt_player_metadata_load, 1);
 587
 588                 sao->getMeta().setString(attr, value);
 589         }
 590         sao->getMeta().setModified(false);
 591         sqlite3_reset(m_stmt_player_metadata_load);
 592         return true;
 593 }
 594
 595 bool PlayerDatabaseSQLite3::removePlayer(const std::string &name)
 596 {
 597         if (!playerDataExists(name))
 598                 return false;
 599
 600         str_to_sqlite(m_stmt_player_remove, 1, name);
 601         sqlite3_vrfy(sqlite3_step(m_stmt_player_remove), SQLITE_DONE);
 602         sqlite3_reset(m_stmt_player_remove);
 603         return true;
 604 }
 605
 606 void PlayerDatabaseSQLite3::listPlayers(std::vector<std::string> &res)
 607 {
 608         verifyDatabase();
 609
 610         while (sqlite3_step(m_stmt_player_list) == SQLITE_ROW)
 611                 res.push_back(sqlite_to_string(m_stmt_player_list, 0));
 612
 613         sqlite3_reset(m_stmt_player_list);
 614 }
 615
 616 /*
 617  * Auth database
 618  */
 619
 620 AuthDatabaseSQLite3::AuthDatabaseSQLite3(const std::string &savedir) :
 621                 Database_SQLite3(savedir, "auth"), AuthDatabase()
 622 {
 623 }
 624
 625 AuthDatabaseSQLite3::~AuthDatabaseSQLite3()
 626 {
 627         FINALIZE_STATEMENT(m_stmt_read)
 628         FINALIZE_STATEMENT(m_stmt_write)
 629         FINALIZE_STATEMENT(m_stmt_create)
 630         FINALIZE_STATEMENT(m_stmt_delete)
 631         FINALIZE_STATEMENT(m_stmt_list_names)
 632         FINALIZE_STATEMENT(m_stmt_read_privs)
 633         FINALIZE_STATEMENT(m_stmt_write_privs)
 634         FINALIZE_STATEMENT(m_stmt_delete_privs)
 635         FINALIZE_STATEMENT(m_stmt_last_insert_rowid)
 636 }
 637
 638 void AuthDatabaseSQLite3::createDatabase()
 639 {
 640         assert(m_database); // Pre-condition
 641
 642         SQLOK(sqlite3_exec(m_database,
 643                 "CREATE TABLE IF NOT EXISTS `auth` ("
 644                         "`id` INTEGER PRIMARY KEY AUTOINCREMENT,"
 645                         "`name` VARCHAR(32) UNIQUE,"
 646                         "`password` VARCHAR(512),"
 647                         "`last_login` INTEGER"
 648                 ");",
 649                 NULL, NULL, NULL),
 650                 "Failed to create auth table");
 651
 652         SQLOK(sqlite3_exec(m_database,
 653                 "CREATE TABLE IF NOT EXISTS `user_privileges` ("
 654                         "`id` INTEGER,"
 655                         "`privilege` VARCHAR(32),"
 656                         "PRIMARY KEY (id, privilege)"
 657                         "CONSTRAINT fk_id FOREIGN KEY (id) REFERENCES auth (id) ON DELETE CASCADE"
 658                 ");",
 659                 NULL, NULL, NULL),
 660                 "Failed to create auth privileges table");
 661 }
 662
 663 void AuthDatabaseSQLite3::initStatements()
 664 {
 665         PREPARE_STATEMENT(read, "SELECT id, name, password, last_login FROM auth WHERE name = ?");
 666         PREPARE_STATEMENT(write, "UPDATE auth set name = ?, password = ?, last_login = ? WHERE id = ?");
 667         PREPARE_STATEMENT(create, "INSERT INTO auth (name, password, last_login) VALUES (?, ?, ?)");
 668         PREPARE_STATEMENT(delete, "DELETE FROM auth WHERE name = ?");
 669
 670         PREPARE_STATEMENT(list_names, "SELECT name FROM auth ORDER BY name DESC");
 671
 672         PREPARE_STATEMENT(read_privs, "SELECT privilege FROM user_privileges WHERE id = ?");
 673         PREPARE_STATEMENT(write_privs, "INSERT OR IGNORE INTO user_privileges (id, privilege) VALUES (?, ?)");
 674         PREPARE_STATEMENT(delete_privs, "DELETE FROM user_privileges WHERE id = ?");
 675
 676         PREPARE_STATEMENT(last_insert_rowid, "SELECT last_insert_rowid()");
 677 }
 678
 679 bool AuthDatabaseSQLite3::getAuth(const std::string &name, AuthEntry &res)
 680 {
 681         verifyDatabase();
 682         str_to_sqlite(m_stmt_read, 1, name);
 683         if (sqlite3_step(m_stmt_read) != SQLITE_ROW) {
 684                 sqlite3_reset(m_stmt_read);
 685                 return false;
 686         }
 687         res.id = sqlite_to_uint(m_stmt_read, 0);
 688         res.name = sqlite_to_string(m_stmt_read, 1);
 689         res.password = sqlite_to_string(m_stmt_read, 2);
 690         res.last_login = sqlite_to_int64(m_stmt_read, 3);
 691         sqlite3_reset(m_stmt_read);
 692
 693         int64_to_sqlite(m_stmt_read_privs, 1, res.id);
 694         while (sqlite3_step(m_stmt_read_privs) == SQLITE_ROW) {
 695                 res.privileges.emplace_back(sqlite_to_string(m_stmt_read_privs, 0));
 696         }
 697         sqlite3_reset(m_stmt_read_privs);
 698
 699         return true;
 700 }
 701
 702 bool AuthDatabaseSQLite3::saveAuth(const AuthEntry &authEntry)
 703 {
 704         beginSave();
 705
 706         str_to_sqlite(m_stmt_write, 1, authEntry.name);
 707         str_to_sqlite(m_stmt_write, 2, authEntry.password);
 708         int64_to_sqlite(m_stmt_write, 3, authEntry.last_login);
 709         int64_to_sqlite(m_stmt_write, 4, authEntry.id);
 710         sqlite3_vrfy(sqlite3_step(m_stmt_write), SQLITE_DONE);
 711         sqlite3_reset(m_stmt_write);
 712
 713         writePrivileges(authEntry);
 714
 715         endSave();
 716         return true;
 717 }
 718
 719 bool AuthDatabaseSQLite3::createAuth(AuthEntry &authEntry)
 720 {
 721         beginSave();
 722
 723         // id autoincrements
 724         str_to_sqlite(m_stmt_create, 1, authEntry.name);
 725         str_to_sqlite(m_stmt_create, 2, authEntry.password);
 726         int64_to_sqlite(m_stmt_create, 3, authEntry.last_login);
 727         sqlite3_vrfy(sqlite3_step(m_stmt_create), SQLITE_DONE);
 728         sqlite3_reset(m_stmt_create);
 729
 730         // obtain id and write back to original authEntry
 731         sqlite3_step(m_stmt_last_insert_rowid);
 732         authEntry.id = sqlite_to_uint(m_stmt_last_insert_rowid, 0);
 733         sqlite3_reset(m_stmt_last_insert_rowid);
 734
 735         writePrivileges(authEntry);
 736
 737         endSave();
 738         return true;
 739 }
 740
 741 bool AuthDatabaseSQLite3::deleteAuth(const std::string &name)
 742 {
 743         verifyDatabase();
 744
 745         str_to_sqlite(m_stmt_delete, 1, name);
 746         sqlite3_vrfy(sqlite3_step(m_stmt_delete), SQLITE_DONE);
 747         int changes = sqlite3_changes(m_database);
 748         sqlite3_reset(m_stmt_delete);
 749
 750         // privileges deleted by foreign key on delete cascade
 751
 752         return changes > 0;
 753 }
 754
 755 void AuthDatabaseSQLite3::listNames(std::vector<std::string> &res)
 756 {
 757         verifyDatabase();
 758
 759         while (sqlite3_step(m_stmt_list_names) == SQLITE_ROW) {
 760                 res.push_back(sqlite_to_string(m_stmt_list_names, 0));
 761         }
 762         sqlite3_reset(m_stmt_list_names);
 763 }
 764
 765 void AuthDatabaseSQLite3::reload()
 766 {
 767         // noop for SQLite
 768 }
 769
 770 void AuthDatabaseSQLite3::writePrivileges(const AuthEntry &authEntry)
 771 {
 772         int64_to_sqlite(m_stmt_delete_privs, 1, authEntry.id);
 773         sqlite3_vrfy(sqlite3_step(m_stmt_delete_privs), SQLITE_DONE);
 774         sqlite3_reset(m_stmt_delete_privs);
 775         for (const std::string &privilege : authEntry.privileges) {
 776                 int64_to_sqlite(m_stmt_write_privs, 1, authEntry.id);
 777                 str_to_sqlite(m_stmt_write_privs, 2, privilege);
 778                 sqlite3_vrfy(sqlite3_step(m_stmt_write_privs), SQLITE_DONE);
 779                 sqlite3_reset(m_stmt_write_privs);
 780         }
 781 }