From 0ff6217227ce551d6c91524c8e7fd37352e20490 Mon Sep 17 00:00:00 2001 From: fusion32 Date: Sun, 19 Oct 2025 19:17:37 -0300 Subject: add include path for PostgreSQL + overall cleanup Some distributions place libpq headers under /usr/include/postgresql which is why it's added to the include path. And even though we don't support it yet, the same happens with MariaDB, with headers being placed under /usr/include/mariadb. --- Makefile | 8 ++--- config.cfg.dist | 16 +++++----- postgres/README.txt | 6 +++- sqlite/z-001-migrate-v01-to-v02.sql | 2 +- src/database_mariadb.cc | 10 +++++++ src/database_mysql.cc | 6 ---- src/database_postgres.cc | 37 +---------------------- src/querymanager.cc | 58 ++++++++++++++++++------------------- src/querymanager.hh | 12 ++++---- 9 files changed, 64 insertions(+), 91 deletions(-) create mode 100644 src/database_mariadb.cc delete mode 100644 src/database_mysql.cc diff --git a/Makefile b/Makefile index 972092f..ad0020c 100644 --- a/Makefile +++ b/Makefile @@ -26,11 +26,11 @@ ifeq ($(DATABASE), sqlite) CFLAGS += -DDATABASE_SQLITE=1 else ifeq ($(DATABASE), postgres) DATABASEOBJ = $(BUILDDIR)/database_postgres.obj - CFLAGS += -DDATABASE_POSTGRESQL=1 + CFLAGS += -DDATABASE_POSTGRESQL=1 -I/usr/include/postgresql LFLAGS += -lpq else ifeq ($(DATABASE), mariadb) - DATABASEOBJ = $(BUILDDIR)/database_mysql.obj - CFLAGS += -DDATABASE_MYSQL=1 -DDATABASE_MARIADB=1 + DATABASEOBJ = $(BUILDDIR)/database_mariadb.obj + CFLAGS += -DDATABASE_MARIADB=1 -I/usr/include/mariadb LFLAGS += -lmariadb else $(error Unsupported DATABASE: `$(DATABASE)`. Valid options are `sqlite`, `postgres`, or `mariadb`) @@ -68,7 +68,7 @@ $(BUILDDIR)/database_postgres.obj: $(SRCDIR)/database_postgres.cc $(SRCDIR)/quer @mkdir -p $(@D) $(CXX) -c $(CXXFLAGS) -o $@ $< -$(BUILDDIR)/database_mysql.obj: $(SRCDIR)/database_mysql.cc $(SRCDIR)/querymanager.hh +$(BUILDDIR)/database_mariadb.obj: $(SRCDIR)/database_mariadb.cc $(SRCDIR)/querymanager.hh @mkdir -p $(@D) $(CXX) -c $(CXXFLAGS) -o $@ $< diff --git a/config.cfg.dist b/config.cfg.dist index 26f7a21..3d5c685 100644 --- a/config.cfg.dist +++ b/config.cfg.dist @@ -22,14 +22,14 @@ PostgreSQL.SSLMode = "" PostgreSQL.SSLRootCert = "" PostgreSQL.MaxCachedStatements = 100 -# MySQL/MariaDB Config -MySQL.Host = "localhost" -MySQL.Port = "3306" -MySQL.DBName = "tibia" -MySQL.User = "tibia" -MySQL.Password = "" -MySQL.UnixSocket = "" -MySQL.MaxCachedStatements = 100 +# MariaDB Config +MariaDB.Host = "localhost" +MariaDB.Port = "3306" +MariaDB.DBName = "tibia" +MariaDB.User = "tibia" +MariaDB.Password = "" +MariaDB.UnixSocket = "" +MariaDB.MaxCachedStatements = 100 # Connection Config QueryManagerPort = 7173 diff --git a/postgres/README.txt b/postgres/README.txt index a96995b..8042c81 100644 --- a/postgres/README.txt +++ b/postgres/README.txt @@ -93,9 +93,13 @@ psql -U postgres tibia 2 - Set default privileges. Newly created databases may have some default PUBLIC privileges that we'll want to revoke to make sure the set of users that are able to connect is tighly controlled. Then, for users that are able to connect, we -want to give default access privileges to tables. +want to grant default access privileges on tables, while revoking the ability to +create or rename objects (tables, views, sequences, indexes). Note that a schema +in PostgreSQL is just a namespace for objects and new databases should have the +*public* schema created by default. ``` REVOKE ALL ON DATABASE tibia FROM PUBLIC; +REVOKE CREATE ON SCHEMA public FROM PUBLIC; ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO PUBLIC; diff --git a/sqlite/z-001-migrate-v01-to-v02.sql b/sqlite/z-001-migrate-v01-to-v02.sql index f471f53..19fdddb 100644 --- a/sqlite/z-001-migrate-v01-to-v02.sql +++ b/sqlite/z-001-migrate-v01-to-v02.sql @@ -2,7 +2,7 @@ -- must be manually executed as `sqlite3 -bail -echo tibia.db < migration.sql` -- because the original schema didn't have a `Patches` table which is necessary -- with the new automatic patching system. Future migration scripts can be placed --- in `patches/` for automatic execution but not this one unfortunately. +-- in `patches/` for automatic execution but not this one, unfortunately. -- These changes are already present in the latest `schema.sql`, so trying to -- apply it to a newly created database will result in errors. For more details -- see `sqlite/README.txt`. diff --git a/src/database_mariadb.cc b/src/database_mariadb.cc new file mode 100644 index 0000000..121f676 --- /dev/null +++ b/src/database_mariadb.cc @@ -0,0 +1,10 @@ +#if DATABASE_MARIADB +#include "querymanager.hh" + +// TODO(fusion): If we decide to implement MySQL, we should use MariaDB instead. +// It is a better alternative overall, and targeting a single database system +// should help us leverage its strongest features, which may not always be +// supported by both. +#error "MariaDB is not currently supported." + +#endif //DATABASE_MARIADB diff --git a/src/database_mysql.cc b/src/database_mysql.cc deleted file mode 100644 index 583bb7d..0000000 --- a/src/database_mysql.cc +++ /dev/null @@ -1,6 +0,0 @@ -#if DATABASE_MYSQL -#include "querymanager.hh" - -// TODO - -#endif //DATABASE_MYSQL diff --git a/src/database_postgres.cc b/src/database_postgres.cc index 3b19914..b53e780 100644 --- a/src/database_postgres.cc +++ b/src/database_postgres.cc @@ -476,7 +476,7 @@ static int GetResultByteA(PGresult *Result, int Row, int Col, uint8 *Buffer, int return Size; } -#if 1 +#if 0 // NOTE(fusion): DO NOT REMOVE. It is currently not being used so it's switched // off to avoid compiler warnings. static int GetResultIPAddress(PGresult *Result, int Row, int Col){ @@ -1271,41 +1271,6 @@ TDatabase *DatabaseOpen(void){ return NULL; } -#if 0 - { - // TODO(fusion): REMOVE. This is for testing query input/output, to make sure - // they're consitent across different formats (text/binary). - const char *Stmt = PrepareQuery(Database, - "SELECT NULL::BOOLEAN, NULL::INTEGER, NULL::TEXT, NULL::BYTEA," - " NULL::INET, NULL::TIMESTAMP, NULL::INTERVAL"); - ASSERT(Stmt != NULL); - - for(int i = 0; i <= 1; i += 1) - for(int j = 0; j <= 1; j += 1){ - LOG("TEST (%d, %d)", i, j); - ParamBuffer Params; - ParamBegin(&Params, 0, i); - PGresult *Result = PQexecPrepared(Database->Handle, Stmt, Params.NumParams, - Params.Values, Params.Lengths, Params.Formats, j); - AutoResultClear ResultGuard(Result); - if(PQresultStatus(Result) == PGRES_TUPLES_OK){ - LOG("0: %d", GetResultBool(Result, 0, 0)); - LOG("1: %d", GetResultInt(Result, 0, 1)); - LOG("2: %s", GetResultText(Result, 0, 2)); - LOG("3: %d", GetResultByteA(Result, 0, 3, NULL, 0)); - LOG("4: %d", GetResultIPAddress(Result, 0, 4)); - LOG("5: %d", GetResultTimestamp(Result, 0, 5)); - LOG("6: %d", GetResultInterval(Result, 0, 6)); - }else{ - LOG_ERR("Failed to execute query: %s", PQerrorMessage(Database->Handle)); - } - } - - DatabaseClose(Database); - return NULL; - } -#endif - return Database; } diff --git a/src/querymanager.cc b/src/querymanager.cc index bac3c74..42a4408 100644 --- a/src/querymanager.cc +++ b/src/querymanager.cc @@ -704,20 +704,20 @@ bool ReadConfig(const char *FileName, TConfig *Config){ ParseStringBuf(Config->PostgreSQL.SSLRootCert, Val); }else if(StringEqCI(Key, "PostgreSQL.MaxCachedStatements")){ ParseInteger(&Config->PostgreSQL.MaxCachedStatements, Val); - }else if(StringEqCI(Key, "MySQL.Host")){ - ParseStringBuf(Config->MySQL.Host, Val); - }else if(StringEqCI(Key, "MySQL.Port")){ - ParseStringBuf(Config->MySQL.Port, Val); - }else if(StringEqCI(Key, "MySQL.DBName")){ - ParseStringBuf(Config->MySQL.DBName, Val); - }else if(StringEqCI(Key, "MySQL.User")){ - ParseStringBuf(Config->MySQL.User, Val); - }else if(StringEqCI(Key, "MySQL.Password")){ - ParseStringBuf(Config->MySQL.Password, Val); - }else if(StringEqCI(Key, "MySQL.UnixSocket")){ - ParseStringBuf(Config->MySQL.UnixSocket, Val); - }else if(StringEqCI(Key, "MySQL.MaxCachedStatements")){ - ParseInteger(&Config->MySQL.MaxCachedStatements, Val); + }else if(StringEqCI(Key, "MariaDB.Host")){ + ParseStringBuf(Config->MariaDB.Host, Val); + }else if(StringEqCI(Key, "MariaDB.Port")){ + ParseStringBuf(Config->MariaDB.Port, Val); + }else if(StringEqCI(Key, "MariaDB.DBName")){ + ParseStringBuf(Config->MariaDB.DBName, Val); + }else if(StringEqCI(Key, "MariaDB.User")){ + ParseStringBuf(Config->MariaDB.User, Val); + }else if(StringEqCI(Key, "MariaDB.Password")){ + ParseStringBuf(Config->MariaDB.Password, Val); + }else if(StringEqCI(Key, "MariaDB.UnixSocket")){ + ParseStringBuf(Config->MariaDB.UnixSocket, Val); + }else if(StringEqCI(Key, "MariaDB.MaxCachedStatements")){ + ParseInteger(&Config->MariaDB.MaxCachedStatements, Val); }else if(StringEqCI(Key, "QueryManagerPort")){ ParseInteger(&Config->QueryManagerPort, Val); }else if(StringEqCI(Key, "QueryManagerPassword")){ @@ -791,14 +791,14 @@ int main(int argc, const char **argv){ StringBufCopy(g_Config.PostgreSQL.SSLRootCert, ""); g_Config.PostgreSQL.MaxCachedStatements = 100; - // MySQL/MariaDB Config - 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, ""); - g_Config.MySQL.MaxCachedStatements = 100; + // MariaDB Config + StringBufCopy(g_Config.MariaDB.Host, "localhost"); + StringBufCopy(g_Config.MariaDB.Port, "3306"); + StringBufCopy(g_Config.MariaDB.DBName, "tibia"); + StringBufCopy(g_Config.MariaDB.User, "tibia"); + StringBufCopy(g_Config.MariaDB.Password, ""); + StringBufCopy(g_Config.MariaDB.UnixSocket, ""); + g_Config.MariaDB.MaxCachedStatements = 100; // Connection Config g_Config.QueryManagerPort = 7174; @@ -830,13 +830,13 @@ int main(int argc, const char **argv){ 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 host: \"%s\"", g_Config.MySQL.Host); - 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 unix socket: \"%s\"", g_Config.MySQL.UnixSocket); - LOG("MySQL max cached statements: %d", g_Config.MySQL.MaxCachedStatements); +#elif DATABASE_MARIADB + LOG("MariaDB host: \"%s\"", g_Config.MariaDB.Host); + LOG("MariaDB port: \"%s\"", g_Config.MariaDB.Port); + LOG("MariaDB dbname: \"%s\"", g_Config.MariaDB.DBName); + LOG("MariaDB user: \"%s\"", g_Config.MariaDB.User); + LOG("MariaDB unix socket: \"%s\"", g_Config.MariaDB.UnixSocket); + LOG("MariaDB max cached statements: %d", g_Config.MariaDB.MaxCachedStatements); #endif LOG("Query manager port: %d", g_Config.QueryManagerPort); LOG("Query worker threads: %d", g_Config.QueryWorkerThreads); diff --git a/src/querymanager.hh b/src/querymanager.hh index dcf9271..b652e74 100644 --- a/src/querymanager.hh +++ b/src/querymanager.hh @@ -76,9 +76,9 @@ typedef size_t usize; TRAP(); \ }while(0) -#if (DATABASE_SQLITE + DATABASE_POSTGRESQL + DATABASE_MYSQL) == 0 +#if (DATABASE_SQLITE + DATABASE_POSTGRESQL + DATABASE_MARIADB) == 0 # error "No database system defined." -#elif (DATABASE_SQLITE + DATABASE_POSTGRESQL + DATABASE_MYSQL) > 1 +#elif (DATABASE_SQLITE + DATABASE_POSTGRESQL + DATABASE_MARIADB) > 1 # error "Multiple database systems defined." #endif @@ -86,8 +86,8 @@ typedef size_t usize; # define DATABASE_SYSTEM_NAME "SQLite" #elif DATABASE_POSTGRESQL # define DATABASE_SYSTEM_NAME "PostgreSQL" -#elif DATABASE_MYSQL -# define DATABASE_SYSTEM_NAME "MySQL" +#elif DATABASE_MARIADB +# define DATABASE_SYSTEM_NAME "MariaDB" #endif struct TConfig{ @@ -117,7 +117,7 @@ struct TConfig{ int MaxCachedStatements; } PostgreSQL; - // MySQL/MariaDB Config + // MariaDB Config struct{ char Host[100]; char Port[30]; @@ -126,7 +126,7 @@ struct TConfig{ char Password[30]; char UnixSocket[100]; int MaxCachedStatements; - } MySQL; + } MariaDB; // Connection Config int QueryManagerPort; -- cgit v1.2.3