Introduction

This extension provides procedures for constructing SQL queries from S-expressions.

No support for actually accessing a database is provided so this extension is meant to be used together with other extensions like postgresql or mysql.

Notes

• sql.egg is incomplete. It mostly contains what I've needed so far :)
• sql.egg has only been used with PostgreSQL so far.

NULL object

procedure: (sql:null)

Returns the NULL object

procedure: (sql:null? X)

Returns #t if X is the NULL object, #f otherwise.

These functions can be redefined to recognize the NULL object of the database that is in use, for example:

(require-extension postgresql sql)
(define (sql:null) pg:sql-null-object)
(define sql:null? pg:sql-null-object?)

'() is also recognized as NULL by the sql egg, no matter how sql:null? is defined.

Quoting strings

procedure: (sql:quote S)

Returns a copy of S with ' replaced by '' and \ replaced by \\.

Transforming S-expressions to SQL

procedure: (sql:transform EXPR)

Returns the S-expression EXPR converted to SQL syntax as a list. The following transformation rules exist:

(and)	->	TRUE
(and x ...)	->	(x AND ...)
(or)	->	FALSE
(or x ...)	->	(x OR ...)
(not x)	->	(NOT x)
(null? x)	->	(x IS NULL)
(= x (sql:null))	->	(x IS NULL)
(binary-operator x1 x2)	->	(x1 op x2)
(n-ary-operator x ...)	->	(x op ...)
(-> type x)	->	(x::type)
(as x alias)	->	x AS alias
(asc x)	->	x ASC
(desc x)	->	x DESC
(extract f s)	->	EXTRACT(f FROM s)
(substring s start count)	->	SUBSTRING(s FROM start FOR count)
(string-append x ...)	->	(x || ...)
(bitwise-and x ...)	->	(x & ...)
(bitwise-ior x ...)	->	(x | ...)
(bitwise-xor x ...)	->	(x # ...)
(bitwise-not x)	->	(~x)
(join/on type a b on)	->	(a type JOIN b ON on)
(join/using type a b (using1 u2 ...))	->	(a type JOIN b USING (using1,u2,...))
(join/natural type a b)	->	(a NATURAL type JOIN b)
(func x ...)	->	func(x,...)
string	->	'(quoted-string)'
#t	->	TRUE
#f	->	FALSE
(sql:null)	->	NULL
()	->	NULL

procedure: (sql:list->string L)

Returns the elements of L concatenated as a string.

parameter: sql:binary-operators

A list of binary operators recognized by sql:transform, by default '(< > <= >= = <> != << >> like).

parameter: sql:n-ary-operators

A list of n-ary operators recognized by sql:transform, by default '(+ - * /).

Utility functions for generating common SQL queries

procedure: (sql:select WHAT #!key FROM WHERE GROUP-BY HAVING ORDER-BY LIMIT OFFSET DISTINCT)

Returns a SQL SELECT-query as a string. WHAT, FROM, GROUP-BY and ORDER-BY are lists of S-expressions. WHERE, HAVING, LIMIT and OFFSET are S-expressions. DISTINCT is a boolean. All keyword arguments are optional.

procedure: (sql:insert TABLE VALUES)

Returns a SQL INSERT-query as a string. TABLE is a string or symbol naming the table to receive the data. VALUES is an alist mapping column names (symbols) to values (S-expressions)

procedure: (sql:delete TABLE WHERE)

Returns a SQL DELETE-query as a string. TABLE is a string or symbol naming the table to modify. WHERE is either an S-expression specifying the rows to delete or #f.

procedure: (sql:update TABLE UPDATES WHERE)

Returns a SQL UPDATE-query as a string. TABLE is a string or symbol naming the table to modify. UPDATES is an alist mapping column names (symbols) to values (S-expressions), WHERE is either an S-expression specifying the rows to modify or #f.