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 | |
| 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')
| -rw-r--r-- | src/db.rs | 166 | ||||
| -rw-r--r-- | src/lib.rs | 2 | ||||
| -rw-r--r-- | src/routes/mod.rs | 1 | ||||
| -rw-r--r-- | src/routes/probesets.rs | 382 |
4 files changed, 551 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()) +} diff --git a/src/lib.rs b/src/lib.rs index 9fd2ce5..e573c92 100644 --- a/src/lib.rs +++ b/src/lib.rs @@ -120,6 +120,8 @@ pub fn app() -> Router { .routes(routes!(routes::publications::publication_traits)) .routes(routes!(routes::search::search)) .routes(routes!(routes::correlation::correlation)) + .routes(routes!(routes::probesets::probeset_by_name)) + .routes(routes!(routes::probesets::gene_traits)) .routes(routes!(routes::plot::plot_rqtl)) .routes(routes!(routes::plot::plot_gemma)) .routes(routes!(routes::phewas::group_phewas)) diff --git a/src/routes/mod.rs b/src/routes/mod.rs index c601f2d..388894f 100644 --- a/src/routes/mod.rs +++ b/src/routes/mod.rs @@ -15,6 +15,7 @@ pub mod groups; pub mod mapping_methods; pub mod phewas; pub mod plot; +pub mod probesets; pub mod publications; pub mod qtl2; pub mod rqtl; diff --git a/src/routes/probesets.rs b/src/routes/probesets.rs new file mode 100644 index 0000000..42d341c --- /dev/null +++ b/src/routes/probesets.rs @@ -0,0 +1,382 @@ +//! `GET /probesets/{name}` and `GET /genes/{symbol}/traits`: cross-dataset +//! lookups over the expression traits. +//! +//! Both are questions the search index cannot answer. A probeset id is not a +//! Xapian field, so `1427571_at` finds nothing exact and the only route to it +//! is guessing a dataset and calling `/datasets/{ds}/probesets/{name}`. +//! A gene symbol can be searched, but the reply is capped, ungrouped and +//! counts different probesets and platforms in one list. These two endpoints +//! are the indexed lookups: `ProbeSet.Name` and `ProbeSet.Symbol` both have +//! one, and the answer carries the panel and the mapping statistics. +//! +//! What this returns is **metadata**: which datasets carry a probeset, and +//! what its QTL scan found in each. It deliberately does not return trait +//! values -- those are per-dataset and authorized per dataset, and the `next` +//! link goes to the endpoint that checks. So a token buys the index; the +//! values stay where they always were. + +use axum::Json; +use axum::extract::{Path, Query}; +use axum::http::StatusCode; +use serde::Deserialize; + +use gnauth_macros::gnauth; + +use crate::auth::AuthToken; +use crate::db::{self, ProbesetHit}; +use crate::error::{ApiError, Unauthorized}; +use crate::response::{Ctx, Envelope, path_segment}; +use crate::suggest::{self, Link}; + +#[derive(Deserialize, utoipa::IntoParams)] +pub struct ProbesetParams { + /// Only this group (an InbredSet name, e.g. BXD). + pub group: Option<String>, + /// Only this species (e.g. mouse). + pub species: Option<String>, + /// Only datasets whose platform label contains this, e.g. M430. + pub platform: Option<String>, + /// One-based page. Default 1. + pub page: Option<u32>, + /// Rows per page. Default 100, at most 5000. + pub limit: Option<u32>, +} + +#[derive(Deserialize, utoipa::IntoParams)] +pub struct GeneParams { + /// Only this group (an InbredSet name, e.g. BXD). + pub group: Option<String>, + /// Only this species (e.g. mouse). + pub species: Option<String>, + /// Only datasets whose platform label contains this, e.g. M430. + pub platform: Option<String>, + /// `dataset` (default) or `lod`, strongest peak first. + pub sort: Option<String>, + /// Only traits whose peak reached at least this LOD. + pub min_lod: Option<f64>, + /// One-based page. Default 1. + pub page: Option<u32>, + /// Rows per page. Default 100, at most 5000. + pub limit: Option<u32>, +} + +/// The columns of both replies, in this order. +const COLUMNS: [&str; 13] = [ + "dataset", + "group", + "species", + "platform", + "name", + "symbol", + "chr", + "mb", + "mean", + "lrs", + "lod", + "locus", + "additive", +]; + +#[utoipa::path(get, path = "/probesets/{name}", + summary = "Every dataset that carries a probeset with this name", + description = "A cross-dataset lookup on `ProbeSet.Name` (indexed): one row per dataset, \ +with the panel (group, species, platform) and the probeset's mapping statistics there (mean, \ +LRS, LOD, peak locus, additive). Suffixed variants match too -- `1427571_at` also finds \ +`1427571_at_A` -- and each row's `name` says which one it was. Metadata only: values are \ +per-dataset and live at /datasets/{dataset}/probesets/{name}/values.", + params(("name" = String, Path, description = "Probeset id, e.g. 1427571_at"), ProbesetParams), + responses( + (status = 200, description = "One row per dataset, positional (meta.columns names them)", body = Envelope<serde_json::Value>), + Unauthorized, + (status = 404, description = "No probeset with that name", body = ApiError) + ) +)] +#[gnauth(token_only)] +pub async fn probeset_by_name( + ctx: Ctx, + Path(name): Path<String>, + Query(params): Query<ProbesetParams>, + _token: AuthToken, +) -> Result<Json<Envelope<serde_json::Value>>, ApiError> { + let rid = ctx.request_info(); + let lookup = name.clone(); + let hits = tokio::task::spawn_blocking(move || db::probeset_hits(Some(&lookup), None)) + .await + .map_err(|error| internal(error.to_string(), rid.clone()))? + .map_err(|error| ApiError::from_db(error, "probeset_by_name").with_request_id(rid.clone()))?; + if hits.is_empty() { + return Err(ApiError::new( + StatusCode::NOT_FOUND, + "probeset_not_found", + "Probeset not found", + format!("No probeset named '{name}' (or a suffixed variant of it)."), + "probeset_by_name", + ) + .with_request_id(rid)); + } + let total = hits.len(); + let filtered = filter(hits, ¶ms.group, ¶ms.species, ¶ms.platform); + let matched = filtered.len(); + let page = page(&filtered, params.page, params.limit); + let datasets: Vec<String> = filtered + .iter() + .map(|hit| hit.dataset.clone()) + .collect::<std::collections::BTreeSet<_>>() + .into_iter() + .collect(); + let variants: std::collections::BTreeSet<&str> = filtered + .iter() + .map(|hit| hit.name.as_str()) + .filter(|candidate| *candidate != name) + .collect(); + let mut note = format!( + "{total} row(s) over {} dataset(s) carry '{name}'", + datasets.len() + ); + if !variants.is_empty() { + note.push_str(&format!( + ", including the suffixed variant(s) {}", + variants.into_iter().collect::<Vec<_>>().join(", ") + )); + } + note.push_str(&format!( + "; {matched} remain after the filters, {} on this page", + page.rows_len + )); + let mut envelope = ctx + .envelope(serde_json::Value::Array(page.rows)) + .with_columns(COLUMNS.iter().map(|name| name.to_string()).collect(), page.rows_len) + .with_note(¬e) + .with_note( + "Metadata only: which datasets carry the probeset and what their scans found. The \ + trait's values are per-dataset and authorized per dataset -- follow `next`.", + ); + if let Some(dataset) = datasets.first() { + envelope = envelope.with_next( + &format!( + "/datasets/{}/probesets/{}/values", + path_segment(dataset), + path_segment(&name) + ), + "The probeset's value per strain in the first matching dataset (with mapping \ + suggestions attached)", + ); + } + envelope = envelope.with_links(probeset_links(&datasets, &name)); + Ok(Json(envelope)) +} + +#[utoipa::path(get, path = "/genes/{symbol}/traits", + summary = "Every expression trait of a gene symbol, across datasets", + description = "A cross-dataset lookup on `ProbeSet.Symbol` (indexed): one row per dataset \ +and probeset, with a real total (unlike /search's capped list) and the panel each trait belongs \ +to. Filter with ?group=, ?species=, ?platform= and ?min_lod=; sort with ?sort=lod for the \ +strongest peaks first. Metadata only: values are per-dataset.", + params(("symbol" = String, Path, description = "Gene symbol, e.g. Shh"), GeneParams), + responses( + (status = 200, description = "One row per dataset and probeset, positional (meta.columns names them)", body = Envelope<serde_json::Value>), + Unauthorized, + (status = 404, description = "No probeset carries that symbol", body = ApiError) + ) +)] +#[gnauth(token_only)] +pub async fn gene_traits( + ctx: Ctx, + Path(symbol): Path<String>, + Query(params): Query<GeneParams>, + _token: AuthToken, +) -> Result<Json<Envelope<serde_json::Value>>, ApiError> { + let rid = ctx.request_info(); + let lookup = symbol.clone(); + let hits = tokio::task::spawn_blocking(move || db::probeset_hits(None, Some(&lookup))) + .await + .map_err(|error| internal(error.to_string(), rid.clone()))? + .map_err(|error| ApiError::from_db(error, "gene_traits").with_request_id(rid.clone()))?; + if hits.is_empty() { + return Err(ApiError::new( + StatusCode::NOT_FOUND, + "gene_not_found", + "No probeset for that symbol", + format!("No probeset in any dataset carries the symbol '{symbol}'."), + "gene_traits", + ) + .with_request_id(rid)); + } + let total = hits.len(); + let mut filtered = filter(hits, ¶ms.group, ¶ms.species, ¶ms.platform); + if let Some(min_lod) = params.min_lod { + filtered.retain(|hit| hit.lod.is_some_and(|lod| lod >= min_lod)); + } + let matched = filtered.len(); + let probesets: Vec<String> = filtered + .iter() + .map(|hit| hit.name.clone()) + .collect::<std::collections::BTreeSet<_>>() + .into_iter() + .collect(); + let datasets: Vec<String> = filtered + .iter() + .map(|hit| hit.dataset.clone()) + .collect::<std::collections::BTreeSet<_>>() + .into_iter() + .collect(); + match params.sort.as_deref() { + Some("lod") => filtered.sort_by(|left, right| { + right + .lod + .unwrap_or(f64::NEG_INFINITY) + .partial_cmp(&left.lod.unwrap_or(f64::NEG_INFINITY)) + .unwrap_or(std::cmp::Ordering::Equal) + }), + Some("dataset") | None => filtered.sort_by(|left, right| { + left.dataset + .cmp(&right.dataset) + .then(left.name.cmp(&right.name)) + }), + Some(other) => { + return Err(ApiError::new( + StatusCode::UNPROCESSABLE_ENTITY, + "invalid_sort", + "Invalid sort", + format!("sort '{other}' not recognized; use 'dataset' or 'lod'."), + "gene_traits", + ) + .with_request_id(rid)); + } + } + let page = page(&filtered, params.page, params.limit); + let mut envelope = ctx + .envelope(serde_json::Value::Array(page.rows)) + .with_columns(COLUMNS.iter().map(|name| name.to_string()).collect(), page.rows_len) + .with_note(&format!( + "{} expression trait row(s) across {} dataset(s) and {} probeset(s) carry the symbol \ + '{symbol}'{}; {matched} remain after the filters, {} on this page.", + total, + datasets.len(), + probesets.len(), + match (params.group.as_deref(), params.species.as_deref(), params.platform.as_deref()) { + (None, None, None) => "".to_string(), + _ => " (all unfiltered)".to_string(), + }, + page.rows_len, + )) + .with_note( + "Metadata only: values are per-dataset and authorized per dataset. A peak's `locus` \ + is a marker name in that dataset's genotype version; resolving it to a position is \ + /groups/{group}/genotypes/{version}/markers/{marker}/location.", + ); + if let Some(dataset) = datasets.first() { + if let Some(name) = probesets.first() { + envelope = envelope.with_next( + &format!( + "/datasets/{}/probesets/{}/values", + path_segment(dataset), + path_segment(name) + ), + "One of these traits' values per strain, with the mapping suggestions attached", + ); + } + } + envelope = envelope.with_links(probeset_links(&datasets, probesets.first().map(String::as_str).unwrap_or(""))); + Ok(Json(envelope)) +} + +/// The rows one page holds. +struct Page { + rows: Vec<serde_json::Value>, + rows_len: usize, +} + +/// Rounded for reading: the database's floats carry more digits than a reply +/// needs, and 6.63333333333333 is noise where 6.633 is the number. +fn round(value: Option<f64>, places: f64) -> Option<f64> { + let factor = 10f64.powf(places); + value.map(|value| (value * factor).round() / factor) +} + +fn page(hits: &[ProbesetHit], page: Option<u32>, limit: Option<u32>) -> Page { + let limit = limit.unwrap_or(100).clamp(1, 5_000) as usize; + let page = page.unwrap_or(1).max(1) as usize; + let rows: Vec<serde_json::Value> = hits + .iter() + .skip((page - 1) * limit) + .take(limit) + .map(|hit| { + serde_json::json!([ + hit.dataset, + hit.group, + hit.species, + hit.platform, + hit.name, + hit.symbol, + hit.chr, + round(hit.mb, 4.0), + round(hit.mean, 3.0), + round(hit.lrs, 3.0), + round(hit.lod, 3.0), + hit.locus, + round(hit.additive, 4.0), + ]) + }) + .collect(); + let rows_len = rows.len(); + Page { rows, rows_len } +} + +/// The `group`/`species`/`platform` filters, all optional and all substring +/// for `platform` (there is no platform id in this schema). +fn filter( + hits: Vec<ProbesetHit>, + group: &Option<String>, + species: &Option<String>, + platform: &Option<String>, +) -> Vec<ProbesetHit> { + hits.into_iter() + .filter(|hit| { + group + .as_deref() + .is_none_or(|wanted| hit.group.as_deref() == Some(wanted)) + }) + .filter(|hit| { + species + .as_deref() + .is_none_or(|wanted| hit.species.as_deref() == Some(wanted)) + }) + .filter(|hit| { + platform.as_deref().is_none_or(|wanted| { + hit.platform + .as_deref() + .is_some_and(|actual| actual.to_lowercase().contains(&wanted.to_lowercase())) + }) + }) + .collect() +} + +/// Where a cross-dataset answer leads: the first dataset's values, the +/// group's genotype surface, and the listings. +fn probeset_links(datasets: &[String], name: &str) -> Vec<Link> { + let mut links = Vec::new(); + if !datasets.is_empty() && !name.is_empty() { + links.push(( + format!("/datasets/{}/probesets", path_segment(&datasets[0])), + "The other probesets of the first matching dataset", + )); + } + links.push(( + "/groups".to_string(), + "Every group, to pick a panel to map against", + )); + links.extend(suggest::root_links()); + links +} + +fn internal(message: String, rid: crate::response::RequestInfo) -> ApiError { + ApiError::new( + StatusCode::INTERNAL_SERVER_ERROR, + "internal_error", + "Internal error", + message, + "probeset_lookup", + ) + .with_request_id(rid) +} |
