From: Felix Dörre <felix@dogcraft.de>
Date: Sat, 4 Nov 2017 22:50:45 +0000 (+0100)
Subject: add: text-attachments for certificates
X-Git-Url: https://code.wpia.club/?p=gigi.git;a=commitdiff_plain;h=c4c60e1b9446e5ab69b8431ce71a2fbe11d47ef5

add: text-attachments for certificates

Change-Id: Ie19e3229557f829f4c6ec9617daa34f3238b1e85
---

diff --git a/src/club/wpia/gigi/database/DatabaseConnection.java b/src/club/wpia/gigi/database/DatabaseConnection.java
index 3f0acd8a..2a3691bf 100644
--- a/src/club/wpia/gigi/database/DatabaseConnection.java
+++ b/src/club/wpia/gigi/database/DatabaseConnection.java
@@ -122,7 +122,7 @@ public class DatabaseConnection {
 
     }
 
-    public static final int CURRENT_SCHEMA_VERSION = 30;
+    public static final int CURRENT_SCHEMA_VERSION = 31;
 
     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 416d6a18..0a96801c 100644
--- a/src/club/wpia/gigi/database/tableStructure.sql
+++ b/src/club/wpia/gigi/database/tableStructure.sql
@@ -381,7 +381,7 @@ CREATE TABLE "schemeVersion" (
   "version" smallint NOT NULL,
   PRIMARY KEY ("version")
 );
-INSERT INTO "schemeVersion" (version)  VALUES(30);
+INSERT INTO "schemeVersion" (version)  VALUES(31);
 
 DROP TABLE IF EXISTS `passwordResetTickets`;
 CREATE TABLE `passwordResetTickets` (
@@ -686,3 +686,15 @@ CREATE TABLE "nameParts" (
   "type" "namePartType" NOT NULL,
   "value" varchar(255) NOT NULL
 );
+
+
+DROP TABLE IF EXISTS "certificateAttachment";
+DROP TYPE IF EXISTS "certificateAttachmentType";
+CREATE TYPE "certificateAttachmentType" AS ENUM ('CSR','CRT');
+
+CREATE TABLE "certificateAttachment" (
+  "certid" int NOT NULL,
+  "type" "certificateAttachmentType" NOT NULL,
+  "content" text NOT NULL,
+  PRIMARY KEY ("certid", "type")
+);
diff --git a/src/club/wpia/gigi/database/upgrade/from_30.sql b/src/club/wpia/gigi/database/upgrade/from_30.sql
new file mode 100644
index 00000000..bc9ccd41
--- /dev/null
+++ b/src/club/wpia/gigi/database/upgrade/from_30.sql
@@ -0,0 +1,8 @@
+CREATE TYPE "certificateAttachmentType" AS ENUM ('CSR','CRT');
+
+CREATE TABLE "certificateAttachment" (
+  "certid" int NOT NULL,
+  "type" "certificateAttachmentType" NOT NULL,
+  "content" text NOT NULL,
+  PRIMARY KEY ("certid", "type")
+);
diff --git a/src/club/wpia/gigi/dbObjects/Certificate.java b/src/club/wpia/gigi/dbObjects/Certificate.java
index bd1e7744..d5679029 100644
--- a/src/club/wpia/gigi/dbObjects/Certificate.java
+++ b/src/club/wpia/gigi/dbObjects/Certificate.java
@@ -47,6 +47,15 @@ public class Certificate implements IdCachable {
         }
     }
 
+    public enum AttachmentType implements DBEnum {
+        CSR, CRT;
+
+        @Override
+        public String getDBName() {
+            return toString();
+        }
+    }
+
     public enum SANType implements DBEnum {
         EMAIL("email"), DNS("DNS");
 
@@ -553,4 +562,35 @@ public class Certificate implements IdCachable {
         }
         return certs;
     }
+
+    public void addAttachment(AttachmentType tp, String data) throws GigiApiException {
+        if (getAttachment(tp) != null) {
+            throw new GigiApiException("Cannot override attachment");
+        }
+        if (data == null) {
+            throw new GigiApiException("Attachment must not be null");
+        }
+        try (GigiPreparedStatement ps = new GigiPreparedStatement("INSERT INTO `certificateAttachment` SET `certid`=?, `type`=?::`certificateAttachmentType`, `content`=?")) {
+            ps.setInt(1, getId());
+            ps.setEnum(2, tp);
+            ps.setString(3, data);
+            ps.execute();
+        }
+    }
+
+    public String getAttachment(AttachmentType tp) throws GigiApiException {
+        try (GigiPreparedStatement ps = new GigiPreparedStatement("SELECT `content` FROM `certificateAttachment` WHERE `certid`=? AND `type`=?::`certificateAttachmentType`")) {
+            ps.setInt(1, getId());
+            ps.setEnum(2, tp);
+            GigiResultSet rs = ps.executeQuery();
+            if ( !rs.next()) {
+                return null;
+            }
+            String s = rs.getString(1);
+            if (rs.next()) {
+                throw new GigiApiException("Invalid database state");
+            }
+            return s;
+        }
+    }
 }
diff --git a/tests/club/wpia/gigi/dbObjects/TestCertificate.java b/tests/club/wpia/gigi/dbObjects/TestCertificate.java
index 519bd59e..694bc491 100644
--- a/tests/club/wpia/gigi/dbObjects/TestCertificate.java
+++ b/tests/club/wpia/gigi/dbObjects/TestCertificate.java
@@ -9,8 +9,7 @@ import java.security.KeyPair;
 import org.junit.Test;
 
 import club.wpia.gigi.GigiApiException;
-import club.wpia.gigi.dbObjects.Certificate;
-import club.wpia.gigi.dbObjects.Digest;
+import club.wpia.gigi.dbObjects.Certificate.AttachmentType;
 import club.wpia.gigi.dbObjects.Certificate.CSRType;
 import club.wpia.gigi.testUtils.ClientBusinessTest;
 
@@ -32,4 +31,43 @@ public class TestCertificate extends ClientBusinessTest {
         c.setLoginEnabled(false);
         assertFalse(c.isLoginEnabled());
     }
+
+    @Test
+    public void testAttachment() throws GeneralSecurityException, IOException, GigiApiException {
+        KeyPair kp = generateKeypair();
+        String key = generatePEMCSR(kp, "CN=testmail@example.com");
+        Certificate c = new Certificate(u, u, Certificate.buildDN("CN", "testmail@example.com"), Digest.SHA256, key, CSRType.CSR, getClientProfile());
+        assertNull(c.getAttachment(AttachmentType.CRT));
+        assertNull(c.getAttachment(AttachmentType.CSR));
+        c.addAttachment(AttachmentType.CSR, "a");
+        assertNull(c.getAttachment(AttachmentType.CRT));
+        assertEquals("a", c.getAttachment(AttachmentType.CSR));
+        try {
+            c.addAttachment(AttachmentType.CSR, "different CSR");
+            fail("double add attachment must fail");
+        } catch (GigiApiException e) {
+            // expected
+        }
+        assertNull(c.getAttachment(AttachmentType.CRT));
+        assertEquals("a", c.getAttachment(AttachmentType.CSR));
+        try {
+            c.addAttachment(AttachmentType.CRT, null);
+            fail("attachment must not be null");
+        } catch (GigiApiException e) {
+            // expected
+        }
+        assertNull(c.getAttachment(AttachmentType.CRT));
+        assertEquals("a", c.getAttachment(AttachmentType.CSR));
+        c.addAttachment(AttachmentType.CRT, "b");
+        assertEquals("a", c.getAttachment(AttachmentType.CSR));
+        assertEquals("b", c.getAttachment(AttachmentType.CRT));
+        try {
+            c.addAttachment(AttachmentType.CRT, "different CRT");
+            fail("double add attachment must fail");
+        } catch (GigiApiException e) {
+            // expected
+        }
+        assertEquals("a", c.getAttachment(AttachmentType.CSR));
+        assertEquals("b", c.getAttachment(AttachmentType.CRT));
+    }
 }