Eureka! Install Guide
=====================
Himanshu Rathod <Himanshu.Rathod@emory.edu>

This is document contains details about the installation process for
Eureka!

== Prerequisites
* An Oracle database instance with *`SYSTEM`* privileges, in order to
create users/schemas, and grant appropriate privileges to the created
users/schemas.

== Clone Eureka! AMI
When creating a new Amazon AWS instance, use the following AMI:
*`ami-b09133d9`*.

== Set up the database users for Eureka!

=== Create Eureka! users
Create two users in the database, one for the Eureka! services layer,
and one for the Eureka! ETL backend.  The following SQL code snippet
can be used to create these users.  Be sure to change "PASSWORD" to
secure passwords.  These passwords will be used in later steps to
configure Eureka!.

[source,sql]
----
create user eurekaservice identified by "PASSWORD"
default tablespace users quota 1000M on users temporary tablespace
temp;

create user eurekabackend identified by "PASSWORD" default tablespace
users quota 1000M on users temporary tablespace temp;
----

=== Assign proper privileges to the Eureka! users.
Assign proper privileges to the users, to allow the application to
connect and perform the normal insert, delete, and other operations on
the data. The following SQL code snippet will grant the proper
permission to the users.

[source,sql]
----
grant create session to eurekaservice;
grant create table to eurekaservice;
grant create view to eurekaservice;
grant create any index to eurekaservice;
grant create public synonym to eurekaservice;
grant drop public synonym to eurekaservice;
grant create sequence to eurekaservice;
grant create procedure to eurekaservice;
grant create any type to eurekaservice;
grant alter any type to eurekaservice;
grant drop any type to eurekaservice;
grant execute any type to eurekaservice;
grant under any type to eurekaservice;
grant create any trigger to eurekaservice;
grant alter any trigger to eurekaservice;
grant drop any trigger to eurekaservice;

grant create session to eurekabackend;
grant create table to eurekabackend;
grant create view to eurekabackend;
grant create any index to eurekabackend;
grant create public synonym to eurekabackend;
grant drop public synonym to eurekabackend;
grant create sequence to eurekabackend;
grant create procedure to eurekabackend;
grant create any type to eurekabackend;
grant alter any type to eurekabackend;
grant drop any type to eurekabackend;
grant execute any type to eurekabackend;
grant under any type to eurekabackend;
grant create any trigger to eurekabackend;
grant alter any trigger to eurekabackend;
grant drop any trigger to eurekabackend;
----

=== Create Eureka! tables
Create necessary tables for the Eureka! service layer. The following
SQL code snippet will create the tables.

[source,sql]
----
create table eurekaservice.file_errors (
id number(19,0) not null,
lineNumber number(19,0),
text varchar2(255 char),
type varchar2(255 char),
fileupload_id number(19,0),
primary key (id));

create table eurekaservice.file_uploads (
id number(19,0) not null,
completed number(1,0) not null,
location varchar2(255 char),
processed number(1,0) not null,
timestamp timestamp,
validated number(1,0) not null,
user_id number(19,0),
primary key (id));

create table eurekaservice.file_warnings (
id number(19,0) not null,
lineNumber number(19,0),
text varchar2(255 char),
type varchar2(255 char),
fileupload_id number(19,0),
primary key (id));

create table eurekaservice.roles (
id number(19,0) not null,
defaultRole number(1,0),
name varchar2(255 char),
primary key (id));

create table eurekaservice.user_role (
roleId number(19,0) not null,
userId number(19,0) not null);

create table eurekaservice.users (
id number(19,0) not null,
active number(1,0) not null,
email varchar2(255 char),
firstName varchar2(255 char),
lastLogin timestamp,
lastName varchar2(255 char),
organization varchar2(255 char),
password varchar2(255 char),
verificationCode varchar2(255 char),
verified number(1,0) not null,
primary key (id));

alter table eurekaservice.file_errors
add constraint FK6239938EACFDA6AD
foreign key (fileupload_id) references file_uploads;

alter table eurekaservice.file_uploads
add constraint FK2F9E12CFD40C420D
foreign key (user_id) references users;

alter table eurekaservice.file_warnings
add constraint FK849BEBBAACFDA6AD
foreign key (fileupload_id) references file_uploads;

alter table eurekaservice.user_role
add constraint FK143BF46AA5A730BA
foreign key (roleId) references roles;

alter table eurekaservice.user_role
add constraint FK143BF46AAAFC8624
foreign key (userId) references users;

create sequence eurekaservice.FILEINFO_SEQ;

create sequence eurekaservice.FILE_SEQ;

create sequence eurekaservice.ROLE_SEQ;

create sequence eurekaservice.USER_SEQ;
----

Create necessary tables for the Eureka! backend layer. The following
SQL code snippet will create the tables.

[source,sql]
----
create table eurekabackend.configurations (
id number(19,0) not null,
i2b2DataPass varchar2(255 char),
i2b2DataSchema varchar2(255 char),
i2b2Host varchar2(255 char),
i2b2MetaPass varchar2(255 char),
i2b2MetaSchema varchar2(255 char),
i2b2Port number(10,0),
ontology varchar2(255 char),
protempaDatabaseName varchar2(255 char),
protempaHost varchar2(255 char),
protempaPass varchar2(255 char),
protempaPort number(10,0),
protempaSchema varchar2(255 char),
userId number(19,0),
primary key (id));

create table eurekabackend.job_events (
id number(19,0) not null,
exceptionStackTrace raw(255),
message varchar2(255 char),
state varchar2(255 char),
timeStamp timestamp,
job_id number(19,0),
primary key (id));

create table eurekabackend.jobs (
id number(19,0) not null,
configurationId number(19,0),
timestamp timestamp,
userId number(19,0),
primary key (id));
alter table job_events
add constraint FK88C003DB45BC207
foreign key (job_id) references jobs;

create sequence eurekabackend.CONFIG_SEQ;

create sequence eurekabackend.JOBEVENT_SEQ;

create sequence eurekabackend.JOB_SEQ;
----

=== Create seed data for Eureka!
Add the necessary seed data to the tables created for the Eureka!
users.  There are some pieces of data that should be present in the
database before the system is started up.

Insert the necessary roles into the roles table, using the
following SQL.  The code will add three roles to the system, a
superuser role, an admin role, and a researcher role.  A superuser can
not be deactivated or deleted from the system, but can de-activate any
other user.  An admin user can control other admin users, as well as
researcher users.  A researcher user is not allowed to see the
administration panel, but is allowed to upload data to the system, and
view results.

[source,sql]
----
insert into eurekaservice.roles
(id, defaultrole, name) values
(eurekaservice.role_seq.nextval, 0, 'superuser');

insert into eurekaservice.roles
(id, defaultrole, name) values
(eurekaservice.role_seq.nextval, 0, 'admin');

insert into eurekaservice.roles
(id, defaultrole, name) values
(eurekaservice.role_seq.nextval, 1, 'researcher');
----

Add a super-user account to the database.  This account can then be
used to activate other accounts.  The following will set the password
of the user to ``TODO! set password'', using an md5 hashing algorithm,
so that the actual password is never stored in clear text in the
database.  Please change the "organization" and "email" columns to
values appropriate for your organization.

[source,sql]
----
insert into eurekaservice.users
(id,active,email,firstname,lastlogin,lastname,organization,
password,verificationcode,verified) values
(eurekaservice.user_seq.nextval,1,'super.user@example.com',
'Super',sysdate,'User','MYORG','9db86aae4feff703ea5396ab7be73d2',
'1234-abcd',1);
----

Now connect the super-user account to the the appropriate roles.  The
following SQL code snippet grants the new user all the role
permissions.  The statements assume that the user has ID of 1, while
the roles have IDs of 1 through 3. Please adust the SQL if that is
not the case.

[source,sql]
----
insert into eurekaservice.user_role (roleid,userid) values (1,1);

insert into eurekaservice.user_role (roleid,userid) values (2,1);

insert into eurekaservice.user_role (roleid,userid) values (3,1);
----

== Update Apache Tomcat configuration

Update the Apache Tomcat configuration to use the appropriate users
and passwords to connect to the database.  The file that contains the
information is *`/opt/apps/tomcat/default/conf/context.xml`*.Replace
the *`SERVICE_PASSWORD`*, *`BACKEND_PASSWORD`*, *`DB_HOST`*,
*`DB_NAME`*, and *`MAIL_SERVER`* place-holders in the file. Use the
passwords previously chosen for the eurekaservice and eurekabackend
users.  The DB_HOST and DB_NAME place-holders represent the hostname
and database service name in which the users where created
previously. The MAIL_SERVER place-holder should be the fully-qualified
name of an SMTP host that the software can use to deliver emails to
the system administrator, as well as the system users.  The following
code snippet shows the main contents of the file.

[source,xml]
----
<Resource name="jdbc/EurekaService" auth="Container"
          type="javax.sql.DataSource"
          driverClassName="oracle.jdbc.OracleDriver"
          factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
          url="jdbc:oracle:thin:@DB_HOST:1521:DB_NAME"
          username="eurekaservice" password="SERVICE_PASSWORD"
          initialSize="1" maxActive="3" maxIdle="1" minIdle="1"
          maxWait="-1"/>
<Resource name="jdbc/EurekaBackend" auth="Container"
          type="javax.sql.DataSource"
          driverClassName="oracle.jdbc.OracleDriver"
          factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
          url="jdbc:oracle:thin:@DB_HOST:1521:DB_NAME"
          username="eurekabackend" password="BACKEND_PASSWORD"
          initialSize="1" maxActive="3" maxIdle="3" minIdle="1"
          maxWait="-1"/>
<Resource name="mail/Session" auth="Container"
          type="javax.mail.Session"
          mail.smtp.host="MAIL_SERVER"
          mail.from="Eureka! Admin &lt;no-reply@MAIL_SERVER&gt;" />
----

== Create data schema and tables

Create the user/schema and tables necessary to hold uploaded data,
which will be processed by Eureka!.

=== Create a new user in the database.

Please be sure to change the "PASSWORD" token to a secure password.

[source,sql]
----
create user user_1_protempa identified by "PASSWORD"
default tablespace users
quota 1000M on users
temporary tablespace temp;
----

=== Grant appropriate privileges to the user

[source,sql]
----
grant create session to user_1_protempa;
grant create table to user_1_protempa;
grant create view to user_1_protempa;
grant create any index to user_1_protempa;
grant create public synonym to user_1_protempa;
grant drop public synonym to user_1_protempa;
grant create sequence to user_1_protempa;
grant create procedure to user_1_protempa;
grant create any type to user_1_protempa;
grant alter any type to user_1_protempa;
grant drop any type to user_1_protempa;
grant execute any type to user_1_protempa;
grant under any type to user_1_protempa;
grant create any trigger to user_1_protempa;
grant alter any trigger to user_1_protempa;
grant drop any trigger to user_1_protempa;
----

=== Create necessary tables in the data schema

[source,sql]
----
create table user_1_protempa.patient (
patient_key number(22,0) not null,
first_name varchar2(32),
last_name varchar2(32),
dob date ,
language varchar2(32),
marital_status varchar2(32),
race varchar2(32),
gender varchar2(16),
constraint patient_pk primary key (patient_key));

create table user_1_protempa.provider (
provider_key number(22,0) not null,
first_name varchar2(32),
last_name varchar2(32),
constraint provider_pk primary key (provider_key));

create table user_1_protempa.encounter (
encounter_key number(22,0) not null,
patient_key number(22,0) not null,
provider_key number(22,0) not null,
ts_start timestamp(4),
ts_end timestamp(4),
encounter_type varchar2(64),
discharge_disp varchar2(64),
constraint encounter_pk primary key (encounter_key));

create table user_1_protempa.cpt_event (
event_key varchar2(32) not null,
encounter_key number(22,0) not null,
ts_obx timestamp(4),
entity_id varchar2(128) not null,
constraint cpt_event_pk primary key (event_key));

create table user_1_protempa.icd9d_event (
event_key varchar2(32) not null,
encounter_key number(22,0) not null,
ts_obx timestamp(4),
entity_id varchar2(128) not null,
constraint icd9d_event_pk primary key (event_key));

create table user_1_protempa.icd9p_event (
event_key varchar2(32) not null,
encounter_key number(22,0) not null,
ts_obx timestamp(4),
entity_id varchar2(128) not null,
constraint icd9p_event_pk primary key (event_key));

create table user_1_protempa.meds_event (
event_key varchar2(32) not null,
encounter_key number(22,0) not null,
ts_obx timestamp(4),
entity_id varchar2(128) not null,
constraint meds_event_pk primary key (event_key));

create table user_1_protempa.labs_event (
event_key varchar2(32) not null,
encounter_key number(22,0) not null,
ts_obx timestamp(4),
entity_id varchar2(128) not null,
result_str varchar2(32),
result_num number(18,4),
units varchar2(16),
flag varchar2(8),
constraint labs_event_pk primary key (event_key));

create table user_1_protempa.vitals_event (
event_key varchar2(32) not null,
encounter_key number(22,0) not null,
ts_obx timestamp(4),
entity_id varchar2(128) not null,
result_str varchar2(32),
result_num number(18,4),
units varchar2(16),
flag varchar2(8),
constraint vitals_event_pk primary key (event_key));
----

== Update the Protempa configuration file

The Eureka! application uses the Protempa software to process the data uploaded
by the user. To allow Protempa to retrieve data from the data schema, the
Protempa configuration for the user must be updated. Each Eureka! user will
have a separate configuration file, located at /opt/cvrg_users/user<USER_ID>
/.protempa-configs/erat-diagnoses-direct. Substitute "USER_ID" with the
database ID of the user. Use the following code snippet for the user created
earlier. Substitute the "DB_HOST", "DB_NAME", and "PASSWORD" place-holders with
appropriate values.

----
[edu.emory.cci.registry.spreadsheet.dsb.RegistryVM_0]
dataSourceBackendId=AIW
databaseId = jdbc:oracle:thin:@DB_HOST:1521:DB_NAME
username = user_1_protempa
password = PASSWORD

[org.protempa.ksb.protege.LocalKnowledgeSourceBackend_1]
projectString = /opt/protempa/AIW.pprj
units = ABSOLUTE
----

== Set up database for i2b2

Eureka! requires an i2b2 database to function correctly. If your organization
does not already have an i2b2 instance to load data to, the following steps
will create the necessary users, tables, procedures, etc.

=== i2b2 Hive Setup

Create the i2b2 hive user. Please substitute "PASSWORD" with a secure password.

[source,sql]
----
create user i2b2hive identified by "PASSWORD"
default tablespace users
quota 1000M on users
temporary tablespace temp;
----

Grant appropriate privileges to the hive user.

[source,sql]
----
grant create session to i2b2hive;
grant create table to i2b2hive;
grant create view to i2b2hive;
grant create any index to i2b2hive;
grant create public synonym to i2b2hive;
grant drop public synonym to i2b2hive;
grant create sequence to i2b2hive;
grant create procedure to i2b2hive;
grant create any type to i2b2hive;
grant alter any type to i2b2hive;
grant drop any type to i2b2hive;
grant execute any type to i2b2hive;
grant under any type to i2b2hive;
grant create any trigger to i2b2hive;
grant alter any trigger to i2b2hive;
grant drop any trigger to i2b2hive;
----

Configure i2b2 Hive data upload process.

Edit the
*`/opt/sources/edu.harvard.i2b2.data/Release_1-5/NewInstall/Hivedata/db.properties`*
file to specify database properties. The following snippet shows the contents
of the file. Edit the "DB_HOST", "DB_NAME" place-holders to suit your
environment. The "PASSWORD" place-holder should be replaced with the password
selected in the previous step.

----
db.type=oracle
db.username=i2b2hive
db.password=PASSWORD
db.server=DB_HOST:1521:DB_NAME
db.driver=oracle.jdbc.driver.OracleDriver
db.url=jdbc:oracle:thin:@DB_HOST:1521:DB_NAME
----

Create the i2b2 Hive tables. Run the following ant command to create the
database tables needed by the i2b2 Hive module.

----
cd /opt/sources/edu.harvard.i2b2.data/Release_1-5/NewInstall/Hivedata/
ant -f data_build.xml create_hivedata_tables_release_1-5
----

Insert seed data for the i2b2 Hive module using the following ant command.

----
cd /opt/sources/edu.harvard.i2b2.data/Release_1-5/NewInstall/Hivedata/
ant -f data_build.xml db_hivedata_load_data
----

=== i2b2 Project Management Cell Setup

Create the i2b2 Project Management (PM) user. Please substitute "PASSWORD" with
a secure password.

[source,sql]
----
create user i2b2pm identified by "PASSWORD"
default tablespace users
quota 1000M on users
temporary tablespace temp;
----

Grant appropriate privileges to the PM user.

[source,sql]
----
grant create session to i2b2pm;
grant create table to i2b2pm;
grant create view to i2b2pm;
grant create any index to i2b2pm;
grant create public synonym to i2b2pm;
grant drop public synonym to i2b2pm;
grant create sequence to i2b2pm;
grant create procedure to i2b2pm;
grant create any type to i2b2pm;
grant alter any type to i2b2pm;
grant drop any type to i2b2pm;
grant execute any type to i2b2pm;
grant under any type to i2b2pm;
grant create any trigger to i2b2pm;
grant alter any trigger to i2b2pm;
grant drop any trigger to i2b2pm;
----

Configure the i2b2 PM data upload process. Edit the +
*`/opt/sources/edu.harvard.i2b2.data/Release_1-5/NewInstall/Pmdata/db.properties`* +
file to specify the database properties. The following code snippet shows the
contents of the file. Edit the "DB_HOST", "DB_NAME" place-holders to suit your
environment. The "PASSWORD" place-holder should be replaced with the password
selected in the previous step.

----
db.type=oracle
db.username=i2b2pm
db.password=PASSWORD
db.server=DB_HOST:1521:DB_NAME
db.driver=oracle.jdbc.driver.OracleDriver
db.url=jdbc:oracle:thin:@DB_HOST:1521:DB_NAME
----

Create the i2b2 PM tables and triggers by running the following ant command.

----
cd /opt/sources/edu.harvard.i2b2.data/Release_1-5/NewInstall/Pmdata/
ant -f data_build.xml create_pmdata_tables_release_1-5
ant -f data_build.xml create_triggers_release_1-5
----

Insert the seed data for the i2b2 PM module.

----
cd /opt/sources/edu.harvard.i2b2.data/Release_1-5/NewInstall/Pmdata/
ant -f data_build.xml db_pmdata_load_data
----

=== i2b2 Metadata Cell Setup

Create the i2b2 Metadata user. Please substitute "PASSWORD" with a secure
password.

[source,sql]
----
create user user_1_metadata identified by "PASSWORD"
default tablespace users
quota 1000M on users
temporary tablespace temp;
----

Grant appropriate privileges to the Metadata user.

[source,sql]
----
grant create session to user_1_metadata;
grant create table to user_1_metadata;
grant create view to user_1_metadata;
grant create any index to user_1_metadata;
grant create public synonym to user_1_metadata;
grant drop public synonym to user_1_metadata;
grant create sequence to user_1_metadata;
grant create procedure to user_1_metadata;
grant create any type to user_1_metadata;
grant alter any type to user_1_metadata;
grant drop any type to user_1_metadata;
grant execute any type to user_1_metadata;
grant under any type to user_1_metadata;
grant create any trigger to user_1_metadata;
grant alter any trigger to user_1_metadata;
grant drop any trigger to user_1_metadata;
----

Configure the i2b2 metadata upload process. Edit the +
*`/opt/sources/edu.harvard.i2b2.data/Release_1-5/NewInstall/Metadata/db.properties`*+
file to specify the database properties. The following code snippet shows the
contents of the file. Edit the "DB_HOST", "DB_NAME" place-holders to suit your
environment. The "PASSWORD" place-holder should be replaced with the password
selected in the previous step.

----
db.type=oracle
db.username=user_1_metadata
db.password=PASSWORD
db.server=DB_HOST:1521:DB_NAME
db.driver=oracle.jdbc.driver.OracleDriver
db.url=jdbc:oracle:thin:@DB_HOST:1521:DB_NAME
db.project=user_1_project
----

Create the i2b2 Metadata tables and triggers by running the following ant
command.

----
cd /opt/sources/edu.harvard.i2b2.data/Release_1-5/NewInstall/Metadata/
ant -f data_build.xml create_metadata_tables_release_1-5
----

Insert the seed data for the i2b2 Metadata module.

----
cd /opt/sources/edu.harvard.i2b2.data/Release_1-5/NewInstall/Metadata/
ant -f data_build.xml db_metadata_load_data
----

=== i2b2 Data Cell Setup

Create the i2b2 Data user. Please substitute "PASSWORD" with a secure password.

[source,sql]
----
create user user_1_data identified by "PASSWORD"
default tablespace users
quota 1000M on users
temporary tablespace temp;
----

Grant appropriate privileges to the Data user.

[source,sql]
----
grant create session to user_1_data;
grant create table to user_1_data;
grant create view to user_1_data;
grant create any index to user_1_data;
grant create public synonym to user_1_data;
grant drop public synonym to user_1_data;
grant create sequence to user_1_data;
grant create procedure to user_1_data;
grant create any type to user_1_data;
grant alter any type to user_1_data;
grant drop any type to user_1_data;
grant execute any type to user_1_data;
grant under any type to user_1_data;
grant create any trigger to user_1_data;
grant alter any trigger to user_1_data;
grant drop any trigger to user_1_data;
----

Configure the i2b2 data upload process. Edit the
*`/opt/sources/edu.harvard.i2b2.data/Release_1-5/NewInstall/Demodata/db.properties`*
file to specify the database properties. The following code snippet shows the
contents of the file. Edit the "DB_HOST", "DB_NAME" place-holders to suit your
environment. The "PASSWORD" place-holder should be replaced with the password
selected in the previous step.

----
db.type=oracle
db.username=user_1_data
db.password=PASSWORD
db.server=DB_HOST:1521:DB_NAME
db.driver=oracle.jdbc.driver.OracleDriver
db.url=jdbc:oracle:thin:@DB_HOST:1521:DB_NAME
db.project=user_1_project
----

Create the i2b2 Data tables and triggers by running the following ant command.

----
cd /opt/sources/edu.harvard.i2b2.data/Release_1-5/NewInstall/Demodata/
ant -f data_build.xml create_demodata_tables_release_1-5
ant -f data_build.xml create_procedures_release_1-5
----

Insert the seed data for the i2b2 Data module.

----
cd /opt/sources/edu.harvard.i2b2.data/Release_1-5/NewInstall/Demodata/
ant -f data_build.xml db_demodata_load_data
----

=== i2b2 Workdata Cell Setup

Create the i2b2 Workdata user. Please substitute "PASSWORD" with a secure
password.

[source,sql]
----
create user user_1_workdata identified by "PASSWORD"
default tablespace users
quota 1000M on users
temporary tablespace temp;
----

Grant appropriate privileges to the Workdata user.

[source,sql]
----
grant create session to user_1_workdata;
grant create table to user_1_workdata;
grant create view to user_1_workdata;
grant create any index to user_1_workdata;
grant create public synonym to user_1_workdata;
grant drop public synonym to user_1_workdata;
grant create sequence to user_1_workdata;
grant create procedure to user_1_workdata;
grant create any type to user_1_workdata;
grant alter any type to user_1_workdata;
grant drop any type to user_1_workdata;
grant execute any type to user_1_workdata;
grant under any type to user_1_workdata;
grant create any trigger to user_1_workdata;
grant alter any trigger to user_1_workdata;
grant drop any trigger to user_1_workdata;
----

Configure the i2b2 work data upload process. Edit the
*`/opt/sources/edu.harvard.i2b2.data/Release_1-5/NewInstall/Workdata/db.properties`*
file to specify the database properties. The following code snippet shows the
contents of the file. Edit the "DB_HOST", "DB_NAME" place-holders to suit your
environment. The "PASSWORD" place-holder should be replaced with the password
selected in the previous step.

----
db.type=oracle
db.username=user_1_workdata
db.password=PASSWORD
db.server=DB_HOST:1521:DB_NAME
db.driver=oracle.jdbc.driver.OracleDriver
db.url=jdbc:oracle:thin:@DB_HOST:1521:DB_NAME
db.project=user_1_project
----

Create the i2b2 Workdata tables and triggers by running the following ant
command.

----
cd /opt/sources/edu.harvard.i2b2.data/Release_1-5/NewInstall/Workdata/
ant -f data_build.xml create_workdata_tables_release_1-5
----

Insert the seed data for the i2b2 Workdata module.

----
cd /opt/sources/edu.harvard.i2b2.data/Release_1-5/NewInstall/Demodata/
ant -f data_build.xml db_workdata_load_data
----

== Configure i2b2

In order to use i2b2, configuration files must be updated to inform i2b2 of the
new users and passwords. The i2b2 install can be found in the /opt/apps/jboss/
default/server/default directory. Within that directory, the deploy and conf
sub-directories both contain files relevant to i2b2.

=== Configure Hive datasource

The i2b2hive data source is configured in the following files:

* /opt/apps/jboss/default/server/default/deploy/work-ds.xml
* /opt/apps/jboss/default/server/default/deploy/ont-ds.xml
* /opt/apps/jboss/default/server/default/deploy/crc-jms-ds.xml
* /opt/apps/jboss/default/server/default/conf/crcloaderapp/CRCLoaderApplicationContext.xml
* /opt/apps/jboss/default/server/default/conf/crcapp/CRCApplicationContext.xml

See the code snippets below for the contents of the file. Update the "DB_HOST"
and "DB_NAME" place-holders to suit your environment. The "PASSWORD"
place-holder should be replaced with the password chosen for the "i2b2hive"
user in the previous section.

.deploy/work-ds.xml
[source,xml]
----
<?xml version="1.0" encoding="UTF-8"?>
<datasources>
<local-tx-datasource>
<jndi-name>WorkplaceBootStrapDS</jndi-name>
<driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
<connection-url>jdbc:oracle:thin:@DB_HOST:1521:DB_NAME</connection-url>
<user-name>i2b2hive</user-name>
<password>PASSWORD</password>
</local-tx-datasource>
</datasources>
----

.deploy/ont-ds.xml
[source,xml]
----
<?xml version="1.0" encoding="UTF-8"?>
<datasources>
<local-tx-datasource>
<jndi-name>OntologyBootStrapDS</jndi-name>
<driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
<connection-url>jdbc:oracle:thin:@DB_HOST:1521:DB_NAME</connection-url>
<user-name>i2b2hive</user-name>
<password>PASSWORD</password>
</local-tx-datasource>
</datasources>
----

.deploy/crc-jms-ds.xml

[source,xml]
----
<?xml version="1.0" encoding="UTF-8"?>
<datasources>
<local-tx-datasource>
<jndi-name>DefaultDS</jndi-name>
<connection-url>jdbc:oracle:thin:@DB_HOST:1521:DB_NAME</connection-url>
<driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
<user-name>i2b2hive</user-name>
<password>PASSWORD</password>
<idle-timeout-minutes>1</idle-timeout-minutes>
<exception-sorter-class-name>
org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter
</exception-sorter-class-name>
<metadata>
<type-mapping>Oracle9i</type-mapping>
</metadata>
</local-tx-datasource>
</datasources>
----

.conf/crcloaderapp/CRCLoaderApplicationContext.xml

[source,sql]
----
...
<bean id="LoaderLookupDS"
class="org.apache.commons.dbcp.BasicDataSource"
destroy-method="close">
<property name="driverClassName"
value="oracle.jdbc.driver.OracleDriver"/>
<property name="url"
value="jdbc:oracle:thin:@DB_HOST:1521:DB_NAME"/>
<property name="username" value="i2b2hive"/>
<property name="password" value="PASSWORD"/>
</bean>
...
----

.conf/crcapp/CRCApplicationContext.xml

[source,xml]
----
...
<bean id="CRCDataSourceLookup"
class="org.apache.commons.dbcp.BasicDataSource"
destroy-method="close">
<property name="driverClassName"
value="oracle.jdbc.driver.OracleDriver"/>
<property name="url"
value="jdbc:oracle:thin:@DB_HOST:1521:DB_NAME"/>
<property name="username" value="i2b2hive"/>
<property name="password" value="PASSWORD"/>
</bean>
...
----

=== Configure Project Management datasource

The project management datasource is configured in the +
*`/opt/apps/jboss/default/server/default/deploy/pm-ds.xml`* +
file. Replace the "DB_HOST" and "DB_NAME" place-holders to suit your
environment. The "PASSWORD" place-holder should be replaced with the
password previously chosen for the "i2b2pm" user.

[source,xml]
----
<?xml version="1.0" encoding="UTF-8"?>
<datasources>
<local-tx-datasource>
<jndi-name>PMBootStrapDS</jndi-name>
<driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
<connection-url>jdbc:oracle:thin:@DB_HOST:1521:DB_NAME</connection-url>
<user-name>i2b2pm</user-name>
<password>PASSWORD</password>
</local-tx-datasource>
</datasources>
----


=== Configure the data, workdata, and metadata datasources

Configure the necessary datasources to allow i2b2 to store and
retrieve information about a project's data, metadata, and work
data. This information is configured in the +
*`/opt/apps/jboss/default/server/default/deploy/eureka-project-1-ds.xml`* +
file. Replace the "DB_HOST" and "DB_NAME"
place-holders with values appropriate for your environment. The
"PASSWORD" place-holders should be replaced with the passwords chosen
for the "user_1_data", "user_1_metadata", and "user_1_workdata" users
earlier in the set up process.  Also notice the "jndi-name" attributes
of the datasources. The JNDI names are used later when updating
information in the i2b2 PM schema to properly use the new
datasources. The datasources are used for one specific project, which
is assigned to one user. As a result, each i2b2 user will have a set
of data, metadata, and workdata schemas associated.

[source,xml]
----
<?xml version="1.0" encoding="UTF-8"?>
<datasources>

<!-- The data datasource -->
<local-tx-datasource>
<jndi-name>EurekaProject1DataDS</jndi-name>
<driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
<connection-url>jdbc:oracle:thin:@DB_HOST:1521:DB_NAME</connection-url>
<user-name>user_1_data</user-name>
<password>PASSWORD</password>
<idle-timeout-minutes>1</idle-timeout-minutes>
<exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter</exception-sorter-class-name>
<metadata>
<type-mapping>Oracle9i</type-mapping>
</metadata>
</local-tx-datasource>

<!-- The metadata datasource -->
<local-tx-datasource>
<jndi-name>EurekaProject1MetadataDS</jndi-name>
<driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
<connection-url>jdbc:oracle:thin:@DB_HOST:1521:DB_NAME</connection-url>
<user-name>user_1_metadata</user-name>
<password>PASSWORD</password>
</local-tx-datasource>

<!-- The workdata datasource -->
<local-tx-datasource>
<jndi-name>EurekaProject1WorkDS</jndi-name>
<driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
<connection-url>jdbc:oracle:thin:@DB_HOST:1521:DB_NAME</connection-url>
<user-name>user_1_workdata</user-name>
<password>PASSWORD</password>
</local-tx-datasource>

</datasources>
----


=== Create a new user in i2b2

To create a new user in i2b2, use the administration interface located at the
following URL. Please replace "your.company.com" with the appropriate IP
address or hostname of your EC2 instance.

----
https://your.company.com/i2b2/admin
----

The user name should be the same as the "email" field in the Eureka! user set
up. For example, 'super.user@exmaple.com' as previously used in this guide. As
more users are added to the Eureka! database, the same users need to be added
to i2b2. The "email" attribute in Eureka! will be the same as the username
attribute for i2b2 user. The passwords will also be the same for both systems,
because Eureka! utilizes the same hashing mechanism for storing passwords that
i2b2 uses.

=== Create a new project in i2b2

To create a new project in i2b2, use the administration interface at the
following URL. Please replace "your.company.com" with the appropriate IP
address or hostname of your EC2 instance.

----
https://your.company.com/i2b2/admin
----

The default username and password for the adminstrative user are "i2b2" and
"demouser", respectively. The project should be named "EurekaProject1", and the
path set to "/EurekaProject1".

Once the project has been created, use the adminstration interface to add the
user to the database. All permissions, except for "admin" and "editor" should
be set for the user.

=== Update i2b2 datasource names.

After the user and project have been created, the i2b2 database should be
updated in order to allow i2b2 to locate the datasources previously created.
The following SQL code will add the correct entries in the database to ensure
that the newly created project uses the datasources created earlier. This will
ensure that the data is retrieved and stored in the correct database schemas.

[source,sql]
----
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, c_db_tooltip,
c_comment, c_entry_date, c_change_date, c_status_cd)
values(
'i2b2demo', '/EurekaProject1/', '@', 'user_1_data',
'java:EurekaProject1DataDS', 'ORACLE', 'EurekaProject1DataDS',
null, null, null, null, null);

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, c_db_tooltip,
c_comment, c_entry_date, c_change_date, c_status_cd)
values(
'i2b2demo', 'EurekaProject1/', '@', 'user_1_metadata',
'java:EurekaProject1MetadataDS', 'ORACLE',
'EurekaProject1MetadataDS', null, null, null, null, null);

insert into i2b2hive.work_db_lookup(
c_domain_id,
c_project_path, c_owner_id, c_db_fullschema, c_db_datasource,
c_db_servertype, c_db_nicename, c_db_tooltip, c_comment,
c_entry_date, c_change_date, c_status_cd)
values (
'i2b2demo', 'EurekaProject1/', '@', 'user_1_workdata',
'java:EurekaProject1WorkDS', 'ORACLE',
'EurekaProject1WorkDS', null, null, null, null, null);
----

=== Add a new i2b2 metadata table

The Eureka! i2b2 loader process will store the metadata for a project in a
table called cardiovascularregistry. In order to allow the loader process to
store this data, the table must be created before running Eureka!. The
following SQL creates the table.

[source,sql]
----
create table user_1_metadata.cardiovascularregistry (
c_hlevel decimal(22) not null,
c_fullname varchar2(700) not null,
c_name varchar2(2000) not null,
c_synonym_cd char(1) not null,
c_visualattributes char(3) not null,
c_totalnum decimal(22),
c_basecode varchar2(50),
c_metadataxml clob,
c_facttablecolumn varchar2(50) not null,
c_tablename varchar2(50) not null,
c_columnname varchar2(50) not null,
c_columndatatype varchar2(50) not null,
c_operator varchar2(10) not null,
c_dimcode varchar2(700) not null,
c_comment clob,
c_tooltip varchar2(900),
update_date timestamp not null,
download_date timestamp,
import_date timestamp,
sourcesystem_cd varchar2(50),
valuetype_cd varchar2(50));
----

=== Update i2b2 metata table pointers

The table_access table in the i2b2 metadata schema controls which tables are
used when creating the metadata concept navigation tree in the user interface.
The following SQL code will update the database to use the new table for
metadata in the previous step.

[source,sql]
----
delete from user_1_metadata.table_access;

insert into user_1_metadata.table_access (
c_table_cd, c_table_name, c_protected_access, c_hlevel,
c_fullname, c_name, c_synonym_cd, c_visualattributes,
c_totalnum, c_basecode, c_metadataxml, c_facttablecolumn,
c_dimtablename, c_columnname, c_columndatatype, c_operator,
c_dimcode, c_comment, c_tooltip, c_entry_date,
c_change_date, c_status_cd, valuetype_cd)
values (
'cardiovascularregistry', 'CardiovascularRegistry', 'N', 0,
'\CardiovascularRegistry\', 'CardiovascularRegistry', 'N',
'CA ', null, null, '<Clob>', 'concept_cd',
'concept_dimension', 'concept_path', 'T', 'LIKE',
'\CardiovascularRegistry\', '<Clob>',
'CardiovascularRegistry', null, null, null, null);
----

Configure the Eureka! i2b2 loader.

Once the i2b2 database is properly set up, the i2b2 loader process in Eureka!
should be configured. The configuration file is located in the /opt/cvrg_users/
user<USER_ID> directory. The <USER_ID> place-holder in the directory name
corresponds to the database id of the user in the eurekaservice.users table.
Inside the directory, a conf.xml file can be found. The file contains
information about the i2b2 usernames and passwords to be used when uploading
information to the i2b2 database. The following is an example file.

In the file, replace the "DB_HOST", "DB_NAME", and "PASSWORD" place-holders
with the appropriate values for your environment.

[source,xml]
----
<?xml version="1.0" encoding="UTF-8"?>
<root name="cardiovascular">

<dictionary>
<entry key="observationFact"   value="batchInsert"/>
<entry key="metaTableName"     value="cardiovascularregistry"/>
<entry key="rootNodeName"      value="CardiovascularRegistry"/>
<entry key="serializeOntology" value="false"/>
<entry key="unSerializeOntology" value="true"/>
<entry key="sourceSystemCode"  value="cardiovascular"/>
<entry key="truncateTables"    value="true"/>
<entry key="protempaRecordAdapter"   value="edu.emory.cci.registry.cardiovascular.RecordAdapter"/>
</dictionary>
<database>
<dbschema key="dataschema" connect="jdbc:oracle:thin:@DB_HOST:1521:DB_NAME" user="user_1_data" passwd="PASSWORD"/>
<dbschema key="metaschema" connect="jdbc:oracle:thin:@DB_HOST:1521:DB_NAME" user="user_1_metadata" passwd="PASSWORD"/>
</database>
<filesystem>
<file key="cpt"     rw="" uri="/opt/cvrg_users/user1/output/cpt"/>
<file key="meds"    rw="" uri="/opt/cvrg_users/user1/output/meds"/>
<file key="labs"    rw="" uri="/opt/cvrg_users/user1/output/labs"/>
<file key="icd9d"   rw="" uri="/opt/cvrg_users/user1/output/icd9d"/>
<file key="icd9p"   rw="" uri="/opt/cvrg_users/user1/output/icd9p"/>
<file key="vital"   rw="" uri="/opt/cvrg_users/user1/output/vitals"/>
<file key="ontologyR" rw="r"  uri="/opt/cvrg_users/user1/output/ontR"/>
<file key="ontologyW" rw="rw" uri="/opt/cvrg_users/user1/output/ontW"/>
<set key="#fileSetZero">
<file key="meds"/>
<file key="cpt"/>
<file key="labs"/>
<file key="icd9d"/>
<file key="icd9p"/>
<file key="vital"/>
</set>
</filesystem>
<meta>
<!-- these are the large scale components of the tree. 'key' corresponds to ... -->
<!-- ks = knowledge source , pointer = root entity in protege -->
<branch key="vital" source="ks" pointer="VitalSign"       handlerClass="edu.emory.cci.registry.i2b2metareader.KSProxy" sourceSys="VITAL"/>
<branch key="meds"  source="ks" pointer="MED:medications" handlerClass="edu.emory.cci.registry.i2b2metareader.KSProxy" sourceSys="MED"/>
<branch key="cpt"   source="ks" pointer="CPTCode"         handlerClass="edu.emory.cci.registry.i2b2metareader.KSProxy" sourceSys="CPT"/>
<branch key="icd9d" source="ks" pointer="ICD9:Diagnoses"  handlerClass="edu.emory.cci.registry.i2b2metareader.KSProxy" sourceSys="ICD9D"/>
<branch key="icd9p" source="ks" pointer="ICD9:Procedures" handlerClass="edu.emory.cci.registry.i2b2metareader.KSProxy" sourceSys="ICD9P"/>
<branch key="labs"  source="ks" pointer="LAB:LabTest"     handlerClass="edu.emory.cci.registry.i2b2metareader.KSProxy" sourceSys="LAB"/>
<!-- vs = knowledge source: value set , pointer = entity in protege -->
<branch key="dschgDisp" source="vs" pointer="dischargeDisposition" handlerClass="edu.emory.cci.registry.i2b2metareader.KSValueSetProxy" sourceSys="DD"/>
<branch key="encType"   source="vs" pointer="type"                 handlerClass="edu.emory.cci.registry.i2b2metareader.KSValueSetProxy" sourceSys="ET"/>
<!-- fs = file , pointer = key name of file or fileset -->
<branch key="providers" source="fs" pointer="#fileSetZero" handlerClass="edu.emory.cci.registry.i2b2metareader.Provider"      sourceSys="PRV"/>
<!-- denovo means the handlerClass will create branch based on hard-coded, internal information , pointer = unused -->
</meta>
<i2b2metadatatree>
<!-- displayName = what shows up in the UI -->
<!-- skipGen = hack to ablate a series of redundant parent-child nodes (see source code) -->
<!-- branch = points to data structure declared above in meta node -->
<!-- metatable = corresponding table name in the meta schema -->

<!--  each folder must have a relation to a branch (in meta)  -->
<folder displayName="CPT Codes"             skipGen="2" branch="cpt"       metatable="cptCodes"/>
<folder displayName="ICD9 Diagnostic Codes" skipGen="2" branch="icd9d"     metatable="icd9diagnosticCodes"/>
<folder displayName="ICD9 Procedure Codes"  skipGen="2" branch="icd9p"     metatable="icd9procedureCodes"/>
<folder displayName="Laboratory Tests"      skipGen="2" branch="labs"      metatable="laboratoryTests"/>
<folder displayName="Discharge Disposition" skipGen="1" branch="dschgDisp" metatable="dischargeDisposition"/>
<folder displayName="Encounter Type"        skipGen="1" branch="encType"   metatable="encounterType"/>
<folder displayName="Providers"             skipGen="1" branch="providers" metatable="providers"/>
<folder displayName="Medication"            skipGen="3" branch="meds"      metatable="medication"/>
<folder displayName="Vital Signs"           skipGen="2" branch="vital"     metatable="vitalSigns"/>
</i2b2metadatatree>
<observations>
<!-- process nodes instruct the etl to sample & persist observation_fact records -->
<process file="meds"    handlerClass="edu.emory.cci.registry.i2b2datareader.FactMedication"/>
<process file="cpt"     handlerClass="edu.emory.cci.registry.i2b2datareader.FactCPT"/>
<process file="icd9d"   handlerClass="edu.emory.cci.registry.i2b2datareader.FactICD9D"/>
<process file="labs"    handlerClass="edu.emory.cci.registry.i2b2datareader.FactLaboratory"/>
<process file="icd9p"   handlerClass="edu.emory.cci.registry.i2b2datareader.FactICD9P"/>
<process file="vital"   handlerClass="edu.emory.cci.registry.i2b2datareader.FactVital"/>
<process file="#fileSetZero" handlerClass="edu.emory.cci.registry.i2b2datareader.FactProvider"/>
<process file="#fileSetZero" handlerClass="edu.emory.cci.registry.i2b2datareader.FactDischargeDisposition"/>
<process file="#fileSetZero" handlerClass="edu.emory.cci.registry.i2b2datareader.FactEncounterType"/>
</observations>
<mappings>
</mappings>
</root>
----

== Securing client-server communication

Because Eureka! deals with sensitive data (such as medical records), it uses
SSL encryption for HTTP (HTTPS) to communicate with the web browser. In order
for HTTPS to work correctly, an SSL certificate must be generate for the
Eureka! host. The following process will generate a self-signed certificate for
the host. If your institution has a valid SSL certificate already, you can skip
the self-signed certificate generation on move on to the configuration step.

=== Creating the SSL certificate.

Generate a self-signed certificate and key using the following commands. The
certificate will be valid for one year (365 days). Please replace the
"HOST_NAME" place-holder with the fully qualified domain name of your Eureka!
server.

----
cd /root

openssl req -newkey rsa:2048 -keyout HOST_NAME.key -nodes -x509 -days
365 -out HOST_NAME.crt
----

=== Configure Apache HTTPD

The Apache HTTPD server on the Eureka! host is already configured to use SSL.
However, the SSL certificate used must be configured to match the hostname. Use
the following commands to copy the certificate and key to the correct location.
Please replace the "HOST_NAME" place-holder with the fully qualified domain
name of the Eureka! host.

----
cd /root

cp HOST_NAME.key /etc/pki/tls/private/HOST_NAME.key

cp HOST_NAME.crt /etc/pki/tls/certs/HOST_NAME.crt
----

Once the files are in the proper location, Apache HTTPD must be configured to
use the files. The configuration is done in the /etc/httpd/conf.d/ssl.conf
file. Simply update the SSLCertificateFile and SSLCertificateKeyFile parameters
in the file. Please replace the "HOST_NAME" place-holder with the fully
qualified domain name of the Eureka! host.

----
...
SSLCertificateFile /etc/pki/tls/certs/HOST_NAME.crt
SSLCertificateKeyFile /etc/pki/tls/private/HOST_NAME.key
...
----
