Skip to content

Commit 601ce32

Browse files
fusion32fusion32
committed
modify how automatic SQLite patches/upgrades works + overall cleanup
1 parent d92f642 commit 601ce32

9 files changed

Lines changed: 322 additions & 189 deletions

File tree

‎postgres/schema.sql‎

Lines changed: 11 additions & 13 deletions
Original file line numberDiff line numberDiff line change
@@ -29,6 +29,17 @@ CREATE COLLATION NOCASE (
2929
-- using them here. It might be a good idea for consistency, but it's not a hard
3030
-- requirement.
3131

32+
-- NOTE(fusion): A table with schema information. It currently only contains the
33+
-- schema version which is checked at startup against `POSTGRESQL_SCHEMA_VERSION`,
34+
-- defined in `database_postgres.cc`. The query manager will only startup if the
35+
-- versions match.
36+
CREATE TABLE SchemaInfo (
37+
Key TEXT NOT NULL COLLATE NOCASE,
38+
Value TEXT NOT NULL,
39+
PRIMARY KEY (Key)
40+
);
41+
INSERT INTO SchemaInfo (Key, Value) VALUES ('VERSION', '1');
42+
3243
-- Primary Tables
3344
--==============================================================================
3445
CREATE TABLE Worlds (
@@ -280,17 +291,4 @@ CREATE TABLE OnlineCharacters (
280291
PRIMARY KEY (WorldID, Name)
281292
);
282293

283-
-- Schema Info
284-
--==============================================================================
285-
-- NOTE(fusion): The `SchemaInfo` table should hold information about the schema
286-
-- and be used for consistency checks at startup. It currently only contains the
287-
-- schema version, which I feel is the only value needed.
288-
CREATE TABLE SchemaInfo (
289-
Key TEXT NOT NULL COLLATE NOCASE,
290-
Value TEXT NOT NULL,
291-
PRIMARY KEY (Key)
292-
);
293-
294-
INSERT INTO SchemaInfo (Key, Value) VALUES ('VERSION', '1');
295-
296294
COMMIT;

‎sqlite/README.txt‎

Lines changed: 11 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -1,10 +1,11 @@
1-
The query manager will properly initialize and upgrade the database schema
2-
based on files in this folder. The initial schema should be in `schema.sql` and
3-
upgrades should be in `upgrade-N.sql` where N is a number starting from 1. The
4-
`upgrade-N.sql` file should take the database from version N to N + 1.
5-
The only restriction to these files is that they can't have transaction
6-
statements ("BEGIN", "ROLLBACK", "COMMIT") because the query manager will
7-
already bundle them into a transaction that also sets `user_version`, and
8-
and nested transactions aren't allowed.
9-
The current database version can be retrieved with the "PRAGMA user_version"
10-
query in the SQLite shell.
1+
The query manager will initialize and patch the database schema, at startup,
2+
based on the files in this folder. The initial schema is inside `schema.sql` and
3+
shouldn't be modified, to make sure the database can be initialized if everything
4+
else fails. Patches and modifications should be placed in `patches/` with no
5+
particular name restrictions except for having an `.sql` extension. These patch
6+
files will be executed exactly ONCE. If multiple patches are pending at startup,
7+
they're executed in alphabetical order.
8+
As for statement restrictions, the only thing prohibited is the presence of
9+
transaction statements "BEGIN", "ROLLBACK", and "COMMIT". This is because all
10+
patches will be bundled into the same transaction, to ensure atomicity.
11+

sqlite/init.sql renamed to sqlite/patches/9999-initial-data.sql

Lines changed: 3 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -1,12 +1,7 @@
1-
-- NOTE(fusion): The query manager WON'T automatically run this but the game
2-
-- server still requires at least the world config to be able to boot up. It
3-
-- is probably a good idea to keep this separated from `schema.sql` and then
4-
-- running it with `sqlite3 -echo tibia.db < sqlite/init.sql`, although it is
5-
-- not mandatory.
6-
-- Because this isn't automatically managed, all queries are wrapped in a
7-
-- transaction to avoid partial writes in case of errors.
1+
-- NOTE(fusion): This file contains sample initial data and will be executed
2+
-- automatically as a patch by the query manager. See `sqlite/README.txt` for
3+
-- more details.
84
--==============================================================================
9-
BEGIN;
105

116
INSERT INTO Worlds (WorldID, Name, Type, RebootTime, Host, Port, MaxPlayers,
127
PremiumPlayerBuffer, MaxNewbies, PremiumNewbieBuffer)
@@ -100,4 +95,3 @@ INSERT INTO CharacterRights (CharacterID, Name)
10095
(1, 'CLEANUP_FIELDS'),
10196
(1, 'NO_STATISTICS');
10297

103-
COMMIT;

‎sqlite/schema.sql‎

Lines changed: 63 additions & 59 deletions
Original file line numberDiff line numberDiff line change
@@ -1,6 +1,27 @@
1+
-- Database ApplicationID and UserVersion
2+
--==============================================================================
3+
-- NOTE(fusion): SQLite's application id, used to identify an existing database.
4+
-- The query manager will only access an existing database whose application id
5+
-- is exactly 0x54694442 which is the ASCII for "TiDB" or "Tibia Database".
6+
PRAGMA application_id = 0x54694442;
7+
8+
-- NOTE(fusion): SQLite's user version, used to track the current schema version.
9+
-- The query manager will only access an existing database whose user version is
10+
-- exactly `SQLITE_USER_VERSION`, defined in `database_sqlite.cc`.
11+
PRAGMA user_version = 1;
12+
13+
-- NOTE(fusion): A table with the history of applied patches. It will be inspected
14+
-- at startup to decide which patches still need to be applied. See `sqlite/README.txt`
15+
-- for more details.
16+
CREATE TABLE Patches (
17+
FileName TEXT NOT NULL COLLATE NOCASE,
18+
Timestamp INTEGER NOT NULL,
19+
UNIQUE (FileName)
20+
);
21+
122
-- Primary Tables
223
--==============================================================================
3-
CREATE TABLE IF NOT EXISTS Worlds (
24+
CREATE TABLE Worlds (
425
WorldID INTEGER NOT NULL,
526
Name TEXT NOT NULL COLLATE NOCASE,
627
Type INTEGER NOT NULL,
@@ -19,7 +40,7 @@ CREATE TABLE IF NOT EXISTS Worlds (
1940
UNIQUE (Name)
2041
);
2142

22-
CREATE TABLE IF NOT EXISTS Accounts (
43+
CREATE TABLE Accounts (
2344
AccountID INTEGER NOT NULL,
2445
Email TEXT NOT NULL COLLATE NOCASE,
2546
Auth BLOB NOT NULL,
@@ -30,7 +51,7 @@ CREATE TABLE IF NOT EXISTS Accounts (
3051
UNIQUE (Email)
3152
);
3253

33-
CREATE TABLE IF NOT EXISTS Characters (
54+
CREATE TABLE Characters (
3455
WorldID INTEGER NOT NULL,
3556
CharacterID INTEGER NOT NULL,
3657
AccountID INTEGER NOT NULL,
@@ -49,69 +70,62 @@ CREATE TABLE IF NOT EXISTS Characters (
4970
PRIMARY KEY (CharacterID),
5071
UNIQUE (Name)
5172
);
52-
CREATE INDEX IF NOT EXISTS CharactersWorldIndex
53-
ON Characters(WorldID, IsOnline);
54-
CREATE INDEX IF NOT EXISTS CharactersAccountIndex
55-
ON Characters(AccountID, IsOnline);
56-
CREATE INDEX IF NOT EXISTS CharactersGuildIndex
57-
ON Characters(Guild, Rank);
73+
CREATE INDEX CharactersWorldIndex ON Characters(WorldID, IsOnline);
74+
CREATE INDEX CharactersAccountIndex ON Characters(AccountID, IsOnline);
75+
CREATE INDEX CharactersGuildIndex ON Characters(Guild, Rank);
5876

5977
/*
6078
-- TODO(fusion): Have group rights instead of adding individual rights to characters?
6179
ALTER TABLE Characters ADD GroupID INTEGER NOT NULL;
62-
CREATE TABLE IF NOT EXISTS CharacterRights (
80+
CREATE TABLE CharacterRights (
6381
GroupID INTEGER NOT NULL,
6482
Name TEXT NOT NULL COLLATE NOCASE,
6583
PRIMARY KEY(GroupID, Name)
6684
);
6785
*/
6886

69-
CREATE TABLE IF NOT EXISTS CharacterRights (
87+
CREATE TABLE CharacterRights (
7088
CharacterID INTEGER NOT NULL,
7189
Name TEXT NOT NULL COLLATE NOCASE,
7290
PRIMARY KEY(CharacterID, Name)
7391
);
7492

75-
CREATE TABLE IF NOT EXISTS CharacterDeaths (
93+
CREATE TABLE CharacterDeaths (
7694
CharacterID INTEGER NOT NULL,
7795
Level INTEGER NOT NULL,
7896
OffenderID INTEGER NOT NULL,
7997
Remark TEXT NOT NULL,
8098
Unjustified INTEGER NOT NULL,
8199
Timestamp INTEGER NOT NULL
82100
);
83-
CREATE INDEX IF NOT EXISTS CharacterDeathsCharacterIndex
84-
ON CharacterDeaths(CharacterID, Level);
85-
CREATE INDEX IF NOT EXISTS CharacterDeathsOffenderIndex
86-
ON CharacterDeaths(OffenderID, Unjustified);
101+
CREATE INDEX CharacterDeathsCharacterIndex ON CharacterDeaths(CharacterID, Level);
102+
CREATE INDEX CharacterDeathsOffenderIndex ON CharacterDeaths(OffenderID, Unjustified);
87103

88-
CREATE TABLE IF NOT EXISTS Buddies (
104+
CREATE TABLE Buddies (
89105
WorldID INTEGER NOT NULL,
90106
AccountID INTEGER NOT NULL,
91107
BuddyID INTEGER NOT NULL,
92108
PRIMARY KEY (WorldID, AccountID, BuddyID)
93109
);
94110

95-
CREATE TABLE IF NOT EXISTS WorldInvitations (
111+
CREATE TABLE WorldInvitations (
96112
WorldID INTEGER NOT NULL,
97113
CharacterID INTEGER NOT NULL,
98114
PRIMARY KEY (WorldID, CharacterID)
99115
);
100116

101-
CREATE TABLE IF NOT EXISTS LoginAttempts (
117+
CREATE TABLE LoginAttempts (
102118
AccountID INTEGER NOT NULL,
103119
IPAddress INTEGER NOT NULL,
104120
Timestamp INTEGER NOT NULL,
105121
Failed INTEGER NOT NULL
106122
);
107-
CREATE INDEX IF NOT EXISTS LoginAttemptsAccountIndex
108-
ON LoginAttempts(AccountID, Timestamp);
109-
CREATE INDEX IF NOT EXISTS LoginAttemptsAddressIndex
110-
ON LoginAttempts(IPAddress, Timestamp);
123+
CREATE INDEX LoginAttemptsAccountIndex ON LoginAttempts(AccountID, Timestamp);
124+
CREATE INDEX LoginAttemptsAddressIndex ON LoginAttempts(IPAddress, Timestamp);
111125

112126
-- House Tables
113127
--==============================================================================
114-
CREATE TABLE IF NOT EXISTS Houses (
128+
CREATE TABLE Houses (
115129
WorldID INTEGER NOT NULL,
116130
HouseID INTEGER NOT NULL,
117131
Name TEXT NOT NULL,
@@ -126,7 +140,7 @@ CREATE TABLE IF NOT EXISTS Houses (
126140
PRIMARY KEY (WorldID, HouseID)
127141
);
128142

129-
CREATE TABLE IF NOT EXISTS HouseOwners (
143+
CREATE TABLE HouseOwners (
130144
WorldID INTEGER NOT NULL,
131145
HouseID INTEGER NOT NULL,
132146
OwnerID INTEGER NOT NULL,
@@ -137,7 +151,7 @@ CREATE TABLE IF NOT EXISTS HouseOwners (
137151
-- NOTE(fusion): Auctions with a NULL `FinishTime` aren't active, to avoid running
138152
-- multiple times with no actual bidder. It should be set after the first bid along
139153
-- with `BidderID` and `BidAmount`.
140-
CREATE TABLE IF NOT EXISTS HouseAuctions (
154+
CREATE TABLE HouseAuctions (
141155
WorldID INTEGER NOT NULL,
142156
HouseID INTEGER NOT NULL,
143157
BidderID INTEGER DEFAULT NULL,
@@ -146,40 +160,36 @@ CREATE TABLE IF NOT EXISTS HouseAuctions (
146160
PRIMARY KEY (WorldID, HouseID)
147161
);
148162

149-
CREATE TABLE IF NOT EXISTS HouseTransfers (
163+
CREATE TABLE HouseTransfers (
150164
WorldID INTEGER NOT NULL,
151165
HouseID INTEGER NOT NULL,
152166
NewOwnerID INTEGER NOT NULL,
153167
Price INTEGER NOT NULL,
154168
PRIMARY KEY (WorldID, HouseID)
155169
);
156170

157-
CREATE TABLE IF NOT EXISTS HouseAuctionExclusions (
171+
CREATE TABLE HouseAuctionExclusions (
158172
CharacterID INTEGER NOT NULL,
159173
Issued INTEGER NOT NULL,
160174
Until INTEGER NOT NULL,
161175
BanishmentID INTEGER NOT NULL
162176
);
163-
CREATE INDEX IF NOT EXISTS HouseAuctionExclusionsIndex
164-
ON HouseAuctionExclusions(CharacterID, Until);
177+
CREATE INDEX HouseAuctionExclusionsIndex ON HouseAuctionExclusions(CharacterID, Until);
165178

166-
CREATE TABLE IF NOT EXISTS HouseAssignments (
179+
CREATE TABLE HouseAssignments (
167180
WorldID INTEGER NOT NULL,
168181
HouseID INTEGER NOT NULL,
169182
OwnerID INTEGER NOT NULL,
170183
Price INTEGER NOT NULL,
171184
Timestamp INTEGER NOT NULL
172185
);
173-
CREATE INDEX IF NOT EXISTS HouseAssignmentsHouseIndex
174-
ON HouseAssignments(WorldID, HouseID);
175-
CREATE INDEX IF NOT EXISTS HouseAssignmentsTimeIndex
176-
ON HouseAssignments(WorldID, Timestamp);
177-
CREATE INDEX IF NOT EXISTS HouseAssignmentsOwnerIndex
178-
ON HouseAssignments(OwnerID);
186+
CREATE INDEX HouseAssignmentsHouseIndex ON HouseAssignments(WorldID, HouseID);
187+
CREATE INDEX HouseAssignmentsTimeIndex ON HouseAssignments(WorldID, Timestamp);
188+
CREATE INDEX HouseAssignmentsOwnerIndex ON HouseAssignments(OwnerID);
179189

180190
-- Banishment Tables
181191
--==============================================================================
182-
CREATE TABLE IF NOT EXISTS Banishments (
192+
CREATE TABLE Banishments (
183193
BanishmentID INTEGER NOT NULL,
184194
AccountID INTEGER NOT NULL,
185195
IPAddress INTEGER NOT NULL,
@@ -191,10 +201,9 @@ CREATE TABLE IF NOT EXISTS Banishments (
191201
Until INTEGER NOT NULL,
192202
PRIMARY KEY (BanishmentID)
193203
);
194-
CREATE INDEX IF NOT EXISTS BanishmentsAccountIndex
195-
ON Banishments(AccountID, Until, FinalWarning);
204+
CREATE INDEX BanishmentsAccountIndex ON Banishments(AccountID, Until, FinalWarning);
196205

197-
CREATE TABLE IF NOT EXISTS IPBanishments (
206+
CREATE TABLE IPBanishments (
198207
CharacterID INTEGER NOT NULL,
199208
IPAddress INTEGER NOT NULL,
200209
GamemasterID INTEGER NOT NULL,
@@ -203,12 +212,10 @@ CREATE TABLE IF NOT EXISTS IPBanishments (
203212
Issued INTEGER NOT NULL,
204213
Until INTEGER NOT NULL
205214
);
206-
CREATE INDEX IF NOT EXISTS IPBanishmentsAddressIndex
207-
ON IPBanishments(IPAddress);
208-
CREATE INDEX IF NOT EXISTS IPBanishmentsCharacterIndex
209-
ON IPBanishments(CharacterID);
215+
CREATE INDEX IPBanishmentsAddressIndex ON IPBanishments(IPAddress);
216+
CREATE INDEX IPBanishmentsCharacterIndex ON IPBanishments(CharacterID);
210217

211-
CREATE TABLE IF NOT EXISTS Namelocks (
218+
CREATE TABLE Namelocks (
212219
CharacterID INTEGER NOT NULL,
213220
IPAddress INTEGER NOT NULL,
214221
GamemasterID INTEGER NOT NULL,
@@ -219,17 +226,16 @@ CREATE TABLE IF NOT EXISTS Namelocks (
219226
PRIMARY KEY (CharacterID)
220227
);
221228

222-
CREATE TABLE IF NOT EXISTS Notations (
229+
CREATE TABLE Notations (
223230
CharacterID INTEGER NOT NULL,
224231
IPAddress INTEGER NOT NULL,
225232
GamemasterID INTEGER NOT NULL,
226233
Reason TEXT NOT NULL,
227234
Comment TEXT NOT NULL
228235
);
229-
CREATE INDEX IF NOT EXISTS NotationsCharacterIndex
230-
ON Notations(CharacterID);
236+
CREATE INDEX NotationsCharacterIndex ON Notations(CharacterID);
231237

232-
CREATE TABLE IF NOT EXISTS Statements (
238+
CREATE TABLE Statements (
233239
WorldID INTEGER NOT NULL,
234240
Timestamp INTEGER NOT NULL,
235241
StatementID INTEGER NOT NULL,
@@ -238,10 +244,9 @@ CREATE TABLE IF NOT EXISTS Statements (
238244
Text TEXT NOT NULL,
239245
PRIMARY KEY (WorldID, Timestamp, StatementID)
240246
);
241-
CREATE INDEX IF NOT EXISTS StatementsCharacterIndex
242-
ON Statements(CharacterID, Timestamp);
247+
CREATE INDEX StatementsCharacterIndex ON Statements(CharacterID, Timestamp);
243248

244-
CREATE TABLE IF NOT EXISTS ReportedStatements (
249+
CREATE TABLE ReportedStatements (
245250
WorldID INTEGER NOT NULL,
246251
Timestamp INTEGER NOT NULL,
247252
StatementID INTEGER NOT NULL,
@@ -252,25 +257,24 @@ CREATE TABLE IF NOT EXISTS ReportedStatements (
252257
Comment TEXT NOT NULL,
253258
PRIMARY KEY (WorldID, Timestamp, StatementID)
254259
);
255-
CREATE INDEX IF NOT EXISTS ReportedStatementsCharacterIndex
256-
ON ReportedStatements(CharacterID, Timestamp);
257-
CREATE INDEX IF NOT EXISTS ReportedStatementsBanishmentIndex
258-
ON ReportedStatements(BanishmentID);
260+
CREATE INDEX ReportedStatementsCharacterIndex ON ReportedStatements(CharacterID, Timestamp);
261+
CREATE INDEX ReportedStatementsBanishmentIndex ON ReportedStatements(BanishmentID);
259262

260263
-- Info Tables
261264
--==============================================================================
262-
CREATE TABLE IF NOT EXISTS KillStatistics (
265+
CREATE TABLE KillStatistics (
263266
WorldID INTEGER NOT NULL,
264267
RaceName TEXT NOT NULL COLLATE NOCASE,
265268
TimesKilled INTEGER NOT NULL,
266269
PlayersKilled INTEGER NOT NULL,
267270
PRIMARY KEY (WorldID, RaceName)
268271
);
269272

270-
CREATE TABLE IF NOT EXISTS OnlineCharacters (
273+
CREATE TABLE OnlineCharacters (
271274
WorldID INTEGER NOT NULL,
272275
Name TEXT NOT NULL COLLATE NOCASE,
273276
Level INTEGER NOT NULL,
274277
Profession TEXT NOT NULL,
275278
PRIMARY KEY (WorldID, Name)
276279
);
280+

‎sqlite/z-001-migrate-v01-to-v02.sql‎

Lines changed: 14 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,14 @@
1+
-- NOTE(fusion): This file contains the migration script from v0.1 to v0.2. It
2+
-- can be placed into `patches/` to upgrade an existing database. Note that these
3+
-- changes are already present in the latest `schema.sql`, so trying to patch a
4+
-- newly created database will probably result in errors. See `sqlite/README.txt`
5+
-- for more details.
6+
--==============================================================================
7+
8+
ALTER TABLE Worlds RENAME COLUMN OnlineRecord TO OnlinePeak;
9+
ALTER TABLE Worlds RENAME COLUMN OnlineRecordTimestamp TO OnlinePeakTimestamp;
10+
ALTER TABLE CharacterRights RENAME COLUMN Right TO Name;
11+
12+
ALTER TABLE Worlds ADD COLUMN LastStartup INTEGER NOT NULL DEFAULT 0;
13+
ALTER TABLE Worlds ADD COLUMN LastShutdown INTEGER NOT NULL DEFAULT 0;
14+

‎src/database_postgres.cc‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1093,7 +1093,7 @@ const char *PrepareQuery(TDatabase *Database, const char *Text){
10931093
Stmt->Text = strdup(Text);
10941094
ASSERT(Stmt->Text != NULL);
10951095

1096-
#if 1 // DEBUG_STATEMENT_CACHE
1096+
#if DEBUG_STATEMENT_CACHE
10971097
{
10981098
char Preview[30];
10991099
StringBufCopyEllipsis(Preview, Text);

0 commit comments

Comments
 (0)