update documentation + wrap the few SQLite scripts

3 files changed, 28 insertions, 10 deletions

diff --git a/sqlite/patches/.gitignore b/sqlite/patches/.gitignore
new file mode 100644
index 0000000..d6b7ef3
--- /dev/null
+++ b/sqlite/patches/.gitignore
@@ -0,0 +1,2 @@
+*
+!.gitignore
diff --git a/sqlite/z-001-migrate-v01-to-v02.sql b/sqlite/z-001-migrate-v01-to-v02.sql
index b7df053..f471f53 100644
--- a/sqlite/z-001-migrate-v01-to-v02.sql
+++ b/sqlite/z-001-migrate-v01-to-v02.sql
@@ -1,14 +1,30 @@
 -- 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.
+-- must be manually executed as `sqlite3 -bail -echo tibia.db < migration.sql`
+-- because the original schema didn't have a `Patches` table which is necessary
+-- with the new automatic patching system. Future migration scripts can be placed
+-- in `patches/` for automatic execution but not this one unfortunately.
+--  These changes are already present in the latest `schema.sql`, so trying to
+-- apply it to a newly created database will result in errors. For more details
+-- see `sqlite/README.txt`.
 --==============================================================================
 
-ALTER TABLE Worlds          RENAME COLUMN OnlineRecord          TO OnlinePeak;
-ALTER TABLE Worlds          RENAME COLUMN OnlineRecordTimestamp TO OnlinePeakTimestamp;
-ALTER TABLE CharacterRights RENAME COLUMN Right                 TO Name;
+BEGIN;
+
+PRAGMA application_id = 0x54694442;
+
+PRAGMA user_version = 1;
 
+CREATE TABLE Patches (
+	FileName TEXT NOT NULL COLLATE NOCASE,
+	Timestamp INTEGER NOT NULL,
+	UNIQUE (FileName)
+);
+
+ALTER TABLE Worlds          RENAME COLUMN OnlineRecord TO OnlinePeak;
+ALTER TABLE Worlds          RENAME COLUMN OnlineRecordTimestamp TO OnlinePeakTimestamp;
 ALTER TABLE Worlds          ADD COLUMN LastStartup INTEGER NOT NULL DEFAULT 0;
 ALTER TABLE Worlds          ADD COLUMN LastShutdown INTEGER NOT NULL DEFAULT 0;
+ALTER TABLE CharacterRights RENAME COLUMN Right TO Name;
+
+COMMIT;
 
diff --git a/sqlite/patches/9999-initial-data.sql b/sqlite/z-999-initial-data.sql
index 53d92f2..d20fee0 100644
--- a/sqlite/patches/9999-initial-data.sql
+++ b/sqlite/z-999-initial-data.sql
@@ -1,6 +1,6 @@
--- 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.
+-- NOTE(fusion): This file contains sample initial data and can be executed
+-- automatically as a patch by the query manager if placed at `sqlite/patches`.
+-- See `sqlite/README.txt` for more details.
 --==============================================================================
 
 INSERT INTO Worlds (WorldID, Name, Type, RebootTime, Host, Port, MaxPlayers,