Restructured SQL versioning files for database migration.
Change-Id: Icd61033cd0c6904d4dcde11e1a3d4d4169e55c2e
diff --git a/full/src/main/resources/db/new-mysql/V2.0__insert_annotations.sql b/full/src/main/resources/db/insert/V2.0__insert_annotations.sql
similarity index 100%
rename from full/src/main/resources/db/new-mysql/V2.0__insert_annotations.sql
rename to full/src/main/resources/db/insert/V2.0__insert_annotations.sql
diff --git a/full/src/main/resources/db/new-mysql/V2.1__insert_annotation_pairs.sql b/full/src/main/resources/db/insert/V2.1__insert_annotation_pairs.sql
similarity index 100%
rename from full/src/main/resources/db/new-mysql/V2.1__insert_annotation_pairs.sql
rename to full/src/main/resources/db/insert/V2.1__insert_annotation_pairs.sql
diff --git a/full/src/main/resources/db/new-mysql/V2.2__insert_annotation_pair_values.sql b/full/src/main/resources/db/insert/V2.2__insert_annotation_pair_values.sql
similarity index 100%
rename from full/src/main/resources/db/new-mysql/V2.2__insert_annotation_pair_values.sql
rename to full/src/main/resources/db/insert/V2.2__insert_annotation_pair_values.sql
diff --git a/full/src/main/resources/db/new-mysql/V2.3__insert_resources.sql b/full/src/main/resources/db/insert/V2.3__insert_resources.sql
similarity index 100%
rename from full/src/main/resources/db/new-mysql/V2.3__insert_resources.sql
rename to full/src/main/resources/db/insert/V2.3__insert_resources.sql
diff --git a/full/src/main/resources/db/new-mysql/V2.4__insert_resource_layers.sql b/full/src/main/resources/db/insert/V2.4__insert_resource_layers.sql
similarity index 100%
rename from full/src/main/resources/db/new-mysql/V2.4__insert_resource_layers.sql
rename to full/src/main/resources/db/insert/V2.4__insert_resource_layers.sql
diff --git a/full/src/main/resources/db/new-mysql/V3.1__insert_virtual_corpus.sql b/full/src/main/resources/db/insert/V3.1__insert_virtual_corpus.sql
similarity index 100%
rename from full/src/main/resources/db/new-mysql/V3.1__insert_virtual_corpus.sql
rename to full/src/main/resources/db/insert/V3.1__insert_virtual_corpus.sql
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
similarity index 100%
rename from full/src/main/resources/db/new-sqlite/V1__Initial_version.sql
rename to full/src/main/resources/db/new-sqlite/V1__initial_version.sql
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
deleted file mode 100644
index 911f5de..0000000
--- a/full/src/main/resources/db/new-sqlite/V2.0__insert_annotations.sql
+++ /dev/null
@@ -1,33 +0,0 @@
---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
deleted file mode 100644
index 3bf0f32..0000000
--- a/full/src/main/resources/db/new-sqlite/V2.1__insert_annotation_pairs.sql
+++ /dev/null
@@ -1,180 +0,0 @@
-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
deleted file mode 100644
index 6f628d2..0000000
--- a/full/src/main/resources/db/new-sqlite/V2.2__insert_annotation_pair_values.sql
+++ /dev/null
@@ -1,27 +0,0 @@
-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
deleted file mode 100644
index 55cf005..0000000
--- a/full/src/main/resources/db/new-sqlite/V2.3__insert_resources.sql
+++ /dev/null
@@ -1 +0,0 @@
-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
deleted file mode 100644
index 98871f7..0000000
--- a/full/src/main/resources/db/new-sqlite/V2.4__insert_resource_layers.sql
+++ /dev/null
@@ -1,6 +0,0 @@
-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/db/new-sqlite/V3.1__insert_virtual_corpus.sql b/full/src/main/resources/db/new-sqlite/V3.1__insert_virtual_corpus.sql
deleted file mode 100644
index 442a39c..0000000
--- a/full/src/main/resources/db/new-sqlite/V3.1__insert_virtual_corpus.sql
+++ /dev/null
@@ -1,89 +0,0 @@
--- 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 48d2d9d..65fba6b 100644
--- a/full/src/main/resources/jdbc.properties
+++ b/full/src/main/resources/jdbc.properties
@@ -7,7 +7,9 @@
#jdbc.url=jdbc:mysql://localhost:3306/kustvakt?autoReconnect=true
#jdbc.username=korap
#jdbc.password=password
-#jdbc.schemaPath=db.new-mysql
+
+# db.insert contains test data, omit it in production setting
+#jdbc.schemaPath=db.new-mysql, db.insert
#-------------------------------------------------------------------------------
@@ -19,4 +21,5 @@
# jdbc.url=jdbc:sqlite:kustvakt_init_test.sqlite
jdbc.username=pc
jdbc.password=pc
-jdbc.schemaPath=db.new-sqlite
+# db.insert contains test data, omit it in production setting
+jdbc.schemaPath=db.new-sqlite, db.insert