Scheme Boston: January 2003 meeting

Implementing a Simple Picture Gallery using spgsql

Notes by Ken Williams <>

The fifth meeting of Scheme Boston took place at Northeastern University Monday night, January 13, 2003, with about 20 people in attendance.

Ryan Culpepper, a graduate student at Northeastern, briefly demonstrated a web-based application program that uses his library spgsql to access a database, then discussed some highlights of the code. The application is a simple picture gallery program that permits web access to a collection of pictures stored on a PostgreSQL server. The application supports searching for pictures by caption, user login, separate "domains" for each user, and the adding of comments to an overall list of comments. The web-based version of this program is a project that Ryan started as an undergraduate at Rice, and which is still ongoing.

The entire application is written in Scheme and is a good example of a non-trivial web-based servlet that runs on a PLT Scheme web server. The main interaction (event) loop illustrates the standard paradigm of using send/suspend to send a page of XHTML to the user's browser, then suspending to await a response.

(let reset ([user #f]
	    [request initial-request])
  (let interact ([request request])
    (let* ([bindings (request-bindings request)]
           [action (if (exists-binding? 'action bindings)
                       (string->symbol (extract-binding/single 'action bindings))
      (case action
         (interact (send/suspend (make-page "spg-sql: Search" user
                                             (search-body user))))]
         (interact (send/suspend (make-page "spg-sql: Search Results" user
                                             (listing-body user bindings))))]
         (interact (send/suspend (make-page "spg-sql: Log in" user
         (let* [(username (extract-binding/single 'username bindings))
                (password (extract-binding/single 'password bindings))]
           (if (login-valid? username password)
               (reset username (send/suspend (lambda (url) (redirect-to url))))
               (interact (send/suspend 
                          (make-page "spg-sql: Login failed" user
                                     (failed-login-body username))))))]
         (reset #f (send/suspend (make-page "spg-sql: Logged out" #f
                                            (lambda (user)
                                              `(div "Logged out")))))]
         (interact (send/suspend (make-page "spg-sql: About" user
         (interact (send/suspend (make-page "spg-sql: Comment" user
         (let ([comment (extract-binding/single 'commenttext bindings)])
           (comment-add! comment)
           (interact (send/suspend (make-page "spg-sql: Comment" user
This seems like a natural way to structure such a program, but it's only made possible through the capabilities of the PLT Scheme web-server collection. The make-page procedure generates a page from some parameters and a block of XHTML (actually, an s-expression representing HTML). The extract-binding/single is a PLT procedure that extracts bindings filled in by a user in a form and passed back from the browser.

Ryan has provided this spgsql demo page if you want to have a look at the code. The link provides most of what you need to get the application running at home if you have a PostgreSQL server and a PLT web server running. You will need to create a database schema from the English description that he gives. Also, as indicated in the link, you can get the spgsql library from the Sourceforge project page. You can check it out into a directory ./spgsql via anonymous CVS using the following commands:
  cvs -z3 -d:pserver:$who:$dir login
  cvs -z3 -d:pserver:$who:$dir co spgsql

Of course, the real meat of Ryan's example application is its interaction with a database server using embedded SQL commands. To accomplish this, Ryan first had to implement a PostgreSQL client that can communicate with the server using the low-level protocols specific to Postgres. From the user's standpoint, interactions with the server are made through a connection object. Multiple connections can be open, but the current connection is maintained as a thread-specific parameter available through a call to get-connection.

; picture-by-caption-map:
;    (Id Caption Filename -> 'a) Caption Domains -> (listof 'a)

; Maps a function over all pictures with captions containing the
; given caption string and belonging to one of the domains given.

; picture-by-caption-map: f caption domains -> (listof 'a)
; f: id caption filename -> 'a

(define (picture-by-caption-map f caption domains)
  (let ([constraints (generate-domain-constraints-sql domains)])
    (printf "Domain constraints: '~a'~n" constraints)
    (send (get-connection) map
          (sql-format "select mid, caption, filename"
                      "from chi_images"
                      "where position(
		        lower(~a) in lower(caption)) > 0"
                      "and (~a)"
                      "order by caption"
                      `(string ,caption)
                      `(sql ,constraints))
          f )))
The (slightly edited) excerpt above shows the embedded SQL being formatted by sql-format and sent to the connection object as a parameter to the map operation.

Ryan described several of the various methods that spgsql provides on connection objects, including exec and fold. The exec method sends an arbitrary SQL command to the server. The fold method combines and accumulates result rows of a single SELECT statement using using a supplied combining function. Queries may include any SQL construct supported by PostgreSQL.

Ryan briefly discussed the use of transactions, but indicated that it's still not clear how a program should behave if control jumps back into a previously aborted transaction (through the use of dynamic-wind).

Complete examples can be found in the samples directory of the Schematics Sourceforge spgsql CVS distribution. Also, the Schematics web site provides some high-level documentation on the spgsql project page.

After the talk, Mike Machenry agreed to talk about the GUI test builder that he is developing for the PLT Scheme group.