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

     1  -- +goose Up
     2  
     3  -- create the history and current tables for game team scores
     4  create table if not exists game_team_scores (
     5         game_id bytea not null,
     6         team_id bytea not null,
     7         epoch_id bigint not null,
     8         score NUMERIC NOT NULL,
     9         vega_time timestamp with time zone not null,
    10         primary key (vega_time, game_id, team_id)
    11  );
    12  
    13  create table if not exists game_party_scores (
    14         game_id bytea not null,
    15         party_id bytea not null,
    16         team_id bytea,
    17         epoch_id bigint not null,
    18         score NUMERIC NOT NULL,
    19         staking_balance HUGEINT,    
    20  	open_volume HUGEINT,
    21         total_fees_paid HUGEINT not null,
    22         is_eligible boolean,
    23         rank integer,
    24         vega_time timestamp with time zone not null,
    25         primary key (vega_time, game_id, party_id)
    26  );
    27  
    28  
    29  select create_hypertable('game_team_scores', 'vega_time', chunk_time_interval => INTERVAL '1 day');
    30  select create_hypertable('game_party_scores', 'vega_time', chunk_time_interval => INTERVAL '1 day');
    31  
    32  create table if not exists game_team_scores_current (
    33         game_id bytea not null,
    34         team_id bytea not null,
    35         epoch_id bigint not null,
    36         score NUMERIC NOT NULL,
    37         vega_time timestamp with time zone not null,
    38         primary key (game_id, team_id)
    39  );
    40  
    41  create table if not exists game_party_scores_current (
    42         game_id bytea not null,
    43         party_id bytea not null,
    44         team_id bytea,
    45         epoch_id bigint not null,
    46         score NUMERIC NOT NULL,
    47         staking_balance HUGEINT,    
    48         open_volume HUGEINT,
    49         total_fees_paid HUGEINT not null,
    50         is_eligible boolean,
    51         rank integer,
    52         vega_time timestamp with time zone not null,
    53         primary key (game_id, party_id)
    54  );
    55  
    56  -- create the trigger functions and triggers
    57  -- +goose StatementBegin
    58  create or replace function update_game_team_scores()
    59         returns trigger
    60         language plpgsql
    61  as $$
    62     begin
    63          insert into game_team_scores_current(game_id,team_id, epoch_id, score, vega_time)
    64          values (new.game_id, new.team_id, new.epoch_id, new.score, new.vega_time)
    65          on conflict(game_id, team_id)
    66          do update set
    67             epoch_id = excluded.epoch_id,
    68             score = excluded.score,
    69  	       vega_time = excluded.vega_time;
    70          return null;
    71     end;
    72  $$;
    73  
    74  create or replace function update_game_party_scores()
    75         returns trigger
    76         language plpgsql
    77  as $$
    78     begin
    79          insert into game_party_scores_current(game_id,party_id, team_id, epoch_id, score, 
    80          staking_balance,open_volume,total_fees_paid,is_eligible,vega_time)
    81          values (new.game_id, new.party_id, new.team_id, new.epoch_id, new.score, 
    82                  new.staking_balance, new.open_volume, new.total_fees_paid,
    83                  new.is_eligible,new.vega_time)
    84          on conflict(game_id, party_id)
    85          do update set
    86             epoch_id = excluded.epoch_id,
    87             team_id = excluded.team_id,
    88             score = excluded.score,
    89             staking_balance = excluded.staking_balance,
    90             open_volume = excluded.open_volume,
    91             total_fees_paid = excluded.total_fees_paid,
    92             is_eligible = excluded.is_eligible,
    93             vega_time = excluded.vega_time;
    94          return null;
    95     end;
    96  $$;
    97  -- +goose StatementEnd
    98  
    99  create trigger update_game_team_scores
   100      after insert or update
   101      on game_team_scores
   102      for each row execute function update_game_team_scores();
   103  
   104  create trigger update_game_party_scores
   105      after insert or update
   106      on game_party_scores
   107      for each row execute function update_game_party_scores();
   108  
   109  -- +goose Down
   110  
   111  drop table if exists game_team_scores_current;
   112  drop table if exists game_team_scores;
   113  drop function if exists update_game_team_scores;
   114  
   115  drop table if exists game_party_scores_current;
   116  drop table if exists game_party_scores;
   117  drop function if exists update_game_party_scores;