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 "content_sao.h"
40 #include "remoteplayer.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::verifyDatabase()
95 if (PQstatus(m_conn) == CONNECTION_OK)
102 void Database_PostgreSQL::ping()
104 if (PQping(m_connect_string.c_str()) != PQPING_OK) {
105 throw DatabaseException(std::string(
106 "PostgreSQL database error: ") +
107 PQerrorMessage(m_conn));
111 bool Database_PostgreSQL::initialized() const
113 return (PQstatus(m_conn) == CONNECTION_OK);
116 PGresult *Database_PostgreSQL::checkResults(PGresult *result, bool clear)
118 ExecStatusType statusType = PQresultStatus(result);
120 switch (statusType) {
121 case PGRES_COMMAND_OK:
122 case PGRES_TUPLES_OK:
124 case PGRES_FATAL_ERROR:
126 throw DatabaseException(
127 std::string("PostgreSQL database error: ") +
128 PQresultErrorMessage(result));
137 void Database_PostgreSQL::createTableIfNotExists(const std::string &table_name,
138 const std::string &definition)
140 std::string sql_check_table = "SELECT relname FROM pg_class WHERE relname='" +
142 PGresult *result = checkResults(PQexec(m_conn, sql_check_table.c_str()), false);
144 // If table doesn't exist, create it
145 if (!PQntuples(result)) {
146 checkResults(PQexec(m_conn, definition.c_str()));
152 void Database_PostgreSQL::beginSave()
155 checkResults(PQexec(m_conn, "BEGIN;"));
158 void Database_PostgreSQL::endSave()
160 checkResults(PQexec(m_conn, "COMMIT;"));
163 MapDatabasePostgreSQL::MapDatabasePostgreSQL(const std::string &connect_string):
164 Database_PostgreSQL(connect_string),
171 void MapDatabasePostgreSQL::createDatabase()
173 createTableIfNotExists("blocks",
174 "CREATE TABLE blocks ("
179 "PRIMARY KEY (posX,posY,posZ)"
183 infostream << "PostgreSQL: Map Database was initialized." << std::endl;
186 void MapDatabasePostgreSQL::initStatements()
188 prepareStatement("read_block",
189 "SELECT data FROM blocks "
190 "WHERE posX = $1::int4 AND posY = $2::int4 AND "
193 if (getPGVersion() < 90500) {
194 prepareStatement("write_block_insert",
195 "INSERT INTO blocks (posX, posY, posZ, data) SELECT "
196 "$1::int4, $2::int4, $3::int4, $4::bytea "
197 "WHERE NOT EXISTS (SELECT true FROM blocks "
198 "WHERE posX = $1::int4 AND posY = $2::int4 AND "
201 prepareStatement("write_block_update",
202 "UPDATE blocks SET data = $4::bytea "
203 "WHERE posX = $1::int4 AND posY = $2::int4 AND "
206 prepareStatement("write_block",
207 "INSERT INTO blocks (posX, posY, posZ, data) VALUES "
208 "($1::int4, $2::int4, $3::int4, $4::bytea) "
209 "ON CONFLICT ON CONSTRAINT blocks_pkey DO "
210 "UPDATE SET data = $4::bytea");
213 prepareStatement("delete_block", "DELETE FROM blocks WHERE "
214 "posX = $1::int4 AND posY = $2::int4 AND posZ = $3::int4");
216 prepareStatement("list_all_loadable_blocks",
217 "SELECT posX, posY, posZ FROM blocks");
220 bool MapDatabasePostgreSQL::saveBlock(const v3s16 &pos, const std::string &data)
222 // Verify if we don't overflow the platform integer with the mapblock size
223 if (data.size() > INT_MAX) {
224 errorstream << "Database_PostgreSQL::saveBlock: Data truncation! "
225 << "data.size() over 0xFFFFFFFF (== " << data.size()
237 const void *args[] = { &x, &y, &z, data.c_str() };
238 const int argLen[] = {
239 sizeof(x), sizeof(y), sizeof(z), (int)data.size()
241 const int argFmt[] = { 1, 1, 1, 1 };
243 if (getPGVersion() < 90500) {
244 execPrepared("write_block_update", ARRLEN(args), args, argLen, argFmt);
245 execPrepared("write_block_insert", ARRLEN(args), args, argLen, argFmt);
247 execPrepared("write_block", ARRLEN(args), args, argLen, argFmt);
252 void MapDatabasePostgreSQL::loadBlock(const v3s16 &pos, std::string *block)
261 const void *args[] = { &x, &y, &z };
262 const int argLen[] = { sizeof(x), sizeof(y), sizeof(z) };
263 const int argFmt[] = { 1, 1, 1 };
265 PGresult *results = execPrepared("read_block", ARRLEN(args), args,
266 argLen, argFmt, false);
270 if (PQntuples(results))
271 *block = std::string(PQgetvalue(results, 0, 0), PQgetlength(results, 0, 0));
276 bool MapDatabasePostgreSQL::deleteBlock(const v3s16 &pos)
285 const void *args[] = { &x, &y, &z };
286 const int argLen[] = { sizeof(x), sizeof(y), sizeof(z) };
287 const int argFmt[] = { 1, 1, 1 };
289 execPrepared("delete_block", ARRLEN(args), args, argLen, argFmt);
294 void MapDatabasePostgreSQL::listAllLoadableBlocks(std::vector<v3s16> &dst)
298 PGresult *results = execPrepared("list_all_loadable_blocks", 0,
299 NULL, NULL, NULL, false, false);
301 int numrows = PQntuples(results);
303 for (int row = 0; row < numrows; ++row)
304 dst.push_back(pg_to_v3s16(results, 0, 0));
312 PlayerDatabasePostgreSQL::PlayerDatabasePostgreSQL(const std::string &connect_string):
313 Database_PostgreSQL(connect_string),
320 void PlayerDatabasePostgreSQL::createDatabase()
322 createTableIfNotExists("player",
323 "CREATE TABLE player ("
324 "name VARCHAR(60) NOT NULL,"
325 "pitch NUMERIC(15, 7) NOT NULL,"
326 "yaw NUMERIC(15, 7) NOT NULL,"
327 "posX NUMERIC(15, 7) NOT NULL,"
328 "posY NUMERIC(15, 7) NOT NULL,"
329 "posZ NUMERIC(15, 7) NOT NULL,"
331 "breath INT NOT NULL,"
332 "creation_date TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT NOW(),"
333 "modification_date TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT NOW(),"
338 createTableIfNotExists("player_inventories",
339 "CREATE TABLE player_inventories ("
340 "player VARCHAR(60) NOT NULL,"
341 "inv_id INT NOT NULL,"
342 "inv_width INT NOT NULL,"
343 "inv_name TEXT NOT NULL DEFAULT '',"
344 "inv_size INT NOT NULL,"
345 "PRIMARY KEY(player, inv_id),"
346 "CONSTRAINT player_inventories_fkey FOREIGN KEY (player) REFERENCES "
347 "player (name) ON DELETE CASCADE"
351 createTableIfNotExists("player_inventory_items",
352 "CREATE TABLE player_inventory_items ("
353 "player VARCHAR(60) NOT NULL,"
354 "inv_id INT NOT NULL,"
355 "slot_id INT NOT NULL,"
356 "item TEXT NOT NULL DEFAULT '',"
357 "PRIMARY KEY(player, inv_id, slot_id),"
358 "CONSTRAINT player_inventory_items_fkey FOREIGN KEY (player) REFERENCES "
359 "player (name) ON DELETE CASCADE"
363 createTableIfNotExists("player_metadata",
364 "CREATE TABLE player_metadata ("
365 "player VARCHAR(60) NOT NULL,"
366 "attr VARCHAR(256) NOT NULL,"
368 "PRIMARY KEY(player, attr),"
369 "CONSTRAINT player_metadata_fkey FOREIGN KEY (player) REFERENCES "
370 "player (name) ON DELETE CASCADE"
374 infostream << "PostgreSQL: Player Database was inited." << std::endl;
377 void PlayerDatabasePostgreSQL::initStatements()
379 if (getPGVersion() < 90500) {
380 prepareStatement("create_player",
381 "INSERT INTO player(name, pitch, yaw, posX, posY, posZ, hp, breath) VALUES "
382 "($1, $2, $3, $4, $5, $6, $7::int, $8::int)");
384 prepareStatement("update_player",
385 "UPDATE SET pitch = $2, yaw = $3, posX = $4, posY = $5, posZ = $6, hp = $7::int, "
386 "breath = $8::int, modification_date = NOW() WHERE name = $1");
388 prepareStatement("save_player",
389 "INSERT INTO player(name, pitch, yaw, posX, posY, posZ, hp, breath) VALUES "
390 "($1, $2, $3, $4, $5, $6, $7::int, $8::int)"
391 "ON CONFLICT ON CONSTRAINT player_pkey DO UPDATE SET pitch = $2, yaw = $3, "
392 "posX = $4, posY = $5, posZ = $6, hp = $7::int, breath = $8::int, "
393 "modification_date = NOW()");
396 prepareStatement("remove_player", "DELETE FROM player WHERE name = $1");
398 prepareStatement("load_player_list", "SELECT name FROM player");
400 prepareStatement("remove_player_inventories",
401 "DELETE FROM player_inventories WHERE player = $1");
403 prepareStatement("remove_player_inventory_items",
404 "DELETE FROM player_inventory_items WHERE player = $1");
406 prepareStatement("add_player_inventory",
407 "INSERT INTO player_inventories (player, inv_id, inv_width, inv_name, inv_size) VALUES "
408 "($1, $2::int, $3::int, $4, $5::int)");
410 prepareStatement("add_player_inventory_item",
411 "INSERT INTO player_inventory_items (player, inv_id, slot_id, item) VALUES "
412 "($1, $2::int, $3::int, $4)");
414 prepareStatement("load_player_inventories",
415 "SELECT inv_id, inv_width, inv_name, inv_size FROM player_inventories "
416 "WHERE player = $1 ORDER BY inv_id");
418 prepareStatement("load_player_inventory_items",
419 "SELECT slot_id, item FROM player_inventory_items WHERE "
420 "player = $1 AND inv_id = $2::int");
422 prepareStatement("load_player",
423 "SELECT pitch, yaw, posX, posY, posZ, hp, breath FROM player WHERE name = $1");
425 prepareStatement("remove_player_metadata",
426 "DELETE FROM player_metadata WHERE player = $1");
428 prepareStatement("save_player_metadata",
429 "INSERT INTO player_metadata (player, attr, value) VALUES ($1, $2, $3)");
431 prepareStatement("load_player_metadata",
432 "SELECT attr, value FROM player_metadata WHERE player = $1");
436 bool PlayerDatabasePostgreSQL::playerDataExists(const std::string &playername)
440 const char *values[] = { playername.c_str() };
441 PGresult *results = execPrepared("load_player", 1, values, false);
443 bool res = (PQntuples(results) > 0);
448 void PlayerDatabasePostgreSQL::savePlayer(RemotePlayer *player)
450 PlayerSAO* sao = player->getPlayerSAO();
456 v3f pos = sao->getBasePosition();
457 std::string pitch = ftos(sao->getPitch());
458 std::string yaw = ftos(sao->getYaw());
459 std::string posx = ftos(pos.X);
460 std::string posy = ftos(pos.Y);
461 std::string posz = ftos(pos.Z);
462 std::string hp = itos(sao->getHP());
463 std::string breath = itos(sao->getBreath());
464 const char *values[] = {
468 posx.c_str(), posy.c_str(), posz.c_str(),
473 const char* rmvalues[] = { player->getName() };
476 if (getPGVersion() < 90500) {
477 if (!playerDataExists(player->getName()))
478 execPrepared("create_player", 8, values, true, false);
480 execPrepared("update_player", 8, values, true, false);
483 execPrepared("save_player", 8, values, true, false);
485 // Write player inventories
486 execPrepared("remove_player_inventories", 1, rmvalues);
487 execPrepared("remove_player_inventory_items", 1, rmvalues);
489 std::vector<const InventoryList*> inventory_lists = sao->getInventory()->getLists();
490 for (u16 i = 0; i < inventory_lists.size(); i++) {
491 const InventoryList* list = inventory_lists[i];
492 const std::string &name = list->getName();
493 std::string width = itos(list->getWidth()),
494 inv_id = itos(i), lsize = itos(list->getSize());
496 const char* inv_values[] = {
503 execPrepared("add_player_inventory", 5, inv_values);
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(), slotId = itos(j);
510 const char* invitem_values[] = {
516 execPrepared("add_player_inventory_item", 4, invitem_values);
520 execPrepared("remove_player_metadata", 1, rmvalues);
521 const PlayerAttributes &attrs = sao->getExtendedAttributes();
522 for (const auto &attr : attrs) {
523 const char *meta_values[] = {
528 execPrepared("save_player_metadata", 3, meta_values);
533 bool PlayerDatabasePostgreSQL::loadPlayer(RemotePlayer *player, PlayerSAO *sao)
538 const char *values[] = { player->getName() };
539 PGresult *results = execPrepared("load_player", 1, values, false, false);
541 // Player not found, return not found
542 if (!PQntuples(results)) {
547 sao->setPitch(pg_to_float(results, 0, 0));
548 sao->setYaw(pg_to_float(results, 0, 1));
549 sao->setBasePosition(v3f(
550 pg_to_float(results, 0, 2),
551 pg_to_float(results, 0, 3),
552 pg_to_float(results, 0, 4))
554 sao->setHPRaw((s16) pg_to_int(results, 0, 5));
555 sao->setBreath((u16) pg_to_int(results, 0, 6), false);
560 results = execPrepared("load_player_inventories", 1, values, false, false);
562 int resultCount = PQntuples(results);
564 for (int row = 0; row < resultCount; ++row) {
565 InventoryList* invList = player->inventory.
566 addList(PQgetvalue(results, row, 2), pg_to_uint(results, row, 3));
567 invList->setWidth(pg_to_uint(results, row, 1));
569 u32 invId = pg_to_uint(results, row, 0);
570 std::string invIdStr = itos(invId);
572 const char* values2[] = {
576 PGresult *results2 = execPrepared("load_player_inventory_items", 2,
577 values2, false, false);
579 int resultCount2 = PQntuples(results2);
580 for (int row2 = 0; row2 < resultCount2; row2++) {
581 const std::string itemStr = PQgetvalue(results2, row2, 1);
582 if (itemStr.length() > 0) {
584 stack.deSerialize(itemStr);
585 invList->changeItem(pg_to_uint(results2, row2, 0), stack);
593 results = execPrepared("load_player_metadata", 1, values, false);
595 int numrows = PQntuples(results);
596 for (int row = 0; row < numrows; row++) {
597 sao->setExtendedAttribute(PQgetvalue(results, row, 0),PQgetvalue(results, row, 1));
605 bool PlayerDatabasePostgreSQL::removePlayer(const std::string &name)
607 if (!playerDataExists(name))
612 const char *values[] = { name.c_str() };
613 execPrepared("remove_player", 1, values);
618 void PlayerDatabasePostgreSQL::listPlayers(std::vector<std::string> &res)
622 PGresult *results = execPrepared("load_player_list", 0, NULL, false);
624 int numrows = PQntuples(results);
625 for (int row = 0; row < numrows; row++)
626 res.emplace_back(PQgetvalue(results, row, 0));
631 #endif // USE_POSTGRESQL