How to reset label sequences?

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

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 `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';`
Participant (Registration)	SYS_UID	To check if there is a value already present To update the value (if a value is already present): To insert a value (if no value is present) (MySQL):
Participant	CP_UID(n)	Oracle MySQL 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:
Visit	EVENT_UID(n)	Check for presence of any existing sequences Oracle: Recreate sequences for all visits MySQL: : Recreate sequences for all visits 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 Delete all pre-existing sequences
Visit	PPI_UID	Check for presence of any existing sequences Oracle: Recreate sequences for all visits MySQL: : Recreate sequences for all visits Note: If the table `key_seq_generator` contains the row for `('PPI_UID', <COLLECTION_PROTOCOL_REG_ID>)` combination then update the key sequence value using below SQL Delete all pre-existing sequences
Visit	PUID	Check for presence of any existing sequences Oracle: Recreate sequences for all visits MySQL: : Recreate sequences for all visits Note: If the table `key_seq_generator` contains the row for `('VISIT_NAME_PUID', <PARTICIPANT_ID>)` combination then update the key sequence value using below SQL Delete all pre-existing sequences
Visit	VISIT_UID	Check for existing sequence Delete the existing sequence Insert new sequence
Visit	SYS_UID	Oracle MySQL 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:
Specimen	PSPEC_UID	Check for presence of any pre-existing sequences Delete pre-existing sequences Oracle: Recreate sequences for all parent specimens MySQL: Recreate sequences for all parent specimens
Specimen	CP_PPI_UID	Oracle MySQL
Specimen	VISIT_SP_TYPE_UID	Note: If the table `key_seq_generator` contains the row for `('VISIT_SP_TYPE_UID', <Visit Name> , and <Specimen_Type_ID>)` combination then update the key sequence value using the below SQL MySQL Check for presence of any pre-existing sequences Delete pre-existing sequences Oracle: Recreate sequences for all specimens MySQL: Recreate sequences for all specimens
Specimen	PPI_SPEC_TYPE_UID	Check for presence of any pre-existing sequences Delete pre-existing sequences 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
Distribution Protocol	DP_UID	Oracle MySQL Note : <DP ID> value will be system identifier of that distribution protocol which can be found in the URL after clicking on DP.
Specimen	PRIMARY_SPMN_SP_TYPE_ABBR_UID	Delete pre-existing sequences MySQL: Recreate sequences
Specimen	SPEC_CP_UID	Resetting existing key sequence ID Adding the new key sequence ID

MySQL

MySQL: : Recreate sequences for all primary specimens