From c7ba0d7ba91c9d3d12c651ecba0ddf8f27835d4c Mon Sep 17 00:00:00 2001 From: =?utf8?q?Felix=20D=C3=B6rre?= Date: Mon, 6 Nov 2017 22:00:58 +0100 Subject: [PATCH] add: table to store certificate logs Change-Id: Icf0b8af5b7c40264acdf0d08f395f5f235962dfc --- src/club/wpia/gigi/database/DatabaseConnection.java | 2 +- src/club/wpia/gigi/database/tableStructure.sql | 13 +++++++++++-- src/club/wpia/gigi/database/upgrade/from_33.sql | 9 +++++++++ util-testing/club/wpia/gigi/util/SimpleSigner.java | 2 +- 4 files changed, 22 insertions(+), 4 deletions(-) create mode 100644 src/club/wpia/gigi/database/upgrade/from_33.sql diff --git a/src/club/wpia/gigi/database/DatabaseConnection.java b/src/club/wpia/gigi/database/DatabaseConnection.java index 51e58728..10e81e79 100644 --- a/src/club/wpia/gigi/database/DatabaseConnection.java +++ b/src/club/wpia/gigi/database/DatabaseConnection.java @@ -181,7 +181,7 @@ public class DatabaseConnection { } - public static final int CURRENT_SCHEMA_VERSION = 33; + public static final int CURRENT_SCHEMA_VERSION = 34; public static final int CONNECTION_TIMEOUT = 24 * 60 * 60; diff --git a/src/club/wpia/gigi/database/tableStructure.sql b/src/club/wpia/gigi/database/tableStructure.sql index bf457af3..440bdec7 100644 --- a/src/club/wpia/gigi/database/tableStructure.sql +++ b/src/club/wpia/gigi/database/tableStructure.sql @@ -243,7 +243,7 @@ CREATE TABLE "jobs" ( "targetId" int NOT NULL, "task" "jobType" NOT NULL, "state" "jobState" NOT NULL DEFAULT 'open', - "warning" smallint NOT NULL DEFAULT '0', + "attempt" smallint NOT NULL DEFAULT '0', "executeFrom" DATE, "executeTo" VARCHAR(11), PRIMARY KEY ("id") @@ -378,7 +378,7 @@ CREATE TABLE "schemeVersion" ( "version" smallint NOT NULL, PRIMARY KEY ("version") ); -INSERT INTO "schemeVersion" (version) VALUES(33); +INSERT INTO "schemeVersion" (version) VALUES(34); DROP TABLE IF EXISTS `passwordResetTickets`; CREATE TABLE `passwordResetTickets` ( @@ -695,3 +695,12 @@ CREATE TABLE "certificateAttachment" ( "content" text NOT NULL, PRIMARY KEY ("certid", "type") ); + +DROP TABLE IF EXISTS "jobLog"; +CREATE TABLE "jobLog" ( + "jobid" int NOT NULL, + "attempt" smallint NOT NULL, + "content" text NOT NULL, + PRIMARY KEY ("jobid", "attempt") +); +CREATE INDEX ON "jobLog" ("jobid"); diff --git a/src/club/wpia/gigi/database/upgrade/from_33.sql b/src/club/wpia/gigi/database/upgrade/from_33.sql new file mode 100644 index 00000000..53e0d458 --- /dev/null +++ b/src/club/wpia/gigi/database/upgrade/from_33.sql @@ -0,0 +1,9 @@ +CREATE TABLE "jobLog" ( + "jobid" int NOT NULL, + "attempt" smallint NOT NULL, + "content" text NOT NULL, + PRIMARY KEY ("jobid", "attempt") +); +CREATE INDEX ON "jobLog" ("jobid"); + +ALTER TABLE "jobs" RENAME COLUMN "warning" TO "attempt"; diff --git a/util-testing/club/wpia/gigi/util/SimpleSigner.java b/util-testing/club/wpia/gigi/util/SimpleSigner.java index b1c31509..67a546c9 100644 --- a/util-testing/club/wpia/gigi/util/SimpleSigner.java +++ b/util-testing/club/wpia/gigi/util/SimpleSigner.java @@ -139,7 +139,7 @@ public class SimpleSigner { "WHERE `certId`=?"); updateMail = new GigiPreparedStatement("UPDATE certs SET created=NOW(), serial=?, caid=?, expire=? WHERE id=?"); - warnMail = new GigiPreparedStatement("UPDATE jobs SET warning=warning+1, state=CASE WHEN warning<3 THEN 'open'::`jobState` ELSE 'error'::`jobState` END WHERE id=?"); + warnMail = new GigiPreparedStatement("UPDATE jobs SET attempt=attempt+1, state=CASE WHEN attempt<3 THEN 'open'::`jobState` ELSE 'error'::`jobState` END WHERE id=?"); revoke = new GigiPreparedStatement("SELECT certs.id, jobs.id FROM jobs INNER JOIN certs ON jobs.`targetId`=certs.id" + " WHERE jobs.state='open' AND task='revoke'"); revokeCompleted = new GigiPreparedStatement("UPDATE `certs` SET revoked=NOW() WHERE id=?"); -- 2.39.2