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 3f0acd8a4fc2c41252867d1d3433834327d7862f..2a3691bfbd6639680d2da62a5b5c7ad46d6f1856 100644 (file)
--- 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;
 
 
     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 416d6a18d06fa2a4fc4079b7cff0844c08cb1fb2..0a96801cc5eee16082a22dcfc1631f334db10487 100644 (file)
--- 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")
 );
   "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` (
 
 DROP TABLE IF EXISTS `passwordResetTickets`;
 CREATE TABLE `passwordResetTickets` (


@@ -686,3 +686,15 @@ CREATE TABLE "nameParts" (
   "type" "namePartType" NOT NULL,
   "value" varchar(255) NOT NULL
 );
   "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 (file)
index 0000000..bc9ccd4
--- /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 bd1e7744f7bd939185fe5516dfc0920e24e96e0c..d5679029c474d51b819049fe0e4083e419375ac3 100644 (file)
--- 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");
 
     public enum SANType implements DBEnum {
         EMAIL("email"), DNS("DNS");
 


@@ -553,4 +562,35 @@ public class Certificate implements IdCachable {
         }
         return certs;
     }
         }
         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 519bd59e8acc98ac196f429bb97584295b910976..694bc491c8f514550664419d54528cecf9273b72 100644 (file)
--- 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 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;
 
 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());
     }
         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));
+    }

 }
 }