src/database/database-postgresql.cpp

   1 /*
   2 Copyright (C) 2016 Loic Blot <loic.blot@unix-experience.fr>
   3
   4 This program is free software; you can redistribute it and/or modify
   5 it under the terms of the GNU Lesser General Public License as published by
   6 the Free Software Foundation; either version 2.1 of the License, or
   7 (at your option) any later version.
   8
   9 This program is distributed in the hope that it will be useful,
  10 but WITHOUT ANY WARRANTY; without even the implied warranty of
  11 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  12 GNU Lesser General Public License for more details.
  13
  14 You should have received a copy of the GNU Lesser General Public License along
  15 with this program; if not, write to the Free Software Foundation, Inc.,
  16 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
  17 */
  18
  19 #include "config.h"
  20
  21 #if USE_POSTGRESQL
  22
  23 #include "database-postgresql.h"
  24
  25 #ifdef _WIN32
  26         // Without this some of the network functions are not found on mingw
  27         #ifndef _WIN32_WINNT
  28                 #define _WIN32_WINNT 0x0501
  29         #endif
  30         #include <windows.h>
  31         #include <winsock2.h>
  32 #else
  33 #include <netinet/in.h>
  34 #endif
  35
  36 #include "debug.h"
  37 #include "exceptions.h"
  38 #include "settings.h"
  39 #include "remoteplayer.h"
  40 #include "server/player_sao.h"
  41
  42 Database_PostgreSQL::Database_PostgreSQL(const std::string &connect_string) :
  43         m_connect_string(connect_string)
  44 {
  45         if (m_connect_string.empty()) {
  46                 throw SettingNotFoundException(
  47                         "Set pgsql_connection string in world.mt to "
  48                         "use the postgresql backend\n"
  49                         "Notes:\n"
  50                         "pgsql_connection has the following form: \n"
  51                         "\tpgsql_connection = host=127.0.0.1 port=5432 user=mt_user "
  52                         "password=mt_password dbname=minetest_world\n"
  53                         "mt_user should have CREATE TABLE, INSERT, SELECT, UPDATE and "
  54                         "DELETE rights on the database.\n"
  55                         "Don't create mt_user as a SUPERUSER!");
  56         }
  57 }
  58
  59 Database_PostgreSQL::~Database_PostgreSQL()
  60 {
  61         PQfinish(m_conn);
  62 }
  63
  64 void Database_PostgreSQL::connectToDatabase()
  65 {
  66         m_conn = PQconnectdb(m_connect_string.c_str());
  67
  68         if (PQstatus(m_conn) != CONNECTION_OK) {
  69                 throw DatabaseException(std::string(
  70                         "PostgreSQL database error: ") +
  71                         PQerrorMessage(m_conn));
  72         }
  73
  74         m_pgversion = PQserverVersion(m_conn);
  75
  76         /*
  77         * We are using UPSERT feature from PostgreSQL 9.5
  78         * to have the better performance where possible.
  79         */
  80         if (m_pgversion < 90500) {
  81                 warningstream << "Your PostgreSQL server lacks UPSERT "
  82                         << "support. Use version 9.5 or better if possible."
  83                         << std::endl;
  84         }
  85
  86         infostream << "PostgreSQL Database: Version " << m_pgversion
  87                         << " Connection made." << std::endl;
  88
  89         createDatabase();
  90         initStatements();
  91 }
  92
  93 void Database_PostgreSQL::pingDatabase()
  94 {
  95         // Verify DB connection with ping
  96         try {
  97                 ping();
  98         } catch (const DatabaseException &e) {
  99                 // If ping failed, show the error and try reconnect
 100                 PQreset(m_conn);
 101
 102                 errorstream << e.what() << std::endl
 103                         << "Reconnecting to database " << m_connect_string << std::endl;
 104                 connectToDatabase();
 105         }
 106 }
 107
 108 void Database_PostgreSQL::ping()
 109 {
 110         if (PQping(m_connect_string.c_str()) != PQPING_OK) {
 111                 throw DatabaseException(std::string(
 112                         "PostgreSQL database error: ") +
 113                         PQerrorMessage(m_conn));
 114         }
 115 }
 116
 117 bool Database_PostgreSQL::initialized() const
 118 {
 119         return (PQstatus(m_conn) == CONNECTION_OK);
 120 }
 121
 122 PGresult *Database_PostgreSQL::checkResults(PGresult *result, bool clear)
 123 {
 124         ExecStatusType statusType = PQresultStatus(result);
 125
 126         switch (statusType) {
 127         case PGRES_COMMAND_OK:
 128         case PGRES_TUPLES_OK:
 129                 break;
 130         case PGRES_FATAL_ERROR:
 131         default:
 132                 throw DatabaseException(
 133                         std::string("PostgreSQL database error: ") +
 134                         PQresultErrorMessage(result));
 135         }
 136
 137         if (clear)
 138                 PQclear(result);
 139
 140         return result;
 141 }
 142
 143 void Database_PostgreSQL::createTableIfNotExists(const std::string &table_name,
 144                 const std::string &definition)
 145 {
 146         std::string sql_check_table = "SELECT relname FROM pg_class WHERE relname='" +
 147                 table_name + "';";
 148         PGresult *result = checkResults(PQexec(m_conn, sql_check_table.c_str()), false);
 149
 150         // If table doesn't exist, create it
 151         if (!PQntuples(result)) {
 152                 checkResults(PQexec(m_conn, definition.c_str()));
 153         }
 154
 155         PQclear(result);
 156 }
 157
 158 void Database_PostgreSQL::beginSave()
 159 {
 160         pingDatabase();
 161         checkResults(PQexec(m_conn, "BEGIN;"));
 162 }
 163
 164 void Database_PostgreSQL::endSave()
 165 {
 166         checkResults(PQexec(m_conn, "COMMIT;"));
 167 }
 168
 169 void Database_PostgreSQL::rollback()
 170 {
 171         checkResults(PQexec(m_conn, "ROLLBACK;"));
 172 }
 173
 174 MapDatabasePostgreSQL::MapDatabasePostgreSQL(const std::string &connect_string):
 175         Database_PostgreSQL(connect_string),
 176         MapDatabase()
 177 {
 178         connectToDatabase();
 179 }
 180
 181
 182 void MapDatabasePostgreSQL::createDatabase()
 183 {
 184         createTableIfNotExists("blocks",
 185                 "CREATE TABLE blocks ("
 186                         "posX INT NOT NULL,"
 187                         "posY INT NOT NULL,"
 188                         "posZ INT NOT NULL,"
 189                         "data BYTEA,"
 190                         "PRIMARY KEY (posX,posY,posZ)"
 191                         ");"
 192         );
 193
 194         infostream << "PostgreSQL: Map Database was initialized." << std::endl;
 195 }
 196
 197 void MapDatabasePostgreSQL::initStatements()
 198 {
 199         prepareStatement("read_block",
 200                 "SELECT data FROM blocks "
 201                         "WHERE posX = $1::int4 AND posY = $2::int4 AND "
 202                         "posZ = $3::int4");
 203
 204         if (getPGVersion() < 90500) {
 205                 prepareStatement("write_block_insert",
 206                         "INSERT INTO blocks (posX, posY, posZ, data) SELECT "
 207                                 "$1::int4, $2::int4, $3::int4, $4::bytea "
 208                                 "WHERE NOT EXISTS (SELECT true FROM blocks "
 209                                 "WHERE posX = $1::int4 AND posY = $2::int4 AND "
 210                                 "posZ = $3::int4)");
 211
 212                 prepareStatement("write_block_update",
 213                         "UPDATE blocks SET data = $4::bytea "
 214                                 "WHERE posX = $1::int4 AND posY = $2::int4 AND "
 215                                 "posZ = $3::int4");
 216         } else {
 217                 prepareStatement("write_block",
 218                         "INSERT INTO blocks (posX, posY, posZ, data) VALUES "
 219                                 "($1::int4, $2::int4, $3::int4, $4::bytea) "
 220                                 "ON CONFLICT ON CONSTRAINT blocks_pkey DO "
 221                                 "UPDATE SET data = $4::bytea");
 222         }
 223
 224         prepareStatement("delete_block", "DELETE FROM blocks WHERE "
 225                 "posX = $1::int4 AND posY = $2::int4 AND posZ = $3::int4");
 226
 227         prepareStatement("list_all_loadable_blocks",
 228                 "SELECT posX, posY, posZ FROM blocks");
 229 }
 230
 231 bool MapDatabasePostgreSQL::saveBlock(const v3s16 &pos, const std::string &data)
 232 {
 233         // Verify if we don't overflow the platform integer with the mapblock size
 234         if (data.size() > INT_MAX) {
 235                 errorstream << "Database_PostgreSQL::saveBlock: Data truncation! "
 236                         << "data.size() over 0xFFFFFFFF (== " << data.size()
 237                         << ")" << std::endl;
 238                 return false;
 239         }
 240
 241         pingDatabase();
 242
 243         s32 x, y, z;
 244         x = htonl(pos.X);
 245         y = htonl(pos.Y);
 246         z = htonl(pos.Z);
 247
 248         const void *args[] = { &x, &y, &z, data.c_str() };
 249         const int argLen[] = {
 250                 sizeof(x), sizeof(y), sizeof(z), (int)data.size()
 251         };
 252         const int argFmt[] = { 1, 1, 1, 1 };
 253
 254         if (getPGVersion() < 90500) {
 255                 execPrepared("write_block_update", ARRLEN(args), args, argLen, argFmt);
 256                 execPrepared("write_block_insert", ARRLEN(args), args, argLen, argFmt);
 257         } else {
 258                 execPrepared("write_block", ARRLEN(args), args, argLen, argFmt);
 259         }
 260         return true;
 261 }
 262
 263 void MapDatabasePostgreSQL::loadBlock(const v3s16 &pos, std::string *block)
 264 {
 265         pingDatabase();
 266
 267         s32 x, y, z;
 268         x = htonl(pos.X);
 269         y = htonl(pos.Y);
 270         z = htonl(pos.Z);
 271
 272         const void *args[] = { &x, &y, &z };
 273         const int argLen[] = { sizeof(x), sizeof(y), sizeof(z) };
 274         const int argFmt[] = { 1, 1, 1 };
 275
 276         PGresult *results = execPrepared("read_block", ARRLEN(args), args,
 277                 argLen, argFmt, false);
 278
 279         *block = "";
 280
 281         if (PQntuples(results))
 282                 *block = std::string(PQgetvalue(results, 0, 0), PQgetlength(results, 0, 0));
 283
 284         PQclear(results);
 285 }
 286
 287 bool MapDatabasePostgreSQL::deleteBlock(const v3s16 &pos)
 288 {
 289         pingDatabase();
 290
 291         s32 x, y, z;
 292         x = htonl(pos.X);
 293         y = htonl(pos.Y);
 294         z = htonl(pos.Z);
 295
 296         const void *args[] = { &x, &y, &z };
 297         const int argLen[] = { sizeof(x), sizeof(y), sizeof(z) };
 298         const int argFmt[] = { 1, 1, 1 };
 299
 300         execPrepared("delete_block", ARRLEN(args), args, argLen, argFmt);
 301
 302         return true;
 303 }
 304
 305 void MapDatabasePostgreSQL::listAllLoadableBlocks(std::vector<v3s16> &dst)
 306 {
 307         pingDatabase();
 308
 309         PGresult *results = execPrepared("list_all_loadable_blocks", 0,
 310                 NULL, NULL, NULL, false, false);
 311
 312         int numrows = PQntuples(results);
 313
 314         for (int row = 0; row < numrows; ++row)
 315                 dst.push_back(pg_to_v3s16(results, row, 0));
 316
 317         PQclear(results);
 318 }
 319
 320 /*
 321  * Player Database
 322  */
 323 PlayerDatabasePostgreSQL::PlayerDatabasePostgreSQL(const std::string &connect_string):
 324         Database_PostgreSQL(connect_string),
 325         PlayerDatabase()
 326 {
 327         connectToDatabase();
 328 }
 329
 330
 331 void PlayerDatabasePostgreSQL::createDatabase()
 332 {
 333         createTableIfNotExists("player",
 334                 "CREATE TABLE player ("
 335                         "name VARCHAR(60) NOT NULL,"
 336                         "pitch NUMERIC(15, 7) NOT NULL,"
 337                         "yaw NUMERIC(15, 7) NOT NULL,"
 338                         "posX NUMERIC(15, 7) NOT NULL,"
 339                         "posY NUMERIC(15, 7) NOT NULL,"
 340                         "posZ NUMERIC(15, 7) NOT NULL,"
 341                         "hp INT NOT NULL,"
 342                         "breath INT NOT NULL,"
 343                         "creation_date TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT NOW(),"
 344                         "modification_date TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT NOW(),"
 345                         "PRIMARY KEY (name)"
 346                         ");"
 347         );
 348
 349         createTableIfNotExists("player_inventories",
 350                 "CREATE TABLE player_inventories ("
 351                         "player VARCHAR(60) NOT NULL,"
 352                         "inv_id INT NOT NULL,"
 353                         "inv_width INT NOT NULL,"
 354                         "inv_name TEXT NOT NULL DEFAULT '',"
 355                         "inv_size INT NOT NULL,"
 356                         "PRIMARY KEY(player, inv_id),"
 357                         "CONSTRAINT player_inventories_fkey FOREIGN KEY (player) REFERENCES "
 358                         "player (name) ON DELETE CASCADE"
 359                         ");"
 360         );
 361
 362         createTableIfNotExists("player_inventory_items",
 363                 "CREATE TABLE player_inventory_items ("
 364                         "player VARCHAR(60) NOT NULL,"
 365                         "inv_id INT NOT NULL,"
 366                         "slot_id INT NOT NULL,"
 367                         "item TEXT NOT NULL DEFAULT '',"
 368                         "PRIMARY KEY(player, inv_id, slot_id),"
 369                         "CONSTRAINT player_inventory_items_fkey FOREIGN KEY (player) REFERENCES "
 370                         "player (name) ON DELETE CASCADE"
 371                         ");"
 372         );
 373
 374         createTableIfNotExists("player_metadata",
 375                 "CREATE TABLE player_metadata ("
 376                         "player VARCHAR(60) NOT NULL,"
 377                         "attr VARCHAR(256) NOT NULL,"
 378                         "value TEXT,"
 379                         "PRIMARY KEY(player, attr),"
 380                         "CONSTRAINT player_metadata_fkey FOREIGN KEY (player) REFERENCES "
 381                         "player (name) ON DELETE CASCADE"
 382                         ");"
 383         );
 384
 385         infostream << "PostgreSQL: Player Database was inited." << std::endl;
 386 }
 387
 388 void PlayerDatabasePostgreSQL::initStatements()
 389 {
 390         if (getPGVersion() < 90500) {
 391                 prepareStatement("create_player",
 392                         "INSERT INTO player(name, pitch, yaw, posX, posY, posZ, hp, breath) VALUES "
 393                                 "($1, $2, $3, $4, $5, $6, $7::int, $8::int)");
 394
 395                 prepareStatement("update_player",
 396                         "UPDATE SET pitch = $2, yaw = $3, posX = $4, posY = $5, posZ = $6, hp = $7::int, "
 397                                 "breath = $8::int, modification_date = NOW() WHERE name = $1");
 398         } else {
 399                 prepareStatement("save_player",
 400                         "INSERT INTO player(name, pitch, yaw, posX, posY, posZ, hp, breath) VALUES "
 401                                 "($1, $2, $3, $4, $5, $6, $7::int, $8::int)"
 402                                 "ON CONFLICT ON CONSTRAINT player_pkey DO UPDATE SET pitch = $2, yaw = $3, "
 403                                 "posX = $4, posY = $5, posZ = $6, hp = $7::int, breath = $8::int, "
 404                                 "modification_date = NOW()");
 405         }
 406
 407         prepareStatement("remove_player", "DELETE FROM player WHERE name = $1");
 408
 409         prepareStatement("load_player_list", "SELECT name FROM player");
 410
 411         prepareStatement("remove_player_inventories",
 412                 "DELETE FROM player_inventories WHERE player = $1");
 413
 414         prepareStatement("remove_player_inventory_items",
 415                 "DELETE FROM player_inventory_items WHERE player = $1");
 416
 417         prepareStatement("add_player_inventory",
 418                 "INSERT INTO player_inventories (player, inv_id, inv_width, inv_name, inv_size) VALUES "
 419                         "($1, $2::int, $3::int, $4, $5::int)");
 420
 421         prepareStatement("add_player_inventory_item",
 422                 "INSERT INTO player_inventory_items (player, inv_id, slot_id, item) VALUES "
 423                         "($1, $2::int, $3::int, $4)");
 424
 425         prepareStatement("load_player_inventories",
 426                 "SELECT inv_id, inv_width, inv_name, inv_size FROM player_inventories "
 427                         "WHERE player = $1 ORDER BY inv_id");
 428
 429         prepareStatement("load_player_inventory_items",
 430                 "SELECT slot_id, item FROM player_inventory_items WHERE "
 431                         "player = $1 AND inv_id = $2::int");
 432
 433         prepareStatement("load_player",
 434                 "SELECT pitch, yaw, posX, posY, posZ, hp, breath FROM player WHERE name = $1");
 435
 436         prepareStatement("remove_player_metadata",
 437                 "DELETE FROM player_metadata WHERE player = $1");
 438
 439         prepareStatement("save_player_metadata",
 440                 "INSERT INTO player_metadata (player, attr, value) VALUES ($1, $2, $3)");
 441
 442         prepareStatement("load_player_metadata",
 443                 "SELECT attr, value FROM player_metadata WHERE player = $1");
 444
 445 }
 446
 447 bool PlayerDatabasePostgreSQL::playerDataExists(const std::string &playername)
 448 {
 449         pingDatabase();
 450
 451         const char *values[] = { playername.c_str() };
 452         PGresult *results = execPrepared("load_player", 1, values, false);
 453
 454         bool res = (PQntuples(results) > 0);
 455         PQclear(results);
 456         return res;
 457 }
 458
 459 void PlayerDatabasePostgreSQL::savePlayer(RemotePlayer *player)
 460 {
 461         PlayerSAO* sao = player->getPlayerSAO();
 462         if (!sao)
 463                 return;
 464
 465         pingDatabase();
 466
 467         v3f pos = sao->getBasePosition();
 468         std::string pitch = ftos(sao->getLookPitch());
 469         std::string yaw = ftos(sao->getRotation().Y);
 470         std::string posx = ftos(pos.X);
 471         std::string posy = ftos(pos.Y);
 472         std::string posz = ftos(pos.Z);
 473         std::string hp = itos(sao->getHP());
 474         std::string breath = itos(sao->getBreath());
 475         const char *values[] = {
 476                 player->getName(),
 477                 pitch.c_str(),
 478                 yaw.c_str(),
 479                 posx.c_str(), posy.c_str(), posz.c_str(),
 480                 hp.c_str(),
 481                 breath.c_str()
 482         };
 483
 484         const char* rmvalues[] = { player->getName() };
 485         beginSave();
 486
 487         if (getPGVersion() < 90500) {
 488                 if (!playerDataExists(player->getName()))
 489                         execPrepared("create_player", 8, values, true, false);
 490                 else
 491                         execPrepared("update_player", 8, values, true, false);
 492         }
 493         else
 494                 execPrepared("save_player", 8, values, true, false);
 495
 496         // Write player inventories
 497         execPrepared("remove_player_inventories", 1, rmvalues);
 498         execPrepared("remove_player_inventory_items", 1, rmvalues);
 499
 500         std::vector<const InventoryList*> inventory_lists = sao->getInventory()->getLists();
 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                         std::ostringstream os;
 518                         list->getItem(j).serialize(os);
 519                         std::string itemStr = os.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         pingDatabase();
 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         pingDatabase();
 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         pingDatabase();
 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), AuthDatabase()
 647 {
 648         connectToDatabase();
 649 }
 650
 651 void AuthDatabasePostgreSQL::createDatabase()
 652 {
 653         createTableIfNotExists("auth",
 654                 "CREATE TABLE auth ("
 655                         "id SERIAL,"
 656                         "name TEXT UNIQUE,"
 657                         "password TEXT,"
 658                         "last_login INT NOT NULL DEFAULT 0,"
 659                         "PRIMARY KEY (id)"
 660                 ");");
 661
 662         createTableIfNotExists("user_privileges",
 663                 "CREATE TABLE user_privileges ("
 664                         "id INT,"
 665                         "privilege TEXT,"
 666                         "PRIMARY KEY (id, privilege),"
 667                         "CONSTRAINT fk_id FOREIGN KEY (id) REFERENCES auth (id) ON DELETE CASCADE"
 668                 ");");
 669 }
 670
 671 void AuthDatabasePostgreSQL::initStatements()
 672 {
 673         prepareStatement("auth_read", "SELECT id, name, password, last_login FROM auth WHERE name = $1");
 674         prepareStatement("auth_write", "UPDATE auth SET name = $1, password = $2, last_login = $3 WHERE id = $4");
 675         prepareStatement("auth_create", "INSERT INTO auth (name, password, last_login) VALUES ($1, $2, $3) RETURNING id");
 676         prepareStatement("auth_delete", "DELETE FROM auth WHERE name = $1");
 677
 678         prepareStatement("auth_list_names", "SELECT name FROM auth ORDER BY name DESC");
 679
 680         prepareStatement("auth_read_privs", "SELECT privilege FROM user_privileges WHERE id = $1");
 681         prepareStatement("auth_write_privs", "INSERT INTO user_privileges (id, privilege) VALUES ($1, $2)");
 682         prepareStatement("auth_delete_privs", "DELETE FROM user_privileges WHERE id = $1");
 683 }
 684
 685 bool AuthDatabasePostgreSQL::getAuth(const std::string &name, AuthEntry &res)
 686 {
 687         pingDatabase();
 688
 689         const char *values[] = { name.c_str() };
 690         PGresult *result = execPrepared("auth_read", 1, values, false, false);
 691         int numrows = PQntuples(result);
 692         if (numrows == 0) {
 693                 PQclear(result);
 694                 return false;
 695         }
 696
 697         res.id = pg_to_uint(result, 0, 0);
 698         res.name = std::string(PQgetvalue(result, 0, 1), PQgetlength(result, 0, 1));
 699         res.password = std::string(PQgetvalue(result, 0, 2), PQgetlength(result, 0, 2));
 700         res.last_login = pg_to_int(result, 0, 3);
 701
 702         PQclear(result);
 703
 704         std::string playerIdStr = itos(res.id);
 705         const char *privsValues[] = { playerIdStr.c_str() };
 706         PGresult *results = execPrepared("auth_read_privs", 1, privsValues, false);
 707
 708         numrows = PQntuples(results);
 709         for (int row = 0; row < numrows; row++)
 710                 res.privileges.emplace_back(PQgetvalue(results, row, 0));
 711
 712         PQclear(results);
 713
 714         return true;
 715 }
 716
 717 bool AuthDatabasePostgreSQL::saveAuth(const AuthEntry &authEntry)
 718 {
 719         pingDatabase();
 720
 721         beginSave();
 722
 723         std::string lastLoginStr = itos(authEntry.last_login);
 724         std::string idStr = itos(authEntry.id);
 725         const char *values[] = {
 726                 authEntry.name.c_str() ,
 727                 authEntry.password.c_str(),
 728                 lastLoginStr.c_str(),
 729                 idStr.c_str(),
 730         };
 731         execPrepared("auth_write", 4, values);
 732
 733         writePrivileges(authEntry);
 734
 735         endSave();
 736         return true;
 737 }
 738
 739 bool AuthDatabasePostgreSQL::createAuth(AuthEntry &authEntry)
 740 {
 741         pingDatabase();
 742
 743         std::string lastLoginStr = itos(authEntry.last_login);
 744         const char *values[] = {
 745                 authEntry.name.c_str() ,
 746                 authEntry.password.c_str(),
 747                 lastLoginStr.c_str()
 748         };
 749
 750         beginSave();
 751
 752         PGresult *result = execPrepared("auth_create", 3, values, false, false);
 753
 754         int numrows = PQntuples(result);
 755         if (numrows == 0) {
 756                 errorstream << "Strange behaviour on auth creation, no ID returned." << std::endl;
 757                 PQclear(result);
 758                 rollback();
 759                 return false;
 760         }
 761
 762         authEntry.id = pg_to_uint(result, 0, 0);
 763         PQclear(result);
 764
 765         writePrivileges(authEntry);
 766
 767         endSave();
 768         return true;
 769 }
 770
 771 bool AuthDatabasePostgreSQL::deleteAuth(const std::string &name)
 772 {
 773         pingDatabase();
 774
 775         const char *values[] = { name.c_str() };
 776         execPrepared("auth_delete", 1, values);
 777
 778         // privileges deleted by foreign key on delete cascade
 779         return true;
 780 }
 781
 782 void AuthDatabasePostgreSQL::listNames(std::vector<std::string> &res)
 783 {
 784         pingDatabase();
 785
 786         PGresult *results = execPrepared("auth_list_names", 0,
 787                 NULL, NULL, NULL, false, false);
 788
 789         int numrows = PQntuples(results);
 790
 791         for (int row = 0; row < numrows; ++row)
 792                 res.emplace_back(PQgetvalue(results, row, 0));
 793
 794         PQclear(results);
 795 }
 796
 797 void AuthDatabasePostgreSQL::reload()
 798 {
 799         // noop for PgSQL
 800 }
 801
 802 void AuthDatabasePostgreSQL::writePrivileges(const AuthEntry &authEntry)
 803 {
 804         std::string authIdStr = itos(authEntry.id);
 805         const char *values[] = { authIdStr.c_str() };
 806         execPrepared("auth_delete_privs", 1, values);
 807
 808         for (const std::string &privilege : authEntry.privileges) {
 809                 const char *values[] = { authIdStr.c_str(), privilege.c_str() };
 810                 execPrepared("auth_write_privs", 2, values);
 811         }
 812 }
 813
 814
 815 #endif // USE_POSTGRESQL