1 files changed, 51 insertions, 0 deletions
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);