/
How to reset label sequences?
Got feedback or spotted a mistake?
Leave a comment at the end of this page or email contact@krishagni.com
How to reset label sequences?
- Poornima Govindrao
- Saurav Singh
- Prayuja Teli (Unlicensed)
Owned by Poornima Govindrao
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 (Registration) | SYS_UID | To check if there is a value already present select * from key_seq_generator
where
key_type = 'Registration' and
key_value = 'SYS_UID';
To update the value (if a value is already present): update
key_seq_generator
set
key_sequence_id = <max registration number like 1000>
where
key_type = 'Registration' and
key_value = 'SYS_UID';
To insert a value (if no value is present) (MySQL): insert into key_seq_generator
(key_type, key_value, key_sequence_id)
values
('Registration', 'SYS_UID', <max registration number like 1000>); |
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>);
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>)
);
If the token is used for create KIT-NAMES for visits, the use below query to find the existing counter: select * from key_seq_generator where key_value = 'Unknown_<EVENT_UID>'
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 | PPI_UID | Check for presence of any existing sequences select
count(*)
from
key_seq_generator
where
key_type = 'PPI_UID' and
key_value in (
select
distinct visit.collection_protocol_reg_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 cpr.activity_status = 'Active'
);
insert into key_seq_generator
(identifier, key_type, key_value, key_sequence_id)
select
key_generator_seq.nextval, 'PPI_UID', t.key_value, t.key_sequence_id
from (
select
visit.COLLECTION_PROTOCOL_REG_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
cpr.activity_status = 'Active'
group by
visit.COLLECTION_PROTOCOL_REG_ID
) t;
insert into key_seq_generator
(key_type, key_value, key_sequence_id)
select
'PPI_UID',
visit.COLLECTION_PROTOCOL_REG_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 cpr.activity_status = 'Active'
group by
visit.COLLECTION_PROTOCOL_REG_ID;
update
key_seq_generator
set
key_sequence_id = <max visit number for COLLECTION_PROTOCOL_REG_ID>
where
key_type = 'PPI_UID' and
key_value = '<COLLECTION_PROTOCOL_REG_ID>';
delete from
key_seq_generator
where
key_type = 'PPI_UID' and
key_value in (
select
distinct visit.collection_protocol_reg_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 cpr.activity_status = 'Active'
); |
Visit | PUID | Check for presence of any existing sequences select
count(*)
from
key_seq_generator
where
key_type = 'VISIT_NAME_PUID' and
key_value in (
select
distinct cpr.participant_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 cpr.activity_status = 'Active'
);
insert into key_seq_generator
(identifier, key_type, key_value, key_sequence_id)
select
key_generator_seq.nextval, 'VISIT_NAME_PUID', t.key_value, t.key_sequence_id
from (
select
cpr.participant_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 cpr.activity_status = 'Active'
group by
cpr.participant_id
) t;
insert into key_seq_generator
(key_type, key_value, key_sequence_id)
select
'VISIT_NAME_PUID',
cpr.participant_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 cpr.activity_status = 'Active'
group by
cpr.participant_id;
update
key_seq_generator
set
key_sequence_id = <total number of visits for PARTICIPANT_ID across all CPs>
where
key_type = 'VISIT_NAME_PUID' and
key_value = '<PARTICIPANT_ID>';
delete from
key_seq_generator
where
key_type = 'VISIT_NAME_PUID' and
key_value in (
select
distinct cpr.participant_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 cpr.activity_status = 'Active'
); |
Visit | VISIT_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
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 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; |
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;
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_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; |
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
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 | Note: If the table MySQLupdate
key_seq_generator
set (key_value,key_type,key_sequence_id)
set key_sequence_id = (<max specimen number of specific type under a visit>)
where
key_value = '<Visit Name>_<Specimen_Type_ID>' and key_type = 'VISIT_SP_TYPE_UID';
select
count(*)
from
key_seq_generator
where
key_type = 'VISIT_SP_TYPE_UID' and
key_value in (
select
concat(visit.name,'_',spec.specimen_type_id)
from
catissue_specimen_coll_group visit
inner join catissue_specimen spec
on visit.identifier=spec.specimen_collection_group_id
where
visit.activity_status = 'Active' and
spec.collection_protocol_id in (<cp_ids>)
group by
visit.name, spec.specimen_type_id);
Delete pre-existing sequences delete from
key_seq_generator
where
key_type = 'VISIT_SP_TYPE_UID' and
key_value in (
select
concat(visit.name,'_',spec.specimen_type_id)
from
catissue_specimen_coll_group visit
inner join catissue_specimen spec
on visit.identifier=spec.specimen_collection_group_id
where
visit.activity_status = 'Active' and
spec.collection_protocol_id in (<cp_ids>)
group by
visit.name, spec.specimen_type_id);
insert into key_seq_generator
(identifier, key_type, key_value, key_sequence_id)
select
key_generator_seq.nextval, 'VISIT_SP_TYPE_UID', t.key_value, t.key_sequence_value
from (
select
concat(visit.name,'_',spec.specimen_type_id) as key_value,
count(spec.label) as key_sequence_value
from
catissue_specimen_coll_group visit
inner join catissue_specimen spec
on visit.identifier=spec.specimen_collection_group_id
where
visit.activity_status = 'Active' and
spec.collection_protocol_id in (<cp_ids>)
group by
visit.name, spec.specimen_type_id;
) t;
MySQL: Recreate sequences for all specimens insert into key_seq_generator
(key_type, key_value, key_sequence_id)
select 'VISIT_SP_TYPE_UID' as key_type,
concat(visit.name,'_',spec.specimen_type_id) as key_value,
count(spec.label) as key_sequence_id
from
catissue_specimen_coll_group visit
inner join catissue_specimen spec
on visit.identifier=spec.specimen_collection_group_id
where
visit.activity_status = 'Active' and
spec.collection_protocol_id in (<cp_ids>)
group by
visit.name, spec.specimen_type_id; |
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
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 specimensinsert 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));
Resetting existing sequence: UPDATE key_seq_generator
SET key_sequence_id = <max number of specimen already created for the PPID + Specimen Type combination>
WHERE key_type = 'PPI_SPEC_TYPE_UID' AND key_value = '<PPID>_<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>);
|
Specimen | PRIMARY_SPMN_SP_TYPE_ABBR_UID | Delete pre-existing sequences delete from
key_seq_generator
where
key_type = 'PRIMARY_SPMN_SP_TYPE_ABBR_UID' and
key_value in (
select
concat(t.specimen_id, concat('_', t.abbr)) as key_value
from (
select
p.identifier as specimen_id,
pv_props.value as abbr,
count(*) as specimens
from
catissue_specimen p
inner join catissue_specimen_hierarchy h on h.ancestor_id = p.identifier
inner join catissue_specimen d on d.identifier = h.descendent_id
inner join catissue_permissible_value pv on pv.identifier = d.specimen_type_id
inner join os_pv_props pv_props on pv_props.pv_id = pv.identifier
where
p.collection_protocol_id in (<cp_id>) and
p.lineage = 'New' and
p.activity_status != 'Disabled' and
d.activity_status != 'Disabled' and
pv_props.name = 'abbreviation'
group by
p.identifier, pv_props.value
) t
)MySQL: Recreate sequences insert into
key_seq_generator(key_type, key_value, key_sequence_id)
select
'PRIMARY_SPMN_SP_TYPE_ABBR_UID' as key_type,
concat(t.specimen_id, concat('_', t.abbr)) as key_value,
t.specimens as key_sequence_id
from (
select
p.identifier as specimen_id,
pv_props.value as abbr,
count(*) as specimens
from
catissue_specimen p
inner join catissue_specimen_hierarchy h on h.ancestor_id = p.identifier
inner join catissue_specimen d on d.identifier = h.descendent_id
inner join catissue_permissible_value pv on pv.identifier = d.specimen_type_id
inner join os_pv_props pv_props on pv_props.pv_id = pv.identifier
where
p.collection_protocol_id in (<cp_ids>) and
p.lineage = 'New' and
p.activity_status != 'Disabled' and
d.activity_status != 'Disabled' and
pv_props.name = 'abbreviation'
group by
p.identifier, pv_props.value
) t |
Specimen | SPEC_CP_UID | Resetting existing key sequence ID update
key_seq_generator
set
key_sequence_id = <one less than the next expected number>
where
key_value = '<CP_ID>'
and key_type = 'SPEC_CP_UID';Adding the new key sequence ID insert into
key_seq_generator (
key_value, key_sequence_id, key_type
)
VALUES
('<CP_ID>', <one less than the next expected number>, 'SPEC_CP_UID'); |
, multiple selections available,
Related content
Label Examples
Label Examples
More like this
PPID Format
PPID Format
Read with this
How to reset PSPEC_UID sequences?
How to reset PSPEC_UID sequences?
More like this
Specimen Label Format
Specimen Label Format
Read with this
How to temporarily turn off auto-generated labels?
How to temporarily turn off auto-generated labels?
More like this
Visit Name Format
Visit Name Format
Read with this
Got feedback or spotted a mistake?
Leave a comment at the end of this page or email contact@krishagni.com