From 27a0df43b33b712462ab5be65462162b9e4bcd29 Mon Sep 17 00:00:00 2001 From: fusion32 Date: Thu, 23 Oct 2025 04:26:11 -0300 Subject: change how guild data is stored --- postgres/initial-data.sql | 117 --------------------------------- postgres/schema.sql | 46 +++++++++++-- postgres/z-002-migrate-v02-to-v03.sql | 54 +++++++++++++++ postgres/z-999-initial-data.sql | 119 ++++++++++++++++++++++++++++++++++ sqlite/schema.sql | 44 +++++++++++-- sqlite/z-001-migrate-v01-to-v02.sql | 6 +- sqlite/z-002-migrate-v02-to-v03.sql | 50 ++++++++++++++ src/database_postgres.cc | 92 ++++++++++++++++---------- src/database_sqlite.cc | 91 +++++++++++++++++--------- src/query.cc | 20 ++++-- src/querymanager.cc | 2 +- src/querymanager.hh | 18 +++-- 12 files changed, 452 insertions(+), 207 deletions(-) delete mode 100644 postgres/initial-data.sql create mode 100644 postgres/z-002-migrate-v02-to-v03.sql create mode 100644 postgres/z-999-initial-data.sql create mode 100644 sqlite/z-002-migrate-v02-to-v03.sql diff --git a/postgres/initial-data.sql b/postgres/initial-data.sql deleted file mode 100644 index fbe40c7..0000000 --- a/postgres/initial-data.sql +++ /dev/null @@ -1,117 +0,0 @@ --- NOTE(fusion): This file contains sample initial data. It's wrapped into a --- transaction to avoid errors from leaving the database in some partial state. -BEGIN; - --- 111111/tibia -INSERT INTO Accounts (AccountID, Email, Auth) - VALUES (111111, '@tibia', '\x206699cbc2fae1683118c873d746aa376049cb5923ef0980298bb7acbba527ec9e765668f7a338dffea34acf61a20efb654c1e9c62d35148dba2aeeef8dc7788'); - --- NOTE(fusion): This would generally be a sequence of insertions but the way --- PostgreSQL handles unique auto increment columns makes so manually inserted --- values end up colliding with generated values at some point. --- On a real scenario, we'd never have manually assigned ids unless we're --- restoring some database dump, in which case it would also have saved --- sequences. -WITH - WorldIns AS ( - INSERT INTO Worlds (Name, Type, RebootTime, Host, Port, MaxPlayers, - PremiumPlayerBuffer, MaxNewbies, PremiumNewbieBuffer) - VALUES ('Zanera', 0, 5, 'localhost', 7172, 1000, 100, 300, 100) - RETURNING WorldID, Name - ), - CharacterIns AS ( - INSERT INTO Characters (WorldID, AccountID, Name, Sex) - SELECT W.WorldID, C.AccountID, C.Name, C.Sex - FROM WorldIns AS W, - LATERAL (VALUES - (111111, 'Gamemaster on ' || W.Name, 1), - (111111, 'Player on ' || W.Name, 1) - ) AS C (AccountID, Name, Sex) - RETURNING CharacterID, Name - ) -INSERT INTO CharacterRights(CharacterID, Name) - SELECT C.CharacterID, R.RightName - FROM CharacterIns AS C, - (VALUES - ('NOTATION'), - ('NAMELOCK'), - ('STATEMENT_REPORT'), - ('BANISHMENT'), - ('FINAL_WARNING'), - ('IP_BANISHMENT'), - ('KICK'), - ('HOME_TELEPORT'), - ('GAMEMASTER_BROADCAST'), - ('ANONYMOUS_BROADCAST'), - ('NO_BANISHMENT'), - ('ALLOW_MULTICLIENT'), - ('LOG_COMMUNICATION'), - ('READ_GAMEMASTER_CHANNEL'), - ('READ_TUTOR_CHANNEL'), - ('HIGHLIGHT_HELP_CHANNEL'), - ('SEND_BUGREPORTS'), - ('NAME_INSULTING'), - ('NAME_SENTENCE'), - ('NAME_NONSENSICAL_LETTERS'), - ('NAME_BADLY_FORMATTED'), - ('NAME_NO_PERSON'), - ('NAME_CELEBRITY'), - ('NAME_COUNTRY'), - ('NAME_FAKE_IDENTITY'), - ('NAME_FAKE_POSITION'), - ('STATEMENT_INSULTING'), - ('STATEMENT_SPAMMING'), - ('STATEMENT_ADVERT_OFFTOPIC'), - ('STATEMENT_ADVERT_MONEY'), - ('STATEMENT_NON_ENGLISH'), - ('STATEMENT_CHANNEL_OFFTOPIC'), - ('STATEMENT_VIOLATION_INCITING'), - ('CHEATING_BUG_ABUSE'), - ('CHEATING_GAME_WEAKNESS'), - ('CHEATING_MACRO_USE'), - ('CHEATING_MODIFIED_CLIENT'), - ('CHEATING_HACKING'), - ('CHEATING_MULTI_CLIENT'), - ('CHEATING_ACCOUNT_TRADING'), - ('CHEATING_ACCOUNT_SHARING'), - ('GAMEMASTER_THREATENING'), - ('GAMEMASTER_PRETENDING'), - ('GAMEMASTER_INFLUENCE'), - ('GAMEMASTER_FALSE_REPORTS'), - ('KILLING_EXCESSIVE_UNJUSTIFIED'), - ('DESTRUCTIVE_BEHAVIOUR'), - ('SPOILING_AUCTION'), - ('INVALID_PAYMENT'), - ('TELEPORT_TO_CHARACTER'), - ('TELEPORT_TO_MARK'), - ('TELEPORT_VERTICAL'), - ('TELEPORT_TO_COORDINATE'), - ('LEVITATE'), - ('SPECIAL_MOVEUSE'), - ('MODIFY_GOSTRENGTH'), - ('SHOW_COORDINATE'), - ('RETRIEVE'), - ('ENTER_HOUSES'), - ('OPEN_NAMEDOORS'), - ('INVULNERABLE'), - ('UNLIMITED_MANA'), - ('KEEP_INVENTORY'), - ('ALL_SPELLS'), - ('UNLIMITED_CAPACITY'), - ('ATTACK_EVERYWHERE'), - ('NO_LOGOUT_BLOCK'), - ('GAMEMASTER_OUTFIT'), - ('ILLUMINATE'), - ('CHANGE_PROFESSION'), - ('IGNORED_BY_MONSTERS'), - ('SHOW_KEYHOLE_NUMBERS'), - ('CREATE_OBJECTS'), - ('CREATE_MONEY'), - ('CREATE_MONSTERS'), - ('CHANGE_SKILLS'), - ('CLEANUP_FIELDS'), - ('NO_STATISTICS') - ) AS R (RightName) - WHERE C.Name LIKE 'Gamemaster%' COLLATE "default"; - -COMMIT; 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/z-999-initial-data.sql b/postgres/z-999-initial-data.sql new file mode 100644 index 0000000..139c4e1 --- /dev/null +++ b/postgres/z-999-initial-data.sql @@ -0,0 +1,119 @@ +-- 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 +INSERT INTO Accounts (AccountID, Email, Auth) + VALUES (111111, '@tibia', '\x206699cbc2fae1683118c873d746aa376049cb5923ef0980298bb7acbba527ec9e765668f7a338dffea34acf61a20efb654c1e9c62d35148dba2aeeef8dc7788'); + +-- NOTE(fusion): This would generally be a sequence of insertions but the way +-- PostgreSQL handles unique auto increment columns makes so manually inserted +-- values end up colliding with generated values at some point. +-- On a real scenario, we'd never have manually assigned ids unless we're +-- restoring some database dump, in which case it would also have saved +-- sequences. +WITH + WorldIns AS ( + INSERT INTO Worlds (Name, Type, RebootTime, Host, Port, MaxPlayers, + PremiumPlayerBuffer, MaxNewbies, PremiumNewbieBuffer) + VALUES ('Zanera', 0, 5, 'localhost', 7172, 1000, 100, 300, 100) + RETURNING WorldID, Name + ), + CharacterIns AS ( + INSERT INTO Characters (WorldID, AccountID, Name, Sex) + SELECT W.WorldID, C.AccountID, C.Name, C.Sex + FROM WorldIns AS W, + LATERAL (VALUES + (111111, 'Gamemaster on ' || W.Name, 1), + (111111, 'Player on ' || W.Name, 1) + ) AS C (AccountID, Name, Sex) + RETURNING CharacterID, Name + ) +INSERT INTO CharacterRights(CharacterID, Name) + SELECT C.CharacterID, R.RightName + FROM CharacterIns AS C, + (VALUES + ('NOTATION'), + ('NAMELOCK'), + ('STATEMENT_REPORT'), + ('BANISHMENT'), + ('FINAL_WARNING'), + ('IP_BANISHMENT'), + ('KICK'), + ('HOME_TELEPORT'), + ('GAMEMASTER_BROADCAST'), + ('ANONYMOUS_BROADCAST'), + ('NO_BANISHMENT'), + ('ALLOW_MULTICLIENT'), + ('LOG_COMMUNICATION'), + ('READ_GAMEMASTER_CHANNEL'), + ('READ_TUTOR_CHANNEL'), + ('HIGHLIGHT_HELP_CHANNEL'), + ('SEND_BUGREPORTS'), + ('NAME_INSULTING'), + ('NAME_SENTENCE'), + ('NAME_NONSENSICAL_LETTERS'), + ('NAME_BADLY_FORMATTED'), + ('NAME_NO_PERSON'), + ('NAME_CELEBRITY'), + ('NAME_COUNTRY'), + ('NAME_FAKE_IDENTITY'), + ('NAME_FAKE_POSITION'), + ('STATEMENT_INSULTING'), + ('STATEMENT_SPAMMING'), + ('STATEMENT_ADVERT_OFFTOPIC'), + ('STATEMENT_ADVERT_MONEY'), + ('STATEMENT_NON_ENGLISH'), + ('STATEMENT_CHANNEL_OFFTOPIC'), + ('STATEMENT_VIOLATION_INCITING'), + ('CHEATING_BUG_ABUSE'), + ('CHEATING_GAME_WEAKNESS'), + ('CHEATING_MACRO_USE'), + ('CHEATING_MODIFIED_CLIENT'), + ('CHEATING_HACKING'), + ('CHEATING_MULTI_CLIENT'), + ('CHEATING_ACCOUNT_TRADING'), + ('CHEATING_ACCOUNT_SHARING'), + ('GAMEMASTER_THREATENING'), + ('GAMEMASTER_PRETENDING'), + ('GAMEMASTER_INFLUENCE'), + ('GAMEMASTER_FALSE_REPORTS'), + ('KILLING_EXCESSIVE_UNJUSTIFIED'), + ('DESTRUCTIVE_BEHAVIOUR'), + ('SPOILING_AUCTION'), + ('INVALID_PAYMENT'), + ('TELEPORT_TO_CHARACTER'), + ('TELEPORT_TO_MARK'), + ('TELEPORT_VERTICAL'), + ('TELEPORT_TO_COORDINATE'), + ('LEVITATE'), + ('SPECIAL_MOVEUSE'), + ('MODIFY_GOSTRENGTH'), + ('SHOW_COORDINATE'), + ('RETRIEVE'), + ('ENTER_HOUSES'), + ('OPEN_NAMEDOORS'), + ('INVULNERABLE'), + ('UNLIMITED_MANA'), + ('KEEP_INVENTORY'), + ('ALL_SPELLS'), + ('UNLIMITED_CAPACITY'), + ('ATTACK_EVERYWHERE'), + ('NO_LOGOUT_BLOCK'), + ('GAMEMASTER_OUTFIT'), + ('ILLUMINATE'), + ('CHANGE_PROFESSION'), + ('IGNORED_BY_MONSTERS'), + ('SHOW_KEYHOLE_NUMBERS'), + ('CREATE_OBJECTS'), + ('CREATE_MONEY'), + ('CREATE_MONSTERS'), + ('CHANGE_SKILLS'), + ('CLEANUP_FIELDS'), + ('NO_STATISTICS') + ) AS R (RightName) + WHERE C.Name LIKE 'Gamemaster%' COLLATE "default"; + +COMMIT; 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 *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 *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 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(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 *Auctions); bool FinishHouseTransfers(TDatabase *Database, int WorldID, DynamicArray *Transfers); -- cgit v1.2.3