Added new tables to sqlite (used for the test suite).
Change-Id: Ia3c85fb98d81cb6d09423c91cd333d9f14928484
diff --git a/full/pom.xml b/full/pom.xml
index 9e83366..ae87426 100644
--- a/full/pom.xml
+++ b/full/pom.xml
@@ -179,7 +179,13 @@
<artifactId>hibernate-entitymanager</artifactId>
<version>5.1.8.Final</version>
</dependency>
-
+
+ <!-- MySql -->
+ <dependency>
+ <groupId>mysql</groupId>
+ <artifactId>mysql-connector-java</artifactId>
+ <version>5.1.21</version>
+ </dependency>
<!-- Jersey -->
<dependency>
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
diff --git a/full/src/test/java/de/ids_mannheim/korap/dao/VirtualCorpusDaoTest.java b/full/src/test/java/de/ids_mannheim/korap/dao/VirtualCorpusDaoTest.java
index d9b5ad2..d3def19 100644
--- a/full/src/test/java/de/ids_mannheim/korap/dao/VirtualCorpusDaoTest.java
+++ b/full/src/test/java/de/ids_mannheim/korap/dao/VirtualCorpusDaoTest.java
@@ -1,4 +1,4 @@
-package de.ids_mannheim.de.korap.dao;
+package de.ids_mannheim.korap.dao;
import static org.junit.Assert.assertEquals;
diff --git a/full/src/test/resources/test-config.xml b/full/src/test/resources/test-config.xml
index b720834..c5c47af 100644
--- a/full/src/test/resources/test-config.xml
+++ b/full/src/test/resources/test-config.xml
@@ -60,7 +60,7 @@
<property name="url" value="${jdbc.url}" />
<property name="username" value="${jdbc.username}" />
<property name="password" value="${jdbc.password}" />
- <property name="maxTotal" value="1" />
+ <property name="maxTotal" value="4" />
<property name="maxIdle" value="1" />
<property name="minIdle" value="1" />
<property name="maxWaitMillis" value="15000" />
@@ -70,7 +70,7 @@
<!-- use SingleConnection only for testing! -->
<bean id="sqliteDataSource"
class="org.springframework.jdbc.datasource.SingleConnectionDataSource"
- lazy-init="true" destroy-method="destroy">
+ lazy-init="true">
<!-- <property name="driverClassName" value="${jdbc.driverClassName}" /> -->
<property name="url" value="${jdbc.url}" />
<property name="username" value="${jdbc.username}" />
@@ -87,7 +87,7 @@
<property name="jdbcUrl" value="${jdbc.url}" />
<property name="user" value="${jdbc.username}" />
<property name="password" value="${jdbc.password}" />
- <property name="maxPoolSize" value="1" />
+ <property name="maxPoolSize" value="4" />
<property name="minPoolSize" value="1" />
<property name="maxStatements" value="1" />
<property name="testConnectionOnCheckout" value="true" />
@@ -102,16 +102,19 @@
<property name="baselineOnMigrate" value="false" />
<property name="locations" value="${jdbc.schemaPath}" />
<property name="dataSource" ref="sqliteDataSource" />
+ <!-- <property name="dataSource" ref="dataSource" /> -->
</bean>
<bean id="kustvakt_db" class="de.ids_mannheim.korap.handlers.JDBCClient">
+ <!-- <constructor-arg index="0" ref="dataSource" /> -->
<constructor-arg index="0" ref="sqliteDataSource" />
- <property name="database" value="sqlite" />
+ <property name="database" value="${jdbc.database}" />
</bean>
<bean id="entityManagerFactory"
class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
- <property name="dataSource" ref="dataSource" />
+ <!-- <property name="dataSource" ref="dataSource" /> -->
+ <property name="dataSource" ref="sqliteDataSource" />
<property name="packagesToScan" value="de.ids_mannheim.korap.entity" />
<property name="jpaVendorAdapter">
<bean id="jpaVendorAdapter"
diff --git a/full/src/test/resources/test-jdbc.properties b/full/src/test/resources/test-jdbc.properties
index ef7749d..7311a23 100644
--- a/full/src/test/resources/test-jdbc.properties
+++ b/full/src/test/resources/test-jdbc.properties
@@ -3,9 +3,9 @@
jdbc.database=sqlite
jdbc.driverClassName=org.sqlite.JDBC
-#jdbc.url=jdbc:sqlite:dbtest.sqlite
jdbc.url=jdbc:sqlite::memory:
-# jdbc.url=jdbc:sqlite:kustvakt_init_test.sqlite
+#jdbc.url=jdbc:sqlite:testDB.sqlite
jdbc.username=pc
jdbc.password=pc
jdbc.schemaPath=classpath:db.sqlite
+#jdbc.schemaPath=classpath:db.new-sqlite
\ No newline at end of file