►Snooze: Object Relational Mapping of PLT Structures
►6 Queries
▼6.2 Query syntax
►6.2.9 Procedural query layer
On this page:
6.2.9.1 Select statements
sql: select
6.2.9.2 From clauses
sql: inner
sql: left
sql: right
sql: outer
6.2.9.3 Expressions (where/ on/ having clauses)
sql: or
sql: and
sql: not
sql: =
sql: <>
sql: <
sql: >
sql: <=
sql: >=
sql: +
sql: -
sql: *
sql: /
sql: abs
sql: floor
sql: ceiling
sql: round
sql: like
sql: regexp-match
sql: regexp-match-ci
sql: string-append
sql: string-replace
sql: regexp-replace
sql: regexp-replace*
sql: regexp-replace-ci
sql: regexp-replace*-ci
sql: ->string
sql: ->symbol
sql: if
sql: cond
sql: null?
sql: coalesce
sql: in
sql: count
sql: max
sql: min
sql: average
sql: count*
6.2.9.4 Order clauses
sql: order
sql: asc
sql: desc
6.2.9.5 Aliases
sql: alias
6.2.9.6 Underlying data structures
query
source
source-alias
entity-alias
query-alias
join
expression
column
attribute-alias
expression-alias
function
aggregate
literal
order
  ← prev  up  next → 
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 ())

(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)

  ← prev  up  next →