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         // 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 "log.h"
  37 #include "exceptions.h"
  38 #include "settings.h"
  39 #include "content_sao.h"
  40 #include "remoteplayer.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::verifyDatabase()
  94 {
  95         if (PQstatus(m_conn) == CONNECTION_OK)
  96                 return;
  97
  98         PQreset(m_conn);
  99         ping();
 100 }
 101
 102 void Database_PostgreSQL::ping()
 103 {
 104         if (PQping(m_connect_string.c_str()) != PQPING_OK) {
 105                 throw DatabaseException(std::string(
 106                         "PostgreSQL database error: ") +
 107                         PQerrorMessage(m_conn));
 108         }
 109 }
 110
 111 bool Database_PostgreSQL::initialized() const
 112 {
 113         return (PQstatus(m_conn) == CONNECTION_OK);
 114 }
 115
 116 PGresult *Database_PostgreSQL::checkResults(PGresult *result, bool clear)
 117 {
 118         ExecStatusType statusType = PQresultStatus(result);
 119
 120         switch (statusType) {
 121         case PGRES_COMMAND_OK:
 122         case PGRES_TUPLES_OK:
 123                 break;
 124         case PGRES_FATAL_ERROR:
 125         default:
 126                 throw DatabaseException(
 127                         std::string("PostgreSQL database error: ") +
 128                         PQresultErrorMessage(result));
 129         }
 130
 131         if (clear)
 132                 PQclear(result);
 133
 134         return result;
 135 }
 136
 137 void Database_PostgreSQL::createTableIfNotExists(const std::string &table_name,
 138                 const std::string &definition)
 139 {
 140         std::string sql_check_table = "SELECT relname FROM pg_class WHERE relname='" +
 141                 table_name + "';";
 142         PGresult *result = checkResults(PQexec(m_conn, sql_check_table.c_str()), false);
 143
 144         // If table doesn't exist, create it
 145         if (!PQntuples(result)) {
 146                 checkResults(PQexec(m_conn, definition.c_str()));
 147         }
 148
 149         PQclear(result);
 150 }
 151
 152 void Database_PostgreSQL::beginSave()
 153 {
 154         verifyDatabase();
 155         checkResults(PQexec(m_conn, "BEGIN;"));
 156 }
 157
 158 void Database_PostgreSQL::endSave()
 159 {
 160         checkResults(PQexec(m_conn, "COMMIT;"));
 161 }
 162
 163 MapDatabasePostgreSQL::MapDatabasePostgreSQL(const std::string &connect_string):
 164         Database_PostgreSQL(connect_string),
 165         MapDatabase()
 166 {
 167         connectToDatabase();
 168 }
 169
 170
 171 void MapDatabasePostgreSQL::createDatabase()
 172 {
 173         createTableIfNotExists("blocks",
 174                 "CREATE TABLE blocks ("
 175                         "posX INT NOT NULL,"
 176                         "posY INT NOT NULL,"
 177                         "posZ INT NOT NULL,"
 178                         "data BYTEA,"
 179                         "PRIMARY KEY (posX,posY,posZ)"
 180                         ");"
 181         );
 182
 183         infostream << "PostgreSQL: Map Database was initialized." << std::endl;
 184 }
 185
 186 void MapDatabasePostgreSQL::initStatements()
 187 {
 188         prepareStatement("read_block",
 189                 "SELECT data FROM blocks "
 190                         "WHERE posX = $1::int4 AND posY = $2::int4 AND "
 191                         "posZ = $3::int4");
 192
 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 "
 199                                 "posZ = $3::int4)");
 200
 201                 prepareStatement("write_block_update",
 202                         "UPDATE blocks SET data = $4::bytea "
 203                                 "WHERE posX = $1::int4 AND posY = $2::int4 AND "
 204                                 "posZ = $3::int4");
 205         } else {
 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");
 211         }
 212
 213         prepareStatement("delete_block", "DELETE FROM blocks WHERE "
 214                 "posX = $1::int4 AND posY = $2::int4 AND posZ = $3::int4");
 215
 216         prepareStatement("list_all_loadable_blocks",
 217                 "SELECT posX, posY, posZ FROM blocks");
 218 }
 219
 220 bool MapDatabasePostgreSQL::saveBlock(const v3s16 &pos, const std::string &data)
 221 {
 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()
 226                         << ")" << std::endl;
 227                 return false;
 228         }
 229
 230         verifyDatabase();
 231
 232         s32 x, y, z;
 233         x = htonl(pos.X);
 234         y = htonl(pos.Y);
 235         z = htonl(pos.Z);
 236
 237         const void *args[] = { &x, &y, &z, data.c_str() };
 238         const int argLen[] = {
 239                 sizeof(x), sizeof(y), sizeof(z), (int)data.size()
 240         };
 241         const int argFmt[] = { 1, 1, 1, 1 };
 242
 243         if (getPGVersion() < 90500) {
 244                 execPrepared("write_block_update", ARRLEN(args), args, argLen, argFmt);
 245                 execPrepared("write_block_insert", ARRLEN(args), args, argLen, argFmt);
 246         } else {
 247                 execPrepared("write_block", ARRLEN(args), args, argLen, argFmt);
 248         }
 249         return true;
 250 }
 251
 252 void MapDatabasePostgreSQL::loadBlock(const v3s16 &pos, std::string *block)
 253 {
 254         verifyDatabase();
 255
 256         s32 x, y, z;
 257         x = htonl(pos.X);
 258         y = htonl(pos.Y);
 259         z = htonl(pos.Z);
 260
 261         const void *args[] = { &x, &y, &z };
 262         const int argLen[] = { sizeof(x), sizeof(y), sizeof(z) };
 263         const int argFmt[] = { 1, 1, 1 };
 264
 265         PGresult *results = execPrepared("read_block", ARRLEN(args), args,
 266                 argLen, argFmt, false);
 267
 268         *block = "";
 269
 270         if (PQntuples(results))
 271                 *block = std::string(PQgetvalue(results, 0, 0), PQgetlength(results, 0, 0));
 272
 273         PQclear(results);
 274 }
 275
 276 bool MapDatabasePostgreSQL::deleteBlock(const v3s16 &pos)
 277 {
 278         verifyDatabase();
 279
 280         s32 x, y, z;
 281         x = htonl(pos.X);
 282         y = htonl(pos.Y);
 283         z = htonl(pos.Z);
 284
 285         const void *args[] = { &x, &y, &z };
 286         const int argLen[] = { sizeof(x), sizeof(y), sizeof(z) };
 287         const int argFmt[] = { 1, 1, 1 };
 288
 289         execPrepared("delete_block", ARRLEN(args), args, argLen, argFmt);
 290
 291         return true;
 292 }
 293
 294 void MapDatabasePostgreSQL::listAllLoadableBlocks(std::vector<v3s16> &dst)
 295 {
 296         verifyDatabase();
 297
 298         PGresult *results = execPrepared("list_all_loadable_blocks", 0,
 299                 NULL, NULL, NULL, false, false);
 300
 301         int numrows = PQntuples(results);
 302
 303         for (int row = 0; row < numrows; ++row)
 304                 dst.push_back(pg_to_v3s16(results, 0, 0));
 305
 306         PQclear(results);
 307 }
 308
 309 /*
 310  * Player Database
 311  */
 312 PlayerDatabasePostgreSQL::PlayerDatabasePostgreSQL(const std::string &connect_string):
 313         Database_PostgreSQL(connect_string),
 314         PlayerDatabase()
 315 {
 316         connectToDatabase();
 317 }
 318
 319
 320 void PlayerDatabasePostgreSQL::createDatabase()
 321 {
 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,"
 330                         "hp INT 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(),"
 334                         "PRIMARY KEY (name)"
 335                         ");"
 336         );
 337
 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"
 348                         ");"
 349         );
 350
 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"
 360                         ");"
 361         );
 362
 363         createTableIfNotExists("player_metadata",
 364                 "CREATE TABLE player_metadata ("
 365                         "player VARCHAR(60) NOT NULL,"
 366                         "attr VARCHAR(256) NOT NULL,"
 367                         "value TEXT,"
 368                         "PRIMARY KEY(player, attr),"
 369                         "CONSTRAINT player_metadata_fkey FOREIGN KEY (player) REFERENCES "
 370                         "player (name) ON DELETE CASCADE"
 371                         ");"
 372         );
 373
 374         infostream << "PostgreSQL: Player Database was inited." << std::endl;
 375 }
 376
 377 void PlayerDatabasePostgreSQL::initStatements()
 378 {
 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)");
 383
 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");
 387         } else {
 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()");
 394         }
 395
 396         prepareStatement("remove_player", "DELETE FROM player WHERE name = $1");
 397
 398         prepareStatement("load_player_list", "SELECT name FROM player");
 399
 400         prepareStatement("remove_player_inventories",
 401                 "DELETE FROM player_inventories WHERE player = $1");
 402
 403         prepareStatement("remove_player_inventory_items",
 404                 "DELETE FROM player_inventory_items WHERE player = $1");
 405
 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)");
 409
 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)");
 413
 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");
 417
 418         prepareStatement("load_player_inventory_items",
 419                 "SELECT slot_id, item FROM player_inventory_items WHERE "
 420                         "player = $1 AND inv_id = $2::int");
 421
 422         prepareStatement("load_player",
 423                 "SELECT pitch, yaw, posX, posY, posZ, hp, breath FROM player WHERE name = $1");
 424
 425         prepareStatement("remove_player_metadata",
 426                 "DELETE FROM player_metadata WHERE player = $1");
 427
 428         prepareStatement("save_player_metadata",
 429                 "INSERT INTO player_metadata (player, attr, value) VALUES ($1, $2, $3)");
 430
 431         prepareStatement("load_player_metadata",
 432                 "SELECT attr, value FROM player_metadata WHERE player = $1");
 433
 434 }
 435
 436 bool PlayerDatabasePostgreSQL::playerDataExists(const std::string &playername)
 437 {
 438         verifyDatabase();
 439
 440         const char *values[] = { playername.c_str() };
 441         PGresult *results = execPrepared("load_player", 1, values, false);
 442
 443         bool res = (PQntuples(results) > 0);
 444         PQclear(results);
 445         return res;
 446 }
 447
 448 void PlayerDatabasePostgreSQL::savePlayer(RemotePlayer *player)
 449 {
 450         PlayerSAO* sao = player->getPlayerSAO();
 451         if (!sao)
 452                 return;
 453
 454         verifyDatabase();
 455
 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[] = {
 465                 player->getName(),
 466                 pitch.c_str(),
 467                 yaw.c_str(),
 468                 posx.c_str(), posy.c_str(), posz.c_str(),
 469                 hp.c_str(),
 470                 breath.c_str()
 471         };
 472
 473         const char* rmvalues[] = { player->getName() };
 474         beginSave();
 475
 476         if (getPGVersion() < 90500) {
 477                 if (!playerDataExists(player->getName()))
 478                         execPrepared("create_player", 8, values, true, false);
 479                 else
 480                         execPrepared("update_player", 8, values, true, false);
 481         }
 482         else
 483                 execPrepared("save_player", 8, values, true, false);
 484
 485         // Write player inventories
 486         execPrepared("remove_player_inventories", 1, rmvalues);
 487         execPrepared("remove_player_inventory_items", 1, rmvalues);
 488
 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());
 494
 495                 const char* inv_values[] = {
 496                         player->getName(),
 497                         inv_id.c_str(),
 498                         width.c_str(),
 499                         name.c_str(),
 500                         lsize.c_str()
 501                 };
 502                 execPrepared("add_player_inventory", 5, inv_values);
 503
 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);
 508
 509                         const char* invitem_values[] = {
 510                                 player->getName(),
 511                                 inv_id.c_str(),
 512                                 slotId.c_str(),
 513                                 itemStr.c_str()
 514                         };
 515                         execPrepared("add_player_inventory_item", 4, invitem_values);
 516                 }
 517         }
 518
 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[] = {
 523                         player->getName(),
 524                         it->first.c_str(),
 525                         it->second.c_str()
 526                 };
 527                 execPrepared("save_player_metadata", 3, meta_values);
 528         }
 529         endSave();
 530 }
 531
 532 bool PlayerDatabasePostgreSQL::loadPlayer(RemotePlayer *player, PlayerSAO *sao)
 533 {
 534         sanity_check(sao);
 535         verifyDatabase();
 536
 537         const char *values[] = { player->getName() };
 538         PGresult *results = execPrepared("load_player", 1, values, false, false);
 539
 540         // Player not found, return not found
 541         if (!PQntuples(results)) {
 542                 PQclear(results);
 543                 return false;
 544         }
 545
 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))
 552         );
 553         sao->setHPRaw((s16) pg_to_int(results, 0, 5));
 554         sao->setBreath((u16) pg_to_int(results, 0, 6), false);
 555
 556         PQclear(results);
 557
 558         // Load inventory
 559         results = execPrepared("load_player_inventories", 1, values, false, false);
 560
 561         int resultCount = PQntuples(results);
 562
 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));
 567
 568                 u32 invId = pg_to_uint(results, row, 0);
 569                 std::string invIdStr = itos(invId);
 570
 571                 const char* values2[] = {
 572                         player->getName(),
 573                         invIdStr.c_str()
 574                 };
 575                 PGresult *results2 = execPrepared("load_player_inventory_items", 2,
 576                         values2, false, false);
 577
 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) {
 582                                 ItemStack stack;
 583                                 stack.deSerialize(itemStr);
 584                                 invList->changeItem(pg_to_uint(results2, row2, 0), stack);
 585                         }
 586                 }
 587                 PQclear(results2);
 588         }
 589
 590         PQclear(results);
 591
 592         results = execPrepared("load_player_metadata", 1, values, false);
 593
 594         int numrows = PQntuples(results);
 595         for (int row = 0; row < numrows; row++) {
 596                 sao->setExtendedAttribute(PQgetvalue(results, row, 0),PQgetvalue(results, row, 1));
 597         }
 598
 599         PQclear(results);
 600
 601         return true;
 602 }
 603
 604 bool PlayerDatabasePostgreSQL::removePlayer(const std::string &name)
 605 {
 606         if (!playerDataExists(name))
 607                 return false;
 608
 609         verifyDatabase();
 610
 611         const char *values[] = { name.c_str() };
 612         execPrepared("remove_player", 1, values);
 613
 614         return true;
 615 }
 616
 617 void PlayerDatabasePostgreSQL::listPlayers(std::vector<std::string> &res)
 618 {
 619         verifyDatabase();
 620
 621         PGresult *results = execPrepared("load_player_list", 0, NULL, false);
 622
 623         int numrows = PQntuples(results);
 624         for (int row = 0; row < numrows; row++)
 625                 res.push_back(PQgetvalue(results, row, 0));
 626
 627         PQclear(results);
 628 }
 629
 630 #endif // USE_POSTGRESQL