How to fix missing collection and received event fields?

It has been observed that when the OpenSpecimen database is upgraded to versions v6.2 or higher, both the API and UI display blank values for some fields of the collection and received events. This issue is observed largely on legacy databases (i.e. databases upgraded from caTissue or OpenSpecimen v1.1) using Oracle. These databases have managedTables=false for the system events like collection event, received event, disposal event, and transfer event. When managedTables=false, it means the table schema will be managed by the DE/custom forms library. As a result, when we changed the field types of collection container/procedure to the PV driven fancy controls, DE added new table columns to capture the data of these fields. This is wrong.

For all system events, the corresponding form metadata should have managedForms = true. This will let DE know that the table schema will be managed by the host application (OpenSpecimen). DE will be responsible for storing and retrieving data from these tables.

To fix the problem, follow the below steps:

1. Create convenience functions to convert a column from BLOB to CLOB and vice-versa. These functions are needed to update the specimen events form metadata. Execute the below SQLs:

   CREATE OR REPLACE FUNCTION convert_to_clob(l_blob BLOB) return CLOB IS l_clob CLOB; l_dest_offset NUMBER := 1; l_src_offset NUMBER := 1; l_lang_context NUMBER := dbms_lob.default_lang_ctx; l_warning NUMBER; BEGIN dbms_lob.createtemporary(l_clob, TRUE); dbms_lob.converttoclob( dest_lob => l_clob, src_blob => l_blob, amount => dbms_lob.lobmaxsize, dest_offset => l_dest_offset, src_offset => l_src_offset, blob_csid => nls_charset_id('AL32UTF8'), lang_context => l_lang_context, warning => l_warning ); RETURN l_clob; END convert_to_clob;
   CREATE OR REPLACE FUNCTION convert_to_blob(l_clob CLOB) RETURN BLOB IS l_blob BLOB; l_dest_offset NUMBER := 1; l_src_offset NUMBER := 1; l_lang_context NUMBER := dbms_lob.default_lang_ctx; l_warning NUMBER; BEGIN dbms_lob.createtemporary(l_blob, TRUE); dbms_lob.converttoblob( dest_lob => l_blob, src_clob => l_clob, amount => dbms_lob.lobmaxsize, dest_offset => l_dest_offset, src_offset => l_src_offset, blob_csid => nls_charset_id('AL32UTF8'), lang_context => l_lang_context, warning => l_warning ); RETURN l_blob; END convert_to_blob;

2. Update the specimen events form metadata by executing the following DML:

   UPDATE dyextn_containers SET xml = convert_to_blob( replace( convert_to_clob(xml), '<managedTables>false</managedTables>', '<managedTables>true</managedTables>' ) ) WHERE name in ( 'SpecimenCollectionEvent', 'SpecimenReceivedEvent', 'SpecimenTransferEvent', 'SpecimenDisposalEvent' );

3. Copy the collection and received event field values to the correct table columns (as expected by the app):

4. Manipulate the forms import log to allow OpenSpecimen re-import the events form metadata:

5. Restart OpenSpecimen

6. On successful restart, the collection and received event field values should appear in both the API response and on the UI pages.