code.vegaprotocol.io/vega@v0.79.0/datanode/sqlstore/migrations/0002_iceberg_orders.sql (about)

     1  -- +goose Up
     2  
     3  ALTER TABLE orders
     4        ADD COLUMN reserved_remaining BIGINT,
     5        ADD COLUMN initial_peak_size BIGINT,
     6        ADD COLUMN minimum_peak_size BIGINT;
     7  
     8  ALTER TABLE orders_live
     9        ADD COLUMN reserved_remaining BIGINT,
    10        ADD COLUMN initial_peak_size BIGINT,
    11        ADD COLUMN minimum_peak_size BIGINT;
    12  
    13  CREATE OR REPLACE VIEW orders_current_versions AS (
    14     SELECT DISTINCT ON (id, version) * FROM orders ORDER BY id, version DESC, vega_time DESC
    15  );
    16  
    17  CREATE OR REPLACE VIEW orders_current_desc
    18   AS
    19  SELECT DISTINCT ON (orders.created_at, orders.id) *
    20  FROM orders
    21  ORDER BY orders.created_at DESC, orders.id, orders.vega_time DESC, orders.seq_num DESC;
    22  
    23  
    24  CREATE OR REPLACE VIEW orders_current_desc_by_market
    25   AS
    26  SELECT DISTINCT ON (orders.created_at, orders.market_id, orders.id) *
    27  FROM orders
    28  ORDER BY orders.created_at DESC, orders.market_id, orders.id, orders.vega_time DESC, orders.seq_num DESC;
    29  
    30  CREATE OR REPLACE VIEW orders_current_desc_by_party
    31  AS
    32  SELECT DISTINCT ON (orders.created_at, orders.party_id, orders.id) *
    33          FROM orders
    34          ORDER BY orders.created_at DESC, orders.party_id, orders.id, orders.vega_time DESC, orders.seq_num DESC;
    35  
    36  CREATE OR REPLACE VIEW orders_current_desc_by_reference
    37  AS
    38  SELECT DISTINCT ON (orders.created_at, orders.reference, orders.id) *
    39          FROM orders
    40          ORDER BY orders.created_at DESC, orders.reference, orders.id, orders.vega_time DESC, orders.seq_num DESC;
    41  
    42  
    43  -- +goose Down
    44  
    45  drop view orders_current_versions;
    46  drop view orders_current_desc;
    47  drop view orders_current_desc_by_reference;
    48  drop view orders_current_desc_by_party;
    49  drop view orders_current_desc_by_market;
    50  
    51  
    52  ALTER TABLE orders
    53        DROP COLUMN IF EXISTS reserved_remaining,
    54        DROP COLUMN IF EXISTS initial_peak_size,
    55        DROP COLUMN IF EXISTS minimum_peak_size;
    56  
    57  ALTER TABLE orders_live
    58        DROP COLUMN IF EXISTS reserved_remaining,
    59        DROP COLUMN IF EXISTS initial_peak_size,
    60        DROP COLUMN IF EXISTS minimum_peak_size;
    61  
    62  CREATE OR REPLACE VIEW orders_current_versions AS (
    63     SELECT DISTINCT ON (id, version) * FROM orders ORDER BY id, version DESC, vega_time DESC
    64  );
    65  
    66  CREATE VIEW orders_current_desc
    67   AS
    68  SELECT DISTINCT ON (orders.created_at, orders.id) *
    69  FROM orders
    70  ORDER BY orders.created_at DESC, orders.id, orders.vega_time DESC, orders.seq_num DESC;
    71  
    72  
    73  CREATE VIEW orders_current_desc_by_market
    74   AS
    75  SELECT DISTINCT ON (orders.created_at, orders.market_id, orders.id) *
    76  FROM orders
    77  ORDER BY orders.created_at DESC, orders.market_id, orders.id, orders.vega_time DESC, orders.seq_num DESC;
    78  
    79  CREATE VIEW orders_current_desc_by_party
    80  AS
    81  SELECT DISTINCT ON (orders.created_at, orders.party_id, orders.id) *
    82          FROM orders
    83          ORDER BY orders.created_at DESC, orders.party_id, orders.id, orders.vega_time DESC, orders.seq_num DESC;
    84  
    85  CREATE VIEW orders_current_desc_by_reference
    86  AS
    87  SELECT DISTINCT ON (orders.created_at, orders.reference, orders.id) *
    88          FROM orders
    89          ORDER BY orders.created_at DESC, orders.reference, orders.id, orders.vega_time DESC, orders.seq_num DESC;