blob: 2c6be6cfff0fa8bcfd3e0c47b99938267b59949d (
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
88
|
#lang racket/base
(require racket/path)
(require (prefix-in db: db))
(define db-logger (make-logger 'ebus-db (current-logger)))
(define db-file? (make-parameter
(build-path (path-only (find-system-path 'run-file)) "database.sqlite3")))
(define pool
(db:virtual-connection
(db:connection-pool
(lambda ()
(log-message db-logger 'info (format "Open database file ~a" (db-file?)) #t)
(with-handlers ([exn:fail? (lambda (exn)
(log-message db-logger 'error (format "Error opening database ~a" (db-file?)) #t) (raise exn))])
(db:postgresql-connect #:user "ebus"
#:database "ebus"
#:password "ebus"
#:server "localhost")))
;; (db:sqlite3-connect #:database (db-file?))))
;;
#:max-connections 5
#:max-idle-connections 2
)))
;; Test Database Connection
;; Returns #t on success, #f otherwise
(define (db-test)
(with-handlers ([exn:fail? (lambda (exn) (log-message db-logger 'error (format "Error: ~a" exn) #t) #f)])
(= (db:query-value pool "SELECT 1") 1)))
;; Query ID of sensor given by sensor-name
;; Returns null if sensor is undefined
(define (get-sensor-id sensor-name)
(define sql-stmt "SELECT id FROM sensor WHERE name = $1")
(with-handlers ([exn:fail? (lambda (exn) (void))])
(db:query-value pool sql-stmt sensor-name)))
;; Create Sensor-ID with given name
;; returns id
(define (create-sensor-id sensor-name)
(log-message db-logger 'info (format "create sensor id for ~a" sensor-name) #t)
(db:query-exec pool "INSERT INTO sensor(name) VALUES ($1)" sensor-name)
(get-sensor-id sensor-name))
;; Get ID of sensor given by sensor-name
;; define sensor if needed
(define (get-or-create-sensor-id sensor-name)
(define id (get-sensor-id sensor-name))
(cond ((void? id) (create-sensor-id sensor-name))
(else id)))
;; Insert Field in Database
;; Decide Database-Datatype from Ebus-Datatype
;; then calls 'insert`
(define (db-insert-field sensor-name datatype offset value)
(cond ((member datatype (list "data1c" "data2b" "data2c"))
;; float
(insert sensor-name value db:sql-null db:sql-null))
((member datatype (list "bit" "byte" "data1b" "word" "bcd"))
;; int
(insert sensor-name db:sql-null value db:sql-null))
((member datatype (list "byteEnum"))
;; string
(insert sensor-name db:sql-null db:sql-null value))
(else (log-message db-logger 'error (format "Datatype ~a is not support by DB" datatype) #t))))
(define (insert sensor-name value-float value-int value-string)
(define sensor-id (get-or-create-sensor-id sensor-name))
(define type (cond ((not (db:sql-null? value-string)) "string")
((not (db:sql-null? value-float)) "float")
((not (db:sql-null? value-int)) "int")))
(define sql-stmt
(string-append "INSERT INTO value(timestamp, sensor_id, type, value_float, value_int, value_string) "
"VALUES (CURRENT_TIMESTAMP, $1, $2, $3, $4, $5)"))
(log-message db-logger 'info (string-append sql-stmt "\n\t\t"
(format
"sensor-id=~a type=~a value-float=~a value-int=~a value-string=~a"
sensor-id type value-float value-int value-string)) #t)
(db:query-exec pool sql-stmt sensor-id type value-float value-int value-string))
(provide
db-file?
db-logger
db-test
db-insert-field)
|
