6 Queries

SQL on Wikipedia

Snooze uses a combinator-based query language that supports the most useful bits of SQL including joins, nested queries, aggregate functions and limits and offsets.

Procedures in the query language are prefixed with sql: to distinguish them from standard Scheme operators. Snooze also provides a syntax layer that removes the need for these prefixes.

6.1 Query 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.

• Working with generators

  g:find is like find-all but returns a generator of the results found.

All of these procedures take a select statement as an argument. These statements are created using the Snooze query language, a combinator library and syntax layer that mirrors SQL in Scheme.

6.2 Working with generators

Generators are a lightweight iteration mechanism similar to the ports of R5RS Scheme and the streams of SRFI 40. They form a convenient, lightweight way of iterating through and modifying large datasets such as those returned by Snooze’s g:find procedure.

Generators are defined in the Unlib package on PLaneT, and are reprovided by "snooze.ss". See the documentation in Unlib for more information: (part ("(planet gen.ss (untyped unlib.plt 3 11))" "top")).

6.3 Procedural query language

Snooze provides two ways of writing queries:

• a combinator-based procedural query language, described below;

• a thin syntax wrapper that provides a layer of convenience on top of the procedural language.

This section describes the procedural language.

6.3.1 Aliases

To create a query, you first need to alias one or more entities and attributes. This is analogous to the "AS" operator from SQL: by aliasing a single item more than once, you can refer to it in more than one context without ambiguity. While SQL allows you to omit aliases where you are only referring to an item once, Snooze requires you to be explicit at all times.

(sql:alias id entity) → entity-alias?

id : symbol?

entity : entity?

(sql:alias id query) → query-alias?

id : symbol?

query : query?

(sql:alias id expr) → expression-alias?

id : symbol?

expr : expression?

(sql:alias entity attr) → attribute-alias?

entity : entity-alias?

attr : (U attribute? symbol?)

Creates an alias for the supplied entity, attribute, SQL query, or SQL expression. Aliases defined with this procedure can only be used with the procedural version of the query language.

(let-alias ([id datum] ...) expr ...)

Syntax wrapper for sql:alias that expands into a let block that binds ids to aliases for each datum.

Each datum can be an entity, attribute, query, expression as decribed above, or it can be the name of a persistent struct. In this last case, let-alias binds identifiers for aliases for the corresponding entity and all of its attributes.

Examples:

> (define-persistent-struct person       ([name type:string] [age type:integer]))

> (let-alias ([P person])       (pretty-print (list P P-id P-age)))

(#(struct:entity-alias      P      #<entity:person>)    #(struct:attribute-alias      #(struct:integer-type #t #f)      P-id      #(struct:entity-alias        P        #<entity:person>)      #<attr:person-id>)    #(struct:attribute-alias      #(struct:integer-type #t #f)      P-age      #(struct:entity-alias        P        #<entity:person>)      #<attr:person-age>))

(define-alias id datum)

Version of let-alias that expands into a define statement.

6.3.2 Select statements

(sql:select   #:from from            [ #:what what             #:where where             #:order order             #:limit limit             #:offset offset             #:group group             #:distinct distinct])   →   query?

from : (U source? query?)

what   :   (U expression? source-alias? (listof (U expression? source-alias?)) #f)       =   #f

where : (U expression? #f) = #f

order : (listof order?) = null

limit : (U integer? #f) = #f

offset : (U integer? #f) = #f

group : (listof (U column? source-alias?)) = null

distinct : (U expression? #t #f) = #f

Creates a query that can be used on find-all, find-one and g:find. The arguments are as follows:

The #:from argument, like the FROM clause in SQL, determines the source of the results.

The #:what argument determines the data to be selected. It is analogous to the field list that goes between the keywords SELECT and "FROM" in SQL. The what clause helps Snooze determine the type of result to return from find-all, find-one or g:find. The default value is derived from the #:from clause.

The optional #:where argument, like the WHERE clause in SQL, allows you to filter the results returned. The default is a pass-all filter.

The optional #:order argument, like the ORDER clause in argument specifies the order in which results should be retrieved. The default is a random order.

The optional #:limit and #:offset, like the LIMIT and OFFSET clauses in SQL, allow you to specify a subset of the results to retrieve. The default values return all results.

The optional #:group argument, like the GROUP BY clause in SQL, lets you group results when using aggregate functions. The default value does no grouping.

The optional #:distinct argument, like the DISTINCT ON clause in SQL, specifies a uniqueness constraint on the results returned:

• #t may be used to indicate that rows should be completely unique (the equivalent of the equal? comparison in Scheme);

• an expression may be used to indicate when two rows are considered equal.

Omitting the #:distinct argument disables uniqueness checking.

6.3.3 #:from clauses

The #:from clause specifies the source of a query’s data. A source can be one of the following:

• an entity alias;

• a alias of another query;

• a join over two other sources;

Joins on Wikipedia

Snooze supports four types of join:

(sql:inner left right on) → source

left : source

right : source

on : expr

Creates an inner join on two sources. The on argument specifies the join criteria.

(sql:left left right on) → source

left : source

right : source

on : expr

Creates a left outer join on two sources. The on argument specifies the join criteria.

(sql:right left right on) → source

left : source

right : source

on : expr

Creates a right outer join on two sources. The on argument specifies the join criteria.

(sql:outer left right) → source

left : source

right : source

Creates a natural join on two sources.

6.3.4 #:what clauses

The #:what clause specifies the type of result to be retrieved from the query. This must be a subset of the information available in the #:from clause. The result type is determined as follows:

• if #:what is an expression or attribute alias, each result is a Scheme value of the corresponding type;

• if #:what is an entity alias, each result is a persistent struct of the corresponding type (#f is used to represent NULL);

• if #:what is a query alias, each result is of the type determined by the query’s what clause;

• if #:what is a list of expressions, entity aliases and query aliases, each result is a list of items of the appropriate types.

If the #:what clause is omitted, Snooze infers it from the contents of the #:from clause:

• if #:from is a single entity, each result is a persistent struct of that type;

• if #:from is a query, each result is of the type determined by the query’s what clause;

• if #:from is a join, each result is a flattened list of the results determined by the arguments of the join.

For example, the following code would return a list of results of type (list person string integer):

(define-alias P1 person)

(define-alias P2 person)

(find-all (sql:select #:what (list P1 P1-name (sql:max P2-age))

#:from ; ...))

6.3.5 Expressions

Various clauses, such as #:where and #:having and the conditions of the various joins, are based on expressions involving functions and attribute aliases.

The expression language, which is a subset of the query language, is described below. These forms are essentially wrappers for SQL expressions, so SQL semantics prevail over Scheme semantics.

6.3.5.1 Expression types

Each expression has a type, which is one of the following:

• boolean

• numeric, with integer and real subtypes;

• character, with string and symbol subtypes;

• temporal, with time-utc and time-tai subtypes.

The procedures below all operate on arguments that are expressions. The type of the resulting expression is determined by the types of its arguments. If literal Scheme values are supplied as arguments, they are automatically quoted to literal expressions of the relevant type.

6.3.5.2 Boolean operators

These functions operate on boolean arguments and produce boolean results:

(sql:or arg ...) → function?

arg : expr+quotable

The Boolean OR of the arguments.

(sql:and arg ...) → function?

arg : expr+quotable

The Boolean AND of the arguments.

(sql:not arg) → function?

arg : expr+quotable

The negation of the argument.

6.3.5.3 Simple comparison functions

These functions operate on numeric, boolean, string, symbolic or temporal arguments, as long as consistent types are used. For example, (sql:= "str" 'str) is a valid comparison whereas (sql:= "str" 123) is not. The result type is always boolean.

(sql:= arg1 arg2) → function?

arg1 : expr+quotable

arg2 : expr+quotable

The equivalent of Scheme’s equal?.

(sql:<> arg1 arg2) → function?

arg1 : expr+quotable

arg2 : expr+quotable

The negation of sql:=.

(sql:< arg1 arg2) → function?

arg1 : expr+quotable

arg2 : expr+quotable

(sql:> arg1 arg2) → function?

arg1 : expr+quotable

arg2 : expr+quotable

(sql:<= arg1 arg2) → function?

arg1 : expr+quotable

arg2 : expr+quotable

(sql:>= arg1 arg2) → function?

arg1 : expr+quotable

arg2 : expr+quotable

6.3.5.4 Mathematical functions

The following operate on arguments of numeric or temporal types and return a result of the relevant type:

(sql:+ arg1 ...) → function?

arg1 : expr+quotable

(sql:- arg1 ...) → function?

arg1 : expr+quotable

The following operate on arguments of numeric types only:

(sql:* arg1 ...) → function?

arg1 : expr+quotable

(sql:/ arg1 arg2) → function?

arg1 : expr+quotable

arg2 : expr+quotable

(sql:abs arg) → function?

arg : expr+quotable

(sql:floor arg) → function?

arg : expr+quotable

(sql:ceiling arg) → function?

arg : expr+quotable

(sql:round arg) → function?

arg : expr+quotable

6.3.5.5 String and pattern matching functions

Pattern matching in PostgreSQL

These functions operate on character arguments and return character results:

(sql:like str pattern) → function?

str : expression?

pattern : expression?

LIKE in PostgreSQL

The SQL LIKE function. Operates on character arguments and returns a boolean result. The second argument is the pattern to match against.

(sql:regexp-match str pattern) → function?

str : expression?

pattern : expression?

POSIX Regular Expressions in PostgreSQL

POSIX regular expression pattern matching. Operators on character arguments and returns a boolean result. The second argument is the regular expression to match against.

(sql:regexp-match-ci str pattern) → function?

str : expression?

pattern : expression?

Case insensitive version of sql:regexp-match.

(sql:string-append arg1 ...) → function?

arg1 : expression?

Concatenates the arguments. Operates on character arguments and returns a result of the same type.

(sql:string-replace   haystack             needle             replacement)   →   function?

haystack : expression?

needle : expression?

replacement : expression?

Searches for needle in haystack and replaces all occurrences with replacement. Operates on character arguments and returns a result of the same type.

(sql:regexp-replace   haystack             pattern             replacement)   →   function?

haystack : expression?

pattern : expression?

replacement : expression?

Searches for the POSIX regular expression pattern in haystack and replaces the first occurrence with replacement. Operates on character arguments and returns a result of the same type.

(sql:regexp-replace*   haystack             pattern             replacement)   →   function?

haystack : expression?

pattern : expression?

replacement : expression?

Like sql:regexp-replace but replaces all occurrences of pattern rather than just the first.

(sql:regexp-replace-ci   haystack             pattern             replacement)   →   function?

haystack : expression?

pattern : expression?

replacement : expression?

Case insensitive version of sql:regexp-replace.

(sql:regexp-replace*-ci   haystack             pattern             replacement)   →   function?

haystack : expression?

pattern : expression?

replacement : expression?

Case insensitive version of sql:regexp-replace*.

(sql:->string datum format-string) → function?

datum : expr+quotable

format-string : expr+quotable

String formatting functions in PostgreSQL

Creates a TO_CHAR function that converts any data type to a string. format-string must be a string-valued expression that specifies the format to use in the conversion (see the PostgreSQL documentation for examples).

(sql:->symbol datum format-string) → function?

datum : expr+quotable

format-string : expr+quotable

Like sql:->string but returns a function of symbol type.

6.3.5.6 Conditional functions

(sql:if test then [else]) → function?

test : expr+quotable?

then : expr+quotable?

else : (U expr+quotable? #f) = #f

Creates a function that performs an if-then-else test on its arguments. test must be boolean valued. If test evaluates to #t, the value of then is calculated and returned. If test evaluates to #f, the value of else is calucated and returned instead. If test evaluates to #f and else is omitted, NULL is returned.

then and else must have the same type: the return value is a function of the same type.

(sql:cond clause ...)

clause   =   [test-expr value-expr]     |   [else      value-expr]

cond-like syntax that expands into a chain of calls to sql:if. All value-exprs must have the same types.

6.3.5.7 Functions related to NULL

(sql:null? arg) → function?

arg : expr+quotable?

IS NULL in PostgreSQL

Creates a function that determines whether the argument is NULL. Direct comparison with NULL using sql:= does not work because in SQL semantics NULL is not equal to itself.

(sql:coalesce arg ...) → function?

arg : expr+quotable?

Creates a function that returns the value of the leftmost non-NULL argument. All arguments must be of compatible types.

6.3.5.8 IN functions

(sql:in needle haystack) → function?

needle : expr+quotable

haystack : (U (listof quotable) query?)

Creates an SQL IN function that searches for needle in haystack and returns #t if it is found or #f otherwise.

haystack is a rather non-standard type of function argument. It can be one of the following:

• a list of Scheme literals that can be quoted as literal expressions;

• a query that selects a single expression or attribute from a source.

6.3.5.9 Aggregate functions

Aggregate functions in PostgreSQL

Snooze supports the four most common SQL aggregate functions applied to individual columns: COUNT, MAX, MIN and AVERAGE. There is also a variant of COUNT that can be applied to whole entities and subqueries.

There are many subtleties to the use of aggregate functions in SQL that Snooze does not concern itself with. For example, different DBMSs support different types of argument for each aggregate function. If your DBMS rejects a query for any reason, Snooze will raise exn:fail and provide you with the error message.

(sql:count column) → aggregate?

column : column?

Creates an aggregate function that returns the number of non-null values in the specified column.

(sql:max column) → aggregate?

column : column?

Creates an aggregate function that returns the maximum value in the specified column.

(sql:min column) → aggregate?

column : column?

Creates an aggregate function that returns the minimum value in the specified column.

(sql:average column) → aggregate?

column : column?

Creates an aggregate function that returns the mean value in the specified column.

(sql:count* [arg]) → aggregate?

arg : (U source? #f) = #f

Creates an aggregate function that returns the number of non-null rows from the specified entity or subquery (equivalent to the SQL "COUNT(foo.*)"). If arg is omitted or #f, the function returns the number of non-null rows from the current query (equivalent to the SQL "COUNT(*)").

6.3.6 #:order clauses

The #:order clause specifies the order in which results should be returned as a list of order terms constructed with the procedures below.

Terms are listed in descending order of precedence. The SQL-97 standard dictates that aliased expression terms must appear in the #:what clause:

(sql:order column dir) → order?

column : column?

dir : (U 'asc 'desc)

Sorts by column in ascending or descending order.

(sql:asc column) → order?

column : column?

Short-hand for (sql:order item 'asc).

(sql:desc column) → order?

column : column?

Short-hand for (sql:order item 'desc).

6.3.7 #:group and #:having clauses

GROUP in PostgreSQL

SQL provides a mechanism for grouping and filtering the results of queries that involve aggregate functions. The semantics are confusing, unintuitive, and outside the scope of this manual. Snooze provides access to these features (for hardier programmers) via the #:group and #:having clauses:

The #:group clause is a list of the columns by which results should be grouped. Entity and query aliases may also be used: if so, they are expanded into their constituent columns.

The #:having clause is like the #:where clause but eliminates groups from the query results.

6.4 Syntax query language

Mirrors.plt on PLaneT

Snooze provides a syntax wrapper for the procedural query language. This looks and behaves in the same way as the various languages in the Mirrors package.

Informally, you can convert a procedural query to a syntax one by wrapping it in an (sql ...) form and removing the sql: prefixes from each term. Use unquote to switch back into Scheme at any point.

Formally, the sql macro has the following syntax. Attribute, expression, query and entity aliases must be defined with define-alias or let-alias:

(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

6.5 Underlying query structures

(require (planet untyped/snooze:2/sql/sql-struct))

The sql:foo procedures described above create and return structures of a number of types, listed for completeness below.

It is rare that an application programmer has to interact directly with these structures: most of the forms below not provided via (planet untyped/snooze).

(struct   query  ( what         distinct         from         where         group         order         having         limit         offset         local-columns         imported-columns         extract-info))

what : (listof column?)

distinct : (U expression? #t #f)

from : source?

where : (U expression? #f)

group : (listof expression?)

order : (listof order?)

having : (U expression? #f)

limit : (U integer? #f)

offset : (U integer? #f)

local-columns : (listof column?)

imported-columns : (listof column?)

extract-info : (U entity? type? (listof (U entity? type?)))

A query or subquery: the result of an sql:select expression or equivalent. local-columns, imported-columns and extract-info are used internally within Snooze.

The following are used in #:from clauses and as shortcuts in #:what and #:group clauses:

(struct source ())

A query data source.

(struct (source-alias source) (name value))

name : symbol?

value : (U entity? query?)

{ An alias of an entity or subquery.}

(struct (entity-alias source-alias) ())

An alias for an entity.

(struct (query-alias source-alias) ())

An alias for a subquery.

(struct (join source) (op left right on))

op : (U 'inner 'outer 'left 'right)

left : source?

right : source?

on : (U expression? #f)

A join over two other sources. on is required if op is 'inner, 'left or 'right, and forbidden if op is 'outer.

The following represent expressions and columns used in #:what, #:where and #:having clauses and the conditions in inner, left and right joins:

(struct expression (type))

type : type?

An expression used in a #:where or #:having statement or a join condition.

(struct (column expression) (name))

name : symbol?

A named column in a #:what or #:group clause.

(struct (attribute-alias column) (entity attribute))

entity : entity-alias?

attribute : attribute?

A column containing the value of an ERA attribute.

(struct (expression-alias column) (value))

value : expression?

A column containing the result of an SQL expression.

(struct (function expression) (op args))

op : symbol?

args : (listof (U expression? special-argument))

An SQL operator (e.g. +) or function call (e.g. floor or count).

Most functions take other expressions as arguments. Some special functions such as "sql:in" and sql:count* have arguments of special types.

(struct (aggregate function) ())

An aggregate function call.

(struct (literal expression) (value))

value : any

A literal value. The interpretation of value depends on the expression type.

(struct order (expression direction))

expression : expression?

direction : (U 'asc 'desc)

A term in an #:order statement.