51 lines
1.5 KiB
SQL
51 lines
1.5 KiB
SQL
-- 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);
|
|
|