#include "debug.h"
#include "exceptions.h"
#include "settings.h"
-#include "content_sao.h"
#include "remoteplayer.h"
+#include "server/player_sao.h"
Database_PostgreSQL::Database_PostgreSQL(const std::string &connect_string) :
m_connect_string(connect_string)
initStatements();
}
-void Database_PostgreSQL::verifyDatabase()
+void Database_PostgreSQL::pingDatabase()
{
- if (PQstatus(m_conn) == CONNECTION_OK)
- return;
-
- PQreset(m_conn);
- ping();
+ // Verify DB connection with ping
+ try {
+ ping();
+ } catch (const DatabaseException &e) {
+ // If ping failed, show the error and try reconnect
+ PQreset(m_conn);
+
+ errorstream << e.what() << std::endl
+ << "Reconnecting to database " << m_connect_string << std::endl;
+ connectToDatabase();
+ }
}
void Database_PostgreSQL::ping()
void Database_PostgreSQL::beginSave()
{
- verifyDatabase();
+ pingDatabase();
checkResults(PQexec(m_conn, "BEGIN;"));
}
checkResults(PQexec(m_conn, "COMMIT;"));
}
+void Database_PostgreSQL::rollback()
+{
+ checkResults(PQexec(m_conn, "ROLLBACK;"));
+}
+
MapDatabasePostgreSQL::MapDatabasePostgreSQL(const std::string &connect_string):
Database_PostgreSQL(connect_string),
MapDatabase()
return false;
}
- verifyDatabase();
+ pingDatabase();
s32 x, y, z;
x = htonl(pos.X);
void MapDatabasePostgreSQL::loadBlock(const v3s16 &pos, std::string *block)
{
- verifyDatabase();
+ pingDatabase();
s32 x, y, z;
x = htonl(pos.X);
bool MapDatabasePostgreSQL::deleteBlock(const v3s16 &pos)
{
- verifyDatabase();
+ pingDatabase();
s32 x, y, z;
x = htonl(pos.X);
void MapDatabasePostgreSQL::listAllLoadableBlocks(std::vector<v3s16> &dst)
{
- verifyDatabase();
+ pingDatabase();
PGresult *results = execPrepared("list_all_loadable_blocks", 0,
NULL, NULL, NULL, false, false);
int numrows = PQntuples(results);
for (int row = 0; row < numrows; ++row)
- dst.push_back(pg_to_v3s16(results, 0, 0));
+ dst.push_back(pg_to_v3s16(results, row, 0));
PQclear(results);
}
bool PlayerDatabasePostgreSQL::playerDataExists(const std::string &playername)
{
- verifyDatabase();
+ pingDatabase();
const char *values[] = { playername.c_str() };
PGresult *results = execPrepared("load_player", 1, values, false);
if (!sao)
return;
- verifyDatabase();
+ pingDatabase();
v3f pos = sao->getBasePosition();
std::string pitch = ftos(sao->getLookPitch());
};
execPrepared("save_player_metadata", 3, meta_values);
}
- sao->getMeta().setModified(false);
endSave();
+
+ player->onSuccessfulSave();
}
bool PlayerDatabasePostgreSQL::loadPlayer(RemotePlayer *player, PlayerSAO *sao)
{
sanity_check(sao);
- verifyDatabase();
+ pingDatabase();
const char *values[] = { player->getName() };
PGresult *results = execPrepared("load_player", 1, values, false, false);
pg_to_float(results, 0, 3),
pg_to_float(results, 0, 4))
);
- sao->setHPRaw((s16) pg_to_int(results, 0, 5));
+ sao->setHPRaw((u16) pg_to_int(results, 0, 5));
sao->setBreath((u16) pg_to_int(results, 0, 6), false);
PQclear(results);
if (!playerDataExists(name))
return false;
- verifyDatabase();
+ pingDatabase();
const char *values[] = { name.c_str() };
execPrepared("remove_player", 1, values);
void PlayerDatabasePostgreSQL::listPlayers(std::vector<std::string> &res)
{
- verifyDatabase();
+ pingDatabase();
PGresult *results = execPrepared("load_player_list", 0, NULL, false);
PQclear(results);
}
+AuthDatabasePostgreSQL::AuthDatabasePostgreSQL(const std::string &connect_string) :
+ Database_PostgreSQL(connect_string), AuthDatabase()
+{
+ connectToDatabase();
+}
+
+void AuthDatabasePostgreSQL::createDatabase()
+{
+ createTableIfNotExists("auth",
+ "CREATE TABLE auth ("
+ "id SERIAL,"
+ "name TEXT UNIQUE,"
+ "password TEXT,"
+ "last_login INT NOT NULL DEFAULT 0,"
+ "PRIMARY KEY (id)"
+ ");");
+
+ createTableIfNotExists("user_privileges",
+ "CREATE TABLE user_privileges ("
+ "id INT,"
+ "privilege TEXT,"
+ "PRIMARY KEY (id, privilege),"
+ "CONSTRAINT fk_id FOREIGN KEY (id) REFERENCES auth (id) ON DELETE CASCADE"
+ ");");
+}
+
+void AuthDatabasePostgreSQL::initStatements()
+{
+ prepareStatement("auth_read", "SELECT id, name, password, last_login FROM auth WHERE name = $1");
+ prepareStatement("auth_write", "UPDATE auth SET name = $1, password = $2, last_login = $3 WHERE id = $4");
+ prepareStatement("auth_create", "INSERT INTO auth (name, password, last_login) VALUES ($1, $2, $3) RETURNING id");
+ prepareStatement("auth_delete", "DELETE FROM auth WHERE name = $1");
+
+ prepareStatement("auth_list_names", "SELECT name FROM auth ORDER BY name DESC");
+
+ prepareStatement("auth_read_privs", "SELECT privilege FROM user_privileges WHERE id = $1");
+ prepareStatement("auth_write_privs", "INSERT INTO user_privileges (id, privilege) VALUES ($1, $2)");
+ prepareStatement("auth_delete_privs", "DELETE FROM user_privileges WHERE id = $1");
+}
+
+bool AuthDatabasePostgreSQL::getAuth(const std::string &name, AuthEntry &res)
+{
+ pingDatabase();
+
+ const char *values[] = { name.c_str() };
+ PGresult *result = execPrepared("auth_read", 1, values, false, false);
+ int numrows = PQntuples(result);
+ if (numrows == 0) {
+ PQclear(result);
+ return false;
+ }
+
+ res.id = pg_to_uint(result, 0, 0);
+ res.name = std::string(PQgetvalue(result, 0, 1), PQgetlength(result, 0, 1));
+ res.password = std::string(PQgetvalue(result, 0, 2), PQgetlength(result, 0, 2));
+ res.last_login = pg_to_int(result, 0, 3);
+
+ PQclear(result);
+
+ std::string playerIdStr = itos(res.id);
+ const char *privsValues[] = { playerIdStr.c_str() };
+ PGresult *results = execPrepared("auth_read_privs", 1, privsValues, false);
+
+ numrows = PQntuples(results);
+ for (int row = 0; row < numrows; row++)
+ res.privileges.emplace_back(PQgetvalue(results, row, 0));
+
+ PQclear(results);
+
+ return true;
+}
+
+bool AuthDatabasePostgreSQL::saveAuth(const AuthEntry &authEntry)
+{
+ pingDatabase();
+
+ beginSave();
+
+ std::string lastLoginStr = itos(authEntry.last_login);
+ std::string idStr = itos(authEntry.id);
+ const char *values[] = {
+ authEntry.name.c_str() ,
+ authEntry.password.c_str(),
+ lastLoginStr.c_str(),
+ idStr.c_str(),
+ };
+ execPrepared("auth_write", 4, values);
+
+ writePrivileges(authEntry);
+
+ endSave();
+ return true;
+}
+
+bool AuthDatabasePostgreSQL::createAuth(AuthEntry &authEntry)
+{
+ pingDatabase();
+
+ std::string lastLoginStr = itos(authEntry.last_login);
+ const char *values[] = {
+ authEntry.name.c_str() ,
+ authEntry.password.c_str(),
+ lastLoginStr.c_str()
+ };
+
+ beginSave();
+
+ PGresult *result = execPrepared("auth_create", 3, values, false, false);
+
+ int numrows = PQntuples(result);
+ if (numrows == 0) {
+ errorstream << "Strange behaviour on auth creation, no ID returned." << std::endl;
+ PQclear(result);
+ rollback();
+ return false;
+ }
+
+ authEntry.id = pg_to_uint(result, 0, 0);
+ PQclear(result);
+
+ writePrivileges(authEntry);
+
+ endSave();
+ return true;
+}
+
+bool AuthDatabasePostgreSQL::deleteAuth(const std::string &name)
+{
+ pingDatabase();
+
+ const char *values[] = { name.c_str() };
+ execPrepared("auth_delete", 1, values);
+
+ // privileges deleted by foreign key on delete cascade
+ return true;
+}
+
+void AuthDatabasePostgreSQL::listNames(std::vector<std::string> &res)
+{
+ pingDatabase();
+
+ PGresult *results = execPrepared("auth_list_names", 0,
+ NULL, NULL, NULL, false, false);
+
+ int numrows = PQntuples(results);
+
+ for (int row = 0; row < numrows; ++row)
+ res.emplace_back(PQgetvalue(results, row, 0));
+
+ PQclear(results);
+}
+
+void AuthDatabasePostgreSQL::reload()
+{
+ // noop for PgSQL
+}
+
+void AuthDatabasePostgreSQL::writePrivileges(const AuthEntry &authEntry)
+{
+ std::string authIdStr = itos(authEntry.id);
+ const char *values[] = { authIdStr.c_str() };
+ execPrepared("auth_delete_privs", 1, values);
+
+ for (const std::string &privilege : authEntry.privileges) {
+ const char *values[] = { authIdStr.c_str(), privilege.c_str() };
+ execPrepared("auth_write_privs", 2, values);
+ }
+}
+
+
#endif // USE_POSTGRESQL