aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--postgres/schema.sql46
-rw-r--r--postgres/z-002-migrate-v02-to-v03.sql54
-rw-r--r--postgres/z-999-initial-data.sql (renamed from postgres/initial-data.sql)4
-rw-r--r--sqlite/schema.sql44
-rw-r--r--sqlite/z-001-migrate-v01-to-v02.sql6
-rw-r--r--sqlite/z-002-migrate-v02-to-v03.sql50
-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
11 files changed, 336 insertions, 91 deletions
diff --git a/postgres/schema.sql b/postgres/schema.sql
index efe9133..08837c5 100644
--- a/postgres/schema.sql
+++ b/postgres/schema.sql
@@ -2,6 +2,8 @@
-- the database in some partial state. Also, notice we don't use `IF NOT EXISTS`
-- because it could also leave the database in a bad state if for example, some
-- old version of a table already existed.
+--==============================================================================
+
BEGIN;
-- IMPORTANT(fusion): PosgreSQL doesn't have a defined `NOCASE` collation like
@@ -78,9 +80,6 @@ CREATE TABLE Characters (
AccountID INTEGER NOT NULL,
Name TEXT NOT NULL COLLATE NOCASE,
Sex SMALLINT NOT NULL,
- Guild TEXT NOT NULL COLLATE NOCASE DEFAULT '',
- Rank TEXT NOT NULL COLLATE NOCASE DEFAULT '',
- Title TEXT NOT NULL DEFAULT '',
Level SMALLINT NOT NULL DEFAULT 0,
Profession TEXT NOT NULL DEFAULT '',
Residence TEXT NOT NULL DEFAULT '',
@@ -93,7 +92,6 @@ CREATE TABLE Characters (
);
CREATE INDEX CharactersWorldIndex ON Characters(WorldID, IsOnline);
CREATE INDEX CharactersAccountIndex ON Characters(AccountID, IsOnline);
-CREATE INDEX CharactersGuildIndex ON Characters(Guild, Rank);
-- NOTE(fusion): It seems `RIGHT` is a reserved keyword and trying to use
-- it as a column name will generate an error.
@@ -136,6 +134,46 @@ CREATE TABLE LoginAttempts (
CREATE INDEX LoginAttemptsAccountIndex ON LoginAttempts(AccountID, Timestamp);
CREATE INDEX LoginAttemptsAddressIndex ON LoginAttempts(IPAddress, Timestamp);
+-- Guild Tables
+--==============================================================================
+CREATE TABLE Guilds (
+ WorldID INTEGER NOT NULL,
+ GuildID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
+ Name TEXT NOT NULL COLLATE NOCASE,
+ LeaderID INTEGER NOT NULL,
+ Created TIMESTAMPTZ NOT NULL,
+ PRIMARY KEY (GuildID),
+ UNIQUE (Name),
+ UNIQUE (LeaderID)
+);
+
+CREATE TABLE GuildRanks (
+ GuildID INTEGER NOT NULL,
+ Rank SMALLINT NOT NULL,
+ Name TEXT NOT NULL,
+ PRIMARY KEY (GuildID, Rank)
+);
+
+CREATE TABLE GuildMembers (
+ GuildID INTEGER NOT NULL,
+ CharacterID INTEGER NOT NULL,
+ Rank SMALLINT NOT NULL,
+ Title TEXT NOT NULL,
+ Joined TIMESTAMPTZ NOT NULL,
+ PRIMARY KEY (CharacterID)
+);
+CREATE INDEX GuildMembersGuildIndex ON GuildMembers(GuildID, Rank);
+
+CREATE TABLE GuildInvites (
+ GuildID INTEGER NOT NULL,
+ CharacterID INTEGER NOT NULL,
+ RecruiterID INTEGER NOT NULL,
+ Timestamp TIMESTAMPTZ NOT NULL,
+ PRIMARY KEY (GuildID, CharacterID)
+);
+CREATE INDEX GuildInvitesCharacterIndex ON GuildInvites(CharacterID);
+CREATE INDEX GuildInvitesRecruiterIndex ON GuildInvites(RecruiterID);
+
-- House Tables
--==============================================================================
CREATE TABLE Houses (
diff --git a/postgres/z-002-migrate-v02-to-v03.sql b/postgres/z-002-migrate-v02-to-v03.sql
new file mode 100644
index 0000000..52b02df
--- /dev/null
+++ b/postgres/z-002-migrate-v02-to-v03.sql
@@ -0,0 +1,54 @@
+-- NOTE(fusion): This file contains the migration script from v0.2 to v0.3. It's
+-- put inside a transaction to avoid errors from leaving the database in some
+-- partial state.
+-- These changes are already present in the latest `schema.sql`, so trying to
+-- apply them on a newly created database will result in errors.
+--==============================================================================
+
+BEGIN;
+
+DROP INDEX CharactersGuildIndex;
+ALTER TABLE Characters DROP COLUMN Guild;
+ALTER TABLE Characters DROP COLUMN Rank;
+ALTER TABLE Characters DROP COLUMN Title;
+
+CREATE TABLE Guilds (
+ WorldID INTEGER NOT NULL,
+ GuildID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
+ Name TEXT NOT NULL COLLATE NOCASE,
+ LeaderID INTEGER NOT NULL,
+ Created TIMESTAMPTZ NOT NULL,
+ PRIMARY KEY (GuildID),
+ UNIQUE (Name),
+ UNIQUE (LeaderID)
+);
+
+CREATE TABLE GuildRanks (
+ GuildID INTEGER NOT NULL,
+ Rank SMALLINT NOT NULL,
+ Name TEXT NOT NULL,
+ PRIMARY KEY (GuildID, Rank)
+);
+
+CREATE TABLE GuildMembers (
+ GuildID INTEGER NOT NULL,
+ CharacterID INTEGER NOT NULL,
+ Rank SMALLINT NOT NULL,
+ Title TEXT NOT NULL,
+ Joined TIMESTAMPTZ NOT NULL,
+ PRIMARY KEY (CharacterID)
+);
+CREATE INDEX GuildMembersGuildIndex ON GuildMembers(GuildID, Rank);
+
+CREATE TABLE GuildInvites (
+ GuildID INTEGER NOT NULL,
+ CharacterID INTEGER NOT NULL,
+ RecruiterID INTEGER NOT NULL,
+ Timestamp TIMESTAMPTZ NOT NULL,
+ PRIMARY KEY (GuildID, CharacterID)
+);
+CREATE INDEX GuildInvitesCharacterIndex ON GuildInvites(CharacterID);
+CREATE INDEX GuildInvitesRecruiterIndex ON GuildInvites(RecruiterID);
+
+COMMIT;
+
diff --git a/postgres/initial-data.sql b/postgres/z-999-initial-data.sql
index fbe40c7..139c4e1 100644
--- a/postgres/initial-data.sql
+++ b/postgres/z-999-initial-data.sql
@@ -1,5 +1,7 @@
--- NOTE(fusion): This file contains sample initial data. It's wrapped into a
+-- NOTE(fusion): This file contains sample initial data. It's put inside a
-- transaction to avoid errors from leaving the database in some partial state.
+--==============================================================================
+
BEGIN;
-- 111111/tibia
diff --git a/sqlite/schema.sql b/sqlite/schema.sql
index 0ab3a9f..2ee1a8b 100644
--- a/sqlite/schema.sql
+++ b/sqlite/schema.sql
@@ -57,9 +57,6 @@ CREATE TABLE Characters (
AccountID INTEGER NOT NULL,
Name TEXT NOT NULL COLLATE NOCASE,
Sex INTEGER NOT NULL,
- Guild TEXT NOT NULL COLLATE NOCASE DEFAULT '',
- Rank TEXT NOT NULL COLLATE NOCASE DEFAULT '',
- Title TEXT NOT NULL DEFAULT '',
Level INTEGER NOT NULL DEFAULT 0,
Profession TEXT NOT NULL DEFAULT '',
Residence TEXT NOT NULL DEFAULT '',
@@ -72,7 +69,6 @@ CREATE TABLE Characters (
);
CREATE INDEX CharactersWorldIndex ON Characters(WorldID, IsOnline);
CREATE INDEX CharactersAccountIndex ON Characters(AccountID, IsOnline);
-CREATE INDEX CharactersGuildIndex ON Characters(Guild, Rank);
/*
-- TODO(fusion): Have group rights instead of adding individual rights to characters?
@@ -123,6 +119,46 @@ CREATE TABLE LoginAttempts (
CREATE INDEX LoginAttemptsAccountIndex ON LoginAttempts(AccountID, Timestamp);
CREATE INDEX LoginAttemptsAddressIndex ON LoginAttempts(IPAddress, Timestamp);
+-- Guild Tables
+--==============================================================================
+CREATE TABLE Guilds (
+ WorldID INTEGER NOT NULL,
+ GuildID INTEGER NOT NULL,
+ Name TEXT NOT NULL COLLATE NOCASE,
+ LeaderID INTEGER NOT NULL,
+ Created INTEGER NOT NULL,
+ PRIMARY KEY (GuildID),
+ UNIQUE (Name),
+ UNIQUE (LeaderID)
+);
+
+CREATE TABLE GuildRanks (
+ GuildID INTEGER NOT NULL,
+ Rank INTEGER NOT NULL,
+ Name TEXT NOT NULL,
+ PRIMARY KEY (GuildID, Rank)
+);
+
+CREATE TABLE GuildMembers (
+ GuildID INTEGER NOT NULL,
+ CharacterID INTEGER NOT NULL,
+ Rank INTEGER NOT NULL,
+ Title TEXT NOT NULL,
+ Joined INTEGER NOT NULL,
+ PRIMARY KEY (CharacterID)
+);
+CREATE INDEX GuildMembersGuildIndex ON GuildMembers(GuildID, Rank);
+
+CREATE TABLE GuildInvites (
+ GuildID INTEGER NOT NULL,
+ CharacterID INTEGER NOT NULL,
+ RecruiterID INTEGER NOT NULL,
+ Timestamp INTEGER NOT NULL,
+ PRIMARY KEY (GuildID, CharacterID)
+);
+CREATE INDEX GuildInvitesCharacterIndex ON GuildInvites(CharacterID);
+CREATE INDEX GuildInvitesRecruiterIndex ON GuildInvites(RecruiterID);
+
-- House Tables
--==============================================================================
CREATE TABLE Houses (
diff --git a/sqlite/z-001-migrate-v01-to-v02.sql b/sqlite/z-001-migrate-v01-to-v02.sql
index 19fdddb..a874194 100644
--- a/sqlite/z-001-migrate-v01-to-v02.sql
+++ b/sqlite/z-001-migrate-v01-to-v02.sql
@@ -2,10 +2,10 @@
-- must be manually executed as `sqlite3 -bail -echo tibia.db < migration.sql`
-- because the original schema didn't have a `Patches` table which is necessary
-- with the new automatic patching system. Future migration scripts can be placed
--- in `patches/` for automatic execution but not this one, unfortunately.
+-- in `patches/` for automatic execution but not this one, unfortunately. For
+-- more details see `sqlite/README.txt`.
-- These changes are already present in the latest `schema.sql`, so trying to
--- apply it to a newly created database will result in errors. For more details
--- see `sqlite/README.txt`.
+-- apply them to a newly created database will result in errors.
--==============================================================================
BEGIN;
diff --git a/sqlite/z-002-migrate-v02-to-v03.sql b/sqlite/z-002-migrate-v02-to-v03.sql
new file mode 100644
index 0000000..eeb1d9d
--- /dev/null
+++ b/sqlite/z-002-migrate-v02-to-v03.sql
@@ -0,0 +1,50 @@
+-- NOTE(fusion): This file contains the migration script from v0.2 to v0.3. It
+-- can be executed automatically as a patch if placed at `sqlite/patches`. For
+-- more details see `sqlite/README.txt`.
+-- These changes are already present in the latest `schema.sql`, so trying to
+-- apply them to a newly created database will result in errors.
+--==============================================================================
+
+DROP INDEX CharactersGuildIndex;
+ALTER TABLE Characters DROP COLUMN Guild;
+ALTER TABLE Characters DROP COLUMN Rank;
+ALTER TABLE Characters DROP COLUMN Title;
+
+CREATE TABLE Guilds (
+ WorldID INTEGER NOT NULL,
+ GuildID INTEGER NOT NULL,
+ Name TEXT NOT NULL COLLATE NOCASE,
+ LeaderID INTEGER NOT NULL,
+ Created INTEGER NOT NULL,
+ PRIMARY KEY (GuildID),
+ UNIQUE (Name),
+ UNIQUE (LeaderID)
+);
+
+CREATE TABLE GuildRanks (
+ GuildID INTEGER NOT NULL,
+ Rank INTEGER NOT NULL,
+ Name TEXT NOT NULL,
+ PRIMARY KEY (GuildID, Rank)
+);
+
+CREATE TABLE GuildMembers (
+ GuildID INTEGER NOT NULL,
+ CharacterID INTEGER NOT NULL,
+ Rank INTEGER NOT NULL,
+ Title TEXT NOT NULL,
+ Joined INTEGER NOT NULL,
+ PRIMARY KEY (CharacterID)
+);
+CREATE INDEX GuildMembersGuildIndex ON GuildMembers(GuildID, Rank);
+
+CREATE TABLE GuildInvites (
+ GuildID INTEGER NOT NULL,
+ CharacterID INTEGER NOT NULL,
+ RecruiterID INTEGER NOT NULL,
+ Timestamp INTEGER NOT NULL,
+ PRIMARY KEY (GuildID, CharacterID)
+);
+CREATE INDEX GuildInvitesCharacterIndex ON GuildInvites(CharacterID);
+CREATE INDEX GuildInvitesRecruiterIndex ON GuildInvites(RecruiterID);
+
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 21:46:24 +0000