Updated tables and added VirtualCorpusDao.
Change-Id: I48e4d077d1063262117637f71e11091f8eafa978
diff --git a/full/src/main/java/de/ids_mannheim/korap/constants/GroupMemberStatus.java b/full/src/main/java/de/ids_mannheim/korap/constants/GroupMemberStatus.java
new file mode 100644
index 0000000..39a2015
--- /dev/null
+++ b/full/src/main/java/de/ids_mannheim/korap/constants/GroupMemberStatus.java
@@ -0,0 +1,5 @@
+package de.ids_mannheim.korap.constants;
+
+public enum GroupMemberStatus {
+ ACTIVE, PENDING, DELETED;
+}
diff --git a/full/src/main/java/de/ids_mannheim/korap/constants/UserGroupStatus.java b/full/src/main/java/de/ids_mannheim/korap/constants/UserGroupStatus.java
new file mode 100644
index 0000000..1cac9ec
--- /dev/null
+++ b/full/src/main/java/de/ids_mannheim/korap/constants/UserGroupStatus.java
@@ -0,0 +1,5 @@
+package de.ids_mannheim.korap.constants;
+
+public enum UserGroupStatus {
+ ACTIVE, DELETED, HIDDEN;
+}
diff --git a/full/src/main/java/de/ids_mannheim/korap/constants/VirtualCorpusAccessStatus.java b/full/src/main/java/de/ids_mannheim/korap/constants/VirtualCorpusAccessStatus.java
new file mode 100644
index 0000000..530526a
--- /dev/null
+++ b/full/src/main/java/de/ids_mannheim/korap/constants/VirtualCorpusAccessStatus.java
@@ -0,0 +1,6 @@
+package de.ids_mannheim.korap.constants;
+
+public enum VirtualCorpusAccessStatus {
+
+ ACTIVE, PENDING, DELETED, HIDDEN;
+}
diff --git a/full/src/main/java/de/ids_mannheim/korap/constants/VirtualCorpusType.java b/full/src/main/java/de/ids_mannheim/korap/constants/VirtualCorpusType.java
new file mode 100644
index 0000000..db4a461
--- /dev/null
+++ b/full/src/main/java/de/ids_mannheim/korap/constants/VirtualCorpusType.java
@@ -0,0 +1,6 @@
+package de.ids_mannheim.korap.constants;
+
+public enum VirtualCorpusType {
+
+ PREDEFINED, PROJECT, PRIVATE, PUBLISHED;
+}
diff --git a/full/src/main/java/de/ids_mannheim/korap/dao/AnnotationDao.java b/full/src/main/java/de/ids_mannheim/korap/dao/AnnotationDao.java
index 366546e..b8cf357 100644
--- a/full/src/main/java/de/ids_mannheim/korap/dao/AnnotationDao.java
+++ b/full/src/main/java/de/ids_mannheim/korap/dao/AnnotationDao.java
@@ -71,7 +71,7 @@
// EM: Hibernate bug in join n:m (see AnnotationPair.values).
// There should not be any redundant AnnotationPair.
- // The redundancy can be alsp avoided with fetch=FetchType.EAGER
+ // The redundancy can be also avoided with fetch=FetchType.EAGER
// because Hibernate does 2 selects.
query.distinct(true);
query = query.select(annotationPair);
diff --git a/full/src/main/java/de/ids_mannheim/korap/dao/VirtualCorpusDao.java b/full/src/main/java/de/ids_mannheim/korap/dao/VirtualCorpusDao.java
new file mode 100644
index 0000000..74b5850
--- /dev/null
+++ b/full/src/main/java/de/ids_mannheim/korap/dao/VirtualCorpusDao.java
@@ -0,0 +1,101 @@
+package de.ids_mannheim.korap.dao;
+
+import java.util.List;
+
+import javax.persistence.EntityManager;
+import javax.persistence.PersistenceContext;
+import javax.persistence.Query;
+import javax.persistence.criteria.CriteriaBuilder;
+import javax.persistence.criteria.CriteriaQuery;
+import javax.persistence.criteria.Join;
+import javax.persistence.criteria.Predicate;
+import javax.persistence.criteria.Root;
+
+import org.springframework.stereotype.Component;
+
+import de.ids_mannheim.korap.constants.GroupMemberStatus;
+import de.ids_mannheim.korap.constants.UserGroupStatus;
+import de.ids_mannheim.korap.constants.VirtualCorpusAccessStatus;
+import de.ids_mannheim.korap.entity.UserGroup;
+import de.ids_mannheim.korap.entity.VirtualCorpus;
+import de.ids_mannheim.korap.entity.VirtualCorpusAccessGroup;
+import de.ids_mannheim.korap.exceptions.KustvaktException;
+import de.ids_mannheim.korap.exceptions.StatusCodes;
+
+/** VirtualCorpusDao manages SQL queries regarding virtual corpora,
+ * e.g. retrieving and storing virtual corpora.
+ *
+ * @author margaretha
+ *
+ */
+@Component
+public class VirtualCorpusDao {
+
+ @PersistenceContext
+ private EntityManager entityManager;
+
+
+ public void storeVirtualCorpus (VirtualCorpus virtualCorpus) {
+ entityManager.getTransaction().begin();
+ entityManager.persist(virtualCorpus);
+ entityManager.getTransaction().commit();
+ }
+
+
+ public VirtualCorpus retrieveVirtualCorpusById (int id)
+ throws KustvaktException {
+ if (id == 0) {
+ throw new KustvaktException(StatusCodes.MISSING_ARGUMENT, "id",
+ String.valueOf(id));
+ }
+ CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
+ CriteriaQuery<VirtualCorpus> query =
+ criteriaBuilder.createQuery(VirtualCorpus.class);
+ Root<VirtualCorpus> virtualCorpus = query.from(VirtualCorpus.class);
+ query.select(virtualCorpus);
+ query.where(criteriaBuilder.equal(virtualCorpus.get("id"), id));
+ Query q = entityManager.createQuery(query);
+ return (VirtualCorpus) q.getSingleResult();
+ }
+
+
+ public List<VirtualCorpus> retrieveVirtualCorpusByUser (String userId) {
+ CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
+ CriteriaQuery<VirtualCorpus> query =
+ criteriaBuilder.createQuery(VirtualCorpus.class);
+
+ Root<VirtualCorpus> virtualCorpus = query.from(VirtualCorpus.class);
+ virtualCorpus.fetch("accessGroup");
+
+ Join<VirtualCorpus, VirtualCorpusAccessGroup> accessGroup =
+ virtualCorpus.join("accessGroup");
+
+ Predicate corpusStatus = criteriaBuilder.notEqual(
+ accessGroup.get("status"), VirtualCorpusAccessStatus.HIDDEN);
+ Predicate userGroupStatus = criteriaBuilder.notEqual(
+ accessGroup.get("userGroup").get("status"),
+ UserGroupStatus.DELETED);
+
+ Join<VirtualCorpusAccessGroup, UserGroup> userGroupMembers =
+ accessGroup.join("userGroup").join("members");
+
+ Predicate memberStatus = criteriaBuilder.equal(
+ userGroupMembers.get("status"), GroupMemberStatus.ACTIVE);
+
+ Predicate user =
+ criteriaBuilder.equal(userGroupMembers.get("userId"), userId);
+
+ query.select(virtualCorpus);
+ query.where(criteriaBuilder.and(corpusStatus, userGroupStatus,
+ memberStatus, user));
+ Query q = entityManager.createQuery(query);
+ return q.getResultList();
+ }
+
+
+ // EM: what is needed for admin?
+ public List<VirtualCorpus> retrieveVirtualCorpusByAdmin () {
+ return null;
+
+ }
+}
diff --git a/full/src/main/java/de/ids_mannheim/korap/entity/UserGroup.java b/full/src/main/java/de/ids_mannheim/korap/entity/UserGroup.java
index 7f29477..d51e00f 100644
--- a/full/src/main/java/de/ids_mannheim/korap/entity/UserGroup.java
+++ b/full/src/main/java/de/ids_mannheim/korap/entity/UserGroup.java
@@ -4,6 +4,8 @@
import javax.persistence.Column;
import javax.persistence.Entity;
+import javax.persistence.EnumType;
+import javax.persistence.Enumerated;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
@@ -11,6 +13,10 @@
import javax.persistence.OneToMany;
import javax.persistence.Table;
+import org.hibernate.annotations.Fetch;
+import org.hibernate.annotations.FetchMode;
+
+import de.ids_mannheim.korap.constants.UserGroupStatus;
import lombok.Getter;
import lombok.Setter;
@@ -36,8 +42,12 @@
private String name;
@Column(name = "created_by")
private String createdBy;
+
+ @Enumerated(EnumType.STRING)
+ private UserGroupStatus status;
- @OneToMany(fetch = FetchType.LAZY)
+ @Fetch(FetchMode.SELECT)
+ @OneToMany(mappedBy="group")//, fetch = FetchType.LAZY)
List<UserGroupMember> members;
@OneToMany(mappedBy = "virtualCorpus", fetch = FetchType.LAZY)
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 6abe32b..db705c4 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
@@ -4,6 +4,8 @@
import javax.persistence.Column;
import javax.persistence.Entity;
+import javax.persistence.EnumType;
+import javax.persistence.Enumerated;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
@@ -16,6 +18,7 @@
import javax.persistence.Table;
import javax.persistence.UniqueConstraint;
+import de.ids_mannheim.korap.constants.GroupMemberStatus;
import lombok.Getter;
import lombok.Setter;
@@ -36,12 +39,14 @@
@GeneratedValue(strategy = GenerationType.IDENTITY)
private int id;
@Column(name = "user_id")
- private int userId;
- private String status;
+ private String userId;
@Column(name = "created_by")
private String createdBy;
@Column(name = "deleted_by")
private String deletedBy;
+
+ @Enumerated(EnumType.STRING)
+ private GroupMemberStatus status;
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "group_id")
diff --git a/full/src/main/java/de/ids_mannheim/korap/entity/VirtualCorpus.java b/full/src/main/java/de/ids_mannheim/korap/entity/VirtualCorpus.java
index b100600..ff1d09a 100644
--- a/full/src/main/java/de/ids_mannheim/korap/entity/VirtualCorpus.java
+++ b/full/src/main/java/de/ids_mannheim/korap/entity/VirtualCorpus.java
@@ -4,12 +4,16 @@
import javax.persistence.Column;
import javax.persistence.Entity;
+import javax.persistence.EnumType;
+import javax.persistence.Enumerated;
+import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.OneToMany;
import javax.persistence.Table;
+import de.ids_mannheim.korap.constants.VirtualCorpusType;
import lombok.Getter;
import lombok.Setter;
@@ -34,7 +38,8 @@
@GeneratedValue(strategy = GenerationType.IDENTITY)
private int id;
private String name;
- private String type;
+ @Enumerated(EnumType.STRING)
+ private VirtualCorpusType type;
private String status;
private String description;
@Column(name = "required_access")
@@ -45,7 +50,7 @@
@Column(name = "created_by")
private String createdBy;
- @OneToMany(mappedBy = "userGroup")
+ @OneToMany(mappedBy = "userGroup", fetch=FetchType.LAZY)
List<VirtualCorpusAccessGroup> accessGroup;
diff --git a/full/src/main/java/de/ids_mannheim/korap/entity/VirtualCorpusAccessGroup.java b/full/src/main/java/de/ids_mannheim/korap/entity/VirtualCorpusAccessGroup.java
index eb1fcb1..76e49eb 100644
--- a/full/src/main/java/de/ids_mannheim/korap/entity/VirtualCorpusAccessGroup.java
+++ b/full/src/main/java/de/ids_mannheim/korap/entity/VirtualCorpusAccessGroup.java
@@ -2,14 +2,17 @@
import javax.persistence.Column;
import javax.persistence.Entity;
+import javax.persistence.EnumType;
+import javax.persistence.Enumerated;
+import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
-import javax.persistence.Index;
+import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
-import javax.persistence.PrimaryKeyJoinColumn;
import javax.persistence.Table;
+import de.ids_mannheim.korap.constants.VirtualCorpusAccessStatus;
import lombok.Getter;
import lombok.Setter;
@@ -24,14 +27,12 @@
@Setter
@Getter
@Entity
-@Table(name = "vc_access_group", indexes = {
- @Index(unique = true, columnList = "virtual_corpus_id, group_id") })
+@Table(name = "virtual_corpus_access")
public class VirtualCorpusAccessGroup {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private int id;
- private String status;
@Column(name = "created_by")
private String createdBy;
@Column(name = "approved_by")
@@ -39,13 +40,16 @@
@Column(name = "deleted_by")
private String deletedBy;
- @ManyToOne
- @PrimaryKeyJoinColumn(name = "virtual_corpus_id",
+ @Enumerated(EnumType.STRING)
+ private VirtualCorpusAccessStatus status;
+
+ @ManyToOne(fetch=FetchType.LAZY)
+ @JoinColumn(name = "virtual_corpus_id",
referencedColumnName = "id")
private VirtualCorpus virtualCorpus;
- @ManyToOne
- @PrimaryKeyJoinColumn(name = "group_id", referencedColumnName = "id")
+ @ManyToOne(fetch=FetchType.LAZY)
+ @JoinColumn(name = "user_group_id", referencedColumnName = "id")
private UserGroup userGroup;
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
new file mode 100644
index 0000000..d558e47
--- /dev/null
+++ b/full/src/main/resources/db/new-mysql/V1.1__create_virtual_corpus_tables.sql
@@ -0,0 +1,67 @@
+CREATE TABLE IF NOT EXISTS user_group (
+ id INTEGER PRIMARY KEY AUTO_INCREMENT,
+ name varchar(100) NOT NULL,
+ status varchar(100) NOT NULL,
+ created_by varchar(100) NOT NULL
+);
+
+CREATE TABLE IF NOT EXISTS user_group_member (
+ id INTEGER PRIMARY KEY AUTO_INCREMENT,
+ user_id varchar(100) NOT NULL,
+ group_id int(11) NOT NULL,
+ status varchar(100) NOT NULL,
+ created_by varchar(100) NOT NULL,
+ deleted_by varchar(100) DEFAULT NULL,
+ UNIQUE INDEX unique_index (user_id,group_id),
+ FOREIGN KEY (group_id)
+ REFERENCES user_group (id)
+ ON DELETE CASCADE
+);
+
+CREATE TABLE IF NOT EXISTS role (
+ id varchar(100) NOT NULL,
+ privilege varchar(100) NOT NULL,
+ PRIMARY KEY (id)
+);
+
+CREATE TABLE IF NOT EXISTS group_member_role (
+ id INTEGER PRIMARY KEY AUTO_INCREMENT,
+ group_member_id int(11) NOT NULL,
+ role_id varchar(100) NOT NULL,
+ UNIQUE INDEX unique_index (group_member_id,role_id),
+ FOREIGN KEY (group_member_id)
+ REFERENCES user_group_member (id)
+ ON DELETE CASCADE,
+ FOREIGN KEY (role_id)
+ REFERENCES role (id)
+ ON DELETE CASCADE
+);
+
+CREATE TABLE IF NOT EXISTS virtual_corpus (
+ id INTEGER PRIMARY KEY AUTO_INCREMENT,
+ name varchar(255) NOT NULL,
+ type varchar(100) NOT NULL,
+ required_access varchar(100) NOT NULL,
+ created_by varchar(100) NOT NULL,
+ description varchar(255) DEFAULT NULL,
+ status varchar(100) DEFAULT NULL,
+ collection_query varchar(2000) NOT NULL,
+ definition varchar(255) DEFAULT NULL
+);
+
+CREATE TABLE IF NOT EXISTS virtual_corpus_access (
+ id INTEGER PRIMARY KEY AUTO_INCREMENT,
+ virtual_corpus_id int(11) NOT NULL,
+ user_group_id int(11) NOT NULL,
+ status varchar(100) NOT NULL,
+ created_by varchar(100) NOT NULL,
+ approved_by varchar(100) DEFAULT NULL,
+ deleted_by varchar(100) DEFAULT NULL,
+ UNIQUE INDEX unique_index (virtual_corpus_id,user_group_id),
+ FOREIGN KEY (user_group_id)
+ REFERENCES user_group (id)
+ ON DELETE CASCADE,
+ FOREIGN KEY (virtual_corpus_id)
+ REFERENCES virtual_corpus (id)
+ ON DELETE CASCADE
+);
\ No newline at end of file
diff --git a/full/src/main/resources/db/new-mysql/V3.1__insert_virtual_corpus.sql b/full/src/main/resources/db/new-mysql/V3.1__insert_virtual_corpus.sql
new file mode 100644
index 0000000..1d1cc9b
--- /dev/null
+++ b/full/src/main/resources/db/new-mysql/V3.1__insert_virtual_corpus.sql
@@ -0,0 +1,84 @@
+-- dummy data only for testing
+
+-- user groups
+INSERT INTO user_group(name,status,created_by)
+ VALUES ("dory group","active","dory");
+
+INSERT INTO user_group(name,status,created_by)
+ VALUES ("auto group","hidden","system");
+
+INSERT INTO user_group(name,status,created_by)
+ VALUES ("all users","hidden","system");
+
+INSERT INTO user_group(name,status,created_by)
+ VALUES ("deleted group","deleted","dory");
+
+
+
+-- user group members
+INSERT INTO user_group_member(user_id, group_id, status, created_by)
+ SELECT "dory",
+ (SELECT id from user_group where name = "dory group"),
+ "ACTIVE","dory";
+
+INSERT INTO user_group_member(user_id, group_id, status, created_by)
+ SELECT "nemo",
+ (SELECT id from user_group where name = "dory group"),
+ "ACTIVE","dory";
+
+INSERT INTO user_group_member(user_id, group_id, status, created_by)
+ SELECT "marlin",
+ (SELECT id from user_group where name = "dory group"),
+ "PENDING","dory";
+
+INSERT INTO user_group_member(user_id, group_id, status, created_by, deleted_by)
+ SELECT "pearl",
+ (SELECT id from user_group where name = "dory group"),
+ "DELETED","dory", "pearl";
+
+
+-- virtual corpora
+INSERT INTO virtual_corpus(name, type, required_access, created_by, description, status, collection_query)
+ VALUES ("dory VC", "PRIVATE", "FREE", "dory", "test vc", "experimental","sigle=GOE");
+
+INSERT INTO virtual_corpus(name, type, required_access, created_by, description, status, collection_query)
+ VALUES ("group VC", "PROJECT", "PUB", "dory", "test vc", "experimental","sigle=GOE");
+
+INSERT INTO virtual_corpus(name, type, required_access, created_by, description, status, collection_query)
+ VALUES ("system VC", "PREDEFINED", "ALL", "system", "test vc", "experimental","sigle=GOE");
+
+INSERT INTO virtual_corpus(name, type, required_access, created_by, description, status, collection_query)
+ VALUES ("published VC", "PUBLISHED", "ALL", "marlin", "test vc", "experimental","sigle=GOE");
+
+
+-- virtual corpus access
+INSERT INTO virtual_corpus_access(virtual_corpus_id, user_group_id, status, created_by)
+ SELECT
+ (SELECT id from virtual_corpus where name = "group VC"),
+ (SELECT id from user_group where name = "dory group"),
+ "ACTIVE", "dory";
+
+INSERT INTO virtual_corpus_access(virtual_corpus_id, user_group_id, status, created_by)
+ SELECT
+ (SELECT id from virtual_corpus where name = "system VC"),
+ (SELECT id from user_group where name = "all users"),
+ "ACTIVE", "system";
+
+INSERT INTO virtual_corpus_access(virtual_corpus_id, user_group_id, status, created_by)
+ SELECT
+ (SELECT id from virtual_corpus where name = "published VC"),
+ (SELECT id from user_group where name = "all users"),
+ "HIDDEN", "marlin";
+
+INSERT INTO virtual_corpus_access(virtual_corpus_id, user_group_id, status, created_by)
+ SELECT
+ (SELECT id from virtual_corpus where name = "published VC"),
+ (SELECT id from user_group where name = "auto group"),
+ "ACTIVE", "system";
+
+
+-- Summary user VC Lists
+-- dory: dory VC, group VC, system VC
+-- nemo: group VC, published VC, system VC
+-- marlin: published VC, system VC
+-- pearl: system VC
diff --git a/full/src/test/java/de/ids_mannheim/de/korap/dao/VirtualCorpusDaoTest.java b/full/src/test/java/de/ids_mannheim/de/korap/dao/VirtualCorpusDaoTest.java
new file mode 100644
index 0000000..828a467
--- /dev/null
+++ b/full/src/test/java/de/ids_mannheim/de/korap/dao/VirtualCorpusDaoTest.java
@@ -0,0 +1,58 @@
+package de.ids_mannheim.de.korap.dao;
+
+import static org.junit.Assert.assertEquals;
+
+import java.util.List;
+
+import org.junit.Test;
+import org.springframework.beans.factory.annotation.Autowired;
+
+import de.ids_mannheim.korap.config.BeanConfigTest;
+import de.ids_mannheim.korap.dao.VirtualCorpusDao;
+import de.ids_mannheim.korap.entity.VirtualCorpus;
+import de.ids_mannheim.korap.exceptions.KustvaktException;
+
+public class VirtualCorpusDaoTest extends BeanConfigTest{
+
+ @Autowired
+ VirtualCorpusDao dao;
+
+
+ @Test
+ public void retrieveVirtualCorpusByUserDory () {
+ List<VirtualCorpus> virtualCorpora =
+ dao.retrieveVirtualCorpusByUser("dory");
+ assertEquals(3,virtualCorpora.size());
+ }
+
+
+ @Test
+ public void retrieveVirtualCorpusByUserNemo () {
+ List<VirtualCorpus> virtualCorpora =
+ dao.retrieveVirtualCorpusByUser("nemo");
+
+ }
+
+
+ @Test
+ public void retrieveVirtualCorpusByUserMarlin () {
+ List<VirtualCorpus> virtualCorpora =
+ dao.retrieveVirtualCorpusByUser("marlin");
+
+ }
+
+
+ @Test
+ public void retrieveVirtualCorpusByUserPearl () {
+ List<VirtualCorpus> virtualCorpora =
+ dao.retrieveVirtualCorpusByUser("pearl");
+ }
+
+
+ @Override
+ public void initMethod () throws KustvaktException {
+ // TODO Auto-generated method stub
+
+ }
+
+}
diff --git a/full/src/test/resources/test-config.xml b/full/src/test/resources/test-config.xml
index 593d38e..e158b6b 100644
--- a/full/src/test/resources/test-config.xml
+++ b/full/src/test/resources/test-config.xml
@@ -35,6 +35,7 @@
<property name="locations">
<array>
<value>classpath:test-jdbc.properties</value>
+ <value>file:./test-jdbc.properties</value>
<value>classpath:hibernate.properties</value>
<value>classpath:kustvakt-test.conf</value>
</array>