Added new tables to sqlite (used for the test suite).
Change-Id: Ia3c85fb98d81cb6d09423c91cd333d9f14928484
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
index 03a042c..69eb5ef 100644
--- a/full/src/main/java/de/ids_mannheim/korap/dao/VirtualCorpusDao.java
+++ b/full/src/main/java/de/ids_mannheim/korap/dao/VirtualCorpusDao.java
@@ -33,26 +33,22 @@
* @author margaretha
*
*/
+@Transactional
@Component
public class VirtualCorpusDao {
@PersistenceContext
private EntityManager entityManager;
-
-
- @Transactional
+
public void storeVirtualCorpus (VirtualCorpus virtualCorpus) {
entityManager.persist(virtualCorpus);
}
-
- @Transactional
public void deleteVirtualCorpus (int id) throws KustvaktException {
VirtualCorpus vc = retrieveVCById(id);
entityManager.remove(vc);
}
-
public List<VirtualCorpus> retrieveVCByType (VirtualCorpusType type)
throws KustvaktException {
if (type == null) {
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
new file mode 100644
index 0000000..fed899e
--- /dev/null
+++ b/full/src/main/resources/db/new-sqlite/V1.1__create_virtual_corpus_tables.sql
@@ -0,0 +1,85 @@
+CREATE TABLE IF NOT EXISTS user_group (
+ id INTEGER PRIMARY KEY AUTOINCREMENT,
+ name varchar(100) NOT NULL,
+ status varchar(100) NOT NULL,
+ created_by varchar(100) NOT NULL
+);
+
+CREATE INDEX user_group_index ON user_group(status);
+
+
+CREATE TABLE IF NOT EXISTS user_group_member (
+ id INTEGER PRIMARY KEY AUTOINCREMENT,
+ 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,
+ FOREIGN KEY (group_id)
+ REFERENCES user_group (id)
+ ON DELETE CASCADE
+);
+
+CREATE UNIQUE INDEX user_group_member_index
+ ON user_group_member(user_id,group_id);
+CREATE INDEX user_group_member_status_index
+ ON user_group_member(status);
+
+
+CREATE TABLE IF NOT EXISTS role (
+ id varchar(100) PRIMARY KEY NOT NULL,
+ privilege varchar(100) NOT NULL
+);
+
+CREATE TABLE IF NOT EXISTS group_member_role (
+ id INTEGER PRIMARY KEY AUTOINCREMENT,
+ group_member_id int(11) NOT NULL,
+ role_id varchar(100) NOT NULL,
+ FOREIGN KEY (group_member_id)
+ REFERENCES user_group_member (id)
+ ON DELETE CASCADE,
+ FOREIGN KEY (role_id)
+ REFERENCES role (id)
+ ON DELETE CASCADE
+);
+
+CREATE UNIQUE INDEX group_member_role_index
+ ON group_member_role(group_member_id,role_id);
+
+
+CREATE TABLE IF NOT EXISTS virtual_corpus (
+ id INTEGER PRIMARY KEY AUTOINCREMENT,
+ 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 INDEX virtual_corpus_owner_index ON virtual_corpus(created_by);
+CREATE INDEX virtual_corpus_type_index ON virtual_corpus(type);
+
+CREATE TABLE IF NOT EXISTS virtual_corpus_access (
+ id INTEGER PRIMARY KEY AUTOINCREMENT,
+ 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,
+ FOREIGN KEY (user_group_id)
+ REFERENCES user_group (id)
+ ON DELETE CASCADE,
+ FOREIGN KEY (virtual_corpus_id)
+ REFERENCES virtual_corpus (id)
+ ON DELETE CASCADE
+);
+
+CREATE INDEX virtual_corpus_status_index
+ ON virtual_corpus_access(status);
+CREATE INDEX virtual_corpus_access_unique_index
+ ON virtual_corpus_access(virtual_corpus_id,user_group_id);
+
diff --git a/full/src/main/resources/db/new-sqlite/V1__Initial_version.sql b/full/src/main/resources/db/new-sqlite/V1__Initial_version.sql
index eeb9a54..2f8002c 100644
--- a/full/src/main/resources/db/new-sqlite/V1__Initial_version.sql
+++ b/full/src/main/resources/db/new-sqlite/V1__Initial_version.sql
@@ -1,4 +1,3 @@
-
CREATE TABLE IF NOT EXISTS annotation(
id INTEGER PRIMARY KEY AUTOINCREMENT,
code VARCHAR(20) NOT NULL,
@@ -7,7 +6,7 @@
de_description VARCHAR(100)
);
-create unique index annotation_index on annotation (code, type);
+CREATE UNIQUE INDEX annotation_index ON annotation (code, type);
CREATE TABLE IF NOT EXISTS annotation_pair(
id INTEGER PRIMARY KEY AUTOINCREMENT,
@@ -23,7 +22,7 @@
);
-create unique index annotation_pair_index on annotation_pair (annotation1, annotation2);
+CREATE UNIQUE INDEX annotation_pair_index ON annotation_pair (annotation1, annotation2);
CREATE TABLE IF NOT EXISTS annotation_pair_value(
id INTEGER PRIMARY KEY AUTOINCREMENT,
@@ -37,7 +36,7 @@
ON DELETE CASCADE
);
-create unique index annotation_pair_value_index on annotation_pair_value (pair_id, value_id);
+CREATE UNIQUE INDEX annotation_pair_value_index ON annotation_pair_value (pair_id, value_id);
CREATE TABLE resource(
id VARCHAR(100) PRIMARY KEY UNIQUE NOT NULL,
@@ -58,5 +57,5 @@
ON DELETE CASCADE
);
-create unique index resource_layer_index on resource_layer (resource_id, layer_id);
+CREATE UNIQUE INDEX resource_layer_index ON resource_layer (resource_id, layer_id);
diff --git a/full/src/main/resources/db/new-sqlite/V3.1__insert_virtual_corpus.sql b/full/src/main/resources/db/new-sqlite/V3.1__insert_virtual_corpus.sql
new file mode 100644
index 0000000..442a39c
--- /dev/null
+++ b/full/src/main/resources/db/new-sqlite/V3.1__insert_virtual_corpus.sql
@@ -0,0 +1,89 @@
+-- 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";
+
+INSERT INTO user_group_member(user_id, group_id, status, created_by)
+ SELECT "pearl",
+ (SELECT id from user_group where name = "auto group"),
+ "ACTIVE","system";
+
+
+-- 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, system VC
+-- marlin: published VC, system VC
+-- pearl: system VC, published VC
diff --git a/full/src/main/resources/jdbc.properties b/full/src/main/resources/jdbc.properties
index adf9a92..48d2d9d 100644
--- a/full/src/main/resources/jdbc.properties
+++ b/full/src/main/resources/jdbc.properties
@@ -15,7 +15,7 @@
jdbc.database=sqlite
jdbc.driverClassName=org.sqlite.JDBC
-jdbc.url=jdbc:sqlite:db-new.sqlite
+jdbc.url=jdbc:sqlite:db.sqlite
# jdbc.url=jdbc:sqlite:kustvakt_init_test.sqlite
jdbc.username=pc
jdbc.password=pc