github.com/bartle-stripe/trillian@v1.2.1/storage/mysql/storage.sql (about) 1 # MySQL / MariaDB version of the tree schema 2 3 -- --------------------------------------------- 4 -- Tree stuff here 5 -- --------------------------------------------- 6 7 -- Tree parameters should not be changed after creation. Doing so can 8 -- render the data in the tree unusable or inconsistent. 9 CREATE TABLE IF NOT EXISTS Trees( 10 TreeId BIGINT NOT NULL, 11 TreeState ENUM('ACTIVE', 'FROZEN', 'DRAINING') NOT NULL, 12 TreeType ENUM('LOG', 'MAP', 'PREORDERED_LOG') NOT NULL, 13 HashStrategy ENUM('RFC6962_SHA256', 'TEST_MAP_HASHER', 'OBJECT_RFC6962_SHA256', 'CONIKS_SHA512_256') NOT NULL, 14 HashAlgorithm ENUM('SHA256') NOT NULL, 15 SignatureAlgorithm ENUM('ECDSA', 'RSA') NOT NULL, 16 DisplayName VARCHAR(20), 17 Description VARCHAR(200), 18 CreateTimeMillis BIGINT NOT NULL, 19 UpdateTimeMillis BIGINT NOT NULL, 20 MaxRootDurationMillis BIGINT NOT NULL, 21 PrivateKey MEDIUMBLOB NOT NULL, 22 PublicKey MEDIUMBLOB NOT NULL, 23 Deleted BOOLEAN, 24 DeleteTimeMillis BIGINT, 25 PRIMARY KEY(TreeId) 26 ); 27 28 -- This table contains tree parameters that can be changed at runtime such as for 29 -- administrative purposes. 30 CREATE TABLE IF NOT EXISTS TreeControl( 31 TreeId BIGINT NOT NULL, 32 SigningEnabled BOOLEAN NOT NULL, 33 SequencingEnabled BOOLEAN NOT NULL, 34 SequenceIntervalSeconds INTEGER NOT NULL, 35 PRIMARY KEY(TreeId), 36 FOREIGN KEY(TreeId) REFERENCES Trees(TreeId) ON DELETE CASCADE 37 ); 38 39 CREATE TABLE IF NOT EXISTS Subtree( 40 TreeId BIGINT NOT NULL, 41 SubtreeId VARBINARY(255) NOT NULL, 42 Nodes MEDIUMBLOB NOT NULL, 43 SubtreeRevision INTEGER NOT NULL, -- negated because DESC indexes aren't supported :/ 44 PRIMARY KEY(TreeId, SubtreeId, SubtreeRevision), 45 FOREIGN KEY(TreeId) REFERENCES Trees(TreeId) ON DELETE CASCADE 46 ); 47 48 -- The TreeRevisionIdx is used to enforce that there is only one STH at any 49 -- tree revision 50 CREATE TABLE IF NOT EXISTS TreeHead( 51 TreeId BIGINT NOT NULL, 52 TreeHeadTimestamp BIGINT, 53 TreeSize BIGINT, 54 RootHash VARBINARY(255) NOT NULL, 55 RootSignature VARBINARY(1024) NOT NULL, 56 TreeRevision BIGINT, 57 PRIMARY KEY(TreeId, TreeHeadTimestamp), 58 FOREIGN KEY(TreeId) REFERENCES Trees(TreeId) ON DELETE CASCADE 59 ); 60 61 CREATE UNIQUE INDEX TreeHeadRevisionIdx 62 ON TreeHead(TreeId, TreeRevision); 63 64 -- --------------------------------------------- 65 -- Log specific stuff here 66 -- --------------------------------------------- 67 68 -- Creating index at same time as table allows some storage engines to better 69 -- optimize physical storage layout. Most engines allow multiple nulls in a 70 -- unique index but some may not. 71 72 -- A leaf that has not been sequenced has a row in this table. If duplicate leaves 73 -- are allowed they will all reference this row. 74 CREATE TABLE IF NOT EXISTS LeafData( 75 TreeId BIGINT NOT NULL, 76 -- This is a personality specific has of some subset of the leaf data. 77 -- It's only purpose is to allow Trillian to identify duplicate entries in 78 -- the context of the personality. 79 LeafIdentityHash VARBINARY(255) NOT NULL, 80 -- This is the data stored in the leaf for example in CT it contains a DER encoded 81 -- X.509 certificate but is application dependent 82 LeafValue LONGBLOB NOT NULL, 83 -- This is extra data that the application can associate with the leaf should it wish to. 84 -- This data is not included in signing and hashing. 85 ExtraData LONGBLOB, 86 -- The timestamp from when this leaf data was first queued for inclusion. 87 QueueTimestampNanos BIGINT NOT NULL, 88 PRIMARY KEY(TreeId, LeafIdentityHash), 89 FOREIGN KEY(TreeId) REFERENCES Trees(TreeId) ON DELETE CASCADE 90 ); 91 92 -- When a leaf is sequenced a row is added to this table. If logs allow duplicates then 93 -- multiple rows will exist with different sequence numbers. The signed timestamp 94 -- will be communicated via the unsequenced table as this might need to be unique, depending 95 -- on the log parameters and we can't insert into this table until we have the sequence number 96 -- which is not available at the time we queue the entry. We need both hashes because the 97 -- LeafData table is keyed by the raw data hash. 98 CREATE TABLE IF NOT EXISTS SequencedLeafData( 99 TreeId BIGINT NOT NULL, 100 SequenceNumber BIGINT UNSIGNED NOT NULL, 101 -- This is a personality specific has of some subset of the leaf data. 102 -- It's only purpose is to allow Trillian to identify duplicate entries in 103 -- the context of the personality. 104 LeafIdentityHash VARBINARY(255) NOT NULL, 105 -- This is a MerkleLeafHash as defined by the treehasher that the log uses. For example for 106 -- CT this hash will include the leaf prefix byte as well as the leaf data. 107 MerkleLeafHash VARBINARY(255) NOT NULL, 108 IntegrateTimestampNanos BIGINT NOT NULL, 109 PRIMARY KEY(TreeId, SequenceNumber), 110 FOREIGN KEY(TreeId) REFERENCES Trees(TreeId) ON DELETE CASCADE, 111 FOREIGN KEY(TreeId, LeafIdentityHash) REFERENCES LeafData(TreeId, LeafIdentityHash) ON DELETE CASCADE 112 ); 113 114 CREATE TABLE IF NOT EXISTS Unsequenced( 115 TreeId BIGINT NOT NULL, 116 -- The bucket field is to allow the use of time based ring bucketed schemes if desired. If 117 -- unused this should be set to zero for all entries. 118 Bucket INTEGER NOT NULL, 119 -- This is a personality specific hash of some subset of the leaf data. 120 -- It's only purpose is to allow Trillian to identify duplicate entries in 121 -- the context of the personality. 122 LeafIdentityHash VARBINARY(255) NOT NULL, 123 -- This is a MerkleLeafHash as defined by the treehasher that the log uses. For example for 124 -- CT this hash will include the leaf prefix byte as well as the leaf data. 125 MerkleLeafHash VARBINARY(255) NOT NULL, 126 QueueTimestampNanos BIGINT NOT NULL, 127 -- This is a SHA256 hash of the TreeID, LeafIdentityHash and QueueTimestampNanos. It is used 128 -- for batched deletes from the table when trillian_log_server and trillian_log_signer are 129 -- built with the batched_queue tag. 130 QueueID VARBINARY(32) DEFAULT NULL UNIQUE, 131 PRIMARY KEY (TreeId, Bucket, QueueTimestampNanos, LeafIdentityHash) 132 ); 133 134 135 -- --------------------------------------------- 136 -- Map specific stuff here 137 -- --------------------------------------------- 138 139 CREATE TABLE IF NOT EXISTS MapLeaf( 140 TreeId BIGINT NOT NULL, 141 KeyHash VARBINARY(255) NOT NULL, 142 -- MapRevision is stored negated to invert ordering in the primary key index 143 -- st. more recent revisions come first. 144 MapRevision BIGINT NOT NULL, 145 LeafValue LONGBLOB NOT NULL, 146 PRIMARY KEY(TreeId, KeyHash, MapRevision), 147 FOREIGN KEY(TreeId) REFERENCES Trees(TreeId) ON DELETE CASCADE 148 ); 149 150 151 CREATE TABLE IF NOT EXISTS MapHead( 152 TreeId BIGINT NOT NULL, 153 MapHeadTimestamp BIGINT, 154 RootHash VARBINARY(255) NOT NULL, 155 MapRevision BIGINT, 156 RootSignature VARBINARY(1024) NOT NULL, 157 MapperData MEDIUMBLOB, 158 PRIMARY KEY(TreeId, MapHeadTimestamp), 159 FOREIGN KEY(TreeId) REFERENCES Trees(TreeId) ON DELETE CASCADE 160 ); 161 162 CREATE UNIQUE INDEX MapHeadRevisionIdx 163 ON MapHead(TreeId, MapRevision);