running version update; fixed tests all over; created more extensive rest tests
diff --git a/src/main/resources/db/mysql/V0.1__userdatabase.sql b/src/main/resources/db/mysql/V0.1__userdatabase.sql
new file mode 100644
index 0000000..6444651
--- /dev/null
+++ b/src/main/resources/db/mysql/V0.1__userdatabase.sql
@@ -0,0 +1,104 @@
+-- rename all columns in new way!
+CREATE TABLE IF NOT EXISTS korap_users (
+ id INTEGER PRIMARY KEY AUTO_INCREMENT,
+ username VARCHAR(100) NOT NULL UNIQUE,
+ password VARCHAR(100) NOT NULL,
+ account_lock boolean NOT NULL,
+ account_creation BIGINT NOT NULL,
+ type INTEGER DEFAULT 0,
+ uri_fragment VARCHAR(100),
+ uri_expiration BIGINT,
+ loginSuccess INTEGER,
+ loginFailed INTEGER,
+ account_link VARCHAR(100)
+);
+
+CREATE TABLE IF NOT EXISTS shib_users (
+ id INTEGER PRIMARY KEY AUTO_INCREMENT,
+ username VARCHAR(100) NOT NULL UNIQUE,
+ account_creation BIGINT NOT NULL,
+ type INTEGER DEFAULT 1,
+ loginSuccess INTEGER,
+ loginFailed INTEGER,
+ account_link VARCHAR(100)
+);
+
+CREATE TABLE IF NOT EXISTS user_details (
+ Id INTEGER PRIMARY KEY AUTO_INCREMENT,
+ user_id 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 (user_id)
+ references korap_users (id)
+ on delete cascade
+);
+
+CREATE TABLE IF NOT EXISTS user_settings (
+ Id INTEGER PRIMARY KEY AUTO_INCREMENT,
+ user_id 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 (user_id)
+ references korap_users (id)
+ on delete cascade
+);
+
+-- deprecated
+CREATE OR REPLACE VIEW allusers AS
+ SELECT
+ id,
+ username,
+ password,
+ account_lock,
+ account_creation,
+ type,
+ uri_fragment,
+ uri_expiration,
+ loginSuccess,
+ loginFailed,
+ account_link
+ from
+ korap_users
+ UNION ALL SELECT
+ id,
+ username,
+ NULL as password,
+ NULL as account_lock,
+ account_creation,
+ type,
+ NULL as uri_fragment,
+ NULL as uri_expiration,
+ loginSuccess,
+ loginFailed,
+ account_link
+ from
+ shib_users;
diff --git a/src/main/resources/db/mysql/V0.2__resourcesdatabase.sql b/src/main/resources/db/mysql/V0.2__resourcesdatabase.sql
new file mode 100644
index 0000000..01b8c26
--- /dev/null
+++ b/src/main/resources/db/mysql/V0.2__resourcesdatabase.sql
@@ -0,0 +1,60 @@
+-- why unsigned?
+CREATE TABLE IF NOT EXISTS resource_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 BIGINT NOT NULL,
+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
+);
+
+
+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,
+ query VARCHAR(500),
+ user_id INTEGER,
+ created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
+ -- is foreign key constraint valid after refactoring?
+ foreign key (id) references resource_store(id)
+ on delete cascade);
+
+
+CREATE TABLE IF NOT EXISTS matchInfo (
+ id INTEGER PRIMARY KEY AUTO_INCREMENT,
+ user_id BIGINT NOT NULL,
+ matchInfo VARCHAR(100)
+);
+
+CREATE TABLE IF NOT EXISTS doc_store (
+ id INTEGER PRIMARY KEY AUTO_INCREMENT,
+ persistent_id VARCHAR(100) UNIQUE,
+ created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
+ disabled BOOLEAN default true
+);
\ No newline at end of file
diff --git a/src/main/resources/db/mysql/V0.3__securitydatabase.sql b/src/main/resources/db/mysql/V0.3__securitydatabase.sql
new file mode 100644
index 0000000..b7d826b
--- /dev/null
+++ b/src/main/resources/db/mysql/V0.3__securitydatabase.sql
@@ -0,0 +1,135 @@
+-- last_modified timestamp ON UPDATE CURRENT_TIMESTAMP,
+CREATE TABLE IF NOT EXISTS policy_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 group_ref (
+ id INTEGER PRIMARY KEY AUTO_INCREMENT,
+ group_id VARCHAR(100) NOT NULL,
+ policy_id 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,
+ user_id BIGINT NOT NULL,
+ -- make integer
+ group_id VARCHAR(100) NOT NULL,
+ admin BOOLEAN NOT NULL DEFAULT FALSE,
+ FOREIGN KEY (group_id)
+ REFERENCES group_store (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,
+ policy_id INTEGER NOT NULL,
+ param_id INTEGER NOT NULL,
+ value VARCHAR(100) NOT NULL,
+ flag BOOLEAN NOT NULL DEFAULT FALSE,
+ FOREIGN KEY (param_id)
+ REFERENCES param_store (id),
+ FOREIGN KEY (policy_id)
+ REFERENCES policy_store (id)
+);
+
+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 group_id,
+ 127 as perm,
+ creator,
+ NULL as expire,
+ rs.created as enable,
+ null as iprange
+ from
+ resource_store as rs;
+
+
+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)
+);
+
+
+-- indices
+create trigger delete_policy after delete on resource_store
+for each row delete from policy_store where target_id=OLD.id;
+
+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 ;
+
+-- todo: are this automatically adapted when refactoring?
+CREATE INDEX group_index ON group_users(user_id);
+CREATE INDEX policy_index ON group_ref(policy_id);
+CREATE UNIQUE INDEX resource_tree_index ON resource_tree (parent_id, depth, child_id);
+CREATE UNIQUE INDEX param_unique ON param_store (p_key, p_value);
+
+-- foreign key constraints
diff --git a/src/main/resources/db/mysql/V1.0__initial_version.sql b/src/main/resources/db/mysql/V1.0__initial_version.sql
deleted file mode 100644
index 8e4c17b..0000000
--- a/src/main/resources/db/mysql/V1.0__initial_version.sql
+++ /dev/null
@@ -1,326 +0,0 @@
-
--- 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.2__oauth2_tables_mysql.sql b/src/main/resources/db/mysql/V1.2__oauth2_tables_mysql.sql
index 227d660..fe75267 100644
--- a/src/main/resources/db/mysql/V1.2__oauth2_tables_mysql.sql
+++ b/src/main/resources/db/mysql/V1.2__oauth2_tables_mysql.sql
@@ -1,6 +1,6 @@
-- oauth2 db tables
-create table oauth2_client (
+create table if not exists oauth2_client (
client_id VARCHAR(100) UNIQUE PRIMARY KEY,
client_secret VARCHAR(200),
redirect_uri VARCHAR(250),
@@ -11,7 +11,7 @@
-- status 1 = valid, 0 = revoked, -1 = disabled
-create table oauth2_access_token (
+create table if not exists oauth2_access_token (
id INTEGER PRIMARY KEY AUTO_INCREMENT,
access_token VARCHAR(300),
auth_code VARCHAR(250),
@@ -33,7 +33,7 @@
-- also scopes?
-create table oauth2_refresh_token (
+create table if not exists oauth2_refresh_token (
id INTEGER PRIMARY KEY AUTO_INCREMENT,
client_id VARCHAR(100),
user_id INTEGER,
diff --git a/src/main/resources/db/mysql/initial_version.sql b/src/main/resources/db/mysql/initial_version.sql
new file mode 100644
index 0000000..3bff5cc
--- /dev/null
+++ b/src/main/resources/db/mysql/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),
+ -- is foreign key constraint valid after refactoring?
+ 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_store (
+-- id INTEGER PRIMARY KEY AUTO_INCREMENT,
+-- persistent_id VARCHAR(100) UNIQUE,
+-- created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
+-- disabled BOOLEAN default true
+--);
+
+-- 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/mysql_refactoring.sql
similarity index 77%
rename from src/main/resources/db/mysql/V1.1__mysql_refactoring.sql
rename to src/main/resources/db/mysql/mysql_refactoring.sql
index 4cc3b21..9b7cd6c 100644
--- a/src/main/resources/db/mysql/V1.1__mysql_refactoring.sql
+++ b/src/main/resources/db/mysql/mysql_refactoring.sql
@@ -5,6 +5,7 @@
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;
@@ -13,6 +14,19 @@
alter table korap_users change column URI_PASS_Fragment uri_fragment VARCHAR(100);
alter table korap_users change column URI_Expiration uri_expiration TIMESTAMP;
+alter table korap_users drop column URI_CONF_Fragment;
+alter table korap_users change column accountLock account_lock BOOLEAN not null default false;
+alter table korap_users change column accountCreation account_creation TIMESTAMP default CURRENT_TIMESTAMP not null;
+alter table korap_users change column accountExpiration account_expiration TIMESTAMP;
+alter table korap_users change column accountLink account_link VARCHAR(100);
+
+
+alter table shib_users change column accountCreation account_creation TIMESTAMP default CURRENT_TIMESTAMP not null;
+alter table shib_users change column accountExpiration account_expiration TIMESTAMP;
+alter table shib_users change column accountLink account_link VARCHAR(100);
+
+alter table user_details change column userID user_id INTEGER NOT NULL UNIQUE;
+
drop view allusers;
rename table r_store TO resource_store;
@@ -30,7 +44,7 @@
rename table cstorage to coll_store;
alter table coll_store add column (
created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
-userID INTEGER);
+user_id INTEGER);
alter table coll_store drop column refCorpus;
@@ -49,21 +63,21 @@
--);
-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
-);
+--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;
+alter table group_ref change policyId policy_id INTEGER;
drop view p_view;
create or replace view policy_view as
diff --git a/src/main/resources/db/sqlite/V1__Initial_version.sql b/src/main/resources/db/sqlite/V1__Initial_version.sql
index ee32961..08fbae3 100644
--- a/src/main/resources/db/sqlite/V1__Initial_version.sql
+++ b/src/main/resources/db/sqlite/V1__Initial_version.sql
@@ -2,23 +2,23 @@
id INTEGER PRIMARY KEY AUTOINCREMENT,
username VARCHAR(150) NOT NULL UNIQUE,
password VARCHAR(100) NOT NULL,
-accountLock boolean NOT NULL,
-accountCreation TIMESTAMP NOT NULL,
+account_lock boolean NOT NULL,
+account_creation BIGINT NOT NULL,
-- deprecate this
type INTEGER DEFAULT 0,
uri_fragment VARCHAR(100),
-uri_expiration TIMESTAMP,
-accountLink VARCHAR(100)
+uri_expiration BIGINT,
+account_link VARCHAR(100)
);
CREATE TABLE IF NOT EXISTS shib_users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
username VARCHAR(150) NOT NULL UNIQUE,
-accountCreation TIMESTAMP NOT NULL,
+account_creation BIGINT,
type INTEGER DEFAULT 1,
loginSuccess INTEGER,
loginFailed INTEGER,
-accountLink VARCHAR(100)
+account_link VARCHAR(100)
);
CREATE TABLE IF NOT EXISTS user_details (
@@ -83,7 +83,7 @@
name VARCHAR(150),
description VARCHAR(200),
query VARCHAR(500),
-created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
+created BIGINT NOT NULL,
user_id INTEGER,
foreign key(user_id)
references korap_users(id)
@@ -98,7 +98,7 @@
aud_location VARCHAR(100),
aud_field_1 VARCHAR(400),
aud_args VARCHAR(400),
-aud_timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
+aud_timestamp BIGINT,
aud_status VARCHAR(100)
);
@@ -113,20 +113,20 @@
CREATE TABLE IF NOT EXISTS policy_store (
id INTEGER PRIMARY KEY AUTOINCREMENT,
target_id BIGINT NOT NULL,
-created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
+created BIGINT NOT NULL,
creator INTEGER NOT NULL,
posix SMALLINT NOT NULL,
-expire timestamp,
-enable timestamp NOT NULL,
+expire BIGINT,
+enable BIGINT 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 DATE DEFAULT CURRENT_TIMESTAMP,
-disabled BOOLEAN default true
+persistent_id VARCHAR(100) UNIQUE,
+created BIGINT NOT NULL DEFAULT CURRENT_TIMESTAMP,
+disabled BOOLEAN DEFAULT TRUE
);
CREATE TABLE IF NOT EXISTS group_ref (
@@ -244,7 +244,7 @@
-- in case of code authorization, should match auth code scopes!
-- use scopes for levelaccess descriptor level[rw],level[r]
scopes VARCHAR(350),
-expiration TIMESTAMP,
+expiration BIGINT NOT NULL,
FOREIGN KEY (user_id)
REFERENCES korap_users(id),
FOREIGN KEY (client_id)
@@ -257,7 +257,7 @@
id INTEGER PRIMARY KEY AUTOINCREMENT,
client_id VARCHAR(100),
user_id INTEGER,
-expiration TIMESTAMP,
+expiration BIGINT NOT NULL,
scopes VARCHAR(350),
FOREIGN KEY (user_id)
REFERENCES korap_users(id)
@@ -324,10 +324,10 @@
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);
+VALUES (NEW.id, NEW.id, 0, NEW.persistent_id);
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
+SELECT parent_id, NEW.id, depth + 1, name_path || "/" || NEW.persistent_id FROM resource_tree
WHERE child_id = NEW.parent_id;
END;
diff --git a/src/main/resources/default-config.xml b/src/main/resources/default-config.xml
index 3f9f6a6..a756340 100644
--- a/src/main/resources/default-config.xml
+++ b/src/main/resources/default-config.xml
@@ -63,19 +63,19 @@
<!--class="org.apache.commons.dbcp2.BasicDataSource"-->
<!-- org.springframework.jdbc.datasource.SingleConnectionDataSource -->
<bean id="dataSource"
- class="org.apache.commons.dbcp2.BasicDataSource"
+ 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>-->
- <property name="initialSize" value="2"/>
- <property name="maxIdle" value="2"/>
- <property name="poolPreparedStatements" value="true"/>
+ <property name="suppressClose">
+ <value>true</value>
+ </property>
+ <!--<property name="initialSize" value="2"/>-->
+ <!--<property name="maxIdle" value="2"/>-->
+ <!--<property name="poolPreparedStatements" value="true"/>-->
</bean>
<!-- to configure database for sqlite, mysql, etc. migrations -->