//! Read-only access to the GeneNetwork MariaDB database (db_webqtl). use mysql::prelude::{FromRow, Queryable}; use mysql::{Opts, OptsBuilder, Pool, PoolOpts, params}; use serde::Serialize; use std::sync::{Mutex, OnceLock}; /// Connection string, mysql://user:password@host/database. Same /// SQL_URI config key GeneNetwork3 uses, so the two services can share /// configuration. fn sql_uri() -> String { crate::config::Config::load().sql_uri } /// Why a database-backed operation failed. /// /// The distinction is minted at the `pool()` choke point, the single /// place a connection is (re)established: `Unavailable` means the pool /// could not be built at all, so no configured/reachable database /// exists and DB-backed routes are meaningless (they map to a typed /// 503 `db_unavailable`). `Query` is any failure on an already-built /// pool -- a bad query, or a connection that died after the pool was /// built; those keep the existing 500 `database_error` shape. See the /// lane REPORT's probe-window limitation. #[derive(Debug)] pub enum DbError { /// The pool could not be built: `SQL_URI` is unset/unparseable or /// the database is unreachable. Minted only here. Unavailable(String), /// A query (or pool checkout) failed on an established pool. Query(mysql::Error), } impl std::fmt::Display for DbError { fn fmt(&self, f: &mut std::fmt::Formatter<'_>) -> std::fmt::Result { match self { DbError::Unavailable(detail) => write!(f, "{detail}"), DbError::Query(error) => write!(f, "{error}"), } } } impl std::error::Error for DbError {} impl From for DbError { fn from(error: mysql::Error) -> Self { DbError::Query(error) } } /// The shared connection pool (see `pool()` and `forget_pool()`). static POOL: OnceLock>> = OnceLock::new(); /// The shared connection pool, built once and reused by every query /// function below. mysql::Pool::new eagerly opens PoolConstraints:: /// DEFAULT's 10 connections before returning (about 7s against this /// database), so building a fresh Pool per request -- as every function /// here used to -- paid that cost on every single API call. Pool is a /// cheap, thread-safe, Arc-backed handle (see its own doc comment), so /// cloning the cached one out of the Mutex is effectively free. /// /// OnceLock alone can't hold this, since OnceLock::get_or_init's /// initializer isn't allowed to fail (get_or_try_init is still /// unstable, tracking issue 109737) and Pool::new can fail; a Mutex /// around an Option gives the same "build once" behavior while still /// letting a failed attempt be retried on the next call instead of /// wedging into a permanent error. /// /// `reset_connection` is off because it costs a full round trip per /// checkout (a COM_RESET_CONNECTION on return) and nothing here needs it: /// the queries are read-only and keep no session state. Against a /// database about 130 ms away that round trip tripled the cost of a /// query. /// /// `check_health` is a different trade, and on by default /// (`SQL_CHECK_HEALTH`): it pings a connection taken from the pool and /// replaces it if the ping fails, which is what keeps one server-side /// close of an idle session from turning into a failed request per dead /// socket. Turning it off saves a round trip -- worth it against a /// remote database, worth nothing against loopback -- but then a closed /// connection surfaces as `database_error` on the next request that /// happens to draw it, which is exactly what happened here on /// 2026-11-07: ten pooled connections went away and the next ten /// requests each 500'd with `CodecError { IO error: Broken pipe }`. fn pool() -> Result { let mut guard = POOL.get_or_init(|| Mutex::new(None)).lock().unwrap(); if let Some(pool) = guard.as_ref() { return Ok(pool.clone()); } let config = crate::config::Config::load(); let opts = OptsBuilder::from_opts( Opts::from_url(&sql_uri()).map_err(|e| DbError::Unavailable(e.to_string()))?, ) .pool_opts( PoolOpts::default() .with_reset_connection(false) .with_check_health(config.sql_check_health), ); let new_pool = Pool::new(opts).map_err(|e| DbError::Unavailable(e.to_string()))?; *guard = Some(new_pool.clone()); Ok(new_pool) } /// Does this error mean the *connection* went away, rather than the query /// being wrong? /// /// The driver reports a peer that closed under it as an IO error nested /// in its own wrapper (`CodecError { IO error: Broken pipe }`), or as /// `io::ErrorKind::{ConnectionReset, ConnectionAborted, UnexpectedEof}` /// depending on where it surfaces. These are the cases where retrying on /// a fresh connection is the right answer and the query is not at fault. pub fn is_connection_loss(error: &DbError) -> bool { let DbError::Query(error) = error else { return false; }; is_connection_loss_detail(&error.to_string()) } /// The same test against a driver message already rendered, so a caller /// that has to borrow the text anyway (for the error it returns) can ask /// the question without a second copy of the error. pub fn is_connection_loss_detail(message: &str) -> bool { let text = message.to_lowercase(); [ "broken pipe", "connection reset", "connection aborted", "unexpected eof", "server has gone away", "lost connection", "connection was closed", ] .iter() .any(|marker| text.contains(marker)) } /// Drop the cached pool, so the next call builds a fresh one. /// /// Called when a request reports a connection loss: the pool may be full /// of sockets the server has closed, and rebuilding it is the only way to /// stop the next requests drawing them one by one. Cheap when it happens /// (a rebuild opens 10 connections) and never when it does not. pub fn forget_pool() { if let Some(guard) = POOL.get() { if let Ok(mut slot) = guard.lock() { *slot = None; } } } /// The last database failure this process saw, so a caller's report of a /// 500 can be matched to a cause even when nobody was watching the log. /// /// Recorded in [`crate::error::ApiError::from_db`], i.e. at the one place /// every database error passes through, and read by `/version`: "the API /// answers but data endpoints fail" is otherwise indistinguishable from an /// authentication problem from the outside, which is exactly the confusion /// that sent a user to the wrong conclusion on 2026-11-08. static LAST_FAILURE: OnceLock>> = OnceLock::new(); /// How a database failure happened: what an operator needs to tell "the /// server is down" from "a connection died under us" from "the query was /// wrong". #[derive(Debug, Clone, Copy, PartialEq, Eq)] pub enum FailureKind { /// Could not build a connection/pool at all: the database is down or /// refuses this configuration. Unavailable, /// The server closed a connection we were using. The pool is rebuilt /// when this is seen, so it is usually recovered-from by the next /// request -- and it is the failure that *looks* like a server fault /// while being a socket-level event. ConnectionLost, /// A query the database rejected. Query, } impl FailureKind { pub fn as_str(self) -> &'static str { match self { FailureKind::Unavailable => "unavailable", FailureKind::ConnectionLost => "connection_lost", FailureKind::Query => "query", } } } /// One recorded failure: when, what kind, and the driver's message. #[derive(Debug, Clone)] pub struct Failure { pub at: std::time::Instant, pub kind: FailureKind, pub detail: String, } /// Remember `failure` as the last database failure. pub fn record_failure(kind: FailureKind, detail: impl Into) { if let Ok(mut slot) = LAST_FAILURE.get_or_init(|| Mutex::new(None)).lock() { *slot = Some(Failure { at: std::time::Instant::now(), kind, detail: detail.into(), }); } } /// The last recorded failure, if it was less than `max_age` ago. pub fn recent_failure(max_age: std::time::Duration) -> Option { let guard = LAST_FAILURE.get_or_init(|| Mutex::new(None)).lock().ok()?; let failure = guard.as_ref()?; (failure.at.elapsed() < max_age).then(|| failure.clone()) } /// Database reachability, cached for a few seconds: what `/version` /// reports, so "the service answers but nothing behind it works" is /// visible without trying a data endpoint. pub fn health() -> bool { static LAST: OnceLock>> = OnceLock::new(); let cache = LAST.get_or_init(|| Mutex::new(None)); if let Ok(guard) = cache.lock() { if let Some((checked_at, healthy)) = *guard { if checked_at.elapsed() < std::time::Duration::from_secs(10) { return healthy; } } } let healthy = probe().is_ok(); if let Ok(mut guard) = cache.lock() { *guard = Some((std::time::Instant::now(), healthy)); } healthy } /// Startup reachability probe: builds the shared pool (connecting to /// `SQL_URI`) and runs `SELECT 1`. `Ok(())` means the database is /// reachable; an `Err` is exactly what makes DB-backed routes answer /// 503. Used by src/main.rs to refuse (`production`) or warn loudly /// (`dev`) before serving, and by gn-indexer to refuse before touching /// an index. The built pool is cached, so a successful probe warms the /// pool the routes then reuse. pub fn probe() -> Result<(), DbError> { let pool = pool()?; let mut conn = pool.get_conn()?; conn.query_drop("SELECT 1")?; Ok(()) } /// GeneNetwork stores LRS (likelihood ratio statistic); LOD = LRS / 4.604 /// (the divisor GeneNetwork's RDF data uses; 2 ln 10 is 4.605). Every /// `lod` the API reports is derived with this one constant -- in SQL for rows read from the database, via `lod` for /// values that arrive as LRS (gn3 search hits). pub const LRS_PER_LOD: f64 = 4.604; /// The envelope note that goes with any response carrying `lod`. pub const LOD_NOTE: &str = "lod = lrs / 4.604"; /// LOD score for an LRS value; see LRS_PER_LOD. pub fn lod(lrs: f64) -> f64 { lrs / LRS_PER_LOD } #[derive(Serialize, utoipa::ToSchema)] pub struct Species { pub name: String, pub menu_name: Option, pub family: String, } /// Mirrors GeneNetwork3's gn3.db.species.get_all_species. pub fn get_all_species() -> Result, DbError> { let pool = pool()?; let mut conn = pool.get_conn()?; conn.query_map( "SELECT Name, MenuName, IFNULL(Family, 'None') FROM Species \ ORDER BY IFNULL(FamilyOrderId, SpeciesName) ASC, \ IFNULL(Family, SpeciesName) ASC, OrderId ASC", |(name, menu_name, family)| Species { name, menu_name, family, }, ) .map_err(DbError::from) } /// One full row of the Species table. #[derive(Serialize, utoipa::ToSchema)] pub struct SpeciesDetail { pub id: u16, pub species_id: Option, pub species_name: Option, pub name: String, pub menu_name: Option, pub full_name: String, pub family: Option, pub family_order_id: Option, pub taxonomy_id: Option, pub order_id: Option, } /// Columns of the Species row, in SELECT order. type SpeciesRow = ( u16, Option, Option, String, Option, String, Option, Option, Option, Option, ); /// Fetch one species by Name, FullName or SpeciesName, matching /// GeneNetwork2's wqflask.api.router.get_species_info, but returning /// every column of the Species table rather than a fixed subset. pub fn get_species_by_name(name: &str) -> Result, DbError> { let pool = pool()?; let mut conn = pool.get_conn()?; conn.exec_first( "SELECT Id, SpeciesId, SpeciesName, Name, MenuName, FullName, \ Family, FamilyOrderId, TaxonomyId, OrderId \ FROM Species WHERE Name = :name OR FullName = :name \ OR SpeciesName = :name", mysql::params! { "name" => name }, ) .map(|row: Option| { row.map( |( id, species_id, species_name, name, menu_name, full_name, family, family_order_id, taxonomy_id, order_id, )| { SpeciesDetail { id, species_id, species_name, name, menu_name, full_name, family, family_order_id, taxonomy_id, order_id, } }, ) }) .map_err(DbError::from) } /// Summary of one InbredSet row, matching the shape of GeneNetwork2's /// GET /api/v_pre1/groups response (field names there: Id, SpeciesId, /// DisplayName, Name, FullName, public, MappingMethodId, GeneticType). #[derive(Serialize, utoipa::ToSchema)] pub struct Group { pub id: u32, pub species_id: Option, pub display_name: Option, pub name: String, pub full_name: Option, pub public: Option, pub mapping_method_id: Option, pub genetic_type: Option, /// The population category, e.g. "Reference Populations (replicate /// average, SE, N)". pub family: Option, /// The species' short name, e.g. "mouse" (as listed by /species). pub species: Option, /// The mapping tool offered for the group, e.g. "qtlreaper" or /// "Rqtl" (the MappingMethod row `mapping_method_id` points to; see /// get_mapping_methods). pub mapping_method: Option, } /// Mirrors GeneNetwork2's wqflask.api.router.get_groups_list, plus the /// family and species name (filtering is done by the handler). pub fn get_all_groups() -> Result, DbError> { let pool = pool()?; let mut conn = pool.get_conn()?; conn.query_map( "SELECT InbredSet.InbredSetId, InbredSet.SpeciesId, InbredSet.InbredSetName, \ InbredSet.Name, InbredSet.FullName, InbredSet.public, \ InbredSet.MappingMethodId, InbredSet.GeneticType, InbredSet.Family, \ Species.Name, MappingMethod.Name \ FROM InbredSet \ LEFT JOIN Species ON Species.Id = InbredSet.SpeciesId \ LEFT JOIN MappingMethod ON MappingMethod.Id = InbredSet.MappingMethodId \ ORDER BY InbredSet.Name ASC", |( id, species_id, display_name, name, full_name, public, mapping_method_id, genetic_type, family, species, mapping_method, )| { Group { id, species_id, display_name, name, full_name, public, mapping_method_id, genetic_type, family, species, mapping_method, } }, ) .map_err(DbError::from) } /// One mapping tool GeneNetwork offers (a MappingMethod row), e.g. /// qtlreaper, Rqtl, HappyR, PLINK, FastMap. A group points at one of /// these through InbredSet.MappingMethodId (see `Group::mapping_method`); /// the table also lists tools no group uses. #[derive(Serialize, utoipa::ToSchema)] pub struct MappingMethod { pub id: u16, pub name: String, } pub fn get_mapping_methods() -> Result, DbError> { let pool = pool()?; let mut conn = pool.get_conn()?; conn.query_map( "SELECT Id, Name FROM MappingMethod ORDER BY Id", |(id, name)| MappingMethod { id, name }, ) .map_err(DbError::from) } /// A GeneNetwork accession number (the "GN602" of a dataset's page): an /// InfoFiles row, resolved to the dataset it describes. #[derive(Serialize, utoipa::ToSchema)] pub struct Accession { /// The number, without the "GN" prefix. pub accession: u32, /// The dataset's name (InfoFiles.InfoPageName), e.g. "BXDPublish". pub name: String, /// The page title, e.g. "BXD Published Phenotypes". pub title: Option, /// "publish" (a phenotype dataset) or "probeset" (an expression /// dataset); None when the name matches neither. pub category: Option, /// The short name of the group the dataset belongs to. pub group: Option, } /// Resolve an accession number: its InfoFiles row, then the dataset of /// that name (PublishFreeze, else ProbeSetFreeze) for its category and /// group. None when no InfoFiles row has the accession. pub fn get_accession(accession: u32) -> Result, DbError> { let pool = pool()?; let mut conn = pool.get_conn()?; let row: Option<(String, Option)> = conn.exec_first( "SELECT InfoPageName, InfoPageTitle FROM InfoFiles \ WHERE GN_AccesionId = :accession LIMIT 1", params! { "accession" => accession }, )?; let Some((name, title)) = row else { return Ok(None); }; let publish: Option = conn.exec_first( "SELECT InbredSet.Name FROM PublishFreeze \ JOIN InbredSet ON InbredSet.Id = PublishFreeze.InbredSetId \ WHERE PublishFreeze.Name = :name LIMIT 1", params! { "name" => &name }, )?; let (category, group) = match publish { Some(group) => (Some("publish".to_string()), Some(group)), None => { let probeset: Option = conn.exec_first( "SELECT InbredSet.Name FROM ProbeSetFreeze \ JOIN ProbeFreeze ON ProbeFreeze.ProbeFreezeId = ProbeSetFreeze.ProbeFreezeId \ JOIN InbredSet ON InbredSet.Id = ProbeFreeze.InbredSetId \ WHERE ProbeSetFreeze.Name = :name LIMIT 1", params! { "name" => &name }, )?; match probeset { Some(group) => (Some("probeset".to_string()), Some(group)), None => (None, None), } } }; Ok(Some(Accession { accession, name, title, category, group, })) } /// One full row of the InbredSet table. InbredSet has 14 columns, more /// than mysql_common's FromRow is implemented for tuples (up to 12), so /// this derives FromRow instead of being built from a tuple like /// SpeciesDetail above. FromRow matches by column name (PascalCase of /// the field name) unless overridden with `#[mysql(rename = "...")]`, /// needed here for `public` (lowercase in the schema) and for the /// fields below that use the "group" alias (see docs/genenetwork_datatypes.md) /// instead of "inbred_set" so InbredSet stays a MariaDB-only name. #[derive(Serialize, utoipa::ToSchema, FromRow)] #[mysql(rename_all = "PascalCase")] pub struct GroupDetail { pub id: u16, #[mysql(rename = "InbredSetId")] pub group_id: u32, #[mysql(rename = "InbredSetName")] pub display_name: Option, pub name: String, pub species_id: Option, pub full_name: Option, #[mysql(rename = "public")] pub public: Option, pub mapping_method_id: Option, pub genetic_type: Option, pub family: Option, pub family_order: Option, pub menu_order_id: f64, #[mysql(rename = "InbredSetCode")] pub group_code: Option, pub description: Option, /// The mapping tool's name (the MappingMethod row `mapping_method_id` /// points to), e.g. "qtlreaper". pub mapping_method: Option, } /// Fetch one group by InbredSetName, Name, FullName or numeric Id, /// matching GeneNetwork2's wqflask.api.router.get_group_info (which /// doesn't match by Id) plus gn-guile's gn.data.population:population-info /// (which only matches by Id), returning every column of the InbredSet /// table rather than a fixed subset. pub fn get_group_by_name(name: &str) -> Result, DbError> { let pool = pool()?; let mut conn = pool.get_conn()?; conn.exec_first( "SELECT InbredSet.Id, InbredSet.InbredSetId, InbredSet.InbredSetName, InbredSet.Name, \ InbredSet.SpeciesId, InbredSet.FullName, InbredSet.public, \ InbredSet.MappingMethodId, InbredSet.GeneticType, InbredSet.Family, \ InbredSet.FamilyOrder, InbredSet.MenuOrderId, InbredSet.InbredSetCode, \ InbredSet.Description, MappingMethod.Name AS MappingMethod \ FROM InbredSet \ LEFT JOIN MappingMethod ON MappingMethod.Id = InbredSet.MappingMethodId \ WHERE InbredSet.InbredSetName = :name OR InbredSet.Name = :name \ OR InbredSet.FullName = :name OR CAST(InbredSet.Id AS CHAR) = :name", mysql::params! { "name" => name }, ) .map_err(DbError::from) } /// One strain/sample belonging to a group, matching gn-guile's /// gn.data.strains:strain-id-names. #[derive(Serialize, utoipa::ToSchema)] pub struct Strain { pub id: u32, pub name: String, pub used_for_mapping: bool, } /// Fetch the strains/samples belonging to the group with the given /// InbredSet Id, optionally restricted to those used for mapping. /// Mirrors gn-guile's gn.data.strains:strain-id-names, which takes the /// same InbredSetId and used-for-mapping? arguments; unlike it, this /// returns strain names as well as ids. pub fn get_group_strains( group_id: u16, used_for_mapping: Option, ) -> Result, DbError> { let pool = pool()?; let mut conn = pool.get_conn()?; conn.exec_map( "SELECT StrainId, Strain.Name, Used_for_mapping \ FROM Strain, StrainXRef \ WHERE StrainXRef.StrainId = Strain.Id AND StrainXRef.InbredSetId = :group_id \ AND (:used_for_mapping IS NULL \ OR Used_for_mapping = IF(:used_for_mapping, 'Y', 'N')) \ ORDER BY StrainId", mysql::params! { "group_id" => group_id, "used_for_mapping" => used_for_mapping }, |(id, name, used_for_mapping): (u32, String, String)| Strain { id, name, used_for_mapping: used_for_mapping == "Y", }, ) .map_err(DbError::from) } /// One strain's value for a published (phenotype) trait, matching /// GeneNetwork2's wqflask.api.router.trait_sample_data (the Publish /// branch, trimmed to just name and value). #[derive(Serialize, utoipa::ToSchema)] pub struct TraitValue { pub name: String, pub value: Option, } /// Fetch the per-strain values of one published trait (a PublishXRef /// row) within a group. Mirrors gn-guile's /// gn.data.dataset:get-bxd-publish-name-value-dict, generalized to any /// group rather than hardcoding InbredSetId=1 (BXD); PublishXRef.Id /// alone isn't unique across groups (only the pair (InbredSetId, Id) /// is), so group_id is required to pick the right trait, the same way /// GeneNetwork2's trait_sample_data combines trait_name with a group /// or dataset to resolve a Publish trait. /// A group's `Name` from its `InbredSet.Id`. /// /// Needed where a route is keyed by a *dataset* name but the links it offers /// are group paths: BXDPublish's traits live in group BXD, and /// `/groups/BXDPublish/phewas` is a 404 (no genotype files for it). pub fn get_group_name(group_id: u16) -> Result, DbError> { let pool = pool()?; let mut conn = pool.get_conn()?; conn.exec_first( "SELECT Name FROM InbredSet WHERE Id = :id LIMIT 1", mysql::params! { "id" => group_id }, ) .map_err(DbError::from) } pub fn get_group_trait_values(group_id: u16, trait_id: u32) -> Result, DbError> { let pool = pool()?; let mut conn = pool.get_conn()?; conn.exec_map( "SELECT Strain.Name, PublishData.value \ FROM PublishData, Strain, PublishXRef \ WHERE PublishXRef.InbredSetId = :group_id AND PublishXRef.Id = :trait_id \ AND PublishXRef.DataId = PublishData.Id AND PublishData.StrainId = Strain.Id \ ORDER BY Strain.Name", mysql::params! { "group_id" => group_id, "trait_id" => trait_id }, |(name, value)| TraitValue { name, value }, ) .map_err(DbError::from) } /// One sample's value for one case attribute, in a trait's own sample /// order -- what `/groups/{group}/traits/{id}/case-attributes/{name}` /// returns, so a covariate lines up with the trait's values without the /// caller re-sorting anything. #[derive(Serialize)] pub struct TraitCaseAttribute { /// The sample (or strain) name, exactly as the trait's values use it. pub name: String, /// The attribute's text, `None` where the sample has no value for it. pub value: Option, } /// One attribute of one trait: its values on that trait's samples, and /// what the attribute means (`CaseAttribute.Description`, advertised /// because the names are not self-explanatory -- `SeqCvge` is "Depth of /// DNA sequencing for 1 male of each strain in 2016"). pub struct TraitCaseAttributeLookup { pub description: Option, pub values: Vec, } /// A trait's sample names, in the order its values come back. pub fn get_group_trait_samples(group_id: u16, trait_id: u32) -> Result, DbError> { Ok(get_group_trait_values(group_id, trait_id)? .into_iter() .map(|value| value.name) .collect()) } /// One case attribute's values for one trait, in the trait's sample order. /// /// `None` means the group does not define that attribute at all (an empty /// vector is a different answer: the attribute exists but no sample of /// this trait has a value for it). pub fn get_trait_case_attribute( group_id: u16, trait_id: u32, attribute: &str, ) -> Result, DbError> { let pool = pool()?; let mut conn = pool.get_conn()?; let defined: Option<(String, String)> = conn.exec_first( "SELECT Name, Description FROM CaseAttribute \ WHERE InbredSetId = :group_id AND Name = :attribute LIMIT 1", mysql::params! { "group_id" => group_id, "attribute" => attribute }, )?; let Some((_, description)) = defined else { return Ok(None); }; let description = description.trim().to_string(); let samples = get_group_trait_samples(group_id, trait_id)?; // The attribute's values for the whole group, then aligned onto the // trait's samples: the case-attribute table is keyed by // (InbredSetId, CaseAttributeId), the same pairing // get_group_case_attribute_values uses. let rows: Vec<(String, String)> = conn.exec( "SELECT Strain.Name, CaseAttributeXRefNew.Value \ FROM CaseAttribute \ JOIN CaseAttributeXRefNew \ ON CaseAttribute.CaseAttributeId = CaseAttributeXRefNew.CaseAttributeId \ AND CaseAttribute.InbredSetId = CaseAttributeXRefNew.InbredSetId \ JOIN Strain ON CaseAttributeXRefNew.StrainId = Strain.Id \ WHERE CaseAttributeXRefNew.InbredSetId = :group_id \ AND CaseAttribute.Name = :attribute", mysql::params! { "group_id" => group_id, "attribute" => attribute }, )?; let by_name: std::collections::HashMap = rows.into_iter() .map(|(strain, value)| { let value = value.trim().to_string(); (strain, value) }) .collect(); Ok(Some(TraitCaseAttributeLookup { description: (!description.is_empty()).then_some(description), values: samples .into_iter() .map(|name| { let value = by_name.get(&name).cloned().filter(|v| !v.is_empty()); TraitCaseAttribute { name, value } }) .collect(), })) } /// One published (phenotype) trait's metadata: its PublishXRef mapping /// stats plus its Phenotype description/units and Publication /// reference. See docs/genenetwork_datatypes.md for how these three /// tables relate. Phenotype and Publication are joined with LEFT JOIN /// (a handful of PublishXRef rows have a PhenotypeId/PublicationId /// with no matching row), so every field from them is an Option even /// though most are NOT NULL in their own table. /// /// PublishXRef has more columns than used here (e.g. Sequence, /// comments); only the ones GeneNetwork2's trait_info/precompute /// tooling actually look at are included. Derives FromRow rather than /// being built from a tuple, both because the combined column count is /// over the 12-element tuple limit (see GroupDetail above) and because /// three tables share column names (Id, Units, Year, ...) that need /// distinct SQL aliases; the aliases below are written to match /// mysql_common's UpperCamelCase column-name conversion exactly /// (e.g. `pubmed_id` looks for column "PubmedId", not "PubMedId"), so /// no field needs a `#[mysql(rename = "...")]` override. #[derive(Serialize, utoipa::ToSchema, FromRow)] #[mysql(rename_all = "PascalCase")] pub struct TraitInfo { pub id: u32, pub phenotype_id: u16, pub publication_id: u16, pub mean: Option, pub locus: Option, pub lrs: Option, pub lod: Option, pub additive: Option, pub nsamples: Option, pub pre_publication_description: Option, pub post_publication_description: Option, pub original_description: Option, pub units: Option, pub pubmed_id: Option, pub title: Option, pub authors: Option, pub journal: Option, pub volume: Option, pub pages: Option, pub month: Option, pub year: Option, /// The PublishFreeze this trait belongs to, e.g. "BXDPublish". pub dataset: Option, /// The trait's short name (Phenotype.Post_publication_abbreviation, /// else Pre_publication_abbreviation), e.g. "5HT_MB_M". pub abbreviation: Option, /// The trait's page on GeneNetwork's website. pub url: Option, } /// Where GeneNetwork's website shows one trait; see TraitInfo::url. const GN_SHOW_TRAIT_URL: &str = "https://genenetwork.org/show_trait"; /// A published trait's short name; shared by every query that reports /// or filters on it. const ABBREVIATION_SQL: &str = "COALESCE(NULLIF(Phenotype.Post_publication_abbreviation, ''), Phenotype.Pre_publication_abbreviation)"; /// The SELECT behind every TraitInfo row; callers add the WHERE /// `filter` and the ORDER BY. PublishFreeze is joined on InbredSetId, /// i.e. one dataset per group, as everywhere else in this API. fn trait_info_sql(filter: &str, order_by: &str) -> String { format!( "SELECT PublishXRef.Id AS Id, PublishXRef.PhenotypeId AS PhenotypeId, \ PublishXRef.PublicationId AS PublicationId, PublishXRef.mean AS Mean, \ PublishXRef.Locus AS Locus, PublishXRef.LRS AS Lrs, \ PublishXRef.LRS / {LRS_PER_LOD} AS Lod, \ PublishXRef.additive AS Additive, PublishXRef.NSamples AS Nsamples, \ Phenotype.Pre_publication_description AS PrePublicationDescription, \ Phenotype.Post_publication_description AS PostPublicationDescription, \ Phenotype.Original_description AS OriginalDescription, \ Phenotype.Units AS Units, \ Publication.PubMed_ID AS PubmedId, Publication.Title AS Title, \ Publication.Authors AS Authors, Publication.Journal AS Journal, \ Publication.Volume AS Volume, Publication.Pages AS Pages, \ Publication.Month AS Month, Publication.Year AS Year, \ PublishFreeze.Name AS Dataset, \ {ABBREVIATION_SQL} AS Abbreviation, \ CONCAT('{GN_SHOW_TRAIT_URL}?trait_id=', PublishXRef.Id, '&dataset=', \ PublishFreeze.Name) AS Url \ FROM PublishXRef \ LEFT JOIN Phenotype ON Phenotype.Id = PublishXRef.PhenotypeId \ LEFT JOIN Publication ON Publication.Id = PublishXRef.PublicationId \ LEFT JOIN PublishFreeze ON PublishFreeze.InbredSetId = PublishXRef.InbredSetId \ WHERE {filter} \ ORDER BY {order_by}" ) } /// Fetch one published trait's metadata (see TraitInfo) within a /// group. Like get_group_trait_values, PublishXRef.Id alone isn't /// unique across groups, so group_id is required. pub fn get_group_trait_info(group_id: u16, trait_id: u32) -> Result, DbError> { let pool = pool()?; let mut conn = pool.get_conn()?; conn.exec_first( trait_info_sql( "PublishXRef.InbredSetId = :group_id AND PublishXRef.Id = :trait_id", "PublishXRef.Id", ), mysql::params! { "group_id" => group_id, "trait_id" => trait_id }, ) .map_err(DbError::from) } /// Fetch every published trait's metadata (see TraitInfo) within a /// group, the listing equivalent of get_group_trait_info. pub fn get_group_traits(group_id: u16) -> Result, DbError> { let pool = pool()?; let mut conn = pool.get_conn()?; conn.exec( trait_info_sql("PublishXRef.InbredSetId = :group_id", "PublishXRef.Id"), mysql::params! { "group_id" => group_id }, ) .map_err(DbError::from) } /// The traits of a group whose short name (either the post- or the /// pre-publication abbreviation) is `abbreviation`. pub fn get_group_traits_by_abbreviation( group_id: u16, abbreviation: &str, ) -> Result, DbError> { let pool = pool()?; let mut conn = pool.get_conn()?; conn.exec( trait_info_sql( "PublishXRef.InbredSetId = :group_id \ AND (Phenotype.Post_publication_abbreviation = :abbreviation \ OR Phenotype.Pre_publication_abbreviation = :abbreviation)", "PublishXRef.Id", ), mysql::params! { "group_id" => group_id, "abbreviation" => abbreviation }, ) .map_err(DbError::from) } /// Published traits of a group whose peak marker is one of `loci`, /// strongest first -- a PheWAS at one locus. /// /// `PublishXRef.Locus` is the marker a trait's QTL peaks at, so this is /// "every trait peaking at these markers", which combined with a window /// around one position (see src/routes/phewas.rs) is a PheWAS around that /// position. pub fn get_phewas_traits(group_id: u16, loci: &[String]) -> Result, DbError> { if loci.is_empty() { return Ok(Vec::new()); } // Only names that came out of the deployment's marker table reach // here, and they are re-checked anyway: a marker name is // alphanumeric plus `_.:-`, so a quote can never get into the IN // list below. let safe: Vec<&str> = loci .iter() .map(String::as_str) .filter(|locus| { !locus.is_empty() && locus .chars() .all(|c| c.is_ascii_alphanumeric() || "_.:-".contains(c)) }) .collect(); if safe.is_empty() { return Ok(Vec::new()); } let pool = pool()?; let mut conn = pool.get_conn()?; let mut traits = Vec::new(); // Keep the IN list modest for the query planner, and the result // order stable across chunks by sorting once at the end. for chunk in safe.chunks(200) { let list = chunk .iter() .map(|locus| format!("'{locus}'")) .collect::>() .join(","); let mut rows: Vec = conn.exec( trait_info_sql( &format!("PublishXRef.InbredSetId = :group_id AND PublishXRef.Locus IN ({list})"), "PublishXRef.LRS DESC", ), mysql::params! { "group_id" => group_id }, )?; traits.append(&mut rows); } traits.sort_by(|a, b| { b.lrs .unwrap_or(0.0) .partial_cmp(&a.lrs.unwrap_or(0.0)) .unwrap_or(std::cmp::Ordering::Equal) }); Ok(traits) } /// Every published trait (across groups) reported in the publication /// with this PubMed id, or None when no such publication exists. pub fn get_publication_traits(pubmed_id: u32) -> Result>, DbError> { let pool = pool()?; let mut conn = pool.get_conn()?; let exists: Option = conn.exec_first( "SELECT Id FROM Publication WHERE PubMed_ID = :pubmed_id LIMIT 1", mysql::params! { "pubmed_id" => pubmed_id }, )?; if exists.is_none() { return Ok(None); } conn.exec( trait_info_sql( "Publication.PubMed_ID = :pubmed_id", "PublishFreeze.Name, PublishXRef.Id", ), mysql::params! { "pubmed_id" => pubmed_id }, ) .map(Some) .map_err(DbError::from) } /// What search hits are annotated with from PublishXRef/Phenotype /// (gn3's hits carry neither). #[derive(Default, Clone)] pub struct PublishAnnotation { pub locus: Option, pub abbreviation: Option, } /// Peak marker names and short names for published traits, keyed by /// (dataset name, PublishXRef.Id), in one query. Traits that don't /// exist are absent from the result. pub fn get_publish_annotations( keys: &[(String, u32)], ) -> Result, DbError> { let pool = pool()?; let mut conn = pool.get_conn()?; let pairs = vec!["(?, ?)"; keys.len()].join(", "); let rows: Vec<(String, u32, Option, Option)> = conn.exec( format!( "SELECT PublishFreeze.Name, PublishXRef.Id, PublishXRef.Locus, {ABBREVIATION_SQL} \ FROM PublishXRef \ JOIN PublishFreeze ON PublishFreeze.InbredSetId = PublishXRef.InbredSetId \ LEFT JOIN Phenotype ON Phenotype.Id = PublishXRef.PhenotypeId \ WHERE (PublishFreeze.Name, PublishXRef.Id) IN ({pairs})" ), keys.iter() .flat_map(|(dataset, id)| [mysql::Value::from(dataset), mysql::Value::from(*id)]) .collect::>(), )?; Ok(rows .into_iter() .map(|(dataset, id, locus, abbreviation)| { ( (dataset, id), PublishAnnotation { locus, abbreviation, }, ) }) .collect()) } /// One expression (ProbeSet) dataset: a ProbeSetFreeze row, matching /// the shape of GeneNetwork2's GET /api/v_pre1/datasets/ /// response (field names there: Id, ProbeFreezeId, AvgID, /// Short_Abbreviation, Long_Abbreviation, FullName, ShortName, /// CreateTime, public, confidentiality, DataScale -- renamed here the /// same way GroupDetail renames away from "inbred_set": Name/Name2 are /// clearer than GN2's legacy "Short_Abbreviation"/"Long_Abbreviation"). #[derive(Serialize, utoipa::ToSchema)] pub struct Dataset { pub id: u16, pub probe_freeze_id: u16, pub name: String, pub name2: Option, pub full_name: String, pub short_name: String, pub create_time: String, pub public: u8, pub confidentiality: u8, pub data_scale: String, /// What the dataset was measured in, e.g. "Retina mRNA" (the Tissue /// row its ProbeFreeze points to). pub tissue: Option, } /// Fetch the expression datasets belonging to a group. Mirrors /// GeneNetwork2's wqflask.api.router.get_datasets_for_group (without /// its species-scoped variant, which this API doesn't need: a group /// name alone already picks one species). pub fn get_group_datasets(group_id: u16) -> Result, DbError> { let pool = pool()?; let mut conn = pool.get_conn()?; conn.exec_map( "SELECT ProbeSetFreeze.Id, ProbeSetFreeze.ProbeFreezeId, ProbeSetFreeze.Name, \ ProbeSetFreeze.Name2, ProbeSetFreeze.FullName, ProbeSetFreeze.ShortName, \ CAST(ProbeSetFreeze.CreateTime AS CHAR), ProbeSetFreeze.public, \ ProbeSetFreeze.confidentiality, ProbeSetFreeze.DataScale, Tissue.Name \ FROM ProbeSetFreeze \ JOIN ProbeFreeze ON ProbeFreeze.ProbeFreezeId = ProbeSetFreeze.ProbeFreezeId \ LEFT JOIN Tissue ON Tissue.Id = ProbeFreeze.TissueId \ WHERE ProbeFreeze.InbredSetId = :group_id \ ORDER BY ProbeSetFreeze.Name", mysql::params! { "group_id" => group_id }, |( id, probe_freeze_id, name, name2, full_name, short_name, create_time, public, confidentiality, data_scale, tissue, )| { Dataset { id, probe_freeze_id, name, name2, full_name, short_name, create_time, public, confidentiality, data_scale, tissue, } }, ) .map_err(DbError::from) } /// One strain's case-attribute values within a group: extra per-strain /// metadata (e.g. age, sex, treatment) beyond genotype/phenotype data, /// matching GeneNetwork3's gn3.api.case_attributes (genenetwork4's own /// read-only equivalent of its /case-attribute/{id}/values -- the only /// place this concept exists; neither GeneNetwork2 nor gn-guile expose /// it). Keyed by attribute name rather than a fixed field set, since /// which attributes exist is itself per-group data (see CaseAttribute /// below). #[derive(Serialize, utoipa::ToSchema)] pub struct StrainCaseAttributes { pub strain: String, pub case_attributes: std::collections::HashMap, } /// A group's case attributes: every attribute name defined for it /// (`CaseAttribute.Name`), what each one means (`CaseAttribute.Description`), /// and, for each strain that has at least one value set, that strain's /// values -- combined into one response rather than GN3's separate /// .../names and .../values endpoints, since neither list is large enough /// on its own to need splitting. The descriptions are what make the names /// usable: `SeqCvge` is unguessable, and its description -- "Depth of DNA /// sequencing for 1 male of each strain in 2016" -- says both what it is /// and why it is not a phenotype. #[derive(Serialize, utoipa::ToSchema)] pub struct CaseAttributes { pub names: Vec, /// Attribute name -> description, for the names that have one. A /// description can be empty in the database; those are left out /// rather than served as "". pub descriptions: std::collections::BTreeMap, pub values: Vec, } /// Fetch every case-attribute name and description defined for a group, /// matching GeneNetwork3's /// gn3.api.case_attributes.__case_attribute_labels_by_inbred_set__ (which /// selects the name only; `CaseAttribute.Description` is what genenetwork4 /// adds -- CaseAttributeId/InbredSetId are already implied by this being /// scoped to one group). fn get_group_case_attribute_definitions(group_id: u16) -> Result, DbError> { let pool = pool()?; let mut conn = pool.get_conn()?; conn.exec( "SELECT Name, Description FROM CaseAttribute \ WHERE InbredSetId = :group_id ORDER BY Name", mysql::params! { "group_id" => group_id }, ) .map_err(DbError::from) } /// Fetch every strain's case-attribute values for a group, based on /// GeneNetwork3's gn3.api.case_attributes.__case_attribute_values_by_inbred_set__, /// fixed to also join on InbredSetId: `CaseAttributeId` alone is not /// unique across groups (it's a shared vocabulary -- e.g. id 37 means /// "Epoch" for dozens of unrelated groups at once; the table's real /// key is the pair `(InbredSetId, CaseAttributeId)`), so GN3's version /// of this join -- `ON ca.CaseAttributeId=caxrn.CaseAttributeId`, /// `WHERE caxrn.InbredSetId=...` with no matching condition on `ca`'s /// own InbredSetId -- pulls in every other group's attribute that /// happens to share a `CaseAttributeId` number, not just this group's. /// Confirmed against this database: without the extra join condition /// below, BXD's (group 1) result included a spurious "Strain" = "Live" /// attribute that belongs to a different, unrelated group entirely. /// Rows come back one (strain, attribute, value) triple at a time, /// ordered by strain name, so adjacent rows for the same strain are /// folded into one `StrainCaseAttributes` as they're read rather than /// grouped afterwards. fn get_group_case_attribute_values(group_id: u16) -> Result, DbError> { let pool = pool()?; let mut conn = pool.get_conn()?; let rows: Vec<(String, String, String)> = conn.exec( "SELECT s.Name, ca.Name, caxrn.Value \ FROM CaseAttribute AS ca \ JOIN CaseAttributeXRefNew AS caxrn \ ON ca.CaseAttributeId = caxrn.CaseAttributeId \ AND ca.InbredSetId = caxrn.InbredSetId \ JOIN Strain AS s ON caxrn.StrainId = s.Id \ WHERE caxrn.InbredSetId = :group_id \ ORDER BY s.Name", mysql::params! { "group_id" => group_id }, )?; let mut values: Vec = Vec::new(); for (strain, attribute_name, attribute_value) in rows { match values.last_mut() { Some(last) if last.strain == strain => { last.case_attributes.insert(attribute_name, attribute_value); } _ => { let mut case_attributes = std::collections::HashMap::new(); case_attributes.insert(attribute_name, attribute_value); values.push(StrainCaseAttributes { strain, case_attributes, }); } } } Ok(values) } /// Fetch a group's case attributes: every attribute name defined for /// it, and every strain's values for them. See `CaseAttributes`. pub fn get_group_case_attributes(group_id: u16) -> Result { let definitions = get_group_case_attribute_definitions(group_id)?; let mut names = Vec::with_capacity(definitions.len()); let mut descriptions = std::collections::BTreeMap::new(); for (name, description) in definitions { let description = description.trim().to_string(); if !description.is_empty() { descriptions.insert(name.clone(), description); } names.push(name); } Ok(CaseAttributes { names, descriptions, values: get_group_case_attribute_values(group_id)?, }) } /// Fetch the expression datasets belonging to a species (across all of /// its groups), the species-scoped equivalent of get_group_datasets. /// GeneNetwork2's own species-scoped variant of /// wqflask.api.router.get_datasets_for_group still requires a group /// name (species only disambiguates it); this doesn't need one. pub fn get_species_datasets(species_id: u16) -> Result, DbError> { let pool = pool()?; let mut conn = pool.get_conn()?; conn.exec_map( "SELECT ProbeSetFreeze.Id, ProbeSetFreeze.ProbeFreezeId, ProbeSetFreeze.Name, \ ProbeSetFreeze.Name2, ProbeSetFreeze.FullName, ProbeSetFreeze.ShortName, \ CAST(ProbeSetFreeze.CreateTime AS CHAR), ProbeSetFreeze.public, \ ProbeSetFreeze.confidentiality, ProbeSetFreeze.DataScale, Tissue.Name \ FROM ProbeSetFreeze \ JOIN ProbeFreeze ON ProbeFreeze.ProbeFreezeId = ProbeSetFreeze.ProbeFreezeId \ JOIN InbredSet ON InbredSet.Id = ProbeFreeze.InbredSetId \ LEFT JOIN Tissue ON Tissue.Id = ProbeFreeze.TissueId \ WHERE InbredSet.SpeciesId = :species_id \ ORDER BY ProbeSetFreeze.Name", mysql::params! { "species_id" => species_id }, |( id, probe_freeze_id, name, name2, full_name, short_name, create_time, public, confidentiality, data_scale, tissue, )| { Dataset { id, probe_freeze_id, name, name2, full_name, short_name, create_time, public, confidentiality, data_scale, tissue, } }, ) .map_err(DbError::from) } /// Columns of the Dataset row, in SELECT order. type DatasetRow = ( u16, u16, String, Option, String, String, String, u8, u8, String, Option, ); /// Fetch one dataset by Name, Name2, FullName, ShortName or numeric /// Id, matching GeneNetwork2's wqflask.api.router.get_dataset_info's /// matching logic for its ProbeSet (mRNA expression) branch. pub fn get_dataset_by_name(name: &str) -> Result, DbError> { let pool = pool()?; let mut conn = pool.get_conn()?; conn.exec_first( "SELECT ProbeSetFreeze.Id, ProbeSetFreeze.ProbeFreezeId, ProbeSetFreeze.Name, \ ProbeSetFreeze.Name2, ProbeSetFreeze.FullName, ProbeSetFreeze.ShortName, \ CAST(ProbeSetFreeze.CreateTime AS CHAR), ProbeSetFreeze.public, \ ProbeSetFreeze.confidentiality, ProbeSetFreeze.DataScale, Tissue.Name \ FROM ProbeSetFreeze \ LEFT JOIN ProbeFreeze ON ProbeFreeze.ProbeFreezeId = ProbeSetFreeze.ProbeFreezeId \ LEFT JOIN Tissue ON Tissue.Id = ProbeFreeze.TissueId \ WHERE ProbeSetFreeze.Name = :name OR ProbeSetFreeze.Name2 = :name \ OR ProbeSetFreeze.FullName = :name OR ProbeSetFreeze.ShortName = :name \ OR CAST(ProbeSetFreeze.Id AS CHAR) = :name", mysql::params! { "name" => name }, ) .map(|row: Option| { row.map( |( id, probe_freeze_id, name, name2, full_name, short_name, create_time, public, confidentiality, data_scale, tissue, )| { Dataset { id, probe_freeze_id, name, name2, full_name, short_name, create_time, public, confidentiality, data_scale, tissue, } }, ) }) .map_err(DbError::from) } /// One row of the PublishFreeze table -- the Publish/phenotype /// equivalent of Dataset (ProbeSetFreeze), used by resolve_trait_dataset /// below to let /traits/{dataset}/... address phenotype data the same /// way it addresses expression data. Unlike ProbeSetFreeze, a group /// normally has only one PublishFreeze, so PublishXRef is still queried /// by inbred_set_id (get_group_trait_info/get_group_trait_values/ /// get_group_traits, unchanged) rather than by this row's own id. #[derive(Serialize, utoipa::ToSchema, FromRow)] #[mysql(rename_all = "PascalCase")] pub struct PublishDataset { pub id: u16, pub inbred_set_id: u16, pub name: String, pub short_name: String, pub full_name: String, pub create_time: String, #[mysql(rename = "public")] pub public: u8, #[mysql(rename = "confidentiality")] pub confidentiality: u8, } /// Fetch one PublishFreeze by its own Id/Name/ShortName, or by its /// owning group's Id/Name/InbredSetName/FullName -- mirroring /// GeneNetwork2's wqflask.api.router.trait_sample_data's Publish /// branch, which accepts either (e.g. "BXDPublish" or plain "BXD"). pub fn get_publish_freeze_by_name(name: &str) -> Result, DbError> { let pool = pool()?; let mut conn = pool.get_conn()?; conn.exec_first( "SELECT PublishFreeze.Id, PublishFreeze.InbredSetId, PublishFreeze.Name, \ PublishFreeze.ShortName, PublishFreeze.FullName, \ CAST(PublishFreeze.CreateTime AS CHAR) AS CreateTime, \ PublishFreeze.public, PublishFreeze.confidentiality \ FROM PublishFreeze \ LEFT JOIN InbredSet ON InbredSet.Id = PublishFreeze.InbredSetId \ WHERE PublishFreeze.Name = :name OR PublishFreeze.ShortName = :name \ OR CAST(PublishFreeze.Id AS CHAR) = :name \ OR InbredSet.Name = :name OR InbredSet.InbredSetName = :name \ OR InbredSet.FullName = :name OR CAST(InbredSet.Id AS CHAR) = :name", mysql::params! { "name" => name }, ) .map_err(DbError::from) } /// Which category a `/traits/{dataset}/...` request's dataset resolved /// to -- see resolve_trait_dataset. pub enum TraitDataset { ProbeSet(Dataset), Publish(PublishDataset), } /// Resolve a dataset name shared by the /traits/... endpoints to the /// one category it actually belongs to: ProbeSetFreeze is tried first, /// then PublishFreeze (a dataset name is expected to match at most one /// of these). Geno/GenoFreeze isn't supported yet, matching the rest /// of this API. pub fn resolve_trait_dataset(name: &str) -> Result, DbError> { if let Some(dataset) = get_dataset_by_name(name)? { return Ok(Some(TraitDataset::ProbeSet(dataset))); } if let Some(publish) = get_publish_freeze_by_name(name)? { return Ok(Some(TraitDataset::Publish(publish))); } Ok(None) } /// One expression (ProbeSet) trait's metadata: its ProbeSetXRef /// mapping stats plus its ProbeSet gene annotation. The expression /// equivalent of TraitInfo (for Publish/phenotype traits) above, but /// addressed differently: ProbeSetXRef has no single Id column of its /// own (its primary key is DataId; the natural key is the pair /// (ProbeSetFreezeId, ProbeSetId)), so a trait here is identified by /// dataset name plus ProbeSet.Name rather than a single numeric id. #[derive(Serialize, utoipa::ToSchema)] pub struct ProbeSetInfo { pub probeset_id: u32, pub name: String, pub symbol: Option, pub description: Option, pub chr: Option, pub mb: Option, pub mean: Option, pub locus: Option, pub lrs: Option, pub lod: Option, pub p_value: Option, pub additive: Option, } /// Columns of the joined ProbeSet row, in SELECT order. type ProbeSetInfoRow = ( u32, String, Option, Option, Option, Option, Option, Option, Option, Option, Option, Option, ); /// Fetch one expression trait's metadata (see ProbeSetInfo) within a /// dataset, matching GeneNetwork2's wqflask.api.router.get_trait_info's /// ProbeSet branch (which additionally takes a Mb/Chr pair from /// ProbeSet, as done here, though GN2 doesn't join ProbeSetXRef's /// pValue). pub fn get_dataset_probeset_info( dataset_id: u16, probeset_name: &str, ) -> Result, DbError> { let pool = pool()?; let mut conn = pool.get_conn()?; conn.exec_first( format!( "SELECT ProbeSet.Id, ProbeSet.Name, ProbeSet.Symbol, ProbeSet.description, \ ProbeSet.Chr, ProbeSet.Mb, ProbeSetXRef.mean, ProbeSetXRef.Locus, \ ProbeSetXRef.LRS, ProbeSetXRef.LRS / {LRS_PER_LOD}, ProbeSetXRef.pValue, \ ProbeSetXRef.additive \ FROM ProbeSet \ JOIN ProbeSetXRef ON ProbeSetXRef.ProbeSetId = ProbeSet.Id \ WHERE ProbeSetXRef.ProbeSetFreezeId = :dataset_id AND ProbeSet.Name = :probeset_name" ), mysql::params! { "dataset_id" => dataset_id, "probeset_name" => probeset_name }, ) .map(|row: Option| { row.map( |( probeset_id, name, symbol, description, chr, mb, mean, locus, lrs, lod, p_value, additive, )| { ProbeSetInfo { probeset_id, name, symbol, description, chr, mb, mean, locus, lrs, lod, p_value, additive, } }, ) }) .map_err(DbError::from) } /// Fetch every expression (ProbeSet) trait's metadata (see /// ProbeSetInfo) within a dataset, the listing equivalent of /// get_dataset_probeset_info. pub fn get_dataset_probesets(dataset_id: u16) -> Result, DbError> { let pool = pool()?; let mut conn = pool.get_conn()?; conn.exec_map( format!( "SELECT ProbeSet.Id, ProbeSet.Name, ProbeSet.Symbol, ProbeSet.description, \ ProbeSet.Chr, ProbeSet.Mb, ProbeSetXRef.mean, ProbeSetXRef.Locus, \ ProbeSetXRef.LRS, ProbeSetXRef.LRS / {LRS_PER_LOD}, ProbeSetXRef.pValue, \ ProbeSetXRef.additive \ FROM ProbeSet \ JOIN ProbeSetXRef ON ProbeSetXRef.ProbeSetId = ProbeSet.Id \ WHERE ProbeSetXRef.ProbeSetFreezeId = :dataset_id \ ORDER BY ProbeSet.Id" ), mysql::params! { "dataset_id" => dataset_id }, |( probeset_id, name, symbol, description, chr, mb, mean, locus, lrs, lod, p_value, additive, )| { ProbeSetInfo { probeset_id, name, symbol, description, chr, mb, mean, locus, lrs, lod, p_value, additive, } }, ) .map_err(DbError::from) } /// One strain's value for an expression (ProbeSet) trait, matching /// GeneNetwork2's wqflask.api.router.trait_sample_data (the ProbeSet /// branch, trimmed to just name and value); the expression equivalent /// of TraitValue (for Publish/phenotype traits) above. #[derive(Serialize, utoipa::ToSchema)] pub struct ProbeSetValue { pub name: String, pub value: Option, } /// Fetch the per-strain values of one expression trait within a /// dataset, addressed the same way as get_dataset_probeset_info. pub fn get_dataset_probeset_values( dataset_id: u16, probeset_name: &str, ) -> Result, DbError> { let pool = pool()?; let mut conn = pool.get_conn()?; conn.exec_map( "SELECT Strain.Name, ProbeSetData.value \ FROM ProbeSetData, Strain, ProbeSetXRef, ProbeSet \ WHERE ProbeSetXRef.ProbeSetFreezeId = :dataset_id AND ProbeSet.Name = :probeset_name \ AND ProbeSetXRef.ProbeSetId = ProbeSet.Id \ AND ProbeSetXRef.DataId = ProbeSetData.Id AND ProbeSetData.StrainId = Strain.Id \ ORDER BY Strain.Name", mysql::params! { "dataset_id" => dataset_id, "probeset_name" => probeset_name }, |(name, value)| ProbeSetValue { name, value }, ) .map_err(DbError::from) } /// The short group name a dataset belongs to (`InbredSet.Name`), which is how /// genotype files are named. pub fn get_dataset_group_name(dataset_name: &str) -> Result, DbError> { let pool = pool()?; let mut conn = pool.get_conn()?; let probeset: Option = conn .exec_first( "SELECT InbredSet.Name FROM ProbeSetFreeze \ JOIN ProbeFreeze ON ProbeFreeze.ProbeFreezeId = ProbeSetFreeze.ProbeFreezeId \ JOIN InbredSet ON InbredSet.Id = ProbeFreeze.InbredSetId \ WHERE ProbeSetFreeze.Name = :name LIMIT 1", mysql::params! { "name" => dataset_name }, ) .map_err(DbError::from)?; if probeset.is_some() { return Ok(probeset); } conn.exec_first( "SELECT InbredSet.Name FROM PublishFreeze \ JOIN InbredSet ON InbredSet.Id = PublishFreeze.InbredSetId \ WHERE PublishFreeze.Name = :name LIMIT 1", mysql::params! { "name" => dataset_name }, ) .map_err(DbError::from) } /// The group's strains that are used for mapping, the fallback sample axis /// when a group has no genotype file on this machine. pub fn get_group_mapping_samples(group_name: &str) -> Result, DbError> { let Some(group) = get_group_by_name(group_name)? else { return Ok(Vec::new()); }; Ok(get_group_strains(group.id, Some(true))? .into_iter() .map(|strain| strain.name) .collect()) } /// The species of the group a dataset belongs to (`InbredSet.SpeciesId` -> /// `Species.Name`), which is how GN2 decides whether a gene id needs /// translating to mouse before it is looked up in `LCorrRamin3`. pub fn get_dataset_species(dataset_name: &str) -> Result, DbError> { let pool = pool()?; let mut conn = pool.get_conn()?; conn.exec_first( "SELECT Species.Name FROM ProbeSetFreeze \ JOIN ProbeFreeze ON ProbeFreeze.ProbeFreezeId = ProbeSetFreeze.ProbeFreezeId \ JOIN InbredSet ON InbredSet.Id = ProbeFreeze.InbredSetId \ JOIN Species ON Species.Id = InbredSet.SpeciesId \ WHERE ProbeSetFreeze.Name = :name LIMIT 1", mysql::params! { "name" => dataset_name }, ) .map_err(DbError::from) } /// `ProbeSet.Name` -> `ProbeSet.Symbol` for one probeset dataset, the axis /// the tissue correlation is keyed on. pub fn get_dataset_symbols(dataset_id: u16) -> Result, DbError> { let pool = pool()?; let mut conn = pool.get_conn()?; conn.exec_map( "SELECT ProbeSet.Name, ProbeSet.Symbol \ FROM ProbeSet JOIN ProbeSetXRef ON ProbeSetXRef.ProbeSetId = ProbeSet.Id \ WHERE ProbeSetXRef.ProbeSetFreezeId = :dataset_id \ AND ProbeSet.Symbol IS NOT NULL AND ProbeSet.Symbol <> '' \ ORDER BY ProbeSet.Id", mysql::params! { "dataset_id" => dataset_id }, |(name, symbol): (String, String)| (name, symbol), ) .map_err(DbError::from) } /// `ProbeSet.Name` -> `ProbeSetXRef.GeneId` for one probeset dataset, the axis /// the literature correlation is keyed on. pub fn get_dataset_geneids(dataset_id: u16) -> Result, DbError> { let pool = pool()?; let mut conn = pool.get_conn()?; conn.exec_map( // The gene id lives on ProbeSet, not on ProbeSetXRef: GN3's own mapping // is `ProbeSet.GeneId`, and ProbeSetXRef has no GeneId column here. "SELECT ProbeSet.Name, ProbeSet.GeneId \ FROM ProbeSet JOIN ProbeSetXRef ON ProbeSetXRef.ProbeSetId = ProbeSet.Id \ WHERE ProbeSetXRef.ProbeSetFreezeId = :dataset_id \ AND ProbeSet.GeneId IS NOT NULL AND ProbeSet.GeneId <> '' \ ORDER BY ProbeSet.Id", mysql::params! { "dataset_id" => dataset_id }, |(name, gene_id): (String, String)| (name, gene_id), ) .map_err(DbError::from) } /// Tissue values keyed by *symbol*, as `(TissueID, value)` pairs. /// /// GN2 reduces a symbol to one probe -- the one with the largest `Mean` in /// `TissueProbeSetXRef` -- and then reads that probe's values. It keeps them as /// a bare list and pairs two lists *by position*, which is only meaningful if /// both probes cover the same tissues: on this database they do not. The /// values are keyed by `TissueID` here so a caller can pair them by tissue /// rather than by position. pub fn get_tissue_values( symbols: &[String], ) -> Result>, DbError> { if symbols.is_empty() { return Ok(std::collections::HashMap::new()); } let pool = pool()?; let mut conn = pool.get_conn()?; let placeholders = std::iter::repeat("?") .take(symbols.len()) .collect::>() .join(", "); let rows: Vec<(String, u16, f64)> = conn.exec( format!( "SELECT xref.Symbol, data.TissueID, data.value \ FROM TissueProbeSetData data \ JOIN TissueProbeSetXRef xref ON xref.DataId = data.Id \ JOIN (SELECT Symbol, MAX(Mean) AS best_mean FROM TissueProbeSetXRef \ WHERE TissueProbeSetFreezeId = 1 AND Symbol IN ({placeholders}) \ GROUP BY Symbol) best \ ON best.Symbol = xref.Symbol AND xref.Mean = best.best_mean \ WHERE xref.TissueProbeSetFreezeId = 1 \ ORDER BY xref.Symbol, data.TissueID" ), symbols.iter().collect::>(), )?; let mut out: std::collections::HashMap> = std::collections::HashMap::new(); for (symbol, tissue_id, value) in rows { out.entry(symbol.to_lowercase()).or_default().push((tissue_id, value)); } Ok(out) } /// Gene ids translated to the mouse id `LCorrRamin3` is keyed on. /// /// GN2's `convert_to_mouse_gene_id`: a mouse id is used as it stands, a rat or /// human id is looked up in `GeneIDXRef`. pub fn mouse_gene_ids( species: &str, gene_ids: &[String], ) -> Result, DbError> { let mut out = std::collections::HashMap::new(); if gene_ids.is_empty() { return Ok(out); } let numeric: Vec<(String, i64)> = gene_ids .iter() .filter_map(|g| g.trim().parse::().ok().map(|v| (g.clone(), v))) .collect(); if species.eq_ignore_ascii_case("mouse") { for (raw, value) in numeric { out.insert(raw, value); } return Ok(out); } let column = match species.to_ascii_lowercase().as_str() { "rat" => "rat", "human" => "human", _ => return Ok(out), }; let pool = pool()?; let mut conn = pool.get_conn()?; let placeholders = std::iter::repeat("?") .take(numeric.len()) .collect::>() .join(", "); let rows: Vec<(i64, i64)> = conn.exec( format!("SELECT {column}, mouse FROM GeneIDXRef WHERE {column} IN ({placeholders})"), numeric.iter().map(|(_, value)| *value).collect::>(), )?; let by_source: std::collections::HashMap = rows.into_iter().collect(); for (raw, value) in numeric { if let Some(mouse) = by_source.get(&value) { out.insert(raw, *mouse); } } Ok(out) } /// `LCorrRamin3` values for one reference gene against many targets, in both /// directions, preferring GN2's direction (`GeneId1` = target, `GeneId2` = /// reference) where both rows exist. /// /// Chunked because a probeset dataset has tens of thousands of gene ids and /// MySQL refuses a statement with more than 65 535 placeholders (measured: /// "Prepared statement contains too many placeholders" on the first attempt). pub fn get_literature_values( reference: i64, targets: &[i64], ) -> Result, DbError> { let mut forward: std::collections::HashMap = std::collections::HashMap::new(); let mut reverse: std::collections::HashMap = std::collections::HashMap::new(); for chunk in targets.chunks(LITERATURE_CHUNK) { let pool = pool()?; let mut conn = pool.get_conn()?; let placeholders = std::iter::repeat("?") .take(chunk.len()) .collect::>() .join(", "); let mut params: Vec = Vec::with_capacity(chunk.len() * 2 + 2); params.push(reference); params.extend(chunk.iter().copied()); params.push(reference); params.extend(chunk.iter().copied()); let rows: Vec<(i64, i64, f64)> = conn.exec( format!( "SELECT GeneId1, GeneId2, value FROM LCorrRamin3 \ WHERE (GeneId2 = ? AND GeneId1 IN ({placeholders})) \ OR (GeneId1 = ? AND GeneId2 IN ({placeholders}))" ), params, )?; for (gene1, gene2, value) in rows { if gene2 == reference { forward.insert(gene1, value); } else if gene1 == reference { reverse.insert(gene2, value); } } } for (target, value) in reverse { forward.entry(target).or_insert(value); } Ok(forward) } /// How many genes go into one `IN (...)` clause: MySQL's placeholder ceiling is /// 65 535 per statement, and this query uses two lists. const LITERATURE_CHUNK: usize = 4_000; /// Every value of every trait in a probeset dataset, restricted to a set of /// individuals: one record per (trait, individual, value). /// /// This is the bulk half of the sample-axis correlation, and the whole cost of /// it: `ProbeSetData` is 5.3e9 rows, so the query narrows by the dataset and by /// the individuals the reference trait has values for, and never loads a /// dataset whole. Ordering is by trait then individual so the caller can group /// as it streams. pub fn get_dataset_values_for_individuals( dataset_id: u16, individuals: &[String], ) -> Result)>, DbError> { if individuals.is_empty() { return Ok(Vec::new()); } let pool = pool()?; let mut conn = pool.get_conn()?; // The mysql crate sends a Vec as one value, so the IN list is written out // as placeholders. let placeholders = std::iter::repeat_n("?", individuals.len()) .collect::>() .join(","); let query = format!( "SELECT ProbeSet.Name, Strain.Name, ProbeSetData.value \ FROM ProbeSetData, Strain, ProbeSetXRef, ProbeSet \ WHERE ProbeSetXRef.ProbeSetFreezeId = ? \ AND ProbeSetXRef.ProbeSetId = ProbeSet.Id \ AND ProbeSetXRef.DataId = ProbeSetData.Id \ AND ProbeSetData.StrainId = Strain.Id \ AND Strain.Name IN ({placeholders}) \ ORDER BY ProbeSet.Name, Strain.Name" ); let mut params = mysql::Params::Positional(vec![mysql::Value::from(dataset_id)]); if let mysql::Params::Positional(values) = &mut params { values.extend(individuals.iter().map(|name| mysql::Value::from(name.as_str()))); } conn.exec_map(&query, params, |(trait_name, individual, value)| { (trait_name, individual, value) }) .map_err(DbError::from) } /// The same bulk fetch for a Publish (phenotype) dataset. /// /// `PublishXRef.Id` is the trait's handle within that dataset -- GN2 addresses /// phenotype traits by it ("1427571_at" style names belong to the expression /// sets) -- and the values live in `PublishData`, keyed by the same InbredSetId /// as the reference trait's group. Returned as (trait id, individual, value) /// so the caller can group it exactly like the probeset case. /// Chosen traits of a probeset dataset, restricted to chosen individuals. /// /// The matrix needs a handful of traits out of a dataset that holds tens of /// thousands, so it can afford neither "fetch the dataset" nor one query per /// trait: this is one round trip for all of them. pub fn get_dataset_values_for_traits( dataset_id: u16, traits: &[String], individuals: &[String], ) -> Result)>, DbError> { if traits.is_empty() || individuals.is_empty() { return Ok(Vec::new()); } let pool = pool()?; let mut conn = pool.get_conn()?; let columns = std::iter::repeat_n("?", individuals.len()) .collect::>() .join(","); let names = std::iter::repeat_n("?", traits.len()) .collect::>() .join(","); let query = format!( "SELECT ProbeSet.Name, Strain.Name, ProbeSetData.value \ FROM ProbeSetData, Strain, ProbeSetXRef, ProbeSet \ WHERE ProbeSetXRef.ProbeSetFreezeId = ? \ AND ProbeSet.Name IN ({names}) \ AND ProbeSetXRef.ProbeSetId = ProbeSet.Id \ AND ProbeSetXRef.DataId = ProbeSetData.Id \ AND ProbeSetData.StrainId = Strain.Id \ AND Strain.Name IN ({columns}) \ ORDER BY ProbeSet.Name, Strain.Name" ); let mut params = mysql::Params::Positional(vec![mysql::Value::from(dataset_id)]); if let mysql::Params::Positional(values) = &mut params { values.extend(traits.iter().map(|name| mysql::Value::from(name.as_str()))); values.extend(individuals.iter().map(|name| mysql::Value::from(name.as_str()))); } conn.exec_map(&query, params, |(trait_name, individual, value)| { (trait_name, individual, value) }) .map_err(DbError::from) } /// The same for phenotype (Publish) traits, addressed by numeric id. pub fn get_publish_values_for_traits( inbred_set_id: u16, trait_ids: &[u32], individuals: &[String], ) -> Result)>, DbError> { if trait_ids.is_empty() || individuals.is_empty() { return Ok(Vec::new()); } let pool = pool()?; let mut conn = pool.get_conn()?; let columns = std::iter::repeat_n("?", individuals.len()) .collect::>() .join(","); let ids = std::iter::repeat_n("?", trait_ids.len()) .collect::>() .join(","); let query = format!( "SELECT CAST(PublishXRef.Id AS CHAR), Strain.Name, PublishData.value \ FROM PublishData, Strain, PublishXRef \ WHERE PublishXRef.InbredSetId = ? AND PublishXRef.Id IN ({ids}) \ AND PublishXRef.DataId = PublishData.Id \ AND PublishData.StrainId = Strain.Id \ AND Strain.Name IN ({columns}) \ ORDER BY PublishXRef.Id, Strain.Name" ); let mut params = mysql::Params::Positional(vec![mysql::Value::from(inbred_set_id)]); if let mysql::Params::Positional(values) = &mut params { values.extend(trait_ids.iter().map(|id| mysql::Value::from(*id))); values.extend(individuals.iter().map(|name| mysql::Value::from(name.as_str()))); } conn.exec_map(&query, params, |(trait_id, individual, value)| { (trait_id, individual, value) }) .map_err(DbError::from) } pub fn get_publish_values_for_individuals( inbred_set_id: u16, individuals: &[String], ) -> Result)>, DbError> { if individuals.is_empty() { return Ok(Vec::new()); } let pool = pool()?; let mut conn = pool.get_conn()?; let placeholders = std::iter::repeat_n("?", individuals.len()) .collect::>() .join(","); let query = format!( "SELECT CAST(PublishXRef.Id AS CHAR), Strain.Name, PublishData.value \ FROM PublishData, Strain, PublishXRef \ WHERE PublishXRef.InbredSetId = ? AND PublishXRef.DataId = PublishData.Id \ AND PublishData.StrainId = Strain.Id \ AND Strain.Name IN ({placeholders}) \ ORDER BY PublishXRef.Id, Strain.Name" ); let mut params = mysql::Params::Positional(vec![mysql::Value::from(inbred_set_id)]); if let mysql::Params::Positional(values) = &mut params { values.extend(individuals.iter().map(|name| mysql::Value::from(name.as_str()))); } conn.exec_map(&query, params, |(trait_id, individual, value)| { (trait_id, individual, value) }) .map_err(DbError::from) } #[cfg(test)] mod tests { use super::*; fn query_error(text: &str) -> DbError { DbError::Query(mysql::Error::IoError(std::io::Error::other( text.to_string(), ))) } #[test] fn a_peer_that_went_away_is_a_connection_loss_and_a_bad_query_is_not() { for text in [ "CodecError { IO error: `Broken pipe (os error 32)' }", "Connection reset by peer", "connection aborted", "unexpected eof", "MySQL server has gone away", "Lost connection to MySQL server during query", ] { assert!(is_connection_loss(&query_error(text)), "{text}"); } for text in [ "Table 'db_webqtl.Nope' doesn't exist", "You have an error in your SQL syntax", ] { assert!(!is_connection_loss(&query_error(text)), "{text}"); } assert!(!is_connection_loss(&DbError::Unavailable( "bad uri".to_string() ))); } } #[cfg(test)] mod failure_tests { use super::*; use std::time::Duration; /// The memory of the last failure is what makes `/version` able to say /// "the data endpoints failed a minute ago" while its own probe is /// healthy -- the exact confusion behind a user reading a database /// fault as an authentication problem. #[test] fn a_recent_failure_is_remembered_and_ages_out() { record_failure(FailureKind::ConnectionLost, "CodecError { IO error: Broken pipe }"); let failure = recent_failure(Duration::from_secs(60)).expect("just recorded"); assert_eq!(failure.kind, FailureKind::ConnectionLost); assert!(failure.kind.as_str() == "connection_lost"); assert!(failure.detail.contains("Broken pipe")); // Aged out: the note must not outlive its usefulness. assert!(recent_failure(Duration::ZERO).is_none()); } #[test] fn a_driver_message_is_recognised_as_a_connection_loss() { for message in [ "CodecError { IO error: Broken pipe }", "server has gone away", "Lost connection to MySQL server during query", "connection reset by peer", ] { assert!(is_connection_loss_detail(message), "{message}"); } assert!(!is_connection_loss_detail( "Table 'db_webqtl.Nope' doesn't exist" )); } } // --------------------------------------------------------------------------- // 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, #[serde(skip_serializing_if = "Option::is_none")] pub species: Option, /// 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, pub name: String, #[serde(skip_serializing_if = "Option::is_none")] pub symbol: Option, #[serde(skip_serializing_if = "Option::is_none")] pub chr: Option, #[serde(skip_serializing_if = "Option::is_none")] pub mb: Option, #[serde(skip_serializing_if = "Option::is_none")] pub mean: Option, #[serde(skip_serializing_if = "Option::is_none")] pub lrs: Option, #[serde(skip_serializing_if = "Option::is_none")] pub lod: Option, #[serde(skip_serializing_if = "Option::is_none")] pub locus: Option, #[serde(skip_serializing_if = "Option::is_none")] pub additive: Option, } /// 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, 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, Option, Option, i64, Option, Option, Option, Option, ); let rows: Vec = 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, Option, Option); /// 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, DbError> { let pool = pool()?; let mut conn = pool.get_conn()?; let freezes: Vec<(i64, String, String, Option, i64)> = conn.exec_map( "SELECT Id, Name, FullName, Name2, ProbeFreezeId FROM ProbeSetFreeze", (), |row| row, )?; let panels: Vec<(i64, Option, Option)> = 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()) }