roger-mahler
commented
4 years ago This script, added as 20191212_DML_UPDATE_SAMPLE_ALT_REFS must be included as a post-action in the Bugs Import Checklist.
begin;
do $$
begin
begin
insert into tbl_alt_ref_types (alt_ref_type_id, alt_ref_type, description)
values (9, 'Bugs sample code', 'Unique identifier for (physical) samples in the BugsCEP database')
on conflict do nothing;
insert into tbl_sample_alt_refs (alt_ref, physical_sample_id, alt_ref_type_id)
select bugs_identifier, sead_reference_id, 9
from bugs_import.bugs_trace b
where bugs_table = 'TSample'
on conflict (sample_alt_ref_id) do update
set alt_ref = excluded.alt_ref,
physical_sample_id = excluded.physical_sample_id;
exception when sqlstate 'GUARD' then
raise notice 'ALREADY EXECUTED';
end;
end $$;
commit;
In BugsCEP, unique identifier for a sample is TSample.SampleCODE. This is stored in bugs_trace during import but not stored in the main SEAD database. SampleCODE needs storing in SEAD as: tbl_sample_alt_refs.alt_ref with type "Database identifier" (new alt_ref_type)
This is needed for tracing samples through SEAD to Bugs, for example when Francesca creates a matrix of all insect samples.