Database Information

WARNING: This document contains information on the GN databases which will change over time. The GN database is currently MySQL based and, while efficient, contains a number of design choices we want to grow ‘out’ of. Especially with an eye on reproducibility we want to introduce versioning.

So do not treat the information in this document as a final way of accessing data. It is better to use the REST API.

The small test database (2GB)

The default install comes with a smaller database which includes a number of the BXD’s and the Human liver dataset (GSE9588).

GeneNetwork database

Estimated table sizes

select table_name,round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` from information_schema.TABLES where table_schema = “db_webqtl” order by data_length;

~~————————-~~————+

table_name

Size in MB

~~————————-~~————+

ProbeSetData	59358.80
SnpAll	15484.67
ProbeData	22405.44
SnpPattern	9177.05
ProbeSetSE	14551.02
QuickSearch	5972.86
ProbeSetXRef	4532.89
LCorrRamin3	18506.53
ProbeSE	6263.83
ProbeSet	2880.21
Probe	2150.30
GenoData	3291.91
CeleraINFO_mm6	989.80
pubmedsearch	1032.50
ProbeXRef	743.38
GeneRIF_BASIC	448.54
BXDSnpPosition	224.44
EnsemblProbe	133.66
EnsemblProbeLocation	105.49
Genbank	37.71
TissueProbeSetData	74.42
AccessLog	42.38
GeneList	34.11
Geno	33.90
MachineAccessLog	28.34
IndelAll	22.42
PublishData	22.54
TissueProbeSetXRef	14.73
ProbeH2	13.26
GenoXRef	22.83
TempData	8.35
GeneList_rn3	5.54
GORef	4.97
Phenotype	6.50
temporary	3.59
InfoFiles	3.32
Publication	3.42
Homologene	5.69
Datasets	2.31
GeneList_rn33	2.61
PublishSE	4.71
GeneRIF	2.18
Vlookup	1.87
H2	2.18
PublishXRef	2.18
NStrain	4.80
IndelXRef	2.91
Strain	1.07
GeneMap_cuiyan	0.51
user_collection	0.30
CaseAttributeXRef	0.44
StrainXRef	0.56
GeneIDXRef	0.77
Docs	0.17
News	0.17
ProbeSetFreeze	0.22
GeneRIFXRef	0.24
Sample	0.06
login	0.06
user	0.04
TableFieldAnnotation	0.05
DatasetMapInvestigator	0.05
User	0.04
ProbeFreeze	0.06
TableComments	0.02
Investigators	0.02
DBList	0.03
Tissue	0.02
GeneChip	0.01
GeneCategory	0.01
SampleXRef	0.01
InbredSet	0.01
SnpAllele_to_be_deleted	0.00
Organizations	0.01
PublishFreeze	0.00
GenoFreeze	0.00
Chr_Length	0.01
SnpSource	0.00
AvgMethod	0.00
Species	0.00
Dataset_mbat	0.00
TissueProbeFreeze	0.00
EnsemblChip	0.00
TissueProbeSetFreeze	0.01
UserPrivilege	0.00
CaseAttribute	0.00
MappingMethod	0.00
DBType	0.00
InfoFilesUser_md5	0.00
GenoCode	0.00
DatasetStatus	0.00
GeneChipEnsemblXRef	0.00
GenoSE	0.00
user_openids	0.00
roles_users	0.00
role	0.00
Temp	NULL

~~————————-~~————+ 97 rows in set, 1 warning (0.01 sec)

All *Data tables are large

User access

According to the meta data:

This table tracks access time and IP addresses. Used for logging in registered users and tracking cookies.

select * from AccessLog limit 5;

~~——-~~———————+—————-+

id

accesstime

ip_address

~~——-~~———————+—————-+

12174	2003-10-28 02:17:41	130.120.104.71
12173	2003-10-28 02:16:27	130.120.104.71
3	2003-02-22 07:38:33	192.117.159.1
4	2003-02-22 07:49:13	192.117.159.1
5	2003-02-22 07:51:08	192.117.159.1

~~——-~~———————+—————-+

select * from AccessLog order by accesstime desc limit 5; ~~———~~———————+—————+

id

accesstime

ip_address

~~———~~———————+—————+

1025735	2016-02-08 14:23:29	100.43.81.157
1025734	2016-02-08 13:54:28	180.76.15.144
1025733	2016-02-08 13:43:37	66.249.65.217
1025732	2016-02-08 13:39:50	66.249.65.217
1025731	2016-02-08 13:15:46	66.249.65.217

~~———~~———————+—————+

Quite a few trait page hits:

select count(*) from AccessLog;

~~———-~~

count(*)

~~———-~~

1025685

~~———-~~

show indexes from AccessLog; ~~———–~~————+———-+————–+————-+———–+————-+———-+——–+——+————+———+—————+

Table

Non_unique

Key_name

Seq_in_index

Column_name

Collation

Cardinality

Sub_part

Packed

Null

Index_type

Comment

Index_comment

~~———–~~————+———-+————–+————-+———–+————-+———-+——–+——+————+———+—————+

AccessLog

0

PRIMARY

1

id

A

1025685

NULL

BTREE

~~———–~~————+———-+————–+————-+———–+————-+———-+——–+——+————+———+—————+

This table is being used by both GN1 and GN2 from the trait pages!

grep -ir AccessLog *|grep -e "^gn1\|^gn2"|grep \.py|grep -v doc

gn1/web/webqtl/showTrait/ShowTraitPage.py: query = “SELECT count(id) FROM AccessLog WHERE ip_address = %s and \ gn1/web/webqtl/showTrait/ShowTraitPage.py: self.cursor.execute(“insert into AccessLog(accesstime,ip_address) values(Now(),%s)” ,user_ip) gn1/web/webqtl/textUI/cmdClass.py: query = “”“SELECT count(id) FROM AccessLog WHERE ip_address = %s AND UNIX_TIMESTAMP()-UNIX_TIMESTAMP(accesstime)<86400”“” gn1/web/webqtl/textUI/cmdClass.py: query = “”“INSERT INTO AccessLog(accesstime,ip_address) values(Now(),%s)”“” gn2/wqflask/wqflask/show_trait/show_trait_page.py: query = “SELECT count(id) FROM AccessLog WHERE ip_address = %s and \ gn2/wqflask/wqflask/show_trait/show_trait_page.py: self.cursor.execute(“insert into AccessLog(accesstime,ip_address) values(Now(),%s)”, user_ip)

When looking at the code in GN1 and GN2 it restricts the daily use of the trait data page (set to 1,000 - whoever reaches that?). Unlike mentioned in the schema description, this table does not keep track of cookies.

From the code it looks like GN2 uses a mixture of Redis and sqlalchemy to keep track of logged in sessions (see gn2/wqflask/wqflask/user_manager.py) and cookies through a user_uuid in model.py.

In gn2/wqflask/wqflask/templates/collections/view_anonymous.html it show_trait_page appears to be loaded (need to check).

AvgMethod

Probesetfreeze refers to AvgMethod

BXDSnPosition

Snp table (all snps)

Mapping in GN1 shows snps when you select a chromosome.

CaseAttribute(XRef)

Metadata

CeleralINFO_mm6

?

Chr_Length (/cross/BXD.json)

Default mm9, column for mm8

select * from Chr_Length;

Name	SpeciesId	OrderId	Length	Length_mm8
1	1	1	197195432	197069962
2	1	2	181748087	181976762

Table should be merged with

Dataset_mbat

Menu for BXD (linkouts)

DatasetMapInvestigator

Arthur?

DataSets

Information/metadata

DatasetStatus

Arthur private/public

DBList and DBType

Hooked in API (URL encoding)

Docs

GN2 only (see menu bar)

Ensembl*

Probe information

(will be deprecated)

Genbank

Linkout and not important

GeneCategory

Not important. GeneWiki notes function classification.

Deprecate.

GeneChip

GeneIDXRef

Interspecies gene comparison

GeneList

Track info

Genlist_rn3(3)

Rat list

GeneMap_cuiyan

Link outs

GeneRIF

Wiki info (nightly updated from NCBI)

XRef should be foreign keys

Geno (genotype/marker/’marker’.json)

SNP or marker info

INFO:base.trait:.sql: retrieve_info: select Geno.Chr, Geno.Mb from Geno, Species where Species.Name = ‘mouse’ and Geno.Name = ‘rs3693478’ and Geno.SpeciesId = Species.Id

Id

SpeciesId

Name

Marker_Name

Chr

Mb

Sequence

Source

chr_num

Source2

Comments

used_by_geno_file

Mb_mm8

Chr_mm8

1

01.001.695

1

4.678288

GCCCTGCCCACCTCAGAGCAAGCTGCCACCCAGGAGTCCGTGTTTCAGGAGATGTGTGAGGAGGGCCTGCTGGAGGAGTGTGATGGTGAGGATGAGGCAGGCCGTGCCGCG[T/C]AGCCAGAGGCTGGTGATGGGACCACCGAGATCTCACCCACTGGTGCTGCTGATCCTGAGAAGAGGATGGAGAAGAAGACGGAGCAGCAGCACACCGGCGGCGGGAGAAAGCTGCTCGTAAGCTGCTCGTAAGCTACGGGTGCAGCAGGCTGCACTTAGGGCAGCCCGGCTTCAGCACCAAGAACTCTTCAGGCTGCATGGGATCAAGGCCCAGGTGGCCCGAAGGCTGGCAGAACTCGCACACGGGAGGGAGCAGCAGCGCATACAGCGACTGGCAGAGGCTGACAAGCCCCGAAGGCTGGGACGACTCAAGTACCAGGCTCCTGACATTGATGTGCAGCTCAGCTCTGAGCTGTCTGGCCCACTCAGGACACTGAAACCAGAAGGTCACATTCTCCAAGACAGGTTCAAGAGCTTCCAGAAGAGAAATATGATTGAGCCCCGAGAACGAGCCAAGTTCAAGCGCAAATAAAAAATGAAGTTGGTGGAGAAGCGGGCCTACCATGAGATTCAGTTGTAGCTGTGCAGATGTCGGAGCCCCGCCCCTCAATAAAGTTCTGTGACAAAAAAAAAAAAAAAAAAAGAAGAAGAAGAAGAAAAGGAAAAAAAAGAAGAAAAAGAAAAAAAAAGAAAAAAGAAAAAGAAAACACATCACTTGGCAAAACTCCATAGACTCTATGTGATTCATGTTTCAAACATGCACCTA

GNF_SNP

1

GNF

NULL

4.678288

1

GenoCode

Belongs to someone else

GenoData

Allele info

GenoFreeze

Big menu (Freeze refers to menu)

GenoSE

SE standard err, not used

GenoXREF

Very important. Key links between Geno, GenoData

GORef

GO terms

H2

Heritability for probeset(?)

Homologene

Homology, not used much

InbredSet (/cross/BXD.info)

Group in menu

Indelall, SnpAll, SnpPattern, SnpSource

Indel Snp browser (variant browser Gn1)

Info*

Infra system PhP

Data Info button

Infosystem users has separate entries

Also Investigators, User, Organizations,

LCorrRamin3

Lit. Correlations Prof. Ramin

Login

GN2 login info

MachineAccessLog

Old

MappingMethod

GN1

News

GN2

NStrain

pheno publishfreeze (menu) xref (keys) xref links to publish (pubmed), phenotype, pubishdata geno genofreeze xref (keys) xref links to publish (pubmed), genotype, genodata probeset/expr. probesetfreeze xref (keys) xref links to publish (pubmed), probeset, probesetdata probe/expr. probefreeze xref (keys) xref links to publish (pubmed), probe, probedata

Each dataset has 3 values (real value (1), number of samples (2), stderr (3))

NStrain = number of phenotype samples

ProbesetFreeze contains all data, incl. metabolomic.

Phenotype

This table contains names, full descriptions, and short symbols for traits and phenotype used primarily in the Published Phenotypes databases.

Contains 10k rows, March 2016, of which 5000 are for the BXDs).

Id

Pre_publication_description

Post_publication_description

Original_description

Units

Pre_publication_abbreviation

Post_publication_abbreviation

Lab_code

Submitter

Owner

Authorized_Users

—-—————————–+———————————————————————————————————————-+————————————————————————————————————————————————————-+———————-+——————————+——————————-+———-+————-+——-+——————+

1	NULL	Hippocampus weight	Original post publication description: Hippocampus weight	Unknown	NULL	HPCWT	NULL	robwilliams	NULL	robwilliams
2	NULL	Cerebellum weight	Original post publication description: Cerebellum weight	mg	NULL	CBLWT	NULL	robwilliams	NULL	robwilliams
3	NULL	Interleukin 1 activity by peritoneal macrophages stimulated with 10 ug/ml lipopolysaccharide [units/100 ug protein]	Original post publication description: Interleukin 1 activity by peritoneal macrophages stimulated with 10 ug/ml lipopolysaccharide [units/100 ug protein]	units/100 ug protein	NULL	IL1Activity	NULL	robwilliams	NULL	robwilliams
4	NULL	Central nervous system, morphology: Cerebellum weight, whole, bilateral in adults of both sexes [mg]	Original post publication description: Cerebellum weight [mg]	mg	NULL	CBLWT2	NULL	robwilliams	NULL	robwilliams
5	NULL	The coat color of 79 BXD RI strain	Original post publication description: The coat color of 79 BXD RI strain	Unknown	NULL	CoatColor	NULL	robwilliams	NULL	robwilliams

—-—————————–+———————————————————————————————————————-+————————————————————————————————————————————————————-+———————-+——————————+——————————-+———-+————-+——-+——————+ 5 rows in set (0.00 sec)

ProbeData

Table with fine-grained probe level Affymetrix data only. Contains 1 billion rows March 2016. This table may be deletable since it is only used by the Probe Table display in GN1. Not used in GN2 (double-check).

In comparison the “ProbeSetData” table contains more molecular assay data, including probe set data, RNA-seq data, proteomic data, and metabolomic data. 2.5 billion rows March 2016. In comparison, ProbeData contains data only for Affymetrix probe level data (e.g. Exon array probes and M430 probes).

“ProbeData.StrainId” should be “CaseId” or “SampleId”.

“ProbeData” should probably be “AssayData” or something more neutral.

select * from ProbeData limit 2; ~~——–~~———-+———+

Id

StrainId

value

~~——–~~———-+———+

503636	42	11.6906
503636	43	11.4205

~~——–~~———-+———+ 2 rows in set (0.00 sec)

select count(*) from ProbeData limit 2; ~~———–~~

count(*)

~~———–~~

976753435

~~———–~~ 1 row in set (0.00 sec)

ProbeSet

Comment: PLEASE CHANGE TABLE NAME and rework fields carefully. This is a terrible table but it works well (RWW March 2016). It is used in combination with the crucial TRAIT DATA and ANALYSIS pages in GN1 and GN2. It is also used by annotators using the UPDATE INFO AND DATA web form to correct and update annotation. It is used by Arthur to enter new annotation files and metadata for arrays, genes, proteins, metabolites. The main problem with this table is that it is doing too much work.

Initially (2003) this table contained only Affymetrix ProbeSet data for mouse (U74aV2 initially). Many other array platforms for different species were added. At least four other major categories of molecular assays have been added since about 2010.

RNA-seq annotation and sequence data for transcripts using ENSEMBL identifiers or NCBI NM_XXXXX and NR_XXXXX type identifiers
Protein and peptide annotation and sequence data (see BXD Liver Proteome data, SRM and SWATH type data) with identifiers such as “abcb10_q9ji39_t311” for SRM data and “LLGNMIVIVLGHHLGKDFTPAAQAA” for SWATH data where the latter is just the peptide fragment that has been quantified. Data first entered in 2015 for work by Rudi Aebersold and colleagues.
Metabolite annotation and metadata (see BXD Liver Metabolome data) with identifiers that are usually Mass charge ratios such as “149.0970810_MZ”
Epigenomic and methylome data (e.g. Human CANDLE Methylation data with identifiers such as “cg24523000”)

It would make good sense to break this table into four or more types of molecular assay metadata or annotation tables) (AssayRNA_Anno, AssayProtein_Anno, AssayMetabolite_Anno, AssayEpigenome_Anno, AssayMetagenome_Anno), since these assays will have many differences in annotation content compared to RNAs.

Some complex logic is used to update contents of this table when annotators modify and correct the information (for example, updating gene symbols). These features requested by Rob so that annotating one gene symbol in one species would annotate all gene symbols in the same species based on common NCBI GeneID number. For example, changing the gene alias for one ProbeSet.Id will changing the list of aliases in all instances with the same gene symbol.

If the ProbeSet.BlatSeq (or is this ProbSetTargetSeq) is identical between different ProbeSet.Ids then annotation is forced to be the same even if the symbol or geneID is different. This “feature” was implemented when we found many probe sets with identical sequence but different annotations and identifiers.

select count(*) from ProbeSet limit 5; ~~———-~~

count(*)

~~———-~~

4351030

~~———-~~

Id

ChipId

Name

TargetId

Symbol

description

Chr

Mb

alias

GeneId

GenbankId

SNP

BlatSeq

TargetSeq

UniGeneId

Strand_Probe

Strand_Gene

OMIM

comments

Probe_set_target_region

Probe_set_specificity

Probe_set_BLAT_score

Probe_set_Blat_Mb_start

Probe_set_Blat_Mb_end

Probe_set_strand

Probe_set_Note_by_RW

flag

Symbol_H

description_H

chromosome_H

MB_H

alias_H

GeneId_H

chr_num

name_num

Probe_Target_Description

RefSeq_TranscriptId

Chr_mm8

Mb_mm8

Probe_set_Blat_Mb_start_mm8

Probe_set_Blat_Mb_end_mm8

HomoloGeneID

Biotype_ENS

ProteinID

ProteinName

Flybase_Id

HMDB_ID

Confidence

ChEBI_ID

ChEMBL_ID

CAS_number

PubChem_ID

ChemSpider_ID

UNII_ID

EC_number

KEGG_ID

Molecular_Weight

Nugowiki_ID

Type

Tissue

PrimaryName

SecondaryNames

PeptideSequence

————–+———-+———-+——–+———————————————-+——+———–+———-+——–+———–+——+——————————————————————————————————————————————————————————+—————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————+———–+————–+————-+——–+———-+————————-+———————–+———————-+————————-+———————–+——————+———————-+——+———-+—————+————–+——+———+———-+———+———-+————————–+———————+———+———–+—————————–+—————————+————–+————-+———–+————-+————+———+————+———-+———–+————+————+—————+———+———–+———+——————+————-+——+——–+————-+—————-+—————–+

7282

1

93288_at

NULL

Arpc2

actin related protein 2/3 complex, subunit 2

1

74.310961

AK008777

76709

AI835883

0

CCGACTTCCTTAAGGTGCTCAACCGGACTGCTTGCTACTGGATAATCGTGAGGGATTCTCCATTTGGGTTCCATTTTGTACGAGTTTGGCAAATAACCTGCAGAAACGAGCTGTGCTTGCAAGGACTTGATAGTTCCTAATCCTTTTCCAAGCTGTTTGCTTTGCAATATGT

ccgacttccttaaggtgctcaaccgtnnnnnnccnannnnccnagaaaaaagaaatgaaaannnnnnnnnnnnnnnnnnnttcatcccgctaactcttgggaactgaggaggaagcgctgtcgaccgaagnntggactgcttgctactggataatcgtnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnntgagggattctccatttgggttccattttgtacgagtttggcaaataacctgcagaaacgagctgtgcttgcaaggacttgatagttcctaagaattanaanaaaaaaaanaanttccacttgatcaanttaattcccttttatttttcctccctcantccccttccttttccaagctgtttgctttgcaatatgt

Mm.337038

+

604224

NULL

8.45

169

74.310961

74.31466

NULL

3

NULL

1

93288

NULL

XM_129773

1

74.197594

74.201293

4187

NULL

————–+———-+———-+——–+———————————————-+——+———–+———-+——–+———–+——+——————————————————————————————————————————————————————————+—————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————+———–+————–+————-+——–+———-+————————-+———————–+———————-+————————-+———————–+——————+———————-+——+———-+—————+————–+——+———+———-+———+———-+————————–+———————+———+———–+—————————–+—————————+————–+————-+———–+————-+————+———+————+———-+———–+————+————+—————+———+———–+———+——————+————-+——+——–+————-+—————-+—————–+ 2 rows in set (0.00 sec)

ProbeSetXRef (phenotypes/dataset_name.json)

For every probe set (read dataset measuring point):

select * from ProbeSetXRef;

ProbeSetFreezeId

ProbeSetId

DataId

Locus_old

LRS_old

pValue_old

mean

se

Locus

LRS

pValue

additive

h2

112

123528

23439389

NULL

6.7460707070707

NULL

rs6239372

10.9675593568894

0.567

0.0448545966228878

NULL

112

123527

23439388

NULL

6.19416161616162

NULL

rs13476936

10.9075670392762

0.567

-0.0358456732993988

NULL

where ProbeSetFreezeId is the dataset (experiment). ProbesetId refers to the probe set information (measuring point). DataId points to the data point. The othe values are used for search.

It is used in search thus:

SELECT distinct ProbeSet.Name as TNAME, ProbeSetXRef.Mean as TMEAN, ProbeSetXRef.LRS as TLRS, ProbeSetXRef.PVALUE as TPVALUE, ProbeSet.Chr_num as TCHR_NUM, ProbeSet.Mb as TMB, ProbeSet.Symbol as TSYMBOL, ProbeSet.name_num as TNAME_NUM FROM ProbeSetXRef, ProbeSet WHERE ProbeSet.Id = ProbeSetXRef.ProbeSetId and ProbeSetXRef.ProbeSetFreezeId = 112 ORDER BY ProbeSet.symbol ASC limit 5;

TNAME	TMEAN	TLRS	TPVALUE	TCHR_NUM	TMB	TSYMBOL	TNAME_NUM
1445618_at	7.05679797979798	13.5417452764616	0.17	8	75.077895	NULL	1445618
1452452_at	7.232	30.4944361132252	0.0000609756097560421	12	12.6694	NULL	1452452

ProbeSetData

Probedata - main molecular data. Probesets, metabolome,

Almost all important molecular assay data is in this table including probe set data, RNA-seq data, proteomic data, and metabolomic data. 2.5 billion rows March 2016. In comparison, ProbeData contains data only for Affymetrix probe level data (e.g. Exon array probes and M430 probes).

select count(*) from ProbeSetData limit 5; ~~—————~~

count(*)

~~—————~~

2,510,566,472

~~—————~~

select * from ProbeSetData limit 5; —-———-+——-+

Id

StrainId

value

—-———-+——-+

1	1	5.742
1	2	5.006
1	3	6.079
1	4	6.414
1	5	4.885

—-———-+——-+

show indexes from ProbeSetData; ~~————–~~————+———-+————–+————-+———–+————-+———-+——–+——+————+———+—————+

Table

Non_unique

Key_name

Seq_in_index

Column_name

Collation

Cardinality

Sub_part

Packed

Null

Index_type

Comment

Index_comment

~~————–~~————+———-+————–+————-+———–+————-+———-+——–+——+————+———+—————+

ProbeSetData

0

DataId

1

Id

A

34868978

NULL

BTREE

ProbeSetData

0

DataId

2

StrainId

A

2510566472

NULL

BTREE

~~————–~~————+———-+————–+————-+———–+————-+———-+——–+——+————+———+—————+

select * from Strain limit 5; —-———-+———-+———–+——–+——-+

Id

Name

Name2

SpeciesId

Symbol

Alias

—-———-+———-+———–+——–+——-+

1	B6D2F1	B6D2F1	1	NULL	NULL
2	C57BL/6J	C57BL/6J	1	B6J	NULL
3	DBA/2J	DBA/2J	1	D2J	NULL
4	BXD1	BXD1	1	NULL	NULL
5	BXD2	BXD2	1	NULL	NULL

—-———-+———-+———–+——–+——-+

show indexes from Strain; ~~——–~~————+———-+————–+————-+———–+————-+———-+——–+——+————+———+—————+

Table

Non_unique

Key_name

Seq_in_index

Column_name

Collation

Cardinality

Sub_part

Packed

Null

Index_type

Comment

Index_comment

~~——–~~————+———-+————–+————-+———–+————-+———-+——–+——+————+———+—————+

Strain

0

PRIMARY

1

Id

A

14368

NULL

BTREE

Strain

0

Name

1

Name

A

14368

NULL

YES

BTREE

Strain

0

Name

2

SpeciesId

A

14368

NULL

BTREE

Strain

1

Symbol

1

Symbol

A

14368

NULL

YES

BTREE

~~——–~~————+———-+————–+————-+———–+————-+———-+——–+——+————+———+—————+

A typical query may look like

SELECT Strain.Name, ProbeSetData.value, ProbeSetSE.error, ProbeSetData.Id FROM (ProbeSetData, ProbeSetFreeze, Strain, ProbeSet, ProbeSetXRef) left join ProbeSetSE on (ProbeSetSE.DataId = ProbeSetData.Id AND ProbeSetSE.StrainId = ProbeSetData.StrainId) WHERE ProbeSetFreeze.name = ‘B139_K_1206_M’ AND ProbeSetXRef.ProbeSetId = ProbeSet.Id AND ProbeSetXRef.ProbeSetFreezeId = ProbeSetFreeze.Id AND ProbeSetXRef.DataId = ProbeSetData.Id AND ProbeSetData.StrainId = Strain.Id Order BY Strain.Name

~~——-~~——-+——-+———-+

Name

value

error

Id

~~——-~~——-+——-+———-+

SM001	38.3	NULL	25309550
SM001	2.7	NULL	25309520
SM001	20.3	NULL	25309507
SM001	125.8	NULL	25309511
SM001	8.2	NULL	25309534

~~——-~~——-+——-+———-+ 5 rows in set (22.28 sec)

select * from ProbeSetFreeze limit 5; —-—————+——-+————-+———————————+———————————————+————————-+————+———–+——–+—————–+—————–+———–+

Id

ProbeFreezeId

AvgID

Name

Name2

FullName

ShortName

CreateTime

OrderList

public

confidentiality

AuthorisedUsers

DataScale

—-—————+——-+————-+———————————+———————————————+————————-+————+———–+——–+—————–+—————–+———–+

1	3	1	Br_U_0803_M	BXDMicroArray_ProbeSet_August03	UTHSC Brain mRNA U74Av2 (Aug03) MAS5	Brain U74Av2 08/03 MAS5	2003-08-01	NULL	NULL	log2
2	10	1	Br_U_0603_M	BXDMicroArray_ProbeSet_June03	UTHSC Brain mRNA U74Av2 (Jun03) MAS5	Brain U74Av2 06/03 MAS5	2003-06-01	NULL	NULL	log2
3	8	1	Br_U_0303_M	BXDMicroArray_ProbeSet_March03	UTHSC Brain mRNA U74Av2 (Mar03) MAS5	Brain U74Av2 03/03 MAS5	2003-03-01	NULL	NULL	log2
4	5	1	Br_U_0503_M	BXDMicroArray_ProbeSet_May03	UTHSC Brain mRNA U74Av2 (May03) MAS5	Brain U74Av2 05/03 MAS5	2003-05-01	NULL	NULL	log2
5	4	1	HC_U_0303_M	GNFMicroArray_ProbeSet_March03	GNF Hematopoietic Cells U74Av2 (Mar03) MAS5	GNF U74Av2 03/03 MAS5	2003-03-01	NULL	NULL	log2

—-—————+——-+————-+———————————+———————————————+————————-+————+———–+——–+—————–+—————–+———–+

select * from ProbeSetXRef limit 5; ~~——————~~————+——–+————+——————–+————+——————-+———————+—————–+——————–+——–+———————-+——+

ProbeSetFreezeId

ProbeSetId

DataId

Locus_old

LRS_old

pValue_old

mean

se

Locus

LRS

pValue

additive

h2

~~——————~~————+——–+————+——————–+————+——————-+———————+—————–+——————–+——–+———————-+——+

1	1	1	10.095.400	13.3971627898894	0.163	5.48794285714286	0.08525787814808819	rs13480619	12.590069931048001	0.269	-0.28515625	NULL
1	2	2	D15Mit189	10.042057464356201	0.431	9.90165714285714	0.0374686634976217	CEL-17_50896182	10.5970737900941	0.304	-0.11678333333333299	NULL
1	3	3	D5Mit139	5.43678531742749	0.993	7.83948571428571	0.0457583416912569	rs13478499	6.0970532702754	0.988	0.112957489878542	NULL
1	4	4	D1Mit511	9.87815279480766	0.483	8.315628571428569	0.0470396593931327	rs6154379	11.774867551173099	0.286	-0.157113725490196	NULL
1	5	5	D16H21S16	10.191723834264499	0.528	9.19345714285714	0.0354801718293322	rs4199265	10.923263374016202	0.468	0.11476470588235299	NULL

~~——————~~————+——–+————+——————–+————+——————-+———————+—————–+——————–+——–+———————-+——+

Note that the following unlimited search is very slow:

select max(value) from ProbeSetData;

~~————~~

max(value)

~~————~~

26436006

~~————~~ 1 row in set (2 min 16.31 sec)

which is in some form is used in the search page, see the search code.

Improvements?

Suggestions on the schema page:

“StrainId” should be “CaseId” or “SampleId”.

“ProbeSetData” should probably be “AssayData” or something more neutral.

Comments

I think the ProbeSetData table should be generalized to a ‘phenotypes’ table with an ‘sample_id’ column and a ‘value’ column.

A new table ‘samples’ will link each sample against an ‘experiment’, an ‘individual’ and which in turn can link to a ‘strain’.

Experiment is here in a wide sense, GTex can be one - I don’t want to use dataset ;)

This means a (slight) reordering:

phenotypes: (id), sample_id, value samples: experiment_id, individual_id experiments: name, version individual: strain_id strains: species_id species: …

ProbeData is also interesting, because it has the same structure as ProbeSetData, but only contains microarrays. This tables should be one (when we clear up the cross-referencing) as they both contain phenotype values. Both are large tables.

PublishData is another phenotype table with values only which can be merged into that same table.

So we have phenotype data in 3 tables with exactly the same layout. There is also TissueProbeSet*, but we’ll ignore those for now. I think we should merge these into one and have the sample ref refer to the type of data (probeset, probe, metabolomics, whatever). These are all phenotype values and by having them split into different tables they won’t play well when looking for correlations.

ProbeSet contains the metadata on the probes and should (eventually) move into NoSQL. There is plenty redundancy in that table now.

I know it is going to be a pain to reorganize the database, but if we want to use it in the long run we are going to have to simplify it.

ProbeSetFreeze and ProbeFreeze (/dataset/name.json)

GN_SERVER: /dataset/HC_M2_0606_P.json

ProbesetFreeze contains DataSet information, such as name, fullname of datasets, as well as whether they are public and how the data is scaled:

select * from ProbeSetFreeze;

Id

ProbeFreezeId

AvgID

Name

Name2

FullName

ShortName

CreateTime

OrderList

public

confidentiality

AuthorisedUsers

DataScale

112

30

2

HC_M2_0606_P

Hippocampus_M430_V2_BXD_PDNN_Jun06

Hippocampus Consortium M430v2 (Jun06) PDNN

Hippocampus M430v2 BXD 06/06 PDNN

2006-06-23

NULL

2

0

NULL

log2

Another table contains a tissue reference and a back reference to the cross type:

select * from ProbeFreeze;

Id	ProbeFreezeId	ChipId	TissueId	Name	FullName	ShortName	CreateTime	InbredSetId
30	30	4	9	Hippocampus Consortium M430v2 Probe (Jun06)			2006-07-07	1

NOTE: these tables can probably be merged into one.

show indexes from ProbeSetFreeze; ~~—————-~~————+———–+————–+————-+———–+————-+———-+——–+——+————+———+—————+

Table

Non_unique

Key_name

Seq_in_index

Column_name

Collation

Cardinality

Sub_part

Packed

Null

Index_type

Comment

Index_comment

~~—————-~~————+———–+————–+————-+———–+————-+———-+——–+——+————+———+—————+

ProbeSetFreeze

0

PRIMARY

1

Id

A

2

NULL

BTREE

ProbeSetFreeze

0

FullName

1

FullName

A

2

NULL

BTREE

ProbeSetFreeze

0

Name

1

Name

A

2

NULL

YES

BTREE

ProbeSetFreeze

1

NameIndex

1

Name2

A

2

NULL

BTREE

~~—————-~~————+———–+————–+————-+———–+————-+———-+——–+——+————+———+—————+

ProbeSetSE

select * from ProbeSetSE limit 5; ~~——–~~———-+———-+

DataId

StrainId

error

~~——–~~———-+———-+

1	1	0.681091
1	2	0.361151
1	3	0.364342
1	4	0.827588
1	5	0.303492

~~——–~~———-+———-+

Publication

Publication:

Id

PubMed_ID

Abstract

Title

Pages

Month

Year

Publishdata (all pheno)

One of three phenotype tables.

mysql> select * from PublishData limit 5; ~~———~~———-+——-+

Id

StrainId

value

~~———~~———-+——-+

8966353	349	29.6
8966353	350	27.8
8966353	351	26.6
8966353	352	28.5
8966353	353	24.6

~~———~~———-+——-+ 5 rows in set (0.25 sec)

See below for phenotype access.

QuickSearch

No longer used

role

empty

Sample*

No longer used

Species & Strain (should be sample)

select * from Species; —-———–+———————-+—————-+———————-+————————-+————+———+

Id

SpeciesId

SpeciesName

Name

MenuName

FullName

TaxonomyId

OrderId

—-———–+———————-+—————-+———————-+————————-+————+———+

1	1	Mouse	mouse	Mouse	Mus musculus	10090	30
2	2	Rat	rat	Rat	Rattus norvegicus	10116	40
3	3	Arabidopsis thaliana	arabidopsis	Arabidopsis thaliana	Arabidopsis thaliana	3702	60
4	4	Human	human	Human	Homo sapiens	9606	10
5	5	Barley	barley	Barley	Hordeum vulgare	4513	70
6	6	Drosophila	drosophila	Drosophila	Drosophila melanogaster	7227	50
7	7	Macaque monkey	macaque monkey	Macaque monkey	Macaca mulatta	9544	20
8	8	Soybean	soybean	Soybean	Soybean	3847	80
9	9	Tomato	tomato	Tomato	Tomato	4081	90

—-———–+———————-+—————-+———————-+————————-+————+———+

InbredSet

Menu

TableComments

Metadata on DB

Temp*

User upload data

Tissue

Menu - 3rd level

TissueP*

Correlation tables

User collection

User selection - retained

UserPrivilege

Vlookup

Fetching Data

Search

What happens at the main search menu can be emulated with curl.

Search Mouse mRNA from HC_M2_0606_P dataset

curl "http://localhost:5003/search?species=mouse&group=BXD&ppocampus+mRNA&dataset=HC_M2_0606_P&search_terms_or=&search_terms_and=MEAN%3D%2815+16%29+LRS%3D%2823+46%29+&FormID=searchResult"

the first SQL query checks whether a dataset is confidential or not ayd fetches full name info

INFO:base.data_set:.sql: geno_mrna_confidentiality: u’SELECT Id, Name, FullName, confidentiality,AuthorisedUsers FROM ProbeSetFreeze WHERE Name = “HC_M2_0606_P”’

The following is executed once per search:

INFO:db.call:.sql: retrieve_other_names: SELECT ProbeSetFreeze.Id, ProbeSetFreeze.Name, ProbeSetFreeze.FullName, ProbeSetFreeze.ShortName, ProbeSetFreeze.DataScale, Tissue.Name FROM ProbeSetFreeze, ProbeFreeze, Tissue WHERE ProbeSetFreeze.public > 0 AND ProbeSetFreeze.ProbeFreezeId = ProbeFreeze.Id AND ProbeFreeze.TissueId = Tissue.Id AND (ProbeSetFreeze.Name = ‘HC_M2_0606_P’ OR ProbeSetFreeze.FullName = ‘HC_M2_0606_P’ OR ProbeSetFreeze.ShortName = ‘HC_M2_0606_P’)

INFO:db.call:.sql: (112, ‘HC_M2_0606_P’, ‘Hippocampus Consortium M430v2 (Jun06) PDNN’, ‘Hippocampus M430v2 BXD 06/06 PDNN’, ‘log2’, ‘Hippocampus mRNA’)

INFO:db.call:.sql: __init__: SELECT InbredSet.Name, InbredSet.Id FROM InbredSet, ProbeSetFreeze, ProbeFreeze WHERE ProbeFreeze.InbredSetId = InbredSet.Id AND ProbeFreeze.Id = ProbeSetFreeze.ProbeFreezeId AND ProbeSetFreeze.Name = “HC_M2_0606_P”

INFO:db.call:.sql: (‘BXD’, 1)

The actual search is

SELECT distinct ProbeSet.Name as TNAME, 0 as thistable, ProbeSetXRef.Mean as TMEAN, ProbeSetXRef.LRS as TLRS, ProbeSetXRef.PVALUE as TPVALUE, ProbeSet.Chr_num as TCHR_NUM, ProbeSet.Mb as TMB, ProbeSet.Symbol as TSYMBOL, ProbeSet.name_num as TNAME_NUM FROM ProbeSetXRef, ProbeSet WHERE ProbeSet.Id = ProbeSetXRef.ProbeSetId and ProbeSetXRef.ProbeSetFreezeId = 112 ORDER BY ProbeSet.symbol ASC limit 5;

INFO:base.species:.sql: __init__: Select Chr_Length.Name, Chr_Length.OrderId, Length from Chr_Length, InbredSet where Chr_Length.SpeciesId = InbredSet.SpeciesId AND InbredSet.Name = ‘BXD’ Order by OrderId

And the following are executed for every trait:

INFO:wqflask.do_search:.sql: execute: SELECT distinct ProbeSet.Name as TNAME, 0 as thistable, ProbeSetXRef.Mean as TMEAN, ProbeSetXRef.LRS as TLRS, ProbeSetXRef.PVALUE as TPVALUE, ProbeSet.Chr_num as TCHR_NUM, ProbeSet.Mb as TMB, ProbeSet.Symbol as TSYMBOL, ProbeSet.name_num as TNAME_NUM FROM ProbeSetXRef, ProbeSet WHERE (( ProbeSetXRef.mean > 15.0 and ProbeSetXRef.mean < 16.0 )AND( ProbeSetXRef.LRS > 23.0 and ProbeSetXRef.LRS < 46.0 )) and ProbeSet.Id = ProbeSetXRef.ProbeSetId and ProbeSetXRef.ProbeSetFreezeId = 112 ORDER BY ProbeSet.symbol ASC

INFO:base.trait:.sql: retrieve_info: SELECT ProbeSet.name, ProbeSet.symbol, ProbeSet.description, ProbeSet.probe_target_description, ProbeSet.chr, ProbeSet.mb, ProbeSet.alias, ProbeSet.geneid, ProbeSet.genbankid, ProbeSet.unigeneid, ProbeSet.omim, ProbeSet.refseq_transcriptid, ProbeSet.blatseq, ProbeSet.targetseq, ProbeSet.chipid, ProbeSet.comments, ProbeSet.strand_probe, ProbeSet.strand_gene, ProbeSet.probe_set_target_region, ProbeSet.probe_set_specificity, ProbeSet.probe_set_blat_score, ProbeSet.probe_set_blat_mb_start, ProbeSet.probe_set_blat_mb_end, ProbeSet.probe_set_strand, ProbeSet.probe_set_note_by_rw, ProbeSet.flag

FROM ProbeSet, ProbeSetFreeze, ProbeSetXRef WHERE ProbeSetXRef.ProbeSetFreezeId = ProbeSetFreeze.Id AND ProbeSetXRef.ProbeSetId = ProbeSet.Id AND ProbeSetFreeze.Name = ‘HC_M2_0606_P’ AND ProbeSet.Name = ‘1443823_s_at’

INFO:base.trait:.sql: retrieve_info: SELECT HomologeneId FROM Homologene, Species, InbredSet WHERE Homologene.GeneId =98660 AND InbredSet.Name = ‘BXD’ AND InbredSet.SpeciesId = Species.Id AND Species.TaxonomyId = Homologene.TaxonomyId

INFO:base.trait:.sql: retrieve_info: SELECT ProbeSetXRef.Locus, ProbeSetXRef.LRS, ProbeSetXRef.pValue, ProbeSetXRef.mean, ProbeSetXRef.additive FROM ProbeSetXRef, ProbeSet WHERE ProbeSetXRef.ProbeSetId = ProbeSet.Id AND ProbeSet.Name = “1443823_s_at” AND ProbeSetXRef.ProbeSetFreezeId =112

INFO:base.trait:.sql: retrieve_info: select Geno.Chr, Geno.Mb from Geno, Species where Species.Name = ‘mouse’ and Geno.Name = ‘NES13033186’ and Geno.SpeciesId = Species.Id

and finally mean and chromosome positions are fetched a second time for every trait with

INFO:base.data_set:.sql: get_trait_info: select ProbeSetXRef.mean from ProbeSetXRef, ProbeSet where ProbeSetXRef.ProbeSetFreezeId = 112 and ProbeSet.Id = ProbeSetXRef.ProbeSetId and ProbeSet.Name = ‘1443823_s_at’

INFO:base.data_set:.sql: get_trait_info: select Geno.Chr, Geno.Mb from Geno, Species where Species.Name = ‘mouse’ and Geno.Name = ‘NES13033186’ and Geno.SpeciesId = Species.Id

(that is a bug!).

Fetch phenotype information

Through the trait page

When hitting the trait page, e.g.

curl “http://localhost:5003/show_trait?trait_id=1443823_s_aet=HC_M2_0606_P”

First the BXD’s are queried with

DEBUG:base.data_set:.get_samplelist: Sample list: : [‘BXD1’, ‘BXD2’, ‘BXD5’, …

main probeset info (trait) is retrieved with

SELECT ProbeSet.name, ProbeSet.symbol, ProbeSet.description, ProbeSet.probe_target_description, ProbeSet.chr, ProbeSet.mb, ProbeSet.alias, ProbeSet.geneid, ProbeSet.genbankid, ProbeSet.unigeneid, ProbeSet.omim, ProbeSet.refseq_transcriptid, ProbeSet.blatseq, ProbeSet.targetseq, ProbeSet.chipid, ProbeSet.comments, ProbeSet.strand_probe, ProbeSet.strand_gene, ProbeSet.probe_set_target_region, ProbeSet.probe_set_specificity, ProbeSet.probe_set_blat_score, ProbeSet.probe_set_blat_mb_start, ProbeSet.probe_set_blat_mb_end, ProbeSet.probe_set_strand, ProbeSet.probe_set_note_by_rw, ProbeSet.flag FROM ProbeSet, ProbeSetFreeze, ProbeSetXRef WHERE ProbeSetXRef.ProbeSetFreezeId = ProbeSetFreeze.Id AND ProbeSetXRef.ProbeSetId = ProbeSet.Id AND ProbeSetFreeze.Name = ‘HC_M2_0606_P’ AND ProbeSet.Name = ‘1443823_s_at’

Followed by

INFO:base.trait:.sql: retrieve_info: SELECT ProbeSetXRef.Locus, ProbeSetXRef.LRS, ProbeSetXRef.pValue, ProbeSetXRef.mean, ProbeSetXRef.additive FROM ProbeSetXRef, ProbeSet WHERE ProbeSetXRef.ProbeSetId = ProbeSet.Id AND ProbeSet.Name = “1443823_s_at” AND ProbeSetXRef.ProbeSetFreezeId =112

Locus	LRS	pValue	mean	additive
NES13033186	35.466324074542	0.00000900000000003676	15.0551313131313	-0.16750405405405402

Then the interesting bit, the sample data is fetched with

INFO:base.data_set:.sql: retrieve_sample_data: SELECT Strain.Name, ProbeSetData.value, ProbeSetSE.error, ProbeSetData.Id, Strain.Name2 FROM (ProbeSetData, ProbeSetFreeze, Strain, ProbeSet, ProbeSetXRef) left join ProbeSetSE on (ProbeSetSE.DataId = ProbeSetData.Id AND ProbeSetSE.StrainId = ProbeSetData.StrainId) WHERE ProbeSet.Name = ‘1443823_s_at’ AND ProbeSetXRef.ProbeSetId = ProbeSet.Id AND ProbeSetXRef.ProbeSetFreezeId = ProbeSetFreeze.Id AND ProbeSetFreeze.Name = ‘HC_M2_0606_P’ AND ProbeSetXRef.DataId = ProbeSetData.Id AND ProbeSetData.StrainId = Strain.Id Order BY Strain.Name

| BXD12 | 15.192 | NULL | 23422417 | BXD12 | etc.

Name	value	error	Id	Name2
129S1/SvImJ	14.552	NULL	23422417	129S1/SvImJ
A/J	14.34	NULL	23422417	A/J
AKR/J	14.338	NULL	23422417	AKR/J
B6D2F1	15.251	NULL	23422417	B6D2F1
BALB/cByJ	14.164	NULL	23422417	BALB/cByJ
BALB/cJ	14.563	NULL	23422417	BALB/cJ
BXD1	15.198	NULL	23422417	BXD1
BXD11	15.084	NULL	23422417	BXD11

Then some repeated queries and this fetchest the nearest SNP

INFO:wqflask.show_trait.show_trait:.sql: get_nearest_marker: SELECT Geno.Name FROM Geno, GenoXRef, GenoFreeze WHERE Geno.Chr = ‘1’ AND GenoXRef.GenoId = Geno.Id AND GenoFreeze.Id = GenoXRef.GenoFreezeId AND GenoFreeze.Name = ‘BXDGeno’ ORDER BY ABS( Geno.Mb - 173.149434) limit 1;

Name

NES13033186

Digging deeper

To get at phenotype data ProbeSetData is the main table (almost all important molecular assay data is in this table including probe set data, RNA-seq data, proteomic data, and metabolomic data. 2.5 billion rows March 2016)

select count(*) from ProbeSetData limit 5; ~~—————~~

count(*)

~~—————~~

2,510,566,472

~~—————~~

select * from ProbeSetData limit 5; —-———-+——-+

Id

StrainId

value

—-———-+——-+

1	1	5.742
1	2	5.006
1	3	6.079
1	4	6.414
1	5	4.885

—-———-+——-+

This table is used in

wqflask/base/do_search.py
wqflask/base/data_set.py
wqflask/utility/AJAX_table.py
wqflask/wqflask/correlation/show_corr_results.py

In there we find ‘ProbeSetData.Id = ProbeSetXRef.dataId’.

select * from ProbeSetXRef limit 5; ~~——————~~————+——–+————+——————–+————+——————-+———————+—————–+——————–+——–+———————-+——+

ProbeSetFreezeId

ProbeSetId

DataId

Locus_old

LRS_old

pValue_old

mean

se

Locus

LRS

pValue

additive

h2

~~——————~~————+——–+————+——————–+————+——————-+———————+—————–+——————–+——–+———————-+——+

1	1	1	10.095.400	13.3971627898894	0.163	5.48794285714286	0.08525787814808819	rs13480619	12.590069931048001	0.269	-0.28515625	NULL
1	2	2	D15Mit189	10.042057464356201	0.431	9.90165714285714	0.0374686634976217	CEL-17_50896182	10.5970737900941	0.304	-0.11678333333333299	NULL
1	3	3	D5Mit139	5.43678531742749	0.993	7.83948571428571	0.0457583416912569	rs13478499	6.0970532702754	0.988	0.112957489878542	NULL
1	4	4	D1Mit511	9.87815279480766	0.483	8.315628571428569	0.0470396593931327	rs6154379	11.774867551173099	0.286	-0.157113725490196	NULL
1	5	5	D16H21S16	10.191723834264499	0.528	9.19345714285714	0.0354801718293322	rs4199265	10.923263374016202	0.468	0.11476470588235299	NULL

~~——————~~————+——–+————+——————–+————+——————-+———————+—————–+——————–+——–+———————-+——+

i.e., for Strain Id 1 (DataId) 1, the locus ‘10.095.400’ has a phenotype value of 5.742.

Interestingly ProbeData and PublishData have the same layout as ProbeSetData. ProbeData is only in use for Affy assays - and not used for computations. PublishData contains trait values. ProbeSetData.id matches ProbeSetXRef.DataId while PublishData.id matches PublishXRef.DataId.

select * from PublishXRef limit 3; ~~——-~~————-+————-+—————+———+—————-+——————+———–+———-+——————————————————-+

Id

InbredSetId

PhenotypeId

PublicationId

DataId

Locus

LRS

additive

Sequence

comments

~~——-~~————-+————-+—————+———+—————-+——————+———–+———-+——————————————————-+

10001	8	1	1	8966353	D2Mit5	10.18351644706	-1.20875	1
10001	7	2	53	8966813	D7Mit25UT	9.85534330983917	-2.86875	1
10001	4	3	81	8966947	CEL-6_57082524	11.7119505898121	-23.28875	1	elissa modified Abstract at Tue Jun 7 11:38:00 2005

~~——-~~————-+————-+—————+———+—————-+——————+———–+———-+——————————————————-+ 3 rows in set (0.00 sec)

ties the trait data (PublishData) with the inbredsetid (matching PublishFreeze.InbredSetId), locus and publication.

select * from PublishFreeze -> ; —-————+————————–+————-+————+——–+————-+—————–+—————–+

Id

Name

FullName

ShortName

CreateTime

public

InbredSetId

confidentiality

AuthorisedUsers

—-————+————————–+————-+————+——–+————-+—————–+—————–+

1	BXDPublish	BXD Published Phenotypes	BXDPublish	2004-07-17	2	1	0	NULL
18	HLCPublish	HLC Published Phenotypes	HLC Publish	2012-02-20	2	34	0	NULL

—-————+————————–+————-+————+——–+————-+—————–+—————–+ 2 rows in set (0.02 sec)

which gives us the datasets.

GeneNetwork1 has a limited REST interface, if you do

curl "http://robot.genenetwork.org/webqtl/main.py?cmd=get&probeset=1443823_s_at&db=HC_M2_0606_P"

we get

ProbeSetID      B6D2F1  C57BL/6J        DBA/2J  BXD1    BXD2    BXD5    BXD6   BXD8     BXD9    BXD11   BXD12   BXD13   BXD15   BXD16   BXD19   BXD20   BXD21  BXD22    BXD23   BXD24   BXD27   BXD28   BXD29   BXD31   BXD32   BXD33   BXD34  BXD38    BXD39   BXD40   BXD42   BXD67   BXD68   BXD43   BXD44   BXD45   BXD48  BXD50    BXD51   BXD55   BXD60   BXD61   BXD62   BXD63   BXD64   BXD65   BXD66  BXD69    BXD70   BXD73   BXD74   BXD75   BXD76   BXD77   BXD79   BXD73a  BXD83  BXD84    BXD85   BXD86   BXD87   BXD89   BXD90   BXD65b  BXD93   BXD94   A/J    AKR/J    C3H/HeJ C57BL/6ByJ      CXB1    CXB2    CXB3    CXB4    CXB5    CXB6   CXB7     CXB8    CXB9    CXB10   CXB11   CXB12   CXB13   BXD48a  129S1/SvImJ    BALB/cJ  BALB/cByJ       LG/J    NOD/ShiLtJ      PWD/PhJ BXD65a  BXD98   BXD99  CAST/EiJ KK/HlJ  WSB/EiJ NZO/HlLtJ       PWK/PhJ D2B6F1
1443823_s_at    15.251  15.626  14.716  15.198  14.918  15.057  15.232  14.968 14.87    15.084  15.192  14.924  15.343  15.226  15.364  15.36   14.792  14.908 15.344   14.948  15.08   15.021  15.176  15.14   14.796  15.443  14.636  14.921 15.22    15.62   14.816  15.39   15.428  14.982  15.05   15.13   14.722  14.636 15.242   15.527  14.825  14.416  15.125  15.362  15.226  15.176  15.328  14.895 15.141   15.634  14.922  14.764  15.122  15.448  15.398  15.089  14.765  15.234 15.302   14.774  14.979  15.212  15.29   15.012  15.041  15.448  14.34   14.338 14.809   15.046  14.816  15.232  14.933  15.255  15.21   14.766  14.8    15.506 15.749   15.274  15.599  15.673  14.651  14.692  14.552  14.563  14.164  14.546 15.044   14.695  15.162  14.772  14.645  15.493  14.75   14.786  15.003  15.148 15.221

(see https://github.com/genenetwork/gn_server/blob/master/doc/API.md for the latest REST API).

getTraitData is defined in the file web/webqtl/textUI/cmdClass.py. probe is None, so the code at line 199 is run

query = “SELECT Strain.Name, %sData.value from %sData, Strain, %s, %sXRef WHERE %s.Name = ‘%s’ and %sXRef.%sId = %s.Id and %sXRef.%sFreezeId = %d and %sXRef.DataId = %sData.Id and %sData.StrainId = Strain.Id order by Strain.Id” % (prefix, prefix, prefix, prefix, prefix, probeset,prefix, prefix, prefix, prefix, prefix, dbId, prefix, prefix, prefix)

where prefix is ProbeSet (one presumes). So, let’s see if we can do this by hand

SELECT Strain.Name, ProbeSetData.value from ProbeSetData, Strain, ProbeSet, ProbeSetXRef WHERE ProbeSet.Name = ‘1443823_s_at’ and ProbeSetXRef.ProbeSetId = ProbeSet.Id and ProbeSetXRef.ProbeSetFreezeId = $dbid and ProbeSetXRef.DataId = ProbeSetData.Id and ProbeSetData.StrainId = Strain.Id ORDER BY Strain.Id

The $dbid is listed in the ProbeSetFreeze table,

SELECT ProbeFreezeId,Name FROM ProbeSetFreeze WHERE Name=’HC_M2_0606_P’ limit 5;

~~—————~~————–+

ProbeFreezeId

Name

~~—————~~————–+

30	HC_M2_0606_P

~~—————~~————–+

select id,name from ProbeSet WHERE Name = ‘1443823_s_at’ limit 5; ~~——–~~————–+

id

name

~~——–~~————–+

106556

1443823_s_at

~~——–~~————–+

So

query = “SELECT Strain.Name, %sData.value from %sData, Strain, %s, %sXRef WHERE %s.Name = ‘%s’ and %sXRef.%sId = %s.Id and %sXRef.%sFreezeId = %d and %sXRef.DataId = %sData.Id and %sData.StrainId = Strain.Id order by Strain.Id” % (prefix, prefix, prefix, prefix, prefix, probeset,prefix, prefix, prefix, prefix, prefix, dbId, prefix, prefix, prefix)

CORRECT NAME:

SELECT Strain.Name, ProbeSetData.value from ProbeSetData, Strain, ProbeSet, ProbeSetXRef WHERE ProbeSet.Name = ‘at_probe’ and ProbeSetXRef.ProbeSetId = ProbeSet.Id and ProbeSetXRef.ProbeSetFreezeId = dbid and ProbeSetXRef.DataId = ProbeSetData.Id and ProbeSetData.StrainId = Strain.Id order by Strain.Id

select * from ProbeSetXRef WHERE probesetfreezeid=30 limit 5; ~~——————~~————+——–+————+——————–+————+——————–+——————–+—————–+——————–+——–+——————-+——+

ProbeSetFreezeId

ProbeSetId

DataId

Locus_old

LRS_old

pValue_old

mean

se

Locus

LRS

pValue

additive

h2

~~——————~~————+——–+————+——————–+————+——————–+——————–+—————–+——————–+——–+——————-+——+

30	1	445962	01.059.350	7.1753152078069595	0.961	30.0646	1.79763935596594	rs13475891	7.1753152078069204	0.973	4.71778125	NULL
30	2	445963	D4Mit156	7.58944292943285	0.724	232.38328571428602	9.00278909374791	CEL-4_118751423	7.57513435426218	0.793	25.2660951417004	NULL
30	3	445964	D1Mit134	9.766065497826819	0.737	47.206	1.58413526287766	mCV23431007	9.76606549782677	0.797	-4.82405952380952	NULL
30	4	445965	D1Mit155	18.0045829157241	0.033	132.29248571428602	4.37799472291842	rs3689947	17.9365068406286	0.049	-16.945619047619	NULL
30	5	445966	D5Mit197	9.51068902627823	0.476	271.309971428571	7.4294268316065395	rs6239372	10.4214974316601	0.41	-25.6148045454546	NULL

~~——————~~————+——–+————+——————–+————+——————–+——————–+—————–+——————–+——–+——————-+——+

So, apparently ProbeSetFreezeID points to the database identifier in ProbeSetFreeze which has the name of the ‘DB’. OK, that kinda makes sense now. Meanwhile Probeset.name points to the phenotype name.

ProbeSetXRef binds these tables together. Finally there is the data in

select * from ProbeSetData limit 5; —-———-+——-+

Id

StrainId

value

—-———-+——-+

1	1	5.742
1	2	5.006
1	3	6.079
1	4	6.414
1	5	4.885

—-———-+——-+ 5 rows in set (0.00 sec)

linked by ProbeSetXRef.dataid.

For PublishData:

List datasets for BXD (InbredSetId=1):

select * from PublishXRef where InbredSetId=1 limit 3; ~~——-~~————-+————-+—————+———+———–+——————+——————+———-+——————————————————————————–+

Id

InbredSetId

PhenotypeId

PublicationId

DataId

Locus

LRS

additive

Sequence

comments

~~——-~~————-+————-+—————+———+———–+——————+——————+———-+——————————————————————————–+

10001

1

4

116

8967043

rs8253516

13.4974914158039

2.39444444444444

1

robwilliams modified post_publication_description at Mon Jul 30 14:58:10 2012

10002

1

10

116

8967044

rs3666069

22.0042692151629

2.08178571428572

1

robwilliams modified phenotype at Thu Oct 28 21:43:28 2010

10003

1

15

116

8967045

D18Mit4

15.5929163293343

19.0882352941176

1

robwilliams modified phenotype at Mon May 23 20:52:19 2011

~~——-~~————-+————-+—————+———+———–+——————+——————+———-+——————————————————————————–+

where ID is the ‘record’ or, effectively, dataset.

select distinct(publicationid) from PublishXRef where InbredSetId=1 limit 3; ~~—————~~

publicationid

~~—————~~

116

117

118

~~—————~~

select distinct PublishXRef.id,publicationid,phenotypeid,Phenotype.post_publication_description from PublishXRef,Phenotype where InbredSetId=1 and phenotypeid=Phenotype.id limit 3; ~~——-~~—————+————-+—————————————————————————————————————————-+

id

publicationid

phenotypeid

post_publication_description

~~——-~~—————+————-+—————————————————————————————————————————-+

10001	116	4	Central nervous system, morphology: Cerebellum weight [mg]
10002	116	10	Central nervous system, morphology: Cerebellum weight after adjustment for covariance with brain size [mg]
10003	116	15	Central nervous system, morphology: Brain weight, male and female adult average, unadjusted for body weight, age, sex [mg]

~~——-~~—————+————-+—————————————————————————————————————————-+

The id field is the same that is used in the GN2 web interface and the PublicationID ties the datasets together.

To list trait values:

SELECT Strain.Name, PublishData.id, PublishData.value from (Strain,PublishData, PublishXRef) Where PublishData.StrainId = Strain.id limit 3;

—————+——-+

Name

id

value

—————+——-+

CXB1	8966353	29.6
CXB1	8966353	29.6
CXB1	8966353	29.6

—————+——-+

here id should match dataid again:

SELECT Strain.Name, PublishData.id, PublishData.value from (Strain,PublishData, PublishXRef) Where PublishData.StrainId = Strain.id and PublishXRef.dataid=8967043 and PublishXRef.dataid=PublishData.id limit 3; —————+——-+

Name

id

value

—————+——-+

BXD1	8967043	61.4
BXD2	8967043	49
BXD5	8967043	62.5

—————+——-+

Datasets

The REST API aims to present a unified interface for genotype and phenotype data. Phenotype datasets appear in two major forms in the database and we want to present them as one resource.

Dataset names are defined in ProbeSetFreeze.name and Published.id -> publication (we’ll ignore the probe dataset that uses ProbeFreeze.name). These tables should be meshed. It looks like the ids are non-overlapping with the publish record IDs starting at 10,001 (someone has been smart, though it sets the limit of probesets now to 10,000).

The datasets are organized differently in these tables. All published BXD data is grouped on BXDpublished with the publications as ‘datasets’. So, that is how we list them in the REST API.

To fetch all the datasets we first list ProbeSetFreeze entries. Then we list the Published entries.

Fetch genotype information

SNPs

The SNP count info for the BXD is calculated like this

while startMb<endMb:
    snp_count = g.db.execute("""
            select
                    count(*) from BXDSnpPosition
            where
                    Chr = '%s' AND Mb >= %2.6f AND Mb < %2.6f AND
                    StrainId1 = %d AND StrainId2 = %d
            """ % (chrName, startMb, startMb+stepMb, strainId1, strainId2)).fetchone()[0]
    SNPCounts.append(snp_count)
    startMb += stepMb

select * from BXDSnpPosition limit 5; —————–+———–+———-+

Chr

StrainId1

StrainId2

Mb

—————–+———–+———-+

1	2	3	0.002477
1	2	3	0.002592
1	2	3	0.00283
1	2	3	0.002994
1	2	3	0.003299

—————–+———–+———-+

Other SNP tables containing

select * from SnpSource limit 5; Empty set (0.00 sec)

select * from SnpAll limit 5; Empty set (0.00 sec)

mysql> select * from SnpAll limit 5; Empty set (0.00 sec)

mysql> select * from SnpPattern limit 5; Empty set (0.00 sec)

mysql> select * from SnpSource limit 5; Empty set (0.00 sec)

Hmmm. This is the test database. Then there are the plink files and VCF files.

Files

database.org

Latest commit

History

database.org

File metadata and controls

Database Information

The small test database (2GB)

GeneNetwork database

Estimated table sizes

User access

AvgMethod

BXDSnPosition

CaseAttribute(XRef)

CeleralINFO_mm6

Chr_Length (/cross/BXD.json)

Dataset_mbat

DatasetMapInvestigator

DataSets

DatasetStatus

DBList and DBType

Docs

Ensembl*

Genbank

GeneCategory

GeneChip

GeneIDXRef

GeneList

Genlist_rn3(3)

GeneMap_cuiyan

GeneRIF

Geno (genotype/marker/’marker’.json)

GenoCode

GenoData

GenoFreeze

GenoSE

GenoXREF

GORef

H2

Homologene

InbredSet (/cross/BXD.info)

Indelall, SnpAll, SnpPattern, SnpSource

Info*

LCorrRamin3

Login

MachineAccessLog

MappingMethod

News

NStrain

Phenotype

ProbeData

ProbeSet

ProbeSetXRef (phenotypes/dataset_name.json)

ProbeSetData

Improvements?

Comments

ProbeSetFreeze and ProbeFreeze (/dataset/name.json)

ProbeSetSE

Publication

Publishdata (all pheno)

QuickSearch

role

Sample*

Species & Strain (should be sample)

InbredSet

TableComments

Temp*

Tissue

TissueP*

User collection

UserPrivilege

Vlookup

Fetching Data

Search

Search Mouse mRNA from HC_M2_0606_P dataset

Fetch phenotype information

Through the trait page

Digging deeper

For PublishData:

Datasets