Why do specimen hierarchical queries return incorrect results?

The specimen hierarchical queries use the table catissue_specimen_hierarchy to establish specimens' ancestors and descendants. To keep the hierarchy table in sync with the master table catissue_specimen, OpenSpecimen uses a database trigger facility so that whenever a specimen record is added or updated, the changes are reflected in the hierarchy table as well.

The hierarchical queries return incorrect results when the hierarchy table is out of sync with the master table. This typically happens when the trigger is missing from the OpenSpecimen schema/database.

Remedy:

Step #1: Recreate the trigger

Using below SQL, recreate the trigger in OpenSpecimen schema/database. Ensure to change the statement delimiter from; to something different like //

This ensures subsequent changes in the master table are propagated to the hierarchy table.

create trigger trg_specimen_replication after insert on catissue_specimen
  for each row 
  begin
    if (new.parent_specimen_id is not null ) then
      insert into catissue_specimen_hierarchy 
        (ancestor_id, descendent_id) 
      select 
        csh.ancestor_id, new.identifier 
      from 
        catissue_specimen_hierarchy csh 
      where 
        csh.descendent_id = new.parent_specimen_id;
    end if;

    insert into catissue_specimen_hierarchy (ancestor_id, descendent_id) values (new.identifier, new.identifier); 

  end;
//

create or replace trigger tgr_specimen_replication after insert on catissue_specimen
  for each row
  begin
    if (:new.parent_specimen_id is not null ) then
      insert into catissue_specimen_hierarchy
        (ancestor_id, descendent_id) 
      select 
        csh.ancestor_id, :new.identifier 
      from 
        catissue_specimen_hierarchy csh 
      where 
        csh.descendent_id = :new.parent_specimen_id;
    end if;
  
    insert into catissue_specimen_hierarchy (ancestor_id, descendent_id) values (:new.identifier, :new.identifier);
  end;
//

Step 2: Resynchronise the hierarchy table 

When executing this step, care should be taken that no data entry is in progress. Otherwise, the results are undefined.

• Log in as a super administrator
• From Home page, navigate to REST API Invoker (Home → Extras → REST API Invoker)
• Choose the HTTP method as POST
• Type the API URL as http[s]://<host>:<port>/openspecimen/rest/ng/tools/resync-specimens-hierarchy
• Leave the Body empty/blank
• Click on Send

You should receive an HTTP 200 OK response. The resync task runs in the background and the user is notified by email on its completion.

The specimen hierarchy table is now ready for use in various default and custom reports involving hierarchy queries.