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 --- sqlite/README.txt | 21 +++--- sqlite/init.sql | 103 ----------------------------- sqlite/patches/9999-initial-data.sql | 97 ++++++++++++++++++++++++++++ sqlite/schema.sql | 122 ++++++++++++++++++----------------- sqlite/z-001-migrate-v01-to-v02.sql | 14 ++++ 5 files changed, 185 insertions(+), 172 deletions(-) delete mode 100644 sqlite/init.sql create mode 100644 sqlite/patches/9999-initial-data.sql create mode 100644 sqlite/z-001-migrate-v01-to-v02.sql (limited to 'sqlite') 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/init.sql deleted file mode 100644 index 9d561fc..0000000 --- a/sqlite/init.sql +++ /dev/null @@ -1,103 +0,0 @@ --- 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. ---============================================================================== -BEGIN; - -INSERT INTO Worlds (WorldID, Name, Type, RebootTime, Host, Port, MaxPlayers, - PremiumPlayerBuffer, MaxNewbies, PremiumNewbieBuffer) - VALUES (1, 'Zanera', 0, 5, 'localhost', 7172, 1000, 100, 300, 100); - --- 111111/tibia -INSERT INTO Accounts (AccountID, Email, Auth) - VALUES (111111, '@tibia', X'206699cbc2fae1683118c873d746aa376049cb5923ef0980298bb7acbba527ec9e765668f7a338dffea34acf61a20efb654c1e9c62d35148dba2aeeef8dc7788'); - -INSERT INTO Characters (WorldID, CharacterID, AccountID, Name, Sex) - VALUES (1, 1, 111111, 'Gamemaster', 1), (1, 2, 111111, 'Player', 1); - -INSERT INTO CharacterRights (CharacterID, Name) - VALUES - (1, 'NOTATION'), - (1, 'NAMELOCK'), - (1, 'STATEMENT_REPORT'), - (1, 'BANISHMENT'), - (1, 'FINAL_WARNING'), - (1, 'IP_BANISHMENT'), - (1, 'KICK'), - (1, 'HOME_TELEPORT'), - (1, 'GAMEMASTER_BROADCAST'), - (1, 'ANONYMOUS_BROADCAST'), - (1, 'NO_BANISHMENT'), - (1, 'ALLOW_MULTICLIENT'), - (1, 'LOG_COMMUNICATION'), - (1, 'READ_GAMEMASTER_CHANNEL'), - (1, 'READ_TUTOR_CHANNEL'), - (1, 'HIGHLIGHT_HELP_CHANNEL'), - (1, 'SEND_BUGREPORTS'), - (1, 'NAME_INSULTING'), - (1, 'NAME_SENTENCE'), - (1, 'NAME_NONSENSICAL_LETTERS'), - (1, 'NAME_BADLY_FORMATTED'), - (1, 'NAME_NO_PERSON'), - (1, 'NAME_CELEBRITY'), - (1, 'NAME_COUNTRY'), - (1, 'NAME_FAKE_IDENTITY'), - (1, 'NAME_FAKE_POSITION'), - (1, 'STATEMENT_INSULTING'), - (1, 'STATEMENT_SPAMMING'), - (1, 'STATEMENT_ADVERT_OFFTOPIC'), - (1, 'STATEMENT_ADVERT_MONEY'), - (1, 'STATEMENT_NON_ENGLISH'), - (1, 'STATEMENT_CHANNEL_OFFTOPIC'), - (1, 'STATEMENT_VIOLATION_INCITING'), - (1, 'CHEATING_BUG_ABUSE'), - (1, 'CHEATING_GAME_WEAKNESS'), - (1, 'CHEATING_MACRO_USE'), - (1, 'CHEATING_MODIFIED_CLIENT'), - (1, 'CHEATING_HACKING'), - (1, 'CHEATING_MULTI_CLIENT'), - (1, 'CHEATING_ACCOUNT_TRADING'), - (1, 'CHEATING_ACCOUNT_SHARING'), - (1, 'GAMEMASTER_THREATENING'), - (1, 'GAMEMASTER_PRETENDING'), - (1, 'GAMEMASTER_INFLUENCE'), - (1, 'GAMEMASTER_FALSE_REPORTS'), - (1, 'KILLING_EXCESSIVE_UNJUSTIFIED'), - (1, 'DESTRUCTIVE_BEHAVIOUR'), - (1, 'SPOILING_AUCTION'), - (1, 'INVALID_PAYMENT'), - (1, 'TELEPORT_TO_CHARACTER'), - (1, 'TELEPORT_TO_MARK'), - (1, 'TELEPORT_VERTICAL'), - (1, 'TELEPORT_TO_COORDINATE'), - (1, 'LEVITATE'), - (1, 'SPECIAL_MOVEUSE'), - (1, 'MODIFY_GOSTRENGTH'), - (1, 'SHOW_COORDINATE'), - (1, 'RETRIEVE'), - (1, 'ENTER_HOUSES'), - (1, 'OPEN_NAMEDOORS'), - (1, 'INVULNERABLE'), - (1, 'UNLIMITED_MANA'), - (1, 'KEEP_INVENTORY'), - (1, 'ALL_SPELLS'), - (1, 'UNLIMITED_CAPACITY'), - (1, 'ATTACK_EVERYWHERE'), - (1, 'NO_LOGOUT_BLOCK'), - (1, 'GAMEMASTER_OUTFIT'), - (1, 'ILLUMINATE'), - (1, 'CHANGE_PROFESSION'), - (1, 'IGNORED_BY_MONSTERS'), - (1, 'SHOW_KEYHOLE_NUMBERS'), - (1, 'CREATE_OBJECTS'), - (1, 'CREATE_MONEY'), - (1, 'CREATE_MONSTERS'), - (1, 'CHANGE_SKILLS'), - (1, 'CLEANUP_FIELDS'), - (1, 'NO_STATISTICS'); - -COMMIT; diff --git a/sqlite/patches/9999-initial-data.sql b/sqlite/patches/9999-initial-data.sql new file mode 100644 index 0000000..53d92f2 --- /dev/null +++ b/sqlite/patches/9999-initial-data.sql @@ -0,0 +1,97 @@ +-- 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. +--============================================================================== + +INSERT INTO Worlds (WorldID, Name, Type, RebootTime, Host, Port, MaxPlayers, + PremiumPlayerBuffer, MaxNewbies, PremiumNewbieBuffer) + VALUES (1, 'Zanera', 0, 5, 'localhost', 7172, 1000, 100, 300, 100); + +-- 111111/tibia +INSERT INTO Accounts (AccountID, Email, Auth) + VALUES (111111, '@tibia', X'206699cbc2fae1683118c873d746aa376049cb5923ef0980298bb7acbba527ec9e765668f7a338dffea34acf61a20efb654c1e9c62d35148dba2aeeef8dc7788'); + +INSERT INTO Characters (WorldID, CharacterID, AccountID, Name, Sex) + VALUES (1, 1, 111111, 'Gamemaster', 1), (1, 2, 111111, 'Player', 1); + +INSERT INTO CharacterRights (CharacterID, Name) + VALUES + (1, 'NOTATION'), + (1, 'NAMELOCK'), + (1, 'STATEMENT_REPORT'), + (1, 'BANISHMENT'), + (1, 'FINAL_WARNING'), + (1, 'IP_BANISHMENT'), + (1, 'KICK'), + (1, 'HOME_TELEPORT'), + (1, 'GAMEMASTER_BROADCAST'), + (1, 'ANONYMOUS_BROADCAST'), + (1, 'NO_BANISHMENT'), + (1, 'ALLOW_MULTICLIENT'), + (1, 'LOG_COMMUNICATION'), + (1, 'READ_GAMEMASTER_CHANNEL'), + (1, 'READ_TUTOR_CHANNEL'), + (1, 'HIGHLIGHT_HELP_CHANNEL'), + (1, 'SEND_BUGREPORTS'), + (1, 'NAME_INSULTING'), + (1, 'NAME_SENTENCE'), + (1, 'NAME_NONSENSICAL_LETTERS'), + (1, 'NAME_BADLY_FORMATTED'), + (1, 'NAME_NO_PERSON'), + (1, 'NAME_CELEBRITY'), + (1, 'NAME_COUNTRY'), + (1, 'NAME_FAKE_IDENTITY'), + (1, 'NAME_FAKE_POSITION'), + (1, 'STATEMENT_INSULTING'), + (1, 'STATEMENT_SPAMMING'), + (1, 'STATEMENT_ADVERT_OFFTOPIC'), + (1, 'STATEMENT_ADVERT_MONEY'), + (1, 'STATEMENT_NON_ENGLISH'), + (1, 'STATEMENT_CHANNEL_OFFTOPIC'), + (1, 'STATEMENT_VIOLATION_INCITING'), + (1, 'CHEATING_BUG_ABUSE'), + (1, 'CHEATING_GAME_WEAKNESS'), + (1, 'CHEATING_MACRO_USE'), + (1, 'CHEATING_MODIFIED_CLIENT'), + (1, 'CHEATING_HACKING'), + (1, 'CHEATING_MULTI_CLIENT'), + (1, 'CHEATING_ACCOUNT_TRADING'), + (1, 'CHEATING_ACCOUNT_SHARING'), + (1, 'GAMEMASTER_THREATENING'), + (1, 'GAMEMASTER_PRETENDING'), + (1, 'GAMEMASTER_INFLUENCE'), + (1, 'GAMEMASTER_FALSE_REPORTS'), + (1, 'KILLING_EXCESSIVE_UNJUSTIFIED'), + (1, 'DESTRUCTIVE_BEHAVIOUR'), + (1, 'SPOILING_AUCTION'), + (1, 'INVALID_PAYMENT'), + (1, 'TELEPORT_TO_CHARACTER'), + (1, 'TELEPORT_TO_MARK'), + (1, 'TELEPORT_VERTICAL'), + (1, 'TELEPORT_TO_COORDINATE'), + (1, 'LEVITATE'), + (1, 'SPECIAL_MOVEUSE'), + (1, 'MODIFY_GOSTRENGTH'), + (1, 'SHOW_COORDINATE'), + (1, 'RETRIEVE'), + (1, 'ENTER_HOUSES'), + (1, 'OPEN_NAMEDOORS'), + (1, 'INVULNERABLE'), + (1, 'UNLIMITED_MANA'), + (1, 'KEEP_INVENTORY'), + (1, 'ALL_SPELLS'), + (1, 'UNLIMITED_CAPACITY'), + (1, 'ATTACK_EVERYWHERE'), + (1, 'NO_LOGOUT_BLOCK'), + (1, 'GAMEMASTER_OUTFIT'), + (1, 'ILLUMINATE'), + (1, 'CHANGE_PROFESSION'), + (1, 'IGNORED_BY_MONSTERS'), + (1, 'SHOW_KEYHOLE_NUMBERS'), + (1, 'CREATE_OBJECTS'), + (1, 'CREATE_MONEY'), + (1, 'CREATE_MONSTERS'), + (1, 'CHANGE_SKILLS'), + (1, 'CLEANUP_FIELDS'), + (1, 'NO_STATISTICS'); + 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; + -- cgit v1.2.3