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)
48 if (m_connect_string.empty()) {
49 throw SettingNotFoundException(
50 "Set pgsql_connection string in world.mt to "
51 "use the postgresql backend\n"
53 "pgsql_connection has the following form: \n"
54 "\tpgsql_connection = host=127.0.0.1 port=5432 user=mt_user "
55 "password=mt_password dbname=minetest_world\n"
56 "mt_user should have CREATE TABLE, INSERT, SELECT, UPDATE and "
57 "DELETE rights on the database.\n"
58 "Don't create mt_user as a SUPERUSER!");
62 Database_PostgreSQL::~Database_PostgreSQL()
67 void Database_PostgreSQL::connectToDatabase()
69 m_conn = PQconnectdb(m_connect_string.c_str());
71 if (PQstatus(m_conn) != CONNECTION_OK) {
72 throw DatabaseException(std::string(
73 "PostgreSQL database error: ") +
74 PQerrorMessage(m_conn));
77 m_pgversion = PQserverVersion(m_conn);
80 * We are using UPSERT feature from PostgreSQL 9.5
81 * to have the better performance where possible.
83 if (m_pgversion < 90500) {
84 warningstream << "Your PostgreSQL server lacks UPSERT "
85 << "support. Use version 9.5 or better if possible."
89 infostream << "PostgreSQL Database: Version " << m_pgversion
90 << " Connection made." << std::endl;
96 void Database_PostgreSQL::verifyDatabase()
98 if (PQstatus(m_conn) == CONNECTION_OK)
105 void Database_PostgreSQL::ping()
107 if (PQping(m_connect_string.c_str()) != PQPING_OK) {
108 throw DatabaseException(std::string(
109 "PostgreSQL database error: ") +
110 PQerrorMessage(m_conn));
114 bool Database_PostgreSQL::initialized() const
116 return (PQstatus(m_conn) == CONNECTION_OK);
119 PGresult *Database_PostgreSQL::checkResults(PGresult *result, bool clear)
121 ExecStatusType statusType = PQresultStatus(result);
123 switch (statusType) {
124 case PGRES_COMMAND_OK:
125 case PGRES_TUPLES_OK:
127 case PGRES_FATAL_ERROR:
129 throw DatabaseException(
130 std::string("PostgreSQL database error: ") +
131 PQresultErrorMessage(result));
140 void Database_PostgreSQL::createTableIfNotExists(const std::string &table_name,
141 const std::string &definition)
143 std::string sql_check_table = "SELECT relname FROM pg_class WHERE relname='" +
145 PGresult *result = checkResults(PQexec(m_conn, sql_check_table.c_str()), false);
147 // If table doesn't exist, create it
148 if (!PQntuples(result)) {
149 checkResults(PQexec(m_conn, definition.c_str()));
155 void Database_PostgreSQL::beginSave()
158 checkResults(PQexec(m_conn, "BEGIN;"));
161 void Database_PostgreSQL::endSave()
163 checkResults(PQexec(m_conn, "COMMIT;"));
166 MapDatabasePostgreSQL::MapDatabasePostgreSQL(const std::string &connect_string):
167 Database_PostgreSQL(connect_string),
174 void MapDatabasePostgreSQL::createDatabase()
176 createTableIfNotExists("blocks",
177 "CREATE TABLE blocks ("
182 "PRIMARY KEY (posX,posY,posZ)"
186 infostream << "PostgreSQL: Map Database was initialized." << std::endl;
189 void MapDatabasePostgreSQL::initStatements()
191 prepareStatement("read_block",
192 "SELECT data FROM blocks "
193 "WHERE posX = $1::int4 AND posY = $2::int4 AND "
196 if (getPGVersion() < 90500) {
197 prepareStatement("write_block_insert",
198 "INSERT INTO blocks (posX, posY, posZ, data) SELECT "
199 "$1::int4, $2::int4, $3::int4, $4::bytea "
200 "WHERE NOT EXISTS (SELECT true FROM blocks "
201 "WHERE posX = $1::int4 AND posY = $2::int4 AND "
204 prepareStatement("write_block_update",
205 "UPDATE blocks SET data = $4::bytea "
206 "WHERE posX = $1::int4 AND posY = $2::int4 AND "
209 prepareStatement("write_block",
210 "INSERT INTO blocks (posX, posY, posZ, data) VALUES "
211 "($1::int4, $2::int4, $3::int4, $4::bytea) "
212 "ON CONFLICT ON CONSTRAINT blocks_pkey DO "
213 "UPDATE SET data = $4::bytea");
216 prepareStatement("delete_block", "DELETE FROM blocks WHERE "
217 "posX = $1::int4 AND posY = $2::int4 AND posZ = $3::int4");
219 prepareStatement("list_all_loadable_blocks",
220 "SELECT posX, posY, posZ FROM blocks");
223 bool MapDatabasePostgreSQL::saveBlock(const v3s16 &pos, const std::string &data)
225 // Verify if we don't overflow the platform integer with the mapblock size
226 if (data.size() > INT_MAX) {
227 errorstream << "Database_PostgreSQL::saveBlock: Data truncation! "
228 << "data.size() over 0xFFFFFFFF (== " << data.size()
240 const void *args[] = { &x, &y, &z, data.c_str() };
241 const int argLen[] = {
242 sizeof(x), sizeof(y), sizeof(z), (int)data.size()
244 const int argFmt[] = { 1, 1, 1, 1 };
246 if (getPGVersion() < 90500) {
247 execPrepared("write_block_update", ARRLEN(args), args, argLen, argFmt);
248 execPrepared("write_block_insert", ARRLEN(args), args, argLen, argFmt);
250 execPrepared("write_block", ARRLEN(args), args, argLen, argFmt);
255 void MapDatabasePostgreSQL::loadBlock(const v3s16 &pos, std::string *block)
264 const void *args[] = { &x, &y, &z };
265 const int argLen[] = { sizeof(x), sizeof(y), sizeof(z) };
266 const int argFmt[] = { 1, 1, 1 };
268 PGresult *results = execPrepared("read_block", ARRLEN(args), args,
269 argLen, argFmt, false);
273 if (PQntuples(results))
274 *block = std::string(PQgetvalue(results, 0, 0), PQgetlength(results, 0, 0));
279 bool MapDatabasePostgreSQL::deleteBlock(const v3s16 &pos)
288 const void *args[] = { &x, &y, &z };
289 const int argLen[] = { sizeof(x), sizeof(y), sizeof(z) };
290 const int argFmt[] = { 1, 1, 1 };
292 execPrepared("delete_block", ARRLEN(args), args, argLen, argFmt);
297 void MapDatabasePostgreSQL::listAllLoadableBlocks(std::vector<v3s16> &dst)
301 PGresult *results = execPrepared("list_all_loadable_blocks", 0,
302 NULL, NULL, NULL, false, false);
304 int numrows = PQntuples(results);
306 for (int row = 0; row < numrows; ++row)
307 dst.push_back(pg_to_v3s16(results, 0, 0));
315 PlayerDatabasePostgreSQL::PlayerDatabasePostgreSQL(const std::string &connect_string):
316 Database_PostgreSQL(connect_string),
323 void PlayerDatabasePostgreSQL::createDatabase()
325 createTableIfNotExists("player",
326 "CREATE TABLE player ("
327 "name VARCHAR(60) NOT NULL,"
328 "pitch NUMERIC(15, 7) NOT NULL,"
329 "yaw NUMERIC(15, 7) NOT NULL,"
330 "posX NUMERIC(15, 7) NOT NULL,"
331 "posY NUMERIC(15, 7) NOT NULL,"
332 "posZ NUMERIC(15, 7) NOT NULL,"
334 "breath INT NOT NULL,"
335 "creation_date TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT NOW(),"
336 "modification_date TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT NOW(),"
341 createTableIfNotExists("player_inventories",
342 "CREATE TABLE player_inventories ("
343 "player VARCHAR(60) NOT NULL,"
344 "inv_id INT NOT NULL,"
345 "inv_width INT NOT NULL,"
346 "inv_name TEXT NOT NULL DEFAULT '',"
347 "inv_size INT NOT NULL,"
348 "PRIMARY KEY(player, inv_id),"
349 "CONSTRAINT player_inventories_fkey FOREIGN KEY (player) REFERENCES "
350 "player (name) ON DELETE CASCADE"
354 createTableIfNotExists("player_inventory_items",
355 "CREATE TABLE player_inventory_items ("
356 "player VARCHAR(60) NOT NULL,"
357 "inv_id INT NOT NULL,"
358 "slot_id INT NOT NULL,"
359 "item TEXT NOT NULL DEFAULT '',"
360 "PRIMARY KEY(player, inv_id, slot_id),"
361 "CONSTRAINT player_inventory_items_fkey FOREIGN KEY (player) REFERENCES "
362 "player (name) ON DELETE CASCADE"
366 createTableIfNotExists("player_metadata",
367 "CREATE TABLE player_metadata ("
368 "player VARCHAR(60) NOT NULL,"
369 "attr VARCHAR(256) NOT NULL,"
371 "PRIMARY KEY(player, attr),"
372 "CONSTRAINT player_metadata_fkey FOREIGN KEY (player) REFERENCES "
373 "player (name) ON DELETE CASCADE"
377 infostream << "PostgreSQL: Player Database was inited." << std::endl;
380 void PlayerDatabasePostgreSQL::initStatements()
382 if (getPGVersion() < 90500) {
383 prepareStatement("create_player",
384 "INSERT INTO player(name, pitch, yaw, posX, posY, posZ, hp, breath) VALUES "
385 "($1, $2, $3, $4, $5, $6, $7::int, $8::int)");
387 prepareStatement("update_player",
388 "UPDATE SET pitch = $2, yaw = $3, posX = $4, posY = $5, posZ = $6, hp = $7::int, "
389 "breath = $8::int, modification_date = NOW() WHERE name = $1");
391 prepareStatement("save_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)"
394 "ON CONFLICT ON CONSTRAINT player_pkey DO UPDATE SET pitch = $2, yaw = $3, "
395 "posX = $4, posY = $5, posZ = $6, hp = $7::int, breath = $8::int, "
396 "modification_date = NOW()");
399 prepareStatement("remove_player", "DELETE FROM player WHERE name = $1");
401 prepareStatement("load_player_list", "SELECT name FROM player");
403 prepareStatement("remove_player_inventories",
404 "DELETE FROM player_inventories WHERE player = $1");
406 prepareStatement("remove_player_inventory_items",
407 "DELETE FROM player_inventory_items WHERE player = $1");
409 prepareStatement("add_player_inventory",
410 "INSERT INTO player_inventories (player, inv_id, inv_width, inv_name, inv_size) VALUES "
411 "($1, $2::int, $3::int, $4, $5::int)");
413 prepareStatement("add_player_inventory_item",
414 "INSERT INTO player_inventory_items (player, inv_id, slot_id, item) VALUES "
415 "($1, $2::int, $3::int, $4)");
417 prepareStatement("load_player_inventories",
418 "SELECT inv_id, inv_width, inv_name, inv_size FROM player_inventories "
419 "WHERE player = $1 ORDER BY inv_id");
421 prepareStatement("load_player_inventory_items",
422 "SELECT slot_id, item FROM player_inventory_items WHERE "
423 "player = $1 AND inv_id = $2::int");
425 prepareStatement("load_player",
426 "SELECT pitch, yaw, posX, posY, posZ, hp, breath FROM player WHERE name = $1");
428 prepareStatement("remove_player_metadata",
429 "DELETE FROM player_metadata WHERE player = $1");
431 prepareStatement("save_player_metadata",
432 "INSERT INTO player_metadata (player, attr, value) VALUES ($1, $2, $3)");
434 prepareStatement("load_player_metadata",
435 "SELECT attr, value FROM player_metadata WHERE player = $1");
439 bool PlayerDatabasePostgreSQL::playerDataExists(const std::string &playername)
443 const char *values[] = { playername.c_str() };
444 PGresult *results = execPrepared("load_player", 1, values, false);
446 bool res = (PQntuples(results) > 0);
451 void PlayerDatabasePostgreSQL::savePlayer(RemotePlayer *player)
453 PlayerSAO* sao = player->getPlayerSAO();
459 v3f pos = sao->getBasePosition();
460 std::string pitch = ftos(sao->getPitch());
461 std::string yaw = ftos(sao->getYaw());
462 std::string posx = ftos(pos.X);
463 std::string posy = ftos(pos.Y);
464 std::string posz = ftos(pos.Z);
465 std::string hp = itos(sao->getHP());
466 std::string breath = itos(sao->getBreath());
467 const char *values[] = {
471 posx.c_str(), posy.c_str(), posz.c_str(),
476 const char* rmvalues[] = { player->getName() };
479 if (getPGVersion() < 90500) {
480 if (!playerDataExists(player->getName()))
481 execPrepared("create_player", 8, values, true, false);
483 execPrepared("update_player", 8, values, true, false);
486 execPrepared("save_player", 8, values, true, false);
488 // Write player inventories
489 execPrepared("remove_player_inventories", 1, rmvalues);
490 execPrepared("remove_player_inventory_items", 1, rmvalues);
492 std::vector<const InventoryList*> inventory_lists = sao->getInventory()->getLists();
493 for (u16 i = 0; i < inventory_lists.size(); i++) {
494 const InventoryList* list = inventory_lists[i];
495 std::string name = list->getName(), width = itos(list->getWidth()),
496 inv_id = itos(i), lsize = itos(list->getSize());
498 const char* inv_values[] = {
505 execPrepared("add_player_inventory", 5, inv_values);
507 for (u32 j = 0; j < list->getSize(); j++) {
508 std::ostringstream os;
509 list->getItem(j).serialize(os);
510 std::string itemStr = os.str(), slotId = itos(j);
512 const char* invitem_values[] = {
518 execPrepared("add_player_inventory_item", 4, invitem_values);
522 execPrepared("remove_player_metadata", 1, rmvalues);
523 const PlayerAttributes &attrs = sao->getExtendedAttributes();
524 for (PlayerAttributes::const_iterator it = attrs.begin(); it != attrs.end(); ++it) {
525 const char *meta_values[] = {
530 execPrepared("save_player_metadata", 3, meta_values);
535 bool PlayerDatabasePostgreSQL::loadPlayer(RemotePlayer *player, PlayerSAO *sao)
540 const char *values[] = { player->getName() };
541 PGresult *results = execPrepared("load_player", 1, values, false, false);
543 // Player not found, return not found
544 if (!PQntuples(results)) {
549 sao->setPitch(pg_to_float(results, 0, 0));
550 sao->setYaw(pg_to_float(results, 0, 1));
551 sao->setBasePosition(v3f(
552 pg_to_float(results, 0, 2),
553 pg_to_float(results, 0, 3),
554 pg_to_float(results, 0, 4))
556 sao->setHPRaw((s16) pg_to_int(results, 0, 5));
557 sao->setBreath((u16) pg_to_int(results, 0, 6), false);
562 results = execPrepared("load_player_inventories", 1, values, false, false);
564 int resultCount = PQntuples(results);
566 for (int row = 0; row < resultCount; ++row) {
567 InventoryList* invList = player->inventory.
568 addList(PQgetvalue(results, row, 2), pg_to_uint(results, row, 3));
569 invList->setWidth(pg_to_uint(results, row, 1));
571 u32 invId = pg_to_uint(results, row, 0);
572 std::string invIdStr = itos(invId);
574 const char* values2[] = {
578 PGresult *results2 = execPrepared("load_player_inventory_items", 2,
579 values2, false, false);
581 int resultCount2 = PQntuples(results2);
582 for (int row2 = 0; row2 < resultCount2; row2++) {
583 const std::string itemStr = PQgetvalue(results2, row2, 1);
584 if (itemStr.length() > 0) {
586 stack.deSerialize(itemStr);
587 invList->addItem(pg_to_uint(results2, row2, 0), stack);
595 results = execPrepared("load_player_metadata", 1, values, false);
597 int numrows = PQntuples(results);
598 for (int row = 0; row < numrows; row++) {
599 sao->setExtendedAttribute(PQgetvalue(results, row, 0),PQgetvalue(results, row, 1));
607 bool PlayerDatabasePostgreSQL::removePlayer(const std::string &name)
609 if (!playerDataExists(name))
614 const char *values[] = { name.c_str() };
615 execPrepared("remove_player", 1, values);
620 void PlayerDatabasePostgreSQL::listPlayers(std::vector<std::string> &res)
624 PGresult *results = execPrepared("load_player_list", 0, NULL, false);
626 int numrows = PQntuples(results);
627 for (int row = 0; row < numrows; row++)
628 res.push_back(PQgetvalue(results, row, 0));
633 #endif // USE_POSTGRESQL