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

     1  -- +goose Up
     2  
     3  create type stop_order_expiry_strategy as enum('EXPIRY_STRATEGY_UNSPECIFIED', 'EXPIRY_STRATEGY_CANCELS', 'EXPIRY_STRATEGY_SUBMIT');
     4  create type stop_order_trigger_direction as enum('TRIGGER_DIRECTION_UNSPECIFIED', 'TRIGGER_DIRECTION_RISES_ABOVE', 'TRIGGER_DIRECTION_FALLS_BELOW');
     5  create type stop_order_status as enum('STATUS_UNSPECIFIED', 'STATUS_PENDING', 'STATUS_CANCELLED', 'STATUS_STOPPED', 'STATUS_TRIGGERED', 'STATUS_EXPIRED', 'STATUS_REJECTED');
     6  
     7  create table if not exists stop_orders(
     8      id bytea not null,
     9      oco_link_id bytea,
    10      expires_at timestamp with time zone,
    11      expiry_strategy stop_order_expiry_strategy not null,
    12      trigger_direction stop_order_trigger_direction not null,
    13      status stop_order_status not null,
    14      created_at timestamp with time zone not null,
    15      updated_at timestamp with time zone,
    16      order_id bytea not null,
    17      trigger_price text,
    18      trigger_percent_offset text,
    19      party_id bytea not null,
    20      market_id bytea not null,
    21      vega_time timestamp with time zone not null,
    22      seq_num bigint not null,
    23      tx_hash bytea not null,
    24      submission jsonb,
    25      primary key(vega_time, seq_num)
    26  );
    27  
    28  create index idx_stop_orders_id_vega_time_seq_num on stop_orders(id, vega_time desc, seq_num desc);
    29  create index idx_stop_orders_created_at_id_vega_time_seq_num on stop_orders(created_at desc, id, vega_time desc, seq_num desc);
    30  create index idx_stop_orders_market_id_created_at_id_vega_time_seq_num on stop_orders(market_id, created_at desc, id, vega_time desc, seq_num desc);
    31  create index idx_stop_orders_party_id_created_at_id_vega_time_seq_num on stop_orders(party_id, created_at desc, id, vega_time desc, seq_num desc);
    32  
    33  create or replace view stop_orders_current_desc
    34  as
    35      select distinct on (so.created_at, so.id) *
    36      from stop_orders so
    37      order by so.created_at desc, so.id, so.vega_time desc, so.seq_num desc;
    38  
    39  create or replace view stop_orders_current_desc_by_market
    40  as
    41      select distinct on (so.created_at, so.market_id, so.id) *
    42      from stop_orders so
    43      order by so.created_at desc, so.market_id, so.id, so.vega_time desc, so.seq_num desc;
    44  
    45  create or replace view stop_orders_current_desc_by_party
    46  as
    47  select distinct on (so.created_at, so.party_id, so.id) *
    48  from stop_orders so
    49  order by so.created_at desc, so.party_id, so.id, so.vega_time desc, so.seq_num desc;
    50  
    51  -- +goose Down
    52  
    53  drop view if exists stop_orders_current_desc_by_party;
    54  drop view if exists stop_orders_current_desc_by_market;
    55  drop view if exists stop_orders_current_desc;
    56  drop index if exists idx_stop_orders_party_id_created_at_id_vega_time_seq_num;
    57  drop index if exists idx_stop_orders_market_id_created_at_id_vega_time_seq_num;
    58  drop index if exists idx_stop_orders_created_at_id_vega_time_seq_num;
    59  drop index if exists idx_stop_orders_id_vega_time_seq_num;
    60  drop table if exists stop_orders;
    61  drop type if exists stop_order_status;
    62  drop type if exists stop_order_trigger_direction;
    63  drop type if exists stop_order_expiry_strategy;