Added new database setup for sqlite.
Change-Id: I38a948ba20687e9a8449aa6ae2548fd447d5a151
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
new file mode 100644
index 0000000..eeb9a54
--- /dev/null
+++ b/full/src/main/resources/db/new-sqlite/V1__Initial_version.sql
@@ -0,0 +1,62 @@
+
+CREATE TABLE IF NOT EXISTS annotation(
+ id INTEGER PRIMARY KEY AUTOINCREMENT,
+ code VARCHAR(20) NOT NULL,
+ type VARCHAR(20) NOT NULL,
+ description VARCHAR(100) NOT NULL,
+ de_description VARCHAR(100)
+);
+
+create unique index annotation_index on annotation (code, type);
+
+CREATE TABLE IF NOT EXISTS annotation_pair(
+ id INTEGER PRIMARY KEY AUTOINCREMENT,
+ annotation1 INTEGER NOT NULL,
+ annotation2 INTEGER NOT NULL,
+ description VARCHAR(300) NOT NULL,
+ FOREIGN KEY (annotation1)
+ REFERENCES annotation (id)
+ ON DELETE CASCADE,
+ FOREIGN KEY (annotation2)
+ REFERENCES annotation (id)
+ ON DELETE CASCADE
+
+);
+
+create unique index annotation_pair_index on annotation_pair (annotation1, annotation2);
+
+CREATE TABLE IF NOT EXISTS annotation_pair_value(
+ id INTEGER PRIMARY KEY AUTOINCREMENT,
+ pair_id INTEGER NOT NULL,
+ value_id INTEGER NOT NULL,
+ FOREIGN KEY (pair_id)
+ REFERENCES annotation_pair (id)
+ ON DELETE CASCADE,
+ FOREIGN KEY (value_id)
+ REFERENCES annotation (id)
+ ON DELETE CASCADE
+);
+
+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,
+ de_title VARCHAR(100) NOT NULL,
+ en_title VARCHAR(100) NOT NULL,
+ en_description VARCHAR(100)
+);
+
+CREATE TABLE resource_layer(
+ id INTEGER PRIMARY KEY AUTOINCREMENT,
+ resource_id VARCHAR(100) NOT NULL,
+ layer_id INTEGER NOT NULL,
+ FOREIGN KEY (resource_id)
+ REFERENCES resource (id)
+ ON DELETE CASCADE,
+ FOREIGN KEY (layer_id)
+ REFERENCES annotation_pair (id)
+ ON DELETE CASCADE
+);
+
+create unique index resource_layer_index on resource_layer (resource_id, layer_id);
+
diff --git a/full/src/main/resources/db/new-sqlite/V2.0__insert_annotations.sql b/full/src/main/resources/db/new-sqlite/V2.0__insert_annotations.sql
new file mode 100644
index 0000000..911f5de
--- /dev/null
+++ b/full/src/main/resources/db/new-sqlite/V2.0__insert_annotations.sql
@@ -0,0 +1,33 @@
+--foundries
+INSERT INTO annotation (code, type, description) VALUES("base","foundry","Base");
+INSERT INTO annotation (code, type, description) VALUES("dereko","foundry","DeReKo");
+INSERT INTO annotation (code, type, description) VALUES("corenlp","foundry","CoreNLP");
+INSERT INTO annotation (code, type, description) VALUES("cnx","foundry","Connexor");
+INSERT INTO annotation (code, type, description) VALUES("drukola","foundry","DruKoLa");
+INSERT INTO annotation (code, type, description) VALUES("glemm","foundry","Glemm");
+INSERT INTO annotation (code, type, description) VALUES("malt","foundry","Malt");
+INSERT INTO annotation (code, type, description) VALUES("marmot","foundry","MarMot");
+INSERT INTO annotation (code, type, description) VALUES("mate","foundry","Mate");
+INSERT INTO annotation (code, type, description) VALUES("mdp","foundry","MD parser");
+INSERT INTO annotation (code, type, description) VALUES("opennlp","foundry","OpenNLP");
+INSERT INTO annotation (code, type, description) VALUES("sgbr","foundry","Schreibgebrauch");
+INSERT INTO annotation (code, type, description) VALUES("tt","foundry","Tree Tagger");
+INSERT INTO annotation (code, type, description) VALUES("xip","foundry","Xerox Incremental Parser");
+
+--layers
+INSERT INTO annotation (code, type, description) VALUES("c","layer","Constituency");
+INSERT INTO annotation (code, type, description) VALUES("d","layer","Dependency");
+INSERT INTO annotation (code, type, description) VALUES("p","layer","Part of speech");
+INSERT INTO annotation (code, type, description) VALUES("l","layer","Lemma");
+INSERT INTO annotation (code, type, description) VALUES("lv","layer","Lemma variant");
+INSERT INTO annotation (code, type, description) VALUES("m","layer","Morphology");
+INSERT INTO annotation (code, type, description) VALUES("ne","layer","Named entity");
+INSERT INTO annotation (code, type, description) VALUES("s","layer","Structure");
+INSERT INTO annotation (code, type, description) VALUES("syn","layer","Syntax");
+
+--values
+INSERT INTO annotation (code, type, description) VALUES("s","value","Sentence");
+INSERT INTO annotation (code, type, description) VALUES("p","value","Paragraph");
+INSERT INTO annotation (code, type, description) VALUES("t","value","Text");
+INSERT INTO annotation (code, type, description) VALUES("ADJA","value","Attributive Adjective");
+
diff --git a/full/src/main/resources/db/new-sqlite/V2.1__insert_annotation_pairs.sql b/full/src/main/resources/db/new-sqlite/V2.1__insert_annotation_pairs.sql
new file mode 100644
index 0000000..3bf0f32
--- /dev/null
+++ b/full/src/main/resources/db/new-sqlite/V2.1__insert_annotation_pairs.sql
@@ -0,0 +1,180 @@
+INSERT INTO annotation_pair (annotation1, annotation2, description)
+ SELECT (SELECT a.id FROM annotation as a WHERE a.code="base"),
+ (SELECT a.id FROM annotation as a WHERE a.description="Structure"),
+ "Base structure layer";
+
+INSERT INTO annotation_pair (annotation1, annotation2, description)
+ SELECT (SELECT a.id FROM annotation as a WHERE a.code="dereko"),
+ (SELECT a.id FROM annotation as a WHERE a.description="Structure"),
+ "DeReKo structure layer";
+
+INSERT INTO annotation_pair (annotation1, annotation2, description)
+ SELECT (SELECT a.id FROM annotation as a WHERE a.code="cnx"),
+ (SELECT a.id FROM annotation as a WHERE a.description="Constituency"),
+ "Connexor constituency layer";
+
+INSERT INTO annotation_pair (annotation1, annotation2, description)
+ SELECT (SELECT a.id FROM annotation as a WHERE a.code="cnx"),
+ (SELECT a.id FROM annotation as a WHERE a.description="Syntax"),
+ "Connexor syntax layer";
+
+INSERT INTO annotation_pair (annotation1, annotation2, description)
+ SELECT (SELECT a.id FROM annotation as a WHERE a.code="cnx"),
+ (SELECT a.id FROM annotation as a WHERE a.description="Part of speech"),
+ "Connexor part of speech layer";
+
+INSERT INTO annotation_pair (annotation1, annotation2, description)
+ SELECT (SELECT a.id FROM annotation as a WHERE a.code="cnx"),
+ (SELECT a.id FROM annotation as a WHERE a.description="Lemma"),
+ "Connexor lemma layer";
+
+INSERT INTO annotation_pair (annotation1, annotation2, description)
+ SELECT (SELECT a.id FROM annotation as a WHERE a.code="cnx"),
+ (SELECT a.id FROM annotation as a WHERE a.description="Morphology"),
+ "Connexor morphology layer";
+
+INSERT INTO annotation_pair (annotation1, annotation2, description)
+ SELECT (SELECT a.id FROM annotation as a WHERE a.code="corenlp"),
+ (SELECT a.id FROM annotation as a WHERE a.description="Constituency"),
+ "CoreNLP constituency layer";
+
+INSERT INTO annotation_pair (annotation1, annotation2, description)
+ SELECT (SELECT a.id FROM annotation as a WHERE a.code="corenlp"),
+ (SELECT a.id FROM annotation as a WHERE a.description="Part of speech"),
+ "CoreNLP part of speech layer";
+
+INSERT INTO annotation_pair (annotation1, annotation2, description)
+ SELECT (SELECT a.id FROM annotation as a WHERE a.code="corenlp"),
+ (SELECT a.id FROM annotation as a WHERE a.description="Structure"),
+ "CoreNLP structure layer";
+
+INSERT INTO annotation_pair (annotation1, annotation2, description)
+ SELECT (SELECT a.id FROM annotation as a WHERE a.code="corenlp"),
+ (SELECT a.id FROM annotation as a WHERE a.description="Named entity"),
+ "CoreNLP named entity layer";
+
+INSERT INTO annotation_pair (annotation1, annotation2, description)
+ SELECT (SELECT a.id FROM annotation as a WHERE a.code="drukola"),
+ (SELECT a.id FROM annotation as a WHERE a.description="Lemma"),
+ "DruKoLa lemma layer";
+
+INSERT INTO annotation_pair (annotation1, annotation2, description)
+ SELECT (SELECT a.id FROM annotation as a WHERE a.code="drukola"),
+ (SELECT a.id FROM annotation as a WHERE a.description="Part of speech"),
+ "DruKoLa part of speech layer";
+
+INSERT INTO annotation_pair (annotation1, annotation2, description)
+ SELECT (SELECT a.id FROM annotation as a WHERE a.code="drukola"),
+ (SELECT a.id FROM annotation as a WHERE a.description="Morphology"),
+ "DruKoLa morphology layer";
+
+
+INSERT INTO annotation_pair (annotation1, annotation2, description)
+ SELECT (SELECT a.id FROM annotation as a WHERE a.code="glemm"),
+ (SELECT a.id FROM annotation as a WHERE a.description="Lemma"),
+ "Glemm lemma layer";
+
+INSERT INTO annotation_pair (annotation1, annotation2, description)
+ SELECT (SELECT a.id FROM annotation as a WHERE a.code="malt"),
+ (SELECT a.id FROM annotation as a WHERE a.description="Dependency"),
+ "Malt dependency layer";
+
+INSERT INTO annotation_pair (annotation1, annotation2, description)
+ SELECT (SELECT a.id FROM annotation as a WHERE a.code="marmot"),
+ (SELECT a.id FROM annotation as a WHERE a.description="Part of speech"),
+ "MarMot part of speech layer";
+
+INSERT INTO annotation_pair (annotation1, annotation2, description)
+ SELECT (SELECT a.id FROM annotation as a WHERE a.code="marmot"),
+ (SELECT a.id FROM annotation as a WHERE a.description="Morphology"),
+ "MarMot morphology layer";
+
+INSERT INTO annotation_pair (annotation1, annotation2, description)
+ SELECT (SELECT a.id FROM annotation as a WHERE a.code="mate"),
+ (SELECT a.id FROM annotation as a WHERE a.description="Dependency"),
+ "Mate dependency layer";
+
+INSERT INTO annotation_pair (annotation1, annotation2, description)
+ SELECT (SELECT a.id FROM annotation as a WHERE a.code="mate"),
+ (SELECT a.id FROM annotation as a WHERE a.description="Lemma"),
+ "Mate lemma layer";
+
+INSERT INTO annotation_pair (annotation1, annotation2, description)
+ SELECT (SELECT a.id FROM annotation as a WHERE a.code="mate"),
+ (SELECT a.id FROM annotation as a WHERE a.description="Part of speech"),
+ "Mate part of speech layer";
+
+INSERT INTO annotation_pair (annotation1, annotation2, description)
+ SELECT (SELECT a.id FROM annotation as a WHERE a.code="mate"),
+ (SELECT a.id FROM annotation as a WHERE a.description="Morphology"),
+ "Mate morphology layer";
+
+INSERT INTO annotation_pair (annotation1, annotation2, description)
+ SELECT (SELECT a.id FROM annotation as a WHERE a.code="mdp"),
+ (SELECT a.id FROM annotation as a WHERE a.description="Dependency"),
+ "MD parser dependency layer";
+
+INSERT INTO annotation_pair (annotation1, annotation2, description)
+ SELECT (SELECT a.id FROM annotation as a WHERE a.code="opennlp"),
+ (SELECT a.id FROM annotation as a WHERE a.description="Part of speech"),
+ "OpenNLP part of speech layer";
+
+INSERT INTO annotation_pair (annotation1, annotation2, description)
+ SELECT (SELECT a.id FROM annotation as a WHERE a.code="opennlp"),
+ (SELECT a.id FROM annotation as a WHERE a.description="Structure"),
+ "OpenNLP structure layer";
+
+INSERT INTO annotation_pair (annotation1, annotation2, description)
+ SELECT (SELECT a.id FROM annotation as a WHERE a.code="sgbr"),
+ (SELECT a.id FROM annotation as a WHERE a.description="Part of speech"),
+ "Schreibgebrauch part of speech layer";
+
+INSERT INTO annotation_pair (annotation1, annotation2, description)
+ SELECT (SELECT a.id FROM annotation as a WHERE a.code="sgbr"),
+ (SELECT a.id FROM annotation as a WHERE a.description="Lemma"),
+ "Schreibgebrauch lemma layer";
+
+INSERT INTO annotation_pair (annotation1, annotation2, description)
+ SELECT (SELECT a.id FROM annotation as a WHERE a.code="sgbr"),
+ (SELECT a.id FROM annotation as a WHERE a.description="Lemma variant"),
+ "Schreibgebrauch lemma variant layer";
+
+INSERT INTO annotation_pair (annotation1, annotation2, description)
+ SELECT (SELECT a.id FROM annotation as a WHERE a.code="tt"),
+ (SELECT a.id FROM annotation as a WHERE a.description="Part of speech"),
+ "Tree Tagger part of speech layer";
+
+INSERT INTO annotation_pair (annotation1, annotation2, description)
+ SELECT (SELECT a.id FROM annotation as a WHERE a.code="tt"),
+ (SELECT a.id FROM annotation as a WHERE a.description="Lemma"),
+ "Tree Tagger lemma layer";
+
+INSERT INTO annotation_pair (annotation1, annotation2, description)
+ SELECT (SELECT a.id FROM annotation as a WHERE a.code="tt"),
+ (SELECT a.id FROM annotation as a WHERE a.description="Structure"),
+ "Tree Tagger structure layer";
+
+INSERT INTO annotation_pair (annotation1, annotation2, description)
+ SELECT (SELECT a.id FROM annotation as a WHERE a.code="xip"),
+ (SELECT a.id FROM annotation as a WHERE a.description="Lemma"),
+ "Xerox Incremental Parser lemma layer";
+
+INSERT INTO annotation_pair (annotation1, annotation2, description)
+ SELECT (SELECT a.id FROM annotation as a WHERE a.code="xip"),
+ (SELECT a.id FROM annotation as a WHERE a.description="Structure"),
+ "Xerox Incremental Parser structure layer";
+
+INSERT INTO annotation_pair (annotation1, annotation2, description)
+ SELECT (SELECT a.id FROM annotation as a WHERE a.code="xip"),
+ (SELECT a.id FROM annotation as a WHERE a.description="Part of speech"),
+ "Xerox Incremental Parser part of speech layer";
+
+INSERT INTO annotation_pair (annotation1, annotation2, description)
+ SELECT (SELECT a.id FROM annotation as a WHERE a.code="xip"),
+ (SELECT a.id FROM annotation as a WHERE a.description="Constituency"),
+ "Xerox Incremental Parser constituency layer";
+
+INSERT INTO annotation_pair (annotation1, annotation2, description)
+ SELECT (SELECT a.id FROM annotation as a WHERE a.code="xip"),
+ (SELECT a.id FROM annotation as a WHERE a.description="Dependency"),
+ "Xerox Incremental Parser dependency layer";
diff --git a/full/src/main/resources/db/new-sqlite/V2.2__insert_annotation_pair_values.sql b/full/src/main/resources/db/new-sqlite/V2.2__insert_annotation_pair_values.sql
new file mode 100644
index 0000000..6f628d2
--- /dev/null
+++ b/full/src/main/resources/db/new-sqlite/V2.2__insert_annotation_pair_values.sql
@@ -0,0 +1,27 @@
+INSERT INTO annotation_pair_value (pair_id, value_id)
+ SELECT
+ (SELECT ap.id FROM annotation_pair as ap WHERE
+ ap.annotation1 = (SELECT a.id FROM annotation as a WHERE a.code="opennlp") AND
+ ap.annotation2 = (SELECT a.id FROM annotation as a WHERE a.description="Structure")),
+ (SELECT a.id FROM annotation as a WHERE a.description="Sentence");
+
+INSERT INTO annotation_pair_value (pair_id, value_id)
+ SELECT
+ (SELECT ap.id FROM annotation_pair as ap WHERE
+ ap.annotation1 = (SELECT a.id FROM annotation as a WHERE a.code="opennlp") AND
+ ap.annotation2 = (SELECT a.id FROM annotation as a WHERE a.description="Structure")),
+ (SELECT a.id FROM annotation as a WHERE a.description="Paragraph");
+
+INSERT INTO annotation_pair_value (pair_id, value_id)
+ SELECT
+ (SELECT ap.id FROM annotation_pair as ap WHERE
+ ap.annotation1 = (SELECT a.id FROM annotation as a WHERE a.code="opennlp") AND
+ ap.annotation2 = (SELECT a.id FROM annotation as a WHERE a.description="Part of speech")),
+ (SELECT a.id FROM annotation as a WHERE a.description="Attributive Adjective");
+
+INSERT INTO annotation_pair_value (pair_id, value_id)
+ SELECT
+ (SELECT ap.id FROM annotation_pair as ap WHERE
+ ap.annotation1 = (SELECT a.id FROM annotation as a WHERE a.code="dereko") AND
+ ap.annotation2 = (SELECT a.id FROM annotation as a WHERE a.description="Structure")),
+ (SELECT a.id FROM annotation as a WHERE a.description="Sentence");
diff --git a/full/src/main/resources/db/new-sqlite/V2.3__insert_resources.sql b/full/src/main/resources/db/new-sqlite/V2.3__insert_resources.sql
new file mode 100644
index 0000000..55cf005
--- /dev/null
+++ b/full/src/main/resources/db/new-sqlite/V2.3__insert_resources.sql
@@ -0,0 +1 @@
+INSERT INTO resource (id, de_title, en_title) VALUES("WPD15","Deutsche Wikipedia Artikel 2015","German Wikipedia Articles 2015");
diff --git a/full/src/main/resources/db/new-sqlite/V2.4__insert_resource_layers.sql b/full/src/main/resources/db/new-sqlite/V2.4__insert_resource_layers.sql
new file mode 100644
index 0000000..98871f7
--- /dev/null
+++ b/full/src/main/resources/db/new-sqlite/V2.4__insert_resource_layers.sql
@@ -0,0 +1,6 @@
+INSERT INTO resource_layer (resource_id, layer_id)
+ SELECT
+ (SELECT id FROM resource WHERE id = "WPD15"),
+ (SELECT ap.id FROM annotation_pair as ap WHERE
+ ap.annotation1 = (SELECT a.id FROM annotation as a WHERE a.code="opennlp") AND
+ ap.annotation2 = (SELECT a.id FROM annotation as a WHERE a.description="Part of speech"));
\ No newline at end of file
diff --git a/full/src/main/resources/default-config.xml b/full/src/main/resources/default-config.xml
index 903521c..c6cd7d2 100644
--- a/full/src/main/resources/default-config.xml
+++ b/full/src/main/resources/default-config.xml
@@ -101,7 +101,8 @@
<!-- <property name="validateOnMigrate" value="false" /> -->
<!-- <property name="cleanOnValidationError" value="true" /> -->
<property name="locations" value="${jdbc.schemaPath}" />
- <property name="dataSource" ref="dataSource" />
+ <!-- <property name="dataSource" ref="dataSource" /> -->
+ <property name="dataSource" ref="sqliteDataSource" />
</bean>
diff --git a/full/src/main/resources/jdbc.properties b/full/src/main/resources/jdbc.properties
index 8cd88d0..ba8fb6d 100644
--- a/full/src/main/resources/jdbc.properties
+++ b/full/src/main/resources/jdbc.properties
@@ -2,21 +2,21 @@
# MySQL Settings
# todo: test user and password in classpath file
-jdbc.database=mysql
-jdbc.driverClassName=com.mysql.jdbc.Driver
-jdbc.url=jdbc:mysql://localhost:3306/kustvakt?autoReconnect=true
-jdbc.username=korap
-jdbc.password=password
-jdbc.schemaPath=db.new-mysql
+#jdbc.database=mysql
+#jdbc.driverClassName=com.mysql.jdbc.Driver
+#jdbc.url=jdbc:mysql://localhost:3306/kustvakt?autoReconnect=true
+#jdbc.username=korap
+#jdbc.password=password
+#jdbc.schemaPath=db.new-mysql
#-------------------------------------------------------------------------------
# Sqlite Settings
-#jdbc.database=sqlite
-#jdbc.driverClassName=org.sqlite.JDBC
-#jdbc.url=jdbc:sqlite:db.sqlite
-## jdbc.url=jdbc:sqlite:kustvakt_init_test.sqlite
-#jdbc.username=pc
-#jdbc.password=pc
-#jdbc.schemaPath=classpath:db.sqlite
+jdbc.database=sqlite
+jdbc.driverClassName=org.sqlite.JDBC
+jdbc.url=jdbc:sqlite:db.new-sqlite
+# jdbc.url=jdbc:sqlite:kustvakt_init_test.sqlite
+jdbc.username=pc
+jdbc.password=pc
+jdbc.schemaPath=db.new-sqlite