beginning of postgres driver + overall tweaks

2 files changed, 409 insertions, 0 deletions

diff --git a/postgres/init.sql b/postgres/init.sql
new file mode 100644
index 0000000..d26379c
--- /dev/null
+++ b/postgres/init.sql
@@ -0,0 +1,117 @@
+-- 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 already 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 = 'Gamemaster';
+
+COMMIT;
diff --git a/postgres/schema.sql b/postgres/schema.sql
new file mode 100644
index 0000000..548e43c
--- /dev/null
+++ b/postgres/schema.sql
@@ -0,0 +1,292 @@
+-- NOTE(fusion): Everything is inside a transaction to avoid errors from leaving
+-- 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
+-- SQLite and doesn't use a default case-insensitive collation like MySQL. The
+-- simplest alternative is to create a custom collation.
+--  This is only supported with PostgreSQL 10+ but it should safe to assume that
+-- it's widely supported, given that it's almost 10 years old and well past its
+-- end-of-life.
+--  For more information see:
+--   https://www.postgresql.org/docs/current/collation.html
+CREATE COLLATION NOCASE (
+    provider = icu,
+    deterministic = false,
+    locale = 'und-u-ks-level2'
+);
+
+-- IMPORTANT(fusion): Since we're already assuming PostgreSQL 10+ for collations,
+-- it's probably a good idea to use IDENTITY columns instead of SERIAL. They're
+-- also supported with PostgreSQL 10+ and are supposed so fix some shortcommings
+-- of SERIAL.
+
+-- TODO(fusion): SQLite tables didn't use foreign key constraints so I'm also not
+-- using them here. It might be a good idea for consistency, but it's not a hard
+-- requirement.
+
+-- Primary Tables
+--==============================================================================
+CREATE TABLE Worlds (
+    WorldID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
+    Name TEXT NOT NULL COLLATE NOCASE,
+    Type SMALLINT NOT NULL,
+    RebootTime SMALLINT NOT NULL,
+    Host TEXT NOT NULL,
+    Port INTEGER NOT NULL,
+    MaxPlayers SMALLINT NOT NULL,
+    PremiumPlayerBuffer SMALLINT NOT NULL,
+    MaxNewbies SMALLINT NOT NULL,
+    PremiumNewbieBuffer SMALLINT NOT NULL,
+    OnlineRecord SMALLINT NOT NULL DEFAULT 0,
+    OnlineRecordTimestamp TIMESTAMPTZ NOT NULL DEFAULT 'epoch',
+    PRIMARY KEY (WorldID),
+    UNIQUE (Name)
+);
+
+CREATE TABLE Accounts (
+    AccountID INTEGER NOT NULL,
+    Email TEXT NOT NULL COLLATE NOCASE,
+    Auth BYTEA NOT NULL,
+    PremiumEnd TIMESTAMPTZ NOT NULL DEFAULT 'epoch',
+    PendingPremiumDays SMALLINT NOT NULL DEFAULT 0,
+    Deleted BOOLEAN NOT NULL DEFAULT FALSE,
+    PRIMARY KEY (AccountID),
+    UNIQUE (Email)
+);
+
+CREATE TABLE Characters (
+    WorldID INTEGER NOT NULL,
+    CharacterID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
+    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 '',
+    LastLoginTime TIMESTAMPTZ NOT NULL DEFAULT 'epoch',
+    TutorActivities INTEGER NOT NULL DEFAULT 0,
+    IsOnline SMALLINT NOT NULL DEFAULT 0,
+    Deleted BOOLEAN NOT NULL DEFAULT FALSE,
+    PRIMARY KEY (CharacterID),
+    UNIQUE (Name)
+);
+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.
+CREATE TABLE CharacterRights (
+    CharacterID INTEGER NOT NULL,
+    Name TEXT NOT NULL COLLATE NOCASE,
+    PRIMARY KEY(CharacterID, Name)
+);
+
+CREATE TABLE CharacterDeaths (
+    CharacterID INTEGER NOT NULL,
+    Level SMALLINT NOT NULL,
+    OffenderID INTEGER NOT NULL,
+    Remark TEXT NOT NULL,
+    Unjustified BOOLEAN NOT NULL,
+    Timestamp TIMESTAMPTZ NOT NULL
+);
+CREATE INDEX CharacterDeathsCharacterIndex ON CharacterDeaths(CharacterID, Level);
+CREATE INDEX CharacterDeathsOffenderIndex  ON CharacterDeaths(OffenderID, Unjustified);
+
+CREATE TABLE Buddies (
+    WorldID INTEGER NOT NULL,
+    AccountID INTEGER NOT NULL,
+    BuddyID INTEGER NOT NULL,
+    PRIMARY KEY (WorldID, AccountID, BuddyID)
+);
+
+CREATE TABLE WorldInvitations (
+    WorldID INTEGER NOT NULL,
+    CharacterID INTEGER NOT NULL,
+    PRIMARY KEY (WorldID, CharacterID)
+);
+
+CREATE TABLE LoginAttempts (
+    AccountID INTEGER NOT NULL,
+    IPAddress INET NOT NULL,
+    Timestamp TIMESTAMPTZ NOT NULL,
+    Failed BOOLEAN NOT NULL
+);
+CREATE INDEX LoginAttemptsAccountIndex ON LoginAttempts(AccountID, Timestamp);
+CREATE INDEX LoginAttemptsAddressIndex ON LoginAttempts(IPAddress, Timestamp);
+
+-- House Tables
+--==============================================================================
+CREATE TABLE Houses (
+    WorldID INTEGER NOT NULL,
+    HouseID INTEGER NOT NULL,
+    Name TEXT NOT NULL,
+    Rent INTEGER NOT NULL,
+    Description TEXT NOT NULL,
+    Size INTEGER NOT NULL,
+    PositionX INTEGER NOT NULL,
+    PositionY INTEGER NOT NULL,
+    PositionZ INTEGER NOT NULL,
+    Town TEXT NOT NULL,
+    GuildHouse BOOLEAN NOT NULL,
+    PRIMARY KEY (WorldID, HouseID)
+);
+
+CREATE TABLE HouseOwners (
+    WorldID INTEGER NOT NULL,
+    HouseID INTEGER NOT NULL,
+    OwnerID INTEGER NOT NULL,
+    PaidUntil TIMESTAMPTZ NOT NULL,
+    PRIMARY KEY (WorldID, HouseID)
+);
+
+-- NOTE(fusion): Auctions with a NULL `FinishTime` aren't active, to avoid running
+-- multiple times with no actual bidder. It should be set after the first bid along
+-- with `BidderID` and `BidAmount`.
+CREATE TABLE HouseAuctions (
+    WorldID INTEGER NOT NULL,
+    HouseID INTEGER NOT NULL,
+    BidderID INTEGER DEFAULT NULL,
+    BidAmount INTEGER DEFAULT NULL,
+    FinishTime TIMESTAMPTZ DEFAULT NULL,
+    PRIMARY KEY (WorldID, HouseID)
+);
+
+CREATE TABLE HouseTransfers (
+    WorldID INTEGER NOT NULL,
+    HouseID INTEGER NOT NULL,
+    NewOwnerID INTEGER NOT NULL,
+    Price INTEGER NOT NULL,
+    PRIMARY KEY (WorldID, HouseID)
+);
+
+CREATE TABLE HouseAuctionExclusions (
+    CharacterID INTEGER NOT NULL,
+    Issued TIMESTAMPTZ NOT NULL,
+    Until TIMESTAMPTZ NOT NULL,
+    BanishmentID INTEGER NOT NULL
+);
+CREATE INDEX HouseAuctionExclusionsIndex ON HouseAuctionExclusions(CharacterID, Until);
+
+CREATE TABLE HouseAssignments (
+    WorldID INTEGER NOT NULL,
+    HouseID INTEGER NOT NULL,
+    OwnerID INTEGER NOT NULL,
+    Price INTEGER NOT NULL,
+    Timestamp TIMESTAMPTZ NOT NULL
+);
+CREATE INDEX HouseAssignmentsHouseIndex ON HouseAssignments(WorldID, HouseID);
+CREATE INDEX HouseAssignmentsTimeIndex  ON HouseAssignments(WorldID, Timestamp);
+CREATE INDEX HouseAssignmentsOwnerIndex ON HouseAssignments(OwnerID);
+
+-- Banishment Tables
+--==============================================================================
+CREATE TABLE Banishments (
+    BanishmentID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
+    AccountID INTEGER NOT NULL,
+    IPAddress INET NOT NULL,
+    GamemasterID INTEGER NOT NULL,
+    Reason TEXT NOT NULL,
+    Comment TEXT NOT NULL,
+    FinalWarning BOOLEAN NOT NULL,
+    Issued TIMESTAMPTZ NOT NULL,
+    Until TIMESTAMPTZ NOT NULL,
+    PRIMARY KEY (BanishmentID)
+);
+CREATE INDEX BanishmentsAccountIndex ON Banishments(AccountID, Until, FinalWarning);
+
+CREATE TABLE IPBanishments (
+    CharacterID INTEGER NOT NULL,
+    IPAddress INET NOT NULL,
+    GamemasterID INTEGER NOT NULL,
+    Reason TEXT NOT NULL,
+    Comment TEXT NOT NULL,
+    Issued TIMESTAMPTZ NOT NULL,
+    Until TIMESTAMPTZ NOT NULL
+);
+CREATE INDEX IPBanishmentsAddressIndex   ON IPBanishments(IPAddress);
+CREATE INDEX IPBanishmentsCharacterIndex ON IPBanishments(CharacterID);
+
+CREATE TABLE Namelocks (
+    CharacterID INTEGER NOT NULL,
+    IPAddress INET NOT NULL,
+    GamemasterID INTEGER NOT NULL,
+    Reason TEXT NOT NULL,
+    Comment TEXT NOT NULL,
+    Attempts INTEGER NOT NULL DEFAULT 0,
+    Approved BOOLEAN NOT NULL DEFAULT FALSE,
+    PRIMARY KEY (CharacterID)
+);
+
+CREATE TABLE Notations (
+    CharacterID INTEGER NOT NULL,
+    IPAddress INET NOT NULL,
+    GamemasterID INTEGER NOT NULL,
+    Reason TEXT NOT NULL,
+    Comment TEXT NOT NULL
+);
+CREATE INDEX NotationsCharacterIndex ON Notations(CharacterID);
+
+CREATE TABLE Statements (
+    WorldID INTEGER NOT NULL,
+    Timestamp TIMESTAMPTZ NOT NULL,
+    StatementID INTEGER NOT NULL,
+    CharacterID INTEGER NOT NULL,
+    Channel TEXT NOT NULL,
+    Text TEXT NOT NULL,
+    PRIMARY KEY (WorldID, Timestamp, StatementID)
+);
+CREATE INDEX StatementsCharacterIndex ON Statements(CharacterID, Timestamp);
+
+CREATE TABLE ReportedStatements (
+    WorldID INTEGER NOT NULL,
+    Timestamp TIMESTAMPTZ NOT NULL,
+    StatementID INTEGER NOT NULL,
+    CharacterID INTEGER NOT NULL,
+    BanishmentID INTEGER NOT NULL,
+    ReporterID INTEGER NOT NULL,
+    Reason TEXT NOT NULL,
+    Comment TEXT NOT NULL,
+    PRIMARY KEY (WorldID, Timestamp, StatementID)
+);
+CREATE INDEX ReportedStatementsCharacterIndex  ON ReportedStatements(CharacterID, Timestamp);
+CREATE INDEX ReportedStatementsBanishmentIndex ON ReportedStatements(BanishmentID);
+
+-- Info Tables
+--==============================================================================
+CREATE TABLE KillStatistics (
+    WorldID INTEGER NOT NULL,
+    RaceName TEXT NOT NULL COLLATE NOCASE,
+    TimesKilled INTEGER NOT NULL,
+    PlayersKilled INTEGER NOT NULL,
+    PRIMARY KEY (WorldID, RaceName)
+);
+
+CREATE TABLE OnlineCharacters (
+    WorldID INTEGER NOT NULL,
+    Name TEXT NOT NULL COLLATE NOCASE,
+    Level SMALLINT NOT NULL,
+    Profession TEXT NOT NULL,
+    PRIMARY KEY (WorldID, Name)
+);
+
+-- Schema Info
+--==============================================================================
+-- NOTE(fusion): The `SchemaInfo` table should hold information about the schema
+-- and be used for consistency checks at startup. It currently only contains the
+-- schema version, which I feel is the only value needed.
+CREATE TABLE SchemaInfo (
+    Key TEXT NOT NULL COLLATE NOCASE,
+    Value TEXT NOT NULL,
+    PRIMARY KEY (Key)
+);
+
+INSERT INTO SchemaInfo (Key, Value) VALUES ('VERSION', '1');
+
+COMMIT;