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         const char *type) :
  44         m_connect_string(connect_string)
  45 {
  46         if (m_connect_string.empty()) {
  47                 // Use given type to reference the exact setting in the error message
  48                 std::string s = type;
  49                 std::string msg =
  50                         "Set pgsql" + s + "_connection string in world.mt to "
  51                         "use the postgresql backend\n"
  52                         "Notes:\n"
  53                         "pgsql" + s + "_connection has the following form: \n"
  54                         "\tpgsql" + s + "_connection = host=127.0.0.1 port=5432 "
  55                         "user=mt_user password=mt_password dbname=minetest" + s + "\n"
  56                         "mt_user should have CREATE TABLE, INSERT, SELECT, UPDATE and "
  57                         "DELETE rights on the database. "
  58                         "Don't create mt_user as a SUPERUSER!";
  59                 throw SettingNotFoundException(msg);
  60         }
  61 }
  62
  63 Database_PostgreSQL::~Database_PostgreSQL()
  64 {
  65         PQfinish(m_conn);
  66 }
  67
  68 void Database_PostgreSQL::connectToDatabase()
  69 {
  70         m_conn = PQconnectdb(m_connect_string.c_str());
  71
  72         if (PQstatus(m_conn) != CONNECTION_OK) {
  73                 throw DatabaseException(std::string(
  74                         "PostgreSQL database error: ") +
  75                         PQerrorMessage(m_conn));
  76         }
  77
  78         m_pgversion = PQserverVersion(m_conn);
  79
  80         /*
  81         * We are using UPSERT feature from PostgreSQL 9.5
  82         * to have the better performance where possible.
  83         */
  84         if (m_pgversion < 90500) {
  85                 warningstream << "Your PostgreSQL server lacks UPSERT "
  86                         << "support. Use version 9.5 or better if possible."
  87                         << std::endl;
  88         }
  89
  90         infostream << "PostgreSQL Database: Version " << m_pgversion
  91                         << " Connection made." << std::endl;
  92
  93         createDatabase();
  94         initStatements();
  95 }
  96
  97 void Database_PostgreSQL::verifyDatabase()
  98 {
  99         if (PQstatus(m_conn) == CONNECTION_OK)
 100                 return;
 101
 102         PQreset(m_conn);
 103         ping();
 104 }
 105
 106 void Database_PostgreSQL::ping()
 107 {
 108         if (PQping(m_connect_string.c_str()) != PQPING_OK) {
 109                 throw DatabaseException(std::string(
 110                         "PostgreSQL database error: ") +
 111                         PQerrorMessage(m_conn));
 112         }
 113 }
 114
 115 bool Database_PostgreSQL::initialized() const
 116 {
 117         return (PQstatus(m_conn) == CONNECTION_OK);
 118 }
 119
 120 PGresult *Database_PostgreSQL::checkResults(PGresult *result, bool clear)
 121 {
 122         ExecStatusType statusType = PQresultStatus(result);
 123
 124         switch (statusType) {
 125         case PGRES_COMMAND_OK:
 126         case PGRES_TUPLES_OK:
 127                 break;
 128         case PGRES_FATAL_ERROR:
 129         default:
 130                 throw DatabaseException(
 131                         std::string("PostgreSQL database error: ") +
 132                         PQresultErrorMessage(result));
 133         }
 134
 135         if (clear)
 136                 PQclear(result);
 137
 138         return result;
 139 }
 140
 141 void Database_PostgreSQL::createTableIfNotExists(const std::string &table_name,
 142                 const std::string &definition)
 143 {
 144         std::string sql_check_table = "SELECT relname FROM pg_class WHERE relname='" +
 145                 table_name + "';";
 146         PGresult *result = checkResults(PQexec(m_conn, sql_check_table.c_str()), false);
 147
 148         // If table doesn't exist, create it
 149         if (!PQntuples(result)) {
 150                 checkResults(PQexec(m_conn, definition.c_str()));
 151         }
 152
 153         PQclear(result);
 154 }
 155
 156 void Database_PostgreSQL::beginSave()
 157 {
 158         verifyDatabase();
 159         checkResults(PQexec(m_conn, "BEGIN;"));
 160 }
 161
 162 void Database_PostgreSQL::endSave()
 163 {
 164         checkResults(PQexec(m_conn, "COMMIT;"));
 165 }
 166
 167 void Database_PostgreSQL::rollback()
 168 {
 169         checkResults(PQexec(m_conn, "ROLLBACK;"));
 170 }
 171
 172 MapDatabasePostgreSQL::MapDatabasePostgreSQL(const std::string &connect_string):
 173         Database_PostgreSQL(connect_string, ""),
 174         MapDatabase()
 175 {
 176         connectToDatabase();
 177 }
 178
 179
 180 void MapDatabasePostgreSQL::createDatabase()
 181 {
 182         createTableIfNotExists("blocks",
 183                 "CREATE TABLE blocks ("
 184                         "posX INT NOT NULL,"
 185                         "posY INT NOT NULL,"
 186                         "posZ INT NOT NULL,"
 187                         "data BYTEA,"
 188                         "PRIMARY KEY (posX,posY,posZ)"
 189                         ");"
 190         );
 191
 192         infostream << "PostgreSQL: Map Database was initialized." << std::endl;
 193 }
 194
 195 void MapDatabasePostgreSQL::initStatements()
 196 {
 197         prepareStatement("read_block",
 198                 "SELECT data FROM blocks "
 199                         "WHERE posX = $1::int4 AND posY = $2::int4 AND "
 200                         "posZ = $3::int4");
 201
 202         if (getPGVersion() < 90500) {
 203                 prepareStatement("write_block_insert",
 204                         "INSERT INTO blocks (posX, posY, posZ, data) SELECT "
 205                                 "$1::int4, $2::int4, $3::int4, $4::bytea "
 206                                 "WHERE NOT EXISTS (SELECT true FROM blocks "
 207                                 "WHERE posX = $1::int4 AND posY = $2::int4 AND "
 208                                 "posZ = $3::int4)");
 209
 210                 prepareStatement("write_block_update",
 211                         "UPDATE blocks SET data = $4::bytea "
 212                                 "WHERE posX = $1::int4 AND posY = $2::int4 AND "
 213                                 "posZ = $3::int4");
 214         } else {
 215                 prepareStatement("write_block",
 216                         "INSERT INTO blocks (posX, posY, posZ, data) VALUES "
 217                                 "($1::int4, $2::int4, $3::int4, $4::bytea) "
 218                                 "ON CONFLICT ON CONSTRAINT blocks_pkey DO "
 219                                 "UPDATE SET data = $4::bytea");
 220         }
 221
 222         prepareStatement("delete_block", "DELETE FROM blocks WHERE "
 223                 "posX = $1::int4 AND posY = $2::int4 AND posZ = $3::int4");
 224
 225         prepareStatement("list_all_loadable_blocks",
 226                 "SELECT posX, posY, posZ FROM blocks");
 227 }
 228
 229 bool MapDatabasePostgreSQL::saveBlock(const v3s16 &pos, const std::string &data)
 230 {
 231         // Verify if we don't overflow the platform integer with the mapblock size
 232         if (data.size() > INT_MAX) {
 233                 errorstream << "Database_PostgreSQL::saveBlock: Data truncation! "
 234                         << "data.size() over 0xFFFFFFFF (== " << data.size()
 235                         << ")" << std::endl;
 236                 return false;
 237         }
 238
 239         verifyDatabase();
 240
 241         s32 x, y, z;
 242         x = htonl(pos.X);
 243         y = htonl(pos.Y);
 244         z = htonl(pos.Z);
 245
 246         const void *args[] = { &x, &y, &z, data.c_str() };
 247         const int argLen[] = {
 248                 sizeof(x), sizeof(y), sizeof(z), (int)data.size()
 249         };
 250         const int argFmt[] = { 1, 1, 1, 1 };
 251
 252         if (getPGVersion() < 90500) {
 253                 execPrepared("write_block_update", ARRLEN(args), args, argLen, argFmt);
 254                 execPrepared("write_block_insert", ARRLEN(args), args, argLen, argFmt);
 255         } else {
 256                 execPrepared("write_block", ARRLEN(args), args, argLen, argFmt);
 257         }
 258         return true;
 259 }
 260
 261 void MapDatabasePostgreSQL::loadBlock(const v3s16 &pos, std::string *block)
 262 {
 263         verifyDatabase();
 264
 265         s32 x, y, z;
 266         x = htonl(pos.X);
 267         y = htonl(pos.Y);
 268         z = htonl(pos.Z);
 269
 270         const void *args[] = { &x, &y, &z };
 271         const int argLen[] = { sizeof(x), sizeof(y), sizeof(z) };
 272         const int argFmt[] = { 1, 1, 1 };
 273
 274         PGresult *results = execPrepared("read_block", ARRLEN(args), args,
 275                 argLen, argFmt, false);
 276
 277         if (PQntuples(results))
 278                 block->assign(PQgetvalue(results, 0, 0), PQgetlength(results, 0, 0));
 279         else
 280                 block->clear();
 281
 282         PQclear(results);
 283 }
 284
 285 bool MapDatabasePostgreSQL::deleteBlock(const v3s16 &pos)
 286 {
 287         verifyDatabase();
 288
 289         s32 x, y, z;
 290         x = htonl(pos.X);
 291         y = htonl(pos.Y);
 292         z = htonl(pos.Z);
 293
 294         const void *args[] = { &x, &y, &z };
 295         const int argLen[] = { sizeof(x), sizeof(y), sizeof(z) };
 296         const int argFmt[] = { 1, 1, 1 };
 297
 298         execPrepared("delete_block", ARRLEN(args), args, argLen, argFmt);
 299
 300         return true;
 301 }
 302
 303 void MapDatabasePostgreSQL::listAllLoadableBlocks(std::vector<v3s16> &dst)
 304 {
 305         verifyDatabase();
 306
 307         PGresult *results = execPrepared("list_all_loadable_blocks", 0,
 308                 NULL, NULL, NULL, false, false);
 309
 310         int numrows = PQntuples(results);
 311
 312         for (int row = 0; row < numrows; ++row)
 313                 dst.push_back(pg_to_v3s16(results, row, 0));
 314
 315         PQclear(results);
 316 }
 317
 318 /*
 319  * Player Database
 320  */
 321 PlayerDatabasePostgreSQL::PlayerDatabasePostgreSQL(const std::string &connect_string):
 322         Database_PostgreSQL(connect_string, "_player"),
 323         PlayerDatabase()
 324 {
 325         connectToDatabase();
 326 }
 327
 328
 329 void PlayerDatabasePostgreSQL::createDatabase()
 330 {
 331         createTableIfNotExists("player",
 332                 "CREATE TABLE player ("
 333                         "name VARCHAR(60) NOT NULL,"
 334                         "pitch NUMERIC(15, 7) NOT NULL,"
 335                         "yaw NUMERIC(15, 7) NOT NULL,"
 336                         "posX NUMERIC(15, 7) NOT NULL,"
 337                         "posY NUMERIC(15, 7) NOT NULL,"
 338                         "posZ NUMERIC(15, 7) NOT NULL,"
 339                         "hp INT NOT NULL,"
 340                         "breath INT NOT NULL,"
 341                         "creation_date TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT NOW(),"
 342                         "modification_date TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT NOW(),"
 343                         "PRIMARY KEY (name)"
 344                         ");"
 345         );
 346
 347         createTableIfNotExists("player_inventories",
 348                 "CREATE TABLE player_inventories ("
 349                         "player VARCHAR(60) NOT NULL,"
 350                         "inv_id INT NOT NULL,"
 351                         "inv_width INT NOT NULL,"
 352                         "inv_name TEXT NOT NULL DEFAULT '',"
 353                         "inv_size INT NOT NULL,"
 354                         "PRIMARY KEY(player, inv_id),"
 355                         "CONSTRAINT player_inventories_fkey FOREIGN KEY (player) REFERENCES "
 356                         "player (name) ON DELETE CASCADE"
 357                         ");"
 358         );
 359
 360         createTableIfNotExists("player_inventory_items",
 361                 "CREATE TABLE player_inventory_items ("
 362                         "player VARCHAR(60) NOT NULL,"
 363                         "inv_id INT NOT NULL,"
 364                         "slot_id INT NOT NULL,"
 365                         "item TEXT NOT NULL DEFAULT '',"
 366                         "PRIMARY KEY(player, inv_id, slot_id),"
 367                         "CONSTRAINT player_inventory_items_fkey FOREIGN KEY (player) REFERENCES "
 368                         "player (name) ON DELETE CASCADE"
 369                         ");"
 370         );
 371
 372         createTableIfNotExists("player_metadata",
 373                 "CREATE TABLE player_metadata ("
 374                         "player VARCHAR(60) NOT NULL,"
 375                         "attr VARCHAR(256) NOT NULL,"
 376                         "value TEXT,"
 377                         "PRIMARY KEY(player, attr),"
 378                         "CONSTRAINT player_metadata_fkey FOREIGN KEY (player) REFERENCES "
 379                         "player (name) ON DELETE CASCADE"
 380                         ");"
 381         );
 382
 383         infostream << "PostgreSQL: Player Database was inited." << std::endl;
 384 }
 385
 386 void PlayerDatabasePostgreSQL::initStatements()
 387 {
 388         if (getPGVersion() < 90500) {
 389                 prepareStatement("create_player",
 390                         "INSERT INTO player(name, pitch, yaw, posX, posY, posZ, hp, breath) VALUES "
 391                                 "($1, $2, $3, $4, $5, $6, $7::int, $8::int)");
 392
 393                 prepareStatement("update_player",
 394                         "UPDATE SET pitch = $2, yaw = $3, posX = $4, posY = $5, posZ = $6, hp = $7::int, "
 395                                 "breath = $8::int, modification_date = NOW() WHERE name = $1");
 396         } else {
 397                 prepareStatement("save_player",
 398                         "INSERT INTO player(name, pitch, yaw, posX, posY, posZ, hp, breath) VALUES "
 399                                 "($1, $2, $3, $4, $5, $6, $7::int, $8::int)"
 400                                 "ON CONFLICT ON CONSTRAINT player_pkey DO UPDATE SET pitch = $2, yaw = $3, "
 401                                 "posX = $4, posY = $5, posZ = $6, hp = $7::int, breath = $8::int, "
 402                                 "modification_date = NOW()");
 403         }
 404
 405         prepareStatement("remove_player", "DELETE FROM player WHERE name = $1");
 406
 407         prepareStatement("load_player_list", "SELECT name FROM player");
 408
 409         prepareStatement("remove_player_inventories",
 410                 "DELETE FROM player_inventories WHERE player = $1");
 411
 412         prepareStatement("remove_player_inventory_items",
 413                 "DELETE FROM player_inventory_items WHERE player = $1");
 414
 415         prepareStatement("add_player_inventory",
 416                 "INSERT INTO player_inventories (player, inv_id, inv_width, inv_name, inv_size) VALUES "
 417                         "($1, $2::int, $3::int, $4, $5::int)");
 418
 419         prepareStatement("add_player_inventory_item",
 420                 "INSERT INTO player_inventory_items (player, inv_id, slot_id, item) VALUES "
 421                         "($1, $2::int, $3::int, $4)");
 422
 423         prepareStatement("load_player_inventories",
 424                 "SELECT inv_id, inv_width, inv_name, inv_size FROM player_inventories "
 425                         "WHERE player = $1 ORDER BY inv_id");
 426
 427         prepareStatement("load_player_inventory_items",
 428                 "SELECT slot_id, item FROM player_inventory_items WHERE "
 429                         "player = $1 AND inv_id = $2::int");
 430
 431         prepareStatement("load_player",
 432                 "SELECT pitch, yaw, posX, posY, posZ, hp, breath FROM player WHERE name = $1");
 433
 434         prepareStatement("remove_player_metadata",
 435                 "DELETE FROM player_metadata WHERE player = $1");
 436
 437         prepareStatement("save_player_metadata",
 438                 "INSERT INTO player_metadata (player, attr, value) VALUES ($1, $2, $3)");
 439
 440         prepareStatement("load_player_metadata",
 441                 "SELECT attr, value FROM player_metadata WHERE player = $1");
 442
 443 }
 444
 445 bool PlayerDatabasePostgreSQL::playerDataExists(const std::string &playername)
 446 {
 447         verifyDatabase();
 448
 449         const char *values[] = { playername.c_str() };
 450         PGresult *results = execPrepared("load_player", 1, values, false);
 451
 452         bool res = (PQntuples(results) > 0);
 453         PQclear(results);
 454         return res;
 455 }
 456
 457 void PlayerDatabasePostgreSQL::savePlayer(RemotePlayer *player)
 458 {
 459         PlayerSAO* sao = player->getPlayerSAO();
 460         if (!sao)
 461                 return;
 462
 463         verifyDatabase();
 464
 465         v3f pos = sao->getBasePosition();
 466         std::string pitch = ftos(sao->getLookPitch());
 467         std::string yaw = ftos(sao->getRotation().Y);
 468         std::string posx = ftos(pos.X);
 469         std::string posy = ftos(pos.Y);
 470         std::string posz = ftos(pos.Z);
 471         std::string hp = itos(sao->getHP());
 472         std::string breath = itos(sao->getBreath());
 473         const char *values[] = {
 474                 player->getName(),
 475                 pitch.c_str(),
 476                 yaw.c_str(),
 477                 posx.c_str(), posy.c_str(), posz.c_str(),
 478                 hp.c_str(),
 479                 breath.c_str()
 480         };
 481
 482         const char* rmvalues[] = { player->getName() };
 483         beginSave();
 484
 485         if (getPGVersion() < 90500) {
 486                 if (!playerDataExists(player->getName()))
 487                         execPrepared("create_player", 8, values, true, false);
 488                 else
 489                         execPrepared("update_player", 8, values, true, false);
 490         }
 491         else
 492                 execPrepared("save_player", 8, values, true, false);
 493
 494         // Write player inventories
 495         execPrepared("remove_player_inventories", 1, rmvalues);
 496         execPrepared("remove_player_inventory_items", 1, rmvalues);
 497
 498         const auto &inventory_lists = sao->getInventory()->getLists();
 499         std::ostringstream oss;
 500         for (u16 i = 0; i < inventory_lists.size(); i++) {
 501                 const InventoryList* list = inventory_lists[i];
 502                 const std::string &name = list->getName();
 503                 std::string width = itos(list->getWidth()),
 504                         inv_id = itos(i), lsize = itos(list->getSize());
 505
 506                 const char* inv_values[] = {
 507                         player->getName(),
 508                         inv_id.c_str(),
 509                         width.c_str(),
 510                         name.c_str(),
 511                         lsize.c_str()
 512                 };
 513                 execPrepared("add_player_inventory", 5, inv_values);
 514
 515                 for (u32 j = 0; j < list->getSize(); j++) {
 516                         oss.str("");
 517                         oss.clear();
 518                         list->getItem(j).serialize(oss);
 519                         std::string itemStr = oss.str(), slotId = itos(j);
 520
 521                         const char* invitem_values[] = {
 522                                 player->getName(),
 523                                 inv_id.c_str(),
 524                                 slotId.c_str(),
 525                                 itemStr.c_str()
 526                         };
 527                         execPrepared("add_player_inventory_item", 4, invitem_values);
 528                 }
 529         }
 530
 531         execPrepared("remove_player_metadata", 1, rmvalues);
 532         const StringMap &attrs = sao->getMeta().getStrings();
 533         for (const auto &attr : attrs) {
 534                 const char *meta_values[] = {
 535                         player->getName(),
 536                         attr.first.c_str(),
 537                         attr.second.c_str()
 538                 };
 539                 execPrepared("save_player_metadata", 3, meta_values);
 540         }
 541         endSave();
 542
 543         player->onSuccessfulSave();
 544 }
 545
 546 bool PlayerDatabasePostgreSQL::loadPlayer(RemotePlayer *player, PlayerSAO *sao)
 547 {
 548         sanity_check(sao);
 549         verifyDatabase();
 550
 551         const char *values[] = { player->getName() };
 552         PGresult *results = execPrepared("load_player", 1, values, false, false);
 553
 554         // Player not found, return not found
 555         if (!PQntuples(results)) {
 556                 PQclear(results);
 557                 return false;
 558         }
 559
 560         sao->setLookPitch(pg_to_float(results, 0, 0));
 561         sao->setRotation(v3f(0, pg_to_float(results, 0, 1), 0));
 562         sao->setBasePosition(v3f(
 563                 pg_to_float(results, 0, 2),
 564                 pg_to_float(results, 0, 3),
 565                 pg_to_float(results, 0, 4))
 566         );
 567         sao->setHPRaw((u16) pg_to_int(results, 0, 5));
 568         sao->setBreath((u16) pg_to_int(results, 0, 6), false);
 569
 570         PQclear(results);
 571
 572         // Load inventory
 573         results = execPrepared("load_player_inventories", 1, values, false, false);
 574
 575         int resultCount = PQntuples(results);
 576
 577         for (int row = 0; row < resultCount; ++row) {
 578                 InventoryList* invList = player->inventory.
 579                         addList(PQgetvalue(results, row, 2), pg_to_uint(results, row, 3));
 580                 invList->setWidth(pg_to_uint(results, row, 1));
 581
 582                 u32 invId = pg_to_uint(results, row, 0);
 583                 std::string invIdStr = itos(invId);
 584
 585                 const char* values2[] = {
 586                         player->getName(),
 587                         invIdStr.c_str()
 588                 };
 589                 PGresult *results2 = execPrepared("load_player_inventory_items", 2,
 590                         values2, false, false);
 591
 592                 int resultCount2 = PQntuples(results2);
 593                 for (int row2 = 0; row2 < resultCount2; row2++) {
 594                         const std::string itemStr = PQgetvalue(results2, row2, 1);
 595                         if (itemStr.length() > 0) {
 596                                 ItemStack stack;
 597                                 stack.deSerialize(itemStr);
 598                                 invList->changeItem(pg_to_uint(results2, row2, 0), stack);
 599                         }
 600                 }
 601                 PQclear(results2);
 602         }
 603
 604         PQclear(results);
 605
 606         results = execPrepared("load_player_metadata", 1, values, false);
 607
 608         int numrows = PQntuples(results);
 609         for (int row = 0; row < numrows; row++) {
 610                 sao->getMeta().setString(PQgetvalue(results, row, 0), PQgetvalue(results, row, 1));
 611         }
 612         sao->getMeta().setModified(false);
 613
 614         PQclear(results);
 615
 616         return true;
 617 }
 618
 619 bool PlayerDatabasePostgreSQL::removePlayer(const std::string &name)
 620 {
 621         if (!playerDataExists(name))
 622                 return false;
 623
 624         verifyDatabase();
 625
 626         const char *values[] = { name.c_str() };
 627         execPrepared("remove_player", 1, values);
 628
 629         return true;
 630 }
 631
 632 void PlayerDatabasePostgreSQL::listPlayers(std::vector<std::string> &res)
 633 {
 634         verifyDatabase();
 635
 636         PGresult *results = execPrepared("load_player_list", 0, NULL, false);
 637
 638         int numrows = PQntuples(results);
 639         for (int row = 0; row < numrows; row++)
 640                 res.emplace_back(PQgetvalue(results, row, 0));
 641
 642         PQclear(results);
 643 }
 644
 645 AuthDatabasePostgreSQL::AuthDatabasePostgreSQL(const std::string &connect_string) :
 646         Database_PostgreSQL(connect_string, "_auth"),
 647         AuthDatabase()
 648 {
 649         connectToDatabase();
 650 }
 651
 652 void AuthDatabasePostgreSQL::createDatabase()
 653 {
 654         createTableIfNotExists("auth",
 655                 "CREATE TABLE auth ("
 656                         "id SERIAL,"
 657                         "name TEXT UNIQUE,"
 658                         "password TEXT,"
 659                         "last_login INT NOT NULL DEFAULT 0,"
 660                         "PRIMARY KEY (id)"
 661                 ");");
 662
 663         createTableIfNotExists("user_privileges",
 664                 "CREATE TABLE user_privileges ("
 665                         "id INT,"
 666                         "privilege TEXT,"
 667                         "PRIMARY KEY (id, privilege),"
 668                         "CONSTRAINT fk_id FOREIGN KEY (id) REFERENCES auth (id) ON DELETE CASCADE"
 669                 ");");
 670 }
 671
 672 void AuthDatabasePostgreSQL::initStatements()
 673 {
 674         prepareStatement("auth_read", "SELECT id, name, password, last_login FROM auth WHERE name = $1");
 675         prepareStatement("auth_write", "UPDATE auth SET name = $1, password = $2, last_login = $3 WHERE id = $4");
 676         prepareStatement("auth_create", "INSERT INTO auth (name, password, last_login) VALUES ($1, $2, $3) RETURNING id");
 677         prepareStatement("auth_delete", "DELETE FROM auth WHERE name = $1");
 678
 679         prepareStatement("auth_list_names", "SELECT name FROM auth ORDER BY name DESC");
 680
 681         prepareStatement("auth_read_privs", "SELECT privilege FROM user_privileges WHERE id = $1");
 682         prepareStatement("auth_write_privs", "INSERT INTO user_privileges (id, privilege) VALUES ($1, $2)");
 683         prepareStatement("auth_delete_privs", "DELETE FROM user_privileges WHERE id = $1");
 684 }
 685
 686 bool AuthDatabasePostgreSQL::getAuth(const std::string &name, AuthEntry &res)
 687 {
 688         verifyDatabase();
 689
 690         const char *values[] = { name.c_str() };
 691         PGresult *result = execPrepared("auth_read", 1, values, false, false);
 692         int numrows = PQntuples(result);
 693         if (numrows == 0) {
 694                 PQclear(result);
 695                 return false;
 696         }
 697
 698         res.id = pg_to_uint(result, 0, 0);
 699         res.name = std::string(PQgetvalue(result, 0, 1), PQgetlength(result, 0, 1));
 700         res.password = std::string(PQgetvalue(result, 0, 2), PQgetlength(result, 0, 2));
 701         res.last_login = pg_to_int(result, 0, 3);
 702
 703         PQclear(result);
 704
 705         std::string playerIdStr = itos(res.id);
 706         const char *privsValues[] = { playerIdStr.c_str() };
 707         PGresult *results = execPrepared("auth_read_privs", 1, privsValues, false);
 708
 709         numrows = PQntuples(results);
 710         for (int row = 0; row < numrows; row++)
 711                 res.privileges.emplace_back(PQgetvalue(results, row, 0));
 712
 713         PQclear(results);
 714
 715         return true;
 716 }
 717
 718 bool AuthDatabasePostgreSQL::saveAuth(const AuthEntry &authEntry)
 719 {
 720         verifyDatabase();
 721
 722         beginSave();
 723
 724         std::string lastLoginStr = itos(authEntry.last_login);
 725         std::string idStr = itos(authEntry.id);
 726         const char *values[] = {
 727                 authEntry.name.c_str() ,
 728                 authEntry.password.c_str(),
 729                 lastLoginStr.c_str(),
 730                 idStr.c_str(),
 731         };
 732         execPrepared("auth_write", 4, values);
 733
 734         writePrivileges(authEntry);
 735
 736         endSave();
 737         return true;
 738 }
 739
 740 bool AuthDatabasePostgreSQL::createAuth(AuthEntry &authEntry)
 741 {
 742         verifyDatabase();
 743
 744         std::string lastLoginStr = itos(authEntry.last_login);
 745         const char *values[] = {
 746                 authEntry.name.c_str() ,
 747                 authEntry.password.c_str(),
 748                 lastLoginStr.c_str()
 749         };
 750
 751         beginSave();
 752
 753         PGresult *result = execPrepared("auth_create", 3, values, false, false);
 754
 755         int numrows = PQntuples(result);
 756         if (numrows == 0) {
 757                 errorstream << "Strange behaviour on auth creation, no ID returned." << std::endl;
 758                 PQclear(result);
 759                 rollback();
 760                 return false;
 761         }
 762
 763         authEntry.id = pg_to_uint(result, 0, 0);
 764         PQclear(result);
 765
 766         writePrivileges(authEntry);
 767
 768         endSave();
 769         return true;
 770 }
 771
 772 bool AuthDatabasePostgreSQL::deleteAuth(const std::string &name)
 773 {
 774         verifyDatabase();
 775
 776         const char *values[] = { name.c_str() };
 777         execPrepared("auth_delete", 1, values);
 778
 779         // privileges deleted by foreign key on delete cascade
 780         return true;
 781 }
 782
 783 void AuthDatabasePostgreSQL::listNames(std::vector<std::string> &res)
 784 {
 785         verifyDatabase();
 786
 787         PGresult *results = execPrepared("auth_list_names", 0,
 788                 NULL, NULL, NULL, false, false);
 789
 790         int numrows = PQntuples(results);
 791
 792         for (int row = 0; row < numrows; ++row)
 793                 res.emplace_back(PQgetvalue(results, row, 0));
 794
 795         PQclear(results);
 796 }
 797
 798 void AuthDatabasePostgreSQL::reload()
 799 {
 800         // noop for PgSQL
 801 }
 802
 803 void AuthDatabasePostgreSQL::writePrivileges(const AuthEntry &authEntry)
 804 {
 805         std::string authIdStr = itos(authEntry.id);
 806         const char *values[] = { authIdStr.c_str() };
 807         execPrepared("auth_delete_privs", 1, values);
 808
 809         for (const std::string &privilege : authEntry.privileges) {
 810                 const char *values[] = { authIdStr.c_str(), privilege.c_str() };
 811                 execPrepared("auth_write_privs", 2, values);
 812         }
 813 }
 814
 815
 816 #endif // USE_POSTGRESQL