#include "log.h"
#include "exceptions.h"
#include "settings.h"
+#include "content_sao.h"
+#include "remoteplayer.h"
-Database_PostgreSQL::Database_PostgreSQL(const Settings &conf) :
- m_connect_string(""),
+Database_PostgreSQL::Database_PostgreSQL(const std::string &connect_string) :
+ m_connect_string(connect_string),
m_conn(NULL),
m_pgversion(0)
{
- if (!conf.getNoEx("pgsql_connection", m_connect_string)) {
+ if (m_connect_string.empty()) {
throw SettingNotFoundException(
"Set pgsql_connection string in world.mt to "
"use the postgresql backend\n"
"DELETE rights on the database.\n"
"Don't create mt_user as a SUPERUSER!");
}
-
- connectToDatabase();
}
Database_PostgreSQL::~Database_PostgreSQL()
return (PQstatus(m_conn) == CONNECTION_OK);
}
-void Database_PostgreSQL::initStatements()
-{
- prepareStatement("read_block",
- "SELECT data FROM blocks "
- "WHERE posX = $1::int4 AND posY = $2::int4 AND "
- "posZ = $3::int4");
-
- if (m_pgversion < 90500) {
- prepareStatement("write_block_insert",
- "INSERT INTO blocks (posX, posY, posZ, data) SELECT "
- "$1::int4, $2::int4, $3::int4, $4::bytea "
- "WHERE NOT EXISTS (SELECT true FROM blocks "
- "WHERE posX = $1::int4 AND posY = $2::int4 AND "
- "posZ = $3::int4)");
-
- prepareStatement("write_block_update",
- "UPDATE blocks SET data = $4::bytea "
- "WHERE posX = $1::int4 AND posY = $2::int4 AND "
- "posZ = $3::int4");
- } else {
- prepareStatement("write_block",
- "INSERT INTO blocks (posX, posY, posZ, data) VALUES "
- "($1::int4, $2::int4, $3::int4, $4::bytea) "
- "ON CONFLICT ON CONSTRAINT blocks_pkey DO "
- "UPDATE SET data = $4::bytea");
- }
-
- prepareStatement("delete_block", "DELETE FROM blocks WHERE "
- "posX = $1::int4 AND posY = $2::int4 AND posZ = $3::int4");
-
- prepareStatement("list_all_loadable_blocks",
- "SELECT posX, posY, posZ FROM blocks");
-}
-
PGresult *Database_PostgreSQL::checkResults(PGresult *result, bool clear)
{
ExecStatusType statusType = PQresultStatus(result);
return result;
}
-void Database_PostgreSQL::createDatabase()
+void Database_PostgreSQL::createTableIfNotExists(const std::string &table_name,
+ const std::string &definition)
{
- PGresult *result = checkResults(PQexec(m_conn,
- "SELECT relname FROM pg_class WHERE relname='blocks';"),
- false);
+ std::string sql_check_table = "SELECT relname FROM pg_class WHERE relname='" +
+ table_name + "';";
+ PGresult *result = checkResults(PQexec(m_conn, sql_check_table.c_str()), false);
// If table doesn't exist, create it
if (!PQntuples(result)) {
- static const char* dbcreate_sql = "CREATE TABLE blocks ("
- "posX INT NOT NULL,"
- "posY INT NOT NULL,"
- "posZ INT NOT NULL,"
- "data BYTEA,"
- "PRIMARY KEY (posX,posY,posZ)"
- ");";
- checkResults(PQexec(m_conn, dbcreate_sql));
+ checkResults(PQexec(m_conn, definition.c_str()));
}
PQclear(result);
-
- infostream << "PostgreSQL: Game Database was inited." << std::endl;
}
-
void Database_PostgreSQL::beginSave()
{
verifyDatabase();
checkResults(PQexec(m_conn, "COMMIT;"));
}
-bool Database_PostgreSQL::saveBlock(const v3s16 &pos,
- const std::string &data)
+MapDatabasePostgreSQL::MapDatabasePostgreSQL(const std::string &connect_string):
+ Database_PostgreSQL(connect_string),
+ MapDatabase()
+{
+ connectToDatabase();
+}
+
+
+void MapDatabasePostgreSQL::createDatabase()
+{
+ createTableIfNotExists("blocks",
+ "CREATE TABLE blocks ("
+ "posX INT NOT NULL,"
+ "posY INT NOT NULL,"
+ "posZ INT NOT NULL,"
+ "data BYTEA,"
+ "PRIMARY KEY (posX,posY,posZ)"
+ ");"
+ );
+
+ infostream << "PostgreSQL: Map Database was initialized." << std::endl;
+}
+
+void MapDatabasePostgreSQL::initStatements()
+{
+ prepareStatement("read_block",
+ "SELECT data FROM blocks "
+ "WHERE posX = $1::int4 AND posY = $2::int4 AND "
+ "posZ = $3::int4");
+
+ if (getPGVersion() < 90500) {
+ prepareStatement("write_block_insert",
+ "INSERT INTO blocks (posX, posY, posZ, data) SELECT "
+ "$1::int4, $2::int4, $3::int4, $4::bytea "
+ "WHERE NOT EXISTS (SELECT true FROM blocks "
+ "WHERE posX = $1::int4 AND posY = $2::int4 AND "
+ "posZ = $3::int4)");
+
+ prepareStatement("write_block_update",
+ "UPDATE blocks SET data = $4::bytea "
+ "WHERE posX = $1::int4 AND posY = $2::int4 AND "
+ "posZ = $3::int4");
+ } else {
+ prepareStatement("write_block",
+ "INSERT INTO blocks (posX, posY, posZ, data) VALUES "
+ "($1::int4, $2::int4, $3::int4, $4::bytea) "
+ "ON CONFLICT ON CONSTRAINT blocks_pkey DO "
+ "UPDATE SET data = $4::bytea");
+ }
+
+ prepareStatement("delete_block", "DELETE FROM blocks WHERE "
+ "posX = $1::int4 AND posY = $2::int4 AND posZ = $3::int4");
+
+ prepareStatement("list_all_loadable_blocks",
+ "SELECT posX, posY, posZ FROM blocks");
+}
+
+bool MapDatabasePostgreSQL::saveBlock(const v3s16 &pos, const std::string &data)
{
// Verify if we don't overflow the platform integer with the mapblock size
if (data.size() > INT_MAX) {
errorstream << "Database_PostgreSQL::saveBlock: Data truncation! "
- << "data.size() over 0xFFFF (== " << data.size()
- << ")" << std::endl;
+ << "data.size() over 0xFFFFFFFF (== " << data.size()
+ << ")" << std::endl;
return false;
}
};
const int argFmt[] = { 1, 1, 1, 1 };
- if (m_pgversion < 90500) {
+ if (getPGVersion() < 90500) {
execPrepared("write_block_update", ARRLEN(args), args, argLen, argFmt);
execPrepared("write_block_insert", ARRLEN(args), args, argLen, argFmt);
} else {
return true;
}
-void Database_PostgreSQL::loadBlock(const v3s16 &pos,
- std::string *block)
+void MapDatabasePostgreSQL::loadBlock(const v3s16 &pos, std::string *block)
{
verifyDatabase();
const int argFmt[] = { 1, 1, 1 };
PGresult *results = execPrepared("read_block", ARRLEN(args), args,
- argLen, argFmt, false);
+ argLen, argFmt, false);
*block = "";
- if (PQntuples(results)) {
- *block = std::string(PQgetvalue(results, 0, 0),
- PQgetlength(results, 0, 0));
- }
+ if (PQntuples(results))
+ *block = std::string(PQgetvalue(results, 0, 0), PQgetlength(results, 0, 0));
PQclear(results);
}
-bool Database_PostgreSQL::deleteBlock(const v3s16 &pos)
+bool MapDatabasePostgreSQL::deleteBlock(const v3s16 &pos)
{
verifyDatabase();
return true;
}
-void Database_PostgreSQL::listAllLoadableBlocks(std::vector<v3s16> &dst)
+void MapDatabasePostgreSQL::listAllLoadableBlocks(std::vector<v3s16> &dst)
{
verifyDatabase();
PGresult *results = execPrepared("list_all_loadable_blocks", 0,
- NULL, NULL, NULL, false, false);
+ NULL, NULL, NULL, false, false);
int numrows = PQntuples(results);
- for (int row = 0; row < numrows; ++row) {
+ for (int row = 0; row < numrows; ++row)
dst.push_back(pg_to_v3s16(results, 0, 0));
+
+ PQclear(results);
+}
+
+/*
+ * Player Database
+ */
+PlayerDatabasePostgreSQL::PlayerDatabasePostgreSQL(const std::string &connect_string):
+ Database_PostgreSQL(connect_string),
+ PlayerDatabase()
+{
+ connectToDatabase();
+}
+
+
+void PlayerDatabasePostgreSQL::createDatabase()
+{
+ createTableIfNotExists("player",
+ "CREATE TABLE player ("
+ "name VARCHAR(60) NOT NULL,"
+ "pitch NUMERIC(15, 7) NOT NULL,"
+ "yaw NUMERIC(15, 7) NOT NULL,"
+ "posX NUMERIC(15, 7) NOT NULL,"
+ "posY NUMERIC(15, 7) NOT NULL,"
+ "posZ NUMERIC(15, 7) NOT NULL,"
+ "hp INT NOT NULL,"
+ "breath INT NOT NULL,"
+ "creation_date TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT NOW(),"
+ "modification_date TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT NOW(),"
+ "PRIMARY KEY (name)"
+ ");"
+ );
+
+ createTableIfNotExists("player_inventories",
+ "CREATE TABLE player_inventories ("
+ "player VARCHAR(60) NOT NULL,"
+ "inv_id INT NOT NULL,"
+ "inv_width INT NOT NULL,"
+ "inv_name TEXT NOT NULL DEFAULT '',"
+ "inv_size INT NOT NULL,"
+ "PRIMARY KEY(player, inv_id),"
+ "CONSTRAINT player_inventories_fkey FOREIGN KEY (player) REFERENCES "
+ "player (name) ON DELETE CASCADE"
+ ");"
+ );
+
+ createTableIfNotExists("player_inventory_items",
+ "CREATE TABLE player_inventory_items ("
+ "player VARCHAR(60) NOT NULL,"
+ "inv_id INT NOT NULL,"
+ "slot_id INT NOT NULL,"
+ "item TEXT NOT NULL DEFAULT '',"
+ "PRIMARY KEY(player, inv_id, slot_id),"
+ "CONSTRAINT player_inventory_items_fkey FOREIGN KEY (player) REFERENCES "
+ "player (name) ON DELETE CASCADE"
+ ");"
+ );
+
+ createTableIfNotExists("player_metadata",
+ "CREATE TABLE player_metadata ("
+ "player VARCHAR(60) NOT NULL,"
+ "attr VARCHAR(256) NOT NULL,"
+ "value TEXT,"
+ "PRIMARY KEY(player, attr),"
+ "CONSTRAINT player_metadata_fkey FOREIGN KEY (player) REFERENCES "
+ "player (name) ON DELETE CASCADE"
+ ");"
+ );
+
+ infostream << "PostgreSQL: Player Database was inited." << std::endl;
+}
+
+void PlayerDatabasePostgreSQL::initStatements()
+{
+ if (getPGVersion() < 90500) {
+ prepareStatement("create_player",
+ "INSERT INTO player(name, pitch, yaw, posX, posY, posZ, hp, breath) VALUES "
+ "($1, $2, $3, $4, $5, $6, $7::int, $8::int)");
+
+ prepareStatement("update_player",
+ "UPDATE SET pitch = $2, yaw = $3, posX = $4, posY = $5, posZ = $6, hp = $7::int, "
+ "breath = $8::int, modification_date = NOW() WHERE name = $1");
+ } else {
+ prepareStatement("save_player",
+ "INSERT INTO player(name, pitch, yaw, posX, posY, posZ, hp, breath) VALUES "
+ "($1, $2, $3, $4, $5, $6, $7::int, $8::int)"
+ "ON CONFLICT ON CONSTRAINT player_pkey DO UPDATE SET pitch = $2, yaw = $3, "
+ "posX = $4, posY = $5, posZ = $6, hp = $7::int, breath = $8::int, "
+ "modification_date = NOW()");
+ }
+
+ prepareStatement("remove_player", "DELETE FROM player WHERE name = $1");
+
+ prepareStatement("load_player_list", "SELECT name FROM player");
+
+ prepareStatement("remove_player_inventories",
+ "DELETE FROM player_inventories WHERE player = $1");
+
+ prepareStatement("remove_player_inventory_items",
+ "DELETE FROM player_inventory_items WHERE player = $1");
+
+ prepareStatement("add_player_inventory",
+ "INSERT INTO player_inventories (player, inv_id, inv_width, inv_name, inv_size) VALUES "
+ "($1, $2::int, $3::int, $4, $5::int)");
+
+ prepareStatement("add_player_inventory_item",
+ "INSERT INTO player_inventory_items (player, inv_id, slot_id, item) VALUES "
+ "($1, $2::int, $3::int, $4)");
+
+ prepareStatement("load_player_inventories",
+ "SELECT inv_id, inv_width, inv_name, inv_size FROM player_inventories "
+ "WHERE player = $1 ORDER BY inv_id");
+
+ prepareStatement("load_player_inventory_items",
+ "SELECT slot_id, item FROM player_inventory_items WHERE "
+ "player = $1 AND inv_id = $2::int");
+
+ prepareStatement("load_player",
+ "SELECT pitch, yaw, posX, posY, posZ, hp, breath FROM player WHERE name = $1");
+
+ prepareStatement("remove_player_metadata",
+ "DELETE FROM player_metadata WHERE player = $1");
+
+ prepareStatement("save_player_metadata",
+ "INSERT INTO player_metadata (player, attr, value) VALUES ($1, $2, $3)");
+
+ prepareStatement("load_player_metadata",
+ "SELECT attr, value FROM player_metadata WHERE player = $1");
+
+}
+
+bool PlayerDatabasePostgreSQL::playerDataExists(const std::string &playername)
+{
+ verifyDatabase();
+
+ const char *values[] = { playername.c_str() };
+ PGresult *results = execPrepared("load_player", 1, values, false);
+
+ bool res = (PQntuples(results) > 0);
+ PQclear(results);
+ return res;
+}
+
+void PlayerDatabasePostgreSQL::savePlayer(RemotePlayer *player)
+{
+ PlayerSAO* sao = player->getPlayerSAO();
+ if (!sao)
+ return;
+
+ verifyDatabase();
+
+ v3f pos = sao->getBasePosition();
+ std::string pitch = ftos(sao->getPitch());
+ std::string yaw = ftos(sao->getYaw());
+ std::string posx = ftos(pos.X);
+ std::string posy = ftos(pos.Y);
+ std::string posz = ftos(pos.Z);
+ std::string hp = itos(sao->getHP());
+ std::string breath = itos(sao->getBreath());
+ const char *values[] = {
+ player->getName(),
+ pitch.c_str(),
+ yaw.c_str(),
+ posx.c_str(), posy.c_str(), posz.c_str(),
+ hp.c_str(),
+ breath.c_str()
+ };
+
+ const char* rmvalues[] = { player->getName() };
+ beginSave();
+
+ if (getPGVersion() < 90500) {
+ if (!playerDataExists(player->getName()))
+ execPrepared("create_player", 8, values, true, false);
+ else
+ execPrepared("update_player", 8, values, true, false);
+ }
+ else
+ execPrepared("save_player", 8, values, true, false);
+
+ // Write player inventories
+ execPrepared("remove_player_inventories", 1, rmvalues);
+ execPrepared("remove_player_inventory_items", 1, rmvalues);
+
+ std::vector<const InventoryList*> inventory_lists = sao->getInventory()->getLists();
+ for (u16 i = 0; i < inventory_lists.size(); i++) {
+ const InventoryList* list = inventory_lists[i];
+ std::string name = list->getName(), width = itos(list->getWidth()),
+ inv_id = itos(i), lsize = itos(list->getSize());
+
+ const char* inv_values[] = {
+ player->getName(),
+ inv_id.c_str(),
+ width.c_str(),
+ name.c_str(),
+ lsize.c_str()
+ };
+ execPrepared("add_player_inventory", 5, inv_values);
+
+ for (u32 j = 0; j < list->getSize(); j++) {
+ std::ostringstream os;
+ list->getItem(j).serialize(os);
+ std::string itemStr = os.str(), slotId = itos(j);
+
+ const char* invitem_values[] = {
+ player->getName(),
+ inv_id.c_str(),
+ slotId.c_str(),
+ itemStr.c_str()
+ };
+ execPrepared("add_player_inventory_item", 4, invitem_values);
+ }
+ }
+
+ execPrepared("remove_player_metadata", 1, rmvalues);
+ const PlayerAttributes &attrs = sao->getExtendedAttributes();
+ for (PlayerAttributes::const_iterator it = attrs.begin(); it != attrs.end(); ++it) {
+ const char *meta_values[] = {
+ player->getName(),
+ it->first.c_str(),
+ it->second.c_str()
+ };
+ execPrepared("save_player_metadata", 3, meta_values);
}
+ endSave();
+}
+
+bool PlayerDatabasePostgreSQL::loadPlayer(RemotePlayer *player, PlayerSAO *sao)
+{
+ sanity_check(sao);
+ verifyDatabase();
+
+ const char *values[] = { player->getName() };
+ PGresult *results = execPrepared("load_player", 1, values, false, false);
+
+ // Player not found, return not found
+ if (!PQntuples(results)) {
+ PQclear(results);
+ return false;
+ }
+
+ sao->setPitch(pg_to_float(results, 0, 0));
+ sao->setYaw(pg_to_float(results, 0, 1));
+ sao->setBasePosition(v3f(
+ pg_to_float(results, 0, 2),
+ pg_to_float(results, 0, 3),
+ pg_to_float(results, 0, 4))
+ );
+ sao->setHPRaw((s16) pg_to_int(results, 0, 5));
+ sao->setBreath((u16) pg_to_int(results, 0, 6), false);
+
+ PQclear(results);
+
+ // Load inventory
+ results = execPrepared("load_player_inventories", 1, values, false, false);
+
+ int resultCount = PQntuples(results);
+
+ for (int row = 0; row < resultCount; ++row) {
+ InventoryList* invList = player->inventory.
+ addList(PQgetvalue(results, row, 2), pg_to_uint(results, row, 3));
+ invList->setWidth(pg_to_uint(results, row, 1));
+
+ u32 invId = pg_to_uint(results, row, 0);
+ std::string invIdStr = itos(invId);
+
+ const char* values2[] = {
+ player->getName(),
+ invIdStr.c_str()
+ };
+ PGresult *results2 = execPrepared("load_player_inventory_items", 2,
+ values2, false, false);
+
+ int resultCount2 = PQntuples(results2);
+ for (int row2 = 0; row2 < resultCount2; row2++) {
+ const std::string itemStr = PQgetvalue(results2, row2, 1);
+ if (itemStr.length() > 0) {
+ ItemStack stack;
+ stack.deSerialize(itemStr);
+ invList->addItem(pg_to_uint(results2, row2, 0), stack);
+ }
+ }
+ PQclear(results2);
+ }
+
+ PQclear(results);
+
+ results = execPrepared("load_player_metadata", 1, values, false);
+
+ int numrows = PQntuples(results);
+ for (int row = 0; row < numrows; row++) {
+ sao->setExtendedAttribute(PQgetvalue(results, row, 0),PQgetvalue(results, row, 1));
+ }
+
+ PQclear(results);
+
+ return true;
+}
+
+bool PlayerDatabasePostgreSQL::removePlayer(const std::string &name)
+{
+ if (!playerDataExists(name))
+ return false;
+
+ verifyDatabase();
+
+ const char *values[] = { name.c_str() };
+ execPrepared("remove_player", 1, values);
+
+ return true;
+}
+
+void PlayerDatabasePostgreSQL::listPlayers(std::vector<std::string> &res)
+{
+ verifyDatabase();
+
+ PGresult *results = execPrepared("load_player_list", 0, NULL, false);
+
+ int numrows = PQntuples(results);
+ for (int row = 0; row < numrows; row++)
+ res.push_back(PQgetvalue(results, row, 0));
PQclear(results);
}
projects / dragonfireclient.git / blobdiff