code.vegaprotocol.io/vega@v0.79.0/blockexplorer/store/migrations/0001_initial.sql (about)

     1  -- +goose Up
     2  -- The blocks table records metadata about each block.
     3  -- The block record does not include its events or transactions (see tx_results).
     4  CREATE TABLE blocks (
     5    rowid      BIGSERIAL PRIMARY KEY,
     6  
     7    height     BIGINT NOT NULL,
     8    chain_id   VARCHAR NOT NULL,
     9  
    10    -- When this block header was logged into the sink, in UTC.
    11    created_at TIMESTAMPTZ NOT NULL,
    12  
    13    UNIQUE (height, chain_id)
    14  );
    15  
    16  -- Index blocks by height and chain, since we need to resolve block IDs when
    17  -- indexing transaction records and transaction events.
    18  CREATE INDEX idx_blocks_height_chain ON blocks(height, chain_id);
    19  
    20  -- The tx_results table records metadata about transaction results.  Note that
    21  -- the events from a transaction are stored separately.
    22  CREATE TABLE tx_results (
    23    rowid BIGSERIAL PRIMARY KEY,
    24  
    25    -- The block to which this transaction belongs.
    26    block_id BIGINT NOT NULL REFERENCES blocks(rowid),
    27    -- The sequential index of the transaction within the block.
    28    index INTEGER NOT NULL,
    29    -- When this result record was logged into the sink, in UTC.
    30    created_at TIMESTAMPTZ NOT NULL,
    31    -- The hex-encoded hash of the transaction.
    32    tx_hash VARCHAR NOT NULL,
    33    -- The protobuf wire encoding of the TxResult message.
    34    tx_result BYTEA NOT NULL,
    35  
    36    UNIQUE (block_id, index)
    37  );
    38  
    39  -- The events table records events. All events (both block and transaction) are
    40  -- associated with a block ID; transaction events also have a transaction ID.
    41  CREATE TABLE events (
    42    rowid BIGSERIAL PRIMARY KEY,
    43  
    44    -- The block and transaction this event belongs to.
    45    -- If tx_id is NULL, this is a block event.
    46    block_id BIGINT NOT NULL REFERENCES blocks(rowid),
    47    tx_id    BIGINT NULL REFERENCES tx_results(rowid),
    48  
    49    -- The application-defined type label for the event.
    50    type VARCHAR NOT NULL
    51  );
    52  
    53  -- The attributes table records event attributes.
    54  CREATE TABLE attributes (
    55     event_id      BIGINT NOT NULL REFERENCES events(rowid),
    56     key           VARCHAR NOT NULL, -- bare key
    57     composite_key VARCHAR NOT NULL, -- composed type.key
    58     value         VARCHAR NULL,
    59  
    60     UNIQUE (event_id, key)
    61  );
    62  
    63  -- A joined view of events and their attributes. Events that do not have any
    64  -- attributes are represented as a single row with empty key and value fields.
    65  CREATE VIEW event_attributes AS
    66    SELECT block_id, tx_id, type, key, composite_key, value
    67    FROM events LEFT JOIN attributes ON (events.rowid = attributes.event_id);
    68  
    69  -- A joined view of all block events (those having tx_id NULL).
    70  CREATE VIEW block_events AS
    71    SELECT blocks.rowid as block_id, height, chain_id, type, key, composite_key, value
    72    FROM blocks JOIN event_attributes ON (blocks.rowid = event_attributes.block_id)
    73    WHERE event_attributes.tx_id IS NULL;
    74  
    75  -- A joined view of all transaction events.
    76  CREATE VIEW tx_events AS
    77    SELECT height, index, chain_id, type, key, composite_key, value, tx_results.created_at
    78    FROM blocks JOIN tx_results ON (blocks.rowid = tx_results.block_id)
    79    JOIN event_attributes ON (tx_results.rowid = event_attributes.tx_id)
    80    WHERE event_attributes.tx_id IS NOT NULL;
    81  
    82  
    83  -- +goose Down
    84  
    85  DROP VIEW IF EXISTS tx_events
    86  DROP VIEW IF EXISTS block_events
    87  DROP VIEW IF EXISTS event_attributes
    88  DROP TABLE IF EXISTS attributes
    89  DROP TABLE IF EXISTS events
    90  DROP TABLE IF EXISTS tx_results
    91  DROP TABLE IF EXISTS blocks