diff options
| -rw-r--r-- | Makefile | 2 | ||||
| -rw-r--r-- | sql/schema.sql | 41 | ||||
| -rw-r--r-- | src/connections.cc | 273 | ||||
| -rw-r--r-- | src/database.cc | 390 | ||||
| -rw-r--r-- | src/querymanager.cc | 10 | ||||
| -rw-r--r-- | src/querymanager.hh | 35 |
6 files changed, 685 insertions, 66 deletions
@@ -4,7 +4,7 @@ OUTPUTEXE = querymanager CC = gcc CXX = g++ -CFLAGS = -m64 -fno-strict-aliasing -pedantic -Wall -Wextra -pthread +CFLAGS = -m64 -fno-strict-aliasing -pedantic -Wno-unused-parameter -Wall -Wextra -pthread CXXFLAGS = $(CFLAGS) --std=c++11 LFLAGS = -Wl,-t diff --git a/sql/schema.sql b/sql/schema.sql index 6ec136b..61430ed 100644 --- a/sql/schema.sql +++ b/sql/schema.sql @@ -36,12 +36,16 @@ CREATE TABLE IF NOT EXISTS Characters ( Rank TEXT NOT NULL COLLATE NOCASE DEFAULT '', Title TEXT NOT NULL COLLATE NOCASE DEFAULT '', IsOnline INTEGER NOT NULL DEFAULT 0, + Deleted INTEGER NOT NULL DEFAULT 0, PRIMARY KEY (CharacterID), UNIQUE (Name) ); -CREATE INDEX IF NOT EXISTS CharactersWorldIndex ON Characters(WorldID, IsOnline); -CREATE INDEX IF NOT EXISTS CharactersAccountIndex ON Characters(AccountID, IsOnline); -CREATE INDEX IF NOT EXISTS CharactersGuildIndex ON Characters(Guild); +CREATE INDEX IF NOT EXISTS CharactersWorldIndex + ON Characters(WorldID, IsOnline); +CREATE INDEX IF NOT EXISTS CharactersAccountIndex + ON Characters(AccountID, IsOnline); +CREATE INDEX IF NOT EXISTS CharactersGuildIndex + ON Characters(Guild, Rank); CREATE TABLE IF NOT EXISTS CharacterBuddies ( CharacterID INTEGER NOT NULL, @@ -92,9 +96,9 @@ CREATE TABLE IF NOT EXISTS HouseOwners ( CREATE TABLE IF NOT EXISTS HouseAuctions ( WorldID INTEGER NOT NULL, HouseID INTEGER NOT NULL, - BidderID INTEGER, - BidAmount INTEGER, - FinishTime INTEGER, + BidderID INTEGER DEFAULT NULL, + BidAmount INTEGER DEFAULT NULL, + FinishTime INTEGER DEFAULT NULL, PRIMARY KEY (WorldID, HouseID) ); @@ -106,6 +110,14 @@ CREATE TABLE IF NOT EXISTS HouseTransfers ( PRIMARY KEY (WorldID, HouseID) ); +CREATE TABLE IF NOT EXISTS HouseAuctionExclusions ( + CharacterID INTEGER NOT NULL, + Until INTEGER NOT NULL, + BanishmentID INTEGER NOT NULL +); +CREATE INDEX IF NOT EXISTS HouseAuctionExclusionsIndex + ON HouseAuctionExclusions(CharacterID, Until); + CREATE TABLE IF NOT EXISTS HouseAssignments ( WorldID INTEGER NOT NULL, HouseID INTEGER NOT NULL, @@ -113,8 +125,12 @@ CREATE TABLE IF NOT EXISTS HouseAssignments ( Price INTEGER NOT NULL, Timestamp INTEGER NOT NULL ); -CREATE INDEX IF NOT EXISTS HouseAssignmentsHouseIndex ON HouseAssignments(WorldID, HouseID); -CREATE INDEX IF NOT EXISTS HouseAssignmentsOwnerIndex ON HouseAssignments(OwnerID); +CREATE INDEX IF NOT EXISTS HouseAssignmentsHouseIndex + ON HouseAssignments(WorldID, HouseID); +CREATE INDEX IF NOT EXISTS HouseAssignmentsTimeIndex + ON HouseAssignments(WorldID, Timestamp); +CREATE INDEX IF NOT EXISTS HouseAssignmentsOwnerIndex + ON HouseAssignments(OwnerID); -- Banishment Tables --============================================================================== @@ -155,9 +171,10 @@ CREATE TABLE IF NOT EXISTS Notations ( Reason TEXT NOT NULL, Comment TEXT NOT NULL ); -CREATE INDEX IF NOT EXISTS NotationsCharacterIndex ON Notations(CharacterID); +CREATE INDEX IF NOT EXISTS NotationsCharacterIndex + ON Notations(CharacterID); -CREATE TABLE IF NOT EXISTS Statements ( +CREATE TABLE IF NOT EXISTS ReportedStatements ( Timestamp INTEGER NOT NULL, StatementID INTEGER NOT NULL, CharacterID INTEGER NOT NULL, @@ -169,6 +186,8 @@ CREATE TABLE IF NOT EXISTS Statements ( Comment TEXT NOT NULL, PRIMARY KEY (Timestamp, StatementID) ); +CREATE INDEX IF NOT EXISTS ReportedStatementsCharacterIndex + ON ReportedStatements(CharacterID, Timestamp); -- Info Tables --============================================================================== @@ -192,4 +211,4 @@ CREATE TABLE IF NOT EXISTS OnlineCharacters ( --============================================================================== INSERT INTO Worlds (Name, Type, RebootTime, Address, Port, MaxPlayers, PremiumPlayerBuffer, MaxNewbies, PremiumNewbieBuffer) - VALUES ("Zanera", 0, 5, 0x7F000001, 7172, 1000, 100, 300, 100); + VALUES ('Zanera', 0, 5, 0x7F000001, 7172, 1000, 100, 300, 100); diff --git a/src/connections.cc b/src/connections.cc index cfb0a01..d775b0b 100644 --- a/src/connections.cc +++ b/src/connections.cc @@ -550,39 +550,194 @@ void ProcessRemoveBuddyQuery(TConnection *Connection, TReadBuffer *Buffer){ } void ProcessDecrementIsOnlineQuery(TConnection *Connection, TReadBuffer *Buffer){ - SendQueryStatusFailed(Connection); + if(Connection->ApplicationType != APPLICATION_TYPE_GAME){ + SendQueryStatusFailed(Connection); + return; + } + + int CharacterID = (int)Buffer->Read32(); + if(!DecrementIsOnline(Connection->WorldID, CharacterID)){ + SendQueryStatusFailed(Connection); + return; + } + + SendQueryStatusOk(Connection); } void ProcessFinishAuctionsQuery(TConnection *Connection, TReadBuffer *Buffer){ - SendQueryStatusFailed(Connection); + if(Connection->ApplicationType != APPLICATION_TYPE_GAME){ + SendQueryStatusFailed(Connection); + return; + } + + DynamicArray<THouseAuction> Auctions; + if(!FinishHouseAuctions(Connection->WorldID, &Auctions)){ + SendQueryStatusFailed(Connection); + return; + } + + TWriteBuffer WriteBuffer = PrepareResponse(Connection, QUERY_STATUS_OK); + int NumAuctions = std::min<int>(Auctions.Length(), UINT16_MAX); + WriteBuffer.Write16((uint16)NumAuctions); + for(int i = 0; i < NumAuctions; i += 1){ + WriteBuffer.Write16((uint16)Auctions[i].HouseID); + WriteBuffer.Write32((uint32)Auctions[i].BidderID); + WriteBuffer.WriteString(Auctions[i].BidderName); + WriteBuffer.Write32((uint32)Auctions[i].BidAmount); + } + SendResponse(Connection, &WriteBuffer); } void ProcessTransferHousesQuery(TConnection *Connection, TReadBuffer *Buffer){ - SendQueryStatusFailed(Connection); + if(Connection->ApplicationType != APPLICATION_TYPE_GAME){ + SendQueryStatusFailed(Connection); + return; + } + + DynamicArray<THouseTransfer> Transfers; + if(!FinishHouseTransfers(Connection->WorldID, &Transfers)){ + SendQueryStatusFailed(Connection); + return; + } + + TWriteBuffer WriteBuffer = PrepareResponse(Connection, QUERY_STATUS_OK); + int NumTransfers = std::min<int>(Transfers.Length(), UINT16_MAX); + WriteBuffer.Write16((uint16)NumTransfers); + for(int i = 0; i < NumTransfers; i += 1){ + WriteBuffer.Write16((uint16)Transfers[i].HouseID); + WriteBuffer.Write32((uint32)Transfers[i].NewOwnerID); + WriteBuffer.WriteString(Transfers[i].NewOwnerName); + WriteBuffer.Write32((uint32)Transfers[i].Price); + } + SendResponse(Connection, &WriteBuffer); } void ProcessEvictFreeAccountsQuery(TConnection *Connection, TReadBuffer *Buffer){ - SendQueryStatusFailed(Connection); + if(Connection->ApplicationType != APPLICATION_TYPE_GAME){ + SendQueryStatusFailed(Connection); + return; + } + + DynamicArray<THouseEviction> Evictions; + if(!LoadFreeAccountEvictions(Connection->WorldID, &Evictions)){ + SendQueryStatusFailed(Connection); + return; + } + + TWriteBuffer WriteBuffer = PrepareResponse(Connection, QUERY_STATUS_OK); + int NumEvictions = std::min<int>(Evictions.Length(), UINT16_MAX); + WriteBuffer.Write16((uint16)NumEvictions); + for(int i = 0; i < NumEvictions; i += 1){ + WriteBuffer.Write16((uint16)Evictions[i].HouseID); + WriteBuffer.Write32((uint32)Evictions[i].OwnerID); + } + SendResponse(Connection, &WriteBuffer); } void ProcessEvictDeletedCharactersQuery(TConnection *Connection, TReadBuffer *Buffer){ - SendQueryStatusFailed(Connection); + if(Connection->ApplicationType != APPLICATION_TYPE_GAME){ + SendQueryStatusFailed(Connection); + return; + } + + DynamicArray<THouseEviction> Evictions; + if(!LoadDeletedCharacterEvictions(Connection->WorldID, &Evictions)){ + SendQueryStatusFailed(Connection); + return; + } + + TWriteBuffer WriteBuffer = PrepareResponse(Connection, QUERY_STATUS_OK); + int NumEvictions = std::min<int>(Evictions.Length(), UINT16_MAX); + WriteBuffer.Write16((uint16)NumEvictions); + for(int i = 0; i < NumEvictions; i += 1){ + WriteBuffer.Write16((uint16)Evictions[i].HouseID); + } + SendResponse(Connection, &WriteBuffer); } void ProcessEvictExGuildleadersQuery(TConnection *Connection, TReadBuffer *Buffer){ - SendQueryStatusFailed(Connection); + if(Connection->ApplicationType != APPLICATION_TYPE_GAME){ + SendQueryStatusFailed(Connection); + return; + } + + // NOTE(fusion): This is a bit different from the other eviction functions. + // The server doesn't maintain guild information for characters so it will + // send a list of guild houses with their owners and we're supposed to check + // whether the owner is still a guild leader. I don't think we should check + // any other information as the server is authoritative on house information. + DynamicArray<int> Evictions; + int NumGuildHouses = Buffer->Read16(); + for(int i = 0; i < NumGuildHouses; i += 1){ + bool IsGuildLeader = false; + int HouseID = Buffer->Read16(); + int OwnerID = (int)Buffer->Read32(); + if(!CheckGuildLeaderStatus(Connection->WorldID, OwnerID, &IsGuildLeader)){ + SendQueryStatusFailed(Connection); + return; + } + + if(!IsGuildLeader){ + Evictions.Push(HouseID); + } + } + + TWriteBuffer WriteBuffer = PrepareResponse(Connection, QUERY_STATUS_OK); + int NumEvictions = std::min<int>(Evictions.Length(), UINT16_MAX); + WriteBuffer.Write16((uint16)NumEvictions); + for(int i = 0; i < NumEvictions; i += 1){ + WriteBuffer.Write16((uint16)Evictions[i]); + } + SendResponse(Connection, &WriteBuffer); } void ProcessInsertHouseOwnerQuery(TConnection *Connection, TReadBuffer *Buffer){ - SendQueryStatusFailed(Connection); + if(Connection->ApplicationType != APPLICATION_TYPE_GAME){ + SendQueryStatusFailed(Connection); + return; + } + + int HouseID = Buffer->Read16(); + int OwnerID = (int)Buffer->Read32(); + int PaidUntil = (int)Buffer->Read32(); + if(!InsertHouseOwner(Connection->WorldID, HouseID, OwnerID, PaidUntil)){ + SendQueryStatusFailed(Connection); + return; + } + + SendQueryStatusOk(Connection); } void ProcessUpdateHouseOwnerQuery(TConnection *Connection, TReadBuffer *Buffer){ - SendQueryStatusFailed(Connection); + if(Connection->ApplicationType != APPLICATION_TYPE_GAME){ + SendQueryStatusFailed(Connection); + return; + } + + int HouseID = Buffer->Read16(); + int OwnerID = (int)Buffer->Read32(); + int PaidUntil = (int)Buffer->Read32(); + if(!UpdateHouseOwner(Connection->WorldID, HouseID, OwnerID, PaidUntil)){ + SendQueryStatusFailed(Connection); + return; + } + + SendQueryStatusOk(Connection); } void ProcessDeleteHouseOwnerQuery(TConnection *Connection, TReadBuffer *Buffer){ - SendQueryStatusFailed(Connection); + if(Connection->ApplicationType != APPLICATION_TYPE_GAME){ + SendQueryStatusFailed(Connection); + return; + } + + int HouseID = Buffer->Read16(); + if(!DeleteHouseOwner(Connection->WorldID, HouseID)){ + SendQueryStatusFailed(Connection); + return; + } + + SendQueryStatusOk(Connection); } void ProcessGetHouseOwnersQuery(TConnection *Connection, TReadBuffer *Buffer){ @@ -591,30 +746,58 @@ void ProcessGetHouseOwnersQuery(TConnection *Connection, TReadBuffer *Buffer){ return; } - DynamicArray<THouseOwner> HouseOwners; - if(!LoadHouseOwners(Connection->WorldID, &HouseOwners)){ + DynamicArray<THouseOwner> Owners; + if(!LoadHouseOwners(Connection->WorldID, &Owners)){ SendQueryStatusFailed(Connection); return; } TWriteBuffer WriteBuffer = PrepareResponse(Connection, QUERY_STATUS_OK); - int NumHouseOwners = std::min<int>(HouseOwners.Length(), UINT16_MAX); - WriteBuffer.Write16((uint16)NumHouseOwners); - for(int i = 0; i < NumHouseOwners; i += 1){ - WriteBuffer.Write16((uint16)HouseOwners[i].HouseID); - WriteBuffer.Write32((uint32)HouseOwners[i].OwnerID); - WriteBuffer.WriteString(HouseOwners[i].OwnerName); - WriteBuffer.Write32((uint32)HouseOwners[i].PaidUntil); + int NumOwners = std::min<int>(Owners.Length(), UINT16_MAX); + WriteBuffer.Write16((uint16)NumOwners); + for(int i = 0; i < NumOwners; i += 1){ + WriteBuffer.Write16((uint16)Owners[i].HouseID); + WriteBuffer.Write32((uint32)Owners[i].OwnerID); + WriteBuffer.WriteString(Owners[i].OwnerName); + WriteBuffer.Write32((uint32)Owners[i].PaidUntil); } SendResponse(Connection, &WriteBuffer); } void ProcessGetAuctionsQuery(TConnection *Connection, TReadBuffer *Buffer){ - SendQueryStatusFailed(Connection); + if(Connection->ApplicationType != APPLICATION_TYPE_GAME){ + SendQueryStatusFailed(Connection); + return; + } + + DynamicArray<int> Auctions; + if(!LoadHouseAuctions(Connection->WorldID, &Auctions)){ + SendQueryStatusFailed(Connection); + return; + } + + TWriteBuffer WriteBuffer = PrepareResponse(Connection, QUERY_STATUS_OK); + int NumAuctions = std::min<int>(Auctions.Length(), UINT16_MAX); + WriteBuffer.Write16((uint16)NumAuctions); + for(int i = 0; i < NumAuctions; i += 1){ + WriteBuffer.Write16((uint16)Auctions[i]); + } + SendResponse(Connection, &WriteBuffer); } void ProcessStartAuctionQuery(TConnection *Connection, TReadBuffer *Buffer){ - SendQueryStatusFailed(Connection); + if(Connection->ApplicationType != APPLICATION_TYPE_GAME){ + SendQueryStatusFailed(Connection); + return; + } + + int HouseID = Buffer->Read16(); + if(!StartHouseAuction(Connection->WorldID, HouseID)){ + SendQueryStatusFailed(Connection); + return; + } + + SendQueryStatusOk(Connection); } void ProcessInsertHousesQuery(TConnection *Connection, TReadBuffer *Buffer){ @@ -687,7 +870,6 @@ void ProcessCreatePlayerlistQuery(TConnection *Connection, TReadBuffer *Buffer){ return; } - TransactionScope Tx("OnlineList"); if(!Tx.Begin()){ SendQueryStatusFailed(Connection); @@ -761,11 +943,56 @@ void ProcessLoadPlayersQuery(TConnection *Connection, TReadBuffer *Buffer){ } void ProcessExcludeFromAuctionsQuery(TConnection *Connection, TReadBuffer *Buffer){ - SendQueryStatusFailed(Connection); + if(Connection->ApplicationType != APPLICATION_TYPE_GAME){ + SendQueryStatusFailed(Connection); + return; + } + + TransactionScope Tx("ExcludeFromAuctions"); + if(!Tx.Begin()){ + SendQueryStatusFailed(Connection); + return; + } + + int CharacterID = (int)Buffer->Read32(); + bool Banish = Buffer->ReadFlag(); + int Duration = 7 * 86400; // one week + int BanishmentID = 0; + if(Banish){ + Duration = 30 * 86400; // one month +#if 0 + // TODO(fusion): When banishment is implemented. + if(!BanishCharacterAccount(CharacterID, &BanishmentID, ..., "Spoiling Auction")){ + SendQueryStatusFailed(Connection); + return; + } +#endif + } + + if(!ExcludeFromAuctions(Connection->WorldID, CharacterID, Duration, BanishmentID)){ + SendQueryStatusFailed(Connection); + return; + } + + if(!Tx.Commit()){ + SendQueryStatusFailed(Connection); + return; + } + + SendQueryStatusOk(Connection); } void ProcessCancelHouseTransferQuery(TConnection *Connection, TReadBuffer *Buffer){ - SendQueryStatusFailed(Connection); + if(Connection->ApplicationType != APPLICATION_TYPE_GAME){ + SendQueryStatusFailed(Connection); + return; + } + + // TODO(fusion): Not sure what this is used for. Maybe house transfer rows + // are kept permanently and this query is used to delete/flag it, in case + // the it didn't complete. We might need to refine `FinishHouseTransfers`. + //int HouseID = Buffer->Read16(); + SendQueryStatusOk(Connection); } void ProcessLoadWorldConfigQuery(TConnection *Connection, TReadBuffer *Buffer){ diff --git a/src/database.cc b/src/database.cc index 1064547..4311fa2 100644 --- a/src/database.cc +++ b/src/database.cc @@ -144,7 +144,7 @@ bool TransactionScope::Commit(void){ // Queries //============================================================================== bool LoadWorldID(const char *WorldName, int *WorldID){ - ASSERT(WorldName && WorldID); + ASSERT(WorldName != NULL && WorldID != NULL); sqlite3_stmt *Stmt = PrepareQuery( "SELECT WorldID FROM Worlds WHERE Name = ?1"); if(Stmt == NULL){ @@ -166,8 +166,283 @@ bool LoadWorldID(const char *WorldName, int *WorldID){ return true; } -bool LoadHouseOwners(int WorldID, DynamicArray<THouseOwner> *HouseOwners){ - ASSERT(HouseOwners); +bool DecrementIsOnline(int WorldID, int CharacterID){ + // NOTE(fusion): A character is uniquely identified by its id. The world id + // check is purely to avoid a world from modifying a character from another + // world. + sqlite3_stmt *Stmt = PrepareQuery( + "UPDATE Characters SET IsOnline = IsOnline - 1" + " 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){ + 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 FinishHouseAuctions(int WorldID, DynamicArray<THouseAuction> *Auctions){ + ASSERT(Auctions != NULL); + // TODO(fusion): If the application crashes while processing finished auctions, + // non processed auctions will be lost but with no other side-effects. It could + // be an inconvenience but it's not a big problem. + sqlite3_stmt *Stmt = PrepareQuery( + "DELETE FROM HouseAuctions" + " WHERE WorldID = ?1 AND FinishTime != NULL AND FinishTime <= UNIXEPOCH()" + " RETURNING HouseID, BidderID, BidAmount, FinishTime," + " (SELECT Name FROM Characters WHERE CharacterID = BidderID)"); + 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; + } + + while(sqlite3_step(Stmt) == SQLITE_ROW){ + THouseAuction Auction = {}; + Auction.HouseID = sqlite3_column_int(Stmt, 0); + Auction.BidderID = sqlite3_column_int(Stmt, 1); + Auction.BidAmount = sqlite3_column_int(Stmt, 2); + Auction.FinishTime = sqlite3_column_int(Stmt, 3); + StringCopy(Auction.BidderName, sizeof(Auction.BidderName), + (const char*)sqlite3_column_text(Stmt, 4)); + Auctions->Push(Auction); + } + + if(sqlite3_errcode(g_Database) != SQLITE_DONE){ + LOG_ERR("Failed to execute query: %s", sqlite3_errmsg(g_Database)); + return false; + } + + return true; +} + +bool FinishHouseTransfers(int WorldID, DynamicArray<THouseTransfer> *Transfers){ + ASSERT(Transfers != NULL); + // TODO(fusion): Same as `FinishHouseAuctions` but with house transfers. + sqlite3_stmt *Stmt = PrepareQuery( + "DELETE FROM HouseTransfers" + " WHERE WorldID = ?1" + " RETURNING HouseID, NewOwnerID, Price," + " (SELECT Name FROM Characters WHERE CharacterID = NewOwnerID)"); + 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; + } + + while(sqlite3_step(Stmt) == SQLITE_ROW){ + THouseTransfer Transfer = {}; + Transfer.HouseID = sqlite3_column_int(Stmt, 0); + Transfer.NewOwnerID = sqlite3_column_int(Stmt, 1); + Transfer.Price = sqlite3_column_int(Stmt, 2); + StringCopy(Transfer.NewOwnerName, sizeof(Transfer.NewOwnerName), + (const char*)sqlite3_column_text(Stmt, 4)); + Transfers->Push(Transfer); + } + + if(sqlite3_errcode(g_Database) != SQLITE_DONE){ + LOG_ERR("Failed to execute query: %s", sqlite3_errmsg(g_Database)); + return false; + } + + return true; +} + +bool LoadFreeAccountEvictions(int WorldID, DynamicArray<THouseEviction> *Evictions){ + ASSERT(Evictions != NULL); + sqlite3_stmt *Stmt = PrepareQuery( + "SELECT O.HouseID, O.OwnerID" + " FROM HouseOwners AS O" + " LEFT JOIN CharacterRights AS R" + " ON R.CharacterID = O.OwnerID AND R.Right = 'PREMIUM_ACCOUNT'" + " WHERE O.WorldID = ?1 AND R.CharacterID IS NULL"); + 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; + } + + while(sqlite3_step(Stmt) == SQLITE_ROW){ + THouseEviction Eviction = {}; + Eviction.HouseID = sqlite3_column_int(Stmt, 0); + Eviction.OwnerID = sqlite3_column_int(Stmt, 1); + Evictions->Push(Eviction); + } + + if(sqlite3_errcode(g_Database) != SQLITE_DONE){ + LOG_ERR("Failed to execute query: %s", sqlite3_errmsg(g_Database)); + return false; + } + + return true; +} + +bool LoadDeletedCharacterEvictions(int WorldID, DynamicArray<THouseEviction> *Evictions){ + ASSERT(Evictions != NULL); + sqlite3_stmt *Stmt = PrepareQuery( + "SELECT O.HouseID, O.OwnerID" + " FROM HouseOwners AS O" + " LEFT JOIN Characters AS C ON C.CharacterID = O.OwnerID" + " WHERE O.WorldID = ?1" + " AND (C.CharacterID IS NULL OR C.Deleted != 0)"); + 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; + } + + while(sqlite3_step(Stmt) == SQLITE_ROW){ + THouseEviction Eviction = {}; + Eviction.HouseID = sqlite3_column_int(Stmt, 0); + Eviction.OwnerID = sqlite3_column_int(Stmt, 1); + Evictions->Push(Eviction); + } + + if(sqlite3_errcode(g_Database) != SQLITE_DONE){ + LOG_ERR("Failed to execute query: %s", sqlite3_errmsg(g_Database)); + return false; + } + + return true; +} + +bool CheckGuildLeaderStatus(int WorldID, int CharacterID, bool *IsGuildLeader){ + // NOTE(fusion): Same as `DecrementIsOnline`. + ASSERT(IsGuildLeader != NULL); + sqlite3_stmt *Stmt = PrepareQuery( + "SELECT Guild, Rank 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){ + LOG_ERR("Failed to bind parameters: %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; + } + + *IsGuildLeader = false; + if(ErrorCode == SQLITE_ROW){ + const char *Guild = (const char*)sqlite3_column_text(Stmt, 0); + const char *Rank = (const char*)sqlite3_column_text(Stmt, 1); + if(Guild != NULL && Rank != NULL && Guild[0] != 0 && StringEqCI(Rank, "Leader")){ + *IsGuildLeader = true; + } + } + + return true; +} + +bool InsertHouseOwner(int WorldID, int HouseID, int OwnerID, int PaidUntil){ + sqlite3_stmt *Stmt = PrepareQuery( + "INSERT INTO HouseOwners (WorldID, HouseID, OwnerID, PaidUntil)" + " VALUES (?1, ?2, ?3, ?4)"); + 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, HouseID) != SQLITE_OK + || sqlite3_bind_int(Stmt, 3, OwnerID) != SQLITE_OK + || sqlite3_bind_int(Stmt, 4, PaidUntil) != 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 UpdateHouseOwner(int WorldID, int HouseID, int OwnerID, int PaidUntil){ + sqlite3_stmt *Stmt = PrepareQuery( + "UPDATE HouseOwners SET OwnerID = ?3, PaidUntil = ?4" + " WHERE WorldID = ?1 AND HouseID = ?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, HouseID) != SQLITE_OK + || sqlite3_bind_int(Stmt, 3, OwnerID) != SQLITE_OK + || sqlite3_bind_int(Stmt, 4, PaidUntil) != 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 DeleteHouseOwner(int WorldID, int HouseID){ + sqlite3_stmt *Stmt = PrepareQuery( + "DELETE FROM HouseOwners" + " WHERE WorldID = ?1 AND HouseID = ?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, HouseID) != 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 LoadHouseOwners(int WorldID, DynamicArray<THouseOwner> *Owners){ + ASSERT(Owners != NULL); sqlite3_stmt *Stmt = PrepareQuery( "SELECT O.HouseID, O.OwnerID, C.Name, O.PaidUntil" " FROM HouseOwners AS O" @@ -190,7 +465,33 @@ bool LoadHouseOwners(int WorldID, DynamicArray<THouseOwner> *HouseOwners){ StringCopy(Owner.OwnerName, sizeof(Owner.OwnerName), (const char*)sqlite3_column_text(Stmt, 2)); Owner.PaidUntil = sqlite3_column_int(Stmt, 3); - HouseOwners->Push(Owner); + Owners->Push(Owner); + } + + if(sqlite3_errcode(g_Database) != SQLITE_DONE){ + LOG_ERR("Failed to execute query: %s", sqlite3_errmsg(g_Database)); + return false; + } + + return true; +} + +bool LoadHouseAuctions(int WorldID, DynamicArray<int> *Auctions){ + ASSERT(Auctions != NULL); + sqlite3_stmt *Stmt = PrepareQuery( + "SELECT HouseID FROM HouseAuctions WHERE WorldID = ?1"); + 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; + } + + while(sqlite3_step(Stmt) == SQLITE_ROW){ + Auctions->Push(sqlite3_column_int(Stmt, 0)); } if(sqlite3_errcode(g_Database) != SQLITE_DONE){ @@ -201,6 +502,28 @@ bool LoadHouseOwners(int WorldID, DynamicArray<THouseOwner> *HouseOwners){ return true; } +bool StartHouseAuction(int WorldID, int HouseID){ + sqlite3_stmt *Stmt = PrepareQuery( + "INSERT INTO HouseAuctions (WorldID, HouseID) VALUES (?1, ?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, HouseID) != 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 DeleteHouses(int WorldID){ sqlite3_stmt *Stmt = PrepareQuery( "DELETE FROM Houses WHERE WorldID = ?1"); @@ -345,7 +668,7 @@ bool InsertOnlineCharacters(int WorldID, int NumCharacters, TOnlineCharacter *Ch } bool CheckOnlineRecord(int WorldID, int NumCharacters, bool *NewRecord){ - ASSERT(NewRecord); + ASSERT(NewRecord != NULL); sqlite3_stmt *Stmt = PrepareQuery( "UPDATE Worlds SET OnlineRecord = ?2" " WHERE WorldID = ?1 AND OnlineRecord < ?2"); @@ -354,13 +677,9 @@ bool CheckOnlineRecord(int WorldID, int NumCharacters, bool *NewRecord){ return false; } - if(sqlite3_bind_int(Stmt, 1, WorldID) != SQLITE_OK){ - LOG_ERR("Failed to bind WorldID: %s", sqlite3_errmsg(g_Database)); - return false; - } - - if(sqlite3_bind_int(Stmt, 2, NumCharacters) != SQLITE_OK){ - LOG_ERR("Failed to bind NumCharacters: %s", sqlite3_errmsg(g_Database)); + 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)); return false; } @@ -375,7 +694,7 @@ bool CheckOnlineRecord(int WorldID, int NumCharacters, bool *NewRecord){ bool LoadCharacterIndex(int WorldID, int MinimumCharacterID, int MaxEntries, int *NumEntries, TCharacterIndexEntry *Entries){ - ASSERT(MaxEntries > 0 && NumEntries && Entries); + ASSERT(MaxEntries > 0 && NumEntries != NULL && Entries != NULL); sqlite3_stmt *Stmt = PrepareQuery( "SELECT CharacterID, Name FROM Characters" " WHERE WorldID = ?1 AND CharacterID >= ?2" @@ -385,18 +704,10 @@ bool LoadCharacterIndex(int WorldID, int MinimumCharacterID, return false; } - if(sqlite3_bind_int(Stmt, 1, WorldID) != SQLITE_OK){ - LOG_ERR("Failed to bind WorldID: %s", sqlite3_errmsg(g_Database)); - return false; - } - - if(sqlite3_bind_int(Stmt, 2, MinimumCharacterID) != SQLITE_OK){ - LOG_ERR("Failed to bind MinimumCharacterID: %s", sqlite3_errmsg(g_Database)); - return false; - } - - if(sqlite3_bind_int(Stmt, 3, MaxEntries) != SQLITE_OK){ - LOG_ERR("Failed to bind MaxEntries: %s", sqlite3_errmsg(g_Database)); + 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){ + LOG_ERR("Failed to bind parameters: %s", sqlite3_errmsg(g_Database)); return false; } @@ -419,8 +730,35 @@ bool LoadCharacterIndex(int WorldID, int MinimumCharacterID, return true; } +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" + " 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, Duration) != SQLITE_OK + || sqlite3_bind_int(Stmt, 3, BanishmentID) != 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 LoadWorldConfig(int WorldID, TWorldConfig *WorldConfig){ - ASSERT(WorldConfig); + ASSERT(WorldConfig != NULL); sqlite3_stmt *Stmt = PrepareQuery( "SELECT Type, RebootTime, Address, Port, MaxPlayers," " PremiumPlayerBuffer, MaxNewbies, PremiumNewbieBuffer" diff --git a/src/querymanager.cc b/src/querymanager.cc index 57b3ef9..7d6c830 100644 --- a/src/querymanager.cc +++ b/src/querymanager.cc @@ -112,17 +112,21 @@ bool StringEqCI(const char *A, const char *B){ bool StringCopyN(char *Dest, int DestCapacity, const char *Src, int SrcLength){ ASSERT(DestCapacity > 0); bool Result = (SrcLength < DestCapacity); - if(Result){ + if(Result && SrcLength > 0){ memcpy(Dest, Src, SrcLength); Dest[SrcLength] = 0; }else{ Dest[0] = 0; } - return true; + return Result; } bool StringCopy(char *Dest, int DestCapacity, const char *Src){ - return StringCopyN(Dest, DestCapacity, Src, (int)strlen(Src)); + // IMPORTANT(fusion): `sqlite3_column_text` may return NULL if the column is + // also NULL so we have an incentive to properly handle the case where `Src` + // is NULL. + int SrcLength = (Src != NULL ? (int)strlen(Src) : 0); + return StringCopyN(Dest, DestCapacity, Src, SrcLength); } bool ReadBooleanConfig(bool *Dest, const char *Val){ diff --git a/src/querymanager.hh b/src/querymanager.hh index ad8c78b..8fdd0a8 100644 --- a/src/querymanager.hh +++ b/src/querymanager.hh @@ -646,6 +646,26 @@ void ProcessConnectionQuery(TConnection *Connection); // database.cc //============================================================================== +struct THouseAuction{ + int HouseID; + int BidderID; + char BidderName[30]; + int BidAmount; + int FinishTime; +}; + +struct THouseTransfer{ + int HouseID; + int NewOwnerID; + char NewOwnerName[30]; + int Price; +}; + +struct THouseEviction{ + int HouseID; + int OwnerID; +}; + struct THouseOwner{ int HouseID; int OwnerID; @@ -701,8 +721,18 @@ public: }; bool LoadWorldID(const char *WorldName, int *WorldID); -//bool DecrementIsOnline(int WorldID, int CharacterID); -bool LoadHouseOwners(int WorldID, DynamicArray<THouseOwner> *HouseOwners); +bool DecrementIsOnline(int WorldID, int CharacterID); +bool FinishHouseAuctions(int WorldID, DynamicArray<THouseAuction> *Auctions); +bool FinishHouseTransfers(int WorldID, DynamicArray<THouseTransfer> *Transfers); +bool LoadFreeAccountEvictions(int WorldID, DynamicArray<THouseEviction> *Evictions); +bool LoadDeletedCharacterEvictions(int WorldID, DynamicArray<THouseEviction> *Evictions); +bool CheckGuildLeaderStatus(int WorldID, int CharacterID, bool *IsGuildLeader); +bool InsertHouseOwner(int WorldID, int HouseID, int OwnerID, int PaidUntil); +bool UpdateHouseOwner(int WorldID, int HouseID, int OwnerID, int PaidUntil); +bool DeleteHouseOwner(int WorldID, int HouseID); +bool LoadHouseOwners(int WorldID, DynamicArray<THouseOwner> *Owners); +bool LoadHouseAuctions(int WorldID, DynamicArray<int> *Auctions); +bool StartHouseAuction(int WorldID, int HouseID); bool DeleteHouses(int WorldID); bool InsertHouses(int WorldID, int NumHouses, THouse *Houses); bool ClearIsOnline(int WorldID, int *NumAffectedCharacters); @@ -711,6 +741,7 @@ bool InsertOnlineCharacters(int WorldID, int NumCharacters, TOnlineCharacter *Ch bool CheckOnlineRecord(int WorldID, int NumCharacters, bool *NewRecord); bool LoadCharacterIndex(int WorldID, int MinimumCharacterID, int MaxEntries, int *NumEntries, TCharacterIndexEntry *Entries); +bool ExcludeFromAuctions(int WorldID, int CharacterID, int Duration, int BanishmentID); bool LoadWorldConfig(int WorldID, TWorldConfig *WorldConfig); bool FileExists(const char *FileName); |