From 601ce32ee8d0cee32b66c87b0bfefa7424a5b8c8 Mon Sep 17 00:00:00 2001 From: fusion32 Date: Sat, 18 Oct 2025 02:32:59 -0300 Subject: modify how automatic SQLite patches/upgrades works + overall cleanup --- src/database_postgres.cc | 2 +- src/database_sqlite.cc | 248 +++++++++++++++++++++++++++++++++-------------- src/querymanager.cc | 57 ++++++----- src/querymanager.hh | 11 ++- 4 files changed, 220 insertions(+), 98 deletions(-) (limited to 'src') diff --git a/src/database_postgres.cc b/src/database_postgres.cc index 241637a..37c1cc4 100644 --- a/src/database_postgres.cc +++ b/src/database_postgres.cc @@ -1093,7 +1093,7 @@ const char *PrepareQuery(TDatabase *Database, const char *Text){ Stmt->Text = strdup(Text); ASSERT(Stmt->Text != NULL); -#if 1 // DEBUG_STATEMENT_CACHE +#if DEBUG_STATEMENT_CACHE { char Preview[30]; StringBufCopyEllipsis(Preview, Text); diff --git a/src/database_sqlite.cc b/src/database_sqlite.cc index c2afa83..a2c4e4e 100644 --- a/src/database_sqlite.cc +++ b/src/database_sqlite.cc @@ -2,6 +2,17 @@ #include "querymanager.hh" #include "sqlite3.h" +#include +#include + +// NOTE(fusion): SQLite's application id, used to identify an existing database. +// It is currently being set to ASCII "TiDB" for "Tibia Database". +#define SQLITE_APPLICATION_ID 0x54694442 + +// NOTE(fusion): SQLite's user version, used to track the current schema version. +// It is hardcoded because schema changes will usually result in query changes. +#define SQLITE_USER_VERSION 1 + struct TCachedStatement{ sqlite3_stmt *Stmt; int LastUsed; @@ -14,10 +25,6 @@ struct TDatabase{ TCachedStatement *CachedStatements; }; -// NOTE(fusion): SQLite's application id. We're currently setting it to ASCII -// "TiDB" for "Tibia Database". -constexpr int g_ApplicationID = 0x54694442; - // Statement Cache //============================================================================== // IMPORTANT(fusion): Prepared statements that are not reset after use may keep @@ -115,13 +122,23 @@ static sqlite3_stmt *PrepareQuery(TDatabase *Database, const char *Text){ Entry->Stmt = Stmt; Entry->LastUsed = GetMonotonicUptime(); Entry->Hash = Hash; + +#if DEBUG_STATEMENT_CACHE + { + char Preview[30]; + StringBufCopyEllipsis(Preview, Text); + LOG("New statement cached: \"%s\"", Preview); + } +#endif }else{ if(sqlite3_stmt_busy(Stmt) != 0){ - LOG_WARN("Statement \"%.30s%s\" wasn't properly reset. Use the" + char Preview[30]; + StringBufCopyEllipsis(Preview, Text); + LOG_WARN("Statement \"%s\" wasn't properly reset. Use the" " `AutoStmtReset` wrapper or manually reset it after usage" " to avoid it holding onto an older view of the database," " making changes from other processes not visible.", - Text, (strlen(Text) > 30 ? "..." : "")); + Preview); sqlite3_reset(Stmt); } @@ -147,15 +164,6 @@ static sqlite3_stmt *PrepareQuery(TDatabase *Database, const char *Text){ // hand with snprintf or other similar formatting functions. In particular, this // rule apply for `application_id` and `user_version` which we modify. -static bool FileExists(const char *FileName){ - FILE *File = fopen(FileName, "rb"); - bool Result = (File != NULL); - if(Result){ - fclose(File); - } - return Result; -} - static bool ExecFile(TDatabase *Database, const char *FileName){ FILE *File = fopen(FileName, "rb"); if(File == NULL){ @@ -211,25 +219,46 @@ static bool ExecInternal(TDatabase *Database, const char *Format, ...){ return true; } -static bool GetPragmaInt(TDatabase *Database, const char *Name, int *OutValue){ +static bool QueryInternal(TDatabase *Database, int *OutValue, const char *Format, ...) ATTR_PRINTF(3, 4); +static bool QueryInternal(TDatabase *Database, int *OutValue, const char *Format, ...){ + va_list ap; + va_start(ap, Format); char Text[1024]; - snprintf(Text, sizeof(Text), "PRAGMA %s", Name); + int Written = vsnprintf(Text, sizeof(Text), Format, ap); + va_end(ap); + + if(Written >= (int)sizeof(Text)){ + LOG_ERR("Query is too long"); + return false; + } sqlite3_stmt *Stmt; if(sqlite3_prepare_v2(Database->Handle, Text, -1, &Stmt, NULL) != SQLITE_OK){ - LOG_ERR("Failed to retrieve %s (PREP): %s", Name, sqlite3_errmsg(Database->Handle)); + LOG_ERR("Failed to prepare query \"%s\": %s", + Text, sqlite3_errmsg(Database->Handle)); return false; } - bool Result = (sqlite3_step(Stmt) == SQLITE_ROW); - if(!Result){ - LOG_ERR("Failed to retrieve %s (STEP): %s", Name, sqlite3_errmsg(Database->Handle)); - }else if(OutValue){ + int ErrorCode = sqlite3_step(Stmt); + if(ErrorCode != SQLITE_ROW && ErrorCode != SQLITE_DONE){ + LOG_ERR("Failed to execute query \"%s\": %s", + Text, sqlite3_errmsg(Database->Handle)); + sqlite3_finalize(Stmt); + return false; + } + + if(OutValue != NULL){ + if(ErrorCode == SQLITE_DONE || sqlite3_data_count(Stmt) == 0){ + LOG_ERR("Query \"%s\" returned no data", Text); + sqlite3_finalize(Stmt); + return false; + } + *OutValue = sqlite3_column_int(Stmt, 0); } sqlite3_finalize(Stmt); - return Result; + return true; } static bool InitDatabaseSchema(TDatabase *Database){ @@ -238,95 +267,170 @@ static bool InitDatabaseSchema(TDatabase *Database){ return false; } + // IMPORTANT(fusion): The schema init script should set`application_id` and + // `user_version` appropriately. if(!ExecFile(Database, "sqlite/schema.sql")){ LOG_ERR("Failed to execute \"sqlite/schema.sql\""); return false; } - if(!ExecInternal(Database, "PRAGMA application_id = %d", g_ApplicationID)){ - LOG_ERR("Failed to set application id"); + return Tx.Commit(); +} + +static bool GetPatchTimestamp(TDatabase *Database, const char *FileName, int *Timestamp){ + sqlite3_stmt *Stmt; + const char *Text = "SELECT Timestamp FROM Patches WHERE FileName = ?1"; + if(sqlite3_prepare_v2(Database->Handle, Text, -1, &Stmt, NULL) != SQLITE_OK + || sqlite3_bind_text(Stmt, 1, FileName, -1, NULL) != SQLITE_OK){ + LOG_ERR("Failed to prepare query: %s", sqlite3_errmsg(Database->Handle)); + sqlite3_finalize(Stmt); return false; } - if(!ExecInternal(Database, "PRAGMA user_version = 1")){ - LOG_ERR("Failed to set user version"); + int ErrorCode = sqlite3_step(Stmt); + if(ErrorCode != SQLITE_ROW && ErrorCode != SQLITE_DONE){ + LOG_ERR("Failed to execute query: %s", sqlite3_errmsg(Database->Handle)); + sqlite3_finalize(Stmt); return false; } - return Tx.Commit(); + *Timestamp = (ErrorCode == SQLITE_ROW ? sqlite3_column_int(Stmt, 0) : 0); + sqlite3_finalize(Stmt); + return true; } -static bool UpgradeDatabaseSchema(TDatabase *Database, int UserVersion){ - char FileName[256]; - int NewVersion = UserVersion; - while(true){ - snprintf(FileName, sizeof(FileName), "sqlite/upgrade-%d.sql", NewVersion); - if(FileExists(FileName)){ - NewVersion += 1; - }else{ - break; - } +static bool InsertPatch(TDatabase *Database, const char *FileName){ + sqlite3_stmt *Stmt = NULL; + const char *Text = "INSERT INTO Patches (FileName, Timestamp) VALUES (?1, UNIXEPOCH())"; + if(sqlite3_prepare_v2(Database->Handle, Text, -1, &Stmt, NULL) != SQLITE_OK + || sqlite3_bind_text(Stmt, 1, FileName, -1, NULL) != SQLITE_OK){ + LOG_ERR("Failed to prepare query: %s", sqlite3_errmsg(Database->Handle)); + sqlite3_finalize(Stmt); + return false; } - if(UserVersion != NewVersion){ - LOG("Upgrading database schema to version %d", NewVersion); + if(sqlite3_step(Stmt) != SQLITE_DONE){ + LOG_ERR("Failed to execute query: %s", sqlite3_errmsg(Database->Handle)); + sqlite3_finalize(Stmt); + return false; + } - TransactionScope Tx("SchemaUpgrade"); - if(!Tx.Begin(Database)){ - return false; + sqlite3_finalize(Stmt); + return true; +} + +static bool ApplyDatabasePatches(TDatabase *Database, const char *DirName){ + LOG("Looking for patches at \"%s\"...", DirName); + + TransactionScope Tx("SchemaPatches"); + if(!Tx.Begin(Database)){ + return false; + } + + DIR *PatchDir = opendir(DirName); + if(PatchDir == NULL && errno == ENOENT){ + LOG("Directory \"%s\" not found, skipping...", DirName); + return true; + } + + if(PatchDir == NULL){ + LOG_ERR("Failed to open directory \"%s\": (%d) %s", + DirName, errno, strerrordesc_np(errno)); + return false; + } + + bool Abort = false; + DynamicArray PatchList; + while(struct dirent *DirEntry = readdir(PatchDir)){ + if(DirEntry->d_type != DT_REG || !StringEndsWithCI(DirEntry->d_name, ".sql")){ + continue; } - while(UserVersion < NewVersion){ - snprintf(FileName, sizeof(FileName), "upgrade-%d.sql", UserVersion); - if(!ExecFile(Database, FileName)){ - LOG_ERR("Failed to execute \"%s\"", FileName); - return false; - } - UserVersion += 1; + int PatchTimestamp; + if(!GetPatchTimestamp(Database, DirEntry->d_name, &PatchTimestamp)){ + Abort = true; + break; } - if(!ExecInternal(Database, "PRAGMA user_version = %d", UserVersion)){ - LOG_ERR("Failed to set user version"); - return false; + if(PatchTimestamp > 0){ + char DateString[256]; + StringBufFormatTime(DateString, "%Y-%m-%d", PatchTimestamp); + LOG("\"%s\": patch already applied on %s", DirEntry->d_name, DateString); + }else{ + PatchList.Push(*DirEntry); } + } - if(!Tx.Commit()){ - return false; + if(!Abort && !PatchList.Empty()){ + std::sort(PatchList.begin(), PatchList.end(), + [](const struct dirent &A, const struct dirent &B) -> bool { + return strcmp(A.d_name, B.d_name) < 0; + }); + + for(const struct dirent &DirEntry: PatchList){ + LOG("\"%s\": applying patch...", DirEntry.d_name); + + char FilePath[4096]; + StringBufFormat(FilePath, "%s/%s", DirName, DirEntry.d_name); + if(!ExecFile(Database, FilePath) || !InsertPatch(Database, DirEntry.d_name)){ + Abort = true; + break; + } } } - return true; + closedir(PatchDir); + return !Abort && Tx.Commit(); } static bool CheckDatabaseSchema(TDatabase *Database){ - int ApplicationID, UserVersion; - if(!GetPragmaInt(Database, "application_id", &ApplicationID) - || !GetPragmaInt(Database, "user_version", &UserVersion)){ + int ApplicationID, NumObjects; + if(!QueryInternal(Database, &ApplicationID, "PRAGMA application_id") + || !QueryInternal(Database, &NumObjects, "SELECT COUNT(*) FROM sqlite_master")){ return false; } - if(ApplicationID != g_ApplicationID){ - if(ApplicationID != 0){ - LOG_ERR("Database has unknown application id %08X (expected %08X)", - ApplicationID, g_ApplicationID); - return false; - }else if(UserVersion != 0){ - LOG_ERR("Database has non zero user version %d", UserVersion); - return false; - }else if(!InitDatabaseSchema(Database)){ + // IMPORTANT(fusion): Only initialize the schema if the database has no + // application id and has no objects defined (tables, indexes, views, or + // triggers). + if(ApplicationID == 0 && NumObjects == 0){ + if(!InitDatabaseSchema(Database)){ LOG_ERR("Failed to initialize database schema"); return false; } - UserVersion = 1; + // NOTE(fusion): Refresh database information after initalizing schema. + if(!QueryInternal(Database, &ApplicationID, "PRAGMA application_id") + || !QueryInternal(Database, &NumObjects, "SELECT COUNT(*) FROM sqlite_master")){ + return false; + } + } + + if(ApplicationID != SQLITE_APPLICATION_ID){ + LOG_ERR("Application ID mismatch (expected %08X, got %08X)", + SQLITE_APPLICATION_ID, ApplicationID); + return false; + } + + // IMPORTANT(fusion): After checking the application id, we want to apply + // patches before checking user version, just in case some migration needs + // to take place. + if(!ApplyDatabasePatches(Database, "sqlite/patches")){ + LOG_ERR("Failed to apply database patches"); + return false; + } + + int UserVersion; + if(!QueryInternal(Database, &UserVersion, "PRAGMA user_version")){ + return false; } - if(!UpgradeDatabaseSchema(Database, UserVersion)){ - LOG_ERR("Failed to upgrade database schema"); + if(UserVersion != SQLITE_USER_VERSION){ + LOG_ERR("User Version mismatch (expected %d, got %d)", + SQLITE_USER_VERSION, UserVersion); return false; } - LOG("Database version: %d", UserVersion); return true; } diff --git a/src/querymanager.cc b/src/querymanager.cc index 86c2e47..983e65f 100644 --- a/src/querymanager.cc +++ b/src/querymanager.cc @@ -28,11 +28,9 @@ void LogAdd(const char *Prefix, const char *Format, ...){ } 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, - LocalTime.tm_hour, LocalTime.tm_min, LocalTime.tm_sec, - Prefix, Entry); + char TimeString[128]; + StringBufFormatTime(TimeString, "%Y-%m-%d %H:%M:%S", (int)time(NULL)); + fprintf(stdout, "%s [%s] %s\n", TimeString, Prefix, Entry); fflush(stdout); } } @@ -55,11 +53,9 @@ void LogAddVerbose(const char *Prefix, const char *Function, if(Length > 0){ (void)File; (void)Line; - struct tm LocalTime = GetLocalTime(time(NULL)); - fprintf(stdout, "%04d/%02d/%02d %02d:%02d:%02d [%s] %s: %s\n", - LocalTime.tm_year + 1900, LocalTime.tm_mon + 1, LocalTime.tm_mday, - LocalTime.tm_hour, LocalTime.tm_min, LocalTime.tm_sec, - Prefix, Function, Entry); + char TimeString[128]; + StringBufFormatTime(TimeString, "%Y-%m-%d %H:%M:%S", (int)time(NULL)); + fprintf(stdout, "%s [%s] %s: %s\n", TimeString, Prefix, Function, Entry); fflush(stdout); } } @@ -152,26 +148,18 @@ bool StringEmpty(const char *String){ bool StringEq(const char *A, const char *B){ int Index = 0; - while(true){ - if(A[Index] != B[Index]){ - return false; - }else if(A[Index] == 0){ - return true; - } + while(A[Index] != 0 && A[Index] == B[Index]){ Index += 1; } + return A[Index] == B[Index]; } bool StringEqCI(const char *A, const char *B){ int Index = 0; - while(true){ - if(tolower(A[Index]) != tolower(B[Index])){ - return false; - }else if(A[Index] == 0){ - return true; - } + while(A[Index] != 0 && tolower(A[Index]) == tolower(B[Index])){ Index += 1; } + return tolower(A[Index]) == tolower(B[Index]); } bool StringStartsWith(const char *String, const char *Prefix){ @@ -196,6 +184,16 @@ bool StringStartsWithCI(const char *String, const char *Prefix){ return true; } +bool StringEndsWith(const char *String, const char *Suffix){ + int SuffixOffset = ((int)strlen(String) - (int)strlen(Suffix)); + return SuffixOffset >= 0 && StringEq(String + SuffixOffset, Suffix); +} + +bool StringEndsWithCI(const char *String, const char *Suffix){ + int SuffixOffset = ((int)strlen(String) - (int)strlen(Suffix)); + return SuffixOffset >= 0 && StringEqCI(String + SuffixOffset, Suffix); +} + bool StringCopyN(char *Dest, int DestCapacity, const char *Src, int SrcLength){ ASSERT(DestCapacity > 0); bool Result = (SrcLength < DestCapacity); @@ -241,6 +239,21 @@ bool StringFormat(char *Dest, int DestCapacity, const char *Format, ...){ return Written >= 0 && Written < DestCapacity; } +bool StringFormatTime(char *Dest, int DestCapacity, const char *Format, int Timestamp){ + struct tm tm = GetLocalTime((int)Timestamp); + int Result = (int)strftime(Dest, DestCapacity, Format, &tm); + + // NOTE(fusion): `strftime` will should return ZERO if it's unable to fit + // the result in the supplied buffer, which is annoying because ZERO may + // not represent a failure if the result is an empty string. + ASSERT(Result >= 0 && Result < DestCapacity); + if(Result == 0){ + memset(Dest, 0, DestCapacity); + } + + return Result != 0; +} + int UTF8SequenceSize(uint8 LeadingByte){ if((LeadingByte & 0x80) == 0){ return 1; diff --git a/src/querymanager.hh b/src/querymanager.hh index 99cea94..dcf9271 100644 --- a/src/querymanager.hh +++ b/src/querymanager.hh @@ -158,10 +158,13 @@ bool StringEq(const char *A, const char *B); bool StringEqCI(const char *A, const char *B); bool StringStartsWith(const char *String, const char *Prefix); bool StringStartsWithCI(const char *String, const char *Prefix); +bool StringEndsWith(const char *String, const char *Suffix); +bool StringEndsWithCI(const char *String, const char *Suffix); 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); +bool StringFormatTime(char *Dest, int DestCapacity, const char *Format, int Timestamp); int UTF8SequenceSize(uint8 LeadingByte); bool UTF8IsTrailingByte(uint8 Byte); int UTF8EncodedSize(int Codepoint); @@ -183,12 +186,14 @@ 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 ParseStringBuf(Dest, String) ParseString(Dest, sizeof(Dest), String) +#define StringBufCopyEllipsis(Dest, Src) StringCopyEllipsis(Dest, sizeof(Dest), Src); +#define StringBufFormat(Dest, ...) StringFormat(Dest, sizeof(Dest), __VA_ARGS__) +#define StringBufFormatTime(Dest, Format, Timestamp) \ + StringFormatTime(Dest, sizeof(Dest), Format, Timestamp); #define ParseHexStringBuf(Dest, String) ParseHexString(Dest, sizeof(Dest), String); +#define ParseStringBuf(Dest, String) ParseString(Dest, sizeof(Dest), String) // AtomicInt //============================================================================== -- cgit v1.2.3