github.com/letsencrypt/boulder@v0.20251208.0/sa/db/boulder_sa/20230419000000_CombinedSchema.sql (about)

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