6.2.9 Procedural query layer
The query language described in the previous sections is a
syntax wrapper for a set of constructors for specially
designed SQL AST nodes.
Code that uses the constructors is generally more verbose than
code that uses the syntax query language. However, in some cases
it is useful to have access to standard Scheme procedures such
as apply and map when building queries.
The elements of the procedural language are listed below
for completeness. See the documentation for the syntax wrappers
for more information.
6.2.9.1 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 |
6.2.9.2 From clauses
(sql:inner left right on) → source? |
left : source? |
right : source? |
on : expr? |
(sql:left left right on) → source? |
left : source? |
right : source? |
on : expr? |
(sql:right left right on) → source? |
left : source? |
right : source? |
on : expr? |
(sql:outer left right) → source? |
left : source? |
right : source? |
6.2.9.3 Expressions (where/on/having clauses)
(sql:or arg ...) → function? |
arg : expr+quotable? |
(sql:and arg ...) → function? |
arg : expr+quotable? |
(sql:not arg) → function? |
arg : 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? |
(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 ...) → function? |
arg1 : expr+quotable? |
(sql:- arg1 ...) → function? |
arg1 : expr+quotable? |
(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? |
(sql:like str pattern) → function? |
str : expression? |
pattern : expression? |
(sql:regexp-match str pattern) → function? |
str : expression? |
pattern : expression? |
(sql:regexp-match-ci str pattern) → function? |
str : expression? |
pattern : expression? |
(sql:string-append arg1 ...) → function? |
arg1 : expression? |
(sql:string-replace | | haystack | | | | | | | needle | | | | | | | replacement) | | → | | function? |
|
haystack : expression? |
needle : expression? |
replacement : expression? |
(sql:regexp-replace | | haystack | | | | | | | pattern | | | | | | | replacement) | | → | | function? |
|
haystack : expression? |
pattern : expression? |
replacement : expression? |
(sql:regexp-replace* | | haystack | | | | | | | pattern | | | | | | | replacement) | | → | | function? |
|
haystack : expression? |
pattern : expression? |
replacement : expression? |
(sql:regexp-replace-ci | | haystack | | | | | | | pattern | | | | | | | replacement) | | → | | function? |
|
haystack : expression? |
pattern : expression? |
replacement : expression? |
(sql:regexp-replace*-ci | | haystack | | | | | | | pattern | | | | | | | replacement) | | → | | function? |
|
haystack : expression? |
pattern : expression? |
replacement : expression? |
(sql:->string datum format-string) → function? |
datum : expr+quotable? |
format-string : expr+quotable? |
(sql:->symbol datum format-string) → function? |
datum : expr+quotable? |
format-string : expr+quotable? |
(sql:if test then [else]) → function? |
test : expr+quotable? |
then : expr+quotable? |
else : (U expr+quotable? #f) = #f |
(sql:cond clause ...) |
|
clause | | = | | [test-expr value-expr] | | | | | | [else value-expr] |
|
(sql:null? arg) → function? |
arg : expr+quotable? |
(sql:coalesce arg ...) → function? |
arg : expr+quotable? |
(sql:in needle haystack) → function? |
needle : expr+quotable? |
haystack : (U (listof quotable?) query?) |
(sql:count column) → aggregate? |
column : column? |
(sql:max column) → aggregate? |
column : column? |
(sql:min column) → aggregate? |
column : column? |
(sql:average column) → aggregate? |
column : column? |
(sql:count* [arg]) → aggregate? |
arg : (U source? #f) = #f |
6.2.9.4 Order clauses
(sql:order column dir) → order? |
column : column? |
dir : (U 'asc 'desc) |
(sql:asc column) → order? |
column : column? |
(sql:desc column) → order? |
column : column? |
6.2.9.5 Aliases
(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?) |
6.2.9.6 Underlying data structures
(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?))) |
(struct (source-alias source) (name value)) |
name : symbol? |
value : (U entity? query?) |
(struct (entity-alias source-alias) ()) |
(struct (query-alias source-alias) ()) |
(struct (join source) (op left right on)) |
op : (U 'inner 'outer 'left 'right) |
left : source? |
right : source? |
on : (U expression? #f) |
(struct expression (type)) |
type : type? |
(struct (column expression) (name)) |
name : symbol? |
(struct (attribute-alias column) (entity attribute)) |
entity : entity-alias? |
attribute : attribute? |
(struct (expression-alias column) (value)) |
value : expression? |
(struct (function expression) (op args)) |
op : symbol? |
args : (listof (U expression? special-argument)) |
(struct (aggregate function) ()) |
(struct (literal expression) (value)) |
value : any |
(struct order (expression direction)) |
expression : expression? |
direction : (U 'asc 'desc) |