diff --git a/i2b2-database/initdb-data/30-shrine-modifications.sh b/i2b2-database/initdb-data/30-shrine-modifications.sh
index 667c3bc..81a639f 100644
--- a/i2b2-database/initdb-data/30-shrine-modifications.sh
+++ b/i2b2-database/initdb-data/30-shrine-modifications.sh
@@ -1,158 +1,174 @@
#!/bin/bash
set -e
### in scenario of adding medco to an existing i2b2 installation: this is the additional shrine stuff
# db lookups
psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" -d "$I2B2_DOMAIN_NAME" <<-EOSQL
insert into i2b2hive.ont_db_lookup (c_domain_id, c_project_path, c_owner_id, c_db_fullschema, c_db_datasource, c_db_servertype, c_db_nicename)
values ('$I2B2_DOMAIN_NAME', 'MedCo-SHRINE/', '@', 'shrine_ont', 'java:/OntologyShrineDS', 'POSTGRESQL', 'MedCo-SHRINE')
on conflict do nothing;
insert into i2b2hive.crc_db_lookup (c_domain_id, c_project_path, c_owner_id, c_db_fullschema, c_db_datasource, c_db_servertype, c_db_nicename)
values ('$I2B2_DOMAIN_NAME', '/MedCo-SHRINE/', '@', 'i2b2demodata', 'java:/QueryToolDemoDS', 'POSTGRESQL', 'MedCo-SHRINE')
on conflict do nothing;
EOSQL
####################################################################################
######################### information about password hash ##########################
####################################################################################
### how to generate the hash (from shrine sources folder)
# cd "install/i2b2-1.7/i2b2"
# javac ./I2b2PasswordCryptor.java
# SHRINE_PW=$(java -classpath ./ I2b2PasswordCryptor <thepassword>)
### some encrypted versions:
# demouser= 9117d59a69dc49807671a51f10ab7f
# prigen2017= f8eb764674b57b5710e3c1665464e29
# pFjy3EjDVwLfT2rB9xkK= 7cb1ac9deab165535494d60da1d3d7e
####################################################################################
# pm data
psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" -d "$I2B2_DOMAIN_NAME" <<-EOSQL
-
-
insert into i2b2pm.pm_cell_data (cell_id, project_path, name, method_cd, url, can_override, status_cd)
values ('CRC', '/MedCo-SHRINE', 'MedCo-SHRINE Federated Query', 'REST', 'https://shrine-server:6443/shrine/rest/i2b2/', 1, 'A');
insert into i2b2pm.pm_project_data (project_id, project_name, project_wiki, project_path, status_cd)
values ('MedCo-SHRINE', 'MedCo-SHRINE', 'https://github.com/lca1/medco', '/MedCo-SHRINE', 'A');
INSERT INTO i2b2pm.PM_USER_DATA (USER_ID, FULL_NAME, PASSWORD, STATUS_CD)
VALUES('medcoshrineuser', 'MedCo SHRINE User', 'f8eb764674b57b5710e3c1665464e29', 'A');
insert into i2b2pm.pm_project_user_roles (project_id, user_id, user_role_cd, status_cd)
- values ('MedCo-SHRINE', 'medcoshrineuser', 'USER', 'A');
+ values ('MedCo-SHRINE', 'medcoshrineuser', 'USER', 'A');
insert into i2b2pm.pm_project_user_roles (project_id, user_id, user_role_cd, status_cd)
- values ('MedCo-SHRINE', 'medcoshrineuser', 'DATA_OBFSC', 'A');
+ values ('MedCo-SHRINE', 'medcoshrineuser', 'DATA_OBFSC', 'A');
+ INSERT INTO i2b2pm.PM_USER_DATA (USER_ID, FULL_NAME, PASSWORD, STATUS_CD)
+ VALUES('medcoservice', 'MedCo Service User', '7cb1ac9deab165535494d60da1d3d7e', 'A');
+ INSERT INTO i2b2pm.PM_PROJECT_USER_ROLES (PROJECT_ID, USER_ID, USER_ROLE_CD, STATUS_CD)
+ VALUES('@', 'medcoservice', 'USER', 'A');
+ INSERT INTO i2b2pm.PM_PROJECT_USER_ROLES (PROJECT_ID, USER_ID, USER_ROLE_CD, STATUS_CD)
+ VALUES('@', 'medcoservice', 'DATA_DEID', 'A');
+ INSERT INTO i2b2pm.PM_PROJECT_USER_ROLES (PROJECT_ID, USER_ID, USER_ROLE_CD, STATUS_CD)
+ VALUES('@', 'medcoservice', 'DATA_OBFSC', 'A');
+ INSERT INTO i2b2pm.PM_PROJECT_USER_ROLES (PROJECT_ID, USER_ID, USER_ROLE_CD, STATUS_CD)
+ VALUES('@', 'medcoservice', 'DATA_AGG', 'A');
+ INSERT INTO i2b2pm.PM_PROJECT_USER_ROLES (PROJECT_ID, USER_ID, USER_ROLE_CD, STATUS_CD)
+ VALUES('@', 'medcoservice', 'DATA_LDS', 'A');
+ INSERT INTO i2b2pm.PM_PROJECT_USER_ROLES (PROJECT_ID, USER_ID, USER_ROLE_CD, STATUS_CD)
+ VALUES('@', 'medcoservice', 'EDITOR', 'A');
+ INSERT INTO i2b2pm.PM_PROJECT_USER_ROLES (PROJECT_ID, USER_ID, USER_ROLE_CD, STATUS_CD)
+ VALUES('@', 'medcoservice', 'DATA_PROT', 'A');
+ INSERT INTO i2b2pm.PM_PROJECT_USER_ROLES (PROJECT_ID, USER_ID, USER_ROLE_CD, STATUS_CD)
+ VALUES('@', 'medcoservice', 'MANAGER', 'A');
INSERT INTO i2b2pm.pm_user_params(datatype_cd, user_id, param_name_cd, value, change_date, entry_date, status_cd) VALUES
- ('T', 'AGG_SERVICE_ACCOUNT', 'qep', 'true', 'NOW()', 'NOW()', 'A');
+ ('T', 'medcoservice', 'qep', 'true', 'NOW()', 'NOW()', 'A');
INSERT INTO i2b2pm.pm_user_params(datatype_cd, user_id, param_name_cd, value, change_date, entry_date, status_cd) VALUES
('T', 'medcoadmin', 'DataSteward', 'true', 'NOW()', 'NOW()', 'A');
EOSQL
# add demo shrine ontology structure
psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" -d "$I2B2_DOMAIN_NAME" <<-EOSQL
CREATE TABLE shrine_ont.SHRINE
(
C_HLEVEL NUMERIC(22,0),
C_FULLNAME VARCHAR(900),
C_NAME VARCHAR(2000),
C_SYNONYM_CD CHAR(1),
C_VISUALATTRIBUTES CHAR(3),
C_TOTALNUM NUMERIC(22,0),
C_BASECODE VARCHAR(450),
C_METADATAXML TEXT,
C_FACTTABLECOLUMN VARCHAR(50),
C_TABLENAME VARCHAR(50),
C_COLUMNNAME VARCHAR(50),
C_COLUMNDATATYPE VARCHAR(50),
C_OPERATOR VARCHAR(10),
C_DIMCODE VARCHAR(900),
C_COMMENT TEXT,
C_TOOLTIP VARCHAR(900),
UPDATE_DATE DATE,
DOWNLOAD_DATE DATE,
IMPORT_DATE DATE,
SOURCESYSTEM_CD VARCHAR(50),
VALUETYPE_CD VARCHAR(50),
M_APPLIED_PATH VARCHAR(900),
M_EXCLUSION_CD VARCHAR(900)
);
grant all privileges on all tables in schema shrine_ont to shrine_ont;
grant all privileges on all sequences in schema shrine_ont to shrine_ont;
grant all privileges on all functions in schema shrine_ont to shrine_ont;
grant all privileges on all tables in schema shrine_ont to i2b2metadata;
grant all privileges on all sequences in schema shrine_ont to i2b2metadata;
grant all privileges on all functions in schema shrine_ont to i2b2metadata;
EOSQL
# add encrypted dummy flags for patient_dimension in crc schema
psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" -d "$I2B2_DOMAIN_NAME" <<-EOSQL
ALTER TABLE i2b2demodata.patient_dimension ADD COLUMN enc_dummy_flag_cd character(88);
COMMENT ON COLUMN i2b2demodata.patient_dimension.enc_dummy_flag_cd IS 'base64-encoded encrypted dummy flag (0 or 1)';
INSERT INTO i2b2demodata.code_lookup VALUES
('patient_dimension', 'enc_dummy_flag_cd', 'CRC_COLUMN_DESCRIPTOR', 'Encrypted Dummy Flag', NULL, NULL, NULL,
NULL, 'NOW()', NULL, 1);
EOSQL
# add i2b2 test query term for shrine (ontology + database)
psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" -d "$I2B2_DOMAIN_NAME" <<-EOSQL
INSERT INTO i2b2metadata.sensitive_tagged VALUES
(2, '\medco\tagged\TESTKEY\', '', 'N', 'LH ', NULL, 'TAG_ID:TESTKEY', NULL, 'concept_cd', 'concept_dimension',
'concept_path', 'T', 'LIKE', '\medco\tagged\TESTKEY\', NULL, NULL, 'NOW()', NULL, NULL, NULL, 'TAG_ID', '@',
NULL, NULL, NULL, NULL);
INSERT INTO i2b2demodata.concept_dimension VALUES
('\medco\tagged\TESTKEY\', 'TAG_ID:TESTKEY', NULL, NULL, NULL, NULL, 'NOW()', NULL, -1);
INSERT INTO i2b2demodata.patient_mapping VALUES
('TESTPATIENT', 'TESTSITE', -1, NULL, 'MedCo', NULL, NULL, NULL, 'NOW()', NULL, -1);
INSERT INTO i2b2demodata.patient_mapping VALUES
('-1', 'HIVE', -1, 'A', 'HIVE', NULL, 'NOW()', 'NOW()', 'NOW()', 'edu.harvard.i2b2.crc', -1);
INSERT INTO i2b2demodata.patient_dimension VALUES
(-1, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'NOW()', NULL, -1,
'FzXxSbBn86gMmF7WT6a4kHDcHrOg3SEkaojcPm7U3qsQp0bhzaLZLYenL/+yNS5j39TFcLU1uSUE5I8tD3Qryw==');
INSERT INTO i2b2demodata.encounter_mapping VALUES
('TESTVISIT', 'TESTSITE', 'MedCo', -1, 'TESTPATIENT', 'TESTSITE', NULL, NULL, NULL, NULL, 'NOW()', NULL, -1);
INSERT INTO i2b2demodata.encounter_mapping VALUES
('-1', 'HIVE', 'HIVE', -1, 'TESTPATIENT', 'TESTSITE', 'A', NULL, 'NOW()', 'NOW()', 'NOW()', 'edu.harvard.i2b2.crc', -1);
INSERT INTO i2b2demodata.visit_dimension VALUES
(-1, -1, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'NOW()', 'TESTSITE', -1);
INSERT INTO i2b2demodata.provider_dimension VALUES
('TESTSITE', '\medco\institutions\TESTSITE\', 'TESTSITE', NULL, NULL, NULL, 'NOW()', NULL, -1);
INSERT INTO i2b2demodata.observation_fact VALUES
(-1, -1, 'TAG_ID:TESTKEY', 'TESTSITE', 'NOW()', '@', 1, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'TESTSITE',
NULL, NULL, NULL, NULL, 'NOW()', NULL, -1, -1);
EOSQL
# original shrine ontology: only the version key
psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" -d "$I2B2_DOMAIN_NAME" <<-EOSQL
INSERT into shrine_ont.TABLE_ACCESS
( C_TABLE_CD, C_TABLE_NAME, C_PROTECTED_ACCESS, C_HLEVEL, C_NAME, C_FULLNAME, C_SYNONYM_CD, C_VISUALATTRIBUTES,
C_TOOLTIP, C_FACTTABLECOLUMN, C_DIMTABLENAME, C_COLUMNNAME, C_COLUMNDATATYPE, C_DIMCODE, C_OPERATOR) values
( 'SHRINE', 'SHRINE', 'N', 0, 'SHRINE Ontology', '\SHRINE\', 'N', 'CH', 'SHRINE Ontology', 'concept_cd',
'concept_dimension', 'concept_path', 'T', '\SHRINE\', 'LIKE')
on conflict do nothing;
INSERT INTO shrine_ont.SHRINE (C_HLEVEL, C_FULLNAME, C_NAME, C_SYNONYM_CD, C_VISUALATTRIBUTES, C_TOTALNUM, C_BASECODE, C_METADATAXML,
C_FACTTABLECOLUMN, C_TABLENAME, C_COLUMNNAME, C_COLUMNDATATYPE, C_OPERATOR, C_DIMCODE, C_COMMENT, C_TOOLTIP,
UPDATE_DATE, DOWNLOAD_DATE, IMPORT_DATE, SOURCESYSTEM_CD, VALUETYPE_CD, M_APPLIED_PATH, M_EXCLUSION_CD ) VALUES
(1, '\SHRINE\ONTOLOGYVERSION\', 'ONTOLOGYVERSION', 'N', 'FH', NULL, NULL, '', 'concept_cd', 'concept_dimension',
'concept_path', 'T', 'LIKE', '\SHRINE\ONTOLOGYVERSION\', '', 'ONTOLOGYVERSION\', NULL, NULL, NULL, 'SHRINE', NULL,
'@', NULL );
INSERT INTO shrine_ont.SHRINE (C_HLEVEL, C_FULLNAME, C_NAME, C_SYNONYM_CD, C_VISUALATTRIBUTES, C_TOTALNUM, C_BASECODE, C_METADATAXML,
C_FACTTABLECOLUMN, C_TABLENAME, C_COLUMNNAME, C_COLUMNDATATYPE, C_OPERATOR, C_DIMCODE, C_COMMENT, C_TOOLTIP,
UPDATE_DATE, DOWNLOAD_DATE, IMPORT_DATE, SOURCESYSTEM_CD, VALUETYPE_CD, M_APPLIED_PATH, M_EXCLUSION_CD ) VALUES
(2, '\SHRINE\ONTOLOGYVERSION\MedCo-SHRINE_Ontology_Empty\', 'MedCo-SHRINE_Ontology_Empty', 'N', 'LH', NULL, NULL,
'', 'concept_cd', 'concept_dimension', 'concept_path', 'T', 'LIKE', '\SHRINE\ONTOLOGYVERSION\MedCo-SHRINE_Ontology_Empty\',
'', 'ONTOLOGYVERSION\MedCo-SHRINE_Ontology_Empty\', NULL, NULL, NULL, 'SHRINE', NULL, '@', NULL );
EOSQL
# full loading of shrine ontology disabled
#wget https://open.med.harvard.edu/svn/shrine-ontology/SHRINE_Demo_Downloads/trunk/ShrineDemo.sql
#sed -i '1s/^/SET search_path TO shrine_ont;\n/' Shrine.sql
#psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" -d "$I2B2_DOMAIN_NAME" < ShrineDemo.sql
diff --git a/shrine-server/conf/shrine.conf b/shrine-server/conf/shrine.conf
index 1766572..8e611d1 100644
--- a/shrine-server/conf/shrine.conf
+++ b/shrine-server/conf/shrine.conf
@@ -1,199 +1,199 @@
shrine {
pmEndpoint {
url = "http://i2b2-server:8080/i2b2/services/PMService/getServices"
timeout {
seconds = 10
}
}
ontEndpoint {
url = "http://i2b2-server:8080/i2b2/services/OntologyService/"
}
hiveCredentials {
# todo: parameters to take into account
domain = ${I2B2_DOMAIN_NAME}
- username = "medcouser"
- password = ${ADMIN_PASSWORD}
+ username = "medcoservice"
+ password = ${DB_PASSWORD}
crcProjectId = "MedCo"
ontProjectId = "MedCo-SHRINE"
}
queryEntryPoint {
create = true
includeAggregateResults = false
maxQueryWaitTime {
minutes = 5
}
trustModelIsHub = true
attachSigningCert = true
authenticationType = "pm"
#authorizationType = "shrine-steward"
authorizationType = "none"
shrineSteward {
- qepUserName = "AGG_SERVICE_ACCOUNT" // name of user the steward will submit queries as
+ qepUserName = "medcoservice" // name of user the steward will submit queries as
qepPassword = ${DB_PASSWORD}
stewardBaseUrl = "https://shrine-server:6443" // typically hostname+port of Tomcat server running steward.war
}
}
hub {
create = true
shouldQuerySelf = false
maxQueryWaitTime {
minutes = 4.5
}
downstreamNodes {
include file("SHRINE_DOWNSTREAM_NODES_FILE_PATH")
}
}
adapter {
crcEndpoint {
url = "http://i2b2-server:8080/i2b2/services/MedCoQueryService/"
}
setSizeObfuscation = false
adapterMappingsFileName = "AdapterMappings.xml"
# lockout disabled
adapterLockoutAttemptsThreshold = 0
maxSignatureAge {
minutes = 5
}
//obfuscation {
//binSize = 5 //by default. Round to the nearest binSize. Use 1 for no effect (to match SHRINE 1.21 and earlier).
//sigma = 6.5 //by default. Noise to inject. Use 0 for no effect. (Use 1.33 to match SHRINE 1.21 and earlier).
//clamp = 10 //by default. Maximum ammount of noise to inject. (Use 3 to match SHRINE 1.21 and earlier).
//}
// disabled
botDefense {
countsAndMilliseconds = [ //to turn off, use an empty json list
// {count = 10, milliseconds = 60000}, //allow up to 10 queries in one minute by default
// {count = 200, milliseconds = 36000000} //allow up to 200 queries in 10 hours by default
]
}
}
networkStatusQuery = "\\\\ENCRYPTED_KEY\\TESTKEY\\"
humanReadableNodeName = ${SHRINE_NODE_NAME}
shrineDatabaseType = "mysql"
keystore {
file = ${CONF_DIR}/${SHRINE_KEYSTORE_NODE_ALIAS}.keystore
password = ${ADMIN_PASSWORD}
privateKeyAlias = ${SHRINE_KEYSTORE_NODE_ALIAS}-private
keyStoreType = "JKS"
caCertAliases = ["shrine-hub-ca"]
// maps site alias (from downstream nodes) to the keystore alias
#aliasMap = {
# include file("SHRINE_ALIAS_MAP_FILE_PATH")
#}
}
breakdownResultOutputTypes {
PATIENT_AGE_COUNT_XML {
description = "Age patient breakdown"
}
PATIENT_RACE_COUNT_XML {
description = "Race patient breakdown"
}
PATIENT_VITALSTATUS_COUNT_XML {
description = "Vital Status patient breakdown"
}
PATIENT_GENDER_COUNT_XML {
description = "Gender patient breakdown"
}
}
steward {
//Can be Pending, Approved, or TopcisIgnoredJustLog
//Pending - new topics start in the Pending state; researchers must wait for the Steward to approve them
//Approved - new topics start in the Approved state; researchers can use them immediately
//TopicsIgnoredJustLog - all queries are logged and approved; researchers don't need to create topics
createTopicsMode = Approved
database {
dataSourceFrom = "JNDI" // Can be JNDI or testDataSource. Use testDataSource for tests, JNDI everywhere else
jndiDataSourceName = "java:comp/env/jdbc/stewardDB" //leave out for tests
slickProfileClassName = "slick.driver.MySQLDriver$" // Can be scala.slick.driver.H2Driver$
// scala.slick.driver.MySQLDriver$
// scala.slick.driver.PostgresDriver$
// scala.slick.driver.SQLServerDriver$
// scala.slick.driver.JdbcDriver$
// com.typesafe.slick.driver.oracle.OracleDriver$
//
// (Yes, with the $ on the end)
// Note that SQLServerDriver and OracleDriver are not included with
// Slick by default. Either use JdbcDriver, or experiment with the
// closed-source package at:
// http://slick.typesafe.com/doc/2.1.0/extensions.html
}
emailDataSteward {
sendAuditEmails = false
// todo: fill if emails necessary
}
}
authenticate {
usersource {
domain = ${I2B2_DOMAIN_NAME}
}
}
problem {
problemHandler = "net.shrine.problem.LogAndDatabaseProblemHandler$"
}
dashboard {
gruntWatch = false //false for production, true for mvn tomcat7:run . Allows the client javascript and html files to be loaded via gruntWatch .
happyBaseUrl = "https://shrine-server:6443/shrine/rest/happy"
statusBaseUrl = "https://shrine-server:6443/shrine/rest/internalstatus"
database {
dataSourceFrom = "JNDI" //Can be JNDI or testDataSource . Use testDataSource for tests, JNDI everywhere else
jndiDataSourceName = "java:comp/env/jdbc/problemDB" //or leave out for tests
slickProfileClassName = "slick.driver.MySQLDriver$" // Can be
// slick.driver.H2Driver$
// slick.driver.MySQLDriver$
// slick.driver.PostgresDriver$
// slick.driver.SQLServerDriver$
// slick.driver.JdbcDriver$
// freeslick.OracleProfile$
// freeslick.MSSQLServerProfile$
//
// (Yes, with the $ on the end)
createTablesOnStart = false //for testing with H2 in memory, when not running unit tests. Set to false normally
}
}
metaData {
// todo: put appropriate info
siteAdminsContactInfo = ["admin1@example.com", "admin2@example.com"]
dataStewardContactInfo = "data.steward@example.com"
}
email {
// todo: fill if emails necessary
}
status {
permittedHostOfOrigin = "shrine-server"
}
}