Merge branch 'testing' of https://github.com/genenetwork/genenetwork2

2 files changed, 182 insertions, 9 deletions

diff --git a/doc/README.org b/doc/README.org
index a39ef603..937a9549 100644
--- a/doc/README.org
+++ b/doc/README.org
@@ -104,11 +104,29 @@ As root configure and run
 :  mysqld --datadir=/var/mysql --initialize-insecure
 :  mkdir -p /var/run/mysqld
 :  chown mysql.mysql ~/mysql /var/run/mysqld
-:  su mysql -c mysqld --datadir=/var/mysql --explicit_defaults_for_timestamp -P 12048
+:  mysqld -u mysql --datadir=/var/mysql --explicit_defaults_for_timestamp -P 12048"
 
-/etc/my.cnf
-[mysqld]
-user=root
+If you want to run as root you may have to set
+
+: /etc/my.cnf
+: [mysqld]
+: user=root
+
+To check error output in a file on start-up run with something like
+
+: mysqld -u mysql --console  --explicit_defaults_for_timestamp  --datadir=/gnu/mysql --log-error=~/test.log
+
+Other tips are that Guix installs mysqld in your profile, so this may work
+
+: /home/user/.guix-profile/bin/mysqld -u mysql --explicit_defaults_for_timestamp  --datadir=/gnu/mysql
+
+When you get errors like:
+
+: qlalchemy.exc.IntegrityError: (_mysql_exceptions.IntegrityError) (1215, 'Cannot add foreign key constraint')
+
+you may need to set
+
+: set foreign_key_checks=0
 
 ** Load the small database in MySQL
 
diff --git a/doc/database.org b/doc/database.org
index 624174a4..5107b660 100644
--- a/doc/database.org
+++ b/doc/database.org
@@ -1,9 +1,19 @@
-- github Document reduction issue
+* 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
+[[https://github.com/genenetwork/gn_server/blob/master/doc/API.md][REST API]].
 
 * The small test database (2GB)
 
 The default install comes with a smaller database which includes a
-number of the BSD's and the Human liver dataset (GSE9588).
+number of the BXD's and the Human liver dataset (GSE9588).
 
 * GeneNetwork database
 
@@ -750,9 +760,30 @@ show indexes from ProbeSetFreeze;
 |      1 |        5 | 0.303492 |
 +--------+----------+----------+
 
-** Publication and publishdata (all pheno)
+** Publication
+
+Publication:
+
+| Id   | PubMed_ID | Abstract    | Title   | Pages   | Month | Year |
+
 
-Phenotype pubs
+** 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
 
@@ -1073,7 +1104,37 @@ select * from ProbeSetXRef limit 5;
 i.e., for Strain Id 1 (DataId) 1, the locus '10.095.400' has a
 phenotype value of 5.742.
 
-GeneNetwork1 already has a limited REST interface, if you do
+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"
 
@@ -1082,6 +1143,9 @@ 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 [[https://github.com/genenetwork/genenetwork/blob/master/web/webqtl/textUI/cmdClass.py#L134][web/webqtl/textUI/cmdClass.py]].
 probe is None, so the code at line 199 is run
 
@@ -1165,6 +1229,97 @@ select * from ProbeSetData limit 5;
 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