2014-11-03

PostgreSQL for R7RS Scheme

I've been writing the library for PostgreSQL and it seems very basic things are working. So let me introduce it.

The repository is here: PostgreSQL binding for R7RS Scheme

Currently, it supports following R7RS implementations;
  • Sagittarius 0.5.9 or later
  • Gauche 0.9.4 or later
  • Chibi Scheme 0.7 or later
The library is written as portable as possible so if other implementations support required SRFIs, then they should also be able to use it. I'm assuming implementations support all R7RS standard library, though.

The library consists 2 parts; one is API layer and the other one is PostgreSQL frontend commands layer. The latter one is not documented so it is your own risk to use it and might be changed in the future. Following example shows how to use the high level APIs;
(import (scheme base) (postgresql))

;; for convenience
(define (print . args) (for-each display args) (newline))

;; user: postgres
;; pass: postgres
;; use default datebase
;; The connection is *not* opened yet.
(define conn (make-postgresql-connection 
       "localhost" "5432" #f "postgres" "postgres"))

;; open connection.
(postgresql-open-connection! conn)

;; inserts a record
;; this returns an affected row number
(postgresql-execute-sql! conn 
  "insert into test (id, name) values (1, 'name')")

;; execute a SQL directly. This stores all record in
;; the query object. So it is not a good idea to use
;; this to a table contains more than 10000 record.
(let ((r (postgresql-execute-sql! conn "select * from test")))
  ;; fetching the result. returning value could be either
  ;; a vector or #f
  (print (postgresql-fetch-query! r)))

;; Using prepared statement
(let ((p (postgresql-prepared-statement 
   conn "select * from test where name = $1")))
  ;; binds a parameter. it can take variable length
  ;; arguments
  (postgresql-bind-parameters! p "name")
  (let ((q (postgresql-execute! p)))
    ;; same as direct execution
    (print (postgresql-fetch-query! q)))
  ;; prepared statement must be closed.
  (postgresql-close-prepared-statement! p))

;; terminate session and close connection.
(postgresql-terminate! conn)
There are still bunch of functionalities missing, for example, it doesn't have transaction API, nor proper data conversion for insert/update statements. But I think it's a good start point.

To run above script, there is a bit complicated way to do it. Assume you're runnig the script in the project directory.
# for Sagittarius
sash -Llib -S.sld example.scm
# for Gauche
gosh -r7 -Ilib -e '(set! *load-suffixes* (cons ".sld" *load-suffixes*))' example.scm
# for Chibi
chibi-scheme -Ilib example.scm
Gauche is the trickiest one, there is no explicit command line option to prepend/append library suffixes.

Your opinions or pull requests are always welcome :)

No comments:

Post a Comment