blob: ccd414abfba8a6ffa03cade983b330573fce9e61 (
plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
|
#!
Module for handling SQL DB primitives. Note that GN queries should go into gn/data
!#
(define-module (gn db mysql)
#: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)
#:use-module (rnrs base)
#:use-module (dbi dbi)
#:export (
;; DB <- don't export
call-with-db
get-row
get-rows
get-rows-apply
has-result?
ensure
;db-open
;db-query
;db-get-row
;db-display-rows
;db-get-rows-apply
))
(define (db-open)
(begin
;; (display "===> OPENING DB")
;; (newline)
(let [(db (dbi-open "mysql" "webqtlout:webqtlout:db_webqtl:tcp:127.0.0.1:3306"))]
(ensure db)
db
)))
(define (call-with-db thunk)
(thunk (db-open)))
(define (ensure db)
"Use DBI-style handle to report an error. On error the program will stop."
(match (dbi-get_status db)
((stat . msg) (if (= stat 0)
#t
(begin
(display msg)
(newline)
(assert stat))))))
(define (has-result? db)
"Return #t or #f if result is valid"
(match (dbi-get_status db)
((stat . msg) (= stat 0))))
(define (get-row db)
"Return record and #f is it was the last one"
(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 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;\")
(ensure db)
(display (get-rows db '()))
(((StrainId . 4) (Name . BXD1)) ((StrainId . 5) (Name . BXD2))...
"
(let [(row (dbi-get_row db))]
(if row
(get-rows db (append list `(,row)))
list)))
(define (get-rows-apply db func lst)
"Similar to db-get-rows with a function that gets applied on every record, e.g.
(define ids (db-get-rows-apply db (lambda (r) `(,(assoc-ref r \"StrainId\") . ,(assoc-ref r \"Name\"))) '()))
"
(let [(row (dbi-get_row db))]
(begin
; (display row)
(if row
(get-rows-apply db func (append lst `(,(func row))))
lst)))
)
|
