▼DB: Database Connectivity
►5 Utilities
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?
pg-box
pg-path
pg-circle
5.4 Testing Database Programs
high-latency-connection
  top← prev  up  next → 

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)

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

(tm:date 0 0 30 7 0 0 0 0)

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

(tm:date 0 0 0 0 25 12 1980 0)

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

(tm:date 0 0 0 0 1 1 1970 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)

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

  exterior : linear-ring?
  interior : (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)

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.

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 Testing Database Programs

 (require db/util/testing)

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.

  top← prev  up  next →