2 Copyright (C) 2016 Loic Blot <loic.blot@unix-experience.fr>
4 This program is free software; you can redistribute it and/or modify
5 it under the terms of the GNU Lesser General Public License as published by
6 the Free Software Foundation; either version 2.1 of the License, or
7 (at your option) any later version.
9 This program is distributed in the hope that it will be useful,
10 but WITHOUT ANY WARRANTY; without even the implied warranty of
11 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
12 GNU Lesser General Public License for more details.
14 You should have received a copy of the GNU Lesser General Public License along
15 with this program; if not, write to the Free Software Foundation, Inc.,
16 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
23 #include "database-postgresql.h"
26 // Without this some of the network functions are not found on mingw
28 #define _WIN32_WINNT 0x0501
33 #include <netinet/in.h>
37 #include "exceptions.h"
39 #include "remoteplayer.h"
40 #include "server/player_sao.h"
42 Database_PostgreSQL::Database_PostgreSQL(const std::string &connect_string) :
43 m_connect_string(connect_string)
45 if (m_connect_string.empty()) {
46 throw SettingNotFoundException(
47 "Set pgsql_connection string in world.mt to "
48 "use the postgresql backend\n"
50 "pgsql_connection has the following form: \n"
51 "\tpgsql_connection = host=127.0.0.1 port=5432 user=mt_user "
52 "password=mt_password dbname=minetest_world\n"
53 "mt_user should have CREATE TABLE, INSERT, SELECT, UPDATE and "
54 "DELETE rights on the database.\n"
55 "Don't create mt_user as a SUPERUSER!");
59 Database_PostgreSQL::~Database_PostgreSQL()
64 void Database_PostgreSQL::connectToDatabase()
66 m_conn = PQconnectdb(m_connect_string.c_str());
68 if (PQstatus(m_conn) != CONNECTION_OK) {
69 throw DatabaseException(std::string(
70 "PostgreSQL database error: ") +
71 PQerrorMessage(m_conn));
74 m_pgversion = PQserverVersion(m_conn);
77 * We are using UPSERT feature from PostgreSQL 9.5
78 * to have the better performance where possible.
80 if (m_pgversion < 90500) {
81 warningstream << "Your PostgreSQL server lacks UPSERT "
82 << "support. Use version 9.5 or better if possible."
86 infostream << "PostgreSQL Database: Version " << m_pgversion
87 << " Connection made." << std::endl;
93 void Database_PostgreSQL::pingDatabase()
95 // Verify DB connection with ping
98 } catch (const DatabaseException &e) {
99 // If ping failed, show the error and try reconnect
102 errorstream << e.what() << std::endl
103 << "Reconnecting to database " << m_connect_string << std::endl;
108 void Database_PostgreSQL::ping()
110 if (PQping(m_connect_string.c_str()) != PQPING_OK) {
111 throw DatabaseException(std::string(
112 "PostgreSQL database error: ") +
113 PQerrorMessage(m_conn));
117 bool Database_PostgreSQL::initialized() const
119 return (PQstatus(m_conn) == CONNECTION_OK);
122 PGresult *Database_PostgreSQL::checkResults(PGresult *result, bool clear)
124 ExecStatusType statusType = PQresultStatus(result);
126 switch (statusType) {
127 case PGRES_COMMAND_OK:
128 case PGRES_TUPLES_OK:
130 case PGRES_FATAL_ERROR:
132 throw DatabaseException(
133 std::string("PostgreSQL database error: ") +
134 PQresultErrorMessage(result));
143 void Database_PostgreSQL::createTableIfNotExists(const std::string &table_name,
144 const std::string &definition)
146 std::string sql_check_table = "SELECT relname FROM pg_class WHERE relname='" +
148 PGresult *result = checkResults(PQexec(m_conn, sql_check_table.c_str()), false);
150 // If table doesn't exist, create it
151 if (!PQntuples(result)) {
152 checkResults(PQexec(m_conn, definition.c_str()));
158 void Database_PostgreSQL::beginSave()
161 checkResults(PQexec(m_conn, "BEGIN;"));
164 void Database_PostgreSQL::endSave()
166 checkResults(PQexec(m_conn, "COMMIT;"));
169 MapDatabasePostgreSQL::MapDatabasePostgreSQL(const std::string &connect_string):
170 Database_PostgreSQL(connect_string),
177 void MapDatabasePostgreSQL::createDatabase()
179 createTableIfNotExists("blocks",
180 "CREATE TABLE blocks ("
185 "PRIMARY KEY (posX,posY,posZ)"
189 infostream << "PostgreSQL: Map Database was initialized." << std::endl;
192 void MapDatabasePostgreSQL::initStatements()
194 prepareStatement("read_block",
195 "SELECT data FROM blocks "
196 "WHERE posX = $1::int4 AND posY = $2::int4 AND "
199 if (getPGVersion() < 90500) {
200 prepareStatement("write_block_insert",
201 "INSERT INTO blocks (posX, posY, posZ, data) SELECT "
202 "$1::int4, $2::int4, $3::int4, $4::bytea "
203 "WHERE NOT EXISTS (SELECT true FROM blocks "
204 "WHERE posX = $1::int4 AND posY = $2::int4 AND "
207 prepareStatement("write_block_update",
208 "UPDATE blocks SET data = $4::bytea "
209 "WHERE posX = $1::int4 AND posY = $2::int4 AND "
212 prepareStatement("write_block",
213 "INSERT INTO blocks (posX, posY, posZ, data) VALUES "
214 "($1::int4, $2::int4, $3::int4, $4::bytea) "
215 "ON CONFLICT ON CONSTRAINT blocks_pkey DO "
216 "UPDATE SET data = $4::bytea");
219 prepareStatement("delete_block", "DELETE FROM blocks WHERE "
220 "posX = $1::int4 AND posY = $2::int4 AND posZ = $3::int4");
222 prepareStatement("list_all_loadable_blocks",
223 "SELECT posX, posY, posZ FROM blocks");
226 bool MapDatabasePostgreSQL::saveBlock(const v3s16 &pos, const std::string &data)
228 // Verify if we don't overflow the platform integer with the mapblock size
229 if (data.size() > INT_MAX) {
230 errorstream << "Database_PostgreSQL::saveBlock: Data truncation! "
231 << "data.size() over 0xFFFFFFFF (== " << data.size()
243 const void *args[] = { &x, &y, &z, data.c_str() };
244 const int argLen[] = {
245 sizeof(x), sizeof(y), sizeof(z), (int)data.size()
247 const int argFmt[] = { 1, 1, 1, 1 };
249 if (getPGVersion() < 90500) {
250 execPrepared("write_block_update", ARRLEN(args), args, argLen, argFmt);
251 execPrepared("write_block_insert", ARRLEN(args), args, argLen, argFmt);
253 execPrepared("write_block", ARRLEN(args), args, argLen, argFmt);
258 void MapDatabasePostgreSQL::loadBlock(const v3s16 &pos, std::string *block)
267 const void *args[] = { &x, &y, &z };
268 const int argLen[] = { sizeof(x), sizeof(y), sizeof(z) };
269 const int argFmt[] = { 1, 1, 1 };
271 PGresult *results = execPrepared("read_block", ARRLEN(args), args,
272 argLen, argFmt, false);
276 if (PQntuples(results))
277 *block = std::string(PQgetvalue(results, 0, 0), PQgetlength(results, 0, 0));
282 bool MapDatabasePostgreSQL::deleteBlock(const v3s16 &pos)
291 const void *args[] = { &x, &y, &z };
292 const int argLen[] = { sizeof(x), sizeof(y), sizeof(z) };
293 const int argFmt[] = { 1, 1, 1 };
295 execPrepared("delete_block", ARRLEN(args), args, argLen, argFmt);
300 void MapDatabasePostgreSQL::listAllLoadableBlocks(std::vector<v3s16> &dst)
304 PGresult *results = execPrepared("list_all_loadable_blocks", 0,
305 NULL, NULL, NULL, false, false);
307 int numrows = PQntuples(results);
309 for (int row = 0; row < numrows; ++row)
310 dst.push_back(pg_to_v3s16(results, row, 0));
318 PlayerDatabasePostgreSQL::PlayerDatabasePostgreSQL(const std::string &connect_string):
319 Database_PostgreSQL(connect_string),
326 void PlayerDatabasePostgreSQL::createDatabase()
328 createTableIfNotExists("player",
329 "CREATE TABLE player ("
330 "name VARCHAR(60) NOT NULL,"
331 "pitch NUMERIC(15, 7) NOT NULL,"
332 "yaw NUMERIC(15, 7) NOT NULL,"
333 "posX NUMERIC(15, 7) NOT NULL,"
334 "posY NUMERIC(15, 7) NOT NULL,"
335 "posZ NUMERIC(15, 7) NOT NULL,"
337 "breath INT NOT NULL,"
338 "creation_date TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT NOW(),"
339 "modification_date TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT NOW(),"
344 createTableIfNotExists("player_inventories",
345 "CREATE TABLE player_inventories ("
346 "player VARCHAR(60) NOT NULL,"
347 "inv_id INT NOT NULL,"
348 "inv_width INT NOT NULL,"
349 "inv_name TEXT NOT NULL DEFAULT '',"
350 "inv_size INT NOT NULL,"
351 "PRIMARY KEY(player, inv_id),"
352 "CONSTRAINT player_inventories_fkey FOREIGN KEY (player) REFERENCES "
353 "player (name) ON DELETE CASCADE"
357 createTableIfNotExists("player_inventory_items",
358 "CREATE TABLE player_inventory_items ("
359 "player VARCHAR(60) NOT NULL,"
360 "inv_id INT NOT NULL,"
361 "slot_id INT NOT NULL,"
362 "item TEXT NOT NULL DEFAULT '',"
363 "PRIMARY KEY(player, inv_id, slot_id),"
364 "CONSTRAINT player_inventory_items_fkey FOREIGN KEY (player) REFERENCES "
365 "player (name) ON DELETE CASCADE"
369 createTableIfNotExists("player_metadata",
370 "CREATE TABLE player_metadata ("
371 "player VARCHAR(60) NOT NULL,"
372 "attr VARCHAR(256) NOT NULL,"
374 "PRIMARY KEY(player, attr),"
375 "CONSTRAINT player_metadata_fkey FOREIGN KEY (player) REFERENCES "
376 "player (name) ON DELETE CASCADE"
380 infostream << "PostgreSQL: Player Database was inited." << std::endl;
383 void PlayerDatabasePostgreSQL::initStatements()
385 if (getPGVersion() < 90500) {
386 prepareStatement("create_player",
387 "INSERT INTO player(name, pitch, yaw, posX, posY, posZ, hp, breath) VALUES "
388 "($1, $2, $3, $4, $5, $6, $7::int, $8::int)");
390 prepareStatement("update_player",
391 "UPDATE SET pitch = $2, yaw = $3, posX = $4, posY = $5, posZ = $6, hp = $7::int, "
392 "breath = $8::int, modification_date = NOW() WHERE name = $1");
394 prepareStatement("save_player",
395 "INSERT INTO player(name, pitch, yaw, posX, posY, posZ, hp, breath) VALUES "
396 "($1, $2, $3, $4, $5, $6, $7::int, $8::int)"
397 "ON CONFLICT ON CONSTRAINT player_pkey DO UPDATE SET pitch = $2, yaw = $3, "
398 "posX = $4, posY = $5, posZ = $6, hp = $7::int, breath = $8::int, "
399 "modification_date = NOW()");
402 prepareStatement("remove_player", "DELETE FROM player WHERE name = $1");
404 prepareStatement("load_player_list", "SELECT name FROM player");
406 prepareStatement("remove_player_inventories",
407 "DELETE FROM player_inventories WHERE player = $1");
409 prepareStatement("remove_player_inventory_items",
410 "DELETE FROM player_inventory_items WHERE player = $1");
412 prepareStatement("add_player_inventory",
413 "INSERT INTO player_inventories (player, inv_id, inv_width, inv_name, inv_size) VALUES "
414 "($1, $2::int, $3::int, $4, $5::int)");
416 prepareStatement("add_player_inventory_item",
417 "INSERT INTO player_inventory_items (player, inv_id, slot_id, item) VALUES "
418 "($1, $2::int, $3::int, $4)");
420 prepareStatement("load_player_inventories",
421 "SELECT inv_id, inv_width, inv_name, inv_size FROM player_inventories "
422 "WHERE player = $1 ORDER BY inv_id");
424 prepareStatement("load_player_inventory_items",
425 "SELECT slot_id, item FROM player_inventory_items WHERE "
426 "player = $1 AND inv_id = $2::int");
428 prepareStatement("load_player",
429 "SELECT pitch, yaw, posX, posY, posZ, hp, breath FROM player WHERE name = $1");
431 prepareStatement("remove_player_metadata",
432 "DELETE FROM player_metadata WHERE player = $1");
434 prepareStatement("save_player_metadata",
435 "INSERT INTO player_metadata (player, attr, value) VALUES ($1, $2, $3)");
437 prepareStatement("load_player_metadata",
438 "SELECT attr, value FROM player_metadata WHERE player = $1");
442 bool PlayerDatabasePostgreSQL::playerDataExists(const std::string &playername)
446 const char *values[] = { playername.c_str() };
447 PGresult *results = execPrepared("load_player", 1, values, false);
449 bool res = (PQntuples(results) > 0);
454 void PlayerDatabasePostgreSQL::savePlayer(RemotePlayer *player)
456 PlayerSAO* sao = player->getPlayerSAO();
462 v3f pos = sao->getBasePosition();
463 std::string pitch = ftos(sao->getLookPitch());
464 std::string yaw = ftos(sao->getRotation().Y);
465 std::string posx = ftos(pos.X);
466 std::string posy = ftos(pos.Y);
467 std::string posz = ftos(pos.Z);
468 std::string hp = itos(sao->getHP());
469 std::string breath = itos(sao->getBreath());
470 const char *values[] = {
474 posx.c_str(), posy.c_str(), posz.c_str(),
479 const char* rmvalues[] = { player->getName() };
482 if (getPGVersion() < 90500) {
483 if (!playerDataExists(player->getName()))
484 execPrepared("create_player", 8, values, true, false);
486 execPrepared("update_player", 8, values, true, false);
489 execPrepared("save_player", 8, values, true, false);
491 // Write player inventories
492 execPrepared("remove_player_inventories", 1, rmvalues);
493 execPrepared("remove_player_inventory_items", 1, rmvalues);
495 std::vector<const InventoryList*> inventory_lists = sao->getInventory()->getLists();
496 for (u16 i = 0; i < inventory_lists.size(); i++) {
497 const InventoryList* list = inventory_lists[i];
498 const std::string &name = list->getName();
499 std::string width = itos(list->getWidth()),
500 inv_id = itos(i), lsize = itos(list->getSize());
502 const char* inv_values[] = {
509 execPrepared("add_player_inventory", 5, inv_values);
511 for (u32 j = 0; j < list->getSize(); j++) {
512 std::ostringstream os;
513 list->getItem(j).serialize(os);
514 std::string itemStr = os.str(), slotId = itos(j);
516 const char* invitem_values[] = {
522 execPrepared("add_player_inventory_item", 4, invitem_values);
526 execPrepared("remove_player_metadata", 1, rmvalues);
527 const StringMap &attrs = sao->getMeta().getStrings();
528 for (const auto &attr : attrs) {
529 const char *meta_values[] = {
534 execPrepared("save_player_metadata", 3, meta_values);
538 player->onSuccessfulSave();
541 bool PlayerDatabasePostgreSQL::loadPlayer(RemotePlayer *player, PlayerSAO *sao)
546 const char *values[] = { player->getName() };
547 PGresult *results = execPrepared("load_player", 1, values, false, false);
549 // Player not found, return not found
550 if (!PQntuples(results)) {
555 sao->setLookPitch(pg_to_float(results, 0, 0));
556 sao->setRotation(v3f(0, pg_to_float(results, 0, 1), 0));
557 sao->setBasePosition(v3f(
558 pg_to_float(results, 0, 2),
559 pg_to_float(results, 0, 3),
560 pg_to_float(results, 0, 4))
562 sao->setHPRaw((u16) pg_to_int(results, 0, 5));
563 sao->setBreath((u16) pg_to_int(results, 0, 6), false);
568 results = execPrepared("load_player_inventories", 1, values, false, false);
570 int resultCount = PQntuples(results);
572 for (int row = 0; row < resultCount; ++row) {
573 InventoryList* invList = player->inventory.
574 addList(PQgetvalue(results, row, 2), pg_to_uint(results, row, 3));
575 invList->setWidth(pg_to_uint(results, row, 1));
577 u32 invId = pg_to_uint(results, row, 0);
578 std::string invIdStr = itos(invId);
580 const char* values2[] = {
584 PGresult *results2 = execPrepared("load_player_inventory_items", 2,
585 values2, false, false);
587 int resultCount2 = PQntuples(results2);
588 for (int row2 = 0; row2 < resultCount2; row2++) {
589 const std::string itemStr = PQgetvalue(results2, row2, 1);
590 if (itemStr.length() > 0) {
592 stack.deSerialize(itemStr);
593 invList->changeItem(pg_to_uint(results2, row2, 0), stack);
601 results = execPrepared("load_player_metadata", 1, values, false);
603 int numrows = PQntuples(results);
604 for (int row = 0; row < numrows; row++) {
605 sao->getMeta().setString(PQgetvalue(results, row, 0), PQgetvalue(results, row, 1));
607 sao->getMeta().setModified(false);
614 bool PlayerDatabasePostgreSQL::removePlayer(const std::string &name)
616 if (!playerDataExists(name))
621 const char *values[] = { name.c_str() };
622 execPrepared("remove_player", 1, values);
627 void PlayerDatabasePostgreSQL::listPlayers(std::vector<std::string> &res)
631 PGresult *results = execPrepared("load_player_list", 0, NULL, false);
633 int numrows = PQntuples(results);
634 for (int row = 0; row < numrows; row++)
635 res.emplace_back(PQgetvalue(results, row, 0));
640 #endif // USE_POSTGRESQL