From f188d54236256e3b820425a98d7d06e422673a97 Mon Sep 17 00:00:00 2001 From: fusion32 Date: Mon, 13 Oct 2025 14:56:50 -0300 Subject: beginning of postgres driver + overall tweaks --- .gitignore | 5 +- config.cfg | 35 +-- postgres/init.sql | 117 +++++++++ postgres/schema.sql | 292 ++++++++++++++++++++++ sqlite/schema.sql | 8 +- src/database_postgres.cc | 617 ++++++++++++++++++++++++++++++++++++++++++++++- src/database_sqlite.cc | 19 +- src/query.cc | 3 +- src/querymanager.cc | 130 ++++++---- src/querymanager.hh | 60 +++-- 10 files changed, 1194 insertions(+), 92 deletions(-) create mode 100644 postgres/init.sql create mode 100644 postgres/schema.sql diff --git a/.gitignore b/.gitignore index 8e0799f..e32b57d 100644 --- a/.gitignore +++ b/.gitignore @@ -1,6 +1,9 @@ .vscode +.cache bin build local *.log -*.db \ No newline at end of file +*.db +compile_commands.json + diff --git a/config.cfg b/config.cfg index 076c535..e2c77d3 100644 --- a/config.cfg +++ b/config.cfg @@ -3,27 +3,34 @@ MaxCachedHostNames = 100 HostNameExpireTime = 30m # SQLite Config -SQLite.File = "tibia.db" -SQLite.MaxCachedStatements = 100 +#SQLite.File = "tibia.db" +#SQLite.MaxCachedStatements = 100 # PostgreSQL Config -#PostgreSQL.UnixSocket = "" -#PostgreSQL.Host = "localhost" -#PostgreSQL.Port = 5432 -#PostgreSQL.User = "postgres" +# NOTE(fusion): These options are passed directly to `PQconnectdbParams`. +# Empty values are ignored, meaning their defaults will be used instead. +# For more information see: +# https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-PARAMKEYWORDS +PostgreSQL.Host = "localhost" +PostgreSQL.Port = "5432" +PostgreSQL.DBName = "tibia" +PostgreSQL.User = "tibia" #PostgreSQL.Password = "" -#PostgreSQL.Database = "tibia" -#PostgreSQL.TLS = true -#PostgreSQL.MaxCachedStatements = 100 +PostgreSQL.Password = "password" +PostgreSQL.ConnectTimeout = "" +PostgreSQL.ClientEncoding = "UTF8" +PostgreSQL.ApplicationName = "QueryManager" +PostgreSQL.SSLMode = "" +PostgreSQL.SSLRootCert = "" +PostgreSQL.MaxCachedStatements = 100 # MySQL/MariaDB Config -#MySQL.UnixSocket = "" #MySQL.Host = "localhost" -#MySQL.Port = 3306 -#MySQL.User = "mysql" +#MySQL.Port = "3306" +#MySQL.DBName = "tibia" +#MySQL.User = "tibia" #MySQL.Password = "" -#MySQL.Database = "tibia" -#MySQL.TLS = true +#MySQL.UnixSocket = "" #MySQL.MaxCachedStatements = 100 # Connection Config diff --git a/postgres/init.sql b/postgres/init.sql new file mode 100644 index 0000000..d26379c --- /dev/null +++ b/postgres/init.sql @@ -0,0 +1,117 @@ +-- NOTE(fusion): This file contains sample initial data. It's wrapped into a +-- transaction to avoid errors from leaving the database in some partial state. +BEGIN; + +-- 111111/tibia +INSERT INTO Accounts (AccountID, Email, Auth) + VALUES (111111, '@tibia', '\x206699cbc2fae1683118c873d746aa376049cb5923ef0980298bb7acbba527ec9e765668f7a338dffea34acf61a20efb654c1e9c62d35148dba2aeeef8dc7788'); + +-- NOTE(fusion): This would generally be a sequence of insertions but the way +-- PostgreSQL handles unique auto increment columns makes so manually inserted +-- values end up colliding with generated values at some point. +-- On a real scenario, we'd never have manually assigned ids unless we're +-- restoring some database dump, in which case it would already have saved +-- sequences. +WITH + WorldIns AS ( + INSERT INTO Worlds (Name, Type, RebootTime, Host, Port, MaxPlayers, + PremiumPlayerBuffer, MaxNewbies, PremiumNewbieBuffer) + VALUES ('Zanera', 0, 5, 'localhost', 7172, 1000, 100, 300, 100) + RETURNING WorldID, Name + ), + CharacterIns AS ( + INSERT INTO Characters (WorldID, AccountID, Name, Sex) + SELECT W.WorldID, C.AccountID, C.Name, C.Sex + FROM WorldIns AS W, + LATERAL (VALUES + (111111, 'Gamemaster on ' || W.Name, 1), + (111111, 'Player on ' || W.Name, 1) + ) AS C (AccountID, Name, Sex) + RETURNING CharacterID, Name + ) +INSERT INTO CharacterRights(CharacterID, Name) + SELECT C.CharacterID, R.RightName + FROM CharacterIns AS C, + (VALUES + ('NOTATION'), + ('NAMELOCK'), + ('STATEMENT_REPORT'), + ('BANISHMENT'), + ('FINAL_WARNING'), + ('IP_BANISHMENT'), + ('KICK'), + ('HOME_TELEPORT'), + ('GAMEMASTER_BROADCAST'), + ('ANONYMOUS_BROADCAST'), + ('NO_BANISHMENT'), + ('ALLOW_MULTICLIENT'), + ('LOG_COMMUNICATION'), + ('READ_GAMEMASTER_CHANNEL'), + ('READ_TUTOR_CHANNEL'), + ('HIGHLIGHT_HELP_CHANNEL'), + ('SEND_BUGREPORTS'), + ('NAME_INSULTING'), + ('NAME_SENTENCE'), + ('NAME_NONSENSICAL_LETTERS'), + ('NAME_BADLY_FORMATTED'), + ('NAME_NO_PERSON'), + ('NAME_CELEBRITY'), + ('NAME_COUNTRY'), + ('NAME_FAKE_IDENTITY'), + ('NAME_FAKE_POSITION'), + ('STATEMENT_INSULTING'), + ('STATEMENT_SPAMMING'), + ('STATEMENT_ADVERT_OFFTOPIC'), + ('STATEMENT_ADVERT_MONEY'), + ('STATEMENT_NON_ENGLISH'), + ('STATEMENT_CHANNEL_OFFTOPIC'), + ('STATEMENT_VIOLATION_INCITING'), + ('CHEATING_BUG_ABUSE'), + ('CHEATING_GAME_WEAKNESS'), + ('CHEATING_MACRO_USE'), + ('CHEATING_MODIFIED_CLIENT'), + ('CHEATING_HACKING'), + ('CHEATING_MULTI_CLIENT'), + ('CHEATING_ACCOUNT_TRADING'), + ('CHEATING_ACCOUNT_SHARING'), + ('GAMEMASTER_THREATENING'), + ('GAMEMASTER_PRETENDING'), + ('GAMEMASTER_INFLUENCE'), + ('GAMEMASTER_FALSE_REPORTS'), + ('KILLING_EXCESSIVE_UNJUSTIFIED'), + ('DESTRUCTIVE_BEHAVIOUR'), + ('SPOILING_AUCTION'), + ('INVALID_PAYMENT'), + ('TELEPORT_TO_CHARACTER'), + ('TELEPORT_TO_MARK'), + ('TELEPORT_VERTICAL'), + ('TELEPORT_TO_COORDINATE'), + ('LEVITATE'), + ('SPECIAL_MOVEUSE'), + ('MODIFY_GOSTRENGTH'), + ('SHOW_COORDINATE'), + ('RETRIEVE'), + ('ENTER_HOUSES'), + ('OPEN_NAMEDOORS'), + ('INVULNERABLE'), + ('UNLIMITED_MANA'), + ('KEEP_INVENTORY'), + ('ALL_SPELLS'), + ('UNLIMITED_CAPACITY'), + ('ATTACK_EVERYWHERE'), + ('NO_LOGOUT_BLOCK'), + ('GAMEMASTER_OUTFIT'), + ('ILLUMINATE'), + ('CHANGE_PROFESSION'), + ('IGNORED_BY_MONSTERS'), + ('SHOW_KEYHOLE_NUMBERS'), + ('CREATE_OBJECTS'), + ('CREATE_MONEY'), + ('CREATE_MONSTERS'), + ('CHANGE_SKILLS'), + ('CLEANUP_FIELDS'), + ('NO_STATISTICS') + ) AS R (RightName) + WHERE C.Name = 'Gamemaster'; + +COMMIT; diff --git a/postgres/schema.sql b/postgres/schema.sql new file mode 100644 index 0000000..548e43c --- /dev/null +++ b/postgres/schema.sql @@ -0,0 +1,292 @@ +-- NOTE(fusion): Everything is inside a transaction to avoid errors from leaving +-- the database in some partial state. Also, notice we don't use `IF NOT EXISTS` +-- because it could also leave the database in a bad state if for example, some +-- old version of a table already existed. +BEGIN; + +-- IMPORTANT(fusion): PosgreSQL doesn't have a defined `NOCASE` collation like +-- SQLite and doesn't use a default case-insensitive collation like MySQL. The +-- simplest alternative is to create a custom collation. +-- This is only supported with PostgreSQL 10+ but it should safe to assume that +-- it's widely supported, given that it's almost 10 years old and well past its +-- end-of-life. +-- For more information see: +-- https://www.postgresql.org/docs/current/collation.html +CREATE COLLATION NOCASE ( + provider = icu, + deterministic = false, + locale = 'und-u-ks-level2' +); + +-- IMPORTANT(fusion): Since we're already assuming PostgreSQL 10+ for collations, +-- it's probably a good idea to use IDENTITY columns instead of SERIAL. They're +-- also supported with PostgreSQL 10+ and are supposed so fix some shortcommings +-- of SERIAL. + +-- TODO(fusion): SQLite tables didn't use foreign key constraints so I'm also not +-- using them here. It might be a good idea for consistency, but it's not a hard +-- requirement. + +-- Primary Tables +--============================================================================== +CREATE TABLE Worlds ( + WorldID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY, + Name TEXT NOT NULL COLLATE NOCASE, + Type SMALLINT NOT NULL, + RebootTime SMALLINT NOT NULL, + Host TEXT NOT NULL, + Port INTEGER NOT NULL, + MaxPlayers SMALLINT NOT NULL, + PremiumPlayerBuffer SMALLINT NOT NULL, + MaxNewbies SMALLINT NOT NULL, + PremiumNewbieBuffer SMALLINT NOT NULL, + OnlineRecord SMALLINT NOT NULL DEFAULT 0, + OnlineRecordTimestamp TIMESTAMPTZ NOT NULL DEFAULT 'epoch', + PRIMARY KEY (WorldID), + UNIQUE (Name) +); + +CREATE TABLE Accounts ( + AccountID INTEGER NOT NULL, + Email TEXT NOT NULL COLLATE NOCASE, + Auth BYTEA NOT NULL, + PremiumEnd TIMESTAMPTZ NOT NULL DEFAULT 'epoch', + PendingPremiumDays SMALLINT NOT NULL DEFAULT 0, + Deleted BOOLEAN NOT NULL DEFAULT FALSE, + PRIMARY KEY (AccountID), + UNIQUE (Email) +); + +CREATE TABLE Characters ( + WorldID INTEGER NOT NULL, + CharacterID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY, + AccountID INTEGER NOT NULL, + Name TEXT NOT NULL COLLATE NOCASE, + Sex SMALLINT NOT NULL, + Guild TEXT NOT NULL COLLATE NOCASE DEFAULT '', + Rank TEXT NOT NULL COLLATE NOCASE DEFAULT '', + Title TEXT NOT NULL DEFAULT '', + Level SMALLINT NOT NULL DEFAULT 0, + Profession TEXT NOT NULL DEFAULT '', + Residence TEXT NOT NULL DEFAULT '', + LastLoginTime TIMESTAMPTZ NOT NULL DEFAULT 'epoch', + TutorActivities INTEGER NOT NULL DEFAULT 0, + IsOnline SMALLINT NOT NULL DEFAULT 0, + Deleted BOOLEAN NOT NULL DEFAULT FALSE, + PRIMARY KEY (CharacterID), + UNIQUE (Name) +); +CREATE INDEX CharactersWorldIndex ON Characters(WorldID, IsOnline); +CREATE INDEX CharactersAccountIndex ON Characters(AccountID, IsOnline); +CREATE INDEX CharactersGuildIndex ON Characters(Guild, Rank); + +-- NOTE(fusion): It seems `RIGHT` is a reserved keyword and trying to use +-- it as a column name will generate an error. +CREATE TABLE CharacterRights ( + CharacterID INTEGER NOT NULL, + Name TEXT NOT NULL COLLATE NOCASE, + PRIMARY KEY(CharacterID, Name) +); + +CREATE TABLE CharacterDeaths ( + CharacterID INTEGER NOT NULL, + Level SMALLINT NOT NULL, + OffenderID INTEGER NOT NULL, + Remark TEXT NOT NULL, + Unjustified BOOLEAN NOT NULL, + Timestamp TIMESTAMPTZ NOT NULL +); +CREATE INDEX CharacterDeathsCharacterIndex ON CharacterDeaths(CharacterID, Level); +CREATE INDEX CharacterDeathsOffenderIndex ON CharacterDeaths(OffenderID, Unjustified); + +CREATE TABLE Buddies ( + WorldID INTEGER NOT NULL, + AccountID INTEGER NOT NULL, + BuddyID INTEGER NOT NULL, + PRIMARY KEY (WorldID, AccountID, BuddyID) +); + +CREATE TABLE WorldInvitations ( + WorldID INTEGER NOT NULL, + CharacterID INTEGER NOT NULL, + PRIMARY KEY (WorldID, CharacterID) +); + +CREATE TABLE LoginAttempts ( + AccountID INTEGER NOT NULL, + IPAddress INET NOT NULL, + Timestamp TIMESTAMPTZ NOT NULL, + Failed BOOLEAN NOT NULL +); +CREATE INDEX LoginAttemptsAccountIndex ON LoginAttempts(AccountID, Timestamp); +CREATE INDEX LoginAttemptsAddressIndex ON LoginAttempts(IPAddress, Timestamp); + +-- House Tables +--============================================================================== +CREATE TABLE Houses ( + WorldID INTEGER NOT NULL, + HouseID INTEGER NOT NULL, + Name TEXT NOT NULL, + Rent INTEGER NOT NULL, + Description TEXT NOT NULL, + Size INTEGER NOT NULL, + PositionX INTEGER NOT NULL, + PositionY INTEGER NOT NULL, + PositionZ INTEGER NOT NULL, + Town TEXT NOT NULL, + GuildHouse BOOLEAN NOT NULL, + PRIMARY KEY (WorldID, HouseID) +); + +CREATE TABLE HouseOwners ( + WorldID INTEGER NOT NULL, + HouseID INTEGER NOT NULL, + OwnerID INTEGER NOT NULL, + PaidUntil TIMESTAMPTZ NOT NULL, + PRIMARY KEY (WorldID, HouseID) +); + +-- NOTE(fusion): Auctions with a NULL `FinishTime` aren't active, to avoid running +-- multiple times with no actual bidder. It should be set after the first bid along +-- with `BidderID` and `BidAmount`. +CREATE TABLE HouseAuctions ( + WorldID INTEGER NOT NULL, + HouseID INTEGER NOT NULL, + BidderID INTEGER DEFAULT NULL, + BidAmount INTEGER DEFAULT NULL, + FinishTime TIMESTAMPTZ DEFAULT NULL, + PRIMARY KEY (WorldID, HouseID) +); + +CREATE TABLE HouseTransfers ( + WorldID INTEGER NOT NULL, + HouseID INTEGER NOT NULL, + NewOwnerID INTEGER NOT NULL, + Price INTEGER NOT NULL, + PRIMARY KEY (WorldID, HouseID) +); + +CREATE TABLE HouseAuctionExclusions ( + CharacterID INTEGER NOT NULL, + Issued TIMESTAMPTZ NOT NULL, + Until TIMESTAMPTZ NOT NULL, + BanishmentID INTEGER NOT NULL +); +CREATE INDEX HouseAuctionExclusionsIndex ON HouseAuctionExclusions(CharacterID, Until); + +CREATE TABLE HouseAssignments ( + WorldID INTEGER NOT NULL, + HouseID INTEGER NOT NULL, + OwnerID INTEGER NOT NULL, + Price INTEGER NOT NULL, + Timestamp TIMESTAMPTZ NOT NULL +); +CREATE INDEX HouseAssignmentsHouseIndex ON HouseAssignments(WorldID, HouseID); +CREATE INDEX HouseAssignmentsTimeIndex ON HouseAssignments(WorldID, Timestamp); +CREATE INDEX HouseAssignmentsOwnerIndex ON HouseAssignments(OwnerID); + +-- Banishment Tables +--============================================================================== +CREATE TABLE Banishments ( + BanishmentID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY, + AccountID INTEGER NOT NULL, + IPAddress INET NOT NULL, + GamemasterID INTEGER NOT NULL, + Reason TEXT NOT NULL, + Comment TEXT NOT NULL, + FinalWarning BOOLEAN NOT NULL, + Issued TIMESTAMPTZ NOT NULL, + Until TIMESTAMPTZ NOT NULL, + PRIMARY KEY (BanishmentID) +); +CREATE INDEX BanishmentsAccountIndex ON Banishments(AccountID, Until, FinalWarning); + +CREATE TABLE IPBanishments ( + CharacterID INTEGER NOT NULL, + IPAddress INET NOT NULL, + GamemasterID INTEGER NOT NULL, + Reason TEXT NOT NULL, + Comment TEXT NOT NULL, + Issued TIMESTAMPTZ NOT NULL, + Until TIMESTAMPTZ NOT NULL +); +CREATE INDEX IPBanishmentsAddressIndex ON IPBanishments(IPAddress); +CREATE INDEX IPBanishmentsCharacterIndex ON IPBanishments(CharacterID); + +CREATE TABLE Namelocks ( + CharacterID INTEGER NOT NULL, + IPAddress INET NOT NULL, + GamemasterID INTEGER NOT NULL, + Reason TEXT NOT NULL, + Comment TEXT NOT NULL, + Attempts INTEGER NOT NULL DEFAULT 0, + Approved BOOLEAN NOT NULL DEFAULT FALSE, + PRIMARY KEY (CharacterID) +); + +CREATE TABLE Notations ( + CharacterID INTEGER NOT NULL, + IPAddress INET NOT NULL, + GamemasterID INTEGER NOT NULL, + Reason TEXT NOT NULL, + Comment TEXT NOT NULL +); +CREATE INDEX NotationsCharacterIndex ON Notations(CharacterID); + +CREATE TABLE Statements ( + WorldID INTEGER NOT NULL, + Timestamp TIMESTAMPTZ NOT NULL, + StatementID INTEGER NOT NULL, + CharacterID INTEGER NOT NULL, + Channel TEXT NOT NULL, + Text TEXT NOT NULL, + PRIMARY KEY (WorldID, Timestamp, StatementID) +); +CREATE INDEX StatementsCharacterIndex ON Statements(CharacterID, Timestamp); + +CREATE TABLE ReportedStatements ( + WorldID INTEGER NOT NULL, + Timestamp TIMESTAMPTZ NOT NULL, + StatementID INTEGER NOT NULL, + CharacterID INTEGER NOT NULL, + BanishmentID INTEGER NOT NULL, + ReporterID INTEGER NOT NULL, + Reason TEXT NOT NULL, + Comment TEXT NOT NULL, + PRIMARY KEY (WorldID, Timestamp, StatementID) +); +CREATE INDEX ReportedStatementsCharacterIndex ON ReportedStatements(CharacterID, Timestamp); +CREATE INDEX ReportedStatementsBanishmentIndex ON ReportedStatements(BanishmentID); + +-- Info Tables +--============================================================================== +CREATE TABLE KillStatistics ( + WorldID INTEGER NOT NULL, + RaceName TEXT NOT NULL COLLATE NOCASE, + TimesKilled INTEGER NOT NULL, + PlayersKilled INTEGER NOT NULL, + PRIMARY KEY (WorldID, RaceName) +); + +CREATE TABLE OnlineCharacters ( + WorldID INTEGER NOT NULL, + Name TEXT NOT NULL COLLATE NOCASE, + Level SMALLINT NOT NULL, + Profession TEXT NOT NULL, + PRIMARY KEY (WorldID, Name) +); + +-- Schema Info +--============================================================================== +-- NOTE(fusion): The `SchemaInfo` table should hold information about the schema +-- and be used for consistency checks at startup. It currently only contains the +-- schema version, which I feel is the only value needed. +CREATE TABLE SchemaInfo ( + Key TEXT NOT NULL COLLATE NOCASE, + Value TEXT NOT NULL, + PRIMARY KEY (Key) +); + +INSERT INTO SchemaInfo (Key, Value) VALUES ('VERSION', '1'); + +COMMIT; diff --git a/sqlite/schema.sql b/sqlite/schema.sql index 677d778..106c780 100644 --- a/sqlite/schema.sql +++ b/sqlite/schema.sql @@ -36,7 +36,7 @@ CREATE TABLE IF NOT EXISTS Characters ( Sex INTEGER NOT NULL, Guild TEXT NOT NULL COLLATE NOCASE DEFAULT '', Rank TEXT NOT NULL COLLATE NOCASE DEFAULT '', - Title TEXT NOT NULL COLLATE NOCASE DEFAULT '', + Title TEXT NOT NULL DEFAULT '', Level INTEGER NOT NULL DEFAULT 0, Profession TEXT NOT NULL DEFAULT '', Residence TEXT NOT NULL DEFAULT '', @@ -132,9 +132,9 @@ CREATE TABLE IF NOT EXISTS HouseOwners ( PRIMARY KEY (WorldID, HouseID) ); --- NOTE(fusion): An auction would have a non null `FinishTime` but it doesn't make --- sense to finish an auction just to restart it afterwards so it should only be --- set after the first bid, along with `BidderID` and `BidAmount`. +-- NOTE(fusion): Auctions with a NULL `FinishTime` aren't active, to avoid running +-- multiple times with no actual bidder. It should be set after the first bid along +-- with `BidderID` and `BidAmount`. CREATE TABLE IF NOT EXISTS HouseAuctions ( WorldID INTEGER NOT NULL, HouseID INTEGER NOT NULL, diff --git a/src/database_postgres.cc b/src/database_postgres.cc index 3b24967..7c0f3f9 100644 --- a/src/database_postgres.cc +++ b/src/database_postgres.cc @@ -1,6 +1,621 @@ #if DATABASE_POSTGRESQL #include "querymanager.hh" +#include "libpq-fe.h" -// TODO +// IMPORTANT(fusion): These are the OIDs for a few of built-in data types in +// PostgreSQL. They're taken from `catalog/pg_type_d.h` which is not included +// with libpq but should be STABLE across different versions and are needed +// for properly handling binary data from the server. +#define BOOLOID 16 +#define BYTEAOID 17 +#define CHAROID 18 +#define INT8OID 20 +#define INT2OID 21 +#define INT4OID 23 +#define TEXTOID 25 +#define FLOAT4OID 700 +#define FLOAT8OID 701 +#define INETOID 869 +#define VARCHAROID 1043 +#define DATEOID 1082 +#define TIMEOID 1083 +#define TIMESTAMPOID 1114 +#define TIMESTAMPTZOID 1184 +#define INTERVALOID 1186 +#define TIMETZOID 1266 + +struct TCachedStatement{ + char Name[16]; + int LastUsed; + uint32 Hash; + char *Text; +}; + +struct TDatabase{ + PGconn *Handle; + int MaxCachedStatements; + TCachedStatement *CachedStatements; +}; + +// Statement Cache +//============================================================================== +// NOTE(fusion): Prepared statements are stored server-side and only referenced +// by name. They're not shared between sessions and are automatically cleaned up +// when the connection is CLOSED or RESET. + +void EnsureStatementCache(TDatabase *Database){ + ASSERT(Database != NULL); + if(Database->CachedStatements == NULL){ + ASSERT(g_Config.PostgreSQL.MaxCachedStatements > 0); + Database->MaxCachedStatements = g_Config.PostgreSQL.MaxCachedStatements; + if(Database->MaxCachedStatements > 9999){ + LOG_WARN("There is currently a hard limit of 9999 max cached statements" + " for PostgreSQL but it should be way more than needed because" + " there are ABSOLUTELY NOT 9999 different queries."); + Database->MaxCachedStatements = 9999; + } + + Database->CachedStatements = (TCachedStatement*)calloc( + Database->MaxCachedStatements, sizeof(TCachedStatement)); + for(int i = 0; i < Database->MaxCachedStatements; i += 1){ + if(!StringBufFormat(Database->CachedStatements[i].Name, "STMT%d", i)){ + PANIC("Failed to format statement cache entry name for STMT%d", i); + } + } + } +} + +void DeleteStatementCache(TDatabase *Database){ + ASSERT(Database != NULL); + if(Database->CachedStatements != NULL){ + ASSERT(Database->MaxCachedStatements > 0); + for(int i = 0; i < Database->MaxCachedStatements; i += 1){ + TCachedStatement *Entry = &Database->CachedStatements[i]; + // NOTE(fusion): There is little reason to use `PQclosePrepared` here + // because this function would usually be called with `PQreset` or + // `PQfinish` which should already clear any prepared statements + // created for the session. + if(Entry->Text != NULL){ + free(Entry->Text); + Entry->LastUsed = 0; + Entry->Hash = 0; + Entry->Text = NULL; + } + } + + // TODO(fusion): We might not need to use `PQclosePrepared` but it could + // be a good idea to do some ExecInternal("DEALLOCATE ALL"), which would + // clear all prepared statements from the current session. + + free(Database->CachedStatements); + Database->MaxCachedStatements = 0; + Database->CachedStatements = NULL; + } +} + +// IMPORTANT(fusion): Even though it is possible to declare parameter types with +// OIDs, it is simpler to use explicit casts such as `$1::INTEGER` to enforce types. +// It also makes so all relevant information about the query is packed into `Text` +// so we don't need to track anything else to ensure statements with different +// types are kept separate. +const char *PrepareQuery(TDatabase *Database, const char *Text){ + ASSERT(Database != NULL); + EnsureStatementCache(Database); + + TCachedStatement *Stmt = NULL; + int LeastRecentlyUsed = 0; + int LeastRecentlyUsedTime = Database->CachedStatements[0].LastUsed; + uint32 Hash = HashString(Text); + for(int i = 0; i < Database->MaxCachedStatements; i += 1){ + TCachedStatement *Entry = &Database->CachedStatements[i]; + + if(Entry->LastUsed < LeastRecentlyUsedTime){ + LeastRecentlyUsed = i; + LeastRecentlyUsedTime = Entry->LastUsed; + } + + if(Entry->Text != NULL && Entry->Hash == Hash){ + if(StringEq(Entry->Text, Text)){ + Stmt = Entry; + Entry->LastUsed = GetMonotonicUptimeMS(); + break; + } + } + } + + if(Stmt == NULL){ + Stmt = &Database->CachedStatements[LeastRecentlyUsed]; + + if(Stmt->Text != NULL){ + PGresult *Result = PQclosePrepared(Database->Handle, Stmt->Name); + if(PQresultStatus(Result) != PGRES_COMMAND_OK){ + char OldPreview[30]; + StringBufCopyEllipsis(OldPreview, Stmt->Text); + LOG_ERR("Failed to close prepared query \"%s\": %s", + OldPreview, PQerrorMessage(Database->Handle)); + } + PQclear(Result); + free(Stmt->Text); + } + + { + PGresult *Result = PQprepare(Database->Handle, Stmt->Name, Text, 0, NULL); + if(PQresultStatus(Result) != PGRES_COMMAND_OK){ + char NewPreview[30]; + StringBufCopyEllipsis(NewPreview, Text); + LOG_ERR("Failed to prepare query \"%s\": %s", + NewPreview, PQerrorMessage(Database->Handle)); + PQclear(Result); + return NULL; + } + PQclear(Result); + } + + + Stmt->LastUsed = GetMonotonicUptimeMS(); + Stmt->Hash = Hash; + Stmt->Text = strdup(Text); + ASSERT(Stmt->Text != NULL); + +#if 1 // DEBUG_STATEMENT_CACHE + { + char Preview[30]; + StringBufCopyEllipsis(Preview, Text); + LOG("New statement cached: \"%s\"", Preview); + + PGresult *Result = PQdescribePrepared(Database->Handle, Stmt->Name); + if(PQresultStatus(Result) == PGRES_COMMAND_OK){ + LOG(" PARAM OIDs:"); + for(int i = 0; i < PQnparams(Result); i += 1){ + LOG(" $%d: %d", i, PQparamtype(Result, i)); + } + + LOG(" RESULT OIDs:"); + for(int i = 0; i < PQnfields(Result); i += 1){ + LOG(" (%d) %s: %d", i, PQfname(Result, i), PQftype(Result, i)); + } + } + PQclear(Result); + } +#endif + } + + return Stmt->Name; +} + +// Database Management +//============================================================================== +void DatabaseClose(TDatabase *Database){ + if(Database != NULL){ + if(Database->Handle != NULL){ + PQfinish(Database->Handle); + Database->Handle = NULL; + } + + free(Database); + } +} + +TDatabase *DatabaseOpen(void){ + const char *Keys[] = { + "host", + "port", + "dbname", + "user", + "password", + "connect_timeout", + "client_encoding", + "application_name", + "sslmode", + "sslrootcert", + NULL, // sentinel + }; + + const char *Values[] = { + g_Config.PostgreSQL.Host, + g_Config.PostgreSQL.Port, + g_Config.PostgreSQL.DBName, + g_Config.PostgreSQL.User, + g_Config.PostgreSQL.Password, + g_Config.PostgreSQL.ConnectTimeout, + g_Config.PostgreSQL.ClientEncoding, + g_Config.PostgreSQL.ApplicationName, + g_Config.PostgreSQL.SSLMode, + g_Config.PostgreSQL.SSLRootCert, + NULL, // sentinel + }; + + TDatabase *Database = (TDatabase*)calloc(1, sizeof(TDatabase)); + Database->Handle = PQconnectdbParams(Keys, Values, 0); + if(Database->Handle == NULL){ + LOG_ERR("Failed to allocate database connection"); + DatabaseClose(Database); + return NULL; + } + + if(PQstatus(Database->Handle) != CONNECTION_OK){ + LOG_ERR("Failed to establish connection: %s", PQerrorMessage(Database->Handle)); + DatabaseClose(Database); + return NULL; + } + +//===== + // TODO(fusion): REMOVE. + { + const char *Stmt = PrepareQuery(Database, + "SELECT Value::INTEGER FROM SchemaInfo WHERE Key = $1::TEXT"); + if(Stmt == NULL){ + LOG_ERR("Failed to prepare query"); + DatabaseClose(Database); + return NULL; + } + + const char *ParamValues[] = { "VERSION" }; + PGresult *Result = PQexecPrepared(Database->Handle, Stmt, 1, ParamValues, NULL, NULL, 1); + if(PQresultStatus(Result) != PGRES_TUPLES_OK){ + LOG_ERR("Failed to execute query: %s", PQerrorMessage(Database->Handle)); + PQclear(Result); + DatabaseClose(Database); + return NULL; + } + + LOG("VERSION: %d", BufferRead32BE((const uint8*)PQgetvalue(Result, 0, 0))); + PQclear(Result); + } + + const char *Stmt = PrepareQuery(Database, "SELECT Value FROM SchemaInfo WHERE Key = 'VERSION'"); + if(Stmt == NULL){ + LOG_ERR("Failed to prepare query"); + DatabaseClose(Database); + return NULL; + } + + int i = 0; + do{ + PGresult *Result = PQexecPrepared(Database->Handle, Stmt, 0, NULL, NULL, NULL, 1); + if(PQresultStatus(Result) != PGRES_TUPLES_OK){ + LOG_ERR("Failed to execute query: %s", PQerrorMessage(Database->Handle)); + PQclear(Result); + DatabaseClose(Database); + return NULL; + } + LOG("VERSION: OID = %u, LEN = %d", PQftype(Result, 0), PQgetlength(Result, 0, 0)); + PQclear(Result); + //PQreset(Database->Handle); + }while(i++ < 2); +//===== + + return Database; +} + +int DatabaseChanges(TDatabase *Database){ + ASSERT(Database != NULL); + // TODO? + return 0; +} + +bool DatabaseCheckpoint(TDatabase *Database){ + ASSERT(Database != NULL); + bool Result = true; + if(PQstatus(Database->Handle) != CONNECTION_OK){ + DeleteStatementCache(Database); + PQreset(Database->Handle); + Result = (PQstatus(Database->Handle) == CONNECTION_OK); + } + return Result; +} + +int DatabaseMaxConcurrency(void){ + return INT_MAX; +} + +// TransactionScope +//============================================================================== +TransactionScope::TransactionScope(const char *Context){ + m_Context = (Context != NULL ? Context : "NOCONTEXT"); + m_Database = NULL; +} + +TransactionScope::~TransactionScope(void){ + // TODO +} + +bool TransactionScope::Begin(TDatabase *Database){ + // TODO + return false; +} + +bool TransactionScope::Commit(void){ + // TODO + return false; +} + +// Primary Tables +//============================================================================== +bool GetWorldID(TDatabase *Database, const char *World, int *WorldID){ +// const Oid ParamTypes[] = { TEXTOID }; + const char *Stmt = PrepareQuery(Database, + "SELECT WorldID FROM Worlds WHERE Name = $1::TEXT"); +// + return false; +} + +bool GetWorlds(TDatabase *Database, DynamicArray *Worlds){ + return false; +} + +bool GetWorldConfig(TDatabase *Database, int WorldID, TWorldConfig *WorldConfig){ + return false; +} + +bool AccountExists(TDatabase *Database, int AccountID, const char *Email, bool *Result){ + return false; +} + +bool AccountNumberExists(TDatabase *Database, int AccountID, bool *Result){ + return false; +} + +bool AccountEmailExists(TDatabase *Database, const char *Email, bool *Result){ + return false; +} + +bool CreateAccount(TDatabase *Database, int AccountID, const char *Email, const uint8 *Auth, int AuthSize){ + return false; +} + +bool GetAccountData(TDatabase *Database, int AccountID, TAccount *Account){ + return false; +} + +bool GetAccountOnlineCharacters(TDatabase *Database, int AccountID, int *OnlineCharacters){ + return false; +} + +bool IsCharacterOnline(TDatabase *Database, int CharacterID, bool *Result){ + return false; +} + +bool ActivatePendingPremiumDays(TDatabase *Database, int AccountID){ + return false; +} + +bool GetCharacterEndpoints(TDatabase *Database, int AccountID, DynamicArray *Characters){ + return false; +} + +bool GetCharacterSummaries(TDatabase *Database, int AccountID, DynamicArray *Characters){ + return false; +} + +bool CharacterNameExists(TDatabase *Database, const char *Name, bool *Result){ + return false; +} + +bool CreateCharacter(TDatabase *Database, int WorldID, int AccountID, const char *Name, int Sex){ + return false; +} + +bool GetCharacterID(TDatabase *Database, int WorldID, const char *CharacterName, int *CharacterID){ + return false; +} + +bool GetCharacterLoginData(TDatabase *Database, const char *CharacterName, TCharacterLoginData *Character){ + return false; +} + +bool GetCharacterProfile(TDatabase *Database, const char *CharacterName, TCharacterProfile *Character){ + return false; +} + +bool GetCharacterRight(TDatabase *Database, int CharacterID, const char *Right, bool *Result){ + return false; +} + +bool GetCharacterRights(TDatabase *Database, int CharacterID, DynamicArray *Rights){ + return false; +} + +bool GetGuildLeaderStatus(TDatabase *Database, int WorldID, int CharacterID, bool *Result){ + return false; +} + +bool IncrementIsOnline(TDatabase *Database, int WorldID, int CharacterID){ + return false; +} + +bool DecrementIsOnline(TDatabase *Database, int WorldID, int CharacterID){ + return false; +} + +bool ClearIsOnline(TDatabase *Database, int WorldID, int *NumAffectedCharacters){ + return false; +} + +bool LogoutCharacter(TDatabase *Database, int WorldID, int CharacterID, int Level, + const char *Profession, const char *Residence, int LastLoginTime, int TutorActivities){ + return false; +} + +bool GetCharacterIndexEntries(TDatabase *Database, int WorldID, int MinimumCharacterID, + int MaxEntries, int *NumEntries, TCharacterIndexEntry *Entries){ + return false; +} + +bool InsertCharacterDeath(TDatabase *Database, int WorldID, int CharacterID, int Level, + int OffenderID, const char *Remark, bool Unjustified, int Timestamp){ + return false; +} + +bool InsertBuddy(TDatabase *Database, int WorldID, int AccountID, int BuddyID){ + return false; +} + +bool DeleteBuddy(TDatabase *Database, int WorldID, int AccountID, int BuddyID){ + return false; +} + +bool GetBuddies(TDatabase *Database, int WorldID, int AccountID, DynamicArray *Buddies){ + return false; +} + +bool GetWorldInvitation(TDatabase *Database, int WorldID, int CharacterID, bool *Result){ + return false; +} + +bool InsertLoginAttempt(TDatabase *Database, int AccountID, int IPAddress, bool Failed){ + return false; +} + +bool GetAccountFailedLoginAttempts(TDatabase *Database, int AccountID, int TimeWindow, int *Result){ + return false; +} + +bool GetIPAddressFailedLoginAttempts(TDatabase *Database, int IPAddress, int TimeWindow, int *Result){ + return false; +} + + +// House Tables +//============================================================================== +bool FinishHouseAuctions(TDatabase *Database, int WorldID, DynamicArray *Auctions){ + return false; +} + +bool FinishHouseTransfers(TDatabase *Database, int WorldID, DynamicArray *Transfers){ + return false; +} + +bool GetFreeAccountEvictions(TDatabase *Database, int WorldID, DynamicArray *Evictions){ + return false; +} + +bool GetDeletedCharacterEvictions(TDatabase *Database, int WorldID, DynamicArray *Evictions){ + return false; +} + +bool InsertHouseOwner(TDatabase *Database, int WorldID, int HouseID, int OwnerID, int PaidUntil){ + return false; +} + +bool UpdateHouseOwner(TDatabase *Database, int WorldID, int HouseID, int OwnerID, int PaidUntil){ + return false; +} + +bool DeleteHouseOwner(TDatabase *Database, int WorldID, int HouseID){ + return false; +} + +bool GetHouseOwners(TDatabase *Database, int WorldID, DynamicArray *Owners){ + return false; +} + +bool GetHouseAuctions(TDatabase *Database, int WorldID, DynamicArray *Auctions){ + return false; +} + +bool StartHouseAuction(TDatabase *Database, int WorldID, int HouseID){ + return false; +} + +bool DeleteHouses(TDatabase *Database, int WorldID){ + return false; +} + +bool InsertHouses(TDatabase *Database, int WorldID, int NumHouses, THouse *Houses){ + return false; +} + +bool ExcludeFromAuctions(TDatabase *Database, int WorldID, int CharacterID, int Duration, int BanishmentID){ + return false; +} + + +// Banishment Tables +//============================================================================== +bool IsCharacterNamelocked(TDatabase *Database, int CharacterID, bool *Result){ + return false; +} + +bool GetNamelockStatus(TDatabase *Database, int CharacterID, TNamelockStatus *Status){ + return false; +} + +bool InsertNamelock(TDatabase *Database, int CharacterID, int IPAddress, + int GamemasterID, const char *Reason, const char *Comment){ + return false; +} + +bool IsAccountBanished(TDatabase *Database, int AccountID, bool *Result){ + return false; +} + +bool GetBanishmentStatus(TDatabase *Database, int CharacterID, TBanishmentStatus *Status){ + return false; +} + +bool InsertBanishment(TDatabase *Database, int CharacterID, int IPAddress, int GamemasterID, + const char *Reason, const char *Comment, bool FinalWarning, int Duration, int *BanishmentID){ + return false; +} + +bool GetNotationCount(TDatabase *Database, int CharacterID, int *Result){ + return false; +} + +bool InsertNotation(TDatabase *Database, int CharacterID, int IPAddress, + int GamemasterID, const char *Reason, const char *Comment){ + return false; +} + +bool IsIPBanished(TDatabase *Database, int IPAddress, bool *Result){ + return false; +} + +bool InsertIPBanishment(TDatabase *Database, int CharacterID, int IPAddress, + int GamemasterID, const char *Reason, const char *Comment, int Duration){ + return false; +} + +bool IsStatementReported(TDatabase *Database, int WorldID, TStatement *Statement, bool *Result){ + return false; +} + +bool InsertStatements(TDatabase *Database, int WorldID, int NumStatements, TStatement *Statements){ + return false; +} + +bool InsertReportedStatement(TDatabase *Database, int WorldID, TStatement *Statement, + int BanishmentID, int ReporterID, const char *Reason, const char *Comment){ + return false; +} + + +// Info Tables +//============================================================================== +bool GetKillStatistics(TDatabase *Database, int WorldID, DynamicArray *Stats){ + return false; +} + +bool MergeKillStatistics(TDatabase *Database, int WorldID, int NumStats, TKillStatistics *Stats){ + return false; +} + +bool GetOnlineCharacters(TDatabase *Database, int WorldID, DynamicArray *Characters){ + return false; +} + +bool DeleteOnlineCharacters(TDatabase *Database, int WorldID){ + return false; +} + +bool InsertOnlineCharacters(TDatabase *Database, int WorldID, + int NumCharacters, TOnlineCharacter *Characters){ + return false; +} + +bool CheckOnlineRecord(TDatabase *Database, int WorldID, int NumCharacters, bool *NewRecord){ + return false; +} #endif //DATABASE_POSTGRESQL diff --git a/src/database_sqlite.cc b/src/database_sqlite.cc index 37a62e0..50f8bda 100644 --- a/src/database_sqlite.cc +++ b/src/database_sqlite.cc @@ -4,7 +4,7 @@ struct TCachedStatement{ sqlite3_stmt *Stmt; - int64 LastUsed; + int LastUsed; uint32 Hash; }; @@ -43,6 +43,7 @@ public: }; static void EnsureStatementCache(TDatabase *Database){ + ASSERT(Database != NULL); if(Database->CachedStatements == NULL){ ASSERT(g_Config.SQLite.MaxCachedStatements > 0); Database->MaxCachedStatements = g_Config.SQLite.MaxCachedStatements; @@ -52,6 +53,7 @@ static void EnsureStatementCache(TDatabase *Database){ } static void DeleteStatementCache(TDatabase *Database){ + ASSERT(Database != NULL); if(Database->CachedStatements != NULL){ ASSERT(Database->MaxCachedStatements > 0); for(int i = 0; i < Database->MaxCachedStatements; i += 1){ @@ -72,11 +74,12 @@ static void DeleteStatementCache(TDatabase *Database){ } static sqlite3_stmt *PrepareQuery(TDatabase *Database, const char *Text){ + ASSERT(Database != NULL); EnsureStatementCache(Database); sqlite3_stmt *Stmt = NULL; int LeastRecentlyUsed = 0; - int64 LeastRecentlyUsedTime = Database->CachedStatements[0].LastUsed; + int LeastRecentlyUsedTime = Database->CachedStatements[0].LastUsed; uint32 Hash = HashString(Text); for(int i = 0; i < Database->MaxCachedStatements; i += 1){ TCachedStatement *Entry = &Database->CachedStatements[i]; @@ -89,7 +92,7 @@ static sqlite3_stmt *PrepareQuery(TDatabase *Database, const char *Text){ if(Entry->Stmt != NULL && Entry->Hash == Hash){ const char *EntryText = sqlite3_sql(Entry->Stmt); ASSERT(EntryText != NULL); - if(strcmp(EntryText, Text) == 0){ + if(StringEq(EntryText, Text)){ Stmt = Entry->Stmt; Entry->LastUsed = GetMonotonicUptimeMS(); break; @@ -128,7 +131,7 @@ static sqlite3_stmt *PrepareQuery(TDatabase *Database, const char *Text){ return Stmt; } -// Database Initialization +// Database Management //============================================================================== // NOTE(fusion): From `https://www.sqlite.org/pragma.html`: // "Some pragmas take effect during the SQL compilation stage, not the execution @@ -441,7 +444,7 @@ bool TransactionScope::Commit(void){ return true; } -// Primary tables +// Primary Tables //============================================================================== bool GetWorldID(TDatabase *Database, const char *World, int *WorldID){ ASSERT(Database != NULL && World != NULL && WorldID != NULL); @@ -1488,7 +1491,7 @@ bool GetIPAddressFailedLoginAttempts(TDatabase *Database, int IPAddress, int Tim return true; } -// House tables +// House Tables //============================================================================== bool FinishHouseAuctions(TDatabase *Database, int WorldID, DynamicArray *Auctions){ ASSERT(Database != NULL && Auctions != NULL); @@ -1896,7 +1899,7 @@ bool ExcludeFromAuctions(TDatabase *Database, int WorldID, int CharacterID, int return sqlite3_changes(Database->Handle) > 0; } -// Banishment tables +// Banishment Tables //============================================================================== bool IsCharacterNamelocked(TDatabase *Database, int CharacterID, bool *Result){ ASSERT(Database != NULL && Result != NULL); @@ -2291,7 +2294,7 @@ bool InsertReportedStatement(TDatabase *Database, int WorldID, TStatement *State return true; } -// Info tables +// Info Tables //============================================================================== bool GetKillStatistics(TDatabase *Database, int WorldID, DynamicArray *Stats){ ASSERT(Database != NULL && Stats != NULL); diff --git a/src/query.cc b/src/query.cc index b980b71..6c2512a 100644 --- a/src/query.cc +++ b/src/query.cc @@ -323,7 +323,8 @@ bool InitQuery(void){ } if(NumWorkersDone > 0){ - LOG_ERR("%d worker threads failed to initialize", NumWorkersDone); + LOG_ERR("%d worker thread%s failed to initialize", + NumWorkersDone, (NumWorkersDone == 1 ? "" : "s")); return false; } diff --git a/src/querymanager.cc b/src/querymanager.cc index 004bc35..773ebb3 100644 --- a/src/querymanager.cc +++ b/src/querymanager.cc @@ -9,8 +9,8 @@ # error "Operating system not currently supported." #endif +int64 g_StartTimeMS = 0; AtomicInt g_ShutdownSignal = {}; -int g_StartTimeMS = 0; TConfig g_Config = {}; void LogAdd(const char *Prefix, const char *Format, ...){ @@ -20,7 +20,14 @@ void LogAdd(const char *Prefix, const char *Format, ...){ vsnprintf(Entry, sizeof(Entry), Format, ap); va_end(ap); - if(!StringEmpty(Entry)){ + // NOTE(fusion): Trim trailing whitespace. + int Length = (int)strlen(Entry); + while(Length > 0 && isspace(Entry[Length - 1])){ + Entry[Length - 1] = 0; + Length -= 1; + } + + if(Length > 0){ 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, @@ -38,7 +45,14 @@ void LogAddVerbose(const char *Prefix, const char *Function, vsnprintf(Entry, sizeof(Entry), Format, ap); va_end(ap); - if(!StringEmpty(Entry)){ + // NOTE(fusion): Trim trailing whitespace. + int Length = (int)strlen(Entry); + while(Length > 0 && isspace(Entry[Length - 1])){ + Entry[Length - 1] = 0; + Length -= 1; + } + + if(Length > 0){ (void)File; (void)Line; struct tm LocalTime = GetLocalTime(time(NULL)); @@ -160,6 +174,31 @@ bool StringCopy(char *Dest, int DestCapacity, const char *Src){ return StringCopyN(Dest, DestCapacity, Src, SrcLength); } +void StringCopyEllipsis(char *Dest, int DestCapacity, const char *Src){ + ASSERT(DestCapacity > 0); + int SrcLength = (Src != NULL ? (int)strlen(Src) : 0); + if(SrcLength < DestCapacity){ + memcpy(Dest, Src, SrcLength); + Dest[SrcLength] = 0; + }else{ + memcpy(Dest, Src, DestCapacity); + if(DestCapacity >= 4){ + Dest[DestCapacity - 4] = '.'; + Dest[DestCapacity - 3] = '.'; + Dest[DestCapacity - 2] = '.'; + } + Dest[DestCapacity - 1] = 0; + } +} + +bool StringFormat(char *Dest, int DestCapacity, const char *Format, ...){ + va_list ap; + va_start(ap, Format); + int Written = vsnprintf(Dest, DestCapacity, Format, ap); + va_end(ap); + return Written >= 0 && Written < DestCapacity; +} + uint32 HashString(const char *String){ // FNV1a 32-bits uint32 Hash = 0x811C9DC5U; @@ -383,37 +422,41 @@ bool ReadConfig(const char *FileName, TConfig *Config){ }else if(StringEqCI(Key, "SQLite.MaxCachedStatements")){ ReadIntegerConfig(&Config->SQLite.MaxCachedStatements, Val); #elif DATABASE_POSTGRESQL - }else if(StringEqCI(Key, "PostgreSQL.UnixSocket")){ - ReadStringBufConfig(Config->PostgreSQL.UnixSocket, Val); }else if(StringEqCI(Key, "PostgreSQL.Host")){ ReadStringBufConfig(Config->PostgreSQL.Host, Val); }else if(StringEqCI(Key, "PostgreSQL.Port")){ - ReadIntegerConfig(&Config->PostgreSQL.Port, Val); + ReadStringBufConfig(Config->PostgreSQL.Port, Val); + }else if(StringEqCI(Key, "PostgreSQL.DBName")){ + ReadStringBufConfig(Config->PostgreSQL.DBName, Val); }else if(StringEqCI(Key, "PostgreSQL.User")){ ReadStringBufConfig(Config->PostgreSQL.User, Val); }else if(StringEqCI(Key, "PostgreSQL.Password")){ ReadStringBufConfig(Config->PostgreSQL.Password, Val); - }else if(StringEqCI(Key, "PostgreSQL.Database")){ - ReadStringBufConfig(Config->PostgreSQL.Database, Val); - }else if(StringEqCI(Key, "PostgreSQL.TLS")){ - ReadBooleanConfig(&Config->PostgreSQL.TLS, Val); + }else if(StringEqCI(Key, "PostgreSQL.ConnectTimeout")){ + ReadStringBufConfig(Config->PostgreSQL.ConnectTimeout, Val); + }else if(StringEqCI(Key, "PostgreSQL.ClientEncoding")){ + ReadStringBufConfig(Config->PostgreSQL.ClientEncoding, Val); + }else if(StringEqCI(Key, "PostgreSQL.ApplicationName")){ + ReadStringBufConfig(Config->PostgreSQL.ApplicationName, Val); + }else if(StringEqCI(Key, "PostgreSQL.SSLMode")){ + ReadStringBufConfig(Config->PostgreSQL.SSLMode, Val); + }else if(StringEqCI(Key, "PostgreSQL.SSLRootCert")){ + ReadStringBufConfig(Config->PostgreSQL.SSLRootCert, Val); }else if(StringEqCI(Key, "PostgreSQL.MaxCachedStatements")){ ReadIntegerConfig(&Config->PostgreSQL.MaxCachedStatements, Val); #elif DATABASE_MYSQL - }else if(StringEqCI(Key, "MySQL.UnixSocket")){ - ReadStringBufConfig(Config->MySQL.UnixSocket, Val); }else if(StringEqCI(Key, "MySQL.Host")){ ReadStringBufConfig(Config->MySQL.Host, Val); }else if(StringEqCI(Key, "MySQL.Port")){ - ReadIntegerConfig(&Config->MySQL.Port, Val); + ReadStringBufConfig(Config->MySQL.Port, Val); + }else if(StringEqCI(Key, "MySQL.DBName")){ + ReadStringBufConfig(Config->MySQL.DBName, Val); }else if(StringEqCI(Key, "MySQL.User")){ ReadStringBufConfig(Config->MySQL.User, Val); }else if(StringEqCI(Key, "MySQL.Password")){ ReadStringBufConfig(Config->MySQL.Password, Val); - }else if(StringEqCI(Key, "MySQL.Database")){ - ReadStringBufConfig(Config->MySQL.Database, Val); - }else if(StringEqCI(Key, "MySQL.TLS")){ - ReadBooleanConfig(&Config->MySQL.TLS, Val); + }else if(StringEqCI(Key, "MySQL.UnixSocket")){ + ReadStringBufConfig(Config->MySQL.UnixSocket, Val); }else if(StringEqCI(Key, "MySQL.MaxCachedStatements")){ ReadIntegerConfig(&Config->MySQL.MaxCachedStatements, Val); #endif @@ -462,6 +505,7 @@ int main(int argc, const char **argv){ (void)argc; (void)argv; + g_StartTimeMS = GetClockMonotonicMS(); AtomicStore(&g_ShutdownSignal, 0); if(!SigHandler(SIGPIPE, SIG_IGN) || !SigHandler(SIGINT, ShutdownHandler) @@ -469,8 +513,6 @@ int main(int argc, const char **argv){ return EXIT_FAILURE; } - g_StartTimeMS = GetClockMonotonicMS(); - // HostCache Config g_Config.MaxCachedHostNames = 100; g_Config.HostNameExpireTime = 30 * 60 * 1000; // milliseconds @@ -480,22 +522,24 @@ int main(int argc, const char **argv){ StringBufCopy(g_Config.SQLite.File, "tibia.db"); g_Config.SQLite.MaxCachedStatements = 100; #elif DATABASE_POSTGRESQL - StringBufCopy(g_Config.PostgreSQL.UnixSocket, ""); - StringBufCopy(g_Config.PostgreSQL.Host, "localhost"); - g_Config.PostgreSQL.Port = 5432; - StringBufCopy(g_Config.PostgreSQL.User, "postgres"); - StringBufCopy(g_Config.PostgreSQL.Password, ""); - StringBufCopy(g_Config.PostgreSQL.Database, "tibia"); - g_Config.PostgreSQL.TLS = true; + StringBufCopy(g_Config.PostgreSQL.Host, "localhost"); + StringBufCopy(g_Config.PostgreSQL.Port, "5432"); + StringBufCopy(g_Config.PostgreSQL.DBName, "tibia"); + StringBufCopy(g_Config.PostgreSQL.User, "tibia"); + StringBufCopy(g_Config.PostgreSQL.Password, ""); + StringBufCopy(g_Config.PostgreSQL.ConnectTimeout, ""); + StringBufCopy(g_Config.PostgreSQL.ClientEncoding, "UTF8"); + StringBufCopy(g_Config.PostgreSQL.ApplicationName, "QueryManager"); + StringBufCopy(g_Config.PostgreSQL.SSLMode, ""); + StringBufCopy(g_Config.PostgreSQL.SSLRootCert, ""); g_Config.PostgreSQL.MaxCachedStatements = 100; #elif DATABASE_MYSQL + StringBufCopy(g_Config.MySQL.Host, "localhost"); + StringBufCopy(g_Config.MySQL.Port, "3306"); + StringBufCopy(g_Config.MySQL.DBName, "tibia"); + StringBufCopy(g_Config.MySQL.User, "tibia"); + StringBufCopy(g_Config.MySQL.Password, ""); StringBufCopy(g_Config.MySQL.UnixSocket, ""); - StringBufCopy(g_Config.MySQL.Host, "localhost"); - g_Config.MySQL.Port = 5432; - StringBufCopy(g_Config.MySQL.User, "postgres"); - StringBufCopy(g_Config.MySQL.Password, ""); - StringBufCopy(g_Config.MySQL.Database, "tibia"); - g_Config.MySQL.TLS = true; g_Config.MySQL.MaxCachedStatements = 100; #endif @@ -508,7 +552,7 @@ int main(int argc, const char **argv){ g_Config.MaxConnections = 25; g_Config.MaxConnectionIdleTime = 60 * 1000; // milliseconds - LOG("Tibia Query Manager v0.2"); + LOG("Tibia Query Manager v0.2 (%s)", DATABASE_SYSTEM_NAME); if(!ReadConfig("config.cfg", &g_Config)){ return EXIT_FAILURE; } @@ -520,25 +564,27 @@ int main(int argc, const char **argv){ LOG("SQLite file: \"%s\"", g_Config.SQLite.File); LOG("SQLite max cached statements: %d", g_Config.SQLite.MaxCachedStatements); #elif DATABASE_POSTGRESQL - LOG("PostgreSQL unix socket: \"%s\"", g_Config.PostgreSQL.UnixSocket); LOG("PostgreSQL host: \"%s\"", g_Config.PostgreSQL.Host); - LOG("PostgreSQL port: %d", g_Config.PostgreSQL.Port); + LOG("PostgreSQL port: \"%s\"", g_Config.PostgreSQL.Port); + LOG("PostgreSQL dbname: \"%s\"", g_Config.PostgreSQL.DBName); LOG("PostgreSQL user: \"%s\"", g_Config.PostgreSQL.User); - LOG("PostgreSQL database: \"%s\"", g_Config.PostgreSQL.Database); - LOG("PostgreSQL TLS: %s", g_Config.PostgreSQL.TLS ? "true" : "false"); + LOG("PostgreSQL connect_timeout: \"%s\"", g_Config.PostgreSQL.ConnectTimeout); + LOG("PostgreSQL client_encoding: \"%s\"", g_Config.PostgreSQL.ClientEncoding); + LOG("PostgreSQL application_name: \"%s\"", g_Config.PostgreSQL.ApplicationName); + LOG("PostgreSQL sslmode: \"%s\"", g_Config.PostgreSQL.SSLMode); + LOG("PostgreSQL sslrootcert: \"%s\"", g_Config.PostgreSQL.SSLRootCert); LOG("PostgreSQL max cached statements: %d", g_Config.PostgreSQL.MaxCachedStatements); #elif DATABASE_MYSQL - LOG("MySQL unix socket: \"%s\"", g_Config.MySQL.UnixSocket); LOG("MySQL host: \"%s\"", g_Config.MySQL.Host); - LOG("MySQL port: %d", g_Config.MySQL.Port); + LOG("MySQL port: \"%s\"", g_Config.MySQL.Port); + LOG("MySQL dbname: \"%s\"", g_Config.MySQL.DBName); LOG("MySQL user: \"%s\"", g_Config.MySQL.User); - LOG("MySQL database: \"%s\"", g_Config.MySQL.Database); - LOG("MySQL TLS: %s", g_Config.MySQL.TLS ? "true" : "false"); + LOG("MySQL unix socket: \"%s\"", g_Config.MySQL.UnixSocket); LOG("MySQL max cached statements: %d", g_Config.MySQL.MaxCachedStatements); #endif LOG("Query manager port: %d", g_Config.QueryManagerPort); LOG("Query worker threads: %d", g_Config.QueryWorkerThreads); - LOG("Query buffer size: %d", g_Config.QueryBufferSize); + LOG("Query buffer size: %dB", g_Config.QueryBufferSize); LOG("Query max attempts: %d", g_Config.QueryMaxAttempts); LOG("Max connections: %d", g_Config.MaxConnections); LOG("Max connection idle time: %dms", g_Config.MaxConnectionIdleTime); diff --git a/src/querymanager.hh b/src/querymanager.hh index 2ae12b1..8f7d638 100644 --- a/src/querymanager.hh +++ b/src/querymanager.hh @@ -75,8 +75,18 @@ typedef size_t usize; TRAP(); \ }while(0) -#if !DATABASE_SQLITE && !DATABASE_POSTGRESQL && !DATABASE_MYSQL +#if (DATABASE_SQLITE + DATABASE_POSTGRESQL + DATABASE_MYSQL) == 0 # error "No database system defined." +#elif (DATABASE_SQLITE + DATABASE_POSTGRESQL + DATABASE_MYSQL) > 1 +# error "Multiple database systems defined." +#endif + +#if DATABASE_SQLITE +# define DATABASE_SYSTEM_NAME "SQLite" +#elif DATABASE_POSTGRESQL +# define DATABASE_SYSTEM_NAME "PostgreSQL" +#elif DATABASE_MYSQL +# define DATABASE_SYSTEM_NAME "MySQL" #endif struct TConfig{ @@ -92,24 +102,28 @@ struct TConfig{ } SQLite; #elif DATABASE_POSTGRESQL struct{ - char UnixSocket[100]; + // NOTE(fusion): Most of these are stored as strings because that is the + // format the connector expects for connect parameters. char Host[100]; - int Port; + char Port[30]; + char DBName[30]; char User[30]; char Password[30]; - char Database[30]; - bool TLS; + char ConnectTimeout[30]; + char ClientEncoding[30]; + char ApplicationName[30]; + char SSLMode[30]; + char SSLRootCert[100]; int MaxCachedStatements; } PostgreSQL; #elif DATABASE_MYSQL struct{ - char UnixSocket[100]; char Host[100]; - int Port; + char Port[30]; + char DBName[30]; char User[30]; char Password[30]; - char Database[30]; - bool TLS; + char UnixSocket[100]; int MaxCachedStatements; } MySQL; #endif @@ -142,6 +156,8 @@ 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); bool StringCopy(char *Dest, int DestCapacity, const char *Src); +void StringCopyEllipsis(char *Dest, int DestCapacity, const char *Src); +bool StringFormat(char *Dest, int DestCapacity, const char *Format, ...) ATTR_PRINTF(3, 4); uint32 HashString(const char *String); bool ParseIPAddress(const char *String, int *OutAddr); @@ -154,6 +170,8 @@ bool ReadConfig(const char *FileName, TConfig *Config); // IMPORTANT(fusion): These macros should only be used when `Dest` is a char array // to simplify the call to `StringCopy` where we'd use `sizeof(Dest)` to determine // the size of the destination anyways. +#define StringBufFormat(Dest, ...) StringFormat(Dest, sizeof(Dest), __VA_ARGS__) +#define StringBufCopyEllipsis(Dest, Src) StringCopyEllipsis(Dest, sizeof(Dest), Src); #define StringBufCopy(Dest, Src) StringCopy(Dest, sizeof(Dest), Src) #define StringBufCopyN(Dest, Src, SrcLength) StringCopyN(Dest, sizeof(Dest), Src, SrcLength) #define ReadStringBufConfig(Dest, Val) ReadStringConfig(Dest, sizeof(Dest), Val) @@ -502,7 +520,6 @@ private: T *NewData = (T*)realloc(m_Data, sizeof(T) * (usize)NewCapacity); if(NewData == NULL){ PANIC("Failed to resize dynamic array from %d to %d", OldCapacity, NewCapacity); - return; } // NOTE(fusion): Zero initialize newly allocated elements. @@ -777,7 +794,15 @@ struct TOnlineCharacter{ char Profession[30]; }; +// NOTE(fusion): Database Management struct TDatabase; +void DatabaseClose(TDatabase *Database); +TDatabase *DatabaseOpen(void); +int DatabaseChanges(TDatabase *Database); +bool DatabaseCheckpoint(TDatabase *Database); +int DatabaseMaxConcurrency(void); + +// NOTE(fusion): TransactionScope struct TransactionScope{ private: const char *m_Context; @@ -790,14 +815,7 @@ public: bool Commit(void); }; -// NOTE(fusion): Database management. -TDatabase *DatabaseOpen(void); -void DatabaseClose(TDatabase *Database); -int DatabaseChanges(TDatabase *Database); -bool DatabaseCheckpoint(TDatabase *Database); -int DatabaseMaxConcurrency(void); - -// NOTE(fusion): Primary tables. +// NOTE(fusion): Primary Tables bool GetWorldID(TDatabase *Database, const char *World, int *WorldID); bool GetWorlds(TDatabase *Database, DynamicArray *Worlds); bool GetWorldConfig(TDatabase *Database, int WorldID, TWorldConfig *WorldConfig); @@ -836,7 +854,7 @@ bool InsertLoginAttempt(TDatabase *Database, int AccountID, int IPAddress, bool bool GetAccountFailedLoginAttempts(TDatabase *Database, int AccountID, int TimeWindow, int *Result); bool GetIPAddressFailedLoginAttempts(TDatabase *Database, int IPAddress, int TimeWindow, int *Result); -// NOTE(fusion): House tables. +// NOTE(fusion): House Tables bool FinishHouseAuctions(TDatabase *Database, int WorldID, DynamicArray *Auctions); bool FinishHouseTransfers(TDatabase *Database, int WorldID, DynamicArray *Transfers); bool GetFreeAccountEvictions(TDatabase *Database, int WorldID, DynamicArray *Evictions); @@ -851,7 +869,7 @@ bool DeleteHouses(TDatabase *Database, int WorldID); bool InsertHouses(TDatabase *Database, int WorldID, int NumHouses, THouse *Houses); bool ExcludeFromAuctions(TDatabase *Database, int WorldID, int CharacterID, int Duration, int BanishmentID); -// NOTE(fusion): Banishment tables. +// NOTE(fusion): Banishment Tables bool IsCharacterNamelocked(TDatabase *Database, int CharacterID, bool *Result); bool GetNamelockStatus(TDatabase *Database, int CharacterID, TNamelockStatus *Status); bool InsertNamelock(TDatabase *Database, int CharacterID, int IPAddress, @@ -871,7 +889,7 @@ bool InsertStatements(TDatabase *Database, int WorldID, int NumStatements, TStat bool InsertReportedStatement(TDatabase *Database, int WorldID, TStatement *Statement, int BanishmentID, int ReporterID, const char *Reason, const char *Comment); -// NOTE(fusion): Info tables. +// NOTE(fusion): Info Tables bool GetKillStatistics(TDatabase *Database, int WorldID, DynamicArray *Stats); bool MergeKillStatistics(TDatabase *Database, int WorldID, int NumStats, TKillStatistics *Stats); bool GetOnlineCharacters(TDatabase *Database, int WorldID, DynamicArray *Characters); -- cgit v1.2.3