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

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