From 067008c5f094ba9606daacbe540f6b929dc124ea Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Sun, 14 Apr 2024 15:31:28 +0200 Subject: Adding upstream version 1:1.3.2. Signed-off-by: Daniel Baumann --- schema/mysql-upgrades/1.0.0.sql | 27 +++++++++++ schema/mysql-upgrades/1.1.0.sql | 4 ++ schema/mysql-upgrades/1.2.0.sql | 103 ++++++++++++++++++++++++++++++++++++++++ schema/mysql-upgrades/1.3.0.sql | 51 ++++++++++++++++++++ 4 files changed, 185 insertions(+) create mode 100644 schema/mysql-upgrades/1.0.0.sql create mode 100644 schema/mysql-upgrades/1.1.0.sql create mode 100644 schema/mysql-upgrades/1.2.0.sql create mode 100644 schema/mysql-upgrades/1.3.0.sql (limited to 'schema/mysql-upgrades') 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; diff --git a/schema/mysql-upgrades/1.1.0.sql b/schema/mysql-upgrades/1.1.0.sql new file mode 100644 index 0000000..055d783 --- /dev/null +++ b/schema/mysql-upgrades/1.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/schema/mysql-upgrades/1.2.0.sql b/schema/mysql-upgrades/1.2.0.sql new file mode 100644 index 0000000..1fdd74f --- /dev/null +++ b/schema/mysql-upgrades/1.2.0.sql @@ -0,0 +1,103 @@ +ALTER TABLE x509_certificate + MODIFY self_signed enum('n', 'y', 'yes', 'no') NOT NULL DEFAULT 'n', + MODIFY ca enum('n', 'y', 'yes', 'no') NOT NULL, + MODIFY trusted enum('n', 'y', 'yes', 'no') NOT NULL DEFAULT 'n', + ADD COLUMN ctime_tmp bigint unsigned DEFAULT NULL, + ADD COLUMN mtime_tmp bigint unsigned DEFAULT NULL; + +UPDATE x509_certificate SET self_signed = 'y' WHERE self_signed = 'yes'; +UPDATE x509_certificate SET self_signed = 'n' WHERE self_signed = 'no'; + +UPDATE x509_certificate SET ca = 'y' WHERE ca = 'yes'; +UPDATE x509_certificate SET ca = 'n' WHERE ca = 'no'; + +UPDATE x509_certificate SET trusted = 'y' WHERE trusted = 'yes'; +UPDATE x509_certificate SET trusted = 'n' WHERE trusted = 'no'; + +UPDATE x509_certificate SET mtime_tmp = UNIX_TIMESTAMP(mtime) * 1000.0, ctime_tmp = UNIX_TIMESTAMP(ctime) * 1000.0; +UPDATE x509_certificate SET valid_from = valid_from * 1000.0, valid_to = valid_to * 1000.0; + +ALTER TABLE x509_certificate + MODIFY self_signed enum('n', 'y') NOT NULL DEFAULT 'n', + MODIFY ca enum('n', 'y') NOT NULL, + MODIFY trusted enum('n', 'y') NOT NULL DEFAULT 'n', + DROP COLUMN mtime, + DROP COLUMN ctime, + CHANGE COLUMN ctime_tmp ctime bigint unsigned DEFAULT NULL, + CHANGE COLUMN mtime_tmp mtime bigint unsigned DEFAULT NULL; + +ALTER TABLE x509_certificate_chain + MODIFY valid enum('n', 'y', 'yes', 'no') NOT NULL DEFAULT 'n', + ADD COLUMN ctime_tmp bigint unsigned NOT NULL; + +UPDATE x509_certificate_chain SET valid = 'y' WHERE valid = 'yes'; +UPDATE x509_certificate_chain SET valid = 'n' WHERE valid = 'no'; + +UPDATE x509_certificate_chain SET ctime_tmp = UNIX_TIMESTAMP(ctime) * 1000.0; + +ALTER TABLE x509_certificate_chain + MODIFY valid enum('n', 'y') NOT NULL DEFAULT 'n', + DROP ctime, + CHANGE ctime_tmp ctime bigint unsigned NOT NULL; + +ALTER TABLE x509_certificate_chain_link ADD COLUMN ctime_tmp bigint unsigned NOT NULL; + +UPDATE x509_certificate_chain_link SET ctime_tmp = UNIX_TIMESTAMP(ctime) * 1000.0; + +ALTER TABLE x509_certificate_chain_link + DROP COLUMN ctime, + CHANGE ctime_tmp ctime bigint unsigned NOT NULL; + +ALTER TABLE x509_certificate_subject_alt_name ADD COLUMN ctime_tmp bigint unsigned NOT NULL; + +UPDATE x509_certificate_subject_alt_name SET ctime_tmp = UNIX_TIMESTAMP(ctime) * 1000.0; + +ALTER TABLE x509_certificate_subject_alt_name + DROP COLUMN ctime, + CHANGE ctime_tmp ctime bigint unsigned NOT NULL; + +ALTER TABLE x509_dn ADD COLUMN ctime_tmp bigint unsigned NOT NULL; + +UPDATE x509_dn SET ctime_tmp = UNIX_TIMESTAMP(ctime) * 1000.0; + +ALTER TABLE x509_dn + DROP COLUMN ctime, + CHANGE ctime_tmp ctime bigint unsigned NOT NULL; + +ALTER TABLE x509_job_run + ADD COLUMN starttime_tmp bigint unsigned DEFAULT NULL, + ADD COLUMN endtime_tmp bigint unsigned DEFAULT NULL, + ADD COLUMN ctime_tmp bigint unsigned DEFAULT NULL, + ADD COLUMN mtime_tmp bigint unsigned DEFAULT NULL; + +UPDATE x509_job_run SET + starttime_tmp = UNIX_TIMESTAMP(start_time) * 1000.0, + endtime_tmp = UNIX_TIMESTAMP(end_time) * 1000.0, + ctime_tmp = UNIX_TIMESTAMP(ctime) * 1000.0, + mtime_tmp = UNIX_TIMESTAMP(mtime) * 1000.0; + +ALTER TABLE x509_job_run + DROP COLUMN start_time, + DROP COLUMN end_time, + DROP COLUMN mtime, + DROP COLUMN ctime, + CHANGE starttime_tmp start_time bigint unsigned DEFAULT NULL, + CHANGE endtime_tmp end_time bigint unsigned DEFAULT NULL, + CHANGE ctime_tmp ctime bigint unsigned DEFAULT NULL, + CHANGE mtime_tmp mtime bigint unsigned DEFAULT NULL; + +ALTER TABLE x509_target ADD COLUMN last_scan bigint unsigned DEFAULT NULL AFTER latest_certificate_chain_id; +UPDATE x509_target SET last_scan = UNIX_TIMESTAMP() * 1000.0; +ALTER TABLE x509_target MODIFY COLUMN last_scan bigint unsigned NOT NULL; + +ALTER TABLE x509_target + ADD COLUMN ctime_tmp bigint unsigned DEFAULT NULL, + ADD COLUMN mtime_tmp bigint unsigned DEFAULT NULL; + +UPDATE x509_target SET ctime_tmp = UNIX_TIMESTAMP(ctime) * 1000.0, mtime_tmp = UNIX_TIMESTAMP(mtime) * 1000.0; + +ALTER TABLE x509_target + DROP COLUMN ctime, + DROP COLUMN mtime, + CHANGE ctime_tmp ctime bigint unsigned DEFAULT NULL, + CHANGE mtime_tmp mtime bigint unsigned DEFAULT NULL; diff --git a/schema/mysql-upgrades/1.3.0.sql b/schema/mysql-upgrades/1.3.0.sql new file mode 100644 index 0000000..f31e8bd --- /dev/null +++ b/schema/mysql-upgrades/1.3.0.sql @@ -0,0 +1,51 @@ +CREATE TABLE x509_job ( + id int(10) unsigned NOT NULL AUTO_INCREMENT, + name varchar(255) NOT NULL COLLATE utf8mb4_unicode_ci, + author varchar(255) NOT NULL COLLATE utf8mb4_unicode_ci, + cidrs text NOT NULL, + ports text NOT NULL, + exclude_targets text DEFAULT NULL, + ctime bigint unsigned NOT NULL, + mtime bigint unsigned NOT NULL, + + PRIMARY KEY (id), + UNIQUE (name) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; + +CREATE TABLE x509_schedule ( + id int(10) unsigned NOT NULL AUTO_INCREMENT, + job_id int(10) unsigned NOT NULL, + name varchar(255) NOT NULL COLLATE utf8mb4_unicode_ci, + author varchar(255) NOT NULL COLLATE utf8mb4_unicode_ci, + config text NOT NULL, -- json + ctime bigint unsigned NOT NULL, + mtime bigint unsigned NOT NULL, + + PRIMARY KEY (id), + CONSTRAINT fk_x509_schedule_job FOREIGN KEY (job_id) REFERENCES x509_job (id) ON DELETE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; + +DELETE FROM x509_job_run; +ALTER TABLE x509_job_run + ADD COLUMN job_id int(10) unsigned NOT NULL AFTER id, + ADD COLUMN schedule_id int(10) unsigned DEFAULT NULL AFTER job_id, + DROP COLUMN `name`, + DROP COLUMN ctime, + DROP COLUMN mtime; +ALTER TABLE x509_job_run + ADD CONSTRAINT fk_x509_job_run_job FOREIGN KEY (job_id) REFERENCES x509_job (id) ON DELETE CASCADE, + ADD CONSTRAINT fk_x509_job_run_schedule FOREIGN KEY (schedule_id) REFERENCES x509_schedule (id) ON DELETE CASCADE; + +CREATE TABLE x509_schema ( + id int unsigned NOT NULL AUTO_INCREMENT, + version varchar(64) NOT NULL, + timestamp bigint unsigned NOT NULL, + success enum ('n', 'y') DEFAULT NULL, + reason text DEFAULT NULL, + + PRIMARY KEY (id), + CONSTRAINT idx_x509_schema_version UNIQUE (version) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC; + +INSERT INTO x509_schema (version, timestamp, success, reason) + VALUES ('1.3.0', UNIX_TIMESTAMP() * 1000, 'y', NULL); -- cgit v1.2.3