...

Via direct database inserts

High-level Steps:

1. Create a new table in OpenSpecimen with the necessary data
2. The new table should have a foreign-key to either participant, visit or specimen table.
3. Open the respective XML from the below folder
4. Edit the XML to include all the columns in the table
5. Refer to the existing XMLs to get an idea for the synxtax for different datatypes.with required fields and foreign key to either Participant Registration / Visit / Specimen table. 
6. Load necessary data into table using any of available database tools.
7. Edit Participant Registration / Visit / Specimen query form to specify metadata of the table and its fields added in step 1.

Detailed Steps with Example:

Goal:

We want to capture vital statistics and radiology diagnosis, if any, for every participant visit. Also, we would like to make this captured data available to researchers via OpenSpecimen query interface.

Path to Achieve Goal:

1. Create a table with required fields and foreign key to OpenSpecimen's Visit table as illustrated in below example:
   
   

   Code Block
   language sql
   create table CUSTOM_VISIT_STATS_DIAGNOSIS( IDENTIFIER BIGINT NOT NULL AUTO_INCREMENT, VISIT_ID BIGINT NOT NULL, DIASTOLIC_BP SMALLINT, SYSTOLIC_BP SMALLINT, RADIOLOGY_DIAGNOSIS VARCHAR(255), PRINCIPAL_DIAGNOSIS VARCHAR(255), FOREIGN KEY (VISIT_ID) REFERENCES CATISSUE_SPECIMEN_COLL_GROUP(IDENTIFIER) );

2. Load data into custom visit stats and diagnosis table with relevant participant visit IDs and field values. Assuming we've all our data in a CSV file visit_diagnosis.csv, we'll use following MySQL command to import data into custom table
   
   

   Code Block
   language sql
   LOAD DATA INFILE '<my-sql-files>/visit_diagnosis.csv'INTO TABLE CUSTOM_VISIT_STAT_DIAGNOSIS FIELDS TERMINATED BY ',' IGNORE 1 LINES;

3. Add following query metadata in file $TOMCAT_HOME/webapps/openspecimen/WEB-INF/classes/query-forms/scg.xml just below the </row> corresponding to visit sub-form extensions.
   
   

   Code Block
   language xml
   <row> <subForm> <name>customVisitDiagnosis</name> <!-- unique name within form --> <udn>customVisitDiagnosis</udn> <caption>Visit Stats and Diagnosis</caption> <table>CUSTOM_VISIT_STAT_DIAGNOSIS</table> <!-- custom table name --> <foreignKey>VISIT_ID</foreignKey> <!-- visit foreign key in custom table --> <parentKey>IDENTIFIER</parentKey> <row> <numberField> <name>id</name> <udn>id</udn> <caption>Diagnosis ID</caption> <column>IDENTIFIER</column> </numberField> <numberField> <name>diastolicBp</name> <udn>diastolicBp</udn> <caption>Diastolic BP</caption> <column>DIASTOLIC_DP</column> </numberField> <numberField> <name>systolicBp</name> <udn>systolicBp</udn> <caption>Systolic BP</caption> <column>SYSTOLIC_BP</column> </numberField> <dropDown> <name>radiologyDiagnosis</name> <udn>radiologyDiagnosis</udn> <caption>Radiology Diagnosis</caption> <column>RADIOLOGY_DIAGNOSIS</column> <options> <!-- dropdown values are picked from values available in custom table --> <sql>select distinct radiology_diagnosis from CUSTOM_VISIT_STAT_DIAGNOSIS where radiology_diagnosis is not null</sql> </options> </dropDown> <dropDown> <name>principalDiagnosis</name> <udn>principalDiagnosis</udn> <caption>Principal Diagnosis</caption> <column>PRINCIPAL_DIAGNOSIS</column> <options> <!-- dropdown values are picked from values available in custom table --> <sql>select distinct principal_diagnosis from CUSTOM_VISIT_STAT_DIAGNOSIS where principal_diagnosis is not null</sql> </options> </dropDown> </row> </subForm> </row>

4. Save the file and restart OpenSpecimen

On successful restart of OpenSpecimen, you should be able to see your custom visit stats and diagnosis fields appearing in Visit form of query interface. You can use these fields like any normal OpenSpecimen query fields.

Points to Remember:

1. The query forms are overwritten during upgrade. Therefore a copy of the customised form XML should be retained for merging after the upgrade is done.
2. The query form XML files for Participant registration and Specimen are cpr.xml and specimen.xml respectively.
3. Above example metadata should take care of 80% use cases. For syntax of using other field types, refer existing query form XMLs located in $TOMCAT_HOME/webapps/openspecimen/WEB-INF/classes/query-forms directory.
4. Going forward, in future releases, we plan to allow users define their custom tables and related query metadata in plugin, which the query interface can pick automagically and make them available for querying purpose.