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 std::string name = list->getName(), width = itos(list->getWidth()),
493 inv_id = itos(i), lsize = itos(list->getSize());
495 const char* inv_values[] = {
502 execPrepared("add_player_inventory", 5, inv_values);
504 for (u32 j = 0; j < list->getSize(); j++) {
505 std::ostringstream os;
506 list->getItem(j).serialize(os);
507 std::string itemStr = os.str(), slotId = itos(j);
509 const char* invitem_values[] = {
515 execPrepared("add_player_inventory_item", 4, invitem_values);
519 execPrepared("remove_player_metadata", 1, rmvalues);
520 const PlayerAttributes &attrs = sao->getExtendedAttributes();
521 for (PlayerAttributes::const_iterator it = attrs.begin(); it != attrs.end(); ++it) {
522 const char *meta_values[] = {
527 execPrepared("save_player_metadata", 3, meta_values);
532 bool PlayerDatabasePostgreSQL::loadPlayer(RemotePlayer *player, PlayerSAO *sao)
537 const char *values[] = { player->getName() };
538 PGresult *results = execPrepared("load_player", 1, values, false, false);
540 // Player not found, return not found
541 if (!PQntuples(results)) {
546 sao->setPitch(pg_to_float(results, 0, 0));
547 sao->setYaw(pg_to_float(results, 0, 1));
548 sao->setBasePosition(v3f(
549 pg_to_float(results, 0, 2),
550 pg_to_float(results, 0, 3),
551 pg_to_float(results, 0, 4))
553 sao->setHPRaw((s16) pg_to_int(results, 0, 5));
554 sao->setBreath((u16) pg_to_int(results, 0, 6), false);
559 results = execPrepared("load_player_inventories", 1, values, false, false);
561 int resultCount = PQntuples(results);
563 for (int row = 0; row < resultCount; ++row) {
564 InventoryList* invList = player->inventory.
565 addList(PQgetvalue(results, row, 2), pg_to_uint(results, row, 3));
566 invList->setWidth(pg_to_uint(results, row, 1));
568 u32 invId = pg_to_uint(results, row, 0);
569 std::string invIdStr = itos(invId);
571 const char* values2[] = {
575 PGresult *results2 = execPrepared("load_player_inventory_items", 2,
576 values2, false, false);
578 int resultCount2 = PQntuples(results2);
579 for (int row2 = 0; row2 < resultCount2; row2++) {
580 const std::string itemStr = PQgetvalue(results2, row2, 1);
581 if (itemStr.length() > 0) {
583 stack.deSerialize(itemStr);
584 invList->changeItem(pg_to_uint(results2, row2, 0), stack);
592 results = execPrepared("load_player_metadata", 1, values, false);
594 int numrows = PQntuples(results);
595 for (int row = 0; row < numrows; row++) {
596 sao->setExtendedAttribute(PQgetvalue(results, row, 0),PQgetvalue(results, row, 1));
604 bool PlayerDatabasePostgreSQL::removePlayer(const std::string &name)
606 if (!playerDataExists(name))
611 const char *values[] = { name.c_str() };
612 execPrepared("remove_player", 1, values);
617 void PlayerDatabasePostgreSQL::listPlayers(std::vector<std::string> &res)
621 PGresult *results = execPrepared("load_player_list", 0, NULL, false);
623 int numrows = PQntuples(results);
624 for (int row = 0; row < numrows; row++)
625 res.push_back(PQgetvalue(results, row, 0));
630 #endif // USE_POSTGRESQL