add: handling of who issued a certificate
authorINOPIAE <m.maengel@inopiae.de>
Wed, 27 Jun 2018 06:51:45 +0000 (08:51 +0200)
committerINOPIAE <m.maengel@inopiae.de>
Sat, 21 Jul 2018 14:41:28 +0000 (16:41 +0200)
fixes issue #155

Change-Id: I868c9b9147e647d940508c8f131691e5062c1cf3

src/club/wpia/gigi/database/DatabaseConnection.java
src/club/wpia/gigi/database/tableStructure.sql
src/club/wpia/gigi/database/upgrade/from_36.sql [new file with mode: 0644]
src/club/wpia/gigi/dbObjects/Certificate.java
src/club/wpia/gigi/dbObjects/Name.java
src/club/wpia/gigi/dbObjects/User.java
src/club/wpia/gigi/output/CertificateIterable.java
src/club/wpia/gigi/output/CertificateTable.templ
tests/club/wpia/gigi/dbObjects/TestCertificate.java
tests/club/wpia/gigi/dbObjects/TestUser.java [new file with mode: 0644]

index 04351d1..b5e78ea 100644 (file)
@@ -181,7 +181,7 @@ public class DatabaseConnection {
 
     }
 
-    public static final int CURRENT_SCHEMA_VERSION = 36;
+    public static final int CURRENT_SCHEMA_VERSION = 37;
 
     public static final int CONNECTION_TIMEOUT = 24 * 60 * 60;
 
index 8c697f7..e9097fd 100644 (file)
@@ -163,6 +163,7 @@ CREATE TABLE "certs" (
   "pkhash" char(40) DEFAULT NULL,
   "certhash" char(40) DEFAULT NULL,
   "description" varchar(100) NOT NULL DEFAULT '',
+  "actorid" int NOT NULL,
   PRIMARY KEY ("id")
 );
 CREATE INDEX ON "certs" ("pkhash");
@@ -171,6 +172,7 @@ CREATE INDEX ON "certs" ("created");
 CREATE INDEX ON "certs" ("memid");
 CREATE INDEX ON "certs" ("serial");
 CREATE INDEX ON "certs" ("expire");
+CREATE INDEX ON "certs" ("actorid");
 
 DROP TABLE IF EXISTS "certAvas";
 CREATE TABLE "certAvas" (
@@ -370,7 +372,7 @@ CREATE TABLE "schemeVersion" (
   "version" smallint NOT NULL,
   PRIMARY KEY ("version")
 );
-INSERT INTO "schemeVersion" (version)  VALUES(36);
+INSERT INTO "schemeVersion" (version)  VALUES(37);
 
 DROP TABLE IF EXISTS `passwordResetTickets`;
 CREATE TABLE `passwordResetTickets` (
diff --git a/src/club/wpia/gigi/database/upgrade/from_36.sql b/src/club/wpia/gigi/database/upgrade/from_36.sql
new file mode 100644 (file)
index 0000000..1a33298
--- /dev/null
@@ -0,0 +1,7 @@
+BEGIN;
+ALTER TABLE "certs" ADD COLUMN "actorid" int;
+CREATE INDEX ON "certs" ("actorid");
+UPDATE "certs" SET "actorid" = "memid" WHERE "profile" < 10;
+UPDATE "certs" SET "actorid" = (SELECT "org_admin"."memid" FROM "org_admin" WHERE "org_admin"."orgid" = "certs"."memid" LIMIT 1) WHERE "profile" >= 10;
+ALTER TABLE "certs" ALTER COLUMN "actorid" SET NOT NULL;
+COMMIT;
\ No newline at end of file
index 28c3208..0654a41 100644 (file)
@@ -166,6 +166,8 @@ public class Certificate implements IdCachable {
 
     private String description = "";
 
+    private User actor;
+
     public static final TranslateCommand NOT_LOADED = new TranslateCommand("Certificate could not be loaded");
 
     public static final TranslateCommand NOT_PARSED = new TranslateCommand("Certificate could not be parsed");
@@ -212,13 +214,15 @@ public class Certificate implements IdCachable {
         this.csrType = csrType;
         this.profile = profile;
         this.sans = Arrays.asList(sans);
+        this.actor = actor;
         synchronized (Certificate.class) {
 
-            try (GigiPreparedStatement inserter = new GigiPreparedStatement("INSERT INTO certs SET md=?::`mdType`, csr_type=?::`csrType`, memid=?, profile=?")) {
+            try (GigiPreparedStatement inserter = new GigiPreparedStatement("INSERT INTO certs SET md=?::`mdType`, csr_type=?::`csrType`, memid=?, profile=?, actorid=?")) {
                 inserter.setString(1, md.toString().toLowerCase());
                 inserter.setString(2, this.csrType.toString());
                 inserter.setInt(3, owner.getId());
                 inserter.setInt(4, profile.getId());
+                inserter.setInt(5, this.actor.getId());
                 inserter.execute();
                 id = inserter.lastInsertId();
             }
@@ -253,6 +257,7 @@ public class Certificate implements IdCachable {
         profile = CertificateProfile.getById(rs.getInt("profile"));
         this.serial = rs.getString("serial");
         this.description = rs.getString("description");
+        this.actor = User.getById(rs.getInt("actorid"));
 
         try (GigiPreparedStatement ps2 = new GigiPreparedStatement("SELECT `contents`, `type` FROM `subjectAlternativeNames` WHERE `certId`=?")) {
             ps2.setInt(1, id);
@@ -417,7 +422,7 @@ public class Certificate implements IdCachable {
         if (serial == null) {
             return null;
         }
-        try (GigiPreparedStatement ps = new GigiPreparedStatement("SELECT certs.id, " + CONCAT + " as `subject`, `md`,`memid`, `profile`, `certs`.`serial`, `certs`.`description` FROM `certs` LEFT JOIN `certAvas` ON `certAvas`.`certId`=`certs`.`id` WHERE `serial`=? GROUP BY `certs`.`id`")) {
+        try (GigiPreparedStatement ps = new GigiPreparedStatement("SELECT certs.id, " + CONCAT + " as `subject`, `md`,`memid`, `profile`, `certs`.`serial`, `certs`.`description`, `certs`.`actorid` FROM `certs` LEFT JOIN `certAvas` ON `certAvas`.`certId`=`certs`.`id` WHERE `serial`=? GROUP BY `certs`.`id`")) {
             ps.setString(1, serial.toString(16));
             GigiResultSet rs = ps.executeQuery();
             if ( !rs.next()) {
@@ -443,7 +448,7 @@ public class Certificate implements IdCachable {
         }
 
         try {
-            try (GigiPreparedStatement ps = new GigiPreparedStatement("SELECT certs.id, " + CONCAT + " as subject, md, memid, profile, certs.serial, description FROM `certs` LEFT JOIN `certAvas` ON `certAvas`.`certId`=certs.id WHERE certs.id=? GROUP BY certs.id")) {
+            try (GigiPreparedStatement ps = new GigiPreparedStatement("SELECT certs.id, " + CONCAT + " as subject, md, memid, profile, certs.serial, description, actorid FROM `certs` LEFT JOIN `certAvas` ON `certAvas`.`certId`=certs.id WHERE certs.id=? GROUP BY certs.id")) {
                 ps.setInt(1, id);
                 GigiResultSet rs = ps.executeQuery();
                 if ( !rs.next()) {
@@ -589,6 +594,10 @@ public class Certificate implements IdCachable {
         return description;
     }
 
+    public User getActor() {
+        return actor;
+    }
+
     public static Certificate locateCertificate(String serial, String certData) throws GigiApiException {
         if (serial != null && !serial.isEmpty()) {
             return getBySerial(normalizeSerial(serial));
index d5bff5c..fbcf10e 100644 (file)
@@ -45,6 +45,11 @@ public class Name implements Outputable, IdCachable {
          */
         public abstract void output(PrintWriter out);
 
+        /**
+         * @see Name#toInitialsString()
+         */
+        public abstract String toInitialsString();
+
     }
 
     private static class SingleName extends SchemedName {
@@ -70,6 +75,11 @@ public class Name implements Outputable, IdCachable {
             return singlePart.getValue();
         }
 
+        @Override
+        public String toInitialsString() {
+            return singlePart.getValue().substring(0, 1);
+        }
+
         @Override
         public NameSchemaType getSchemeName() {
             return NameSchemaType.SINGLE;
@@ -223,6 +233,14 @@ public class Name implements Outputable, IdCachable {
         public String toAbbreviatedString() {
             return firstNames[0].getValue() + " " + lastNames[0].getValue().charAt(0) + ".";
         }
+
+        @Override
+        public String toInitialsString() {
+
+            String initals = getInitialByNamePart(firstNames, lastNames, suffixes);
+
+            return initals;
+        }
     }
 
     private int id;
@@ -378,8 +396,8 @@ public class Name implements Outputable, IdCachable {
 
     /**
      * Transforms this String into a short form. This short form should not be
-     * unique. (For "western" names this would be
-     * "firstName firstCharOfLastName.".)
+     * unique. (For "western" names this would be "firstName
+     * firstCharOfLastName.".)
      * 
      * @return the short form of the name
      */
@@ -387,6 +405,17 @@ public class Name implements Outputable, IdCachable {
         return scheme.toAbbreviatedString();
     }
 
+    /**
+     * Transforms this Name object into a short form. This short form might not
+     * be unique. (For "western" names this would be all first letters of each
+     * name part)
+     * 
+     * @return the short form of the name
+     */
+    public String toInitialsString() {
+        return scheme.toInitialsString();
+    }
+
     public int getVerificationPoints() {
         try (GigiPreparedStatement query = new GigiPreparedStatement("SELECT SUM(`points`) FROM (SELECT DISTINCT ON (`from`, `method`) `points` FROM `notary` WHERE `to`=? AND `deleted` IS NULL AND (`expire` IS NULL OR `expire` > CURRENT_TIMESTAMP) ORDER BY `from`, `method`, `when` DESC) AS p")) {
             query.setInt(1, getId());
@@ -460,4 +489,27 @@ public class Name implements Outputable, IdCachable {
         }
         return owner;
     }
+
+    private static String getInitialByNamePart(NamePart[]... npa) {
+        StringBuilder initals = new StringBuilder();
+        for (NamePart[] np : npa) {
+            initals.append(getInitialByNamePart(np));
+        }
+        return initals.toString();
+    }
+
+    private static String getInitialByNamePart(NamePart[] np) {
+        StringBuilder initals = new StringBuilder();
+        for (NamePart p : np) {
+            switch (p.getValue()) {
+            case "-":
+            case "/":
+                break;
+            default:
+                initals.append(p.getValue().substring(0, 1).toUpperCase());
+                break;
+            }
+        }
+        return initals.toString();
+    }
 }
index 9868e36..1c6b39b 100644 (file)
@@ -23,7 +23,6 @@ import club.wpia.gigi.email.EmailProvider;
 import club.wpia.gigi.localisation.Language;
 import club.wpia.gigi.output.DateSelector;
 import club.wpia.gigi.pages.PasswordResetPage;
-import club.wpia.gigi.passwords.PasswordStrengthChecker;
 import club.wpia.gigi.util.CalendarUtil;
 import club.wpia.gigi.util.DayDate;
 import club.wpia.gigi.util.Notary;
@@ -454,6 +453,10 @@ public class User extends CertificateOwner {
 
     }
 
+    public synchronized String getInitials() {
+        return preferredName.toInitialsString();
+    }
+
     public boolean isInGroup(Group g) {
         return groups.contains(g);
     }
index 0932dfa..4ff458e 100644 (file)
@@ -46,6 +46,13 @@ public class CertificateIterable implements IterableDataset {
         vars.put("classIssued", "");
         vars.put("classExpired", "");
         vars.put("revoked", l.getTranslation("N/A"));
+        vars.put("actorinitials", l.getTranslation("N/A"));
+        vars.put("actorname", l.getTranslation("N/A"));
+
+        if (c.getActor() != null) {
+            vars.put("actorinitials", c.getActor().getInitials());
+            vars.put("actorname", c.getActor().getPreferredName().toString() + " <" + c.getActor().getEmail() + ">");
+        }
 
         try {
             if (st == CertificateStatus.ISSUED || st == CertificateStatus.REVOKED) {
index edf01eb..3002524 100644 (file)
@@ -11,6 +11,7 @@
 <th><?=_Expires?></th>
 <th><?=_Login?></th>
 <th><?=_Description?></th>
+<th><?=_Issued by?></th>
 </tr></thead>
 <tbody>
 <? foreach($certs) {?>
@@ -28,6 +29,7 @@
        <td class="<?=$classExpired?>"><?=$expire?></td>
        <td><?=$login?></td>
        <td><?=$description?></td>
+       <td><span title="<?=$actorname?>"><?=$actorinitials?></span></td>
 </tr>
 <? } ?>
 </tbody>
index fe35729..0b4d654 100644 (file)
@@ -67,4 +67,14 @@ public class TestCertificate extends ClientBusinessTest {
         assertEquals(key, c.getAttachment(AttachmentType.CSR));
         assertEquals("b", c.getAttachment(AttachmentType.CRT));
     }
+
+    @Test
+    public void testActor() 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());
+
+        assertEquals(u, c.getActor());
+        assertEquals("AB", c.getActor().getInitials());
+    }
 }
diff --git a/tests/club/wpia/gigi/dbObjects/TestUser.java b/tests/club/wpia/gigi/dbObjects/TestUser.java
new file mode 100644 (file)
index 0000000..b25970b
--- /dev/null
@@ -0,0 +1,67 @@
+package club.wpia.gigi.dbObjects;
+
+import static org.junit.Assert.*;
+
+import org.junit.Test;
+
+import club.wpia.gigi.GigiApiException;
+import club.wpia.gigi.dbObjects.NamePart.NamePartType;
+import club.wpia.gigi.testUtils.ClientBusinessTest;
+
+public class TestUser extends ClientBusinessTest {
+
+    @Test
+    public void testGetInitials() throws GigiApiException {
+        User u0 = User.getById(createVerificationUser("Kurti", "Hansel", createUniqueName() + "@email.com", TEST_PASSWORD));
+
+        assertEquals("KH", u0.getInitials());
+
+        // single name as preferred name
+        Name sName = new Name(u0, new NamePart(NamePartType.SINGLE_NAME, "SingleName"));
+        u0.setPreferredName(sName);
+        assertEquals("S", u0.getInitials());
+
+        // second western style name as preferred name
+        NamePart[] np = {
+                new NamePart(NamePartType.FIRST_NAME, "John"), new NamePart(NamePartType.FIRST_NAME, "Walker"), new NamePart(NamePartType.LAST_NAME, "Hansel")
+        };
+        sName = new Name(u0, np);
+        u0.setPreferredName(sName);
+        assertEquals("JWH", u0.getInitials());
+        // second western style name as preferred name
+
+        NamePart[] np1 = {
+                new NamePart(NamePartType.FIRST_NAME, "Dieter"), new NamePart(NamePartType.LAST_NAME, "Hansel"), new NamePart(NamePartType.LAST_NAME, "von"), new NamePart(NamePartType.LAST_NAME, "Hof"), new NamePart(NamePartType.SUFFIX, "Meister")
+        };
+        sName = new Name(u0, np1);
+        u0.setPreferredName(sName);
+        assertEquals("DHVHM", u0.getInitials());
+
+        // western style name with dash as preferred name (Hans-Peter)
+        NamePart[] np2 = {
+                new NamePart(NamePartType.FIRST_NAME, "Hans-Peter"), new NamePart(NamePartType.LAST_NAME, "Hansel")
+        };
+        sName = new Name(u0, np2);
+        u0.setPreferredName(sName);
+        assertEquals("HH", u0.getInitials());
+
+        // western style name with dash as separate entry as preferred name
+        // (Hans - Peter)
+        NamePart[] np3 = {
+                new NamePart(NamePartType.FIRST_NAME, "Hans"), new NamePart(NamePartType.FIRST_NAME, "-"), new NamePart(NamePartType.FIRST_NAME, "Joachim"), new NamePart(NamePartType.LAST_NAME, "Hansel")
+        };
+        sName = new Name(u0, np3);
+        u0.setPreferredName(sName);
+        assertEquals("HJH", u0.getInitials());
+
+        // western style name with / as separate entry as preferred name
+        // (Hans / Peter)
+        NamePart[] np4 = {
+                new NamePart(NamePartType.FIRST_NAME, "Hans"), new NamePart(NamePartType.FIRST_NAME, "/"), new NamePart(NamePartType.FIRST_NAME, "Peter"), new NamePart(NamePartType.LAST_NAME, "Hansel")
+        };
+        sName = new Name(u0, np4);
+        u0.setPreferredName(sName);
+        assertEquals("HPH", u0.getInitials());
+    }
+
+}