6 Queries

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:

• find-all retrieves a list of all matching results from the database.

• find-one is like find-all but returns the first result found. If no results are found, returns #f instead.

• (part ("(planet gen.ss (untyped unlib.plt 3 23))" "top"))

  g:find is like find-all but returns a result generator.

  Generators, defined in Unlib, are a lightweight iteration mechanism for traversing and modifying large datasets where menory consumption is a concern. Unlib provides useful combinators for manipulating generated data, including memory-efficient equivalents of fold, map and for-each.

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