io.randomseed.utils.db.sql — io.randomseed/utils-db 2.0.11

Random Utilities, SQL helpers

braced-? clj

(braced-? coll)

Returns a parenthesized, comma-separated string of ? placeholders for coll.

braced-join-? clj

(braced-join-? ids)

Returns a parenthesized, comma-separated string of ? placeholders, one per element in ids.

braced-join-col-names clj

(braced-join-col-names cols)

Joins column names into a comma-separated, parenthesized string after converting each to snake_case.

braced-join-col-names-no-conv clj

(braced-join-col-names-no-conv cols)

Joins column names into a comma-separated, parenthesized string without case conversion.

build-query clj macro

(build-query)

(build-query q)

(build-query coll)

(build-query q substitution-map)

(build-query & query-parts substitution-map)

For the given SQL query q and substitution map performs pattern interpolation. If multiple arguments are given the last one will be treated as substitution map.

Tries to convert possible literals given as query parts to strings and then trim them while squeezing repeated spaces at compile time. If some operation cannot be performed in that phase, it generates code which will convert an expression to a string at runtime. Then pattern interpolation is performed on the resulting string, using the provided substitutions map.

If a source string contains %{tag-name} special pattern, tag-name will be looked up in substitution map and the whole pattern will be replaced by the corresponding value.

If a tag name from pattern cannot be found in a substitution map, the pattern will be replaced by an empty string.

A pattern may have a form of %%{tag-name}. In such case any non-nil value being a result of tag name resolution will be quoted using io.randomseed.utils.db.sql/quote.

A synonym of %%table{tag-name} is %[table-name]. A synonym of %%column{tag-name} is %(column-name). A synonym of %%colspec{tag-name} is %<column-table-specification>.

A pattern may have additional modifier before the opening brace. It will be resolved as a symbolic function name to be called in order to transform a value associated with a tag name. If the name is not fully-qualified (does not contain a namespace part) its default namespace will be set to io.randomseed.utils.db.sql.

There is a special pattern using %SOME tag:

• %SOME? variable:has-value#no-value or
• %SOME? variable:has-value

It performs a substitution with has-value string if variable exists and is not nil and not false; otherwise it performs a substitution with no-value string or an empty string if the no-value was not given.

There is also an additional pattern %'column-table-specification' which is a quotation pattern. It uses colspec-quoted function on a given text.

Example:

(build-query "select %%column{id} from %%table{users}"
             "where" :points '> 100
             {:id    :users/id
              :users :users/id})

The above call will generate the following result:

"select `id` from `users` where points > 100"

This is synonymous to:

(build-query "select %(id) from %[users]"
             "where" :points '> 100
             {:id    :users/id
              :users :users/id})

This macro can optionally be called with a single literal sequence given as its first and only argument. In such cache the sequence should contain all arguments, including a substitution map, if applicable.

This macro should NOT be used to dynamically generate queries having thousands of variant substitution parameters as it uses unlimited underlying cache. For such purposes please use build-query-dynamic, or simply utilize parameters of prepared statements.

WARNING: Interpolation pattern may execute arbitrary code since it allows for any function name.

build-query-dynamic clj macro

(build-query-dynamic)

(build-query-dynamic q)

(build-query-dynamic coll)

(build-query-dynamic q substitution-map)

(build-query-dynamic & query-parts substitution-map)

For the given SQL query q and substitution map performs pattern interpolation. If multiple arguments are given the last one will be treated as substitution map.

Tries to convert possible literals given as query parts to strings and then trim them while squeezing repeated spaces at compile time. If some operation cannot be performed in that phase, it generates code which will convert an expression to a string at runtime. Then pattern interpolation is performed on the resulting string, using the provided substitutions map.

If a source string contains %{tag-name} special pattern, tag-name will be looked up in substitution map and the whole pattern will be replaced by the corresponding value.

If a tag name from pattern cannot be found in a substitution map, the pattern will be replaced by an empty string.

A pattern may have a form of %%{tag-name}. In such case any non-nil value being a result of tag name resolution will be quoted using io.randomseed.utils.db.sql/quote.

A synonym of %%table{tag-name} is %[table-name]. A synonym of %%column{tag-name} is %(column-name). A synonym of %%colspec{tag-name} is %<column-table-specification>.

A pattern may have additional modifier before the opening brace. It will be resolved as a symbolic function name to be called in order to transform a value associated with a tag name. If the name is not fully-qualified (does not contain a namespace part) its default namespace will be set to io.randomseed.utils.db.sql.

There is also additional pattern %'column-table-specification' which is a quotation pattern. It uses colspec-quoted function on a given text.

Example:

(build-query-dynamic "select %%column{id} from %%table{users}"
                     "where" :points '> 100
                     {:id    :users/id
                      :users :users/id})

The above call will generate the following result:

"select `id` from `users` where points > 100"

This is synonymous to:

(build-query-dynamic "select %%column{id} from %[users]"
                     "where" :points '> 100
                     {:users :users/id})

This macro can optionally be called with a single literal sequence given as its first and only argument. In such cache the sequence should contain all arguments, including a substitution map, if applicable.

This macro should be used to dynamically generate queries having thousands of variant substitution parameters.

WARNING: Interpolation pattern may execute arbitrary code since it allows for any function name.

col clj

(col col-spec)

(col _ col-spec)

Alias for column. Extracts column name as a snake-cased string from col-spec which may be an identifier or a string. If the identifier has a name, it will be used. For string, it will look for a slash or dot character used as a separator between a table and a column name, to extract the column name. If two arguments are given, the first one is ignored.

Example result: "column_name"

col-kw clj

(col-kw col-spec)

(col-kw _ col-id)

Alias for column-kw. Extracts column name as a lisp-cased keyword from col-spec which may be an identifier or a string. If the identifier has a name, it will be used. For strings (or objects convertable to strings), it will detect slash and dot characters as separators of a namespace and name to pick a column name. If two arguments are given, the first one is ignored.

Example result: :column-name

col-table clj

(col-table col-spec)

(col-table col-id col-spec)

Alias for column-table. Extracts column and table names from col-spec (which may be an identifier or a string) as snake-cased strings of a 2-element vector (first element being a column name, second a table name). If col-spec is an identifier, its namespace and name will be used. If there is no namespace, it will be considered a column name. If two arguments are given, names are extracted separately using column and table functions). If string is given (or an object convertable to a string), a dot or slash character will be used as a splitting point to extract table and column name. Single string without any separator character will be considered a table name.

Example results: ["column_name" "table_name"], ["column_name" nil]

col-table-kw clj

(col-table-kw col-spec)

(col-table-kw col-id col-spec)

Alias for column-table-kw. Extracts column and table names from col-spec (which may be an identifier or a string) as lisp-cased keywords of a 2-element vector (first element being a column name, second a table name). If col-spec is an identifier, its namespace and name will be used. If there is no namespace, it will be considered a column name. If two arguments are given, names are extracted separately using column-kw and table-kw functions). For strings (or objects convertable to strings), it will detect slash and dot characters as separators of a namespace and a name.

Example results: [:column-name :table-name], [:column-name nil]

colspec clj

(colspec col-spec)

(colspec table-id col-id)

Converts a table/column-formatted identifier col-spec into a snake-cased string with first slash replaced by a dot character. If table-id and col-id are given, creates a string of those parts joined with a dot character. If single identifier is given, it uses its namespace and name.

Example results: "table_name.column_name" or "simple_name"

colspec-kw clj

(colspec-kw col-spec)

(colspec-kw table-id col-id)

Converts a table/column or table.column-formatted identifier table-col into a lisp-cased keyword. If table-id and col-id are given, it creates a string of those parts joined with a dot character. If identifier is given, it uses its namespace and name.

For strings and objects convertable to a string, first slash or dot character will be used as a split point. If the col-spec is a string and there is a slash character present in it, it will not be checked for a dot character presence.

Example results: :table-name/column-name or :simple-name

colspec-quoted clj

(colspec-quoted col-spec)

(colspec-quoted table-id col-id)

Converts a table/column-formatted identifier col-spec into a snake-cased string with first slash replaced by a dot character. If table-id and col-id are given, creates a string of those parts joined with a dot character. If identifier is given, it uses its namespace and name. Each part of the name will be quoted.

If the col-spec is a string and there is a slash character present in it, it will not be checked for a dot character presence.

Example results: "`table_name`.`column_name`" or "`simple_name`"

column clj

(column col-spec)

(column _ col-spec)

Extracts column name as a snake-cased string from col-spec which may be an identifier or a string. If the identifier has a name and a namespace, its name will be used. For string, it will look for a slash or dot character used as a separator between a table and a column name, to extract the column name. If two arguments are given, the first one is ignored.

Example result: "column_name"

column-kw clj

(column-kw col-spec)

(column-kw _ col-id)

Extracts column name as a lisp-cased keyword from col-spec which may be an identifier or a string. If the identifier has a name, it will be used. For strings (or objects convertable to strings), it will detect slash and dot characters as separators of a namespace and name to pick a column name. If two arguments are given, the first one is ignored.

Example result: :column-name

column-table clj

(column-table col-spec)

(column-table col-id col-spec)

Extracts column and table names from col-spec (which may be an identifier or a string) as snake-cased strings of a 2-element vector (first element being a column name, second a table name). If col-spec is an identifier, its namespace and name will be used. If there is no namespace, it will be considered a column name. If two arguments are given, names are extracted separately using column and table functions). If string is given (or an object convertable to a string), a dot or slash character will be used as a splitting point to extract table and column name. Single string without any separator character will be considered a table name.

Example results: ["column_name" "table_name"], ["column_name" nil]

column-table-kw clj

(column-table-kw col-spec)

(column-table-kw col-id col-spec)

Extracts column and table names from col-spec (which may be an identifier or a string) as lisp-cased keywords of a 2-element vector (first element being a column name, second a table name). If col-spec is an identifier, its namespace and name will be used. If there is no namespace, it will be considered a column name. If two arguments are given, names are extracted separately using column-kw and table-kw functions). For strings (or objects convertable to strings), it will detect slash and dot characters as separators of a namespace and a name.

Example results: [:column-name :table-name], [:column-name nil]

dbname clj

(dbname v)

If the given value v is an ident, it returns its (optional) namespace and name joined with a dot character. Otherwise it returns a string representation of the given object with a first slash replaced by a dot.

dbname-kw clj

(dbname-kw v)

If the given value v is an ident, it returns its keyword representation. Otherwise it returns a string representation of the given object with dots replaced by slashes.

dbname-quoted clj

(dbname-quoted v)

If the given value v is an ident, it returns its (optional) namespace and name joined with a dot character. Otherwise it returns a string representation of the given object with a first slash replaced by a dot. Each part of a name will be quoted.

delete! clj

(delete! & args)

Convenient delegator to next.jdbc.sql/delete!.

find-by-keys clj

(find-by-keys & args)

Convenient delegator to next.jdbc.sql/find-by-keys.

for-insert-multi-or clj

(for-insert-multi-or table cols rows opts)

Given a table name, a vector of column names, and a vector of row values (each row is a vector of its values), return a vector of the full INSERT SQL string and its parameters. Applies any :table-fn / :column-fn supplied in the options. If :suffix is provided in opts, that string is appended to the INSERT IGNORE ... statement. The IGNORE part can be replaced by supplying :alt-clause option key.

for-insert-or clj

(for-insert-or table key-map opts)

Given a table name and a hash map of column names and their values, return a vector of the full INSERT OR IGNORE SQL string and its parameters. Applies any :table-fn / :column-fn supplied in the options. If :suffix is provided in opts, that string is appended to the INSERT ... statement. If :alt-clause is provided in opts, it will replace the default IGNORE string.

for-replace clj

(for-replace table key-map opts)

Given a table name and a hash map of column names and their values, return a vector of the full REPLACE SQL string and its parameters. Applies any :table-fn / :column-fn supplied in the options. If :suffix is provided in opts, that string is appended to the INSERT ... statement.

for-replace-multi clj

(for-replace-multi table cols rows opts)

Given a table name, a vector of column names, and a vector of row values (each row is a vector of its values), return a vector of the full REPLACE SQL string and its parameters. Applies any :table-fn / :column-fn supplied in the options. If :suffix is provided in opts, that string is appended to the REPLACE ... statement.

get-failed? clj

(get-failed? v)

Returns true if getting from a database failed in post-processing phase (e.g. de-serialization) and the data were broken.

groups-inverter clj

(groups-inverter f)

(groups-inverter f a)

(groups-inverter f a b)

(groups-inverter f a b c)

(groups-inverter f a b c & more)

Helper function for transforming a map of sequences keyed with keywords into a map of elements found in those sequences (as keys) associated with results of calling a function on them with additional arguments, including original map's keys.

In other words: transforms results of clojure.core/group-by into a single map, changing values found in sequences into keys, and associating values to those keys resulting from calling a function.

Takes a function f and additional arguments (zero or more), and returns a function which takes a map m, identity type id-type and a sequence of identifiers ids, and calls f with all arguments and id-type passed on the sequence. Then it calls clojure.core/into to put the result of calling f into a map m.

Example: (groups-inverter get-ids db)

In this example a function will be returned, similar to the below:

(fn [m id-type ids] (into m (get-ids db id-type))).

It is used mainly as a transformer in reduce-kv when dealing with multiple user identifiers grouped by identity type. Having a map of vectors grouped by identity type:

{:email [#io.randomseed.utils.identity.types.Identity{:id-type :email :value "[e-mail]"}],
 :id    [#io.randomseed.utils.identity.types.Identity{:id-type :id    :value 1}
         #io.randomseed.utils.identity.types.Identity{:id-type :id,   :value 42}]}

we can call (reduce-kv (groups-inverter get-ids db) {}) to get:

{#io.randomseed.utils.identity.types.Identity{:id-type :id,    :value 1}               1
 #io.randomseed.utils.identity.types.Identity{:id-type :id,    :value 42}             42
 #io.randomseed.utils.identity.types.Identity{:id-type :email, :value "[e-mail]"} 1}

The get-ids will be called for each identity group, receiving a list of identities and passed arguments with identity type. After getting numerical user identifiers it will associate them with identity objects in a map.

id-from-db clj

(id-from-db v)

Converts the given ID retrieved from a database to a value suitable to be used in Clojure programs. If v is a number or a keyword, it is returned as is. Otherwise it is converted to a keyword.

id-to-db clj

(id-to-db v)

Converts the given ID to a value suitable to be stored in a database. If v is a number, it is passed as is. Otherwise it is converted to a string.

idname clj

(idname v)

If the given value v is an ident, it returns its (optional) namespace and name joined with a dot character. Otherwise it returns the string representation of the given object with slashes replaced by dot characters.

idname-simple clj

(idname-simple v)

If the given value v is an ident, it returns its name. Otherwise it returns the string representation of the given object or nil if the string is empty.

insert-multi-or! clj

(insert-multi-or! connectable table cols rows)

(insert-multi-or! connectable table cols rows opts)

Syntactic sugar over execute! to make inserting columns/rows easier. Same as insert-or! but supports multiple rows to be inserted at once.

insert-or! clj

(insert-or! connectable table key-map)

(insert-or! connectable table key-map opts)

Syntactic sugar over execute-one! to make inserting hash maps easier. Given a connectable object, a table name, and a data hash map, inserts the data as a single row in the database and attempts to return a map of generated keys. By default it uses INSERT OR IGNORE but the IGNORE can be changed to anything by supplying :alt-clause option in opts map.

insert-or-ignore! clj

(insert-or-ignore! connectable table key-map)

(insert-or-ignore! connectable table key-map opts)

Syntactic sugar over execute-one! to make inserting hash maps easier. Given a connectable object, a table name, and a data hash map, inserts the data as a single row in the database and attempts to return a map of generated keys.

insert-or-ignore-multi! clj

(insert-or-ignore-multi! connectable table cols rows)

(insert-or-ignore-multi! connectable table cols rows opts)

Syntactic sugar over execute! to make inserting columns/rows easier. Same as insert-multi! but supports :alt-clause option key.

insert-or-replace! clj

(insert-or-replace! connectable table key-map)

(insert-or-replace! connectable table key-map opts)

Syntactic sugar over execute-one! to make inserting hash maps easier. Given a connectable object, a table name, and a data hash map, inserts the data as a single row in the database and attempts to return a map of generated keys.

insert-or-replace-multi! clj

(insert-or-replace-multi! connectable table cols rows)

(insert-or-replace-multi! connectable table cols rows opts)

Syntactic sugar over execute! to make inserting columns/rows easier. Same as insert-multi! but supports :alt-clause option key.

join-? clj

(join-? ids)

Returns a comma-separated string of ? placeholders, one per element in ids.

join-col-names clj

(join-col-names cols)

Joins column names into a comma-separated string after converting each to snake_case.

join-v=? clj

(join-v=? ids)

Returns a comma-separated string of col = ? assignments, one per element in ids.

make-kw clj

(make-kw name)

(make-kw ns name)

Creates a keyword with the given name and namespace which both can be expressed as strings or idents. If the second argument is nil then a keyword is created using the first argument by simply converting it with the keyword function. If both ns and name are given then the following is applied: if ns or name is a qualified ident, its name and namespace will be joined with a dot character before producing a keyword; additionally, if ns or name is a simple ident, any slash character in its name will be replaced with a dot. If ns or name is not an ident then any slash character in its string representation will be replaced with a dot before creating a keyword.

make-kw-lisp clj

(make-kw-lisp name)

(make-kw-lisp ns name)

Creates a keyword with the given name and namespace which both can be expressed as strings or idents. All underscore characters will be replaced by hyphens. If the second argument is nil then a keyword is created using the first argument by simply converting it with the keyword function. If any given ident is namespaced, only its name is used.

make-kw-simple clj

(make-kw-simple name)

(make-kw-simple ns name)

Creates a keyword with the given name and namespace which both can be expressed as strings or idents. If the second argument is nil then a keyword is created using the first argument by simply converting it with the keyword function. If any given ident is namespaced, only its name is used.

make-kw-snake clj

(make-kw-snake name)

(make-kw-snake ns name)

Creates a keyword with the given name and namespace which both can be expressed as strings or idents. All hyphen characters will be replaced by underscores. If the second argument is nil then a keyword is created using the first argument by simply converting it with the keyword function. If any given ident is namespaced, only its name is used.

query clj

(query & args)

Convenient delegator to next.jdbc.sql/query.

quoted clj

(quoted s)

Quotes the given string according to MySQL / MariaDB quoting rules.

replace! clj

(replace! connectable table key-map)

(replace! connectable table key-map opts)

Syntactic sugar over execute-one! to make inserting hash maps easier. Given a connectable object, a table name, and a data hash map, inserts the data as a single row in the database and attempts to return a map of generated keys. By default it uses REPLACE.

replace-multi! clj

(replace-multi! connectable table cols rows)

(replace-multi! connectable table cols rows opts)

Syntactic sugar over execute! to make inserting columns/rows easier. Same as replace! but supports multiple rows to be inserted at once.

table clj

(table col-spec)

(table col-spec _)

Extracts table name as a snake-cased string from col-spec which may be an identifier or a string. If the identifier has a namespace, it will be used, otherwise its name will be used. For string, it will look for a slash or dot character used as a separator between a table and a column name, to extract the table name. If two arguments are given, the second one is ignored.

Example result: "table_name"

table-col clj

(table-col col-spec)

(table-col col-spec col-id)

Alias for table-column. Extracts table and column names from col-spec (which may be an identifier or a string) as snake-cased strings of a 2-element vector (first element being a table name, second a column name). If col-spec is an identifier, its namespace and name will be used. If there is no namespace, it will be considered a table name. If two arguments are given, names are extracted separately using table and column functions). If string is given (or an object convertable to a string), a dot or slash character will be used as a splitting point to extract table and column name. Single string without any separator character will be considered a table name.

Example results: ["table_name" "column_name"], ["table_name" nil]

table-col-kw clj

(table-col-kw col-id)

(table-col-kw col-spec col-id)

Alias for table-column-kw. Extracts table and column names from col-spec (which may be an identifier or a string) as lisp-cased keywords of a 2-element vector (first element being a table name, second a column name). If col-spec is an identifier, its namespace and name will be used. If there is no namespace, it will be considered a table name. If two arguments are given, names are extracted separately using table-kw and column-kw functions). For strings (or objects convertable to strings), it will detect slash and dot characters as separators of a namespace and a name.

Example results: [:table-name :column-name], [:table-name nil]

table-column clj

(table-column col-spec)

(table-column col-spec col-id)

Extracts table and column names from col-spec (which may be an identifier or a string) as snake-cased strings of a 2-element vector (first element being a table name, second a column name). If col-spec is an identifier, its namespace and name will be used. If there is no namespace, it will be considered a table name. If two arguments are given, names are extracted separately using table and column functions). If string is given (or an object convertable to a string), a dot or slash character will be used as a splitting point to extract table and column name. Single string without any separator character will be considered a table name.

Example results: ["table_name" "column_name"], ["table_name" nil]

table-column-kw clj

(table-column-kw col-spec)

(table-column-kw col-spec col-id)

Extracts table and column names from col-spec (which may be an identifier or a string) as lisp-cased keywords of a 2-element vector (first element being a table name, second a column name). If col-spec is an identifier, its namespace and name will be used. If there is no namespace, it will be considered a table name. If two arguments are given, names are extracted separately using table-kw and column-kw functions). For strings (or objects convertable to strings), it will detect slash and dot characters as separators of a namespace and a name.

Example results: [:table-name :column-name], [:table-name nil]

table-kw clj

(table-kw col-spec)

(table-kw table-id _)

Extracts table name as a lisp-cased keyword from col-spec which may be an identifier or a string. If the identifier has a namespace, it will be used, otherwise its name will be used. For strings (or objects convertable to strings), it will detect slash and dot characters as separators of a namespace and name to pick a table name. If two arguments are given, the second one is ignored.

Example result: :table-name

to-lisp clj

Memoized converter from snake_case to lisp-case (handles namespaced identifiers).

to-lisp-simple clj

Memoized converter from snake_case to lisp-case (simple, name-only).

to-lisp-slashed clj

Memoized converter from snake_case to lisp-case (uses slash as separator).

to-snake clj

Memoized converter from lisp-case to snake_case (handles namespaced identifiers).

to-snake-simple clj

Memoized converter from lisp-case to snake_case (simple, name-only).

to-snake-slashed clj

Memoized converter from lisp-case to snake_case (uses slash as separator).

update! clj

(update! & args)

Convenient delegator to next.jdbc.sql/update!.

values-? clj

(values-? coll)

Returns a VALUES (?,?,...?) SQL fragment with one placeholder per element in coll.