From: INOPIAE <m.maengel@inopiae.de>
Date: Sat, 9 Jul 2016 13:54:57 +0000 (+0200)
Subject: add: check if the last valid verification is within the last 39 month
X-Git-Url: https://code.wpia.club/?p=gigi.git;a=commitdiff_plain;h=dd37e9febc793a8ab08ed3647108cbd017c74527

add: check if the last valid verification is within the last 39 month

Change-Id: I1f6e01b65043a883a178588fb71913b3c68e85f1
---

diff --git a/src/org/cacert/gigi/dbObjects/User.java b/src/org/cacert/gigi/dbObjects/User.java
index 7c45661e..8e9bc762 100644
--- a/src/org/cacert/gigi/dbObjects/User.java
+++ b/src/org/cacert/gigi/dbObjects/User.java
@@ -49,6 +49,11 @@ public class User extends CertificateOwner {
 
     public static final boolean POJAM_ENABLED = false;
 
+    /**
+     * Time in months a verification is considered "recent".
+     */
+    public static final int VERIFICATION_MONTHS = 39;
+
     protected User(GigiResultSet rs) {
         super(rs.getInt("id"));
         updateName(rs);
@@ -568,4 +573,14 @@ public class User extends CertificateOwner {
     private Assurance assuranceByRes(GigiResultSet res) {
         return new Assurance(res.getInt("id"), User.getById(res.getInt("from")), User.getById(res.getInt("to")), res.getString("location"), res.getString("method"), res.getInt("points"), res.getString("date"));
     }
+
+    public static boolean isInVerificationLimit(int id) {
+        try (GigiPreparedStatement ps = new GigiPreparedStatement("SELECT 1 FROM `notary` WHERE `to` = ? AND `when` > (now() - (interval '1 month' * ?)) AND (`expire` IS NULL OR `expire` > now()) AND `deleted` IS NULL;")) {
+            ps.setInt(1, id);
+            ps.setInt(2, VERIFICATION_MONTHS);
+
+            GigiResultSet rs = ps.executeQuery();
+            return rs.next();
+        }
+    }
 }
diff --git a/tests/org/cacert/gigi/dbObjects/TestAssurance.java b/tests/org/cacert/gigi/dbObjects/TestAssurance.java
new file mode 100644
index 00000000..e7ea35e1
--- /dev/null
+++ b/tests/org/cacert/gigi/dbObjects/TestAssurance.java
@@ -0,0 +1,129 @@
+package org.cacert.gigi.dbObjects;
+
+import static org.junit.Assert.*;
+
+import java.io.IOException;
+import java.sql.Timestamp;
+
+import org.cacert.gigi.database.GigiPreparedStatement;
+import org.cacert.gigi.testUtils.ManagedTest;
+import org.junit.Test;
+
+public class TestAssurance extends ManagedTest {
+
+    private final Timestamp yesterday = new Timestamp(System.currentTimeMillis() - 24L * 60 * 60 * 1000L);
+
+    private final Timestamp tomorrow = new Timestamp(System.currentTimeMillis() + 24L * 60 * 60 * 1000L);
+
+    /**
+     * at least 39 months ago, so is outside the window of
+     * {@link User#VERIFICATION_MONTHS}
+     */
+    private final Timestamp min39month = new Timestamp(System.currentTimeMillis() - 24L * 60 * 60 * 39 * 31 * 1000L);
+
+    /**
+     * at least 24 months ago (but less than 39), so is inside the window of
+     * {@link User#VERIFICATION_MONTHS}
+     */
+    private final Timestamp min24month = new Timestamp(System.currentTimeMillis() - 24L * 60 * 60 * 24 * 31 * 1000L);
+
+    private final int agentID;
+
+    private final int applicantID;
+
+    public TestAssurance() {
+        agentID = createAssuranceUser("a", "b", createUniqueName() + "@example.com", TEST_PASSWORD);
+        applicantID = createVerifiedUser("a", "c", createUniqueName() + "@example.com", TEST_PASSWORD);
+    }
+
+    // test for verification in 39 month period
+    private void enterAssurance(int agentID, int applicantID) {
+        try (GigiPreparedStatement ps = new GigiPreparedStatement("INSERT INTO `notary` SET `from`=?, `to`=?, `points`=?, `location`=?, `date`=?")) {
+            ps.setInt(1, agentID);
+            ps.setInt(2, applicantID);
+            ps.setInt(3, 10);
+            ps.setString(4, "test-location");
+            ps.setString(5, "2010-01-01");
+
+            ps.execute();
+        }
+    }
+
+    private void enterAssuranceExpired(int agentID, int applicantID, Timestamp expired) {
+        try (GigiPreparedStatement ps = new GigiPreparedStatement("INSERT INTO `notary` SET `from`=?, `to`=?, `points`=?, `location`=?, `date`=?, `expire`=? ")) {
+            ps.setInt(1, agentID);
+            ps.setInt(2, applicantID);
+            ps.setInt(3, 10);
+            ps.setString(4, "test-location");
+            ps.setString(5, "2010-01-01");
+            ps.setTimestamp(6, expired);
+            ps.execute();
+        }
+    }
+
+    private void enterAssuranceWhen(int agentID, int applicantID, Timestamp when) {
+        try (GigiPreparedStatement ps = new GigiPreparedStatement("INSERT INTO `notary` SET `from`=?, `to`=?, `points`=?, `location`=?, `date`=?, `when`=? ")) {
+            ps.setInt(1, agentID);
+            ps.setInt(2, applicantID);
+            ps.setInt(3, 10);
+            ps.setString(4, "test-location");
+            ps.setString(5, "2010-01-01");
+            ps.setTimestamp(6, when);
+            ps.execute();
+        }
+    }
+
+    private void enterAssuranceDeleted(int agentID, int applicantID, Timestamp deleted) {
+        try (GigiPreparedStatement ps = new GigiPreparedStatement("INSERT INTO `notary` SET `from`=?, `to`=?, `points`=?, `location`=?, `date`=?, `deleted`=? ")) {
+            ps.setInt(1, agentID);
+            ps.setInt(2, applicantID);
+            ps.setInt(3, 10);
+            ps.setString(4, "test-location");
+            ps.setString(5, "2010-01-01");
+            ps.setTimestamp(6, deleted);
+            ps.execute();
+        }
+    }
+
+    @Test
+    public void testVerificationYesterday() throws IOException {
+        enterAssuranceWhen(agentID, applicantID, yesterday);
+        assertTrue(User.isInVerificationLimit(applicantID));
+    }
+
+    @Test
+    public void testApprox24MonthAgo() throws IOException {
+        enterAssuranceWhen(agentID, applicantID, min24month);
+        assertTrue(User.isInVerificationLimit(applicantID));
+    }
+
+    @Test
+    public void testApprox39MonthAgo() throws IOException {
+        enterAssuranceWhen(agentID, applicantID, min39month);
+        assertFalse(User.isInVerificationLimit(applicantID));
+    }
+
+    @Test
+    public void testTomorrowExpired() throws IOException {
+        enterAssuranceExpired(agentID, applicantID, tomorrow);
+        assertTrue(User.isInVerificationLimit(applicantID));
+    }
+
+    @Test
+    public void testYesterdayExpired() throws IOException {
+        enterAssuranceExpired(agentID, applicantID, yesterday);
+        assertFalse(User.isInVerificationLimit(applicantID));
+    }
+
+    @Test
+    public void testNormal() throws IOException {
+        enterAssurance(agentID, applicantID);
+        assertTrue(User.isInVerificationLimit(applicantID));
+    }
+
+    @Test
+    public void testDeletedYesterday() throws IOException {
+        enterAssuranceDeleted(agentID, applicantID, yesterday);
+        assertFalse(User.isInVerificationLimit(applicantID));
+    }
+}