src/database/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         // Without this some of the network functions are not found on mingw
  27         #ifndef _WIN32_WINNT
  28                 #define _WIN32_WINNT 0x0501
  29         #endif
  30         #include <windows.h>
  31         #include <winsock2.h>
  32 #else
  33 #include <netinet/in.h>
  34 #endif
  35
  36 #include "debug.h"
  37 #include "exceptions.h"
  38 #include "settings.h"
  39 #include "remoteplayer.h"
  40 #include "server/player_sao.h"
  41
  42 Database_PostgreSQL::Database_PostgreSQL(const std::string &connect_string) :
  43         m_connect_string(connect_string)
  44 {
  45         if (m_connect_string.empty()) {
  46                 throw SettingNotFoundException(
  47                         "Set pgsql_connection string in world.mt to "
  48                         "use the postgresql backend\n"
  49                         "Notes:\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!");
  56         }
  57 }
  58
  59 Database_PostgreSQL::~Database_PostgreSQL()
  60 {
  61         PQfinish(m_conn);
  62 }
  63
  64 void Database_PostgreSQL::connectToDatabase()
  65 {
  66         m_conn = PQconnectdb(m_connect_string.c_str());
  67
  68         if (PQstatus(m_conn) != CONNECTION_OK) {
  69                 throw DatabaseException(std::string(
  70                         "PostgreSQL database error: ") +
  71                         PQerrorMessage(m_conn));
  72         }
  73
  74         m_pgversion = PQserverVersion(m_conn);
  75
  76         /*
  77         * We are using UPSERT feature from PostgreSQL 9.5
  78         * to have the better performance where possible.
  79         */
  80         if (m_pgversion < 90500) {
  81                 warningstream << "Your PostgreSQL server lacks UPSERT "
  82                         << "support. Use version 9.5 or better if possible."
  83                         << std::endl;
  84         }
  85
  86         infostream << "PostgreSQL Database: Version " << m_pgversion
  87                         << " Connection made." << std::endl;
  88
  89         createDatabase();
  90         initStatements();
  91 }
  92
  93 void Database_PostgreSQL::pingDatabase()
  94 {
  95         // Verify DB connection with ping
  96         try {
  97                 ping();
  98         } catch (const DatabaseException &e) {
  99                 // If ping failed, show the error and try reconnect
 100                 PQreset(m_conn);
 101
 102                 errorstream << e.what() << std::endl
 103                         << "Reconnecting to database " << m_connect_string << std::endl;
 104                 connectToDatabase();
 105         }
 106 }
 107
 108 void Database_PostgreSQL::ping()
 109 {
 110         if (PQping(m_connect_string.c_str()) != PQPING_OK) {
 111                 throw DatabaseException(std::string(
 112                         "PostgreSQL database error: ") +
 113                         PQerrorMessage(m_conn));
 114         }
 115 }
 116
 117 bool Database_PostgreSQL::initialized() const
 118 {
 119         return (PQstatus(m_conn) == CONNECTION_OK);
 120 }
 121
 122 PGresult *Database_PostgreSQL::checkResults(PGresult *result, bool clear)
 123 {
 124         ExecStatusType statusType = PQresultStatus(result);
 125
 126         switch (statusType) {
 127         case PGRES_COMMAND_OK:
 128         case PGRES_TUPLES_OK:
 129                 break;
 130         case PGRES_FATAL_ERROR:
 131         default:
 132                 throw DatabaseException(
 133                         std::string("PostgreSQL database error: ") +
 134                         PQresultErrorMessage(result));
 135         }
 136
 137         if (clear)
 138                 PQclear(result);
 139
 140         return result;
 141 }
 142
 143 void Database_PostgreSQL::createTableIfNotExists(const std::string &table_name,
 144                 const std::string &definition)
 145 {
 146         std::string sql_check_table = "SELECT relname FROM pg_class WHERE relname='" +
 147                 table_name + "';";
 148         PGresult *result = checkResults(PQexec(m_conn, sql_check_table.c_str()), false);
 149
 150         // If table doesn't exist, create it
 151         if (!PQntuples(result)) {
 152                 checkResults(PQexec(m_conn, definition.c_str()));
 153         }
 154
 155         PQclear(result);
 156 }
 157
 158 void Database_PostgreSQL::beginSave()
 159 {
 160         pingDatabase();
 161         checkResults(PQexec(m_conn, "BEGIN;"));
 162 }
 163
 164 void Database_PostgreSQL::endSave()
 165 {
 166         checkResults(PQexec(m_conn, "COMMIT;"));
 167 }
 168
 169 MapDatabasePostgreSQL::MapDatabasePostgreSQL(const std::string &connect_string):
 170         Database_PostgreSQL(connect_string),
 171         MapDatabase()
 172 {
 173         connectToDatabase();
 174 }
 175
 176
 177 void MapDatabasePostgreSQL::createDatabase()
 178 {
 179         createTableIfNotExists("blocks",
 180                 "CREATE TABLE blocks ("
 181                         "posX INT NOT NULL,"
 182                         "posY INT NOT NULL,"
 183                         "posZ INT NOT NULL,"
 184                         "data BYTEA,"
 185                         "PRIMARY KEY (posX,posY,posZ)"
 186                         ");"
 187         );
 188
 189         infostream << "PostgreSQL: Map Database was initialized." << std::endl;
 190 }
 191
 192 void MapDatabasePostgreSQL::initStatements()
 193 {
 194         prepareStatement("read_block",
 195                 "SELECT data FROM blocks "
 196                         "WHERE posX = $1::int4 AND posY = $2::int4 AND "
 197                         "posZ = $3::int4");
 198
 199         if (getPGVersion() < 90500) {
 200                 prepareStatement("write_block_insert",
 201                         "INSERT INTO blocks (posX, posY, posZ, data) SELECT "
 202                                 "$1::int4, $2::int4, $3::int4, $4::bytea "
 203                                 "WHERE NOT EXISTS (SELECT true FROM blocks "
 204                                 "WHERE posX = $1::int4 AND posY = $2::int4 AND "
 205                                 "posZ = $3::int4)");
 206
 207                 prepareStatement("write_block_update",
 208                         "UPDATE blocks SET data = $4::bytea "
 209                                 "WHERE posX = $1::int4 AND posY = $2::int4 AND "
 210                                 "posZ = $3::int4");
 211         } else {
 212                 prepareStatement("write_block",
 213                         "INSERT INTO blocks (posX, posY, posZ, data) VALUES "
 214                                 "($1::int4, $2::int4, $3::int4, $4::bytea) "
 215                                 "ON CONFLICT ON CONSTRAINT blocks_pkey DO "
 216                                 "UPDATE SET data = $4::bytea");
 217         }
 218
 219         prepareStatement("delete_block", "DELETE FROM blocks WHERE "
 220                 "posX = $1::int4 AND posY = $2::int4 AND posZ = $3::int4");
 221
 222         prepareStatement("list_all_loadable_blocks",
 223                 "SELECT posX, posY, posZ FROM blocks");
 224 }
 225
 226 bool MapDatabasePostgreSQL::saveBlock(const v3s16 &pos, const std::string &data)
 227 {
 228         // Verify if we don't overflow the platform integer with the mapblock size
 229         if (data.size() > INT_MAX) {
 230                 errorstream << "Database_PostgreSQL::saveBlock: Data truncation! "
 231                         << "data.size() over 0xFFFFFFFF (== " << data.size()
 232                         << ")" << std::endl;
 233                 return false;
 234         }
 235
 236         pingDatabase();
 237
 238         s32 x, y, z;
 239         x = htonl(pos.X);
 240         y = htonl(pos.Y);
 241         z = htonl(pos.Z);
 242
 243         const void *args[] = { &x, &y, &z, data.c_str() };
 244         const int argLen[] = {
 245                 sizeof(x), sizeof(y), sizeof(z), (int)data.size()
 246         };
 247         const int argFmt[] = { 1, 1, 1, 1 };
 248
 249         if (getPGVersion() < 90500) {
 250                 execPrepared("write_block_update", ARRLEN(args), args, argLen, argFmt);
 251                 execPrepared("write_block_insert", ARRLEN(args), args, argLen, argFmt);
 252         } else {
 253                 execPrepared("write_block", ARRLEN(args), args, argLen, argFmt);
 254         }
 255         return true;
 256 }
 257
 258 void MapDatabasePostgreSQL::loadBlock(const v3s16 &pos, std::string *block)
 259 {
 260         pingDatabase();
 261
 262         s32 x, y, z;
 263         x = htonl(pos.X);
 264         y = htonl(pos.Y);
 265         z = htonl(pos.Z);
 266
 267         const void *args[] = { &x, &y, &z };
 268         const int argLen[] = { sizeof(x), sizeof(y), sizeof(z) };
 269         const int argFmt[] = { 1, 1, 1 };
 270
 271         PGresult *results = execPrepared("read_block", ARRLEN(args), args,
 272                 argLen, argFmt, false);
 273
 274         *block = "";
 275
 276         if (PQntuples(results))
 277                 *block = std::string(PQgetvalue(results, 0, 0), PQgetlength(results, 0, 0));
 278
 279         PQclear(results);
 280 }
 281
 282 bool MapDatabasePostgreSQL::deleteBlock(const v3s16 &pos)
 283 {
 284         pingDatabase();
 285
 286         s32 x, y, z;
 287         x = htonl(pos.X);
 288         y = htonl(pos.Y);
 289         z = htonl(pos.Z);
 290
 291         const void *args[] = { &x, &y, &z };
 292         const int argLen[] = { sizeof(x), sizeof(y), sizeof(z) };
 293         const int argFmt[] = { 1, 1, 1 };
 294
 295         execPrepared("delete_block", ARRLEN(args), args, argLen, argFmt);
 296
 297         return true;
 298 }
 299
 300 void MapDatabasePostgreSQL::listAllLoadableBlocks(std::vector<v3s16> &dst)
 301 {
 302         pingDatabase();
 303
 304         PGresult *results = execPrepared("list_all_loadable_blocks", 0,
 305                 NULL, NULL, NULL, false, false);
 306
 307         int numrows = PQntuples(results);
 308
 309         for (int row = 0; row < numrows; ++row)
 310                 dst.push_back(pg_to_v3s16(results, row, 0));
 311
 312         PQclear(results);
 313 }
 314
 315 /*
 316  * Player Database
 317  */
 318 PlayerDatabasePostgreSQL::PlayerDatabasePostgreSQL(const std::string &connect_string):
 319         Database_PostgreSQL(connect_string),
 320         PlayerDatabase()
 321 {
 322         connectToDatabase();
 323 }
 324
 325
 326 void PlayerDatabasePostgreSQL::createDatabase()
 327 {
 328         createTableIfNotExists("player",
 329                 "CREATE TABLE player ("
 330                         "name VARCHAR(60) NOT NULL,"
 331                         "pitch NUMERIC(15, 7) NOT NULL,"
 332                         "yaw NUMERIC(15, 7) NOT NULL,"
 333                         "posX NUMERIC(15, 7) NOT NULL,"
 334                         "posY NUMERIC(15, 7) NOT NULL,"
 335                         "posZ NUMERIC(15, 7) NOT NULL,"
 336                         "hp INT NOT NULL,"
 337                         "breath INT NOT NULL,"
 338                         "creation_date TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT NOW(),"
 339                         "modification_date TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT NOW(),"
 340                         "PRIMARY KEY (name)"
 341                         ");"
 342         );
 343
 344         createTableIfNotExists("player_inventories",
 345                 "CREATE TABLE player_inventories ("
 346                         "player VARCHAR(60) NOT NULL,"
 347                         "inv_id INT NOT NULL,"
 348                         "inv_width INT NOT NULL,"
 349                         "inv_name TEXT NOT NULL DEFAULT '',"
 350                         "inv_size INT NOT NULL,"
 351                         "PRIMARY KEY(player, inv_id),"
 352                         "CONSTRAINT player_inventories_fkey FOREIGN KEY (player) REFERENCES "
 353                         "player (name) ON DELETE CASCADE"
 354                         ");"
 355         );
 356
 357         createTableIfNotExists("player_inventory_items",
 358                 "CREATE TABLE player_inventory_items ("
 359                         "player VARCHAR(60) NOT NULL,"
 360                         "inv_id INT NOT NULL,"
 361                         "slot_id INT NOT NULL,"
 362                         "item TEXT NOT NULL DEFAULT '',"
 363                         "PRIMARY KEY(player, inv_id, slot_id),"
 364                         "CONSTRAINT player_inventory_items_fkey FOREIGN KEY (player) REFERENCES "
 365                         "player (name) ON DELETE CASCADE"
 366                         ");"
 367         );
 368
 369         createTableIfNotExists("player_metadata",
 370                 "CREATE TABLE player_metadata ("
 371                         "player VARCHAR(60) NOT NULL,"
 372                         "attr VARCHAR(256) NOT NULL,"
 373                         "value TEXT,"
 374                         "PRIMARY KEY(player, attr),"
 375                         "CONSTRAINT player_metadata_fkey FOREIGN KEY (player) REFERENCES "
 376                         "player (name) ON DELETE CASCADE"
 377                         ");"
 378         );
 379
 380         infostream << "PostgreSQL: Player Database was inited." << std::endl;
 381 }
 382
 383 void PlayerDatabasePostgreSQL::initStatements()
 384 {
 385         if (getPGVersion() < 90500) {
 386                 prepareStatement("create_player",
 387                         "INSERT INTO player(name, pitch, yaw, posX, posY, posZ, hp, breath) VALUES "
 388                                 "($1, $2, $3, $4, $5, $6, $7::int, $8::int)");
 389
 390                 prepareStatement("update_player",
 391                         "UPDATE SET pitch = $2, yaw = $3, posX = $4, posY = $5, posZ = $6, hp = $7::int, "
 392                                 "breath = $8::int, modification_date = NOW() WHERE name = $1");
 393         } else {
 394                 prepareStatement("save_player",
 395                         "INSERT INTO player(name, pitch, yaw, posX, posY, posZ, hp, breath) VALUES "
 396                                 "($1, $2, $3, $4, $5, $6, $7::int, $8::int)"
 397                                 "ON CONFLICT ON CONSTRAINT player_pkey DO UPDATE SET pitch = $2, yaw = $3, "
 398                                 "posX = $4, posY = $5, posZ = $6, hp = $7::int, breath = $8::int, "
 399                                 "modification_date = NOW()");
 400         }
 401
 402         prepareStatement("remove_player", "DELETE FROM player WHERE name = $1");
 403
 404         prepareStatement("load_player_list", "SELECT name FROM player");
 405
 406         prepareStatement("remove_player_inventories",
 407                 "DELETE FROM player_inventories WHERE player = $1");
 408
 409         prepareStatement("remove_player_inventory_items",
 410                 "DELETE FROM player_inventory_items WHERE player = $1");
 411
 412         prepareStatement("add_player_inventory",
 413                 "INSERT INTO player_inventories (player, inv_id, inv_width, inv_name, inv_size) VALUES "
 414                         "($1, $2::int, $3::int, $4, $5::int)");
 415
 416         prepareStatement("add_player_inventory_item",
 417                 "INSERT INTO player_inventory_items (player, inv_id, slot_id, item) VALUES "
 418                         "($1, $2::int, $3::int, $4)");
 419
 420         prepareStatement("load_player_inventories",
 421                 "SELECT inv_id, inv_width, inv_name, inv_size FROM player_inventories "
 422                         "WHERE player = $1 ORDER BY inv_id");
 423
 424         prepareStatement("load_player_inventory_items",
 425                 "SELECT slot_id, item FROM player_inventory_items WHERE "
 426                         "player = $1 AND inv_id = $2::int");
 427
 428         prepareStatement("load_player",
 429                 "SELECT pitch, yaw, posX, posY, posZ, hp, breath FROM player WHERE name = $1");
 430
 431         prepareStatement("remove_player_metadata",
 432                 "DELETE FROM player_metadata WHERE player = $1");
 433
 434         prepareStatement("save_player_metadata",
 435                 "INSERT INTO player_metadata (player, attr, value) VALUES ($1, $2, $3)");
 436
 437         prepareStatement("load_player_metadata",
 438                 "SELECT attr, value FROM player_metadata WHERE player = $1");
 439
 440 }
 441
 442 bool PlayerDatabasePostgreSQL::playerDataExists(const std::string &playername)
 443 {
 444         pingDatabase();
 445
 446         const char *values[] = { playername.c_str() };
 447         PGresult *results = execPrepared("load_player", 1, values, false);
 448
 449         bool res = (PQntuples(results) > 0);
 450         PQclear(results);
 451         return res;
 452 }
 453
 454 void PlayerDatabasePostgreSQL::savePlayer(RemotePlayer *player)
 455 {
 456         PlayerSAO* sao = player->getPlayerSAO();
 457         if (!sao)
 458                 return;
 459
 460         pingDatabase();
 461
 462         v3f pos = sao->getBasePosition();
 463         std::string pitch = ftos(sao->getLookPitch());
 464         std::string yaw = ftos(sao->getRotation().Y);
 465         std::string posx = ftos(pos.X);
 466         std::string posy = ftos(pos.Y);
 467         std::string posz = ftos(pos.Z);
 468         std::string hp = itos(sao->getHP());
 469         std::string breath = itos(sao->getBreath());
 470         const char *values[] = {
 471                 player->getName(),
 472                 pitch.c_str(),
 473                 yaw.c_str(),
 474                 posx.c_str(), posy.c_str(), posz.c_str(),
 475                 hp.c_str(),
 476                 breath.c_str()
 477         };
 478
 479         const char* rmvalues[] = { player->getName() };
 480         beginSave();
 481
 482         if (getPGVersion() < 90500) {
 483                 if (!playerDataExists(player->getName()))
 484                         execPrepared("create_player", 8, values, true, false);
 485                 else
 486                         execPrepared("update_player", 8, values, true, false);
 487         }
 488         else
 489                 execPrepared("save_player", 8, values, true, false);
 490
 491         // Write player inventories
 492         execPrepared("remove_player_inventories", 1, rmvalues);
 493         execPrepared("remove_player_inventory_items", 1, rmvalues);
 494
 495         std::vector<const InventoryList*> inventory_lists = sao->getInventory()->getLists();
 496         for (u16 i = 0; i < inventory_lists.size(); i++) {
 497                 const InventoryList* list = inventory_lists[i];
 498                 const std::string &name = list->getName();
 499                 std::string width = itos(list->getWidth()),
 500                         inv_id = itos(i), lsize = itos(list->getSize());
 501
 502                 const char* inv_values[] = {
 503                         player->getName(),
 504                         inv_id.c_str(),
 505                         width.c_str(),
 506                         name.c_str(),
 507                         lsize.c_str()
 508                 };
 509                 execPrepared("add_player_inventory", 5, inv_values);
 510
 511                 for (u32 j = 0; j < list->getSize(); j++) {
 512                         std::ostringstream os;
 513                         list->getItem(j).serialize(os);
 514                         std::string itemStr = os.str(), slotId = itos(j);
 515
 516                         const char* invitem_values[] = {
 517                                 player->getName(),
 518                                 inv_id.c_str(),
 519                                 slotId.c_str(),
 520                                 itemStr.c_str()
 521                         };
 522                         execPrepared("add_player_inventory_item", 4, invitem_values);
 523                 }
 524         }
 525
 526         execPrepared("remove_player_metadata", 1, rmvalues);
 527         const StringMap &attrs = sao->getMeta().getStrings();
 528         for (const auto &attr : attrs) {
 529                 const char *meta_values[] = {
 530                         player->getName(),
 531                         attr.first.c_str(),
 532                         attr.second.c_str()
 533                 };
 534                 execPrepared("save_player_metadata", 3, meta_values);
 535         }
 536         endSave();
 537
 538         player->onSuccessfulSave();
 539 }
 540
 541 bool PlayerDatabasePostgreSQL::loadPlayer(RemotePlayer *player, PlayerSAO *sao)
 542 {
 543         sanity_check(sao);
 544         pingDatabase();
 545
 546         const char *values[] = { player->getName() };
 547         PGresult *results = execPrepared("load_player", 1, values, false, false);
 548
 549         // Player not found, return not found
 550         if (!PQntuples(results)) {
 551                 PQclear(results);
 552                 return false;
 553         }
 554
 555         sao->setLookPitch(pg_to_float(results, 0, 0));
 556         sao->setRotation(v3f(0, pg_to_float(results, 0, 1), 0));
 557         sao->setBasePosition(v3f(
 558                 pg_to_float(results, 0, 2),
 559                 pg_to_float(results, 0, 3),
 560                 pg_to_float(results, 0, 4))
 561         );
 562         sao->setHPRaw((u16) pg_to_int(results, 0, 5));
 563         sao->setBreath((u16) pg_to_int(results, 0, 6), false);
 564
 565         PQclear(results);
 566
 567         // Load inventory
 568         results = execPrepared("load_player_inventories", 1, values, false, false);
 569
 570         int resultCount = PQntuples(results);
 571
 572         for (int row = 0; row < resultCount; ++row) {
 573                 InventoryList* invList = player->inventory.
 574                         addList(PQgetvalue(results, row, 2), pg_to_uint(results, row, 3));
 575                 invList->setWidth(pg_to_uint(results, row, 1));
 576
 577                 u32 invId = pg_to_uint(results, row, 0);
 578                 std::string invIdStr = itos(invId);
 579
 580                 const char* values2[] = {
 581                         player->getName(),
 582                         invIdStr.c_str()
 583                 };
 584                 PGresult *results2 = execPrepared("load_player_inventory_items", 2,
 585                         values2, false, false);
 586
 587                 int resultCount2 = PQntuples(results2);
 588                 for (int row2 = 0; row2 < resultCount2; row2++) {
 589                         const std::string itemStr = PQgetvalue(results2, row2, 1);
 590                         if (itemStr.length() > 0) {
 591                                 ItemStack stack;
 592                                 stack.deSerialize(itemStr);
 593                                 invList->changeItem(pg_to_uint(results2, row2, 0), stack);
 594                         }
 595                 }
 596                 PQclear(results2);
 597         }
 598
 599         PQclear(results);
 600
 601         results = execPrepared("load_player_metadata", 1, values, false);
 602
 603         int numrows = PQntuples(results);
 604         for (int row = 0; row < numrows; row++) {
 605                 sao->getMeta().setString(PQgetvalue(results, row, 0), PQgetvalue(results, row, 1));
 606         }
 607         sao->getMeta().setModified(false);
 608
 609         PQclear(results);
 610
 611         return true;
 612 }
 613
 614 bool PlayerDatabasePostgreSQL::removePlayer(const std::string &name)
 615 {
 616         if (!playerDataExists(name))
 617                 return false;
 618
 619         pingDatabase();
 620
 621         const char *values[] = { name.c_str() };
 622         execPrepared("remove_player", 1, values);
 623
 624         return true;
 625 }
 626
 627 void PlayerDatabasePostgreSQL::listPlayers(std::vector<std::string> &res)
 628 {
 629         pingDatabase();
 630
 631         PGresult *results = execPrepared("load_player_list", 0, NULL, false);
 632
 633         int numrows = PQntuples(results);
 634         for (int row = 0; row < numrows; row++)
 635                 res.emplace_back(PQgetvalue(results, row, 0));
 636
 637         PQclear(results);
 638 }
 639
 640 #endif // USE_POSTGRESQL