change how guild data is stored

1 files changed, 50 insertions, 0 deletions

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