1--
2-- Copyright 2021 The Sigstore Authors.
3--
4-- Licensed under the Apache License, Version 2.0 (the "License");
5-- you may not use this file except in compliance with the License.
6-- You may obtain a copy of the License at
7--
8-- http://www.apache.org/licenses/LICENSE-2.0
9--
10-- Unless required by applicable law or agreed to in writing, software
11-- distributed under the License is distributed on an "AS IS" BASIS,
12-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13-- See the License for the specific language governing permissions and
14-- limitations under the License.
15
16# MySQL / MariaDB version of the tree schema
17
18-- ---------------------------------------------
19-- Tree stuff here
20-- ---------------------------------------------
21
22-- Tree parameters should not be changed after creation. Doing so can
23-- render the data in the tree unusable or inconsistent.
24CREATE TABLE IF NOT EXISTS Trees(
25 TreeId BIGINT NOT NULL,
26 TreeState ENUM('ACTIVE', 'FROZEN', 'DRAINING') NOT NULL,
27 TreeType ENUM('LOG', 'MAP', 'PREORDERED_LOG') NOT NULL,
28 HashStrategy ENUM('RFC6962_SHA256', 'TEST_MAP_HASHER', 'OBJECT_RFC6962_SHA256', 'CONIKS_SHA512_256', 'CONIKS_SHA256') NOT NULL,
29 HashAlgorithm ENUM('SHA256') NOT NULL,
30 SignatureAlgorithm ENUM('ECDSA', 'RSA', 'ED25519') NOT NULL,
31 DisplayName VARCHAR(20),
32 Description VARCHAR(200),
33 CreateTimeMillis BIGINT NOT NULL,
34 UpdateTimeMillis BIGINT NOT NULL,
35 MaxRootDurationMillis BIGINT NOT NULL,
36 PrivateKey MEDIUMBLOB NOT NULL, -- Unused.
37 PublicKey MEDIUMBLOB NOT NULL, -- This is now used to store settings.
38 Deleted BOOLEAN,
39 DeleteTimeMillis BIGINT,
40 PRIMARY KEY(TreeId)
41);
42
43-- This table contains tree parameters that can be changed at runtime such as for
44-- administrative purposes.
45CREATE TABLE IF NOT EXISTS TreeControl(
46 TreeId BIGINT NOT NULL,
47 SigningEnabled BOOLEAN NOT NULL,
48 SequencingEnabled BOOLEAN NOT NULL,
49 SequenceIntervalSeconds INTEGER NOT NULL,
50 PRIMARY KEY(TreeId),
51 FOREIGN KEY(TreeId) REFERENCES Trees(TreeId) ON DELETE CASCADE
52);
53
54CREATE TABLE IF NOT EXISTS Subtree(
55 TreeId BIGINT NOT NULL,
56 SubtreeId VARBINARY(255) NOT NULL,
57 Nodes MEDIUMBLOB NOT NULL,
58 SubtreeRevision INTEGER NOT NULL,
59 -- Key columns must be in ASC order in order to benefit from group-by/min-max
60 -- optimization in MySQL.
61 PRIMARY KEY(TreeId, SubtreeId, SubtreeRevision),
62 FOREIGN KEY(TreeId) REFERENCES Trees(TreeId) ON DELETE CASCADE
63);
64
65-- The TreeRevisionIdx is used to enforce that there is only one STH at any
66-- tree revision
67CREATE TABLE IF NOT EXISTS TreeHead(
68 TreeId BIGINT NOT NULL,
69 TreeHeadTimestamp BIGINT,
70 TreeSize BIGINT,
71 RootHash VARBINARY(255) NOT NULL,
72 RootSignature VARBINARY(1024) NOT NULL,
73 TreeRevision BIGINT,
74 PRIMARY KEY(TreeId, TreeHeadTimestamp),
75 FOREIGN KEY(TreeId) REFERENCES Trees(TreeId) ON DELETE CASCADE
76);
77
78CREATE UNIQUE INDEX TreeHeadRevisionIdx
79 ON TreeHead(TreeId, TreeRevision);
80
81-- ---------------------------------------------
82-- Log specific stuff here
83-- ---------------------------------------------
84
85-- Creating index at same time as table allows some storage engines to better
86-- optimize physical storage layout. Most engines allow multiple nulls in a
87-- unique index but some may not.
88
89-- A leaf that has not been sequenced has a row in this table. If duplicate leaves
90-- are allowed they will all reference this row.
91CREATE TABLE IF NOT EXISTS LeafData(
92 TreeId BIGINT NOT NULL,
93 -- This is a personality specific has of some subset of the leaf data.
94 -- It's only purpose is to allow Trillian to identify duplicate entries in
95 -- the context of the personality.
96 LeafIdentityHash VARBINARY(255) NOT NULL,
97 -- This is the data stored in the leaf for example in CT it contains a DER encoded
98 -- X.509 certificate but is application dependent
99 LeafValue LONGBLOB NOT NULL,
100 -- This is extra data that the application can associate with the leaf should it wish to.
101 -- This data is not included in signing and hashing.
102 ExtraData LONGBLOB,
103 -- The timestamp from when this leaf data was first queued for inclusion.
104 QueueTimestampNanos BIGINT NOT NULL,
105 PRIMARY KEY(TreeId, LeafIdentityHash),
106 FOREIGN KEY(TreeId) REFERENCES Trees(TreeId) ON DELETE CASCADE
107);
108
109-- When a leaf is sequenced a row is added to this table. If logs allow duplicates then
110-- multiple rows will exist with different sequence numbers. The signed timestamp
111-- will be communicated via the unsequenced table as this might need to be unique, depending
112-- on the log parameters and we can't insert into this table until we have the sequence number
113-- which is not available at the time we queue the entry. We need both hashes because the
114-- LeafData table is keyed by the raw data hash.
115CREATE TABLE IF NOT EXISTS SequencedLeafData(
116 TreeId BIGINT NOT NULL,
117 SequenceNumber BIGINT UNSIGNED NOT NULL,
118 -- This is a personality specific has of some subset of the leaf data.
119 -- It's only purpose is to allow Trillian to identify duplicate entries in
120 -- the context of the personality.
121 LeafIdentityHash VARBINARY(255) NOT NULL,
122 -- This is a MerkleLeafHash as defined by the treehasher that the log uses. For example for
123 -- CT this hash will include the leaf prefix byte as well as the leaf data.
124 MerkleLeafHash VARBINARY(255) NOT NULL,
125 IntegrateTimestampNanos BIGINT NOT NULL,
126 PRIMARY KEY(TreeId, SequenceNumber),
127 FOREIGN KEY(TreeId) REFERENCES Trees(TreeId) ON DELETE CASCADE,
128 FOREIGN KEY(TreeId, LeafIdentityHash) REFERENCES LeafData(TreeId, LeafIdentityHash) ON DELETE CASCADE
129);
130
131CREATE INDEX SequencedLeafMerkleIdx
132 ON SequencedLeafData(TreeId, MerkleLeafHash);
133
134CREATE TABLE IF NOT EXISTS Unsequenced(
135 TreeId BIGINT NOT NULL,
136 -- The bucket field is to allow the use of time based ring bucketed schemes if desired. If
137 -- unused this should be set to zero for all entries.
138 Bucket INTEGER NOT NULL,
139 -- This is a personality specific hash of some subset of the leaf data.
140 -- It's only purpose is to allow Trillian to identify duplicate entries in
141 -- the context of the personality.
142 LeafIdentityHash VARBINARY(255) NOT NULL,
143 -- This is a MerkleLeafHash as defined by the treehasher that the log uses. For example for
144 -- CT this hash will include the leaf prefix byte as well as the leaf data.
145 MerkleLeafHash VARBINARY(255) NOT NULL,
146 QueueTimestampNanos BIGINT NOT NULL,
147 -- This is a SHA256 hash of the TreeID, LeafIdentityHash and QueueTimestampNanos. It is used
148 -- for batched deletes from the table when trillian_log_server and trillian_log_signer are
149 -- built with the batched_queue tag.
150 QueueID VARBINARY(32) DEFAULT NULL UNIQUE,
151 PRIMARY KEY (TreeId, Bucket, QueueTimestampNanos, LeafIdentityHash)
152);
View as plain text