aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--sql/schema.sql14
-rw-r--r--src/connections.cc400
-rw-r--r--src/database.cc478
-rw-r--r--src/querymanager.cc34
-rw-r--r--src/querymanager.hh110
-rw-r--r--src/sha256.cc19
6 files changed, 891 insertions, 164 deletions
diff --git a/sql/schema.sql b/sql/schema.sql
index c8a78ec..90f59a8 100644
--- a/sql/schema.sql
+++ b/sql/schema.sql
@@ -6,12 +6,16 @@ CREATE TABLE IF NOT EXISTS Worlds (
Type INTEGER NOT NULL,
RebootTime INTEGER NOT NULL,
IPAddress INTEGER NOT NULL,
+ -- TODO(fusion): Have some hostname resolution cache and send the resolved
+ -- addresses instead of hardcoding them into the database.
+ --Host TEXT NOT NULL,
Port INTEGER NOT NULL,
MaxPlayers INTEGER NOT NULL,
PremiumPlayerBuffer INTEGER NOT NULL,
MaxNewbies INTEGER NOT NULL,
PremiumNewbieBuffer INTEGER NOT NULL,
OnlineRecord INTEGER NOT NULL DEFAULT 0,
+ OnlineRecordTimestamp INTEGER NOT NULL DEFAULT 0,
PRIMARY KEY (WorldID),
UNIQUE (Name)
);
@@ -275,6 +279,16 @@ INSERT INTO Accounts (AccountID, Email, Auth)
INSERT INTO Characters (WorldID, CharacterID, AccountID, Name, Sex)
VALUES (1, 1, 111111, 'Gamemaster', 1), (1, 2, 111111, 'Player', 1);
+/*
+-- TODO(fusion): Have group rights instead of adding individual rights to characters?
+ALTER TABLE Characters ADD GroupID INTEGER NOT NULL;
+CREATE TABLE IF NOT EXISTS CharacterRights (
+ GroupID INTEGER NOT NULL,
+ Right TEXT NOT NULL COLLATE NOCASE,
+ PRIMARY KEY(GroupID, Right)
+);
+*/
+
INSERT INTO CharacterRights (CharacterID, Right)
VALUES
(1, 'NOTATION'),
diff --git a/src/connections.cc b/src/connections.cc
index 7d573ca..070b348 100644
--- a/src/connections.cc
+++ b/src/connections.cc
@@ -525,59 +525,73 @@ void ProcessLoginQuery(TConnection *Connection, TReadBuffer *Buffer){
SendQueryStatusOk(Connection);
}
-// TODO(fusion): This might be replaced with some `LOGIN_WEB` query.
-void ProcessCheckAccountPasswordQuery(TConnection *Connection, TReadBuffer *Buffer){
- char Password[30];
- char IPString[16];
- int AccountID = (int)Buffer->Read32();
- Buffer->ReadString(Password, sizeof(Password));
- Buffer->ReadString(IPString, sizeof(IPString));
-
- int IPAddress = 0;
- if(IPString[0] != 0 && !ParseIPAddress(IPString, &IPAddress)){
- SendQueryStatusFailed(Connection);
- return;
+static int CheckAccountPasswordTransaction(int AccountID, const char *Password, int IPAddress){
+ TransactionScope Tx("CheckAccountPassword");
+ if(!Tx.Begin()){
+ return -1;
}
- // TODO(fusion): This query may return errors 1-4 but their meaning is not
- // clear, since there is no explicit use of it.
- TAccountData Account;
+ TAccount Account;
if(!GetAccountData(AccountID, &Account)){
- SendQueryStatusFailed(Connection);
- return;
+ return -1;
}
if(Account.AccountID == 0){
- SendQueryStatusError(Connection, 1);
- return;
+ return 1;
}
if(!TestPassword(Account.Auth, sizeof(Account.Auth), Password)){
- SendQueryStatusError(Connection, 2);
- return;
+ return 2;
}
- if(IsAccountBanished(Account.AccountID)){
- SendQueryStatusError(Connection, 3);
- return;
+ if(GetAccountFailedLoginAttempts(Account.AccountID, 5 * 60) > 10){
+ return 3;
}
- if(IsIPBanished(IPAddress)){
- SendQueryStatusError(Connection, 4);
+ if(GetIPAddressFailedLoginAttempts(IPAddress, 30 * 60) > 20){
+ return 4;
+ }
+
+ if(!Tx.Commit()){
+ return -1;
+ }
+
+ return 0;
+}
+
+void ProcessCheckAccountPasswordQuery(TConnection *Connection, TReadBuffer *Buffer){
+ char Password[30];
+ char IPString[16];
+ int AccountID = (int)Buffer->Read32();
+ Buffer->ReadString(Password, sizeof(Password));
+ Buffer->ReadString(IPString, sizeof(IPString));
+
+ int IPAddress = 0;
+ if(!ParseIPAddress(IPString, &IPAddress)){
+ SendQueryStatusFailed(Connection);
return;
}
- SendQueryStatusOk(Connection);
+ // NOTE(fusion): Similar to `ProcessLoginAccountQuery`.
+ int Result = CheckAccountPasswordTransaction(AccountID, Password, IPAddress);
+ InsertLoginAttempt(AccountID, IPAddress, (Result != 0));
+ if(Result == -1){
+ SendQueryStatusFailed(Connection);
+ }else if(Result != 0){
+ SendQueryStatusError(Connection, Result);
+ }else{
+ SendQueryStatusOk(Connection);
+ }
}
int LoginAccountTransaction(int AccountID, const char *Password, int IPAddress,
- DynamicArray<TCharacterLoginData> *Characters, int *PremiumDays){
+ DynamicArray<TCharacterEndpoint> *Characters, int *PremiumDays){
TransactionScope Tx("LoginAccount");
if(!Tx.Begin()){
return -1;
}
- TAccountData Account;
+ TAccount Account;
if(!GetAccountData(AccountID, &Account)){
return -1;
}
@@ -590,11 +604,11 @@ int LoginAccountTransaction(int AccountID, const char *Password, int IPAddress,
return 2;
}
- if(GetAccountLoginAttempts(Account.AccountID, 5 * 60) > 10){
+ if(GetAccountFailedLoginAttempts(Account.AccountID, 5 * 60) > 10){
return 3;
}
- if(GetIPAddressLoginAttempts(IPAddress, 30 * 60) > 15){
+ if(GetIPAddressFailedLoginAttempts(IPAddress, 30 * 60) > 20){
return 4;
}
@@ -606,7 +620,7 @@ int LoginAccountTransaction(int AccountID, const char *Password, int IPAddress,
return 6;
}
- if(!GetCharacterList(Account.AccountID, Characters)){
+ if(!GetCharacterEndpoints(Account.AccountID, Characters)){
return -1;
}
@@ -626,13 +640,13 @@ void ProcessLoginAccountQuery(TConnection *Connection, TReadBuffer *Buffer){
Buffer->ReadString(IPString, sizeof(IPString));
int IPAddress = 0;
- if(IPString[0] != 0 && !ParseIPAddress(IPString, &IPAddress)){
+ if(!ParseIPAddress(IPString, &IPAddress)){
SendQueryStatusFailed(Connection);
return;
}
int PremiumDays = 0;
- DynamicArray<TCharacterLoginData> Characters;
+ DynamicArray<TCharacterEndpoint> Characters;
int Result = LoginAccountTransaction(AccountID, Password,
IPAddress, &Characters, &PremiumDays);
@@ -675,14 +689,14 @@ void ProcessLoginAdminQuery(TConnection *Connection, TReadBuffer *Buffer){
static int LoginGameTransaction(int WorldID, int AccountID, const char *CharacterName,
const char *Password, int IPAddress, bool PrivateWorld, bool GamemasterRequired,
- TCharacterData *Character, DynamicArray<TAccountBuddy> *Buddies,
+ TCharacterLoginData *Character, DynamicArray<TAccountBuddy> *Buddies,
DynamicArray<TCharacterRight> *Rights, bool *PremiumAccountActivated){
TransactionScope Tx("LoginGame");
if(!Tx.Begin()){
return -1;
}
- if(!GetCharacterData(CharacterName, Character)){
+ if(!GetCharacterLoginData(CharacterName, Character)){
return -1;
}
@@ -704,7 +718,7 @@ static int LoginGameTransaction(int WorldID, int AccountID, const char *Characte
}
}
- TAccountData Account;
+ TAccount Account;
if(!GetAccountData(AccountID, &Account)){
return -1;
}
@@ -722,11 +736,11 @@ static int LoginGameTransaction(int WorldID, int AccountID, const char *Characte
return 6;
}
- if(GetAccountLoginAttempts(Account.AccountID, 5 * 60) > 10){
+ if(GetAccountFailedLoginAttempts(Account.AccountID, 5 * 60) > 10){
return 7;
}
- if(GetIPAddressLoginAttempts(IPAddress, 30 * 60) > 15){
+ if(GetIPAddressFailedLoginAttempts(IPAddress, 30 * 60) > 20){
return 9;
}
@@ -742,7 +756,7 @@ static int LoginGameTransaction(int WorldID, int AccountID, const char *Characte
return 12;
}
- // TODO(fusion): Probably merge these into a single query?
+ // TODO(fusion): Probably merge these into a single operation?
if(!GetCharacterRight(Character->CharacterID, "ALLOW_MULTICLIENT")
&& GetAccountOnlineCharacters(Account.AccountID) > 0
&& !IsCharacterOnline(Character->CharacterID)){
@@ -806,12 +820,12 @@ void ProcessLoginGameQuery(TConnection *Connection, TReadBuffer *Buffer){
bool GamemasterRequired = Buffer->ReadFlag();
int IPAddress = 0;
- if(IPString[0] != 0 && !ParseIPAddress(IPString, &IPAddress)){
+ if(!ParseIPAddress(IPString, &IPAddress)){
SendQueryStatusFailed(Connection);
return;
}
- TCharacterData Character;
+ TCharacterLoginData Character;
DynamicArray<TAccountBuddy> Buddies;
DynamicArray<TCharacterRight> Rights;
bool PremiumAccountActivated = false;
@@ -904,7 +918,7 @@ void ProcessSetNamelockQuery(TConnection *Connection, TReadBuffer *Buffer){
Buffer->ReadString(Comment, sizeof(Comment));
int IPAddress = 0;
- if(IPString[0] != 0 && !ParseIPAddress(IPString, &IPAddress)){
+ if(!ParseIPAddress(IPString, &IPAddress)){
SendQueryStatusFailed(Connection);
return;
}
@@ -964,7 +978,7 @@ void ProcessBanishAccountQuery(TConnection *Connection, TReadBuffer *Buffer){
bool FinalWarning = Buffer->ReadFlag();
int IPAddress = 0;
- if(IPString[0] != 0 && !ParseIPAddress(IPString, &IPAddress)){
+ if(!ParseIPAddress(IPString, &IPAddress)){
SendQueryStatusFailed(Connection);
return;
}
@@ -1031,7 +1045,7 @@ void ProcessSetNotationQuery(TConnection *Connection, TReadBuffer *Buffer){
Buffer->ReadString(Comment, sizeof(Comment));
int IPAddress = 0;
- if(IPString[0] != 0 && !ParseIPAddress(IPString, &IPAddress)){
+ if(!ParseIPAddress(IPString, &IPAddress)){
SendQueryStatusFailed(Connection);
return;
}
@@ -1193,7 +1207,7 @@ void ProcessBanishIPAddressQuery(TConnection *Connection, TReadBuffer *Buffer){
Buffer->ReadString(Comment, sizeof(Comment));
int IPAddress = 0;
- if(IPString[0] != 0 && !ParseIPAddress(IPString, &IPAddress)){
+ if(!ParseIPAddress(IPString, &IPAddress)){
SendQueryStatusFailed(Connection);
return;
}
@@ -1789,68 +1803,261 @@ void ProcessLoadWorldConfigQuery(TConnection *Connection, TReadBuffer *Buffer){
SendResponse(Connection, &WriteBuffer);
}
-void ProcessGetKeptCharactersQuery(TConnection *Connection, TReadBuffer *Buffer){
- SendQueryStatusFailed(Connection);
-}
+void ProcessCreateAccountQuery(TConnection *Connection, TReadBuffer *Buffer){
+ // TODO(fusion): We'd ideally want to automatically generate an account number
+ // and return it in case of success but that would also require a more robust
+ // website infrastructure with verification e-mails, etc...
+ char Email[100];
+ char Password[30];
+ int AccountID = (int)Buffer->Read32();
+ Buffer->ReadString(Email, sizeof(Email));
+ Buffer->ReadString(Password, sizeof(Password));
-void ProcessGetDeletedCharactersQuery(TConnection *Connection, TReadBuffer *Buffer){
- SendQueryStatusFailed(Connection);
-}
+ // NOTE(fusion): Inputs should be checked before hand.
+ if(AccountID <= 0 || StringEmpty(Email) || StringEmpty(Password)){
+ SendQueryStatusFailed(Connection);
+ return;
+ }
-void ProcessDeleteOldCharacterQuery(TConnection *Connection, TReadBuffer *Buffer){
- SendQueryStatusFailed(Connection);
-}
+ uint8 Auth[64];
+ if(!GenerateAuth(Password, Auth, sizeof(Auth))){
+ SendQueryStatusFailed(Connection);
+ return;
+ }
-void ProcessGetHiddenCharactersQuery(TConnection *Connection, TReadBuffer *Buffer){
- SendQueryStatusFailed(Connection);
-}
+ TransactionScope Tx("CreateAccount");
+ if(!Tx.Begin()){
+ SendQueryStatusFailed(Connection);
+ return;
+ }
-void ProcessCreateHighscoresQuery(TConnection *Connection, TReadBuffer *Buffer){
- SendQueryStatusFailed(Connection);
+ if(AccountNumberExists(AccountID)){
+ SendQueryStatusError(Connection, 1);
+ return;
+ }
+
+ if(AccountEmailExists(Email)){
+ SendQueryStatusError(Connection, 2);
+ return;
+ }
+
+ if(!CreateAccount(AccountID, Email, Auth, sizeof(Auth))){
+ SendQueryStatusFailed(Connection);
+ return;
+ }
+
+ if(!Tx.Commit()){
+ SendQueryStatusFailed(Connection);
+ return;
+ }
+
+ SendQueryStatusOk(Connection);
}
-void ProcessCreateCensusQuery(TConnection *Connection, TReadBuffer *Buffer){
- SendQueryStatusFailed(Connection);
+void ProcessCreateCharacterQuery(TConnection *Connection, TReadBuffer *Buffer){
+ char WorldName[30];
+ char CharacterName[30];
+ Buffer->ReadString(WorldName, sizeof(WorldName));
+ int AccountID = (int)Buffer->Read32();
+ Buffer->ReadString(CharacterName, sizeof(CharacterName));
+ int Sex = Buffer->Read8();
+
+ // NOTE(fusion): Inputs should be checked before hand.
+ if(AccountID <= 0 || (Sex != 1 && Sex != 2)
+ || StringEmpty(WorldName)
+ || StringEmpty(CharacterName)){
+ SendQueryStatusFailed(Connection);
+ return;
+ }
+
+ TransactionScope Tx("CreateCharacter");
+ if(!Tx.Begin()){
+ SendQueryStatusFailed(Connection);
+ return;
+ }
+
+ int WorldID = GetWorldID(WorldName);
+ if(WorldID == 0){
+ SendQueryStatusError(Connection, 1);
+ return;
+ }
+
+ if(!AccountNumberExists(AccountID)){
+ SendQueryStatusError(Connection, 2);
+ return;
+ }
+
+ if(CharacterNameExists(CharacterName)){
+ SendQueryStatusError(Connection, 3);
+ return;
+ }
+
+ if(!CreateCharacter(WorldID, AccountID, CharacterName, Sex)){
+ SendQueryStatusFailed(Connection);
+ return;
+ }
+
+ if(!Tx.Commit()){
+ SendQueryStatusFailed(Connection);
+ return;
+ }
+
+ SendQueryStatusOk(Connection);
}
-void ProcessCreateKillStatisticsQuery(TConnection *Connection, TReadBuffer *Buffer){
- SendQueryStatusFailed(Connection);
+void ProcessGetAccountSummaryQuery(TConnection *Connection, TReadBuffer *Buffer){
+ int AccountID = (int)Buffer->Read32();
+
+ if(AccountID <= 0){
+ SendQueryStatusFailed(Connection);
+ return;
+ }
+
+ TAccount Account;
+ if(!GetAccountData(AccountID, &Account)){
+ SendQueryStatusFailed(Connection);
+ return;
+ }
+
+ if(Account.AccountID != AccountID){
+ SendQueryStatusFailed(Connection);
+ return;
+ }
+
+ DynamicArray<TCharacterSummary> Characters;
+ if(!GetCharacterSummaries(AccountID, &Characters)){
+ SendQueryStatusFailed(Connection);
+ return;
+ }
+
+ TWriteBuffer WriteBuffer = PrepareResponse(Connection, QUERY_STATUS_OK);
+ WriteBuffer.WriteString(Account.Email);
+ WriteBuffer.Write16((uint16)Account.PremiumDays);
+ WriteBuffer.Write16((uint16)Account.PendingPremiumDays);
+ WriteBuffer.WriteFlag(Account.Deleted);
+ int NumCharacters = std::min<int>(Characters.Length(), UINT8_MAX);
+ WriteBuffer.Write8((uint8)NumCharacters);
+ for(int i = 0; i < NumCharacters; i += 1){
+ WriteBuffer.WriteString(Characters[i].Name);
+ WriteBuffer.WriteString(Characters[i].World);
+ WriteBuffer.Write16((uint16)Characters[i].Level);
+ WriteBuffer.WriteString(Characters[i].Profession);
+ WriteBuffer.WriteFlag(Characters[i].Online);
+ WriteBuffer.WriteFlag(Characters[i].Deleted);
+ }
+ SendResponse(Connection, &WriteBuffer);
}
-void ProcessGetPlayersOnlineQuery(TConnection *Connection, TReadBuffer *Buffer){
- SendQueryStatusFailed(Connection);
+void ProcessGetCharacterProfileQuery(TConnection *Connection, TReadBuffer *Buffer){
+ char CharacterName[30];
+ Buffer->ReadString(CharacterName, sizeof(CharacterName));
+
+ if(StringEmpty(CharacterName)){
+ SendQueryStatusFailed(Connection);
+ return;
+ }
+
+ TCharacterProfile Character;
+ if(!GetCharacterProfile(CharacterName, &Character)){
+ SendQueryStatusFailed(Connection);
+ return;
+ }
+
+ if(!StringEqCI(Character.Name, CharacterName)){
+ SendQueryStatusError(Connection, 1);
+ return;
+ }
+
+ TWriteBuffer WriteBuffer = PrepareResponse(Connection, QUERY_STATUS_OK);
+ WriteBuffer.WriteString(Character.Name);
+ WriteBuffer.WriteString(Character.World);
+ WriteBuffer.Write8((uint8)Character.Sex);
+ WriteBuffer.WriteString(Character.Guild);
+ WriteBuffer.WriteString(Character.Rank);
+ WriteBuffer.WriteString(Character.Title);
+ WriteBuffer.Write16((uint16)Character.Level);
+ WriteBuffer.WriteString(Character.Profession);
+ WriteBuffer.WriteString(Character.Residence);
+ WriteBuffer.Write32((uint32)Character.LastLogin);
+ WriteBuffer.Write16((uint16)Character.PremiumDays);
+ WriteBuffer.WriteFlag(Character.Online);
+ WriteBuffer.WriteFlag(Character.Deleted);
+ SendResponse(Connection, &WriteBuffer);
}
void ProcessGetWorldsQuery(TConnection *Connection, TReadBuffer *Buffer){
- SendQueryStatusFailed(Connection);
-}
+ DynamicArray<TWorld> Worlds;
+ if(!GetWorlds(&Worlds)){
+ SendQueryStatusFailed(Connection);
+ return;
+ }
-void ProcessGetServerLoadQuery(TConnection *Connection, TReadBuffer *Buffer){
- SendQueryStatusFailed(Connection);
+ TWriteBuffer WriteBuffer = PrepareResponse(Connection, QUERY_STATUS_OK);
+ int NumWorlds = std::min<int>(Worlds.Length(), UINT8_MAX);
+ WriteBuffer.Write8((uint8)NumWorlds);
+ for(int i = 0; i < NumWorlds; i += 1){
+ WriteBuffer.WriteString(Worlds[i].Name);
+ WriteBuffer.Write8((uint8)Worlds[i].Type);
+ WriteBuffer.Write16((uint16)Worlds[i].NumPlayers);
+ WriteBuffer.Write16((uint16)Worlds[i].MaxPlayers);
+ WriteBuffer.Write16((uint16)Worlds[i].OnlineRecord);
+ WriteBuffer.Write32((uint32)Worlds[i].OnlineRecordTimestamp);
+ }
+ SendResponse(Connection, &WriteBuffer);
}
-void ProcessInsertPaymentDataOldQuery(TConnection *Connection, TReadBuffer *Buffer){
- SendQueryStatusFailed(Connection);
-}
+void ProcessGetOnlineCharactersQuery(TConnection *Connection, TReadBuffer *Buffer){
+ char WorldName[30];
+ Buffer->ReadString(WorldName, sizeof(WorldName));
-void ProcessAddPaymentOldQuery(TConnection *Connection, TReadBuffer *Buffer){
- SendQueryStatusFailed(Connection);
-}
+ int WorldID = GetWorldID(WorldName);
+ if(WorldID == 0){
+ SendQueryStatusFailed(Connection);
+ return;
+ }
-void ProcessCancelPaymentOldQuery(TConnection *Connection, TReadBuffer *Buffer){
- SendQueryStatusFailed(Connection);
-}
+ DynamicArray<TOnlineCharacter> Characters;
+ if(!GetOnlineCharacters(WorldID, &Characters)){
+ SendQueryStatusFailed(Connection);
+ return;
+ }
-void ProcessInsertPaymentDataNewQuery(TConnection *Connection, TReadBuffer *Buffer){
- SendQueryStatusFailed(Connection);
+ TWriteBuffer WriteBuffer = PrepareResponse(Connection, QUERY_STATUS_OK);
+ int NumCharacters = std::min<int>(Characters.Length(), UINT16_MAX);
+ WriteBuffer.Write16((uint16)NumCharacters);
+ for(int i = 0; i < NumCharacters; i += 1){
+ WriteBuffer.WriteString(Characters[i].Name);
+ WriteBuffer.Write16((uint16)Characters[i].Level);
+ WriteBuffer.WriteString(Characters[i].Profession);
+ }
+ SendResponse(Connection, &WriteBuffer);
}
-void ProcessAddPaymentNewQuery(TConnection *Connection, TReadBuffer *Buffer){
- SendQueryStatusFailed(Connection);
-}
+void ProcessGetKillStatisticsQuery(TConnection *Connection, TReadBuffer *Buffer){
+ char WorldName[30];
+ Buffer->ReadString(WorldName, sizeof(WorldName));
-void ProcessCancelPaymentNewQuery(TConnection *Connection, TReadBuffer *Buffer){
- SendQueryStatusFailed(Connection);
+ int WorldID = GetWorldID(WorldName);
+ if(WorldID == 0){
+ SendQueryStatusFailed(Connection);
+ return;
+ }
+
+ DynamicArray<TKillStatistics> Stats;
+ if(!GetKillStatistics(WorldID, &Stats)){
+ SendQueryStatusFailed(Connection);
+ return;
+ }
+
+ TWriteBuffer WriteBuffer = PrepareResponse(Connection, QUERY_STATUS_OK);
+ int NumStats = std::min<int>(Stats.Length(), UINT16_MAX);
+ WriteBuffer.Write16((uint16)NumStats);
+ for(int i = 0; i < NumStats; i += 1){
+ WriteBuffer.WriteString(Stats[i].RaceName);
+ WriteBuffer.Write32((uint32)Stats[i].PlayersKilled);
+ WriteBuffer.Write32((uint32)Stats[i].TimesKilled);
+ }
+ SendResponse(Connection, &WriteBuffer);
}
void ProcessConnectionQuery(TConnection *Connection){
@@ -1906,22 +2113,13 @@ void ProcessConnectionQuery(TConnection *Connection){
case QUERY_EXCLUDE_FROM_AUCTIONS: ProcessExcludeFromAuctionsQuery(Connection, &Buffer); break;
case QUERY_CANCEL_HOUSE_TRANSFER: ProcessCancelHouseTransferQuery(Connection, &Buffer); break;
case QUERY_LOAD_WORLD_CONFIG: ProcessLoadWorldConfigQuery(Connection, &Buffer); break;
- case QUERY_GET_KEPT_CHARACTERS: ProcessGetKeptCharactersQuery(Connection, &Buffer); break;
- case QUERY_GET_DELETED_CHARACTERS: ProcessGetDeletedCharactersQuery(Connection, &Buffer); break;
- case QUERY_DELETE_OLD_CHARACTER: ProcessDeleteOldCharacterQuery(Connection, &Buffer); break;
- case QUERY_GET_HIDDEN_CHARACTERS: ProcessGetHiddenCharactersQuery(Connection, &Buffer); break;
- case QUERY_CREATE_HIGHSCORES: ProcessCreateHighscoresQuery(Connection, &Buffer); break;
- case QUERY_CREATE_CENSUS: ProcessCreateCensusQuery(Connection, &Buffer); break;
- case QUERY_CREATE_KILL_STATISTICS: ProcessCreateKillStatisticsQuery(Connection, &Buffer); break;
- case QUERY_GET_PLAYERS_ONLINE: ProcessGetPlayersOnlineQuery(Connection, &Buffer); break;
+ case QUERY_CREATE_ACCOUNT: ProcessCreateAccountQuery(Connection, &Buffer); break;
+ case QUERY_CREATE_CHARACTER: ProcessCreateCharacterQuery(Connection, &Buffer); break;
+ case QUERY_GET_ACCOUNT_SUMMARY: ProcessGetAccountSummaryQuery(Connection, &Buffer); break;
+ case QUERY_GET_CHARACTER_PROFILE: ProcessGetCharacterProfileQuery(Connection, &Buffer); break;
case QUERY_GET_WORLDS: ProcessGetWorldsQuery(Connection, &Buffer); break;
- case QUERY_GET_SERVER_LOAD: ProcessGetServerLoadQuery(Connection, &Buffer); break;
- case QUERY_INSERT_PAYMENT_DATA_OLD: ProcessInsertPaymentDataOldQuery(Connection, &Buffer); break;
- case QUERY_ADD_PAYMENT_OLD: ProcessAddPaymentOldQuery(Connection, &Buffer); break;
- case QUERY_CANCEL_PAYMENT_OLD: ProcessCancelPaymentOldQuery(Connection, &Buffer); break;
- case QUERY_INSERT_PAYMENT_DATA_NEW: ProcessInsertPaymentDataNewQuery(Connection, &Buffer); break;
- case QUERY_ADD_PAYMENT_NEW: ProcessAddPaymentNewQuery(Connection, &Buffer); break;
- case QUERY_CANCEL_PAYMENT_NEW: ProcessCancelPaymentNewQuery(Connection, &Buffer); break;
+ case QUERY_GET_ONLINE_CHARACTERS: ProcessGetOnlineCharactersQuery(Connection, &Buffer); break;
+ case QUERY_GET_KILL_STATISTICS: ProcessGetKillStatisticsQuery(Connection, &Buffer); break;
default:{
LOG_ERR("Unknown query %d from %s", Query, Connection->RemoteAddress);
SendQueryStatusFailed(Connection);
diff --git a/src/database.cc b/src/database.cc
index 3b1c51e..b873732 100644
--- a/src/database.cc
+++ b/src/database.cc
@@ -16,6 +16,28 @@ constexpr int g_ApplicationID = 0x54694442;
// Statement Cache
//==============================================================================
+// IMPORTANT(fusion): Prepared statements that are not reset after use may keep
+// transactions open in which case an older view to the database is held, making
+// changes from other processes, including the sqlite shell, not visible. I have
+// not found anything on the SQLite docs but here's a stack overflow question:
+// `https://stackoverflow.com/questions/43949228`
+struct AutoStmtReset{
+private:
+ sqlite3_stmt *m_Stmt;
+
+public:
+ AutoStmtReset(sqlite3_stmt *Stmt){
+ m_Stmt = Stmt;
+ }
+
+ ~AutoStmtReset(void){
+ if(m_Stmt){
+ sqlite3_reset(m_Stmt);
+ m_Stmt = NULL;
+ }
+ }
+};
+
uint32 HashText(const char *Text){
// FNV1a 32-bits
uint32 Hash = 0x811C9DC5U;
@@ -66,7 +88,15 @@ sqlite3_stmt *PrepareQuery(const char *Text){
Entry->LastUsed = g_MonotonicTimeMS;
Entry->Hash = Hash;
}else{
- sqlite3_reset(Stmt);
+ if(sqlite3_stmt_busy(Stmt) != 0){
+ LOG_WARN("Statement \"%.30s%s\" wasn't properly reset. Use the"
+ " `AutoStmtReset` wrapper or manually reset it after usage"
+ " to avoid it holding onto an older view of the database,"
+ " making changes from other processes not visible.",
+ Text, (strlen(Text) > 30 ? "..." : ""));
+ sqlite3_reset(Stmt);
+ }
+
sqlite3_clear_bindings(Stmt);
}
@@ -152,6 +182,7 @@ int GetWorldID(const char *WorldName){
return false;
}
+ AutoStmtReset StmtReset(Stmt);
if(sqlite3_bind_text(Stmt, 1, WorldName, -1, NULL) != SQLITE_OK){
LOG_ERR("Failed to bind WorldName: %s", sqlite3_errmsg(g_Database));
return false;
@@ -166,6 +197,42 @@ int GetWorldID(const char *WorldName){
return (ErrorCode == SQLITE_ROW ? sqlite3_column_int(Stmt, 0) : 0);
}
+bool GetWorlds(DynamicArray<TWorld> *Worlds){
+ ASSERT(Worlds != NULL);
+ sqlite3_stmt *Stmt = PrepareQuery(
+ "WITH N (WorldID, NumPlayers) AS ("
+ "SELECT WorldID, COUNT(*) FROM OnlineCharacters GROUP BY WorldID"
+ ")"
+ " SELECT W.Name, W.Type, COALESCE(N.NumPlayers, 0), W.MaxPlayers,"
+ " W.OnlineRecord, W.OnlineRecordTimestamp"
+ " FROM Worlds AS W"
+ " LEFT JOIN N ON W.WorldID = N.WorldID");
+ if(Stmt == NULL){
+ LOG_ERR("Failed to prepare query");
+ return false;
+ }
+
+ AutoStmtReset StmtReset(Stmt);
+ while(sqlite3_step(Stmt) == SQLITE_ROW){
+ TWorld World = {};
+ StringCopy(World.Name, sizeof(World.Name),
+ (const char*)sqlite3_column_text(Stmt, 0));
+ World.Type = sqlite3_column_int(Stmt, 1);
+ World.NumPlayers = sqlite3_column_int(Stmt, 2);
+ World.MaxPlayers = sqlite3_column_int(Stmt, 3);
+ World.OnlineRecord = sqlite3_column_int(Stmt, 4);
+ World.OnlineRecordTimestamp = sqlite3_column_int(Stmt, 5);
+ Worlds->Push(World);
+ }
+
+ if(sqlite3_errcode(g_Database) != SQLITE_DONE){
+ LOG_ERR("Failed to execute query: %s", sqlite3_errmsg(g_Database));
+ return false;
+ }
+
+ return true;
+}
+
bool GetWorldConfig(int WorldID, TWorldConfig *WorldConfig){
ASSERT(WorldConfig != NULL);
sqlite3_stmt *Stmt = PrepareQuery(
@@ -177,6 +244,7 @@ bool GetWorldConfig(int WorldID, TWorldConfig *WorldConfig){
return false;
}
+ AutoStmtReset StmtReset(Stmt);
if(sqlite3_bind_int(Stmt, 1, WorldID) != SQLITE_OK){
LOG_ERR("Failed to bind WorldID: %s", sqlite3_errmsg(g_Database));
return false;
@@ -198,16 +266,118 @@ bool GetWorldConfig(int WorldID, TWorldConfig *WorldConfig){
return true;
}
-bool GetAccountData(int AccountID, TAccountData *Account){
+bool AccountExists(int AccountID, const char *Email){
+ ASSERT(Email != NULL);
+ sqlite3_stmt *Stmt = PrepareQuery(
+ "SELECT 1 FROM Accounts WHERE AccountID = ?1 OR Email = ?2");
+ if(Stmt == NULL){
+ LOG_ERR("Failed to prepare query");
+ return false;
+ }
+
+ AutoStmtReset StmtReset(Stmt);
+ if(sqlite3_bind_int(Stmt, 1, AccountID) != SQLITE_OK
+ || sqlite3_bind_text(Stmt, 2, Email, -1, NULL) != SQLITE_OK){
+ LOG_ERR("Failed to bind parameters: %s", sqlite3_errmsg(g_Database));
+ return false;
+ }
+
+ int ErrCode = sqlite3_step(Stmt);
+ if(ErrCode != SQLITE_ROW && ErrCode != SQLITE_DONE){
+ LOG_ERR("Failed to execute query: %s", sqlite3_errmsg(g_Database));
+ return false;
+ }
+
+ return (ErrCode == SQLITE_ROW);
+}
+
+bool AccountNumberExists(int AccountID){
+ sqlite3_stmt *Stmt = PrepareQuery(
+ "SELECT 1 FROM Accounts WHERE AccountID = ?1");
+ if(Stmt == NULL){
+ LOG_ERR("Failed to prepare query");
+ return false;
+ }
+
+ AutoStmtReset StmtReset(Stmt);
+ if(sqlite3_bind_int(Stmt, 1, AccountID)!= SQLITE_OK){
+ LOG_ERR("Failed to bind AccountID: %s", sqlite3_errmsg(g_Database));
+ return false;
+ }
+
+ int ErrCode = sqlite3_step(Stmt);
+ if(ErrCode != SQLITE_ROW && ErrCode != SQLITE_DONE){
+ LOG_ERR("Failed to execute query: %s", sqlite3_errmsg(g_Database));
+ return false;
+ }
+
+ return (ErrCode == SQLITE_ROW);
+}
+
+bool AccountEmailExists(const char *Email){
+ ASSERT(Email != NULL);
+ sqlite3_stmt *Stmt = PrepareQuery(
+ "SELECT 1 FROM Accounts WHERE Email = ?1");
+ if(Stmt == NULL){
+ LOG_ERR("Failed to prepare query");
+ return false;
+ }
+
+ AutoStmtReset StmtReset(Stmt);
+ if(sqlite3_bind_text(Stmt, 1, Email, -1, NULL) != SQLITE_OK){
+ LOG_ERR("Failed to bind Email: %s", sqlite3_errmsg(g_Database));
+ return false;
+ }
+
+ int ErrCode = sqlite3_step(Stmt);
+ if(ErrCode != SQLITE_ROW && ErrCode != SQLITE_DONE){
+ LOG_ERR("Failed to execute query: %s", sqlite3_errmsg(g_Database));
+ return false;
+ }
+
+ return (ErrCode == SQLITE_ROW);
+}
+
+bool CreateAccount(int AccountID, const char *Email, const uint8 *Auth, int AuthSize){
+ ASSERT(Email != NULL && Auth != NULL && AuthSize > 0);
+ sqlite3_stmt *Stmt = PrepareQuery(
+ "INSERT INTO Accounts (AccountID, Email, Auth)"
+ " VALUES (?1, ?2, ?3)");
+ if(Stmt == NULL){
+ LOG_ERR("Failed to prepare query");
+ return false;
+ }
+
+ AutoStmtReset StmtReset(Stmt);
+ if(sqlite3_bind_int(Stmt, 1, AccountID) != SQLITE_OK
+ || sqlite3_bind_text(Stmt, 2, Email, -1, NULL) != SQLITE_OK
+ || sqlite3_bind_blob(Stmt, 3, Auth, AuthSize, NULL) != SQLITE_OK){
+ LOG_ERR("Failed to bind parameters: %s", sqlite3_errmsg(g_Database));
+ return false;
+ }
+
+ int ErrCode = sqlite3_step(Stmt);
+ if(ErrCode != SQLITE_DONE && ErrCode != SQLITE_CONSTRAINT){
+ LOG_ERR("Failed to execute query: %s", sqlite3_errmsg(g_Database));
+ return false;
+ }
+
+ return (ErrCode == SQLITE_DONE);
+}
+
+bool GetAccountData(int AccountID, TAccount *Account){
ASSERT(Account != NULL);
sqlite3_stmt *Stmt = PrepareQuery(
- "SELECT AccountID, Email, Auth, MAX(PremiumEnd - UNIXEPOCH(), 0), PendingPremiumDays"
+ "SELECT AccountID, Email, Auth,"
+ " MAX(PremiumEnd - UNIXEPOCH(), 0),"
+ " PendingPremiumDays, Deleted"
" FROM Accounts WHERE AccountID = ?1");
if(Stmt == NULL){
LOG_ERR("Failed to prepare query");
return false;
}
+ AutoStmtReset StmtReset(Stmt);
if(sqlite3_bind_int(Stmt, 1, AccountID) != SQLITE_OK){
LOG_ERR("Failed to bind AccountID: %s", sqlite3_errmsg(g_Database));
return false;
@@ -219,7 +389,7 @@ bool GetAccountData(int AccountID, TAccountData *Account){
return false;
}
- memset(Account, 0, sizeof(TAccountData));
+ memset(Account, 0, sizeof(TAccount));
if(ErrorCode == SQLITE_ROW){
Account->AccountID = sqlite3_column_int(Stmt, 0);
StringCopy(Account->Email, sizeof(Account->Email),
@@ -227,8 +397,9 @@ bool GetAccountData(int AccountID, TAccountData *Account){
if(sqlite3_column_bytes(Stmt, 2) == sizeof(Account->Auth)){
memcpy(Account->Auth, sqlite3_column_blob(Stmt, 2), sizeof(Account->Auth));
}
- Account->PremiumDays = (sqlite3_column_int(Stmt, 3) + 86399) / 86400;
+ Account->PremiumDays = RoundSecondsToDays(sqlite3_column_int(Stmt, 3));
Account->PendingPremiumDays = sqlite3_column_int(Stmt, 4);
+ Account->Deleted = (sqlite3_column_int(Stmt, 5) != 0);
}
return true;
@@ -243,6 +414,7 @@ int GetAccountOnlineCharacters(int AccountID){
return 0;
}
+ AutoStmtReset StmtReset(Stmt);
if(sqlite3_bind_int(Stmt, 1, AccountID) != SQLITE_OK){
LOG_ERR("Failed to bind AccountID: %s", sqlite3_errmsg(g_Database));
return 0;
@@ -264,6 +436,7 @@ bool IsCharacterOnline(int CharacterID){
return false;
}
+ AutoStmtReset StmtReset(Stmt);
if(sqlite3_bind_int(Stmt, 1, CharacterID) != SQLITE_OK){
LOG_ERR("Failed to bind CharacterID: %s", sqlite3_errmsg(g_Database));
return false;
@@ -294,6 +467,7 @@ bool ActivatePendingPremiumDays(int AccountID){
return false;
}
+ AutoStmtReset StmtReset(Stmt);
if(sqlite3_bind_int(Stmt, 1, AccountID) != SQLITE_OK){
LOG_ERR("Failed to bind AccountID: %s", sqlite3_errmsg(g_Database));
return false;
@@ -307,7 +481,7 @@ bool ActivatePendingPremiumDays(int AccountID){
return true;
}
-bool GetCharacterList(int AccountID, DynamicArray<TCharacterLoginData> *Characters){
+bool GetCharacterEndpoints(int AccountID, DynamicArray<TCharacterEndpoint> *Characters){
sqlite3_stmt *Stmt = PrepareQuery(
"SELECT C.Name, W.Name, W.IPAddress, W.Port"
" FROM Characters AS C"
@@ -318,13 +492,14 @@ bool GetCharacterList(int AccountID, DynamicArray<TCharacterLoginData> *Characte
return false;
}
+ AutoStmtReset StmtReset(Stmt);
if(sqlite3_bind_int(Stmt, 1, AccountID) != SQLITE_OK){
LOG_ERR("Failed to bind AccountID: %s", sqlite3_errmsg(g_Database));
return false;
}
while(sqlite3_step(Stmt) == SQLITE_ROW){
- TCharacterLoginData Character = {};
+ TCharacterEndpoint Character = {};
StringCopy(Character.Name, sizeof(Character.Name),
(const char*)sqlite3_column_text(Stmt, 0));
StringCopy(Character.WorldName, sizeof(Character.WorldName),
@@ -342,6 +517,97 @@ bool GetCharacterList(int AccountID, DynamicArray<TCharacterLoginData> *Characte
return true;
}
+bool GetCharacterSummaries(int AccountID, DynamicArray<TCharacterSummary> *Characters){
+ sqlite3_stmt *Stmt = PrepareQuery(
+ "SELECT C.Name, W.Name, C.Level, C.Profession, C.IsOnline, C.Deleted"
+ " FROM Characters AS C"
+ " LEFT JOIN Worlds AS W ON W.WorldID = C.WorldID"
+ " WHERE C.AccountID = ?1");
+ if(Stmt == NULL){
+ LOG_ERR("Failed to prepare query");
+ return false;
+ }
+
+ AutoStmtReset StmtReset(Stmt);
+ if(sqlite3_bind_int(Stmt, 1, AccountID) != SQLITE_OK){
+ LOG_ERR("Failed to bind AccountID: %s", sqlite3_errmsg(g_Database));
+ return false;
+ }
+
+ while(sqlite3_step(Stmt) == SQLITE_ROW){
+ TCharacterSummary Character = {};
+ StringCopy(Character.Name, sizeof(Character.Name),
+ (const char*)sqlite3_column_text(Stmt, 0));
+ StringCopy(Character.World, sizeof(Character.World),
+ (const char*)sqlite3_column_text(Stmt, 1));
+ Character.Level = sqlite3_column_int(Stmt, 2);
+ StringCopy(Character.Profession, sizeof(Character.Profession),
+ (const char*)sqlite3_column_text(Stmt, 3));
+ Character.Online = (sqlite3_column_int(Stmt, 4) != 0);
+ Character.Deleted = (sqlite3_column_int(Stmt, 5) != 0);
+ Characters->Push(Character);
+ }
+
+ if(sqlite3_errcode(g_Database) != SQLITE_DONE){
+ LOG_ERR("Failed to execute query: %s", sqlite3_errmsg(g_Database));
+ return false;
+ }
+
+ return true;
+}
+
+bool CharacterNameExists(const char *Name){
+ ASSERT(Name != NULL);
+ sqlite3_stmt *Stmt = PrepareQuery(
+ "SELECT 1 FROM Characters WHERE Name = ?1");
+ if(Stmt == NULL){
+ LOG_ERR("Failed to prepare query");
+ return false;
+ }
+
+ AutoStmtReset StmtReset(Stmt);
+ if(sqlite3_bind_text(Stmt, 1, Name, -1, NULL) != SQLITE_OK){
+ LOG_ERR("Failed to bind Email: %s", sqlite3_errmsg(g_Database));
+ return false;
+ }
+
+ int ErrCode = sqlite3_step(Stmt);
+ if(ErrCode != SQLITE_ROW && ErrCode != SQLITE_DONE){
+ LOG_ERR("Failed to execute query: %s", sqlite3_errmsg(g_Database));
+ return false;
+ }
+
+ return (ErrCode == SQLITE_ROW);
+}
+
+bool CreateCharacter(int WorldID, int AccountID, const char *Name, int Sex){
+ ASSERT(Name != NULL);
+ sqlite3_stmt *Stmt = PrepareQuery(
+ "INSERT INTO Characters (WorldID, AccountID, Name, Sex)"
+ " VALUES (?1, ?2, ?3, ?4)");
+ if(Stmt == NULL){
+ LOG_ERR("Failed to prepare query");
+ return false;
+ }
+
+ AutoStmtReset StmtReset(Stmt);
+ if(sqlite3_bind_int(Stmt, 1, WorldID) != SQLITE_OK
+ || sqlite3_bind_int(Stmt, 2, AccountID) != SQLITE_OK
+ || sqlite3_bind_text(Stmt, 3, Name, -1, NULL) != SQLITE_OK
+ || sqlite3_bind_int(Stmt, 4, Sex) != SQLITE_OK){
+ LOG_ERR("Failed to bind parameters: %s", sqlite3_errmsg(g_Database));
+ return false;
+ }
+
+ int ErrCode = sqlite3_step(Stmt);
+ if(ErrCode != SQLITE_DONE && ErrCode != SQLITE_CONSTRAINT){
+ LOG_ERR("Failed to execute query: %s", sqlite3_errmsg(g_Database));
+ return false;
+ }
+
+ return (ErrCode == SQLITE_DONE);
+}
+
int GetCharacterID(int WorldID, const char *CharacterName){
ASSERT(CharacterName != NULL);
sqlite3_stmt *Stmt = PrepareQuery(
@@ -352,6 +618,7 @@ int GetCharacterID(int WorldID, const char *CharacterName){
return 0;
}
+ AutoStmtReset StmtReset(Stmt);
if(sqlite3_bind_int(Stmt, 1, WorldID) != SQLITE_OK
|| sqlite3_bind_text(Stmt, 2, CharacterName, -1, NULL) != SQLITE_OK){
LOG_ERR("Failed to bind parameters: %s", sqlite3_errmsg(g_Database));
@@ -367,7 +634,7 @@ int GetCharacterID(int WorldID, const char *CharacterName){
return (ErrorCode == SQLITE_ROW ? sqlite3_column_int(Stmt, 0) : 0);
}
-bool GetCharacterData(const char *CharacterName, TCharacterData *Character){
+bool GetCharacterLoginData(const char *CharacterName, TCharacterLoginData *Character){
ASSERT(CharacterName != NULL && Character != NULL);
sqlite3_stmt *Stmt = PrepareQuery(
"SELECT WorldID, CharacterID, AccountID, Name,"
@@ -378,6 +645,7 @@ bool GetCharacterData(const char *CharacterName, TCharacterData *Character){
return 0;
}
+ AutoStmtReset StmtReset(Stmt);
if(sqlite3_bind_text(Stmt, 1, CharacterName, -1, NULL) != SQLITE_OK){
LOG_ERR("Failed to bind CharacterName: %s", sqlite3_errmsg(g_Database));
return 0;
@@ -389,7 +657,7 @@ bool GetCharacterData(const char *CharacterName, TCharacterData *Character){
return 0;
}
- memset(Character, 0, sizeof(TCharacterData));
+ memset(Character, 0, sizeof(TCharacterLoginData));
if(ErrorCode == SQLITE_ROW){
Character->WorldID = sqlite3_column_int(Stmt, 0);
Character->CharacterID = sqlite3_column_int(Stmt, 1);
@@ -403,7 +671,64 @@ bool GetCharacterData(const char *CharacterName, TCharacterData *Character){
(const char*)sqlite3_column_text(Stmt, 6));
StringCopy(Character->Title, sizeof(Character->Title),
(const char*)sqlite3_column_text(Stmt, 7));
- Character->Deleted = sqlite3_column_int(Stmt, 8);
+ Character->Deleted = (sqlite3_column_int(Stmt, 8) != 0);
+ }
+
+ return true;
+}
+
+bool GetCharacterProfile(const char *CharacterName, TCharacterProfile *Character){
+ ASSERT(CharacterName != NULL && Character != NULL);
+ sqlite3_stmt *Stmt = PrepareQuery(
+ "SELECT C.Name, W.Name, C.Sex, C.Guild, C.Rank, C.Title, C.Level,"
+ " C.Profession, C.Residence, C.LastLoginTime, C.IsOnline,"
+ " C.Deleted, MAX(A.PremiumEnd - UNIXEPOCH(), 0)"
+ " FROM Characters AS C"
+ " LEFT JOIN Worlds AS W ON W.WorldID = C.WorldID"
+ " LEFT JOIN Accounts AS A ON A.AccountID = C.AccountID"
+ " LEFT JOIN CharacterRights AS R"
+ " ON R.CharacterID = C.CharacterID"
+ " AND R.Right = 'NO_STATISTICS'"
+ " WHERE C.Name = ?1 AND R.Right IS NULL");
+ if(Stmt == NULL){
+ LOG_ERR("Failed to prepare query");
+ return false;
+ }
+
+ AutoStmtReset StmtReset(Stmt);
+ if(sqlite3_bind_text(Stmt, 1, CharacterName, -1, NULL) != SQLITE_OK){
+ LOG_ERR("Failed to bind CharacterName: %s", sqlite3_errmsg(g_Database));
+ return false;
+ }
+
+ int ErrorCode = sqlite3_step(Stmt);
+ if(ErrorCode != SQLITE_ROW && ErrorCode != SQLITE_DONE){
+ LOG_ERR("Failed to execute query: %s", sqlite3_errmsg(g_Database));
+ return false;
+ }
+
+ memset(Character, 0, sizeof(TCharacterProfile));
+ if(ErrorCode == SQLITE_ROW){
+ StringCopy(Character->Name, sizeof(Character->Name),
+ (const char*)sqlite3_column_text(Stmt, 0));
+ StringCopy(Character->World, sizeof(Character->World),
+ (const char*)sqlite3_column_text(Stmt, 1));
+ Character->Sex = sqlite3_column_int(Stmt, 2);
+ StringCopy(Character->Guild, sizeof(Character->Guild),
+ (const char*)sqlite3_column_text(Stmt, 3));
+ StringCopy(Character->Rank, sizeof(Character->Rank),
+ (const char*)sqlite3_column_text(Stmt, 4));
+ StringCopy(Character->Title, sizeof(Character->Title),
+ (const char*)sqlite3_column_text(Stmt, 5));
+ Character->Level = sqlite3_column_int(Stmt, 6);
+ StringCopy(Character->Profession, sizeof(Character->Profession),
+ (const char*)sqlite3_column_text(Stmt, 7));
+ StringCopy(Character->Residence, sizeof(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));
}
return true;
@@ -419,6 +744,7 @@ bool GetCharacterRight(int CharacterID, const char *Right){
return false;
}
+ AutoStmtReset StmtReset(Stmt);
if(sqlite3_bind_int(Stmt, 1, CharacterID) != SQLITE_OK
|| sqlite3_bind_text(Stmt, 2, Right, -1, NULL) != SQLITE_OK){
LOG_ERR("Failed to bind parameters: %s", sqlite3_errmsg(g_Database));
@@ -443,6 +769,7 @@ bool GetCharacterRights(int CharacterID, DynamicArray<TCharacterRight> *Rights){
return false;
}
+ AutoStmtReset StmtReset(Stmt);
if(sqlite3_bind_int(Stmt, 1, CharacterID) != SQLITE_OK){
LOG_ERR("Failed to bind CharacterID: %s", sqlite3_errmsg(g_Database));
return false;
@@ -473,6 +800,7 @@ bool GetGuildLeaderStatus(int WorldID, int CharacterID){
return false;
}
+ AutoStmtReset StmtReset(Stmt);
if(sqlite3_bind_int(Stmt, 1, WorldID) != SQLITE_OK
|| sqlite3_bind_int(Stmt, 2, CharacterID) != SQLITE_OK){
LOG_ERR("Failed to bind parameters: %s", sqlite3_errmsg(g_Database));
@@ -489,7 +817,7 @@ bool GetGuildLeaderStatus(int WorldID, int CharacterID){
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 && Guild[0] != 0 && Rank != NULL && StringEqCI(Rank, "Leader")){
+ if(Guild != NULL && !StringEmpty(Guild) && Rank != NULL && StringEqCI(Rank, "Leader")){
Result = true;
}
}
@@ -506,6 +834,7 @@ bool IncrementIsOnline(int WorldID, int CharacterID){
return false;
}
+ AutoStmtReset StmtReset(Stmt);
if(sqlite3_bind_int(Stmt, 1, WorldID) != SQLITE_OK
|| sqlite3_bind_int(Stmt, 2, CharacterID) != SQLITE_OK){
LOG_ERR("Failed to bind parameters: %s", sqlite3_errmsg(g_Database));
@@ -532,6 +861,7 @@ bool DecrementIsOnline(int WorldID, int CharacterID){
return false;
}
+ AutoStmtReset StmtReset(Stmt);
if(sqlite3_bind_int(Stmt, 1, WorldID) != SQLITE_OK
|| sqlite3_bind_int(Stmt, 2, CharacterID) != SQLITE_OK){
LOG_ERR("Failed to bind parameters: %s", sqlite3_errmsg(g_Database));
@@ -556,6 +886,7 @@ bool ClearIsOnline(int WorldID, int *NumAffectedCharacters){
return false;
}
+ AutoStmtReset StmtReset(Stmt);
if(sqlite3_bind_int(Stmt, 1, WorldID) != SQLITE_OK){
LOG_ERR("Failed to bind WorldID: %s", sqlite3_errmsg(g_Database));
return false;
@@ -588,6 +919,7 @@ bool LogoutCharacter(int WorldID, int CharacterID, int Level,
return false;
}
+ AutoStmtReset StmtReset(Stmt);
if(sqlite3_bind_int(Stmt, 1, WorldID) != SQLITE_OK
|| sqlite3_bind_int(Stmt, 2, CharacterID) != SQLITE_OK
|| sqlite3_bind_int(Stmt, 3, Level) != SQLITE_OK
@@ -619,6 +951,7 @@ bool GetCharacterIndexEntries(int WorldID, int MinimumCharacterID,
return false;
}
+ AutoStmtReset StmtReset(Stmt);
if(sqlite3_bind_int(Stmt, 1, WorldID) != SQLITE_OK
|| sqlite3_bind_int(Stmt, 2, MinimumCharacterID) != SQLITE_OK
|| sqlite3_bind_int(Stmt, 3, MaxEntries) != SQLITE_OK){
@@ -659,6 +992,7 @@ bool InsertCharacterDeath(int WorldID, int CharacterID, int Level,
return false;
}
+ AutoStmtReset StmtReset(Stmt);
if(sqlite3_bind_int(Stmt, 1, WorldID) != SQLITE_OK
|| sqlite3_bind_int(Stmt, 2, CharacterID) != SQLITE_OK
|| sqlite3_bind_int(Stmt, 3, Level) != SQLITE_OK
@@ -691,6 +1025,7 @@ bool InsertBuddy(int WorldID, int AccountID, int BuddyID){
return false;
}
+ AutoStmtReset StmtReset(Stmt);
if(sqlite3_bind_int(Stmt, 1, WorldID) != SQLITE_OK
|| sqlite3_bind_int(Stmt, 2, AccountID) != SQLITE_OK
|| sqlite3_bind_int(Stmt, 3, BuddyID) != SQLITE_OK){
@@ -715,6 +1050,7 @@ bool DeleteBuddy(int WorldID, int AccountID, int BuddyID){
return false;
}
+ AutoStmtReset StmtReset(Stmt);
if(sqlite3_bind_int(Stmt, 1, WorldID) != SQLITE_OK
|| sqlite3_bind_int(Stmt, 2, AccountID) != SQLITE_OK
|| sqlite3_bind_int(Stmt, 3, BuddyID) != SQLITE_OK){
@@ -745,6 +1081,7 @@ bool GetBuddies(int WorldID, int AccountID, DynamicArray<TAccountBuddy> *Buddies
return false;
}
+ AutoStmtReset StmtReset(Stmt);
if(sqlite3_bind_int(Stmt, 1, WorldID) != SQLITE_OK
|| sqlite3_bind_int(Stmt, 2, AccountID) != SQLITE_OK){
LOG_ERR("Failed to bind parameters: %s", sqlite3_errmsg(g_Database));
@@ -776,6 +1113,7 @@ bool GetWorldInvitation(int WorldID, int CharacterID){
return false;
}
+ AutoStmtReset StmtReset(Stmt);
if(sqlite3_bind_int(Stmt, 1, WorldID) != SQLITE_OK
|| sqlite3_bind_int(Stmt, 2, CharacterID) != SQLITE_OK){
LOG_ERR("Failed to bind parameters: %s", sqlite3_errmsg(g_Database));
@@ -800,6 +1138,7 @@ bool InsertLoginAttempt(int AccountID, int IPAddress, bool Failed){
return false;
}
+ AutoStmtReset StmtReset(Stmt);
if(sqlite3_bind_int(Stmt, 1, AccountID) != SQLITE_OK
|| sqlite3_bind_int(Stmt, 2, IPAddress) != SQLITE_OK
|| sqlite3_bind_int(Stmt, 3, (Failed ? 1 : 0)) != SQLITE_OK){
@@ -815,47 +1154,49 @@ bool InsertLoginAttempt(int AccountID, int IPAddress, bool Failed){
return true;
}
-int GetAccountLoginAttempts(int AccountID, int TimeWindow){
+int GetAccountFailedLoginAttempts(int AccountID, int TimeWindow){
sqlite3_stmt *Stmt = PrepareQuery(
"SELECT COUNT(*) FROM LoginAttempts"
- " WHERE AccountID = ?1 AND Timestamp >= (UNIXEPOCH() - ?2)");
+ " WHERE AccountID = ?1 AND Timestamp >= (UNIXEPOCH() - ?2) AND Failed != 0");
if(Stmt == NULL){
LOG_ERR("Failed to prepare query");
return 0;
}
+ AutoStmtReset StmtReset(Stmt);
if(sqlite3_bind_int(Stmt, 1, AccountID) != SQLITE_OK
|| sqlite3_bind_int(Stmt, 2, TimeWindow) != SQLITE_OK){
LOG_ERR("Failed to bind parameters: %s", sqlite3_errmsg(g_Database));
- return false;
+ return 0;
}
if(sqlite3_step(Stmt) != SQLITE_ROW){
LOG_ERR("Failed to execute query: %s", sqlite3_errmsg(g_Database));
- return false;
+ return 0;
}
return sqlite3_column_int(Stmt, 0);
}
-int GetIPAddressLoginAttempts(int IPAddress, int TimeWindow){
+int GetIPAddressFailedLoginAttempts(int IPAddress, int TimeWindow){
sqlite3_stmt *Stmt = PrepareQuery(
"SELECT COUNT(*) FROM LoginAttempts"
- " WHERE IPAddress = ?1 AND Timestamp >= (UNIXEPOCH() - ?2)");
+ " WHERE IPAddress = ?1 AND Timestamp >= (UNIXEPOCH() - ?2) AND Failed != 0");
if(Stmt == NULL){
LOG_ERR("Failed to prepare query");
return 0;
}
+ AutoStmtReset StmtReset(Stmt);
if(sqlite3_bind_int(Stmt, 1, IPAddress) != SQLITE_OK
|| sqlite3_bind_int(Stmt, 2, TimeWindow) != SQLITE_OK){
LOG_ERR("Failed to bind parameters: %s", sqlite3_errmsg(g_Database));
- return false;
+ return 0;
}
if(sqlite3_step(Stmt) != SQLITE_ROW){
LOG_ERR("Failed to execute query: %s", sqlite3_errmsg(g_Database));
- return false;
+ return 0;
}
return sqlite3_column_int(Stmt, 0);
@@ -878,6 +1219,7 @@ bool FinishHouseAuctions(int WorldID, DynamicArray<THouseAuction> *Auctions){
return false;
}
+ AutoStmtReset StmtReset(Stmt);
if(sqlite3_bind_int(Stmt, 1, WorldID) != SQLITE_OK){
LOG_ERR("Failed to bind WorldID: %s", sqlite3_errmsg(g_Database));
return false;
@@ -915,6 +1257,7 @@ bool FinishHouseTransfers(int WorldID, DynamicArray<THouseTransfer> *Transfers){
return false;
}
+ AutoStmtReset StmtReset(Stmt);
if(sqlite3_bind_int(Stmt, 1, WorldID) != SQLITE_OK){
LOG_ERR("Failed to bind WorldID: %s", sqlite3_errmsg(g_Database));
return false;
@@ -952,6 +1295,7 @@ bool GetFreeAccountEvictions(int WorldID, DynamicArray<THouseEviction> *Eviction
return false;
}
+ AutoStmtReset StmtReset(Stmt);
if(sqlite3_bind_int(Stmt, 1, WorldID) != SQLITE_OK){
LOG_ERR("Failed to bind WorldID: %s", sqlite3_errmsg(g_Database));
return false;
@@ -985,6 +1329,7 @@ bool GetDeletedCharacterEvictions(int WorldID, DynamicArray<THouseEviction> *Evi
return false;
}
+ AutoStmtReset StmtReset(Stmt);
if(sqlite3_bind_int(Stmt, 1, WorldID) != SQLITE_OK){
LOG_ERR("Failed to bind WorldID: %s", sqlite3_errmsg(g_Database));
return false;
@@ -1014,6 +1359,7 @@ bool InsertHouseOwner(int WorldID, int HouseID, int OwnerID, int PaidUntil){
return false;
}
+ AutoStmtReset StmtReset(Stmt);
if(sqlite3_bind_int(Stmt, 1, WorldID) != SQLITE_OK
|| sqlite3_bind_int(Stmt, 2, HouseID) != SQLITE_OK
|| sqlite3_bind_int(Stmt, 3, OwnerID) != SQLITE_OK
@@ -1039,6 +1385,7 @@ bool UpdateHouseOwner(int WorldID, int HouseID, int OwnerID, int PaidUntil){
return false;
}
+ AutoStmtReset StmtReset(Stmt);
if(sqlite3_bind_int(Stmt, 1, WorldID) != SQLITE_OK
|| sqlite3_bind_int(Stmt, 2, HouseID) != SQLITE_OK
|| sqlite3_bind_int(Stmt, 3, OwnerID) != SQLITE_OK
@@ -1064,6 +1411,7 @@ bool DeleteHouseOwner(int WorldID, int HouseID){
return false;
}
+ AutoStmtReset StmtReset(Stmt);
if(sqlite3_bind_int(Stmt, 1, WorldID) != SQLITE_OK
|| sqlite3_bind_int(Stmt, 2, HouseID) != SQLITE_OK){
LOG_ERR("Failed to bind parameters: %s", sqlite3_errmsg(g_Database));
@@ -1090,6 +1438,7 @@ bool GetHouseOwners(int WorldID, DynamicArray<THouseOwner> *Owners){
return false;
}
+ AutoStmtReset StmtReset(Stmt);
if(sqlite3_bind_int(Stmt, 1, WorldID) != SQLITE_OK){
LOG_ERR("Failed to bind WorldID: %s", sqlite3_errmsg(g_Database));
return false;
@@ -1122,6 +1471,7 @@ bool GetHouseAuctions(int WorldID, DynamicArray<int> *Auctions){
return false;
}
+ AutoStmtReset StmtReset(Stmt);
if(sqlite3_bind_int(Stmt, 1, WorldID) != SQLITE_OK){
LOG_ERR("Failed to bind WorldID: %s", sqlite3_errmsg(g_Database));
return false;
@@ -1147,6 +1497,7 @@ bool StartHouseAuction(int WorldID, int HouseID){
return false;
}
+ AutoStmtReset StmtReset(Stmt);
if(sqlite3_bind_int(Stmt, 1, WorldID) != SQLITE_OK
|| sqlite3_bind_int(Stmt, 2, HouseID) != SQLITE_OK){
LOG_ERR("Failed to bind parameters: %s", sqlite3_errmsg(g_Database));
@@ -1169,6 +1520,7 @@ bool DeleteHouses(int WorldID){
return false;
}
+ AutoStmtReset StmtReset(Stmt);
if(sqlite3_bind_int(Stmt, 1, WorldID) != SQLITE_OK){
LOG_ERR("Failed to bind WorldID: %s", sqlite3_errmsg(g_Database));
return false;
@@ -1193,6 +1545,7 @@ bool InsertHouses(int WorldID, int NumHouses, THouse *Houses){
return false;
}
+ AutoStmtReset StmtReset(Stmt);
if(sqlite3_bind_int(Stmt, 1, WorldID) != SQLITE_OK){
LOG_ERR("Failed to bind WorldID: %s", sqlite3_errmsg(g_Database));
return false;
@@ -1237,6 +1590,7 @@ bool ExcludeFromAuctions(int WorldID, int CharacterID, int Duration, int Banishm
return false;
}
+ AutoStmtReset StmtReset(Stmt);
if(sqlite3_bind_int(Stmt, 1, WorldID) != SQLITE_OK
|| sqlite3_bind_int(Stmt, 2, CharacterID) != SQLITE_OK
|| sqlite3_bind_int(Stmt, 3, Duration) != SQLITE_OK
@@ -1269,6 +1623,7 @@ TNamelockStatus GetNamelockStatus(int CharacterID){
return Status;
}
+ AutoStmtReset StmtReset(Stmt);
if(sqlite3_bind_int(Stmt, 1, CharacterID) != SQLITE_OK){
LOG_ERR("Failed to bind parameters: %s", sqlite3_errmsg(g_Database));
return Status;
@@ -1298,6 +1653,7 @@ bool InsertNamelock(int CharacterID, int IPAddress, int GamemasterID,
return false;
}
+ AutoStmtReset StmtReset(Stmt);
if(sqlite3_bind_int(Stmt, 1, CharacterID) != SQLITE_OK
|| sqlite3_bind_int(Stmt, 2, IPAddress) != SQLITE_OK
|| sqlite3_bind_int(Stmt, 3, GamemasterID) != SQLITE_OK
@@ -1325,6 +1681,7 @@ bool IsAccountBanished(int AccountID){
return false;
}
+ AutoStmtReset StmtReset(Stmt);
if(sqlite3_bind_int(Stmt, 1, AccountID) != SQLITE_OK){
LOG_ERR("Failed to bind AccountID: %s", sqlite3_errmsg(g_Database));
return false;
@@ -1351,6 +1708,7 @@ TBanishmentStatus GetBanishmentStatus(int CharacterID){
return Status;
}
+ AutoStmtReset StmtReset(Stmt);
if(sqlite3_bind_int(Stmt, 1, CharacterID) != SQLITE_OK){
LOG_ERR("Failed to bind parameters: %s", sqlite3_errmsg(g_Database));
return Status;
@@ -1391,6 +1749,7 @@ bool InsertBanishment(int CharacterID, int IPAddress, int GamemasterID,
return false;
}
+ AutoStmtReset StmtReset(Stmt);
if(sqlite3_bind_int(Stmt, 1, CharacterID) != SQLITE_OK
|| sqlite3_bind_int(Stmt, 2, IPAddress) != SQLITE_OK
|| sqlite3_bind_int(Stmt, 3, GamemasterID) != SQLITE_OK
@@ -1419,6 +1778,7 @@ int GetNotationCount(int CharacterID){
return 0;
}
+ AutoStmtReset StmtReset(Stmt);
if(sqlite3_bind_int(Stmt, 1, CharacterID) != SQLITE_OK){
LOG_ERR("Failed to bind parameters: %s", sqlite3_errmsg(g_Database));
return 0;
@@ -1444,6 +1804,7 @@ bool InsertNotation(int CharacterID, int IPAddress, int GamemasterID,
return false;
}
+ AutoStmtReset StmtReset(Stmt);
if(sqlite3_bind_int(Stmt, 1, CharacterID) != SQLITE_OK
|| sqlite3_bind_int(Stmt, 2, IPAddress) != SQLITE_OK
|| sqlite3_bind_int(Stmt, 3, GamemasterID) != SQLITE_OK
@@ -1471,6 +1832,7 @@ bool IsIPBanished(int IPAddress){
return false;
}
+ AutoStmtReset StmtReset(Stmt);
if(sqlite3_bind_int(Stmt, 1, IPAddress) != SQLITE_OK){
LOG_ERR("Failed to bind IPAddress: %s", sqlite3_errmsg(g_Database));
return false;
@@ -1497,6 +1859,7 @@ bool InsertIPBanishment(int CharacterID, int IPAddress, int GamemasterID,
return false;
}
+ AutoStmtReset StmtReset(Stmt);
if(sqlite3_bind_int(Stmt, 1, CharacterID) != SQLITE_OK
|| sqlite3_bind_int(Stmt, 2, IPAddress) != SQLITE_OK
|| sqlite3_bind_int(Stmt, 3, GamemasterID) != SQLITE_OK
@@ -1525,6 +1888,7 @@ bool IsStatementReported(int WorldID, TStatement *Statement){
return 0;
}
+ AutoStmtReset StmtReset(Stmt);
if(sqlite3_bind_int(Stmt, 1, WorldID) != SQLITE_OK
|| sqlite3_bind_int(Stmt, 2, Statement->Timestamp) != SQLITE_OK
|| sqlite3_bind_int(Stmt, 3, Statement->StatementID) != SQLITE_OK){
@@ -1555,6 +1919,7 @@ bool InsertStatements(int WorldID, int NumStatements, TStatement *Statements){
return false;
}
+ AutoStmtReset StmtReset(Stmt);
if(sqlite3_bind_int(Stmt, 1, WorldID) != SQLITE_OK){
LOG_ERR("Failed to bind WorldID: %s", sqlite3_errmsg(g_Database));
return false;
@@ -1601,6 +1966,7 @@ bool InsertReportedStatement(int WorldID, TStatement *Statement, int BanishmentI
return false;
}
+ AutoStmtReset StmtReset(Stmt);
if(sqlite3_bind_int(Stmt, 1, WorldID) != SQLITE_OK
|| sqlite3_bind_int(Stmt, 2, Statement->Timestamp) != SQLITE_OK
|| sqlite3_bind_int(Stmt, 3, Statement->StatementID) != SQLITE_OK
@@ -1623,6 +1989,39 @@ bool InsertReportedStatement(int WorldID, TStatement *Statement, int BanishmentI
// Info tables
//==============================================================================
+bool GetKillStatistics(int WorldID, DynamicArray<TKillStatistics> *Stats){
+ ASSERT(Stats != NULL);
+ sqlite3_stmt *Stmt = PrepareQuery(
+ "SELECT RaceName, TimesKilled, PlayersKilled"
+ " FROM KillStatistics WHERE WorldID = ?1");
+ if(Stmt == NULL){
+ LOG_ERR("Failed to prepare query");
+ return false;
+ }
+
+ AutoStmtReset StmtReset(Stmt);
+ if(sqlite3_bind_int(Stmt, 1, WorldID) != SQLITE_OK){
+ LOG_ERR("Failed to bind WorldID: %s", sqlite3_errmsg(g_Database));
+ return false;
+ }
+
+ while(sqlite3_step(Stmt) == SQLITE_ROW){
+ TKillStatistics Entry = {};
+ StringCopy(Entry.RaceName, sizeof(Entry.RaceName),
+ (const char*)sqlite3_column_text(Stmt, 0));
+ Entry.TimesKilled = sqlite3_column_int(Stmt, 1);
+ Entry.PlayersKilled = sqlite3_column_int(Stmt, 2);
+ Stats->Push(Entry);
+ }
+
+ if(sqlite3_errcode(g_Database) != SQLITE_DONE){
+ LOG_ERR("Failed to execute query: %s", sqlite3_errmsg(g_Database));
+ return false;
+ }
+
+ return true;
+}
+
bool MergeKillStatistics(int WorldID, int NumStats, TKillStatistics *Stats){
sqlite3_stmt *Stmt = PrepareQuery(
"INSERT INTO KillStatistics (WorldID, RaceName, TimesKilled, PlayersKilled)"
@@ -1634,6 +2033,7 @@ bool MergeKillStatistics(int WorldID, int NumStats, TKillStatistics *Stats){
return false;
}
+ AutoStmtReset StmtReset(Stmt);
if(sqlite3_bind_int(Stmt, 1, WorldID) != SQLITE_OK){
LOG_ERR("failed to bind WorldID: %s", sqlite3_errmsg(g_Database));
return false;
@@ -1660,6 +2060,40 @@ bool MergeKillStatistics(int WorldID, int NumStats, TKillStatistics *Stats){
return true;
}
+bool GetOnlineCharacters(int WorldID, DynamicArray<TOnlineCharacter> *Characters){
+ ASSERT(Characters != NULL);
+ sqlite3_stmt *Stmt = PrepareQuery(
+ "SELECT Name, Level, Profession"
+ " FROM OnlineCharacters WHERE WorldID = ?1");
+ if(Stmt == NULL){
+ LOG_ERR("Failed to prepare query");
+ return false;
+ }
+
+ AutoStmtReset StmtReset(Stmt);
+ if(sqlite3_bind_int(Stmt, 1, WorldID) != SQLITE_OK){
+ LOG_ERR("Failed to bind WorldID: %s", sqlite3_errmsg(g_Database));
+ return false;
+ }
+
+ while(sqlite3_step(Stmt) == SQLITE_ROW){
+ TOnlineCharacter Character = {};
+ StringCopy(Character.Name, sizeof(Character.Name),
+ (const char*)sqlite3_column_text(Stmt, 0));
+ Character.Level = sqlite3_column_int(Stmt, 1);
+ StringCopy(Character.Profession, sizeof(Character.Profession),
+ (const char*)sqlite3_column_text(Stmt, 2));
+ Characters->Push(Character);
+ }
+
+ if(sqlite3_errcode(g_Database) != SQLITE_DONE){
+ LOG_ERR("Failed to execute query: %s", sqlite3_errmsg(g_Database));
+ return false;
+ }
+
+ return true;
+}
+
bool DeleteOnlineCharacters(int WorldID){
sqlite3_stmt *Stmt = PrepareQuery(
"DELETE FROM OnlineCharacters WHERE WorldID = ?1");
@@ -1668,6 +2102,7 @@ bool DeleteOnlineCharacters(int WorldID){
return false;
}
+ AutoStmtReset StmtReset(Stmt);
if(sqlite3_bind_int(Stmt, 1, WorldID) != SQLITE_OK){
LOG_ERR("Failed to bind WorldID: %s", sqlite3_errmsg(g_Database));
return false;
@@ -1690,6 +2125,7 @@ bool InsertOnlineCharacters(int WorldID, int NumCharacters, TOnlineCharacter *Ch
return false;
}
+ AutoStmtReset StmtReset(Stmt);
if(sqlite3_bind_int(Stmt, 1, WorldID) != SQLITE_OK){
LOG_ERR("Failed to bind WorldID: %s", sqlite3_errmsg(g_Database));
return false;
@@ -1719,13 +2155,15 @@ bool InsertOnlineCharacters(int WorldID, int NumCharacters, TOnlineCharacter *Ch
bool CheckOnlineRecord(int WorldID, int NumCharacters, bool *NewRecord){
ASSERT(NewRecord != NULL);
sqlite3_stmt *Stmt = PrepareQuery(
- "UPDATE Worlds SET OnlineRecord = ?2"
+ "UPDATE Worlds SET OnlineRecord = ?2,"
+ " OnlineRecordTimestamp = UNIXEPOCH()"
" WHERE WorldID = ?1 AND OnlineRecord < ?2");
if(Stmt == NULL){
LOG_ERR("Failed to prepare query");
return false;
}
+ AutoStmtReset StmtReset(Stmt);
if(sqlite3_bind_int(Stmt, 1, WorldID) != SQLITE_OK
|| sqlite3_bind_int(Stmt, 2, NumCharacters) != SQLITE_OK){
LOG_ERR("Failed to bind parameters: %s", sqlite3_errmsg(g_Database));
diff --git a/src/querymanager.cc b/src/querymanager.cc
index 5086032..1ed818e 100644
--- a/src/querymanager.cc
+++ b/src/querymanager.cc
@@ -4,6 +4,7 @@
#if OS_LINUX
# include <errno.h>
# include <signal.h>
+# include <sys/random.h>
#else
# error "Operating system not currently supported."
#endif
@@ -31,7 +32,7 @@ void LogAdd(const char *Prefix, const char *Format, ...){
vsnprintf(Entry, sizeof(Entry), Format, ap);
va_end(ap);
- if(Entry[0] != 0){
+ if(!StringEmpty(Entry)){
struct tm LocalTime = GetLocalTime(time(NULL));
fprintf(stdout, "%04d/%02d/%02d %02d:%02d:%02d [%s] %s\n",
LocalTime.tm_year + 1900, LocalTime.tm_mon + 1, LocalTime.tm_mday,
@@ -48,7 +49,7 @@ void LogAddVerbose(const char *Prefix, const char *Function,
vsnprintf(Entry, sizeof(Entry), Format, ap);
va_end(ap);
- if(Entry[0] != 0){
+ if(!StringEmpty(Entry)){
(void)File;
(void)Line;
struct tm LocalTime = GetLocalTime(time(NULL));
@@ -94,6 +95,30 @@ void SleepMS(int64 DurationMS){
#endif
}
+void CryptoRandom(uint8 *Buffer, int Count){
+#if 0 && OS_WINDOWS
+ // TODO(fusion): Not sure about this one.
+ if(BCryptGenRandom(NULL, Buffer, (ULONG)Count, BCRYPT_USE_SYSTEM_PREFERRED_RNG) != STATUS_SUCCESS){
+ PANIC("Failed to generate cryptographically safe random data.");
+ }
+#else
+ // NOTE(fusion): This shouldn't fail unless the kernel doesn't implement the
+ // required system call, in which case we should have a fallback method. See
+ // `getrandom(2)` for the whole story.
+ if((int)getrandom(Buffer, Count, 0) != Count){
+ PANIC("Failed to generate cryptographically safe random data.");
+ }
+#endif
+}
+
+int RoundSecondsToDays(int Seconds){
+ return (Seconds + 86399) / 86400;
+}
+
+bool StringEmpty(const char *String){
+ return String[0] == 0;
+}
+
bool StringEq(const char *A, const char *B){
int Index = 0;
while(true){
@@ -139,6 +164,11 @@ bool StringCopy(char *Dest, int DestCapacity, const char *Src){
}
bool ParseIPAddress(const char *String, int *OutAddr){
+ if(StringEmpty(String)){
+ LOG_ERR("Empty IP Address string");
+ return false;
+ }
+
int Addr[4];
if(sscanf(String, "%d.%d.%d.%d", &Addr[0], &Addr[1], &Addr[2], &Addr[3]) != 4){
LOG_ERR("Invalid IP Address format \"%s\"", String);
diff --git a/src/querymanager.hh b/src/querymanager.hh
index f0245d4..04f74e9 100644
--- a/src/querymanager.hh
+++ b/src/querymanager.hh
@@ -95,7 +95,10 @@ void LogAddVerbose(const char *Prefix, const char *Function,
struct tm GetLocalTime(time_t t);
int64 GetClockMonotonicMS(void);
void SleepMS(int64 DurationMS);
+void CryptoRandom(uint8 *Buffer, int Count);
+int RoundSecondsToDays(int Seconds);
+bool StringEmpty(const char *String);
bool StringEq(const char *A, const char *B);
bool StringEqCI(const char *A, const char *B);
bool StringCopyN(char *Dest, int DestCapacity, const char *Src, int SrcLength);
@@ -370,7 +373,7 @@ struct TWriteBuffer{
}
void Rewrite16(int Position, uint16 Value){
- if((Position + 2) <= this->Position){
+ if((Position + 2) <= this->Position && !this->Overflowed()){
BufferWrite16LE(this->Buffer + Position, Value);
}
}
@@ -573,22 +576,13 @@ enum : int {
QUERY_EXCLUDE_FROM_AUCTIONS = 51,
QUERY_CANCEL_HOUSE_TRANSFER = 52,
QUERY_LOAD_WORLD_CONFIG = 53,
- QUERY_GET_KEPT_CHARACTERS = 200,
- QUERY_GET_DELETED_CHARACTERS = 201,
- QUERY_DELETE_OLD_CHARACTER = 202,
- QUERY_GET_HIDDEN_CHARACTERS = 203,
- QUERY_CREATE_HIGHSCORES = 204,
- QUERY_CREATE_CENSUS = 205,
- QUERY_CREATE_KILL_STATISTICS = 206,
- QUERY_GET_PLAYERS_ONLINE = 207,
- QUERY_GET_WORLDS = 208,
- QUERY_GET_SERVER_LOAD = 209,
- QUERY_INSERT_PAYMENT_DATA_OLD = 210,
- QUERY_ADD_PAYMENT_OLD = 211,
- QUERY_CANCEL_PAYMENT_OLD = 212,
- QUERY_INSERT_PAYMENT_DATA_NEW = 213,
- QUERY_ADD_PAYMENT_NEW = 214,
- QUERY_CANCEL_PAYMENT_NEW = 215,
+ QUERY_CREATE_ACCOUNT = 100,
+ QUERY_CREATE_CHARACTER = 101,
+ QUERY_GET_ACCOUNT_SUMMARY = 102,
+ QUERY_GET_CHARACTER_PROFILE = 103,
+ QUERY_GET_WORLDS = 150,
+ QUERY_GET_ONLINE_CHARACTERS = 151,
+ QUERY_GET_KILL_STATISTICS = 152,
};
enum ConnectionState: int {
@@ -664,26 +658,25 @@ void ProcessLoadPlayersQuery(TConnection *Connection, TReadBuffer *Buffer);
void ProcessExcludeFromAuctionsQuery(TConnection *Connection, TReadBuffer *Buffer);
void ProcessCancelHouseTransferQuery(TConnection *Connection, TReadBuffer *Buffer);
void ProcessLoadWorldConfigQuery(TConnection *Connection, TReadBuffer *Buffer);
-void ProcessGetKeptCharactersQuery(TConnection *Connection, TReadBuffer *Buffer);
-void ProcessGetDeletedCharactersQuery(TConnection *Connection, TReadBuffer *Buffer);
-void ProcessDeleteOldCharacterQuery(TConnection *Connection, TReadBuffer *Buffer);
-void ProcessGetHiddenCharactersQuery(TConnection *Connection, TReadBuffer *Buffer);
-void ProcessCreateHighscoresQuery(TConnection *Connection, TReadBuffer *Buffer);
-void ProcessCreateCensusQuery(TConnection *Connection, TReadBuffer *Buffer);
-void ProcessCreateKillStatisticsQuery(TConnection *Connection, TReadBuffer *Buffer);
-void ProcessGetPlayersOnlineQuery(TConnection *Connection, TReadBuffer *Buffer);
+void ProcessCreateAccountQuery(TConnection *Connection, TReadBuffer *Buffer);
+void ProcessCreateCharacterQuery(TConnection *Connection, TReadBuffer *Buffer);
+void ProcessGetAccountSummaryQuery(TConnection *Connection, TReadBuffer *Buffer);
+void ProcessGetCharacterProfileQuery(TConnection *Connection, TReadBuffer *Buffer);
void ProcessGetWorldsQuery(TConnection *Connection, TReadBuffer *Buffer);
-void ProcessGetServerLoadQuery(TConnection *Connection, TReadBuffer *Buffer);
-void ProcessInsertPaymentDataOldQuery(TConnection *Connection, TReadBuffer *Buffer);
-void ProcessAddPaymentOldQuery(TConnection *Connection, TReadBuffer *Buffer);
-void ProcessCancelPaymentOldQuery(TConnection *Connection, TReadBuffer *Buffer);
-void ProcessInsertPaymentDataNewQuery(TConnection *Connection, TReadBuffer *Buffer);
-void ProcessAddPaymentNewQuery(TConnection *Connection, TReadBuffer *Buffer);
-void ProcessCancelPaymentNewQuery(TConnection *Connection, TReadBuffer *Buffer);
+void ProcessGetOnlineCharactersQuery(TConnection *Connection, TReadBuffer *Buffer);
void ProcessConnectionQuery(TConnection *Connection);
// database.cc
//==============================================================================
+struct TWorld{
+ char Name[30];
+ int Type;
+ int NumPlayers;
+ int MaxPlayers;
+ int OnlineRecord;
+ int OnlineRecordTimestamp;
+};
+
struct TWorldConfig{
int Type;
int RebootTime;
@@ -695,7 +688,7 @@ struct TWorldConfig{
int PremiumNewbieBuffer;
};
-struct TAccountData{
+struct TAccount{
int AccountID;
char Email[100];
uint8 Auth[64];
@@ -709,14 +702,23 @@ struct TAccountBuddy{
char Name[30];
};
-struct TCharacterLoginData{
+struct TCharacterEndpoint{
char Name[30];
char WorldName[30];
int WorldAddress;
int WorldPort;
};
-struct TCharacterData{
+struct TCharacterSummary{
+ char Name[30];
+ char World[30];
+ int Level;
+ char Profession[30];
+ bool Online;
+ bool Deleted;
+};
+
+struct TCharacterLoginData{
int WorldID;
int CharacterID;
int AccountID;
@@ -728,6 +730,22 @@ struct TCharacterData{
bool Deleted;
};
+struct TCharacterProfile{
+ 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];
+ int LastLogin;
+ int PremiumDays;
+ bool Online;
+ bool Deleted;
+};
+
struct TCharacterRight{
char Name[30];
};
@@ -823,14 +841,23 @@ public:
// NOTE(fusion): Primary tables.
int GetWorldID(const char *WorldName);
+bool GetWorlds(DynamicArray<TWorld> *Worlds);
bool GetWorldConfig(int WorldID, TWorldConfig *WorldConfig);
-bool GetAccountData(int AccountID, TAccountData *Account);
+bool AccountExists(int AccountID, const char *Email);
+bool AccountNumberExists(int AccountID);
+bool AccountEmailExists(const char *Email);
+bool CreateAccount(int AccountID, const char *Email, const uint8 *Auth, int AuthSize);
+bool GetAccountData(int AccountID, TAccount *Account);
int GetAccountOnlineCharacters(int AccountID);
bool IsCharacterOnline(int CharacterID);
bool ActivatePendingPremiumDays(int AccountID);
-bool GetCharacterList(int AccountID, DynamicArray<TCharacterLoginData> *Characters);
+bool GetCharacterEndpoints(int AccountID, DynamicArray<TCharacterEndpoint> *Characters);
+bool GetCharacterSummaries(int AccountID, DynamicArray<TCharacterSummary> *Characters);
+bool CharacterNameExists(const char *Name);
+bool CreateCharacter(int WorldID, int AccountID, const char *Name, int Sex);
int GetCharacterID(int WorldID, const char *CharacterName);
-bool GetCharacterData(const char *CharacterName, TCharacterData *Character);
+bool GetCharacterLoginData(const char *CharacterName, TCharacterLoginData *Character);
+bool GetCharacterProfile(const char *CharacterName, TCharacterProfile *Character);
bool GetCharacterRight(int CharacterID, const char *Right);
bool GetCharacterRights(int CharacterID, DynamicArray<TCharacterRight> *Rights);
bool GetGuildLeaderStatus(int WorldID, int CharacterID);
@@ -849,8 +876,8 @@ bool DeleteBuddy(int WorldID, int AccountID, int BuddyID);
bool GetBuddies(int WorldID, int AccountID, DynamicArray<TAccountBuddy> *Buddies);
bool GetWorldInvitation(int WorldID, int CharacterID);
bool InsertLoginAttempt(int AccountID, int IPAddress, bool Failed);
-int GetAccountLoginAttempts(int AccountID, int TimeWindow);
-int GetIPAddressLoginAttempts(int IPAddress, int TimeWindow);
+int GetAccountFailedLoginAttempts(int AccountID, int TimeWindow);
+int GetIPAddressFailedLoginAttempts(int IPAddress, int TimeWindow);
// NOTE(fusion): House tables.
bool FinishHouseAuctions(int WorldID, DynamicArray<THouseAuction> *Auctions);
@@ -889,7 +916,9 @@ bool InsertReportedStatement(int WorldID, TStatement *Statement, int BanishmentI
int ReporterID, const char *Reason, const char *Comment);
// NOTE(fusion): Info tables.
+bool GetKillStatistics(int WorldID, DynamicArray<TKillStatistics> *Stats);
bool MergeKillStatistics(int WorldID, int NumStats, TKillStatistics *Stats);
+bool GetOnlineCharacters(int WorldID, DynamicArray<TOnlineCharacter> *Characters);
bool DeleteOnlineCharacters(int WorldID);
bool InsertOnlineCharacters(int WorldID, int NumCharacters, TOnlineCharacter *Characters);
bool CheckOnlineRecord(int WorldID, int NumCharacters, bool *NewRecord);
@@ -909,6 +938,7 @@ void ExitDatabase(void);
//==============================================================================
void SHA256(const uint8 *Input, int InputBytes, uint8 *Digest);
bool TestPassword(const uint8 *Auth, int AuthSize, const char *Password);
+bool GenerateAuth(const char *Password, uint8 *Auth, int AuthSize);
bool CheckSHA256(void);
#endif //TIBIA_QUERYMANAGER_HH_
diff --git a/src/sha256.cc b/src/sha256.cc
index 1070c6b..9e2bb23 100644
--- a/src/sha256.cc
+++ b/src/sha256.cc
@@ -144,7 +144,24 @@ bool TestPassword(const uint8 *Auth, int AuthSize, const char *Password){
for(int i = 0; i < 32; i += 1){
Result |= Digest[i] ^ Hash[i];
}
- return Result == 0;;
+ return Result == 0;
+}
+
+bool GenerateAuth(const char *Password, uint8 *Auth, int AuthSize){
+ if(AuthSize != 64){
+ LOG_ERR("Expected 64 bytes buffer for authentication data (got %d)", AuthSize);
+ return false;
+ }
+
+ uint8 *Hash = &Auth[ 0];
+ uint8 *Salt = &Auth[32];
+ CryptoRandom(Salt, 32);
+ SHA256((const uint8*)Password, (int)strlen(Password), Hash);
+ for(int i = 0; i < 32; i += 1){
+ Hash[i] ^= Salt[i];
+ }
+ SHA256(Hash, 32, Hash);
+ return true;
}
// CheckSHA256
generated by cgit v1.2.3 (git 2.46.0) at 2026-07-14 22:30:44 +0000