change how guild data is stored

5 files changed, 144 insertions, 79 deletions

diff --git a/src/database_postgres.cc b/src/database_postgres.cc
index b53e780..d42b5a7 100644
--- a/src/database_postgres.cc
+++ b/src/database_postgres.cc
@@ -1771,8 +1771,7 @@ bool GetCharacterID(TDatabase *Database, int WorldID, const char *CharacterName,
 bool GetCharacterLoginData(TDatabase *Database, const char *CharacterName, TCharacterLoginData *Character){
 	ASSERT(Database != NULL && CharacterName != NULL && Character != NULL);
 	const char *Stmt = PrepareQuery(Database,
-			"SELECT WorldID, CharacterID, AccountID, Name,"
-				" Sex, Guild, Rank, Title, Deleted"
+			"SELECT WorldID, CharacterID, AccountID, Name, Sex, Deleted"
 			" FROM Characters WHERE Name = $1::TEXT");
 	if(Stmt == NULL){
 		LOG_ERR("Failed to prepare query");
@@ -1797,10 +1796,7 @@ bool GetCharacterLoginData(TDatabase *Database, const char *CharacterName, TChar
 		Character->AccountID = GetResultInt(Result, 0, 2);
 		StringBufCopy(Character->Name, GetResultText(Result, 0, 3));
 		Character->Sex = GetResultInt(Result, 0, 4);
-		StringBufCopy(Character->Guild, GetResultText(Result, 0, 5));
-		StringBufCopy(Character->Rank, GetResultText(Result, 0, 6));
-		StringBufCopy(Character->Title, GetResultText(Result, 0, 7));
-		Character->Deleted = GetResultBool(Result, 0, 8);
+		Character->Deleted = GetResultBool(Result, 0, 5);
 	}
 
 	return true;
@@ -1809,7 +1805,7 @@ bool GetCharacterLoginData(TDatabase *Database, const char *CharacterName, TChar
 bool GetCharacterProfile(TDatabase *Database, const char *CharacterName, TCharacterProfile *Character){
 	ASSERT(Database != NULL && CharacterName != NULL && Character != NULL);
 	const char *Stmt = PrepareQuery(Database,
-			"SELECT C.Name, W.Name, C.Sex, C.Guild, C.Rank, C.Title, C.Level,"
+			"SELECT C.CharacterID, C.Name, W.Name, C.Sex, C.Level,"
 				" C.Profession, C.Residence, C.LastLoginTime, C.IsOnline,"
 				" C.Deleted, GREATEST(A.PremiumEnd - CURRENT_TIMESTAMP, '0')"
 			" FROM Characters AS C"
@@ -1817,7 +1813,7 @@ bool GetCharacterProfile(TDatabase *Database, const char *CharacterName, TCharac
 			" LEFT JOIN Accounts AS A ON A.AccountID = C.AccountID"
 			" LEFT JOIN CharacterRights AS R"
 				" ON R.CharacterID = C.CharacterID"
-				" AND R.Name = 'NO_STATISTICS'"
+					" AND R.Name = 'NO_STATISTICS'"
 			" WHERE C.Name = $1::TEXT AND R.Name IS NULL");
 	if(Stmt == NULL){
 		LOG_ERR("Failed to prepare query");
@@ -1837,19 +1833,17 @@ bool GetCharacterProfile(TDatabase *Database, const char *CharacterName, TCharac
 
 	memset(Character, 0, sizeof(TCharacterProfile));
 	if(PQntuples(Result) > 0){
-		StringBufCopy(Character->Name, GetResultText(Result, 0, 0));
-		StringBufCopy(Character->World, GetResultText(Result, 0, 1));
-		Character->Sex = GetResultInt(Result, 0, 2);
-		StringBufCopy(Character->Guild, GetResultText(Result, 0, 3));
-		StringBufCopy(Character->Rank, GetResultText(Result, 0, 4));
-		StringBufCopy(Character->Title, GetResultText(Result, 0, 5));
-		Character->Level = GetResultInt(Result, 0, 6);
-		StringBufCopy(Character->Profession, GetResultText(Result, 0, 7));
-		StringBufCopy(Character->Residence, GetResultText(Result, 0, 8));
-		Character->LastLogin = GetResultTimestamp(Result, 0, 9);
-		Character->Online = GetResultBool(Result, 0, 10);
-		Character->Deleted = GetResultBool(Result, 0, 11);
-		Character->PremiumDays = RoundSecondsToDays(GetResultInterval(Result, 0, 12));
+		Character->CharacterID = GetResultInt(Result, 0, 0);
+		StringBufCopy(Character->Name, GetResultText(Result, 0, 1));
+		StringBufCopy(Character->World, GetResultText(Result, 0, 2));
+		Character->Sex = GetResultInt(Result, 0, 3);
+		Character->Level = GetResultInt(Result, 0, 4);
+		StringBufCopy(Character->Profession, GetResultText(Result, 0, 5));
+		StringBufCopy(Character->Residence, GetResultText(Result, 0, 6));
+		Character->LastLogin = GetResultTimestamp(Result, 0, 7);
+		Character->Online = GetResultBool(Result, 0, 8);
+		Character->Deleted = GetResultBool(Result, 0, 9);
+		Character->PremiumDays = RoundSecondsToDays(GetResultInterval(Result, 0, 10));
 	}
 
 	return true;
@@ -1915,8 +1909,8 @@ bool GetGuildLeaderStatus(TDatabase *Database, int WorldID, int CharacterID, boo
 	ASSERT(Database != NULL && GuildLeader != NULL);
 	// NOTE(fusion): Same as `DecrementIsOnline`.
 	const char *Stmt = PrepareQuery(Database,
-			"SELECT Guild, Rank FROM Characters"
-			" WHERE WorldID = $1::INTEGER AND CharacterID = $2::INTEGER");
+			"SELECT 1 FROM Guilds"
+			" WHERE WorldID = $1::INTEGER AND LeaderID = $2::INTEGER");
 	if(Stmt == NULL){
 		LOG_ERR("Failed to prepare query");
 		return false;
@@ -1934,16 +1928,8 @@ bool GetGuildLeaderStatus(TDatabase *Database, int WorldID, int CharacterID, boo
 		return false;
 	}
 
-	*GuildLeader = false;
-	if(PQntuples(Result) > 0){
-		const char *Guild = GetResultText(Result, 0, 0);
-		const char *Rank = GetResultText(Result, 0, 1);
-		if(Guild != NULL && !StringEmpty(Guild) && Rank != NULL && StringEqCI(Rank, "Leader")){
-			*GuildLeader = true;
-		}
-	}
-
-	return false;
+	*GuildLeader = (PQntuples(Result) > 0);
+	return true;
 }
 
 bool IncrementIsOnline(TDatabase *Database, int WorldID, int CharacterID){
@@ -2341,6 +2327,46 @@ bool GetIPAddressFailedLoginAttempts(TDatabase *Database, int IPAddress, int Tim
 	return true;
 }
 
+// Guild Tables
+//==============================================================================
+bool GetCharacterGuildData(TDatabase *Database, int CharacterID, TCharacterGuildData *GuildData){
+	ASSERT(Database != NULL && GuildData != NULL);
+	const char *Stmt = PrepareQuery(Database,
+			"SELECT G.GuildID, R.Rank, G.Name, R.Name, M.Title"
+			" FROM Characters AS C"
+			" LEFT JOIN GuildMembers AS M ON M.CharacterID = C.CharacterID"
+			" LEFT JOIN Guilds AS G ON G.GuildID = M.GuildID"
+			" LEFT JOIN GuildRanks AS R"
+				" ON R.GuildID = M.GuildID AND R.Rank = M.Rank"
+			" WHERE C.CharacterID = $1::INTEGER");
+	if(Stmt == NULL){
+		LOG_ERR("Failed to prepare query");
+		return false;
+	}
+
+	ParamBuffer Params = {};
+	ParamBegin(&Params, 1, 1);
+	ParamInt(&Params, CharacterID);
+	PGresult *Result = PQexecPrepared(Database->Handle, Stmt, Params.NumParams,
+							Params.Values, Params.Lengths, Params.Formats, 1);
+	AutoResultClear ResultGuard(Result);
+	if(PQresultStatus(Result) != PGRES_TUPLES_OK){
+		LOG_ERR("Failed to execute query: %s", PQerrorMessage(Database->Handle));
+		return false;
+	}
+
+	memset(GuildData, 0, sizeof(TCharacterGuildData));
+	if(PQntuples(Result) > 0){
+		GuildData->GuildID = GetResultInt(Result, 0, 0);
+		GuildData->Rank = GetResultInt(Result, 0, 1);
+		StringBufCopy(GuildData->GuildName, GetResultText(Result, 0, 2));
+		StringBufCopy(GuildData->RankName, GetResultText(Result, 0, 3));
+		StringBufCopy(GuildData->Title, GetResultText(Result, 0, 4));
+	}
+
+	return true;
+}
+
 // House Tables
 //==============================================================================
 bool FinishHouseAuctions(TDatabase *Database, int WorldID, DynamicArray<THouseAuction> *Auctions){
diff --git a/src/database_sqlite.cc b/src/database_sqlite.cc
index 243011c..a3aac0d 100644
--- a/src/database_sqlite.cc
+++ b/src/database_sqlite.cc
@@ -1022,8 +1022,7 @@ bool GetCharacterID(TDatabase *Database, int WorldID, const char *CharacterName,
 bool GetCharacterLoginData(TDatabase *Database, const char *CharacterName, TCharacterLoginData *Character){
 	ASSERT(Database != NULL && CharacterName != NULL && Character != NULL);
 	sqlite3_stmt *Stmt = PrepareQuery(Database,
-			"SELECT WorldID, CharacterID, AccountID, Name,"
-				" Sex, Guild, Rank, Title, Deleted"
+			"SELECT WorldID, CharacterID, AccountID, Name, Sex, Deleted"
 			" FROM Characters WHERE Name = ?1");
 	if(Stmt == NULL){
 		LOG_ERR("Failed to prepare query");
@@ -1049,10 +1048,7 @@ bool GetCharacterLoginData(TDatabase *Database, const char *CharacterName, TChar
 		Character->AccountID = sqlite3_column_int(Stmt, 2);
 		StringBufCopy(Character->Name, (const char*)sqlite3_column_text(Stmt, 3));
 		Character->Sex = sqlite3_column_int(Stmt, 4);
-		StringBufCopy(Character->Guild, (const char*)sqlite3_column_text(Stmt, 5));
-		StringBufCopy(Character->Rank, (const char*)sqlite3_column_text(Stmt, 6));
-		StringBufCopy(Character->Title, (const char*)sqlite3_column_text(Stmt, 7));
-		Character->Deleted = (sqlite3_column_int(Stmt, 8) != 0);
+		Character->Deleted = (sqlite3_column_int(Stmt, 5) != 0);
 	}
 
 	return true;
@@ -1061,7 +1057,7 @@ bool GetCharacterLoginData(TDatabase *Database, const char *CharacterName, TChar
 bool GetCharacterProfile(TDatabase *Database, const char *CharacterName, TCharacterProfile *Character){
 	ASSERT(Database != NULL && CharacterName != NULL && Character != NULL);
 	sqlite3_stmt *Stmt = PrepareQuery(Database,
-			"SELECT C.Name, W.Name, C.Sex, C.Guild, C.Rank, C.Title, C.Level,"
+			"SELECT C.CharacterID, C.Name, W.Name, C.Sex, C.Level,"
 				" C.Profession, C.Residence, C.LastLoginTime, C.IsOnline,"
 				" C.Deleted, MAX(A.PremiumEnd - UNIXEPOCH(), 0)"
 			" FROM Characters AS C"
@@ -1069,7 +1065,7 @@ bool GetCharacterProfile(TDatabase *Database, const char *CharacterName, TCharac
 			" LEFT JOIN Accounts AS A ON A.AccountID = C.AccountID"
 			" LEFT JOIN CharacterRights AS R"
 				" ON R.CharacterID = C.CharacterID"
-				" AND R.Name = 'NO_STATISTICS'"
+					" AND R.Name = 'NO_STATISTICS'"
 			" WHERE C.Name = ?1 AND R.Name IS NULL");
 	if(Stmt == NULL){
 		LOG_ERR("Failed to prepare query");
@@ -1090,19 +1086,17 @@ bool GetCharacterProfile(TDatabase *Database, const char *CharacterName, TCharac
 
 	memset(Character, 0, sizeof(TCharacterProfile));
 	if(ErrorCode == SQLITE_ROW){
-		StringBufCopy(Character->Name, (const char*)sqlite3_column_text(Stmt, 0));
-		StringBufCopy(Character->World, (const char*)sqlite3_column_text(Stmt, 1));
-		Character->Sex = sqlite3_column_int(Stmt, 2);
-		StringBufCopy(Character->Guild, (const char*)sqlite3_column_text(Stmt, 3));
-		StringBufCopy(Character->Rank, (const char*)sqlite3_column_text(Stmt, 4));
-		StringBufCopy(Character->Title, (const char*)sqlite3_column_text(Stmt, 5));
-		Character->Level = sqlite3_column_int(Stmt, 6);
-		StringBufCopy(Character->Profession, (const char*)sqlite3_column_text(Stmt, 7));
-		StringBufCopy(Character->Residence, (const char*)sqlite3_column_text(Stmt, 8));
-		Character->LastLogin = sqlite3_column_int(Stmt, 9);
-		Character->Online = (sqlite3_column_int(Stmt, 10) != 0);
-		Character->Deleted = (sqlite3_column_int(Stmt, 11) != 0);
-		Character->PremiumDays = RoundSecondsToDays(sqlite3_column_int(Stmt, 12));
+		Character->CharacterID = sqlite3_column_int(Stmt, 0);
+		StringBufCopy(Character->Name, (const char*)sqlite3_column_text(Stmt, 1));
+		StringBufCopy(Character->World, (const char*)sqlite3_column_text(Stmt, 2));
+		Character->Sex = sqlite3_column_int(Stmt, 3);
+		Character->Level = sqlite3_column_int(Stmt, 4);
+		StringBufCopy(Character->Profession, (const char*)sqlite3_column_text(Stmt, 5));
+		StringBufCopy(Character->Residence, (const char*)sqlite3_column_text(Stmt, 6));
+		Character->LastLogin = sqlite3_column_int(Stmt, 7);
+		Character->Online = (sqlite3_column_int(Stmt, 8) != 0);
+		Character->Deleted = (sqlite3_column_int(Stmt, 9) != 0);
+		Character->PremiumDays = RoundSecondsToDays(sqlite3_column_int(Stmt, 10));
 	}
 
 	return true;
@@ -1168,8 +1162,8 @@ bool GetGuildLeaderStatus(TDatabase *Database, int WorldID, int CharacterID, boo
 	ASSERT(Database != NULL && GuildLeader != NULL);
 	// NOTE(fusion): Same as `DecrementIsOnline`.
 	sqlite3_stmt *Stmt = PrepareQuery(Database,
-			"SELECT Guild, Rank FROM Characters"
-			" WHERE WorldID = ?1 AND CharacterID = ?2");
+			"SELECT 1 FROM Guilds"
+			" WHERE WorldID = ?1 AND LeaderID = ?2");
 	if(Stmt == NULL){
 		LOG_ERR("Failed to prepare query");
 		return false;
@@ -1188,15 +1182,7 @@ bool GetGuildLeaderStatus(TDatabase *Database, int WorldID, int CharacterID, boo
 		return false;
 	}
 
-	*GuildLeader = false;
-	if(ErrorCode == SQLITE_ROW){
-		const char *Guild = (const char*)sqlite3_column_text(Stmt, 0);
-		const char *Rank = (const char*)sqlite3_column_text(Stmt, 1);
-		if(Guild != NULL && !StringEmpty(Guild) && Rank != NULL && StringEqCI(Rank, "Leader")){
-			*GuildLeader = true;
-		}
-	}
-
+	*GuildLeader = (ErrorCode == SQLITE_ROW);
 	return true;
 }
 
@@ -1592,6 +1578,47 @@ bool GetIPAddressFailedLoginAttempts(TDatabase *Database, int IPAddress, int Tim
 	return true;
 }
 
+// Guild Tables
+//==============================================================================
+bool GetCharacterGuildData(TDatabase *Database, int CharacterID, TCharacterGuildData *GuildData){
+	ASSERT(Database != NULL && GuildData != NULL);
+	sqlite3_stmt *Stmt = PrepareQuery(Database,
+			"SELECT G.GuildID, R.Rank, G.Name, R.Name, M.Title"
+			" FROM Characters AS C"
+			" LEFT JOIN GuildMembers AS M ON M.CharacterID = C.CharacterID"
+			" LEFT JOIN Guilds AS G ON G.GuildID = M.GuildID"
+			" LEFT JOIN GuildRanks AS R"
+				" ON R.GuildID = M.GuildID AND R.Rank = M.Rank"
+			" WHERE C.CharacterID = ?1");
+	if(Stmt == NULL){
+		LOG_ERR("Failed to prepare query");
+		return false;
+	}
+
+	AutoStmtReset StmtReset(Stmt);
+	if(sqlite3_bind_int(Stmt, 1, CharacterID) != SQLITE_OK){
+		LOG_ERR("Failed to bind CharacterID: %s", sqlite3_errmsg(Database->Handle));
+		return false;
+	}
+
+	int ErrorCode = sqlite3_step(Stmt);
+	if(ErrorCode != SQLITE_ROW && ErrorCode != SQLITE_DONE){
+		LOG_ERR("Failed to execute query: %s", sqlite3_errmsg(Database->Handle));
+		return false;
+	}
+
+	memset(GuildData, 0, sizeof(TCharacterGuildData));
+	if(ErrorCode == SQLITE_ROW){
+		GuildData->GuildID = sqlite3_column_int(Stmt, 0);
+		GuildData->Rank = sqlite3_column_int(Stmt, 1);
+		StringBufCopy(GuildData->GuildName, (const char*)sqlite3_column_text(Stmt, 2));
+		StringBufCopy(GuildData->RankName, (const char*)sqlite3_column_text(Stmt, 3));
+		StringBufCopy(GuildData->Title, (const char*)sqlite3_column_text(Stmt, 4));
+	}
+
+	return true;
+}
+
 // House Tables
 //==============================================================================
 bool FinishHouseAuctions(TDatabase *Database, int WorldID, DynamicArray<THouseAuction> *Auctions){
diff --git a/src/query.cc b/src/query.cc
index 52bda66..827d638 100644
--- a/src/query.cc
+++ b/src/query.cc
@@ -679,6 +679,9 @@ static void LoginGameTx(TDatabase *Database, TQuery *Query,
 		QUERY_ERROR_IF(!GamemasterOutfit, 14);
 	}
 
+	TCharacterGuildData GuildData;
+	QUERY_STOP_IF(!GetCharacterGuildData(Database, Character.CharacterID, &GuildData));
+
 	DynamicArray<TAccountBuddy> Buddies;
 	QUERY_STOP_IF(!GetBuddies(Database, Query->WorldID, Account.AccountID, &Buddies));
 
@@ -704,9 +707,9 @@ static void LoginGameTx(TDatabase *Database, TQuery *Query,
 	Response->Write32((uint32)Character.CharacterID);
 	Response->WriteString(Character.Name);
 	Response->Write8((uint8)Character.Sex);
-	Response->WriteString(Character.Guild);
-	Response->WriteString(Character.Rank);
-	Response->WriteString(Character.Title);
+	Response->WriteString(GuildData.GuildName);
+	Response->WriteString(GuildData.RankName);
+	Response->WriteString(GuildData.Title);
 
 	int NumBuddies = std::min<int>(Buddies.Length(), UINT8_MAX);
 	Response->Write8((uint8)NumBuddies);
@@ -1478,15 +1481,18 @@ void ProcessGetCharacterProfile(TDatabase *Database, TQuery *Query){
 
 	TCharacterProfile Character;
 	QUERY_STOP_IF(!GetCharacterProfile(Database, CharacterName, &Character));
-	QUERY_ERROR_IF(!StringEqCI(Character.Name, CharacterName), 1);
+	QUERY_ERROR_IF(Character.CharacterID == 0, 1);
+
+	TCharacterGuildData GuildData;
+	QUERY_STOP_IF(!GetCharacterGuildData(Database, Character.CharacterID, &GuildData));
 
 	TWriteBuffer *Response = QueryBeginResponse(Query, QUERY_STATUS_OK);
 	Response->WriteString(Character.Name);
 	Response->WriteString(Character.World);
 	Response->Write8((uint8)Character.Sex);
-	Response->WriteString(Character.Guild);
-	Response->WriteString(Character.Rank);
-	Response->WriteString(Character.Title);
+	Response->WriteString(GuildData.GuildName);
+	Response->WriteString(GuildData.RankName);
+	Response->WriteString(GuildData.Title);
 	Response->Write16((uint16)Character.Level);
 	Response->WriteString(Character.Profession);
 	Response->WriteString(Character.Residence);
diff --git a/src/querymanager.cc b/src/querymanager.cc
index 42a4408..1c638b2 100644
--- a/src/querymanager.cc
+++ b/src/querymanager.cc
@@ -809,7 +809,7 @@ int main(int argc, const char **argv){
 	g_Config.MaxConnections = 25;
 	g_Config.MaxConnectionIdleTime = 60 * 5; // seconds
 
-	LOG("Tibia Query Manager v0.2 (%s)", DATABASE_SYSTEM_NAME);
+	LOG("Tibia Query Manager v0.3 (%s)", DATABASE_SYSTEM_NAME);
 	if(!ReadConfig("config.cfg", &g_Config)){
 		return EXIT_FAILURE;
 	}
diff --git a/src/querymanager.hh b/src/querymanager.hh
index b652e74..c9ab96b 100644
--- a/src/querymanager.hh
+++ b/src/querymanager.hh
@@ -884,19 +884,22 @@ struct TCharacterLoginData{
 	int AccountID;
 	char Name[30];
 	int Sex;
-	char Guild[30];
-	char Rank[30];
-	char Title[30];
 	bool Deleted;
 };
 
+struct TCharacterGuildData{
+	int GuildID;
+	int Rank;
+	char GuildName[30];
+	char RankName[30];
+	char Title[30];
+};
+
 struct TCharacterProfile{
+	int CharacterID;
 	char Name[30];
 	char World[30];
 	int Sex;
-	char Guild[30];
-	char Rank[30];
-	char Title[30];
 	int Level;
 	char Profession[30];
 	char Residence[30];
@@ -1048,6 +1051,9 @@ bool InsertLoginAttempt(TDatabase *Database, int AccountID, int IPAddress, bool
 bool GetAccountFailedLoginAttempts(TDatabase *Database, int AccountID, int TimeWindow, int *FailedAttempts);
 bool GetIPAddressFailedLoginAttempts(TDatabase *Database, int IPAddress, int TimeWindow, int *FailedAttempts);
 
+// NOTE(fusion): Guild Tables
+bool GetCharacterGuildData(TDatabase *Database, int CharacterID, TCharacterGuildData *GuildData);
+
 // NOTE(fusion): House Tables
 bool FinishHouseAuctions(TDatabase *Database, int WorldID, DynamicArray<THouseAuction> *Auctions);
 bool FinishHouseTransfers(TDatabase *Database, int WorldID, DynamicArray<THouseTransfer> *Transfers);