//! Read-only access to the GeneNetwork MariaDB database (db_webqtl). use mysql::prelude::{FromRow, Queryable}; use mysql::{params, Opts, OptsBuilder, Pool, PoolOpts}; 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 } /// 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. /// /// Two defaults are turned off because each costs a full round trip to /// the database per checkout, on top of the query: `check_health` (a /// ping when a connection is taken from the pool) and /// `reset_connection` (a COM_RESET_CONNECTION when it is returned). /// Together they tripled the cost of every database call against a /// database about 130 ms away (390 ms instead of 130 ms). Nothing here /// needs the reset (queries are read-only and keep no session state), /// and the ping only protects against a connection the server closed /// while idle -- which would now surface as a database error on the /// next query using it (the pool then drops that connection). fn pool() -> Result { static POOL: OnceLock>> = OnceLock::new(); let mut guard = POOL.get_or_init(|| Mutex::new(None)).lock().unwrap(); if let Some(pool) = guard.as_ref() { return Ok(pool.clone()); } let opts = OptsBuilder::from_opts(Opts::from_url(&sql_uri())?).pool_opts( PoolOpts::default() .with_reset_connection(false) .with_check_health(false), ); let new_pool = Pool::new(opts)?; *guard = Some(new_pool.clone()); Ok(new_pool) } /// 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, mysql::Error> { 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, }, ) } /// 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, mysql::Error> { 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, } }, ) }) } /// 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, mysql::Error> { 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, } }, ) } /// 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, mysql::Error> { 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 }, ) } /// 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, mysql::Error> { 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, mysql::Error> { 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 }, ) } /// 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, mysql::Error> { 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", }, ) } /// 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. pub fn get_group_trait_values( group_id: u16, trait_id: u32, ) -> Result, mysql::Error> { 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 }, ) } /// 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, mysql::Error> { 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 }, ) } /// 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, mysql::Error> { 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 }, ) } /// 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, mysql::Error> { 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 }, ) } /// 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>, mysql::Error> { 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) } /// 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, mysql::Error> { 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, mysql::Error> { 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, } }, ) } /// 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`) 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. #[derive(Serialize, utoipa::ToSchema)] pub struct CaseAttributes { pub names: Vec, pub values: Vec, } /// Fetch every case-attribute name defined for a group, matching /// GeneNetwork3's gn3.api.case_attributes.__case_attribute_labels_by_inbred_set__ /// (trimmed to just the name -- Description isn't used by genenetwork4 /// yet, and CaseAttributeId/InbredSetId are already implied by this /// being scoped to one group). fn get_group_case_attribute_names(group_id: u16) -> Result, mysql::Error> { let pool = pool()?; let mut conn = pool.get_conn()?; conn.exec( "SELECT Name FROM CaseAttribute WHERE InbredSetId = :group_id ORDER BY Name", mysql::params! { "group_id" => group_id }, ) } /// 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, mysql::Error> { 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 { Ok(CaseAttributes { names: get_group_case_attribute_names(group_id)?, 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, mysql::Error> { 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, } }, ) } /// 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, mysql::Error> { 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, } }, ) }) } /// 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, mysql::Error> { 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 }, ) } /// 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, mysql::Error> { 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, mysql::Error> { 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, } }, ) }) } /// 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, mysql::Error> { 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, } }, ) } /// 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, mysql::Error> { 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 }, ) }