diff options
| author | Pjotr Prins <pjotr.public01@thebird.nl> | 2026-10-03 15:58:02 +0000 |
|---|---|---|
| committer | Pjotr <wrk@bosa.lan> | 2026-10-04 06:28:08 +0000 |
| commit | 0980c21ff71bf4c8fa73d3193460f7b7f69d9f6b (patch) | |
| tree | 7203119218b5f5fe929f2f7b1372863dee389999 /docs | |
| parent | 0c58ad5611077502c639cb1b3406f476f930c863 (diff) | |
| download | genenetwork4-0980c21ff71bf4c8fa73d3193460f7b7f69d9f6b.tar.gz | |
doc: Add PublishXRef section; sharpen SKILL.md with new lessons.
* docs/genenetwork_datatypes.md (PublishXRef): New section, covering PublishXRef's own fields and its relationships to Phenotype, Publication, PublishData/PublishSE/NStrain and Strain, based on topics/systems/mariadb/precompute-publishdata.gmi. * docs/SKILL.md (mysql crate): Add notes on aliasing joined columns to match FromRow's UpperCamelCase conversion instead of reaching for #[mysql(rename = "...")], and on mysql::Pool::new's expensive default PoolConstraints (eagerly opens 10 connections) making it unsafe to call per request. (Testing): Add timing DB-backed test requests through a shared helper, since that's what actually caught the Pool::new issue above. Co-Authored-By: Claude Sonnet 5 <noreply@anthropic.com> Claude-Session: https://claude.ai/code/session_01XDDpXvZBQat9uSgDeEMJ8d
Diffstat (limited to 'docs')
| -rw-r--r-- | docs/SKILL.md | 45 | ||||
| -rw-r--r-- | docs/genenetwork_datatypes.md | 80 |
2 files changed, 125 insertions, 0 deletions
diff --git a/docs/SKILL.md b/docs/SKILL.md index ead9df4..dade13c 100644 --- a/docs/SKILL.md +++ b/docs/SKILL.md @@ -28,6 +28,38 @@ similar Rust/Axum service backed by a MariaDB/MySQL database. database-specific name (see "Naming" below). - Keep `mysql::params!` queries (`:name` placeholders) over string interpolation even for read-only queries. +- A query that joins several tables and selects same-named columns + from more than one (`Id`, `Units`, `Year`, ...) needs an explicit SQL + `AS` alias per column to disambiguate for `FromRow`. Rather than + reaching for `#[mysql(rename = "...")]` on every such field, write + each alias to already equal that field's default PascalCase + conversion (e.g. a `pubmed_id` field needs the column aliased + `AS PubmedId`, not `AS PubMedId` -- the derive capitalizes each + underscore-separated segment, it doesn't preserve mid-word + capitalization) -- then no renames are needed at all. Verify empirically + with a real query against the live database rather than trusting the + conversion by inspection; it is not compile-checked. +- **`mysql::Pool::new` is expensive -- do not call it per request.** Its + default `PoolConstraints` (`min: 10, max: 100`) makes it eagerly open + 10 real connections before returning, which took about 7 seconds + against a real database in this project despite running on + localhost (a single already-established connection then takes under + 200ms per query) -- plausibly a per-connection handshake/auth cost + multiplied by 10, not network latency (a raw `TcpStream::connect` to + the same address was sub-millisecond). A handler that builds a fresh + `Pool` and drops it at the end of the function -- an easy mistake, + since `Pool::new` reads just like opening any other resource -- + pays this cost on *every single request*. Build one `Pool` lazily on + first use and share it (it's a cheap, thread-safe, `Arc`-backed + handle, so cloning it out of a cache is free): `OnceLock<Mutex<Option<Pool>>>` + works, since `Pool::new` is fallible and `OnceLock::get_or_init`'s + closure isn't allowed to fail (`get_or_try_init` is still unstable, + rust-lang/rust#109737) -- lock, check, build-and-cache on a miss, + return a clone either way. This class of bug is exactly what + per-request query timing (see Testing below) is for: it's invisible + in a quick manual check (the person testing doesn't notice ~7s + consistently, they just think the service is slow) but immediately + obvious once requests are timed individually. ## Naming: database vs. code @@ -103,6 +135,19 @@ Two parallel suites exercising the same endpoints: and rat for a species table, BXD for a groups table) rather than inventing synthetic data -- grep the existing Python test suites of sibling services for what they already rely on being present. +- Route every DB-backed Rust test's request through one shared helper + (e.g. `tests/common::get`, a `mod common;` under `tests/` so every + integration-test file can use it) that times the call and warns on + stderr past a threshold (e.g. 1s). This is what actually caught the + `Pool::new`-per-request bug above -- it was invisible until requests + were timed individually. Warnings only show with `cargo test -- + --nocapture` (or on a failing test; cargo captures output for + passing tests otherwise), so say so in the README. Each integration + test file compiles to its own process, so the one-time cost of + building a share-once resource (like the Pool above) still shows up + once per test *file*, not just once overall -- that's expected, not + a sign the fix didn't work; what matters is that repeated calls + inside one file (or one real server process) stay fast after it. ## Guix packaging diff --git a/docs/genenetwork_datatypes.md b/docs/genenetwork_datatypes.md index 2382d72..f55752e 100644 --- a/docs/genenetwork_datatypes.md +++ b/docs/genenetwork_datatypes.md @@ -203,3 +203,83 @@ populations -- so "RISet" is a slight misnomer for the general case). `gn/data/species.scm`, not backed by `db_webqtl`. - **GeneNetwork4** (this repo): no `/species/{species}/groups`-style endpoint yet. + +## PublishXRef + +The `PublishXRef` table (full `CREATE TABLE` in `docs/mariadb_db-webqtl.md`) +is the central record for a published (phenotype) trait within one +group: one row per trait per group, tying together that trait's +`Phenotype` description, its `Publication` reference, its actual +per-strain values (`PublishData`), and -- for traits that have been +mapped -- a single best QTL hit (`Locus`/`LRS`/`additive`/`mean`). +Explored at length in `topics/systems/mariadb/precompute-publishdata.gmi`, +which walks through precomputing GEMMA hits for every `PublishXRef` row +and surfacing its `Phenotype`/`Publication` metadata alongside them. + +### Fields + +| Field | Meaning | +|-----------------|------------------------------------------------------------------------------------------------------------------------| +| `Id` | Trait identifier within a group; unique only paired with `InbredSetId`, not on its own (see Known quirks). | +| `InbredSetId` | FK to `InbredSet`; every trait belongs to exactly one group. | +| `PhenotypeId` | FK to `Phenotype`, the trait's description and units. | +| `PublicationId` | FK to `Publication`, the article the trait was published in. | +| `DataId` | FK to `PublishData.Id`, the trait's actual per-strain values. | +| `mean` | Mean value across strains, for the trait's single stored QTL hit. | +| `Locus` | Marker/SNP name of that stored hit. | +| `LRS` | LOD/LRS score of that hit. | +| `additive` | Additive effect size of that hit. | +| `Sequence` | Disambiguates multiple `PublishXRef` rows that would otherwise collide on `(InbredSetId, PhenotypeId, PublicationId)`. | +| `comments` | Free-text comment on the mapping. | +| `NSamples` | Number of strains/samples the trait was computed over. | + +### Relationships + +- `PublishXRef.InbredSetId` -> `InbredSet` -- every trait belongs to + exactly one group (see InbredSet section above). +- `PublishXRef.PhenotypeId` -> `Phenotype` -- the trait's + `Pre_publication_description`/`Post_publication_description`/ + `Original_description` and `Units`. +- `PublishXRef.PublicationId` -> `Publication` -- the article the trait + was published in (`Title`, `Authors`, `Journal`, `Volume`, `Pages`, + `Month`, `Year`, `PubMed_ID`, `Abstract`). +- `PublishXRef.DataId` -> `PublishData.Id` -- the trait's actual + per-strain values; `PublishData.StrainId` -> `Strain` names them. + `PublishSE` (per-strain standard error) and `NStrain` (per-strain + sample count) key off the same `(DataId, StrainId)` pair, both + optional (left-joined in GeneNetwork2's own queries). + +### Known quirks + +- `PublishXRef.Id` is not globally unique on its own -- only the pair + `(InbredSetId, Id)` is (`UNIQUE KEY InbredSet`) -- so a group must + always be known to look up a trait by Id. Observed live: Id `10001` + exists under dozens of different groups, each with a different + `DataId`. +- `Publication` coverage is spotty: per precompute-publishdata.gmi's + own count, about 22K of 29K `Publication` rows lack an `Abstract`, + and many `PubMed_ID` values look implausible for a real PubMed ID + (too few digits to be one). Treat `Publication` metadata as + best-effort, not authoritative -- the doc's own conclusion is "It + really is a bit of a mess." +- Only a single highest-LRS hit is stored per trait + (`mean`/`Locus`/`LRS`/`additive`); precompute-publishdata.gmi and + precompute-mapping-input-data.gmi are both about extending this to + store more hits (e.g. every GEMMA SNP above a threshold, not just + the best one) -- not yet reflected in the schema. + +### Existing implementations + +- **GeneNetwork2** (`gn2/wqflask/api/router.py`): `trait_sample_data`'s + Publish branch returns per-strain `PublishData` values (joined with + `PublishSE`/`NStrain`); `get_trait_info`'s Publish branch returns + only `PhenotypeId`/`Locus`/`LRS`/`additive`, without the + `Phenotype`/`Publication` join. +- **gn-guile** (`gn/data/dataset.scm`): `get-bxd-publish-list`, + `get-bxd-publish-values-list`, `get-bxd-publish-name-value-dict` -- + hardcoded to `InbredSetId=1` (BXD) throughout, no `Phenotype`/ + `Publication` join. +- **GeneNetwork4** (this repo): `GET /groups/{group}/traits/{trait_id}` + (the `PublishXRef`/`Phenotype`/`Publication` join above, generalized + to any group) and `GET /groups/{group}/traits/{trait_id}/values` + (per-strain `PublishData` values). |
