On this page:
5.1 Connection utilities
5.1.1 Connection pooling
5.1.2 Virtual connections
5.1.3 Kill-safe connections
5.1.4 Data source names
5.2 Geometric types
5.3 Postgre SQL-specific types

5 Utilities

The bindings described in this section are provided by the specific modules below, not by (planet ryanc/db:1:5).

5.1 Connection utilities

 (require (planet ryanc/db:1:5/util/connect))

5.1.1 Connection pooling

Creating an ordinary connection is often a relatively costly operation; it may involve steps such as process creation and SSL negotiation. A connection pool helps reduce connection costs by reusing connections.

(connection-pool connect 
  [#:max-connections max-connections 
  #:max-idle-connections max-idle-connections]) 
  connect : (-> connection?)
  max-connections : (or/c (integer-in 1 10000) +inf.0) = +inf.0
  max-idle-connections : (or/c (integer-in 1 10000) +inf.0) = 10
Creates a connection pool. The pool consists of up to max-connections, divided between leased connections and up to max-idle-connections idle connections. The pool uses connect to create new connections when needed; the connect function must return a fresh connection each time it is called.


> (define pool
    (lambda () (displayln "connecting!") (sqlite3-connect ....))
    #:max-idle-connections 1))
> (define c1 (connection-pool-lease pool))


> (define c2 (connection-pool-lease pool))


> (disconnect c1)
> (define c3 (connection-pool-lease pool)) ; reuses actual conn. from c1

See also virtual-connection for a mechanism that eliminates the need to explicitly call connection-pool-lease and disconnect.

(connection-pool? x)  boolean?
  x : any/c
Returns #t if x is a connection pool, #f otherwise.

(connection-pool-lease pool [release])  connection?
  pool : connection-pool?
  release : (or/c evt? custodian?) = (current-thread)
Obtains a connection from the connection pool, using an existing idle connection in pool if one is available. If no idle connection is available and the pool contains fewer than its maximum allowed connections, a new connection is created; otherwise an exception is raised.

Calling disconnect on the connection obtained causes the connection to be released back to the connection pool. The connection is also released if release becomes available, if it is a synchronizable event, or if release is shutdown, if it is a custodian. The default for release is the current thread, so the resulting connection is released when the thread that requested it terminates.

When a connection is released, it is kept as an idle connection if pool’s idle connection limit would not be exceeded; otherwise, it is disconnected. If the connection is in a transaction, the transaction is rolled back.

5.1.2 Virtual connections

A virtual connection creates actual connections on demand and automatically releases them when they are no longer needed.

(virtual-connection connect)  connection?
  connect : (or/c (-> connection?) connection-pool?)
Creates a virtual connection that creates actual connections on demand using the connect function, or by calling (connection-pool-lease connect) if connect is a connection pool. A virtual connection encapsulates a mapping of threads to actual connections. When a query function is called with a virtual connection, the current thread’s associated actual connection is used to execute the query. If there is no actual connection associated with the current thread, one is obtained by calling connect. An actual connection is disconnected when its associated thread dies.

Virtual connections are especially useful in contexts such as web servlets, where each request is handled in a fresh thread. A single global virtual connection can be defined, freeing each servlet request from explicitly opening and closing its own connections. In particular, a virtual connection backed by a connection pool combines convenience with efficiency:


> (define the-connection
    (virtual-connection (connection-pool (lambda () ....))))

The resulting virtual connection leases a connection from the pool on demand for each servlet request thread and releases it when the thread terminates (that is, when the request has been handled).

When given a connection produced by virtual-connection, connected? indicates whether there is an actual connection associated with the current thread. Likewise, disconnect causes the current actual connection associated with the thread (if there is one) to be disconnected, but the connection will be recreated if a query function is executed.


> (define c
     (lambda ()
       (printf "connecting!\n")
       (postgresql-connect ....))))
> (connected? c)


> (query-value c "select 1")



> (connected? c)


> (void (thread (lambda () (displayln (query-value c "select 2")))))



> (disconnect c)
> (connected? c)


> (query-value c "select 3")



Connections produced by virtual-connection may not be used with the prepare function. However, they may still be used to execute parameterized queries expressed as strings or encapsulated via virtual-statement.


> (prepare c "select 2 + $1")

prepare: cannot prepare statement with virtual connection

> (query-value c "select 2 + $1" 2)


> (define pst (virtual-statement "select 2 + $1"))
> (query-value c pst 3)


(connection-generator connect)  connection?
  connect : (-> connection?)
Deprecated. Equivalent to (virtual-connection connect).

5.1.3 Kill-safe connections

Creates a proxy for connection c. All queries performed through the proxy are kill-safe; that is, if a thread is killed during a call to a query function such as query, the connection will not become locked or damaged. (Connections are normally thread-safe but not kill-safe.)

Note: A kill-safe connection whose underlying connection uses ports to communicate with a database server is not protected from a custodian shutting down its ports.

5.1.4 Data source names

A DSN (Data Source name) is a symbol associated with a connection specification in a DSN file (inspired by ODBC’s DSNs).

(struct data-source (connector args extensions)
  connector : (or/c 'postgresql 'mysql 'sqlite3 'odbc)
  args : list?
  extensions : (listof (list/c symbol? any/c))
Represents a data source. The connector field determines which connection function is used to create the connection. The args field is a partial list of arguments passed to the connection function; additional arguments may be added when dsn-connect is called. The extensions field contains additional information about a connection; for example, this library’s testing framework uses it to store SQL dialect flags.

Data sources can also be created using the postgresql-data-source, etc auxiliary functions.

(dsn-connect dsn    
  [#:dsn-file dsn-file]    
  arg ...    
  #:<kw> kw-arg ...)  connection?
  dsn : (or/c symbol? data-source?)
  dsn-file : path-string? = (current-dsn-file)
  arg : any/c
  kw-arg : any/c
Makes a connection using the connection information associated with dsn in dsn-file. The given args and kw-args are added to those specified by dsn to form the complete arguments supplied to the connect function.

If dsn-file does not exist, or if it contains no entry for dsn, an exception is raised. If dsn is a data-source, then dsn-file is ignored.


> (put-dsn 'mydb
           (postgresql-data-source #:user "me"
                                   #:database "mydb"
                                   #:password "icecream"))
> (dsn-connect 'me)

(object:connection% ...)

> (dsn-connect 'me #:notice-handler (lambda (code msg) ....))

(object:connection% ...)

A parameter holding the location of the default DSN file. The initial value is a file located immediately within (find-system-path 'prefs-dir).

(get-dsn dsn [default #:dsn-file dsn-file])
  (or/c data-source? any/c)
  dsn : symbol?
  default : any/c = #f
  dsn-file : path-string? = (current-dsn-file)
Returns the data-source associated with dsn in dsn-file.

If dsn-file does not exist, an exception is raised. If dsn-file does not have an entry for dsn, default is called if it is a function or returned otherwise.

(put-dsn dsn ds [#:dsn-file dsn-file])  void?
  dsn : symbol?
  ds : (or/c data-source? #f)
  dsn-file : path-string? = (current-dsn-file)
Associates dsn with the given data source ds in dsn-file, replacing the previous association, if one exists.

  [#:user user 
  #:database database 
  #:server server 
  #:port port 
  #:socket socket 
  #:password password 
  #:allow-cleartext-password? allow-cleartext-password? 
  #:ssl ssl 
  #:notice-handler notice-handler 
  #:notification-handler notification-handler]) 
  user : string? = absent
  database : string? = absent
  server : string? = absent
  port : exact-positive-integer? = absent
  socket : (or/c path-string? 'guess #f) = absent
  password : (or/c string? #f) = absent
  allow-cleartext-password? : boolean? = absent
  ssl : (or/c 'yes 'optional 'no) = absent
  notice-handler : (or/c 'output 'error) = absent
  notification-handler : (or/c 'output 'error) = absent
(mysql-data-source [#:user user 
  #:database database 
  #:server server 
  #:port port 
  #:socket socket 
  #:password password 
  #:notice-handler notice-handler]) 
  user : string? = absent
  database : string? = absent
  server : string? = absent
  port : exact-positive-integer? = absent
  socket : (or/c path-string? 'guess #f) = absent
  password : (or/c string? #f) = absent
  notice-handler : (or/c 'output 'error) = absent
(sqlite3-data-source [#:database database 
  #:mode mode 
  #:busy-retry-limit busy-retry-limit 
  #:busy-retry-delay busy-retry-delay]) 
  database : (or/c path-string? 'memory 'temporary) = absent
  mode : (or/c 'read-only 'read/write 'create) = absent
  busy-retry-limit : (or/c exact-nonnegative-integer? +inf.0)
   = absent
  busy-retry-delay : (and/c rational? (not/c negative?))
   = absent
  [#:dsn dsn 
  #:database database 
  #:user user 
  #:password password 
  #:notice-handler notice-handler 
  #:strict-parameter-types? strict-parameter-types? 
  #:character-mode character-mode]) 
  dsn : (or/c string? #f) = absent
  database : (or/c string? #f) = absent
  user : (or/c string? #f) = absent
  password : (or/c string? #f) = absent
  notice-handler : (or/c 'output 'error) = absent
  strict-parameter-types? : boolean? = absent
  character-mode : (or/c 'wchar 'utf-8 'latin-1) = absent
Analogues of postgresql-connect, mysql-connect, sqlite3-connect, and odbc-connect, respectively, that return a data-source describing the (partial) connection information. All arguments are optional, even those that are mandatory in the corresponding connection function; the missing arguments must be supplied when dsn-connect is called.

5.2 Geometric types

 (require (planet ryanc/db:1:5/util/geometry))

The following structures and functions deal with geometric values based on the OpenGIS (ISO 19125) model.

Note: Geometric columns defined using the PostGIS extension to PostgreSQL are not directly supported. Instead, data should be exchanged in the Well-Known Binary format; conversion of the following structures to and from WKB format is supported by the wkb->geometry and geometry->wkb functions.

(struct point (x y))
  x : real?
  y : real?
Represents an OpenGIS Point.
(struct line-string (points))
  points : (listof point?)
Represents an OpenGIS LineString.
(struct polygon (exterior interior))
  exterior : linear-ring?
  interior : (listof linear-ring?)
Represents an OpenGIS Polygon.
(struct multi-point (elements))
  elements : (listof point?)
Represents an OpenGIS MultiPoint, a collection of points.
(struct multi-line-string (elements))
  elements : (listof line-string?)
Represents an OpenGIS MultiLineString, a collection of line-strings.
(struct multi-polygon (elements))
  elements : (listof polygon?)
Represents an OpenGIS MultiPolygon, a collection of polygons.
(struct geometry-collection (elements))
  elements : (listof geometry2d?)
Represents an OpenGIS GeometryCollection, a collection of arbitrary geometric values.

(geometry2d? x)  boolean?
  x : any/c

(line? x)  boolean?
  x : any/c
Returns #t if x is a line-string consisting of exactly two points (cf OpenGIS Line); #f otherwise.

(linear-ring? x)  boolean?
  x : any/c
Returns #t if x is a line-string whose first and last points are equal (cf OpenGIS LinearRing); #f otherwise.

(geometry->wkb g #:big-endian? big-endian?)  bytes?
  g : geometry2d?
  big-endian? : (system-big-endian?)
Returns the Well-Known Binary (WKB) encoding of the geometric value g. The big-endian? argument determines the byte order used (the WKB format includes byte-order markers, so a robust client should accept either encoding).

(wkb->geometry b)  geometry2d?
  b : bytes?
Decodes the Well-Known Binary (WKB) representation of a geometric value.

5.3 PostgreSQL-specific types

 (require (planet ryanc/db:1:5/util/postgresql))

The following structures represent certain of PostgreSQL’s built-in geometric types that have no appropriate analogue in the OpenGIS model: box, path, and circle. The point, lseg, and polygon PostgreSQL built-in types are represented using point, line-string (line?), and polygon structures.

Note: PostgreSQL’s built-in geometric types are distinct from those provided by the PostGIS extension library (see Geometric types).

(struct pg-box (ne sw))
  ne : point?
  sw : point?

(struct pg-path (closed? points))
  closed? : boolean?
  points : (listof point?)

(struct pg-circle (center radius))
  center : point?
  radius : real?