Versions Compared

Old Version 6

changes.mady.by.user Vinayak Pawar

Saved on

compared with

New Version Current

changes.mady.by.user Prayuja Teli

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

Via Custom forms and bulk upload

  1. Create a custom form via a form builder that contains the fields you want to include.
  2. Hook with the participant, specimen or visit, and one or more collection protocols.
  3. Go to "Import" in the "Collection protocols" list
  4. Download the BO template for the new custom form.
  5. Use the BO CSV file to import data into OpenSpecimen.
  6. Learn more about bulk import here: Bulk Operations (CSV Import)

Via direct database inserts

High-level

...

steps:

  1. Create a table with required fields and foreign key keys to either Participant Registration / Visit / Specimen table. 
  2. Load necessary data into a table using any of the available database tools.
  3. Edit Participant Registration / Visit / Specimen query form to specify metadata of the table and its fields added in step 1.

...

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

...

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

    Code Block
    languagesql
    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 tables 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
    languagesql
    LOAD DATA INFILE '<my-sql-files>/visit_diagnosis.csv'INTO TABLE
  CUSTOM_VISIT_STAT_DIAGNOSIS
FIELDS TERMINATED BY ','
IGNORE 1 LINES;


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

    Code Block
    languagexml
    <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 -->
    <primaryKey>IDENTIFIER</primaryKey>        <!-- this is optional and need not be specified if PK column name is IDENTIFIER -->
    <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 the successful restart of OpenSpecimen, you should be able to see your custom visit stats and diagnosis fields appearing in the Visit form of query interface. You can use these fields like any normal OpenSpecimen query fields.

...

  1. The query forms are overwritten during an upgrade. Therefore a copy of the customised customized form XML should be retained for merging after the upgrade is done. This will be enhanced in the future versions to make it upgrade proof.
  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 the 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 to define their custom tables and related query metadata in a plugin, which the query interface can pick automatically and make them available for querying purposepurposes.