1-- +migrate Up
2-- SQL in section 'Up' is executed when this migration is applied
3
4CREATE TABLE `authz2` (
5 `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
6 `identifierType` tinyint(4) NOT NULL,
7 `identifierValue` varchar(255) NOT NULL,
8 `registrationID` bigint(20) NOT NULL,
9 `status` tinyint(4) NOT NULL,
10 `expires` datetime NOT NULL,
11 `challenges` tinyint(4) NOT NULL,
12 `attempted` tinyint(4) DEFAULT NULL,
13 `attemptedAt` datetime DEFAULT NULL,
14 `token` binary(32) NOT NULL,
15 `validationError` mediumblob DEFAULT NULL,
16 `validationRecord` mediumblob DEFAULT NULL,
17 PRIMARY KEY (`id`),
18 KEY `regID_expires_idx` (`registrationID`,`status`,`expires`),
19 KEY `regID_identifier_status_expires_idx` (`registrationID`,`identifierType`,`identifierValue`,`status`,`expires`),
20 KEY `expires_idx` (`expires`)
21) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
22 PARTITION BY RANGE(id)
23(PARTITION p_start VALUES LESS THAN (MAXVALUE));
24
25CREATE TABLE `blockedKeys` (
26 `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
27 `keyHash` binary(32) NOT NULL,
28 `added` datetime NOT NULL,
29 `source` tinyint(4) NOT NULL,
30 `comment` varchar(255) DEFAULT NULL,
31 `revokedBy` bigint(20) DEFAULT 0,
32 `extantCertificatesChecked` tinyint(1) DEFAULT 0,
33 PRIMARY KEY (`id`),
34 UNIQUE KEY `keyHash` (`keyHash`),
35 KEY `extantCertificatesChecked_idx` (`extantCertificatesChecked`)
36) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
37
38CREATE TABLE `certificateStatus` (
39 `id` bigint(20) NOT NULL AUTO_INCREMENT,
40 `serial` varchar(255) NOT NULL,
41 `subscriberApproved` tinyint(1) DEFAULT 0,
42 `status` varchar(255) NOT NULL,
43 `ocspLastUpdated` datetime NOT NULL,
44 `revokedDate` datetime NOT NULL,
45 `revokedReason` int(11) NOT NULL,
46 `lastExpirationNagSent` datetime NOT NULL,
47 `LockCol` bigint(20) DEFAULT 0,
48 `ocspResponse` blob DEFAULT NULL,
49 `notAfter` datetime DEFAULT NULL,
50 `isExpired` tinyint(1) DEFAULT 0,
51 `issuerID` bigint(20) DEFAULT NULL,
52 PRIMARY KEY (`id`),
53 KEY `serial` (`serial`),
54 KEY `isExpired_ocspLastUpdated_idx` (`isExpired`,`ocspLastUpdated`),
55 KEY `notAfter_idx` (`notAfter`)
56) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
57 PARTITION BY RANGE(id)
58(PARTITION p_start VALUES LESS THAN (MAXVALUE));
59
60CREATE TABLE `certificates` (
61 `id` bigint(20) NOT NULL AUTO_INCREMENT,
62 `registrationID` bigint(20) NOT NULL,
63 `serial` varchar(255) NOT NULL,
64 `digest` varchar(255) NOT NULL,
65 `der` mediumblob NOT NULL,
66 `issued` datetime NOT NULL,
67 `expires` datetime NOT NULL,
68 PRIMARY KEY (`id`),
69 KEY `serial` (`serial`),
70 KEY `regId_certificates_idx` (`registrationID`) COMMENT 'Common lookup',
71 KEY `issued_idx` (`issued`)
72) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
73 PARTITION BY RANGE(id)
74(PARTITION p_start VALUES LESS THAN (MAXVALUE));
75
76CREATE TABLE `certificatesPerName` (
77 `id` bigint(20) NOT NULL AUTO_INCREMENT,
78 `eTLDPlusOne` varchar(255) NOT NULL,
79 `time` datetime NOT NULL,
80 `count` int(11) NOT NULL,
81 PRIMARY KEY (`id`),
82 UNIQUE KEY `eTLDPlusOne_time_idx` (`eTLDPlusOne`,`time`)
83) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
84
85CREATE TABLE `fqdnSets` (
86 `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
87 `setHash` binary(32) NOT NULL,
88 `serial` varchar(255) NOT NULL,
89 `issued` datetime NOT NULL,
90 `expires` datetime NOT NULL,
91 PRIMARY KEY (`id`),
92 KEY `serial` (`serial`),
93 KEY `setHash_issued_idx` (`setHash`,`issued`)
94) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
95 PARTITION BY RANGE(id)
96(PARTITION p_start VALUES LESS THAN (MAXVALUE));
97
98CREATE TABLE `incidents` (
99 `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
100 `serialTable` varchar(128) NOT NULL,
101 `url` varchar(1024) NOT NULL,
102 `renewBy` datetime NOT NULL,
103 `enabled` boolean DEFAULT false,
104 PRIMARY KEY (`id`)
105) CHARSET=utf8mb4;
106
107CREATE TABLE `issuedNames` (
108 `id` bigint(20) NOT NULL AUTO_INCREMENT,
109 `reversedName` varchar(640) CHARACTER SET ascii NOT NULL,
110 `notBefore` datetime NOT NULL,
111 `serial` varchar(255) NOT NULL,
112 `renewal` tinyint(1) NOT NULL DEFAULT 0,
113 PRIMARY KEY (`id`),
114 KEY `reversedName_notBefore_Idx` (`reversedName`,`notBefore`)
115) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
116 PARTITION BY RANGE(id)
117(PARTITION p_start VALUES LESS THAN (MAXVALUE));
118
119CREATE TABLE `keyHashToSerial` (
120 `id` bigint(20) NOT NULL AUTO_INCREMENT,
121 `keyHash` binary(32) NOT NULL,
122 `certNotAfter` datetime NOT NULL,
123 `certSerial` varchar(255) NOT NULL,
124 PRIMARY KEY (`id`),
125 UNIQUE KEY `unique_keyHash_certserial` (`keyHash`,`certSerial`),
126 KEY `keyHash_certNotAfter` (`keyHash`,`certNotAfter`)
127) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
128
129CREATE TABLE `newOrdersRL` (
130 `id` bigint(20) NOT NULL AUTO_INCREMENT,
131 `regID` bigint(20) NOT NULL,
132 `time` datetime NOT NULL,
133 `count` int(11) NOT NULL,
134 PRIMARY KEY (`id`),
135 UNIQUE KEY `regID_time_idx` (`regID`,`time`)
136) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
137
138CREATE TABLE `orderFqdnSets` (
139 `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
140 `setHash` binary(32) NOT NULL,
141 `orderID` bigint(20) NOT NULL,
142 `registrationID` bigint(20) NOT NULL,
143 `expires` datetime NOT NULL,
144 PRIMARY KEY (`id`),
145 KEY `setHash_expires_idx` (`setHash`,`expires`),
146 KEY `orderID_idx` (`orderID`),
147 KEY `orderFqdnSets_registrationID_registrations` (`registrationID`)
148) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
149 PARTITION BY RANGE(id)
150(PARTITION p_start VALUES LESS THAN (MAXVALUE));
151
152CREATE TABLE `orderToAuthz2` (
153 `orderID` bigint(20) NOT NULL,
154 `authzID` bigint(20) NOT NULL,
155 PRIMARY KEY (`orderID`,`authzID`),
156 KEY `authzID` (`authzID`)
157) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
158 PARTITION BY RANGE COLUMNS(orderID, authzID)
159(PARTITION p_start VALUES LESS THAN (MAXVALUE, MAXVALUE));
160
161CREATE TABLE `orders` (
162 `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
163 `registrationID` bigint(20) NOT NULL,
164 `expires` datetime NOT NULL,
165 `error` mediumblob DEFAULT NULL,
166 `certificateSerial` varchar(255) DEFAULT NULL,
167 `beganProcessing` tinyint(1) NOT NULL DEFAULT 0,
168 `created` datetime NOT NULL,
169 PRIMARY KEY (`id`),
170 KEY `reg_status_expires` (`registrationID`,`expires`),
171 KEY `regID_created_idx` (`registrationID`,`created`)
172) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
173 PARTITION BY RANGE(id)
174(PARTITION p_start VALUES LESS THAN (MAXVALUE));
175
176CREATE TABLE `precertificates` (
177 `id` bigint(20) NOT NULL AUTO_INCREMENT,
178 `registrationID` bigint(20) NOT NULL,
179 `serial` varchar(255) NOT NULL,
180 `der` mediumblob NOT NULL,
181 `issued` datetime NOT NULL,
182 `expires` datetime NOT NULL,
183 PRIMARY KEY (`id`),
184 KEY `serial` (`serial`),
185 KEY `regId_precertificates_idx` (`registrationID`),
186 KEY `issued_precertificates_idx` (`issued`)
187) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
188 PARTITION BY RANGE(id)
189(PARTITION p_start VALUES LESS THAN (MAXVALUE));
190
191CREATE TABLE `registrations` (
192 `id` bigint(20) NOT NULL AUTO_INCREMENT,
193 `jwk` mediumblob NOT NULL,
194 `jwk_sha256` varchar(255) NOT NULL,
195 `contact` varchar(191) CHARACTER SET utf8mb4 NOT NULL,
196 `agreement` varchar(255) NOT NULL,
197 `LockCol` bigint(20) NOT NULL,
198 `initialIP` binary(16) NOT NULL DEFAULT '\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0',
199 `createdAt` datetime NOT NULL,
200 `status` varchar(255) NOT NULL DEFAULT 'valid',
201 PRIMARY KEY (`id`),
202 UNIQUE KEY `jwk_sha256` (`jwk_sha256`),
203 KEY `initialIP_createdAt` (`initialIP`,`createdAt`)
204) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
205
206CREATE TABLE `requestedNames` (
207 `id` bigint(20) NOT NULL AUTO_INCREMENT,
208 `orderID` bigint(20) NOT NULL,
209 `reversedName` varchar(253) CHARACTER SET ascii NOT NULL,
210 PRIMARY KEY (`id`),
211 KEY `orderID_idx` (`orderID`),
212 KEY `reversedName_idx` (`reversedName`)
213) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
214 PARTITION BY RANGE(id)
215(PARTITION p_start VALUES LESS THAN (MAXVALUE));
216
217-- Tables below have foreign key constraints, so are created after all other tables.
218
219CREATE TABLE `serials` (
220 `id` bigint(20) NOT NULL AUTO_INCREMENT,
221 `registrationID` bigint(20) NOT NULL,
222 `serial` varchar(255) NOT NULL,
223 `created` datetime NOT NULL,
224 `expires` datetime NOT NULL,
225 PRIMARY KEY (`id`),
226 UNIQUE KEY `serial` (`serial`),
227 KEY `regId_serials_idx` (`registrationID`),
228 CONSTRAINT `regId_serials` FOREIGN KEY (`registrationID`) REFERENCES `registrations` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
229) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
230
231-- +migrate Down
232-- SQL section 'Down' is executed when this migration is rolled back
233
234-- First set of tables have foreign key constraints, so are dropped first.
235DROP TABLE `serials`;
236
237DROP TABLE `authz2`;
238DROP TABLE `blockedKeys`;
239DROP TABLE `certificateStatus`;
240DROP TABLE `certificatesPerName`;
241DROP TABLE `certificates`;
242DROP TABLE `fqdnSets`;
243DROP TABLE `issuedNames`;
244DROP TABLE `keyHashToSerial`;
245DROP TABLE `newOrdersRL`;
246DROP TABLE `orderFqdnSets`;
247DROP TABLE `orderToAuthz2`;
248DROP TABLE `orders`;
249DROP TABLE `precertificates`;
250DROP TABLE `registrations`;
251DROP TABLE `requestedNames`;
View as plain text