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