diff options
| author | Pjotr <wrk@bosa.lan> | 2026-11-10 12:53:06 +0000 |
|---|---|---|
| committer | Pjotr <wrk@bosa.lan> | 2026-11-10 12:53:06 +0000 |
| commit | 7fa899b66f484593e36b175dc6daa5be1d03f2fd (patch) | |
| tree | ff6d341118a542be14462b2fdf44221fb60f1c24 /src/db.rs | |
| parent | 2b41fc94a5e2b33a411d22721a6ee2a97ac9ae60 (diff) | |
| download | genenetwork4-7fa899b66f484593e36b175dc6daa5be1d03f2fd.tar.gz | |
probesets: cross-dataset lookups by probeset id and by gene symbol
A probeset id is not a Xapian field, so /search cannot find 1427571_at
exactly and the only route to it was guessing a dataset and calling
/datasets/{ds}/probesets/{name} until one answered. A gene symbol could
be searched, but the reply was capped at 100 hits, interleaved probesets
and platforms, and carried no total -- so "did I see all of Shh?" had no
answer. Both questions are one indexed query each: ProbeSet.Name_IDX and
symbol_IDX.
* GET /probesets/{name}: one row per dataset that carries the probeset,
with the panel (group, species, platform) and the scan's statistics
(mean, LRS, LOD, locus, additive). Suffixed chip variants match too
-- 1427571_at also finds 1427571_at_A -- and each row's `name` says
which matched, so the answer never hides a substitution. LIKE's
wildcards are escaped, or every Affymetrix `_at` would match any
character. 155 rows over 155 datasets in 32 ms.
* GET /genes/{symbol}/traits: the same rows filtered on Symbol, with a
real total, ?sort=lod, ?min_lod=, ?group=, ?species=, ?platform= and
paging -- "BXD datasets where Shh is a strong eQTL" is one call.
1225 rows over 17 datasets and 13 probesets in 38 ms.
* Both serve metadata, not values. A token is enough for the index;
trait values stay per-dataset and authorized per dataset, and `next`
goes to /datasets/{ds}/probesets/{name}/values, which checks. There
is no cross-dataset values endpoint, deliberately: that is bulk work
and /groups/{g}/traits is the bulk call.
The one-statement join took 5.2 s for Shh -- MySQL rescans
InbredSet/Species per row -- so the 933 ProbeSetFreeze rows and the 102
panels are fetched separately and joined in memory: 70 ms each. nsamples
is left out: it is a ProbeSetData count per row (+0.5 s) and the dataset
record already reports strains.
Diffstat (limited to 'src/db.rs')
| -rw-r--r-- | src/db.rs | 166 |
1 files changed, 166 insertions, 0 deletions
diff --git a/src/db.rs b/src/db.rs index 975a821..480f286 100644 --- a/src/db.rs +++ b/src/db.rs @@ -1606,3 +1606,169 @@ mod failure_tests { )); } } + +// --------------------------------------------------------------------------- +// Cross-dataset probeset lookup. +// +// `ProbeSet` is indexed on `Name` (Name_IDX) and `Symbol` (symbol_IDX), so +// "which datasets carry this probeset" and "every expression trait of this +// gene" are each one indexed query plus two small metadata lookups. Doing it +// in SQL with the joins in one statement was 5 s for `Shh`, because the +// planner rescans InbredSet/Species per row; fetching the 933 ProbeSetFreeze +// rows and the 102 panels separately costs 70 ms each and joins in memory. +// --------------------------------------------------------------------------- + +/// One expression trait, as a cross-dataset lookup sees it: the dataset it +/// lives in, the panel behind it, the probeset, and its mapping statistics. +#[derive(Serialize, utoipa::ToSchema, Clone)] +pub struct ProbesetHit { + pub dataset: String, + pub dataset_fullname: String, + #[serde(skip_serializing_if = "Option::is_none")] + pub group: Option<String>, + #[serde(skip_serializing_if = "Option::is_none")] + pub species: Option<String>, + /// The platform string (`ProbeSetFreeze.Name2`), e.g. + /// "Striatum_M430_V2_RMA_November04". There is no chip table with a + /// platform id here, so this is a label, not a key. + #[serde(skip_serializing_if = "Option::is_none")] + pub platform: Option<String>, + pub name: String, + #[serde(skip_serializing_if = "Option::is_none")] + pub symbol: Option<String>, + #[serde(skip_serializing_if = "Option::is_none")] + pub chr: Option<String>, + #[serde(skip_serializing_if = "Option::is_none")] + pub mb: Option<f64>, + #[serde(skip_serializing_if = "Option::is_none")] + pub mean: Option<f64>, + #[serde(skip_serializing_if = "Option::is_none")] + pub lrs: Option<f64>, + #[serde(skip_serializing_if = "Option::is_none")] + pub lod: Option<f64>, + #[serde(skip_serializing_if = "Option::is_none")] + pub locus: Option<String>, + #[serde(skip_serializing_if = "Option::is_none")] + pub additive: Option<f64>, +} + +/// Escapes LIKE's wildcards so a probeset name's own `_` characters (every +/// Affymetrix id has one) match literally. +fn like_prefix(name: &str) -> String { + let mut escaped = String::with_capacity(name.len() + 4); + for character in name.chars() { + if matches!(character, '%' | '_' | '\\') { + escaped.push('\\'); + } + escaped.push(character); + } + escaped.push('%'); + escaped +} + +/// Every dataset carrying a probeset, by exact name (plus the chip-suffix +/// variants such as `1427571_at_A`) or by gene symbol. +pub fn probeset_hits( + by_name: Option<&str>, + by_symbol: Option<&str>, +) -> Result<Vec<ProbesetHit>, DbError> { + let pool = pool()?; + let mut conn = pool.get_conn()?; + let (predicate, params) = match (by_name, by_symbol) { + (Some(name), None) => ( + "WHERE ProbeSet.Name = :name OR ProbeSet.Name LIKE :prefix", + mysql::params! { "name" => name, "prefix" => like_prefix(name) }, + ), + (None, Some(symbol)) => ( + "WHERE ProbeSet.Symbol = :symbol", + mysql::params! { "symbol" => symbol }, + ), + _ => return Ok(Vec::new()), + }; + type Row = ( + String, + Option<String>, + Option<String>, + Option<f64>, + i64, + Option<f64>, + Option<f64>, + Option<String>, + Option<f64>, + ); + let rows: Vec<Row> = conn.exec_map( + format!( + "SELECT ProbeSet.Name, ProbeSet.Symbol, ProbeSet.Chr, ProbeSet.Mb, \ + ProbeSetXRef.ProbeSetFreezeId, ProbeSetXRef.mean, ProbeSetXRef.LRS, \ + ProbeSetXRef.Locus, ProbeSetXRef.additive \ + FROM ProbeSet \ + JOIN ProbeSetXRef ON ProbeSetXRef.ProbeSetId = ProbeSet.Id \ + {predicate}" + ), + params, + |row| row, + )?; + if rows.is_empty() { + return Ok(Vec::new()); + } + let datasets = dataset_metadata()?; + Ok(rows + .into_iter() + .filter_map(|(name, symbol, chr, mb, freeze_id, mean, lrs, locus, additive)| { + let (_, dataset, fullname, platform, group, species) = datasets + .iter() + .find(|(id, ..)| *id == freeze_id)? + .clone(); + Some(ProbesetHit { + dataset, + dataset_fullname: fullname, + group, + species, + platform, + name, + symbol, + chr, + mb, + mean, + lrs, + lod: lrs.map(lod), + locus, + additive, + }) + }) + .collect()) +} + +/// `(ProbeSetFreezeId, dataset, full name, platform, group, species)`. +type DatasetMeta = (i64, String, String, Option<String>, Option<String>, Option<String>); + +/// The 933 dataset rows and the 102 panels, fetched separately and joined in +/// memory: one statement with both joins rescans InbredSet/Species per row. +fn dataset_metadata() -> Result<Vec<DatasetMeta>, DbError> { + let pool = pool()?; + let mut conn = pool.get_conn()?; + let freezes: Vec<(i64, String, String, Option<String>, i64)> = conn.exec_map( + "SELECT Id, Name, FullName, Name2, ProbeFreezeId FROM ProbeSetFreeze", + (), + |row| row, + )?; + let panels: Vec<(i64, Option<String>, Option<String>)> = conn.exec_map( + "SELECT ProbeFreeze.ProbeFreezeId, InbredSet.Name, Species.Name \ + FROM ProbeFreeze \ + LEFT JOIN InbredSet ON InbredSet.InbredSetId = ProbeFreeze.InbredSetId \ + LEFT JOIN Species ON Species.SpeciesId = InbredSet.SpeciesId", + (), + |row| row, + )?; + Ok(freezes + .into_iter() + .map(|(id, name, full_name, platform, probe_freeze_id)| { + let panel = panels + .iter() + .find(|(id, ..)| *id == probe_freeze_id) + .cloned() + .unwrap_or((probe_freeze_id, None, None)); + (id, name, full_name, platform, panel.1, panel.2) + }) + .collect()) +} |
