Merge pull request #180 from pjotrp/zsloan

Support maintenance scripts

1 files changed, 203 insertions, 4 deletions

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.