How to fix corrupted specimen event forms?

Sometimes, non-system specimen event forms are corrupted, when they are edited using the form designer. This results in errors during data entry, query etc. This happens mostly to the specimen event forms that were created using OpenSpecimen v1.1. To fix the problem, follow the steps given below:

1. Run the query to get the digest and date when the form was last imported:

   MySQL: select l.form_id, l.md5_digest, l.executed_on from os_import_forms_log l inner join dyextn_containers f on f.identifier = l.form_id where f.name = '<corrupted event form name>' order by l.executed_on desc limit 1
   Oracle: select * from ( select tab.*, rownum rnum from ( select l.form_id, l.md5_digest, l.executed_on from os_import_forms_log l inner join dyextn_containers f on f.identifier = l.form_id where f.name = '<corrupted event form name>' order by l.executed_on desc ) tab where rownum <= 1 ) where rnum >= 1;

2. Update the form digest so that it can be re-imported or restored to its working status:

   update os_import_forms_log set md5_digest = concat(md5_digest, '_updated') where md5_digest = '<first row of the query in step 1>'; commit;

3. Trick the importer that the form was not modified since last import:

   update dyextn_containers set last_modify_time = null where identifier = <form_id of the first row of the query in step 1>; commit;

4. Restart OpenSpecimen.

5. Skip to step 8 if your database is MySQL.

6. Oracle Only: Add a couple of helper functions to replace the contents of a BLOB column.

   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;

7. Oracle Only:

   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;

8. Make the default specimen event forms as user managed forms.

   MySQL: update dyextn_containers set xml = replace( xml, '<managedTables>true</managedTables>', '<managedTables>false</managedTables>' ) where name in ( 'SpecimenCellReviewEvent', 'SpecimenCheckInCheckOutEvent', 'SpecimenEmbeddedEvent', 'SpecimenFluidEvent', 'SpecimenProcedureEvent', 'SpecimenSpunEvent', 'SpecimenFixedEvent', 'SpecimenMolecularReviewEvent', 'SpecimenTissueReviewEvent', 'SpecimenFrozenEvent' ); commit;
   Oracle: update dyextn_containers set xml = convert_to_blob( replace( convert_to_clob(xml), '<managedTables>true</managedTables>', '<managedTables>false</managedTables>' ) ) where name in ( 'SpecimenCellReviewEvent', 'SpecimenCheckInCheckOutEvent', 'SpecimenEmbeddedEvent', 'SpecimenFluidEvent', 'SpecimenProcedureEvent', 'SpecimenSpunEvent', 'SpecimenFixedEvent', 'SpecimenMolecularReviewEvent', 'SpecimenTissueReviewEvent', 'SpecimenFrozenEvent' ); commit;

9. On successful execution of all the above steps, restart OpenSpecimen.

10. You should be now able to edit the specimen event forms using form designer.