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

     1  -- +goose Up
     2  
     3  create or replace view stop_orders_current_desc
     4  as
     5      select distinct on (so.created_at, so.id) *
     6      from stop_orders so
     7      order by so.created_at desc, so.id, so.vega_time desc, so.seq_num desc;
     8  
     9  create or replace view stop_orders_current_desc_by_market
    10  as
    11      select distinct on (so.created_at, so.market_id, so.id) *
    12      from stop_orders so
    13      order by so.created_at desc, so.market_id, so.id, so.vega_time desc, so.seq_num desc;
    14  
    15  create or replace view stop_orders_current_desc_by_party
    16  as
    17  select distinct on (so.created_at, so.party_id, so.id) *
    18  from stop_orders so
    19  order by so.created_at desc, so.party_id, so.id, so.vega_time desc, so.seq_num desc;
    20  
    21  alter table stop_orders_live
    22      add column if not exists size_override_setting int not null default 0,
    23      add column if not exists size_override_value varchar null;
    24  
    25  -- +goose StatementBegin
    26  create or replace function stop_orders_live_insert_trigger()
    27  returns trigger
    28      language plpgsql
    29      as $$
    30  begin
    31      delete from stop_orders_live
    32      where id = new.id;
    33  
    34      if new.status in ('STATUS_UNSPECIFIED', 'STATUS_PENDING') then
    35          insert into stop_orders_live
    36          values (new.id, new.oco_link_id, new.expires_at, new.expiry_strategy, new.trigger_direction, new.status,
    37                  new.created_at, new.updated_at, new.order_id, new.trigger_price, new.trigger_percent_offset, new.party_id,
    38                  new.market_id, new.vega_time, new.seq_num, new.tx_hash, new.submission, new.size_override_setting, new.size_override_value);
    39      end if;
    40  
    41      return new;
    42  end;
    43  $$;
    44  
    45  -- +goose StatementEnd
    46  
    47  drop trigger if exists stop_orders_live_insert_trigger on stop_orders;
    48  create trigger stop_orders_live_insert_trigger before insert on stop_orders for each row execute function stop_orders_live_insert_trigger();
    49  
    50  -- +goose Down
    51  
    52  -- restore the views to how they were befpre the migration or network history may fail due to migrations failing
    53  drop view if exists stop_orders_current_desc;
    54  create view stop_orders_current_desc
    55  as
    56  select distinct on (so.created_at, so.id) id, oco_link_id, expires_at, expiry_strategy, trigger_direction, status, created_at,
    57          updated_at, order_id, trigger_price, trigger_percent_offset, party_id, market_id, vega_time, seq_num, tx_hash, submission
    58          from stop_orders so
    59          order by so.created_at desc, so.id, so.vega_time desc, so.seq_num desc;
    60  
    61  drop view if exists stop_orders_current_desc_by_market;
    62  create view stop_orders_current_desc_by_market
    63  as
    64  select distinct on (so.created_at, so.market_id, so.id)id, oco_link_id, expires_at, expiry_strategy, trigger_direction, status, created_at,
    65          updated_at, order_id, trigger_price, trigger_percent_offset, party_id, market_id, vega_time, seq_num, tx_hash, submission
    66          from stop_orders so
    67          order by so.created_at desc, so.market_id, so.id, so.vega_time desc, so.seq_num desc;
    68  
    69  drop view if exists stop_orders_current_desc_by_party;
    70  create view stop_orders_current_desc_by_party
    71  as
    72  select distinct on (so.created_at, so.party_id, so.id)id, oco_link_id, expires_at, expiry_strategy, trigger_direction, status, created_at,
    73          updated_at, order_id, trigger_price, trigger_percent_offset, party_id, market_id, vega_time, seq_num, tx_hash, submission
    74          from stop_orders so
    75          order by so.created_at desc, so.party_id, so.id, so.vega_time desc, so.seq_num desc;