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

     1  -- +goose Up
     2  -- Make our new join table to link oracle data to oracle specs
     3  CREATE TABLE IF NOT EXISTS
     4      oracle_data_oracle_specs (
     5          vega_time TIMESTAMP WITH TIME ZONE NOT NULL,
     6          seq_num BIGINT NOT NULL,
     7          spec_id bytea NOT NULL,
     8          PRIMARY KEY (vega_time, seq_num, spec_id)
     9      );
    10  
    11  CREATE INDEX IF NOT EXISTS idx_oracle_data_oracle_specs_spec_to_data ON oracle_data_oracle_specs (spec_id);
    12  
    13  -- Populate it with the contents of the 'matched_spec_ids' bytea array column
    14  INSERT INTO
    15      oracle_data_oracle_specs
    16  SELECT
    17      vega_time,
    18      seq_num,
    19      UNNEST(matched_spec_ids)
    20  FROM
    21      oracle_data;
    22  
    23  -- Remove old column
    24  ALTER TABLE oracle_data
    25  DROP COLUMN matched_spec_ids;
    26  
    27  -- +goose Down
    28  -- Add back the old column
    29  ALTER TABLE oracle_data
    30  ADD COLUMN matched_spec_ids bytea[];
    31  
    32  -- Populate it with the contents of the oracle_data_oracle_specs table
    33  WITH
    34      stuff AS (
    35          SELECT
    36              ARRAY_AGG(spec_id) AS sids,
    37              vega_time AS vt,
    38              seq_num AS sn
    39          FROM
    40              oracle_data_oracle_specs
    41          GROUP BY
    42              vega_time,
    43              seq_num
    44      )
    45  UPDATE oracle_data
    46  SET
    47      matched_spec_ids = sids
    48  FROM
    49      stuff
    50  WHERE
    51      vega_time = vt
    52      AND seq_num = sn;
    53  
    54  -- Remove the join table
    55  DROP TABLE oracle_data_oracle_specs;