►Toolbox:   Miscellaneous Utilities
▼7 Database
On this page:
~sql
~stmt
sql:  id
current-sql-keywords
sql:  string
sql:  seq
sql:  seq*
sql:  tuple
sql:  tuple*
query:  bag
query:  indexed-list
query:  rows
pre-sql?
    contents    up   

7.2 Formatting SQL🔗

 (require toolbox/db/sql) package: toolbox-db-lib

procedure

(~sql v ...) → string?

  v : pre-sql?
Converts each v argument to a string then concatenates the results. The arguments are converted according to the following rules:

Example:
> (~sql "SELECT " 'id " FROM " 'comment " WHERE " 'rating " > " 0.75)

"SELECT \"id\" FROM \"comment\" WHERE \"rating\" > 0.75"

The ~sql function is especially useful when used @ syntax via the at-exp language.

Example:

@~sql{SELECT name FROM user WHERE id IN @sql:tuple*[user-ids]}

syntax

(~stmt expr ...)

 
  expr : pre-sql?
Equivalent to (lifted-statement (~sql expr ...)). The expr forms may not reference local variables.

procedure

(sql:id name [#:keywords keywords]) → string?

  name : (or/c symbol? string?)
  keywords : (or/c (listof string?) #f) = (current-sql-keywords)
Quotes name as a SQL identifier by surrounding it with double quotes. If name contains double quotes, they are escaped by doubling.

Examples:
> (sql:id "hello")

"\"hello\""

> (sql:id "weird\"id")

"\"weird\"\"id\""

If keywords is not #f, quoting is suppressed for any valid SQL identifiers not in the supplied keywords list (compared using string-ci=?). If using SQLite, current-sql-keywords can be set to (sqlite3-keywords) to safely disable quoting of non-keyword identifiers:

> (current-sql-keywords (sqlite3-keywords))
> (sql:id "hello")

"hello"

> (sql:id "select")

"\"select\""

Changed in version 1.1 of package toolbox-db-lib: Added the #:keywords argument.

parameter

(current-sql-keywords) → (or/c (listof string?) #f)

(current-sql-keywords keywords) → void?
  keywords : (or/c (listof string?) #f)
 = #f
A parameter used by functions like sql:id and ~sql to determine if an identifier must be quoted. The default value of #f assumes all identifiers are keywords.

If using SQLite, this parameter can safely be set to (sqlite3-keywords).

Added in version 1.1 of package toolbox-db-lib.

procedure

(sql:string name) → string?

  name : (or/c symbol? string?)
Quotes name as a SQL string literal by surrounding it with single quotes. If name contains single quotes, they are escaped by doubling.

Examples:
> (sql:string "hello")

"'hello'"

> (sql:string "it's")

"'it''s'"

procedure

(sql:seq v ...) → string?

  v : pre-sql?
Converts each v to a string using ~sql, then concatenates the results with "," between consecutive items.

Example:
> (sql:seq 1 2 3)

"1,2,3"

procedure

(sql:seq* v ... vs) → string?

  v : pre-sql?
  vs : (listof pre-sql?)
Like sql:seq, but the last argument is used as a list of arguments for sql:seq. In other words, the relationship between sql:seq and sql:seq* is the same as the one between string-append and string-append*.

Example:
> (sql:seq* 1 2 '(3 4))

"1,2,3,4"

procedure

(sql:tuple v ...) → string?

  v : pre-sql?
Like sql:seq, but the resulting string is additionally wrapped in parentheses.

Example:
> (sql:tuple 1 2 3)

"(1,2,3)"

procedure

(sql:tuple* v ... vs) → string?

  v : pre-sql?
  vs : (listof pre-sql?)
Like sql:tuple, but the last argument is used as a list of arguments for sql:tuple. In other words, the relationship between sql:tuple and sql:tuple* is the same as the one between string-append and string-append*.

Example:
> (sql:tuple* 1 2 '(3 4))

"(1,2,3,4)"

procedure

(query:bag vs) → string?

  vs : (listof pre-sql?)
Builds a SQL query that returns rows of exactly one column, where each element of vs is an expression that supplies the value of one of the rows.

Examples:
> (query:bag '(1 2 3))

"VALUES (1),(2),(3)"

> (query:bag '())

"SELECT NULL WHERE 0"

In a sense, query:bag is the inverse of query-list. However, because the query contains no ORDER BY clause, the order of the resulting rows cannot be guaranteed. If the order of vs is important, query:indexed-list should be used instead.

procedure

(query:indexed-list vs) → string?

  vs : (listof pre-sql?)
Like query:bag, but the resulting query contains two columns. The first column is a (zero-based) index corresponding to the index of each element v in vs, while the second column is the value of the expression v itself.

Examples:
> (query:indexed-list '(1 2 3))

"VALUES (0,1),(1,2),(2,3)"

> (query:indexed-list '())

"SELECT NULL,NULL WHERE 0"

> (query-list
   (~sql "WITH nums(i,n) AS (" (query:indexed-list (range 10)) ")\n"
         "SELECT n*n FROM nums ORDER BY i"))

'(0 1 4 9 16 25 36 49 64 81)

procedure

(query:rows rows [#:columns num-columns]) → string?

  rows : (listof (vectorof pre-sql?))
  num-columns : (or/c exact-nonnegative-integer? #f) = #f
Builds a SQL query that returns a row for each element row of rows, where each element of row is an expression that supplies the value of one of the columns in the row. Each row must have the same length.

If num-columns is not #f, it supplies the number of columns the query should return. Otherwise, the number of columns is inferred from the length of the elements of rows. If num-columns is #f and no rows are provided, an exn:fail:contract exception is raised.

Examples:
> (query:rows '(#(1 2) #(3 4) #(5 6)))

"VALUES (1,2),(3,4),(5,6)"

> (query:rows '() #:columns 2)

"SELECT NULL,NULL WHERE 0"

> (query:rows '())

query:rows: cannot infer number of columns;

 no rows given and #:columns not specified

procedure

(pre-sql? v) → boolean?

  v : any/c
Returns #t if v is a pre-SQL value: a raw SQL string, a symbol, a rational number, or sql-null. Otherwise, returns #f.

Pre-SQL values can be converted to SQL strings using ~sql.

    contents    up