On this page:
5.1 Datetime Type Utilities
sql-datetime->srfi-date
srfi-date->sql-date
srfi-date->sql-time
srfi-date->sql-time-tz
srfi-date->sql-timestamp
srfi-date->sql-timestamp-tz
sql-day-time-interval->seconds
5.2 Geometric Types
point
line-string
polygon
multi-point
multi-line-string
multi-polygon
geometry-collection
geometry2d?
line?
linear-ring?
geometry->wkb
wkb->geometry
5.3 Postgre  SQL-specific Types
pg-array
pg-array-ref
pg-array->list
list->pg-array
pg-empty-range
pg-range
pg-range-or-empty?
uuid?
pg-box
pg-path
pg-circle
5.4 Cassandra-Specific Functionality
cassandra-consistency
5.5 Testing Database Programs
high-latency-connection
5.6 Unsafe SQLite3 Extensions
sqlite3-load-extension
sqlite3-create-function
sqlite3-create-aggregate

5 Utilities

The bindings described in this section are provided by the specific modules below, not by db or db/base.

5.1 Datetime Type Utilities

 (require db/util/datetime) package: db-lib

procedure

(sql-datetime->srfi-date t)  srfi:date?

  t : (or/c sql-date? sql-time? sql-timestamp?)

procedure

(srfi-date->sql-date d)  sql-date?

  d : srfi:date?

procedure

(srfi-date->sql-time d)  sql-time?

  d : srfi:date?

procedure

(srfi-date->sql-time-tz d)  sql-time?

  d : srfi:date?

procedure

(srfi-date->sql-timestamp d)  sql-timestamp?

  d : srfi:date?

procedure

(srfi-date->sql-timestamp-tz d)  sql-timestamp?

  d : srfi:date?
Converts between this library’s date and time values and SRFI 19’s date values (see srfi/19). SRFI dates store more information than SQL dates and times, so converting a SQL time to a SRFI date, for example, puts zeroes in the year, month, and day fields.

Examples:
> (sql-datetime->srfi-date
   (query-value pgc "select time '7:30'"))

date-year-day: TIME-ERROR type invalid-month-specification:

0

> (sql-datetime->srfi-date
   (query-value pgc "select date '25-dec-1980'"))

eval: unable to replay evaluation of

(sql-datetime->srfi-date (query-value pgc "select date

'25-dec-1980'"))

> (sql-datetime->srfi-date
   (query-value pgc "select timestamp 'epoch'"))

eval: unable to replay evaluation of

(sql-datetime->srfi-date (query-value pgc "select timestamp

'epoch'"))

procedure

(sql-day-time-interval->seconds interval)  rational?

  interval : sql-day-time-interval?
Returns the length of interval in seconds.

5.2 Geometric Types

 (require db/util/geometry) package: db-lib

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

(struct point (x y))

  x : real?
  y : real?
Represents an OpenGIS Point.

struct

(struct line-string (points))

  points : (listof point?)
Represents an OpenGIS LineString.

struct

(struct polygon (exterior interiors))

  exterior : linear-ring?
  interiors : (listof linear-ring?)
Represents an OpenGIS Polygon.

struct

(struct multi-point (elements))

  elements : (listof point?)
Represents an OpenGIS MultiPoint, a collection of points.

struct

(struct multi-line-string (elements))

  elements : (listof line-string?)
Represents an OpenGIS MultiLineString, a collection of line-strings.

struct

(struct multi-polygon (elements))

  elements : (listof polygon?)
Represents an OpenGIS MultiPolygon, a collection of polygons.

struct

(struct geometry-collection (elements))

  elements : (listof geometry2d?)
Represents an OpenGIS GeometryCollection, a collection of arbitrary geometric values.

procedure

(geometry2d? x)  boolean?

  x : any/c

procedure

(line? x)  boolean?

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

procedure

(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.

procedure

(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).

procedure

(wkb->geometry b)  geometry2d?

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

5.3 PostgreSQL-specific Types

 (require db/util/postgresql) package: db-lib

struct

(struct pg-array (dimensions
    dimension-lengths
    dimension-lower-bounds
    contents))
  dimensions : exact-nonnegative-integer?
  dimension-lengths : (listof exact-positive-integer?)
  dimension-lower-bounds : (listof exact-integer?)
  contents : vector?
Represents a PostrgreSQL array. The dimension-lengths and dimension-lower-bounds fields are both lists of dimensions elements. By default, PostgreSQL array indexes start with 1 (not 0), so dimension-lower-bounds is typically a list of 1s.

procedure

(pg-array-ref arr index ...+)  any/c

  arr : pg-array?
  index : exact-integer?
Returns the element of arr at the given position. There must be as many index arguments as the dimension of arr. Recall that PostgreSQL array indexes usually start with 1, not 0.

procedure

(pg-array->list arr)  list?

  arr : pg-array?
Returns a list of arr’s contents. The dimension of arr must be 1; otherwise an error is raised.

procedure

(list->pg-array lst)  pg-array?

  lst : list?
Returns a pg-array of dimension 1 with the contents of lst.

Represents an empty range.

struct

(struct pg-range (lb includes-lb? ub includes-ub?))

  lb : range-type
  includes-lb? : boolean?
  ub : range-type
  includes-ub? : boolean?
Represents a range of values from lb (lower bound) to ub (upper bound). The includes-lb? and includes-ub? fields indicate whether each end of the range is open or closed.

The lb and ub fields must have the same type; the permissible types are exact integers, real numbers, and sql-timestamps. Either or both bounds may also be #f, which indicates the range is unbounded on that end.

procedure

(pg-range-or-empty? v)  boolean?

  v : any/c
Returns #t if v is a pg-range or pg-empty-range instance; otherwise, returns #f.

procedure

(uuid? v)  boolean?

  v : any/c
Returns #t if v is a string that matches the format of a hexadecimal representation of a UUID. Specifically, it must be a series of hexadecimal digits separated by dashes, in the following pattern:

 

uuid

 ::= 

digit16{8,8} - digit16{4,4} - digit16{4,4} - digit16{4,4} - digit16{12,12}

The digits themselves are case-insensitive, accepting both uppercase and lowercase characters. Otherwise, if v is not a string matching the above pattern, this function returns #f.

Added in version 1.1 of package db-lib.

struct

(struct pg-box (ne sw))

  ne : point?
  sw : point?

struct

(struct pg-path (closed? points))

  closed? : boolean?
  points : (listof point?)

struct

(struct pg-circle (center radius))

  center : point?
  radius : real?
These 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).

5.4 Cassandra-Specific Functionality

 (require db/util/cassandra) package: db-lib

parameter

(cassandra-consistency)

  
(or/c 'any 'one 'two 'three 'quorum 'all 'local-quorum
      'each-quorum 'serial 'local-serial 'local-one)
(cassandra-consistency consistency)  void?
  consistency : 
(or/c 'any 'one 'two 'three 'quorum 'all 'local-quorum
      'each-quorum 'serial 'local-serial 'local-one)
Controls the tunable consistency level that Cassandra uses to execute query operations.

The default consistency level is 'one.

5.5 Testing Database Programs

 (require db/util/testing) package: db-lib

This module provides utilities for testing programs that use database connections.

procedure

(high-latency-connection connection 
  latency 
  [#:sleep-atomic? sleep-atomic?]) 
  connection?
  connection : connection?
  latency : (>=/c 0)
  sleep-atomic? : any/c = #f
Returns a proxy connection for connection that introduces latency additional seconds of latency before operations that require communicating with the database back end—prepare, query, start-transaction, etc.

Use this function in performance testing to roughly simulate environments with high-latency communication with a database back end.

If sleep-atomic? is true, then the proxy enters atomic mode before sleeping, to better simulate the effect of a long-running FFI call (see FFI-Based Connections and Concurrency). Even so, it may not accurately simulate an ODBC connection that internally uses cursors to fetch data on demand, as each fetch would introduce additional latency.

5.6 Unsafe SQLite3 Extensions

The procedures documented in this section are unsafe.

In the functions below, the connection argument must be a SQLite connection; otherwise, an exception is raised.

Added in version 1.4 of package db-lib.

procedure

(sqlite3-load-extension c extension-path)  void?

  c : connection?
  extension-path : path-string?
Load the extension library at extension-path for use by the connection c. If the current security guard does not grant read and execute permission on extension-path, an exception is raised.

procedure

(sqlite3-create-function c name arity func)  void?

  c : connection?
  name : (or/c string? symbol?)
  arity : (or/c exact-nonnegative-integer? #f)
  func : procedure?
Creates a normal function named name available to the connection c. The arity argument determines the legal number of arguments; if arity is #f then any number of arguments is allowed (up to the system-determined maximum). Different implementations can be provided for different arities of the same name.

procedure

(sqlite3-create-aggregate c    
  name    
  arity    
  init-acc    
  step-func    
  final-func)  void?
  c : connection?
  name : (or/c string? symbol?)
  arity : (or/c exact-nonnegative-integer? #f)
  init-acc : any/c
  step-func : procedure?
  final-func : procedure?
Like sqlite3-create-aggregate, but creates an aggregate function. The implementation of an aggregate function are like the arguments of fold:

The following relationship roughly holds:

(begin (sqlite3-create-aggregate c "agg" 1 init-acc step-func final-func)
       (query-value c "select agg(expr) from table"))
= (final-func
    (for/fold ([accum init-acc])
              ([v (in-query c "select expr from table")])
      (step-func accum v)))