about summary refs log tree commit diff
path: root/src/db.rs
diff options
context:
space:
mode:
authorPjotr <wrk@bosa.lan>2026-11-10 12:53:06 +0000
committerPjotr <wrk@bosa.lan>2026-11-10 12:53:06 +0000
commit7fa899b66f484593e36b175dc6daa5be1d03f2fd (patch)
treeff6d341118a542be14462b2fdf44221fb60f1c24 /src/db.rs
parent2b41fc94a5e2b33a411d22721a6ee2a97ac9ae60 (diff)
downloadgenenetwork4-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.rs166
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())
+}