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
Database_PostgreSQL(const std::string & connect_string)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
~Database_PostgreSQL()59 Database_PostgreSQL::~Database_PostgreSQL()
60 {
61 PQfinish(m_conn);
62 }
63
connectToDatabase()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
verifyDatabase()93 void Database_PostgreSQL::verifyDatabase()
94 {
95 if (PQstatus(m_conn) == CONNECTION_OK)
96 return;
97
98 PQreset(m_conn);
99 ping();
100 }
101
ping()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
initialized() const111 bool Database_PostgreSQL::initialized() const
112 {
113 return (PQstatus(m_conn) == CONNECTION_OK);
114 }
115
checkResults(PGresult * result,bool clear)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
createTableIfNotExists(const std::string & table_name,const std::string & definition)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
beginSave()152 void Database_PostgreSQL::beginSave()
153 {
154 verifyDatabase();
155 checkResults(PQexec(m_conn, "BEGIN;"));
156 }
157
endSave()158 void Database_PostgreSQL::endSave()
159 {
160 checkResults(PQexec(m_conn, "COMMIT;"));
161 }
162
rollback()163 void Database_PostgreSQL::rollback()
164 {
165 checkResults(PQexec(m_conn, "ROLLBACK;"));
166 }
167
MapDatabasePostgreSQL(const std::string & connect_string)168 MapDatabasePostgreSQL::MapDatabasePostgreSQL(const std::string &connect_string):
169 Database_PostgreSQL(connect_string),
170 MapDatabase()
171 {
172 connectToDatabase();
173 }
174
175
createDatabase()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
initStatements()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
saveBlock(const v3s16 & pos,const std::string & data)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
loadBlock(const v3s16 & pos,std::string * block)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
deleteBlock(const v3s16 & pos)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
listAllLoadableBlocks(std::vector<v3s16> & dst)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 */
PlayerDatabasePostgreSQL(const std::string & connect_string)317 PlayerDatabasePostgreSQL::PlayerDatabasePostgreSQL(const std::string &connect_string):
318 Database_PostgreSQL(connect_string),
319 PlayerDatabase()
320 {
321 connectToDatabase();
322 }
323
324
createDatabase()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
initStatements()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
playerDataExists(const std::string & playername)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
savePlayer(RemotePlayer * player)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
loadPlayer(RemotePlayer * player,PlayerSAO * sao)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
removePlayer(const std::string & name)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
listPlayers(std::vector<std::string> & res)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
AuthDatabasePostgreSQL(const std::string & connect_string)639 AuthDatabasePostgreSQL::AuthDatabasePostgreSQL(const std::string &connect_string) :
640 Database_PostgreSQL(connect_string), AuthDatabase()
641 {
642 connectToDatabase();
643 }
644
createDatabase()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
initStatements()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
getAuth(const std::string & name,AuthEntry & res)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
saveAuth(const AuthEntry & authEntry)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
createAuth(AuthEntry & authEntry)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
deleteAuth(const std::string & name)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
listNames(std::vector<std::string> & res)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
reload()791 void AuthDatabasePostgreSQL::reload()
792 {
793 // noop for PgSQL
794 }
795
writePrivileges(const AuthEntry & authEntry)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
810