modify how automatic SQLite patches/upgrades works + overall cleanup

1 files changed, 63 insertions, 59 deletions

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)
 );
+