add: table to store certificate logs

Change-Id: Icf0b8af5b7c40264acdf0d08f395f5f235962dfc

diff --git a/src/club/wpia/gigi/database/DatabaseConnection.java b/src/club/wpia/gigi/database/DatabaseConnection.java
index 51e58728999046e695a2e09b3d4197c747d6b245..10e81e79df1f1119a05fc5e86606c7c794585c33 100644 (file)
--- 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 bf457af37f3ee6acbac12fa65281677bdbd6f9e1..440bdec788db8fee8de4e8c532dd4b6e439dd97f 100644 (file)
--- 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 (file)
index 0000000..53e0d45
--- /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 b1c31509e88926e38f2dd49752e4852bd92d48a4..67a546c9e7279a8bd7acf364a9d710ddc291c4d5 100644 (file)
--- 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=?");