about summary refs log tree commit diff

GeneNetwork data types

Notes on the core data types GeneNetwork is built around, compiled from factory/docs/gn-gemtext-threads, factory/code/genenetwork2, factory/code/genenetwork3, factory/code/gn-guile, and this repo's own docs/mariadb_db-webqtl.md schema dump.

Species

The Species table (db_webqtl; full CREATE TABLE in docs/mariadb_db-webqtl.md) is the root of GeneNetwork's data hierarchy: every InbredSet (a group/population, e.g. BXD), every Strain, and every chromosome length record (Chr_Length) is scoped to a species via a SpeciesId foreign key.

Fields

Field semantics, from topics/gn-uploader/adding-species.gmi (written while manually adding C. elegans to the database) and cross-checked against the live schema:

Field Meaning
Id Internal species identifier (auto-increment primary key).
SpeciesId Set equal to Id after insert; FKs use this, not Id (see Known quirks).
SpeciesName A layman's name, e.g. "Mouse", "Roundworm", "Barley".
Name Lowercased short name for URLs/API, e.g. "mouse".
MenuName UI display name, e.g. "Mouse (Mus musculus, mm10)".
FullName Binomial (scientific) name, e.g. "Mus musculus".
Family A loose grouping across species, e.g. "Vertebrates", "Nematodes".
FamilyOrderId Sort order within Family; derivation undocumented (TODO).
TaxonomyId NCBI Taxonomy ID, e.g. 10090 (mouse), 6239 (C. elegans).
OrderId Sort order for display; same undocumented TODO as above.

Relationships

  • InbredSet.SpeciesId -> Species -- every group/population (e.g. BXD) belongs to exactly one species.
  • Strain.SpeciesId -> Species -- every strain belongs to exactly one species (genenetwork2/gn2/base/species.py's TheSpecies/ Chromosomes classes join on this).
  • Chr_Length.SpeciesId -> Species -- chromosome names and lengths are per species (used by gn3.db.species.get_chromosome and GN2's Chromosomes.chromosomes, both selecting from Chr_Length joined to either Species directly by name, or indirectly via InbredSet for cases where only a group name is known).
  • GeneIDXRef cross-references gene IDs between species. GeneNetwork's internal gene identifiers are mouse-centric by convention: GN3's gn3.db.species.translate_to_mouse_gene_id translates a rat or human gene ID to its mouse equivalent via this table (returns the ID unchanged if the species is already "mouse").
  • InbredSet.SpeciesId also scopes authorization: GN3's gn3/api/case_attributes.py resolves a species ID from InbredSet to build the resource ID used to check a user's access with gn-auth (https://github.com/genenetwork/gn-auth), so species is part of GN's permission hierarchy, not just a data-organization concept.
  • In the RDF/SPARQL metadata graph (topics/next-gen-databases/gn-classification-scheme.gmi), species is one of the three levels of GN's resource classification scheme (gnc:DatasetType, gnc:Set, gn:Species), alongside InbredSet ("Set") and DatasetType. Every member of the Species table has a corresponding gn:Species-classified RDF resource; gn-guile's gn/data/species.scm builds this metadata from SPARQL, separately from the MariaDB Species table.

Reference genomes and coordinate generations

Nothing in the schema stores an assembly identifier; the only explicit labels are prose in Species.MenuName (verified against db_webqtl on 2026-11-11):

species labelled assembly
human hg19
mouse mm10
rat mRatBN7.2/rn7
fly dm6
nematode ce10
arabidopsis araTha1
medaka Oryl23
bat gsor23
turquoise killifish MPIA_NFZ_2
tomato GCF_001406875.1
soybean US DOE JGI-PGF v2 2021
barley, poplar unlabelled

Every position column exists in three generations: Mb (current), Mb_2016, and a legacy mouse Mb_mm8 -- the same triplication on Chr_Length (Length, Length_2016, Length_mm8), ProbeSet (Chr/Mb, Chr_2016/Mb_2016) and BXDSnpPosition. The labels above do not all agree with the numbers:

  • human is hg19 throughout, in both generations: Chr_Length chr1 is 249 250 621 in Length and Length_2016, and SOX2 sits at chr3 181.429-181.431 Mb (hg19 181 429 788; hg38 would be ~181.71).
  • mouse Length_2016 chr1 = 197 195 432 (mm9 exactly), while the current Length is 197 426 691, which matches no standard build (mm9 197 195 432, mm10 195 471 971, mm39 195 154 058). The probes are mm10-era -- Sox2 at chr3 34.47-35.09 Mb (mm10 ~34.65) -- so the label mm10 is right for ProbeSet while Chr_Length is not.
  • rat current Length chr1 = 282 763 074 (rn6 exactly), i.e. the column is the older build although MenuName claims rn7 (mRatBN7.2, chr1 ~260.5 Mb). Rat probe positions are also internally inconsistent: Sox2's 44 probes span chr2 1.203-117.592 Mb where rn6 Sox2 is ~119 Mb, which reads as unmapped or badly mapped probes rather than one build.
  • Geno has a non-null Mb for every locus, but Mb_2016 only for human (all 367 403 rows), mouse (23 609), soybean (4 938) and barley (791); Mb_mm8, a mouse-named column, is also populated for barley (791) -- legacy columns reused across species.

Practical consequence: a position taken from this database is "GN's current coordinate for that species" and should be quoted with that build, not with a modern assembly. The search index uses the current columns (ProbeSet.Mb, Geno.Mb), so it inherits exactly these builds -- see search-engines.md.

Known quirks

  • GeneNetwork's genotype tables were originally built mouse-only and still carry that legacy: topics/gn-uploader/genotypes-assemblies-markers-and-genenetwork.gmi notes the Geno table has assembly-specific, mouse-named columns (Mb_mm8, Chr_mm8) that don't generalize to other species' genome assemblies.
  • There is no UI for adding a new species; it's done by hand with INSERT INTO Species(...) followed by UPDATE Species SET SpeciesId=Id WHERE ... (see adding-species.gmi for the full worked example, adding C. elegans).
  • FamilyOrderId and OrderId remain undocumented upstream (flagged TODO in adding-species.gmi); their exact intended derivation isn't recorded anywhere found in these repos.

Existing implementations

  • GeneNetwork2 (gn2/wqflask/api/router.py): GET /api/v_pre1/species (all species, columns Id/Name/FullName/ TaxonomyId) and GET /api/v_pre1/species/<species_name> (one species, matched against Name, FullName or SpeciesName, same 4-column subset). gn2/base/species.py's TheSpecies class also exposes a species' chromosomes via Chr_Length.
  • GeneNetwork3: gn3/db/species.py has get_all_species, get_chromosome, species_name (species for an InbredSet group) and translate_to_mouse_gene_id; none of these are exposed as a Species-table REST endpoint. gn3/api/metadata.py's GET /species and GET /species/<name> are SPARQL-based RDF metadata lookups instead, a different data source from the MariaDB Species table.
  • gn-guile (gn/data/species.scm): SPARQL/RDF metadata only, same category as GN3's /species above, not backed by db_webqtl.
  • GeneNetwork4 (this repo): GET /species (all species, mirrors GN3's get_all_species query/ordering) and GET /species/{species} (one species, every column, same match logic as GN2's get_species_info -- see README.md).

InbredSet

The InbredSet table (full CREATE TABLE in docs/mariadb_db-webqtl.md) represents a group/population of genetically related individuals within a species, e.g. BXD (a mouse recombinant inbred panel). Every dataset (PublishFreeze, GenoFreeze, ProbeFreeze/ProbeSetFreeze) belongs to exactly one InbredSet.

A good alias for InbredSet is "Group" -- this is the term used consistently across GeneNetwork's own code and docs, not an invention for this document:

  • GeneNetwork2's REST API names the endpoints GET /api/v_pre1/groups and GET /api/v_pre1/group/<group_name> (gn2/wqflask/api/router.py), mirroring /species//species/<name> one level down.
  • GeneNetwork2's DatasetGroup class (gn2/base/data_set/datasetgroup.py) docstring: "Each group has multiple datasets; each species has multiple groups. For example, Mouse has multiple groups (BXD, BXA, etc)."
  • The variable name group_name (and self.group) appears throughout both GeneNetwork2 and GeneNetwork3 wherever an InbredSet.Name is passed around.
  • topics/documentation/gn-hacking-documentation.gmi has a "Groups" heading stating plainly: "These are in the InbredSet table in the database."

"RISet" (Recombinant Inbred Set) shows up as a secondary, more specialist alias -- GeneNetwork2's API docs (api_readme.md) call the same thing "Groups/RISets" and use group/riset interchangeably in prose, and GeneNetwork3's gn3.db.species.species_name docstring reads "given the group (RISet)". "Group" is the more broadly understood term and the one used in user-facing API routes and UI text; "RISet" is closer to the underlying genetics (not every InbredSet is strictly a recombinant inbred set -- GeneticType also records "intercross" populations -- so "RISet" is a slight misnomer for the general case).

Fields

Field Meaning
Id Internal group identifier (auto-increment primary key).
InbredSetId Set equal to Id after insert; FKs (Publish/Geno/ProbeFreeze) use this.
InbredSetName UI display name -- GN2 calls this DisplayName in JSON -- e.g. "BXD".
Name Short name for URLs/API, e.g. "BXD", "B6D2F2"; usually = InbredSetName.
SpeciesId FK to Species.SpeciesId; a group belongs to exactly one species.
FullName Longer descriptive name, e.g. "BXD RI Family", "BXD Family".
public Numeric visibility level (0-2 seen); threshold semantics undocumented.
MappingMethodId Mapping tool offered, if any; see footnote below the table.
GeneticType Breeding scheme, e.g. "riset" (as in BXD) or "intercross" (F2/AIL).
Family Loose grouping across groups, parallel to Species.Family.
FamilyOrder Sort order within Family (no Id suffix, unlike Species).
MenuOrderId Sort order for display in the group menu.
InbredSetCode Short code, e.g. "BXD", "AXB"; used in precompute/genotype naming.
Description Free-text description of the group.

MappingMethodId is a key into the MappingMethod table (Id, Name): 1 qtlreaper, 2 Rqtl, 3 HappyR, 4 PLINK, 5 FastMap; null means no mapping tool is offered. This is the authoritative reading, and the one genenetwork4 uses (mapping_method is the table's name for the id; GET /mapping-methods lists the table). Queried live: 58 groups use 1, 20 use 2, 2 use 3, 22 have none; PLINK and FastMap are defined but unused. GeneNetwork2's get_mapping_methods (gn2/base/data_set/datasetgroup.py) instead treats the id as a code for a set of tools ("1" = GEMMA + QTLReaper + R/qtl, "2" = GEMMA only, ...); that reading is superseded and is not followed here.

Relationships

  • InbredSet.SpeciesId -> Species -- every group belongs to exactly one species (see Species section above).
  • PublishFreeze.InbredSetId, GenoFreeze.InbredSetId, ProbeFreeze.InbredSetId -> InbredSet -- every dataset belongs to exactly one group; genenetwork2/gn2/base/data_set/__init__.py and datasettype.py join on these throughout.
  • StrainXRef.InbredSetId -> InbredSet -- links which strains/samples are members of a group and whether they're Used_for_mapping (topics/systems/mariadb/precompute-mapping-input-data.gmi).
  • InbredSet.SpeciesId also scopes authorization the same way as described in the Species section: GN3's gn3/api/case_attributes.py resolves a species ID from InbredSet to build the gn-auth resource ID for a given group.
  • In the RDF/SPARQL classification scheme (topics/next-gen-databases/gn-classification-scheme.gmi), InbredSet is the "Set" level (gnc:Set), the middle of the three-level scheme between gnc:DatasetType and gn:Species.

Known quirks

  • public and the confidentiality field seen on dataset-level tables (ProbeSetFreeze, etc.) are separate, differently-scaled visibility concepts; neither's exact value semantics were found documented in these repos.
  • FamilyOrder on InbredSet has no Id suffix, unlike the otherwise parallel Species.FamilyOrderId -- a naming inconsistency between the two tables, not a meaningful difference.
  • Not every InbredSet is a true recombinant inbred set in the genetics sense (see "GeneticType" above and the "RISet" alias discussion) -- InbredSet is GeneNetwork's catch-all table for any group/population, regardless of its breeding design.

Existing implementations

  • GeneNetwork2 (gn2/wqflask/api/router.py): GET /api/v_pre1/groups[/<species_name>] (all groups, optionally scoped to a species) and GET /api/v_pre1/group/<group_name> (one group, matched against InbredSetName, Name or FullName, optionally also scoped to a species). gn2/base/data_set/datasetgroup.py's DatasetGroup class additionally resolves a group's available mapping methods from MappingMethodId, by its own code table rather than the MappingMethod table (see the MappingMethodId field above).
  • GeneNetwork3: no InbredSet-table REST endpoint or gn3/db/ module was found for groups specifically (unlike gn3/db/species.py for species).
  • gn-guile (gn/data/group.scm): get-group-links and get-group-data, SPARQL/RDF metadata only, same pattern as 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 id within a group; unique only paired with InbredSetId (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 rows colliding 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).

ProbeSetXRef

The ProbeSetXRef table (full CREATE TABLE in docs/mariadb_db-webqtl.md) is the expression (mRNA) counterpart to PublishXRef: one row per probe/gene per expression dataset, tying together a probe's actual per-strain values (ProbeSetData) with a single best QTL hit (Locus/LRS/pValue/additive/mean). Unlike PublishXRef, it has no single Id column of its own -- its primary key is DataId, and the natural key identifying one row is the pair (ProbeSetFreezeId, ProbeSetId).

Fields

Field Meaning
ProbeSetFreezeId FK to ProbeSetFreeze; the expression dataset this row belongs to.
ProbeSetId FK to ProbeSet; the probe/gene this row's mapping belongs to.
DataId Primary key; FK to ProbeSetData.Id, the per-strain values.
mean Mean expression value across strains.
se Standard error of the mean.
Locus Marker/SNP name of this trait's single stored QTL hit.
LRS LOD/LRS score of that hit.
pValue p-value of that hit.
additive Additive effect size of that hit.
h2 Heritability estimate.

Relationships

  • ProbeSetXRef.ProbeSetFreezeId -> ProbeSetFreeze -- the expression dataset (e.g. a tissue/platform/batch combination) this mapping belongs to. ProbeSetFreeze.ProbeFreezeId -> ProbeFreeze.ProbeFreezeId (the same "set equal to the primary key after insert" pattern as Species.SpeciesId/InbredSet.InbredSetId) -> ProbeFreeze.InbredSetId -> InbredSet is how an expression dataset is scoped to a group; ProbeFreeze.TissueId -> Tissue names the tissue it was measured in.
  • ProbeSetXRef.ProbeSetId -> ProbeSet -- the probe/gene annotation (Name, Symbol, description, Chr, Mb, alias, GeneId, ...). ProbeSet.Name is only unique per (ChipId, Name), not globally, but is unique in practice within one dataset's member probes, which is how GeneNetwork2 and this repo both address a probe (dataset name plus ProbeSet.Name) without needing ProbeSetId in the URL.
  • ProbeSetXRef.DataId -> ProbeSetData.Id -- the trait's actual per-strain values; ProbeSetData.StrainId -> Strain names them. ProbeSetSE (per-strain standard error) keys off the same (DataId, StrainId) pair, optional (left-joined in GeneNetwork2's own queries), same pattern as PublishSE for PublishData.

Known quirks

  • No (ProbeSetFreezeId, ProbeSetId) UNIQUE KEY is declared in the schema (only separate, non-unique KEYs on each column) -- a probe is assumed to appear at most once per dataset by convention, not by a database constraint; both GeneNetwork2 and this repo rely on that assumption (exec_first/fetchone()).
  • Only a single highest-LRS hit is stored per probe, the same limitation PublishXRef has (see its Known quirks above) -- the same precompute docs discuss extending both tables to store more hits.

Existing implementations

  • GeneNetwork2 (gn2/wqflask/api/router.py): get_datasets_for_group (list, ProbeSetFreeze joined to ProbeFreeze/InbredSet), get_dataset_info's ProbeSet branch (one dataset, joined to Tissue), trait_sample_data's ProbeSet branch (per-strain ProbeSetData values), get_trait_info's ProbeSet branch (ProbeSet annotation plus ProbeSetXRef mapping stats, minus pValue).
  • GeneNetwork4 (this repo): GET /groups/{group}/datasets (list), GET /datasets/{dataset} (one dataset -- not scoped under /groups/{group}, since a dataset name is globally unique on its own), GET /datasets/{dataset}/probesets/{probeset} (the ProbeSetXRef/ProbeSet join above) and GET /datasets/{dataset}/probesets/{probeset}/values (per-strain ProbeSetData values).