ebus-racket: -pq uses synx/libpq

1 files changed, 92 insertions, 0 deletions

diff --git a/ebus-racket/db-pq.rkt b/ebus-racket/db-pq.rkt
new file mode 100644
index 0000000..03ef154
--- /dev/null
+++ b/ebus-racket/db-pq.rkt
@@ -0,0 +1,92 @@
+#lang racket/base
+;; Database Access with synx/libpq ffi bindings
+(require racket/class)
+(require (prefix-in pq: (planet synx/libpq:1:3)))
+
+(define logger (make-logger 'ebus-db (current-logger)))
+
+(define con-user? (make-parameter "ebus"))
+(define con-password? (make-parameter "ebus"))
+(define con-database? (make-parameter "ebus"))
+(define con-server? (make-parameter "localhost"))
+
+(define _con (void))
+(define (con)
+	(when (void? _con)
+	     (log-message logger 'info (format "Connect using libpq to Database: user=~a database=~a server=~a" 
+					       (con-user?) (con-database?) (con-server?)) #t)
+		(set! _con
+			(pq:connect #:host (con-server?)
+                               #:user (con-user?)
+                               #:password (con-password?)
+                               #:dbname (con-database?))))
+	_con)
+
+(define (query-matrix stmt)
+	(con)
+	(send _con exec stmt))
+
+;; Test Database Connection
+;; Returns #t on success, #f otherwise
+(define (pgc-test)
+  (with-handlers ([exn:fail? (lambda (exn) (display exn)(display "\n") #f)])
+    (query-matrix "SELECT 123")
+    #t))
+
+;; Query ID of sensor given by sensor-name
+;; return void 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))])
+    (let* (
+	[result (send (con) exec sql-stmt sensor-name)]
+	[matrix (send result get-matrix)])
+	(caar matrix))))
+
+;; Create Sensor-ID with given name
+;; returns id
+(define (create-sensor-id sensor-name)
+  (log-message logger 'info (format "create sensor id for ~a" sensor-name) #t)
+  (define sql-stmt "INSERT INTO sensor(name) VALUES ($1)")
+  (send (con) exec sql-stmt 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 (insert-field sensor-name datatype offset value)
+  (cond ((member datatype (list "data1c" "data2b" "data2c"))
+  	 (insert sensor-name "float" value))
+  	((member datatype (list "bit" "byte" "data1b" "word" "bcd"))
+  	 (insert sensor-name "int" value))
+  	((member datatype (list "byteEnum"))
+  	 (insert sensor-name "string" value))
+  	(else 
+         (log-message logger 'error (format "Datatype ~a is not support by DB" datatype) #t))))
+
+(define (insert sensor-name type value)
+  (define sensor-id (get-or-create-sensor-id sensor-name))
+  (log-message logger 'info (string-append (format "sensor-id=~a type=~a value=~a"
+					    sensor-id type value)) #t)
+  (cond ((string=? type "string")
+	 (send (con) p-exec "INSERT INTO value (timestamp, sensor_id, type, value_string) VALUES (now(), $1, 'string', $2)" sensor-id value))
+        ((string=? type "float")
+	 (send (con) p-exec "INSERT INTO value (timestamp, sensor_id, type, value_float) VALUES (now(), $1, 'float', $2)" sensor-id value))
+        ((string=? type "int")
+	 (send (con) p-exec "INSERT INTO value (timestamp, sensor_id, type, value_int) VALUES (now(), $1, 'int', $2::integer)" sensor-id value))))
+
+(provide
+ (prefix-out db- logger)
+ (prefix-out db- con-user?)
+ (prefix-out db- con-password?)
+ (prefix-out db- con-database?)
+ (prefix-out db- con-server?)
+ (prefix-out db- pgc-test)
+ (prefix-out db- insert-field))