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

     1  -- +goose Up
     2  
     3  -- create the history and current tables for locked and vesting balances
     4  create table if not exists party_locked_balances (
     5         party_id bytea not null,
     6         asset_id bytea not null,
     7         at_epoch bigint not null,
     8         until_epoch bigint not null,
     9         balance hugeint not null,
    10         vega_time timestamp with time zone not null,
    11         primary key (vega_time, party_id, asset_id, until_epoch)
    12  );
    13  
    14  select create_hypertable('party_locked_balances', 'vega_time', chunk_time_interval => INTERVAL '1 day');
    15  
    16  create table if not exists party_locked_balances_current (
    17         party_id bytea not null,
    18         asset_id bytea not null,
    19         at_epoch bigint not null,
    20         until_epoch bigint not null,
    21         balance hugeint not null,
    22         vega_time timestamp with time zone not null,
    23         primary key (party_id, asset_id, until_epoch)
    24  );
    25  
    26  create table if not exists party_vesting_balances (
    27         party_id bytea not null,
    28         asset_id bytea not null,
    29         at_epoch bigint not null,
    30         balance hugeint not null,
    31         vega_time timestamp with time zone not null,
    32         primary key (vega_time, party_id, asset_id)
    33  );
    34  
    35  select create_hypertable('party_vesting_balances', 'vega_time', chunk_time_interval => INTERVAL '1 day');
    36  
    37  create table if not exists party_vesting_balances_current (
    38         party_id bytea not null,
    39         asset_id bytea not null,
    40         at_epoch bigint not null,
    41         balance hugeint not null,
    42         vega_time timestamp with time zone not null,
    43         primary key (party_id, asset_id)
    44  );
    45  
    46  -- create the trigger functions and triggers
    47  -- +goose StatementBegin
    48  create or replace function update_party_locked_balances()
    49         returns trigger
    50         language plpgsql
    51  as $$
    52     begin
    53          insert into party_locked_balances_current(party_id, asset_id, at_epoch, until_epoch, balance, vega_time)
    54          values (new.party_id, new.asset_id, new.at_epoch, new.until_epoch, new.balance, new.vega_time)
    55          on conflict(party_id, asset_id, until_epoch)
    56          do update set
    57             at_epoch = excluded.at_epoch,
    58             until_epoch = excluded.until_epoch,
    59             balance = excluded.balance,
    60             vega_time = excluded.vega_time;
    61          return null;
    62     end;
    63  $$;
    64  -- +goose StatementEnd
    65  
    66  create trigger update_party_locked_balances
    67      after insert or update
    68      on party_locked_balances
    69      for each row execute function update_party_locked_balances();
    70  
    71  -- +goose StatementBegin
    72  create or replace function update_party_vesting_balances()
    73         returns trigger
    74         language plpgsql
    75  as $$
    76  begin
    77  insert into party_vesting_balances_current(party_id, asset_id, at_epoch, balance, vega_time)
    78  values (new.party_id, new.asset_id, new.at_epoch, new.balance, new.vega_time)
    79      on conflict(party_id, asset_id)
    80          do update set
    81              at_epoch = excluded.at_epoch,
    82              balance = excluded.balance,
    83              vega_time = excluded.vega_time;
    84      return null;
    85  end;
    86  $$;
    87  -- +goose StatementEnd
    88  
    89  create trigger update_party_vesting_balances
    90      after insert or update
    91      on party_vesting_balances
    92      for each row execute function update_party_vesting_balances();
    93  
    94  -- +goose Down
    95  
    96  drop table if exists party_vesting_balances_current;
    97  drop table if exists party_vesting_balances;
    98  drop table if exists party_locked_balances_current;
    99  drop table if exists party_locked_balances;
   100  
   101  drop function if exists update_party_locked_balances;
   102  drop function if exists update_party_vesting_balances;