Added expiration time check for member invitation.

Change-Id: Id119811e7cae6e9418d29618bd89e4f6812bc95a
diff --git a/core/Changes b/core/Changes
index d1d629e..1d33f8d 100644
--- a/core/Changes
+++ b/core/Changes
@@ -3,7 +3,8 @@
 	- added Changes file (margaretha)
 	- merged BeanConfigBaseTest to BeanConfigTest in /full (margaretha)
 	- added status code for already deleted entry (margaretha)
-	- updated library versions and java environment (margaretha)	
+	- updated library versions and java environment (margaretha)
+	- added status codes (margaretha)
 0.59.9 	2017-11-08
 	- fixed missing exception in JsonUtils (margaretha)
 	- fixed and restructured KustvaktResponseHandler (margaretha)
diff --git a/core/src/main/java/de/ids_mannheim/korap/exceptions/StatusCodes.java b/core/src/main/java/de/ids_mannheim/korap/exceptions/StatusCodes.java
index 5b57438..12a28c9 100644
--- a/core/src/main/java/de/ids_mannheim/korap/exceptions/StatusCodes.java
+++ b/core/src/main/java/de/ids_mannheim/korap/exceptions/StatusCodes.java
@@ -79,9 +79,14 @@
     public static final int DB_INSERT_SUCCESSFUL = 505;
     public static final int DB_DELETE_SUCCESSFUL = 506;
     public static final int DB_UPDATE_SUCCESSFUL = 507;
-    
     public static final int DB_ENTRY_EXISTS = 508;
-    public static final int DB_ENTRY_DELETED = 509;
+    
+    
+    // User group and member 
+    public static final int GROUP_MEMBER_EXISTS = 601;
+    public static final int GROUP_MEMBER_DELETED = 602;
+    public static final int INVITATION_EXPIRED = 602;
+    
 
 //    public static final int ARGUMENT_VALIDATION_FAILURE = 700;
     // public static final int ARGUMENT_VALIDATION_FAILURE = 701;
diff --git a/full/Changes b/full/Changes
index b147983..7e42673 100644
--- a/full/Changes
+++ b/full/Changes
@@ -1,15 +1,16 @@
-0.59.10	2018-02-01 
+0.59.10	2018-02-06 
 	- added sort VC by id (margaretha)
 	- added test cases regarding VC sharing (margaretha)
 	- implemented withdraw VC from publication (margaretha)
 	- added Changes file (margaretha)
-	- implemented add users to group (margaretha)
+	- implemented add/invite users to group (margaretha)
 	- implemented delete user-group and member tasks (margaretha)
 	- added userMemberStatus in group lists (margaretha)
 	- updated and added SQL test data (margaretha)
 	- added user group related tests (margaretha)
 	- implemented custom configuration for deleting user groups and members (margaretha)
 	- updated library versions and java environment (margaretha)
+	- added expiration time check for member invitation (margaretha)
 	
 0.59.9 2018-01-19
 	- restructured basic authentication (margaretha)
@@ -21,7 +22,7 @@
 	- fixed foundry rewrite for korap span without wrap node (margaretha)
 	- implemented list user group (margaretha)
 	- implemented delete VC task (margaretha)
-	- implemented create user-group, subscribe to usergroup, unsubscribe to user group tasks(margaretha)
+	- implemented create user-group, subscribe to user-groups, unsubscribe to user-groups tasks(margaretha)
 	- fixed handling JSON mapping exception for missing enums (margaretha)
     - implemented list VC task (margaretha)
     - added KoralQuery in VC lists (margaretha)
diff --git a/full/pom.xml b/full/pom.xml
index bccc9bf..a65127c 100644
--- a/full/pom.xml
+++ b/full/pom.xml
@@ -194,6 +194,11 @@
 			<scope>provided</scope>
 		</dependency>
 		<dependency>
+		    <groupId>org.hibernate</groupId>
+		    <artifactId>hibernate-java8</artifactId>
+		    <version>${hibernate.version}</version>
+		</dependency>
+		<dependency>
 		    <groupId>org.javassist</groupId>
 		    <artifactId>javassist</artifactId>
 		    <version>3.22.0-GA</version>
diff --git a/full/src/main/java/de/ids_mannheim/korap/dao/UserGroupMemberDao.java b/full/src/main/java/de/ids_mannheim/korap/dao/UserGroupMemberDao.java
index 9803461..5aa06d8 100644
--- a/full/src/main/java/de/ids_mannheim/korap/dao/UserGroupMemberDao.java
+++ b/full/src/main/java/de/ids_mannheim/korap/dao/UserGroupMemberDao.java
@@ -36,45 +36,34 @@
         entityManager.persist(member);
     }
 
-    public void addMembers (List<UserGroupMember> members)
-            throws KustvaktException {
-        ParameterChecker.checkObjectValue(members, "List<UserGroupMember>");
+//    @Deprecated
+//    public void addMembers (List<UserGroupMember> members)
+//            throws KustvaktException {
+//        ParameterChecker.checkObjectValue(members, "List<UserGroupMember>");
+//
+//        for (UserGroupMember member : members) {
+//            addMember(member);
+//        }
+//    }
 
-        for (UserGroupMember member : members) {
-            addMember(member);
-        }
+    public void updateMember (UserGroupMember member)
+            throws KustvaktException {
+        ParameterChecker.checkObjectValue(member, "UserGroupMember");
+        entityManager.merge(member);
     }
 
-    public void approveMember (String userId, int groupId)
-            throws KustvaktException {
-        ParameterChecker.checkStringValue(userId, "userId");
-        ParameterChecker.checkIntegerValue(groupId, "groupId");
-
-        UserGroupMember member = retrieveMemberById(userId, groupId);
-        if (member.getStatus().equals(GroupMemberStatus.DELETED)) {
-            throw new KustvaktException(StatusCodes.NOTHING_CHANGED, "Username "
-                    + userId + " had been deleted in group " + groupId, userId);
-        }
-
-        member.setStatus(GroupMemberStatus.ACTIVE);
-        entityManager.persist(member);
-    }
-
-    public void deleteMember (String userId, int groupId, String deletedBy,
+    public void deleteMember (UserGroupMember member, String deletedBy,
             boolean isSoftDelete) throws KustvaktException {
-        ParameterChecker.checkStringValue(userId, "userId");
-        ParameterChecker.checkIntegerValue(groupId, "groupId");
+        ParameterChecker.checkObjectValue(member, "UserGroupMember");
+        ParameterChecker.checkStringValue(deletedBy, "deletedBy");
 
-        UserGroupMember member = retrieveMemberById(userId, groupId);
-        GroupMemberStatus status = member.getStatus();
-        if (isSoftDelete && status.equals(GroupMemberStatus.DELETED)) {
-            throw new KustvaktException(StatusCodes.DB_ENTRY_DELETED,
-                    userId + " has already been deleted from the group.",
-                    userId);
+        if (!entityManager.contains(member)) {
+            member = entityManager.merge(member);
         }
 
         if (isSoftDelete) {
             member.setStatus(GroupMemberStatus.DELETED);
+            member.setDeletedBy(deletedBy);
             entityManager.persist(member);
         }
         else {
diff --git a/full/src/main/java/de/ids_mannheim/korap/entity/UserGroupMember.java b/full/src/main/java/de/ids_mannheim/korap/entity/UserGroupMember.java
index 6e7f874..f148959 100644
--- a/full/src/main/java/de/ids_mannheim/korap/entity/UserGroupMember.java
+++ b/full/src/main/java/de/ids_mannheim/korap/entity/UserGroupMember.java
@@ -1,5 +1,7 @@
 package de.ids_mannheim.korap.entity;
 
+import java.time.ZonedDateTime;
+import java.util.Date;
 import java.util.List;
 
 import javax.persistence.Column;
@@ -47,6 +49,10 @@
     private String createdBy;
     @Column(name = "deleted_by")
     private String deletedBy;
+    
+    // auto update in the database
+    @Column(name = "status_date")
+    private ZonedDateTime statusDate;
 
     @Enumerated(EnumType.STRING)
     private GroupMemberStatus status;
diff --git a/full/src/main/java/de/ids_mannheim/korap/service/UserGroupService.java b/full/src/main/java/de/ids_mannheim/korap/service/UserGroupService.java
index 15f6052..d39eb89 100644
--- a/full/src/main/java/de/ids_mannheim/korap/service/UserGroupService.java
+++ b/full/src/main/java/de/ids_mannheim/korap/service/UserGroupService.java
@@ -1,9 +1,15 @@
 package de.ids_mannheim.korap.service;
 
+import java.time.LocalDateTime;
+import java.time.ZoneId;
+import java.time.ZonedDateTime;
 import java.util.ArrayList;
 import java.util.Collections;
+import java.util.Date;
 import java.util.List;
 
+import org.slf4j.Logger;
+import org.slf4j.LoggerFactory;
 import org.springframework.beans.factory.annotation.Autowired;
 import org.springframework.stereotype.Service;
 
@@ -37,6 +43,8 @@
 @Service
 public class UserGroupService {
 
+    private static Logger jlog =
+            LoggerFactory.getLogger(UserGroupService.class);
     @Autowired
     private UserGroupDao userGroupDao;
     @Autowired
@@ -199,7 +207,8 @@
     public void deleteAutoHiddenGroup (int groupId, String deletedBy)
             throws KustvaktException {
         // default hard delete
-        userGroupDao.deleteGroup(groupId, deletedBy, config.isSoftDeleteAutoGroup());
+        userGroupDao.deleteGroup(groupId, deletedBy,
+                config.isSoftDeleteAutoGroup());
     }
 
     /** Adds a user to the specified usergroup. If the username with 
@@ -225,9 +234,10 @@
         ParameterChecker.checkIntegerValue(groupId, "userGroupId");
 
         if (memberExists(username, groupId, status)) {
-            throw new KustvaktException(StatusCodes.DB_ENTRY_EXISTS,
+            throw new KustvaktException(StatusCodes.GROUP_MEMBER_EXISTS,
                     "Username " + username + " with status " + status
-                            + " exists in user-group " + userGroup.getName(),
+                            + " exists in the user-group "
+                            + userGroup.getName(),
                     username, status.name(), userGroup.getName());
         }
 
@@ -261,7 +271,7 @@
         }
         else if (existingStatus.equals(GroupMemberStatus.DELETED)) {
             // hard delete, not customizable
-            groupMemberDao.deleteMember(username, groupId, "system", false);
+            deleteMember(username, groupId, "system", false);
         }
 
         return false;
@@ -307,26 +317,47 @@
      * @param username the username of the group member
      * @throws KustvaktException
      */
-    public void subscribe (int groupId, String username)
+    public void acceptInvitation (int groupId, String username)
             throws KustvaktException {
-        groupMemberDao.approveMember(username, groupId);
+
+        ParameterChecker.checkStringValue(username, "userId");
+        ParameterChecker.checkIntegerValue(groupId, "groupId");
+
+        UserGroupMember member =
+                groupMemberDao.retrieveMemberById(username, groupId);
+        GroupMemberStatus status = member.getStatus();
+        if (status.equals(GroupMemberStatus.DELETED)) {
+            UserGroup group = userGroupDao.retrieveGroupById(groupId);
+            throw new KustvaktException(StatusCodes.GROUP_MEMBER_DELETED,
+                    username + " has already been deleted from the group "
+                            + group.getName(),
+                    username, group.getName());
+        }
+        else if (member.getStatus().equals(GroupMemberStatus.ACTIVE)) {
+            UserGroup userGroup = retrieveUserGroupById(groupId);
+            throw new KustvaktException(StatusCodes.GROUP_MEMBER_EXISTS,
+                    "Username " + username + " with status " + status
+                            + " exists in the user-group "
+                            + userGroup.getName(),
+                    username, status.name(), userGroup.getName());
+        }
+        // status pending
+        else {
+            jlog.debug("status: " +member.getStatusDate());
+            ZonedDateTime expiration = member.getStatusDate().plusMinutes(30);
+            ZonedDateTime now = ZonedDateTime.now();
+            jlog.debug("expiration: " + expiration + ", now: " + now);
+
+            if (expiration.isAfter(now)){
+                member.setStatus(GroupMemberStatus.ACTIVE);
+                groupMemberDao.updateMember(member);
+            }
+            else{
+                throw new KustvaktException(StatusCodes.INVITATION_EXPIRED);
+            }
+        }
     }
 
-
-    /** Updates the {@link GroupMemberStatus} of a member to 
-     * {@link GroupMemberStatus#DELETED}
-     * 
-     * @param groupId groupId
-     * @param username member's username
-     * @throws KustvaktException
-     */
-    public void unsubscribe (int groupId, String username)
-            throws KustvaktException {
-        groupMemberDao.deleteMember(username, groupId, username,
-                config.isSoftDeleteGroupMember());
-    }
-
-
     public boolean isMember (String username, UserGroup userGroup)
             throws KustvaktException {
         List<UserGroupMember> members =
@@ -349,10 +380,11 @@
                     "Operation " + "'delete group owner'" + "is not allowed.",
                     "delete group owner");
         }
-        else if (isUserGroupAdmin(deletedBy, userGroup)
+        else if (memberId.equals(deletedBy)
+                || isUserGroupAdmin(deletedBy, userGroup)
                 || user.isSystemAdmin()) {
             // soft delete
-            groupMemberDao.deleteMember(memberId, groupId, deletedBy,
+            deleteMember(memberId, groupId, deletedBy,
                     config.isSoftDeleteGroupMember());
         }
         else {
@@ -361,4 +393,29 @@
         }
     }
 
+    /** Updates the {@link GroupMemberStatus} of a member to 
+     * {@link GroupMemberStatus#DELETED}
+     * 
+     * @param userId user to be deleted
+     * @param groupId user-group id
+     * @param deletedBy user that issue the delete 
+     * @param isSoftDelete true if database entry is to be deleted 
+     * permanently, false otherwise
+     * @throws KustvaktException
+     */
+    private void deleteMember (String username, int groupId, String deletedBy,
+            boolean isSoftDelete) throws KustvaktException {
+        UserGroupMember member =
+                groupMemberDao.retrieveMemberById(username, groupId);
+        GroupMemberStatus status = member.getStatus();
+        if (isSoftDelete && status.equals(GroupMemberStatus.DELETED)) {
+            UserGroup group = userGroupDao.retrieveGroupById(groupId);
+            throw new KustvaktException(StatusCodes.GROUP_MEMBER_DELETED,
+                    username + " has already been deleted from the group "
+                            + group.getName(),
+                    username, group.getName());
+        }
+
+        groupMemberDao.deleteMember(member, deletedBy, isSoftDelete);
+    }
 }
diff --git a/full/src/main/java/de/ids_mannheim/korap/web/controller/UserGroupController.java b/full/src/main/java/de/ids_mannheim/korap/web/controller/UserGroupController.java
index ad92c84..eda4eeb 100644
--- a/full/src/main/java/de/ids_mannheim/korap/web/controller/UserGroupController.java
+++ b/full/src/main/java/de/ids_mannheim/korap/web/controller/UserGroupController.java
@@ -59,6 +59,11 @@
     @Autowired
     private UserGroupService service;
 
+    /** Returns all user-groups wherein a user is an active or pending member.
+     * 
+     * @param securityContext
+     * @return a list of user-groups
+     */
     @GET
     @Path("list")
     public Response getUserGroup (@Context SecurityContext securityContext) {
@@ -120,8 +125,7 @@
     @DELETE
     @Path("delete")
     @Consumes(MediaType.APPLICATION_JSON)
-    public Response deleteUserGroup (
-            @Context SecurityContext securityContext,
+    public Response deleteUserGroup (@Context SecurityContext securityContext,
             @QueryParam("groupId") int groupId) {
         TokenContext context =
                 (TokenContext) securityContext.getUserPrincipal();
@@ -133,7 +137,7 @@
             throw responseHandler.throwit(e);
         }
     }
-    
+
     /** Group owner cannot be deleted.
      * 
      * @param securityContext
@@ -183,7 +187,7 @@
         TokenContext context =
                 (TokenContext) securityContext.getUserPrincipal();
         try {
-            service.subscribe(groupId, context.getUsername());
+            service.acceptInvitation(groupId, context.getUsername());
             return Response.ok().build();
         }
         catch (KustvaktException e) {
@@ -200,7 +204,8 @@
         TokenContext context =
                 (TokenContext) securityContext.getUserPrincipal();
         try {
-            service.unsubscribe(groupId, context.getUsername());
+            service.deleteGroupMember(context.getUsername(), groupId,
+                    context.getUsername());
             return Response.ok().build();
         }
         catch (KustvaktException e) {
diff --git a/full/src/main/resources/db/new-mysql/V1.1__create_virtual_corpus_tables.sql b/full/src/main/resources/db/new-mysql/V1.1__create_virtual_corpus_tables.sql
index a8df972..1fe0f26 100644
--- a/full/src/main/resources/db/new-mysql/V1.1__create_virtual_corpus_tables.sql
+++ b/full/src/main/resources/db/new-mysql/V1.1__create_virtual_corpus_tables.sql
@@ -32,6 +32,7 @@
   status varchar(100) NOT NULL,
   created_by varchar(100) NOT NULL,
   deleted_by varchar(100) DEFAULT NULL,
+  status_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
   UNIQUE INDEX unique_index (user_id,group_id),
   INDEX status_index(status),
   FOREIGN KEY (group_id) 
diff --git a/full/src/main/resources/db/new-sqlite/V1.1__create_virtual_corpus_tables.sql b/full/src/main/resources/db/new-sqlite/V1.1__create_virtual_corpus_tables.sql
index 8476535..d1bbcb7 100644
--- a/full/src/main/resources/db/new-sqlite/V1.1__create_virtual_corpus_tables.sql
+++ b/full/src/main/resources/db/new-sqlite/V1.1__create_virtual_corpus_tables.sql
@@ -35,6 +35,8 @@
   status varchar(100) NOT NULL,
   created_by varchar(100) NOT NULL,
   deleted_by varchar(100) DEFAULT NULL,
+-- interprets now as localtime and save it as UTC
+  status_date timestamp DEFAULT (datetime('now','localtime')),
   FOREIGN KEY (group_id) 
   	REFERENCES user_group (id)
   	ON DELETE CASCADE
diff --git a/full/src/main/resources/db/new-sqlite/V1.2__triggers.sql b/full/src/main/resources/db/new-sqlite/V1.2__triggers.sql
new file mode 100644
index 0000000..0acd2f7
--- /dev/null
+++ b/full/src/main/resources/db/new-sqlite/V1.2__triggers.sql
@@ -0,0 +1,9 @@
+--CREATE TRIGGER insert_member_status AFTER INSERT ON user_group_member
+--     BEGIN
+--      UPDATE user_group_member SET status_date = DATETIME('NOW', 'utc')  WHERE rowid = new.rowid;
+--     END;
+--
+CREATE TRIGGER update_member_status AFTER UPDATE ON user_group_member	
+     BEGIN
+      UPDATE user_group_member SET status_date = (datetime('now','localtime'))  WHERE rowid = old.rowid;
+     END;   
diff --git a/full/src/main/resources/default-config.xml b/full/src/main/resources/default-config.xml
index 625460f..cd52fc0 100644
--- a/full/src/main/resources/default-config.xml
+++ b/full/src/main/resources/default-config.xml
@@ -87,6 +87,12 @@
 		<property name="url" value="${jdbc.url}" />
 		<property name="username" value="${jdbc.username}" />
 		<property name="password" value="${jdbc.password}" />
+		<property name="connectionProperties">
+            <props>
+                <prop key="date_string_format">yyyy-MM-dd HH:mm:ss</prop>
+            </props>
+        </property>
+        
 		<!-- relevant for single connection datasource and sqlite -->
 		<property name="suppressClose">
 			<value>true</value>
diff --git a/full/src/main/resources/jdbc.properties b/full/src/main/resources/jdbc.properties
index fb06a12..2bc85dd 100644
--- a/full/src/main/resources/jdbc.properties
+++ b/full/src/main/resources/jdbc.properties
@@ -4,7 +4,7 @@
 
 #jdbc.database=mysql
 #jdbc.driverClassName=com.mysql.jdbc.Driver
-#jdbc.url=jdbc:mysql://localhost:3306/kustvakt?autoReconnect=true
+#jdbc.url=jdbc:mysql://localhost:3306/kustvakt?autoReconnect=true&useLegacyDatetimeCode=false
 #jdbc.username=korap
 #jdbc.password=password
 
diff --git a/full/src/main/resources/log4j.properties b/full/src/main/resources/log4j.properties
index 352062e..e688b81 100644
--- a/full/src/main/resources/log4j.properties
+++ b/full/src/main/resources/log4j.properties
@@ -6,7 +6,7 @@
 
 #log4j.logger.de.ids_mannheim.korap.service.VirtualCorpusService = error, debugLog
 #log4j.logger.de.ids_mannheim.korap.web.controller.AuthenticationController = debug, debugLog, stdout
-#log4j.logger.de.ids_mannheim.korap.resource.rewrite.CollectionRewrite= stdout, debugLog
+#log4j.logger.de.ids_mannheim.korap.service.UserGroupService= stdout, debugLog
 
 # Direct log messages to stdout
 log4j.appender.stdout=org.apache.log4j.ConsoleAppender
diff --git a/full/src/test/java/de/ids_mannheim/korap/web/controller/UserGroupControllerTest.java b/full/src/test/java/de/ids_mannheim/korap/web/controller/UserGroupControllerTest.java
index 537f299..818f215 100644
--- a/full/src/test/java/de/ids_mannheim/korap/web/controller/UserGroupControllerTest.java
+++ b/full/src/test/java/de/ids_mannheim/korap/web/controller/UserGroupControllerTest.java
@@ -236,6 +236,7 @@
                 node.at("/errors/0/1").asText());
     }
 
+    // EM: same as cancel invitation
     private void testDeletePendingMember () throws UniformInterfaceException,
             ClientHandlerException, KustvaktException {
         // dory delete pearl
@@ -270,14 +271,14 @@
                 .delete(ClientResponse.class);
 
         String entity = response.getEntity(String.class);
-        System.out.println(entity);
+        //        System.out.println(entity);
         JsonNode node = JsonUtils.readTree(entity);
         assertEquals(Status.BAD_REQUEST.getStatusCode(), response.getStatus());
-        assertEquals(StatusCodes.DB_ENTRY_DELETED,
+        assertEquals(StatusCodes.GROUP_MEMBER_DELETED,
                 node.at("/errors/0/0").asInt());
-        assertEquals("pearl has already been deleted from the group.",
+        assertEquals("pearl has already been deleted from the group dory group",
                 node.at("/errors/0/1").asText());
-        assertEquals("pearl", node.at("/errors/0/2").asText());
+        assertEquals("[pearl, dory group]", node.at("/errors/0/2").asText());
     }
 
     private void testDeleteGroup (String groupId)
@@ -442,7 +443,7 @@
         //        System.out.println(entity);
         JsonNode node = JsonUtils.readTree(entity);
         assertEquals(Status.BAD_REQUEST.getStatusCode(), response.getStatus());
-        assertEquals(StatusCodes.DB_ENTRY_EXISTS,
+        assertEquals(StatusCodes.GROUP_MEMBER_EXISTS,
                 node.at("/errors/0/0").asInt());
         assertEquals("Username marlin with status PENDING exists in user-group "
                 + "dory group", node.at("/errors/0/1").asText());
@@ -469,8 +470,8 @@
                                 "pass"))
                 .entity(userGroup).post(ClientResponse.class);
 
-//        String entity = response.getEntity(String.class);
-//        System.out.println(entity);
+                String entity = response.getEntity(String.class);
+                System.out.println(entity);
         assertEquals(Status.OK.getStatusCode(), response.getStatus());
 
         // check member
@@ -625,4 +626,62 @@
         assertEquals(1, node.size());
     }
 
+    @Test
+    public void testUnsubscribeDeletedMember ()
+            throws UniformInterfaceException, ClientHandlerException,
+            KustvaktException {
+        // pearl unsubscribes from dory group 
+        MultivaluedMap<String, String> form = new MultivaluedMapImpl();
+        // dory group
+        form.add("groupId", "2");
+
+        ClientResponse response = resource().path("group").path("unsubscribe")
+                .type(MediaType.APPLICATION_FORM_URLENCODED)
+                .header(HttpHeaders.X_FORWARDED_FOR, "149.27.0.32")
+                .header(Attributes.AUTHORIZATION,
+                        handler.createBasicAuthorizationHeaderValue("pearl",
+                                "pass"))
+                .entity(form).post(ClientResponse.class);
+
+        String entity = response.getEntity(String.class);
+        //        System.out.println(entity);
+        JsonNode node = JsonUtils.readTree(entity);
+        assertEquals(Status.BAD_REQUEST.getStatusCode(), response.getStatus());
+        assertEquals(StatusCodes.GROUP_MEMBER_DELETED,
+                node.at("/errors/0/0").asInt());
+        assertEquals("pearl has already been deleted from the group dory group",
+                node.at("/errors/0/1").asText());
+        assertEquals("[pearl, dory group]", node.at("/errors/0/2").asText());
+    }
+    
+    @Test
+    public void testUnsubscribePendingMember ()
+            throws UniformInterfaceException, ClientHandlerException,
+            KustvaktException {
+
+        JsonNode node = retrieveUserGroups("marlin");
+        assertEquals(2, node.size());
+        
+        MultivaluedMap<String, String> form = new MultivaluedMapImpl();
+        // dory group
+        form.add("groupId", "2");
+
+        ClientResponse response = resource().path("group").path("unsubscribe")
+                .type(MediaType.APPLICATION_FORM_URLENCODED)
+                .header(HttpHeaders.X_FORWARDED_FOR, "149.27.0.32")
+                .header(Attributes.AUTHORIZATION,
+                        handler.createBasicAuthorizationHeaderValue("marlin",
+                                "pass"))
+                .entity(form).post(ClientResponse.class);
+
+        String entity = response.getEntity(String.class);
+        //        System.out.println(entity);
+        assertEquals(Status.OK.getStatusCode(), response.getStatus());
+
+         node = retrieveUserGroups("marlin");
+        assertEquals(1, node.size());
+        
+        // invite marlin to dory group to set back the GroupMemberStatus.PENDING
+        testInviteDeletedMember();
+    }
 }
diff --git a/full/src/test/resources/test-config.xml b/full/src/test/resources/test-config.xml
index b416f1c..01f4e75 100644
--- a/full/src/test/resources/test-config.xml
+++ b/full/src/test/resources/test-config.xml
@@ -75,6 +75,12 @@
 		<property name="url" value="${jdbc.url}" />
 		<property name="username" value="${jdbc.username}" />
 		<property name="password" value="${jdbc.password}" />
+		<property name="connectionProperties">
+            <props>
+                <prop key="date_string_format">yyyy-MM-dd HH:mm:ss</prop>
+            </props>
+        </property>
+        
 		<!-- Sqlite can only have a single connection -->
 		<property name="suppressClose">
 			<value>true</value>