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

     1  -- +goose Up
     2  
     3  -- Carry over all reward totals that are missing across epochs.
     4  -- +goose StatementBegin
     5  do $$
     6  declare
     7      first_epoch bigint;
     8      last_epoch bigint;
     9  begin
    10      select min(epoch_id), max(epoch_id) into first_epoch, last_epoch from game_reward_totals;
    11      if first_epoch is null or last_epoch is null then
    12          return;
    13      end if;
    14  
    15      -- for each epoch, we want to carry over rewards that exist in this epoch, but not in the next
    16      -- we have to do it one epoch at a time, so that the carried forward rewards can continue to be carried
    17      -- over to the next epoch as well.
    18      for epoch in first_epoch..last_epoch-1 loop
    19  
    20          INSERT INTO game_reward_totals(game_id, party_id, asset_id, market_id, team_id, total_rewards, total_rewards_quantum, epoch_id)
    21          SELECT DISTINCT -- we need distinct because the game ID is a hash of the dispatch metric on the transfer and these can be duplicated across multiple transfers when they are cancelled and replaced, or have been rejected at some point.
    22              grto.game_id,
    23              grto.party_id,
    24              grto.asset_id,
    25              grto.market_id,
    26              grto.team_id,
    27              grto.total_rewards,
    28              grto.total_rewards_quantum,
    29              (grto.epoch_id + 1) AS epoch_id
    30          FROM game_reward_totals AS grto
    31              -- get the game end date from the transfer table and do not carry over rewards for games that have ended
    32          JOIN transfers_current t on grto.game_id = t.game_id and t.transfer_type = 'Recurring' and t.end_epoch > grto.epoch_id
    33          WHERE grto.epoch_id = epoch
    34          AND NOT EXISTS (
    35              SELECT 1
    36              FROM game_reward_totals AS grtc
    37              WHERE grtc.party_id = grto.party_id
    38              AND grtc.asset_id = grto.asset_id
    39              AND grtc.market_id = grto.market_id
    40              AND grtc.game_id = grto.game_id
    41              AND grtc.team_id = grto.team_id
    42              AND grtc.epoch_id = grto.epoch_id + 1
    43          );
    44      end loop;
    45  
    46  end;
    47  $$;
    48  -- +goose StatementEnd
    49  
    50  
    51  
    52  -- Create a function to carry over data between 2 given epochs.
    53  -- +goose StatementBegin
    54  CREATE OR REPLACE FUNCTION carry_over_rewards_on_epoch()
    55     RETURNS TRIGGER
    56     LANGUAGE PLPGSQL AS
    57  $$
    58  BEGIN
    59      INSERT INTO game_reward_totals (game_id, party_id, asset_id, market_id, team_id, total_rewards, total_rewards_quantum, epoch_id)
    60          SELECT
    61              grto.game_id,
    62              grto.party_id,
    63              grto.asset_id,
    64              grto.market_id,
    65              grto.team_id,
    66              grto.total_rewards,
    67              grto.total_rewards_quantum,
    68              (NEW.id - 1) AS epoch_id
    69          FROM game_reward_totals AS grto
    70           -- get the game end date from the transfer table and do not carry over rewards for games that have ended
    71           JOIN transfers t on grto.game_id = t.game_id and t.end_epoch > grto.epoch_id
    72          WHERE grto.epoch_id = (NEW.id - 2)
    73          AND NOT EXISTS (
    74              SELECT 1
    75              FROM game_reward_totals AS grtc
    76              WHERE grtc.party_id =  grto.party_id
    77              AND grtc.game_id = grto.game_id
    78              AND grtc.asset_id = grto.asset_id
    79              AND grtc.market_id = grto.market_id
    80              AND grtc.team_id = grto.team_id
    81              AND grtc.epoch_id = (NEW.id - 1)
    82          );
    83  RETURN NEW;
    84  END;
    85  $$;
    86  -- +goose StatementEnd
    87  
    88  -- add trigger to the epochs table
    89  CREATE OR REPLACE TRIGGER carry_over_epoch_data
    90      AFTER INSERT
    91      ON epochs
    92      FOR EACH STATEMENT
    93      EXECUTE FUNCTION carry_over_rewards_on_epoch();
    94  
    95  -- +goose Down
    96  
    97  DROP TRIGGER IF EXISTS carry_over_epoch_data ON epochs;
    98  
    99  DROP FUNCTION IF EXISTS carry_over_rewards_on_epoch();