1 files changed, 27 insertions, 0 deletions
diff --git a/schema/mysql-upgrades/1.0.0.sql b/schema/mysql-upgrades/1.0.0.sql
new file mode 100644
index 0000000..28b3e7d
--- /dev/null
+++ b/schema/mysql-upgrades/1.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;