...

Text file src/github.com/letsencrypt/boulder/sa/db/boulder_sa/20230419000000_CombinedSchema.sql

Documentation: github.com/letsencrypt/boulder/sa/db/boulder_sa

     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