about summary refs log tree commit diff
path: root/docs
diff options
context:
space:
mode:
authorPjotr Prins <pjotr.public01@thebird.nl>2026-10-03 15:58:02 +0000
committerPjotr <wrk@bosa.lan>2026-10-04 06:28:08 +0000
commit0980c21ff71bf4c8fa73d3193460f7b7f69d9f6b (patch)
tree7203119218b5f5fe929f2f7b1372863dee389999 /docs
parent0c58ad5611077502c639cb1b3406f476f930c863 (diff)
downloadgenenetwork4-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.md45
-rw-r--r--docs/genenetwork_datatypes.md80
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).