Got feedback or spotted a mistake?

Leave a comment at the end of this page or email contact@krishagni.com

Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 24 Next »

OpenSpecimen allows to auto-generate labels based on various tokens. For more details, refer to Label configuration.

When a new center starts using OpenSpecimen, the legacy data might be in other sources like Excel, different DB, etc. Once the legacy data is imported, the sequences of new participants and specimens need to start from the last number of legacy data. To achieve this, the administrator has to reset the sequences based on the tokens used. Currently, this is achieved using SQLs. Below are some examples:

Please take a backup of the database before running any SQLs

If there are sequence records for the same key_type and key_value, it needs to be deleted before new entries are created


LevelTokenSQL to reset sequence
SpecimenSYS_UID
Oracle
insert into key_seq_generator
  (identifier, key_type, key_value, key_sequence_id)
values
  (key_generator_seq.nextval, 'Specimen', 'SYS_UID', <max specimen number like 1000>);
MySQL
insert into key_seq_generator
  (key_type, key_value, key_sequence_id)
values
  ('Specimen', 'SYS_UID', <max specimen number like 1000>);


Note: If the table key_seq_generator already has a row for the combination ('Specimen', 'SYS_UID') then update the row with the new sequence value using below SQL.


update
  key_seq_generator
set
  key_sequence_id = <max specimen number like 1000>
where
  key_type = 'Specimen' and
  key_value = 'SYS_UID';
ParticipantCP_UID(n)
Oracle
insert into key_seq_generator
  (identifier, key_type, key_value, key_sequence_id)
values
  (key_generator_seq.nextval, 'PPID', <CP ID>, <max PPID number like 1000>);


MySQL
insert into key_seq_generator
  (key_type, key_value, key_sequence_id)
values
  ('PPID', <CP ID>, <max PPID number like 1000>);


Note: If the table key_seq_generator already contains the row for the combination ('PPID', <CP ID>) then update that row with the new key sequence value using below SQL:

update
  key_seq_generator
set
  key_sequence_id = <max PPID number like 1000>
where
  key_type = 'PPID' and
  key_value = '<CP_ID>';
Visit

EVENT_UID(n)

Check for presence of any existing sequences
select
  count(*) 
from 
  key_seq_generator 
where 
  key_type = 'EVENT_UID' and 
  key_value in (
    select
      concat(cpr.protocol_participant_id, concat('_', visit.collection_protocol_event_id))
    from
      catissue_coll_prot_reg cpr
      inner join catissue_specimen_coll_group visit
        on visit.collection_protocol_reg_id = cpr.identifier
    where
      cpr.collection_protocol_id in (<cp_id_list>)
  );
Oracle: Recreate sequences for all visits
insert into key_seq_generator
  (identifier, key_type, key_value, key_sequence_id)
select
  key_generator_seq.nextval, 'EVENT_UID', t.key_value, t.key_sequence_id
from (
  select 
    concat(cpr.protocol_participant_id, concat('_', visit.collection_protocol_event_id)) as key_value,
    count(visit.identifier) as key_sequence_id
  from
    catissue_coll_prot_reg cpr
    inner join catissue_specimen_coll_group visit
      on visit.collection_protocol_reg_id = cpr.identifier
  where
    cpr.collection_protocol_id in (<cp_id_list>) and
    visit.collection_protocol_event_id is not null
  group by
    concat(cpr.protocol_participant_id, concat('_', visit.collection_protocol_event_id))
) t


MySQL: : Recreate sequences for all visits
insert into key_seq_generator
  (key_type, key_value, key_sequence_id)
select
  'EVENT_UID',
  concat(cpr.protocol_participant_id, concat('_', visit.collection_protocol_event_id)),
  count(visit.identifier)
from
  catissue_coll_prot_reg cpr
  inner join catissue_specimen_coll_group visit
    on visit.collection_protocol_reg_id = cpr.identifier
where
  cpr.collection_protocol_id in (<cp_id_list>)
group by
  concat(cpr.protocol_participant_id, concat('_', visit.collection_protocol_event_id));


Note: If the table key_seq_generator contains the row for ('EVENT_ID', <PPID>_<EVENT_ID>) combination then update the key sequence value using below SQL

update
  key_seq_generator
set
  key_sequence_id = <max visit number like 1000>
where
  key_type = 'EVENT_UID' and
  key_value = '<PPID>_<EVENT_ID>';
Delete all pre-existing sequences
delete from 
  key_seq_generator 
where 
  key_type = 'EVENT_UID' and 
  key_value in (
    select
      concat(cpr.protocol_participant_id, concat('_', visit.collection_protocol_event_id))
    from
      catissue_coll_prot_reg cpr
	  inner join catissue_specimen_coll_group visit
	    on visit.collection_protocol_reg_id = cpr.identifier
    where
      cpr.collection_protocol_id in (<cp_id_list>)
  );
VisitVISIT_UID
Check for existing sequence
select
   count(*)
from
   key_seq_generator 
where
   key_type = 'VISIT_UID' 
   and key_value in 
   (
      select
         visit.identifier
      from
         catissue_coll_prot_reg cpr 
         inner join
            catissue_specimen_coll_group visit 
            on visit.collection_protocol_reg_id = cpr.identifier 
         inner join
            catissue_specimen spec 
            on visit.identifier = spec.specimen_collection_group_id 
      where
         cpr.collection_protocol_id in (<cp_id_list>)
   );
Delete the existing sequence
delete
      from
         key_seq_generator 
      where
         key_type = 'VISIT_UID' 
         and key_value in 
         (
            select
               visit.identifier
            from
               catissue_coll_prot_reg cpr 
               inner join
                  catissue_specimen_coll_group visit 
                  on visit.collection_protocol_reg_id = cpr.identifier 
               inner join
                  catissue_specimen spec 
                  on visit.identifier = spec.specimen_collection_group_id 
            where
               cpr.collection_protocol_id in (<cp_id_list>)
         );
Insert new sequence
insert into
   key_seq_generator (key_type, key_value, key_sequence_id) 
   select
      'VISIT_UID',
      visit.identifier,
      count(visit.identifier) 
   from
      catissue_coll_prot_reg cpr 
      inner join
         catissue_specimen_coll_group visit 
         on visit.collection_protocol_reg_id = cpr.identifier 
      inner join
         catissue_specimen spec 
         on visit.identifier = spec.specimen_collection_group_id 
   where
      cpr.collection_protocol_id in (<cp_id_list>)
      and spec.lineage = 'New' 
   group by
      visit.identifier;
VisitSYS_UID
Oracle
insert into key_seq_generator
  (identifier, key_type, key_value, key_sequence_id)
values
  (key_generator_seq.nextval, 'Visit', 'SYS_UID', <max visit number like 1000>);
MySQL
insert into key_seq_generator
  (key_type, key_value, key_sequence_id)
values
  ('Visit', 'SYS_UID', <max visit number like 1000>);

Note: If the table key_seq_generator contains a row for the combination ('Visit', 'SYS_UID') then update the key sequence value using below SQL:

update
  key_seq_generator
set
  key_sequence_id = <max visit number like 1000>
where
  key_type = 'Visit' and
  key_value = 'SYS_UID';
SpecimenPSPEC_UID
Check for presence of any pre-existing sequences
select
  count(*)
from
  key_seq_generator
where
  key_type = 'PSPEC_UID' and 
  key_value in (
    select
      parent.identifier
    from
      catissue_specimen parent
    where
      parent.collection_protocol_id in (<cp_id_list>) and
      parent.collection_status='Collected' and
      parent.activity_status='Active' and
      parent.label is not null
  )
Delete pre-existing sequences
delete from
  key_seq_generator
where
  key_type = 'PSPEC_UID' and 
  key_value in (
    select
      parent.identifier
    from
      catissue_specimen parent
    where
      parent.collection_protocol_id in (<cp_id_list>) and
      parent.collection_status='Collected' and
      parent.activity_status='Active' and
      parent.label is not null
  )
Oracle: Recreate sequences for all parent specimens
insert into key_seq_generator
  (identifier, key_type, key_value, key_sequence_id)
select
  key_generator_seq.nextval, 'PSPEC_UID', t.key_value, t.key_sequence_value
from (
  select
    parent.identifier as key_value, count(child.identifier) as key_sequence_value 
  from 
    catissue_specimen parent 
    inner join catissue_specimen child 
      on parent.identifier=child.parent_specimen_id
  where
    parent.collection_protocol_id in (<cp_id_list>) and
    parent.collection_status='Collected' and
    parent.activity_status='Active' and
    child.activity_status='Active' and 
    parent.label is not null 
  group by 
    parent.identifier
) t;


MySQL: Recreate sequences for all parent specimens
insert into key_seq_generator
  (key_type, key_value, key_sequence_id)
select
  'PSPEC_UID', t.key_value, t.key_sequence_value
from (
  select
    parent.identifier as key_value, count(child.identifier) as key_sequence_value 
  from 
    catissue_specimen parent 
    inner join catissue_specimen child 
      on parent.identifier=child.parent_specimen_id
  where
    parent.collection_status='Collected' and
    parent.activity_status='Active' and
    child.activity_status='Active' and 
    parent.label is not null 
  group by 
    parent.identifier
) t;
SpecimenCP_PPI_UID
Oracle
insert into key_seq_generator
  (identifier, key_type, key_value, key_sequence_id)
select
  key_generator_seq.nextval, 'CP_PPI_UID', t.key_value, t.key_sequence_value
from (
  select
    concat(cpr.collection_protocol_id, concat('_', cpr.protocol_participant_id)) as key_value,
    count(specimen.identifier) as key_sequence_value
  from
    catissue_coll_prot_reg cpr
    inner join catissue_specimen_coll_group visit
      on visit.collection_protocol_reg_id = cpr.identifier 
    inner join catissue_specimen specimen 
      on specimen.specimen_collection_group_id = visit.identifier
  where
    cpr.collection_protocol_id in (<CP IDs>) and
    specimen.lineage = 'New'
  group by 
    concat(cpr.collection_protocol_id, concat('_', cpr.protocol_participant_id))
) t


MySQL
insert into key_seq_generator
  (key_type, key_value, key_sequence_id)
select
  'CP_PPI_UID', t.key_value, t.key_sequence_value
from (
  select
    concat(cpr.collection_protocol_id, concat('_', cpr.protocol_participant_id)) as key_value,
    count(specimen.identifier) as key_sequence_value
  from
    catissue_coll_prot_reg cpr
    inner join catissue_specimen_coll_group visit
      on visit.collection_protocol_reg_id = cpr.identifier 
    inner join catissue_specimen specimen 
      on specimen.specimen_collection_group_id = visit.identifier
  where
    cpr.collection_protocol_id in (<CP IDs>) and
    specimen.lineage = 'New'
  group by 
    concat(cpr.collection_protocol_id, concat('_', cpr.protocol_participant_id))
) t
Specimen

VISIT_SP_TYPE_UID

MySQL
Insert into key_seq_generator
(key_value,key_type,
key_sequence_id) values ('<Visit Name>_<Specimen_type>','VISIT_SP_TYPE_UID', <max specimen number like 1000>);
Oracle
Insert into key_seq_generator
(identifier,key_value,key_type,
key_sequence_id) values (key_generator_seq.nextval,'<Visit Name>_<Specimen_type>','VISIT_SP_TYPE_UID', <max specimen number like 1000>);
Specimen

PPI_SPEC_TYPE_UID

Check for presence of any pre-existing sequences
select
   count(*)
from
   key_seq_generator 
where
   key_type = 'PPI_SPEC_TYPE_UID' 
   and key_value in 
   (
      select
         concat(cpr.protocol_participant_id, concat('_', spec.specimen_type_id)) 
      from
         catissue_coll_prot_reg cpr 
         inner join
            catissue_specimen_coll_group visit 
            on visit.collection_protocol_reg_id = cpr.identifier 
         inner join
            catissue_specimen spec 
            on visit.identifier = spec.specimen_collection_group_id 
      where
         cpr.collection_protocol_id in (<cp_id_list>) 
   );
Delete pre-existing sequences
delete
from
   key_seq_generator 
where
   key_type = 'PPI_SPEC_TYPE_UID' 
   and key_value in 
   (
      select
         concat(cpr.protocol_participant_id, concat('_', spec.specimen_type_id)) 
      from
         catissue_coll_prot_reg cpr 
         inner join
            catissue_specimen_coll_group visit 
            on visit.collection_protocol_reg_id = cpr.identifier 
         inner join
            catissue_specimen spec 
            on visit.identifier = spec.specimen_collection_group_id 
      where
         cpr.collection_protocol_id in (<cp_id_list>) 
   );
Note: Below SQLs are being executed for the label format set at the primary specimen level. If the token is used at derived or aliquot level change the spec.lineage accordingly 
MySQL: : Recreate sequences for all primary specimens
insert into
   key_seq_generator (key_type, key_value, key_sequence_id) 
   select
      'PPI_SPEC_TYPE_UID',
      concat(cpr.protocol_participant_id, concat('_', spec.specimen_type_id)),
      count(cpr.protocol_participant_id) 
   from
      catissue_coll_prot_reg cpr 
      inner join
         catissue_specimen_coll_group visit 
         on visit.collection_protocol_reg_id = cpr.identifier 
      inner join
         catissue_specimen spec 
         on visit.identifier = spec.specimen_collection_group_id 
   where
      cpr.collection_protocol_id in (<cp_id_list>)
      and spec.lineage = 'New' 
   group by
      concat(cpr.protocol_participant_id, concat('_', spec.specimen_type_id));
Distribution ProtocolDP_UID
Oracle
insert into key_seq_generator
  (identifier, key_type, key_value, key_sequence_id)
values
  (key_generator_seq.nextval, 'DP_UID', <DP ID>, <max distributed specimen count>);
MySQL
insert into key_seq_generator
  (key_type, key_value, key_sequence_id)
values
  ('DP_UID', <DP ID>, <max distributed specimen count>);

Note : <DP ID> value will be system identifier of that distribution protocol which can be found in the URL after clicking on DP.

  • No labels
Got feedback or spotted a mistake?

Leave a comment at the end of this page or email contact@krishagni.com