3 files changed, 123 insertions, 0 deletions
diff --git a/etc/schema/mysql-upgrade/v1.0.0.sql b/etc/schema/mysql-upgrade/v1.0.0.sql
new file mode 100644
index 0000000..28b3e7d
--- /dev/null
+++ b/etc/schema/mysql-upgrade/v1.0.0.sql
@@ -0,0 +1,27 @@
+ALTER TABLE x509_target MODIFY COLUMN `port` smallint unsigned NOT NULL;
+
+ALTER TABLE x509_certificate_subject_alt_name DROP FOREIGN KEY x509_fk_certificate_subject_alt_name_certificate_id;
+
+ALTER TABLE x509_certificate_subject_alt_name DROP PRIMARY KEY;
+
+ALTER TABLE x509_certificate_subject_alt_name ADD COLUMN hash binary(32) NOT NULL
+  COMMENT 'sha256 hash of type=value'
+  AFTER certificate_id;
+
+UPDATE x509_certificate_subject_alt_name SET hash = UNHEX(SHA2(CONCAT(type, '=', value), 256));
+
+ALTER TABLE x509_certificate_subject_alt_name ADD PRIMARY KEY(certificate_id, hash);
+
+ALTER TABLE x509_certificate_subject_alt_name ADD
+  CONSTRAINT x509_fk_certificate_subject_alt_name_certificate_id
+  FOREIGN KEY (certificate_id)
+  REFERENCES x509_certificate (id)
+  ON DELETE CASCADE ON UPDATE CASCADE;
+
+ALTER TABLE x509_certificate_subject_alt_name ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=default;
+
+ALTER TABLE x509_target DROP INDEX x509_idx_target_ip_port_hostname;
+
+ALTER TABLE x509_target ADD INDEX x509_idx_target_ip_port_hostname(ip,port,hostname(191));
+
+ALTER TABLE x509_target ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=default;
diff --git a/etc/schema/mysql-upgrade/v1.1.0.sql b/etc/schema/mysql-upgrade/v1.1.0.sql
new file mode 100644
index 0000000..055d783
--- /dev/null
+++ b/etc/schema/mysql-upgrade/v1.1.0.sql
@@ -0,0 +1,4 @@
+ALTER TABLE x509_target DROP INDEX x509_idx_target_ip_port_hostname;
+ALTER TABLE x509_target ADD INDEX x509_idx_target_ip_port (ip, port);
+ALTER TABLE x509_certificate MODIFY COLUMN valid_from bigint(20) NOT NULL;
+ALTER TABLE x509_certificate MODIFY COLUMN valid_to bigint(20) NOT NULL;
diff --git a/etc/schema/mysql.schema.sql b/etc/schema/mysql.schema.sql
new file mode 100644
index 0000000..03c1cc1
--- /dev/null
+++ b/etc/schema/mysql.schema.sql
@@ -0,0 +1,92 @@
+CREATE TABLE x509_certificate (
+  id int(10) unsigned NOT NULL AUTO_INCREMENT,
+  `subject` varchar(255) NOT NULL COMMENT 'CN of the subject DN if present else full subject DN',
+  subject_hash binary(32) NOT NULL COMMENT 'sha256 hash of the full subject DN',
+  `issuer` varchar(255) NOT NULL COMMENT 'CN of the issuer DN if present else full issuer DN',
+  issuer_hash binary(32) NOT NULL COMMENT 'sha256 hash of the full issuer DN',
+  issuer_certificate_id int(10) unsigned DEFAULT NULL,
+  version enum('1','2','3') NOT NULL,
+  self_signed enum('yes','no') NOT NULL DEFAULT 'no',
+  ca enum('yes','no') NOT NULL,
+  trusted enum('yes','no') NOT NULL DEFAULT 'no',
+  pubkey_algo enum('unknown','RSA','DSA','DH','EC') NOT NULL,
+  pubkey_bits smallint(6) unsigned NOT NULL,
+  signature_algo varchar(255) NOT NULL,
+  signature_hash_algo varchar(255) NOT NULL,
+  valid_from bigint(20) NOT NULL,
+  valid_to bigint(20) NOT NULL,
+  fingerprint binary(32) NOT NULL COMMENT 'sha256 hash',
+  `serial` blob NOT NULL,
+  certificate blob NOT NULL COMMENT 'DER encoded certificate',
+  ctime timestamp NULL DEFAULT NULL,
+  mtime timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
+  PRIMARY KEY (id),
+  UNIQUE KEY x509_idx_certificate_fingerprint (fingerprint),
+  KEY x509_fk_certificate_issuer_certificate_id (issuer_certificate_id),
+  CONSTRAINT x509_fk_certificate_issuer_certificate_id FOREIGN KEY (issuer_certificate_id) REFERENCES x509_certificate (id) ON DELETE SET NULL ON UPDATE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
+
+CREATE TABLE x509_certificate_chain (
+  id int(10) unsigned NOT NULL AUTO_INCREMENT,
+  target_id int(10) unsigned NOT NULL,
+  length smallint(6) NOT NULL,
+  valid enum('yes','no') NOT NULL DEFAULT 'no',
+  invalid_reason varchar(255) NULL DEFAULT NULL,
+  ctime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
+  PRIMARY KEY (id)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
+
+CREATE TABLE x509_certificate_chain_link (
+  certificate_chain_id int(10) unsigned NOT NULL,
+  certificate_id int(10) unsigned NOT NULL,
+  `order` tinyint(4) NOT NULL,
+  ctime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
+  PRIMARY KEY (certificate_chain_id,certificate_id,`order`),
+  KEY x509_fk_certificate_chain_link_certificate_id (certificate_id),
+  CONSTRAINT x509_fk_certificate_chain_link_certificate_chain_id FOREIGN KEY (certificate_chain_id) REFERENCES x509_certificate_chain (id) ON DELETE CASCADE ON UPDATE CASCADE,
+  CONSTRAINT x509_fk_certificate_chain_link_certificate_id FOREIGN KEY (certificate_id) REFERENCES x509_certificate (id) ON DELETE CASCADE ON UPDATE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
+
+CREATE TABLE x509_certificate_subject_alt_name (
+  certificate_id int(10) unsigned NOT NULL,
+  hash binary(32) NOT NULL COMMENT 'sha256 hash of type=value',
+  `type` varchar(255) NOT NULL,
+  `value` varchar(255) NOT NULL,
+  ctime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
+  PRIMARY KEY (certificate_id,hash),
+  CONSTRAINT x509_fk_certificate_subject_alt_name_certificate_id FOREIGN KEY (certificate_id) REFERENCES x509_certificate (id) ON DELETE CASCADE ON UPDATE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
+
+CREATE TABLE x509_dn (
+  `hash` binary(32) NOT NULL,
+  `type` enum('issuer','subject') NOT NULL,
+  `order` tinyint(4) unsigned NOT NULL,
+  `key` varchar(255) NOT NULL,
+  `value` varchar(255) NOT NULL,
+  ctime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
+  PRIMARY KEY (`hash`,`type`,`order`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
+
+CREATE TABLE x509_job_run (
+  id int(10) unsigned NOT NULL AUTO_INCREMENT,
+  `name` varchar(255) NOT NULL,
+  total_targets int(10) NOT NULL,
+  finished_targets int(10) NOT NULL,
+  start_time timestamp NULL DEFAULT NULL,
+  end_time timestamp NULL DEFAULT NULL,
+  ctime timestamp NULL DEFAULT NULL,
+  mtime timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
+  PRIMARY KEY (id)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
+
+CREATE TABLE x509_target (
+  id int(10) unsigned NOT NULL AUTO_INCREMENT,
+  ip binary(16) NOT NULL,
+  `port` smallint unsigned NOT NULL,
+  hostname varchar(255) NULL DEFAULT NULL,
+  latest_certificate_chain_id int(10) unsigned NULL DEFAULT NULL,
+  ctime timestamp NULL DEFAULT NULL,
+  mtime timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
+  PRIMARY KEY (id),
+  INDEX x509_idx_target_ip_port (ip, port)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;