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 #include "content_sao.h"
  37 #include "remoteplayer.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 = 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_database(NULL),
 118         m_initialized(false),
 119         m_savedir(savedir),
 120         m_dbname(dbname),
 121         m_stmt_begin(NULL),
 122         m_stmt_end(NULL)
 123 {
 124 }
 125
 126 void Database_SQLite3::beginSave()
 127 {
 128         verifyDatabase();
 129         SQLRES(sqlite3_step(m_stmt_begin), SQLITE_DONE,
 130                 "Failed to start SQLite3 transaction");
 131         sqlite3_reset(m_stmt_begin);
 132 }
 133
 134 void Database_SQLite3::endSave()
 135 {
 136         verifyDatabase();
 137         SQLRES(sqlite3_step(m_stmt_end), SQLITE_DONE,
 138                 "Failed to commit SQLite3 transaction");
 139         sqlite3_reset(m_stmt_end);
 140 }
 141
 142 void Database_SQLite3::openDatabase()
 143 {
 144         if (m_database) return;
 145
 146         std::string dbp = m_savedir + DIR_DELIM + m_dbname + ".sqlite";
 147
 148         // Open the database connection
 149
 150         if (!fs::CreateAllDirs(m_savedir)) {
 151                 infostream << "Database_SQLite3: Failed to create directory \""
 152                         << m_savedir << "\"" << std::endl;
 153                 throw FileNotGoodException("Failed to create database "
 154                                 "save directory");
 155         }
 156
 157         bool needs_create = !fs::PathExists(dbp);
 158
 159         SQLOK(sqlite3_open_v2(dbp.c_str(), &m_database,
 160                         SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL),
 161                 std::string("Failed to open SQLite3 database file ") + dbp);
 162
 163         SQLOK(sqlite3_busy_handler(m_database, Database_SQLite3::busyHandler,
 164                 m_busy_handler_data), "Failed to set SQLite3 busy handler");
 165
 166         if (needs_create) {
 167                 createDatabase();
 168         }
 169
 170         std::string query_str = std::string("PRAGMA synchronous = ")
 171                          + itos(g_settings->getU16("sqlite_synchronous"));
 172         SQLOK(sqlite3_exec(m_database, query_str.c_str(), NULL, NULL, NULL),
 173                 "Failed to modify sqlite3 synchronous mode");
 174         SQLOK(sqlite3_exec(m_database, "PRAGMA foreign_keys = ON", NULL, NULL, NULL),
 175                 "Failed to enable sqlite3 foreign key support");
 176 }
 177
 178 void Database_SQLite3::verifyDatabase()
 179 {
 180         if (m_initialized) return;
 181
 182         openDatabase();
 183
 184         PREPARE_STATEMENT(begin, "BEGIN;");
 185         PREPARE_STATEMENT(end, "COMMIT;");
 186
 187         initStatements();
 188
 189         m_initialized = true;
 190 }
 191
 192 Database_SQLite3::~Database_SQLite3()
 193 {
 194         FINALIZE_STATEMENT(m_stmt_begin)
 195         FINALIZE_STATEMENT(m_stmt_end)
 196
 197         SQLOK_ERRSTREAM(sqlite3_close(m_database), "Failed to close database");
 198 }
 199
 200 /*
 201  * Map database
 202  */
 203
 204 MapDatabaseSQLite3::MapDatabaseSQLite3(const std::string &savedir):
 205         Database_SQLite3(savedir, "map"),
 206         MapDatabase(),
 207         m_stmt_read(NULL),
 208         m_stmt_write(NULL),
 209         m_stmt_list(NULL),
 210         m_stmt_delete(NULL)
 211 {
 212
 213 }
 214
 215 MapDatabaseSQLite3::~MapDatabaseSQLite3()
 216 {
 217         FINALIZE_STATEMENT(m_stmt_read)
 218         FINALIZE_STATEMENT(m_stmt_write)
 219         FINALIZE_STATEMENT(m_stmt_list)
 220         FINALIZE_STATEMENT(m_stmt_delete)
 221 }
 222
 223
 224 void MapDatabaseSQLite3::createDatabase()
 225 {
 226         assert(m_database); // Pre-condition
 227
 228         SQLOK(sqlite3_exec(m_database,
 229                 "CREATE TABLE IF NOT EXISTS `blocks` (\n"
 230                         "       `pos` INT PRIMARY KEY,\n"
 231                         "       `data` BLOB\n"
 232                         ");\n",
 233                 NULL, NULL, NULL),
 234                 "Failed to create database table");
 235 }
 236
 237 void MapDatabaseSQLite3::initStatements()
 238 {
 239         PREPARE_STATEMENT(read, "SELECT `data` FROM `blocks` WHERE `pos` = ? LIMIT 1");
 240 #ifdef __ANDROID__
 241         PREPARE_STATEMENT(write,  "INSERT INTO `blocks` (`pos`, `data`) VALUES (?, ?)");
 242 #else
 243         PREPARE_STATEMENT(write, "REPLACE INTO `blocks` (`pos`, `data`) VALUES (?, ?)");
 244 #endif
 245         PREPARE_STATEMENT(delete, "DELETE FROM `blocks` WHERE `pos` = ?");
 246         PREPARE_STATEMENT(list, "SELECT `pos` FROM `blocks`");
 247
 248         verbosestream << "ServerMap: SQLite3 database opened." << std::endl;
 249 }
 250
 251 inline void MapDatabaseSQLite3::bindPos(sqlite3_stmt *stmt, const v3s16 &pos, int index)
 252 {
 253         SQLOK(sqlite3_bind_int64(stmt, index, getBlockAsInteger(pos)),
 254                 "Internal error: failed to bind query at " __FILE__ ":" TOSTRING(__LINE__));
 255 }
 256
 257 bool MapDatabaseSQLite3::deleteBlock(const v3s16 &pos)
 258 {
 259         verifyDatabase();
 260
 261         bindPos(m_stmt_delete, pos);
 262
 263         bool good = sqlite3_step(m_stmt_delete) == SQLITE_DONE;
 264         sqlite3_reset(m_stmt_delete);
 265
 266         if (!good) {
 267                 warningstream << "deleteBlock: Block failed to delete "
 268                         << PP(pos) << ": " << sqlite3_errmsg(m_database) << std::endl;
 269         }
 270         return good;
 271 }
 272
 273 bool MapDatabaseSQLite3::saveBlock(const v3s16 &pos, const std::string &data)
 274 {
 275         verifyDatabase();
 276
 277 #ifdef __ANDROID__
 278         /**
 279          * Note: For some unknown reason SQLite3 fails to REPLACE blocks on Android,
 280          * deleting them and then inserting works.
 281          */
 282         bindPos(m_stmt_read, pos);
 283
 284         if (sqlite3_step(m_stmt_read) == SQLITE_ROW) {
 285                 deleteBlock(pos);
 286         }
 287         sqlite3_reset(m_stmt_read);
 288 #endif
 289
 290         bindPos(m_stmt_write, pos);
 291         SQLOK(sqlite3_bind_blob(m_stmt_write, 2, data.data(), data.size(), NULL),
 292                 "Internal error: failed to bind query at " __FILE__ ":" TOSTRING(__LINE__));
 293
 294         SQLRES(sqlite3_step(m_stmt_write), SQLITE_DONE, "Failed to save block")
 295         sqlite3_reset(m_stmt_write);
 296
 297         return true;
 298 }
 299
 300 void MapDatabaseSQLite3::loadBlock(const v3s16 &pos, std::string *block)
 301 {
 302         verifyDatabase();
 303
 304         bindPos(m_stmt_read, pos);
 305
 306         if (sqlite3_step(m_stmt_read) != SQLITE_ROW) {
 307                 sqlite3_reset(m_stmt_read);
 308                 return;
 309         }
 310
 311         const char *data = (const char *) sqlite3_column_blob(m_stmt_read, 0);
 312         size_t len = sqlite3_column_bytes(m_stmt_read, 0);
 313
 314         *block = (data) ? std::string(data, len) : "";
 315
 316         sqlite3_step(m_stmt_read);
 317         // We should never get more than 1 row, so ok to reset
 318         sqlite3_reset(m_stmt_read);
 319 }
 320
 321 void MapDatabaseSQLite3::listAllLoadableBlocks(std::vector<v3s16> &dst)
 322 {
 323         verifyDatabase();
 324
 325         while (sqlite3_step(m_stmt_list) == SQLITE_ROW)
 326                 dst.push_back(getIntegerAsBlock(sqlite3_column_int64(m_stmt_list, 0)));
 327
 328         sqlite3_reset(m_stmt_list);
 329 }
 330
 331 /*
 332  * Player Database
 333  */
 334
 335 PlayerDatabaseSQLite3::PlayerDatabaseSQLite3(const std::string &savedir):
 336         Database_SQLite3(savedir, "players"),
 337         PlayerDatabase(),
 338         m_stmt_player_load(NULL),
 339         m_stmt_player_add(NULL),
 340         m_stmt_player_update(NULL),
 341         m_stmt_player_remove(NULL),
 342         m_stmt_player_list(NULL),
 343         m_stmt_player_load_inventory(NULL),
 344         m_stmt_player_load_inventory_items(NULL),
 345         m_stmt_player_add_inventory(NULL),
 346         m_stmt_player_add_inventory_items(NULL),
 347         m_stmt_player_remove_inventory(NULL),
 348         m_stmt_player_remove_inventory_items(NULL),
 349         m_stmt_player_metadata_load(NULL),
 350         m_stmt_player_metadata_remove(NULL),
 351         m_stmt_player_metadata_add(NULL)
 352 {
 353
 354 }
 355 PlayerDatabaseSQLite3::~PlayerDatabaseSQLite3()
 356 {
 357         FINALIZE_STATEMENT(m_stmt_player_load)
 358         FINALIZE_STATEMENT(m_stmt_player_add)
 359         FINALIZE_STATEMENT(m_stmt_player_update)
 360         FINALIZE_STATEMENT(m_stmt_player_remove)
 361         FINALIZE_STATEMENT(m_stmt_player_list)
 362         FINALIZE_STATEMENT(m_stmt_player_add_inventory)
 363         FINALIZE_STATEMENT(m_stmt_player_add_inventory_items)
 364         FINALIZE_STATEMENT(m_stmt_player_remove_inventory)
 365         FINALIZE_STATEMENT(m_stmt_player_remove_inventory_items)
 366         FINALIZE_STATEMENT(m_stmt_player_load_inventory)
 367         FINALIZE_STATEMENT(m_stmt_player_load_inventory_items)
 368         FINALIZE_STATEMENT(m_stmt_player_metadata_load)
 369         FINALIZE_STATEMENT(m_stmt_player_metadata_add)
 370         FINALIZE_STATEMENT(m_stmt_player_metadata_remove)
 371 };
 372
 373
 374 void PlayerDatabaseSQLite3::createDatabase()
 375 {
 376         assert(m_database); // Pre-condition
 377
 378         SQLOK(sqlite3_exec(m_database,
 379                 "CREATE TABLE IF NOT EXISTS `player` ("
 380                         "`name` VARCHAR(50) NOT NULL,"
 381                         "`pitch` NUMERIC(11, 4) NOT NULL,"
 382                         "`yaw` NUMERIC(11, 4) NOT NULL,"
 383                         "`posX` NUMERIC(11, 4) NOT NULL,"
 384                         "`posY` NUMERIC(11, 4) NOT NULL,"
 385                         "`posZ` NUMERIC(11, 4) NOT NULL,"
 386                         "`hp` INT NOT NULL,"
 387                         "`breath` INT NOT NULL,"
 388                         "`creation_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,"
 389                         "`modification_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,"
 390                         "PRIMARY KEY (`name`));",
 391                 NULL, NULL, NULL),
 392                 "Failed to create player table");
 393
 394         SQLOK(sqlite3_exec(m_database,
 395                 "CREATE TABLE IF NOT EXISTS `player_metadata` ("
 396                         "    `player` VARCHAR(50) NOT NULL,"
 397                         "    `metadata` VARCHAR(256) NOT NULL,"
 398                         "    `value` TEXT,"
 399                         "    PRIMARY KEY(`player`, `metadata`),"
 400                         "    FOREIGN KEY (`player`) REFERENCES player (`name`) ON DELETE CASCADE );",
 401                 NULL, NULL, NULL),
 402                 "Failed to create player metadata table");
 403
 404         SQLOK(sqlite3_exec(m_database,
 405                 "CREATE TABLE IF NOT EXISTS `player_inventories` ("
 406                         "   `player` VARCHAR(50) NOT NULL,"
 407                         "       `inv_id` INT NOT NULL,"
 408                         "       `inv_width` INT NOT NULL,"
 409                         "       `inv_name` TEXT NOT NULL DEFAULT '',"
 410                         "       `inv_size` INT NOT NULL,"
 411                         "       PRIMARY KEY(player, inv_id),"
 412                         "   FOREIGN KEY (`player`) REFERENCES player (`name`) ON DELETE CASCADE );",
 413                 NULL, NULL, NULL),
 414                 "Failed to create player inventory table");
 415
 416         SQLOK(sqlite3_exec(m_database,
 417                 "CREATE TABLE `player_inventory_items` ("
 418                         "   `player` VARCHAR(50) NOT NULL,"
 419                         "       `inv_id` INT NOT NULL,"
 420                         "       `slot_id` INT NOT NULL,"
 421                         "       `item` TEXT NOT NULL DEFAULT '',"
 422                         "       PRIMARY KEY(player, inv_id, slot_id),"
 423                         "   FOREIGN KEY (`player`) REFERENCES player (`name`) ON DELETE CASCADE );",
 424                 NULL, NULL, NULL),
 425                 "Failed to create player inventory items table");
 426 }
 427
 428 void PlayerDatabaseSQLite3::initStatements()
 429 {
 430         PREPARE_STATEMENT(player_load, "SELECT `pitch`, `yaw`, `posX`, `posY`, `posZ`, `hp`, "
 431                 "`breath`"
 432                 "FROM `player` WHERE `name` = ?")
 433         PREPARE_STATEMENT(player_add, "INSERT INTO `player` (`name`, `pitch`, `yaw`, `posX`, "
 434                 "`posY`, `posZ`, `hp`, `breath`) VALUES (?, ?, ?, ?, ?, ?, ?, ?)")
 435         PREPARE_STATEMENT(player_update, "UPDATE `player` SET `pitch` = ?, `yaw` = ?, "
 436                         "`posX` = ?, `posY` = ?, `posZ` = ?, `hp` = ?, `breath` = ?, "
 437                         "`modification_date` = CURRENT_TIMESTAMP WHERE `name` = ?")
 438         PREPARE_STATEMENT(player_remove, "DELETE FROM `player` WHERE `name` = ?")
 439         PREPARE_STATEMENT(player_list, "SELECT `name` FROM `player`")
 440
 441         PREPARE_STATEMENT(player_add_inventory, "INSERT INTO `player_inventories` "
 442                 "(`player`, `inv_id`, `inv_width`, `inv_name`, `inv_size`) VALUES (?, ?, ?, ?, ?)")
 443         PREPARE_STATEMENT(player_add_inventory_items, "INSERT INTO `player_inventory_items` "
 444                 "(`player`, `inv_id`, `slot_id`, `item`) VALUES (?, ?, ?, ?)")
 445         PREPARE_STATEMENT(player_remove_inventory, "DELETE FROM `player_inventories` "
 446                 "WHERE `player` = ?")
 447         PREPARE_STATEMENT(player_remove_inventory_items, "DELETE FROM `player_inventory_items` "
 448                 "WHERE `player` = ?")
 449         PREPARE_STATEMENT(player_load_inventory, "SELECT `inv_id`, `inv_width`, `inv_name`, "
 450                 "`inv_size` FROM `player_inventories` WHERE `player` = ? ORDER BY inv_id")
 451         PREPARE_STATEMENT(player_load_inventory_items, "SELECT `slot_id`, `item` "
 452                 "FROM `player_inventory_items` WHERE `player` = ? AND `inv_id` = ?")
 453
 454         PREPARE_STATEMENT(player_metadata_load, "SELECT `metadata`, `value` FROM "
 455                 "`player_metadata` WHERE `player` = ?")
 456         PREPARE_STATEMENT(player_metadata_add, "INSERT INTO `player_metadata` "
 457                 "(`player`, `metadata`, `value`) VALUES (?, ?, ?)")
 458         PREPARE_STATEMENT(player_metadata_remove, "DELETE FROM `player_metadata` "
 459                 "WHERE `player` = ?")
 460         verbosestream << "ServerEnvironment: SQLite3 database opened (players)." << std::endl;
 461 }
 462
 463 bool PlayerDatabaseSQLite3::playerDataExists(const std::string &name)
 464 {
 465         verifyDatabase();
 466         str_to_sqlite(m_stmt_player_load, 1, name);
 467         bool res = (sqlite3_step(m_stmt_player_load) == SQLITE_ROW);
 468         sqlite3_reset(m_stmt_player_load);
 469         return res;
 470 }
 471
 472 void PlayerDatabaseSQLite3::savePlayer(RemotePlayer *player)
 473 {
 474         PlayerSAO* sao = player->getPlayerSAO();
 475         sanity_check(sao);
 476
 477         const v3f &pos = sao->getBasePosition();
 478         // Begin save in brace is mandatory
 479         if (!playerDataExists(player->getName())) {
 480                 beginSave();
 481                 str_to_sqlite(m_stmt_player_add, 1, player->getName());
 482                 double_to_sqlite(m_stmt_player_add, 2, sao->getPitch());
 483                 double_to_sqlite(m_stmt_player_add, 3, sao->getYaw());
 484                 double_to_sqlite(m_stmt_player_add, 4, pos.X);
 485                 double_to_sqlite(m_stmt_player_add, 5, pos.Y);
 486                 double_to_sqlite(m_stmt_player_add, 6, pos.Z);
 487                 int64_to_sqlite(m_stmt_player_add, 7, sao->getHP());
 488                 int64_to_sqlite(m_stmt_player_add, 8, sao->getBreath());
 489
 490                 sqlite3_vrfy(sqlite3_step(m_stmt_player_add), SQLITE_DONE);
 491                 sqlite3_reset(m_stmt_player_add);
 492         } else {
 493                 beginSave();
 494                 double_to_sqlite(m_stmt_player_update, 1, sao->getPitch());
 495                 double_to_sqlite(m_stmt_player_update, 2, sao->getYaw());
 496                 double_to_sqlite(m_stmt_player_update, 3, pos.X);
 497                 double_to_sqlite(m_stmt_player_update, 4, pos.Y);
 498                 double_to_sqlite(m_stmt_player_update, 5, pos.Z);
 499                 int64_to_sqlite(m_stmt_player_update, 6, sao->getHP());
 500                 int64_to_sqlite(m_stmt_player_update, 7, sao->getBreath());
 501                 str_to_sqlite(m_stmt_player_update, 8, player->getName());
 502
 503                 sqlite3_vrfy(sqlite3_step(m_stmt_player_update), SQLITE_DONE);
 504                 sqlite3_reset(m_stmt_player_update);
 505         }
 506
 507         // Write player inventories
 508         str_to_sqlite(m_stmt_player_remove_inventory, 1, player->getName());
 509         sqlite3_vrfy(sqlite3_step(m_stmt_player_remove_inventory), SQLITE_DONE);
 510         sqlite3_reset(m_stmt_player_remove_inventory);
 511
 512         str_to_sqlite(m_stmt_player_remove_inventory_items, 1, player->getName());
 513         sqlite3_vrfy(sqlite3_step(m_stmt_player_remove_inventory_items), SQLITE_DONE);
 514         sqlite3_reset(m_stmt_player_remove_inventory_items);
 515
 516         std::vector<const InventoryList*> inventory_lists = sao->getInventory()->getLists();
 517         for (u16 i = 0; i < inventory_lists.size(); i++) {
 518                 const InventoryList* list = inventory_lists[i];
 519
 520                 str_to_sqlite(m_stmt_player_add_inventory, 1, player->getName());
 521                 int_to_sqlite(m_stmt_player_add_inventory, 2, i);
 522                 int_to_sqlite(m_stmt_player_add_inventory, 3, list->getWidth());
 523                 str_to_sqlite(m_stmt_player_add_inventory, 4, list->getName());
 524                 int_to_sqlite(m_stmt_player_add_inventory, 5, list->getSize());
 525                 sqlite3_vrfy(sqlite3_step(m_stmt_player_add_inventory), SQLITE_DONE);
 526                 sqlite3_reset(m_stmt_player_add_inventory);
 527
 528                 for (u32 j = 0; j < list->getSize(); j++) {
 529                         std::ostringstream os;
 530                         list->getItem(j).serialize(os);
 531                         std::string itemStr = os.str();
 532
 533                         str_to_sqlite(m_stmt_player_add_inventory_items, 1, player->getName());
 534                         int_to_sqlite(m_stmt_player_add_inventory_items, 2, i);
 535                         int_to_sqlite(m_stmt_player_add_inventory_items, 3, j);
 536                         str_to_sqlite(m_stmt_player_add_inventory_items, 4, itemStr);
 537                         sqlite3_vrfy(sqlite3_step(m_stmt_player_add_inventory_items), SQLITE_DONE);
 538                         sqlite3_reset(m_stmt_player_add_inventory_items);
 539                 }
 540         }
 541
 542         str_to_sqlite(m_stmt_player_metadata_remove, 1, player->getName());
 543         sqlite3_vrfy(sqlite3_step(m_stmt_player_metadata_remove), SQLITE_DONE);
 544         sqlite3_reset(m_stmt_player_metadata_remove);
 545
 546         const PlayerAttributes &attrs = sao->getExtendedAttributes();
 547         for (PlayerAttributes::const_iterator it = attrs.begin(); it != attrs.end(); ++it) {
 548                 str_to_sqlite(m_stmt_player_metadata_add, 1, player->getName());
 549                 str_to_sqlite(m_stmt_player_metadata_add, 2, it->first);
 550                 str_to_sqlite(m_stmt_player_metadata_add, 3, it->second);
 551                 sqlite3_vrfy(sqlite3_step(m_stmt_player_metadata_add), SQLITE_DONE);
 552                 sqlite3_reset(m_stmt_player_metadata_add);
 553         }
 554
 555         endSave();
 556 }
 557
 558 bool PlayerDatabaseSQLite3::loadPlayer(RemotePlayer *player, PlayerSAO *sao)
 559 {
 560         verifyDatabase();
 561
 562         str_to_sqlite(m_stmt_player_load, 1, player->getName());
 563         if (sqlite3_step(m_stmt_player_load) != SQLITE_ROW) {
 564                 sqlite3_reset(m_stmt_player_load);
 565                 return false;
 566         }
 567         sao->setPitch(sqlite_to_float(m_stmt_player_load, 0));
 568         sao->setYaw(sqlite_to_float(m_stmt_player_load, 1));
 569         sao->setBasePosition(sqlite_to_v3f(m_stmt_player_load, 2));
 570         sao->setHPRaw((s16) MYMIN(sqlite_to_int(m_stmt_player_load, 5), S16_MAX));
 571         sao->setBreath((u16) MYMIN(sqlite_to_int(m_stmt_player_load, 6), U16_MAX), false);
 572         sqlite3_reset(m_stmt_player_load);
 573
 574         // Load inventory
 575         str_to_sqlite(m_stmt_player_load_inventory, 1, player->getName());
 576         while (sqlite3_step(m_stmt_player_load_inventory) == SQLITE_ROW) {
 577                 InventoryList *invList = player->inventory.addList(
 578                         sqlite_to_string(m_stmt_player_load_inventory, 2),
 579                         sqlite_to_uint(m_stmt_player_load_inventory, 3));
 580                 invList->setWidth(sqlite_to_uint(m_stmt_player_load_inventory, 1));
 581
 582                 u32 invId = sqlite_to_uint(m_stmt_player_load_inventory, 0);
 583
 584                 str_to_sqlite(m_stmt_player_load_inventory_items, 1, player->getName());
 585                 int_to_sqlite(m_stmt_player_load_inventory_items, 2, invId);
 586                 while (sqlite3_step(m_stmt_player_load_inventory_items) == SQLITE_ROW) {
 587                         const std::string itemStr = sqlite_to_string(m_stmt_player_load_inventory_items, 1);
 588                         if (itemStr.length() > 0) {
 589                                 ItemStack stack;
 590                                 stack.deSerialize(itemStr);
 591                                 invList->addItem(sqlite_to_uint(m_stmt_player_load_inventory_items, 0), stack);
 592                         }
 593                 }
 594                 sqlite3_reset(m_stmt_player_load_inventory_items);
 595         }
 596
 597         sqlite3_reset(m_stmt_player_load_inventory);
 598
 599         str_to_sqlite(m_stmt_player_metadata_load, 1, sao->getPlayer()->getName());
 600         while (sqlite3_step(m_stmt_player_metadata_load) == SQLITE_ROW) {
 601                 std::string attr = sqlite_to_string(m_stmt_player_metadata_load, 0);
 602                 std::string value = sqlite_to_string(m_stmt_player_metadata_load, 1);
 603
 604                 sao->setExtendedAttribute(attr, value);
 605         }
 606         sqlite3_reset(m_stmt_player_metadata_load);
 607         return true;
 608 }
 609
 610 bool PlayerDatabaseSQLite3::removePlayer(const std::string &name)
 611 {
 612         if (!playerDataExists(name))
 613                 return false;
 614
 615         str_to_sqlite(m_stmt_player_remove, 1, name);
 616         sqlite3_vrfy(sqlite3_step(m_stmt_player_remove), SQLITE_DONE);
 617         sqlite3_reset(m_stmt_player_remove);
 618         return true;
 619 }
 620
 621 void PlayerDatabaseSQLite3::listPlayers(std::vector<std::string> &res)
 622 {
 623         verifyDatabase();
 624
 625         while (sqlite3_step(m_stmt_player_list) == SQLITE_ROW)
 626                 res.push_back(sqlite_to_string(m_stmt_player_list, 0));
 627
 628         sqlite3_reset(m_stmt_player_list);
 629 }