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