diff --git a/i2b2-database/initdb-data/10-i2b2-data-modifications.sh b/i2b2-database/initdb-data/10-i2b2-data-modifications.sh index b7bba3e..52abd3d 100644 --- a/i2b2-database/initdb-data/10-i2b2-data-modifications.sh +++ b/i2b2-database/initdb-data/10-i2b2-data-modifications.sh @@ -1,97 +1,97 @@ #!/bin/bash set -e ### in scenario of adding medco to an existing i2b2 installation: this is the existing installation # bug fixes psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" -d "$I2B2_DOMAIN_NAME" <<-EOSQL update i2b2hive.crc_db_lookup set c_db_fullschema = 'i2b2demodata' where c_domain_id = 'i2b2demo'; update i2b2hive.im_db_lookup set c_db_fullschema = 'i2b2imdata' where c_domain_id = 'i2b2demo'; update i2b2hive.ont_db_lookup set c_db_fullschema = 'i2b2metadata' where c_domain_id = 'i2b2demo'; update i2b2hive.work_db_lookup set c_db_fullschema = 'i2b2workdata' where c_domain_id = 'i2b2demo'; insert into i2b2pm.pm_cell_params (datatype_cd, cell_id, project_path, param_name_cd, value, changeby_char, status_cd) values ('T', 'FRC', '/', 'DestDir', '$I2B2_FR_FILES_DIR', 'i2b2', 'A'); EOSQL # update hive data (DB lookups) psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" -d "$I2B2_DOMAIN_NAME" <<-EOSQL update i2b2hive.crc_db_lookup SET C_DOMAIN_ID = '$I2B2_DOMAIN_NAME', C_PROJECT_PATH = '/MedCo/', C_DB_NICENAME = 'MedCo' WHERE C_DOMAIN_ID = 'i2b2demo'; UPDATE i2b2hive.im_db_lookup SET - C_DOMAIN_ID = '$I2B2_DOMAIN_NAME', C_PROJECT_PATH = '/MedCo/' + C_DOMAIN_ID = '$I2B2_DOMAIN_NAME', C_PROJECT_PATH = 'MedCo/' WHERE C_DOMAIN_ID = 'i2b2demo'; UPDATE i2b2hive.ont_db_lookup SET - C_DOMAIN_ID = '$I2B2_DOMAIN_NAME', C_PROJECT_PATH = '/MedCo/' + C_DOMAIN_ID = '$I2B2_DOMAIN_NAME', C_PROJECT_PATH = 'MedCo/' WHERE C_DOMAIN_ID = 'i2b2demo'; UPDATE i2b2hive.work_db_lookup SET - C_DOMAIN_ID = '$I2B2_DOMAIN_NAME', C_PROJECT_PATH = '/MedCo/' + C_DOMAIN_ID = '$I2B2_DOMAIN_NAME', C_PROJECT_PATH = 'MedCo/' WHERE C_DOMAIN_ID = 'i2b2demo'; EOSQL # load i2b2 PM data, medco version psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" -d "$I2B2_DOMAIN_NAME" <<-EOSQL UPDATE i2b2pm.pm_hive_data SET DOMAIN_NAME = '$I2B2_DOMAIN_NAME', DOMAIN_ID = '$I2B2_DOMAIN_NAME', HELPURL = 'https://github.com/lca1/medco' WHERE DOMAIN_ID = 'i2b2'; UPDATE i2b2pm.PM_USER_DATA SET USER_ID = 'medcoadmin', FULL_NAME = 'MedCo Admin', PASSWORD = 'f8eb764674b57b5710e3c1665464e29' WHERE USER_ID = 'i2b2'; UPDATE i2b2pm.PM_USER_DATA SET PASSWORD = '7cb1ac9deab165535494d60da1d3d7e' WHERE USER_ID = 'AGG_SERVICE_ACCOUNT'; UPDATE i2b2pm.PM_PROJECT_USER_ROLES SET USER_ID = 'medcoadmin' WHERE USER_ID = 'i2b2'; INSERT INTO i2b2pm.PM_USER_DATA (USER_ID, FULL_NAME, PASSWORD, STATUS_CD) VALUES('medcouser', 'MedCo User', 'f8eb764674b57b5710e3c1665464e29', 'A'); insert into i2b2pm.pm_project_data (project_id, project_name, project_wiki, project_path, status_cd) values ('MedCo', 'MedCo', 'https://github.com/lca1/medco', '/MedCo/', 'A'); INSERT INTO i2b2pm.PM_CELL_DATA (CELL_ID, PROJECT_PATH, NAME, METHOD_CD, URL, CAN_OVERRIDE, STATUS_CD) VALUES('CRC', '/', 'Data Repository', 'REST', 'http://i2b2-server:8080/i2b2/services/QueryToolService/', 1, 'A'); INSERT INTO i2b2pm.PM_CELL_DATA(CELL_ID, PROJECT_PATH, NAME, METHOD_CD, URL, CAN_OVERRIDE, STATUS_CD) VALUES('FRC', '/', 'File Repository ', 'SOAP', 'http://i2b2-server:8080/i2b2/services/FRService/', 1, 'A'); INSERT INTO i2b2pm.PM_CELL_DATA(CELL_ID, PROJECT_PATH, NAME, METHOD_CD, URL, CAN_OVERRIDE, STATUS_CD) VALUES('ONT', '/', 'Ontology Cell', 'REST', 'http://i2b2-server:8080/i2b2/services/OntologyService/', 1, 'A'); INSERT INTO i2b2pm.PM_CELL_DATA(CELL_ID, PROJECT_PATH, NAME, METHOD_CD, URL, CAN_OVERRIDE, STATUS_CD) VALUES('WORK', '/', 'Workplace Cell', 'REST', 'http://i2b2-server:8080/i2b2/services/WorkplaceService/', 1, 'A'); INSERT INTO i2b2pm.PM_CELL_DATA(CELL_ID, PROJECT_PATH, NAME, METHOD_CD, URL, CAN_OVERRIDE, STATUS_CD) VALUES('IM', '/', 'IM Cell', 'REST', 'http://i2b2-server:8080/i2b2/services/IMService/', 1, 'A'); INSERT INTO i2b2pm.PM_PROJECT_USER_ROLES (PROJECT_ID, USER_ID, USER_ROLE_CD, STATUS_CD) VALUES('MedCo', 'AGG_SERVICE_ACCOUNT', 'USER', 'A'); INSERT INTO i2b2pm.PM_PROJECT_USER_ROLES (PROJECT_ID, USER_ID, USER_ROLE_CD, STATUS_CD) VALUES('MedCo', 'AGG_SERVICE_ACCOUNT', 'MANAGER', 'A'); INSERT INTO i2b2pm.PM_PROJECT_USER_ROLES (PROJECT_ID, USER_ID, USER_ROLE_CD, STATUS_CD) VALUES('MedCo', 'AGG_SERVICE_ACCOUNT', 'DATA_OBFSC', 'A'); INSERT INTO i2b2pm.PM_PROJECT_USER_ROLES (PROJECT_ID, USER_ID, USER_ROLE_CD, STATUS_CD) VALUES('MedCo', 'AGG_SERVICE_ACCOUNT', 'DATA_AGG', 'A'); INSERT INTO i2b2pm.PM_PROJECT_USER_ROLES (PROJECT_ID, USER_ID, USER_ROLE_CD, STATUS_CD) VALUES('MedCo', 'medcoadmin', 'MANAGER', 'A'); INSERT INTO i2b2pm.PM_PROJECT_USER_ROLES (PROJECT_ID, USER_ID, USER_ROLE_CD, STATUS_CD) VALUES('MedCo', 'medcoadmin', 'USER', 'A'); INSERT INTO i2b2pm.PM_PROJECT_USER_ROLES (PROJECT_ID, USER_ID, USER_ROLE_CD, STATUS_CD) VALUES('MedCo', 'medcoadmin', 'DATA_OBFSC', 'A'); INSERT INTO i2b2pm.PM_PROJECT_USER_ROLES (PROJECT_ID, USER_ID, USER_ROLE_CD, STATUS_CD) VALUES('MedCo', 'medcouser', 'USER', 'A'); INSERT INTO i2b2pm.PM_PROJECT_USER_ROLES (PROJECT_ID, USER_ID, USER_ROLE_CD, STATUS_CD) VALUES('MedCo', 'medcouser', 'DATA_DEID', 'A'); INSERT INTO i2b2pm.PM_PROJECT_USER_ROLES (PROJECT_ID, USER_ID, USER_ROLE_CD, STATUS_CD) VALUES('MedCo', 'medcouser', 'DATA_OBFSC', 'A'); INSERT INTO i2b2pm.PM_PROJECT_USER_ROLES (PROJECT_ID, USER_ID, USER_ROLE_CD, STATUS_CD) VALUES('MedCo', 'medcouser', 'DATA_AGG', 'A'); INSERT INTO i2b2pm.PM_PROJECT_USER_ROLES (PROJECT_ID, USER_ID, USER_ROLE_CD, STATUS_CD) VALUES('MedCo', 'medcouser', 'DATA_LDS', 'A'); INSERT INTO i2b2pm.PM_PROJECT_USER_ROLES (PROJECT_ID, USER_ID, USER_ROLE_CD, STATUS_CD) VALUES('MedCo', 'medcouser', 'EDITOR', 'A'); INSERT INTO i2b2pm.PM_PROJECT_USER_ROLES (PROJECT_ID, USER_ID, USER_ROLE_CD, STATUS_CD) VALUES('MedCo', 'medcouser', 'DATA_PROT', 'A'); EOSQL diff --git a/i2b2-database/initdb-data/30-shrine-modifications.sh b/i2b2-database/initdb-data/30-shrine-modifications.sh index b37ede3..e66e068 100644 --- a/i2b2-database/initdb-data/30-shrine-modifications.sh +++ b/i2b2-database/initdb-data/30-shrine-modifications.sh @@ -1,108 +1,108 @@ #!/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') + 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'); + 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_project_user_roles (project_id, user_id, user_role_cd, status_cd) - values ('SHRINE', 'medcouser', 'USER', 'A'); + values ('MedCo-SHRINE', 'medcouser', 'USER', 'A'); insert into i2b2pm.pm_project_user_roles (project_id, user_id, user_role_cd, status_cd) - values ('SHRINE', 'medcouser', 'DATA_OBFSC', 'A'); + values ('MedCo-SHRINE', 'medcouser', 'DATA_OBFSC', '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'); 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 demo shrine ontology data TODO: disabled #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', 'CA', 'SHRINE Ontology', 'concept_cd', 'concept_dimension', 'concept_path', 'T', '\SHRINE\', 'LIKE') # on conflict do nothing; #EOSQL #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