Database utilities
Database utilities
(*enable-jmx* config metrics-registry)This function exists to enable starting multiple PuppetDB instances inside a single JVM. Starting up a second instance results in a collision exception between JMX beans from the two instances. Disabling JMX from the broker avoids that issue
This function exists to enable starting multiple PuppetDB instances inside a single JVM. Starting up a second instance results in a collision exception between JMX beans from the two instances. Disabling JMX from the broker avoids that issue
(block-on-schema-mismatch expected-schema)Compares the schema version the local PDB knows about against the version in the database. If these versions don't match it indicates that either a migration has been applied by another PDB or that the local PDB has been upgraded before the needed migration has been applied. Raising an exception here will cause all connection attempts from Hikari to fail until the local PDB has been upgraded or a needed migration is applied
Compares the schema version the local PDB knows about against the version in the database. If these versions don't match it indicates that either a migration has been applied by another PDB or that the local PDB has been upgraded before the needed migration has been applied. Raising an exception here will cause all connection attempts from Hikari to fail until the local PDB has been upgraded or a needed migration is applied
(call-with-array-converted-query-rows query f)(call-with-array-converted-query-rows
[sql & params]
{:keys [as-arrays? fetch-size] :or {fetch-size 500} :as opts}
f)Calls (f rows), where rows is a lazy sequence of rows generated from within a transaction. Converts any java.sql.Array or (.isArray (class v)) values to a vector. The sequence is backed by an active database cursor which will be closed when f returns. Cancels the query if f throws an exception. The option names that correspond to jdbc/result-set-seq options will affect the rows produced as they do for that function. So for example, when as-arrays? is logically true, the result rows will be vectors, not maps, and the first result row will be a vector of column names. Note that this function is deprecated. Please prefer call-with-query-rows, and apply any necessary conversions directly to each row. (Most columns cannot be arrays.)
Calls (f rows), where rows is a lazy sequence of rows generated from within a transaction. Converts any java.sql.Array or (.isArray (class v)) values to a vector. The sequence is backed by an active database cursor which will be closed when f returns. Cancels the query if f throws an exception. The option names that correspond to jdbc/result-set-seq options will affect the rows produced as they do for that function. So for example, when as-arrays? is logically true, the result rows will be vectors, not maps, and the first result row will be a vector of column names. Note that this function is deprecated. Please prefer call-with-query-rows, and apply any necessary conversions directly to each row. (Most columns cannot be arrays.)
(call-with-query-rows query f)(call-with-query-rows [sql & params] {:keys [fetch-size] :as opts} f)Calls (f rows), where rows is a lazy sequence of rows generated from within a transaction. The sequence is backed by an active database cursor which will be closed when f returns. Cancels the query if f throws an exception. The option names that correspond to jdbc/result-set-seq options will affect the rows produced as they do for that function. So for example, when as-arrays? is logically true, the result rows will be vectors, not maps, and the first result row will be a vector of column names.
Calls (f rows), where rows is a lazy sequence of rows generated from within a transaction. The sequence is backed by an active database cursor which will be closed when f returns. Cancels the query if f throws an exception. The option names that correspond to jdbc/result-set-seq options will affect the rows produced as they do for that function. So for example, when as-arrays? is logically true, the result rows will be vectors, not maps, and the first result row will be a vector of column names.
(connection)Returns the db connection. Intended for use during the transition to next.jdbc. Throws if no transaction is open as a safety precaution, given the substantial differences between java.jdbc and next.jdbc's semantics. https://github.com/seancorfield/next-jdbc/blob/5c9d4795e31a71228f99d5c583b40b169921c5a8/doc/migration-from-clojure-java-jdbc.md#transactions
Returns the *db* connection. Intended for use during the transition to next.jdbc. Throws if no transaction is open as a safety precaution, given the substantial differences between java.jdbc and next.jdbc's semantics. https://github.com/seancorfield/next-jdbc/blob/5c9d4795e31a71228f99d5c583b40b169921c5a8/doc/migration-from-clojure-java-jdbc.md#transactions
(count-sql sql)Inputs: [sql :- String] Returns: String
Takes a sql string and returns a modified sql string that will select the count of results that would be returned by the original sql.
Inputs: [sql :- String] Returns: String Takes a sql string and returns a modified sql string that will select the count of results that would be returned by the original sql.
(delete! table where-clause)Calls clojure.jdbc/delete! after adding (jdbc/db) as the first argument.
Calls clojure.jdbc/delete! after adding (jdbc/db) as the first argument.
(disconnect-db db)Forcibly disconnects all connections to the named db. Requires that the current DB session has sufficient authorization.
Forcibly disconnects all connections to the named db. Requires that the current DB session has sufficient authorization.
(disconnect-db-role db user)Forcibly disconnects all connections from the specified role to the named db. Requires that the current DB session has sufficient authorization.
Forcibly disconnects all connections from the specified role to the named db. Requires that the current DB session has sufficient authorization.
(do-commands & commands)Runs the given commands in a transaction on the database given by (jdbc/db). If a command is a collection, converts it to a string via (clojure.string/join command).
Runs the given commands in a transaction on the database given by (jdbc/db). If a command is a collection, converts it to a string via (clojure.string/join command).
(do-prepared sql & params)Executes an optionally parametrized sql expression in a transaction on the database given by (jdbc/db). If a command is a collection, converts it to a string via (clojure.string/join command).
Executes an optionally parametrized sql expression in a transaction on the database given by (jdbc/db). If a command is a collection, converts it to a string via (clojure.string/join command).
(double-quote s)Given a string quote with double quotes and do proper SQL string escaping.
Given a string quote with double quotes and do proper SQL string escaping.
(escape-double-quotes s)Quote a string for SQL double quotes
Quote a string for SQL double quotes
(escape-single-quotes s)Quote a string for SQL single quotes
Quote a string for SQL single quotes
(get-result-count [count-sql & params])Inputs: [[count-sql & params]] Returns: s/Num
Takes a sql string, executes a COUNT statement against the database,
and returns the number of results that the original query would have returned.
Inputs: [[count-sql & params]] Returns: s/Num Takes a sql string, executes a `COUNT` statement against the database, and returns the number of results that the original query would have returned.
(has-database-privilege? user db privilege)Inputs: [user db privilege] Returns: s/Bool
Inputs: [user db privilege] Returns: s/Bool
(has-role? user role privilege)Inputs: [user role privilege] Returns: s/Bool
Inputs: [user role privilege] Returns: s/Bool
(in-clause coll)Create a prepared statement in clause, with a ? for every item in coll
Create a prepared statement in clause, with a ? for every item in coll
(in-clause-multi coll width)Create a prepared statement in clause, with a width-sized series of ? for
every item in coll.
Create a prepared statement in clause, with a `width`-sized series of ? for every item in coll.
(insert! table row)(insert! table columns values)Inserts a single row in either map form or lists of columns & values form. The database to use is given by (jdbc/db). Returns a one-element sequence with the inserted row as returned by the database.
Inserts a single row in either map form or lists of columns & values form. The database to use is given by (jdbc/db). Returns a one-element sequence with the inserted row as returned by the database.
(limited-query-to-vec limit query)Take a limit and an SQL query (with optional parameters), and return the result of the query as a vector. These results, unlike a normal query result, are not tied to the database connection and can be safely returned.
A value of 0 for limit is interpreted as 'no limit'. For any other value,
the function raises an error if the query would return more than limit
results.
Can be invoked in two ways: either passing the limit and the SQL query string, or the limit and a vector of the query string and parameters.
(limited-query-to-vec 1000 "select * from table") (limited-query-to-vec 1000 ["select * from table where column = ?" 12])
Take a limit and an SQL query (with optional parameters), and return the result of the query as a vector. These results, unlike a normal query result, are not tied to the database connection and can be safely returned. A value of `0` for `limit` is interpreted as 'no limit'. For any other value, the function raises an error if the query would return more than `limit` results. Can be invoked in two ways: either passing the limit and the SQL query string, or the limit and a vector of the query string and parameters. (limited-query-to-vec 1000 "select * from table") (limited-query-to-vec 1000 ["select * from table where column = ?" 12])
(local-timeout-ex? ex)Returns true if ex is an exception that might have been thrown as a result of the timeouts set by update-local-timeouts.
Returns true if ex is an exception that might have been thrown as a result of the timeouts set by update-local-timeouts.
(make-connection-pool db-spec)(make-connection-pool {:keys [subprotocol subname user username password
connection-timeout conn-max-age conn-lifetime
read-only? pool-name maximum-pool-size
expected-schema rewrite-batched-inserts]
:as _db-spec}
metrics-registry)Given a DB spec map containing :subprotocol, :subname, :user, and :password keys, return a pooled DB spec map (one containing just the :datasource key with a pooled DataSource object as the value). The returned pooled DB spec can be passed directly as the first argument to clojure.java.jdbc's functions.
Given a DB spec map containing :subprotocol, :subname, :user, and :password keys, return a pooled DB spec map (one containing just the :datasource key with a pooled DataSource object as the value). The returned pooled DB spec can be passed directly as the first argument to clojure.java.jdbc's functions.
(order-by-term->sql [field order])Given a list of legal result columns and a map containing a single order_by term, return the SQL string representing this term for use in an ORDER BY clause.
Given a list of legal result columns and a map containing a single order_by term, return the SQL string representing this term for use in an ORDER BY clause.
(paged-sql sql {:keys [limit offset order_by]})Inputs: [sql :- String {:keys [limit offset order_by]}] Returns: String
Given a sql string and a map of paging options, return a modified SQL string that contains the necessary LIMIT/OFFSET/ORDER BY clauses. The map of paging options can contain any of the following keys:
Note that if no paging options are specified, the original SQL will be returned completely unmodified.
Inputs: [sql :- String {:keys [limit offset order_by]}]
Returns: String
Given a sql string and a map of paging options, return a modified SQL string
that contains the necessary LIMIT/OFFSET/ORDER BY clauses. The map of paging
options can contain any of the following keys:
* :limit (int)
* :offset (int)
* :order_by (array of maps; each map is an order_by term, consisting of
required key :field and optional key :order. Legal values for :order
include 'asc' or 'desc'.)
Note that if no paging options are specified, the original SQL will be
returned completely unmodified.(pooled-datasource options)(pooled-datasource options metrics-registry)Given a database connection attribute map, return a JDBC datasource compatible with clojure.java.jdbc that is backed by a connection pool.
Given a database connection attribute map, return a JDBC datasource compatible with clojure.java.jdbc that is backed by a connection pool.
(query sql-params & remainder)Calls clojure.jdbc/query after adding (jdbc/db) as the first argument.
Calls clojure.jdbc/query after adding (jdbc/db) as the first argument.
(query-to-vec sql-query-and-params)(query-to-vec sql-query & params)Take an SQL query and parameters, and return the result of the query as a vector. These results, unlike a normal query result, are not tied to the database connection and can be safely returned.
Can be invoked in three ways: either passing the SQL query string, or a vector of the query string and substitutions, or you can pass multiple parameters inline.
(query-to-vec "select * from table") (query-to-vec ["select * from table where column = ?" 12]) (query-to-vec "select * from table where column = ?" 12)
Take an SQL query and parameters, and return the result of the query as a vector. These results, unlike a normal query result, are not tied to the database connection and can be safely returned. Can be invoked in three ways: either passing the SQL query string, or a vector of the query string and substitutions, or you can pass multiple parameters inline. (query-to-vec "select * from table") (query-to-vec ["select * from table where column = ?" 12]) (query-to-vec "select * from table where column = ?" 12)
(query-with-resultset [sql-string & params] func)(query-with-resultset [stmt & params] func)(query-with-resultset [options-map sql-string & params] func)Calls clojure.jdbc/db-query-with-resultset after adding (jdbc/db) as the first argument. Note that this will hold the whole resultset in memory due to the default jdbc fetchsize of 0. If streaming is required, use call-with-query-rows.
Calls clojure.jdbc/db-query-with-resultset after adding (jdbc/db) as the first argument. Note that this will hold the whole resultset in memory due to the default jdbc fetchsize of 0. If streaming is required, use call-with-query-rows.
(retry-sql max-attempts cancellation-timeout f)Tries (f) up to max-attempts times, ignoring "transient" exceptions (e.g. connection exceptions). When a cancellation-timeout is provided, also ignores cancelation exceptions (e.g. a statement_timeout). Throws any exception from the final attempt.
Tries (f) up to max-attempts times, ignoring "transient" exceptions (e.g. connection exceptions). When a cancellation-timeout is provided, also ignores cancelation exceptions (e.g. a statement_timeout). Throws *any* exception from the final attempt.
(retry-with-monitored-connection db-spec
status
{:keys [isolation statement-timeout]}
f)(single-quote s)Given a string quote with single quotes and do proper SQL string escaping.
Given a string quote with single quotes and do proper SQL string escaping.
(str-vec->array-literal strvec)Returns a properly quoted sql values literal representing strvecs as a row set of text[], e.g. ["x" ...] -> (array['x', ...]).
Returns a properly quoted sql values literal representing strvecs as a row set of text[], e.g. ["x" ...] -> (array['x', ...]).
(string->text-array-literal s)Escape string s for inclusion in a postgres text[] literal, e.g. "foo"bar" becomes the "foo\"bar" in '{"foo\"bar"}'
Escape string s for inclusion in a postgres text[] literal,
e.g. "foo\"bar" becomes the "foo\\\"bar" in
'{"foo\\\"bar"}'(table-count table)Returns the number of rows in the supplied table
Returns the number of rows in the supplied table
(update! table set-map where-clause)Calls clojure.jdbc/update! after adding (jdbc/db) as the first argument.
Calls clojure.jdbc/update! after adding (jdbc/db) as the first argument.
(update-local-timeouts deadline-ns min-ms)Sets the local timeouts (idle and statement) to respect deadline-ns unless the deadline has passed, then sets them to min-ms.
Sets the local timeouts (idle and statement) to respect deadline-ns unless the deadline has passed, then sets them to min-ms.
(valid-jdbc-query? q)Most SQL queries generated in the PuppetDB code base are represented internally as a vector whose first item is the SQL string (with optional '?' placeholders), and whose remaining items (if any) are simple data types that can be passed to a JDBC prepared statement as parameter values to bind to the placeholders in the SQL string. This function validates that a form complies to this structure. It is intended primarily for use in pre- and post-conditions, for validation.
Most SQL queries generated in the PuppetDB code base are represented internally as a vector whose first item is the SQL string (with optional '?' placeholders), and whose remaining items (if any) are simple data types that can be passed to a JDBC prepared statement as parameter values to bind to the placeholders in the SQL string. This function validates that a form complies to this structure. It is intended primarily for use in pre- and post-conditions, for validation.
Schema type for compiled query-eng queries
Schema type for compiled query-eng queries
(with-transacted-connection db-spec & body)Executes the body within a transaction with isolation level read-committed. Retries the transaction up to 5 times.
Executes the body within a transaction with isolation level read-committed. Retries the transaction up to 5 times.
(with-transacted-connection' db-spec tx-isolation-level & body)Executes the body within a transaction with the specified clojure.jdbc isolation level. If isolation is nil, the connection pool default (read committed) is used. Retries the transaction up to 5 times.
Executes the body within a transaction with the specified clojure.jdbc isolation level. If isolation is nil, the connection pool default (read committed) is used. Retries the transaction up to 5 times.
(with-transacted-connection-fn db-spec isolation f)Calls f within a transaction with the specified clojure.jdbc isolation level. If isolation is nil, the connection pool default (read committed) is used. Retries the transaction up to 5 times.
Calls f within a transaction with the specified clojure.jdbc isolation level. If isolation is nil, the connection pool default (read committed) is used. Retries the transaction up to 5 times.
cljdoc builds & hosts documentation for Clojure/Script libraries
| Ctrl+k | Jump to recent docs |
| ← | Move to previous article |
| → | Move to next article |
| Ctrl+/ | Jump to the search field |