kustvakt extension db merge and maven build for light version
diff --git a/src/main/resources/.DS_Store b/src/main/resources/.DS_Store
index 5008ddf..51b3007 100644
--- a/src/main/resources/.DS_Store
+++ b/src/main/resources/.DS_Store
Binary files differ
diff --git a/src/main/resources/ESAPI.properties b/src/main/resources/ESAPI.properties
new file mode 100644
index 0000000..b822b21
--- /dev/null
+++ b/src/main/resources/ESAPI.properties
@@ -0,0 +1,188 @@
+#
+# OWASP Enterprise Security API (ESAPI) Properties file -- PRODUCTION Version
+#
+# This file is part of the Open Web de.ids_mannheim.korap.news.Application Security Project (OWASP)
+# Enterprise Security API (ESAPI) project. For details, please see
+# http://www.owasp.org/index.php/ESAPI.
+#
+# Copyright (c) 2008,2009 - The OWASP Foundation
+#
+# DISCUSS: This may cause a major backwards compatibility issue, etc. but
+# from a name space perspective, we probably should have prefaced
+# all the property names with ESAPI or at least OWASP. Otherwise
+# there could be problems is someone loads this properties file into
+# the System properties. We could also put this file into the
+# esapi.jar file (perhaps as a ResourceBundle) and then allow an external
+# ESAPI properties be defined that would overwrite these defaults.
+# That keeps the application's properties relatively simple as usually
+# they will only want to override a few properties. If looks like we
+# already support multiple override levels of this in the
+# DefaultSecurityConfiguration class, but I'm suggesting placing the
+# defaults in the esapi.jar itself. That way, if the jar is signed,
+# we could detect if those properties had been tampered with. (The
+# code to isSystem the jar signatures is pretty simple... maybe 70-90 LOC,
+# but off course there is an execution penalty (similar to the way
+# that the separate sunjce.jar used to be when a class from it was
+# first loaded). Thoughts?
+###############################################################################
+
+ESAPI.Randomizer=org.owasp.esapi.reference.DefaultRandomizer
+ESAPI.Validator=org.owasp.esapi.reference.DefaultValidator
+
+#===========================================================================
+# ESAPI Encoder
+#
+# ESAPI canonicalizes input before validation to prevent bypassing filters with encoded attacks.
+# Failure to canonicalize input is a very common mistake when implementing validation schemes.
+# Canonicalization is automatic when using the ESAPI Validator, but you can also use the
+# following code to canonicalize data.
+#
+# ESAPI.Encoder().canonicalize( "%22hello world"" );
+#
+# Multiple encoding is when a single encoding format is applied multiple times. Allowing
+# multiple encoding is strongly discouraged.
+Encoder.AllowMultipleEncoding=false
+
+# Mixed encoding is when multiple different encoding formats are applied, or when
+# multiple formats are nested. Allowing multiple encoding is strongly discouraged.
+Encoder.AllowMixedEncoding=false
+
+# The default list of codecs to apply when canonicalizing untrusted data. The list should include the codecs
+# for all downstream interpreters or decoders. For example, if the data is likely to end up in a URL, HTML, or
+# inside JavaScript, then the list of codecs below is appropriate. The order of the list is not terribly important.
+Encoder.DefaultCodecList=HTMLEntityCodec,PercentCodec,JavaScriptCodec
+
+
+#===========================================================================
+# ESAPI Encryption
+#
+# The ESAPI Encryptor provides basic cryptographic functions with a simplified API.
+# To get started, generate a new key using java -classpath esapi.jar org.owasp.esapi.reference.crypto.JavaEncryptor
+# There is not currently any support for key rotation, so be careful when changing your key and salt as it
+# will invalidate all signed, encrypted, and hashed data.
+#
+# WARNING: Not all combinations of algorithms and key lengths are supported.
+# If you choose to use a key length greater than 128, you MUST download the
+# unlimited strength policy files and install in the lib directory of your JRE/JDK.
+# See http://java.sun.com/javase/downloads/index.jsp for more information.
+#
+# Backward compatibility with ESAPI Java 1.4 is supported by the two deprecated API
+# methods, Encryptor.encrypt(String) and Encryptor.decrypt(String). However, whenever
+# possible, these methods should be avoided as they use ECB cipher mode, which in almost
+# all circumstances a poor choice because of it's weakness. CBC cipher mode is the default
+# for the new Encryptor encrypt / decrypt methods for ESAPI Java 2.0. In general, you
+# should only use this compatibility setting if you have persistent data encrypted with
+# version 1.4 and even then, you should ONLY set this compatibility mode UNTIL
+# you have decrypted all of your old encrypted data and then re-encrypted it with
+# ESAPI 2.0 using CBC mode. If you have some reason to mix the deprecated 1.4 mode
+# with the new 2.0 methods, make sure that you use the same cipher algorithm for both
+# (256-bit AES was the default for 1.4; 128-bit is the default for 2.0; see below for
+# more details.) Otherwise, you will have to use the new 2.0 encrypt / decrypt methods
+# where you can specify a SecretKey. (Note that if you are using the 256-bit AES,
+# that requires downloading the special jurisdiction policy files mentioned above.)
+#
+# ***** IMPORTANT: Do NOT forget to replace these with your own values! *****
+# To calculate these values, you can run:
+# java -classpath esapi.jar org.owasp.esapi.reference.crypto.JavaEncryptor
+#
+#Encryptor.MasterKey=
+## default key
+#Encryptor.MasterSalt=434fsdferbs7sdf5sdf+d23=a
+
+#==============================================================
+Encryptor.MasterKey=Nowq7w96tBckpYCPkoBtjQ==
+Encryptor.MasterSalt=vRaKzzh7hLp9v3CXi7KDI/1yO3A=
+#==============================================================
+
+#===========================================================================
+# ESAPI Intrusion Detection
+#
+# Each event has a base to which .count, .interval, and .action are added
+# The IntrusionException will fire if we receive "count" events within "interval" seconds
+# The IntrusionDetector is configurable to take the following actions: log, logout, and disable
+# (multiple actions separated by commas are allowed e.g. event.test.actions=log,disable
+#
+# Custom Events
+# Names must start with "event." as the base
+# Use IntrusionDetector.addEvent( "test" ) in your code to trigger "event.test" here
+# You can also disable intrusion detection completely by changing
+# the following parameter to true
+#
+IntrusionDetector.Disable=false
+#
+IntrusionDetector.event.test.count=2
+IntrusionDetector.event.test.interval=10
+IntrusionDetector.event.test.actions=disable,log
+
+# Exception Events
+# All EnterpriseSecurityExceptions are registered automatically
+# Call IntrusionDetector.getInstance().addException(e) for Exceptions that do not extend EnterpriseSecurityException
+# Use the fully qualified classname of the exception as the base
+
+# any intrusion is an attack
+IntrusionDetector.org.owasp.esapi.errors.IntrusionException.count=1
+IntrusionDetector.org.owasp.esapi.errors.IntrusionException.interval=1
+IntrusionDetector.org.owasp.esapi.errors.IntrusionException.actions=log,disable,logout
+
+# for test purposes
+# CHECKME: Shouldn't there be something in the property name itself that designates
+# that these are for testing???
+IntrusionDetector.org.owasp.esapi.errors.IntegrityException.count=10
+IntrusionDetector.org.owasp.esapi.errors.IntegrityException.interval=5
+IntrusionDetector.org.owasp.esapi.errors.IntegrityException.actions=log,disable,logout
+
+# rapid validation errors indicate scans or attacks in progress
+org.owasp.esapi.errors.ValidationException.count=10
+org.owasp.esapi.errors.ValidationException.interval=10
+org.owasp.esapi.errors.ValidationException.actions=log,logout
+
+# sessions jumping between hosts indicates session hijacking
+IntrusionDetector.org.owasp.esapi.errors.AuthenticationHostException.count=2
+IntrusionDetector.org.owasp.esapi.errors.AuthenticationHostException.interval=10
+IntrusionDetector.org.owasp.esapi.errors.AuthenticationHostException.actions=log,logout
+
+
+#===========================================================================
+# ESAPI Validation
+#
+# The ESAPI Validator works on regular expressions with defined names. You can define names
+# either here, or you may define application specific patterns in a separate file defined below.
+# This allows enterprises to specify both organizational standards as well as application specific
+# validation rules.
+#
+Validator.ConfigurationFile=validation.properties
+
+# Validators used by ESAPI
+Validator.AccountName=^[a-zA-Z0-9]{3,20}$
+Validator.SystemCommand=^[a-zA-Z\\-\\/]{1,64}$
+Validator.RoleName=^[a-z]{1,20}$
+
+#the word TEST below should be changed to your application
+#name - only relative URL's are supported
+Validator.Redirect=^\\/test.*$
+
+# Global HTTP Validation Rules
+# Values with Base64 encoded data (e.g. encrypted state) will need at least [a-zA-Z0-9\/+=]
+Validator.HTTPScheme=^(http|https)$
+Validator.HTTPServerName=^[a-zA-Z0-9_.\\-]*$
+Validator.HTTPParameterName=^[a-zA-Z0-9_]{1,32}$
+Validator.HTTPParameterValue=^[a-zA-Z0-9.\\-\\/+=@_ ]*$
+Validator.HTTPCookieName=^[a-zA-Z0-9\\-_]{1,32}$
+Validator.HTTPCookieValue=^[a-zA-Z0-9\\-\\/+=_ ]*$
+Validator.HTTPHeaderName=^[a-zA-Z0-9\\-_]{1,32}$
+Validator.HTTPHeaderValue=^[a-zA-Z0-9()\\-=\\*\\.\\?;,+\\/:&_ ]*$
+Validator.HTTPContextPath=^\\/?[a-zA-Z0-9.\\-\\/_]*$
+Validator.HTTPServletPath=^[a-zA-Z0-9.\\-\\/_]*$
+Validator.HTTPPath=^[a-zA-Z0-9.\\-_]*$
+Validator.HTTPQueryString=^[a-zA-Z0-9()\\-=\\*\\.\\?;,+\\/:&_ %]*$
+Validator.HTTPURI=^[a-zA-Z0-9()\\-=\\*\\.\\?;,+\\/:&_ ]*$
+Validator.HTTPURL=^.*$
+Validator.HTTPJSESSIONID=^[A-Z0-9]{10,30}$
+
+# Validation of file related input
+Validator.FileName=^[a-zA-Z0-9!@#$%^&{}\\[\\]()_+\\-=,.~'` ]{1,255}$
+Validator.DirectoryName=^[a-zA-Z0-9:/\\\\!@#$%^&{}\\[\\]()_+\\-=,.~'` ]{1,255}$
+
+# Validation of dates. Controls whether or not 'lenient' dates are accepted.
+# See DataFormat.setLenient(boolean flag) for further details.
+Validator.AcceptLenientDates=false
diff --git a/src/main/resources/db/mysql/V1.0__initial_version.sql b/src/main/resources/db/mysql/V1.0__initial_version.sql
new file mode 100644
index 0000000..8e4c17b
--- /dev/null
+++ b/src/main/resources/db/mysql/V1.0__initial_version.sql
@@ -0,0 +1,326 @@
+
+-- rename all columns in new way!
+CREATE TABLE IF NOT EXISTS korapusers (
+ id INTEGER PRIMARY KEY AUTO_INCREMENT,
+ username VARCHAR(100) NOT NULL UNIQUE,
+ password VARCHAR(100) NOT NULL,
+ accountLock boolean NOT NULL,
+ accountCreation TIMESTAMP NOT NULL,
+ type INTEGER DEFAULT 0,
+ URI_PASS_Fragment VARCHAR(100),
+ URI_CONF_Fragment VARCHAR(100),
+ URI_Expiration TIMESTAMP,
+ loginSuccess INTEGER,
+ loginFailed INTEGER,
+ accountExpiration TIMESTAMP NOT NULL,
+ accountLink VARCHAR(100)
+);
+
+CREATE TABLE IF NOT EXISTS shibusers (
+ id INTEGER PRIMARY KEY AUTO_INCREMENT,
+ username VARCHAR(100) NOT NULL UNIQUE,
+ accountCreation TIMESTAMP NOT NULL,
+ type INTEGER DEFAULT 1,
+ loginSuccess INTEGER,
+ loginFailed INTEGER,
+ accountExpiration TIMESTAMP NOT NULL,
+ accountLink VARCHAR(100)
+);
+
+CREATE TABLE IF NOT EXISTS udetails (
+ Id INTEGER PRIMARY KEY AUTO_INCREMENT,
+ userID INTEGER NOT NULL UNIQUE,
+ firstName VARCHAR(100),
+ lastName VARCHAR(100),
+ gender VARCHAR(100),
+ phone VARCHAR(100),
+ institution VARCHAR(100),
+ email VARCHAR(100),
+ address VARCHAR(100),
+ country VARCHAR(100),
+ privateUsage BOOLEAN,
+ foreign key (userID)
+ references korapusers (id)
+ on delete cascade
+);
+
+CREATE TABLE IF NOT EXISTS usettings (
+ Id INTEGER PRIMARY KEY AUTO_INCREMENT,
+ userID INTEGER NOT NULL UNIQUE,
+ fileNameForExport VARCHAR(100),
+ itemForSimpleAnnotation INTEGER,
+ leftContextItemForExport VARCHAR(100),
+ leftContextSizeForExport INTEGER,
+ locale VARCHAR(100),
+ leftContextItem VARCHAR(100),
+ leftContextSize INTEGER,
+ rightContextItem VARCHAR(100),
+ rightContextItemForExport VARCHAR(100),
+ rightContextSize INTEGER,
+ rightContextSizeForExport INTEGER,
+ selectedCollection VARCHAR(100),
+ queryLanguage VARCHAR(100),
+ pageLength INTEGER,
+ metadataQueryExpertModus BOOLEAN,
+ searchSettingsTab INTEGER,
+ selectedGraphType INTEGER,
+ selectedSortType VARCHAR(100),
+ selectedViewForSearchResults VARCHAR(100),
+ POSFoundry VARCHAR(100),
+ lemmaFoundry VARCHAR(100),
+ constFoundry VARCHAR(100),
+ relFoundry VARCHAR(100),
+ collectData BOOLEAN,
+ foreign key (userID)
+ references korapusers (id)
+ on delete cascade
+);
+
+CREATE OR REPLACE VIEW allusers AS
+ SELECT
+ id,
+ username,
+ password,
+ accountLock,
+ accountCreation,
+ type,
+ URI_PASS_Fragment,
+ URI_CONF_Fragment,
+ URI_Expiration,
+ loginSuccess,
+ loginFailed,
+ accountExpiration,
+ accountLink
+ from
+ korapusers
+ UNION ALL SELECT
+ id,
+ username,
+ NULL as password,
+ NULL as accountLock,
+ accountCreation,
+ type,
+ NULL as URI_PASS_Fragment,
+ NULL as URI_CONF_Fragment,
+ NULL as URI_Expiration,
+ loginSuccess,
+ loginFailed,
+ accountExpiration,
+ accountLink
+ from
+ shibusers;
+
+-- why unsigned?
+CREATE TABLE IF NOT EXISTS r_store (
+id INTEGER PRIMARY KEY AUTO_INCREMENT,
+persistent_id VARCHAR(100) NOT NULL UNIQUE,
+name VARCHAR(100),
+description VARCHAR(300),
+parent_id Integer unsigned null,
+created timestamp default current_timestamp,
+type INTEGER NOT NULL,
+creator INTEGER NOT NULL
+);
+
+CREATE TABLE IF NOT EXISTS uqueries (
+ id INTEGER PRIMARY KEY,
+ queryLanguage VARCHAR(100),
+ name VARCHAR(100),
+ query VARCHAR(200),
+ description VARCHAR(150),
+ foreign key (id)
+ references r_store(id)
+ on delete cascade
+);
+
+CREATE TABLE IF NOT EXISTS r_tree (
+parent_id INTEGER,
+child_id INTEGER,
+depth INTEGER,
+name_path VARCHAR(250),
+PRIMARY KEY (parent_id , child_id),
+foreign key (parent_id)
+references r_store (id)
+on delete cascade,
+foreign key (child_id)
+references r_store (id)
+on delete cascade
+);
+
+
+CREATE TABLE IF NOT EXISTS cstorage (
+ id INTEGER,
+ refCorpus VARCHAR(100),
+ query VARCHAR(500),
+ foreign key (id) references r_store(id)
+ on delete cascade);
+
+
+CREATE TABLE IF NOT EXISTS matchInfo (id INTEGER PRIMARY KEY AUTO_INCREMENT, userid BIGINT NOT NULL,
+matchInfo VARCHAR(100));
+
+CREATE TABLE IF NOT EXISTS resourceRecords (
+ AUD_ID INTEGER PRIMARY KEY AUTO_INCREMENT,
+ AUD_RESOURCE VARCHAR(100),
+ AUD_USER VARCHAR(100),
+ AUD_LOC VARCHAR(100),
+ AUD_OP VARCHAR(100),
+ AUD_TIMESTAMP TIMESTAMP,
+ AUD_FAILURE VARCHAR(100)
+);
+
+CREATE TABLE IF NOT EXISTS databaseRecords (
+ AUD_ID INTEGER PRIMARY KEY AUTO_INCREMENT,
+ AUD_TARGET VARCHAR(100),
+ AUD_USER VARCHAR(100),
+ AUD_LOC VARCHAR(100),
+ AUD_OP VARCHAR(100),
+ AUD_TIMESTAMP TIMESTAMP,
+ AUD_FAILURE VARCHAR(100)
+);
+
+CREATE TABLE IF NOT EXISTS securityRecords (
+ AUD_ID INTEGER PRIMARY KEY AUTO_INCREMENT,
+ AUD_USER VARCHAR(100),
+ AUD_LOC VARCHAR(100),
+ AUD_OP VARCHAR(100),
+ AUD_TIMESTAMP TIMESTAMP,
+ AUD_FAILURE VARCHAR(100)
+);
+
+
+CREATE TABLE IF NOT EXISTS doc_trace (
+id VARCHAR(200) PRIMARY KEY,
+resource INTEGER,
+created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
+foreign key (resource)
+references r_store(id)
+);
+
+-- last_modified timestamp ON UPDATE CURRENT_TIMESTAMP,
+CREATE TABLE IF NOT EXISTS p_store (
+ id INTEGER PRIMARY KEY AUTO_INCREMENT,
+ target_id BIGINT NOT NULL,
+ created TIMESTAMP,
+ creator INTEGER NOT NULL,
+ posix SMALLINT NOT NULL,
+ expire TIMESTAMP NULL,
+ enable TIMESTAMP NULL,
+ iprange varchar(200)
+);
+
+CREATE TABLE IF NOT EXISTS conditionDef (
+ id INTEGER PRIMARY KEY AUTO_INCREMENT,
+ groupRef VARCHAR(100) NOT NULL,
+ policyid INTEGER NOT NULL
+);
+
+
+CREATE TABLE IF NOT EXISTS groupStore (
+ name VARCHAR(100) PRIMARY KEY,
+ description VARCHAR(200),
+ sym_use INTEGER DEFAULT -1,
+ export VARCHAR(30) DEFAULT NULL,
+ query_only VARCHAR(30) DEFAULT NULL,
+ licence INTEGER DEFAULT -1,
+ -- basically every resource we have is an academic resource, thus a non-commercial use is infered!
+ commercial BOOLEAN DEFAULT FALSE
+);
+
+CREATE TABLE IF NOT EXISTS groupUsers (
+ id INTEGER PRIMARY KEY AUTO_INCREMENT,
+ userID BIGINT NOT NULL,
+ groupRef VARCHAR(100) NOT NULL,
+ admin BOOLEAN NOT NULL DEFAULT FALSE,
+ FOREIGN KEY (groupRef)
+ REFERENCES groupStore (name) on delete cascade
+);
+
+CREATE TABLE IF NOT EXISTS paramStore (
+ id INTEGER PRIMARY KEY AUTO_INCREMENT,
+ p_key VARCHAR(100) NOT NULL,
+ p_value VARCHAR(150) NOT NULL,
+ resource INTEGER DEFAULT -1,
+ pid INTEGER DEFAULT -1,
+ FOREIGN KEY (resource)
+ REFERENCES r_store(id)
+ on delete cascade,
+ FOREIGN KEY (pid)
+ REFERENCES p_store(id)
+ on delete cascade
+);
+
+CREATE TABLE IF NOT EXISTS paramMapping (
+ id INTEGER PRIMARY KEY AUTO_INCREMENT,
+ policyID INTEGER NOT NULL,
+ paramID INTEGER NOT NULL,
+ value VARCHAR(100) NOT NULL,
+ flag BOOLEAN NOT NULL DEFAULT FALSE,
+ FOREIGN KEY (paramID)
+ REFERENCES paramStore (id),
+ FOREIGN KEY (policyID)
+ REFERENCES p_store (id)
+);
+
+create or replace view p_view as
+select
+ po.id as pid,
+ po.target_id as id,
+ rs.persistent_id as persistent_id,
+ rs.name as name,
+ rs.type as type,
+ c.groupref as groupref,
+ po.posix as perm,
+ po.creator as creator,
+ po.expire as expire,
+ po.enable as enable,
+ po.iprange as iprange
+from
+p_store as po
+inner join
+conditionDef as c ON c.policyid = po.id
+inner join
+r_store as rs ON rs.id = po.target_id
+union all select
+ - 1 as pid,
+ rs.id as id,
+ rs.persistent_id as persistent_id,
+ rs.name as name,
+ type as type,
+ 'self' as groupref,
+ 127 as perm,
+ creator,
+ NULL as expire,
+ rs.created as enable,
+ null as iprange
+ from
+ r_store as rs;
+
+
+-- indices
+create trigger delete_policy after delete on r_store
+for each row delete from p_store where target_id=OLD.id;
+
+DELIMITER //
+CREATE TRIGGER tree_entry_insert AFTER INSERT ON r_store FOR EACH ROW BEGIN
+ INSERT INTO r_tree (parent_id, child_id, depth, name_path)
+ VALUES (NEW.id, NEW.id, 0, NEW.name);
+ INSERT INTO r_tree (parent_id, child_id, depth, name_path)
+ SELECT parent_id, NEW.id, rt.depth + 1, concat(name_path,"/",NEW.name) FROM r_tree WHERE child_id = NEW.parent_id;
+END; //
+
+DELIMITER ;
+
+-- todo: are this automatically adapted when refactoring?
+CREATE INDEX group_index ON groupUsers(userid);
+CREATE INDEX policy_index ON conditionDef(policyid);
+CREATE UNIQUE INDEX r_tree_index ON r_tree (parent_id, depth, child_id);
+CREATE UNIQUE INDEX para_unique ON paramStore (p_key, p_value);
+
+-- foreign key constraints
+
+
+
+
+
+
diff --git a/src/main/resources/db/mysql/V1.1__mysql_refactoring.sql b/src/main/resources/db/mysql/V1.1__mysql_refactoring.sql
new file mode 100644
index 0000000..4cc3b21
--- /dev/null
+++ b/src/main/resources/db/mysql/V1.1__mysql_refactoring.sql
@@ -0,0 +1,139 @@
+-- alter table korapusers TO korap_users;
+rename table usettings TO user_settings;
+alter table user_settings drop column itemForSimpleAnnotation;
+alter table user_settings drop column searchSettingsTab;
+alter table user_settings drop column selectedGraphType;
+alter table user_settings drop column selectedSortType;
+alter table user_settings drop column selectedViewForSearchResults;
+rename table udetails to user_details;
+rename table uqueries to user_queries;
+rename table korapusers to korap_users;
+rename table shibusers to shib_users;
+rename table matchInfo to match_info;
+
+alter table korap_users change column URI_PASS_Fragment uri_fragment VARCHAR(100);
+alter table korap_users change column URI_Expiration uri_expiration TIMESTAMP;
+drop view allusers;
+
+rename table r_store TO resource_store;
+rename table r_tree TO resource_tree;
+
+rename table groupStore TO group_store;
+rename table groupUsers TO group_users;
+rename table paramStore TO param_store;
+rename table paramMapping TO param_map;
+
+-- todo: what about the moving of the entries?
+-- rather rename than drop!
+-- drop table cstorage;
+-- todo: test
+rename table cstorage to coll_store;
+alter table coll_store add column (
+created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
+userID INTEGER);
+alter table coll_store drop column refCorpus;
+
+
+-- do not recreate -- maintain data
+--CREATE TABLE IF NOT EXISTS coll_store (
+--id INTEGER PRIMARY KEY AUTO_INCREMENT,
+--persistentID VARCHAR(150) UNIQUE,
+--name VARCHAR(150),
+--description VARCHAR(200),
+--query VARCHAR(500),
+--created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
+--userID INTEGER,
+--foreign key(userID)
+--references korap_users(id)
+--on delete cascade
+--);
+
+
+drop table doc_trace;
+
+CREATE TABLE IF NOT EXISTS doc_store (
+id VARCHAR(230) PRIMARY KEY,
+persistent_id VARCHAR(230) UNIQUE,
+created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
+disabled BOOLEAN default true
+);
+
+
+
+rename table p_store to policy_store;
+rename table conditionDef to group_ref;
+alter table group_ref change groupRef group_id VARCHAR(100) NOT NULL;
+alter table group_ref change policyId policy_id VARCHAR(100) NOT NULL;
+
+drop view p_view;
+create or replace view policy_view as
+select
+ po.id as pid,
+ po.target_id as id,
+ rs.persistent_id as persistent_id,
+ rs.name as name,
+ rs.type as type,
+ c.group_id as group_id,
+ po.posix as perm,
+ po.creator as creator,
+ po.expire as expire,
+ po.enable as enable,
+ po.iprange as iprange
+from
+policy_store as po
+inner join
+group_ref as c ON c.policy_id = po.id
+inner join
+resource_store as rs ON rs.id = po.target_id
+union all select
+ - 1 as pid,
+ rs.id as id,
+ rs.persistent_id as persistent_id,
+ rs.name as name,
+ type as type,
+ 'self' as groupId,
+ 127 as perm,
+ creator,
+ NULL as expire,
+ rs.created as enable,
+ null as iprange
+ from
+ resource_store as rs;
+
+
+drop table resourceRecords;
+drop table databaseRecords;
+drop table securityRecords;
+
+CREATE TABLE IF NOT EXISTS audit_records (
+id INTEGER PRIMARY KEY AUTO_INCREMENT,
+aud_category VARCHAR(100),
+aud_target VARCHAR(100),
+aud_user VARCHAR(100),
+aud_location VARCHAR(100),
+aud_operation VARCHAR(100),
+aud_field_1 VARCHAR(400),
+aud_timestamp TIMESTAMP,
+aud_failure VARCHAR(100)
+);
+
+
+
+drop trigger tree_entry_insert;
+
+DELIMITER //
+CREATE TRIGGER tree_entry_insert AFTER INSERT ON resource_store FOR EACH ROW BEGIN
+ INSERT INTO resource_tree (parent_id, child_id, depth, name_path)
+ VALUES (NEW.id, NEW.id, 0, NEW.name);
+ INSERT INTO resource_tree (parent_id, child_id, depth, name_path)
+ SELECT parent_id, NEW.id, depth + 1, concat(name_path,"/",NEW.name)
+ FROM resource_tree WHERE child_id = NEW.parent_id;
+END; //
+
+DELIMITER ;
+
+
+drop trigger delete_policy;
+
+create trigger delete_policy after delete on resource_store
+for each row delete from policy_store where target_id=OLD.id;
\ No newline at end of file
diff --git a/src/main/resources/db/mysql/V1.2__oauth2_tables_mysql.sql b/src/main/resources/db/mysql/V1.2__oauth2_tables_mysql.sql
new file mode 100644
index 0000000..5e9c968
--- /dev/null
+++ b/src/main/resources/db/mysql/V1.2__oauth2_tables_mysql.sql
@@ -0,0 +1,63 @@
+
+-- oauth2 db tables
+create table oauth2_client (
+client_id VARCHAR(100) UNIQUE PRIMARY KEY,
+client_secret VARCHAR(200),
+redirect_uri VARCHAR(250),
+client_type VARCHAR(200),
+is_confidential BOOLEAN DEFAULT FALSE,
+url VARCHAR(200) UNIQUE
+);
+
+create table oauth2_auth_codes (
+id INTEGER PRIMARY KEY AUTO_INCREMENT,
+client_id VARCHAR(100),
+auth_code VARCHAR(250),
+status INTEGER DEFAULT 1,
+scopes VARCHAR (150),
+created TIMESTAMP DEFAULT CURRENT_TIMESTAMP
+);
+
+-- define scopes?!
+create table oauth2_client_authorization (
+id INTEGER PRIMARY KEY AUTO_INCREMENT,
+fk_client_id VARCHAR(100),
+user_id INTEGER,
+FOREIGN KEY (fk_client_id)
+REFERENCES oauth2_client(client_id)
+ON DELETE CASCADE
+);
+
+---- status 1 = valid, 0 = revoked
+--create table oauth2_access_token (
+--id INTEGER PRIMARY KEY AUTO_INCREMENT,
+--access_token VARCHAR(300),
+--auth_code VARCHAR(250),
+--userID INTEGER,
+--status INTEGER DEFAULT 1,
+--expiration TIMESTAMP,
+--scopes VARCHAR (150),
+--FOREIGN KEY (userID)
+--REFERENCES korap_users(id)
+--);
+
+-- status 1 = valid, 0 = revoked, -1 = disabled
+create table oauth2_access_token (
+id INTEGER PRIMARY KEY AUTO_INCREMENT,
+access_token VARCHAR(300),
+auth_code VARCHAR(250),
+client_id VARCHAR(100),
+user_id INTEGER,
+-- make boolean --
+status INTEGER DEFAULT 1,
+-- in case of code authorization, should match auth code scopes!
+-- use scopes for levelaccess descriptor level[rw],level[r]
+scopes VARCHAR(350),
+expiration TIMESTAMP,
+FOREIGN KEY (user_id)
+REFERENCES korap_users(id)
+ON DELETE CASCADE,
+FOREIGN KEY (client_id)
+REFERENCES oauth2_client(client_id)
+ON DELETE CASCADE
+);
diff --git a/src/main/resources/db/mysql/mysql_schema_comp.sql b/src/main/resources/db/mysql/mysql_schema_comp.sql
new file mode 100644
index 0000000..6ee11c5
--- /dev/null
+++ b/src/main/resources/db/mysql/mysql_schema_comp.sql
@@ -0,0 +1,314 @@
+CREATE TABLE IF NOT EXISTS korap_users (
+ id INTEGER PRIMARY KEY AUTO_INCREMENT,
+ username VARCHAR(100) NOT NULL UNIQUE,
+ password VARCHAR(100) NOT NULL,
+ accountLock boolean NOT NULL,
+ accountCreation TIMESTAMP NOT NULL,
+ type INTEGER DEFAULT 0,
+ uriFragment VARCHAR(100),
+ uriExpiration TIMESTAMP,
+ accountLink VARCHAR(100)
+)$$
+
+
+CREATE TABLE IF NOT EXISTS shib_users (
+ id INTEGER PRIMARY KEY AUTO_INCREMENT,
+ username VARCHAR(100) NOT NULL UNIQUE,
+ accountCreation TIMESTAMP NOT NULL,
+ type INTEGER DEFAULT 1,
+ loginSuccess INTEGER,
+ loginFailed INTEGER,
+ accountExpiration TIMESTAMP NOT NULL,
+ accountLink VARCHAR(100)
+)$$
+
+CREATE TABLE IF NOT EXISTS user_details (
+ Id INTEGER PRIMARY KEY AUTO_INCREMENT,
+ userID INTEGER NOT NULL UNIQUE,
+ firstName VARCHAR(100),
+ lastName VARCHAR(100),
+ gender VARCHAR(100),
+ phone VARCHAR(100),
+ institution VARCHAR(100),
+ email VARCHAR(100),
+ address VARCHAR(100),
+ country VARCHAR(100),
+ privateUsage BOOLEAN,
+ foreign key (userId)
+ references korap_users (id)
+ on delete cascade
+)$$
+
+CREATE TABLE IF NOT EXISTS user_settings (
+id INTEGER PRIMARY KEY AUTO_INCREMENT,
+userId INTEGER NOT NULL,
+fileNameForExport VARCHAR(100),
+leftContextItemForExport VARCHAR(100),
+leftContextSizeForExport INTEGER,
+locale VARCHAR(100),
+leftContextItem VARCHAR(100),
+leftContextSize INTEGER,
+rightContextItem VARCHAR(100),
+rightContextItemForExport VARCHAR(100),
+rightContextSize INTEGER,
+rightContextSizeForExport INTEGER,
+selectedCollection VARCHAR(100),
+queryLanguage VARCHAR(100),
+pageLength INTEGER,
+metadataQueryExpertModus BOOLEAN,
+POSFoundry VARCHAR(100),
+lemmaFoundry VARCHAR(100),
+constFoundry VARCHAR(100),
+relFoundry VARCHAR(100),
+collectData BOOLEAN DEFAULT TRUE,
+foreign key (userId)
+references korap_users (id)
+on delete cascade
+)$$
+
+CREATE TABLE IF NOT EXISTS user_queries (
+ id INTEGER PRIMARY KEY,
+ queryLanguage VARCHAR(100),
+ name VARCHAR(100),
+ query VARCHAR(200),
+ description VARCHAR(150),
+ foreign key (id)
+ references resource_store(id)
+ on delete cascade)$$
+
+
+CREATE TABLE IF NOT EXISTS resource_store (
+id INTEGER PRIMARY KEY AUTO_INCREMENT,
+persistentID VARCHAR(100) NOT NULL UNIQUE,
+name VARCHAR(100),
+description VARCHAR(300),
+parentID Integer unsigned null,
+created timestamp default current_timestamp,
+type INTEGER NOT NULL,
+creator INTEGER NOT NULL
+)$$
+
+
+CREATE TABLE IF NOT EXISTS resource_tree (
+parentID INTEGER,
+childID INTEGER,
+depth INTEGER,
+name_path VARCHAR(250),
+PRIMARY KEY (parentID , childID),
+foreign key (parentID)
+references resource_store (id)
+on delete cascade,
+foreign key (childID)
+references resource_store (id)
+on delete cascade
+)$$
+
+
+CREATE TABLE IF NOT EXISTS coll_store (
+id INTEGER PRIMARY KEY AUTO_INCREMENT,
+persistentID VARCHAR(150) UNIQUE,
+name VARCHAR(150),
+description VARCHAR(200),
+query VARCHAR(500),
+created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
+userID INTEGER,
+foreign key(userID)
+references korap_users(id)
+on delete cascade
+)$$
+
+
+
+CREATE TABLE IF NOT EXISTS match_info (
+id INTEGER PRIMARY KEY AUTO_INCREMENT,
+userid BIGINT NOT NULL,
+match_info VARCHAR(100)
+)$$
+
+CREATE TABLE IF NOT EXISTS audit_records (
+id INTEGER PRIMARY KEY AUTO_INCREMENT,
+aud_category VARCHAR(100),
+aud_target VARCHAR(100),
+aud_user VARCHAR(100),
+aud_location VARCHAR(100),
+aud_operation VARCHAR(100),
+aud_field_1 VARCHAR(400),
+aud_timestamp TIMESTAMP,
+aud_failure VARCHAR(100)
+)$$
+
+CREATE TABLE IF NOT EXISTS doc_store (
+id VARCHAR(265) PRIMARY KEY,
+persistentID VARCHAR(265) UNIQUE,
+created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
+disabled BOOLEAN default true
+)$$
+
+-- last_modified timestamp ON UPDATE CURRENT_TIMESTAMP,
+CREATE TABLE IF NOT EXISTS policy_store (
+ id INTEGER PRIMARY KEY AUTO_INCREMENT,
+ targetID BIGINT NOT NULL,
+ created TIMESTAMP,
+ creator INTEGER NOT NULL,
+ posix SMALLINT NOT NULL,
+ expire timestamp null,
+ enable timestamp not null,
+ iprange VARCHAR(200)
+)$$
+
+CREATE TABLE IF NOT EXISTS group_ref (
+ id INTEGER PRIMARY KEY AUTO_INCREMENT,
+ groupId VARCHAR(100) NOT NULL,
+ policyid INTEGER NOT NULL
+)$$
+
+
+CREATE TABLE IF NOT EXISTS group_store (
+ name VARCHAR(100) PRIMARY KEY,
+ description VARCHAR(200),
+ sym_use INTEGER DEFAULT -1,
+ export VARCHAR(30) DEFAULT NULL,
+ query_only VARCHAR(30) DEFAULT NULL,
+ licence INTEGER DEFAULT -1,
+ -- basically every resource we have is an academic resource, thus a non-commercial use is infered!
+ commercial BOOLEAN DEFAULT FALSE
+)$$
+
+CREATE TABLE IF NOT EXISTS group_users (
+ id INTEGER PRIMARY KEY AUTO_INCREMENT,
+ userID BIGINT NOT NULL,
+ groupId VARCHAR(100) NOT NULL,
+ admin BOOLEAN NOT NULL DEFAULT FALSE,
+ FOREIGN KEY (groupId)
+ REFERENCES groupStore (name) on delete cascade
+)$$
+
+CREATE TABLE IF NOT EXISTS param_store (
+ id INTEGER PRIMARY KEY AUTO_INCREMENT,
+ p_key VARCHAR(100) NOT NULL,
+ p_value VARCHAR(150) NOT NULL,
+ resource INTEGER DEFAULT -1,
+ pid INTEGER DEFAULT -1,
+ FOREIGN KEY (resource)
+ REFERENCES resource_store(id)
+ on delete cascade,
+ FOREIGN KEY (pid)
+ REFERENCES policy_store(id)
+ on delete cascade
+)$$
+
+CREATE TABLE IF NOT EXISTS param_map (
+ id INTEGER PRIMARY KEY AUTO_INCREMENT,
+ policyID INTEGER NOT NULL,
+ paramID INTEGER NOT NULL,
+ value VARCHAR(100) NOT NULL,
+ flag BOOLEAN NOT NULL DEFAULT FALSE,
+ FOREIGN KEY (paramID)
+ REFERENCES param_store (id),
+ FOREIGN KEY (policyID)
+ REFERENCES policy_store (id)
+)$$
+
+create or replace view policy_view as
+select
+ po.id as pid,
+ po.targetID as id,
+ rs.persistentID as persistentID,
+ rs.name as name,
+ rs.type as type,
+ c.groupId as groupId,
+ po.posix as perm,
+ po.creator as creator,
+ po.expire as expire,
+ po.enable as enable,
+ po.iprange as iprange
+from
+policy_store as po
+inner join
+group_ref as c ON c.policyid = po.id
+inner join
+resource_store as rs ON rs.id = po.targetid
+union all select
+ - 1 as pid,
+ rs.id as id,
+ rs.persistentID as persistentID,
+ rs.name as name,
+ type as type,
+ 'self' as groupId,
+ 127 as perm,
+ creator,
+ NULL as expire,
+ rs.created as enable,
+ null as iprange
+ from
+ resource_store as rs$$
+
+
+
+-- oauth2 db tables
+create table oauth2_client (
+id INTEGER PRIMARY KEY AUTO_INCREMENT,
+client_id VARCHAR(100),
+client_secret VARCHAR(200),
+redirect_uri VARCHAR(250),
+client_type VARCHAR(200),
+native BOOLEAN DEFAULT FALSE,
+url VARCHAR(200) UNIQUE
+)$$
+
+create table oauth2_auth_codes (
+id INTEGER PRIMARY KEY AUTO_INCREMENT,
+client_id VARCHAR(100),
+auth_code VARCHAR(250),
+status INTEGER DEFAULT 1,
+scopes VARCHAR (150),
+created TIMESTAMP DEFAULT CURRENT_TIMESTAMP
+)$$
+
+create table oauth2_client_authorization (
+id INTEGER PRIMARY KEY AUTO_INCREMENT,
+client_id INTEGER,
+user_id INTEGER,
+-- define scopes?! --
+FOREIGN KEY (client_id) REFERENCES oauth2_client(client_id)
+)$$
+
+-- status 1 = valid, 0 = revoked
+create table oauth2_access_token (
+id INTEGER PRIMARY KEY AUTO_INCREMENT,
+access_token VARCHAR(300),
+auth_code VARCHAR(250),
+userId INTEGER,
+status INTEGER DEFAULT 1,
+expiration TIMESTAMP,
+FOREIGN KEY (userId)
+REFERENCES korap_users(id)
+)$$
+
+
+
+-- indices
+create trigger delete_policy after delete on resource_store
+for each row delete from policy_store where targetID=OLD.id$$
+
+CREATE TRIGGER insert_data AFTER INSERT ON resource_store FOR EACH ROW BEGIN
+ INSERT INTO resource_tree (parentID, childID, depth, name_path)
+ VALUES (NEW.id, NEW.id, 0, NEW.name);
+ INSERT INTO resource_tree (parentID, childID, depth, name_path)
+ SELECT parentID, NEW.id, depth + 1, concat(name_path,"/",NEW.name) FROM resource_tree WHERE childID = NEW.parentID;
+END$$
+
+CREATE INDEX group_index ON group_users(userid)$$
+CREATE INDEX policy_index ON group_ref(policyid)$$
+CREATE UNIQUE INDEX resource_tree_index on resource_tree (parentID, depth, childID)$$
+CREATE UNIQUE INDEX para_unique ON param_store (p_key, p_value)$$
+
+
+
+
+
+
+
+
+
+
diff --git a/src/main/resources/db/sqlite/V1__Initial_version.sql b/src/main/resources/db/sqlite/V1__Initial_version.sql
new file mode 100644
index 0000000..fbe252d
--- /dev/null
+++ b/src/main/resources/db/sqlite/V1__Initial_version.sql
@@ -0,0 +1,374 @@
+CREATE TABLE IF NOT EXISTS korap_users (
+id INTEGER PRIMARY KEY AUTOINCREMENT,
+username VARCHAR(150) NOT NULL UNIQUE,
+password VARCHAR(100) NOT NULL,
+accountLock boolean NOT NULL,
+accountCreation TIMESTAMP NOT NULL,
+-- deprecate this
+type INTEGER DEFAULT 0,
+uri_fragment VARCHAR(100),
+uri_expiration TIMESTAMP,
+accountLink VARCHAR(100)
+);
+
+CREATE TABLE IF NOT EXISTS shib_users (
+id INTEGER PRIMARY KEY AUTOINCREMENT,
+username VARCHAR(150) NOT NULL UNIQUE,
+accountCreation TIMESTAMP NOT NULL,
+type INTEGER DEFAULT 1,
+loginSuccess INTEGER,
+loginFailed INTEGER,
+accountLink VARCHAR(100)
+);
+
+CREATE TABLE IF NOT EXISTS user_details (
+id INTEGER PRIMARY KEY AUTOINCREMENT,
+user_id INTEGER NOT NULL,
+firstName VARCHAR(100),
+lastName VARCHAR(100),
+gender VARCHAR(100),
+phone VARCHAR(100),
+institution VARCHAR(100),
+email VARCHAR(100),
+address VARCHAR(100),
+country VARCHAR(100),
+privateUsage BOOLEAN,
+foreign key (user_id)
+references korap_users (id)
+on delete cascade
+);
+
+CREATE TABLE IF NOT EXISTS user_settings (
+id INTEGER PRIMARY KEY AUTOINCREMENT,
+user_id INTEGER NOT NULL,
+fileNameForExport VARCHAR(100),
+leftContextItemForExport VARCHAR(100),
+leftContextSizeForExport INTEGER,
+locale VARCHAR(100),
+leftContextItem VARCHAR(100),
+leftContextSize INTEGER,
+rightContextItem VARCHAR(100),
+rightContextItemForExport VARCHAR(100),
+rightContextSize INTEGER,
+rightContextSizeForExport INTEGER,
+selectedCollection VARCHAR(100),
+queryLanguage VARCHAR(100),
+pageLength INTEGER,
+metadataQueryExpertModus BOOLEAN,
+POSFoundry VARCHAR(100),
+lemmaFoundry VARCHAR(100),
+constFoundry VARCHAR(100),
+relFoundry VARCHAR(100),
+collectData BOOLEAN,
+foreign key (user_id)
+references korap_users (id)
+on delete cascade
+);
+
+
+CREATE TABLE IF NOT EXISTS user_queries (
+id INTEGER PRIMARY KEY,
+queryLanguage VARCHAR(100),
+name VARCHAR(100),
+query VARCHAR(200),
+description VARCHAR(150),
+foreign key (id)
+references resource_store (id)
+on delete cascade
+);
+
+CREATE TABLE IF NOT EXISTS coll_store (
+id INTEGER PRIMARY KEY AUTOINCREMENT,
+persistent_id VARCHAR(150) UNIQUE,
+name VARCHAR(150),
+description VARCHAR(200),
+query VARCHAR(500),
+created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
+user_id INTEGER,
+foreign key(user_id)
+references korap_users(id)
+on delete cascade
+);
+
+CREATE TABLE IF NOT EXISTS audit_records (
+aud_id INTEGER PRIMARY KEY AUTOINCREMENT,
+aud_category VARCHAR(100),
+aud_target VARCHAR(100),
+aud_user VARCHAR(100),
+aud_location VARCHAR(100),
+aud_field_1 VARCHAR(400),
+aud_args VARCHAR(400),
+aud_timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
+aud_status VARCHAR(100)
+);
+
+-- deprecated
+CREATE TABLE IF NOT EXISTS match_info (
+id INTEGER PRIMARY KEY AUTOINCREMENT,
+userid BIGINT NOT NULL,
+matchInfo VARCHAR(100)
+);
+
+
+CREATE TABLE IF NOT EXISTS policy_store (
+id INTEGER PRIMARY KEY AUTOINCREMENT,
+target_id BIGINT NOT NULL,
+created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
+creator INTEGER NOT NULL,
+posix SMALLINT NOT NULL,
+expire timestamp,
+enable timestamp NOT NULL,
+iprange varchar(200)
+);
+
+-- send disabled documents per corpus to backend, so they can be excluded from searching!
+CREATE TABLE IF NOT EXISTS doc_store (
+id INTEGER PRIMARY KEY AUTOINCREMENT,
+persistent_id VARCHAR(265) UNIQUE,
+created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
+disabled BOOLEAN default true
+);
+
+CREATE TABLE IF NOT EXISTS group_ref (
+id INTEGER PRIMARY KEY AUTOINCREMENT,
+group_id VARCHAR(100) NOT NULL,
+policy_id INTEGER NOT NULL
+);
+
+-- question: grouping of users or grouping of resources required?
+CREATE TABLE IF NOT EXISTS group_store (
+name VARCHAR(100) PRIMARY KEY,
+description VARCHAR(200),
+sym_use INTEGER DEFAULT -1,
+export VARCHAR(30) DEFAULT NULL,
+query_only VARCHAR(30) DEFAULT NULL,
+licence INTEGER DEFAULT -1,
+-- basically every resource we have is an academic resource, thus a non-commercial use is infered!
+commercial BOOLEAN DEFAULT FALSE
+);
+
+CREATE TABLE IF NOT EXISTS group_users (
+id INTEGER PRIMARY KEY AUTOINCREMENT,
+user_id INTEGER NOT NULL,
+group_id VARCHAR(100) NOT NULL,
+admin BOOLEAN NOT NULL DEFAULT FALSE,
+FOREIGN KEY (user_id)
+REFERENCES korap_users(id)
+on delete cascade,
+FOREIGN KEY (group_id)
+REFERENCES group_store (name)
+on delete cascade
+);
+
+
+CREATE TABLE IF NOT EXISTS param_store (
+id INTEGER PRIMARY KEY AUTOINCREMENT,
+p_key VARCHAR(150) NOT NULL,
+p_value VARCHAR(200) NOT NULL,
+resource INTEGER NOT NULL DEFAULT -1,
+pid INTEGER NOT NULL DEFAULT -1,
+FOREIGN KEY (resource)
+REFERENCES resource_store(id)
+on delete cascade,
+FOREIGN KEY (pid)
+REFERENCES policy_store(id)
+on delete cascade
+);
+
+CREATE TABLE IF NOT EXISTS param_map (
+id INTEGER PRIMARY KEY AUTOINCREMENT,
+policyId INTEGER NOT NULL,
+paramId INTEGER NOT NULL,
+value VARCHAR(100) NOT NULL,
+flag BOOLEAN NOT NULL DEFAULT FALSE,
+FOREIGN KEY (paramId)
+ REFERENCES param_store (id),
+FOREIGN KEY (policyId)
+ REFERENCES policy_store (id)
+);
+
+CREATE TABLE IF NOT EXISTS resource_store (
+id INTEGER PRIMARY KEY AUTOINCREMENT,
+persistent_id VARCHAR(100) NOT NULL UNIQUE,
+name VARCHAR(100),
+description VARCHAR(300),
+parent_id Integer unsigned null,
+created timestamp default current_timestamp,
+type INTEGER NOT NULL,
+creator INTEGER NOT NULL
+);
+
+
+CREATE TABLE IF NOT EXISTS resource_tree (
+parent_id INTEGER,
+child_id INTEGER,
+depth INTEGER,
+name_path VARCHAR(250),
+PRIMARY KEY (parent_id, child_id),
+foreign key (parent_id)
+references resource_store (id)
+on delete cascade,
+foreign key (child_id)
+references resource_store (id)
+on delete cascade
+);
+
+
+-- todo: refactor native to confidential, add column application name
+-- remove id and make client_id primary key
+create table IF NOT EXISTS oauth2_client (
+id INTEGER PRIMARY KEY AUTOINCREMENT,
+client_id VARCHAR(100) NOT NULL,
+client_secret VARCHAR(200) NOT NULL,
+redirect_uri VARCHAR(250) NOT NULL,
+client_type VARCHAR(200),
+is_confidential BOOLEAN DEFAULT FALSE,
+url VARCHAR(200) UNIQUE
+);
+
+-- status 1 = valid, 0 = revoked, -1 = disabled
+create table IF NOT EXISTS oauth2_access_token (
+id INTEGER PRIMARY KEY AUTOINCREMENT,
+access_token VARCHAR(300),
+auth_code VARCHAR(250),
+client_id VARCHAR(100),
+user_id INTEGER,
+-- make boolean --
+status INTEGER DEFAULT 1,
+-- in case of code authorization, should match auth code scopes!
+-- use scopes for levelaccess descriptor level[rw],level[r]
+scopes VARCHAR(350),
+expiration TIMESTAMP,
+FOREIGN KEY (user_id)
+REFERENCES korap_users(id)
+);
+
+
+-- a bit confusing. 1. creator is policy creator, 2. creator is resource creator --> different implications
+-- insert resource data from resource_store alltogether, so i dont have to retrieve anything from there?!
+create view if not exists policy_view as
+select
+ po.id as pid,
+ po.target_id as id,
+ rs.persistent_id as persistent_id,
+ rs.name as name,
+ rs.type as type,
+ c.group_id as group_id,
+ po.posix as perm,
+ po.creator as creator,
+ po.expire as expire,
+ po.enable as enable,
+ po.iprange as iprange
+from policy_store as po
+inner join
+group_ref as c ON c.policy_id = po.id
+inner join
+resource_store as rs ON rs.id = po.target_id
+union all select
+ - 1 as pid,
+ rs.id as id,
+ rs.persistent_id as persistent_id,
+ rs.name as name,
+ type as type,
+ 'self' as group_id,
+ 127 as perm,
+ creator,
+ NULL as expire,
+ rs.created as enable,
+ null as iprange
+ from
+ resource_store as rs;
+
+-- deletes a group if the group has no longer members!
+create trigger if not exists group_ref_del after delete on group_ref
+when (select count(*) from group_ref where groupId=OLD.group_id) = 0
+begin delete from groupolicy_store where name=OLD.group_id; end;
+
+ -- create trigger relCr after insert on resource_store
+ -- when (select count(*) from r_tree where parent_id==NEW.id and
+ -- child_id==NEW.id) == 0
+ -- BEGIN
+ -- insert into r_tree (parent_id, child_id, depth)
+ -- VALUES (NEW.id, NEW.id, 0);
+ -- END;
+
+ -- 1. CONCAT(NEW.name,"/")
+ -- 2. CONCAT(name_path, NEW.name, "/")
+
+CREATE TRIGGER IF NOT EXISTS insert_data
+AFTER INSERT ON resource_store
+FOR EACH ROW BEGIN
+INSERT INTO resource_tree (parent_id, child_id, depth, name_path)
+VALUES (NEW.id, NEW.id, 0, NEW.name);
+
+INSERT INTO resource_tree (parent_id, child_id, depth, name_path)
+SELECT parent_id, NEW.id, depth + 1, name_path || "/" || NEW.name FROM resource_tree
+WHERE child_id = NEW.parent_id;
+END;
+
+create trigger if not exists delete_policy after delete on resource_store
+begin delete from policy_store where target_id=OLD.id; end;
+
+ -- 1. requirement: delete hierarchical from resource_store and r_tree -- done!
+ -- 2. todo: subsequently delete from resourcedao extensions if child of deleted resource!
+create trigger if not exists del_tree after delete on resource_store
+begin delete from resource_store where id in (select rs.id from resource_store as rs
+inner join resource_tree as rt on rt.child_id=rs.id where rt.parent_id=OLD.id);
+delete from resource_tree where parent_id=OLD.id; end;
+
+-- mysql on delete cascade todo: test
+create trigger if not exists del_user delete on korap_users
+begin
+ delete from user_settings where user_id=OLD.id;
+ delete from user_details where user_id=OLD.id;
+ delete from group_users where user_id=OLD.id;
+end;
+
+-- indices
+-- test unique index constraints
+create index group_index on group_users(user_id);
+create index policy_index on group_ref(policy_id);
+create index policy_target on policy_store(target_id);
+create unique index r_tree_index on resource_tree (parent_id, depth, child_id);
+create unique index para_unique on param_store (p_key, p_value);
+create unique index conditions on group_ref (policy_id, group_id);
+create unique index groups on group_users (user_id, group_id);
+
+
+-- deprecated
+-- flagr is a reference to the applicable conditions: export, licence
+create table if not exists policy_store2 (
+id integer primary key autoincrement,
+target_id bigint not null,
+creator bigint not null,
+perm integer default -1,
+enable boolean default true,
+master INTEGER UNIQUE default NULL,
+expire timestamp default null,
+iprange varchar(200),
+flagr integer,
+params integer,
+baseline boolean default false,
+FOREIGN KEY (master)
+ REFERENCES policy_store2(id),
+FOREIGN KEY (flagr)
+ REFERENCES flag_store (id),
+FOREIGN KEY (params)
+ REFERENCES param_store (id)
+);
+
+-- grouping is matched with a view where the user and groups are listed together
+create table if not exists flag_store (
+id integer primary key autoincrement,
+export boolean default true,
+sym_use integer default -1,
+grouping varchar(150),
+FOREIGN KEY (grouping)
+ REFERENCES groupings (grouping)
+);
+
+-- todo: ??!
+-- haveing the username as grouping only works with the unique identifier at username
+create view if not exists groupings_view
+as select id as user_id, username as grouping from korap_users
+union all select user_id, group_id as grouping from group_users;
diff --git a/src/main/resources/default-config.xml b/src/main/resources/default-config.xml
index 12dce87..023f671 100644
--- a/src/main/resources/default-config.xml
+++ b/src/main/resources/default-config.xml
@@ -1,25 +1,197 @@
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
+ xmlns:p="http://www.springframework.org/schema/p"
xmlns:util="http://www.springframework.org/schema/util"
+ xmlns:aop="http://www.springframework.org/schema/aop"
+ xmlns:tx="http://www.springframework.org/schema/tx"
xmlns="http://www.springframework.org/schema/beans"
+ xmlns:cache="http://www.springframework.org/schema/cache"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-4.0.xsd
+ http://www.springframework.org/schema/tx
+ http://www.springframework.org/schema/tx/spring-tx-4.0.xsd
+ http://www.springframework.org/schema/aop
+ http://www.springframework.org/schema/aop/spring-aop-4.0.xsd
+ http://www.springframework.org/schema/cache
+ http://www.springframework.org/schema/cache/spring-cache-4.0.xsd
+
+
http://www.springframework.org/schema/util
http://www.springframework.org/schema/util/spring-util-4.0.xsd">
-
+ <cache:annotation-driven/>
<util:properties id="props" location="classpath:kustvakt.conf"/>
- <bean id="kustvakt_auditing"
- class="de.ids_mannheim.korap.interfaces.defaults.DefaultAuditing">
+ <bean id="jdbc_props"
+ class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
+ <property name="locations" value="classpath:jdbc.properties"/>
</bean>
+ <bean id='cacheManager'
+ class='org.springframework.cache.ehcache.EhCacheCacheManager'
+ p:cacheManager-ref='ehcache'/>
+
+ <bean id='ehcache'
+ class='org.springframework.cache.ehcache.EhCacheManagerFactoryBean'
+ p:configLocation='classpath:ehcache.xml'
+ p:shared='true'/>
+
+ <!-- props are injected from default-config.xml -->
<bean id="kustvakt_config"
- class="de.ids_mannheim.korap.config.KustvaktConfiguration">
+ class="de.ids_mannheim.korap.ext.config.ExtConfiguration">
<property name="properties" ref="props"/>
</bean>
+ <bean id="dataSource"
+ class="org.springframework.jdbc.datasource.SingleConnectionDataSource"
+ lazy-init="true">
+ <property name="driverClassName" value="${jdbc.driverClassName}"/>
+ <property name="url" value="${jdbc.url}"/>
+ <property name="username" value="${jdbc.username}"/>
+ <property name="password" value="${jdbc.password}"/>
+ <!-- relevant for single connection datasource and sqlite -->
+ <property name="suppressClose">
+ <value>true</value>
+ </property>
+ </bean>
+
+ <!-- to configure database for sqlite, mysql, etc. migrations -->
+ <bean id="flyway" class="org.flywaydb.core.Flyway" init-method="migrate">
+ <property name="baselineOnMigrate" value="false"/>
+ <property name="locations"
+ value="classpath:${jdbc.schemaPath}"/>
+ <property name="dataSource" ref="dataSource"/>
+ </bean>
+
+ <bean id="kustvakt_db"
+ class="de.ids_mannheim.korap.ext.database.JDBCClient">
+ <constructor-arg index="0" ref="dataSource"/>
+ <!-- deprecated property -->
+ <property name="database" value="${jdbc.database}"/>
+ </bean>
+
+ <bean id="kustvakt_auditing"
+ class="de.ids_mannheim.korap.ext.database.JDBCAuditing">
+ <constructor-arg ref="kustvakt_db"/>
+ </bean>
+
+ <bean id="kustvakt_userdb"
+ class="de.ids_mannheim.korap.ext.database.EntityDao">
+ <constructor-arg ref="kustvakt_db"/>
+ </bean>
+
+ <bean id="collectionProvider"
+ class="de.ids_mannheim.korap.ext.database.CollectionDao">
+ <constructor-arg ref="kustvakt_db"/>
+ </bean>
+
+ <bean id="resourceProvider"
+ class="de.ids_mannheim.korap.ext.database.ResourceDao">
+ <constructor-arg ref="kustvakt_db"/>
+ </bean>
+
+ <bean id="kustvakt_policies"
+ class="de.ids_mannheim.korap.ext.security.dataAccess.PolicyDao">
+ <constructor-arg ref="kustvakt_db"/>
+ </bean>
+
<bean name="kustvakt_encryption"
- class="de.ids_mannheim.korap.interfaces.defaults.DefaultEncryption">
+ class="de.ids_mannheim.korap.ext.security.encryption.KorAPEncryption">
+ <constructor-arg ref="kustvakt_config"/>
+ </bean>
+
+ <bean id="resourceHandler"
+ class="de.ids_mannheim.korap.ext.security.accessControl.ResourceHandler"/>
+
+
+ <!-- authentication providers to use -->
+
+ <bean id="api_auth"
+ class="de.ids_mannheim.korap.ext.security.authentication.APIAuthentication">
+ <constructor-arg
+ type="de.ids_mannheim.korap.config.KustvaktConfiguration"
+ ref="kustvakt_config"/>
+ </bean>
+
+ <bean id="openid_auth"
+ class="de.ids_mannheim.korap.ext.security.authentication.OpenIDconnectAuthentication">
+ <constructor-arg
+ type="de.ids_mannheim.korap.config.KustvaktConfiguration"
+ ref="kustvakt_config"/>
+ <constructor-arg
+ type="de.ids_mannheim.korap.interfaces.PersistenceClient"
+ ref="kustvakt_db"/>
+ </bean>
+
+ <bean id="basic_auth"
+ class="de.ids_mannheim.korap.ext.security.authentication.BasicHttpAuth"/>
+
+
+ <bean id="session_auth"
+ class="de.ids_mannheim.korap.ext.security.authentication.SessionAuthentication">
+ <constructor-arg
+ type="de.ids_mannheim.korap.config.KustvaktConfiguration"
+ ref="kustvakt_config"/>
+ <constructor-arg
+ type="de.ids_mannheim.korap.interfaces.EncryptionIface"
+ ref="kustvakt_encryption"/>
+ </bean>
+
+ <util:list id="auth_providers"
+ value-type="de.ids_mannheim.korap.interfaces.AuthenticationIface">
+ <ref bean="basic_auth"/>
+ <ref bean="session_auth"/>
+ <ref bean="api_auth"/>
+ <ref bean="openid_auth"/>
+ </util:list>
+
+ <!-- specify type for constructor argument -->
+ <bean id="kustvakt_authenticationmanager"
+ class="de.ids_mannheim.korap.ext.security.authentication.KustvaktAuthenticationManager">
+ <constructor-arg
+ type="de.ids_mannheim.korap.interfaces.EntityHandlerIface"
+ ref="kustvakt_userdb"/>
+ <constructor-arg type="de.ids_mannheim.korap.interfaces.EncryptionIface"
+ ref="kustvakt_encryption"/>
+ <constructor-arg ref="kustvakt_config"/>
+ <constructor-arg type="de.ids_mannheim.korap.interfaces.AuditingIface"
+ ref="kustvakt_auditing"/>
+ <!-- inject authentication providers to use -->
+ <property name="providers" ref="auth_providers"/>
+ </bean>
+
+ <!--
+ <util:list id="providers"
+ value-type="de.ids_mannheim.korap.ext.interfaces.ResourceOperationIface">
+ <ref bean="resourceProvider"/>
+ <ref bean="collectionProvider"/>
+ </util:list>
+-->
+
+ <!-- todo: if db interfaces not loaded via spring, does transaction even work then? -->
+ <!-- the transactional advice (i.e. what 'happens'; see the <aop:advisor/> bean below) -->
+ <tx:advice id="txAdvice" transaction-manager="txManager">
+ <!-- the transactional semantics... -->
+ <tx:attributes>
+ <!-- all methods starting with 'get' are read-only -->
+ <tx:method name="get*" read-only="true"
+ rollback-for="KorAPException"/>
+ <!-- other methods use the default transaction settings (see below) -->
+ <tx:method name="*" rollback-for="KorAPException"/>
+ </tx:attributes>
+ </tx:advice>
+
+ <!-- ensure that the above transactional advice runs for any execution
+ of an operation defined by the service interface -->
+ <aop:config>
+ <aop:pointcut id="service"
+ expression="execution(* de.ids_mannheim.korap.interfaces.*.*(..))"/>
+ <aop:advisor advice-ref="txAdvice" pointcut-ref="service"/>
+ </aop:config>
+
+ <!-- similarly, don't forget the PlatformTransactionManager -->
+ <bean id="txManager"
+ class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
+ <property name="dataSource" ref="dataSource"/>
</bean>
</beans>
\ No newline at end of file
diff --git a/src/main/resources/ehcache.xml b/src/main/resources/ehcache.xml
new file mode 100644
index 0000000..3265324
--- /dev/null
+++ b/src/main/resources/ehcache.xml
@@ -0,0 +1,32 @@
+<ehcache xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance'
+ xsi:noNamespaceSchemaLocation='http://ehcache.org/ehcache.xsd'>
+ <defaultCache eternal='true' overflowToDisk='false'/>
+ <!--maxBytesLocalHeap="200M"-->
+ <cache name='users'
+ timeToIdleSeconds="172800"
+ eternal='false'
+ memoryStoreEvictionPolicy="LRU"
+ maxEntriesLocalHeap="50"
+ overflowToDisk='false'/>
+ <cache name='id_tokens'
+ timeToIdleSeconds="172800"
+ eternal='false'
+ memoryStoreEvictionPolicy="LRU"
+ maxEntriesLocalHeap="50"
+ overflowToDisk='false'/>
+ <cache name='auth_codes'
+ timeToIdleSeconds="600"
+ eternal='false'
+ memoryStoreEvictionPolicy="LRU"
+ maxEntriesLocalHeap="100"
+ overflowToDisk='false'/>
+ <cache name='resources'
+ eternal='false'
+ overflowToDisk='false'
+ maxEntriesLocalHeap="60"
+ timeToIdleSeconds="172800"
+ diskSpoolBufferSizeMB="30"
+ diskExpiryThreadIntervalSeconds="120"
+ memoryStoreEvictionPolicy="LRU"
+ statistics="false"/>
+</ehcache>
diff --git a/src/main/resources/light-config.xml b/src/main/resources/light-config.xml
new file mode 100644
index 0000000..12dce87
--- /dev/null
+++ b/src/main/resources/light-config.xml
@@ -0,0 +1,25 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<beans xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
+ xmlns:util="http://www.springframework.org/schema/util"
+ xmlns="http://www.springframework.org/schema/beans"
+ xsi:schemaLocation="http://www.springframework.org/schema/beans
+ http://www.springframework.org/schema/beans/spring-beans-4.0.xsd
+ http://www.springframework.org/schema/util
+ http://www.springframework.org/schema/util/spring-util-4.0.xsd">
+
+
+ <util:properties id="props" location="classpath:kustvakt.conf"/>
+
+ <bean id="kustvakt_auditing"
+ class="de.ids_mannheim.korap.interfaces.defaults.DefaultAuditing">
+ </bean>
+
+ <bean id="kustvakt_config"
+ class="de.ids_mannheim.korap.config.KustvaktConfiguration">
+ <property name="properties" ref="props"/>
+ </bean>
+
+ <bean name="kustvakt_encryption"
+ class="de.ids_mannheim.korap.interfaces.defaults.DefaultEncryption">
+ </bean>
+</beans>
\ No newline at end of file
diff --git a/src/main/resources/validation.properties b/src/main/resources/validation.properties
new file mode 100755
index 0000000..9a0e0e5
--- /dev/null
+++ b/src/main/resources/validation.properties
@@ -0,0 +1,30 @@
+# The ESAPI validator does many security checks on input, such as canonicalization
+# and whitelist validation. Note that all of these validation rules are applied *after*
+# canonicalization. Double-encoded characters (even with different encodings involved,
+# are never allowed.
+#
+# To use:
+#
+# First set up a pattern below. You can choose any name you want, prefixed by the word
+# "Validation." For example:
+# Validation.Email=^[A-Za-z0-9._%-]+@[A-Za-z0-9.-]+\\.[a-zA-Z]{2,4}$
+#
+# Then you can validate in your code against the pattern like this:
+# ESAPI.validator().isValidInput("User Email", input, "Email", maxLength, allowNull);
+# Where maxLength and allowNull are set for you needs, respectively.
+#
+# But note, when you use boolean variants of validation functions, you lose critical
+# canonicalization. It is preferable to use the "get" methods (which throw exceptions) and
+# and use the returned user input which is in canonical form. Consider the following:
+#
+# try {
+# someObject.setEmail(ESAPI.validator().getValidInput("User Email", input, "Email", maxLength, allowNull));
+#
+#Validator.SafeString=^[.;:\\-\\p{Alnum}\\p{Space}]{0,1024}$
+Validator.SafeString=^[.;:,=\\*\/\/_()\\-0-9\\p{L}\\p{Space}]{0,1024}$
+Validator.Email=^[A-Za-z0-9._%'-]+@[A-Za-z0-9.-]+\\.[a-zA-Z]{2,4}$
+Validator.IPAddress=^(?:(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\\.){3}(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)$
+Validator.URL=^(ht|f)tp(s?)\\:\\/\\/[0-9a-zA-Z]([-.\\w]*[0-9a-zA-Z])*(:(0-9)*)*(\\/?)([a-zA-Z0-9\\-\\.\\?\\,\\:\\'\\/\\\\\\+=&%\\$#_]*)?$
+Validator.CreditCard=^(\\d{4}[- ]?){3}\\d{4}$
+Validator.SSN=^(?!000)([0-6]\\d{2}|7([0-6]\\d|7[012]))([ -]?)(?!00)\\d\\d\\3(?!0000)\\d{4}$
+Validator.Password=((?=.*\\d)(?=.*[a-z])(?=.*[A-Z]).{8,20})