1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
|
//! 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<String>,
pub symbol: Option<String>,
pub description: Option<String>,
pub chr: Option<String>,
pub mb: Option<f64>,
pub alias: Option<String>,
pub genbankid: Option<String>,
pub unigeneid: Option<String>,
pub probe_target_description: Option<String>,
pub dataset: Option<String>,
pub dataset_fullname: Option<String>,
pub species: Option<String>,
pub group: Option<String>,
pub tissue: Option<String>,
pub mean: Option<f64>,
pub lrs: Option<f64>,
pub additive: Option<f64>,
pub geno_chr: Option<String>,
pub geno_mb: Option<f64>,
}
/// One published (phenotype) crawl row.
#[derive(Default, Clone)]
pub struct PhenotypeRow {
pub species: Option<String>,
pub group: Option<String>,
pub dataset: Option<String>,
pub dataset_fullname: Option<String>,
pub name: Option<i64>,
pub abbreviation: Option<String>,
pub description: Option<String>,
pub lab_code: Option<String>,
pub abstract_: Option<String>,
pub title: Option<String>,
pub authors: Option<String>,
pub year: Option<i64>,
pub pubmed_id: Option<i64>,
pub lrs: Option<f64>,
pub additive: Option<f64>,
pub inbredsetcode: Option<String>,
pub mean: Option<f64>,
pub nsamples: Option<i64>,
pub geno_chr: Option<String>,
pub geno_mb: Option<f64>,
}
fn connect(sql_uri: &str) -> Result<Conn, String> {
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<Option<String>, String> {
match row.get_opt::<Option<String>, _>(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<Option<f64>, String> {
match row.get_opt::<Option<f64>, _>(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<Option<i64>, String> {
match row.get_opt::<Option<i64>, _>(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<Vec<GeneRow>, String> {
let mut conn = connect(sql_uri)?;
let rows: Vec<Row> = 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<Vec<PhenotypeRow>, String> {
let mut conn = connect(sql_uri)?;
let rows: Vec<Row> = 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)
}
|