Adding upstream version 1:1.3.2.upstream/1%1.3.2upstream

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

4 files changed, 185 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;
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);