src/database-postgresql.cpp

   1 /*
   2 Copyright (C) 2016 Loic Blot <loic.blot@unix-experience.fr>
   3
   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.
   8
   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.
  13
  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.
  17 */
  18
  19 #include "config.h"
  20
  21 #if USE_POSTGRESQL
  22
  23 #include "database-postgresql.h"
  24
  25 #ifdef _WIN32
  26         #ifndef WIN32_LEAN_AND_MEAN
  27                 #define WIN32_LEAN_AND_MEAN
  28         #endif
  29         // Without this some of the network functions are not found on mingw
  30         #ifndef _WIN32_WINNT
  31                 #define _WIN32_WINNT 0x0501
  32         #endif
  33         #include <windows.h>
  34         #include <winsock2.h>
  35 #else
  36 #include <netinet/in.h>
  37 #endif
  38
  39 #include "log.h"
  40 #include "exceptions.h"
  41 #include "settings.h"
  42 #include "content_sao.h"
  43 #include "remoteplayer.h"
  44
  45 Database_PostgreSQL::Database_PostgreSQL(const std::string &connect_string) :
  46         m_connect_string(connect_string)
  47 {
  48         if (m_connect_string.empty()) {
  49                 throw SettingNotFoundException(
  50                         "Set pgsql_connection string in world.mt to "
  51                         "use the postgresql backend\n"
  52                         "Notes:\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!");
  59         }
  60 }
  61
  62 Database_PostgreSQL::~Database_PostgreSQL()
  63 {
  64         PQfinish(m_conn);
  65 }
  66
  67 void Database_PostgreSQL::connectToDatabase()
  68 {
  69         m_conn = PQconnectdb(m_connect_string.c_str());
  70
  71         if (PQstatus(m_conn) != CONNECTION_OK) {
  72                 throw DatabaseException(std::string(
  73                         "PostgreSQL database error: ") +
  74                         PQerrorMessage(m_conn));
  75         }
  76
  77         m_pgversion = PQserverVersion(m_conn);
  78
  79         /*
  80         * We are using UPSERT feature from PostgreSQL 9.5
  81         * to have the better performance where possible.
  82         */
  83         if (m_pgversion < 90500) {
  84                 warningstream << "Your PostgreSQL server lacks UPSERT "
  85                         << "support. Use version 9.5 or better if possible."
  86                         << std::endl;
  87         }
  88
  89         infostream << "PostgreSQL Database: Version " << m_pgversion
  90                         << " Connection made." << std::endl;
  91
  92         createDatabase();
  93         initStatements();
  94 }
  95
  96 void Database_PostgreSQL::verifyDatabase()
  97 {
  98         if (PQstatus(m_conn) == CONNECTION_OK)
  99                 return;
 100
 101         PQreset(m_conn);
 102         ping();
 103 }
 104
 105 void Database_PostgreSQL::ping()
 106 {
 107         if (PQping(m_connect_string.c_str()) != PQPING_OK) {
 108                 throw DatabaseException(std::string(
 109                         "PostgreSQL database error: ") +
 110                         PQerrorMessage(m_conn));
 111         }
 112 }
 113
 114 bool Database_PostgreSQL::initialized() const
 115 {
 116         return (PQstatus(m_conn) == CONNECTION_OK);
 117 }
 118
 119 PGresult *Database_PostgreSQL::checkResults(PGresult *result, bool clear)
 120 {
 121         ExecStatusType statusType = PQresultStatus(result);
 122
 123         switch (statusType) {
 124         case PGRES_COMMAND_OK:
 125         case PGRES_TUPLES_OK:
 126                 break;
 127         case PGRES_FATAL_ERROR:
 128         default:
 129                 throw DatabaseException(
 130                         std::string("PostgreSQL database error: ") +
 131                         PQresultErrorMessage(result));
 132         }
 133
 134         if (clear)
 135                 PQclear(result);
 136
 137         return result;
 138 }
 139
 140 void Database_PostgreSQL::createTableIfNotExists(const std::string &table_name,
 141                 const std::string &definition)
 142 {
 143         std::string sql_check_table = "SELECT relname FROM pg_class WHERE relname='" +
 144                 table_name + "';";
 145         PGresult *result = checkResults(PQexec(m_conn, sql_check_table.c_str()), false);
 146
 147         // If table doesn't exist, create it
 148         if (!PQntuples(result)) {
 149                 checkResults(PQexec(m_conn, definition.c_str()));
 150         }
 151
 152         PQclear(result);
 153 }
 154
 155 void Database_PostgreSQL::beginSave()
 156 {
 157         verifyDatabase();
 158         checkResults(PQexec(m_conn, "BEGIN;"));
 159 }
 160
 161 void Database_PostgreSQL::endSave()
 162 {
 163         checkResults(PQexec(m_conn, "COMMIT;"));
 164 }
 165
 166 MapDatabasePostgreSQL::MapDatabasePostgreSQL(const std::string &connect_string):
 167         Database_PostgreSQL(connect_string),
 168         MapDatabase()
 169 {
 170         connectToDatabase();
 171 }
 172
 173
 174 void MapDatabasePostgreSQL::createDatabase()
 175 {
 176         createTableIfNotExists("blocks",
 177                 "CREATE TABLE blocks ("
 178                         "posX INT NOT NULL,"
 179                         "posY INT NOT NULL,"
 180                         "posZ INT NOT NULL,"
 181                         "data BYTEA,"
 182                         "PRIMARY KEY (posX,posY,posZ)"
 183                         ");"
 184         );
 185
 186         infostream << "PostgreSQL: Map Database was initialized." << std::endl;
 187 }
 188
 189 void MapDatabasePostgreSQL::initStatements()
 190 {
 191         prepareStatement("read_block",
 192                 "SELECT data FROM blocks "
 193                         "WHERE posX = $1::int4 AND posY = $2::int4 AND "
 194                         "posZ = $3::int4");
 195
 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 "
 202                                 "posZ = $3::int4)");
 203
 204                 prepareStatement("write_block_update",
 205                         "UPDATE blocks SET data = $4::bytea "
 206                                 "WHERE posX = $1::int4 AND posY = $2::int4 AND "
 207                                 "posZ = $3::int4");
 208         } else {
 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");
 214         }
 215
 216         prepareStatement("delete_block", "DELETE FROM blocks WHERE "
 217                 "posX = $1::int4 AND posY = $2::int4 AND posZ = $3::int4");
 218
 219         prepareStatement("list_all_loadable_blocks",
 220                 "SELECT posX, posY, posZ FROM blocks");
 221 }
 222
 223 bool MapDatabasePostgreSQL::saveBlock(const v3s16 &pos, const std::string &data)
 224 {
 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()
 229                         << ")" << std::endl;
 230                 return false;
 231         }
 232
 233         verifyDatabase();
 234
 235         s32 x, y, z;
 236         x = htonl(pos.X);
 237         y = htonl(pos.Y);
 238         z = htonl(pos.Z);
 239
 240         const void *args[] = { &x, &y, &z, data.c_str() };
 241         const int argLen[] = {
 242                 sizeof(x), sizeof(y), sizeof(z), (int)data.size()
 243         };
 244         const int argFmt[] = { 1, 1, 1, 1 };
 245
 246         if (getPGVersion() < 90500) {
 247                 execPrepared("write_block_update", ARRLEN(args), args, argLen, argFmt);
 248                 execPrepared("write_block_insert", ARRLEN(args), args, argLen, argFmt);
 249         } else {
 250                 execPrepared("write_block", ARRLEN(args), args, argLen, argFmt);
 251         }
 252         return true;
 253 }
 254
 255 void MapDatabasePostgreSQL::loadBlock(const v3s16 &pos, std::string *block)
 256 {
 257         verifyDatabase();
 258
 259         s32 x, y, z;
 260         x = htonl(pos.X);
 261         y = htonl(pos.Y);
 262         z = htonl(pos.Z);
 263
 264         const void *args[] = { &x, &y, &z };
 265         const int argLen[] = { sizeof(x), sizeof(y), sizeof(z) };
 266         const int argFmt[] = { 1, 1, 1 };
 267
 268         PGresult *results = execPrepared("read_block", ARRLEN(args), args,
 269                 argLen, argFmt, false);
 270
 271         *block = "";
 272
 273         if (PQntuples(results))
 274                 *block = std::string(PQgetvalue(results, 0, 0), PQgetlength(results, 0, 0));
 275
 276         PQclear(results);
 277 }
 278
 279 bool MapDatabasePostgreSQL::deleteBlock(const v3s16 &pos)
 280 {
 281         verifyDatabase();
 282
 283         s32 x, y, z;
 284         x = htonl(pos.X);
 285         y = htonl(pos.Y);
 286         z = htonl(pos.Z);
 287
 288         const void *args[] = { &x, &y, &z };
 289         const int argLen[] = { sizeof(x), sizeof(y), sizeof(z) };
 290         const int argFmt[] = { 1, 1, 1 };
 291
 292         execPrepared("delete_block", ARRLEN(args), args, argLen, argFmt);
 293
 294         return true;
 295 }
 296
 297 void MapDatabasePostgreSQL::listAllLoadableBlocks(std::vector<v3s16> &dst)
 298 {
 299         verifyDatabase();
 300
 301         PGresult *results = execPrepared("list_all_loadable_blocks", 0,
 302                 NULL, NULL, NULL, false, false);
 303
 304         int numrows = PQntuples(results);
 305
 306         for (int row = 0; row < numrows; ++row)
 307                 dst.push_back(pg_to_v3s16(results, 0, 0));
 308
 309         PQclear(results);
 310 }
 311
 312 /*
 313  * Player Database
 314  */
 315 PlayerDatabasePostgreSQL::PlayerDatabasePostgreSQL(const std::string &connect_string):
 316         Database_PostgreSQL(connect_string),
 317         PlayerDatabase()
 318 {
 319         connectToDatabase();
 320 }
 321
 322
 323 void PlayerDatabasePostgreSQL::createDatabase()
 324 {
 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,"
 333                         "hp INT 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(),"
 337                         "PRIMARY KEY (name)"
 338                         ");"
 339         );
 340
 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"
 351                         ");"
 352         );
 353
 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"
 363                         ");"
 364         );
 365
 366         createTableIfNotExists("player_metadata",
 367                 "CREATE TABLE player_metadata ("
 368                         "player VARCHAR(60) NOT NULL,"
 369                         "attr VARCHAR(256) NOT NULL,"
 370                         "value TEXT,"
 371                         "PRIMARY KEY(player, attr),"
 372                         "CONSTRAINT player_metadata_fkey FOREIGN KEY (player) REFERENCES "
 373                         "player (name) ON DELETE CASCADE"
 374                         ");"
 375         );
 376
 377         infostream << "PostgreSQL: Player Database was inited." << std::endl;
 378 }
 379
 380 void PlayerDatabasePostgreSQL::initStatements()
 381 {
 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)");
 386
 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");
 390         } else {
 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()");
 397         }
 398
 399         prepareStatement("remove_player", "DELETE FROM player WHERE name = $1");
 400
 401         prepareStatement("load_player_list", "SELECT name FROM player");
 402
 403         prepareStatement("remove_player_inventories",
 404                 "DELETE FROM player_inventories WHERE player = $1");
 405
 406         prepareStatement("remove_player_inventory_items",
 407                 "DELETE FROM player_inventory_items WHERE player = $1");
 408
 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)");
 412
 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)");
 416
 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");
 420
 421         prepareStatement("load_player_inventory_items",
 422                 "SELECT slot_id, item FROM player_inventory_items WHERE "
 423                         "player = $1 AND inv_id = $2::int");
 424
 425         prepareStatement("load_player",
 426                 "SELECT pitch, yaw, posX, posY, posZ, hp, breath FROM player WHERE name = $1");
 427
 428         prepareStatement("remove_player_metadata",
 429                 "DELETE FROM player_metadata WHERE player = $1");
 430
 431         prepareStatement("save_player_metadata",
 432                 "INSERT INTO player_metadata (player, attr, value) VALUES ($1, $2, $3)");
 433
 434         prepareStatement("load_player_metadata",
 435                 "SELECT attr, value FROM player_metadata WHERE player = $1");
 436
 437 }
 438
 439 bool PlayerDatabasePostgreSQL::playerDataExists(const std::string &playername)
 440 {
 441         verifyDatabase();
 442
 443         const char *values[] = { playername.c_str() };
 444         PGresult *results = execPrepared("load_player", 1, values, false);
 445
 446         bool res = (PQntuples(results) > 0);
 447         PQclear(results);
 448         return res;
 449 }
 450
 451 void PlayerDatabasePostgreSQL::savePlayer(RemotePlayer *player)
 452 {
 453         PlayerSAO* sao = player->getPlayerSAO();
 454         if (!sao)
 455                 return;
 456
 457         verifyDatabase();
 458
 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[] = {
 468                 player->getName(),
 469                 pitch.c_str(),
 470                 yaw.c_str(),
 471                 posx.c_str(), posy.c_str(), posz.c_str(),
 472                 hp.c_str(),
 473                 breath.c_str()
 474         };
 475
 476         const char* rmvalues[] = { player->getName() };
 477         beginSave();
 478
 479         if (getPGVersion() < 90500) {
 480                 if (!playerDataExists(player->getName()))
 481                         execPrepared("create_player", 8, values, true, false);
 482                 else
 483                         execPrepared("update_player", 8, values, true, false);
 484         }
 485         else
 486                 execPrepared("save_player", 8, values, true, false);
 487
 488         // Write player inventories
 489         execPrepared("remove_player_inventories", 1, rmvalues);
 490         execPrepared("remove_player_inventory_items", 1, rmvalues);
 491
 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());
 497
 498                 const char* inv_values[] = {
 499                         player->getName(),
 500                         inv_id.c_str(),
 501                         width.c_str(),
 502                         name.c_str(),
 503                         lsize.c_str()
 504                 };
 505                 execPrepared("add_player_inventory", 5, inv_values);
 506
 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);
 511
 512                         const char* invitem_values[] = {
 513                                 player->getName(),
 514                                 inv_id.c_str(),
 515                                 slotId.c_str(),
 516                                 itemStr.c_str()
 517                         };
 518                         execPrepared("add_player_inventory_item", 4, invitem_values);
 519                 }
 520         }
 521
 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[] = {
 526                         player->getName(),
 527                         it->first.c_str(),
 528                         it->second.c_str()
 529                 };
 530                 execPrepared("save_player_metadata", 3, meta_values);
 531         }
 532         endSave();
 533 }
 534
 535 bool PlayerDatabasePostgreSQL::loadPlayer(RemotePlayer *player, PlayerSAO *sao)
 536 {
 537         sanity_check(sao);
 538         verifyDatabase();
 539
 540         const char *values[] = { player->getName() };
 541         PGresult *results = execPrepared("load_player", 1, values, false, false);
 542
 543         // Player not found, return not found
 544         if (!PQntuples(results)) {
 545                 PQclear(results);
 546                 return false;
 547         }
 548
 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))
 555         );
 556         sao->setHPRaw((s16) pg_to_int(results, 0, 5));
 557         sao->setBreath((u16) pg_to_int(results, 0, 6), false);
 558
 559         PQclear(results);
 560
 561         // Load inventory
 562         results = execPrepared("load_player_inventories", 1, values, false, false);
 563
 564         int resultCount = PQntuples(results);
 565
 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));
 570
 571                 u32 invId = pg_to_uint(results, row, 0);
 572                 std::string invIdStr = itos(invId);
 573
 574                 const char* values2[] = {
 575                         player->getName(),
 576                         invIdStr.c_str()
 577                 };
 578                 PGresult *results2 = execPrepared("load_player_inventory_items", 2,
 579                         values2, false, false);
 580
 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) {
 585                                 ItemStack stack;
 586                                 stack.deSerialize(itemStr);
 587                                 invList->addItem(pg_to_uint(results2, row2, 0), stack);
 588                         }
 589                 }
 590                 PQclear(results2);
 591         }
 592
 593         PQclear(results);
 594
 595         results = execPrepared("load_player_metadata", 1, values, false);
 596
 597         int numrows = PQntuples(results);
 598         for (int row = 0; row < numrows; row++) {
 599                 sao->setExtendedAttribute(PQgetvalue(results, row, 0),PQgetvalue(results, row, 1));
 600         }
 601
 602         PQclear(results);
 603
 604         return true;
 605 }
 606
 607 bool PlayerDatabasePostgreSQL::removePlayer(const std::string &name)
 608 {
 609         if (!playerDataExists(name))
 610                 return false;
 611
 612         verifyDatabase();
 613
 614         const char *values[] = { name.c_str() };
 615         execPrepared("remove_player", 1, values);
 616
 617         return true;
 618 }
 619
 620 void PlayerDatabasePostgreSQL::listPlayers(std::vector<std::string> &res)
 621 {
 622         verifyDatabase();
 623
 624         PGresult *results = execPrepared("load_player_list", 0, NULL, false);
 625
 626         int numrows = PQntuples(results);
 627         for (int row = 0; row < numrows; row++)
 628                 res.push_back(PQgetvalue(results, row, 0));
 629
 630         PQclear(results);
 631 }
 632
 633 #endif // USE_POSTGRESQL