diff options
Diffstat (limited to 'doc')
| -rw-r--r-- | doc/Architecture.org | 135 | ||||
| -rw-r--r-- | doc/README.org | 46 | ||||
| -rw-r--r-- | doc/database.org | 207 |
3 files changed, 354 insertions, 34 deletions
diff --git a/doc/Architecture.org b/doc/Architecture.org index ed19889c..04e05e40 100644 --- a/doc/Architecture.org +++ b/doc/Architecture.org @@ -1,19 +1,20 @@ -* GeneNetwork Architecture - #+TITLE: Installing GeneNetwork services * Table of Contents :TOC: - - [[#genenetwork-architecture][GeneNetwork Architecture]] - - [[#introduction][Introduction]] - - [[#webserver][Webserver]] - - [[#gnserver-rest][GnServer (REST)]] + - [[#introduction][Introduction]] + - [[#webserver][Webserver]] + - [[#gnserver-rest][GnServer (REST)]] + - [[#gnexec][GnExec]] + - [[#database][Database]] + - [[#phenotypes][Phenotypes]] + - [[#genotypes][Genotypes]] -** Introduction +* Introduction This document describes the architecture of GN2. Because GN2 is evolving, only a high-level overview is given here. -** Webserver +* Webserver The main [[https://github.com/genenetwork/genenetwork2][GN2 webserver]] is built on [[http://flask.pocoo.org/][Python flask]] and this GN2 source code can be found on [[https://github.com/genenetwork/genenetwork2/tree/master/wqflask/wqflask][github]] in the wqflask directory. The routing @@ -44,7 +45,7 @@ Consortium M430v2 (Jun06) PDNN to find all records with MEAN between 15 and 16 and with LRS between 23 and 46.'. Then the results are added to a table which is displayed using a JS [[https://datatables.net/][DataTable container]]. -** GnServer (REST) +* GnServer (REST) The [[https://github.com/genenetwork/gn_server][GnServer REST API]] is built on high performance [[http://elixir-lang.org/][Elixir]] with [[https://github.com/falood/maru][Maru]]. Mainly the GnServer serves JSON requests, for example to fetch data @@ -52,4 +53,118 @@ from the database. To get the menu data in YAML you can do something like : curl localhost:8880/int/menu/main.json|ruby extra/json2yaml.rb -(json2yaml.rb is in the gn_server repo). +(json2yaml.rb is in the gn_server repo). For the current API definition +see [[https://github.com/genenetwork/gn_server/doc/API.md][GnServer REST API]] documentation. + +* GnExec + +GnExec, also written in Elixir, executes commands using a separate +daemon. + +* Database +** Phenotypes + +Phenotypes are stored in the SQL database. For what happens at the +database level see [[database.org]]. A test database can be downloaded - +see the installation [[./README.org][instructions]]. + +** Genotypes + +Genotypes are stored in genotype files. These are part of the GNU Guix +distribution, see the installation [[./README.org][instructions]]. Genotype files are +currently in GN1 format, and will be aligned with the [[http://kbroman.org/qtl2/pages/sampledata.html][R/qtl2 formats]]. + +GN1-style (still default GN2) for the stored file BXD.geno: + +#+begin_src js +@name:BXD +@type:riset +@mat:B +@pat:D +@het:H +@unk:U +Chr Locus cM Mb BXD1 BXD2 BXD5 BXD6 BXD8 BXD9 BXD11 BXD12 BXD13 BXD14 BX +D15 BXD16 BXD18 BXD19 BXD20 BXD21 BXD22 BXD23 BXD24a BXD24 BXD25 BXD27 BXD28 BX +D29 BXD30 BXD31 BXD32 BXD33 BXD34 BXD35 BXD36 BXD37 BXD38 BXD39 BXD40 BXD41 BXD4 +2 BXD43 BXD44 BXD45 BXD48 BXD49 BXD50 BXD51 BXD52 BXD53 BXD54 BXD55 BXD56 BXD59 +BXD60 BXD61 BXD62 BXD63 BXD64 BXD65 BXD66 BXD67 BXD68 BXD69 BXD70 BXD71 BXD72 BX +D73 BXD74 BXD75 BXD76 BXD77 BXD78 BXD79 BXD80 BXD81 BXD83 BXD84 BXD85 BXD86 BXD8 +7 BXD88 BXD89 BXD90 BXD91 BXD92 BXD93 BXD94 BXD95 BXD96 BXD97 BXD98 BXD99 BXD100 + BXD101 BXD102 BXD103 +1 rs6269442 0.0 3.482275 B B D D D B B D B B D D B D D D D B B B D B D D B B B +B B B B B B D B D B B D B B H H B D B B H H B B D D D D D B B H B B B B D B D B +D D D D D H B D D B D B B D D B D D B B B B B B B D +1 rs6365999 0.0 4.811062 B B D D D B B D B B D D B D D D D B B B D B D D B B B +B B B B B B D B D B B D B B H H B D B B H H B B D D D D D B B H B B B B D B D B +D D D D D H B D D B D B B D D B D D B B B B B B U D +... +#+end_src + +and, for example, in the method run_rqtl_geno this file gets +loaded. For GnServer, however, we only want to deal with standardized +R/qtl formatted data, so with gn_extra we convert the original format +into R/qtl format with geno2rqtl with one adaptation: the geno table +is transposed so now becomes + +#+begin_src js +marker,BXD1,BXD2,BXD5,BXD6,BXD8,BXD9,BXD11,BXD12,BXD13,BXD14,BXD15,BXD16,BXD18,BXD19,BXD20,BXD21,BXD22,BXD23,BXD24a,BXD24,BXD25,BXD27,BXD28,BXD29,BXD30,BXD31,BXD32,BXD33,BXD34,BXD35,BXD36,BXD37,BXD38,BXD39,BXD40,BXD41,BXD42,BXD43,BXD44,BXD45,BXD48,BXD49,BXD50,BXD51,BXD52,BXD53,BXD54,BXD55,BXD56,BXD59,BXD60,BXD61,BXD62,BXD63,BXD64,BXD65,BXD66,BXD67,BXD68,BXD69,BXD70,BXD71,BXD72,BXD73,BXD74,BXD75,BXD76,BXD77,BXD78,BXD79,BXD80,BXD81,BXD83,BXD84,BXD85,BXD86,BXD87,BXD88,BXD89,BXD90,BXD91,BXD92,BXD93,BXD94,BXD95,BXD96,BXD97,BXD98,BXD99,BXD100,BXD101,BXD102,BXD103 +1,B,B,D,D,D,B,B,D,B,B,D,D,B,D,D,D,D,B,B,B,D,B,D,D,B,B,B,B,B,B,B,B,B,D,B,D,B,B,D,B,B,H,H,B,D,B,B,H,H,B,B,D,D,D,D,D,B,B,H,B,B,B,B,D,B,D,B,D,D,D,D,D,H,B,D,D,B,D,B,B,D,D,B,D,D,B,B,B,B,B,B,B,D +2,B,B,D,D,D,B,B,D,B,B,D,D,B,D,D,D,D,B,B,B,D,B,D,D,B,B,B,B,B,B,B,B,B,D,B,D,B,B,D,B,B,H,H,B,D,B,B,H,H,B,B,D,D,D,D,D,B,B,H,B,B,B,B,D,B,D,B,D,D,D,D,D,H,B,D,D,B,D,B,B,D,D,B,D,D,B,B,B,B,B,B,U,D +3,B,B,D,D,D,B,B,D,B,B,D,D,B,D,D,D,D,B,B,B,D,B,D,D,B,B,B,B,B,B,B,B,B,D,B,D,B,D,D,B,B,H,H,B,B,B,B,H,H,B,B,D,D,D,D,B,B,B,H,B,B,B,B,D,B,D,B,D,D,D,D,D,H,B,D,D,B,D,B,B,D,D,B,D,D,B,B,B,B,B,B,U,D +... +#+end_src js + +i.e. individuals are columns and markers are rows. Alternatively it could look like + +#+begin_src js +marker,BXD1,BXD2,BXD5,BXD6,BXD8,BXD9,BXD11,BXD12,BXD13,BXD14,BXD15,BXD16,BXD18,BXD19,BXD20,BXD21,BXD22,BXD23,BXD24a,BXD24,BXD25,BXD27,BXD28,BXD29,BXD30,BXD31,BXD32,BXD33,BXD34,BXD35,BXD36,BXD37,BXD38,BXD39,BXD40,BXD41,BXD42,BXD43,BXD44,BXD45,BXD48,BXD49,BXD50,BXD51,BXD52,BXD53,BXD54,BXD55,BXD56,BXD59,BXD60,BXD61,BXD62,BXD63,BXD64,BXD65,BXD66,BXD67,BXD68,BXD69,BXD70,BXD71,BXD72,BXD73,BXD74,BXD75,BXD76,BXD77,BXD78,BXD79,BXD80,BXD81,BXD83,BXD84,BXD85,BXD86,BXD87,BXD88,BXD89,BXD90,BXD91,BXD92,BXD93,BXD94,BXD95,BXD96,BXD97,BXD98,BXD99,BXD100,BXD101,BXD102,BXD103 +rs6269442,B,B,D,D,D,B,B,D,B,B,D,D,B,D,D,D,D,B,B,B,D,B,D,D,B,B,B,B,B,B,B,B,B,D,B,D,B,B,D,B,B,H,H,B,D,B,B,H,H,B,B,D,D,D,D,D,B,B,H,B,B,B,B,D,B,D,B,D,D,D,D,D,H,B,D,D,B,D,B,B,D,D,B,D,D,B,B,B,B,B,B,B,D +rs6365999,B,B,D,D,D,B,B,D,B,B,D,D,B,D,D,D,D,B,B,B,D,B,D,D,B,B,B,B,B,B,B,B,B,D,B,D,B,B,D,B,B,H,H,B,D,B,B,H,H,B,B,D,D,D,D,D,B,B,H,B,B,B,B,D,B,D,B,D,D,D,D,D,H,B,D,D,B,D,B,B,D,D,B,D,D,B,B,B,B,B,B,U,D +rs6376963,B,B,D,D,D,B,B,D,B,B,D,D,B,D,D,D,D,B,B,B,D,B,D,D,B,B,B,B,B,B,B,B,B,D,B,D,B,D,D,B,B,H,H,B,B,B,B,H,H,B,B,D,D,D,D,B,B,B,H,B,B,B,B,D,B,D,B,D,D,D,D,D,H,B,D,D,B,D,B,B,D,D,B,D,D,B,B,B,B,B,B,U,D +#+end_src js + +This is also the format provided by R/qtl in +https://github.com/rqtl/qtl2data/tree/master/DO_Recla which we will +use as the base line for the REST server. In the meta json file the +genotype data is tagged as transposed: + +#+begin_src js +{ +"description": "DO data from Recla et al. (2014) Mamm Genome 25:211-222", +"crosstype": "do", +"geno": "recla_geno.csv", +"geno_transposed": true, +"founder_geno": "recla_foundergeno.csv", +"founder_geno_transposed": true, +"genotypes": { + "1": "1", + "2": "2", + "3": "3" +}, +"pheno": "recla_pheno.csv", +"pheno_transposed": false, +"covar": "recla_covar.csv", +"sex": { + "covar": "Sex", + "female": "female", + "male": "male" +}, +"x_chr": "X", +"cross_info": { + "covar": "ngen" +}, +"gmap": "recla_gmap.csv", +"pmap": "recla_pmap.csv", +"alleles": ["A", "B", "C", "D", "E", "F", "G", "H"] +} +#+end_src + +Meanwhile the gmap file looks like + +#+begin_src js +marker,chr,pos,Mb +rs6269442,1,0.0,3.482275 +rs6365999,1,0.0,4.811062 +rs6376963,1,0.895,5.008089 +rs3677817,1,1.185,5.176058 +#+end_src diff --git a/doc/README.org b/doc/README.org index b3c78f29..aa05654f 100644 --- a/doc/README.org +++ b/doc/README.org @@ -117,7 +117,7 @@ cd guix-gn-latest ** Step 3: Authorize the GN Guix server GN2 has its own GNU Guix binary distribution server. To trust it you have -to add the following key +to add the following key #+begin_src scheme (public-key @@ -136,9 +136,9 @@ guix archive --authorize and hit Ctrl-D. -Now you can use the substitute server to install GN2 binaries. +Now you can use the substitute server to install GN2 binaries. -** Step 4: Install and run GN2 +** Step 4: Install and run GN2 Since this is a quick and dirty install we are going to override the GNU Guix package path by pointing the package path to our repository: @@ -208,7 +208,7 @@ https://s3.amazonaws.com/genenetwork2/db_webqtl_s.zip Check the md5sum. After installation inflate the database binary in the MySQL directory -(this installation path is subject to change soon) +(this installation path is subject to change soon) : chown -R mysql:mysql db_webqtl_s/ : chmod 700 db_webqtl_s/ @@ -271,10 +271,10 @@ R_LIBS_SITE are set) from the information given by guix: Inside the repository: : cd genenetwork2 -: ./bin/genenetwork2 +: ./bin/genenetwork2 -Will fire up your local repo http://localhost:5003/ using the -settings in ./etc/default_settings.py. These settings may +Will fire up your local repo http://localhost:5003/ using the +settings in ./etc/default_settings.py. These settings may not reflect your system. To override settings create your own from a copy of default_settings.py and pass it into GN2 with @@ -348,7 +348,7 @@ Make dirs Add users -: adduser nobody ; addgroup nobody +: adduser nobody ; addgroup nobody Run nginx @@ -392,6 +392,12 @@ Make a note of the paths with ./pre-inst-env guix package --search-paths #+end_src bash +or this should also work if guix is installed + +#+begin_src bash +guix package --search-paths +#+end_src bash + After setting the paths for the server #+begin_src bash @@ -413,7 +419,7 @@ genenetwork2 will start the default server which listens on port 5003, i.e., http://localhost:5003/. -OK, we are where we were before with step 4. Only difference is that we +OK, we are where we were before with step 4. Only difference is that we used our own compiled guix server. * Trouble shooting @@ -433,7 +439,7 @@ On one system: : export R_LIBS_SITE="$HOME/.guix-profile/site-library/" : export GEM_PATH="$HOME/.guix-profile/lib/ruby/gems/2.2.0" -and perhaps a few more. +and perhaps a few more. ** ERROR: can not find directory $HOME/gn2_data The default settings file looks in your $HOME/gn2_data. Since these @@ -466,7 +472,7 @@ and a download of the test database. <user01> set to the ones in ~/.guix-profile/ <pjotrp> good, and you are in gn-latest-guix repo [07:06] <user01> yep [07:07] -<pjotrp> git log shows +<pjotrp> git log shows Author: David Thompson <dthompson2@worcester.edu> Date: Sun Mar 27 21:20:19 2016 -0400 @@ -488,7 +494,7 @@ genenetwork2-files-small 1.0 out ../guix-bioinformatics/gn/packages/g <user01> hah, I don't have screen installed yet [07:11] <pjotrp> comes with guix ;) [07:12] <pjotrp> no worries, you can run it any way you want -<pjotrp> $HOME/.guix-profile/bin/guix-daemon --build-users-group=guixbuild +<pjotrp> $HOME/.guix-profile/bin/guix-daemon --build-users-group=guixbuild <user01> then something's weird, because it says I don't have it <pjotrp> oh, you need to install it first [07:13] <pjotrp> guix package -A screen @@ -546,11 +552,11 @@ The following derivations would be built: <pjotrp> https://github.com/pjotrp/guix-notes/blob/master/REPRODUCIBLE.org <pjotrp> this is exactly what we are doing now <user01> alrighty [07:35] -<pjotrp> To see if a remote server has a guix server running it should respond +<pjotrp> To see if a remote server has a guix server running it should respond [07:36] <pjotrp> lynx http://guix.genenetwork.org:8080 --dump <pjotrp> Resource not found: / -<pjotrp> +<pjotrp> <pjotrp> you see that? <user01> yes [07:37] <pjotrp> good. The main hydra server is too slow. So on my laptop I forced @@ -558,7 +564,7 @@ The following derivations would be built: <pjotrp> env GUIX_PACKAGE_PATH=../guix-bioinformatics/ ./pre-inst-env guix package -i genenetwork2 --dry-run --substitute-urls="http://mirror.hydra.gnu.org" -<pjotrp> +<pjotrp> <pjotrp> the list looks the same to me [07:40] <user01> me too <pjotrp> note that some packages will be built and some downloaded, right? @@ -688,7 +694,7 @@ The following derivations would be built: <pjotrp> everything should be pre-built from guix.genenetwork.org <pjotrp> you are downloading? <user02> yes [09:15] -<pjotrp> cool. Maybe an idea to set up a server +<pjotrp> cool. Maybe an idea to set up a server <pjotrp> for your own use <user02> Stuck at downloading preprocesscore <pjotrp> should not [09:24] @@ -735,7 +741,7 @@ The following derivations would be built: <pjotrp> should be at /gnu/store/y1f3r2xs3fhyadd46nd2aqbr2p9qv2ra-r-biocpreprocesscore-1.32.0 [09:33] -<pjotrp> +<pjotrp> <user03> pjotrp: Possibly we should use the archive utility of Guix to do deployment to avoid such out-of-sync differences :) [09:34] <pjotrp> maybe. I did not get archive to update profiles properly [09:37] @@ -802,7 +808,7 @@ The following derivations would be built: <pjotrp> but do not checkout that genetwork2_diet <pjotrp> we reverted to the main tree <pjotrp> clone git@github.com:genenetwork/genenetwork2.git [09:53] -<pjotrp> instead and checkout the staging branch +<pjotrp> instead and checkout the staging branch <pjotrp> that is effectively my branch [09:54] <pjotrp> when that is done you should be able to fire up the webserver from there [09:55] @@ -825,7 +831,7 @@ The following derivations would be built: <user01> yep <pjotrp> that can also run on remote files over ssh <pjotrp> that's an alternative -<pjotrp> kudos for using emacs :), wdyt user03 +<pjotrp> kudos for using emacs :), wdyt user03 <user02> 79 minutes to go downloading the db <pjotrp> user02: sorry about that [09:59] <pjotrp> it is 2GB @@ -850,7 +856,7 @@ The following derivations would be built: --substitute-urls="http://guix.genenetwork.org:8080" [10:08] <pjotrp> elixir 1.2.3 out ../guix-bioinformatics/gn/packages/elixir.scm:31:2 -<pjotrp> +<pjotrp> <pjotrp> I am building it on guix.genenetwork.org right now [10:09] <user01> nice [10:10] #+end_src diff --git a/doc/database.org b/doc/database.org index d4c04848..624174a4 100644 --- a/doc/database.org +++ b/doc/database.org @@ -207,10 +207,18 @@ Metadata ? -** Chr_Length +** 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) @@ -275,10 +283,19 @@ Wiki info (nightly updated from NCBI) XRef should be foreign keys -** Geno +** 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 | 1 | 01.001.695 | 01.001.695 | 1 | 4.678288 | GCCCTGCCCACCTCAGAGCAAGCTGCCACCCAGGAGTCCGTGTTTCAGGAGATGTGTGAGGAGGGCCTGCTGGAGGAGTGTGATGGTGAGGATGAGGCAGGCCGTGCCGCG[T/C]AGCCAGAGGCTGGTGATGGGACCACCGAGATCTCACCCACTGGTGCTGCTGATCCTGAGAAGAGGATGGAGAAGAAGACGGAGCAGCAGCACACCGGCGGCGGGAGAAAGCTGCTCGTAAGCTGCTCGTAAGCTACGGGTGCAGCAGGCTGCACTTAGGGCAGCCCGGCTTCAGCACCAAGAACTCTTCAGGCTGCATGGGATCAAGGCCCAGGTGGCCCGAAGGCTGGCAGAACTCGCACACGGGAGGGAGCAGCAGCGCATACAGCGACTGGCAGAGGCTGACAAGCCCCGAAGGCTGGGACGACTCAAGTACCAGGCTCCTGACATTGATGTGCAGCTCAGCTCTGAGCTGTCTGGCCCACTCAGGACACTGAAACCAGAAGGTCACATTCTCCAAGACAGGTTCAAGAGCTTCCAGAAGAGAAATATGATTGAGCCCCGAGAACGAGCCAAGTTCAAGCGCAAATAAAAAATGAAGTTGGTGGAGAAGCGGGCCTACCATGAGATTCAGTTGTAGCTGTGCAGATGTCGGAGCCCCGCCCCTCAATAAAGTTCTGTGACAAAAAAAAAAAAAAAAAAAGAAGAAGAAGAAGAAAAGGAAAAAAAAGAAGAAAAAGAAAAAAAAAGAAAAAAGAAAAAGAAAACACATCACTTGGCAAAACTCCATAGACTCTATGTGATTCATGTTTCAAACATGCACCTA | GNF_SNP | 1 | GNF | NULL | NULL | 4.678288 | 1 | + ** GenoCode Belongs to someone else @@ -311,7 +328,7 @@ Heritability for probeset(?) Homology, not used much -** InbredSet +** InbredSet (/cross/BXD.info) Group in menu @@ -489,6 +506,35 @@ select count(*) from ProbeSet limit 5; +------+--------+----------+----------+--------+----------------------------------------------+------+-----------+----------+--------+-----------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+--------------+-------------+--------+----------+-------------------------+-----------------------+----------------------+-------------------------+-----------------------+------------------+----------------------+------+----------+---------------+--------------+------+---------+----------+---------+----------+--------------------------+---------------------+---------+-----------+-----------------------------+---------------------------+--------------+-------------+-----------+-------------+------------+---------+------------+----------+-----------+------------+------------+---------------+---------+-----------+---------+------------------+-------------+------+--------+-------------+----------------+-----------------+ 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 | NULL | NULL | 6.7460707070707 | NULL | rs6239372 | 10.9675593568894 | 0.567 | 0.0448545966228878 | NULL | +| 112 | 123527 | 23439388 | NULL | NULL | 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 @@ -691,6 +737,19 @@ show indexes from ProbeSetFreeze; | ProbeSetFreeze | 1 | NameIndex | 1 | Name2 | A | 2 | NULL | 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 and publishdata (all pheno) Phenotype pubs @@ -794,6 +853,18 @@ INFO:db.call:.sql: __init__: 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 @@ -874,7 +945,87 @@ INFO:base.data_set:.sql: get_trait_info: (that is a bug!). -** Fetch phenotypes +** 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 + +| 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 | +| BXD12 | 15.192 | NULL | 23422417 | BXD12 | + etc. + +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 @@ -1014,3 +1165,51 @@ select * from ProbeSetData limit 5; 5 rows in set (0.00 sec) linked by ProbeSetXRef.dataid. +** Fetch genotype information + +*** SNPs + +The SNP count info for the BXD is calculated like this + +#+begin_src python + 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 +#+end_src + +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. |