schema.sql

CREATE DATABASE jpworgen;

CREATE TABLE IF NOT EXISTS jpworgen.ActiveItems
(
    UpdateTime DateTime('UTC') CODEC (DoubleDelta),
    ScanTime   DateTime('UTC'),
    RealmId    UInt16,
    Namespace  LowCardinality(String),
    Faction    Enum8('Alliance' = 0, 'Horde' = 1),
    AuctionId  UInt32,
    ItemId     UInt32,
    Bid        UInt32,
    Buyout     UInt32,
    Quantity   UInt16,
    TimeLeft   Enum8('SHORT' = 3, 'MEDIUM' = 2, 'LONG' = 1, 'VERY_LONG' = 0)
) ENGINE = ReplacingMergeTree(UpdateTime)
      TTL UpdateTime + INTERVAL 30 DAY
      ORDER BY (UpdateTime, Namespace, RealmId, Faction, AuctionId);

CREATE TABLE IF NOT EXISTS jpworgen.Aggregations
(
    HouseName      LowCardinality(String),
    RealmId        UInt16,
    Namespace      LowCardinality(String),
    UpdateTime     DateTime('UTC') CODEC (DoubleDelta),
    ScanTime       DateTime('UTC'),
    Faction        Enum8('Alliance' = 0, 'Horde' = 1),
    Population     Enum8('Low' = 0, 'Medium' = 1, 'High' = 2, 'Full' = 3),
    ItemId         UInt32,
    TotalQuantity  UInt32,
    MedianQuantity UInt16,
    MedianBuyout   UInt32,
    MinBuyout      UInt32,
    MaxBuyout      UInt32,
    NumPosts       UInt32
) ENGINE = MergeTree
      ORDER BY (UpdateTime, HouseName, ItemId)
      PARTITION BY toYYYYMM(UpdateTime);

CREATE TABLE IF NOT EXISTS jpworgen.AuctHouses
(
    Id            UInt16,
    Namespace     String,
    Population    String,
    Region        String,
    DisplayRegion String,
    Name          String,
    Category      String,
    Locale        String,
    Timezone      String,
    RealmType     String,
    IsTournament  UInt8,
    Slug          String,
    HouseName     String,
    Faction       Enum8('Alliance' = 0, 'Horde' = 1)
) ENGINE = Join(ANY, LEFT, Id, Faction);

CREATE TABLE IF NOT EXISTS jpworgen.ItemInfo
(
    ItemId        UInt32,
    Name          String,
    Quality       LowCardinality(String),
    Level         UInt8,
    IconUrl       String,
    ItemClass     LowCardinality(String),
    ItemSubclass  LowCardinality(String),
    InventoryType LowCardinality(String)
) ENGINE = Join(ANY, LEFT, ItemId);

CREATE TABLE IF NOT EXISTS jpworgen.Realms
(
    Id            UInt16,
    Namespace     String,
    Population    Enum8('Low' = 0, 'Medium' = 1, 'High' = 2, 'Full' = 3),
    Region        String,
    DisplayRegion String,
    Name          String,
    Category      String,
    Locale        String,
    Timezone      String,
    RealmType     String,
    IsTournament  UInt8,
    Slug          String
) ENGINE = Join(ANY, LEFT, Id)

CREATE TABLE IF NOT EXISTS jpworgen.UpdateSentinels
(
    Namespace String,
    RealmId   UInt16,
    Faction   Enum8('Alliance' = 0, 'Horde' = 1)
) ENGINE = TinyLog;

CREATE TABLE IF NOT EXISTS jpworgen.SoldAuctions
(
    HouseName        LowCardinality(String),
    SoldTime         DateTime('UTC') CODEC (DoubleDelta),
    LatestUpdateTime DateTime('UTC') CODEC (DoubleDelta),
    PreciseSoldTime  DateTime('UTC') CODEC (ZSTD),
    ItemId           UInt32 CODEC (T64),
    Namespace        LowCardinality(String),
    MinBuyout        UInt32 CODEC (ZSTD),
    AvgBuyout        UInt32 CODEC (ZSTD),
    MedBuyout        UInt32 CODEC (ZSTD),
    MaxBuyout        UInt32 CODEC (ZSTD),
    MedQuantity      UInt16 CODEC (ZSTD),
    AvgQuantity      Float32 CODEC (ZSTD),
    TotalQuantity    UInt32 CODEC (ZSTD),
    NumPosts         UInt32 CODEC (ZSTD)
) ENGINE = MergeTree
      ORDER BY (SoldTime, HouseName, ItemId)
      PARTITION BY toYYYYMM(SoldTime);

CREATE TABLE IF NOT EXISTS jpworgen.SoldAuctionsIndividual
(
    HouseName          LowCardinality(String),
    UpdateTime         DateTime('UTC') CODEC (DoubleDelta),
    ScanTime           DateTime('UTC') CODEC (ZSTD),
    RealmId            UInt16 CODEC (ZSTD),
    Namespace          LowCardinality(String),
    Faction            Enum8('Alliance' = 0, 'Horde' = 1),
    AuctionId          UInt32 CODEC (T64),
    ItemId             UInt32 CODEC (ZSTD),
    Bid                UInt32 CODEC (ZSTD),
    Buyout             UInt32 CODEC (ZSTD),
    Quantity           UInt16 CODEC (ZSTD),
    TimeLeft           Enum8('SHORT' = 3, 'MEDIUM' = 2, 'LONG' = 1, 'VERY_LONG' = 0),
    LatestUpdateTime   DateTime('UTC') CODEC (DoubleDelta),
    BuyoutPer          UInt32 CODEC (ZSTD),
    LatestUpdateBuyout UInt32 CODEC (ZSTD),
    PercentDiff        Float32 CODEC (ZSTD)
) ENGINE = ReplacingMergeTree
      ORDER BY (UpdateTime, HouseName, ItemId, AuctionId)
      PARTITION BY toYYYYMM(UpdateTime);

INSERT INTO jpworgen.UpdateSentinels (Namespace, RealmId, Faction)
VALUES ('dynamic-classic-us', 4409, 'Alliance'),
       ('dynamic-classic-eu', 4678, 'Alliance'),
       ('dynamic-classic1x-us', 5124, 'Alliance'),
       ('dynamic-classic1x-eu', 5258, 'Alliance');

INSERT INTO jpworgen.AuctHouses
SELECT *,
       concat(Realms.Name, ' - Alliance') AS HouseName,
       'Alliance'                         AS Faction
FROM jpworgen.Realms AS Realms
UNION ALL
SELECT *,
       concat(Realms2.Name, ' - Horde') AS HouseName,
       'Horde'                          AS Faction
FROM jpworgen.Realms AS Realms2;

CREATE FUNCTION FormatGoldNum AS(Input) -> divide(Input, 10000);
CREATE FUNCTION GoldNum AS (Price) -> round(divide(Price, 10000), 2);
CREATE FUNCTION FormatGoldText AS(Price) -> concat(toString(round(divide(Price, 10000), 2)), 'g');
CREATE FUNCTION PercentDiffBuyout AS(CurrentPrice, TargetPrice) -> if(CurrentPrice != 0,
                                                                      round(100.0 * (TargetPrice - CurrentPrice) / CurrentPrice, 2),
                                                                      0);
CREATE FUNCTION FormatPercentText AS(Input) -> concat(toString(Input), '%');


CREATE TABLE jpworgen.NamespaceToDisplayRegion
(
    Namespace String,
    DisplayRegion String
) ENGINE = Join(ANY, LEFT, Namespace);

INSERT INTO jpworgen.NamespaceToDisplayRegion
SELECT DISTINCT Namespace, DisplayRegion
FROM jpworgen.Realms;

CREATE TABLE IF NOT EXISTS jpworgen.AllServers
(
    UpdateTime         DateTime('UTC') CODEC (DoubleDelta),
    Namespace          LowCardinality(String),
    ItemId             UInt32,
    ItemName           String CODEC (ZSTD),
    DisplayRegion      LowCardinality(String),
    MedianBuyout       UInt32 CODEC (T64),
    MinBuyout          UInt32 CODEC (T64)
) ENGINE = ReplacingMergeTree
      ORDER BY (DisplayRegion, UpdateTime, ItemName)
      PARTITION BY toYYYYMM(UpdateTime);

CREATE MATERIALIZED VIEW jpworgen.AllServersView TO jpworgen.AllServers AS
SELECT
       AGG.UpdateTime AS UpdateTime,
       AGG.Namespace AS Namespace,
       AGG.ItemId AS ItemId,
       II.Name AS ItemName,
       NamespaceToDisplayRegion.DisplayRegion AS DisplayRegion,
       AGG.MedianBuyout AS MedianBuyout,
       AGG.MinBuyout AS MinBuyout
    FROM
    (SELECT
           UpdateTime,
           Namespace,
           ItemId,
           toUInt32(median(AGG.MinBuyout)) AS MedianBuyout,
           min(AGG.MinBuyout) AS MinBuyout
    FROM jpworgen.Aggregations AS AGG
    WHERE Namespace != ''
    GROUP BY UpdateTime, Namespace, AGG.ItemId) AS AGG
ANY LEFT JOIN jpworgen.ItemInfo II on AGG.ItemId = II.ItemId
ANY LEFT JOIN jpworgen.NamespaceToDisplayRegion on NamespaceToDisplayRegion.Namespace = AGG.Namespace;