Next: (kracht strings), Previous: (kracht prelude), Up: API
Undocumented macro.
Undocumented macro.
Undocumented macro.
Undocumented macro.
Escape string S for inclusion inside a single-quoted SQL literal by doubling each single quote (e.g. "O’Brien" becomes "O”Brien"). Non-string values are formatted with ‘format‘ first.
Doubling quotes alone is correct for PostgreSQL, SQLite, and standard SQL, where a backslash is an ordinary character. Pass #:escape-backslash? #t for MySQL/MariaDB, whose default parser also treats a backslash as an escape character inside string literals.
A NUL character is rejected with an error rather than emitted, since no SQL text type can store one. Prefer the parameterized builders below (‘sql-insert’, ‘sql-update’, ‘sql-parameterize’, ...) over interpolating escaped values.
Convert a key-value pair A into a SQL assignment clause string formatted as "column_name = value".
The key symbol is mapped via ‘symbol->sql-column‘. Booleans are converted to 1 or 0, numbers are unquoted, and all other types are single-quoted with embedded single quotes escaped. This interpolates values into the statement; prefer ‘sql-update’, which binds them instead.
Render NAME (a symbol or string) as a safely delimited SQL identifier.
With the default ‘#:dialect ’ansi’ the name is wrapped in double quotes with any embedded double quote doubled. With ‘#:dialect ’mysql’ (or ‘’mariadb’) it is wrapped in backticks with embedded backticks doubled instead, because MySQL reads a double-quoted token as a string unless ANSI_QUOTES is enabled.
Use this for column and table names that come from data. ‘symbol->sql-column’ only maps hyphens to underscores and does not defend against a name that contains quotes or other punctuation. A NUL character is rejected.
Convert SQL column name string X into a Scheme symbol by replacing underscores with hyphens.
Build a ‘DELETE FROM ...’ query. WHERE is a list of conditions (see ‘sql-where-fragment’) that becomes the ‘WHERE’ clause; with no conditions the query deletes every row.
Build an ‘INSERT INTO ... VALUES ...’ query from ROW, a non-empty association list of column/value pairs. The values become bound parameters in the order they appear in ROW.
Use ‘sql-insert-many’ to insert several rows with a single statement.
RETURNING, when given, is a list of columns – or ‘(*)’ for all columns – to append as a ‘RETURNING’ clause, as PostgreSQL and SQLite support.
Build an ‘INSERT INTO ... VALUES (...), (...)’ query from ROWS, a non-empty list of association lists. The column list comes from the first row; every other row must carry the same columns, and all the values are bound in that column order, row by row.
Passing a single row is fine, and is exactly what ‘sql-insert’ does.
RETURNING, when given, is a list of columns – or ‘(*)’ for all columns – to append as a ‘RETURNING’ clause, as PostgreSQL and SQLite support.
Return a <sql-query> that binds BINDINGS into TEMPLATE, replacing each ‘?’ with the placeholder for STYLE.
Use this for statements the builders above do not cover. TEMPLATE must contain exactly as many ‘?’ characters as there are bindings; the count is checked, so a mismatch is reported instead of producing a subtly wrong query. There is no escape for a literal ‘?’, so keep those out of the template.
Render the placeholder standing for the INDEX-th (1-based) bound parameter in STYLE.
STYLE is one of the symbols ‘question’ (‘?’; SQLite, MySQL, JDBC), ‘numbered’ (‘$1’; PostgreSQL), ‘percent’ (‘%s’; psycopg and other pyformat drivers) or ‘dbi’ (‘#?’; guile-dbi); or a procedure of one argument, which is called with INDEX and must return the placeholder string.
Return query Q as an association list with the keys ‘sql’ and ‘params’.
Return the SQL string of query Q and its list of parameters, as two values, ready for ‘receive’ or ‘call-with-values’.
Transform a SQL row representation XS into a Scheme-idiomatic association list. If XS is an alist, it converts its column keys into hyphenated Scheme symbols. If XS is not an alist, it returns XS unaltered.
Build a ‘SELECT ...’ query. COLUMNS is a list of columns, defaulting to ‘(*)’ for all of them. WHERE is a list of conditions (see ‘sql-where-fragment’). ORDER-BY is a list of ‘(column [direction])’ pairs.
LIMIT and OFFSET, when given, are bound as parameters rather than interpolated, so they cannot carry SQL into the statement.
Build an ‘UPDATE ... SET ...’ query from ROW, a non-empty association list of column/value pairs; the values become bound parameters in ROW order.
WHERE is a list of conditions (see ‘sql-where-fragment’) that becomes the ‘WHERE’ clause. Passing none updates every row – that is occasionally what you want, but it is a good way to lose data, so check before you mean it.
Build an upsert for a single ROW. See ‘sql-upsert-many’, which takes the same arguments but a list of rows.
Build an ‘INSERT ... ON CONFLICT ...’ (PostgreSQL and SQLite) or ‘INSERT ... ON DUPLICATE KEY UPDATE ...’ (MySQL and MariaDB) query from ROWS, a non-empty list of association lists – see ‘sql-insert-many’ for how ROWS is read.
DIALECT is ‘postgres’ (or ‘postgresql’), ‘sqlite’, ‘mysql’ (or ‘mariadb’), or ‘ansi’, which is treated as the ON CONFLICT form.
CONFLICT-TARGET is the column, or list of columns, the conflict is detected on. It is required for the ON CONFLICT form unless UPDATE is ‘nothing’, because PostgreSQL and SQLite allow a bare ‘ON CONFLICT DO NOTHING’; it must be left out for MySQL, which takes the conflict key from a unique index instead.
UPDATE says what happens on a conflict:
’auto the default: overwrite every inserted column that is not part of CONFLICT-TARGET. MySQL has no conflict target, so there ’auto covers the key columns too; name them in ‘(columns)’ to leave the key alone ’nothing ‘ON CONFLICT DO NOTHING’; PostgreSQL and SQLite only, since MySQL has no equivalent (columns) overwrite just these columns
Every value comes from the row being inserted, through EXCLUDED or VALUES(), so the statement binds no parameters beyond the ones its INSERT already does. A new value computed from the old one – a counter, say – is outside what this builder expresses; write that statement with ‘sql-parameterize’.
RETURNING, when given, is a list of columns – or ‘(*)’ – for PostgreSQL and SQLite. MySQL has no ‘RETURNING’ clause.
Build a ‘ WHERE ...’ fragment from CONDITIONS, for statements you compose yourself. The result is a <sql-query> whose SQL is the empty string when CONDITIONS is empty.
A condition is one of these shapes:
(column . value) – column = value (the short form) (column value) – column = value (column operator value) – column OPERATOR value, for one of the comparison operators: =, <>, !=, <, <=, >, >=, like, ilike, not-like, not-ilike, is, is-not (column ’in values) – column IN (values ...) (column ’not-in values) – column NOT IN (values ...) (column ’is ’null) – column IS NULL (column ’is-not ’null) – column IS NOT NULL
Conditions are joined with ‘ AND ’. A column may be a symbol (hyphens become underscores via ‘symbol->sql-column’) or a string; either is quoted unless #:quote-identifiers? is #f, and quoted the way DIALECT requires.
START-INDEX is the index of the first placeholder, for composing this fragment after another one that has already bound values.
Convert Scheme symbol X into a SQL column name string by replacing hyphens with underscores.
Convert a list of symbols XS into a single string of comma-separated SQL column names.
Convert a Scheme value A into its raw SQL literal string representation. Numbers are kept raw, booleans are mapped to "1" or "0", and other types are wrapped in single quotes with embedded single quotes escaped. This interpolates the value into the statement; prefer the parameterized builders, which bind it instead.
Next: (kracht strings), Previous: (kracht prelude), Up: API