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

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

Returns #t if x is a point, line-string, polygon, multi-point, multi-line-string, multi-polygon, or geometry-collection; #f othewise.

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.

struct
(struct pg-empty-range ())

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.

(require db/unsafe/sqlite3)

package: db-lib

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:

init-acc is the initial accumulator value.
step-func receives arity+1 arguments: the current accumulator value, followed by the arguments of the current “step”; the function’s result becomes the accumulator value for the next step. The step arguments are SQLite values; the accumulator argument and result can be arbitrary Racket values.
final-func receives one argument: the final accumulator value; the function produces the result of the aggregate function, which must be a SQLite value.

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

top ← prev up next →

1	Using Database Connections
2	Connections
3	Queries
4	SQL Types and Conversions
5	Utilities
6	Notes

5.1	Datetime Type Utilities
5.2	Geometric Types
5.3	Postgre SQL-specific Types
5.4	Cassandra-Specific Functionality
5.5	Testing Database Programs
5.6	Unsafe SQLite3 Extensions