//! The crawl: the SQL queries and typed row structs the document //! builder consumes. //! //! The queries and joins are the indexer's own (`genes_query` and //! `phenotypes_query` in index-genenetwork), rendered by its //! `serialize_sql`. The one deliberate difference is the phenotype //! filter: the Python query has NO `WHERE` clause, which is the leak //! this lane closes by construction — both queries now carry //! `confidentiality < 1 AND public > 0`. //! //! The phenotype join fans out on `InbredSetId` only, so a group's two //! PublishFreezes (e.g. BXDPublish and GITrMetPublish) each index every //! trait under their own dataset label. This cross-product is INTENTIONAL //! reference semantics and is reproduced, not "cleaned up". use mysql::prelude::Queryable; use mysql::{Conn, Opts, OptsBuilder, Row}; /// The phenotype filter, fully qualified (the table's `public` column /// collides with InbredSet.public, so both predicates need the table /// name). const PHENOTYPE_PUBLIC_FILTER: &str = "PublishFreeze.confidentiality < 1 AND PublishFreeze.public > 0"; /// The gene filter, fully qualified against ProbeSetFreeze. const GENE_PUBLIC_FILTER: &str = "ProbeSetFreeze.confidentiality < 1 AND ProbeSetFreeze.public > 0"; /// One expression (gene) crawl row; each field is absent when SQL /// returned NULL (the indexer's `MonadicDict` drops NULLs). #[derive(Default, Clone)] pub struct GeneRow { pub name: Option, pub symbol: Option, pub description: Option, pub chr: Option, pub mb: Option, pub alias: Option, pub genbankid: Option, pub unigeneid: Option, pub probe_target_description: Option, pub dataset: Option, pub dataset_fullname: Option, pub species: Option, pub group: Option, pub tissue: Option, pub mean: Option, pub lrs: Option, pub additive: Option, pub geno_chr: Option, pub geno_mb: Option, } /// One published (phenotype) crawl row. #[derive(Default, Clone)] pub struct PhenotypeRow { pub species: Option, pub group: Option, pub dataset: Option, pub dataset_fullname: Option, pub name: Option, pub abbreviation: Option, pub description: Option, pub lab_code: Option, pub abstract_: Option, pub title: Option, pub authors: Option, pub year: Option, pub pubmed_id: Option, pub lrs: Option, pub additive: Option, pub inbredsetcode: Option, pub mean: Option, pub nsamples: Option, pub geno_chr: Option, pub geno_mb: Option, } fn connect(sql_uri: &str) -> Result { let opts = Opts::from_url(sql_uri).map_err(|e| format!("bad SQL_URI: {e}"))?; Conn::new(OptsBuilder::from_opts(opts)).map_err(|e| format!("cannot connect: {e}")) } fn text(row: &Row, column: &str) -> Result, String> { match row.get_opt::, _>(column) { Some(Ok(value)) => Ok(value), Some(Err(e)) => Err(format!("column {column}: {e}")), None => Err(format!("column {column} not in result set")), } } fn float(row: &Row, column: &str) -> Result, String> { match row.get_opt::, _>(column) { Some(Ok(value)) => Ok(value), Some(Err(e)) => Err(format!("column {column}: {e}")), None => Err(format!("column {column} not in result set")), } } fn integer(row: &Row, column: &str) -> Result, String> { match row.get_opt::, _>(column) { Some(Ok(value)) => Ok(value), Some(Err(e)) => Err(format!("column {column}: {e}")), None => Err(format!("column {column} not in result set")), } } fn genes_sql() -> String { format!( "SELECT ProbeSet.Name AS name, ProbeSet.Symbol AS symbol, \ ProbeSet.description AS description, ProbeSet.Chr AS chr, ProbeSet.Mb as mb, \ ProbeSet.alias AS alias, ProbeSet.GenbankId AS genbankid, \ ProbeSet.UniGeneId AS unigeneid, \ ProbeSet.Probe_Target_Description AS probe_target_description, \ ProbeSetFreeze.Name AS dataset, ProbeSetFreeze.FullName AS dataset_fullname, \ Species.Name AS species, InbredSet.Name AS `group`, Tissue.Name AS tissue, \ ProbeSetXRef.Mean AS mean, ProbeSetXRef.LRS AS lrs, \ ProbeSetXRef.additive AS additive, Geno.Chr AS geno_chr, Geno.Mb as geno_mb \ FROM Species \ INNER JOIN InbredSet ON InbredSet.SpeciesId = Species.Id \ INNER JOIN ProbeFreeze ON ProbeFreeze.InbredSetId = InbredSet.Id \ INNER JOIN Tissue ON ProbeFreeze.TissueId = Tissue.Id \ INNER JOIN ProbeSetFreeze ON ProbeSetFreeze.ProbeFreezeId = ProbeFreeze.Id \ INNER JOIN ProbeSetXRef ON ProbeSetXRef.ProbeSetFreezeId = ProbeSetFreeze.Id \ INNER JOIN ProbeSet ON ProbeSet.Id = ProbeSetXRef.ProbeSetId \ LEFT JOIN Geno ON ProbeSetXRef.Locus = Geno.Name AND Geno.SpeciesId = Species.Id \ WHERE {GENE_PUBLIC_FILTER}" ) } fn phenotypes_sql(filtered: bool) -> String { let where_clause = if filtered { format!(" WHERE {PHENOTYPE_PUBLIC_FILTER}") } else { String::new() }; format!( "SELECT Species.Name AS species, InbredSet.Name AS `group`, \ PublishFreeze.Name AS dataset, PublishFreeze.FullName AS dataset_fullname, \ PublishXRef.Id AS name, \ COALESCE(Phenotype.Post_publication_abbreviation, \ Phenotype.Pre_publication_abbreviation) AS abbreviation, \ COALESCE(Phenotype.Post_publication_description, \ Phenotype.Pre_publication_description) AS description, \ Phenotype.Lab_code, Publication.Abstract, Publication.Title, \ Publication.Authors AS authors, \ IF(CONVERT(Publication.Year, UNSIGNED)=0, NULL, \ CONVERT(Publication.Year, UNSIGNED)) AS year, \ Publication.PubMed_ID AS pubmed_id, PublishXRef.LRS as lrs, \ PublishXRef.additive, InbredSet.InbredSetCode AS inbredsetcode, \ PublishXRef.mean, PublishXRef.NSamples AS nsamples, \ Geno.Chr as geno_chr, Geno.Mb as geno_mb \ FROM Species \ INNER JOIN InbredSet ON InbredSet.SpeciesId = Species.Id \ INNER JOIN PublishFreeze ON PublishFreeze.InbredSetId = InbredSet.Id \ INNER JOIN PublishXRef ON PublishXRef.InbredSetId = InbredSet.Id \ INNER JOIN Phenotype ON PublishXRef.PhenotypeId = Phenotype.Id \ INNER JOIN Publication ON PublishXRef.PublicationId = Publication.Id \ LEFT JOIN Geno ON PublishXRef.Locus = Geno.Name AND Geno.SpeciesId = Species.Id\ {where_clause}" ) } /// Crawl every public, non-confidential expression record. pub fn crawl_genes(sql_uri: &str) -> Result, String> { let mut conn = connect(sql_uri)?; let rows: Vec = conn .query(genes_sql()) .map_err(|e| format!("gene crawl failed: {e}"))?; let mut out = Vec::with_capacity(rows.len()); for row in &rows { out.push(GeneRow { name: text(row, "name")?, symbol: text(row, "symbol")?, description: text(row, "description")?, chr: text(row, "chr")?, mb: float(row, "mb")?, alias: text(row, "alias")?, genbankid: text(row, "genbankid")?, unigeneid: text(row, "unigeneid")?, probe_target_description: text(row, "probe_target_description")?, dataset: text(row, "dataset")?, dataset_fullname: text(row, "dataset_fullname")?, species: text(row, "species")?, group: text(row, "group")?, tissue: text(row, "tissue")?, mean: float(row, "mean")?, lrs: float(row, "lrs")?, additive: float(row, "additive")?, geno_chr: text(row, "geno_chr")?, geno_mb: float(row, "geno_mb")?, }); } Ok(out) } /// Crawl published phenotype records. `filtered=false` drops the /// public/confidentiality predicate: that is the leak control, used /// only by the gate-4 RED run. pub fn crawl_phenotypes(sql_uri: &str, filtered: bool) -> Result, String> { let mut conn = connect(sql_uri)?; let rows: Vec = conn .query(phenotypes_sql(filtered)) .map_err(|e| format!("phenotype crawl failed: {e}"))?; let mut out = Vec::with_capacity(rows.len()); for row in &rows { out.push(PhenotypeRow { species: text(row, "species")?, group: text(row, "group")?, dataset: text(row, "dataset")?, dataset_fullname: text(row, "dataset_fullname")?, name: integer(row, "name")?, abbreviation: text(row, "abbreviation")?, description: text(row, "description")?, lab_code: text(row, "Lab_code")?, abstract_: text(row, "Abstract")?, title: text(row, "Title")?, authors: text(row, "authors")?, year: integer(row, "year")?, pubmed_id: integer(row, "pubmed_id")?, lrs: float(row, "lrs")?, additive: float(row, "additive")?, inbredsetcode: text(row, "inbredsetcode")?, mean: float(row, "mean")?, nsamples: integer(row, "nsamples")?, geno_chr: text(row, "geno_chr")?, geno_mb: float(row, "geno_mb")?, }); } Ok(out) }