From: INOPIAE Date: Wed, 27 Jun 2018 06:51:45 +0000 (+0200) Subject: add: handling of who issued a certificate X-Git-Url: https://code.wpia.club/?p=gigi.git;a=commitdiff_plain;h=e19697179fb3d680062918e011f41e1d89e31778 add: handling of who issued a certificate fixes issue #155 Change-Id: I868c9b9147e647d940508c8f131691e5062c1cf3 --- diff --git a/src/club/wpia/gigi/database/DatabaseConnection.java b/src/club/wpia/gigi/database/DatabaseConnection.java index 04351d1a..b5e78eaa 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 = 36; + public static final int CURRENT_SCHEMA_VERSION = 37; 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 8c697f78..e9097fd1 100644 --- a/src/club/wpia/gigi/database/tableStructure.sql +++ b/src/club/wpia/gigi/database/tableStructure.sql @@ -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 index 00000000..1a33298e --- /dev/null +++ b/src/club/wpia/gigi/database/upgrade/from_36.sql @@ -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 diff --git a/src/club/wpia/gigi/dbObjects/Certificate.java b/src/club/wpia/gigi/dbObjects/Certificate.java index 28c32088..0654a416 100644 --- a/src/club/wpia/gigi/dbObjects/Certificate.java +++ b/src/club/wpia/gigi/dbObjects/Certificate.java @@ -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)); diff --git a/src/club/wpia/gigi/dbObjects/Name.java b/src/club/wpia/gigi/dbObjects/Name.java index d5bff5cb..fbcf10ee 100644 --- a/src/club/wpia/gigi/dbObjects/Name.java +++ b/src/club/wpia/gigi/dbObjects/Name.java @@ -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(); + } } diff --git a/src/club/wpia/gigi/dbObjects/User.java b/src/club/wpia/gigi/dbObjects/User.java index 9868e36a..1c6b39b3 100644 --- a/src/club/wpia/gigi/dbObjects/User.java +++ b/src/club/wpia/gigi/dbObjects/User.java @@ -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); } diff --git a/src/club/wpia/gigi/output/CertificateIterable.java b/src/club/wpia/gigi/output/CertificateIterable.java index 0932dfa2..4ff458e9 100644 --- a/src/club/wpia/gigi/output/CertificateIterable.java +++ b/src/club/wpia/gigi/output/CertificateIterable.java @@ -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) { diff --git a/src/club/wpia/gigi/output/CertificateTable.templ b/src/club/wpia/gigi/output/CertificateTable.templ index edf01ebc..30025248 100644 --- a/src/club/wpia/gigi/output/CertificateTable.templ +++ b/src/club/wpia/gigi/output/CertificateTable.templ @@ -11,6 +11,7 @@ + @@ -28,6 +29,7 @@ + diff --git a/tests/club/wpia/gigi/dbObjects/TestCertificate.java b/tests/club/wpia/gigi/dbObjects/TestCertificate.java index fe35729a..0b4d6549 100644 --- a/tests/club/wpia/gigi/dbObjects/TestCertificate.java +++ b/tests/club/wpia/gigi/dbObjects/TestCertificate.java @@ -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 index 00000000..b25970b6 --- /dev/null +++ b/tests/club/wpia/gigi/dbObjects/TestUser.java @@ -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()); + } + +}