From 27a0df43b33b712462ab5be65462162b9e4bcd29 Mon Sep 17 00:00:00 2001 From: fusion32 Date: Thu, 23 Oct 2025 04:26:11 -0300 Subject: change how guild data is stored --- postgres/initial-data.sql | 117 --------------------------------- postgres/schema.sql | 46 +++++++++++-- postgres/z-002-migrate-v02-to-v03.sql | 54 +++++++++++++++ postgres/z-999-initial-data.sql | 119 ++++++++++++++++++++++++++++++++++ 4 files changed, 215 insertions(+), 121 deletions(-) delete mode 100644 postgres/initial-data.sql create mode 100644 postgres/z-002-migrate-v02-to-v03.sql create mode 100644 postgres/z-999-initial-data.sql (limited to 'postgres') diff --git a/postgres/initial-data.sql b/postgres/initial-data.sql deleted file mode 100644 index fbe40c7..0000000 --- a/postgres/initial-data.sql +++ /dev/null @@ -1,117 +0,0 @@ --- NOTE(fusion): This file contains sample initial data. It's wrapped into a --- transaction to avoid errors from leaving the database in some partial state. -BEGIN; - --- 111111/tibia -INSERT INTO Accounts (AccountID, Email, Auth) - VALUES (111111, '@tibia', '\x206699cbc2fae1683118c873d746aa376049cb5923ef0980298bb7acbba527ec9e765668f7a338dffea34acf61a20efb654c1e9c62d35148dba2aeeef8dc7788'); - --- NOTE(fusion): This would generally be a sequence of insertions but the way --- PostgreSQL handles unique auto increment columns makes so manually inserted --- values end up colliding with generated values at some point. --- On a real scenario, we'd never have manually assigned ids unless we're --- restoring some database dump, in which case it would also have saved --- sequences. -WITH - WorldIns AS ( - INSERT INTO Worlds (Name, Type, RebootTime, Host, Port, MaxPlayers, - PremiumPlayerBuffer, MaxNewbies, PremiumNewbieBuffer) - VALUES ('Zanera', 0, 5, 'localhost', 7172, 1000, 100, 300, 100) - RETURNING WorldID, Name - ), - CharacterIns AS ( - INSERT INTO Characters (WorldID, AccountID, Name, Sex) - SELECT W.WorldID, C.AccountID, C.Name, C.Sex - FROM WorldIns AS W, - LATERAL (VALUES - (111111, 'Gamemaster on ' || W.Name, 1), - (111111, 'Player on ' || W.Name, 1) - ) AS C (AccountID, Name, Sex) - RETURNING CharacterID, Name - ) -INSERT INTO CharacterRights(CharacterID, Name) - SELECT C.CharacterID, R.RightName - FROM CharacterIns AS C, - (VALUES - ('NOTATION'), - ('NAMELOCK'), - ('STATEMENT_REPORT'), - ('BANISHMENT'), - ('FINAL_WARNING'), - ('IP_BANISHMENT'), - ('KICK'), - ('HOME_TELEPORT'), - ('GAMEMASTER_BROADCAST'), - ('ANONYMOUS_BROADCAST'), - ('NO_BANISHMENT'), - ('ALLOW_MULTICLIENT'), - ('LOG_COMMUNICATION'), - ('READ_GAMEMASTER_CHANNEL'), - ('READ_TUTOR_CHANNEL'), - ('HIGHLIGHT_HELP_CHANNEL'), - ('SEND_BUGREPORTS'), - ('NAME_INSULTING'), - ('NAME_SENTENCE'), - ('NAME_NONSENSICAL_LETTERS'), - ('NAME_BADLY_FORMATTED'), - ('NAME_NO_PERSON'), - ('NAME_CELEBRITY'), - ('NAME_COUNTRY'), - ('NAME_FAKE_IDENTITY'), - ('NAME_FAKE_POSITION'), - ('STATEMENT_INSULTING'), - ('STATEMENT_SPAMMING'), - ('STATEMENT_ADVERT_OFFTOPIC'), - ('STATEMENT_ADVERT_MONEY'), - ('STATEMENT_NON_ENGLISH'), - ('STATEMENT_CHANNEL_OFFTOPIC'), - ('STATEMENT_VIOLATION_INCITING'), - ('CHEATING_BUG_ABUSE'), - ('CHEATING_GAME_WEAKNESS'), - ('CHEATING_MACRO_USE'), - ('CHEATING_MODIFIED_CLIENT'), - ('CHEATING_HACKING'), - ('CHEATING_MULTI_CLIENT'), - ('CHEATING_ACCOUNT_TRADING'), - ('CHEATING_ACCOUNT_SHARING'), - ('GAMEMASTER_THREATENING'), - ('GAMEMASTER_PRETENDING'), - ('GAMEMASTER_INFLUENCE'), - ('GAMEMASTER_FALSE_REPORTS'), - ('KILLING_EXCESSIVE_UNJUSTIFIED'), - ('DESTRUCTIVE_BEHAVIOUR'), - ('SPOILING_AUCTION'), - ('INVALID_PAYMENT'), - ('TELEPORT_TO_CHARACTER'), - ('TELEPORT_TO_MARK'), - ('TELEPORT_VERTICAL'), - ('TELEPORT_TO_COORDINATE'), - ('LEVITATE'), - ('SPECIAL_MOVEUSE'), - ('MODIFY_GOSTRENGTH'), - ('SHOW_COORDINATE'), - ('RETRIEVE'), - ('ENTER_HOUSES'), - ('OPEN_NAMEDOORS'), - ('INVULNERABLE'), - ('UNLIMITED_MANA'), - ('KEEP_INVENTORY'), - ('ALL_SPELLS'), - ('UNLIMITED_CAPACITY'), - ('ATTACK_EVERYWHERE'), - ('NO_LOGOUT_BLOCK'), - ('GAMEMASTER_OUTFIT'), - ('ILLUMINATE'), - ('CHANGE_PROFESSION'), - ('IGNORED_BY_MONSTERS'), - ('SHOW_KEYHOLE_NUMBERS'), - ('CREATE_OBJECTS'), - ('CREATE_MONEY'), - ('CREATE_MONSTERS'), - ('CHANGE_SKILLS'), - ('CLEANUP_FIELDS'), - ('NO_STATISTICS') - ) AS R (RightName) - WHERE C.Name LIKE 'Gamemaster%' COLLATE "default"; - -COMMIT; diff --git a/postgres/schema.sql b/postgres/schema.sql index efe9133..08837c5 100644 --- a/postgres/schema.sql +++ b/postgres/schema.sql @@ -2,6 +2,8 @@ -- the database in some partial state. Also, notice we don't use `IF NOT EXISTS` -- because it could also leave the database in a bad state if for example, some -- old version of a table already existed. +--============================================================================== + BEGIN; -- IMPORTANT(fusion): PosgreSQL doesn't have a defined `NOCASE` collation like @@ -78,9 +80,6 @@ CREATE TABLE Characters ( AccountID INTEGER NOT NULL, Name TEXT NOT NULL COLLATE NOCASE, Sex SMALLINT NOT NULL, - Guild TEXT NOT NULL COLLATE NOCASE DEFAULT '', - Rank TEXT NOT NULL COLLATE NOCASE DEFAULT '', - Title TEXT NOT NULL DEFAULT '', Level SMALLINT NOT NULL DEFAULT 0, Profession TEXT NOT NULL DEFAULT '', Residence TEXT NOT NULL DEFAULT '', @@ -93,7 +92,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); -- NOTE(fusion): It seems `RIGHT` is a reserved keyword and trying to use -- it as a column name will generate an error. @@ -136,6 +134,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 GENERATED ALWAYS AS IDENTITY, + Name TEXT NOT NULL COLLATE NOCASE, + LeaderID INTEGER NOT NULL, + Created TIMESTAMPTZ NOT NULL, + PRIMARY KEY (GuildID), + UNIQUE (Name), + UNIQUE (LeaderID) +); + +CREATE TABLE GuildRanks ( + GuildID INTEGER NOT NULL, + Rank SMALLINT NOT NULL, + Name TEXT NOT NULL, + PRIMARY KEY (GuildID, Rank) +); + +CREATE TABLE GuildMembers ( + GuildID INTEGER NOT NULL, + CharacterID INTEGER NOT NULL, + Rank SMALLINT NOT NULL, + Title TEXT NOT NULL, + Joined TIMESTAMPTZ 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 TIMESTAMPTZ 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/postgres/z-002-migrate-v02-to-v03.sql b/postgres/z-002-migrate-v02-to-v03.sql new file mode 100644 index 0000000..52b02df --- /dev/null +++ b/postgres/z-002-migrate-v02-to-v03.sql @@ -0,0 +1,54 @@ +-- NOTE(fusion): This file contains the migration script from v0.2 to v0.3. It's +-- put inside a transaction to avoid errors from leaving the database in some +-- partial state. +-- These changes are already present in the latest `schema.sql`, so trying to +-- apply them on a newly created database will result in errors. +--============================================================================== + +BEGIN; + +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 GENERATED ALWAYS AS IDENTITY, + Name TEXT NOT NULL COLLATE NOCASE, + LeaderID INTEGER NOT NULL, + Created TIMESTAMPTZ NOT NULL, + PRIMARY KEY (GuildID), + UNIQUE (Name), + UNIQUE (LeaderID) +); + +CREATE TABLE GuildRanks ( + GuildID INTEGER NOT NULL, + Rank SMALLINT NOT NULL, + Name TEXT NOT NULL, + PRIMARY KEY (GuildID, Rank) +); + +CREATE TABLE GuildMembers ( + GuildID INTEGER NOT NULL, + CharacterID INTEGER NOT NULL, + Rank SMALLINT NOT NULL, + Title TEXT NOT NULL, + Joined TIMESTAMPTZ 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 TIMESTAMPTZ NOT NULL, + PRIMARY KEY (GuildID, CharacterID) +); +CREATE INDEX GuildInvitesCharacterIndex ON GuildInvites(CharacterID); +CREATE INDEX GuildInvitesRecruiterIndex ON GuildInvites(RecruiterID); + +COMMIT; + diff --git a/postgres/z-999-initial-data.sql b/postgres/z-999-initial-data.sql new file mode 100644 index 0000000..139c4e1 --- /dev/null +++ b/postgres/z-999-initial-data.sql @@ -0,0 +1,119 @@ +-- NOTE(fusion): This file contains sample initial data. It's put inside a +-- transaction to avoid errors from leaving the database in some partial state. +--============================================================================== + +BEGIN; + +-- 111111/tibia +INSERT INTO Accounts (AccountID, Email, Auth) + VALUES (111111, '@tibia', '\x206699cbc2fae1683118c873d746aa376049cb5923ef0980298bb7acbba527ec9e765668f7a338dffea34acf61a20efb654c1e9c62d35148dba2aeeef8dc7788'); + +-- NOTE(fusion): This would generally be a sequence of insertions but the way +-- PostgreSQL handles unique auto increment columns makes so manually inserted +-- values end up colliding with generated values at some point. +-- On a real scenario, we'd never have manually assigned ids unless we're +-- restoring some database dump, in which case it would also have saved +-- sequences. +WITH + WorldIns AS ( + INSERT INTO Worlds (Name, Type, RebootTime, Host, Port, MaxPlayers, + PremiumPlayerBuffer, MaxNewbies, PremiumNewbieBuffer) + VALUES ('Zanera', 0, 5, 'localhost', 7172, 1000, 100, 300, 100) + RETURNING WorldID, Name + ), + CharacterIns AS ( + INSERT INTO Characters (WorldID, AccountID, Name, Sex) + SELECT W.WorldID, C.AccountID, C.Name, C.Sex + FROM WorldIns AS W, + LATERAL (VALUES + (111111, 'Gamemaster on ' || W.Name, 1), + (111111, 'Player on ' || W.Name, 1) + ) AS C (AccountID, Name, Sex) + RETURNING CharacterID, Name + ) +INSERT INTO CharacterRights(CharacterID, Name) + SELECT C.CharacterID, R.RightName + FROM CharacterIns AS C, + (VALUES + ('NOTATION'), + ('NAMELOCK'), + ('STATEMENT_REPORT'), + ('BANISHMENT'), + ('FINAL_WARNING'), + ('IP_BANISHMENT'), + ('KICK'), + ('HOME_TELEPORT'), + ('GAMEMASTER_BROADCAST'), + ('ANONYMOUS_BROADCAST'), + ('NO_BANISHMENT'), + ('ALLOW_MULTICLIENT'), + ('LOG_COMMUNICATION'), + ('READ_GAMEMASTER_CHANNEL'), + ('READ_TUTOR_CHANNEL'), + ('HIGHLIGHT_HELP_CHANNEL'), + ('SEND_BUGREPORTS'), + ('NAME_INSULTING'), + ('NAME_SENTENCE'), + ('NAME_NONSENSICAL_LETTERS'), + ('NAME_BADLY_FORMATTED'), + ('NAME_NO_PERSON'), + ('NAME_CELEBRITY'), + ('NAME_COUNTRY'), + ('NAME_FAKE_IDENTITY'), + ('NAME_FAKE_POSITION'), + ('STATEMENT_INSULTING'), + ('STATEMENT_SPAMMING'), + ('STATEMENT_ADVERT_OFFTOPIC'), + ('STATEMENT_ADVERT_MONEY'), + ('STATEMENT_NON_ENGLISH'), + ('STATEMENT_CHANNEL_OFFTOPIC'), + ('STATEMENT_VIOLATION_INCITING'), + ('CHEATING_BUG_ABUSE'), + ('CHEATING_GAME_WEAKNESS'), + ('CHEATING_MACRO_USE'), + ('CHEATING_MODIFIED_CLIENT'), + ('CHEATING_HACKING'), + ('CHEATING_MULTI_CLIENT'), + ('CHEATING_ACCOUNT_TRADING'), + ('CHEATING_ACCOUNT_SHARING'), + ('GAMEMASTER_THREATENING'), + ('GAMEMASTER_PRETENDING'), + ('GAMEMASTER_INFLUENCE'), + ('GAMEMASTER_FALSE_REPORTS'), + ('KILLING_EXCESSIVE_UNJUSTIFIED'), + ('DESTRUCTIVE_BEHAVIOUR'), + ('SPOILING_AUCTION'), + ('INVALID_PAYMENT'), + ('TELEPORT_TO_CHARACTER'), + ('TELEPORT_TO_MARK'), + ('TELEPORT_VERTICAL'), + ('TELEPORT_TO_COORDINATE'), + ('LEVITATE'), + ('SPECIAL_MOVEUSE'), + ('MODIFY_GOSTRENGTH'), + ('SHOW_COORDINATE'), + ('RETRIEVE'), + ('ENTER_HOUSES'), + ('OPEN_NAMEDOORS'), + ('INVULNERABLE'), + ('UNLIMITED_MANA'), + ('KEEP_INVENTORY'), + ('ALL_SPELLS'), + ('UNLIMITED_CAPACITY'), + ('ATTACK_EVERYWHERE'), + ('NO_LOGOUT_BLOCK'), + ('GAMEMASTER_OUTFIT'), + ('ILLUMINATE'), + ('CHANGE_PROFESSION'), + ('IGNORED_BY_MONSTERS'), + ('SHOW_KEYHOLE_NUMBERS'), + ('CREATE_OBJECTS'), + ('CREATE_MONEY'), + ('CREATE_MONSTERS'), + ('CHANGE_SKILLS'), + ('CLEANUP_FIELDS'), + ('NO_STATISTICS') + ) AS R (RightName) + WHERE C.Name LIKE 'Gamemaster%' COLLATE "default"; + +COMMIT; -- cgit v1.2.3