This library provides an S-expression notation for a subset of SQL. It provides forms that produce statements (as opaque values rather than strings) that can be used directly with Racket’s db library. It also provides macros and functions for creating and manipulating SQL ASTs.

1Using the SQL Library🔗 i

This library complements the db library. The database library manages connecting to databases and executing queries; this library helps construction of the queries to execute.

We’ll start by going through the examples Introduction to Using Database Connections using this library’s SQL notation instead.

> (require sql db)
> (define pgc....)

First we create a temporary table to play around with:

> (query-exec pgc
(create-table #:temporarythe_numbers
#:columns[ninteger#:not-null][dvarchar]))

> (query-exec pgc
(insert #:intothe_numbers#:set[n0][d"nothing"]))

Let’s take a look at the statements that just went by:

> (create-table #:temporarythe_numbers
#:columns[ninteger#:not-null][dvarchar])

(sql-statement "... not in current dialect ...")
> (insert #:intothe_numbers#:set[n0][d"nothing"])

(sql-statement "INSERT INTO the_numbers (n, d) VALUES (0, 'nothing')")

Now let’s add another row, using “computed” values rather than literals. We can use unquote (or ,) in a scalar expression position to insert a Racket value:

> (define n11)
> (define d1"the loneliest number")

> (query-exec pgc
(insert #:intothe_numbers#:set[n,n1][d,d1]))

Let’s look at that last statement:

> (insert #:intothe_numbers#:set[n,n1][d,d1])

(sql-statement

"INSERT INTO the_numbers (n, d) VALUES (?, ?)"

1

"the loneliest number")

The unquote d expressions turned into parameter placeholders, and the statement stores their values separately. Strangely, the placeholders appear as ?, and PostgreSQL doesn’t understand ? placeholders; they should have been 1ドル and 2ドル. But the statement seems to have worked. What’s going on?

We need to set the interactive printing dialect to PostgreSQL. This has no effect on the query operations; they set the dialect independently based on the database connection.

> (parameterize ((current-sql-dialect 'postgresql))
(print (insert #:intothe_numbers#:set[n,n1][d,d1])))

(sql-statement "INSERT INTO the_numbers (n, d) VALUES (1,ドル 2ドル)" 1 "the loneliest number")

And now we see 1ドル and 2ドル as expected.

We can introduce placeholders explicitly (although unquote is usually more convenient). An explicit placeholder is written ?, regardless of the dialect. The parameters are given in the query call as usual:

> (query-exec pgc
(insert #:intothe_numbers#:set[n?][d?])
(+ 11)"company")

It is not currently possible to mix explicit placeholders and unquote parameters:

> (query-exec pgc
(insert #:intothe_numbers#:set[n,3][d?])
"a crowd")

eval:2:0: insert: cannot use both placeholders and unquoted

values

in: (insert #:into the_numbers #:set (n (unquote 3)) (d

?))

You can, of course, mix constant literals and unquote s (or placeholders).

> (query-exec pgc
(insert #:intothe_numbers#:set[n3][d,"a crowd"]))

SELECT statements are constructed similarly, and they follow the same rules regarding parameters. The statements work the same with all of the query operations.

> (query pgc
(select nd#:fromthe_numbers#:where(= (%n2)0)))

(rows-result

'(((name . "n") (typeid . 23) (type-size . 4) (type-mod . -1))

((name . "d") (typeid . 1043) (type-size . -1) (type-mod . -1)))

'(#(0 "nothing") #(2 "company")))

> (query-rows pgc
(select nd#:fromthe_numbers#:where(= (+ nn)(* nn))))

'(#(0 "nothing") #(2 "company"))

> (query-row pgc
(select nd#:fromthe_numbers#:where(< n1)))

'#(0 "nothing")

> (query-list pgc
(select d#:fromthe_numbers#:where(= 0(%n2))))

'("nothing" "company")

> (query-value pgc
(select (string_aggd", ")#:fromthe_numbers#:where(= 0(%n2))))

"nothing, company"

There are S-expression notations for many common SQL operators and expression forms. See SQL Scalar Expressions for details.

The rest of this manual uses the default SQL1992 dialect for printing results:

> (current-sql-dialect #f)

2Statement Forms🔗 i

The macros in this section create statement values suitable for passing to the query functions of the db library. These statement values satisfy the db library’s statement? predicate. They are different from the statement ASTs produced by statement-qq .

The printing of a statement value is controlled by (current-sql-dialect ), but the code it generates when passed to a query function is determined by the dialect of the connection the query is performed on.

syntax
( sql statement)
(sql ddl-statement)

Produces a statement value that can be passed to a db query function. The syntax corresponds to the syntax of the statement or ddl-statement nonterminals from S-expression Syntax for SQL.

Examples:

> (sql (select abc#:frommytable#:where(> a10)))

(sql-statement "SELECT a, b, c FROM mytable WHERE (a > 10)")
> (sql (insert #:intomytable#:set[a1][b2][c3]))

(sql-statement "INSERT INTO mytable (a, b, c) VALUES (1, 2, 3)")

> (sql (create-table numbers
#:columns[ninteger#:not-null][ttext]
#:constraints(primary-keyn)))

(sql-statement

"CREATE TABLE numbers (n integer NOT NULL, t text, PRIMARY KEY (n))")

syntax
( select select-item...select-clause...)
(select select-clause...)

syntax
( insert #:intotable-nameassign-clausemaybe-conflict)
(insert #:intotable-namemaybe-columns#:fromtable-exprmaybe-conflict)

syntax
( update table-nameassign-clausemaybe-where)

syntax
( delete #:fromtable-namemaybe-where)

Like sql , but specialized to the syntax of the select-statement, insert-statement, update-statement, and delete-statement nonterminals from S-expression Syntax for SQL, respectively.

Examples:

> (select abc#:frommytable#:where(> a10))

(sql-statement "SELECT a, b, c FROM mytable WHERE (a > 10)")
> (insert #:intomytable#:set[a1][b2][c3])

(sql-statement "INSERT INTO mytable (a, b, c) VALUES (1, 2, 3)")

> (insert #:intomytable
#:from(select abc
#:fromother_table
#:where(is-not-nulld)))

(sql-statement

"INSERT INTO mytable SELECT a, b, c FROM other_table WHERE (d IS NOT NULL)")

Equivalent to

(sql (selectselect-item... select-clause... ))
(sql (insert#:intotable-nameassign-clause))

and so forth.

Changed in version 1.1 of package sql: Added #:or-ignore for insert .

syntax
( create-table maybe-tempmaybe-ifnotexiststable-name
#:columnscolumn-def...
maybe-constraints)

(create-table maybe-tempmaybe-ifnotexiststable-name
#:asstatement)

syntax
( create-view maybe-tempview-name
statement)

Like sql , but specialized to the syntax of the DDL create-table-statement and create-view-statement, respectively.

Example:

> (create-table numbers
#:columns[ninteger#:not-null][ttext]
#:constraints(primary-keyn))

(sql-statement

"CREATE TABLE numbers (n integer NOT NULL, t text, PRIMARY KEY (n))")

Equivalent to

(sql (create-tablemaybe-temptable-name
#:columnscolumn-def...
maybe-constraints))

and so forth.

Changed in version 1.1 of package sql: Added #:if-not-exists option.

procedure
( sql-statement? v)→boolean?
v:any/c

Returns #t if v is a statement value returned by one of the forms in this section such as select , #f otherwise.

procedure
( sql-statement->string statement[dialect])→string?
statement:sql-statement?
dialect:(or/c symbol? #f)=(current-sql-dialect )

Produces SQL code as a string for the given statement according to the rules of dialect.

3S-expression Syntax for SQL🔗 i

This section describes this library’s S-expression syntax for a subset of SQL. The SQL support is organized by nonterminals (eg statement, table-ref, and scalar-expr); the grammar handled by this library is adapted from A Guide to the SQL Standard, 4th ed. by C. J. Date and Hugh Darwen. Each non-terminal has the following:

an S-expression syntax,
an AST type predicate, and
a quasiquotation macro to produce AST values from the S-expression syntax.

All literals are recognized symbolically, rather than by identifier binding, to avoid cluttering the namespace. The AST type representations are not considered public; they are likely to change in future versions of this library.

procedure
( sql-ast->string ast[dialect])→string?

ast :
(or/c name-ast? scalar-expr-ast? table-expr-ast?
table-ref-ast? statement-ast? ddl-ast? )
dialect:(or/c symbol? #f)=(current-sql-dialect )

Produces SQL code as a string for the given AST to a string according to the rules of dialect. Examples are given throughout the following sections.

3.1SQL Names and Identifiers🔗 i

A name is either an unqualified identifier or an identifier qualified with another name, which depending on its usage might represent a catalog, schema, table, range variable, etc.

Concrete SQL has both unquoted and quoted identifiers. Different SQL environments (eg, database backends) have different restrictions on unquoted identifiers, regarding illegal characters and reserved words. Most (but not all) systems also apply some case-folding rule to unquoted identifiers (eg, PostgreSQL converts to lowercase, some others convert to uppercase).

Similarly, this library has both “tagged” and “untagged” notations for identifiers and names. Untagged identifiers are written as raw symbols; they are short and convenient, but they run the risk of confusion with operators and special symbols used by this library. Examples of special symbols include select, as, and from. Examples of identifiers containing operator characters include hello-goodbye and first/last. These identifiers must be written in tagged form.

ident = symbol

| (Ident:string)

| (Ident:symbol)

name = symbol

| ident

| (Name:name...+)

(Ident:symbol)
Unquoted if possible; case-folded and quoted according the SQL dialect’s rules if symbol is a reserved word or contains illegal characters.
(Ident:MyTable);MyTable
(Ident:Select);"SELECT"
(Ident:a+b.c);"a+b.c"

(Ident:string)
Always quoted without case-folding.
(Ident:"MyTable");"MyTable"
(Ident:"Select");"Select"
(Ident:"x1.$!!");"x1.$!!"

(Name:name...+)
Qualified name; each name except the last qualifies the name to its right.
(Name:xyz);x.y.z
(Name:xy.z);x.y.z
(Name:x(Ident:y.z));x."y.z"

symbol
Must not be a special symbol; otherwise an error is raised.
Equivalent to (Ident:symbol) if symbol contains no dot (.) characters and matches the pattern #px"^(?:\\p{L}|_)(?:\\p{L}|\\p{N}|[_$])*$"—that is, a letter or underscore followed by zero or more letters, numbers, underscores, and dollar signs.
If symbol consists of dot-separated parts satisfying the rule above, it is equivalent to (Name:part... ).
MyTable;MyTable
x.y.z;x.y.z
x.select.as;x."SELECT"."AS"

Because case-folding behavior is system-dependent, it is wisest to either always quote a given name or never quote it.

syntax
( ident-qq ident)

procedure
( ident-ast? v)→boolean?
v:any/c

Quasiquotation macro and predicate, respectively, for ident.

Examples:

> (sql-ast->string (ident-qq MyTable))

"MyTable"
> (sql-ast->string (ident-qq (Ident:MyTable)))

"MyTable"
> (sql-ast->string (ident-qq (Ident:"MyTable")))

"\"MyTable\""
> (sql-ast->string (ident-qq Select))

"\"SELECT\""
> (sql-ast->string (ident-qq (Ident:Select)))

"\"SELECT\""
> (sql-ast->string (ident-qq (Ident:"Select")))

"\"Select\""
> (sql-ast->string (ident-qq (Ident:a+b.c)))

"\"A+B.C\""
> (sql-ast->string (ident-qq select ))

eval:17:0: ident-qq: special symbol cannot be used as

untagged identifier

at: select

in: (ident-qq select)

parsing context:

while parsing Ident

term: select

location: eval:17:0
> (sql-ast->string (ident-qq a+b.c))

eval:18:0: ident-qq: illegal character in untagged

identifier

at: a+b.c

in: (ident-qq a+b.c)

parsing context:

while parsing Ident

term: a+b.c

location: eval:18:0

syntax
( name-qq name)

procedure
( name-ast? v)→boolean?
v:any/c

Quasiquotation macro and predicate, respectively, for name.

Examples:

> (sql-ast->string (name-qq (Name:xyz)))

"x.y.z"
> (sql-ast->string (name-qq (Name:x.yz)))

"x.y.z"
> (sql-ast->string (name-qq x.y.z))

"x.y.z"
> (sql-ast->string (name-qq x.select.as))

"x.\"SELECT\".\"AS\""

3.2SQL Scalar Expressions🔗 i

A scalar expression is either a name, a literal integer or string value, or an application of some function or operator. Note: not every kind of expression is supported in every SQL dialect.

scalar-expr = name

| exact-integer

| string

| (existstable-expr)

| (inscalar-expr#:fromtable-expr)

| (inscalar-expr#:valuesscalar-expr...)

| (case[scalar-exprscalar-expr]...maybe-else)

| (case#:ofscalar-expr[scalar-exprscalar-expr]...maybe-else)

| (compare-operatorscalar-expr#:sometable-expr)

| (compare-operatorscalar-expr#:alltable-expr)

| (namescalar-expr...)

| table-expr

| (operator/specialscalar-expr...)

| ?

| (unquoteracket-expr)

(existstable-expr)
Produces an EXISTS expression:
(exists(select 1#:fromt));EXISTS (SELECT 1 FROM t)

(inscalar-expr#:fromtable-expr)
(inscalar-expr#:valuesscalar-expr...)
There are two forms of IN expression, one for table expressions and one for lists of scalar expressions:
(inx#:from(select y#:fromys));x IN (SELECT y FROM ys)
(inx#:values123);x IN (1, 2, 3)

(case[scalar-exprscalar-expr]...maybe-else)
(case#:ofscalar-expr[scalar-exprscalar-expr]...maybe-else)
There are two forms of CASE expression, one like Racket’s cond and the other like Racket’s case :
(case [(= x0)"zero"][else "no"]);CASE WHEN x = 0 THEN 'zero' ELSE 'no' END
(case #:ofx[0"zero"][else "no"]);CASE x WHEN 0 THEN 'zero' ELSE 'no' END

(compare-operatorscalar-expr#:sometable-expr)
(compare-operatorscalar-expr#:alltable-expr)
Produces an “all-or-any” comparison between a scalar (or row) expression and a table expression.
(= x#:some(select y#:fromys));x = SOME (SELECT y FROM ys)
(< x#:all(select y#:fromys));x < ALL (select y FROM ys)

(namescalar-expr...)
Represents an ordinary function call; no arity checking is done.
(coalescexyz);coalesce(x, y, z)

table-expr
Represents a subquery; the query must return at most one row.
(select y#:fromys#:where(x= 0));(SELECT y FROM ys WHERE x = 0)

syntax
( scalar-expr-qq scalar-expr)

procedure
( scalar-expr-ast? v)→boolean?
v:any/c

Quasiquotation macro and predicate, respectively, for scalar-expr.

Examples:

> (sql-ast->string (scalar-expr-qq mytable.mycolumn))

"mytable.mycolumn"
> (sql-ast->string (scalar-expr-qq 42))

"42"
> (sql-ast->string (scalar-expr-qq "Salutations"))

"'Salutations'"
> (sql-ast->string (scalar-expr-qq "a 'tricky' string"))

"'a ''tricky'' string'"
> (sql-ast->string (scalar-expr-qq (log (- 1p))))

"log((1 - p))"
> (sql-ast->string (scalar-expr-qq (and (> x10)(< x55))))

"((x > 10) AND (x < 55))"
> (sql-ast->string (scalar-expr-qq (coalescexyz)))

"coalesce(x, y, z)"
> (sql-ast->string (scalar-expr-qq (cast"2015-03-15"DATE)))

"CAST('2015-03-15' AS \"DATE\")"
> (sql-ast->string (scalar-expr-qq (extractYEARdob)))

"EXTRACT(\"YEAR\" FROM dob)"
> (sql-ast->string (scalar-expr-qq (is-nullmytable.mycolumn)))

"(mytable.mycolumn IS NULL)"
> (sql-ast->string (scalar-expr-qq (likeph_num"555-____")))

"(ph_num LIKE '555-____')"
> (sql-ast->string (scalar-expr-qq (||lname", "fname)))

"(lname || ', ' || fname)"

3.2.1Special Scalar Expressions🔗 i

(operator/specialscalar-expr...)
This function-like syntax is used to represent uses of SQL operators, standard SQL functions that don’t use ordinary function-call notation, and a few other special cases, listed below.

The CAST and EXTRACT special functions:
(cast"2015-03-15"DATE);CAST('2015-03-15' AS DATE)
(cast"123"(NUMERIC50));CAST('123' AS NUMERIC(5, 0))
(extractYEARdob);EXTRACT(YEAR FROM dob)
Note that as above, types and fields are written as “scalar expressions”, in a mild abuse of syntax.
The OVERLAY, POSITION, and SUBSTRING functions:
(overlay"abc""z"21);OVERLAY('abc' PLACING 'z' FROM 2 FOR 1)
(position"c""abc");POSITION('c' IN 'abc)
(substring "abc"21);SUBSTRING('abc' FROM 2 FOR 1)
The TRIM function is written using one of the following variants:
(trim-leading"z""zzabc");TRIM(LEADING 'z' FROM 'zzabc')
(trim-trailing"z""abczz");TRIM(TRAILING 'z' FROM 'abczz')
(trim-both"z""zzabczz");TRIM(BOTH 'z' FROM 'zzabczz')
The syntax COUNT(*) can be written as follows:
(count-all);COUNT(*)
The chaining arithmetic operators +, -, *, and /:
(+ 1234);1 + 2 + 3 + 4
The chaining infix logical operators AND and OR:
(and xyz);x AND y AND z
(or xyz);x OR y OR z
The chaining infix binary operator || can be written as \|\| or as ||; the latter reads as the empty symbol.
(||lname", "fname);lname || ', ' || fname
(\|\|lname", "fname);lname || ', ' || fname
Any identifier consisting of only characters in ~!@#%^&*-_=+|<>?/ is considered a non-chaining infix binary operator:
(< xy);x < y
(%&!12);1 %&! 2
The following operators:
(not x);NOT x
(is-nullx);x IS NULL
(is-not-nullx);x IS NOT NULL
(is-truex);x IS TRUE
(is-not-truex);x IS NOT TRUE
(is-falsex);x IS FALSE
(is-not-falsex);x IS NOT FALSE
(is-unknownx);x IS UNKNOWN
(is-not-unknownx);x IS NOT UNKNOWN
(collatexutf8);x COLLATE utf8
(between-and5110);5 BETWEEN 1 AND 10
(distinct-fromxy);x DISTINCT FROM y
(like"abc""a%");'abc' LIKE 'a%'
(ilike"aBC""ab_");'aBC' ILIKE 'ab_'
(similar-to"abc""(a|z)%");'abc' SIMILAR TO '(a|z)%'
Field selection is written as a regular identifier (or *) prefixed by a dot.
(.citystate);(state).city
(.*table1);(table1).*
(.*);*
Row constructors (the ROW syntax is a PostgreSQL extension):
(%row123);(1, 2, 3)
(row123);ROW(1, 2, 3)
Arrays and array indexing (PostgreSQL extension):
(%array123);ARRAY[1, 2, 3]
(%refx1);(x)[1]
(%refx123);(x)[1,2,3]

3.2.2Placeholders and Unquoted Parameters🔗 i

There are two variants of scalar-expr that enable the construction of parameterized queries. The first is the placeholder, written ? (regardless of the notation used by the database the query is to be sent to). The second is the unquote form, which is equivalent to inserting a placeholder and also providing the expression as a query parameter.

scalar-expr = ....

| ?

| (unquoteracket-expr)

Note: Due to limitations in the db library, unquote parameters and ordinary placeholders cannot be mixed in the same statement.

Example:

> (select a#:frommytable#:where(= b?))

(sql-statement "SELECT a FROM mytable WHERE (b = ?)")

The resulting statement can be used with parameters thus:

(query-value c(select a#:frommytable#:where(= b?))10)

Using the unquote form eliminates the need to keep track of positional parameters; instead, the parameter value is written as a Racket expression within the statement. It is automatically translated to SQL code containing placeholders.

Examples:

> (define b-param10)
> (select a#:frommytable#:where(= b,b-param))

(sql-statement "SELECT a FROM mytable WHERE (b = ?)" 10)

The resulting statement must be called without additional parameters:

(query-value c(select a#:frommytable#:where(= b,b-param)))

Note that placeholder syntax varies between SQL dialects. We can see the code a statement produces for a specific dialect by setting the current-sql-dialect parameter:

> (parameterize ((current-sql-dialect 'postgresql))
(sql-statement->string (select a#:frommytable#:where(= b,b-param))))

"SELECT a FROM mytable WHERE (b = 1ドル)"

3.3SQL Table References and Expressions🔗 i

A table reference is either a reference to a defined table (or view) or a computed table with a name or named components. A table expression can be formed using join and set operations.

table-ref = table-name

| (astable-namerange-var-ident)

| (astable-exprrange-var-ident)

| table-expr

Note: in the final variant of table-ref, the table-expr must be a join table expression, specifically.

table-expr = (cross-jointable-reftable-ref)

| (join-optable-reftable-refjoin-condition)

(set-optable-reftable-ref

maybe-allcorrespond-clause)

| (valuesscalar-expr...)

| (values*(scalar-expr...)...)

| select-statement

join-op = inner-join

| left-join

| right-join

| full-join

| union-join

join-condition = #:natural

| #:usingcolumn-ident...

| #:onscalar-expr

set-op = union

| except

| intersect

maybe-all =

| #:all

correspond-clause =

| #:corresponding

| #:corresponding-bycolumn-ident...

syntax
( table-ref-qq table-ref)

procedure
( table-ref-ast? v)→boolean?
v:any/c

Quasiquotation macro and predicate, respectively, for table-ref.

Examples:

> (sql-ast->string (table-ref-qq supplier))

"supplier"
> (sql-ast->string (table-ref-qq (assuppliers)))

"supplier AS s"
> (sql-ast->string (table-ref-qq (inner-joinsupplierpart#:usingsupply_id)))

"supplier INNER JOIN part USING (supply_id)"

syntax
( table-expr-qq table-expr)

procedure
( table-expr-ast? v)→boolean?
v:any/c

Quasiquotation macro and predicate, respectively, for table-expr.

3.4SQL Statements🔗 i

A statement is one of the four standard DML statements or a WITH statement that combines them with one or more common table expressions.

statement = select-statement

| insert-statement

| update-statement

| delete-statement

| with-statement

Select

select-statement = (selectdistinct-clauseselect-item...select-clause...)

| (selectdistinct-clauseselect-clause...)

select-clause = #:valuesselect-item...

| #:fromtable-ref...

| #:wherecondition-scalar-expr...

| #:group-bycolumn-ident...

| #:havingcondition-scalar-expr...

| #:order-byorder-item...

| #:limitscalar-expr

| #:offsetscalar-expr

select-item = scalar-expr

| (asscalar-exprident)

distinct-clause =

| #:all

| #:distinct

order-item = scalar-expr#:asc

| scalar-expr#:desc

| scalar-expr

A select-statement can contain each kind of select-clause at most once. The clauses can occur in any order. If the first form of select-statement is used (that is, with the initial select-items), then the #:values clause may not also be used.

Changed in version 1.2 of package sql: Added distinct-clause

Insert

insert-statement = (insert#:intotable-nameassign-clausemaybe-conflict)

(insert#:intotable-namemaybe-columns

#:fromtable-exprmaybe-conflict)

assign-clause = #:set[column-identscalar-expr]...

maybe-columns =

| #:columnscolumn-ident...

maybe-conflict =

| #:or-ignore

| #:or-fail

Changed in version 1.1 of package sql: Added #:or-ignore.

Update

update-statement = (updatetable-nameassign-clausemaybe-where)

assign-clause = #:set[column-identscalar-expr]...

maybe-where =

| #:wherecondition-scalar-expr...

Delete

delete-statement = (delete#:fromtable-namemaybe-where)

maybe-where =

| #:wherecondition-scalar-expr...

With

with-statement =

(withmaybe-rec([table-ident/columnscte])

statement)

maybe-rec =

| #:recursive

table-ident/columns = table-ident

| (table-identcolumn-ident...)

cte = statement

| table-expr

Different database systems place different restrictions on what kinds of statements and table expressions they allow within WITH statements.

Changed in version 1.6 of package sql: Allowed table-expr for right-hand side.

syntax
( statement-qq statement)

procedure
( statement-ast? v)→boolean?
v:any/c

Quasiquotation macro and predicate, respectively, for statement.

Examples:

> (sql-ast->string
(statement-qq (select abc#:frommytable#:where(> a10))))

"SELECT a, b, c FROM mytable WHERE (a > 10)"

> (sql-ast->string
(statement-qq (insert #:intomytable#:set[a1][b2][c3])))

"INSERT INTO mytable (a, b, c) VALUES (1, 2, 3)"

syntax
( select-item-qq select-item)

procedure
( select-item-ast? v)→boolean?
v:any/c

Quasiquotation macro and predicate, respectively, for select-item. Note that a select-item AST cannot be used with sql-ast->string unless it is just scalar-expr.

A select-item AST can be a list of select-item ASTs, representing multiple items to be spliced into the SELECT statement.

Added in version 1.6 of package sql.

3.5SQL DDL Statements🔗 i

ddl-statement = create-table-statement

| create-view-statement

create-table-statement =

(create-tablemaybe-tempmaybe-ifnotexiststable-name

#:columnscolumn-def...

maybe-constraints)

(create-tablemaybe-tempmaybe-ifnotexiststable-name

#:asstatement)

maybe-temp =

| #:temporary

maybe-ifnotexists =

| #:if-not-exists

column-def = [column-identtypecolumn-option...]

column-option = #:not-null

| #:defaultscalar-expr

maybe-constraints =

| #:constraintsconstraint-decl...

constraint-decl = (constraintconstraint-identconstraint)

| constraint

constraint = (primary-keycolumn-ident...)

| (uniquecolumn-ident...)

| (checkscalar-expr)

(foreign-keycolumn-ident...

#:referencestable-ident/columns

action-decl...)

action-decl = #:on-deleteaction

| #:on-updateaction

action = #:set-null

| #:set-default

| #:cascade

| #:restrict

| #:no-action

create-view =

(create-viewmaybe-tempview-name

statement)

Changed in version 1.1 of package sql: Added #:if-not-exists option for create-table.
Changed in version 1.4: Added action-decl for foreign-key and the #:default column-option for column-def.

syntax
( ddl-qq ddl-statement)

procedure
( ddl-ast? v)→boolean?
v:any/c

Quasiquotation macro and predicate, respectively, for ddl-statement.

3.6SQL Dialect🔗 i

parameter
( current-sql-dialect )→(or/c symbol? #f)
(current-sql-dialect dialect)→void?
dialect:(or/c symbol? #f)

Controls the default dialect used when converting SQL ASTs to strings using functions such as sql-ast->string .

This parameter does not affect statement (sql-statement? ) values used with connection query methods; generation of SQL code for a query method automatically uses the dialect associated with the connection the query is performed on.

3.7Dynamic Statement Composition and SQL Injection🔗 i

This library allows the dynamic composition of statements and the injection of SQL text using the following extensions to the SQL grammar.

Warning: Never use the INJECT forms to include SQL computed from an untrusted source. Use placeholders or unquote parameters instead; see Placeholders and Unquoted Parameters.

ident = ....

| (Ident:AST(unquoteast-racket-expr))

name = ....

| (Name:AST(unquoteast-racket-expr))

select-item = ....

| (SelectItem:AST(unquoteast-racket-expr))

scalar-expr = ....

| (ScalarExpr:AST(unquoteast-racket-expr))

| (ScalarExpr:INJECT(unquotestring-racket-expr))

table-expr = ....

| (TableExpr:AST(unquoteast-racket-expr))

| (TableExpr:INJECT(unquotestring-racket-expr))

table-ref = ....

| (TableRef:AST(unquoteast-racket-expr))

| (TableRef:INJECT(unquotestring-racket-expr))

Changed in version 1.2 of package sql: Added Ident:AST and Name:AST.
Changed in version 1.6: Added SelectItem:AST. See note under select-item-ast? about lists.

procedure
( make-ident-ast s)→ident-ast?
s:(or/c symbol? string? )

Produces an identifier AST from s by applying the rules in SQL Names and Identifiers to the term `(Ident:,s).

Added in version 1.2 of package sql.

Examples:

> (sql-ast->string (make-ident-ast 'MyTable))

"MyTable"
> (sql-ast->string (make-ident-ast "MyTable"))

"\"MyTable\""
> (sql-ast->string (make-ident-ast 'Select))

"\"SELECT\""
> (sql-ast->string (make-ident-ast 'a+b.c))

"\"A+B.C\""

procedure
( make-name-ast s)→name-ast?
s:(flat-rec-contract Csymbol? name-ast? (listof C))

Produces a name AST from s according to the following rules:

If s is a symbol, it is parsed according to the symbol case of the name nonterminal (see SQL Names and Identifiers).
If s is a name-ast? , it is returned unchanged.
If s is a list, each component is parsed a name-ast? and the components are joined.

Added in version 1.2 of package sql.

Examples:

> (sql-ast->string (make-name-ast 'x))

"x"
> (sql-ast->string (make-name-ast 'x.y.z))

"x.y.z"
> (sql-ast->string (make-name-ast (list 'x.y(make-name-ast 'a.b))))

"x.y.a.b"

procedure
( value->scalar-expr-ast v)→scalar-expr-ast?
v:any/c

Produces a scalar expression AST representing the Racket value value.

Equivalent to (scalar-expr-qq ,value).

Added in version 1.2 of package sql.

procedure
( make-values*-table-expr-ast l-rows)→table-expr-ast?
l-rows:(listof (listof scalar-expr-ast? ))

Produces a table expression AST of the same kind as the (values*(scalar-expr... )... ) case of the table-expr nonterminal, but where the number of rows and columns may be determined dynamically.

Note that all of the rows (i.e. the inner lists) must be of the same length.

Added in version 1.3 of package sql.

Examples:

> (struct produce-item(namenumtype)#:transparent)

> (define (insert-produceitems)
(insert
#:intoproduce
#:columnsnamenumtype
#:from
(TableExpr:AST
,(make-values*-table-expr-ast
(for/list ([item(in-list items)])
(match-define (produce-itemnamenumtype)
item)
(map value->scalar-expr-ast
(list namenumtype)))))))

> (sql-statement->string
(insert-produce
(list (produce-item"apples"1"standard")
(produce-item"bananas"5"organic")
(produce-item"cranberries"50"canned"))))

"INSERT INTO produce (name, num, type) VALUES (?, ?, ?), (?, ?, ?), (?, ?, ?)"

top ← prev up next →