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
pg-custom-type
pg-custom-type?
postgresql-connection<%>
add-custom-types
async-message-evt
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* 0 30 7 1 1 0 0 0 #f 0 0 "")

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

(date* 0 0 0 25 12 1980 4 359 #f 0 0 "")

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

(date* 0 0 0 1 1 1970 4 0 #f 0 0 "")

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.
Changed in version 1.8: Made the check stricter: no characters are allowed before or after the UUID.

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

procedure

(pg-custom-type typeid    
  typename    
  [basetype    
  #:recv recv-convert    
  #:send send-convert])  pg-custom-type?
  typeid : exact-nonnegative-integer?
  typename : symbol?
  basetype : (or/c #f symbol? exact-nonnegative-integer?) = #f
  recv-convert : (or/c #f (-> any/c any/c)) = values
  send-convert : (or/c #f (-> any/c any/c)) = values
Creates a custom type descriptor that can be used with PostgreSQL connections; see add-custom-types.

The typeid refers to the OID of a row in the server’s pg_type system table. The typename symbol is the name this library uses for the type in parameter descriptions, error messages, etc; it is not necessarily the same as the server’s type name for typeid. The server’s type name must be used in SQL statements.

When the type identified by typeid appears in a query result, the result value is first received according to basetype, then the resulting Racket value is converted using recv-convert. If basetype is #f (the default), it is treated like the bytea type; that is, recv-convert gets the byte string sent by the server. If recv-convert is #f, the type is not allowed as a result type.

When the type identified by typeid is used as a query parameter, the argument value is first converted using send-convert, and the converted value is sent according to basetype. If send-convert is #f, the type is not allowed as a parameter type.

Examples:
> (define cidr-typeid
    (query-value pgc "select oid from pg_type where typname = $1" "cidr"))
> cidr-typeid

650

> (send pgc add-custom-types
        (list (pg-custom-type cidr-typeid 'cidr
                              #:recv bytes->list
                              #:send list->bytes)))
> (query-value pgc "select cidr '127.0.0.0/24'")

'(2 24 1 4 127 0 0 0)

Added in version 1.8 of package db-lib.

procedure

(pg-custom-type? v)  boolean?

  v : any/c
Returns #t if v was created with pg-custom-type, #f otherwise.

Added in version 1.8 of package db-lib.

interface

postgresql-connection<%> : interface?

Interface for additional operations implemented by connections created with postgresql-connect.

method

(send a-postgresql-connection add-custom-types types)  void?

  types : (listof pg-custom-type?)
Registers the given types with a-postgresql-connection for use as query parameter and result types. See pg-custom-type for details.

method

(send a-postgresql-connection async-message-evt)  evt?

Returns a synchronizable event that becomes ready when input is available from the backend. When the event is selected, it attempts to handle any asynchronous notice and notification messages; its synchronization result is #t if any messages were handled by the event’s synchronization, #f otherwise.

Note that the event is highly prone to “false alarms”, when it becomes ready but produces #f.

Added in version 1.8 of package db-lib.

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