honey.sql.helpers

(source)
Helper functions for the built-in clauses in honey.sql. All helper functions are inherently variadic. In general, `(helper :foo expr)` will produce `{:helper [:foo expr]}`, with a few exceptions: see the docstring of the helper function for details. Typical usage is threaded, like this: ``` (-> (select :a :b :c) (from :table) (where [:= :id 42]) (sql/format)) ``` Therefore all helpers can take an existing DSL expression as their first argument or, if the first argument is not a hash map, an empty DSL is assumed -- an empty hash map. The above is therefore equivalent to: ``` (-> {} (select :a :b :c) (from :table) (where [:= :id 42]) (sql/format)) ``` Some of the helper functions here have `:arglists` metadata in an attempt to provide better hints for auto-complete in editors but those `:arglists` _always omit the DSL argument_ to avoid duplicating the various argument lists. When you see an auto-complete suggestion like: bulk-collect-into [varname] [varname n] bear in mind that a DSL hash map can always be threaded in so the following (pseudo) arities are also available: bulk-collect-into [dsl varname] [dsl varname n] The actual arguments are: bulk-collect-info [& args] (as they are for all helper functions).
Public Variable Short Description
add-column (clj) Add a single column to a table (see `alter-table`).
add-index (clj) Like add-column, this accepts any number of SQL elements that describe a new index to be added: (add-index :unique :name-key :first-name :last-name) Produces: UNIQUE name_key(first_name, last_name).
alter-column (clj) Like add-column, accepts any number of SQL elements that describe the new column definition: (alter-column :name [:varchar 64] [:not nil]).
alter-table (clj) Alter table takes a SQL entity (the name of the table to modify) and any number of optional SQL clauses to be applied in a single statement.
bulk-collect-into (clj) Accepts a variable name, optionally followed by a limit expression.
columns (clj) To be used with `insert-into` to specify the list of column names for the insert operation.
compare-with (clj) Accepts a time interval such as: (compare-with 1 :week :ago) Produces: COMPARE WITH 1 WEEK AGO.
composite (clj) Accepts any number of SQL expressions and produces a composite value from them: (composite :a 42) Produces: (a, ?) Parameters: 42.
create-extension (clj) Accepts an extension name to create and optionally a flag to trigger IF NOT EXISTS in the SQL: (create-extension :postgis) (create-extension :postgis :if-not-exists).
create-index (clj) Accepts an index spexification and a column specification.
create-materialized-view (clj) Accepts a single view name to create.
create-or-replace-view (clj) Accepts a single view name to create.
create-table (clj) Accepts a table name to create and optionally a flag to trigger IF NOT EXISTS in the SQL: (create-table :foo) (create-table :foo :if-not-exists).
create-table-as (clj) Accepts a table name to create and optionally a flag to trigger IF NOT EXISTS in the SQL: (create-table-as :foo) (create-table-as :foo :if-not-exists).
create-view (clj) Accepts a single view name to create.
cross-join (clj) Accepts one or more CROSS JOIN expressions.
delete (clj) For deleting from multiple tables.
delete-from (clj) For deleting from a single table.
distinct (clj) Like `select-distinct` but produces DISTINCT...
do-nothing (clj) Called with no arguments, produces DO NOTHING.
do-update-set (clj) Accepts one or more columns to update, or a hash map of column/value pairs (like `set`), optionally followed by a `WHERE` clause.
drop-column (clj) Takes one or more column names (use with `alter-table`).
drop-extension (clj) Accepts one or more extension names to drop.
drop-index (clj) Like drop-table, accepts a single index name: (drop-index :name-key).
drop-materialized-view (clj) Accepts one or more materialied view names to drop.
drop-table (clj) Accepts one or more table names to drop.
drop-view (clj) Accepts one or more view names to drop.
except (clj) Accepts any number of SQL clauses (queries) on which to perform a set except.
except-all (clj) Accepts any number of SQL clauses (queries) on which to perform a set except all.
expr (clj) Like `distinct` but produces ...
facet (clj) Accepts any number of column names, or column/alias pairs, or SQL expressions (optionally aliased): (facet :id [:foo :bar] [[:max :quux]]) Produces: FACET id, foo AS bar, MAX(quux).
fetch (clj) Accepts a single SQL expression: (fetch 10) Produces: FETCH ? ONLY Parameters: 10.
filter (clj) Accepts alternating expressions and clauses and produces a FILTER expression: (filter :%count.* (where :> i 5)) Produces: COUNT(*) FILTER (WHERE i > ?) Parameters: 5.
for (clj) Accepts a lock strength, optionally followed by one or more table names, optionally followed by a qualifier.
from (clj) Accepts one or more table names, or table/alias pairs.
full-join (clj) Accepts one or more FULL JOIN expressions.
generic-helper-unary (clj) Clauses that accept only a single item can be implemented using this helper, as: (defn my-helper [& args] (generic-helper-unary :my-clause args)) Even though your helper is designed for clauses that accept only a single item, you should still define it as variadic, because that is the convention all helpers use here.
generic-helper-variadic (clj) Most clauses that accept a sequence of items can be implemented using this helper, as: (defn my-helper [& args] (generic-helper-variadic :my-clause args)).
group-by (clj) Accepts one or more SQL expressions to group by.
having (clj) Like `where`, accepts one or more SQL expressions (conditions) and combines them with AND (by default): (having [:> :count 0] [:<> :name nil]) or: (having :and [:> :count 0] [:<> :name nil]) Produces: HAVING (count > ?) AND (name IS NOT NULL) Parameters: 0 (having :> :count 0) Produces: HAVING count > ? Parameters: 0 (having :or [:> :count 0] [:= :name ""]) Produces: HAVING (count > ?) OR (name = ?) Parameters: 0 "".
inner-join (clj) An alternative name to `join`, this accepts one or more INNER JOIN expressions.
insert-into (clj) Accepts a table name or a table/alias pair.
intersect (clj) Accepts any number of SQL clauses (queries) on which to perform a set intersection.
into (clj) Accepts table name, optionally followed a database name.
join (clj) Accepts one or more (INNER) JOIN expressions.
join-by (clj) Accepts a sequence of join clauses to be generated in a specific order.
lateral (clj) Accepts a SQL clause or a SQL expression: (lateral (-> (select '*) (from 'foo))) (lateral '(calc_value bar)) Produces: LATERAL (SELECT * FROM foo) LATERAL CALC_VALUE(bar).
left-join (clj) Accepts one or more LEFT JOIN expressions.
limit (clj) Specific to some databases (notabley MySQL), accepts a single SQL expression: (limit 40) Produces: LIMIT ? Parameters: 40 The two-argument syntax is not supported: use `offset` instead: `LIMIT 20,10` is equivalent to `LIMIT 10 OFFSET 20` (-> (limit 10) (offset 20)).
lock (clj) Intended for MySQL, this accepts a lock mode.
modify-column (clj) Like add-column, accepts any number of SQL elements that describe the new column definition: (modify-column :name [:varchar 64] [:not nil]) MySQL-specific, deprecated.
offset (clj) Accepts a single SQL expression: (offset 10) Produces: OFFSET ? Parameters: 10.
on-conflict (clj) Accepts zero or more SQL entities (keywords or symbols), optionally followed by a single SQL clause (`{:where <condition>}`).
on-constraint (clj) Accepts a single constraint name.
on-duplicate-key-update (clj) MySQL's upsert facility.
order-by (clj) Accepts one or more expressions to order by.
outer-join (clj) Accepts one or more OUTER JOIN expressions.
over (clj) Accepts any number of OVER clauses, each of which is a pair of an aggregate function and a window function or a triple of an aggregate function, a window function, and an alias: (select :id (over [[:avg :salary] (partition-by :department)])) Produces: SELECT id, AVG(salary) OVER ()PARTITION BY department).
partition-by (clj) Accepts one or more columns or SQL expressions to partition by as part of a `WINDOW` expression.
refresh-materialized-view (clj) Accepts a materialied view name to refresh.
rename-column (clj) Accepts two column names: the original name and the new name to which it should be renamed: (rename-column :name :full-name).
rename-table (clj) Accepts a single table name and, despite its name, actually means RENAME TO: (alter-table :foo (rename-table :bar)) Produces: ALTER TABLE foo RENAME TO bar.
replace-into (clj) Accepts a table name or a table/alias pair.
returning (clj) Accepts any number of column names to return from an insert operation: (returning :*) and (returning :a :b) Produce: RETURNING * and RETURNING a, b respectively.
right-join (clj) Accepts one or more RIGHT JOIN expressions.
select (clj) Accepts any number of column names, or column/alias pairs, or SQL expressions (optionally aliased): (select :id [:foo :bar] [[:max :quux]]) Produces: SELECT id, foo AS bar, MAX(quux) The special column name :* produces * for 'all columns'.
select-distinct (clj) Like `select` but produces SELECT DISTINCT.
select-distinct-on (clj) Accepts a sequence of one or more columns for the distinct clause, followed by any number of column names, or column/alias pairs, or SQL expressions (optionally aliased), as for `select`: (select-distinct-on [:a :b] :c [:d :dd]) Produces: SELECT DISTINCT ON(a, b) c, d AS dd.
select-distinct-top (clj) Like `select-top` but produces SELECT DISTINCT TOP...
select-top (clj) Accepts a TOP expression, followed by any number of column names, or column/alias pairs, or SQL expressions (optionally aliased), as for `select`.
set (clj) Accepts a hash map specifying column names and the values to be assigned to them, as part of `update`: (-> (update :foo) (set {:a 1 :b nil})) Produces: UPDATE foo SET a = ?, b = NULL.
since (clj) Accepts a time interval such as: (since 2 :days :ago) Produces: SINCE 2 DAYS AGO.
table (clj) Accepts a single table name and produces TABLE name This is equivalent to: SELECT * FROM name.
timeseries (clj) Accepts a time interval such as: (timeseries 1 :day) or: (timeseries :auto) Produces: TIMESERIES 1 DAY Or: TIMESERIES AUTO.
truncate (clj) Accepts a single table name to truncate.
union (clj) Accepts any number of SQL clauses (queries) on which to perform a set union.
union-all (clj) Accepts any number of SQL clauses (queries) on which to perform a set union all.
until (clj) Accepts a time interval such as: (until 1 :month :ago) Produces: UNTIL 1 MONTH AGO.
update (clj) Accepts either a table name or a table/alias pair.
upsert (clj) Provided purely to ease migration from nilenso/honeysql-postgres this accepts a single clause, constructed from on-conflict, do-nothing or do-update-set, and where.
using (clj) Accepts similar arguments to `select` as part of a SQL `USING` clause.
values (clj) Accepts a single argument: a collection of row values.
where (clj) Accepts one or more SQL expressions (conditions) and combines them with AND (by default): (where [:= :status 0] [:<> :task "backup"]) or: (where :and [:= :status 0] [:<> :task "backup"]) Produces: WHERE (status = ?) AND (task <> ?) Parameters: 0 "backup" For a single expression, the brackets can be omitted: (where := :status 0) ; same as (where [:= :status 0]) With multiple expressions, the conjunction may be specified as a leading symbol: (where :or [:= :status 0] [:= :task "stop"]) Produces: WHERE (status = 0) OR (task = ?) Parameters: 0 "stop".
window (clj) Accepts a window name followed by a partition by clause.
with (clj) Accepts one or more CTE definitions.
with-columns (clj) Accepts any number of column descriptions.
with-data (clj) Accepts a Boolean determining WITH DATA vs WITH NO DATA.
with-recursive (clj) Accepts one or more CTE definitions.
within-group (clj) Accepts alternating expressions and clauses and produces a WITHIN GROUP expression: (within-group :%count.* (where :> i 5)) Produces: COUNT(*) WITHIN GROUP (WHERE i > ?) Parameters: 5.