Next: , Previous: , Up: Top   [Contents][Index]


14 Single-Table Abstraction

Sometimes it is convenient to focus on a single table, adding rows to it or drawing queries from it exclusively of any other tables in the database. Likewise with a view, except that only queries are possible. This chapter describes Guile-PG facilities for this kind of use.

To get started, load the postgres-table module:

(use-modules (database postgres-table))

Basically, the pgtable-manager procedure encapsulates specification, query and mutation (including create/delete) of a PostgreSQL table; and the pgtable-worker procedure encapsulates pgtable-manager.

Procedure: pgtable-manager db-spec table-name defs

Return a closure that manages a table specified by db-spec table-name defs.

db-spec can either be a string simply naming the database to use, a string comprised of space-separated var=val pairs, an empty string, or an already existing connection. Database Connections.

table-name is a string naming the table to be managed.

defs is an alist of column definitions of the form (name type [option…]), with name and type symbols and each option a string. An old format w/o options is recognized also, but deprecated; support for it will go away in a future release.

The closure accepts a single keyword (or symbol) arg choice and returns a procedure. Here are the accepted keywords along w/ the args (if any) taken by the returned procedure.

* #:drop
* #:create
* #:insert-values [data ...]
* #:insert-col-values cols [data ...]
* #:insert-alist alist
* #:delete-rows where-condition
* #:update-col cols data where-condition
* #:update-col-alist alist where-condition
* #:select outspec [rest-clauses ...]
  #:tuples-result->object-alist res
  #:tuples-result->alists res
  #:tuples-result->rows res
  #:trace-exec oport
  #:finish
  #:k var

The starred (*) procedures return whatever pg-exec returns for that type of procedure. Arguments can be:

var

A keyword, one of #:table-name, #:col-defs, or #:connection.

data

One or more Scheme objects.

cols

Either a list of column names (symbols), or a single string of comma-delimited column names.

where-condition

A prefix-style expression.

outspec

Either the result of compile-outspec, or a spec that compile-outspec can process to produce such a result.

rest-clauses

Zero or more prefix-style expressions.

res

A tuples result, as returned by pg-exec (assuming no error occurred).

oport

An output port to write the pg-exec command to immediately prior to executing it. Use #f to disable tracing.

As a special case, #:finish closes the (internal) connection and arranges for all future invocations of the closure to signal a ‘dead connection’ error.

Procedure: compile-outspec spec defs

Return a compiled outspec object from spec and defs, suitable for passing to the select choice of pgtable-manager. defs is the same as that for pgtable-manager. spec can be one of the following:

A "bad select part" error results if specified columns or types do not exist, or if other syntax errors are found in spec.

Procedure: pgtable-worker db-spec table-name defs

Take db-spec, table-name and defs (exactly the same as for pgtable-manager) and return a procedure worker similar to that returned by pgtable-manager except that the data choices table-name, defs and pgdb result in error (only those choices which return a procedure remain), and more importantly, worker actually does the actions (applying the chosen procedure to its args). For example:

(define M (pgtable-manager spec name defs))
(define W (pgtable-worker  spec name defs))

(equal? ((M #:tuples-result->alists) ((M #:select) #t))
        (W #:tuples-result->alists (W #:select #t)))
⇒ #t

This example is not intended to be wry commentary on the behavioral patterns of human managers and workers, btw.

The other procedures are useful for succinctly codifying queries, and translating the result en mass to Scheme objects. [NOTE: These will definitely migrate to other modules at some point before 1.0 release.]

Procedure: sql-pre string

Return string marked as preformatted. This inhibits certain types of processing when passed through the other procedures defined in this module. Repeated calls do not nest.

The rest of this chapter is an extended example showing some of the uses of pgtable-manager.

(define m (pgtable-manager "ttn" "guile_pg_demo"
                           '((i          serial)
                             (date       timestamp)
                             (amount     float4)
                             (catcode    text)
                             (details    *text))))

((m #:create))
⇒ #<PG-RESULT:COMMAND_OK:0:0>

(define add (m #:insert-col-values))
(define sel (m #:select))

(add '(date amount) (current-time) 1.98)
⇒ #<PG-RESULT:COMMAND_OK:0:0>

(add '(date amount) (current-time) 2.98)
⇒ #<PG-RESULT:COMMAND_OK:0:0>

(define (under n)
  (sel '((integer #f (count *)))  ;; outspec
       #:where `(< amount ,n)))   ;; prefix-style expression

(define result (under 2.00))

result
⇒ #<PG-RESULT:TUPLES_OK:1:1>

((m #:tuples-result->object-alist) result)
⇒ ((count 1))

((m #:drop))
⇒ (#<PG-RESULT:COMMAND_OK:0:0> #<PG-RESULT:COMMAND_OK:0:0>)

Two things to note in this example: (1) Both #:create and #:drop invocations evaluate the operating thunk directly (double parentheses); and (2) The drop returns a list of result values to accommodate the possibility (shown here) of a serial column type, which requires an additional drop of the associated PostgreSQL sequence used to implement the expected serial behavior. (As of PostgreSQL 7.x, the sequence name is constructed like so: TABLENAME_COLNAME_seq. For more info, see the PostgreSQL User Guide, Chapter 3: Data Types.)


Next: , Previous: , Up: Top   [Contents][Index]