summaryrefslogtreecommitdiffstats
path: root/schema/pgsql-upgrades/1.3.0.sql
diff options
context:
space:
mode:
Diffstat (limited to '')
-rw-r--r--schema/pgsql-upgrades/1.3.0.sql49
1 files changed, 49 insertions, 0 deletions
diff --git a/schema/pgsql-upgrades/1.3.0.sql b/schema/pgsql-upgrades/1.3.0.sql
new file mode 100644
index 0000000..7e1f43a
--- /dev/null
+++ b/schema/pgsql-upgrades/1.3.0.sql
@@ -0,0 +1,49 @@
+CREATE TABLE x509_job (
+ id serial PRIMARY KEY,
+ name varchar(255) NOT NULL,
+ author varchar(255) NOT NULL,
+ cidrs text NOT NULL,
+ ports text NOT NULL,
+ exclude_targets text DEFAULT NULL,
+ ctime bigint NOT NULL,
+ mtime bigint NOT NULL,
+
+ UNIQUE (name)
+);
+
+CREATE TABLE x509_schedule (
+ id serial PRIMARY KEY,
+ job_id int NOT NULL,
+ name varchar(255) NOT NULL,
+ author varchar(255) NOT NULL,
+ config text NOT NULL, -- json
+ ctime bigint NOT NULL,
+ mtime bigint NOT NULL,
+
+ CONSTRAINT fk_x509_schedule_job FOREIGN KEY (job_id) REFERENCES x509_job (id) ON DELETE CASCADE
+);
+
+DELETE FROM x509_job_run;
+ALTER TABLE x509_job_run
+ ADD COLUMN job_id int NOT NULL,
+ ADD COLUMN schedule_id int DEFAULT NULL,
+ 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 serial,
+ version varchar(64) NOT NULL,
+ timestamp bigint NOT NULL,
+ success boolenum DEFAULT NULL,
+ reason text DEFAULT NULL,
+
+ CONSTRAINT pk_x509_schema PRIMARY KEY (id),
+ CONSTRAINT idx_x509_schema_version UNIQUE (version)
+);
+
+INSERT INTO x509_schema (version, timestamp, success, reason)
+ VALUES ('1.3.0', UNIX_TIMESTAMP() * 1000, 'y', NULL);