Player data to Database (#5475)

* Player data to Database

Add player data into databases (SQLite3 & PG only)

PostgreSQL & SQLite: better POO Design for databases

Add --migrate-players argument to server + deprecation warning

* Remove players directory if empty

1 files changed, 400 insertions, 70 deletions

diff --git a/src/database-postgresql.cpp b/src/database-postgresql.cpp
index 83678fd52..a6b62bad5 100644
--- a/src/database-postgresql.cpp
+++ b/src/database-postgresql.cpp
@@ -39,13 +39,15 @@ with this program; if not, write to the Free Software Foundation, Inc.,
 #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"
@@ -57,8 +59,6 @@ Database_PostgreSQL::Database_PostgreSQL(const Settings &conf) :
 			"DELETE rights on the database.\n"
 			"Don't create mt_user as a SUPERUSER!");
 	}
-
-	connectToDatabase();
 }
 
 Database_PostgreSQL::~Database_PostgreSQL()
@@ -118,40 +118,6 @@ bool Database_PostgreSQL::initialized() const
 	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);
@@ -173,30 +139,21 @@ PGresult *Database_PostgreSQL::checkResults(PGresult *result, bool clear)
 	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();
@@ -208,14 +165,70 @@ void Database_PostgreSQL::endSave()
 	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;
 	}
 
@@ -232,7 +245,7 @@ bool Database_PostgreSQL::saveBlock(const v3s16 &pos,
 	};
 	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 {
@@ -241,8 +254,7 @@ bool Database_PostgreSQL::saveBlock(const v3s16 &pos,
 	return true;
 }
 
-void Database_PostgreSQL::loadBlock(const v3s16 &pos,
-		std::string *block)
+void MapDatabasePostgreSQL::loadBlock(const v3s16 &pos, std::string *block)
 {
 	verifyDatabase();
 
@@ -256,19 +268,17 @@ void Database_PostgreSQL::loadBlock(const v3s16 &pos,
 	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();
 
@@ -286,18 +296,338 @@ bool Database_PostgreSQL::deleteBlock(const v3s16 &pos)
 	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);
 }