?  search:
summary | log | graphiclog1 | graphiclog2 | commit | commitdiff | tree | refs | edit | fork
blame | history | raw | HEAD
cleanup
[waspsaliva.git] / src / database / database-sqlite3.cpp
blob4560743b98e03ba78b2e39553fd272ea9a68edb0
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 *block = (data) ? std::string(data, len) : "";
306
307 sqlite3_step(m_stmt_read);
308 // We should never get more than 1 row, so ok to reset
309 sqlite3_reset(m_stmt_read);
310 }
311
312 void MapDatabaseSQLite3::listAllLoadableBlocks(std::vector<v3s16> &dst)
313 {
314 verifyDatabase();
315
316 while (sqlite3_step(m_stmt_list) == SQLITE_ROW)
317 dst.push_back(getIntegerAsBlock(sqlite3_column_int64(m_stmt_list, 0)));
318
319 sqlite3_reset(m_stmt_list);
320 }
321
322 /*
323 * Player Database
324 */
325
326 PlayerDatabaseSQLite3::PlayerDatabaseSQLite3(const std::string &savedir):
327 Database_SQLite3(savedir, "players"),
328 PlayerDatabase()
329 {
330 }
331
332 PlayerDatabaseSQLite3::~PlayerDatabaseSQLite3()
333 {
334 FINALIZE_STATEMENT(m_stmt_player_load)
335 FINALIZE_STATEMENT(m_stmt_player_add)
336 FINALIZE_STATEMENT(m_stmt_player_update)
337 FINALIZE_STATEMENT(m_stmt_player_remove)
338 FINALIZE_STATEMENT(m_stmt_player_list)
339 FINALIZE_STATEMENT(m_stmt_player_add_inventory)
340 FINALIZE_STATEMENT(m_stmt_player_add_inventory_items)
341 FINALIZE_STATEMENT(m_stmt_player_remove_inventory)
342 FINALIZE_STATEMENT(m_stmt_player_remove_inventory_items)
343 FINALIZE_STATEMENT(m_stmt_player_load_inventory)
344 FINALIZE_STATEMENT(m_stmt_player_load_inventory_items)
345 FINALIZE_STATEMENT(m_stmt_player_metadata_load)
346 FINALIZE_STATEMENT(m_stmt_player_metadata_add)
347 FINALIZE_STATEMENT(m_stmt_player_metadata_remove)
348 };
349
350
351 void PlayerDatabaseSQLite3::createDatabase()
352 {
353 assert(m_database); // Pre-condition
354
355 SQLOK(sqlite3_exec(m_database,
356 "CREATE TABLE IF NOT EXISTS `player` ("
357 "`name` VARCHAR(50) NOT NULL,"
358 "`pitch` NUMERIC(11, 4) NOT NULL,"
359 "`yaw` NUMERIC(11, 4) NOT NULL,"
360 "`posX` NUMERIC(11, 4) NOT NULL,"
361 "`posY` NUMERIC(11, 4) NOT NULL,"
362 "`posZ` NUMERIC(11, 4) NOT NULL,"
363 "`hp` INT NOT NULL,"
364 "`breath` INT NOT NULL,"
365 "`creation_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,"
366 "`modification_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,"
367 "PRIMARY KEY (`name`));",
368 NULL, NULL, NULL),
369 "Failed to create player table");
370
371 SQLOK(sqlite3_exec(m_database,
372 "CREATE TABLE IF NOT EXISTS `player_metadata` ("
373 " `player` VARCHAR(50) NOT NULL,"
374 " `metadata` VARCHAR(256) NOT NULL,"
375 " `value` TEXT,"
376 " PRIMARY KEY(`player`, `metadata`),"
377 " FOREIGN KEY (`player`) REFERENCES player (`name`) ON DELETE CASCADE );",
378 NULL, NULL, NULL),
379 "Failed to create player metadata table");
380
381 SQLOK(sqlite3_exec(m_database,
382 "CREATE TABLE IF NOT EXISTS `player_inventories` ("
383 " `player` VARCHAR(50) NOT NULL,"
384 " `inv_id` INT NOT NULL,"
385 " `inv_width` INT NOT NULL,"
386 " `inv_name` TEXT NOT NULL DEFAULT '',"
387 " `inv_size` INT NOT NULL,"
388 " PRIMARY KEY(player, inv_id),"
389 " FOREIGN KEY (`player`) REFERENCES player (`name`) ON DELETE CASCADE );",
390 NULL, NULL, NULL),
391 "Failed to create player inventory table");
392
393 SQLOK(sqlite3_exec(m_database,
394 "CREATE TABLE `player_inventory_items` ("
395 " `player` VARCHAR(50) NOT NULL,"
396 " `inv_id` INT NOT NULL,"
397 " `slot_id` INT NOT NULL,"
398 " `item` TEXT NOT NULL DEFAULT '',"
399 " PRIMARY KEY(player, inv_id, slot_id),"
400 " FOREIGN KEY (`player`) REFERENCES player (`name`) ON DELETE CASCADE );",
401 NULL, NULL, NULL),
402 "Failed to create player inventory items table");
403 }
404
405 void PlayerDatabaseSQLite3::initStatements()
406 {
407 PREPARE_STATEMENT(player_load, "SELECT `pitch`, `yaw`, `posX`, `posY`, `posZ`, `hp`, "
408 "`breath`"
409 "FROM `player` WHERE `name` = ?")
410 PREPARE_STATEMENT(player_add, "INSERT INTO `player` (`name`, `pitch`, `yaw`, `posX`, "
411 "`posY`, `posZ`, `hp`, `breath`) VALUES (?, ?, ?, ?, ?, ?, ?, ?)")
412 PREPARE_STATEMENT(player_update, "UPDATE `player` SET `pitch` = ?, `yaw` = ?, "
413 "`posX` = ?, `posY` = ?, `posZ` = ?, `hp` = ?, `breath` = ?, "
414 "`modification_date` = CURRENT_TIMESTAMP WHERE `name` = ?")
415 PREPARE_STATEMENT(player_remove, "DELETE FROM `player` WHERE `name` = ?")
416 PREPARE_STATEMENT(player_list, "SELECT `name` FROM `player`")
417
418 PREPARE_STATEMENT(player_add_inventory, "INSERT INTO `player_inventories` "
419 "(`player`, `inv_id`, `inv_width`, `inv_name`, `inv_size`) VALUES (?, ?, ?, ?, ?)")
420 PREPARE_STATEMENT(player_add_inventory_items, "INSERT INTO `player_inventory_items` "
421 "(`player`, `inv_id`, `slot_id`, `item`) VALUES (?, ?, ?, ?)")
422 PREPARE_STATEMENT(player_remove_inventory, "DELETE FROM `player_inventories` "
423 "WHERE `player` = ?")
424 PREPARE_STATEMENT(player_remove_inventory_items, "DELETE FROM `player_inventory_items` "
425 "WHERE `player` = ?")
426 PREPARE_STATEMENT(player_load_inventory, "SELECT `inv_id`, `inv_width`, `inv_name`, "
427 "`inv_size` FROM `player_inventories` WHERE `player` = ? ORDER BY inv_id")
428 PREPARE_STATEMENT(player_load_inventory_items, "SELECT `slot_id`, `item` "
429 "FROM `player_inventory_items` WHERE `player` = ? AND `inv_id` = ?")
430
431 PREPARE_STATEMENT(player_metadata_load, "SELECT `metadata`, `value` FROM "
432 "`player_metadata` WHERE `player` = ?")
433 PREPARE_STATEMENT(player_metadata_add, "INSERT INTO `player_metadata` "
434 "(`player`, `metadata`, `value`) VALUES (?, ?, ?)")
435 PREPARE_STATEMENT(player_metadata_remove, "DELETE FROM `player_metadata` "
436 "WHERE `player` = ?")
437 verbosestream << "ServerEnvironment: SQLite3 database opened (players)." << std::endl;
438 }
439
440 bool PlayerDatabaseSQLite3::playerDataExists(const std::string &name)
441 {
442 verifyDatabase();
443 str_to_sqlite(m_stmt_player_load, 1, name);
444 bool res = (sqlite3_step(m_stmt_player_load) == SQLITE_ROW);
445 sqlite3_reset(m_stmt_player_load);
446 return res;
447 }
448
449 void PlayerDatabaseSQLite3::savePlayer(RemotePlayer *player)
450 {
451 PlayerSAO* sao = player->getPlayerSAO();
452 sanity_check(sao);
453
454 const v3f &pos = sao->getBasePosition();
455 // Begin save in brace is mandatory
456 if (!playerDataExists(player->getName())) {
457 beginSave();
458 str_to_sqlite(m_stmt_player_add, 1, player->getName());
459 double_to_sqlite(m_stmt_player_add, 2, sao->getLookPitch());
460 double_to_sqlite(m_stmt_player_add, 3, sao->getRotation().Y);
461 double_to_sqlite(m_stmt_player_add, 4, pos.X);
462 double_to_sqlite(m_stmt_player_add, 5, pos.Y);
463 double_to_sqlite(m_stmt_player_add, 6, pos.Z);
464 int64_to_sqlite(m_stmt_player_add, 7, sao->getHP());
465 int64_to_sqlite(m_stmt_player_add, 8, sao->getBreath());
466
467 sqlite3_vrfy(sqlite3_step(m_stmt_player_add), SQLITE_DONE);
468 sqlite3_reset(m_stmt_player_add);
469 } else {
470 beginSave();
471 double_to_sqlite(m_stmt_player_update, 1, sao->getLookPitch());
472 double_to_sqlite(m_stmt_player_update, 2, sao->getRotation().Y);
473 double_to_sqlite(m_stmt_player_update, 3, pos.X);
474 double_to_sqlite(m_stmt_player_update, 4, pos.Y);
475 double_to_sqlite(m_stmt_player_update, 5, pos.Z);
476 int64_to_sqlite(m_stmt_player_update, 6, sao->getHP());
477 int64_to_sqlite(m_stmt_player_update, 7, sao->getBreath());
478 str_to_sqlite(m_stmt_player_update, 8, player->getName());
479
480 sqlite3_vrfy(sqlite3_step(m_stmt_player_update), SQLITE_DONE);
481 sqlite3_reset(m_stmt_player_update);
482 }
483
484 // Write player inventories
485 str_to_sqlite(m_stmt_player_remove_inventory, 1, player->getName());
486 sqlite3_vrfy(sqlite3_step(m_stmt_player_remove_inventory), SQLITE_DONE);
487 sqlite3_reset(m_stmt_player_remove_inventory);
488
489 str_to_sqlite(m_stmt_player_remove_inventory_items, 1, player->getName());
490 sqlite3_vrfy(sqlite3_step(m_stmt_player_remove_inventory_items), SQLITE_DONE);
491 sqlite3_reset(m_stmt_player_remove_inventory_items);
492
493 std::vector<const InventoryList*> inventory_lists = sao->getInventory()->getLists();
494 for (u16 i = 0; i < inventory_lists.size(); i++) {
495 const InventoryList* list = inventory_lists[i];
496
497 str_to_sqlite(m_stmt_player_add_inventory, 1, player->getName());
498 int_to_sqlite(m_stmt_player_add_inventory, 2, i);
499 int_to_sqlite(m_stmt_player_add_inventory, 3, list->getWidth());
500 str_to_sqlite(m_stmt_player_add_inventory, 4, list->getName());
501 int_to_sqlite(m_stmt_player_add_inventory, 5, list->getSize());
502 sqlite3_vrfy(sqlite3_step(m_stmt_player_add_inventory), SQLITE_DONE);
503 sqlite3_reset(m_stmt_player_add_inventory);
504
505 for (u32 j = 0; j < list->getSize(); j++) {
506 std::ostringstream os;
507 list->getItem(j).serialize(os);
508 std::string itemStr = os.str();
509
510 str_to_sqlite(m_stmt_player_add_inventory_items, 1, player->getName());
511 int_to_sqlite(m_stmt_player_add_inventory_items, 2, i);
512 int_to_sqlite(m_stmt_player_add_inventory_items, 3, j);
513 str_to_sqlite(m_stmt_player_add_inventory_items, 4, itemStr);
514 sqlite3_vrfy(sqlite3_step(m_stmt_player_add_inventory_items), SQLITE_DONE);
515 sqlite3_reset(m_stmt_player_add_inventory_items);
516 }
517 }
518
519 str_to_sqlite(m_stmt_player_metadata_remove, 1, player->getName());
520 sqlite3_vrfy(sqlite3_step(m_stmt_player_metadata_remove), SQLITE_DONE);
521 sqlite3_reset(m_stmt_player_metadata_remove);
522
523 const StringMap &attrs = sao->getMeta().getStrings();
524 for (const auto &attr : attrs) {
525 str_to_sqlite(m_stmt_player_metadata_add, 1, player->getName());
526 str_to_sqlite(m_stmt_player_metadata_add, 2, attr.first);
527 str_to_sqlite(m_stmt_player_metadata_add, 3, attr.second);
528 sqlite3_vrfy(sqlite3_step(m_stmt_player_metadata_add), SQLITE_DONE);
529 sqlite3_reset(m_stmt_player_metadata_add);
530 }
531
532 endSave();
533
534 player->onSuccessfulSave();
535 }
536
537 bool PlayerDatabaseSQLite3::loadPlayer(RemotePlayer *player, PlayerSAO *sao)
538 {
539 verifyDatabase();
540
541 str_to_sqlite(m_stmt_player_load, 1, player->getName());
542 if (sqlite3_step(m_stmt_player_load) != SQLITE_ROW) {
543 sqlite3_reset(m_stmt_player_load);
544 return false;
545 }
546 sao->setLookPitch(sqlite_to_float(m_stmt_player_load, 0));
547 sao->setPlayerYaw(sqlite_to_float(m_stmt_player_load, 1));
548 sao->setBasePosition(sqlite_to_v3f(m_stmt_player_load, 2));
549 sao->setHPRaw((u16) MYMIN(sqlite_to_int(m_stmt_player_load, 5), U16_MAX));
550 sao->setBreath((u16) MYMIN(sqlite_to_int(m_stmt_player_load, 6), U16_MAX), false);
551 sqlite3_reset(m_stmt_player_load);
552
553 // Load inventory
554 str_to_sqlite(m_stmt_player_load_inventory, 1, player->getName());
555 while (sqlite3_step(m_stmt_player_load_inventory) == SQLITE_ROW) {
556 InventoryList *invList = player->inventory.addList(
557 sqlite_to_string(m_stmt_player_load_inventory, 2),
558 sqlite_to_uint(m_stmt_player_load_inventory, 3));
559 invList->setWidth(sqlite_to_uint(m_stmt_player_load_inventory, 1));
560
561 u32 invId = sqlite_to_uint(m_stmt_player_load_inventory, 0);
562
563 str_to_sqlite(m_stmt_player_load_inventory_items, 1, player->getName());
564 int_to_sqlite(m_stmt_player_load_inventory_items, 2, invId);
565 while (sqlite3_step(m_stmt_player_load_inventory_items) == SQLITE_ROW) {
566 const std::string itemStr = sqlite_to_string(m_stmt_player_load_inventory_items, 1);
567 if (itemStr.length() > 0) {
568 ItemStack stack;
569 stack.deSerialize(itemStr);
570 invList->changeItem(sqlite_to_uint(m_stmt_player_load_inventory_items, 0), stack);
571 }
572 }
573 sqlite3_reset(m_stmt_player_load_inventory_items);
574 }
575
576 sqlite3_reset(m_stmt_player_load_inventory);
577
578 str_to_sqlite(m_stmt_player_metadata_load, 1, sao->getPlayer()->getName());
579 while (sqlite3_step(m_stmt_player_metadata_load) == SQLITE_ROW) {
580 std::string attr = sqlite_to_string(m_stmt_player_metadata_load, 0);
581 std::string value = sqlite_to_string(m_stmt_player_metadata_load, 1);
582
583 sao->getMeta().setString(attr, value);
584 }
585 sao->getMeta().setModified(false);
586 sqlite3_reset(m_stmt_player_metadata_load);
587 return true;
588 }
589
590 bool PlayerDatabaseSQLite3::removePlayer(const std::string &name)
591 {
592 if (!playerDataExists(name))
593 return false;
594
595 str_to_sqlite(m_stmt_player_remove, 1, name);
596 sqlite3_vrfy(sqlite3_step(m_stmt_player_remove), SQLITE_DONE);
597 sqlite3_reset(m_stmt_player_remove);
598 return true;
599 }
600
601 void PlayerDatabaseSQLite3::listPlayers(std::vector<std::string> &res)
602 {
603 verifyDatabase();
604
605 while (sqlite3_step(m_stmt_player_list) == SQLITE_ROW)
606 res.push_back(sqlite_to_string(m_stmt_player_list, 0));
607
608 sqlite3_reset(m_stmt_player_list);
609 }
610
611 /*
612 * Auth database
613 */
614
615 AuthDatabaseSQLite3::AuthDatabaseSQLite3(const std::string &savedir) :
616 Database_SQLite3(savedir, "auth"), AuthDatabase()
617 {
618 }
619
620 AuthDatabaseSQLite3::~AuthDatabaseSQLite3()
621 {
622 FINALIZE_STATEMENT(m_stmt_read)
623 FINALIZE_STATEMENT(m_stmt_write)
624 FINALIZE_STATEMENT(m_stmt_create)
625 FINALIZE_STATEMENT(m_stmt_delete)
626 FINALIZE_STATEMENT(m_stmt_list_names)
627 FINALIZE_STATEMENT(m_stmt_read_privs)
628 FINALIZE_STATEMENT(m_stmt_write_privs)
629 FINALIZE_STATEMENT(m_stmt_delete_privs)
630 FINALIZE_STATEMENT(m_stmt_last_insert_rowid)
631 }
632
633 void AuthDatabaseSQLite3::createDatabase()
634 {
635 assert(m_database); // Pre-condition
636
637 SQLOK(sqlite3_exec(m_database,
638 "CREATE TABLE IF NOT EXISTS `auth` ("
639 "`id` INTEGER PRIMARY KEY AUTOINCREMENT,"
640 "`name` VARCHAR(32) UNIQUE,"
641 "`password` VARCHAR(512),"
642 "`last_login` INTEGER"
643 ");",
644 NULL, NULL, NULL),
645 "Failed to create auth table");
646
647 SQLOK(sqlite3_exec(m_database,
648 "CREATE TABLE IF NOT EXISTS `user_privileges` ("
649 "`id` INTEGER,"
650 "`privilege` VARCHAR(32),"
651 "PRIMARY KEY (id, privilege)"
652 "CONSTRAINT fk_id FOREIGN KEY (id) REFERENCES auth (id) ON DELETE CASCADE"
653 ");",
654 NULL, NULL, NULL),
655 "Failed to create auth privileges table");
656 }
657
658 void AuthDatabaseSQLite3::initStatements()
659 {
660 PREPARE_STATEMENT(read, "SELECT id, name, password, last_login FROM auth WHERE name = ?");
661 PREPARE_STATEMENT(write, "UPDATE auth set name = ?, password = ?, last_login = ? WHERE id = ?");
662 PREPARE_STATEMENT(create, "INSERT INTO auth (name, password, last_login) VALUES (?, ?, ?)");
663 PREPARE_STATEMENT(delete, "DELETE FROM auth WHERE name = ?");
664
665 PREPARE_STATEMENT(list_names, "SELECT name FROM auth ORDER BY name DESC");
666
667 PREPARE_STATEMENT(read_privs, "SELECT privilege FROM user_privileges WHERE id = ?");
668 PREPARE_STATEMENT(write_privs, "INSERT OR IGNORE INTO user_privileges (id, privilege) VALUES (?, ?)");
669 PREPARE_STATEMENT(delete_privs, "DELETE FROM user_privileges WHERE id = ?");
670
671 PREPARE_STATEMENT(last_insert_rowid, "SELECT last_insert_rowid()");
672 }
673
674 bool AuthDatabaseSQLite3::getAuth(const std::string &name, AuthEntry &res)
675 {
676 verifyDatabase();
677 str_to_sqlite(m_stmt_read, 1, name);
678 if (sqlite3_step(m_stmt_read) != SQLITE_ROW) {
679 sqlite3_reset(m_stmt_read);
680 return false;
681 }
682 res.id = sqlite_to_uint(m_stmt_read, 0);
683 res.name = sqlite_to_string(m_stmt_read, 1);
684 res.password = sqlite_to_string(m_stmt_read, 2);
685 res.last_login = sqlite_to_int64(m_stmt_read, 3);
686 sqlite3_reset(m_stmt_read);
687
688 int64_to_sqlite(m_stmt_read_privs, 1, res.id);
689 while (sqlite3_step(m_stmt_read_privs) == SQLITE_ROW) {
690 res.privileges.emplace_back(sqlite_to_string(m_stmt_read_privs, 0));
691 }
692 sqlite3_reset(m_stmt_read_privs);
693
694 return true;
695 }
696
697 bool AuthDatabaseSQLite3::saveAuth(const AuthEntry &authEntry)
698 {
699 beginSave();
700
701 str_to_sqlite(m_stmt_write, 1, authEntry.name);
702 str_to_sqlite(m_stmt_write, 2, authEntry.password);
703 int64_to_sqlite(m_stmt_write, 3, authEntry.last_login);
704 int64_to_sqlite(m_stmt_write, 4, authEntry.id);
705 sqlite3_vrfy(sqlite3_step(m_stmt_write), SQLITE_DONE);
706 sqlite3_reset(m_stmt_write);
707
708 writePrivileges(authEntry);
709
710 endSave();
711 return true;
712 }
713
714 bool AuthDatabaseSQLite3::createAuth(AuthEntry &authEntry)
715 {
716 beginSave();
717
718 // id autoincrements
719 str_to_sqlite(m_stmt_create, 1, authEntry.name);
720 str_to_sqlite(m_stmt_create, 2, authEntry.password);
721 int64_to_sqlite(m_stmt_create, 3, authEntry.last_login);
722 sqlite3_vrfy(sqlite3_step(m_stmt_create), SQLITE_DONE);
723 sqlite3_reset(m_stmt_create);
724
725 // obtain id and write back to original authEntry
726 sqlite3_step(m_stmt_last_insert_rowid);
727 authEntry.id = sqlite_to_uint(m_stmt_last_insert_rowid, 0);
728 sqlite3_reset(m_stmt_last_insert_rowid);
729
730 writePrivileges(authEntry);
731
732 endSave();
733 return true;
734 }
735
736 bool AuthDatabaseSQLite3::deleteAuth(const std::string &name)
737 {
738 verifyDatabase();
739
740 str_to_sqlite(m_stmt_delete, 1, name);
741 sqlite3_vrfy(sqlite3_step(m_stmt_delete), SQLITE_DONE);
742 int changes = sqlite3_changes(m_database);
743 sqlite3_reset(m_stmt_delete);
744
745 // privileges deleted by foreign key on delete cascade
746
747 return changes > 0;
748 }
749
750 void AuthDatabaseSQLite3::listNames(std::vector<std::string> &res)
751 {
752 verifyDatabase();
753
754 while (sqlite3_step(m_stmt_list_names) == SQLITE_ROW) {
755 res.push_back(sqlite_to_string(m_stmt_list_names, 0));
756 }
757 sqlite3_reset(m_stmt_list_names);
758 }
759
760 void AuthDatabaseSQLite3::reload()
761 {
762 // noop for SQLite
763 }
764
765 void AuthDatabaseSQLite3::writePrivileges(const AuthEntry &authEntry)
766 {
767 int64_to_sqlite(m_stmt_delete_privs, 1, authEntry.id);
768 sqlite3_vrfy(sqlite3_step(m_stmt_delete_privs), SQLITE_DONE);
769 sqlite3_reset(m_stmt_delete_privs);
770 for (const std::string &privilege : authEntry.privileges) {
771 int64_to_sqlite(m_stmt_write_privs, 1, authEntry.id);
772 str_to_sqlite(m_stmt_write_privs, 2, privilege);
773 sqlite3_vrfy(sqlite3_step(m_stmt_write_privs), SQLITE_DONE);
774 sqlite3_reset(m_stmt_write_privs);
775 }
776 }
minetest hacked client (dragonfire fork)