In /i2b2/data/ folder

CRC

Open the file: /i2b2/data/Crcdata/scripts/crc_create_query_{db}.sql  and run the last three relevant sections (upgrading from 1.7.09c all the way to 1.7.12):

For example, for SQL server:

--==============================================================
-- Database Script to upgrade CRC from 1.7.09c to 1.7.10 
--==============================================================

-- New column added to support new SQL breakdowns - roles based access

alter table QT_QUERY_RESULT_TYPE add USER_ROLE_CD VARCHAR(255);

--==============================================================
-- Database Script to upgrade CRC from 1.7.10 to 1.7.11 
--==============================================================

insert into QT_PRIVILEGE(PROTECTION_LABEL_CD, DATAPROT_CD, HIVEMGMT_CD) values ('SETFINDER_QRY_PROTECTED','DATA_PROT','USER')

--==============================================================
-- Database Script to upgrade CRC from 1.7.11 to 1.7.12 
--==============================================================

alter table QT_QUERY_RESULT_TYPE add CLASSNAME VARCHAR(200)
;
update QT_QUERY_RESULT_TYPE set CLASSNAME='edu.harvard.i2b2.crc.dao.setfinder.QueryResultPatientSetGenerator' where NAME='PATIENTSET'
;
update QT_QUERY_RESULT_TYPE set CLASSNAME='edu.harvard.i2b2.crc.dao.setfinder.QueryResultEncounterSetGenerator' where NAME='PATIENT_ENCOUNTER_SET'
;
update QT_QUERY_RESULT_TYPE set CLASSNAME='edu.harvard.i2b2.crc.dao.setfinder.QueryResultPatientCountGenerator' where NAME='PATIENT_COUNT_XML'
;
update QT_QUERY_RESULT_TYPE set CLASSNAME='edu.harvard.i2b2.crc.dao.setfinder.QueryResultGenerator' where NAME='PATIENT_GENDER_COUNT_XML'
;
update QT_QUERY_RESULT_TYPE set CLASSNAME='edu.harvard.i2b2.crc.dao.setfinder.QueryResultGenerator' where NAME='PATIENT_VITALSTATUS_COUNT_XML'
;
update QT_QUERY_RESULT_TYPE set CLASSNAME='edu.harvard.i2b2.crc.dao.setfinder.QueryResultGenerator' where NAME='PATIENT_RACE_COUNT_XML'
;
update QT_QUERY_RESULT_TYPE set CLASSNAME='edu.harvard.i2b2.crc.dao.setfinder.QueryResultGenerator' where NAME='PATIENT_AGE_COUNT_XML'
;
update QT_QUERY_RESULT_TYPE set CLASSNAME='edu.harvard.i2b2.crc.dao.setfinder.QueryResultPatientSQLCountGenerator' where NAME='PATIENT_LOS_XML'
;
update QT_QUERY_RESULT_TYPE set CLASSNAME='edu.harvard.i2b2.crc.dao.setfinder.QueryResultPatientSQLCountGenerator' where NAME='PATIENT_TOP20MEDS_XML'
;
update QT_QUERY_RESULT_TYPE set CLASSNAME='edu.harvard.i2b2.crc.dao.setfinder.QueryResultPatientSQLCountGenerator' where NAME='PATIENT_TOP20DIAG_XML'
;
update QT_QUERY_RESULT_TYPE set CLASSNAME='edu.harvard.i2b2.crc.dao.setfinder.QueryResultPatientSQLCountGenerator' where NAME='PATIENT_INOUT_XML'
;

Open the file: /i2b2/data/Hivedata/scripts/upgrade_{db}_i2b2hive_tables.sql  and run the last required section:

For example, for SQL server:

CREATE TABLE HIVE_CELL_PARAMS ( 
ID INT NOT NULL,
DATATYPE_CD VARCHAR(50) NULL,
CELL_ID VARCHAR(50) NOT NULL,
PARAM_NAME_CD VARCHAR(200) NOT NULL,
VALUE VARCHAR(MAX) NULL,
CHANGE_DATE DATETIME NULL,
ENTRY_DATE DATETIME NULL,
CHANGEBY_CHAR VARCHAR(50) NULL,
STATUS_CD VARCHAR(50) NULL,
CONSTRAINT HIVE_CE__PK PRIMARY KEY(ID)
);


INSERT INTO HIVE_CELL_PARAMS(ID, DATATYPE_CD, CELL_ID, PARAM_NAME_CD, VALUE, CHANGE_DATE, ENTRY_DATE, CHANGEBY_CHAR, STATUS_CD)
VALUES(33, 'T', 'CRC', 'queryprocessor.jndi.queryinfolocal', 'ejb.querytool.QueryInfoLocal', NULL, NULL, NULL, 'A');
INSERT INTO HIVE_CELL_PARAMS(ID, DATATYPE_CD, CELL_ID, PARAM_NAME_CD, VALUE, CHANGE_DATE, ENTRY_DATE, CHANGEBY_CHAR, STATUS_CD)
VALUES(31, 'T', 'CRC', 'queryprocessor.jndi.querymanagerlocal', 'ejb.querytool.QueryManagerLocal', NULL, NULL, NULL, 'A');
INSERT INTO HIVE_CELL_PARAMS(ID, DATATYPE_CD, CELL_ID, PARAM_NAME_CD, VALUE, CHANGE_DATE, ENTRY_DATE, CHANGEBY_CHAR, STATUS_CD)
VALUES(37, 'T', 'CRC', 'queryprocessor.jndi.querymanagerremote', 'ejb.querytool.QueryManager', NULL, NULL, NULL, 'A');
INSERT INTO HIVE_CELL_PARAMS(ID, DATATYPE_CD, CELL_ID, PARAM_NAME_CD, VALUE, CHANGE_DATE, ENTRY_DATE, CHANGEBY_CHAR, STATUS_CD)
VALUES(61, 'T', 'ONT', 'applicationName', 'Ontology Cell', NULL, NULL, NULL, 'A');
INSERT INTO HIVE_CELL_PARAMS(ID, DATATYPE_CD, CELL_ID, PARAM_NAME_CD, VALUE, CHANGE_DATE, ENTRY_DATE, CHANGEBY_CHAR, STATUS_CD)
VALUES(63, 'T', 'CRC', 'applicationName', 'CRC Cell', NULL, NULL, NULL, 'A');
INSERT INTO HIVE_CELL_PARAMS(ID, DATATYPE_CD, CELL_ID, PARAM_NAME_CD, VALUE, CHANGE_DATE, ENTRY_DATE, CHANGEBY_CHAR, STATUS_CD)
VALUES(62, 'T', 'ONT', 'applicationVersion', '1.7', NULL, NULL, NULL, 'A');
INSERT INTO HIVE_CELL_PARAMS(ID, DATATYPE_CD, CELL_ID, PARAM_NAME_CD, VALUE, CHANGE_DATE, ENTRY_DATE, CHANGEBY_CHAR, STATUS_CD)
VALUES(64, 'T', 'CRC', 'applicationVersion', '1.7', NULL, NULL, NULL, 'A');
INSERT INTO HIVE_CELL_PARAMS(ID, DATATYPE_CD, CELL_ID, PARAM_NAME_CD, VALUE, CHANGE_DATE, ENTRY_DATE, CHANGEBY_CHAR, STATUS_CD)
VALUES(16, 'T', 'CRC', 'edu.harvard.i2b2.crc.analysis.queue.large.jobcheck.timemills', '60000', NULL, NULL, NULL, 'A');
INSERT INTO HIVE_CELL_PARAMS(ID, DATATYPE_CD, CELL_ID, PARAM_NAME_CD, VALUE, CHANGE_DATE, ENTRY_DATE, CHANGEBY_CHAR, STATUS_CD)
VALUES(14, 'T', 'CRC', 'edu.harvard.i2b2.crc.analysis.queue.large.maxjobcount', '1', NULL, NULL, NULL, 'A');
INSERT INTO HIVE_CELL_PARAMS(ID, DATATYPE_CD, CELL_ID, PARAM_NAME_CD, VALUE, CHANGE_DATE, ENTRY_DATE, CHANGEBY_CHAR, STATUS_CD)
VALUES(13, 'T', 'CRC', 'edu.harvard.i2b2.crc.analysis.queue.large.timeoutmills', '43200000', NULL, NULL, NULL, 'A');
INSERT INTO HIVE_CELL_PARAMS(ID, DATATYPE_CD, CELL_ID, PARAM_NAME_CD, VALUE, CHANGE_DATE, ENTRY_DATE, CHANGEBY_CHAR, STATUS_CD)
VALUES(15, 'T', 'CRC', 'edu.harvard.i2b2.crc.analysis.queue.medium.jobcheck.timemills', '60000', NULL, NULL, NULL, 'A');
INSERT INTO HIVE_CELL_PARAMS(ID, DATATYPE_CD, CELL_ID, PARAM_NAME_CD, VALUE, CHANGE_DATE, ENTRY_DATE, CHANGEBY_CHAR, STATUS_CD)
VALUES(12, 'T', 'CRC', 'edu.harvard.i2b2.crc.analysis.queue.medium.maxjobcount', '4', NULL, NULL, NULL, 'A');
INSERT INTO HIVE_CELL_PARAMS(ID, DATATYPE_CD, CELL_ID, PARAM_NAME_CD, VALUE, CHANGE_DATE, ENTRY_DATE, CHANGEBY_CHAR, STATUS_CD)
... (shortened)

Ontology

Open the file: /i2b2/data/Metadata/scripts/ont_{db}.sql  and run the script:

For example, for SQL server:

alter table TABLE_ACCESS add C_ONTOLOGY_PROTECTION VARCHAR(MAX);

Note: For ACT, you'll want to apply this statement to both your local i2b2 ACT ontology, and your SHRINE ACT ontology (if you use two separate i2b2 projects).

PM

Open the file: /i2b2/data/Pmdata/scripts/upgrade_{db}_i2b2pm_tables.sql  and run the last relevant section (upgrading from 1.7.09c):

For example, for SQL server:

ALTER TABLE PM_USER_LOGIN
DROP PRIMARY KEY;

CREATE INDEX PM_USER_LOGIN_IDX ON PM_USER_LOGIN(USER_ID, ENTRY_DATE);

Example

Properties files are stored at /opt/wildfly-{version}.Final/standalone/configuration/ on Linux and c:\opt\wildfly-{version}.Final\standalone\configuration\ on Windows. The table can be edited with a SQL editor in hive_cell_params.