racket: aufgraeumt, data2c gefixt

1 files changed, 88 insertions, 0 deletions

diff --git a/ebus-racket/ebus/db.rkt b/ebus-racket/ebus/db.rkt
new file mode 100644
index 0000000..2c6be6c
--- /dev/null
+++ b/ebus-racket/ebus/db.rkt
@@ -0,0 +1,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)