From 8bc672f7111287e6e0b66e5897e80795652dc1c7 Mon Sep 17 00:00:00 2001 From: fusion32 Date: Mon, 14 Jul 2025 19:46:36 -0300 Subject: impl banishment, deaths, and buddies queries --- src/connections.cc | 310 +++++++++++++++++++++++++++++++++++++++++++++++----- src/database.cc | 296 +++++++++++++++++++++++++++++++++++++++++++++++-- src/querymanager.hh | 34 +++++- 3 files changed, 592 insertions(+), 48 deletions(-) (limited to 'src') diff --git a/src/connections.cc b/src/connections.cc index 3672eaa..e4ac2f2 100644 --- a/src/connections.cc +++ b/src/connections.cc @@ -402,6 +402,22 @@ void ExitConnections(void){ // Connection Queries //============================================================================== +void CompoundBanishment(TBanishmentStatus Status, int *Days, bool *FinalWarning){ + // TODO(fusion): We might want to add all these constants as config values. + ASSERT(Days != NULL && FinalWarning != NULL); + if(Status.FinalWarning){ + *FinalWarning = false; + *Days = 0; // permanent + }else if(Status.TimesBanished > 5 || *FinalWarning){ + *FinalWarning = true; + if(*Days < 30){ + *Days = 30; + }else{ + *Days *= 2; + } + } +} + TWriteBuffer PrepareResponse(TConnection *Connection, int Status){ if(Connection->State != CONNECTION_PROCESSING){ LOG_ERR("Connection %s is not processing query (State: %d)", @@ -534,7 +550,6 @@ void ProcessSetNamelockQuery(TConnection *Connection, TReadBuffer *Buffer){ Buffer->ReadString(Reason, sizeof(Reason)); Buffer->ReadString(Comment, sizeof(Comment)); - // TODO(fusion): Might not even allow empty ip string. int IPAddress = 0; if(IPString[0] != 0 && !ParseIPAddress(IPString, &IPAddress)){ SendQueryStatusFailed(Connection); @@ -588,14 +603,13 @@ void ProcessBanishAccountQuery(TConnection *Connection, TReadBuffer *Buffer){ char IPString[16]; char Reason[200]; char Comment[200]; - int GamemasterID = Buffer->Read16(); + int GamemasterID = (int)Buffer->Read32(); Buffer->ReadString(CharacterName, sizeof(CharacterName)); Buffer->ReadString(IPString, sizeof(IPString)); Buffer->ReadString(Reason, sizeof(Reason)); Buffer->ReadString(Comment, sizeof(Comment)); bool FinalWarning = Buffer->ReadFlag(); - // TODO(fusion): Might not even allow empty ip string. int IPAddress = 0; if(IPString[0] != 0 && !ParseIPAddress(IPString, &IPAddress)){ SendQueryStatusFailed(Connection); @@ -626,17 +640,9 @@ void ProcessBanishAccountQuery(TConnection *Connection, TReadBuffer *Buffer){ return; } - // TODO(fusion): We might want to add all these constants as config values. - int BanishmentID; + int BanishmentID = 0; int Days = 7; - if(Status.FinalWarning){ - Days = 0; // permanent - FinalWarning = false; - }else if(Status.TimesBanished >= 5 || FinalWarning){ - Days = 30; - FinalWarning = true; - } - + CompoundBanishment(Status, &Days, &FinalWarning); if(!InsertBanishment(CharacterID, IPAddress, GamemasterID, Reason, Comment, FinalWarning, Days * 86400, &BanishmentID)){ SendQueryStatusFailed(Connection); @@ -656,27 +662,277 @@ void ProcessBanishAccountQuery(TConnection *Connection, TReadBuffer *Buffer){ } void ProcessSetNotationQuery(TConnection *Connection, TReadBuffer *Buffer){ - SendQueryStatusFailed(Connection); + if(Connection->ApplicationType != APPLICATION_TYPE_GAME){ + SendQueryStatusFailed(Connection); + return; + } + + char CharacterName[30]; + char IPString[16]; + char Reason[200]; + char Comment[200]; + int GamemasterID = Buffer->Read32(); + Buffer->ReadString(CharacterName, sizeof(CharacterName)); + Buffer->ReadString(IPString, sizeof(IPString)); + Buffer->ReadString(Reason, sizeof(Reason)); + Buffer->ReadString(Comment, sizeof(Comment)); + + int IPAddress = 0; + if(IPString[0] != 0 && !ParseIPAddress(IPString, &IPAddress)){ + SendQueryStatusFailed(Connection); + return; + } + + TransactionScope Tx("SetNotation"); + if(!Tx.Begin()){ + SendQueryStatusFailed(Connection); + return; + } + + int CharacterID = GetCharacterID(Connection->WorldID, CharacterName); + if(CharacterID == 0){ + SendQueryStatusError(Connection, 1); + return; + } + + // TODO(fusion): Might be `NO_BANISHMENT`. + if(!GetCharacterRight(CharacterID, "NOTATION")){ + SendQueryStatusError(Connection, 2); + return; + } + + int BanishmentID = 0; + if(GetNotationCount(CharacterID) >= 5){ + int BanishmentDays = 7; + bool FinalWarning = false; + TBanishmentStatus Status = GetBanishmentStatus(CharacterID); + CompoundBanishment(Status, &BanishmentDays, &FinalWarning); + if(!InsertBanishment(CharacterID, IPAddress, 0, "Excessive Notations", + "", FinalWarning, BanishmentDays, &BanishmentID)){ + SendQueryStatusFailed(Connection); + return; + } + } + + if(!InsertNotation(CharacterID, IPAddress, GamemasterID, Reason, Comment)){ + SendQueryStatusFailed(Connection); + return; + } + + if(!Tx.Commit()){ + SendQueryStatusFailed(Connection); + return; + } + + TWriteBuffer WriteBuffer = PrepareResponse(Connection, QUERY_STATUS_OK); + WriteBuffer.Write32((uint32)BanishmentID); + SendResponse(Connection, &WriteBuffer); } void ProcessReportStatementQuery(TConnection *Connection, TReadBuffer *Buffer){ - SendQueryStatusFailed(Connection); + if(Connection->ApplicationType != APPLICATION_TYPE_GAME){ + SendQueryStatusFailed(Connection); + return; + } + + char CharacterName[30]; + char Reason[200]; + char Comment[200]; + int ReporterID = Buffer->Read32(); + Buffer->ReadString(CharacterName, sizeof(CharacterName)); + Buffer->ReadString(Reason, sizeof(Reason)); + Buffer->ReadString(Comment, sizeof(Comment)); + int BanishmentID = Buffer->Read32(); + int StatementID = Buffer->Read32(); + int NumStatements = Buffer->Read16(); + + if(StatementID == 0){ + LOG_ERR("Missing reported statement id"); + SendQueryStatusFailed(Connection); + return; + } + + if(NumStatements == 0){ + LOG_ERR("Missing report statements"); + SendQueryStatusFailed(Connection); + return; + } + + TStatement *ReportedStatement = NULL; + TStatement *Statements = (TStatement*)alloca(NumStatements * sizeof(TStatement)); + for(int i = 0; i < NumStatements; i += 1){ + Statements[i].StatementID = (int)Buffer->Read32(); + Statements[i].Timestamp = (int)Buffer->Read32(); + Statements[i].CharacterID = (int)Buffer->Read32(); + Buffer->ReadString(Statements[i].Channel, sizeof(Statements[i].Channel)); + Buffer->ReadString(Statements[i].Text, sizeof(Statements[i].Text)); + + if(Statements[i].StatementID == StatementID){ + if(ReportedStatement != NULL){ + LOG_WARN("Reported statement (%d, %d, %d) appears multiple times", + Connection->WorldID, Statements[i].Timestamp, + Statements[i].StatementID); + } + ReportedStatement = &Statements[i]; + } + } + + if(ReportedStatement == NULL){ + LOG_ERR("Missing reported statement"); + SendQueryStatusFailed(Connection); + return; + } + + TransactionScope Tx("ReportStatement"); + if(!Tx.Begin()){ + SendQueryStatusFailed(Connection); + return; + } + + int CharacterID = GetCharacterID(Connection->WorldID, CharacterName); + if(CharacterID == 0){ + SendQueryStatusError(Connection, 1); + return; + }else if(ReportedStatement->CharacterID != CharacterID){ + LOG_ERR("Reported statement character mismatch"); + SendQueryStatusFailed(Connection); + return; + } + + if(IsStatementReported(Connection->WorldID, ReportedStatement)){ + SendQueryStatusError(Connection, 2); + return; + } + + if(!InsertStatements(Connection->WorldID, NumStatements, Statements)){ + SendQueryStatusFailed(Connection); + return; + } + + if(!InsertReportedStatement(Connection->WorldID, ReportedStatement, + BanishmentID, ReporterID, Reason, Comment)){ + SendQueryStatusFailed(Connection); + return; + } + + if(!Tx.Commit()){ + SendQueryStatusFailed(Connection); + return; + } + + SendQueryStatusOk(Connection); } -void ProcessBanishIpAddressQuery(TConnection *Connection, TReadBuffer *Buffer){ - SendQueryStatusFailed(Connection); +void ProcessBanishIPAddressQuery(TConnection *Connection, TReadBuffer *Buffer){ + if(Connection->ApplicationType != APPLICATION_TYPE_GAME){ + SendQueryStatusFailed(Connection); + return; + } + + char CharacterName[30]; + char IPString[16]; + char Reason[200]; + char Comment[200]; + int GamemasterID = Buffer->Read16(); + Buffer->ReadString(CharacterName, sizeof(CharacterName)); + Buffer->ReadString(IPString, sizeof(IPString)); + Buffer->ReadString(Reason, sizeof(Reason)); + Buffer->ReadString(Comment, sizeof(Comment)); + + int IPAddress = 0; + if(IPString[0] != 0 && !ParseIPAddress(IPString, &IPAddress)){ + SendQueryStatusFailed(Connection); + return; + } + + TransactionScope Tx("BanishIP"); + if(!Tx.Begin()){ + SendQueryStatusFailed(Connection); + return; + } + + int CharacterID = GetCharacterID(Connection->WorldID, CharacterName); + if(CharacterID == 0){ + SendQueryStatusError(Connection, 1); + return; + } + + // TODO(fusion): Might be `NO_BANISHMENT`. + if(!GetCharacterRight(CharacterID, "IP_BANISHMENT")){ + SendQueryStatusError(Connection, 2); + return; + } + + // IMPORTANT(fusion): It is not a good idea to ban an IP address, specially + // V4 addresses, as they may be dynamically assigned or represent the address + // of a public ISP router that manages multiple clients. + int BanishmentDays = 3; + if(!InsertIPBanishment(CharacterID, IPAddress, GamemasterID, + Reason, Comment, BanishmentDays * 86400)){ + SendQueryStatusFailed(Connection); + return; + } + + if(!Tx.Commit()){ + SendQueryStatusFailed(Connection); + return; + } + + SendQueryStatusOk(Connection); } void ProcessLogCharacterDeathQuery(TConnection *Connection, TReadBuffer *Buffer){ - SendQueryStatusFailed(Connection); + if(Connection->ApplicationType != APPLICATION_TYPE_GAME){ + SendQueryStatusFailed(Connection); + return; + } + + char Remark[30]; + int CharacterID = (int)Buffer->Read32(); + int Level = Buffer->Read16(); + int OffenderID = (int)Buffer->Read32(); + Buffer->ReadString(Remark, sizeof(Remark)); + bool Unjustified = Buffer->ReadFlag(); + int Timestamp = (int)Buffer->Read32(); + if(!InsertCharacterDeath(Connection->WorldID, CharacterID, Level, + OffenderID, Remark, Unjustified, Timestamp)){ + SendQueryStatusFailed(Connection); + return; + } + + SendQueryStatusOk(Connection); } void ProcessAddBuddyQuery(TConnection *Connection, TReadBuffer *Buffer){ - SendQueryStatusFailed(Connection); + if(Connection->ApplicationType != APPLICATION_TYPE_GAME){ + SendQueryStatusFailed(Connection); + return; + } + + int AccountID = (int)Buffer->Read32(); + int BuddyID = (int)Buffer->Read32(); + if(!InsertBuddy(Connection->WorldID, AccountID, BuddyID)){ + SendQueryStatusFailed(Connection); + return; + } + + SendQueryStatusOk(Connection); } void ProcessRemoveBuddyQuery(TConnection *Connection, TReadBuffer *Buffer){ - SendQueryStatusFailed(Connection); + if(Connection->ApplicationType != APPLICATION_TYPE_GAME){ + SendQueryStatusFailed(Connection); + return; + } + + int AccountID = (int)Buffer->Read32(); + int BuddyID = (int)Buffer->Read32(); + if(!DeleteBuddy(Connection->WorldID, AccountID, BuddyID)){ + SendQueryStatusFailed(Connection); + return; + } + + SendQueryStatusOk(Connection); } void ProcessDecrementIsOnlineQuery(TConnection *Connection, TReadBuffer *Buffer){ @@ -930,7 +1186,7 @@ void ProcessInsertHousesQuery(TConnection *Connection, TReadBuffer *Buffer){ return; } - TransactionScope Tx("UpdateHouses"); + TransactionScope Tx("InsertHouses"); if(!Tx.Begin()){ SendQueryStatusFailed(Connection); return; @@ -1083,16 +1339,10 @@ void ProcessExcludeFromAuctionsQuery(TConnection *Connection, TReadBuffer *Buffe int ExclusionDays = 7; int BanishmentID = 0; if(Banish){ - bool FinalWarning = false; int BanishmentDays = 7; + bool FinalWarning = false; TBanishmentStatus Status = GetBanishmentStatus(CharacterID); - if(Status.FinalWarning){ - BanishmentDays = 0; // permanent - }else if(Status.TimesBanished >= 5){ - BanishmentDays = 30; - FinalWarning = true; - } - + CompoundBanishment(Status, &BanishmentDays, &FinalWarning); if(!InsertBanishment(CharacterID, 0, 0, "Spoiling Auction", "", FinalWarning, BanishmentDays * 86400, &BanishmentID)){ SendQueryStatusFailed(Connection); @@ -1243,7 +1493,7 @@ void ProcessConnectionQuery(TConnection *Connection){ case QUERY_BANISH_ACCOUNT: ProcessBanishAccountQuery(Connection, &Buffer); break; case QUERY_SET_NOTATION: ProcessSetNotationQuery(Connection, &Buffer); break; case QUERY_REPORT_STATEMENT: ProcessReportStatementQuery(Connection, &Buffer); break; - case QUERY_BANISH_IP_ADDRESS: ProcessBanishIpAddressQuery(Connection, &Buffer); break; + case QUERY_BANISH_IP_ADDRESS: ProcessBanishIPAddressQuery(Connection, &Buffer); break; case QUERY_LOG_CHARACTER_DEATH: ProcessLogCharacterDeathQuery(Connection, &Buffer); break; case QUERY_ADD_BUDDY: ProcessAddBuddyQuery(Connection, &Buffer); break; case QUERY_REMOVE_BUDDY: ProcessRemoveBuddyQuery(Connection, &Buffer); break; diff --git a/src/database.cc b/src/database.cc index 5d48bac..eb91dbc 100644 --- a/src/database.cc +++ b/src/database.cc @@ -308,6 +308,7 @@ bool DecrementIsOnline(int WorldID, int CharacterID){ } bool ClearIsOnline(int WorldID, int *NumAffectedCharacters){ + ASSERT(NumAffectedCharacters != NULL); sqlite3_stmt *Stmt = PrepareQuery( "UPDATE Characters SET IsOnline = 0" " WHERE WorldID = ?1 AND IsOnline != 0"); @@ -368,6 +369,92 @@ bool GetCharacterIndexEntries(int WorldID, int MinimumCharacterID, return true; } +bool InsertCharacterDeath(int WorldID, int CharacterID, int Level, + int OffenderID, const char *Remark, bool Unjustified, int Timestamp){ + // NOTE(fusion): Same as `DecrementIsOnline`. + sqlite3_stmt *Stmt = PrepareQuery( + "INSERT INTO CharacterDeaths (CharacterID, Level," + " OffenderID, Remark, Unjustified, Timestamp)" + " SELECT ?2, ?3, ?4, ?5, ?6, ?7 FROM Characters" + " WHERE WorldID = ?1 AND CharacterID = ?2"); + if(Stmt == NULL){ + LOG_ERR("Failed to prepare query"); + return false; + } + + 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 + || sqlite3_bind_int(Stmt, 4, OffenderID) != SQLITE_OK + || sqlite3_bind_text(Stmt, 5, Remark, -1, NULL) != SQLITE_OK + || sqlite3_bind_int(Stmt, 6, (Unjustified ? 1 : 0)) != SQLITE_OK + || sqlite3_bind_int(Stmt, 7, Timestamp) != SQLITE_OK){ + LOG_ERR("Failed to bind parameters: %s", sqlite3_errmsg(g_Database)); + return false; + } + + if(sqlite3_step(Stmt) != SQLITE_DONE){ + LOG_ERR("Failed to execute query: %s", sqlite3_errmsg(g_Database)); + return false; + } + + return sqlite3_changes(g_Database) > 0; +} + +bool InsertBuddy(int WorldID, int AccountID, int BuddyID){ + // NOTE(fusion): Same as `DecrementIsOnline`. + // NOTE(fusion): Use the `IGNORE` conflict resolution to make duplicate row + // errors appear as successful insertions. + sqlite3_stmt *Stmt = PrepareQuery( + "INSERT OR IGNORE INTO Buddies (WorldID, AccountID, BuddyID)" + " SELECT ?1, ?2, ?3 FROM Characters" + " WHERE WorldID = ?1 AND CharacterID = ?3"); + if(Stmt == NULL){ + LOG_ERR("Failed to prepare query"); + return false; + } + + 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){ + LOG_ERR("Failed to bind parameters: %s", sqlite3_errmsg(g_Database)); + return false; + } + + if(sqlite3_step(Stmt) != SQLITE_DONE){ + LOG_ERR("Failed to execute query: %s", sqlite3_errmsg(g_Database)); + return false; + } + + return true; +} + +bool DeleteBuddy(int WorldID, int AccountID, int BuddyID){ + sqlite3_stmt *Stmt = PrepareQuery( + "DELETE FROM Buddies" + " WHERE WorldID = ?1 AND AccountID = ?2 AND BuddyID = ?3"); + if(Stmt == NULL){ + LOG_ERR("Failed to prepare query"); + return false; + } + + 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){ + LOG_ERR("Failed to bind parameters: %s", sqlite3_errmsg(g_Database)); + return false; + } + + if(sqlite3_step(Stmt) != SQLITE_DONE){ + LOG_ERR("Failed to execute query: %s", sqlite3_errmsg(g_Database)); + return false; + } + + // NOTE(fusion): Always return true here even if there were no deleted rows + // to make them appear as successful deletions. + return true; +} + // House tables //============================================================================== bool FinishHouseAuctions(int WorldID, DynamicArray *Auctions){ @@ -689,6 +776,7 @@ bool DeleteHouses(int WorldID){ } bool InsertHouses(int WorldID, int NumHouses, THouse *Houses){ + ASSERT(NumHouses > 0 && Houses != NULL); sqlite3_stmt *Stmt = PrepareQuery( "INSERT INTO Houses (WorldID, HouseID, Name, Rent, Description," " Size, PositionX, PositionY, PositionZ, Town, GuildHouse)" @@ -734,8 +822,8 @@ bool InsertHouses(int WorldID, int NumHouses, THouse *Houses){ bool ExcludeFromAuctions(int WorldID, int CharacterID, int Duration, int BanishmentID){ // NOTE(fusion): Same as `DecrementIsOnline`. sqlite3_stmt *Stmt = PrepareQuery( - "INSERT INTO HouseAuctionExclusions (CharacterID, Until, BanishmentID)" - " SELECT CharacterID, (UNIXEPOCH() + ?3), ?4 FROM Characters" + "INSERT INTO HouseAuctionExclusions (CharacterID, Issued, Until, BanishmentID)" + " SELECT ?2, UNIXEPOCH(), (UNIXEPOCH() + ?3), ?4 FROM Characters" " WHERE WorldID = ?1 AND CharacterID = ?2"); if(Stmt == NULL){ LOG_ERR("Failed to prepare query"); @@ -787,8 +875,8 @@ TNamelockStatus GetNamelockStatus(int CharacterID){ return Status; } -bool InsertNamelock(int CharacterID, int IPAddress, - int GamemasterID, const char *Reason, const char *Comment){ +bool InsertNamelock(int CharacterID, int IPAddress, int GamemasterID, + const char *Reason, const char *Comment){ ASSERT(Reason != NULL && Comment != NULL); sqlite3_stmt *Stmt = PrepareQuery( "INSERT INTO Namelocks (CharacterID, IPAddress, GamemasterID, Reason, Comment)" @@ -852,17 +940,15 @@ TBanishmentStatus GetBanishmentStatus(int CharacterID){ return Status; } -bool InsertBanishment(int CharacterID, int IPAddress, - int GamemasterID, const char *Reason, const char *Comment, - bool FinalWarning, int Duration, int *BanishmentID){ - // TODO(fusion): Not sure if I like these insertions with subqueries. We might - // as well just get the account id before hand. - ASSERT(BanishmentID); +bool InsertBanishment(int CharacterID, int IPAddress, int GamemasterID, + const char *Reason, const char *Comment, bool FinalWarning, + int Duration, int *BanishmentID){ + ASSERT(Reason != NULL && Comment != NULL && BanishmentID != NULL); sqlite3_stmt *Stmt = PrepareQuery( "INSERT INTO Banishments (AccountID, IPAddress, GamemasterID," " Reason, Comment, FinalWarning, Issued, Until)" - " VALUES ((SELECT AccountID FROM Characters WHERE CharacterID = ?1)," - " ?2, ?3, ?4, ?5, ?6, UNIXEPOCH(), UNIXEPOCH() + ?7)" + " SELECT AccountID, ?2, ?3, ?4, ?5, ?6, UNIXEPOCH(), UNIXEPOCH() + ?7" + " FROM Characters WHERE CharacterID = ?1" " RETURNING BanishmentID"); if(Stmt == NULL){ LOG_ERR("Failed to prepare query"); @@ -889,6 +975,192 @@ bool InsertBanishment(int CharacterID, int IPAddress, return true; } +int GetNotationCount(int CharacterID){ + sqlite3_stmt *Stmt = PrepareQuery( + "SELECT COUNT(*) FROM Notations WHERE CharacterID = ?1"); + if(Stmt == NULL){ + LOG_ERR("Failed to prepare query"); + return 0; + } + + if(sqlite3_bind_int(Stmt, 1, CharacterID) != SQLITE_OK){ + LOG_ERR("Failed to bind parameters: %s", sqlite3_errmsg(g_Database)); + return 0; + } + + if(sqlite3_step(Stmt) != SQLITE_ROW){ + LOG_ERR("Failed to execute query: %s", sqlite3_errmsg(g_Database)); + return 0; + } + + return sqlite3_column_int(Stmt, 0); +} + +bool InsertNotation(int CharacterID, int IPAddress, int GamemasterID, + const char *Reason, const char *Comment){ + ASSERT(Reason != NULL && Comment != NULL); + sqlite3_stmt *Stmt = PrepareQuery( + "INSERT INTO Notations (CharacterID, IPAddress," + " GamemasterID, Reason, Comment)" + " VALUES (?1, ?2, ?3, ?4, ?5)"); + if(Stmt == NULL){ + LOG_ERR("Failed to prepare query"); + return false; + } + + 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 + || sqlite3_bind_text(Stmt, 4, Reason, -1, NULL) != SQLITE_OK + || sqlite3_bind_text(Stmt, 5, Comment, -1, NULL) != SQLITE_OK){ + LOG_ERR("Failed to bind parameters: %s", sqlite3_errmsg(g_Database)); + return false; + } + + if(sqlite3_step(Stmt) != SQLITE_DONE){ + LOG_ERR("Failed to execute query: %s", sqlite3_errmsg(g_Database)); + return false; + } + + return true; +} + +bool InsertIPBanishment(int CharacterID, int IPAddress, int GamemasterID, + const char *Reason, const char *Comment, int Duration){ + ASSERT(Reason != NULL && Comment != NULL); + sqlite3_stmt *Stmt = PrepareQuery( + "INSERT INTO IPBanishments (CharacterID, IPAddress," + " GamemasterID, Reason, Comment, Issued, Until)" + " VALUES (?1, ?2, ?3, ?4, ?5, UNIXEPOCH(), UNIXEPOCH() + ?6)"); + if(Stmt == NULL){ + LOG_ERR("Failed to prepare query"); + return false; + } + + 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 + || sqlite3_bind_text(Stmt, 4, Reason, -1, NULL) != SQLITE_OK + || sqlite3_bind_text(Stmt, 5, Comment, -1, NULL) != SQLITE_OK + || sqlite3_bind_int(Stmt, 6, Duration) != SQLITE_OK){ + LOG_ERR("Failed to bind parameters: %s", sqlite3_errmsg(g_Database)); + return false; + } + + if(sqlite3_step(Stmt) != SQLITE_DONE){ + LOG_ERR("Failed to execute query: %s", sqlite3_errmsg(g_Database)); + return false; + } + + return true; +} + +bool IsStatementReported(int WorldID, TStatement *Statement){ + ASSERT(Statement != NULL); + sqlite3_stmt *Stmt = PrepareQuery( + "SELECT 1 FROM Statements" + " WHERE WorldID = ?1 AND Timestamp = ?2 AND StatementID = ?3"); + if(Stmt == NULL){ + LOG_ERR("Failed to prepare query"); + return 0; + } + + 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){ + LOG_ERR("Failed to bind parameters: %s", sqlite3_errmsg(g_Database)); + return 0; + } + + 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; + } + + return (ErrorCode == SQLITE_ROW); +} + +bool InsertStatements(int WorldID, int NumStatements, TStatement *Statements){ + // NOTE(fusion): Use the `IGNORE` conflict resolution because different + // reports may include the same statements for context and I assume it's + // not uncommon to see overlaps. + ASSERT(NumStatements > 0 && Statements != NULL); + sqlite3_stmt *Stmt = PrepareQuery( + "INSERT OR IGNORE INTO Statements (WorldID, Timestamp," + " StatementID, CharacterID, Channel, Text)" + " VALUES (?1, ?2, ?3, ?4, ?5, ?6)"); + if(Stmt == NULL){ + LOG_ERR("Failed to prepare query"); + return false; + } + + if(sqlite3_bind_int(Stmt, 1, WorldID) != SQLITE_OK){ + LOG_ERR("Failed to bind WorldID: %s", sqlite3_errmsg(g_Database)); + return false; + } + + for(int i = 0; i < NumStatements; i += 1){ + if(Statements[i].StatementID == 0){ + LOG_WARN("Skipping statement without id"); + continue; + } + + if(sqlite3_bind_int(Stmt, 2, Statements[i].Timestamp) != SQLITE_OK + || sqlite3_bind_int(Stmt, 3, Statements[i].StatementID) != SQLITE_OK + || sqlite3_bind_int(Stmt, 4, Statements[i].CharacterID) != SQLITE_OK + || sqlite3_bind_text(Stmt, 5, Statements[i].Channel, -1, NULL) != SQLITE_OK + || sqlite3_bind_text(Stmt, 6, Statements[i].Text, -1, NULL) != SQLITE_OK){ + LOG_ERR("Failed to bind parameters for statement %d: %s", + Statements[i].StatementID, sqlite3_errmsg(g_Database)); + return false; + } + + if(sqlite3_step(Stmt) != SQLITE_DONE){ + LOG_ERR("Failed to insert statement %d: %s", + Statements[i].StatementID, sqlite3_errmsg(g_Database)); + return false; + } + + sqlite3_reset(Stmt); + } + + return true; +} + +bool InsertReportedStatement(int WorldID, TStatement *Statement, int BanishmentID, + int ReporterID, const char *Reason, const char *Comment){ + ASSERT(Statement != NULL && Reason != NULL && Comment != NULL); + sqlite3_stmt *Stmt = PrepareQuery( + "INSERT INTO ReportedStatements (WorldID, Timestamp," + " StatementID, CharacterID, BanishmentID, ReporterID," + " Reason, Comment)" + " VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8)"); + if(Stmt == NULL){ + LOG_ERR("Failed to prepare query"); + return false; + } + + 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 + || sqlite3_bind_int(Stmt, 4, Statement->CharacterID) != SQLITE_OK + || sqlite3_bind_int(Stmt, 5, BanishmentID) != SQLITE_OK + || sqlite3_bind_int(Stmt, 6, ReporterID) != SQLITE_OK + || sqlite3_bind_text(Stmt, 7, Reason, -1, NULL) != SQLITE_OK + || sqlite3_bind_text(Stmt, 8, Comment, -1, NULL) != SQLITE_OK){ + LOG_ERR("Failed to bind parameters: %s", sqlite3_errmsg(g_Database)); + return false; + } + + if(sqlite3_step(Stmt) != SQLITE_DONE){ + LOG_ERR("Failed to execute query: %s", sqlite3_errmsg(g_Database)); + return false; + } + + return true; +} + // Info tables //============================================================================== bool DeleteOnlineCharacters(int WorldID){ diff --git a/src/querymanager.hh b/src/querymanager.hh index 9e54ea8..5276d2c 100644 --- a/src/querymanager.hh +++ b/src/querymanager.hh @@ -602,7 +602,7 @@ void ProcessSetNamelockQuery(TConnection *Connection, TReadBuffer *Buffer); void ProcessBanishAccountQuery(TConnection *Connection, TReadBuffer *Buffer); void ProcessSetNotationQuery(TConnection *Connection, TReadBuffer *Buffer); void ProcessReportStatementQuery(TConnection *Connection, TReadBuffer *Buffer); -void ProcessBanishIpAddressQuery(TConnection *Connection, TReadBuffer *Buffer); +void ProcessBanishIPAddressQuery(TConnection *Connection, TReadBuffer *Buffer); void ProcessLogCharacterDeathQuery(TConnection *Connection, TReadBuffer *Buffer); void ProcessAddBuddyQuery(TConnection *Connection, TReadBuffer *Buffer); void ProcessRemoveBuddyQuery(TConnection *Connection, TReadBuffer *Buffer); @@ -714,6 +714,14 @@ struct TBanishmentStatus{ int TimesBanished; }; +struct TStatement{ + int Timestamp; + int StatementID; + int CharacterID; + char Channel[30]; + char Text[256]; +}; + struct TOnlineCharacter{ char Name[30]; int Level; @@ -743,6 +751,10 @@ bool DecrementIsOnline(int WorldID, int CharacterID); bool ClearIsOnline(int WorldID, int *NumAffectedCharacters); bool GetCharacterIndexEntries(int WorldID, int MinimumCharacterID, int MaxEntries, int *NumEntries, TCharacterIndexEntry *Entries); +bool InsertCharacterDeath(int WorldID, int CharacterID, int Level, + int OffenderID, const char *Remark, bool Unjustified, int Timestamp); +bool InsertBuddy(int WorldID, int AccountID, int BuddyID); +bool DeleteBuddy(int WorldID, int AccountID, int BuddyID); // NOTE(fusion): House tables. bool FinishHouseAuctions(int WorldID, DynamicArray *Auctions); @@ -761,12 +773,22 @@ bool ExcludeFromAuctions(int WorldID, int CharacterID, int Duration, int Banishm // NOTE(fusion): Banishment tables. TNamelockStatus GetNamelockStatus(int CharacterID); -bool InsertNamelock(int CharacterID, int IPAddress, - int GamemasterID, const char *Reason, const char *Comment); +bool InsertNamelock(int CharacterID, int IPAddress, int GamemasterID, + const char *Reason, const char *Comment); TBanishmentStatus GetBanishmentStatus(int CharacterID); -bool InsertBanishment(int CharacterID, int IPAddress, - int GamemasterID, const char *Reason, const char *Comment, - bool FinalWarning, int Duration, int *BanishmentID); +bool InsertBanishment(int CharacterID, int IPAddress, int GamemasterID, + const char *Reason, const char *Comment, bool FinalWarning, + int Duration, int *BanishmentID); +int GetNotationCount(int CharacterID); +bool InsertNotation(int CharacterID, int IPAddress, int GamemasterID, + const char *Reason, const char *Comment); +bool InsertIPBanishment(int CharacterID, int IPAddress, int GamemasterID, + const char *Reason, const char *Comment, int Duration); + +bool IsStatementReported(int WorldID, TStatement *Statement); +bool InsertStatements(int WorldID, int NumStatements, TStatement *Statements); +bool InsertReportedStatement(int WorldID, TStatement *Statement, int BanishmentID, + int ReporterID, const char *Reason, const char *Comment); // NOTE(fusion): Info tables. bool DeleteOnlineCharacters(int WorldID); -- cgit v1.2.3