diff options
| -rw-r--r-- | postgres/schema.sql | 24 | ||||
| -rw-r--r-- | sqlite/README.txt | 21 | ||||
| -rw-r--r-- | sqlite/patches/9999-initial-data.sql (renamed from sqlite/init.sql) | 12 | ||||
| -rw-r--r-- | sqlite/schema.sql | 122 | ||||
| -rw-r--r-- | sqlite/z-001-migrate-v01-to-v02.sql | 14 | ||||
| -rw-r--r-- | src/database_postgres.cc | 2 | ||||
| -rw-r--r-- | src/database_sqlite.cc | 248 | ||||
| -rw-r--r-- | src/querymanager.cc | 57 | ||||
| -rw-r--r-- | src/querymanager.hh | 11 |
9 files changed, 322 insertions, 189 deletions
diff --git a/postgres/schema.sql b/postgres/schema.sql index 32afe2c..efe9133 100644 --- a/postgres/schema.sql +++ b/postgres/schema.sql @@ -29,6 +29,17 @@ CREATE COLLATION NOCASE ( -- using them here. It might be a good idea for consistency, but it's not a hard -- requirement. +-- NOTE(fusion): A table with schema information. It currently only contains the +-- schema version which is checked at startup against `POSTGRESQL_SCHEMA_VERSION`, +-- defined in `database_postgres.cc`. The query manager will only startup if the +-- versions match. +CREATE TABLE SchemaInfo ( + Key TEXT NOT NULL COLLATE NOCASE, + Value TEXT NOT NULL, + PRIMARY KEY (Key) +); +INSERT INTO SchemaInfo (Key, Value) VALUES ('VERSION', '1'); + -- Primary Tables --============================================================================== CREATE TABLE Worlds ( @@ -280,17 +291,4 @@ CREATE TABLE OnlineCharacters ( 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/README.txt b/sqlite/README.txt index 26f782a..8ef89f5 100644 --- a/sqlite/README.txt +++ b/sqlite/README.txt @@ -1,10 +1,11 @@ - The query manager will properly initialize and upgrade the database schema -based on files in this folder. The initial schema should be in `schema.sql` and -upgrades should be in `upgrade-N.sql` where N is a number starting from 1. The -`upgrade-N.sql` file should take the database from version N to N + 1. - The only restriction to these files is that they can't have transaction -statements ("BEGIN", "ROLLBACK", "COMMIT") because the query manager will -already bundle them into a transaction that also sets `user_version`, and -and nested transactions aren't allowed. - The current database version can be retrieved with the "PRAGMA user_version" -query in the SQLite shell.
\ No newline at end of file + The query manager will initialize and patch the database schema, at startup, +based on the files in this folder. The initial schema is inside `schema.sql` and +shouldn't be modified, to make sure the database can be initialized if everything +else fails. Patches and modifications should be placed in `patches/` with no +particular name restrictions except for having an `.sql` extension. These patch +files will be executed exactly ONCE. If multiple patches are pending at startup, +they're executed in alphabetical order. + As for statement restrictions, the only thing prohibited is the presence of +transaction statements "BEGIN", "ROLLBACK", and "COMMIT". This is because all +patches will be bundled into the same transaction, to ensure atomicity. + diff --git a/sqlite/init.sql b/sqlite/patches/9999-initial-data.sql index 9d561fc..53d92f2 100644 --- a/sqlite/init.sql +++ b/sqlite/patches/9999-initial-data.sql @@ -1,12 +1,7 @@ --- NOTE(fusion): The query manager WON'T automatically run this but the game --- server still requires at least the world config to be able to boot up. It --- is probably a good idea to keep this separated from `schema.sql` and then --- running it with `sqlite3 -echo tibia.db < sqlite/init.sql`, although it is --- not mandatory. --- Because this isn't automatically managed, all queries are wrapped in a --- transaction to avoid partial writes in case of errors. +-- NOTE(fusion): This file contains sample initial data and will be executed +-- automatically as a patch by the query manager. See `sqlite/README.txt` for +-- more details. --============================================================================== -BEGIN; INSERT INTO Worlds (WorldID, Name, Type, RebootTime, Host, Port, MaxPlayers, PremiumPlayerBuffer, MaxNewbies, PremiumNewbieBuffer) @@ -100,4 +95,3 @@ INSERT INTO CharacterRights (CharacterID, Name) (1, 'CLEANUP_FIELDS'), (1, 'NO_STATISTICS'); -COMMIT; diff --git a/sqlite/schema.sql b/sqlite/schema.sql index ead3908..0ab3a9f 100644 --- a/sqlite/schema.sql +++ b/sqlite/schema.sql @@ -1,6 +1,27 @@ +-- Database ApplicationID and UserVersion +--============================================================================== +-- NOTE(fusion): SQLite's application id, used to identify an existing database. +-- The query manager will only access an existing database whose application id +-- is exactly 0x54694442 which is the ASCII for "TiDB" or "Tibia Database". +PRAGMA application_id = 0x54694442; + +-- NOTE(fusion): SQLite's user version, used to track the current schema version. +-- The query manager will only access an existing database whose user version is +-- exactly `SQLITE_USER_VERSION`, defined in `database_sqlite.cc`. +PRAGMA user_version = 1; + +-- NOTE(fusion): A table with the history of applied patches. It will be inspected +-- at startup to decide which patches still need to be applied. See `sqlite/README.txt` +-- for more details. +CREATE TABLE Patches ( + FileName TEXT NOT NULL COLLATE NOCASE, + Timestamp INTEGER NOT NULL, + UNIQUE (FileName) +); + -- Primary Tables --============================================================================== -CREATE TABLE IF NOT EXISTS Worlds ( +CREATE TABLE Worlds ( WorldID INTEGER NOT NULL, Name TEXT NOT NULL COLLATE NOCASE, Type INTEGER NOT NULL, @@ -19,7 +40,7 @@ CREATE TABLE IF NOT EXISTS Worlds ( UNIQUE (Name) ); -CREATE TABLE IF NOT EXISTS Accounts ( +CREATE TABLE Accounts ( AccountID INTEGER NOT NULL, Email TEXT NOT NULL COLLATE NOCASE, Auth BLOB NOT NULL, @@ -30,7 +51,7 @@ CREATE TABLE IF NOT EXISTS Accounts ( UNIQUE (Email) ); -CREATE TABLE IF NOT EXISTS Characters ( +CREATE TABLE Characters ( WorldID INTEGER NOT NULL, CharacterID INTEGER NOT NULL, AccountID INTEGER NOT NULL, @@ -49,30 +70,27 @@ CREATE TABLE IF NOT EXISTS Characters ( 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, Rank); +CREATE INDEX CharactersWorldIndex ON Characters(WorldID, IsOnline); +CREATE INDEX CharactersAccountIndex ON Characters(AccountID, IsOnline); +CREATE INDEX CharactersGuildIndex ON Characters(Guild, Rank); /* -- TODO(fusion): Have group rights instead of adding individual rights to characters? ALTER TABLE Characters ADD GroupID INTEGER NOT NULL; -CREATE TABLE IF NOT EXISTS CharacterRights ( +CREATE TABLE CharacterRights ( GroupID INTEGER NOT NULL, Name TEXT NOT NULL COLLATE NOCASE, PRIMARY KEY(GroupID, Name) ); */ -CREATE TABLE IF NOT EXISTS CharacterRights ( +CREATE TABLE CharacterRights ( CharacterID INTEGER NOT NULL, Name TEXT NOT NULL COLLATE NOCASE, PRIMARY KEY(CharacterID, Name) ); -CREATE TABLE IF NOT EXISTS CharacterDeaths ( +CREATE TABLE CharacterDeaths ( CharacterID INTEGER NOT NULL, Level INTEGER NOT NULL, OffenderID INTEGER NOT NULL, @@ -80,38 +98,34 @@ CREATE TABLE IF NOT EXISTS CharacterDeaths ( Unjustified INTEGER NOT NULL, Timestamp INTEGER NOT NULL ); -CREATE INDEX IF NOT EXISTS CharacterDeathsCharacterIndex - ON CharacterDeaths(CharacterID, Level); -CREATE INDEX IF NOT EXISTS CharacterDeathsOffenderIndex - ON CharacterDeaths(OffenderID, Unjustified); +CREATE INDEX CharacterDeathsCharacterIndex ON CharacterDeaths(CharacterID, Level); +CREATE INDEX CharacterDeathsOffenderIndex ON CharacterDeaths(OffenderID, Unjustified); -CREATE TABLE IF NOT EXISTS Buddies ( +CREATE TABLE Buddies ( WorldID INTEGER NOT NULL, AccountID INTEGER NOT NULL, BuddyID INTEGER NOT NULL, PRIMARY KEY (WorldID, AccountID, BuddyID) ); -CREATE TABLE IF NOT EXISTS WorldInvitations ( +CREATE TABLE WorldInvitations ( WorldID INTEGER NOT NULL, CharacterID INTEGER NOT NULL, PRIMARY KEY (WorldID, CharacterID) ); -CREATE TABLE IF NOT EXISTS LoginAttempts ( +CREATE TABLE LoginAttempts ( AccountID INTEGER NOT NULL, IPAddress INTEGER NOT NULL, Timestamp INTEGER NOT NULL, Failed INTEGER NOT NULL ); -CREATE INDEX IF NOT EXISTS LoginAttemptsAccountIndex - ON LoginAttempts(AccountID, Timestamp); -CREATE INDEX IF NOT EXISTS LoginAttemptsAddressIndex - ON LoginAttempts(IPAddress, Timestamp); +CREATE INDEX LoginAttemptsAccountIndex ON LoginAttempts(AccountID, Timestamp); +CREATE INDEX LoginAttemptsAddressIndex ON LoginAttempts(IPAddress, Timestamp); -- House Tables --============================================================================== -CREATE TABLE IF NOT EXISTS Houses ( +CREATE TABLE Houses ( WorldID INTEGER NOT NULL, HouseID INTEGER NOT NULL, Name TEXT NOT NULL, @@ -126,7 +140,7 @@ CREATE TABLE IF NOT EXISTS Houses ( PRIMARY KEY (WorldID, HouseID) ); -CREATE TABLE IF NOT EXISTS HouseOwners ( +CREATE TABLE HouseOwners ( WorldID INTEGER NOT NULL, HouseID INTEGER NOT NULL, OwnerID INTEGER NOT NULL, @@ -137,7 +151,7 @@ CREATE TABLE IF NOT EXISTS HouseOwners ( -- 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 ( +CREATE TABLE HouseAuctions ( WorldID INTEGER NOT NULL, HouseID INTEGER NOT NULL, BidderID INTEGER DEFAULT NULL, @@ -146,7 +160,7 @@ CREATE TABLE IF NOT EXISTS HouseAuctions ( PRIMARY KEY (WorldID, HouseID) ); -CREATE TABLE IF NOT EXISTS HouseTransfers ( +CREATE TABLE HouseTransfers ( WorldID INTEGER NOT NULL, HouseID INTEGER NOT NULL, NewOwnerID INTEGER NOT NULL, @@ -154,32 +168,28 @@ CREATE TABLE IF NOT EXISTS HouseTransfers ( PRIMARY KEY (WorldID, HouseID) ); -CREATE TABLE IF NOT EXISTS HouseAuctionExclusions ( +CREATE TABLE HouseAuctionExclusions ( CharacterID INTEGER NOT NULL, Issued INTEGER NOT NULL, Until INTEGER NOT NULL, BanishmentID INTEGER NOT NULL ); -CREATE INDEX IF NOT EXISTS HouseAuctionExclusionsIndex - ON HouseAuctionExclusions(CharacterID, Until); +CREATE INDEX HouseAuctionExclusionsIndex ON HouseAuctionExclusions(CharacterID, Until); -CREATE TABLE IF NOT EXISTS HouseAssignments ( +CREATE TABLE HouseAssignments ( WorldID INTEGER NOT NULL, HouseID INTEGER NOT NULL, OwnerID INTEGER NOT NULL, Price INTEGER NOT NULL, Timestamp INTEGER NOT NULL ); -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); +CREATE INDEX HouseAssignmentsHouseIndex ON HouseAssignments(WorldID, HouseID); +CREATE INDEX HouseAssignmentsTimeIndex ON HouseAssignments(WorldID, Timestamp); +CREATE INDEX HouseAssignmentsOwnerIndex ON HouseAssignments(OwnerID); -- Banishment Tables --============================================================================== -CREATE TABLE IF NOT EXISTS Banishments ( +CREATE TABLE Banishments ( BanishmentID INTEGER NOT NULL, AccountID INTEGER NOT NULL, IPAddress INTEGER NOT NULL, @@ -191,10 +201,9 @@ CREATE TABLE IF NOT EXISTS Banishments ( Until INTEGER NOT NULL, PRIMARY KEY (BanishmentID) ); -CREATE INDEX IF NOT EXISTS BanishmentsAccountIndex - ON Banishments(AccountID, Until, FinalWarning); +CREATE INDEX BanishmentsAccountIndex ON Banishments(AccountID, Until, FinalWarning); -CREATE TABLE IF NOT EXISTS IPBanishments ( +CREATE TABLE IPBanishments ( CharacterID INTEGER NOT NULL, IPAddress INTEGER NOT NULL, GamemasterID INTEGER NOT NULL, @@ -203,12 +212,10 @@ CREATE TABLE IF NOT EXISTS IPBanishments ( Issued INTEGER NOT NULL, Until INTEGER NOT NULL ); -CREATE INDEX IF NOT EXISTS IPBanishmentsAddressIndex - ON IPBanishments(IPAddress); -CREATE INDEX IF NOT EXISTS IPBanishmentsCharacterIndex - ON IPBanishments(CharacterID); +CREATE INDEX IPBanishmentsAddressIndex ON IPBanishments(IPAddress); +CREATE INDEX IPBanishmentsCharacterIndex ON IPBanishments(CharacterID); -CREATE TABLE IF NOT EXISTS Namelocks ( +CREATE TABLE Namelocks ( CharacterID INTEGER NOT NULL, IPAddress INTEGER NOT NULL, GamemasterID INTEGER NOT NULL, @@ -219,17 +226,16 @@ CREATE TABLE IF NOT EXISTS Namelocks ( PRIMARY KEY (CharacterID) ); -CREATE TABLE IF NOT EXISTS Notations ( +CREATE TABLE Notations ( CharacterID INTEGER NOT NULL, IPAddress INTEGER NOT NULL, GamemasterID INTEGER NOT NULL, Reason TEXT NOT NULL, Comment TEXT NOT NULL ); -CREATE INDEX IF NOT EXISTS NotationsCharacterIndex - ON Notations(CharacterID); +CREATE INDEX NotationsCharacterIndex ON Notations(CharacterID); -CREATE TABLE IF NOT EXISTS Statements ( +CREATE TABLE Statements ( WorldID INTEGER NOT NULL, Timestamp INTEGER NOT NULL, StatementID INTEGER NOT NULL, @@ -238,10 +244,9 @@ CREATE TABLE IF NOT EXISTS Statements ( Text TEXT NOT NULL, PRIMARY KEY (WorldID, Timestamp, StatementID) ); -CREATE INDEX IF NOT EXISTS StatementsCharacterIndex - ON Statements(CharacterID, Timestamp); +CREATE INDEX StatementsCharacterIndex ON Statements(CharacterID, Timestamp); -CREATE TABLE IF NOT EXISTS ReportedStatements ( +CREATE TABLE ReportedStatements ( WorldID INTEGER NOT NULL, Timestamp INTEGER NOT NULL, StatementID INTEGER NOT NULL, @@ -252,14 +257,12 @@ CREATE TABLE IF NOT EXISTS ReportedStatements ( Comment TEXT NOT NULL, PRIMARY KEY (WorldID, Timestamp, StatementID) ); -CREATE INDEX IF NOT EXISTS ReportedStatementsCharacterIndex - ON ReportedStatements(CharacterID, Timestamp); -CREATE INDEX IF NOT EXISTS ReportedStatementsBanishmentIndex - ON ReportedStatements(BanishmentID); +CREATE INDEX ReportedStatementsCharacterIndex ON ReportedStatements(CharacterID, Timestamp); +CREATE INDEX ReportedStatementsBanishmentIndex ON ReportedStatements(BanishmentID); -- Info Tables --============================================================================== -CREATE TABLE IF NOT EXISTS KillStatistics ( +CREATE TABLE KillStatistics ( WorldID INTEGER NOT NULL, RaceName TEXT NOT NULL COLLATE NOCASE, TimesKilled INTEGER NOT NULL, @@ -267,10 +270,11 @@ CREATE TABLE IF NOT EXISTS KillStatistics ( PRIMARY KEY (WorldID, RaceName) ); -CREATE TABLE IF NOT EXISTS OnlineCharacters ( +CREATE TABLE OnlineCharacters ( WorldID INTEGER NOT NULL, Name TEXT NOT NULL COLLATE NOCASE, Level INTEGER NOT NULL, Profession TEXT NOT NULL, PRIMARY KEY (WorldID, Name) ); + diff --git a/sqlite/z-001-migrate-v01-to-v02.sql b/sqlite/z-001-migrate-v01-to-v02.sql new file mode 100644 index 0000000..b7df053 --- /dev/null +++ b/sqlite/z-001-migrate-v01-to-v02.sql @@ -0,0 +1,14 @@ +-- NOTE(fusion): This file contains the migration script from v0.1 to v0.2. It +-- can be placed into `patches/` to upgrade an existing database. Note that these +-- changes are already present in the latest `schema.sql`, so trying to patch a +-- newly created database will probably result in errors. See `sqlite/README.txt` +-- for more details. +--============================================================================== + +ALTER TABLE Worlds RENAME COLUMN OnlineRecord TO OnlinePeak; +ALTER TABLE Worlds RENAME COLUMN OnlineRecordTimestamp TO OnlinePeakTimestamp; +ALTER TABLE CharacterRights RENAME COLUMN Right TO Name; + +ALTER TABLE Worlds ADD COLUMN LastStartup INTEGER NOT NULL DEFAULT 0; +ALTER TABLE Worlds ADD COLUMN LastShutdown INTEGER NOT NULL DEFAULT 0; + 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 <errno.h> +#include <dirent.h> + +// 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<struct dirent> 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 //============================================================================== |