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

6 Queries

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
        maybe-what
        maybe-where
        maybe-group
        maybe-having
        maybe-order
        maybe-limit
        maybe-offset
        maybe-distinct)
     
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