On this page:
6.1 Query methods/ procedures
6.2 Query syntax

6 Queries

SQL on Wikipedia

Snooze uses a combinator-based query language that mirrors a large subset of standard SQL, including joins, nested queries, aggregate functions, limits and offsets.

The sql form is used to enter the query language, and unquote is used to escape back into Scheme. Because sql statements are executed in the DBMS, the semantics of the query language are closer to SQL than Scheme.

Here is an example of a simple query:

  ; (listof person)
  ; Find all people in the database:
  (find-all (sql (select #:from person)))

The sql block describes the query to perform. The select form creates a SELECT statement that selects all person records in the database. The find-all procedure sends the query to the database, retrieves all the available query results, and turns them into a list.

Snooze automatically infers the type of query result. This example is selecting person records, so the results of the find-all is a (listof person).

The query language lets you customise the type of result in various ways. For example:

  ; (listof string)
  ; Find the names of all people from the database.
  (find-all (sql (select #:what person.name
                         #:from person)))
  ; (listof (list string integer))
  ; Find the names and ages of all people from the database.
  (find-all (sql (select #:what (person.name person.age)
                         #:from person)))

In the query language, dotted operators in identifiers are treated like operators meaning "attribute of". For example, person.name means "the name attribute of the person entity". The part before the dot has to be an entity or entity alias (see below).

6.1 Query methods/procedures

snooze<%> supplies three query methods that retrieve data in different ways:

6.2 Query syntax

Formally, sql blocks have the syntax below. The parts of the syntax are described in subsequent sections:

(sql top)
top = query
  | order
  | expr
select = 
(select from
from = #:from source
maybe-what = #:what what
  | #:what (what ...)
maybe-where = #:where expr
  | #:where #f
  | #:where ,scheme-expr
maybe-group = #:group (group ...)
  | #:group ,scheme-expr
maybe-having = #:having expr
  | #:having #f
maybe-order = #:order (order ...)
  | #:order ,scheme-expr
maybe-limit = #:limit integer
  | #:limit #f
  | #:limit ,scheme-expr
maybe-offset = #:offset integer
  | #:offset #f
  | #:offset ,scheme-expr
maybe-distinct = #:distinct #t
  | #:distinct expr
  | #:distinct #f
  | #:distinct ,scheme-expr
source = entity-alias
  | query-alias
  | query
  | (outer source source)
  | (inner source source expr)
  | (left  source source expr)
  | (right source source expr)
  | ,scheme-expr
what = expr
  | entity-alias
  | query-alias
  | ,scheme-expr
group = expr
  | entity-alias
  | query-alias
  | ,scheme-expr
expr = (or expr ...)
  | (and expr ...)
  | ...
  | attribute-alias
  | expression-alias
  | literal
  | ,scheme-expr
order = (asc column)
  | (desc column)
  | ,scheme-expr