diff options
Diffstat (limited to 'sqlite')
| -rw-r--r-- | sqlite/schema.sql | 44 | ||||
| -rw-r--r-- | sqlite/z-001-migrate-v01-to-v02.sql | 6 | ||||
| -rw-r--r-- | sqlite/z-002-migrate-v02-to-v03.sql | 50 |
3 files changed, 93 insertions, 7 deletions
diff --git a/sqlite/schema.sql b/sqlite/schema.sql index 0ab3a9f..2ee1a8b 100644 --- a/sqlite/schema.sql +++ b/sqlite/schema.sql @@ -57,9 +57,6 @@ CREATE TABLE Characters ( AccountID INTEGER NOT NULL, Name TEXT NOT NULL COLLATE NOCASE, Sex INTEGER NOT NULL, - Guild TEXT NOT NULL COLLATE NOCASE DEFAULT '', - Rank TEXT NOT NULL COLLATE NOCASE DEFAULT '', - Title TEXT NOT NULL DEFAULT '', Level INTEGER NOT NULL DEFAULT 0, Profession TEXT NOT NULL DEFAULT '', Residence TEXT NOT NULL DEFAULT '', @@ -72,7 +69,6 @@ CREATE TABLE Characters ( ); 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? @@ -123,6 +119,46 @@ CREATE TABLE LoginAttempts ( CREATE INDEX LoginAttemptsAccountIndex ON LoginAttempts(AccountID, Timestamp); CREATE INDEX LoginAttemptsAddressIndex ON LoginAttempts(IPAddress, Timestamp); +-- Guild Tables +--============================================================================== +CREATE TABLE Guilds ( + WorldID INTEGER NOT NULL, + GuildID INTEGER NOT NULL, + Name TEXT NOT NULL COLLATE NOCASE, + LeaderID INTEGER NOT NULL, + Created INTEGER NOT NULL, + PRIMARY KEY (GuildID), + UNIQUE (Name), + UNIQUE (LeaderID) +); + +CREATE TABLE GuildRanks ( + GuildID INTEGER NOT NULL, + Rank INTEGER NOT NULL, + Name TEXT NOT NULL, + PRIMARY KEY (GuildID, Rank) +); + +CREATE TABLE GuildMembers ( + GuildID INTEGER NOT NULL, + CharacterID INTEGER NOT NULL, + Rank INTEGER NOT NULL, + Title TEXT NOT NULL, + Joined INTEGER NOT NULL, + PRIMARY KEY (CharacterID) +); +CREATE INDEX GuildMembersGuildIndex ON GuildMembers(GuildID, Rank); + +CREATE TABLE GuildInvites ( + GuildID INTEGER NOT NULL, + CharacterID INTEGER NOT NULL, + RecruiterID INTEGER NOT NULL, + Timestamp INTEGER NOT NULL, + PRIMARY KEY (GuildID, CharacterID) +); +CREATE INDEX GuildInvitesCharacterIndex ON GuildInvites(CharacterID); +CREATE INDEX GuildInvitesRecruiterIndex ON GuildInvites(RecruiterID); + -- House Tables --============================================================================== CREATE TABLE Houses ( diff --git a/sqlite/z-001-migrate-v01-to-v02.sql b/sqlite/z-001-migrate-v01-to-v02.sql index 19fdddb..a874194 100644 --- a/sqlite/z-001-migrate-v01-to-v02.sql +++ b/sqlite/z-001-migrate-v01-to-v02.sql @@ -2,10 +2,10 @@ -- 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. +-- in `patches/` for automatic execution but not this one, unfortunately. For +-- more details see `sqlite/README.txt`. -- 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`. +-- apply them to a newly created database will result in errors. --============================================================================== BEGIN; diff --git a/sqlite/z-002-migrate-v02-to-v03.sql b/sqlite/z-002-migrate-v02-to-v03.sql new file mode 100644 index 0000000..eeb1d9d --- /dev/null +++ b/sqlite/z-002-migrate-v02-to-v03.sql @@ -0,0 +1,50 @@ +-- NOTE(fusion): This file contains the migration script from v0.2 to v0.3. It +-- can be executed automatically as a patch if placed at `sqlite/patches`. For +-- more details see `sqlite/README.txt`. +-- These changes are already present in the latest `schema.sql`, so trying to +-- apply them to a newly created database will result in errors. +--============================================================================== + +DROP INDEX CharactersGuildIndex; +ALTER TABLE Characters DROP COLUMN Guild; +ALTER TABLE Characters DROP COLUMN Rank; +ALTER TABLE Characters DROP COLUMN Title; + +CREATE TABLE Guilds ( + WorldID INTEGER NOT NULL, + GuildID INTEGER NOT NULL, + Name TEXT NOT NULL COLLATE NOCASE, + LeaderID INTEGER NOT NULL, + Created INTEGER NOT NULL, + PRIMARY KEY (GuildID), + UNIQUE (Name), + UNIQUE (LeaderID) +); + +CREATE TABLE GuildRanks ( + GuildID INTEGER NOT NULL, + Rank INTEGER NOT NULL, + Name TEXT NOT NULL, + PRIMARY KEY (GuildID, Rank) +); + +CREATE TABLE GuildMembers ( + GuildID INTEGER NOT NULL, + CharacterID INTEGER NOT NULL, + Rank INTEGER NOT NULL, + Title TEXT NOT NULL, + Joined INTEGER NOT NULL, + PRIMARY KEY (CharacterID) +); +CREATE INDEX GuildMembersGuildIndex ON GuildMembers(GuildID, Rank); + +CREATE TABLE GuildInvites ( + GuildID INTEGER NOT NULL, + CharacterID INTEGER NOT NULL, + RecruiterID INTEGER NOT NULL, + Timestamp INTEGER NOT NULL, + PRIMARY KEY (GuildID, CharacterID) +); +CREATE INDEX GuildInvitesCharacterIndex ON GuildInvites(CharacterID); +CREATE INDEX GuildInvitesRecruiterIndex ON GuildInvites(RecruiterID); + |