change how guild data is stored

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