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

     1  -- +goose Up
     2  
     3  ALTER TABLE market_data ADD COLUMN last_traded_price HUGEINT;
     4  ALTER TABLE current_market_data ADD COLUMN last_traded_price HUGEINT;
     5  
     6  UPDATE market_data SET last_traded_price = 0;
     7  UPDATE current_market_data SET last_traded_price = 0;
     8  
     9  -- +goose StatementBegin
    10  CREATE OR REPLACE FUNCTION update_current_market_data()
    11      RETURNS TRIGGER
    12      LANGUAGE PLPGSQL AS
    13  $$
    14  BEGIN
    15      INSERT INTO current_market_data(synthetic_time,tx_hash,vega_time,seq_num,market,mark_price,best_bid_price,best_bid_volume,
    16                                      best_offer_price,best_offer_volume,best_static_bid_price,best_static_bid_volume,
    17                                      best_static_offer_price,best_static_offer_volume,mid_price,static_mid_price,open_interest,
    18                                      auction_end,auction_start,indicative_price,indicative_volume,market_trading_mode,
    19                                      auction_trigger,extension_trigger,target_stake,supplied_stake,price_monitoring_bounds,
    20                                      market_value_proxy,liquidity_provider_fee_shares,market_state,next_mark_to_market, market_growth, last_traded_price)
    21      VALUES(NEW.synthetic_time, NEW.tx_hash, NEW.vega_time, NEW.seq_num, NEW.market,
    22             NEW.mark_price, NEW.best_bid_price, NEW.best_bid_volume, NEW.best_offer_price,
    23             NEW.best_offer_volume, NEW.best_static_bid_price, NEW.best_static_bid_volume,
    24             NEW.best_static_offer_price, NEW.best_static_offer_volume, NEW.mid_price,
    25             NEW.static_mid_price, NEW.open_interest, NEW.auction_end, NEW.auction_start,
    26             NEW.indicative_price, NEW.indicative_volume, NEW.market_trading_mode,
    27             NEW.auction_trigger, NEW.extension_trigger, NEW.target_stake, NEW.supplied_stake,
    28             NEW.price_monitoring_bounds, NEW.market_value_proxy,
    29             NEW.liquidity_provider_fee_shares, NEW.market_state, NEW.next_mark_to_market, NEW.market_growth, NEW.last_traded_price)
    30      ON CONFLICT(market) DO UPDATE SET
    31                                        synthetic_time=EXCLUDED.synthetic_time,tx_hash=EXCLUDED.tx_hash,vega_time=EXCLUDED.vega_time,seq_num=EXCLUDED.seq_num,market=EXCLUDED.market,mark_price=EXCLUDED.mark_price,
    32                                        best_bid_price=EXCLUDED.best_bid_price,best_bid_volume=EXCLUDED.best_bid_volume,best_offer_price=EXCLUDED.best_offer_price,best_offer_volume=EXCLUDED.best_offer_volume,
    33                                        best_static_bid_price=EXCLUDED.best_static_bid_price,best_static_bid_volume=EXCLUDED.best_static_bid_volume,best_static_offer_price=EXCLUDED.best_static_offer_price,
    34                                        best_static_offer_volume=EXCLUDED.best_static_offer_volume,mid_price=EXCLUDED.mid_price,static_mid_price=EXCLUDED.static_mid_price,open_interest=EXCLUDED.open_interest,
    35                                        auction_end=EXCLUDED.auction_end,auction_start=EXCLUDED.auction_start,indicative_price=EXCLUDED.indicative_price,indicative_volume=EXCLUDED.indicative_volume,
    36                                        market_trading_mode=EXCLUDED.market_trading_mode,auction_trigger=EXCLUDED.auction_trigger,extension_trigger=EXCLUDED.extension_trigger,target_stake=EXCLUDED.target_stake,
    37                                        supplied_stake=EXCLUDED.supplied_stake,price_monitoring_bounds=EXCLUDED.price_monitoring_bounds,
    38                                        market_value_proxy=EXCLUDED.market_value_proxy,liquidity_provider_fee_shares=EXCLUDED.liquidity_provider_fee_shares,market_state=EXCLUDED.market_state,
    39                                        next_mark_to_market=EXCLUDED.next_mark_to_market,market_growth=EXCLUDED.market_growth,last_traded_price=EXCLUDED.last_traded_price;
    40  
    41      RETURN NULL;
    42  END;
    43  $$;
    44  -- +goose StatementEnd
    45  
    46  -- +goose Down
    47  
    48  ALTER TABLE market_data DROP COLUMN IF EXISTS last_traded_price;
    49  ALTER TABLE current_market_data DROP COLUMN IF EXISTS last_traded_price;
    50  
    51  -- +goose StatementBegin
    52  CREATE OR REPLACE FUNCTION update_current_market_data()
    53      RETURNS TRIGGER
    54      LANGUAGE PLPGSQL AS
    55  $$
    56  BEGIN
    57      INSERT INTO current_market_data(synthetic_time,tx_hash,vega_time,seq_num,market,mark_price,best_bid_price,best_bid_volume,
    58                                      best_offer_price,best_offer_volume,best_static_bid_price,best_static_bid_volume,
    59                                      best_static_offer_price,best_static_offer_volume,mid_price,static_mid_price,open_interest,
    60                                      auction_end,auction_start,indicative_price,indicative_volume,market_trading_mode,
    61                                      auction_trigger,extension_trigger,target_stake,supplied_stake,price_monitoring_bounds,
    62                                      market_value_proxy,liquidity_provider_fee_shares,market_state,next_mark_to_market,market_growth)
    63      VALUES(NEW.synthetic_time, NEW.tx_hash, NEW.vega_time, NEW.seq_num, NEW.market,
    64             NEW.mark_price, NEW.best_bid_price, NEW.best_bid_volume, NEW.best_offer_price,
    65             NEW.best_offer_volume, NEW.best_static_bid_price, NEW.best_static_bid_volume,
    66             NEW.best_static_offer_price, NEW.best_static_offer_volume, NEW.mid_price,
    67             NEW.static_mid_price, NEW.open_interest, NEW.auction_end, NEW.auction_start,
    68             NEW.indicative_price, NEW.indicative_volume, NEW.market_trading_mode,
    69             NEW.auction_trigger, NEW.extension_trigger, NEW.target_stake, NEW.supplied_stake,
    70             NEW.price_monitoring_bounds, NEW.market_value_proxy,
    71             NEW.liquidity_provider_fee_shares, NEW.market_state, NEW.next_mark_to_market, NEW.market_growth)
    72      ON CONFLICT(market) DO UPDATE SET
    73                                        synthetic_time=EXCLUDED.synthetic_time,tx_hash=EXCLUDED.tx_hash,vega_time=EXCLUDED.vega_time,seq_num=EXCLUDED.seq_num,market=EXCLUDED.market,mark_price=EXCLUDED.mark_price,
    74                                        best_bid_price=EXCLUDED.best_bid_price,best_bid_volume=EXCLUDED.best_bid_volume,best_offer_price=EXCLUDED.best_offer_price,best_offer_volume=EXCLUDED.best_offer_volume,
    75                                        best_static_bid_price=EXCLUDED.best_static_bid_price,best_static_bid_volume=EXCLUDED.best_static_bid_volume,best_static_offer_price=EXCLUDED.best_static_offer_price,
    76                                        best_static_offer_volume=EXCLUDED.best_static_offer_volume,mid_price=EXCLUDED.mid_price,static_mid_price=EXCLUDED.static_mid_price,open_interest=EXCLUDED.open_interest,
    77                                        auction_end=EXCLUDED.auction_end,auction_start=EXCLUDED.auction_start,indicative_price=EXCLUDED.indicative_price,indicative_volume=EXCLUDED.indicative_volume,
    78                                        market_trading_mode=EXCLUDED.market_trading_mode,auction_trigger=EXCLUDED.auction_trigger,extension_trigger=EXCLUDED.extension_trigger,target_stake=EXCLUDED.target_stake,
    79                                        supplied_stake=EXCLUDED.supplied_stake,price_monitoring_bounds=EXCLUDED.price_monitoring_bounds,
    80                                        market_value_proxy=EXCLUDED.market_value_proxy,liquidity_provider_fee_shares=EXCLUDED.liquidity_provider_fee_shares,market_state=EXCLUDED.market_state,
    81                                        next_mark_to_market=EXCLUDED.next_mark_to_market,market_growth=EXCLUDED.market_growth;
    82  
    83      RETURN NULL;
    84  END;
    85  $$;
    86  -- +goose StatementEnd