OpenXPKI v2 to v3 migration SQL for MySQL

openxpki_v2_v3_migration.sql

CREATE TABLE IF NOT EXISTS `backend_session` (
  `session_id` varchar(255) NOT NULL,
  `data` longtext,
  `created` int(10) unsigned NOT NULL,
  `modified` int(10) unsigned NOT NULL,
  `ip_address` varchar(45) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `frontend_session` (
  `session_id` varchar(255) NOT NULL,
  `data` longtext,
  `created` int(10) unsigned NOT NULL,
  `modified` int(10) unsigned NOT NULL,
  `ip_address` varchar(45) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `backend_session`
 ADD PRIMARY KEY (`session_id`),
 ADD INDEX(`modified`);

ALTER TABLE `frontend_session`
 ADD PRIMARY KEY (`session_id`),
 ADD INDEX(`modified`);

DROP TABLE IF EXISTS `session`;

/* Type changes */
/* logtimestamp in application_log and audittrail should have 5 decimals (DECIMAL 20,5) */
ALTER TABLE `application_log`
 CHANGE COLUMN `logtimestamp` `logtimestamp` decimal(20,5) unsigned DEFAULT NULL;
ALTER TABLE `audittrail`
 CHANGE COLUMN `logtimestamp` `logtimestamp` decimal(20,5) unsigned DEFAULT NULL;

/* New Fields (see schemas for details) */
/* crl.profile */
/* crl.max_revocation_id */
ALTER TABLE `crl`
 ADD COLUMN `profile` varchar(64) DEFAULT NULL AFTER `issuer_identifier`,
 ADD COLUMN `max_revocation_id` INT NULL DEFAULT NULL AFTER `items`,
 ADD KEY `profile` (`profile`),
 ADD KEY `revocation_id` (`max_revocation_id`);
/* datapool.access_key */
ALTER TABLE `datapool`
 ADD COLUMN `access_key` VARCHAR(255) NULL DEFAULT NULL AFTER `encryption_key`;
/* workflow_archive_at */
ALTER TABLE `workflow`
 ADD COLUMN `workflow_archive_at` int(10) unsigned DEFAULT NULL AFTER `workflow_reap_at`,
 ADD KEY `watchdog_archive_at` (`workflow_archive_at`, `watchdog_key`, `workflow_proc_state`);
/* certificate.revocation_id */
ALTER TABLE `certificate`
 ADD COLUMN `revocation_id` INT NULL DEFAULT NULL AFTER `hold_instruction_code`,
 ADD UNIQUE `revocation_id` (`revocation_id`);