github.com/diadata-org/diadata@v1.4.593/localenv/pginit.sql (about)

     1  CREATE EXTENSION "pgcrypto";
     2  
     3  
     4  -- Table asset is the single source of truth for all assets handled at DIA.
     5  -- If a field is not case sensitive (such as address for Ethereum) it should
     6  -- be all lowercase for consistency reasons.
     7  -- Otherwise it must be as defined in the underlying contract.
     8  CREATE TABLE asset (
     9      asset_id UUID DEFAULT gen_random_uuid(),
    10      symbol text not null,
    11      name text not null,
    12      decimals text,
    13      blockchain text,
    14      address text not null,
    15      UNIQUE (asset_id),
    16      UNIQUE (address, blockchain)
    17  );
    18  
    19  -- Table exchangepair holds all trading pairs for the pair scrapers.
    20  -- The format has to be the same as emitted by the exchange's API in order
    21  -- for the pair scrapers to be able to scrape trading data from the API.
    22  CREATE TABLE exchangepair (
    23      exchangepair_id UUID DEFAULT gen_random_uuid(),
    24      symbol text not null,
    25      foreignname text not null,
    26      exchange text not null,
    27      UNIQUE (foreignname, exchange),
    28      -- These fields reference asset table and should be verified by pairdiscoveryservice.
    29      -- Only trades with verified pairs are processed further and thereby enter price calculation.
    30      verified boolean default false,
    31      id_quotetoken uuid REFERENCES asset(asset_id),
    32      id_basetoken uuid REFERENCES asset(asset_id)
    33  );
    34  
    35  CREATE TABLE exchangesymbol (
    36      exchangesymbol_id UUID DEFAULT gen_random_uuid(),
    37      symbol text not null,
    38      exchange text not null,
    39      UNIQUE (symbol,exchange),
    40      verified boolean default false,
    41      asset_id uuid REFERENCES asset(asset_id)
    42  );
    43  
    44  -- blockchain table stores all blockchains available in our databases
    45  CREATE TABLE blockchain (
    46      blockchain_id integer primary key generated always as identity,
    47      name text not null,
    48      genesisdate timestamp,
    49      nativetoken text,
    50  	verificationmechanism text
    51  );
    52  
    53  CREATE TABLE assetvolume (
    54       asset_id UUID primary key,
    55     volume decimal
    56  
    57  );
    58  
    59  
    60  
    61   
    62  
    63  -- Comments:
    64  
    65  -- We use text instead of char:
    66  -- https://stackoverflow.com/questions/4848964/difference-between-text-and-varchar-character-varying
    67  
    68  -- For the use of primary keys and the advantage of integers. A bit old, but maybe still true?
    69  -- https://stackoverflow.com/questions/337503/whats-the-best-practice-for-primary-keys-in-tables