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 #include <cstdlib>
  42
  43 Database_PostgreSQL::Database_PostgreSQL(const std::string &connect_string,
  44         const char *type) :
  45         m_connect_string(connect_string)
  46 {
  47         if (m_connect_string.empty()) {
  48                 // Use given type to reference the exact setting in the error message
  49                 std::string s = type;
  50                 std::string msg =
  51                         "Set pgsql" + s + "_connection string in world.mt to "
  52                         "use the postgresql backend\n"
  53                         "Notes:\n"
  54                         "pgsql" + s + "_connection has the following form: \n"
  55                         "\tpgsql" + s + "_connection = host=127.0.0.1 port=5432 "
  56                         "user=mt_user password=mt_password dbname=minetest" + s + "\n"
  57                         "mt_user should have CREATE TABLE, INSERT, SELECT, UPDATE and "
  58                         "DELETE rights on the database. "
  59                         "Don't create mt_user as a SUPERUSER!";
  60                 throw SettingNotFoundException(msg);
  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 void Database_PostgreSQL::rollback()
 169 {
 170         checkResults(PQexec(m_conn, "ROLLBACK;"));
 171 }
 172
 173 MapDatabasePostgreSQL::MapDatabasePostgreSQL(const std::string &connect_string):
 174         Database_PostgreSQL(connect_string, ""),
 175         MapDatabase()
 176 {
 177         connectToDatabase();
 178 }
 179
 180
 181 void MapDatabasePostgreSQL::createDatabase()
 182 {
 183         createTableIfNotExists("blocks",
 184                 "CREATE TABLE blocks ("
 185                         "posX INT NOT NULL,"
 186                         "posY INT NOT NULL,"
 187                         "posZ INT NOT NULL,"
 188                         "data BYTEA,"
 189                         "PRIMARY KEY (posX,posY,posZ)"
 190                         ");"
 191         );
 192
 193         infostream << "PostgreSQL: Map Database was initialized." << std::endl;
 194 }
 195
 196 void MapDatabasePostgreSQL::initStatements()
 197 {
 198         prepareStatement("read_block",
 199                 "SELECT data FROM blocks "
 200                         "WHERE posX = $1::int4 AND posY = $2::int4 AND "
 201                         "posZ = $3::int4");
 202
 203         if (getPGVersion() < 90500) {
 204                 prepareStatement("write_block_insert",
 205                         "INSERT INTO blocks (posX, posY, posZ, data) SELECT "
 206                                 "$1::int4, $2::int4, $3::int4, $4::bytea "
 207                                 "WHERE NOT EXISTS (SELECT true FROM blocks "
 208                                 "WHERE posX = $1::int4 AND posY = $2::int4 AND "
 209                                 "posZ = $3::int4)");
 210
 211                 prepareStatement("write_block_update",
 212                         "UPDATE blocks SET data = $4::bytea "
 213                                 "WHERE posX = $1::int4 AND posY = $2::int4 AND "
 214                                 "posZ = $3::int4");
 215         } else {
 216                 prepareStatement("write_block",
 217                         "INSERT INTO blocks (posX, posY, posZ, data) VALUES "
 218                                 "($1::int4, $2::int4, $3::int4, $4::bytea) "
 219                                 "ON CONFLICT ON CONSTRAINT blocks_pkey DO "
 220                                 "UPDATE SET data = $4::bytea");
 221         }
 222
 223         prepareStatement("delete_block", "DELETE FROM blocks WHERE "
 224                 "posX = $1::int4 AND posY = $2::int4 AND posZ = $3::int4");
 225
 226         prepareStatement("list_all_loadable_blocks",
 227                 "SELECT posX, posY, posZ FROM blocks");
 228 }
 229
 230 bool MapDatabasePostgreSQL::saveBlock(const v3s16 &pos, const std::string &data)
 231 {
 232         // Verify if we don't overflow the platform integer with the mapblock size
 233         if (data.size() > INT_MAX) {
 234                 errorstream << "Database_PostgreSQL::saveBlock: Data truncation! "
 235                         << "data.size() over 0xFFFFFFFF (== " << data.size()
 236                         << ")" << std::endl;
 237                 return false;
 238         }
 239
 240         verifyDatabase();
 241
 242         s32 x, y, z;
 243         x = htonl(pos.X);
 244         y = htonl(pos.Y);
 245         z = htonl(pos.Z);
 246
 247         const void *args[] = { &x, &y, &z, data.c_str() };
 248         const int argLen[] = {
 249                 sizeof(x), sizeof(y), sizeof(z), (int)data.size()
 250         };
 251         const int argFmt[] = { 1, 1, 1, 1 };
 252
 253         if (getPGVersion() < 90500) {
 254                 execPrepared("write_block_update", ARRLEN(args), args, argLen, argFmt);
 255                 execPrepared("write_block_insert", ARRLEN(args), args, argLen, argFmt);
 256         } else {
 257                 execPrepared("write_block", ARRLEN(args), args, argLen, argFmt);
 258         }
 259         return true;
 260 }
 261
 262 void MapDatabasePostgreSQL::loadBlock(const v3s16 &pos, std::string *block)
 263 {
 264         verifyDatabase();
 265
 266         s32 x, y, z;
 267         x = htonl(pos.X);
 268         y = htonl(pos.Y);
 269         z = htonl(pos.Z);
 270
 271         const void *args[] = { &x, &y, &z };
 272         const int argLen[] = { sizeof(x), sizeof(y), sizeof(z) };
 273         const int argFmt[] = { 1, 1, 1 };
 274
 275         PGresult *results = execPrepared("read_block", ARRLEN(args), args,
 276                 argLen, argFmt, false);
 277
 278         if (PQntuples(results))
 279                 *block = pg_to_string(results, 0, 0);
 280         else
 281                 block->clear();
 282
 283         PQclear(results);
 284 }
 285
 286 bool MapDatabasePostgreSQL::deleteBlock(const v3s16 &pos)
 287 {
 288         verifyDatabase();
 289
 290         s32 x, y, z;
 291         x = htonl(pos.X);
 292         y = htonl(pos.Y);
 293         z = htonl(pos.Z);
 294
 295         const void *args[] = { &x, &y, &z };
 296         const int argLen[] = { sizeof(x), sizeof(y), sizeof(z) };
 297         const int argFmt[] = { 1, 1, 1 };
 298
 299         execPrepared("delete_block", ARRLEN(args), args, argLen, argFmt);
 300
 301         return true;
 302 }
 303
 304 void MapDatabasePostgreSQL::listAllLoadableBlocks(std::vector<v3s16> &dst)
 305 {
 306         verifyDatabase();
 307
 308         PGresult *results = execPrepared("list_all_loadable_blocks", 0,
 309                 NULL, NULL, NULL, false, false);
 310
 311         int numrows = PQntuples(results);
 312
 313         for (int row = 0; row < numrows; ++row)
 314                 dst.push_back(pg_to_v3s16(results, row, 0));
 315
 316         PQclear(results);
 317 }
 318
 319 /*
 320  * Player Database
 321  */
 322 PlayerDatabasePostgreSQL::PlayerDatabasePostgreSQL(const std::string &connect_string):
 323         Database_PostgreSQL(connect_string, "_player"),
 324         PlayerDatabase()
 325 {
 326         connectToDatabase();
 327 }
 328
 329
 330 void PlayerDatabasePostgreSQL::createDatabase()
 331 {
 332         createTableIfNotExists("player",
 333                 "CREATE TABLE player ("
 334                         "name VARCHAR(60) NOT NULL,"
 335                         "pitch NUMERIC(15, 7) NOT NULL,"
 336                         "yaw NUMERIC(15, 7) NOT NULL,"
 337                         "posX NUMERIC(15, 7) NOT NULL,"
 338                         "posY NUMERIC(15, 7) NOT NULL,"
 339                         "posZ NUMERIC(15, 7) NOT NULL,"
 340                         "hp INT NOT NULL,"
 341                         "breath INT NOT NULL,"
 342                         "creation_date TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT NOW(),"
 343                         "modification_date TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT NOW(),"
 344                         "PRIMARY KEY (name)"
 345                         ");"
 346         );
 347
 348         createTableIfNotExists("player_inventories",
 349                 "CREATE TABLE player_inventories ("
 350                         "player VARCHAR(60) NOT NULL,"
 351                         "inv_id INT NOT NULL,"
 352                         "inv_width INT NOT NULL,"
 353                         "inv_name TEXT NOT NULL DEFAULT '',"
 354                         "inv_size INT NOT NULL,"
 355                         "PRIMARY KEY(player, inv_id),"
 356                         "CONSTRAINT player_inventories_fkey FOREIGN KEY (player) REFERENCES "
 357                         "player (name) ON DELETE CASCADE"
 358                         ");"
 359         );
 360
 361         createTableIfNotExists("player_inventory_items",
 362                 "CREATE TABLE player_inventory_items ("
 363                         "player VARCHAR(60) NOT NULL,"
 364                         "inv_id INT NOT NULL,"
 365                         "slot_id INT NOT NULL,"
 366                         "item TEXT NOT NULL DEFAULT '',"
 367                         "PRIMARY KEY(player, inv_id, slot_id),"
 368                         "CONSTRAINT player_inventory_items_fkey FOREIGN KEY (player) REFERENCES "
 369                         "player (name) ON DELETE CASCADE"
 370                         ");"
 371         );
 372
 373         createTableIfNotExists("player_metadata",
 374                 "CREATE TABLE player_metadata ("
 375                         "player VARCHAR(60) NOT NULL,"
 376                         "attr VARCHAR(256) NOT NULL,"
 377                         "value TEXT,"
 378                         "PRIMARY KEY(player, attr),"
 379                         "CONSTRAINT player_metadata_fkey FOREIGN KEY (player) REFERENCES "
 380                         "player (name) ON DELETE CASCADE"
 381                         ");"
 382         );
 383
 384         infostream << "PostgreSQL: Player Database was inited." << std::endl;
 385 }
 386
 387 void PlayerDatabasePostgreSQL::initStatements()
 388 {
 389         if (getPGVersion() < 90500) {
 390                 prepareStatement("create_player",
 391                         "INSERT INTO player(name, pitch, yaw, posX, posY, posZ, hp, breath) VALUES "
 392                                 "($1, $2, $3, $4, $5, $6, $7::int, $8::int)");
 393
 394                 prepareStatement("update_player",
 395                         "UPDATE SET pitch = $2, yaw = $3, posX = $4, posY = $5, posZ = $6, hp = $7::int, "
 396                                 "breath = $8::int, modification_date = NOW() WHERE name = $1");
 397         } else {
 398                 prepareStatement("save_player",
 399                         "INSERT INTO player(name, pitch, yaw, posX, posY, posZ, hp, breath) VALUES "
 400                                 "($1, $2, $3, $4, $5, $6, $7::int, $8::int)"
 401                                 "ON CONFLICT ON CONSTRAINT player_pkey DO UPDATE SET pitch = $2, yaw = $3, "
 402                                 "posX = $4, posY = $5, posZ = $6, hp = $7::int, breath = $8::int, "
 403                                 "modification_date = NOW()");
 404         }
 405
 406         prepareStatement("remove_player", "DELETE FROM player WHERE name = $1");
 407
 408         prepareStatement("load_player_list", "SELECT name FROM player");
 409
 410         prepareStatement("remove_player_inventories",
 411                 "DELETE FROM player_inventories WHERE player = $1");
 412
 413         prepareStatement("remove_player_inventory_items",
 414                 "DELETE FROM player_inventory_items WHERE player = $1");
 415
 416         prepareStatement("add_player_inventory",
 417                 "INSERT INTO player_inventories (player, inv_id, inv_width, inv_name, inv_size) VALUES "
 418                         "($1, $2::int, $3::int, $4, $5::int)");
 419
 420         prepareStatement("add_player_inventory_item",
 421                 "INSERT INTO player_inventory_items (player, inv_id, slot_id, item) VALUES "
 422                         "($1, $2::int, $3::int, $4)");
 423
 424         prepareStatement("load_player_inventories",
 425                 "SELECT inv_id, inv_width, inv_name, inv_size FROM player_inventories "
 426                         "WHERE player = $1 ORDER BY inv_id");
 427
 428         prepareStatement("load_player_inventory_items",
 429                 "SELECT slot_id, item FROM player_inventory_items WHERE "
 430                         "player = $1 AND inv_id = $2::int");
 431
 432         prepareStatement("load_player",
 433                 "SELECT pitch, yaw, posX, posY, posZ, hp, breath FROM player WHERE name = $1");
 434
 435         prepareStatement("remove_player_metadata",
 436                 "DELETE FROM player_metadata WHERE player = $1");
 437
 438         prepareStatement("save_player_metadata",
 439                 "INSERT INTO player_metadata (player, attr, value) VALUES ($1, $2, $3)");
 440
 441         prepareStatement("load_player_metadata",
 442                 "SELECT attr, value FROM player_metadata WHERE player = $1");
 443
 444 }
 445
 446 bool PlayerDatabasePostgreSQL::playerDataExists(const std::string &playername)
 447 {
 448         verifyDatabase();
 449
 450         const char *values[] = { playername.c_str() };
 451         PGresult *results = execPrepared("load_player", 1, values, false);
 452
 453         bool res = (PQntuples(results) > 0);
 454         PQclear(results);
 455         return res;
 456 }
 457
 458 void PlayerDatabasePostgreSQL::savePlayer(RemotePlayer *player)
 459 {
 460         PlayerSAO* sao = player->getPlayerSAO();
 461         if (!sao)
 462                 return;
 463
 464         verifyDatabase();
 465
 466         v3f pos = sao->getBasePosition();
 467         std::string pitch = ftos(sao->getLookPitch());
 468         std::string yaw = ftos(sao->getRotation().Y);
 469         std::string posx = ftos(pos.X);
 470         std::string posy = ftos(pos.Y);
 471         std::string posz = ftos(pos.Z);
 472         std::string hp = itos(sao->getHP());
 473         std::string breath = itos(sao->getBreath());
 474         const char *values[] = {
 475                 player->getName(),
 476                 pitch.c_str(),
 477                 yaw.c_str(),
 478                 posx.c_str(), posy.c_str(), posz.c_str(),
 479                 hp.c_str(),
 480                 breath.c_str()
 481         };
 482
 483         const char* rmvalues[] = { player->getName() };
 484         beginSave();
 485
 486         if (getPGVersion() < 90500) {
 487                 if (!playerDataExists(player->getName()))
 488                         execPrepared("create_player", 8, values, true, false);
 489                 else
 490                         execPrepared("update_player", 8, values, true, false);
 491         }
 492         else
 493                 execPrepared("save_player", 8, values, true, false);
 494
 495         // Write player inventories
 496         execPrepared("remove_player_inventories", 1, rmvalues);
 497         execPrepared("remove_player_inventory_items", 1, rmvalues);
 498
 499         const auto &inventory_lists = sao->getInventory()->getLists();
 500         std::ostringstream oss;
 501         for (u16 i = 0; i < inventory_lists.size(); i++) {
 502                 const InventoryList* list = inventory_lists[i];
 503                 const std::string &name = list->getName();
 504                 std::string width = itos(list->getWidth()),
 505                         inv_id = itos(i), lsize = itos(list->getSize());
 506
 507                 const char* inv_values[] = {
 508                         player->getName(),
 509                         inv_id.c_str(),
 510                         width.c_str(),
 511                         name.c_str(),
 512                         lsize.c_str()
 513                 };
 514                 execPrepared("add_player_inventory", 5, inv_values);
 515
 516                 for (u32 j = 0; j < list->getSize(); j++) {
 517                         oss.str("");
 518                         oss.clear();
 519                         list->getItem(j).serialize(oss);
 520                         std::string itemStr = oss.str(), slotId = itos(j);
 521
 522                         const char* invitem_values[] = {
 523                                 player->getName(),
 524                                 inv_id.c_str(),
 525                                 slotId.c_str(),
 526                                 itemStr.c_str()
 527                         };
 528                         execPrepared("add_player_inventory_item", 4, invitem_values);
 529                 }
 530         }
 531
 532         execPrepared("remove_player_metadata", 1, rmvalues);
 533         const StringMap &attrs = sao->getMeta().getStrings();
 534         for (const auto &attr : attrs) {
 535                 const char *meta_values[] = {
 536                         player->getName(),
 537                         attr.first.c_str(),
 538                         attr.second.c_str()
 539                 };
 540                 execPrepared("save_player_metadata", 3, meta_values);
 541         }
 542         endSave();
 543
 544         player->onSuccessfulSave();
 545 }
 546
 547 bool PlayerDatabasePostgreSQL::loadPlayer(RemotePlayer *player, PlayerSAO *sao)
 548 {
 549         sanity_check(sao);
 550         verifyDatabase();
 551
 552         const char *values[] = { player->getName() };
 553         PGresult *results = execPrepared("load_player", 1, values, false, false);
 554
 555         // Player not found, return not found
 556         if (!PQntuples(results)) {
 557                 PQclear(results);
 558                 return false;
 559         }
 560
 561         sao->setLookPitch(pg_to_float(results, 0, 0));
 562         sao->setRotation(v3f(0, pg_to_float(results, 0, 1), 0));
 563         sao->setBasePosition(v3f(
 564                 pg_to_float(results, 0, 2),
 565                 pg_to_float(results, 0, 3),
 566                 pg_to_float(results, 0, 4))
 567         );
 568         sao->setHPRaw((u16) pg_to_int(results, 0, 5));
 569         sao->setBreath((u16) pg_to_int(results, 0, 6), false);
 570
 571         PQclear(results);
 572
 573         // Load inventory
 574         results = execPrepared("load_player_inventories", 1, values, false, false);
 575
 576         int resultCount = PQntuples(results);
 577
 578         for (int row = 0; row < resultCount; ++row) {
 579                 InventoryList* invList = player->inventory.
 580                         addList(PQgetvalue(results, row, 2), pg_to_uint(results, row, 3));
 581                 invList->setWidth(pg_to_uint(results, row, 1));
 582
 583                 u32 invId = pg_to_uint(results, row, 0);
 584                 std::string invIdStr = itos(invId);
 585
 586                 const char* values2[] = {
 587                         player->getName(),
 588                         invIdStr.c_str()
 589                 };
 590                 PGresult *results2 = execPrepared("load_player_inventory_items", 2,
 591                         values2, false, false);
 592
 593                 int resultCount2 = PQntuples(results2);
 594                 for (int row2 = 0; row2 < resultCount2; row2++) {
 595                         const std::string itemStr = PQgetvalue(results2, row2, 1);
 596                         if (itemStr.length() > 0) {
 597                                 ItemStack stack;
 598                                 stack.deSerialize(itemStr);
 599                                 invList->changeItem(pg_to_uint(results2, row2, 0), stack);
 600                         }
 601                 }
 602                 PQclear(results2);
 603         }
 604
 605         PQclear(results);
 606
 607         results = execPrepared("load_player_metadata", 1, values, false);
 608
 609         int numrows = PQntuples(results);
 610         for (int row = 0; row < numrows; row++) {
 611                 sao->getMeta().setString(PQgetvalue(results, row, 0), PQgetvalue(results, row, 1));
 612         }
 613         sao->getMeta().setModified(false);
 614
 615         PQclear(results);
 616
 617         return true;
 618 }
 619
 620 bool PlayerDatabasePostgreSQL::removePlayer(const std::string &name)
 621 {
 622         if (!playerDataExists(name))
 623                 return false;
 624
 625         verifyDatabase();
 626
 627         const char *values[] = { name.c_str() };
 628         execPrepared("remove_player", 1, values);
 629
 630         return true;
 631 }
 632
 633 void PlayerDatabasePostgreSQL::listPlayers(std::vector<std::string> &res)
 634 {
 635         verifyDatabase();
 636
 637         PGresult *results = execPrepared("load_player_list", 0, NULL, false);
 638
 639         int numrows = PQntuples(results);
 640         for (int row = 0; row < numrows; row++)
 641                 res.emplace_back(PQgetvalue(results, row, 0));
 642
 643         PQclear(results);
 644 }
 645
 646 AuthDatabasePostgreSQL::AuthDatabasePostgreSQL(const std::string &connect_string) :
 647         Database_PostgreSQL(connect_string, "_auth"),
 648         AuthDatabase()
 649 {
 650         connectToDatabase();
 651 }
 652
 653 void AuthDatabasePostgreSQL::createDatabase()
 654 {
 655         createTableIfNotExists("auth",
 656                 "CREATE TABLE auth ("
 657                         "id SERIAL,"
 658                         "name TEXT UNIQUE,"
 659                         "password TEXT,"
 660                         "last_login INT NOT NULL DEFAULT 0,"
 661                         "PRIMARY KEY (id)"
 662                 ");");
 663
 664         createTableIfNotExists("user_privileges",
 665                 "CREATE TABLE user_privileges ("
 666                         "id INT,"
 667                         "privilege TEXT,"
 668                         "PRIMARY KEY (id, privilege),"
 669                         "CONSTRAINT fk_id FOREIGN KEY (id) REFERENCES auth (id) ON DELETE CASCADE"
 670                 ");");
 671 }
 672
 673 void AuthDatabasePostgreSQL::initStatements()
 674 {
 675         prepareStatement("auth_read", "SELECT id, name, password, last_login FROM auth WHERE name = $1");
 676         prepareStatement("auth_write", "UPDATE auth SET name = $1, password = $2, last_login = $3 WHERE id = $4");
 677         prepareStatement("auth_create", "INSERT INTO auth (name, password, last_login) VALUES ($1, $2, $3) RETURNING id");
 678         prepareStatement("auth_delete", "DELETE FROM auth WHERE name = $1");
 679
 680         prepareStatement("auth_list_names", "SELECT name FROM auth ORDER BY name DESC");
 681
 682         prepareStatement("auth_read_privs", "SELECT privilege FROM user_privileges WHERE id = $1");
 683         prepareStatement("auth_write_privs", "INSERT INTO user_privileges (id, privilege) VALUES ($1, $2)");
 684         prepareStatement("auth_delete_privs", "DELETE FROM user_privileges WHERE id = $1");
 685 }
 686
 687 bool AuthDatabasePostgreSQL::getAuth(const std::string &name, AuthEntry &res)
 688 {
 689         verifyDatabase();
 690
 691         const char *values[] = { name.c_str() };
 692         PGresult *result = execPrepared("auth_read", 1, values, false, false);
 693         int numrows = PQntuples(result);
 694         if (numrows == 0) {
 695                 PQclear(result);
 696                 return false;
 697         }
 698
 699         res.id = pg_to_uint(result, 0, 0);
 700         res.name = pg_to_string(result, 0, 1);
 701         res.password = pg_to_string(result, 0, 2);
 702         res.last_login = pg_to_int(result, 0, 3);
 703
 704         PQclear(result);
 705
 706         std::string playerIdStr = itos(res.id);
 707         const char *privsValues[] = { playerIdStr.c_str() };
 708         PGresult *results = execPrepared("auth_read_privs", 1, privsValues, false);
 709
 710         numrows = PQntuples(results);
 711         for (int row = 0; row < numrows; row++)
 712                 res.privileges.emplace_back(PQgetvalue(results, row, 0));
 713
 714         PQclear(results);
 715
 716         return true;
 717 }
 718
 719 bool AuthDatabasePostgreSQL::saveAuth(const AuthEntry &authEntry)
 720 {
 721         verifyDatabase();
 722
 723         beginSave();
 724
 725         std::string lastLoginStr = itos(authEntry.last_login);
 726         std::string idStr = itos(authEntry.id);
 727         const char *values[] = {
 728                 authEntry.name.c_str() ,
 729                 authEntry.password.c_str(),
 730                 lastLoginStr.c_str(),
 731                 idStr.c_str(),
 732         };
 733         execPrepared("auth_write", 4, values);
 734
 735         writePrivileges(authEntry);
 736
 737         endSave();
 738         return true;
 739 }
 740
 741 bool AuthDatabasePostgreSQL::createAuth(AuthEntry &authEntry)
 742 {
 743         verifyDatabase();
 744
 745         std::string lastLoginStr = itos(authEntry.last_login);
 746         const char *values[] = {
 747                 authEntry.name.c_str() ,
 748                 authEntry.password.c_str(),
 749                 lastLoginStr.c_str()
 750         };
 751
 752         beginSave();
 753
 754         PGresult *result = execPrepared("auth_create", 3, values, false, false);
 755
 756         int numrows = PQntuples(result);
 757         if (numrows == 0) {
 758                 errorstream << "Strange behavior on auth creation, no ID returned." << std::endl;
 759                 PQclear(result);
 760                 rollback();
 761                 return false;
 762         }
 763
 764         authEntry.id = pg_to_uint(result, 0, 0);
 765         PQclear(result);
 766
 767         writePrivileges(authEntry);
 768
 769         endSave();
 770         return true;
 771 }
 772
 773 bool AuthDatabasePostgreSQL::deleteAuth(const std::string &name)
 774 {
 775         verifyDatabase();
 776
 777         const char *values[] = { name.c_str() };
 778         execPrepared("auth_delete", 1, values);
 779
 780         // privileges deleted by foreign key on delete cascade
 781         return true;
 782 }
 783
 784 void AuthDatabasePostgreSQL::listNames(std::vector<std::string> &res)
 785 {
 786         verifyDatabase();
 787
 788         PGresult *results = execPrepared("auth_list_names", 0,
 789                 NULL, NULL, NULL, false, false);
 790
 791         int numrows = PQntuples(results);
 792
 793         for (int row = 0; row < numrows; ++row)
 794                 res.emplace_back(PQgetvalue(results, row, 0));
 795
 796         PQclear(results);
 797 }
 798
 799 void AuthDatabasePostgreSQL::reload()
 800 {
 801         // noop for PgSQL
 802 }
 803
 804 void AuthDatabasePostgreSQL::writePrivileges(const AuthEntry &authEntry)
 805 {
 806         std::string authIdStr = itos(authEntry.id);
 807         const char *values[] = { authIdStr.c_str() };
 808         execPrepared("auth_delete_privs", 1, values);
 809
 810         for (const std::string &privilege : authEntry.privileges) {
 811                 const char *values[] = { authIdStr.c_str(), privilege.c_str() };
 812                 execPrepared("auth_write_privs", 2, values);
 813         }
 814 }
 815
 816 ModStorageDatabasePostgreSQL::ModStorageDatabasePostgreSQL(const std::string &connect_string):
 817         Database_PostgreSQL(connect_string, "_mod_storage"),
 818         ModStorageDatabase()
 819 {
 820         connectToDatabase();
 821 }
 822
 823 void ModStorageDatabasePostgreSQL::createDatabase()
 824 {
 825         createTableIfNotExists("mod_storage",
 826                 "CREATE TABLE mod_storage ("
 827                         "modname TEXT NOT NULL,"
 828                         "key BYTEA NOT NULL,"
 829                         "value BYTEA NOT NULL,"
 830                         "PRIMARY KEY (modname, key)"
 831                 ");");
 832
 833         infostream << "PostgreSQL: Mod Storage Database was initialized." << std::endl;
 834 }
 835
 836 void ModStorageDatabasePostgreSQL::initStatements()
 837 {
 838         prepareStatement("get_all",
 839                 "SELECT key, value FROM mod_storage WHERE modname = $1");
 840         prepareStatement("get_all_keys",
 841                 "SELECT key FROM mod_storage WHERE modname = $1");
 842         prepareStatement("get",
 843                 "SELECT value FROM mod_storage WHERE modname = $1 AND key = $2::bytea");
 844         prepareStatement("has",
 845                 "SELECT true FROM mod_storage WHERE modname = $1 AND key = $2::bytea");
 846         if (getPGVersion() < 90500) {
 847                 prepareStatement("set_insert",
 848                         "INSERT INTO mod_storage (modname, key, value) "
 849                                 "SELECT $1, $2::bytea, $3::bytea "
 850                                         "WHERE NOT EXISTS ("
 851                                                 "SELECT true FROM mod_storage WHERE modname = $1 AND key = $2::bytea"
 852                                         ")");
 853                 prepareStatement("set_update",
 854                         "UPDATE mod_storage SET value = $3::bytea WHERE modname = $1 AND key = $2::bytea");
 855         } else {
 856                 prepareStatement("set",
 857                         "INSERT INTO mod_storage (modname, key, value) VALUES ($1, $2::bytea, $3::bytea) "
 858                                 "ON CONFLICT ON CONSTRAINT mod_storage_pkey DO "
 859                                         "UPDATE SET value = $3::bytea");
 860         }
 861         prepareStatement("remove",
 862                 "DELETE FROM mod_storage WHERE modname = $1 AND key = $2::bytea");
 863         prepareStatement("remove_all",
 864                 "DELETE FROM mod_storage WHERE modname = $1");
 865         prepareStatement("list",
 866                 "SELECT DISTINCT modname FROM mod_storage");
 867 }
 868
 869 void ModStorageDatabasePostgreSQL::getModEntries(const std::string &modname, StringMap *storage)
 870 {
 871         verifyDatabase();
 872
 873         const void *args[] = { modname.c_str() };
 874         const int argLen[] = { -1 };
 875         const int argFmt[] = { 0 };
 876         PGresult *results = execPrepared("get_all", ARRLEN(args),
 877                         args, argLen, argFmt, false);
 878
 879         int numrows = PQntuples(results);
 880
 881         for (int row = 0; row < numrows; ++row)
 882                 (*storage)[pg_to_string(results, row, 0)] = pg_to_string(results, row, 1);
 883
 884         PQclear(results);
 885 }
 886
 887 void ModStorageDatabasePostgreSQL::getModKeys(const std::string &modname,
 888                 std::vector<std::string> *storage)
 889 {
 890         verifyDatabase();
 891
 892         const void *args[] = { modname.c_str() };
 893         const int argLen[] = { -1 };
 894         const int argFmt[] = { 0 };
 895         PGresult *results = execPrepared("get_all_keys", ARRLEN(args),
 896                         args, argLen, argFmt, false);
 897
 898         int numrows = PQntuples(results);
 899
 900         storage->reserve(storage->size() + numrows);
 901         for (int row = 0; row < numrows; ++row)
 902                 storage->push_back(pg_to_string(results, row, 0));
 903
 904         PQclear(results);
 905 }
 906
 907 bool ModStorageDatabasePostgreSQL::getModEntry(const std::string &modname,
 908         const std::string &key, std::string *value)
 909 {
 910         verifyDatabase();
 911
 912         const void *args[] = { modname.c_str(), key.c_str() };
 913         const int argLen[] = { -1, (int)MYMIN(key.size(), INT_MAX) };
 914         const int argFmt[] = { 0, 1 };
 915         PGresult *results = execPrepared("get", ARRLEN(args), args, argLen, argFmt, false);
 916
 917         int numrows = PQntuples(results);
 918         bool found = numrows > 0;
 919
 920         if (found)
 921                 *value = pg_to_string(results, 0, 0);
 922
 923         PQclear(results);
 924
 925         return found;
 926 }
 927
 928 bool ModStorageDatabasePostgreSQL::hasModEntry(const std::string &modname,
 929                 const std::string &key)
 930 {
 931         verifyDatabase();
 932
 933         const void *args[] = { modname.c_str(), key.c_str() };
 934         const int argLen[] = { -1, (int)MYMIN(key.size(), INT_MAX) };
 935         const int argFmt[] = { 0, 1 };
 936         PGresult *results = execPrepared("has", ARRLEN(args), args, argLen, argFmt, false);
 937
 938         int numrows = PQntuples(results);
 939         bool found = numrows > 0;
 940
 941         PQclear(results);
 942
 943         return found;
 944 }
 945
 946 bool ModStorageDatabasePostgreSQL::setModEntry(const std::string &modname,
 947         const std::string &key, const std::string &value)
 948 {
 949         verifyDatabase();
 950
 951         const void *args[] = { modname.c_str(), key.c_str(), value.c_str() };
 952         const int argLen[] = {
 953                 -1,
 954                 (int)MYMIN(key.size(), INT_MAX),
 955                 (int)MYMIN(value.size(), INT_MAX),
 956         };
 957         const int argFmt[] = { 0, 1, 1 };
 958         if (getPGVersion() < 90500) {
 959                 execPrepared("set_insert", ARRLEN(args), args, argLen, argFmt);
 960                 execPrepared("set_update", ARRLEN(args), args, argLen, argFmt);
 961         } else {
 962                 execPrepared("set", ARRLEN(args), args, argLen, argFmt);
 963         }
 964
 965         return true;
 966 }
 967
 968 bool ModStorageDatabasePostgreSQL::removeModEntry(const std::string &modname,
 969                 const std::string &key)
 970 {
 971         verifyDatabase();
 972
 973         const void *args[] = { modname.c_str(), key.c_str() };
 974         const int argLen[] = { -1, (int)MYMIN(key.size(), INT_MAX) };
 975         const int argFmt[] = { 0, 1 };
 976         PGresult *results = execPrepared("remove", ARRLEN(args), args, argLen, argFmt, false);
 977
 978         int affected = atoi(PQcmdTuples(results));
 979
 980         PQclear(results);
 981
 982         return affected > 0;
 983 }
 984
 985 bool ModStorageDatabasePostgreSQL::removeModEntries(const std::string &modname)
 986 {
 987         verifyDatabase();
 988
 989         const void *args[] = { modname.c_str() };
 990         const int argLen[] = { -1 };
 991         const int argFmt[] = { 0 };
 992         PGresult *results = execPrepared("remove_all", ARRLEN(args), args, argLen, argFmt, false);
 993
 994         int affected = atoi(PQcmdTuples(results));
 995
 996         PQclear(results);
 997
 998         return affected > 0;
 999 }
1000
1001 void ModStorageDatabasePostgreSQL::listMods(std::vector<std::string> *res)
1002 {
1003         verifyDatabase();
1004
1005         PGresult *results = execPrepared("list", 0, NULL, false);
1006
1007         int numrows = PQntuples(results);
1008
1009         for (int row = 0; row < numrows; ++row)
1010                 res->push_back(pg_to_string(results, row, 0));
1011
1012         PQclear(results);
1013 }
1014
1015
1016 #endif // USE_POSTGRESQL