def find_or_create_site(db: DatabaseSession, identifier: str,
details: dict) -> Any:
"""
Select encounter site by *identifier*, or insert it if it doesn't exist.
"""
LOG.debug(f"Looking up site «{identifier}»")
site = db.fetch_row(
"""
select site_id as id, identifier
from warehouse.site
where identifier = %s
""", (identifier, ))
if site:
LOG.info(f"Found site {site.id} «{site.identifier}»")
else:
LOG.debug(f"Site «{identifier}» not found, adding")
data = {
"identifier": identifier,
"details": Json(details),
}
site = db.fetch_row(
"""
insert into warehouse.site (identifier, details)
values (%(identifier)s, %(details)s)
returning site_id as id, identifier
""", data)
LOG.info(f"Created site {site.id} «{site.identifier}»")
return site
def find_or_create_target(db: DatabaseSession, identifier: str,
control: bool) -> Any:
"""
Select presence_absence test target by *identifier*, or insert it if it doesn't exist.
"""
LOG.debug(f"Looking up target «{identifier}»")
target = db.fetch_row(
"""
select target_id as id, identifier
from warehouse.target
where identifier = %s
""", (identifier, ))
if target:
LOG.info(f"Found target {target.id} «{target.identifier}»")
else:
LOG.debug(f"Target «{identifier}» not found, adding")
data = {"identifier": identifier, "control": control}
target = db.fetch_row(
"""
insert into warehouse.target (identifier, control)
values (%(identifier)s, %(control)s)
returning target_id as id, identifier
""", data)
LOG.info(f"Created target {target.id} «{target.identifier}»")
return target
def update_sample(db: DatabaseSession,
identifier: str = None,
collection_identifier: str = None,
additional_details: dict = None) -> Any:
"""
Find sample by *identifier* and update with any *additional_details*.
The provided *additional_details* are merged (at the top-level only) into
the existing sample details, if any.
Raises an :class:`SampleNotFoundError` if there is no sample known by
*identifier*.
"""
if identifier:
LOG.debug(f"Looking up sample with identifier «{identifier}»")
sample = db.fetch_row("""
select sample_id as id, identifier, details
from warehouse.sample
where identifier = %s
for update
""", (identifier,))
elif collection_identifier:
LOG.debug(f"Looking up sample with collection_identifier «{collection_identifier}»")
sample = db.fetch_row("""
select sample_id as id, collection_identifier as identifier, details
from warehouse.sample
where collection_identifier = %s
for update
""", (collection_identifier,))
if not sample:
LOG.error(f"No sample found with identifier «{identifier}» or collection identifier «{collection_identifier}»")
raise SampleNotFoundError(identifier)
LOG.info(f"Found sample {sample.id} «{sample.identifier}»")
if additional_details:
LOG.info(f"Updating sample {sample.id} «{sample.identifier}» details")
update_details_nwgc_id(sample, additional_details)
sample = db.fetch_row("""
update warehouse.sample
set details = coalesce(details, '{}') || %s
where sample_id = %s
returning sample_id as id, identifier
""", (Json(additional_details), sample.id))
assert sample.id, "Updating details affected no rows!"
return sample
def update_sample(
db: DatabaseSession,
sample,
encounter_id: Optional[int] = None) -> Optional[MinimalSampleRecord]:
"""
Update sample's encounter_id.
"""
LOG.debug(
f"Updating sample {sample.id}, linked to encounter {encounter_id}")
if sample.encounter_id:
assert sample.encounter_id == encounter_id, \
f"Sample {sample.id} already linked to another encounter {sample.encounter_id}"
return None
sample = db.fetch_row(
"""
update warehouse.sample
set encounter_id = %s
where sample_id = %s
returning sample_id as id, identifier
""", (encounter_id, sample.id))
assert sample.id, "Updating encounter_id affected no rows!"
LOG.info(f"Updated sample {sample.id}")
return sample
def find_sample(db: DatabaseSession, identifier: str, for_update=True) -> Any:
"""
Find sample by *identifier* and return sample.
"""
LOG.debug(f"Looking up sample «{identifier}»")
query_ending = ""
if for_update:
query_ending = "for update"
sample = db.fetch_row(
"""
select sample_id as id, identifier, encounter_id
from warehouse.sample
where identifier = %s or
collection_identifier = %s
""" + query_ending, (
identifier,
identifier,
))
if not sample:
LOG.info(f"No sample with identifier «{identifier}» found")
return None
LOG.info(f"Found sample {sample.id} «{sample.identifier}»")
return sample
def upsert_sample(db: DatabaseSession, collection_identifier: str,
encounter_id: int, details: dict) -> Any:
"""
Upsert collected sample by its *collection_identifier*.
The provided *details* are merged (at the top-level only) into
the existing sample details, if any.
"""
LOG.debug(f"Upserting sample collection «{collection_identifier}»")
data = {
"collection_identifier": collection_identifier,
"encounter_id": encounter_id,
"details": Json(details),
}
sample = db.fetch_row(
"""
insert into warehouse.sample (collection_identifier, encounter_id, details)
values (%(collection_identifier)s, %(encounter_id)s, %(details)s)
on conflict (collection_identifier) do update
set encounter_id = excluded.encounter_id,
details = coalesce(sample.details, '{}') || %(details)s
returning sample_id as id, identifier, collection_identifier, encounter_id
""", data)
assert sample.id, "Upsert affected no rows!"
LOG.info(
f"Upserted sample {sample.id} with collection identifier «{sample.collection_identifier}»"
)
return sample
def upsert_individual(db: DatabaseSession,
identifier: str,
sex: str = None) -> Any:
"""
Upsert individual by their *identifier*.
"""
LOG.debug(f"Upserting individual «{identifier}»")
data = {
"identifier": identifier,
"sex": sex,
}
individual = db.fetch_row(
"""
insert into warehouse.individual (identifier, sex)
values (%(identifier)s, %(sex)s)
on conflict (identifier) do update
set sex = excluded.sex
returning individual_id as id, identifier
""", data)
assert individual.id, "Upsert affected no rows!"
LOG.info(f"Upserted individual {individual.id} «{individual.identifier}»")
return individual
def find_or_create_sequence_read_set(
db: DatabaseSession, document: dict,
sample: MinimalSampleRecord) -> SequenceReadSetRecord:
"""
Find sequence read set given a *sample* and consensus genome record
*document*, inserting the sequence read set if it does not exist.
Return the sequence read set.
"""
urls = document['metadata']['urls']
LOG.debug(
dedent(f"""
Looking up sequence read set with sample ID «{sample.id}» and urls {urls}
"""))
sequence_read_set: SequenceReadSetRecord = db.fetch_row(
"""
select sequence_read_set_id as id, sample_id, urls
from warehouse.sequence_read_set
where sample_id = %s
and urls @> %s
and %s @> urls
""", (sample.id, urls, urls))
if sequence_read_set:
LOG.info(f"Found sequence read set {sequence_read_set.id}")
else:
LOG.debug(
dedent(f"""
Sequence read set not found for sample id «{sample.id}» and urls {urls}
"""))
data = {
"sample_id": sample.id,
"urls": urls,
}
sequence_read_set = db.fetch_row(
"""
insert into warehouse.sequence_read_set (sample_id, urls)
values (%(sample_id)s, %(urls)s)
returning sequence_read_set_id as id, sample_id, urls
""", data)
LOG.info(f"Created sequence read set {sequence_read_set.id}")
return sequence_read_set
def find_kit(db: DatabaseSession, identifier: str) -> KitRecord:
"""
Look for kit using *identifier* within the database
"""
kit: KitRecord = db.fetch_row(
"""
select kit_id as id, identifier, encounter_id, rdt_sample_id, utm_sample_id
from warehouse.kit
where identifier = %s
for update
""", (identifier, ))
return kit
def find_or_create_individual(db: DatabaseSession,
identifier: str,
sex: str,
details: dict = None) -> Any:
"""
Select indinvidual by *identifier*, or insert it if it doesn't exist.
"""
LOG.debug(f"Looking up individual «{identifier}»")
individual = db.fetch_row(
"""
select individual_id as id, identifier
from warehouse.individual
where identifier = %s
""", (identifier, ))
if individual:
LOG.info(f"Found individual {individual.id} «{individual.identifier}»")
else:
LOG.debug(f"individual «{identifier}» not found, adding")
data = {
"identifier": identifier,
"sex": sex,
"details": Json(details),
}
individual = db.fetch_row(
"""
insert into warehouse.individual (identifier, sex, details)
values (%(identifier)s, %(sex)s, %(details)s)
returning individual_id as id, identifier
""", data)
LOG.info(
f"Created individual {individual.id} «{individual.identifier}»")
return individual
def upsert_presence_absence(db: DatabaseSession, identifier: str,
sample_id: int, target_id: int, present: bool,
details: dict) -> Any:
"""
Upsert presence_absence by its *identifier*.
Confirmed with Samplify that their numeric identifier for each test is stable
and persistent.
"""
LOG.debug(f"Upserting presence_absence «{identifier}»")
data = {
"identifier": identifier,
"sample_id": sample_id,
"target_id": target_id,
"present": present,
"details": Json(details)
}
presence_absence = db.fetch_row(
"""
insert into warehouse.presence_absence (
identifier,
sample_id,
target_id,
present,
details)
values (
%(identifier)s,
%(sample_id)s,
%(target_id)s,
%(present)s,
%(details)s)
on conflict (identifier) do update
set sample_id = excluded.sample_id,
target_id = excluded.target_id,
present = excluded.present,
details = coalesce(presence_absence.details, '{}') || excluded.details
returning presence_absence_id as id, identifier
""", data)
assert presence_absence.id, "Upsert affected no rows!"
LOG.info(f"Upserted presence_absence {presence_absence.id} \
«{presence_absence.identifier}»")
return presence_absence
def upsert_encounter(db: DatabaseSession, identifier: str, encountered: str,
individual_id: int, site_id: int, age: Optional[str],
details: dict) -> Any:
"""
Upsert encounter by its *identifier*.
"""
LOG.debug(f"Upserting encounter «{identifier}»")
data = {
"identifier": identifier,
"encountered": encountered,
"individual_id": individual_id,
"site_id": site_id,
"age": age,
"details": Json(details),
}
encounter = db.fetch_row(
"""
insert into warehouse.encounter (
identifier,
individual_id,
site_id,
encountered,
age,
details)
values (
%(identifier)s,
%(individual_id)s,
%(site_id)s,
%(encountered)s::timestamp with time zone,
%(age)s,
%(details)s)
on conflict (identifier) do update
set individual_id = excluded.individual_id,
site_id = excluded.site_id,
encountered = excluded.encountered,
age = excluded.age,
details = excluded.details
returning encounter_id as id, identifier
""", data)
assert encounter.id, "Upsert affected no rows!"
LOG.info(f"Upserted encounter {encounter.id} «{encounter.identifier}»")
return encounter
def insert_fhir_bundle(db: DatabaseSession, bundle: dict) -> None:
"""
Insert FHIR bundles into the receiving area of the database.
"""
LOG.debug(f"Inserting FHIR bundle «{bundle['id']}»")
fhir = db.fetch_row("""
insert into receiving.fhir(document)
values (%s)
returning fhir_id as id
""", (Json(bundle),))
assert fhir.id, "Insert affected no rows!"
LOG.info(f"Inserted FHIR document {fhir.id} «{bundle['id']}»")
def insert_sequence_read_set(db: DatabaseSession, sample_id: int, urls: list):
"""
Insert sequencing read set directly into warehouse.sequence_read_set,
with the *sample_id* and *urls*.
"""
LOG.debug(f"Inserting sequence read set for sample {sample_id}")
data = {"sample_id": sample_id, "urls": urls}
sequence_read_set = db.fetch_row(
"""
insert into warehouse.sequence_read_set (sample_id, urls)
values (%(sample_id)s, %(urls)s)
returning sequence_read_set_id as id
""", data)
assert sequence_read_set.id, "Insert failed"
return sequence_read_set
def find_location(db: DatabaseSession, scale: str, identifier: str) -> Any:
"""
Find a location by *scale* and *identifier*.
"""
LOG.debug(f"Looking up location {(scale, identifier)}")
location = db.fetch_row(
"""
select location_id as id, scale, identifier, hierarchy
from warehouse.location
where (scale, identifier) = (%s, %s)
""", (scale, identifier))
if not location:
LOG.error(f"No location for {(scale, identifier)}")
return None
LOG.info(f"Found location {location.id} as {(scale, identifier)}")
return location
def find_sample(db: DatabaseSession, nwgc_id: str) -> Optional[int]:
"""
Find sample within warehouse that has *nwgc_id* in the sample details.
"""
LOG.debug(f"Looking up sample with NWGC ID: «{nwgc_id}»")
sample = db.fetch_row(
"""
select sample_id as id
from warehouse.sample
where details @> '{"nwgc_id": [%s]}'
""", (int(nwgc_id), ))
if not sample:
LOG.error(f"No sample with NWGC ID «{nwgc_id}» found")
return None
LOG.info(f"Found sample {sample.id}")
return sample.id
def set_use_create(use, description):
"""
Create a new identifier set use.
\b
<use> is the name of the new use.
<description> is a comment explaining the purpose of the use.
"""
session = DatabaseSession()
with session:
use, = session.fetch_row("""
insert into warehouse.identifier_set_use (use, description)
values (%s, %s)
returning use
""", (use, description))
click.echo(
"Created identifier set use " +
click.style(use, bold = True))
def find_organism(db: DatabaseSession,
lineage: str) -> Optional[OrganismRecord]:
"""
Find organism by *lineage* and return organism.
"""
LOG.debug(f"Looking up organism «{lineage}»")
organism = db.fetch_row(
"""
select organism_id as id, lineage
from warehouse.organism
where lineage ~ %s
""", (lineage, ))
if not organism:
LOG.error(f"No organism with lineage «{lineage}» found")
return None
LOG.info(f"Found organism {organism.id} «{organism.lineage}»")
return organism
def find_sample_by_id(db: DatabaseSession, sample_id: int) -> Any:
"""
Find sample by *sample_id* and return sample.
"""
LOG.debug(f"Looking up sample «{sample_id}»")
sample = db.fetch_row(
"""
select sample_id as id, identifier, encounter_id
from warehouse.sample
where sample_id = %s
for update
""", (sample_id, ))
if not sample:
LOG.error(f"No sample with id «{sample_id}» found")
return None
LOG.info(f"Found sample {sample.id} «{sample.identifier}»")
return sample
def upsert_genome(db: DatabaseSession,
sequence_read_set: SequenceReadSetRecord,
organism: OrganismRecord, document: dict) -> GenomeRecord:
"""
Upsert consensus genomes with the given *sequence_read_set*, *organism*,
and consensus genome *document*.
"""
LOG.debug(
dedent(f"""
Upserting genome with sequence read set {sequence_read_set.id},
organism {organism.id} «{organism.lineage}»"""))
data = {
"sample_id": sequence_read_set.sample_id,
"organism_id": organism.id,
"sequence_read_set_id": sequence_read_set.id,
"additional_details": Json(document['summary_stats'])
}
genome: GenomeRecord = db.fetch_row(
"""
insert into warehouse.consensus_genome (sample_id, organism_id,
sequence_read_set_id, details)
values (%(sample_id)s, %(organism_id)s, %(sequence_read_set_id)s,
%(additional_details)s)
on conflict (sample_id, organism_id, sequence_read_set_id) do update
set details = %(additional_details)s
returning consensus_genome_id as id, sample_id, organism_id, sequence_read_set_id
""", data)
assert genome.id, "Upsert affected no rows!"
LOG.info(
dedent(f"""
Upserted genome {genome.id} with sample ID «{genome.sample_id}»,
organism ID «{genome.organism_id}», and sequence read set ID «{genome.sequence_read_set_id}»
"""))
return genome
def set_create(name, description):
"""
Create a new identifier set.
\b
<name> is the name of the new set.
<description> is a comment explaining the purpose of the set.
"""
session = DatabaseSession()
with session:
identifier_set_id, = session.fetch_row("""
insert into warehouse.identifier_set (name, description)
values (%s, %s)
returning identifier_set_id
""", (name, description))
click.echo(
"Created identifier set " +
click.style(name, bold = True) +
f" (#{identifier_set_id})")
def find_encounter(db: DatabaseSession, identifier: str) -> Any:
"""
Given an *identifier* find the corresponding encounter within the
relational warehouse.
"""
LOG.debug(f"Looking up encounter with identifier «{identifier}»")
encounter = db.fetch_row(
"""
select encounter_id as id,
encounter.identifier as identifier,
site.identifier as site
from warehouse.encounter
join warehouse.site using (site_id)
where encounter.identifier = %s
""", (identifier, ))
if not encounter:
return None
LOG.info(f"Found encounter «{encounter.id}»")
return encounter
def upsert_location(db: DatabaseSession, scale: str, identifier: str,
hierarchy: str) -> Any:
"""
Upserts a location by its *scale* and *identifier*.
If *hierarchy* is None, it will be set to the location's
`scale => identifier`. Otherwise, the location's `scale => identifier`
will be appended to the *hierarchy*.
On update, new hierarchy and existing hierarchy are concatenated, with
new hierarchy taking precedence if there is overlap of keys.
"""
LOG.debug(f"Upserting location {(scale, identifier)}")
# Always includes the new location's own scale => identifier in hierarchy
location_hierarchy = f"{scale} => {identifier}".lower()
if hierarchy is None:
hierarchy = location_hierarchy
else:
hierarchy = hierarchy + "," + location_hierarchy
location = db.fetch_row(
"""
insert into warehouse.location (scale, identifier, hierarchy)
values (%s, %s, %s)
on conflict (scale, identifier) do update
set hierarchy = coalesce(location.hierarchy, '') || excluded.hierarchy
returning location_id as id, scale, identifier, hierarchy
""", (scale, identifier, hierarchy))
assert location.id, "Upsert affected no rows!"
LOG.info(
f"Upserted location {location.id} as {(location.scale,location.identifier)}"
)
return location
def set_create(name, use, description):
"""
Create a new identifier set.
\b
<name> is the name of the new set.
<use> is the use classification for this set, valid values can be found using `id3c identifier set-use ls` command.
<description> is a comment explaining the purpose of the set.
"""
session = DatabaseSession()
with session:
identifier_set_id, = session.fetch_row("""
insert into warehouse.identifier_set (name, use, description)
values (%s, %s, %s)
returning identifier_set_id
""", (name, use, description))
click.echo(
"Created identifier set " +
click.style(name, bold = True) +
f" (#{identifier_set_id})")
def upsert_genomic_sequence(db: DatabaseSession, genome: GenomeRecord,
masked_consensus: dict) -> Any:
"""
Upsert genomic sequence given a *genome* record and some information from a
given *masked_consensus*.
"""
sequence_identifier = "".join([
masked_consensus['sequence_identifier'], "-",
str(genome.sequence_read_set_id)
])
LOG.info(f"Upserting genomic sequence «{sequence_identifier}»")
data = {
"identifier": sequence_identifier,
"segment": masked_consensus['sequence_segment'],
"seq": masked_consensus['genomic_sequence'],
"genome_id": genome.id,
}
genomic_sequence = db.fetch_row(
"""
insert into warehouse.genomic_sequence (identifier, segment, seq, consensus_genome_id)
values (%(identifier)s, %(segment)s, %(seq)s, %(genome_id)s)
on conflict (identifier) do update
set seq = excluded.seq,
segment = excluded.segment
returning genomic_sequence_id as id, identifier, segment, seq, consensus_genome_id
""", data)
assert genomic_sequence.consensus_genome_id == genome.id, \
"Provided sequence identifier was not unique, matched a sequence linked to another consensus genome!"
assert genomic_sequence.id, "Upsert affected no rows!"
LOG.info(f"Upserted genomic sequence {genomic_sequence.id}»")
return genomic_sequence
def find_sample(db: DatabaseSession,
identifier: str) -> Optional[SampleRecord]:
"""
Given an *identifier* find the corresponding sample within the
database.
"""
LOG.debug(f"Looking up sample with identifier «{identifier}»")
sample = db.fetch_row(
"""
select sample_id as id,
identifier,
encounter_id,
details ->> 'sample_type' as type
from warehouse.sample
where sample.identifier = %s
""", (identifier, ))
if not sample:
return None
LOG.info(f"Found sample «{sample.id}»")
return sample
def find_sample_origin_by_barcode(db: DatabaseSession,
barcode: str) -> Optional[str]:
"""
Given an SFS *barcode* return the `sample_origin` found in sample.details
"""
sample = db.fetch_row(
"""
select details ->> 'sample_origin' as sample_origin
from warehouse.sample
join warehouse.identifier on sample.identifier = identifier.uuid::text
where barcode = %s
""", (barcode, ))
if not sample:
LOG.error(f"No sample with barcode «{barcode}» found.")
return None
if not sample.sample_origin:
LOG.warning(
f"Sample with barcode «{barcode}» did not have sample_origin in details"
)
return None
return sample.sample_origin
def upsert_kit_with_sample(db: DatabaseSession, identifier: str,
sample: SampleRecord,
additional_details: dict) -> Tuple[KitRecord, str]:
"""
Upsert kit by its *identifier* to include link to a sample.
An existing kit has its *sample_id* updated and the provided
*additional_details* are merged (at the top level only into)
the existing kit details, if any.
"""
LOG.debug(f"Upserting kit «{identifier}»")
data = {
"identifier": identifier,
"sample_id": sample.id,
"additional_details": Json(additional_details)
}
if sample.type == 'utm':
sample_type = "utm_sample_id"
elif sample.type == 'rdt':
sample_type = "rdt_sample_id"
# Look for existing kit
kit = find_kit(db, identifier)
# Nothing found → create
if not kit:
LOG.info("Creating new kit")
status = "created"
kit = db.fetch_row(
sql.SQL("""
insert into warehouse.kit (identifier, {}, details)
values(%(identifier)s,
%(sample_id)s,
%(additional_details)s)
returning kit_id as id,
identifier,
encounter_id,
{}
""").format(sql.Identifier(sample_type),
sql.Identifier(sample_type)), data)
# Found kit → update
else:
status = "updated"
kit_sample_id = getattr(kit, sample_type)
# Warn if kit is already linked to a different sample!
if (kit_sample_id and (sample.id != kit_sample_id)):
LOG.warning(
f"Kit «{kit.id}» already linked to another " +
f"{sample_type} «{kit_sample_id}», linking with «{sample.id}» instead"
)
kit = db.fetch_row(
sql.SQL("""
update warehouse.kit
set {} = %(sample_id)s,
details = coalesce(details, {}) || %(additional_details)s
where kit_id = %(kit_id)s
returning kit_id as id,
identifier,
encounter_id,
{}
""").format(sql.Identifier(sample_type), sql.Literal(Json({})),
sql.Identifier(sample_type)), {
**data, "kit_id": kit.id
})
assert kit.id, "Upsert affected no rows!"
LOG.info(f"Upserted kit {kit.id} with identifier «{kit.identifier}» " +
f"linked to {sample_type} «{getattr(kit, sample_type)}»")
return kit, status
def process_diagnostic_report_bundle_entry(db: DatabaseSession, bundle: Bundle, entry: BundleEntry):
"""
Given an DiagnosticReport resource *entry* from a given *bundle*, processes
the relevant information into the database.
"""
resource, resource_type = resource_and_resource_type(entry)
if resource_type != 'DiagnosticReport':
return
LOG.debug(f"Processing DiagnosticReport Resource «{entry.fullUrl}».")
for reference in resource.specimen:
barcode = None
if not reference.identifier:
specimen = reference.resolved(Specimen)
barcode = identifier(specimen, f"{INTERNAL_SYSTEM}/sample").strip()
elif matching_system(reference.identifier, INTERNAL_SYSTEM):
barcode = reference.identifier.value.strip()
if not barcode:
continue
LOG.debug(f"Looking up collected specimen barcode «{barcode}»")
specimen_identifier = find_identifier(db, barcode)
if not specimen_identifier:
LOG.warning(f"Skipping collected specimen with unknown barcode «{barcode}»")
continue
# By default, assume that the incoming barcode is for a collection identifier
is_collection_identifier = True
try:
assert specimen_identifier.set_name in EXPECTED_COLLECTION_IDENTIFIER_SETS, \
f"Specimen with unexpected «{specimen_identifier.set_name}» barcode «{barcode}»"
except AssertionError:
assert specimen_identifier.set_name in EXPECTED_SAMPLE_IDENTIFIER_SETS, \
f"Specimen with unexpected «{specimen_identifier.set_name}» barcode «{barcode}»"
is_collection_identifier = False
sample = find_sample(db, specimen_identifier.uuid)
if not is_collection_identifier and not sample:
raise SampleNotFoundError("No sample with identifier «{specimen_identifier.uuid}» found.")
# Sometimes the Ellume samples come in faster than the specimen manifest
# is updated. In this case, create a new collection identifier that will
# be filled in later.
if not sample:
LOG.debug(f"Creating sample with collection identifier «{specimen_identifier.uuid}»")
sample = db.fetch_row("""
insert into warehouse.sample (collection_identifier)
values (%s)
returning sample_id as id, collection_identifier
""", (str(specimen_identifier.uuid),))
LOG.info(f"Created sample {sample.id} with collection identifier «{sample.collection_identifier}»")
process_presence_absence_tests(db, resource, sample.id, barcode)
def upsert_sample(db: DatabaseSession,
identifier: str,
collection_identifier: Optional[str],
collection_date: Optional[str],
additional_details: dict) -> Tuple[Any, str]:
"""
Upsert sample by its *identifier* and/or *collection_identifier*.
An existing sample has its *identifier*, *collection_identifier*,
*collection_date* updated, and the provided *additional_details* are
merged (at the top-level only) into the existing sample details, if any.
Raises an exception if there is more than one matching sample.
"""
data = {
"identifier": identifier,
"collection_identifier": collection_identifier,
"collection_date": collection_date,
"additional_details": Json(additional_details),
}
# Look for existing sample(s)
with db.cursor() as cursor:
cursor.execute("""
select sample_id as id, identifier, collection_identifier, encounter_id
from warehouse.sample
where identifier = %(identifier)s
or collection_identifier = %(collection_identifier)s
for update
""", data)
samples = list(cursor)
# Nothing found → create
if not samples:
LOG.info("Creating new sample")
status = 'created'
sample = db.fetch_row("""
insert into warehouse.sample (identifier, collection_identifier, collected, details)
values (%(identifier)s,
%(collection_identifier)s,
date_or_null(%(collection_date)s),
%(additional_details)s)
returning sample_id as id, identifier, collection_identifier, encounter_id
""", data)
# One found → update
elif len(samples) == 1:
status = 'updated'
sample = samples[0]
LOG.info(f"Updating existing sample {sample.id}")
sample = db.fetch_row("""
update warehouse.sample
set identifier = %(identifier)s,
collection_identifier = %(collection_identifier)s,
collected = date_or_null(%(collection_date)s),
details = coalesce(details, '{}') || %(additional_details)s
where sample_id = %(sample_id)s
returning sample_id as id, identifier, collection_identifier, encounter_id
""",
{ **data, "sample_id": sample.id })
assert sample.id, "Update affected no rows!"
# More than one found → error
else:
raise Exception(f"More than one sample matching sample and/or collection barcodes: {samples}")
return sample, status
