Relationship between Collection Protocols and Dynamic Extensions in the database

[bob_lange]

How does a dynamic extension map to the collection protocol(s) in the database? In the application, it seems that a dynamic extension is associated with one or more collection protocols, but I’m having a hard time figuring out that relationship in the database.

[ahegade]

Form can be attached to either Participant, Scg or specimen.

Accordingly they are specified in the CATISSUE_FORM_CONTEXT and the ENTITY_LEVEL column describes to which level its been attached.

Once the data entry is done for the particular form.
The entry is made in CATISSUE_FORM_RECORD_ENTRY, it has column called OBJECT_ID which will be mapped with the CATISSUE_COLL_PROT_REG which has COLLECTION_PROTOCOL_ID as one of its column, from there it can be traced to which Collection Protocol its been associated by referring to CATISSUE_COLLECTION_PROTOCOL.

For Participant level forms.
Object_id of CATISSUE_FORM_RECORD_ENTRY is nothing but cpr id of CATISSUE_COLL_PROT_REG.

For Scg level forms,
Object_id refers to IDENTIFIER of CATISSUE_SPECIMEN_COLL_GROUP which in turn has cpr id, which can be mapped to CATISSUE_COLL_PROT_REG

For Specimen level forms,
Object_id refers to CATISSUE_SPECIMEN, from there get SPECIMEN_COLLECTION_GROUP_ID to know to which Scg the specimen belongs. And from CATISSUE_SPECIMEN_COLL_GROUP it can be traced to Collection Protocol like how its explained for the Scg level forms.

[pgovindrao]

Hello Bob,

To clarify, the details Akshay has provided is as per OS v1.0 DE DB structure. The database structure for DE in caTissue Plus v3.0 was different. If you need that, we will have to get back to you on that.

Thanks,
Poornima Govindrao
Krishagni Solutions Pvt. Ltd.

Biobank LIMS trusted by the world | OpenSpecimen

[bob_lange]

Yes. I do need it for Plus v3.0. Thanks!

[vpawar]

Hello @bob_lange

Very sorry for late response.

caTissue Plus v30 was using NCI’s dynamic extension module. Instead of trying to explain tables, I’ll give query to list all forms along with protocols using them.

Query:

select 
  c.identifier as form_id, 
  c.caption as form_name, 
  cp.title as collection_protocol, 
  dam.name as object_type 
from 
  dyextn_container c 
  inner join dyextn_abstract_form_context fc on fc.container_id = c.identifier 
  left join catissue_cp_studyformcontext cfc on cfc.study_form_context_id = fc.identifier 
  left join dyextn_entity_map map on map.container_id = c.identifier 
  left join catissue_collection_protocol cp on cp.identifier = cfc.collection_protocol_id 
  left join dyextn_abstract_metadata dam on dam.identifier = map.static_entity_id 

1. Object_type column specifies whether the form is used as extension of Participant/Specimen/SCG
2. The form is not in use when both collection_protocol and object_type are null
3. The form is used by all CPs when collection_protocol field is null

I hope above query helps to unravel connection of DE with CP at database level.

Thank you.

[bob_lange]

Yes. That was very helpful. I was able to take that a step further to map to the data elements (or controls) within each DE. However, I’m now a bit confused as to how I can then map this to the data collected by the DE (for each specimen of SCG or participant). Can you point me in the correct direction with that?

[vpawar]

TL;DR

Hello Bob,

I’ll try to illustrate solution to your problem with an example.

Let’s assume you know form id, and entity (CPR/SCG/Specimen) for which you want to find form/DE records.

There are 3 steps involved in reaching the end goal.

Step 1:

Goal: Find out name of table that stores form/DE data

Given a form id, which can be obtained using query listed in above comment, below query will help find out table name. Importance of selecting entity id will be evident in step 3.2.

select 
  c.caption as form_name, e.id as entity_id, dp.name as table_name
from 
  dyextn_container c 
  inner join dyextn_abstract_entity e on c.abstract_entity_id = e.id 
  inner join dyextn_table_properties tp on tp.abstract_entity_id = e.id 
  inner join dyextn_database_properties dp on dp.identifier = tp.identifier 
where 
  c.identifier = 1; # Replace 1 with whatever form id you're interested in

Example output:

+---------------+-----------+------------+
| form_name     | entity_id | table_name |
+---------------+-----------+------------+
| LabAnnotation |      1283 | DE_E_1283  |
+---------------+-----------+------------+

Step 2:

Goal: Find out how static entity records are linked to DE data. Find record IDs to look-up DE records

Given form id, and object id (for example specimen id or cpr id or scg id), below query lists DE form record ids, which can be used to look-up into DE form table obtained in step 1.

select
  re.identifier as record_id, cpr.identifier as cpr_id
from 
  dyextn_container c 
  inner join dyextn_abstract_form_context afc on afc.container_id = c.identifier
  inner join dyextn_abstract_record_entry re on re.abstract_form_context_id = afc.identifier
  inner join catissue_participant_rec_ntry p_re on p_re.identifier = re.identifier
  inner join catissue_coll_prot_reg cpr on cpr.participant_id = p_re.participant_id
where 
  c.identifier = 1 and cpr.identifier = 84; # Replace 1 and 84 with your values

Example output

+-----------+--------+
| record_id | cpr_id |
+-----------+--------+
|         2 |     84 |
+-----------+--------+

####Step 3

Goal: Fetch form data using record ID obtained in step 2

Three sub-steps

####Step 3.1

Find entity IDs of static entities - Participant, SpecimenCollectionGroup, and Specimen. These are 3 entities to which DE forms are attached. Entity IDs are useful in forming look-up column name in step 3.2

select 
  dam.identifier static_entity_id, dam.name as static_entity 
from 
  dyextn_abstract_entity e 
  inner join dyextn_abstract_metadata dam on dam.identifier = e.id 
where 
  dam.name in (
    'edu.wustl.catissuecore.domain.Participant', 
    'edu.wustl.catissuecore.domain.SpecimenCollectionGroup', 
    'edu.wustl.catissuecore.domain.Specimen'
  )

Example output:

+------------------+-------------------------------------------------------+
| static_entity_id | static_entity                                         |
+------------------+-------------------------------------------------------+
|                4 | edu.wustl.catissuecore.domain.Specimen                |
|              379 | edu.wustl.catissuecore.domain.SpecimenCollectionGroup |
|              844 | edu.wustl.catissuecore.domain.Participant             |
+------------------+-------------------------------------------------------+

####Step 3.2

Create look-up column name for fetching form records. Look-up column names have following format:

dyextn_as_<static_entity_id>_<form_entity_id>

For knowing static_entity_id, and form_entity_id, refer step 3.1 and step 1 respectively.

In our example, look-up column name will be: dyextn_as_844_1283

844 is entity ID of Participant and 1283 is entity ID of LabAnnotation form.

####Step 3.3

Obtain form record.

Equipped with all required metadata, we proceed to query DE form data table to obtain required record.

select * from de_e_1283 where dyextn_as_844_1283 = 2

Here integer 2 is record ID that we obtained in step 2. We obtained table name and look-up column names in step 1 and step 3.2 respectively.

If you feel this is all very complicated and unwarranted, then welcome to OpenSpecimen v1.0

Thanks.

Vinayak