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

     1  -- +goose Up
     2  
     3  -- create the history and current tables for parties vestng stats
     4  create table if not exists party_vesting_stats (
     5         party_id bytea not null,
     6         at_epoch bigint not null,
     7         reward_bonus_multiplier NUMERIC(1000, 16) not null,
     8         quantum_balance NUMERIC(1000, 16) not null,
     9         vega_time timestamp with time zone not null,
    10         primary key (vega_time, party_id)
    11  );
    12  
    13  select create_hypertable('party_vesting_stats', 'vega_time', chunk_time_interval => INTERVAL '1 day');
    14  
    15  create table if not exists party_vesting_stats_current (
    16         party_id bytea not null,
    17         at_epoch bigint not null,
    18         reward_bonus_multiplier NUMERIC(1000, 16) not null,
    19         quantum_balance NUMERIC(1000, 16) not null,
    20         vega_time timestamp with time zone not null,
    21         primary key (party_id)
    22  );
    23  
    24  -- create the trigger functions and triggers
    25  -- +goose StatementBegin
    26  create or replace function update_party_vesting_stats()
    27         returns trigger
    28         language plpgsql
    29  as $$
    30     begin
    31          insert into party_vesting_stats_current(party_id, at_epoch, reward_bonus_multiplier, quantum_balance, vega_time)
    32          values (new.party_id, new.at_epoch, new.reward_bonus_multiplier, new.quantum_balance, new.vega_time)
    33          on conflict(party_id)
    34          do update set
    35             at_epoch = excluded.at_epoch,
    36             reward_bonus_multiplier = excluded.reward_bonus_multiplier,
    37  	   quantum_balance = excluded.quantum_balance,
    38  	   vega_time = excluded.vega_time;
    39          return null;
    40     end;
    41  $$;
    42  -- +goose StatementEnd
    43  
    44  create trigger update_party_vesting_stats
    45      after insert or update
    46      on party_vesting_stats
    47      for each row execute function update_party_vesting_stats();
    48  
    49  -- +goose Down
    50  
    51  drop table if exists party_vesting_stats_current;
    52  drop table if exists party_vesting_stats;
    53  
    54  drop function if exists update_party_vesting_stats;