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
Level | Token | SQL to reset sequence |
---|---|---|
Specimen | SYS_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 update key_seq_generator set key_sequence_id = <max specimen number like 1000> where key_type = 'Specimen' and key_value = 'SYS_UID'; |
Participant | CP_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 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 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>) ); |
Visit | SYS_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 update key_seq_generator set key_sequence_id = <max visit number like 1000> where key_type = 'Visit' and key_value = 'SYS_UID'; |
Specimen | PSPEC_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; |
Specimen | CP_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>) ); 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 Protocol | DP_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. |