From b1db013cc01c94e27edf982be9b027a2b0bb9712 Mon Sep 17 00:00:00 2001
From: Pjotr Prins
Date: Sat, 18 Nov 2023 12:33:22 +0100
Subject: Get first BXD dataset for precompute

---
 gn/data/strains.scm                    |  5 +++--
 gn/db/mysql.scm                        |  2 +-
 gn/util/convert.scm                    | 14 ++++++++++++++
 scripts/precompute/precompute-hits.scm | 35 +++++++++++++++++++++++++++++++---
 4 files changed, 50 insertions(+), 6 deletions(-)
 create mode 100644 gn/util/convert.scm

diff --git a/gn/data/strains.scm b/gn/data/strains.scm
index 241ecda..39fe71e 100644
--- a/gn/data/strains.scm
+++ b/gn/data/strains.scm
@@ -7,8 +7,9 @@
   #:use-module (ice-9 string-fun)
   ;; #:use-module (gn db sparql)
   #:use-module (dbi dbi)
-  #:use-module (gn db mysql)
   #:use-module (gn data group)
+  #:use-module (gn db mysql)
+  #:use-module (gn util convert)
   #:use-module (web gn-uri)
 
   #:export (
@@ -24,7 +25,7 @@ map? will say whether the strains/individuals are used for mapping.
 "
   (call-with-db
    (lambda (db)
-     (dbi-query db (string-append "SELECT StrainId,Strain.Name FROM Strain, StrainXRef WHERE StrainXRef.StrainId = Strain.Id AND StrainXRef.InbredSetId = " (format #f "~d" inbred-set)
+     (dbi-query db (string-append "SELECT StrainId,Strain.Name FROM Strain, StrainXRef WHERE StrainXRef.StrainId = Strain.Id AND StrainXRef.InbredSetId = " (int-to-string inbred-set)
                                   (if map?
                                       " AND Used_for_mapping='Y'"
                                       "")
diff --git a/gn/db/mysql.scm b/gn/db/mysql.scm
index 4e2b280..fb7093a 100644
--- a/gn/db/mysql.scm
+++ b/gn/db/mysql.scm
@@ -50,7 +50,7 @@
   (dbi-get_row db))
 
 (define (get-rows db list)
-  "After running dbi-query we can fetch all rows and return them as a list of records, which are assoc list:
+  "After running dbi-query we can fetch all rows and return them as a list of records, which is an alist:
 
     (dbi-query db \"SELECT StrainId,Strain.Name FROM Strain, StrainXRef WHERE StrainXRef.StrainId = Strain.Id AND StrainXRef.InbredSetId = 1 ORDER BY StrainId;\")
     (db-check db)
diff --git a/gn/util/convert.scm b/gn/util/convert.scm
new file mode 100644
index 0000000..ff943e0
--- /dev/null
+++ b/gn/util/convert.scm
@@ -0,0 +1,14 @@
+(define-module (gn util convert)
+  #:use-module (json)
+  #:use-module (ice-9 match)
+  #:use-module (ice-9 format)
+  #:use-module (ice-9 iconv)
+  #:use-module (ice-9 receive)
+  #:use-module (ice-9 string-fun)
+
+  #:export (
+            int-to-string
+            ))
+
+(define (int-to-string i)
+  (format #f "~d" i))
diff --git a/scripts/precompute/precompute-hits.scm b/scripts/precompute/precompute-hits.scm
index a6617d9..28dabc9 100755
--- a/scripts/precompute/precompute-hits.scm
+++ b/scripts/precompute/precompute-hits.scm
@@ -7,14 +7,19 @@
 (use-modules (dbi dbi)
              (gn db mysql)
              (gn data strains)
+             (gn util convert)
              (rnrs base)
              (ice-9 match)
+             (srfi srfi-1)
              )
 
 ;; potentially you want to test connection with mysql client:
 ;;
 ;;    mysql -uwebqtlout -pwebqtlout -A -h 127.0.0.1 -P 3306 db_webqtl -e "show tables;"
 ;;
+;; for now update Locus_old with
+;;
+;;    update ProbeSetXRef set Locus_old=NULL;
 
 (call-with-db
  (lambda (db)
@@ -38,8 +43,32 @@
      ;  )
      ;(db-check2 db)
      ;(newline)
-     ;; get first dataset for precompute
-     (dbi-query db "select ProbeSetId, Locus, DataId from ProbeSetXRef where Locus_old is NULL LIMIT 1")
-     (display (get-row db))
+     ;; ---- get first available dataset for precompute:
+     (dbi-query db "select Locus, DataId, ProbeSetId from ProbeSetXRef where Locus_old is NULL LIMIT 1")
+     (define hit (get-row db))
+     (display hit)
+     (define data-id (assoc-ref hit "DataId"))
+     (display data-id)
      (newline)
+     ;; ---- Get strains and phenotypes for this dataset
+     (dbi-query db (string-append "SELECT StrainId,value from ProbeSetData WHERE Id=" (int-to-string data-id)))
+     (define traits (get-rows-apply db
+                              (lambda (r) `(,(assoc-ref r "StrainId") . ,(assoc-ref r "value")))
+                              '()))
+     ;; ---- Now we need to make sure that all strains belong to BXD
+     (define non-bxd (fold
+               (lambda (strain lst)
+                 (let ([id (car strain)])
+                   (if (assoc id bxd-strains)
+                       lst
+                       (append lst `(,id)))))
+
+               '()
+               traits))
+     (if (= 0 (length non-bxd))
+         (begin
+           (display "WE HAVE OUR BXD DATASET for precompute!")
+           (display traits)
+           (newline)
+         ))
 )))
-- 
cgit v1.2.3