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 void Database_PostgreSQL::rollback()
171 checkResults(PQexec(m_conn, "ROLLBACK;"));
174 MapDatabasePostgreSQL::MapDatabasePostgreSQL(const std::string &connect_string):
175 Database_PostgreSQL(connect_string),
182 void MapDatabasePostgreSQL::createDatabase()
184 createTableIfNotExists("blocks",
185 "CREATE TABLE blocks ("
190 "PRIMARY KEY (posX,posY,posZ)"
194 infostream << "PostgreSQL: Map Database was initialized." << std::endl;
197 void MapDatabasePostgreSQL::initStatements()
199 prepareStatement("read_block",
200 "SELECT data FROM blocks "
201 "WHERE posX = $1::int4 AND posY = $2::int4 AND "
204 if (getPGVersion() < 90500) {
205 prepareStatement("write_block_insert",
206 "INSERT INTO blocks (posX, posY, posZ, data) SELECT "
207 "$1::int4, $2::int4, $3::int4, $4::bytea "
208 "WHERE NOT EXISTS (SELECT true FROM blocks "
209 "WHERE posX = $1::int4 AND posY = $2::int4 AND "
212 prepareStatement("write_block_update",
213 "UPDATE blocks SET data = $4::bytea "
214 "WHERE posX = $1::int4 AND posY = $2::int4 AND "
217 prepareStatement("write_block",
218 "INSERT INTO blocks (posX, posY, posZ, data) VALUES "
219 "($1::int4, $2::int4, $3::int4, $4::bytea) "
220 "ON CONFLICT ON CONSTRAINT blocks_pkey DO "
221 "UPDATE SET data = $4::bytea");
224 prepareStatement("delete_block", "DELETE FROM blocks WHERE "
225 "posX = $1::int4 AND posY = $2::int4 AND posZ = $3::int4");
227 prepareStatement("list_all_loadable_blocks",
228 "SELECT posX, posY, posZ FROM blocks");
231 bool MapDatabasePostgreSQL::saveBlock(const v3s16 &pos, const std::string &data)
233 // Verify if we don't overflow the platform integer with the mapblock size
234 if (data.size() > INT_MAX) {
235 errorstream << "Database_PostgreSQL::saveBlock: Data truncation! "
236 << "data.size() over 0xFFFFFFFF (== " << data.size()
248 const void *args[] = { &x, &y, &z, data.c_str() };
249 const int argLen[] = {
250 sizeof(x), sizeof(y), sizeof(z), (int)data.size()
252 const int argFmt[] = { 1, 1, 1, 1 };
254 if (getPGVersion() < 90500) {
255 execPrepared("write_block_update", ARRLEN(args), args, argLen, argFmt);
256 execPrepared("write_block_insert", ARRLEN(args), args, argLen, argFmt);
258 execPrepared("write_block", ARRLEN(args), args, argLen, argFmt);
263 void MapDatabasePostgreSQL::loadBlock(const v3s16 &pos, std::string *block)
272 const void *args[] = { &x, &y, &z };
273 const int argLen[] = { sizeof(x), sizeof(y), sizeof(z) };
274 const int argFmt[] = { 1, 1, 1 };
276 PGresult *results = execPrepared("read_block", ARRLEN(args), args,
277 argLen, argFmt, false);
281 if (PQntuples(results))
282 *block = std::string(PQgetvalue(results, 0, 0), PQgetlength(results, 0, 0));
287 bool MapDatabasePostgreSQL::deleteBlock(const v3s16 &pos)
296 const void *args[] = { &x, &y, &z };
297 const int argLen[] = { sizeof(x), sizeof(y), sizeof(z) };
298 const int argFmt[] = { 1, 1, 1 };
300 execPrepared("delete_block", ARRLEN(args), args, argLen, argFmt);
305 void MapDatabasePostgreSQL::listAllLoadableBlocks(std::vector<v3s16> &dst)
309 PGresult *results = execPrepared("list_all_loadable_blocks", 0,
310 NULL, NULL, NULL, false, false);
312 int numrows = PQntuples(results);
314 for (int row = 0; row < numrows; ++row)
315 dst.push_back(pg_to_v3s16(results, row, 0));
323 PlayerDatabasePostgreSQL::PlayerDatabasePostgreSQL(const std::string &connect_string):
324 Database_PostgreSQL(connect_string),
331 void PlayerDatabasePostgreSQL::createDatabase()
333 createTableIfNotExists("player",
334 "CREATE TABLE player ("
335 "name VARCHAR(60) NOT NULL,"
336 "pitch NUMERIC(15, 7) NOT NULL,"
337 "yaw NUMERIC(15, 7) NOT NULL,"
338 "posX NUMERIC(15, 7) NOT NULL,"
339 "posY NUMERIC(15, 7) NOT NULL,"
340 "posZ NUMERIC(15, 7) NOT NULL,"
342 "breath INT NOT NULL,"
343 "creation_date TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT NOW(),"
344 "modification_date TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT NOW(),"
349 createTableIfNotExists("player_inventories",
350 "CREATE TABLE player_inventories ("
351 "player VARCHAR(60) NOT NULL,"
352 "inv_id INT NOT NULL,"
353 "inv_width INT NOT NULL,"
354 "inv_name TEXT NOT NULL DEFAULT '',"
355 "inv_size INT NOT NULL,"
356 "PRIMARY KEY(player, inv_id),"
357 "CONSTRAINT player_inventories_fkey FOREIGN KEY (player) REFERENCES "
358 "player (name) ON DELETE CASCADE"
362 createTableIfNotExists("player_inventory_items",
363 "CREATE TABLE player_inventory_items ("
364 "player VARCHAR(60) NOT NULL,"
365 "inv_id INT NOT NULL,"
366 "slot_id INT NOT NULL,"
367 "item TEXT NOT NULL DEFAULT '',"
368 "PRIMARY KEY(player, inv_id, slot_id),"
369 "CONSTRAINT player_inventory_items_fkey FOREIGN KEY (player) REFERENCES "
370 "player (name) ON DELETE CASCADE"
374 createTableIfNotExists("player_metadata",
375 "CREATE TABLE player_metadata ("
376 "player VARCHAR(60) NOT NULL,"
377 "attr VARCHAR(256) NOT NULL,"
379 "PRIMARY KEY(player, attr),"
380 "CONSTRAINT player_metadata_fkey FOREIGN KEY (player) REFERENCES "
381 "player (name) ON DELETE CASCADE"
385 infostream << "PostgreSQL: Player Database was inited." << std::endl;
388 void PlayerDatabasePostgreSQL::initStatements()
390 if (getPGVersion() < 90500) {
391 prepareStatement("create_player",
392 "INSERT INTO player(name, pitch, yaw, posX, posY, posZ, hp, breath) VALUES "
393 "($1, $2, $3, $4, $5, $6, $7::int, $8::int)");
395 prepareStatement("update_player",
396 "UPDATE SET pitch = $2, yaw = $3, posX = $4, posY = $5, posZ = $6, hp = $7::int, "
397 "breath = $8::int, modification_date = NOW() WHERE name = $1");
399 prepareStatement("save_player",
400 "INSERT INTO player(name, pitch, yaw, posX, posY, posZ, hp, breath) VALUES "
401 "($1, $2, $3, $4, $5, $6, $7::int, $8::int)"
402 "ON CONFLICT ON CONSTRAINT player_pkey DO UPDATE SET pitch = $2, yaw = $3, "
403 "posX = $4, posY = $5, posZ = $6, hp = $7::int, breath = $8::int, "
404 "modification_date = NOW()");
407 prepareStatement("remove_player", "DELETE FROM player WHERE name = $1");
409 prepareStatement("load_player_list", "SELECT name FROM player");
411 prepareStatement("remove_player_inventories",
412 "DELETE FROM player_inventories WHERE player = $1");
414 prepareStatement("remove_player_inventory_items",
415 "DELETE FROM player_inventory_items WHERE player = $1");
417 prepareStatement("add_player_inventory",
418 "INSERT INTO player_inventories (player, inv_id, inv_width, inv_name, inv_size) VALUES "
419 "($1, $2::int, $3::int, $4, $5::int)");
421 prepareStatement("add_player_inventory_item",
422 "INSERT INTO player_inventory_items (player, inv_id, slot_id, item) VALUES "
423 "($1, $2::int, $3::int, $4)");
425 prepareStatement("load_player_inventories",
426 "SELECT inv_id, inv_width, inv_name, inv_size FROM player_inventories "
427 "WHERE player = $1 ORDER BY inv_id");
429 prepareStatement("load_player_inventory_items",
430 "SELECT slot_id, item FROM player_inventory_items WHERE "
431 "player = $1 AND inv_id = $2::int");
433 prepareStatement("load_player",
434 "SELECT pitch, yaw, posX, posY, posZ, hp, breath FROM player WHERE name = $1");
436 prepareStatement("remove_player_metadata",
437 "DELETE FROM player_metadata WHERE player = $1");
439 prepareStatement("save_player_metadata",
440 "INSERT INTO player_metadata (player, attr, value) VALUES ($1, $2, $3)");
442 prepareStatement("load_player_metadata",
443 "SELECT attr, value FROM player_metadata WHERE player = $1");
447 bool PlayerDatabasePostgreSQL::playerDataExists(const std::string &playername)
451 const char *values[] = { playername.c_str() };
452 PGresult *results = execPrepared("load_player", 1, values, false);
454 bool res = (PQntuples(results) > 0);
459 void PlayerDatabasePostgreSQL::savePlayer(RemotePlayer *player)
461 PlayerSAO* sao = player->getPlayerSAO();
467 v3f pos = sao->getBasePosition();
468 std::string pitch = ftos(sao->getLookPitch());
469 std::string yaw = ftos(sao->getRotation().Y);
470 std::string posx = ftos(pos.X);
471 std::string posy = ftos(pos.Y);
472 std::string posz = ftos(pos.Z);
473 std::string hp = itos(sao->getHP());
474 std::string breath = itos(sao->getBreath());
475 const char *values[] = {
479 posx.c_str(), posy.c_str(), posz.c_str(),
484 const char* rmvalues[] = { player->getName() };
487 if (getPGVersion() < 90500) {
488 if (!playerDataExists(player->getName()))
489 execPrepared("create_player", 8, values, true, false);
491 execPrepared("update_player", 8, values, true, false);
494 execPrepared("save_player", 8, values, true, false);
496 // Write player inventories
497 execPrepared("remove_player_inventories", 1, rmvalues);
498 execPrepared("remove_player_inventory_items", 1, rmvalues);
500 std::vector<const InventoryList*> inventory_lists = sao->getInventory()->getLists();
501 for (u16 i = 0; i < inventory_lists.size(); i++) {
502 const InventoryList* list = inventory_lists[i];
503 const std::string &name = list->getName();
504 std::string width = itos(list->getWidth()),
505 inv_id = itos(i), lsize = itos(list->getSize());
507 const char* inv_values[] = {
514 execPrepared("add_player_inventory", 5, inv_values);
516 for (u32 j = 0; j < list->getSize(); j++) {
517 std::ostringstream os;
518 list->getItem(j).serialize(os);
519 std::string itemStr = os.str(), slotId = itos(j);
521 const char* invitem_values[] = {
527 execPrepared("add_player_inventory_item", 4, invitem_values);
531 execPrepared("remove_player_metadata", 1, rmvalues);
532 const StringMap &attrs = sao->getMeta().getStrings();
533 for (const auto &attr : attrs) {
534 const char *meta_values[] = {
539 execPrepared("save_player_metadata", 3, meta_values);
543 player->onSuccessfulSave();
546 bool PlayerDatabasePostgreSQL::loadPlayer(RemotePlayer *player, PlayerSAO *sao)
551 const char *values[] = { player->getName() };
552 PGresult *results = execPrepared("load_player", 1, values, false, false);
554 // Player not found, return not found
555 if (!PQntuples(results)) {
560 sao->setLookPitch(pg_to_float(results, 0, 0));
561 sao->setRotation(v3f(0, pg_to_float(results, 0, 1), 0));
562 sao->setBasePosition(v3f(
563 pg_to_float(results, 0, 2),
564 pg_to_float(results, 0, 3),
565 pg_to_float(results, 0, 4))
567 sao->setHPRaw((u16) pg_to_int(results, 0, 5));
568 sao->setBreath((u16) pg_to_int(results, 0, 6), false);
573 results = execPrepared("load_player_inventories", 1, values, false, false);
575 int resultCount = PQntuples(results);
577 for (int row = 0; row < resultCount; ++row) {
578 InventoryList* invList = player->inventory.
579 addList(PQgetvalue(results, row, 2), pg_to_uint(results, row, 3));
580 invList->setWidth(pg_to_uint(results, row, 1));
582 u32 invId = pg_to_uint(results, row, 0);
583 std::string invIdStr = itos(invId);
585 const char* values2[] = {
589 PGresult *results2 = execPrepared("load_player_inventory_items", 2,
590 values2, false, false);
592 int resultCount2 = PQntuples(results2);
593 for (int row2 = 0; row2 < resultCount2; row2++) {
594 const std::string itemStr = PQgetvalue(results2, row2, 1);
595 if (itemStr.length() > 0) {
597 stack.deSerialize(itemStr);
598 invList->changeItem(pg_to_uint(results2, row2, 0), stack);
606 results = execPrepared("load_player_metadata", 1, values, false);
608 int numrows = PQntuples(results);
609 for (int row = 0; row < numrows; row++) {
610 sao->getMeta().setString(PQgetvalue(results, row, 0), PQgetvalue(results, row, 1));
612 sao->getMeta().setModified(false);
619 bool PlayerDatabasePostgreSQL::removePlayer(const std::string &name)
621 if (!playerDataExists(name))
626 const char *values[] = { name.c_str() };
627 execPrepared("remove_player", 1, values);
632 void PlayerDatabasePostgreSQL::listPlayers(std::vector<std::string> &res)
636 PGresult *results = execPrepared("load_player_list", 0, NULL, false);
638 int numrows = PQntuples(results);
639 for (int row = 0; row < numrows; row++)
640 res.emplace_back(PQgetvalue(results, row, 0));
645 AuthDatabasePostgreSQL::AuthDatabasePostgreSQL(const std::string &connect_string) :
646 Database_PostgreSQL(connect_string), AuthDatabase()
651 void AuthDatabasePostgreSQL::createDatabase()
653 createTableIfNotExists("auth",
654 "CREATE TABLE auth ("
658 "last_login INT NOT NULL DEFAULT 0,"
662 createTableIfNotExists("user_privileges",
663 "CREATE TABLE user_privileges ("
666 "PRIMARY KEY (id, privilege),"
667 "CONSTRAINT fk_id FOREIGN KEY (id) REFERENCES auth (id) ON DELETE CASCADE"
671 void AuthDatabasePostgreSQL::initStatements()
673 prepareStatement("auth_read", "SELECT id, name, password, last_login FROM auth WHERE name = $1");
674 prepareStatement("auth_write", "UPDATE auth SET name = $1, password = $2, last_login = $3 WHERE id = $4");
675 prepareStatement("auth_create", "INSERT INTO auth (name, password, last_login) VALUES ($1, $2, $3) RETURNING id");
676 prepareStatement("auth_delete", "DELETE FROM auth WHERE name = $1");
678 prepareStatement("auth_list_names", "SELECT name FROM auth ORDER BY name DESC");
680 prepareStatement("auth_read_privs", "SELECT privilege FROM user_privileges WHERE id = $1");
681 prepareStatement("auth_write_privs", "INSERT INTO user_privileges (id, privilege) VALUES ($1, $2)");
682 prepareStatement("auth_delete_privs", "DELETE FROM user_privileges WHERE id = $1");
685 bool AuthDatabasePostgreSQL::getAuth(const std::string &name, AuthEntry &res)
689 const char *values[] = { name.c_str() };
690 PGresult *result = execPrepared("auth_read", 1, values, false, false);
691 int numrows = PQntuples(result);
697 res.id = pg_to_uint(result, 0, 0);
698 res.name = std::string(PQgetvalue(result, 0, 1), PQgetlength(result, 0, 1));
699 res.password = std::string(PQgetvalue(result, 0, 2), PQgetlength(result, 0, 2));
700 res.last_login = pg_to_int(result, 0, 3);
704 std::string playerIdStr = itos(res.id);
705 const char *privsValues[] = { playerIdStr.c_str() };
706 PGresult *results = execPrepared("auth_read_privs", 1, privsValues, false);
708 numrows = PQntuples(results);
709 for (int row = 0; row < numrows; row++)
710 res.privileges.emplace_back(PQgetvalue(results, row, 0));
717 bool AuthDatabasePostgreSQL::saveAuth(const AuthEntry &authEntry)
723 std::string lastLoginStr = itos(authEntry.last_login);
724 std::string idStr = itos(authEntry.id);
725 const char *values[] = {
726 authEntry.name.c_str() ,
727 authEntry.password.c_str(),
728 lastLoginStr.c_str(),
731 execPrepared("auth_write", 4, values);
733 writePrivileges(authEntry);
739 bool AuthDatabasePostgreSQL::createAuth(AuthEntry &authEntry)
743 std::string lastLoginStr = itos(authEntry.last_login);
744 const char *values[] = {
745 authEntry.name.c_str() ,
746 authEntry.password.c_str(),
752 PGresult *result = execPrepared("auth_create", 3, values, false, false);
754 int numrows = PQntuples(result);
756 errorstream << "Strange behaviour on auth creation, no ID returned." << std::endl;
762 authEntry.id = pg_to_uint(result, 0, 0);
765 writePrivileges(authEntry);
771 bool AuthDatabasePostgreSQL::deleteAuth(const std::string &name)
775 const char *values[] = { name.c_str() };
776 execPrepared("auth_delete", 1, values);
778 // privileges deleted by foreign key on delete cascade
782 void AuthDatabasePostgreSQL::listNames(std::vector<std::string> &res)
786 PGresult *results = execPrepared("auth_list_names", 0,
787 NULL, NULL, NULL, false, false);
789 int numrows = PQntuples(results);
791 for (int row = 0; row < numrows; ++row)
792 res.emplace_back(PQgetvalue(results, row, 0));
797 void AuthDatabasePostgreSQL::reload()
802 void AuthDatabasePostgreSQL::writePrivileges(const AuthEntry &authEntry)
804 std::string authIdStr = itos(authEntry.id);
805 const char *values[] = { authIdStr.c_str() };
806 execPrepared("auth_delete_privs", 1, values);
808 for (const std::string &privilege : authEntry.privileges) {
809 const char *values[] = { authIdStr.c_str(), privilege.c_str() };
810 execPrepared("auth_write_privs", 2, values);
815 #endif // USE_POSTGRESQL