1SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO"; 2SET time_zone = "+00:00"; 3 4CREATE TABLE IF NOT EXISTS `aliases` ( 5 `identifier` varchar(64) DEFAULT NULL, 6 `pki_realm` varchar(255) NOT NULL, 7 `alias` varchar(255) NOT NULL, 8 `group_id` varchar(255) DEFAULT NULL, 9 `generation` smallint(6) DEFAULT NULL, 10 `notafter` int(10) unsigned DEFAULT NULL, 11 `notbefore` int(10) unsigned DEFAULT NULL 12) ENGINE=InnoDB DEFAULT CHARSET=utf8; 13 14CREATE TABLE IF NOT EXISTS `application_log` ( 15 `application_log_id` bigint(20) unsigned NOT NULL, 16 `logtimestamp` decimal(20,5) unsigned DEFAULT NULL, 17 `workflow_id` decimal(49,0) NOT NULL, 18 `priority` int(11) DEFAULT '0', 19 `category` varchar(255) NOT NULL, 20 `message` longtext 21) ENGINE=InnoDB DEFAULT CHARSET=utf8; 22 23CREATE TABLE IF NOT EXISTS `audittrail` ( 24 `audittrail_key` bigint(20) unsigned NOT NULL, 25 `logtimestamp` decimal(20,5) unsigned DEFAULT NULL, 26 `category` varchar(255) DEFAULT NULL, 27 `loglevel` varchar(255) DEFAULT NULL, 28 `message` text 29) ENGINE=InnoDB DEFAULT CHARSET=utf8; 30 31CREATE TABLE IF NOT EXISTS `certificate` ( 32 `pki_realm` varchar(255) DEFAULT NULL, 33 `issuer_dn` varchar(1000) DEFAULT NULL, 34 `cert_key` decimal(49,0) NOT NULL, 35 `issuer_identifier` varchar(64) NOT NULL, 36 `identifier` varchar(64) DEFAULT NULL, 37 `subject` varchar(1000) DEFAULT NULL, 38 `status` enum('ISSUED','HOLD','CRL_ISSUANCE_PENDING','REVOKED','UNKNOWN') DEFAULT 'UNKNOWN', 39 `subject_key_identifier` varchar(255) DEFAULT NULL, 40 `authority_key_identifier` varchar(255) DEFAULT NULL, 41 `notbefore` int(10) unsigned DEFAULT NULL, 42 `notafter` int(10) unsigned DEFAULT NULL, 43 `revocation_time` int(10) unsigned DEFAULT NULL, 44 `invalidity_time` int(10) unsigned DEFAULT NULL, 45 `reason_code` varchar(50) DEFAULT NULL, 46 `hold_instruction_code` varchar(50) DEFAULT NULL, 47 `revocation_id` INT NULL DEFAULT NULL, 48 `req_key` bigint(20) unsigned DEFAULT NULL, 49 `data` longtext 50) ENGINE=InnoDB DEFAULT CHARSET=utf8; 51 52CREATE TABLE IF NOT EXISTS `certificate_attributes` ( 53 `identifier` varchar(64) NOT NULL, 54 `attribute_key` bigint(20) unsigned NOT NULL, 55 `attribute_contentkey` varchar(255) DEFAULT NULL, 56 `attribute_value` varchar(4000) DEFAULT NULL 57) ENGINE=InnoDB DEFAULT CHARSET=utf8; 58 59CREATE TABLE IF NOT EXISTS `crl` ( 60 `pki_realm` varchar(255) NOT NULL, 61 `issuer_identifier` varchar(64) NOT NULL, 62 `profile` varchar(64) DEFAULT NULL, 63 `crl_key` decimal(49,0) NOT NULL, 64 `crl_number` decimal(49,0) DEFAULT NULL, 65 `items` int(10) DEFAULT 0, 66 `max_revocation_id` INT NULL DEFAULT NULL, 67 `data` longtext, 68 `last_update` int(10) unsigned DEFAULT NULL, 69 `next_update` int(10) unsigned DEFAULT NULL, 70 `publication_date` int(10) unsigned DEFAULT NULL 71) ENGINE=InnoDB DEFAULT CHARSET=utf8; 72 73CREATE TABLE IF NOT EXISTS `csr` ( 74 `req_key` bigint(20) unsigned NOT NULL, 75 `pki_realm` varchar(255) NOT NULL, 76 `format` varchar(25) DEFAULT NULL, 77 `profile` varchar(255) DEFAULT NULL, 78 `subject` varchar(1000) DEFAULT NULL, 79 `data` longtext 80) ENGINE=InnoDB DEFAULT CHARSET=utf8; 81 82CREATE TABLE IF NOT EXISTS `csr_attributes` ( 83 `attribute_key` bigint(20) unsigned NOT NULL, 84 `pki_realm` varchar(255) NOT NULL, 85 `req_key` decimal(49,0) NOT NULL, 86 `attribute_contentkey` varchar(255) DEFAULT NULL, 87 `attribute_value` longtext, 88 `attribute_source` text 89) ENGINE=InnoDB DEFAULT CHARSET=utf8; 90 91CREATE TABLE IF NOT EXISTS `datapool` ( 92 `pki_realm` varchar(255) NOT NULL, 93 `namespace` varchar(255) NOT NULL, 94 `datapool_key` varchar(255) NOT NULL, 95 `datapool_value` longtext, 96 `encryption_key` varchar(255) DEFAULT NULL, 97 `access_key` VARCHAR(255) NULL DEFAULT NULL, 98 `notafter` int(10) unsigned DEFAULT NULL, 99 `last_update` int(10) unsigned DEFAULT NULL 100) ENGINE=InnoDB DEFAULT CHARSET=utf8; 101 102CREATE TABLE IF NOT EXISTS `report` ( 103 `report_name` varchar(63) NOT NULL, 104 `pki_realm` varchar(255) NOT NULL, 105 `created` int(11) NOT NULL, 106 `mime_type` varchar(63) NOT NULL, 107 `description` varchar(255) NOT NULL, 108 `report_value` longblob NOT NULL 109) ENGINE=InnoDB DEFAULT CHARSET=utf8; 110 111CREATE TABLE IF NOT EXISTS `secret` ( 112 `pki_realm` varchar(255) NOT NULL, 113 `group_id` varchar(255) NOT NULL, 114 `data` longtext 115) ENGINE=InnoDB DEFAULT CHARSET=utf8; 116 117CREATE TABLE IF NOT EXISTS `backend_session` ( 118 `session_id` varchar(255) NOT NULL, 119 `data` longtext, 120 `created` int(10) unsigned NOT NULL, 121 `modified` int(10) unsigned NOT NULL, 122 `ip_address` varchar(45) DEFAULT NULL 123) ENGINE=InnoDB DEFAULT CHARSET=utf8; 124 125CREATE TABLE IF NOT EXISTS `frontend_session` ( 126 `session_id` varchar(255) NOT NULL, 127 `data` longtext, 128 `created` int(10) unsigned NOT NULL, 129 `modified` int(10) unsigned NOT NULL, 130 `ip_address` varchar(45) DEFAULT NULL 131) ENGINE=InnoDB DEFAULT CHARSET=utf8; 132 133CREATE TABLE IF NOT EXISTS `seq_application_log` ( 134 `seq_number` bigint(20) unsigned NOT NULL, 135 `dummy` int(11) DEFAULT NULL 136) ENGINE=InnoDB DEFAULT CHARSET=utf8; 137 138CREATE TABLE IF NOT EXISTS `seq_audittrail` ( 139 `seq_number` bigint(20) NOT NULL, 140 `dummy` int(11) DEFAULT NULL 141) ENGINE=InnoDB DEFAULT CHARSET=utf8; 142 143CREATE TABLE IF NOT EXISTS `seq_certificate` ( 144 `seq_number` bigint(20) NOT NULL, 145 `dummy` int(11) DEFAULT NULL 146) ENGINE=InnoDB DEFAULT CHARSET=utf8; 147 148CREATE TABLE IF NOT EXISTS `seq_certificate_attributes` ( 149 `seq_number` bigint(20) NOT NULL, 150 `dummy` int(11) DEFAULT NULL 151) ENGINE=InnoDB DEFAULT CHARSET=utf8; 152 153CREATE TABLE IF NOT EXISTS `seq_crl` ( 154 `seq_number` bigint(20) NOT NULL, 155 `dummy` int(11) DEFAULT NULL 156) ENGINE=InnoDB DEFAULT CHARSET=utf8; 157 158CREATE TABLE IF NOT EXISTS `seq_csr` ( 159 `seq_number` bigint(20) NOT NULL, 160 `dummy` int(11) DEFAULT NULL 161) ENGINE=InnoDB DEFAULT CHARSET=utf8; 162 163CREATE TABLE IF NOT EXISTS `seq_csr_attributes` ( 164 `seq_number` bigint(20) NOT NULL, 165 `dummy` int(11) DEFAULT NULL 166) ENGINE=InnoDB DEFAULT CHARSET=utf8; 167 168CREATE TABLE IF NOT EXISTS `seq_secret` ( 169 `seq_number` bigint(20) NOT NULL, 170 `dummy` int(11) DEFAULT NULL 171) ENGINE=InnoDB DEFAULT CHARSET=utf8; 172 173CREATE TABLE IF NOT EXISTS `seq_workflow` ( 174 `seq_number` bigint(20) NOT NULL, 175 `dummy` int(11) DEFAULT NULL 176) ENGINE=InnoDB DEFAULT CHARSET=utf8; 177 178CREATE TABLE IF NOT EXISTS `seq_workflow_history` ( 179 `seq_number` bigint(20) NOT NULL, 180 `dummy` int(11) DEFAULT NULL 181) ENGINE=InnoDB DEFAULT CHARSET=utf8; 182 183CREATE TABLE IF NOT EXISTS `workflow` ( 184 `workflow_id` bigint(20) unsigned NOT NULL, 185 `pki_realm` varchar(255) DEFAULT NULL, 186 `workflow_type` varchar(255) DEFAULT NULL, 187 `workflow_state` varchar(255) DEFAULT NULL, 188 `workflow_last_update` timestamp NOT NULL, 189 `workflow_proc_state` enum('init','running','manual','pause','finished','archived','failed','wakeup','resume','exception','retry_exceeded') DEFAULT 'init', 190 `workflow_wakeup_at` int(10) unsigned DEFAULT NULL, 191 `workflow_count_try` int(10) unsigned DEFAULT NULL, 192 `workflow_reap_at` int(10) unsigned DEFAULT NULL, 193 `workflow_archive_at` int(10) unsigned DEFAULT NULL, 194 `workflow_session` longtext, 195 `watchdog_key` varchar(64) DEFAULT NULL 196) ENGINE=InnoDB DEFAULT CHARSET=utf8; 197 198CREATE TABLE IF NOT EXISTS `workflow_attributes` ( 199 `workflow_id` bigint(20) unsigned NOT NULL, 200 `attribute_contentkey` varchar(255) NOT NULL, 201 `attribute_value` varchar(4000) DEFAULT NULL 202) ENGINE=InnoDB DEFAULT CHARSET=utf8; 203 204CREATE TABLE IF NOT EXISTS `workflow_context` ( 205 `workflow_id` bigint(20) unsigned NOT NULL, 206 `workflow_context_key` varchar(255) NOT NULL, 207 `workflow_context_value` longtext 208) ENGINE=InnoDB DEFAULT CHARSET=utf8; 209 210CREATE TABLE IF NOT EXISTS `workflow_history` ( 211 `workflow_hist_id` bigint(20) unsigned NOT NULL, 212 `workflow_id` bigint(20) unsigned DEFAULT NULL, 213 `workflow_action` varchar(255) DEFAULT NULL, 214 `workflow_description` longtext, 215 `workflow_state` varchar(255) DEFAULT NULL, 216 `workflow_user` varchar(255) DEFAULT NULL, 217 `workflow_node` varchar(64) DEFAULT NULL, 218 `workflow_history_date` timestamp NOT NULL 219) ENGINE=InnoDB DEFAULT CHARSET=utf8; 220 221CREATE TABLE IF NOT EXISTS `ocsp_responses` ( 222 `identifier` varchar(64), 223 `serial_number` varbinary(128) NOT NULL, 224 `authority_key_identifier` varbinary(128) NOT NULL, 225 `body` varbinary(4096) NOT NULL, 226 `expiry` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' 227) ENGINE=InnoDB DEFAULT CHARSET=utf8; 228 229CREATE TABLE IF NOT EXISTS `users` ( 230 `username` varchar(255) NOT NULL, 231 `password` varchar(255) DEFAULT NULL, 232 `pki_realm` varchar(255) DEFAULT NULL, 233 `mail` varchar(255) NOT NULL, 234 `realname` varchar(255) DEFAULT NULL, 235 `role` varchar(255) DEFAULT NULL 236) ENGINE=InnoDB DEFAULT CHARSET=utf8; 237 238ALTER TABLE `aliases` 239 ADD PRIMARY KEY (`pki_realm`,`alias`), 240 ADD KEY `realm_group` (`pki_realm`,`group_id`); 241 242ALTER TABLE `application_log` 243 ADD PRIMARY KEY (`application_log_id`), 244 ADD KEY `workflow_id` (`workflow_id`), 245 ADD KEY `workflow_id_2` (`workflow_id`,`category`,`priority`); 246 247ALTER TABLE `audittrail` 248 ADD PRIMARY KEY (`audittrail_key`); 249 250ALTER TABLE `certificate` 251 ADD PRIMARY KEY (`issuer_identifier`,`cert_key`), 252 ADD KEY `pki_realm` (`pki_realm`), 253 ADD UNIQUE `identifier` (`identifier`), 254 ADD KEY `issuer_identifier` (`issuer_identifier`), 255 ADD KEY `subject` (`subject`(255)), 256 ADD KEY `status` (`status`), 257 ADD KEY `pki_realm_req_key` (`pki_realm`,`req_key`), 258 ADD KEY `req_key` (`req_key`), 259 ADD KEY `notbefore` (`notbefore`), 260 ADD KEY `notafter` (`notafter`), 261 ADD KEY `revocation_time` (`revocation_time`), 262 ADD KEY `invalidity_time` (`invalidity_time`), 263 ADD KEY `reason_code` (`reason_code`), 264 ADD KEY `hold_instruction_code` (`hold_instruction_code`), 265 ADD UNIQUE `revocation_id` (`revocation_id`); 266 267ALTER TABLE `certificate_attributes` 268 ADD PRIMARY KEY (`attribute_key`,`identifier`), 269 ADD KEY `attribute_contentkey` (`attribute_contentkey`), 270 ADD KEY `attribute_value` (`attribute_value`(255)), 271 ADD KEY `identifier` (`identifier`), 272 ADD KEY `identifier_2` (`identifier`,`attribute_contentkey`), 273 ADD KEY `attribute_contentkey_2` (`attribute_contentkey`,`attribute_value`(255)); 274 275ALTER TABLE `crl` 276 ADD PRIMARY KEY (`issuer_identifier`,`crl_key`), 277 ADD KEY `issuer_identifier` (`issuer_identifier`), 278 ADD KEY `profile` (`profile`), 279 ADD KEY `revocation_id` (`max_revocation_id`), 280 ADD KEY `pki_realm` (`pki_realm`), 281 ADD KEY `issuer_identifier_2` (`issuer_identifier`,`last_update`), 282 ADD KEY `crl_number` (`issuer_identifier`,`crl_number`); 283 284ALTER TABLE `csr` 285 ADD PRIMARY KEY (`pki_realm`,`req_key`), 286 ADD KEY `pki_realm` (`pki_realm`), 287 ADD KEY `profile` (`pki_realm`,`profile`), 288 ADD KEY `subject` (`subject`(255)); 289 290ALTER TABLE `csr_attributes` 291 ADD PRIMARY KEY (`attribute_key`,`pki_realm`,`req_key`), 292 ADD KEY `req_key` (`req_key`), 293 ADD KEY `pki_realm_req_key` (`pki_realm`,`req_key`); 294 295ALTER TABLE `datapool` 296 ADD PRIMARY KEY (`pki_realm`,`namespace`,`datapool_key`), 297 ADD KEY `pki_realm` (`pki_realm`,`namespace`), 298 ADD KEY `notafter` (`notafter`); 299 300ALTER TABLE `report` 301 ADD PRIMARY KEY (`report_name`,`pki_realm`); 302 303ALTER TABLE `secret` 304 ADD PRIMARY KEY (`pki_realm`,`group_id`); 305 306ALTER TABLE `backend_session` 307 ADD PRIMARY KEY (`session_id`), 308 ADD INDEX(`modified`); 309 310ALTER TABLE `frontend_session` 311 ADD PRIMARY KEY (`session_id`), 312 ADD INDEX(`modified`); 313 314ALTER TABLE `seq_application_log` 315 ADD PRIMARY KEY (`seq_number`); 316 317ALTER TABLE `seq_audittrail` 318 ADD PRIMARY KEY (`seq_number`); 319 320ALTER TABLE `seq_certificate` 321 ADD PRIMARY KEY (`seq_number`); 322 323ALTER TABLE `seq_certificate_attributes` 324 ADD PRIMARY KEY (`seq_number`); 325 326ALTER TABLE `seq_crl` 327 ADD PRIMARY KEY (`seq_number`); 328 329ALTER TABLE `seq_csr` 330 ADD PRIMARY KEY (`seq_number`); 331 332ALTER TABLE `seq_csr_attributes` 333 ADD PRIMARY KEY (`seq_number`); 334 335ALTER TABLE `seq_secret` 336 ADD PRIMARY KEY (`seq_number`); 337 338ALTER TABLE `seq_workflow` 339 ADD PRIMARY KEY (`seq_number`); 340 341ALTER TABLE `seq_workflow_history` 342 ADD PRIMARY KEY (`seq_number`); 343 344ALTER TABLE `workflow` 345 ADD PRIMARY KEY (`workflow_id`), 346 ADD KEY `pki_realm` (`pki_realm`), 347 ADD KEY `pki_realm_type` (`pki_realm`,`workflow_type`), 348 ADD KEY `pki_realm_state` (`pki_realm`, `workflow_state`), 349 ADD KEY `workflow_proc_state` (`pki_realm`, `workflow_proc_state`), 350 ADD KEY `watchdog_wakeup` (`workflow_wakeup_at`, `watchdog_key`, `workflow_proc_state`), 351 ADD KEY `watchdog_reap` (`workflow_reap_at`, `watchdog_key`, `workflow_proc_state`), 352 ADD KEY `watchdog_archive_at` (`workflow_archive_at`, `watchdog_key`, `workflow_proc_state`); 353 354ALTER TABLE `workflow_attributes` 355 ADD PRIMARY KEY (`workflow_id`,`attribute_contentkey`), 356 ADD KEY `workflow_id` (`workflow_id`), 357 ADD KEY `attribute_contentkey` (`attribute_contentkey`), 358 ADD KEY `attribute_value` (`attribute_value`(255)), 359 ADD KEY `attribute_contentkey_2` (`attribute_contentkey`,`attribute_value`(255)); 360 361ALTER TABLE `workflow_context` 362 ADD PRIMARY KEY (`workflow_id`,`workflow_context_key`); 363 364ALTER TABLE `workflow_history` 365 ADD PRIMARY KEY (`workflow_hist_id`), 366 ADD KEY `workflow_id` (`workflow_id`); 367 368ALTER TABLE `ocsp_responses` 369 ADD PRIMARY KEY (`serial_number`,`authority_key_identifier`), 370 ADD KEY `identifier` (`identifier`); 371 372ALTER TABLE `users` 373 ADD PRIMARY KEY (`username`,`pki_realm`), 374 ADD UNIQUE `mail` (`mail`,`pki_realm`); 375 376ALTER TABLE `audittrail` 377MODIFY `audittrail_key` bigint(20) unsigned NOT NULL AUTO_INCREMENT; 378ALTER TABLE `seq_application_log` 379MODIFY `seq_number` bigint(20) unsigned NOT NULL AUTO_INCREMENT; 380ALTER TABLE `seq_audittrail` 381MODIFY `seq_number` bigint(20) NOT NULL AUTO_INCREMENT; 382ALTER TABLE `seq_certificate` 383MODIFY `seq_number` bigint(20) NOT NULL AUTO_INCREMENT; 384ALTER TABLE `seq_certificate_attributes` 385MODIFY `seq_number` bigint(20) NOT NULL AUTO_INCREMENT; 386ALTER TABLE `seq_crl` 387MODIFY `seq_number` bigint(20) NOT NULL AUTO_INCREMENT; 388ALTER TABLE `seq_csr` 389MODIFY `seq_number` bigint(20) NOT NULL AUTO_INCREMENT; 390ALTER TABLE `seq_csr_attributes` 391MODIFY `seq_number` bigint(20) NOT NULL AUTO_INCREMENT; 392ALTER TABLE `seq_secret` 393MODIFY `seq_number` bigint(20) NOT NULL AUTO_INCREMENT; 394ALTER TABLE `seq_workflow` 395MODIFY `seq_number` bigint(20) NOT NULL AUTO_INCREMENT; 396ALTER TABLE `seq_workflow_history` 397MODIFY `seq_number` bigint(20) NOT NULL AUTO_INCREMENT; 398