github.com/bartle-stripe/trillian@v1.2.1/storage/cloudspanner/spanner.sdl (about)

     1  -- CloudSpanner has a strong recommendation to restrict all data between
     2  -- top-level table rows to less than a few GiB:
     3  --  | As a rule of thumb, the size of every set of related rows in a hierarchy
     4  --  | of parent-child tables should be less than a few GiB. A set of related
     5  --  | rows in a hierarchy of parent-child tables is defined as: (a single row
     6  --  | of a table at the root of a database hierarchy) + (all rows of that table's
     7  --  | descendent tables that share the row's primary key) + (all rows of
     8  --  | interleaved indexes that share the row's primary key).
     9  --  | (https://cloud.google.com/spanner/docs/schema-and-data-model)
    10  -- so we don't interleave our tables.
    11  
    12  CREATE TABLE TreeRoots(
    13    TreeID                INT64 NOT NULL,
    14    TreeState             INT64 NOT NULL,
    15    TreeType              INT64 NOT NULL,
    16    TreeInfo              BYTES(2097152) NOT NULL,
    17    Deleted               BOOL NOT NULL,
    18    DeleteTimeMillis      INT64,
    19  ) PRIMARY KEY(TreeID);
    20  
    21  CREATE INDEX TreeRootsByDeleted
    22    ON TreeRoots (Deleted);
    23  
    24  CREATE TABLE TreeHeads(
    25    TreeID                  INT64 NOT NULL,
    26    TimestampNanos          INT64 NOT NULL,
    27    TreeSize                INT64 NOT NULL,
    28    RootHash                BYTES(256) NOT NULL,
    29    RootSignature           BYTES(1024) NOT NULL,
    30    TreeRevision            INT64 NOT NULL,
    31    TreeMetadata            BYTES(2097152),
    32  ) PRIMARY KEY(TreeID, TreeRevision DESC);
    33  
    34  CREATE TABLE SubtreeData(
    35    TreeID      INT64 NOT NULL,
    36    SubtreeID   BYTES(256) NOT NULL,
    37    Revision    INT64 NOT NULL,
    38    Subtree     BYTES(MAX) NOT NULL
    39  ) PRIMARY KEY(TreeID, SubtreeID, Revision DESC);
    40  
    41  CREATE TABLE LeafData(
    42    TreeID              INT64 NOT NULL,
    43    LeafIdentityHash    BYTES(256) NOT NULL,
    44    LeafValue           BYTES(MAX) NOT NULL,
    45    ExtraData           BYTES(MAX),
    46    QueueTimestampNanos INT64 NOT NULL,
    47  ) PRIMARY KEY(TreeID, LeafIdentityHash);
    48  
    49  CREATE TABLE SequencedLeafData(
    50    TreeID                  INT64 NOT NULL,
    51    SequenceNumber          INT64 NOT NULL,
    52    LeafIdentityHash        BYTES(256) NOT NULL,
    53    MerkleLeafHash          BYTES(256) NOT NULL,
    54    IntegrateTimestampNanos INT64 NOT NULL,
    55  ) PRIMARY KEY(TreeID, SequenceNumber);
    56  
    57  CREATE INDEX SequenceByMerkleHash
    58    ON SequencedLeafData(TreeID, MerkleLeafHash)
    59    STORING(LeafIdentityHash);
    60  
    61  CREATE TABLE Unsequenced(
    62    TreeID                 INT64 NOT NULL,
    63    Bucket                 INT64 NOT NULL,
    64    QueueTimestampNanos    INT64 NOT NULL,
    65    MerkleLeafHash         BYTES(256) NOT NULL,
    66    LeafIdentityHash       BYTES(256) NOT NULL,
    67  ) PRIMARY KEY (TreeID, Bucket, QueueTimestampNanos, MerkleLeafHash);
    68  
    69  CREATE TABLE MapLeafData(
    70    TreeID                INT64 NOT NULL,
    71    LeafIndex             BYTES(256) NOT NULL,
    72    MapRevision           INT64 NOT NULL,
    73    LeafHash              BYTES(256),
    74    LeafValue             BYTES(MAX) NOT NULL,
    75    ExtraData             BYTES(MAX),
    76  ) PRIMARY KEY(TreeID, LeafIndex, MapRevision DESC);