modify how automatic SQLite patches/upgrades works + overall cleanup

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
 //==============================================================================