Version: 4.1.0.4

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

        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