aboutsummaryrefslogtreecommitdiff
path: root/src
diff options
context:
space:
mode:
Diffstat (limited to 'src')
-rw-r--r--src/database_postgres.cc92
-rw-r--r--src/database_sqlite.cc91
-rw-r--r--src/query.cc20
-rw-r--r--src/querymanager.cc2
-rw-r--r--src/querymanager.hh18
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);
generated by cgit v1.2.3 (git 2.46.0) at 2026-07-14 23:17:31 +0000