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 #ifndef WIN32_LEAN_AND_MEAN
27 #define WIN32_LEAN_AND_MEAN
29 // Without this some of the network functions are not found on mingw
31 #define _WIN32_WINNT 0x0501
36 #include <netinet/in.h>
40 #include "exceptions.h"
42 #include "content_sao.h"
43 #include "remoteplayer.h"
45 Database_PostgreSQL::Database_PostgreSQL(const std::string &connect_string) :
46 m_connect_string(connect_string),
50 if (m_connect_string.empty()) {
51 throw SettingNotFoundException(
52 "Set pgsql_connection string in world.mt to "
53 "use the postgresql backend\n"
55 "pgsql_connection has the following form: \n"
56 "\tpgsql_connection = host=127.0.0.1 port=5432 user=mt_user "
57 "password=mt_password dbname=minetest_world\n"
58 "mt_user should have CREATE TABLE, INSERT, SELECT, UPDATE and "
59 "DELETE rights on the database.\n"
60 "Don't create mt_user as a SUPERUSER!");
64 Database_PostgreSQL::~Database_PostgreSQL()
69 void Database_PostgreSQL::connectToDatabase()
71 m_conn = PQconnectdb(m_connect_string.c_str());
73 if (PQstatus(m_conn) != CONNECTION_OK) {
74 throw DatabaseException(std::string(
75 "PostgreSQL database error: ") +
76 PQerrorMessage(m_conn));
79 m_pgversion = PQserverVersion(m_conn);
82 * We are using UPSERT feature from PostgreSQL 9.5
83 * to have the better performance where possible.
85 if (m_pgversion < 90500) {
86 warningstream << "Your PostgreSQL server lacks UPSERT "
87 << "support. Use version 9.5 or better if possible."
91 infostream << "PostgreSQL Database: Version " << m_pgversion
92 << " Connection made." << std::endl;
98 void Database_PostgreSQL::verifyDatabase()
100 if (PQstatus(m_conn) == CONNECTION_OK)
107 void Database_PostgreSQL::ping()
109 if (PQping(m_connect_string.c_str()) != PQPING_OK) {
110 throw DatabaseException(std::string(
111 "PostgreSQL database error: ") +
112 PQerrorMessage(m_conn));
116 bool Database_PostgreSQL::initialized() const
118 return (PQstatus(m_conn) == CONNECTION_OK);
121 PGresult *Database_PostgreSQL::checkResults(PGresult *result, bool clear)
123 ExecStatusType statusType = PQresultStatus(result);
125 switch (statusType) {
126 case PGRES_COMMAND_OK:
127 case PGRES_TUPLES_OK:
129 case PGRES_FATAL_ERROR:
131 throw DatabaseException(
132 std::string("PostgreSQL database error: ") +
133 PQresultErrorMessage(result));
142 void Database_PostgreSQL::createTableIfNotExists(const std::string &table_name,
143 const std::string &definition)
145 std::string sql_check_table = "SELECT relname FROM pg_class WHERE relname='" +
147 PGresult *result = checkResults(PQexec(m_conn, sql_check_table.c_str()), false);
149 // If table doesn't exist, create it
150 if (!PQntuples(result)) {
151 checkResults(PQexec(m_conn, definition.c_str()));
157 void Database_PostgreSQL::beginSave()
160 checkResults(PQexec(m_conn, "BEGIN;"));
163 void Database_PostgreSQL::endSave()
165 checkResults(PQexec(m_conn, "COMMIT;"));
168 MapDatabasePostgreSQL::MapDatabasePostgreSQL(const std::string &connect_string):
169 Database_PostgreSQL(connect_string),
176 void MapDatabasePostgreSQL::createDatabase()
178 createTableIfNotExists("blocks",
179 "CREATE TABLE blocks ("
184 "PRIMARY KEY (posX,posY,posZ)"
188 infostream << "PostgreSQL: Map Database was initialized." << std::endl;
191 void MapDatabasePostgreSQL::initStatements()
193 prepareStatement("read_block",
194 "SELECT data FROM blocks "
195 "WHERE posX = $1::int4 AND posY = $2::int4 AND "
198 if (getPGVersion() < 90500) {
199 prepareStatement("write_block_insert",
200 "INSERT INTO blocks (posX, posY, posZ, data) SELECT "
201 "$1::int4, $2::int4, $3::int4, $4::bytea "
202 "WHERE NOT EXISTS (SELECT true FROM blocks "
203 "WHERE posX = $1::int4 AND posY = $2::int4 AND "
206 prepareStatement("write_block_update",
207 "UPDATE blocks SET data = $4::bytea "
208 "WHERE posX = $1::int4 AND posY = $2::int4 AND "
211 prepareStatement("write_block",
212 "INSERT INTO blocks (posX, posY, posZ, data) VALUES "
213 "($1::int4, $2::int4, $3::int4, $4::bytea) "
214 "ON CONFLICT ON CONSTRAINT blocks_pkey DO "
215 "UPDATE SET data = $4::bytea");
218 prepareStatement("delete_block", "DELETE FROM blocks WHERE "
219 "posX = $1::int4 AND posY = $2::int4 AND posZ = $3::int4");
221 prepareStatement("list_all_loadable_blocks",
222 "SELECT posX, posY, posZ FROM blocks");
225 bool MapDatabasePostgreSQL::saveBlock(const v3s16 &pos, const std::string &data)
227 // Verify if we don't overflow the platform integer with the mapblock size
228 if (data.size() > INT_MAX) {
229 errorstream << "Database_PostgreSQL::saveBlock: Data truncation! "
230 << "data.size() over 0xFFFFFFFF (== " << data.size()
242 const void *args[] = { &x, &y, &z, data.c_str() };
243 const int argLen[] = {
244 sizeof(x), sizeof(y), sizeof(z), (int)data.size()
246 const int argFmt[] = { 1, 1, 1, 1 };
248 if (getPGVersion() < 90500) {
249 execPrepared("write_block_update", ARRLEN(args), args, argLen, argFmt);
250 execPrepared("write_block_insert", ARRLEN(args), args, argLen, argFmt);
252 execPrepared("write_block", ARRLEN(args), args, argLen, argFmt);
257 void MapDatabasePostgreSQL::loadBlock(const v3s16 &pos, std::string *block)
266 const void *args[] = { &x, &y, &z };
267 const int argLen[] = { sizeof(x), sizeof(y), sizeof(z) };
268 const int argFmt[] = { 1, 1, 1 };
270 PGresult *results = execPrepared("read_block", ARRLEN(args), args,
271 argLen, argFmt, false);
275 if (PQntuples(results))
276 *block = std::string(PQgetvalue(results, 0, 0), PQgetlength(results, 0, 0));
281 bool MapDatabasePostgreSQL::deleteBlock(const v3s16 &pos)
290 const void *args[] = { &x, &y, &z };
291 const int argLen[] = { sizeof(x), sizeof(y), sizeof(z) };
292 const int argFmt[] = { 1, 1, 1 };
294 execPrepared("delete_block", ARRLEN(args), args, argLen, argFmt);
299 void MapDatabasePostgreSQL::listAllLoadableBlocks(std::vector<v3s16> &dst)
303 PGresult *results = execPrepared("list_all_loadable_blocks", 0,
304 NULL, NULL, NULL, false, false);
306 int numrows = PQntuples(results);
308 for (int row = 0; row < numrows; ++row)
309 dst.push_back(pg_to_v3s16(results, 0, 0));
317 PlayerDatabasePostgreSQL::PlayerDatabasePostgreSQL(const std::string &connect_string):
318 Database_PostgreSQL(connect_string),
325 void PlayerDatabasePostgreSQL::createDatabase()
327 createTableIfNotExists("player",
328 "CREATE TABLE player ("
329 "name VARCHAR(60) NOT NULL,"
330 "pitch NUMERIC(15, 7) NOT NULL,"
331 "yaw NUMERIC(15, 7) NOT NULL,"
332 "posX NUMERIC(15, 7) NOT NULL,"
333 "posY NUMERIC(15, 7) NOT NULL,"
334 "posZ NUMERIC(15, 7) NOT NULL,"
336 "breath INT NOT NULL,"
337 "creation_date TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT NOW(),"
338 "modification_date TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT NOW(),"
343 createTableIfNotExists("player_inventories",
344 "CREATE TABLE player_inventories ("
345 "player VARCHAR(60) NOT NULL,"
346 "inv_id INT NOT NULL,"
347 "inv_width INT NOT NULL,"
348 "inv_name TEXT NOT NULL DEFAULT '',"
349 "inv_size INT NOT NULL,"
350 "PRIMARY KEY(player, inv_id),"
351 "CONSTRAINT player_inventories_fkey FOREIGN KEY (player) REFERENCES "
352 "player (name) ON DELETE CASCADE"
356 createTableIfNotExists("player_inventory_items",
357 "CREATE TABLE player_inventory_items ("
358 "player VARCHAR(60) NOT NULL,"
359 "inv_id INT NOT NULL,"
360 "slot_id INT NOT NULL,"
361 "item TEXT NOT NULL DEFAULT '',"
362 "PRIMARY KEY(player, inv_id, slot_id),"
363 "CONSTRAINT player_inventory_items_fkey FOREIGN KEY (player) REFERENCES "
364 "player (name) ON DELETE CASCADE"
368 createTableIfNotExists("player_metadata",
369 "CREATE TABLE player_metadata ("
370 "player VARCHAR(60) NOT NULL,"
371 "attr VARCHAR(256) NOT NULL,"
373 "PRIMARY KEY(player, attr),"
374 "CONSTRAINT player_metadata_fkey FOREIGN KEY (player) REFERENCES "
375 "player (name) ON DELETE CASCADE"
379 infostream << "PostgreSQL: Player Database was inited." << std::endl;
382 void PlayerDatabasePostgreSQL::initStatements()
384 if (getPGVersion() < 90500) {
385 prepareStatement("create_player",
386 "INSERT INTO player(name, pitch, yaw, posX, posY, posZ, hp, breath) VALUES "
387 "($1, $2, $3, $4, $5, $6, $7::int, $8::int)");
389 prepareStatement("update_player",
390 "UPDATE SET pitch = $2, yaw = $3, posX = $4, posY = $5, posZ = $6, hp = $7::int, "
391 "breath = $8::int, modification_date = NOW() WHERE name = $1");
393 prepareStatement("save_player",
394 "INSERT INTO player(name, pitch, yaw, posX, posY, posZ, hp, breath) VALUES "
395 "($1, $2, $3, $4, $5, $6, $7::int, $8::int)"
396 "ON CONFLICT ON CONSTRAINT player_pkey DO UPDATE SET pitch = $2, yaw = $3, "
397 "posX = $4, posY = $5, posZ = $6, hp = $7::int, breath = $8::int, "
398 "modification_date = NOW()");
401 prepareStatement("remove_player", "DELETE FROM player WHERE name = $1");
403 prepareStatement("load_player_list", "SELECT name FROM player");
405 prepareStatement("remove_player_inventories",
406 "DELETE FROM player_inventories WHERE player = $1");
408 prepareStatement("remove_player_inventory_items",
409 "DELETE FROM player_inventory_items WHERE player = $1");
411 prepareStatement("add_player_inventory",
412 "INSERT INTO player_inventories (player, inv_id, inv_width, inv_name, inv_size) VALUES "
413 "($1, $2::int, $3::int, $4, $5::int)");
415 prepareStatement("add_player_inventory_item",
416 "INSERT INTO player_inventory_items (player, inv_id, slot_id, item) VALUES "
417 "($1, $2::int, $3::int, $4)");
419 prepareStatement("load_player_inventories",
420 "SELECT inv_id, inv_width, inv_name, inv_size FROM player_inventories "
421 "WHERE player = $1 ORDER BY inv_id");
423 prepareStatement("load_player_inventory_items",
424 "SELECT slot_id, item FROM player_inventory_items WHERE "
425 "player = $1 AND inv_id = $2::int");
427 prepareStatement("load_player",
428 "SELECT pitch, yaw, posX, posY, posZ, hp, breath FROM player WHERE name = $1");
430 prepareStatement("remove_player_metadata",
431 "DELETE FROM player_metadata WHERE player = $1");
433 prepareStatement("save_player_metadata",
434 "INSERT INTO player_metadata (player, attr, value) VALUES ($1, $2, $3)");
436 prepareStatement("load_player_metadata",
437 "SELECT attr, value FROM player_metadata WHERE player = $1");
441 bool PlayerDatabasePostgreSQL::playerDataExists(const std::string &playername)
445 const char *values[] = { playername.c_str() };
446 PGresult *results = execPrepared("load_player", 1, values, false);
448 bool res = (PQntuples(results) > 0);
453 void PlayerDatabasePostgreSQL::savePlayer(RemotePlayer *player)
455 PlayerSAO* sao = player->getPlayerSAO();
461 v3f pos = sao->getBasePosition();
462 std::string pitch = ftos(sao->getPitch());
463 std::string yaw = ftos(sao->getYaw());
464 std::string posx = ftos(pos.X);
465 std::string posy = ftos(pos.Y);
466 std::string posz = ftos(pos.Z);
467 std::string hp = itos(sao->getHP());
468 std::string breath = itos(sao->getBreath());
469 const char *values[] = {
473 posx.c_str(), posy.c_str(), posz.c_str(),
478 const char* rmvalues[] = { player->getName() };
481 if (getPGVersion() < 90500) {
482 if (!playerDataExists(player->getName()))
483 execPrepared("create_player", 8, values, true, false);
485 execPrepared("update_player", 8, values, true, false);
488 execPrepared("save_player", 8, values, true, false);
490 // Write player inventories
491 execPrepared("remove_player_inventories", 1, rmvalues);
492 execPrepared("remove_player_inventory_items", 1, rmvalues);
494 std::vector<const InventoryList*> inventory_lists = sao->getInventory()->getLists();
495 for (u16 i = 0; i < inventory_lists.size(); i++) {
496 const InventoryList* list = inventory_lists[i];
497 std::string name = list->getName(), width = itos(list->getWidth()),
498 inv_id = itos(i), lsize = itos(list->getSize());
500 const char* inv_values[] = {
507 execPrepared("add_player_inventory", 5, inv_values);
509 for (u32 j = 0; j < list->getSize(); j++) {
510 std::ostringstream os;
511 list->getItem(j).serialize(os);
512 std::string itemStr = os.str(), slotId = itos(j);
514 const char* invitem_values[] = {
520 execPrepared("add_player_inventory_item", 4, invitem_values);
524 execPrepared("remove_player_metadata", 1, rmvalues);
525 const PlayerAttributes &attrs = sao->getExtendedAttributes();
526 for (PlayerAttributes::const_iterator it = attrs.begin(); it != attrs.end(); ++it) {
527 const char *meta_values[] = {
532 execPrepared("save_player_metadata", 3, meta_values);
537 bool PlayerDatabasePostgreSQL::loadPlayer(RemotePlayer *player, PlayerSAO *sao)
542 const char *values[] = { player->getName() };
543 PGresult *results = execPrepared("load_player", 1, values, false, false);
545 // Player not found, return not found
546 if (!PQntuples(results)) {
551 sao->setPitch(pg_to_float(results, 0, 0));
552 sao->setYaw(pg_to_float(results, 0, 1));
553 sao->setBasePosition(v3f(
554 pg_to_float(results, 0, 2),
555 pg_to_float(results, 0, 3),
556 pg_to_float(results, 0, 4))
558 sao->setHPRaw((s16) pg_to_int(results, 0, 5));
559 sao->setBreath((u16) pg_to_int(results, 0, 6), false);
564 results = execPrepared("load_player_inventories", 1, values, false, false);
566 int resultCount = PQntuples(results);
568 for (int row = 0; row < resultCount; ++row) {
569 InventoryList* invList = player->inventory.
570 addList(PQgetvalue(results, row, 2), pg_to_uint(results, row, 3));
571 invList->setWidth(pg_to_uint(results, row, 1));
573 u32 invId = pg_to_uint(results, row, 0);
574 std::string invIdStr = itos(invId);
576 const char* values2[] = {
580 PGresult *results2 = execPrepared("load_player_inventory_items", 2,
581 values2, false, false);
583 int resultCount2 = PQntuples(results2);
584 for (int row2 = 0; row2 < resultCount2; row2++) {
585 const std::string itemStr = PQgetvalue(results2, row2, 1);
586 if (itemStr.length() > 0) {
588 stack.deSerialize(itemStr);
589 invList->addItem(pg_to_uint(results2, row2, 0), stack);
597 results = execPrepared("load_player_metadata", 1, values, false);
599 int numrows = PQntuples(results);
600 for (int row = 0; row < numrows; row++) {
601 sao->setExtendedAttribute(PQgetvalue(results, row, 0),PQgetvalue(results, row, 1));
609 bool PlayerDatabasePostgreSQL::removePlayer(const std::string &name)
611 if (!playerDataExists(name))
616 const char *values[] = { name.c_str() };
617 execPrepared("remove_player", 1, values);
622 void PlayerDatabasePostgreSQL::listPlayers(std::vector<std::string> &res)
626 PGresult *results = execPrepared("load_player_list", 0, NULL, false);
628 int numrows = PQntuples(results);
629 for (int row = 0; row < numrows; row++)
630 res.push_back(PQgetvalue(results, row, 0));
635 #endif // USE_POSTGRESQL