New Upstream Release - honeysql-clojure
Ready changes
Summary
Merged new upstream version: 2.4.1026 (was: 2.4.980).
Resulting package
Built on 2023-04-19T19:54 (took 5m17s)
The resulting binary packages can be installed (if you have the apt repository enabled) by running one of:
apt install -t fresh-releases libhoneysql-clojure
Lintian Result
Diff
diff --git a/.github/workflows/test-and-release.yml b/.github/workflows/test-and-release.yml
index 5b275e4..8dbc800 100644
--- a/.github/workflows/test-and-release.yml
+++ b/.github/workflows/test-and-release.yml
@@ -9,19 +9,19 @@ jobs:
build-and-release:
runs-on: ubuntu-latest
steps:
- - uses: actions/checkout@v2
+ - uses: actions/checkout@v3
with:
fetch-depth: 0
- - uses: actions/setup-java@v2
+ - uses: actions/setup-java@v3
with:
distribution: 'adopt'
java-version: '11'
- name: Setup Clojure
uses: DeLaGuardo/setup-clojure@master
with:
- cli: '1.10.3.1053'
+ cli: '1.11.1.1273'
- name: Cache All The Things
- uses: actions/cache@v2
+ uses: actions/cache@v3
with:
path: |
~/.m2/repository
diff --git a/.github/workflows/test-and-snapshot.yml b/.github/workflows/test-and-snapshot.yml
index 4e64c9f..48162fc 100644
--- a/.github/workflows/test-and-snapshot.yml
+++ b/.github/workflows/test-and-snapshot.yml
@@ -9,17 +9,17 @@ jobs:
build-and-snapshot:
runs-on: ubuntu-latest
steps:
- - uses: actions/checkout@v2
- - uses: actions/setup-java@v2
+ - uses: actions/checkout@v3
+ - uses: actions/setup-java@v3
with:
distribution: 'adopt'
java-version: '11'
- name: Setup Clojure
uses: DeLaGuardo/setup-clojure@master
with:
- cli: '1.10.3.1053'
+ cli: '1.11.1.1273'
- name: Cache All The Things
- uses: actions/cache@v2
+ uses: actions/cache@v3
with:
path: |
~/.m2/repository
@@ -39,19 +39,19 @@ jobs:
runs-on: ubuntu-latest
strategy:
matrix:
- java: [ '8', '14', '17' ]
+ java: [ '8', '14', '17', '19' ]
steps:
- - uses: actions/checkout@v2
- - uses: actions/setup-java@v2
+ - uses: actions/checkout@v3
+ - uses: actions/setup-java@v3
with:
distribution: 'adopt'
java-version: ${{ matrix.java }}
- name: Clojure CLI
uses: DeLaGuardo/setup-clojure@master
with:
- cli: '1.10.3.1053'
+ cli: '1.11.1.1273'
- name: Cache All The Things
- uses: actions/cache@v2
+ uses: actions/cache@v3
with:
path: |
~/.m2/repository
diff --git a/.github/workflows/test.yml b/.github/workflows/test.yml
index 10500d7..b04424b 100644
--- a/.github/workflows/test.yml
+++ b/.github/workflows/test.yml
@@ -7,19 +7,19 @@ jobs:
runs-on: ubuntu-latest
strategy:
matrix:
- java: [ '8', '11', '14', '17' ]
+ java: [ '8', '11', '14', '17', '19' ]
steps:
- - uses: actions/checkout@v2
- - uses: actions/setup-java@v2
+ - uses: actions/checkout@v3
+ - uses: actions/setup-java@v3
with:
distribution: 'adopt'
java-version: ${{ matrix.java }}
- name: Clojure CLI
uses: DeLaGuardo/setup-clojure@master
with:
- cli: '1.10.3.1053'
+ cli: '1.11.1.1273'
- name: Cache All The Things
- uses: actions/cache@v2
+ uses: actions/cache@v3
with:
path: |
~/.m2/repository
diff --git a/CHANGELOG.md b/CHANGELOG.md
index 12c950e..3a061e4 100644
--- a/CHANGELOG.md
+++ b/CHANGELOG.md
@@ -1,5 +1,76 @@
# Changes
+* 2.4.1026 -- 2023-04-15
+ * Fix [#486](https://github.com/seancorfield/honeysql/issues/486) by supporting ANSI-style `INTERVAL` syntax.
+ * Fix [#485](https://github.com/seancorfield/honeysql/issues/485) by adding `:with-ordinality` "operator".
+ * Fix [#484](https://github.com/seancorfield/honeysql/issues/484) by adding `TABLE` to `TRUNCATE`.
+ * Fix [#483](https://github.com/seancorfield/honeysql/issues/483) by adding a function-like `:join` syntax to produce nested `JOIN` expressions.
+ * Update `tools.build`; split alias `:test`/`:runner` for friendlier jack-in UX while developing.
+
+* 2.4.1011 -- 2023-03-23
+ * Address [#481](https://github.com/seancorfield/honeysql/issues/481) by adding more examples around `:do-update-set`.
+ * Address [#480](https://github.com/seancorfield/honeysql/issues/480) by clarifying the general relationship between clauses and helpers.
+ * Address [#448](https://github.com/seancorfield/honeysql/issues/448) by adding a new section with hints and tips for database-specific syntax and solutions.
+
+* 2.4.1006 -- 2023-03-17
+ * Fix [#478](https://github.com/seancorfield/honeysql/issues/478) by handling `:do-update-set` correctly in the `upsert` helper and by handling parameters correctly in the `:do-update-set` formatter.
+ * Fix [#476](https://github.com/seancorfield/honeysql/issues/476) by adding support for multiple arguments to `:raw`, essentially restoring 1.x functionality (while still allowing for embedded vectors as expressions, introduced in 2.x).
+
+* 2.4.1002 -- 2023-03-03
+ * Address [#474](https://github.com/seancorfield/honeysql/issues/474) by adding dot-selection special syntax.
+ * Improve docstrings for PostgreSQL operators via PR [#473](https://github.com/seancorfield/honeysql/pull/473) [@holyjak](https://github.com/holyjak).
+ * Address [#471](https://github.com/seancorfield/honeysql/issues/471) by supporting interspersed SQL keywords in function calls.
+ * Fix [#467](https://github.com/seancorfield/honeysql/issues/467) by allowing single keywords (symbols) as a short hand for a single-element sequence in more constructs via PR [#470](https://github.com/seancorfield/honeysql/pull/470) [@p-himik](https://github.com/p-himik).
+ * Address [#466](https://github.com/seancorfield/honeysql/issues/466) by treating `[:and]` as `TRUE` and `[:or]` as `FALSE`.
+ * Fix [#465](https://github.com/seancorfield/honeysql/issues/465) to allow multiple columns in `:order-by` special syntax via PR [#468](https://github.com/seancorfield/honeysql/pull/468) [@p-himik](https://github.com/p-himik).
+ * Fix [#464](https://github.com/seancorfield/honeysql/issues/464) by adding an optional type argument to `:array` via PR [#469](https://github.com/seancorfield/honeysql/pull/469) [@p-himik](https://github.com/p-himik).
+ * Address [#463](https://github.com/seancorfield/honeysql/issues/463) by explaining `:quoted nil` via PR [#475](https://github.com/seancorfield/honeysql/pull/475) [@nharsch](https://github.com/nharsch).
+ * Address [#462](https://github.com/seancorfield/honeysql/issues/462) by adding a note in the documentation for set operations, clarifying precedence issues.
+
+* 2.4.980 -- 2023-02-15
+ * Fix [#461](https://github.com/seancorfield/honeysql/issues/461) -- a regression introduced in 2.4.979 -- by restricting unary operators to just `+`, `-`, and `~` (bitwise negation).
+
+* 2.4.979 -- 2023-02-11
+ * Address [#459](https://github.com/seancorfield/honeysql/issues/459) by making all operators variadic (except `:=` and `:<>`).
+ * Address [#458](https://github.com/seancorfield/honeysql/issues/458) by adding `registered-*?` predicates.
+
+* 2.4.972 -- 2023-02-02
+ * Address [#456](https://github.com/seancorfield/honeysql/issues/456) by allowing `format` to handle expressions (like 1.x could) as well as statements. This should aid with migration from 1.x to 2.x.
+
+* 2.4.969 -- 2023-01-14
+ * Fix [#454](https://github.com/seancorfield/honeysql/issues/454) by allowing `-` to be variadic.
+ * Address [#452](https://github.com/seancorfield/honeysql/pull/452) by adding `:replace-into` to the core SQL supported, instead of just for the MySQL and SQLite dialects (so the latter is not needed yet).
+ * Address [#451](https://github.com/seancorfield/honeysql/issues/451) by adding a test for it, showing how `:nest` produces the desired result.
+ * Address [#447](https://github.com/seancorfield/honeysql/issues/447) by updating GitHub Actions and dependencies.
+ * Address [#445](https://github.com/seancorfield/honeysql/issues/445) and [#453](https://github.com/seancorfield/honeysql/issues/453) by adding key/constraint examples to `CREATE TABLE` docs.
+
+* 2.4.962 -- 2022-12-17
+ * Fix `set-options!` (only `:checking` worked in 2.4.947).
+ * Fix `:cast` formatting when quoting is enabled, via PR [#443](https://github.com/seancorfield/honeysql/pull/443) [duddlf23](https://github.com/duddlf23).
+ * Fix [#441](https://github.com/seancorfield/honeysql/issues/441) by adding `:replace-into` to in-flight clause order (as well as registering it for the `:mysql` dialect).
+ * Fix [#434](https://github.com/seancorfield/honeysql/issues/434) by special-casing `:'ARRAY`.
+ * Fix [#433](https://github.com/seancorfield/honeysql/issues/433) by supporting additional `WITH` syntax, via PR [#432](https://github.com/seancorfield/honeysql/issues/432), [@MawiraIke](https://github.com/MawiraIke). _[Technically, this was in 2.4.947, but I kept the issue open while I wordsmithed the documentation]_
+ * Address [#405](https://github.com/seancorfield/honeysql/issues/405) by adding `:numbered` option, which can also be set globally using `set-options!`.
+
+* 2.4.947 -- 2022-11-05
+ * Fix [#439](https://github.com/seancorfield/honeysql/issues/439) by rewriting how DDL options are processed; also fixes [#386](https://github.com/seancorfield/honeysql/issues/386) and [#437](https://github.com/seancorfield/honeysql/issues/437); **Whilst this is intended to be purely a bug fix, it has the potential to be a breaking change -- hence the version jump to 2.4!**
+ * Fix [#438](https://github.com/seancorfield/honeysql/issues/438) by
+ supporting options on `TRUNCATE`.
+ * Address [#435](https://github.com/seancorfield/honeysql/issues/435) by showing `CREATE TEMP TABLE` etc.
+ * Fix [#431](https://github.com/seancorfield/honeysql/issues/431) -- `WHERE false` differed between the DSL and the `where` helper.
+ * Address [#430](https://github.com/seancorfield/honeysql/issues/430) by treating `:'` as introducing a name that should be treated literally and not formatted as a SQL entity (which respects quoting, dot-splitting, etc); this effectively expands the "escape hatch" introduced via [#352](https://github.com/seancorfield/honeysql/issues/352) in 2.2.868. _Note that the function context behavior formats as a SQL entity, rather than the usual SQL "keyword", whereas this new context is a literal transcription rather than as a SQL entity!_
+ * Address [#427](https://github.com/seancorfield/honeysql/issues/427) by adding `set-options!`.
+ * Address [#415](https://github.com/seancorfield/honeysql/issues/415) by supporting multiple column names in `ADD COLUMN`, `ALTER COLUMN`, `DROP COLUMN`, and `MODIFY COLUMN`.
+
+* 2.3.928 -- 2022-09-04
+ * Address [#425](https://github.com/seancorfield/honeysql/issues/425) by clarifying that `INTERVAL` as special syntax may be MySQL-specific and PostgreSQL uses difference syntax (because `INTERVAL` is a data type there).
+ * Address [#423](https://github.com/seancorfield/honeysql/issues/423) by supporting `DEFAULT` values and `DEFAULT` rows in `VALUES`.
+ * Address [#422](https://github.com/seancorfield/honeysql/issues/422) by auto-quoting unusual entity names when `:quoted` (and `:dialect`) are not specified, making HoneySQL more secure by default.
+ * Fix [#421](https://github.com/seancorfield/honeysql/issues/421) by adding `:replace-into` for `:mysql` dialect.
+ * Address [#419](https://github.com/seancorfield/honeysql/issues/419) by adding `honey.sql.protocols` and `InlineValue` with a `sqlize` function.
+ * Address [#413](https://github.com/seancorfield/honeysql/issues/413) by flagging a lack of `WHERE` clause for `DELETE`, `DELETE FROM`, and `UPDATE` when `:checking :basic` (or `:checking :strict`).
+ * Fix [#392](https://github.com/seancorfield/honeysql/issues/392) by adding support for `WITH` / (`NOT`) `MATERIALIZED` -- via PR [#420](https://github.com/seancorfield/honeysql/issues/420) [@robhanlon22](https://github.com/robhanlon22).
+
* 2.3.911 -- 2022-07-29
* Address [#418](https://github.com/seancorfield/honeysql/issues/418) by documenting a potential "gotcha" with multi-column `IN` expressions (a change from HoneySQL 1.x).
* Fix [#416](https://github.com/seancorfield/honeysql/issues/416) via PR [#417](https://github.com/seancorfield/honeysql/issues/417) from [@corasaurus-hex](https://github.com/corasaurus-hex) -- using the internal default state for the integrity assertion.
@@ -25,7 +96,7 @@
* 2.2.868 -- 2022-02-21
* Address [#387](https://github.com/seancorfield/honeysql/issues/387) by making the function simpler.
* Fix [#385](https://github.com/seancorfield/honeysql/issues/385) by quoting inlined UUIDs.
- * Address [#352](https://github.com/seancorfield/honeysql/issues/352) by treating `:'` as introducing a function name that should be formatted as a SQL entity (respects quoting, dot-splitting, etc).
+ * Address [#352](https://github.com/seancorfield/honeysql/issues/352) by treating `:'` as introducing a function name that should be formatted as a SQL entity (which respects quoting, dot-splitting, etc), rather than as a SQL "keyword".
* 2.2.861 -- 2022-01-30
* Address [#382](https://github.com/seancorfield/honeysql/issues/382) by adding `:case-expr` for BigQuery support.
diff --git a/README.md b/README.md
index 4cb1fc6..50956dd 100644
--- a/README.md
+++ b/README.md
@@ -4,11 +4,12 @@ SQL as Clojure data structures. Build queries programmatically -- even at runtim
## Build
-[](https://clojars.org/com.github.seancorfield/honeysql) [](https://cljdoc.org/d/com.github.seancorfield/honeysql/CURRENT)
+[](https://clojars.org/com.github.seancorfield/honeysql) [](https://cljdoc.org/d/com.github.seancorfield/honeysql/CURRENT) [](https://clojurians.slack.com/app_redirect?channel=honeysql)
+
This project follows the version scheme MAJOR.MINOR.COMMITS where MAJOR and MINOR provide some relative indication of the size of the change, but do not follow semantic versioning. In general, all changes endeavor to be non-breaking (by moving to new names rather than by breaking existing names). COMMITS is an ever-increasing counter of commits since the beginning of this repository.
-> Note: every commit to the **develop** branch runs CI (GitHub Actions) and successful runs push a MAJOR.MINOR.999-SNAPSHOT build to Clojars so the very latest version of HoneySQL is always available either via that [snapshot on Clojars](https://clojars.org/com.github.seancorfield/honeysql) or via a git dependency on the latest SHA.
+> Note: every commit to the **develop** branch runs CI (GitHub Actions) and successful runs push a MAJOR.MINOR.9999-SNAPSHOT build to Clojars so the very latest version of HoneySQL is always available either via that [snapshot on Clojars](https://clojars.org/com.github.seancorfield/honeysql) or via a git dependency on the latest SHA.
HoneySQL 2.x requires Clojure 1.9 or later.
@@ -116,6 +117,13 @@ If you want to format the query as a string with no parameters (e.g. to use the
=> ["SELECT a, b, c FROM foo WHERE foo.a = 'baz'"]
```
+As seen above, the default parameterization uses positional parameters (`?`) with the order of values in the generated vector matching the order of those placeholders in the SQL. As of 2.4.962, you can specified `:numbered true` as an option to produce numbered parameters (`$1`, `$2`, etc):
+
+```clojure
+(sql/format sqlmap {:numbered true})
+=> ["SELECT a, b, c FROM foo WHERE foo.a = $1" "baz"]
+```
+
Namespace-qualified keywords (and symbols) are generally treated as table-qualified columns: `:foo/bar` becomes `foo.bar`, except in contexts where that would be illegal (such as the list of columns in an `INSERT` statement). This approach is likely to be more compatible with code that uses libraries like [`next.jdbc`](https://github.com/seancorfield/next-jdbc) and [`seql`](https://github.com/exoscale/seql), as well as being more convenient in a world of namespace-qualified keywords, following the example of `clojure.spec` etc.
```clojure
@@ -150,6 +158,10 @@ function in the `honey.sql.helpers` namespace:
=> {:select [:a :b :c] :from [:foo] :where [:= :foo.a "baz"]}
```
+In general, `(helper :foo expr)` will produce `{:helper [:foo expr]}`
+(with a few exceptions -- see the docstring of the helper function
+for details).
+
Order doesn't matter (for independent clauses):
```clojure
@@ -388,6 +400,19 @@ INSERT INTO comp_table
VALUES (?, (?, ?)), (?, (?, ?))
"
"small" 1 "inch" "large" 10 "feet"]
+;; with numbered parameters:
+(-> (insert-into :comp_table)
+ (columns :name :comp_column)
+ (values
+ [["small" (composite 1 "inch")]
+ ["large" (composite 10 "feet")]])
+ (sql/format {:pretty true :numbered true}))
+=> ["
+INSERT INTO comp_table
+(name, comp_column)
+VALUES ($1, ($2, $3)), ($4, ($5, $6))
+"
+"small" 1 "inch" "large" 10 "feet"]
;; or as pure data DSL:
(-> {:insert-into [:comp_table],
:columns [:name :comp_column],
@@ -493,11 +518,11 @@ If you want to delete everything from a table, you can use `truncate`:
```clojure
(-> (truncate :films)
(sql/format))
-=> ["TRUNCATE films"]
+=> ["TRUNCATE TABLE films"]
;; or as pure data DSL:
(-> {:truncate :films}
(sql/format))
-=> ["TRUNCATE films"]
+=> ["TRUNCATE TABLE films"]
```
### Set operations
@@ -518,6 +543,8 @@ There are also helpers for each of those:
=> ["SELECT * FROM foo UNION SELECT * FROM bar"]
```
+> Note: different databases have different precedence rules for these set operations when used in combination -- you may need to use `:nest` to add `(` .. `)` in order to combine these operations in a single SQL statement, if the natural order produced by HoneySQL does not work "as expected" for your database.
+
### Functions
Function calls (and expressions with operators) can be specified as
@@ -530,6 +557,8 @@ vectors where the first element is either a keyword or a symbol:
=> ["SELECT * FROM foo WHERE date_created > DATE_ADD(NOW(), INTERVAL ? HOURS)" 24]
```
+> Note: The above example may be specific to MySQL but the general principle of vectors for function calls applies to all dialects.
+
A shorthand syntax also exists for simple function calls:
keywords that begin with `%` are interpreted as SQL function calls:
@@ -594,6 +623,8 @@ unless you have quoting enabled:
=> ["SELECT * FROM `foo` WHERE `my-schema`.`SomeFunction`(`bar`, ?)" 0]
```
+> Note: in non-function contexts, if a keyword begins with `'`, it is transcribed into the SQL exactly as-is, with no case or character conversion at all.
+
### Bindable parameters
Keywords that begin with `?` are interpreted as bindable parameters:
@@ -604,6 +635,12 @@ Keywords that begin with `?` are interpreted as bindable parameters:
(where [:= :a :?baz])
(sql/format {:params {:baz "BAZ"}}))
=> ["SELECT id FROM foo WHERE a = ?" "BAZ"]
+;; or with numbered parameters:
+(-> (select :id)
+ (from :foo)
+ (where [:= :a :?baz])
+ (sql/format {:params {:baz "BAZ"} :numbered true}))
+=> ["SELECT id FROM foo WHERE a = $1" "BAZ"]
;; or as pure data DSL:
(-> {:select [:id], :from [:foo], :where [:= :a :?baz]}
(sql/format {:params {:baz "BAZ"}}))
@@ -698,16 +735,16 @@ have a lot of function calls needed in code:
(sql/format {:pretty true}))
=> ["
INSERT INTO sample
-(location) VALUES (ST_SETSRID(ST_MAKEPOINT(?, ?), CAST(? AS integer)))
+(location) VALUES (ST_SETSRID(ST_MAKEPOINT(?, ?), CAST(? AS INTEGER)))
"
0.291 32.621 4325]
```
-#### Identifiers
+#### Entity Names
-To quote identifiers, pass the `:quoted true` option to `format` and they will
+To quote SQL entity names, pass the `:quoted true` option to `format` and they will
be quoted according to the selected dialect. If you override the dialect in a
-`format` call, by passing the `:dialect` option, identifiers will be automatically
+`format` call, by passing the `:dialect` option, SQL entity names will be automatically
quoted. You can override the dialect and turn off quoting by passing `:quoted false`.
Valid `:dialect` options are `:ansi` (the default, use this for PostgreSQL),
`:mysql`, `:oracle`, or `:sqlserver`:
@@ -828,6 +865,24 @@ LIMIT ?
OFFSET ?
"
"bort" "gabba" 1 2 2 3 1 2 3 10 20 0 50 10]
+;; with numbered parameters:
+(sql/format big-complicated-map
+ {:params {:param1 "gabba" :param2 2}
+ :pretty true :numbered true})
+=> ["
+SELECT DISTINCT f.*, b.baz, c.quux, b.bla AS \"bla-bla\", NOW(), @x := 10
+FROM foo AS f, baz AS b
+INNER JOIN draq ON f.b = draq.x INNER JOIN eldr ON f.e = eldr.t
+LEFT JOIN clod AS c ON f.a = c.d
+RIGHT JOIN bock ON bock.z = c.e
+WHERE ((f.a = $1) AND (b.baz <> $2)) OR (($3 < $4) AND ($5 < $6)) OR (f.e IN ($7, $8, $9)) OR f.e BETWEEN $10 AND $11
+GROUP BY f.a, c.e
+HAVING $12 < f.e
+ORDER BY b.baz DESC, c.quux ASC, f.a NULLS FIRST
+LIMIT $13
+OFFSET $14
+"
+"bort" "gabba" 1 2 2 3 1 2 3 10 20 0 50 10]
```
```clojure
;; Printable and readable
@@ -845,11 +900,9 @@ If your database supports `<=>` as an operator, you can tell HoneySQL about it u
```clojure
(sql/register-op! :<=>)
-;; default is a binary operator:
+;; all operators are assumed to be variadic:
(-> (select :a) (where [:<=> :a "foo"]) sql/format)
=> ["SELECT a WHERE a <=> ?" "foo"]
-;; you can declare that an operator is variadic:
-(sql/register-op! :<=> :variadic true)
(-> (select :a) (where [:<=> "food" :a "fool"]) sql/format)
=> ["SELECT a WHERE ? <=> a <=> ?" "food" "fool"]
```
@@ -878,8 +931,13 @@ Or perhaps your database supports syntax like `a BETWIXT b AND c`, in which case
;; example usage:
(-> (select :a) (where [:betwixt :a 1 10]) sql/format)
=> ["SELECT a WHERE a BETWIXT ? AND ?" 1 10]
+;; with numbered parameters:
+(-> (select :a) (where [:betwixt :a 1 10]) (sql/format {:numbered true}))
+=> ["SELECT a WHERE a BETWIXT $1 AND $2" 1 10]
```
+> Note: the generation of positional placeholders (`?`) or numbered placeholders (`$1`, `$2`, etc) is handled automatically by `format-expr` so you get this behavior "for free" in your extensions, as long as you use the public API for `honey.sql`. You should avoid writing extensions that generate placeholders directly if you want them to work with numbered parameters.
+
You can also register SQL clauses, specifying the keyword, the formatting function, and an existing clause that this new clause should be processed before:
```clojure
@@ -905,6 +963,6 @@ If you find yourself registering an operator, a function (syntax), or a new clau
## License
-Copyright (c) 2020-2021 Sean Corfield. HoneySQL 1.x was copyright (c) 2012-2020 Justin Kramer and Sean Corfield.
+Copyright (c) 2020-2022 Sean Corfield. HoneySQL 1.x was copyright (c) 2012-2020 Justin Kramer and Sean Corfield.
Distributed under the Eclipse Public License, the same as Clojure.
diff --git a/build.clj b/build.clj
index 6c65ce6..af2d90f 100644
--- a/build.clj
+++ b/build.clj
@@ -13,19 +13,36 @@
clojure -A:deps -T:build help/doc"
(:refer-clojure :exclude [test])
- (:require [clojure.tools.build.api :as b]
- [org.corfield.build :as bb]))
+ (:require [clojure.string :as str]
+ [clojure.tools.build.api :as b]
+ [clojure.tools.deps :as t]
+ [deps-deploy.deps-deploy :as dd]))
(def lib 'com.github.seancorfield/honeysql)
-(defn- the-version [patch] (format "2.3.%s" patch))
+(defn- the-version [patch] (format "2.4.%s" patch))
(def version (the-version (b/git-count-revs nil)))
-(def snapshot (the-version "999-SNAPSHOT"))
+(def snapshot (the-version "9999-SNAPSHOT"))
+(def class-dir "target/classes")
+
+(defn- run-task [aliases]
+ (println "\nRunning task for" (str/join "," (map name aliases)))
+ (let [basis (b/create-basis {:aliases aliases})
+ combined (t/combine-aliases basis aliases)
+ cmds (b/java-command
+ {:basis basis
+ :java-opts (:jvm-opts combined)
+ :main 'clojure.main
+ :main-args (:main-opts combined)})
+ {:keys [exit]} (b/process cmds)]
+ (when-not (zero? exit) (throw (ex-info "Task failed" {})))))
(defn eastwood "Run Eastwood." [opts]
- (-> opts (bb/run-task [:eastwood])))
+ (run-task [:eastwood])
+ opts)
(defn gen-doc-tests "Generate tests from doc code blocks." [opts]
- (-> opts (bb/run-task [:gen-doc-tests])))
+ (run-task [:gen-doc-tests])
+ opts)
(defn run-doc-tests
"Generate and run doc tests.
@@ -38,18 +55,29 @@
[:cljs] -- test against ClojureScript"
[{:keys [aliases] :as opts}]
(gen-doc-tests opts)
- (bb/run-tests (assoc opts :aliases
- (-> [:test-doc]
- (into aliases)
- (into (if (some #{:cljs} aliases)
- [:test-doc-cljs]
- [:test-doc-clj])))))
+ (run-task (-> [:test :runner :test-doc]
+ (into aliases)
+ (into (if (some #{:cljs} aliases)
+ [:test-doc-cljs]
+ [:test-doc-clj]))))
opts)
(defn test "Run basic tests." [opts]
- (-> opts
- (update :aliases (fnil conj []) :1.11)
- (bb/run-tests)))
+ (run-task [:test :runner :1.11])
+ opts)
+
+(defn- jar-opts [opts]
+ (let [version (if (:snapshot opts) snapshot version)]
+ (println "\nVersion:" version)
+ (assoc opts
+ :lib lib :version version
+ :jar-file (format "target/%s-%s.jar" lib version)
+ :scm {:tag (str "v" version)}
+ :basis (b/create-basis {})
+ :class-dir class-dir
+ :target "target"
+ :src-dirs ["src"]
+ :src-pom "template/pom.xml")))
(defn ci
"Run the CI pipeline of tests (and build the JAR).
@@ -57,25 +85,25 @@
Default Clojure version is 1.9.0 (:1.9) so :elide
tests for #409 on that version."
[opts]
- (-> opts
- (bb/clean)
- (assoc :lib lib :version (if (:snapshot opts) snapshot version))
- (as-> opts
- (reduce (fn [opts alias]
- (run-doc-tests (assoc opts :aliases [alias])))
- opts
- [:cljs :elide :1.10 :1.11 :master]))
- (eastwood)
- (as-> opts
- (reduce (fn [opts alias]
- (bb/run-tests (assoc opts :aliases [alias])))
- opts
- [:cljs :elide :1.10 :1.11 :master]))
- (bb/clean)
- (assoc :src-pom "template/pom.xml")
- (bb/jar)))
+ (let [aliases [:cljs :elide :1.10 :1.11 :master]
+ opts (jar-opts opts)]
+ (b/delete {:path "target"})
+ (doseq [alias aliases]
+ (run-doc-tests {:aliases [alias]}))
+ (eastwood opts)
+ (doseq [alias aliases]
+ (run-task [:test :runner alias]))
+ (b/delete {:path "target"})
+ (println "\nWriting pom.xml...")
+ (b/write-pom opts)
+ (println "\nCopying source...")
+ (b/copy-dir {:src-dirs ["src"] :target-dir class-dir})
+ (println "\nBuilding JAR...")
+ (b/jar opts))
+ opts)
(defn deploy "Deploy the JAR to Clojars." [opts]
- (-> opts
- (assoc :lib lib :version (if (:snapshot opts) snapshot version))
- (bb/deploy)))
+ (let [{:keys [jar-file] :as opts} (jar-opts opts)]
+ (dd/deploy {:installer :remote :artifact (b/resolve-path jar-file)
+ :pom-file (b/pom-path (select-keys opts [:lib :class-dir]))}))
+ opts)
diff --git a/debian/changelog b/debian/changelog
index 3e730a9..c5d096b 100644
--- a/debian/changelog
+++ b/debian/changelog
@@ -1,3 +1,10 @@
+honeysql-clojure (2.4.1026-1) UNRELEASED; urgency=low
+
+ * New upstream release.
+ * New upstream release.
+
+ -- Debian Janitor <janitor@jelmer.uk> Wed, 19 Apr 2023 19:49:42 -0000
+
honeysql-clojure (2.4.962+really2.3.911-1) unstable; urgency=medium
* Team upload.
diff --git a/deps.edn b/deps.edn
index 92c7a70..4c54435 100644
--- a/deps.edn
+++ b/deps.edn
@@ -3,8 +3,9 @@
:deps {org.clojure/clojure {:mvn/version "1.9.0"}}
:aliases
{;; for help: clojure -A:deps -T:build help/doc
- :build {:deps {io.github.seancorfield/build-clj
- {:git/tag "v0.8.0" :git/sha "9bd8b8a"}}
+ :build {:deps {io.github.clojure/tools.build
+ {:git/tag "v0.9.4" :git/sha "76b78fe"}
+ slipset/deps-deploy {:mvn/version "0.2.1"}}
:ns-default build}
;; versions to test against:
@@ -20,16 +21,18 @@
:test
{:extra-paths ["test"]
:extra-deps {io.github.cognitect-labs/test-runner
- {:git/tag "v0.5.0" :git/sha "48c3c67"}
+ {:git/tag "v0.5.1" :git/sha "dfb30dd"}
org.clojure/core.cache {:mvn/version "RELEASE"}}
:exec-fn cognitect.test-runner.api/test}
+ :runner
+ {:main-opts ["-m" "cognitect.test-runner"]}
;; various "runners" for tests/CI:
:cljs {:extra-deps {olical/cljs-test-runner {:mvn/version "3.8.0"}}
:main-opts ["-m" "cljs-test-runner.main"]}
:gen-doc-tests {:replace-paths ["build"]
- :extra-deps {babashka/fs {:mvn/version "0.1.2"}
+ :extra-deps {babashka/fs {:mvn/version "0.3.17"}
com.github.lread/test-doc-blocks {:mvn/version "1.0.166-alpha"}}
:main-opts ["-m" "honey.gen-doc-tests"]}
@@ -40,5 +43,5 @@
"-c" "{:warnings,{:single-segment-namespace,false}}"
"-d" "target/test-doc-blocks/test"]}
- :eastwood {:extra-deps {jonase/eastwood {:mvn/version "1.0.0"}}
+ :eastwood {:extra-deps {jonase/eastwood {:mvn/version "1.3.0"}}
:main-opts ["-m" "eastwood.lint" "{:source-paths,[\"src\"]}"]}}}
diff --git a/doc/clause-reference.md b/doc/clause-reference.md
index 4b0385a..49e5544 100644
--- a/doc/clause-reference.md
+++ b/doc/clause-reference.md
@@ -10,7 +10,8 @@ Clauses can be specified as keywords or symbols. Use
a space (e.g., `:left-join` is formatted as `LEFT JOIN`).
Except as noted, these clauses apply to all the SQL
-dialects that HoneySQL supports.
+dialects that HoneySQL supports. See also the section on
+[database-specific hints and tips](databases.md).
DDL clauses are listed first, followed by SQL clauses.
@@ -22,6 +23,11 @@ The examples herein assume:
where order-by over partition-by window]])
```
+Every DDL and SQL clause has a corresponding helper function
+in `honey.sql.helpers`. In general, `(helper :foo expr)` will
+produce `{:helper [:foo expr]}` (with a few exceptions -- see
+the docstring of the helper function for details).
+
# DDL Clauses
HoneySQL supports the following DDL clauses as a data DSL.
@@ -85,7 +91,8 @@ user=> (sql/format {:alter-table [:fruit
As can be seen above, `:add-column` and `:alter-column`
both accept a column description (as a sequence of simple
-expressions); `:drop-column` accepts a single column name,
+expressions); `:drop-column` accepts one or more column names
+optionally prefixed by `:if-exists`,
and `:rename-column` accepts a sequence with two column
names: the "from" and the "to" names.
@@ -132,10 +139,9 @@ user=> (sql/format {:alter-table :fruit :rename-table :vegetable})
## create-table, with-columns
-`:create-table` can accept a single table name or a pair
+`:create-table` can accept a single table name or a sequence
containing a table name and a flag indicating the creation
-should be conditional (`:if-not-exists` or the symbol `if-not-exists`,
-although any truthy value will work). `:create-table` should
+should be conditional (`:if-not-exists` or the symbol `if-not-exists`). `:create-table` should
be used with `:with-columns` to specify the actual columns
in the table:
@@ -148,17 +154,76 @@ user=> (sql/format {:create-table :fruit
["CREATE TABLE fruit (id INT NOT NULL, name VARCHAR(32) NOT NULL, cost FLOAT NULL)"]
```
+Any keywords (or symbols) preceding the table name will be
+turned into SQL keywords (this is true for all of the `create-*`
+DSL identifiers):
+
+```clojure
+user=> (sql/format {:create-table [:my :fancy :fruit :if-not-exists]
+ :with-columns
+ [[:id :int [:not nil]]
+ [:name [:varchar 32] [:not nil]]
+ [:cost :float :null]]})
+["CREATE MY FANCY TABLE IF NOT EXISTS fruit (id INT NOT NULL, name VARCHAR(32) NOT NULL, cost FLOAT NULL)"]
+```
+
+This lets you write SQL like `CREATE TEMP TABLE foo ...` etc.
+
The `:with-columns` clause is formatted as if `{:inline true}`
was specified so nothing is parameterized. In addition,
everything except the first element of a column description
will be uppercased (mostly to give the appearance of separating
-the column name from the SQL keywords).
+the column name from the SQL keywords) -- except for keywords
+that start with `'` which will be transcribed into the SQL exactly
+as-is, with no case or character conversion at all. This
+"escape hatch" is intended to allow for SQL dialects that are
+case sensitive and/or have other unusual syntax constraints.
Various function-like expressions can be specified, as shown
in the example above, that allow things like `CHECK` for a
constraint, `FOREIGN KEY` (with a column name), `REFERENCES`
(with a pair of column names). See [Column Descriptors in Special Syntax](special-syntax.md#column-descriptors) for more details.
+For example:
+
+```clojure
+user=> (-> {:create-table :foo
+ :with-columns
+ [[:a :int]
+ [:b :int]
+ [[:primary-key :a :b]]]}
+ (sql/format))
+["CREATE TABLE foo (a INT, b INT, PRIMARY KEY(a, b))"]
+```
+
+or:
+
+```clojure
+user=> (-> {:create-table [:bar]
+ :with-columns
+ [[:a :integer]
+ [:b :integer]
+ [[:constraint :foo_natural_key] :unique [:composite :a :b]]]}
+ (sql/format))
+["CREATE TABLE bar (a INTEGER, b INTEGER, CONSTRAINT foo_natural_key UNIQUE (a, b))"]
+```
+
+or a mix of column constraints and table constraints:
+
+```clojure
+user=> (-> '{create-table quux
+ with-columns
+ ((a integer (constraint a_pos) (check (> a 0)))
+ (b integer)
+ ((constraint a_bigger) (check (< b a))))}
+ (sql/format {:pretty true}))
+["
+CREATE TABLE quux
+(a INTEGER CONSTRAINT a_pos CHECK(a > 0), b INTEGER, CONSTRAINT a_bigger CHECK(b < a))
+"]
+```
+
+
## create-table-as
`:create-table-as` can accept a single table name or a sequence
@@ -211,10 +276,20 @@ WITH NO DATA
" "y"]
```
+As above, any keywords (or symbols) preceding the table name
+will be turned into SQL keywords (this is true for all of the
+`create-*` DSL identifiers) so you can write:
+
+```
+{:create-table-as [:temp :metro :if-not-exists [..]] ..}
+```
+
+to produce `CREATE TEMP TABLE IF NOT EXISTS metro ..`.
+
## create-extension
-`:create-extension` can accept a single extension name or a pair
-of the extension name, followed by
+`:create-extension` can accept a single extension name or a
+sequence of the extension name, followed by
a flag indicating the creation should be conditional
(`:if-not-exists` or the symbol `if-not-exists`).
See the [PostgreSQL](postgresql.md) section for examples.
@@ -290,9 +365,11 @@ order they would appear in a valid SQL statement).
## with, with-recursive
-These provide CTE support for SQL Server. The argument to
+These provide CTE support for several databases.
+In the most common form, the argument to
`:with` (or `:with-recursive`) is a sequences of pairs, each of
-a result set name (or description) and a basic SQL statement.
+a result set name (or description) and either of; a basic SQL
+statement, a string, a keyword or a symbol.
The result set can either be a SQL entity (a simple name)
or a pair of a SQL entity and a set of column names.
@@ -305,6 +382,34 @@ user=> (sql/format '{with ((stuff {select (:*) from (foo)}),
["WITH stuff AS (SELECT * FROM foo), nonsense AS (SELECT * FROM bar) SELECT foo.id, bar.name FROM stuff, nonsense WHERE status = ?" 0]
```
+When the expression is a basic SQL statement in any of the pairs,
+the resulting syntax of the pair is `WITH ident AS expr` as shown above.
+However, when the expression is a string, a keyword or a symbol, the resulting
+syntax of the pair is of the form `WITH expr AS ident` like this:
+
+```clojure
+user=> (sql/format '{with ((ts_upper_bound "2019-08-01 15:23:00"))
+ select :*
+ from (hits)
+ where (= EventDate ts_upper_bound)})
+["WITH ? AS ts_upper_bound SELECT * FROM hits WHERE EventDate = ts_upper_bound" "2019-08-01 15:23:00"]
+```
+
+The syntax only varies for each pair and so you can use both SQL statements
+and keywords/strings/symbols in the same `WITH` clause like this:
+
+```clojure
+user=> (sql/format '{with ((ts_upper_bound "2019-08-01 15:23:00")
+ (review :awesome)
+ (stuff {select (:*) from (songs)}))
+ select :*
+ from (hits, stuff)
+ where (and (= EventDate ts_upper_bound)
+ (= EventReview review))})
+["WITH ? AS ts_upper_bound, awesome AS review, stuff AS (SELECT * FROM songs) SELECT * FROM hits, stuff WHERE (EventDate = ts_upper_bound) AND (EventReview = review)"
+ "2019-08-01 15:23:00"]
+```
+
You can specify a list of columns for the CTE like this:
```clojure
@@ -340,6 +445,8 @@ user=> (sql/format '{union [{select (id,status) from (table-a)}
["SELECT id, status FROM table_a UNION SELECT id, event AS status, from, table_b"]
```
+> Note: different databases have different precedence rules for these set operations when used in combination -- you may need to use `:nest` to add `(` .. `)` in order to combine these operations in a single SQL statement, if the natural order produced by HoneySQL does not work "as expected" for your database.
+
## select, select-distinct, table
`:select` and `:select-distinct` expect a sequence of SQL entities (column names
@@ -383,7 +490,7 @@ user=> (sql/format {:select [[:* :except [:a :b] :replace [[[:inline 2] :c]]]] :
```
The `:table` clause is equivalent to `:select :* :from` and accepts just
-a simple table name -- `:create-table-as` above for an example.
+a simple table name -- see `:create-table-as` above for an example.
## select-distinct-on
@@ -439,7 +546,7 @@ user=> (sql/format '{select * bulk-collect-into [arrv 100] from mytable})
["SELECT * BULK COLLECT INTO arrv LIMIT ? FROM mytable" 100]
```
-## insert-into
+## insert-into, replace-into
There are three use cases with `:insert-into`.
@@ -457,6 +564,10 @@ or a table/column specifier and a SQL query.
For the first and second cases, you'll use the `:values` clause
to specify rows of values to insert.
+`:replace-into` is only supported by MySQL and SQLite but is
+part of HoneySQL's "core" dialect anyway. It produces a `REPLACE INTO`
+statement but otherwise has identical syntax to `:insert-into`.
+
```clojure
;; first case -- table specifier:
user=> (sql/format {:insert-into :transport
@@ -529,11 +640,14 @@ user=> (sql/format {:delete [:order :item]
## truncate
-`:truncate` accepts a simple SQL entity (table name):
+`:truncate` accepts a simple SQL entity (table name)
+or a table name followed by various options:
```clojure
user=> (sql/format '{truncate transport})
-["TRUNCATE transport"]
+["TRUNCATE TABLE transport"]
+user=> (sql/format '{truncate (transport restart identity)})
+["TRUNCATE TABLE transport RESTART IDENTITY"]
```
## columns
@@ -586,7 +700,7 @@ one or more SQL entities. Each entity can either be a
simple table name (keyword or symbol) or a pair of a
table name and an alias.
-`:using` is intended to be used as a simple join with a `:delete-from`
+`:using` is intended to be used as a simple join, for example with a `:delete-from`
clause (see [PostgreSQL DELETE statement](https://www.postgresql.org/docs/12/sql-delete.html)
for more detail).
@@ -664,6 +778,9 @@ user=> (sql/format {:select [:t.ref :pp.code]
["SELECT t.ref, pp.code FROM transaction AS t LEFT JOIN paypal_tx AS pp USING (id) WHERE ? = pp.status" "settled"]
```
+See also the [`:join` special syntax](https://cljdoc.org/d/com.github.seancorfield/honeysql/CURRENT/doc/getting-started/sql-special-syntax-#join)
+for nested `JOIN` expressions.
+
## cross-join
`:cross-join` accepts a single sequence argument that lists
@@ -912,6 +1029,7 @@ In the former case, all of the rows are augmented to have
either `NULL` or `DEFAULT` values for any missing keys (columns).
By default, `NULL` is used but you can specify a set of columns
to get `DEFAULT` values, via the `:values-default-columns` option.
+You can also be explicit and use `[:default]` as a value to generate `DEFAULT`.
In the latter case -- a sequence of sequences --
all of the rows are padded to the same length by adding `nil`
values if needed (since `:values` does not know how or if column
@@ -936,6 +1054,32 @@ user=> (sql/format '{insert-into table
> Note: the `:values-default-columns` option must match how the columns are specified, i.e., as symbols or keywords.
+For databases that allow it, you can insert an entire row of default values,
+if appropriate, using one of the following syntaxes:
+
+```clojure
+user=> (sql/format {:insert-into :table :values []})
+["INSERT INTO table VALUES ()"]
+user=> (sql/format {:insert-into :table :values :default})
+["INSERT INTO table DEFAULT VALUES"]
+```
+
+Some databases support the empty `VALUES` clause, some support `DEFAULT VALUES`, some support neither. Consult your database's documentation to see which approach to use.
+
+For databases that allow it, when specifying multiple rows you use `:default` in
+place of a row to insert default values for that row:
+
+```clojure
+user=> (sql/format {:insert-into :table
+ :values [{:a 1 :b 2 :c 3}
+ :default
+ {:a 4 :b 5 :c 6}]})
+["INSERT INTO table (a, b, c) VALUES (?, ?, ?), DEFAULT, (?, ?, ?)" 6 5 4]
+user=> (sql/format {:insert-into :table
+ :values [[1 2 3] :default [4 5 6]]})
+["INSERT INTO table VALUES (?, ?, ?), DEFAULT, (?, ?, ?)" 1 2 3 4 5 6]
+```
+
## on-conflict, on-constraint, do-nothing, do-update-set
These are grouped together because they are handled
@@ -989,6 +1133,12 @@ user=> (sql/format {:insert-into :companies
:do-update-set {:fields [:name]
:where [:<> :name nil]}})
["INSERT INTO companies (name) VALUES (?) ON CONFLICT (name) DO UPDATE SET name = EXCLUDED.name WHERE name IS NOT NULL" "Microsoft"]
+user=> (sql/format {:insert-into :companies
+ :values [{:name "Microsoft"}]
+ :on-conflict :name
+ :do-update-set {:fields {:name [:+ :table.name 1]}
+ :where [:<> :name nil]}})
+["INSERT INTO companies (name) VALUES (?) ON CONFLICT (name) DO UPDATE SET name = table.name + ? WHERE name IS NOT NULL" "Microsoft" 1]
user=> (sql/format {:insert-into :companies
:values [{:name "Microsoft"}]
:on-conflict {:on-constraint :name-idx}
diff --git a/doc/cljdoc.edn b/doc/cljdoc.edn
index 2866ea6..03594f5 100644
--- a/doc/cljdoc.edn
+++ b/doc/cljdoc.edn
@@ -6,7 +6,8 @@
["SQL Clause Reference" {:file "doc/clause-reference.md"}]
["SQL Operator Reference" {:file "doc/operator-reference.md"}]
["SQL 'Special Syntax'" {:file "doc/special-syntax.md"}]
- ["PostgreSQL Support" {:file "doc/postgresql.md"}]]
+ ["PostgreSQL Support" {:file "doc/postgresql.md"}]
+ ["Other Databases" {:file "doc/databases.md"}]]
["All the Options" {:file "doc/options.md"}]
["Extending HoneySQL" {:file "doc/extending-honeysql.md"}]
["Differences from 1.x" {:file "doc/differences-from-1-x.md"}]]}
diff --git a/doc/databases.md b/doc/databases.md
new file mode 100644
index 0000000..01c2b3c
--- /dev/null
+++ b/doc/databases.md
@@ -0,0 +1,83 @@
+# Other Databases
+
+There is a dedicated section for [PostgreSQL Support](postgres.md).
+This section provides hints and tips for generating SQL for other
+databases.
+
+As a reminder, HoneySQL supports the following dialects out of the box:
+* `:ansi` -- which is the default and provides broad support for PostgreSQL as well
+* `:mysql` -- which includes MariaDB and Percona
+* `:oracle`
+* `:sqlserver` -- Microsoft SQL Server
+
+For the most part, these dialects only change the "stropping" --
+how SQL entities are quoted in the generated SQL -- but dialects
+can change clause order and/or add dialect-specific clauses.
+
+This section is a work-in-progress and more hints and tips will be
+added over time for more databases.
+
+## Precedence
+
+The biggest difference between database dialects tends to be
+precedence. MySQL actually has different precedence in the `SET`
+clause but several databases disagree on the precedence of actual
+"set" operations: `UNION`, `EXCEPT`, `INTERSECT`, etc.
+
+HoneySQL tries to be fairly neutral in this area and follows ANSI SQL
+precedence. This means that some databases may have problems with
+complex SQL operations that combine multiple clauses with contentious
+precedence. In general, you can solve this using the `:nest`
+pseudo-clause in the DSL:
+
+<!-- :test-doc-blocks/skip -->
+```clojure
+{:nest DSL}
+;; will produce DSL wrapped in ( .. )
+```
+
+This should allow you to cater to various databases' precedence
+peculiarities.
+
+## BigQuery (Google)
+
+Function names can be case-sensitive: you can use the "as-is" notation
+for SQL entities to avoid conversion to upper-case: `[:'domain :ref]`
+produces `domain(ref)` rather than `DOMAIN(ref)`.
+
+## ClickHouse
+
+This is another case-sensitive database than requires the "as-is"
+notation described for **BigQuery** above.
+
+`WITH expr AS ident` is supported as a core part of the DSL,
+as of 2.4.962.
+
+## MySQL
+
+When you select the `:mysql` dialect, the precedence of `:set` is
+changed. All the other databases get this correct.
+
+`REPLACE INTO`, while specific to MySQL and SQLite, is supported as
+a core part of the DSL, as `:replace-into`, as of 2.4.969.
+
+## SQLite
+
+Precedence of "set" operations: SQLite differs from other databases
+in handling compound SQL operations that use multiple `UNION`,
+`EXCEPT`, `INTERSECT` clauses. Use `:nest` to disambiguate your
+intentions.
+See issue [#462](https://github.com/seancorfield/honeysql/issues/462)
+for some background on this.
+
+`INSERT OR IGNORE INTO`: this syntax is specific to SQLite for
+performing upserts. However, SQLite supports the PostgreSQL-style
+upsert with `ON CONFLICT` so you can use that syntax instead, for
+`DO NOTHING` and `DO UPDATE SET`. In addition,
+`INSERT OR REPLACE INTO` can be written using just `REPLACE INTO`
+(see below).
+Issue [#448](https://github.com/seancorfield/honeysql/issues/448)
+has more background on this.
+
+`REPLACE INTO`, while specific to MySQL and SQLite, is supported as
+a core part of the DSL, as `:replace-into`, as of 2.4.969.
diff --git a/doc/differences-from-1-x.md b/doc/differences-from-1-x.md
index 936af28..e4124ab 100644
--- a/doc/differences-from-1-x.md
+++ b/doc/differences-from-1-x.md
@@ -63,7 +63,7 @@ Supported Clojure versions: 1.7 and later.
In `deps.edn`:
<!-- :test-doc-blocks/skip -->
```clojure
-com.github.seancorfield/honeysql {:mvn/version "2.3.911"}
+com.github.seancorfield/honeysql {:mvn/version "2.4.1026"}
```
Required as:
@@ -98,21 +98,23 @@ The primary API is just `honey.sql/format`. The `array`, `call`, `inline`, `para
Other `honeysql.core` functions that no longer exist include: `build`, `qualify`, and `quote-identifier`. Many other public functions were essentially undocumented (neither mentioned in the README nor in the tests) and also no longer exist.
+> As of 2.4.1002, the functionality of `qualify` can be achieved through the `:.` dot-selection special syntax.
+
You can now select a non-ANSI dialect of SQL using the new `honey.sql/set-dialect!` function (which sets a default dialect for all `format` operations) or by passing the new `:dialect` option to the `format` function. `:ansi` is the default dialect (which will mostly incorporate PostgreSQL usage over time). Other dialects supported are `:mysql` (which has a different quoting strategy and uses a different ranking for the `:set` clause), `:oracle` (which is essentially the `:ansi` dialect but will control other things over time), and `:sqlserver` (which is essentially the `:ansi` dialect but with a different quoting strategy). Other dialects and changes may be added over time.
> Note: in general, all clauses are available in all dialects in HoneySQL unless the syntax of the clauses conflict between dialects (currently, no such clauses exist). The `:mysql` dialect is the only one so far that changes the priority ordering of a few clauses.
## Option Changes
-The `:quoting <dialect>` option has superseded by the new dialect machinery and a new `:quoted` option that turns quoting on or off. You either use `:dialect <dialect>` instead or set a default dialect (via `set-dialect!`) and then use `:quoted true` in `format` calls where you want quoting.
+The `:quoting <dialect>` option has been superseded by the new dialect machinery and a new `:quoted` option that turns quoting on or off. You either use `:dialect <dialect>` instead (which turns on quoting by default) or set a default dialect (via `set-dialect!`) and then use `:quoted true` in `format` calls where you want quoting.
-Identifiers are automatically quoted if you specify a `:dialect` option to `format`, unless you also specify `:quoted false`.
+SQL entity names are automatically quoted if you specify a `:dialect` option to `format`, unless you also specify `:quoted false`.
The following options are no longer supported:
* `:allow-dashed-names?` -- if you provide dashed-names in 2.x, they will be left as-is if quoting is enabled, else they will be converted to snake_case (so you will either get `"dashed-names"` with quoting or `dashed_names` without). If you want dashed-names to be converted to snake_case when `:quoted true`, you also need to specify `:quoted-snake true`.
* `:allow-namespaced-names?` -- this supported `foo/bar` column names in SQL which I'd like to discourage.
* `:namespace-as-table?` -- this is the default in 2.x: `:foo/bar` will be treated as `foo.bar` which is more in keeping with `next.jdbc`.
-* `:parameterizer` -- this would add a lot of complexity to the formatting engine and I do not know how widely it was used (especially in its arbitrarily extensible form).
+* `:parameterizer` -- this would add a lot of complexity to the formatting engine and I do not know how widely it was used (especially in its arbitrarily extensible form). _[As of 2.4.962, the ability to generated SQL with numbered parameters, i.e., `$1` instead of positional parameters, `?`, has been added via the `:numbered true` option]_
* `:return-param-names` -- this was added to 1.x back in 2013 without an associated issue or PR so I've no idea what use case this was intended to support.
> Note: I expect some push back on those first three options and the associated behavior changes.
@@ -131,7 +133,7 @@ The following new syntax has been added:
* `:default` -- for `DEFAULT` values (in inserts) and for declaring column defaults in table definitions,
* `:escape` -- used to wrap a regular expression so that non-standard escape characters can be provided,
* `:inline` -- used as a function to replace the `sql/inline` / `#sql/inline` machinery,
-* `:interval` -- used as a function to support `INTERVAL <n> <units>`, e.g., `[:interval 30 :days]`,
+* `:interval` -- used as a function to support `INTERVAL <n> <units>`, e.g., `[:interval 30 :days]` for databases that support it (e.g., MySQL) and, as of 2.4.1026, for `INTERVAL 'n units'`, e.g., `[:interval "24 hours"]` for ANSI/PostgreSQL.
* `:lateral` -- used to wrap a statement or expression, to provide a `LATERAL` join,
* `:lift` -- used as a function to prevent interpretation of a Clojure data structure as DSL syntax (e.g., when passing a vector or hash map as a parameter value) -- this should mostly be a replacement for `honeysql.format/value`,
* `:nest` -- used as a function to add an extra level of nesting (parentheses) around an expression,
@@ -189,13 +191,33 @@ user=> (sql/format {:select [[[:exists {:select [:a] :from [:foo]}] :x]]})
["SELECT EXISTS (SELECT a FROM foo) AS x"]
```
+### `ORDER BY` with `NULLS FIRST` or `NULLS LAST`
+
+In HoneySQL 1.x, if you wanted to generate SQL like
+
+```sql
+ORDER BY ... DESC NULLS LAST
+```
+
+you needed to pass `:nulls-last` as a separate keyword, after `:asc` or `:desc`:
+
+```clj
+{:order-by [[:my-column :desc :nulls-last]]}
+```
+
+In HoneySQL 2.x, the direction and the null ordering rule are now combined into a single keyword:
+
+```clj
+{:order-by [[:my-column :desc-nulls-last]]}
+```
+
## Extensibility
The protocols and multimethods in 1.x have all gone away. The primary extension point is `honey.sql/register-clause!` which lets you specify the new clause (keyword), the formatter function for it, and the existing clause that it should be ranked before (`format` processes the DSL in clause order).
You can also register new "functions" that can implement special syntax (such as `:array`, `:inline`, `:raw` etc above) via `honey.sql/register-fn!`. This accepts a "function" name as a keyword and a formatter which will generally be a function of two arguments: the function name (so formatters can be reused across different names) and a vector of the arguments the function should accept.
-And, finally, you can register new operators that will be recognized in expressions via `honey.sql/register-op!`. This accepts an operator name as a keyword and optional named parameters to indicate whether the operator is `:variadic` (the default is strictly binary) and whether it should ignore operands that evaluate to `nil` (via `:ignore-nil`). The latter can make it easier to construct complex expressions programmatically without having to worry about conditionally removing "optional" (`nil`) values.
+And, finally, you can register new operators that will be recognized in expressions via `honey.sql/register-op!`. This accepts an operator name as a keyword and an optional named parameter to indicate whether it should ignore operands that evaluate to `nil` (via `:ignore-nil`). That can make it easier to construct complex expressions programmatically without having to worry about conditionally removing "optional" (`nil`) values.
> Note: because of the changes in the extension machinery between 1.x and 2.x, it is not possible to use the [nilenso/honeysql-postgress](https://github.com/nilenso/honeysql-postgres) library with HoneySQL 2.x but the goal is to incorporate all of the syntax from that library into the core of HoneySQL.
diff --git a/doc/extending-honeysql.md b/doc/extending-honeysql.md
index ea4b904..5c8fe0b 100644
--- a/doc/extending-honeysql.md
+++ b/doc/extending-honeysql.md
@@ -14,6 +14,27 @@ many more. Built in operators include: `:=`, `:+`, `:mod`.
Built in functions (special syntax) include: `:array`, `:case`,
`:cast`, `:inline`, `:raw` and many more.
+See also the section on
+[database-specific hints and tips](databases.md), which may
+let you avoid extending HoneySQL.
+
+## Extending what `:inline` can do
+
+By default, the `:inline` option can convert a fairly
+basic set of values/types to SQL strings:
+* `nil`
+* strings
+* keywords and symbols
+* vectors
+* UUIDs (Clojure only)
+
+Everything is naively converted by calling `str`.
+
+You can extend `honey.sql.protocols/InlineValue` to
+other types and defining how the `sqlize` function
+should behave. It takes a single argument, the value
+to be inlined (converted to a SQL string).
+
## Registering a New Clause Formatter
`honey.sql/register-clause!` accepts a keyword (or a symbol)
@@ -65,16 +86,13 @@ You might have:
`honey.sql/register-op!` accepts a keyword (or a symbol) that
should be treated as a new infix operator.
-By default, operators are treated as strictly binary --
-accepting just two arguments -- and an exception will be
-thrown if they are provided less than two or more than
-two arguments. You can optionally specify that an operator
-can take any number of arguments with `:variadic true`:
+All operators are treated as variadic and an exception will be
+thrown if they are provided no arguments:
```clojure
(require '[honey.sql :as sql])
-(sql/register-op! :<=> :variadic true)
+(sql/register-op! :<=>)
;; and then use the new operator:
(sql/format {:select [:*], :from [:table], :where [:<=> 13 :x 42]})
;; will produce:
@@ -89,7 +107,7 @@ such `nil` expressions. You can specify `:ignore-nil true`
to achieve that:
```clojure
-(sql/register-op! :<=> :variadic true :ignore-nil true)
+(sql/register-op! :<=> :ignore-nil true)
;; and then use the new operator:
(sql/format {:select [:*], :from [:table], :where [:<=> nil :x 42]})
;; will produce:
@@ -162,10 +180,10 @@ of it and would call `sql/format-expr` on each argument:
_New in HoneySQL 2.3.x_
The built-in dialects that HoneySQL supports are:
-* `:ansi` -- the default, that quotes identifiers with double-quotes, like `"this"`
-* `:mysql` -- quotes identifiers with backticks, and changes the precedence of `SET` in `UPDATE`
-* `:oracle` -- quotes identifiers like `:ansi`, and does not use `AS` in aliases
-* `:sqlserver` -- quotes identifiers with brackets, like `[this]`
+* `:ansi` -- the default, that quotes SQL entity names with double-quotes, like `"this"`
+* `:mysql` -- quotes SQL entity names with backticks, and changes the precedence of `SET` in `UPDATE`
+* `:oracle` -- quotes SQL entity names like `:ansi`, and does not use `AS` in aliases
+* `:sqlserver` -- quotes SQL entity names with brackets, like `[this]`
A dialect spec is a hash map containing at least `:quote` but also optionally `:clause-order-fn` and/or `:as`:
* `:quote` -- a unary function that takes a string and returns the quoted version of it
diff --git a/doc/getting-started.md b/doc/getting-started.md
index 09ccb72..fd0603e 100644
--- a/doc/getting-started.md
+++ b/doc/getting-started.md
@@ -10,14 +10,14 @@ For the Clojure CLI, add the following dependency to your `deps.edn` file:
<!-- :test-doc-blocks/skip -->
```clojure
- com.github.seancorfield/honeysql {:mvn/version "2.3.911"}
+ com.github.seancorfield/honeysql {:mvn/version "2.4.1026"}
```
For Leiningen, add the following dependency to your `project.clj` file:
<!-- :test-doc-blocks/skip -->
```clojure
- [com.github.seancorfield/honeysql "2.3.911"]
+ [com.github.seancorfield/honeysql "2.4.1026"]
```
HoneySQL produces SQL statements but does not execute them.
@@ -110,7 +110,34 @@ Some "functions" are considered to be operators. In general,
`42` and `"c"` lifted out into the overall vector result
(with a SQL string followed by all its parameters).
-Operators can be strictly binary or variadic (most are strictly binary).
+> Note: you can use the `:numbered true` option to `format` to produce SQL containing numbered placeholders, like `FOO(a, $1, $2)`, instead of positional placeholders (`?`).
+
+As of 2.4.1002, function calls with "named" arguments are supported
+which some databases support, e.g., MySQL and PostgreSQL both have
+`SUBSTRING()`:
+
+<!-- :test-doc-blocks/skip -->
+```clojure
+[:substring :col 3 4] ;=> SUBSTRING(col, 3, 4)
+;; can also be written:
+[:substring :col :!from 3 :!for 4] ;=> SUBSTRING(col FROM 3 FOR 4)
+```
+
+In a function call, any keywords (or symbols) that begin with `!` followed
+by a letter are treated as inline SQL keywords to be used instead of `,`
+between arguments -- or in front of arguments, such as for `TRIM()`:
+
+<!-- :test-doc-blocks/skip -->
+```clojure
+[:trim :!leading "x" :!from :col] ;=> TRIM(LEADING ? FROM col), with "x" parameter
+[:trim :!both :!from :col] ;=> TRIM(BOTH FROM col), trims spaces
+;; adjacent inline SQL keywords can be combined with a hyphen:
+[:trim :!both-from :col] ;=> TRIM(BOTH FROM col)
+;; (because - in a SQL keyword is replaced by a space)
+```
+
+Operators are all treated as variadic (except for `:=` and
+`:<>` / `:!=` / `:not=` which are binary and require exactly two operands).
Special syntax can have zero or more arguments and each form is
described in the [Special Syntax](special-syntax.md) section.
@@ -179,7 +206,7 @@ expression requires an extra level of nesting:
As indicated in the preceding sections, values found in the DSL data structure
that are not keywords or symbols are lifted out as positional parameters.
-They are replaced by `?` in the generated SQL string and added to the
+By default, they are replaced by `?` in the generated SQL string and added to the
parameter list in order:
<!-- :test-doc-blocks/skip -->
@@ -187,6 +214,14 @@ parameter list in order:
[:between :size 10 20] ;=> "size BETWEEN ? AND ?" with parameters 10 and 20
```
+If you specify the `:numbered true` option to `format`, numbered placeholders (`$1`, `$2`, etc) will be used instead of positional placeholders (`?`).
+
+<!-- :test-doc-blocks/skip -->
+```clojure
+;; with :numbered true option:
+[:between :size 10 20] ;=> "size BETWEEN $1 AND $2" with parameters 10 and 20
+```
+
HoneySQL also supports named parameters. There are two ways
of identifying a named parameter:
* a keyword or symbol that begins with `?`
@@ -206,6 +241,18 @@ call as the `:params` key of the options hash map.
;;=> ["SELECT * FROM table WHERE a = ?" 42]
```
+Or with `:numbered true`:
+```clojure
+(sql/format {:select [:*] :from [:table]
+ :where [:= :a :?x]}
+ {:params {:x 42} :numbered true})
+;;=> ["SELECT * FROM table WHERE a = $1" 42]
+(sql/format {:select [:*] :from [:table]
+ :where [:= :a [:param :x]]}
+ {:params {:x 42} :numbered true})
+;;=> ["SELECT * FROM table WHERE a = $1" 42]
+```
+
## Functional Helpers
In addition to the hash map (and sequences) approach of building
@@ -230,6 +277,10 @@ supports out of the box. In addition, there are helpers for
parts of the SQL DSL (examples of `composite` appear in the [README](/README.md),
examples of `over` appear in the [Clause Reference](clause-reference.md))
+In general, `(helper :foo expr)` will produce `{:helper [:foo expr]}`
+(with a few exceptions -- see the docstring of the helper function
+for details).
+
In addition to being variadic -- which often lets you omit one
level of `[`..`]` -- the helper functions merge clauses, which
can make it easier to build queries programmatically:
@@ -298,6 +349,8 @@ Most databases use `"` for quoting (the `:ansi` and `:oracle` dialects).
The `:sqlserver` dialect uses `[`..`]` and the `:mysql` dialect uses
```..```. In addition, the `:oracle` dialect disables `AS` in aliases.
+> Note: by default, quoting is **off** which produces cleaner-looking SQL and assumes you control all the symbols/keywords used as table, column, and function names -- the "SQL entities". If you are building any SQL or DDL where the table, column, or function names could be provided by an external source, **you should specify `:quoted true` to ensure all SQL entities are safely quoted**. As of 2.3.928, if you do _not_ specify `:quoted` as an option, HoneySQL will automatically quote any SQL entities that seem unusual, i.e., that contain any characters that are not alphanumeric or underscore. Purely alphanumeric entities will not be quoted (no entities were quoted by default prior to 2.3.928). You can prevent that auto-quoting by explicitly passing `:quoted false` into the `format` call but, from a security point of view, you should think very carefully before you do that: quoting entity names helps protect you from injection attacks! As of 2.4.947, you can change the default setting of `:quoted` from `nil` to `true` (or `false`) via the `set-options!` function.
+
Currently, the only dialect that has substantive differences from
the others is `:mysql` for which the `:set` clause
has a different precedence than ANSI SQL.
@@ -309,10 +362,13 @@ before you call `format` for the first time.
You can change the dialect for a single `format` call by
specifying the `:dialect` option in that call.
-SQL entities are not quoted by default but if you specify the
+Alphanumeric SQL entities are not quoted by default but if you specify the
dialect in a `format` call, they will be quoted. If you don't
specify a dialect in the `format` call, you can specify
-`:quoted true` to have SQL entities quoted.
+`:quoted true` to have SQL entities quoted. You can also enable quoting
+globally via the `set-dialect!` function.
+
+If you want to use a dialect _and_ use the default quoting strategy (automatically quote any SQL entities that seem unusual), specify a `:dialect` option and set `:quoted nil`:
<!-- Reminder to doc author:
Reset dialect to default so other blocks are not affected for test-doc-blocks -->
@@ -336,8 +392,13 @@ specify a dialect in the `format` call, you can specify
;; and reset back to the default of :ansi
(sql/set-dialect! :ansi)
;;=> nil
+;; which also resets the quoting default (back to nil)
+;; so only unusual entity names get quoted:
(sql/format '{select (id) from (table)} {:quoted true})
;;=> ["SELECT \"id\" FROM \"table\""]
+;; use default quoting strategy with dialect specific quotes, only unusual entities quoted
+(sql/format '{select (id, iffy##field ) from (table)} {:dialect :sqlserver :quoted nil})
+;; => ["SELECT id, [iffy##field] FROM table"]
```
Out of the box, as part of the extended ANSI SQL support,
@@ -345,6 +406,11 @@ HoneySQL supports quite a few [PostgreSQL extensions](postgresql.md).
> Note: the [nilenso/honeysql-postgres](https://github.com/nilenso/honeysql-postgres) library which provided PostgreSQL support for HoneySQL 1.x does not work with HoneySQL 2.x. However, HoneySQL 2.x includes all of the functionality from that library (up to 0.4.112) out of the box!
+See also the section on
+[database-specific hints and tips](databases.md) which may
+provide ways to satisfy your database's needs without changing
+the dialect or extending HoneySQL.
+
## Reference Documentation
The full list of supported SQL clauses is documented in the
diff --git a/doc/operator-reference.md b/doc/operator-reference.md
index 383e996..b7aef9b 100644
--- a/doc/operator-reference.md
+++ b/doc/operator-reference.md
@@ -34,7 +34,7 @@ can simply evaluate to `nil` instead).
## in
-Binary predicate for checking an expression is
+Predicate for checking an expression is
is a member of a specified set of values.
The two most common forms are:
@@ -73,16 +73,21 @@ This produces `(col1, col2) IN ...`
> Note: This is a change from HoneySQL 1.x which accepted a sequence of column names but required more work for arbitrary expressions.
-## = <> < > <= >=
+## = <>
Binary comparison operators. These expect exactly
two arguments.
`not=` and `!=` are accepted as aliases for `<>`.
+## < > <= >=
+
+Comparison operators. These expect exactly
+two arguments.
+
## is, is-not
-Binary predicates for `NULL` and Boolean values:
+Predicates for `NULL` and Boolean values:
```clojure
{...
@@ -106,16 +111,26 @@ Binary predicates for `NULL` and Boolean values:
## mod, xor, + - * / % | & ^
-Mathematical and bitwise operators. `+` and `*` are
-variadic; the rest are strictly binary operators.
+Mathematical and bitwise operators.
## like, not like, ilike, not ilike, regexp
-Pattern matching binary operators. `regex` is accepted
+Pattern matching operators. `regex` is accepted
as an alias for `regexp`.
`similar-to` and `not-similar-to` are also supported.
+## with ordinality
+
+The ANSI SQL `WITH ORDINALITY` expression is supported as an infix operator:
+
+```clojure
+{...
+ [:with-ordinality [:jsonb_array_elements :j] [:arr :item :index]]
+ ...}
+;;=> ["...JSONB_ARRAY_ELEMENTS(j) WITH ORDINALITY ARR(item, index)..."]
+```
+
## ||
-Variadic string concatenation operator.
+String concatenation operator.
diff --git a/doc/options.md b/doc/options.md
index ff2a80d..c48b2fb 100644
--- a/doc/options.md
+++ b/doc/options.md
@@ -19,11 +19,24 @@ All options may be omitted. The default behavior of each option is described in
* `:checking` -- `:none` (default), `:basic`, or `:strict` to control the amount of lint-like checking that HoneySQL performs,
* `:dialect` -- a keyword that identifies a dialect to be used for this specific call to `format`; the default is to use what was specified in `set-dialect!` or `:ansi` if no other dialect has been set,
* `:inline` -- a Boolean indicating whether or not to inline parameter values, rather than use `?` placeholders and a sequence of parameter values; the default is `false` -- values are not inlined,
+* `:numbered` -- a Boolean indicating whether to generate numbered placeholders in the generated SQL (`$1`, `$2`, etc) or positional placeholders (`?`); the default is `false` (positional placeholders); this option was added in 2.4.962,
* `:params` -- a hash map providing values for named parameters, identified by names (keywords or symbols) that start with `?` in the DSL; the default is that any such named parameters will have `nil` values,
-* `:quoted` -- a Boolean indicating whether or not to quote (strop) identifiers (table and column names); the default is `false` -- identifiers are not quoted,
-* `:quoted-snake` -- a Boolean indicating whether or not quoted and string identifiers should have `-` replaced by `_`; the default is `false` -- quoted and string identifiers are left exactly as-is,
+* `:quoted` -- a Boolean indicating whether or not to quote (strop) SQL entity names (table and column names); the default is `nil` -- alphanumeric SQL entity names are not quoted but (as of 2.3.928) "unusual" SQL entity names are quoted; a `false` value turns off all quoting,
+* `:quoted-snake` -- a Boolean indicating whether or not quoted and string SQL entity names should have `-` replaced by `_`; the default is `false` -- quoted and string SQL entity names are left exactly as-is,
* `:values-default-columns` -- a sequence of column names that should have `DEFAULT` values instead of `NULL` values if used in a `VALUES` clause with no associated matching value in the hash maps passed in; the default behavior is for such missing columns to be given `NULL` values.
+As of 2.4.947, you can call `set-options!` with an options hash map to change the
+global defaults of certain options:
+
+* `:checking` -- can be `:basic` or `:strict`; specify `:none` to reset to the default,
+* `:inline` -- can be `true` but consider the security issues this causes by not using parameterized SQL statements; specify `false` (or `nil`) to reset to the default,
+* `:numbered` -- can be `true` or `false`; specify `false` to reset to the default,
+* `:quoted` -- can be `true` or `false`; specify `nil` to reset to the default; calling `set-dialect!` or providing a `:dialect` option to `format` will override the global default,
+* `:quoted-snake` -- can be `true`; specify `false` (or `nil`) to reset to the default.
+
+Other options may only be specified directly in calls to `format` as they are considered
+per-statement, rather than global.
+
See below for the interaction between `:dialect` and `:quoted`.
## `:cache`
@@ -42,15 +55,18 @@ Added in 2.2.858.
## `:checking`
-The `:checking` option defaults to `:none`. If `:checking :basic` is
-specified, certain obvious errors -- such as `IN` with an empty collection
-or `SELECT` with an empty list of columns --
-are treated as an error and an exception is thrown. If `:checking :strict`
-is specified, certain dubious constructs -- such as `IN` with a collection
-containing `NULL` values -- are also treated as an error and an exception is
-thrown. It is expected that this feature will be expanded over time
+The `:checking` option defaults to `:none`.
+If `:checking :basic` is specified, certain obvious errors
+are treated as an error and an exception is thrown.
+If `:checking :strict` is specified, certain dubious constructs are also treated as an error and an exception is
+thrown.
+It is expected that this feature will be expanded over time
to help avoid generating illegal SQL.
+Here are the checks for each level:
+* `:basic` -- `DELETE` and `DELETE FROM` without a `WHERE` clause; `IN` with an empty collection; `SELECT` with an empty list of columns; `UPDATE` without a `WHERE` clause.
+* `:strict` -- (all the `:basic` checks plus) `IN` with a collection containing `NULL` values (since this will not match rows).
+
## `:dialect`
If `:dialect` is provided, `:quoted` will default to `true` for this call. You can still specify `:quoted false` to turn that back off.
@@ -80,6 +96,15 @@ was wrapped in `[:inline `..`]`:
* keywords and symbols become SQL keywords (uppercase, with `-` replaced by a space),
* everything else is just turned into a string (by calling `str`) and added to the SQL string.
+> Note: you can provide additional inline formatting by extending the `InlineValue` protocol from `honey.sql.protocols` to new types.
+
+## `:numbered`
+
+By default, HoneySQL generates SQL using positional placeholders (`?`).
+Specifying `:numbered true` tells HoneySQL to generate SQL using
+numbered placeholders instead (`$1`, `$2`, etc). This can be set
+globally using `set-options!`.
+
## `:params`
The `:params` option provides a mapping from named parameters
@@ -99,12 +124,12 @@ to values for this call to `format`. For example:
## `:quoted`
If `:quoted true`, or `:dialect` is provided (and `:quoted` is not
-specified as `false`), identifiers that represent
+specified as `false`), SQL entity names that represent
tables and columns will be quoted (stropped) according to the
selected dialect.
-If `:quoted false`, identifiers that represent tables and columns
-will not be quoted. If those identifiers are reserved words in
+If `:quoted false`, SQL entity names that represent tables and columns
+will not be quoted. If those SQL entity names are reserved words in
SQL, the generated SQL will be invalid.
The quoting (stropping) is dialect-dependent:
@@ -113,19 +138,23 @@ The quoting (stropping) is dialect-dependent:
* `:oracle` -- uses double quotes
* `:sqlserver` -- user square brackets
+As of 2.3.928, if `:quoted` and `:dialect` are not provided, and no
+default quoting strategy has been specified (via `set-dialect!`) then
+alphanumeric SQL entity names will not be quoted but "unusual" SQL entity names will
+
## `:quoted-snake`
Where strings are used to identify table or column names, they are
treated as-is. If `:quoted true` (or a `:dialect` is specified),
-those identifiers are quoted as-is.
+those SQL entity names are quoted as-is.
Where keywords or symbols are used to identify table or column
-names, and `:quoted true` is provided, those identifiers are
+names, and `:quoted true` is provided, those SQL entity names are
quoted as-is.
-If `:quoted-snake true` is provided, those identifiers are quoted
+If `:quoted-snake true` is provided, those SQL entity names are quoted
but any `-` in them are replaced by `_` -- that replacement is the
-default in unquoted identifiers.
+default in unquoted SQL entity names.
This allows quoting to be used but still maintain the Clojure
(kebab case) to SQL (snake case) mappings.
diff --git a/doc/postgresql.md b/doc/postgresql.md
index d1dbd39..dfcca9b 100644
--- a/doc/postgresql.md
+++ b/doc/postgresql.md
@@ -10,6 +10,12 @@ Everything that the nilenso library provided (in 0.4.112) is implemented
directly in HoneySQL 2.x although a few things have a
slightly different syntax.
+If you are using HoneySQL with the Node.js PostgreSQL driver, it
+only accepts numbered placeholders, not positional placeholders,
+so you will need to specify the `:numbered true` option that was
+added in 2.4.962. You may find it convenient to set this option
+globally, via `set-options!`.
+
## Code Examples
The code examples herein assume:
@@ -249,6 +255,71 @@ By comparison, this is the DSL structure that nilenso would have required:
:where [:= :user.active false]}}}
```
+All of the examples for `:do-update-set` so far provide one or
+more columns and generated `SET` clauses using `EXCLUDED` columns.
+You can also perform regular `SET` operations, where the right-hand
+side is a full SQL expression by specifying a hash map of column /
+expression pairs, like you would for a regular `:set` clause:
+
+```clojure
+user=> (-> (insert-into :table)
+ (values [{:id "id" :counter 1}])
+ (on-conflict :id)
+ (do-update-set {:counter [:+ :table.counter 1]})
+ (sql/format {:pretty true}))
+["
+INSERT INTO table
+(id, counter) VALUES (?, ?)
+ON CONFLICT (id)
+DO UPDATE SET counter = table.counter + ?
+" "id" 1 1]
+;; using the DSL directly:
+user=> (-> {:insert-into :table
+ :values [{:id "id" :counter 1}]
+ :on-conflict :id
+ :do-update-set {:counter [:+ :table.counter 1]}}
+ (sql/format {:pretty true}))
+["
+INSERT INTO table
+(id, counter) VALUES (?, ?)
+ON CONFLICT (id)
+DO UPDATE SET counter = table.counter + ?
+" "id" 1 1]
+```
+
+If you need to combine a `DO UPDATE SET` hash map expression
+with a `WHERE` clause, you need to explicitly use the `:fields` /
+`:where` format explained above. Here's how those two examples
+look with a `WHERE` clause added:
+
+```clojure
+user=> (-> (insert-into :table)
+ (values [{:id "id" :counter 1}])
+ (on-conflict :id)
+ (do-update-set {:fields {:counter [:+ :table.counter 1]}
+ :where [:> :table.counter 1]})
+ (sql/format {:pretty true}))
+["
+INSERT INTO table
+(id, counter) VALUES (?, ?)
+ON CONFLICT (id)
+DO UPDATE SET counter = table.counter + ? WHERE table.counter > ?
+" "id" 1 1 1]
+;; using the DSL directly:
+user=> (-> {:insert-into :table
+ :values [{:id "id" :counter 1}]
+ :on-conflict :id
+ :do-update-set {:fields {:counter [:+ :table.counter 1]}
+ :where [:> :table.counter 1]}}
+ (sql/format {:pretty true}))
+["
+INSERT INTO table
+(id, counter) VALUES (?, ?)
+ON CONFLICT (id)
+DO UPDATE SET counter = table.counter + ? WHERE table.counter > ?
+" "id" 1 1 1]
+```
+
## INSERT INTO AS
HoneySQL supports aliases directly in `:insert-into` so no special
@@ -316,14 +387,14 @@ user=> (-> (create-table :cities)
;; default values for columns:
user=> (-> (create-table :distributors)
(with-columns [[:did :integer [:primary-key]
- ;; "serial" is inlined as 'SERIAL':
+ ;; "serial" is inlined as 'serial':
[:default [:nextval "serial"]]]
[:name [:varchar 40] [:not nil]]])
(sql/format {:pretty true}))
;; newlines inserted for readability:
["
CREATE TABLE distributors
-(did INTEGER PRIMARY KEY DEFAULT NEXTVAL('SERIAL'), name VARCHAR(40) NOT NULL)
+(did INTEGER PRIMARY KEY DEFAULT NEXTVAL('serial'), name VARCHAR(40) NOT NULL)
"]
;; PostgreSQL CHECK constraint is supported:
user=> (-> (create-table :products)
@@ -335,7 +406,7 @@ user=> (-> (create-table :products)
(sql/format {:pretty true}))
["
CREATE TABLE products
-(product_no INTEGER, name TEXT, price NUMERIC CHECK(PRICE > 0), discounted_price NUMERIC, CHECK((discounted_price > 0) AND (price > discounted_price)))
+(product_no INTEGER, name TEXT, price NUMERIC CHECK(price > 0), discounted_price NUMERIC, CHECK((discounted_price > 0) AND (price > discounted_price)))
"]
;; conditional creation:
user=> (-> (create-table :products :if-not-exists)
diff --git a/doc/special-syntax.md b/doc/special-syntax.md
index 8179c59..4f1deb4 100644
--- a/doc/special-syntax.md
+++ b/doc/special-syntax.md
@@ -8,15 +8,19 @@ The first group are used for SQL expressions. The second (last group) are used p
## array
-Accepts a single argument, which is expected to evaluate to
-a sequence, and produces `ARRAY[?, ?, ..]` for the elements
-of that sequence (as SQL parameters):
+Accepts a single argument, which is expected to evaluate to a sequence,
+with an optional second argument specifying the type of the array,
+and produces `ARRAY[?, ?, ..]` for the elements of that sequence (as SQL parameters):
```clojure
(require '[honey.sql :as sql])
(sql/format-expr [:array (range 5)])
;;=> ["ARRAY[?, ?, ?, ?, ?]" 0 1 2 3 4]
+(sql/format-expr [:array (range 3) :text])
+;;=> ["ARRAY[?, ?, ?]::TEXT[]" 0 1 2]
+(sql/format-expr [:array [] :integer])
+;;=> ["ARRAY[]::INTEGER[]"]
```
> Note: you cannot provide a named parameter as the argument for `:array` because the generated SQL depends on the number of elements in the sequence, so the following throws an exception:
@@ -79,7 +83,7 @@ that produces a SQL type:
```clojure
(sql/format-expr [:cast :a :int])
-;;=> ["CAST(a AS int)"]
+;;=> ["CAST(a AS INT)"]
```
## composite
@@ -101,6 +105,22 @@ Accepts a single expression and prefixes it with `DISTINCT `:
;;=> ["SELECT COUNT(DISTINCT status) AS n FROM table"]
```
+## dot .
+
+Accepts an expression and a field (or column) selection:
+
+```clojure
+(sql/format {:select [ [[:. :t :c]] [[:. :s :t :c]] ]})
+;;=> ["SELECT t.c, s.t.c"]
+```
+
+Can be used with `:nest` for field selection from composites:
+
+```clojure
+(sql/format {:select [ [[:. [:nest :v] :*]] [[:. [:nest [:myfunc :x]] :y]] ]})
+;;=> ["SELECT (v).*, (MYFUNC(x)).y"]
+```
+
## entity
Accepts a single keyword or symbol argument and produces a
@@ -190,12 +210,28 @@ than turning it into a positional parameter:
## interval
-Accepts two arguments: an expression and a keyword (or a symbol)
-that represents a time unit. Produces an `INTERVAL` expression:
+Accepts one or two arguments: either a string or an expression and
+a keyword (or a symbol) that represents a time unit.
+Produces an `INTERVAL` expression:
```clojure
(sql/format-expr [:date_add [:now] [:interval 30 :days]])
;;=> ["DATE_ADD(NOW(), INTERVAL ? DAYS)" 30]
+(sql/format-expr [:date_add [:now] [:interval "24 Hours"]])
+;;=> ["DATE_ADD(NOW(), INTERVAL '24 Hours')"]
+```
+
+> Note: PostgreSQL also has an `INTERVAL` data type which is unrelated to this syntax. In PostgreSQL, the closet equivalent would be `[:cast "30 days" :interval]` which will lift `"30 days"` out as a parameter. In DDL, for PostgreSQL, you can use `:interval` to produce the `INTERVAL` data type (without wrapping it in a vector).
+
+## join
+
+Accepts a table name (or expression) followed by one or more join clauses.
+Produces a nested `JOIN` expression, typically used as the table expression of
+a `JOIN` clause.
+
+```clojure
+(sql/format {:join [[[:join :tbl1 {:left-join [:tbl2 [:using :id]]}]]]})
+;;=> ["INNER JOIN (tbl1 LEFT JOIN tbl2 USING (id))"]
```
## lateral
@@ -330,6 +366,12 @@ parameters from them:
;;=> ["SELECT a, @var := 'foo'"]
(sql/format {:select [:a [[:raw ["@var := " ["foo"]]]]]})
;;=> ["SELECT a, @var := (?)" "foo"]
+;; when multiple expressions are provided, the enclosing
+;; vector can be omitted:
+(sql/format {:select [:a [[:raw "@var := " [:inline "foo"]]]]})
+;;=> ["SELECT a, @var := 'foo'"]
+(sql/format {:select [:a [[:raw "@var := " ["foo"]]]]})
+;;=> ["SELECT a, @var := (?)" "foo"]
```
`:raw` is also supported as a SQL clause for the same reason.
diff --git a/src/honey/sql.cljc b/src/honey/sql.cljc
index 9914650..f981aeb 100644
--- a/src/honey/sql.cljc
+++ b/src/honey/sql.cljc
@@ -28,7 +28,8 @@
* `sql-kw` -- turns a Clojure keyword (or symbol) into SQL code (makes
it uppercase and replaces - with space). "
(:refer-clojure :exclude [format])
- (:require [clojure.string :as str]))
+ (:require [clojure.string :as str]
+ [honey.sql.protocols :as p]))
;; default formatting for known clauses
@@ -54,7 +55,7 @@
:table
:select :select-distinct :select-distinct-on :select-top :select-distinct-top
:into :bulk-collect-into
- :insert-into :update :delete :delete-from :truncate
+ :insert-into :replace-into :update :delete :delete-from :truncate
:columns :set :from :using
:join-by
:join :left-join :right-join :inner-join :outer-join :full-join
@@ -93,6 +94,8 @@
[s x e]
(str s (str/replace x (str e) (str e e)) e))
+(declare register-clause!)
+
(def ^:private dialects
(atom
(reduce-kv (fn [m k v]
@@ -101,20 +104,25 @@
{:ansi {:quote #(strop \" % \")}
:sqlserver {:quote #(strop \[ % \])}
:mysql {:quote #(strop \` % \`)
- :clause-order-fn #(add-clause-before % :set :where)}
+ :clause-order-fn
+ #(add-clause-before % :set :where)}
:oracle {:quote #(strop \" % \") :as false}})))
; should become defonce
(def ^:private default-dialect (atom (:ansi @dialects)))
(def ^:private default-quoted (atom nil))
+(def ^:private default-quoted-snake (atom nil))
+(def ^:private default-inline (atom nil))
+(def ^:private default-checking (atom :none))
+(def ^:private default-numbered (atom false))
(def ^:private ^:dynamic *dialect* nil)
;; nil would be a better default but that makes testing individual
;; functions harder than necessary:
(def ^:private ^:dynamic *clause-order* default-clause-order)
-(def ^:private ^:dynamic *quoted* nil)
-(def ^:private ^:dynamic *quoted-snake* nil)
-(def ^:private ^:dynamic *inline* nil)
+(def ^:private ^:dynamic *quoted* @default-quoted)
+(def ^:private ^:dynamic *quoted-snake* @default-quoted-snake)
+(def ^:private ^:dynamic *inline* @default-inline)
(def ^:private ^:dynamic *params* nil)
(def ^:private ^:dynamic *values-default-columns* nil)
;; there is no way, currently, to enable suspicious characters
@@ -122,11 +130,12 @@
;; can be added to format to turn this on:
(def ^:private ^:dynamic *allow-suspicious-entities* false)
;; "linting" mode (:none, :basic, :strict):
-(def ^:private ^:dynamic *checking* :none)
+(def ^:private ^:dynamic *checking* @default-checking)
;; the current DSL hash map being formatted (for contains-clause?):
(def ^:private ^:dynamic *dsl* nil)
;; caching data to detect expressions that cannot be cached:
(def ^:private ^:dynamic *caching* nil)
+(def ^:private ^:dynamic *numbered* nil)
;; clause helpers
@@ -200,6 +209,9 @@
{:symbol x
:failure (str t)})))))
+(defn- ensure-sequential [xs]
+ (if (sequential? xs) xs [xs]))
+
(defn format-entity
"Given a simple SQL entity (a keyword or symbol -- or string),
return the equivalent SQL fragment (as a string -- no parameters).
@@ -209,13 +221,25 @@
(let [col-fn (if (or *quoted* (string? e))
(if *quoted-snake* name-_ name)
name-_)
- quote-fn (if (or *quoted* (string? e)) (:quote *dialect*) identity)
+ col-e (col-fn e)
+ dialect-q (:quote *dialect* identity)
+ quote-fn (cond (or *quoted* (string? e))
+ dialect-q
+ ;; #422: if default quoting and "unusual"
+ ;; characters in entity, then quote it:
+ (nil? *quoted*)
+ (fn opt-quote [part]
+ (if (re-find #"^[A-Za-z0-9_]+$" part)
+ part
+ (dialect-q part)))
+ :else
+ identity)
parts (if-let [n (when-not (or drop-ns (string? e))
(namespace-_ e))]
- [n (col-fn e)]
+ [n col-e]
(if aliased
- [(col-fn e)]
- (str/split (col-fn e) #"\.")))
+ [col-e]
+ (str/split col-e #"\.")))
entity (str/join "." (map #(cond-> % (not= "*" %) (quote-fn)) parts))
suspicious #";"]
(when-not *allow-suspicious-entities*
@@ -250,7 +274,10 @@
[k]
(let [n (str/replace (name k) "?" "??")]
(if (= \' (first n))
- (format-entity (keyword (subs n 1 (count n))))
+ (let [ident (subs n 1 (count n))
+ ident-l (str/lower-case ident)]
+ (binding [*quoted* (when-not (contains? #{"array"} ident-l) *quoted*)]
+ (format-entity (keyword ident))))
(-> n (dehyphen) (upper-case)))))
(defn- sym->kw
@@ -263,16 +290,24 @@
(keyword (name s)))
s))
-(defn- sqlize-value [x]
- (cond
- (nil? x) "NULL"
- (string? x) (str \' (str/replace x "'" "''") \')
- (ident? x) (sql-kw x)
- (vector? x) (str "[" (str/join ", " (map #'sqlize-value x)) "]")
- ;; issue 385: quoted UUIDs for PostgreSQL/ANSI
- #?(:clj (instance? java.util.UUID x) :cljs false)
- (str \' x \') ; UUID cannot contain quotes
- :else (str x)))
+(extend-protocol p/InlineValue
+ nil
+ (sqlize [_] "NULL")
+ #?(:clj String :cljs string)
+ (sqlize [x] (str \' (str/replace x "'" "''") \'))
+ #?(:clj clojure.lang.Keyword :cljs Keyword)
+ (sqlize [x] (sql-kw x))
+ #?(:clj clojure.lang.Symbol :cljs Symbol)
+ (sqlize [x] (sql-kw x))
+ #?(:clj clojure.lang.IPersistentVector :cljs PersistentVector)
+ (sqlize [x] (str "[" (str/join ", " (map p/sqlize x)) "]"))
+ #?@(:clj [java.util.UUID
+ ;; issue 385: quoted UUIDs for PostgreSQL/ANSI
+ (sqlize [x] (str \' x \'))])
+ #?(:clj Object :cljs default)
+ (sqlize [x] (str x)))
+
+(defn- sqlize-value [x] (p/sqlize x))
(defn- param-value [k]
(if (contains? *params* k)
@@ -285,6 +320,20 @@
{::wrapper
(fn [fk _] (param-value (fk)))}))
+(defn ->numbered [v]
+ (let [n (count (swap! *numbered* conj v))]
+ [(str "$" n) (with-meta (constantly (dec n))
+ {::wrapper
+ (fn [fk _] (get @*numbered* (fk)))})]))
+
+(defn ->numbered-param [k]
+ (let [n (count (swap! *numbered* conj k))]
+ [(str "$" n) (with-meta (constantly (dec n))
+ {::wrapper
+ (fn [fk _] (param-value (get @*numbered* (fk))))})]))
+
+(def ^:private ^:dynamic *formatted-column* (atom false))
+
(defn- format-var [x & [opts]]
;; rather than name/namespace, we want to allow
;; for multiple / in the %fun.call case so that
@@ -297,9 +346,16 @@
"(" (str/join ", " quoted-args) ")")])
(= \? (first c))
(let [k (keyword (subs c 1))]
- (if *inline*
- [(sqlize-value (param-value k))]
- ["?" (->param k)]))
+ (cond *inline*
+ [(sqlize-value (param-value k))]
+ *numbered*
+ (->numbered-param k)
+ :else
+ ["?" (->param k)]))
+ (= \' (first c))
+ (do
+ (reset! *formatted-column* true)
+ [(subs c 1)])
:else
[(format-entity x opts)])))
@@ -409,6 +465,50 @@
(let [[sqls params] (reduce-sql (map #(format-dsl %) xs))]
(into [(str/join (str " " (sql-kw k) " ") sqls)] params)))
+(defn- inline-kw?
+ "Return true if the expression should be treated as an inline SQL keeyword."
+ [expr]
+ (and (ident? expr)
+ (nil? (namespace expr))
+ (re-find #"^![a-zA-Z]" (name expr))))
+
+(defn format-interspersed-expr-list
+ "If there are inline (SQL) keywords, use them to join the formatted
+ expressions together. Otherwise behaves like plain format-expr-list.
+
+ This allows for argument lists like:
+ * [:overlay :foo :*placing :?subs :*from 3 :*for 4]
+ * [:trim :*leading-from :bar]"
+ [args & [opts]]
+ (loop [exprs (map #(format-expr % opts) (remove inline-kw? args))
+ args args
+ prev-in false
+ result []]
+ (if (seq args)
+ (let [[arg & args'] args]
+ (if (inline-kw? arg)
+ (let [sql (sql-kw (keyword (subs (name arg) 1)))]
+ (if (seq result)
+ (let [[cur & params] (peek result)]
+ (recur exprs args' true (conj (pop result)
+ (into [(str cur " " sql)] params))))
+ (recur exprs args' true (conj result [sql]))))
+ (if prev-in
+ (let [[cur & params] (peek result)
+ [sql & params'] (first exprs)]
+ (recur (rest exprs) args' false (conj (pop result)
+ (-> [(str cur " " sql)]
+ (into params)
+ (into params')))))
+ (recur (rest exprs) args' false (conj result (first exprs))))))
+ (reduce-sql result))))
+
+(comment
+ (format-interspersed-expr-list [:foo :*placing :?subs :*from 3 :*for 4]
+ {:params {:subs "bar"}})
+ (format-interspersed-expr-list [:*leading-from " foo "] {})
+ )
+
(defn format-expr-list
"Given a sequence of expressions represented as data, return a pair
where the first element is a sequence of SQL fragments and the second
@@ -496,7 +596,7 @@
(-> [sql'] (into params) (into params'))))
(defn- format-select-into [k xs]
- (let [[v e] (if (sequential? xs) xs [xs])
+ (let [[v e] (ensure-sequential xs)
[sql & params] (when e (format-expr e))]
(into [(str (sql-kw k) " " (format-entity v)
(when sql
@@ -512,18 +612,27 @@
(into [(str (format-entity (first x)) " " sql)] params))
[(format-entity x)]))
-(defn- format-with [k xs]
+(defn- format-with [k xs as-fn]
;; TODO: a sequence of pairs -- X AS expr -- where X is either [entity expr]
;; or just entity, as far as I can tell...
(let [[sqls params]
- (reduce-sql (map (fn [[x expr]]
- (let [[sql & params] (format-with-part x)
- [sql' & params'] (format-dsl expr)]
- ;; according to docs, CTE should _always_ be wrapped:
- (cond-> [(str sql " AS " (str "(" sql' ")"))]
- params (into params)
- params' (into params'))))
- xs))]
+ (reduce-sql
+ (map
+ (fn [[x expr :as with]]
+ (let [[sql & params] (format-with-part x)
+ non-query-expr? (or (ident? expr) (string? expr))
+ [sql' & params'] (if non-query-expr?
+ (format-expr expr)
+ (format-dsl expr))]
+ (if non-query-expr?
+ (cond-> [(str sql' " AS " sql)]
+ params' (into params')
+ params (into params))
+ ;; according to docs, CTE should _always_ be wrapped:
+ (cond-> [(str sql " " (as-fn with) " " (str "(" sql' ")"))]
+ params (into params)
+ params' (into params')))))
+ xs))]
(into [(str (sql-kw k) " " (str/join ", " sqls))] params)))
(defn- format-selector [k xs]
@@ -646,11 +755,12 @@
[]))
(defn- format-group-by [k xs]
- (let [[sqls params] (format-expr-list xs)]
+ (let [[sqls params] (format-expr-list (ensure-sequential xs))]
(into [(str (sql-kw k) " " (str/join ", " sqls))] params)))
(defn- format-order-by [k xs]
- (let [dirs (map #(when (sequential? %) (second %)) xs)
+ (let [xs (ensure-sequential xs)
+ dirs (map #(when (sequential? %) (second %)) xs)
[sqls params]
(format-expr-list (map #(if (sequential? %) (first %) %) xs))]
(into [(str (sql-kw k) " "
@@ -660,7 +770,7 @@
dirs)))] params)))
(defn- format-lock-strength [k xs]
- (let [[strength tables nowait] (if (sequential? xs) xs [xs])]
+ (let [[strength tables nowait] (ensure-sequential xs)]
[(str (sql-kw k) " " (sql-kw strength)
(when tables
(str
@@ -676,59 +786,79 @@
(str " " (sql-kw nowait))))))]))
(defn- format-values [k xs]
- (cond (sequential? (first xs))
- ;; [[1 2 3] [4 5 6]]
- (let [n-1 (map count xs)
- ;; issue #291: ensure all value sequences are the same length
- xs' (if (apply = n-1)
- xs
- (let [n-n (apply max n-1)]
- (map (fn [x] (take n-n (concat x (repeat nil)))) xs)))
- [sqls params]
- (reduce (fn [[sql params] [sqls' params']]
- [(conj sql (str "(" (str/join ", " sqls') ")"))
- (into params params')])
- [[] []]
- (map #'format-expr-list xs'))]
- (into [(str (sql-kw k) " " (str/join ", " sqls))] params))
-
- (map? (first xs))
- ;; [{:a 1 :b 2 :c 3}]
- (let [cols-1 (keys (first xs))
- ;; issue #291: check for all keys in all maps but still
- ;; use the keys from the first map if they match so that
- ;; users can rely on the key ordering if they want to,
- ;; e.g., see test that uses array-map for the first row
- cols-n (into #{} (mapcat keys) xs)
- cols (if (= (set cols-1) cols-n) cols-1 cols-n)
- [sqls params]
- (reduce (fn [[sql params] [sqls' params']]
- [(conj sql (str "(" (str/join ", " sqls') ")"))
- (if params' (into params params') params')])
- [[] []]
- (map (fn [m]
- (format-expr-list
- (map #(get m
- %
- ;; issue #366: use NULL or DEFAULT
- ;; for missing column values:
- (if (contains? *values-default-columns* %)
- [:default]
- nil))
- cols)))
- xs))]
- (into [(str "("
- (str/join ", "
- (map #(format-entity % {:drop-ns true}) cols))
- ") "
- (sql-kw k)
- " "
- (str/join ", " sqls))]
- params))
+ (let [first-xs (when (sequential? xs) (first (drop-while ident? xs)))]
+ (cond (contains? #{:default 'default} xs)
+ [(str (sql-kw xs) " " (sql-kw k))]
+ (empty? xs)
+ [(str (sql-kw k) " ()")]
+ (sequential? first-xs)
+ ;; [[1 2 3] [4 5 6]]
+ (let [n-1 (map count (filter sequential? xs))
+ ;; issue #291: ensure all value sequences are the same length
+ xs' (if (apply = n-1)
+ xs
+ (let [n-n (when (seq n-1) (apply max n-1))]
+ (map (fn [x]
+ (if (sequential? x)
+ (take n-n (concat x (repeat nil)))
+ x))
+ xs)))
+ [sqls params]
+ (reduce (fn [[sql params] [sqls' params']]
+ [(conj sql
+ (if (sequential? sqls')
+ (str "(" (str/join ", " sqls') ")")
+ sqls'))
+ (into params params')])
+ [[] []]
+ (map #(if (sequential? %)
+ (format-expr-list %)
+ [(sql-kw %)])
+ xs'))]
+ (into [(str (sql-kw k) " " (str/join ", " sqls))] params))
+
+ (map? first-xs)
+ ;; [{:a 1 :b 2 :c 3}]
+ (let [cols-1 (keys (first xs))
+ ;; issue #291: check for all keys in all maps but still
+ ;; use the keys from the first map if they match so that
+ ;; users can rely on the key ordering if they want to,
+ ;; e.g., see test that uses array-map for the first row
+ cols-n (into #{} (mapcat keys) (filter map? xs))
+ cols (if (= (set cols-1) cols-n) cols-1 cols-n)
+ [sqls params]
+ (reduce (fn [[sql params] [sqls' params']]
+ [(conj sql
+ (if (sequential? sqls')
+ (str "(" (str/join ", " sqls') ")")
+ sqls'))
+ (if params' (into params params') params')])
+ [[] []]
+ (map (fn [m]
+ (if (map? m)
+ (format-expr-list
+ (map #(get m
+ %
+ ;; issue #366: use NULL or DEFAULT
+ ;; for missing column values:
+ (if (contains? *values-default-columns* %)
+ [:default]
+ nil))
+ cols))
+ [(sql-kw m)]))
+ xs))]
+ (into [(str "("
+ (str/join ", "
+ (map #(format-entity % {:drop-ns true}) cols))
+ ") "
+ (sql-kw k)
+ " "
+ (str/join ", " sqls))]
+ params))
- :else
- (throw (ex-info ":values expects sequences or maps"
- {:first (first xs)}))))
+ :else
+ (throw (ex-info ":values expects a sequence of rows (maps) or column values (sequences)"
+ {:first (first xs)})))))
(comment
(into #{} (mapcat keys) [{:a 1 :b 2} {:b 3 :c 4}])
@@ -768,16 +898,21 @@
(defn- format-do-update-set [k x]
(cond (map? x)
(if (or (contains? x :fields) (contains? x 'fields))
- (let [sets (str/join ", "
- (map (fn [e]
- (let [e (format-entity e {:drop-ns true})]
- (str e " = EXCLUDED." e)))
- (or (:fields x)
- ('fields x))))
+ (let [fields (or (:fields x) ('fields x))
+ [sets & set-params]
+ (if (map? fields)
+ (format-set-exprs k fields)
+ [(str (sql-kw k) " "
+ (str/join ", "
+ (map (fn [e]
+ (let [e (format-entity e {:drop-ns true})]
+ (str e " = EXCLUDED." e)))
+ fields)))])
where (or (:where x) ('where x))
[sql & params] (when where (format-dsl {:where where}))]
- (into [(str (sql-kw k) " " sets
- (when sql (str " " sql)))] params))
+ (-> [(str sets (when sql (str " " sql)))]
+ (into set-params)
+ (into params)))
(format-set-exprs k x))
(sequential? x)
(let [[cols clauses] (split-with (complement map?) x)]
@@ -811,6 +946,17 @@
(str " " (str/join ", " (map #(format-simple-clause % "column/index operations") clauses)))))]
[(str (sql-kw k) " " (format-entity x))]))
+(def ^:private special-ddl-keywords
+ "If these are found in DDL, they should map to the given
+ SQL string instead of what sql-kw would do."
+ {:auto-increment "AUTO_INCREMENT"})
+
+(defn- sql-kw-ddl
+ "Handle SQL keywords in DDL (allowing for special/exceptions)."
+ [id]
+ (or (get special-ddl-keywords (sym->kw id))
+ (sql-kw id)))
+
(defn- format-ddl-options
"Given a sequence of options for a DDL statement (the part that
comes between the entity name being created/dropped and the
@@ -824,33 +970,54 @@
(str/join " "
(map (fn [e]
(if (ident? e)
- (sql-kw e)
+ (sql-kw-ddl e)
(format-simple-expr e context)))
opt))
+ (ident? opt)
+ (sql-kw-ddl opt)
:else
- (sql-kw opt))))
+ (throw (ex-info "expected symbol or keyword"
+ {:unexpected opt})))))
-(defn- destructure-create-item [table context]
+(defn- destructure-ddl-item [table context]
(let [params
(if (sequential? table)
table
[table])
tab? #(or (ident? %) (string? %))
coll (take-while tab? params)
- opts (drop-while tab? params)
+ opts (filter some? (drop-while tab? params))
ine (last coll)
[prequel table ine]
(if (= :if-not-exists (sym->kw ine))
[(butlast (butlast coll)) (last (butlast coll)) ine]
[(butlast coll) (last coll) nil])]
(into [(str/join " " (map sql-kw prequel))
- (format-entity table)
+ (when table (format-entity table))
(when ine (sql-kw ine))]
- (format-ddl-options opts context))))
+ (when opts
+ (format-ddl-options opts context)))))
+
+(defn- format-truncate [_ xs]
+ (let [[table & options] (ensure-sequential xs)
+ [pre table ine options] (destructure-ddl-item [table options] "truncate")]
+ (when (seq pre) (throw (ex-info "TRUNCATE syntax error" {:unexpected pre})))
+ (when (seq ine) (throw (ex-info "TRUNCATE syntax error" {:unexpected ine})))
+ [(str/join " " (cond-> ["TRUNCATE TABLE" table]
+ (seq options)
+ (conj options)))]))
+
+(comment
+ (destructure-ddl-item [:foo [:abc [:continue :wibble] :identity]] "test")
+ (destructure-ddl-item [:foo] "test")
+ (destructure-ddl-item [:id [:int :unsigned :auto-increment]] "test")
+ (destructure-ddl-item [[[:foreign-key :bar]] :quux [[:wibble :wobble]]] "test")
+ (format-truncate :truncate [:foo])
+ )
(defn- format-create [q k item as]
(let [[pre entity ine & more]
- (destructure-create-item item (str (sql-kw q) " options"))]
+ (destructure-ddl-item item (str (sql-kw q) " options"))]
[(str/join " " (remove nil?
(-> [(sql-kw q)
(when (and (= :create q) (seq pre)) pre)
@@ -888,26 +1055,48 @@
(let [[if-exists tables & more] (destructure-drop-items params "DROP options")]
[(str/join " " (remove nil? (into [(sql-kw k) if-exists tables] more)))]))
-(def ^:private ^:dynamic *formatted-column* (atom false))
-
(defn- format-single-column [xs]
- (reset! *formatted-column* true)
- (str/join " " (cons (format-simple-expr (first xs) "column operation")
- (map #(binding [*formatted-column* (atom false)]
- (cond-> (format-simple-expr % "column operation")
- (not @*formatted-column*)
- (upper-case)))
- (rest xs)))))
+ (let [[col & options] (if (ident? (first xs)) xs (cons nil xs))
+ [pre col ine & options]
+ (destructure-ddl-item [col options] "column operation")]
+ (when (seq pre) (throw (ex-info "column syntax error" {:unexpected pre})))
+ (when (seq ine) (throw (ex-info "column syntax error" {:unexpected ine})))
+ (str/join " " (filter seq (cons col options)))))
+
+(comment
+ (destructure-ddl-item [:foo [:abc [:continue :wibble] :identity]] "test")
+ (destructure-ddl-item [:foo] "test")
+ (destructure-ddl-item [:id [:int :unsigned :auto-increment]] "test")
+ (format-single-column [:id :int :unsigned :auto-increment])
+ (format-single-column [[:constraint :code_title] [:primary-key :code :title]])
+ (destructure-ddl-item [[[:foreign-key :bar]] :quux [[:wibble :wobble]]] "test")
+
+ (format-truncate :truncate [:foo])
+
+ (destructure-ddl-item [:address [:text]] "test")
+ (format-single-column [:address :text])
+ (format-single-column [:did :uuid [:default [:gen_random_uuid]]])
+ )
(defn- format-table-columns [_ xs]
[(str "("
(str/join ", " (map #'format-single-column xs))
")")])
-(defn- format-add-item [k spec]
+(defn- format-add-single-item [k spec]
(if (contains? #{:if-not-exists 'if-not-exists} (last spec))
- [(str (sql-kw k) " " (sql-kw :if-not-exists) " " (format-single-column (butlast spec)))]
- [(str (sql-kw k) " " (format-single-column spec))]))
+ (str (sql-kw k) " " (sql-kw :if-not-exists) " " (format-single-column (butlast spec)))
+ (str (sql-kw k) " " (format-single-column spec))))
+
+(defn- format-add-item [k spec]
+ (let [items (if (and (sequential? spec) (sequential? (first spec))) spec [spec])]
+ [(str/join ", " (for [item items] (format-add-single-item k item)))]))
+
+(comment
+ (format-add-item :add-column [:address :text])
+ (format-add-single-item :add-column [:address :text])
+ (format-single-column [:address :text])
+ )
(defn- format-rename-item [k [x y]]
[(str (sql-kw k) " " (format-entity x) " TO " (format-entity y))])
@@ -926,6 +1115,43 @@
(into [(str/join sqls)] params))
[s]))
+(defn- destructure-drop-columns [tables]
+ (let [params
+ (if (sequential? tables)
+ tables
+ [tables])
+ _ (when-not (every? ident? params)
+ (throw (ex-info "DROP COLUMNS expects just column names"
+ {:tables tables})))]
+ (loop [if-exists false coll params sqls []]
+ (if (seq coll)
+ (if (#{:if-exists 'if-exists} (first coll))
+ (recur true (rest coll) sqls)
+ (recur false (rest coll)
+ (conj sqls (cond->> (format-entity (first coll))
+ if-exists
+ (str (sql-kw :if-exists) " ")))))
+ (if if-exists
+ (throw (ex-info (str "DROP COLUMNS: missing column name after IF EXISTS")
+ {:tables tables}))
+ sqls)))))
+
+(defn- format-drop-columns
+ [k params]
+ (let [tables (destructure-drop-columns params)]
+ [(str/join ", " (mapv #(str (sql-kw k) " " %) tables))]))
+
+(defn- check-where
+ "Given a formatter function, performs a pre-flight check that there is
+ a non-empty where clause if at least basic checking is enabled."
+ [formatter]
+ (fn [k xs]
+ (when-not (= :none *checking*)
+ (when-not (seq (:where *dsl*))
+ (throw (ex-info (str (sql-kw k) " without a non-empty WHERE clause is dangerous")
+ {:clause k :where (:where *dsl*)}))))
+ (formatter k xs)))
+
(def ^:private base-clause-order
"The (base) order for known clauses. Can have items added and removed.
@@ -943,7 +1169,7 @@
and removed."
(atom {:alter-table #'format-alter-table
:add-column #'format-add-item
- :drop-column #'format-drop-items
+ :drop-column #'format-drop-columns
:alter-column (fn [k spec]
(format-add-item
(if (mysql?) :modify-column k)
@@ -969,8 +1195,15 @@
:nest (fn [_ x]
(let [[sql & params] (format-dsl x {:nested true})]
(into [sql] params)))
- :with #'format-with
- :with-recursive #'format-with
+ :with (let [as-fn
+ (fn [[_ _ materialization]]
+ (condp = materialization
+ :materialized "AS MATERIALIZED"
+ :not-materialized "AS NOT MATERIALIZED"
+ "AS"))]
+ (fn [k xs] (format-with k xs as-fn)))
+ :with-recursive (let [as-fn (constantly "AS")]
+ (fn [k xs] (format-with k xs as-fn)))
:intersect #'format-on-set-op
:union #'format-on-set-op
:union-all #'format-on-set-op
@@ -985,10 +1218,11 @@
:into #'format-select-into
:bulk-collect-into #'format-select-into
:insert-into #'format-insert
- :update #'format-selector
- :delete #'format-selects
- :delete-from #'format-selector
- :truncate #'format-selector
+ :replace-into #'format-insert
+ :update (check-where #'format-selector)
+ :delete (check-where #'format-selects)
+ :delete-from (check-where #'format-selector)
+ :truncate #'format-truncate
:columns #'format-columns
:set #'format-set-exprs
:from #'format-selects
@@ -1087,9 +1321,10 @@
(def ^:private infix-ops
(-> #{"mod" "and" "or" "xor" "<>" "<=" ">=" "||" "<->"
- "like" "not-like" "regexp" "&&"
+ "like" "not-like" "regexp" "~" "&&"
"ilike" "not-ilike" "similar-to" "not-similar-to"
- "is" "is-not" "not=" "!=" "regex"}
+ "is" "is-not" "not=" "!=" "regex"
+ "with-ordinality"}
(into (map str "+-*%|&^=<>"))
(into (keys infix-aliases))
(into (vals infix-aliases))
@@ -1098,7 +1333,10 @@
(atom)))
(def ^:private op-ignore-nil (atom #{:and :or}))
-(def ^:private op-variadic (atom #{:and :or :+ :* :|| :&&}))
+(def ^:private op-can-be-unary
+ "The operators that can be unary. This is a fixed set until someone
+ identifies any new ones."
+ (atom (into #{} (map (comp keyword str) "+-~"))))
(defn- unwrap [x opts]
(if-let [m (meta x)]
@@ -1110,30 +1348,44 @@
(defn- format-in [in [x y]]
(let [[sql-x & params-x] (format-expr x {:nested true})
[sql-y & params-y] (format-expr y {:nested true})
- values (unwrap (first params-y) {})]
+ [v1 :as values] (map #(unwrap % {}) params-y)]
;; #396: prevent caching IN () when named parameter is used:
(when (and (meta (first params-y))
(::wrapper (meta (first params-y)))
*caching*)
(throw (ex-info "SQL that includes IN () expressions cannot be cached" {})))
(when-not (= :none *checking*)
- (when (or (and (sequential? y) (empty? y))
- (and (sequential? values) (empty? values)))
+ (when (or (and (sequential? y) (empty? y))
+ (and (sequential? v1) (empty? v1)))
(throw (ex-info "IN () empty collection is illegal"
{:clause [in x y]})))
(when (and (= :strict *checking*)
- (or (and (sequential? y) (some nil? y))
- (and (sequential? values) (some nil? values))))
+ (or (and (sequential? y) (some nil? y))
+ (and (sequential? v1) (some nil? v1))))
(throw (ex-info "IN (NULL) does not match"
{:clause [in x y]}))))
- (if (and (= "?" sql-y) (= 1 (count params-y)) (coll? values))
- (let [sql (str "(" (str/join ", " (repeat (count values) "?")) ")")]
- (-> [(str sql-x " " (sql-kw in) " " sql)]
- (into params-x)
- (into values)))
- (-> [(str sql-x " " (sql-kw in) " " sql-y)]
- (into params-x)
- (into params-y)))))
+ (cond (and (not *numbered*)
+ (= "?" sql-y)
+ (= 1 (count params-y))
+ (coll? v1))
+ (let [sql (str "(" (str/join ", " (repeat (count v1) "?")) ")")]
+ (-> [(str sql-x " " (sql-kw in) " " sql)]
+ (into params-x)
+ (into v1)))
+ (and *numbered*
+ (= (str "$" (count @*numbered*)) sql-y)
+ (= 1 (count params-y))
+ (coll? v1))
+ (let [vs (for [v v1] (->numbered v))
+ sql (str "(" (str/join ", " (map first vs)) ")")]
+ (-> [(str sql-x " " (sql-kw in) " " sql)]
+ (into params-x)
+ (conj nil)
+ (into (map second vs))))
+ :else
+ (-> [(str sql-x " " (sql-kw in) " " sql-y)]
+ (into params-x)
+ (into (if *numbered* values params-y))))))
(defn- function-0 [k xs]
[(str (sql-kw k)
@@ -1226,23 +1478,30 @@
:primary-key #'function-0
:references #'function-1
:unique #'function-1-opt
+ :. (fn [_ [expr col subcol]]
+ (let [[sql & params] (format-expr expr)]
+ (into [(str sql "." (format-entity col)
+ (when subcol
+ (str "." (format-entity subcol))))]
+ params)))
;; used in DDL to force rendering as a SQL entity instead
;; of a SQL keyword:
:entity (fn [_ [e]] [(format-entity e)])
;; bigquery column types:
:bigquery/array (fn [_ spec]
[(str "ARRAY<"
- (str/join " " (map #(format-simple-expr % "column operation") spec))
+ (str/join " " (map #(sql-kw %) spec))
">")])
:bigquery/struct (fn [_ spec]
[(str "STRUCT<"
(str/join ", " (map format-single-column spec))
">")])
:array
- (fn [_ [arr]]
+ (fn [_ [arr type]]
;; allow for (unwrap arr) here?
- (let [[sqls params] (format-expr-list arr)]
- (into [(str "ARRAY[" (str/join ", " sqls) "]")] params)))
+ (let [[sqls params] (format-expr-list arr)
+ type-str (when type (str "::" (sql-kw type) "[]"))]
+ (into [(str "ARRAY[" (str/join ", " sqls) "]" type-str)] params)))
:between
(fn [_ [x a b]]
(let [[sql-x & params-x] (format-expr x {:nested true})
@@ -1257,7 +1516,9 @@
:cast
(fn [_ [x type]]
(let [[sql & params] (format-expr x)
- [sql' & params'] (format-expr type)]
+ [sql' & params'] (if (ident? type)
+ [(sql-kw type)]
+ (format-expr type))]
(-> [(str "CAST(" sql " AS " sql' ")")]
(into params)
(into params'))))
@@ -1283,8 +1544,17 @@
(format-expr x)))
:interval
(fn [_ [n units]]
- (let [[sql & params] (format-expr n)]
- (into [(str "INTERVAL " sql " " (sql-kw units))] params)))
+ (if units
+ (let [[sql & params] (format-expr n)]
+ (into [(str "INTERVAL " sql " " (sql-kw units))] params))
+ (binding [*inline* true]
+ (let [[sql & params] (format-expr n)]
+ (into [(str "INTERVAL " sql)] params)))))
+ :join
+ (fn [_ [e & js]]
+ (let [[sqls params] (reduce-sql (cons (format-expr e)
+ (map format-dsl js)))]
+ (into [(str "(" (str/join " " sqls) ")")] params)))
:lateral
(fn [_ [clause-or-expr]]
(if (map? clause-or-expr)
@@ -1294,13 +1564,16 @@
(into [(str "LATERAL " sql)] params))))
:lift
(fn [_ [x]]
- (if *inline*
- ;; this is pretty much always going to be wrong,
- ;; but it could produce a valid result so we just
- ;; assume that the user knows what they are doing:
- [(sqlize-value x)]
- ["?" (with-meta (constantly x)
- {::wrapper (fn [fx _] (fx))})]))
+ (cond *inline*
+ ;; this is pretty much always going to be wrong,
+ ;; but it could produce a valid result so we just
+ ;; assume that the user knows what they are doing:
+ [(sqlize-value x)]
+ *numbered*
+ (->numbered x)
+ :else
+ ["?" (with-meta (constantly x)
+ {::wrapper (fn [fx _] (fx))})]))
:nest
(fn [_ [x]]
(let [[sql & params] (format-expr x)]
@@ -1310,9 +1583,9 @@
(let [[sql & params] (format-expr x {:nested true})]
(into [(str "NOT " sql)] params)))
:order-by
- (fn [k [e q]]
+ (fn [k [e & qs]]
(let [[sql-e & params-e] (format-expr e)
- [sql-q & params-q] (format-dsl {k [q]})]
+ [sql-q & params-q] (format-dsl {k qs})]
(-> [(str sql-e " " sql-q)]
(into params-e)
(into params-q))))
@@ -1332,14 +1605,78 @@
(into [(str/join ", " sqls)] params)))
:param
(fn [_ [k]]
- (if *inline*
- [(sqlize-value (param-value k))]
- ["?" (->param k)]))
+ (cond *inline*
+ [(sqlize-value (param-value k))]
+ *numbered*
+ (->numbered-param k)
+ :else
+ ["?" (->param k)]))
:raw
- (fn [_ [xs]]
- (raw-render xs))
+ (fn [_ [& xs]]
+ ;; #476 : preserve existing single-argument behavior...
+ (if (= 1 (count xs))
+ (raw-render (first xs))
+ ;; ...but allow for multiple arguments now:
+ (raw-render xs)))
:within-group expr-clause-pairs}))
+(defn- format-equality-expr [op' op expr nested]
+ (let [[_ a b & y] expr
+ _ (when (seq y)
+ (throw (ex-info (str "only binary "
+ op'
+ " is supported")
+ {:expr expr})))
+ [s1 & p1] (format-expr a {:nested true})
+ [s2 & p2] (format-expr b {:nested true})]
+ (-> (if (or (nil? a) (nil? b))
+ (str (if (nil? a)
+ (if (nil? b) "NULL" s2)
+ s1)
+ (if (= := op) " IS NULL" " IS NOT NULL"))
+ (str s1 " " (sql-kw op) " " s2))
+ (cond-> nested
+ (as-> s (str "(" s ")")))
+ (vector)
+ (into p1)
+ (into p2))))
+
+(defn- format-infix-expr [op' op expr nested]
+ (let [args (cond->> (rest expr)
+ (contains? @op-ignore-nil op)
+ (remove nil?))
+ args (cond (seq args)
+ args
+ (= :and op)
+ [true]
+ (= :or op)
+ [false]
+ :else ; args is empty and not a special case
+ [])
+ [sqls params]
+ (reduce-sql (map #(format-expr % {:nested true}) args))]
+ (when-not (pos? (count sqls))
+ (throw (ex-info (str "no operands found for " op')
+ {:expr expr})))
+ (into [(cond-> (str/join (str " " (sql-kw op) " ") sqls)
+ (and (contains? @op-can-be-unary op)
+ (= 1 (count sqls)))
+ (as-> s (str (sql-kw op) " " s))
+ nested
+ (as-> s (str "(" s ")")))]
+ params)))
+
+(defn- format-fn-call-expr [op expr]
+ (let [args (rest expr)
+ [sqls params] (format-interspersed-expr-list args)]
+ (into [(str (sql-kw op)
+ (if (and (= 1 (count args))
+ (map? (first args))
+ (= 1 (count sqls)))
+ (str " " (first sqls))
+ (str "(" (str/join ", " sqls) ")")))]
+ params)))
+
(defn format-expr
"Given a data structure that represents a SQL expression and a hash
map of options, return a vector containing a string -- the formatted
@@ -1355,40 +1692,13 @@
(format-dsl expr (assoc opts :nested true))
(sequential? expr)
- (let [op (sym->kw (first expr))]
- (if (keyword? op)
- (cond (contains? @infix-ops op)
- (if (contains? @op-variadic op) ; no aliases here, no special semantics
- (let [x (if (contains? @op-ignore-nil op)
- (remove nil? expr)
- expr)
- [sqls params]
- (reduce-sql (map #(format-expr % {:nested true})
- (rest x)))]
- (into [(cond-> (str/join (str " " (sql-kw op) " ") sqls)
- nested
- (as-> s (str "(" s ")")))]
- params))
- (let [[_ a b & y] expr
- _ (when (seq y)
- (throw (ex-info (str "only binary "
- op
- " is supported")
- {:expr expr})))
- [s1 & p1] (format-expr a {:nested true})
- [s2 & p2] (format-expr b {:nested true})
- op (get infix-aliases op op)]
- (-> (if (and (#{:= :<>} op) (or (nil? a) (nil? b)))
- (str (if (nil? a)
- (if (nil? b) "NULL" s2)
- s1)
- (if (= := op) " IS NULL" " IS NOT NULL"))
- (str s1 " " (sql-kw op) " " s2))
- (cond-> nested
- (as-> s (str "(" s ")")))
- (vector)
- (into p1)
- (into p2))))
+ (let [op' (sym->kw (first expr))
+ op (get infix-aliases op' op')]
+ (if (keyword? op')
+ (cond (contains? @infix-ops op')
+ (if (contains? #{:= :<>} op)
+ (format-equality-expr op' op expr nested)
+ (format-infix-expr op' op expr nested))
(contains? #{:in :not-in} op)
(let [[sql & params] (format-in op (rest expr))]
(into [(if nested (str "(" sql ")") sql)] params))
@@ -1396,15 +1706,7 @@
(let [formatter (get @special-syntax op)]
(formatter op (rest expr)))
:else
- (let [args (rest expr)
- [sqls params] (format-expr-list args)]
- (into [(str (sql-kw op)
- (if (and (= 1 (count args))
- (map? (first args))
- (= 1 (count sqls)))
- (str " " (first sqls))
- (str "(" (str/join ", " sqls) ")")))]
- params)))
+ (format-fn-call-expr op expr))
(let [[sqls params] (format-expr-list expr)]
(into [(str "(" (str/join ", " sqls) ")")] params))))
@@ -1415,9 +1717,12 @@
["NULL"]
:else
- (if *inline*
- [(sqlize-value expr)]
- ["?" expr])))
+ (cond *inline*
+ [(sqlize-value expr)]
+ *numbered*
+ (->numbered expr)
+ :else
+ ["?" expr])))
(defn- check-dialect [dialect]
(when-not (contains? @dialects dialect)
@@ -1449,41 +1754,53 @@
This is the primary API for HoneySQL and handles dialects, quoting,
and named parameters.
+ If the data DSL is a hash map, it will be treated as a SQL statement
+ and formatted via `format-dsl`, otherwise it will be treated as a SQL
+ expression and formatted via `format-expr`.
+
`format` accepts options as either a single hash map argument or
as named arguments (alternating keys and values). If you are using
Clojure 1.11 (or later) you can mix'n'match, providing some options
as named arguments followed by other options in a hash map."
([data] (format data {}))
([data opts]
- (let [cache (:cache opts)
- dialect? (contains? opts :dialect)
- dialect (when dialect? (get @dialects (check-dialect (:dialect opts))))]
+ (let [cache (:cache opts)
+ dialect? (contains? opts :dialect)
+ dialect (when dialect? (get @dialects (check-dialect (:dialect opts))))
+ numbered (if (contains? opts :numbered)
+ (:numbered opts)
+ @default-numbered)
+ formatter (if (map? data) #'format-dsl #'format-expr)]
(binding [*dialect* (if dialect? dialect @default-dialect)
*caching* cache
*checking* (if (contains? opts :checking)
(:checking opts)
- :none)
+ @default-checking)
*clause-order* (if dialect?
(if-let [f (:clause-order-fn dialect)]
(f @base-clause-order)
@current-clause-order)
@current-clause-order)
- *inline* (when (contains? opts :inline)
- (:inline opts))
+ *inline* (if (contains? opts :inline)
+ (:inline opts)
+ @default-inline)
+ *numbered* (when numbered
+ (atom []))
*quoted* (cond (contains? opts :quoted)
(:quoted opts)
dialect?
true
:else
@default-quoted)
- *quoted-snake* (when (contains? opts :quoted-snake)
- (:quoted-snake opts))
+ *quoted-snake* (if (contains? opts :quoted-snake)
+ (:quoted-snake opts)
+ @default-quoted-snake)
*params* (:params opts)
*values-default-columns* (:values-default-columns opts)]
(if cache
- (->> (through-opts opts cache data (fn [_] (format-dsl data (dissoc opts :cache))))
+ (->> (through-opts opts cache data (fn [_] (formatter data (dissoc opts :cache))))
(mapv #(unwrap % opts)))
- (mapv #(unwrap % opts) (format-dsl data opts))))))
+ (mapv #(unwrap % opts) (formatter data opts))))))
([data k v & {:as opts}] (format data (assoc opts k v))))
(defn set-dialect!
@@ -1492,7 +1809,10 @@
Can be: `:ansi` (the default), `:mysql`, `:oracle`, or `:sqlserver`.
Can optionally accept `:quoted true` (or `:quoted false`) to set the
- default global quoting strategy.
+ default global quoting strategy. Without `:quoted`, the default global
+ quoting strategy will be reset (only quoting unusual entity names).
+
+ Note that calling `set-options!` can override this default.
Dialects are always applied to the base order to create the current order."
[dialect & {:keys [quoted]}]
@@ -1501,6 +1821,31 @@
(reset! current-clause-order (f @base-clause-order)))
(reset! default-quoted quoted))
+(defn set-options!
+ "Set default values for any or all of the following options:
+ * :checking
+ * :inline
+ * :numbered
+ * :quoted
+ * :quoted-snake
+ Note that calling `set-dialect!` can override the default for `:quoted`."
+ [opts]
+ (let [unknowns (dissoc opts :checking :inline :numbered :quoted :quoted-snake)]
+ (when (seq unknowns)
+ (throw (ex-info (str (str/join ", " (keys unknowns))
+ " are not options that can be set globally.")
+ unknowns)))
+ (when (contains? opts :checking)
+ (reset! default-checking (:checking opts)))
+ (when (contains? opts :inline)
+ (reset! default-inline (:inline opts)))
+ (when (contains? opts :numbered)
+ (reset! default-numbered (:numbered opts)))
+ (when (contains? opts :quoted)
+ (reset! default-quoted (:quoted opts)))
+ (when (contains? opts :quoted-snake)
+ (reset! default-quoted-snake (:quoted-snake opts)))))
+
(defn clause-order
"Return the current order that known clauses will be applied when
formatting a data structure into SQL. This may be useful when you are
@@ -1538,6 +1883,11 @@
(swap! current-clause-order add-clause-before clause before)
(swap! clause-format assoc clause f))))
+(defn registered-clause?
+ "Return true if the clause is known to HoneySQL."
+ [clause]
+ (contains? @clause-format (sym->kw clause)))
+
(defn register-dialect!
"Register a new dialect. Accepts a dialect name (keyword) and a hash
map that must contain at least a `:quoted` key whose value is a unary
@@ -1568,6 +1918,11 @@
{:dialect-spec dialect-spec}))))
(swap! dialects assoc dialect (assoc dialect-spec :dialect dialect)))
+(defn registered-dialect?
+ "Return true if the dialect is known to HoneySQL."
+ [dialect]
+ (contains? @dialects dialect))
+
(defn get-dialect
"Given a dialect name (keyword), return its definition.
Returns `nil` if the dialect is unknown."
@@ -1593,19 +1948,27 @@
{:type (type formatter)})))
(swap! special-syntax assoc function f))))
+(defn registered-fn?
+ "Return true if the function is known to HoneySQL."
+ [function]
+ (contains? @special-syntax (sym->kw function)))
+
(defn register-op!
- "Register a new infix operator. Operators can be defined to be variadic (the
- default is that they are binary) and may choose to ignore `nil` arguments
- (this can make it easier to programmatically construct the DSL)."
- [op & {:keys [variadic ignore-nil]}]
+ "Register a new infix operator. All operators are variadic and may choose
+ to ignore `nil` arguments (this can make it easier to programmatically
+ construct the DSL)."
+ [op & {:keys [ignore-nil]}]
(let [op (sym->kw op)]
(assert (keyword? op))
(swap! infix-ops conj op)
- (when variadic
- (swap! op-variadic conj op))
(when ignore-nil
(swap! op-ignore-nil conj op))))
+(defn registered-op?
+ "Return true if the operator is known to HoneySQL."
+ [op]
+ (contains? @infix-ops (sym->kw op)))
+
;; helper functions to create HoneySQL data structures from other things
(defn map=
@@ -1652,6 +2015,9 @@
(format {:select [:*] :from [:table]
:where [:< [:date_add :expiry [:interval 30 :days]] [:now]]} {})
(format-expr [:interval 30 :days])
+ (format {:select [:*] :from [:table]
+ :where [:< [:date_add :expiry [:interval "30 Days"]] [:now]]} {})
+ (format-expr [:interval "30 Days"])
(format {:select [:*] :from [:table]
:where [:= :id (int 1)]} {:dialect :mysql})
(map fn? (format {:select [:*] :from [:table]
@@ -1689,7 +2055,7 @@
(sql/format-expr [:primary-key])
(sql/register-op! 'y)
(sql/format {:where '[y 2 3]})
- (sql/register-op! :<=> :variadic true :ignore-nil true)
+ (sql/register-op! :<=> :ignore-nil true)
;; and then use the new operator:
(sql/format {:select [:*], :from [:table], :where [:<=> nil :x 42]})
(sql/register-fn! :foo (fn [f args] ["FOO(?)" (first args)]))
@@ -1701,4 +2067,4 @@
(sql/register-fn! :foo foo-formatter)
(sql/format {:select [:*], :from [:table], :where [:foo [:+ :a 1]]})
- ,)
+ )
diff --git a/src/honey/sql/helpers.cljc b/src/honey/sql/helpers.cljc
index a26e263..90c8dca 100644
--- a/src/honey/sql/helpers.cljc
+++ b/src/honey/sql/helpers.cljc
@@ -51,7 +51,11 @@
;; implementation helpers:
(defn- default-merge [current args]
- (c/into (vec current) args))
+ (let [current (cond
+ (nil? current) []
+ (sequential? current) (vec current)
+ :else [current])]
+ (c/into current args)))
(defn- sym->kw
"Given a symbol, produce a keyword, retaining the namespace
@@ -127,7 +131,7 @@
(defn- helper-merge [data k args]
(if-let [merge-fn (special-merges k)]
- (if-let [clause (merge-fn (get data k) args)]
+ (if-some [clause (merge-fn (get data k) args)]
(assoc data k clause)
data)
(clojure.core/update data k default-merge args)))
@@ -138,6 +142,12 @@
(helper-merge data k args))
(helper-merge {} k args)))
+(defn- generic-grouped [k args]
+ (if (map? (first args))
+ (let [[data & args] args]
+ (helper-merge data k [args]))
+ (helper-merge {} k [args])))
+
(defn- generic-1 [k [data arg]]
(if (map? data)
(assoc data k arg)
@@ -169,15 +179,18 @@
(add-column :name [:varchar 32] [:not nil])"
[& col-elems]
- (generic :add-column col-elems))
+ (generic-grouped :add-column col-elems))
(defn drop-column
- "Takes a single column name (use with `alter-table`).
+ "Takes one or more column names (use with `alter-table`).
+
+ Accepts an `IF EXISTS` flag (keyword or symbol) before
+ any column names.
- (alter-table :foo (drop-column :bar))"
+ (alter-table :foo (drop-column :bar :if-exists :quux))"
{:arglists '([col])}
- [& args]
- (generic-1 :drop-column args))
+ [& col-elems]
+ (generic :drop-column col-elems))
(defn alter-column
"Like add-column, accepts any number of SQL elements
@@ -185,7 +198,7 @@
(alter-column :name [:varchar 64] [:not nil])"
[& col-elems]
- (generic :alter-column col-elems))
+ (generic-grouped :alter-column col-elems))
(defn modify-column
"Like add-column, accepts any number of SQL elements
@@ -196,7 +209,7 @@
MySQL-specific, deprecated. Use `alter-column` and
specify the MySQL dialect to get `MODIFY COLUMN`."
[& col-elems]
- (generic :modify-column col-elems))
+ (generic-grouped :modify-column col-elems))
(defn rename-column
"Accepts two column names: the original name and the
@@ -477,6 +490,19 @@
(generic :insert-into [data [table cols] statement])
(generic :insert-into args'))))
+(defn replace-into
+ "Accepts a table name or a table/alias pair. That
+ can optionally be followed by a collection of
+ column names. That can optionally be followed by
+ a (select) statement clause.
+
+ The arguments are identical to insert-into.
+ The REPLACE INTO statement is only supported by
+ MySQL and SQLite."
+ {:arglists '([table] [table cols] [table statement] [table cols statement])}
+ [& args]
+ (apply insert-into args))
+
(defn update
"Accepts either a table name or a table/alias pair.
@@ -1010,7 +1036,16 @@
(assoc :do-nothing do-nothing)
do-update-set
(assoc :do-update-set (if where
- {:fields do-update-set
+ {:fields
+ (cond (and (= 1 (count do-update-set))
+ (map? (first do-update-set)))
+ (first do-update-set)
+ (every? #(and (vector? %)
+ (= 2 (count %)))
+ do-update-set)
+ (into {} do-update-set)
+ :else
+ do-update-set)
:where where}
do-update-set))))))
diff --git a/src/honey/sql/pg_ops.cljc b/src/honey/sql/pg_ops.cljc
index fd3076f..247d75d 100644
--- a/src/honey/sql/pg_ops.cljc
+++ b/src/honey/sql/pg_ops.cljc
@@ -27,20 +27,36 @@
;; see https://www.postgresql.org/docs/current/functions-json.html
-(def -> "The -> operator." :->)
-(def ->> "The ->> operator." :->>)
-(def hash> "The #> operator." :#>)
-(def hash>> "The #>> operator." :#>>)
-(def at> "The @> operator." (keyword "@>"))
-(def <at "The <@ operator." (keyword "<@"))
-(def ? "The ? operator." :?)
-(def ?| "The ?| operator." :?|)
-(def ?& "The ?& operator." :?&)
-(def || "The || operator." :||)
-(def - "The - operator." :-)
-(def hash- "The #- operator." :#-)
-(def at? "The @? operator." (keyword "@?"))
-(def atat "The @@ operator." (keyword "@@"))
+(def ->
+ "The -> operator for accessing nested JSON(B) values as JSON(B).
+ Ex.:
+ ```clojure
+ (sql/format {:select [[[:->> [:-> :my_column \"kids\" [:inline 0]] \"name\"]]]})
+ ; => [\"SELECT (my_column -> ? -> 0) ->> ?\" \"kids\" \"name\"]
+ ```
+
+ Notice we need to wrap the keys/indices with :inline if we don't want them to become parameters."
+ :->)
+(def ->> "The ->> operator - like -> but returns the value as text instead of a JSON object." :->>)
+(def hash> "The #> operator extracts JSON sub-object at the specified path." :#>)
+(def hash>> "The #>> operator - like hash> but returns the value as text instead of JSON object." :#>>)
+(def at> "The @> operator - does the first JSON value contain the second?" (keyword "@>"))
+(def <at "The <@ operator - is the first JSON value contained in the second?" (keyword "<@"))
+(def ? "The ? operator - does the text string exist as a top-level key or array element within the JSON value?" :?)
+(def ?| "The ?| operator - do any of the strings in the text array exist as top-level keys or array elements?" :?|)
+(def ?& "The ?& operator - do all of the strings in the text array exist as top-level keys or array elements?" :?&)
+(def || "The || operator - concatenates two jsonb values (arrays or objects; anything else treated as 1-element array)." :||)
+(def -
+ "The - operator:
+ - text value: deletes a key (and its value) from a JSON object, or matching string value(s) from a JSON array
+ - int value: deletes the array element with specified index (negative integers count from the end)"
+ :-)
+(def hash- "The #- operator - deletes the field or array element at the specified path, where path elements can be either field keys or array indexes." :#-)
+(def at? "The @? operator - does JSON path return any item for the specified JSON value?" (keyword "@?"))
+(def atat
+ "The @@ operator - returns the result of a JSON path predicate check for the specified JSON value.
+ Only the first item of the result is taken into account. If the result is not Boolean, then NULL is returned."
+ (keyword "@@"))
(def tilde "The case-sensitive regex match operator." (keyword "~"))
(def tilde* "The case-insensitive regex match operator." (keyword "~*"))
@@ -52,7 +68,7 @@
(def !regex !tilde)
(def !iregex !tilde*)
-(sql/register-op! :-> :variadic true)
+(sql/register-op! :->)
(sql/register-op! :->>)
(sql/register-op! :#>)
(sql/register-op! :#>>)
diff --git a/src/honey/sql/protocols.cljc b/src/honey/sql/protocols.cljc
new file mode 100644
index 0000000..4cf6081
--- /dev/null
+++ b/src/honey/sql/protocols.cljc
@@ -0,0 +1,8 @@
+;; copyright (c) 2022 sean corfield, all rights reserved
+
+(ns honey.sql.protocols
+ "InlineValue -- a protocol that defines how to inline
+ values; (sqlize x) produces a SQL string for x.")
+
+(defprotocol InlineValue :extend-via-metadata true
+ (sqlize [this] "Render value inline in a SQL string."))
diff --git a/test/honey/ops_test.cljc b/test/honey/ops_test.cljc
new file mode 100644
index 0000000..870b473
--- /dev/null
+++ b/test/honey/ops_test.cljc
@@ -0,0 +1,12 @@
+;; copyright (c) 2023 sean corfield, all rights reserved
+
+(ns honey.ops-test
+ (:refer-clojure :exclude [format])
+ (:require [clojure.test :refer [deftest is]]
+ [honey.sql :as sut]
+ [honey.sql :as sql]))
+
+(deftest issue-454
+ (is (= ["SELECT a - b - c AS x"]
+ (-> {:select [[[:- :a :b :c] :x]]}
+ (sql/format)))))
diff --git a/test/honey/sql/helpers_test.cljc b/test/honey/sql/helpers_test.cljc
index c0d0654..b1b4a75 100644
--- a/test/honey/sql/helpers_test.cljc
+++ b/test/honey/sql/helpers_test.cljc
@@ -1,4 +1,4 @@
-;; copyright (c) 2020-2021 sean corfield, all rights reserved
+;; copyright (c) 2020-2022 sean corfield, all rights reserved
(ns honey.sql.helpers-test
(:refer-clojure :exclude [filter for group-by partition-by set update])
@@ -144,14 +144,14 @@
(deftest select-top-tests
(testing "Basic TOP syntax"
(is (= ["SELECT TOP(?) foo FROM bar ORDER BY quux ASC" 10]
- (sql/format {:select-top [10 :foo] :from :bar :order-by [:quux]})))
+ (sql/format {:select-top [10 :foo] :from :bar :order-by :quux})))
(is (= ["SELECT TOP(?) foo FROM bar ORDER BY quux ASC" 10]
(sql/format (-> (select-top 10 :foo)
(from :bar)
(order-by :quux))))))
(testing "Expanded TOP syntax"
(is (= ["SELECT TOP(?) PERCENT WITH TIES foo, baz FROM bar ORDER BY quux ASC" 10]
- (sql/format {:select-top [[10 :percent :with-ties] :foo :baz] :from :bar :order-by [:quux]})))
+ (sql/format {:select-top [[10 :percent :with-ties] :foo :baz] :from :bar :order-by :quux})))
(is (= ["SELECT TOP(?) PERCENT WITH TIES foo, baz FROM bar ORDER BY quux ASC" 10]
(sql/format (-> (select-top [10 :percent :with-ties] :foo :baz)
(from :bar)
@@ -249,10 +249,19 @@
(sql/format))))))
(deftest test-cast
- (is (= ["SELECT foo, CAST(bar AS integer)"]
+ (is (= ["SELECT foo, CAST(bar AS INTEGER)"]
(sql/format {:select [:foo [[:cast :bar :integer]]]})))
- (is (= ["SELECT foo, CAST(bar AS integer)"]
- (sql/format {:select [:foo [[:cast :bar 'integer]]]}))))
+ (is (= ["SELECT foo, CAST(bar AS INTEGER)"]
+ (sql/format {:select [:foo [[:cast :bar 'integer]]]})))
+ (is (= ["SELECT foo, CAST(bar AS DOUBLE PRECISION)"] ;; Postgres example
+ (sql/format {:select [:foo [[:cast :bar :double-precision]]]})))
+ (is (= ["SELECT \"foo\", CAST(\"bar\" AS INTEGER)"]
+ (sql/format {:select [:foo [[:cast :bar :integer]]]} {:quoted true})))
+ (is (= ["SELECT `foo`, CAST(`bar` AS INTEGER)"]
+ (sql/format {:select [:foo [[:cast :bar :integer]]]} {:dialect :mysql})))
+ (is (= ["SELECT `foo`, CAST(`bar` AS CHAR(10))"]
+ (sql/format {:select [:foo [[:cast :bar [:char 10]]]]} {:dialect :mysql
+ :inline true}))))
(deftest test-value
(is (= ["INSERT INTO foo (bar) VALUES (?)" {:baz "my-val"}]
@@ -313,7 +322,17 @@
(sql/format {:select [:*]
:from [:customers]
:where [:in :id :?ids]}
- {:params {:ids values}})))))))
+ {:params {:ids values}})))
+ (is (= ["SELECT * FROM customers WHERE id IN ($1, $2)" "1" "2"]
+ (sql/format {:select [:*]
+ :from [:customers]
+ :where [:in :id values]}
+ {:numbered true})))
+ (is (= ["SELECT * FROM customers WHERE id IN ($2, $3)" nil "1" "2"]
+ (sql/format {:select [:*]
+ :from [:customers]
+ :where [:in :id :?ids]}
+ {:params {:ids values} :numbered true})))))))
(deftest test-case
(is (= ["SELECT CASE WHEN foo < ? THEN ? WHEN (foo > ?) AND ((foo MOD ?) = ?) THEN foo / ? ELSE ? END FROM bar"
@@ -631,10 +650,20 @@
(is (= (sql/format (-> (alter-table :fruit)
(add-column :id :int [:not nil])))
["ALTER TABLE fruit ADD COLUMN id INT NOT NULL"]))
+ (is (= (sql/format (-> (alter-table :fruit)
+ (add-column :id :int [:not nil])
+ (add-column :a1 :int nil)
+ (add-column :be :text [:not nil])))
+ ["ALTER TABLE fruit ADD COLUMN id INT NOT NULL, ADD COLUMN a1 INT NULL, ADD COLUMN be TEXT NOT NULL"]))
(is (= (sql/format (alter-table :fruit
(add-column :id :int [:not nil])
- (drop-column :ident)))
- ["ALTER TABLE fruit ADD COLUMN id INT NOT NULL, DROP COLUMN ident"])))
+ (drop-column :ident)
+ (drop-column :if-exists :another)))
+ ["ALTER TABLE fruit ADD COLUMN id INT NOT NULL, DROP COLUMN ident, DROP COLUMN IF EXISTS another"]))
+ (is (= (sql/format (alter-table :fruit
+ (drop-column :a :b :if-exists :c :d)
+ (drop-column :if-exists :e)))
+ ["ALTER TABLE fruit DROP COLUMN a, DROP COLUMN b, DROP COLUMN IF EXISTS c, DROP COLUMN d, DROP COLUMN IF EXISTS e"])))
(deftest issue-293-insert-into-data
;; insert into as (and other tests) based on :insert-into
@@ -836,7 +865,7 @@
[[:filter ; two pairs -- alias is on last pair
[:avg :x [:order-by :y [:a :desc]]] {:where [:< :i 10]}
[:sum :q] {:where [:= :x nil]}] :b]
- [[:within-group [:foo :y] {:order-by [:x]}]]]})
+ [[:within-group [:foo :y] {:order-by :x}]]]})
[(str "SELECT COUNT(*) FILTER (WHERE i > ?) AS a,"
" AVG(x, y ORDER BY a DESC) FILTER (WHERE i < ?),"
" SUM(q) FILTER (WHERE x IS NULL) AS b,"
@@ -877,3 +906,10 @@
{:with [[:a]],
:insert-into [[:quux [:x :y]]
{:select [:id], :from [:table]}]}))))
+
+(deftest issue-431
+ (testing "where false should not be ignored"
+ (is (= {:where false}
+ (where false)))
+ (is (= ["SELECT * FROM table WHERE FALSE"]
+ (sql/format {:select [:*] :from [:table] :where false})))))
diff --git a/test/honey/sql/postgres_test.cljc b/test/honey/sql/postgres_test.cljc
index 00218b1..56fc1a5 100644
--- a/test/honey/sql/postgres_test.cljc
+++ b/test/honey/sql/postgres_test.cljc
@@ -93,6 +93,20 @@
(upsert (-> (on-conflict (on-constraint :distributors_pkey))
do-nothing))
sql/format)))
+ (is (= ["INSERT INTO foo (id, data) VALUES (?, ?) ON CONFLICT (id) DO UPDATE SET into = ((STATE(?), MODIFIED(NOW()))) WHERE state = ?" 1 42 "enabled" "disabled"]
+ (sql/format (-> (insert-into :foo)
+ (values [{:id 1 :data 42}])
+ (upsert (-> (on-conflict :id)
+ (do-update-set [:state "enabled"]
+ [:modified [:now]])
+ (where [:= :state "disabled"])))))))
+ (is (= ["INSERT INTO foo (id, data) VALUES (?, ?) ON CONFLICT (id) DO UPDATE SET state = ?, modified = NOW() WHERE state = ?" 1 42 "enabled" "disabled"]
+ (sql/format (-> (insert-into :foo)
+ (values [{:id 1 :data 42}])
+ (upsert (-> (on-conflict :id)
+ (do-update-set {:state "enabled"
+ :modified [:now]})
+ (where [:= :state "disabled"])))))))
(is (= ["INSERT INTO distributors (did, dname) VALUES (?, ?), (?, ?) ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname" 10 "Pinp Design" 11 "Foo Bar Works"]
(sql/format {:insert-into :distributors
:values [{:did 10 :dname "Pinp Design"}
@@ -185,7 +199,7 @@
[:location :point]])
sql/format))))
(testing "create table with foreign key reference"
- (is (= ["CREATE TABLE weather (city VARCHAR(80) REFERENCES CITIES(CITY), temp_lo INT, temp_hi INT, prcp REAL, date DATE)"]
+ (is (= ["CREATE TABLE weather (city VARCHAR(80) REFERENCES cities(city), temp_lo INT, temp_hi INT, prcp REAL, date DATE)"]
(-> (create-table :weather)
(with-columns [[:city [:varchar :80] [:references :cities :city]]
[:temp_lo :int]
@@ -194,7 +208,7 @@
[:date :date]])
sql/format))))
(testing "creating table with table level constraint"
- (is (= ["CREATE TABLE films (code CHAR(5), title VARCHAR(40), did INTEGER, date_prod DATE, kind VARCHAR(10), CONSTRAINT code_title PRIMARY KEY(CODE, TITLE))"]
+ (is (= ["CREATE TABLE films (code CHAR(5), title VARCHAR(40), did INTEGER, date_prod DATE, kind VARCHAR(10), CONSTRAINT code_title PRIMARY KEY(code, title))"]
(-> (create-table :films)
(with-columns [[:code [:char 5]]
[:title [:varchar 40]]
@@ -204,7 +218,7 @@
[[:constraint :code_title] [:primary-key :code :title]]])
sql/format))))
(testing "creating table with column level constraint"
- (is (= ["CREATE TABLE films (code CHAR(5) CONSTRAINT FIRSTKEY PRIMARY KEY, title VARCHAR(40) NOT NULL, did INTEGER NOT NULL, date_prod DATE, kind VARCHAR(10))"]
+ (is (= ["CREATE TABLE films (code CHAR(5) CONSTRAINT firstkey PRIMARY KEY, title VARCHAR(40) NOT NULL, did INTEGER NOT NULL, date_prod DATE, kind VARCHAR(10))"]
(-> (create-table :films)
(with-columns [[:code [:char 5] [:constraint :firstkey] [:primary-key]]
[:title [:varchar 40] [:not nil]]
@@ -213,13 +227,13 @@
[:kind [:varchar 10]]])
sql/format))))
(testing "creating table with columns with default values"
- (is (= ["CREATE TABLE distributors (did INTEGER PRIMARY KEY DEFAULT NEXTVAL('SERIAL'), name VARCHAR(40) NOT NULL)"]
+ (is (= ["CREATE TABLE distributors (did INTEGER PRIMARY KEY DEFAULT NEXTVAL('serial'), name VARCHAR(40) NOT NULL)"]
(-> (create-table :distributors)
(with-columns [[:did :integer [:primary-key] [:default [:nextval "serial"]]]
[:name [:varchar 40] [:not nil]]])
sql/format))))
(testing "creating table with column checks"
- (is (= ["CREATE TABLE products (product_no INTEGER, name TEXT, price NUMERIC CHECK(PRICE > 0), discounted_price NUMERIC, CHECK((discounted_price > 0) AND (price > discounted_price)))"]
+ (is (= ["CREATE TABLE products (product_no INTEGER, name TEXT, price NUMERIC CHECK(price > 0), discounted_price NUMERIC, CHECK((discounted_price > 0) AND (price > discounted_price)))"]
(-> (create-table :products)
(with-columns [[:product_no :integer]
[:name :text]
@@ -228,7 +242,39 @@
[[:check [:and [:> :discounted_price 0] [:> :price :discounted_price]]]]])
sql/format)))))
+(deftest references-issue-386
+ (is (= ["CREATE TABLE IF NOT EXISTS user (id VARCHAR(255) NOT NULL PRIMARY KEY, company_id INT NOT NULL, name VARCHAR(255) NOT NULL, password VARCHAR(255) NOT NULL, created_time DATETIME DEFAULT CURRENT_TIMESTAMP, updated_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY(company_id) REFERENCES company(id))"]
+ (-> {:create-table [:user :if-not-exists]
+ :with-columns
+ [[:id [:varchar 255] [:not nil] [:primary-key]]
+ [:company-id :int [:not nil]]
+ [:name [:varchar 255] [:not nil]]
+ [:password [:varchar 255] [:not nil]]
+ [:created-time :datetime [:default :CURRENT_TIMESTAMP]]
+ [:updated-time :datetime [:default :CURRENT_TIMESTAMP]
+ :on :update :CURRENT_TIMESTAMP]
+ [[:foreign-key :company-id] [:references :company :id]]]}
+ (sql/format)))))
+
+(deftest create-table-issue-437
+ (is (= ["CREATE TABLE bar (did UUID DEFAULT GEN_RANDOM_UUID(), foo_id VARCHAR NOT NULL, PRIMARY KEY(did, foo_id), FOREIGN KEY(foo_id) REFERENCES foo(id) ON DELETE CASCADE)"]
+ (-> (create-table :bar)
+ (with-columns
+ [[:did :uuid [:default [:gen_random_uuid]]]
+ [:foo-id :varchar [:not nil]]
+ [[:primary-key :did :foo-id]]
+ [[:foreign-key :foo-id]
+ [:references :foo :id]
+ :on-delete :cascade]])
+ (sql/format)))))
+
(deftest over-test
+ (testing "simple window statement"
+ (is (= ["SELECT AVG(salary) OVER w FROM employee WINDOW w AS (PARTITION BY department ORDER BY salary ASC)"]
+ (sql/format {:select [[[:over [[:avg :salary] :w]]]]
+ :from :employee
+ :window [:w {:partition-by :department
+ :order-by :salary}]}))))
(testing "window function over on select statemt"
(is (= ["SELECT id, AVG(salary) OVER (PARTITION BY department ORDER BY designation ASC) AS Average, MAX(salary) OVER w AS MaxSalary FROM employee WINDOW w AS (PARTITION BY department)"]
;; honeysql treats over as a function:
@@ -362,6 +408,18 @@
(modifiers :distinct-on :a :b)
(sql/format :quoting :ansi))))))
+(deftest select-agg-order-by-test
+ (testing "single expression in order by"
+ (is (= ["SELECT ARRAY_AGG(a ORDER BY x) FROM products"])
+ (sql/format
+ {:select [[[:array_agg [:order-by :a :x]]]]
+ :from :products})))
+ (testing "multiple expressions in order by"
+ (is (= ["SELECT ARRAY_AGG(a ORDER BY x ASC, y DESC, z ASC) FROM products"]
+ (sql/format
+ {:select [[[:array_agg [:order-by :a [:x :asc] [:y :desc] :z]]]]
+ :from :products})))))
+
(deftest create-extension-test
;; previously, honeysql required :allow-dashed-names? true
(testing "create extension"
@@ -379,3 +437,21 @@
(is (= ["DROP EXTENSION \"uuid-ossp\""]
(-> (drop-extension :uuid-ossp)
(sql/format {:quoted true}))))))
+
+(deftest issue-453-constraint
+ (testing "standalone constraint"
+ (is (= ["CREATE TABLE bar (a INTEGER, b INTEGER, CONSTRAINT foo_natural_key UNIQUE (a, b))"]
+ (-> {:create-table [:bar]
+ :with-columns
+ [[:a :integer]
+ [:b :integer]
+ [[:constraint :foo_natural_key] :unique [:composite :a :b]]]}
+ (sql/format)))))
+ (testing "inline constraint"
+ (is (= ["CREATE TABLE foo (a INTEGER CONSTRAINT a_pos CHECK(a > 0), b INTEGER, CONSTRAINT a_bigger CHECK(b < a))"]
+ (-> '{create-table foo
+ with-columns
+ ((a integer (constraint a_pos) (check (> a 0)))
+ (b integer)
+ ((constraint a_bigger) (check (< b a))))}
+ (sql/format))))))
diff --git a/test/honey/sql_test.cljc b/test/honey/sql_test.cljc
index efefb8b..80fa6bb 100644
--- a/test/honey/sql_test.cljc
+++ b/test/honey/sql_test.cljc
@@ -68,6 +68,14 @@
(is (= ["INTERVAL ? DAYS" 30]
(sut/format-expr [:interval 30 :days]))))
+(deftest issue-486-interval
+ (is (= ["INTERVAL '30 Days'"]
+ (sut/format-expr [:interval "30 Days"]))))
+
+(deftest issue-455-null
+ (is (= ["WHERE (abc + ?) IS NULL" "abc"]
+ (sut/format {:where [:= [:+ :abc "abc"] nil]}))))
+
(deftest where-test
(is (= ["WHERE id = ?" 1]
(#'sut/format-on-expr :where [:= :id 1]))))
@@ -79,21 +87,62 @@
(sut/format {:select [:*] :from [:table] :where (sut/map= {:id 1})} {:quoted true})))
(is (= ["SELECT \"t\".* FROM \"table\" AS \"t\" WHERE \"id\" = ?" 1]
(sut/format {:select [:t.*] :from [[:table :t]] :where [:= :id 1]} {:quoted true})))
+ (is (= ["SELECT * FROM \"table\" GROUP BY \"foo\""]
+ (sut/format {:select [:*] :from [:table] :group-by :foo} {:quoted true})))
(is (= ["SELECT * FROM \"table\" GROUP BY \"foo\", \"bar\""]
(sut/format {:select [:*] :from [:table] :group-by [:foo :bar]} {:quoted true})))
(is (= ["SELECT * FROM \"table\" GROUP BY DATE(\"bar\")"]
(sut/format {:select [:*] :from [:table] :group-by [[:date :bar]]} {:quoted true})))
+ (is (= ["SELECT * FROM \"table\" ORDER BY \"foo\" ASC"]
+ (sut/format {:select [:*] :from [:table] :order-by :foo} {:quoted true})))
(is (= ["SELECT * FROM \"table\" ORDER BY \"foo\" DESC, \"bar\" ASC"]
(sut/format {:select [:*] :from [:table] :order-by [[:foo :desc] :bar]} {:quoted true})))
(is (= ["SELECT * FROM \"table\" ORDER BY DATE(\"expiry\") DESC, \"bar\" ASC"]
(sut/format {:select [:*] :from [:table] :order-by [[[:date :expiry] :desc] :bar]} {:quoted true})))
(is (= ["SELECT * FROM \"table\" WHERE DATE_ADD(\"expiry\", INTERVAL ? DAYS) < NOW()" 30]
(sut/format {:select [:*] :from [:table] :where [:< [:date_add :expiry [:interval 30 :days]] [:now]]} {:quoted true})))
+ (is (= ["SELECT * FROM \"table\" WHERE DATE_ADD(\"expiry\", INTERVAL '30 Days') < NOW()"]
+ (sut/format {:select [:*] :from [:table] :where [:< [:date_add :expiry [:interval "30 Days"]] [:now]]} {:quoted true})))
(is (= ["SELECT * FROM `table` WHERE `id` = ?" 1]
(sut/format {:select [:*] :from [:table] :where [:= :id 1]} {:dialect :mysql})))
(is (= ["SELECT * FROM \"table\" WHERE \"id\" IN (?, ?, ?, ?)" 1 2 3 4]
(sut/format {:select [:*] :from [:table] :where [:in :id [1 2 3 4]]} {:quoted true}))))
+(deftest general-numbered-tests
+ (is (= ["SELECT * FROM \"table\" WHERE \"id\" = $1" 1]
+ (sut/format {:select [:*] :from [:table] :where [:= :id 1]}
+ {:quoted true :numbered true})))
+ (is (= ["SELECT * FROM \"table\" WHERE \"id\" = $1" 1]
+ (sut/format {:select [:*] :from [:table] :where (sut/map= {:id 1})}
+ {:quoted true :numbered true})))
+ (is (= ["SELECT \"t\".* FROM \"table\" AS \"t\" WHERE \"id\" = $1" 1]
+ (sut/format {:select [:t.*] :from [[:table :t]] :where [:= :id 1]}
+ {:quoted true :numbered true})))
+ (is (= ["SELECT * FROM \"table\" GROUP BY \"foo\", \"bar\""]
+ (sut/format {:select [:*] :from [:table] :group-by [:foo :bar]}
+ {:quoted true :numbered true})))
+ (is (= ["SELECT * FROM \"table\" GROUP BY DATE(\"bar\")"]
+ (sut/format {:select [:*] :from [:table] :group-by [[:date :bar]]}
+ {:quoted true :numbered true})))
+ (is (= ["SELECT * FROM \"table\" ORDER BY \"foo\" DESC, \"bar\" ASC"]
+ (sut/format {:select [:*] :from [:table] :order-by [[:foo :desc] :bar]}
+ {:quoted true :numbered true})))
+ (is (= ["SELECT * FROM \"table\" ORDER BY DATE(\"expiry\") DESC, \"bar\" ASC"]
+ (sut/format {:select [:*] :from [:table] :order-by [[[:date :expiry] :desc] :bar]}
+ {:quoted true :numbered true})))
+ (is (= ["SELECT * FROM \"table\" WHERE DATE_ADD(\"expiry\", INTERVAL $1 DAYS) < NOW()" 30]
+ (sut/format {:select [:*] :from [:table] :where [:< [:date_add :expiry [:interval 30 :days]] [:now]]}
+ {:quoted true :numbered true})))
+ (is (= ["SELECT * FROM \"table\" WHERE DATE_ADD(\"expiry\", INTERVAL '30 Days') < NOW()"]
+ (sut/format {:select [:*] :from [:table] :where [:< [:date_add :expiry [:interval "30 Days"]] [:now]]}
+ {:quoted true :numbered true})))
+ (is (= ["SELECT * FROM `table` WHERE `id` = $1" 1]
+ (sut/format {:select [:*] :from [:table] :where [:= :id 1]}
+ {:dialect :mysql :numbered true})))
+ (is (= ["SELECT * FROM \"table\" WHERE \"id\" IN ($1, $2, $3, $4)" 1 2 3 4]
+ (sut/format {:select [:*] :from [:table] :where [:in :id [1 2 3 4]]}
+ {:quoted true :numbered true}))))
+
;; issue-based tests
(deftest subquery-alias-263
@@ -124,6 +173,12 @@
(deftest test-cte
(is (= (format {:with [[:query {:select [:foo] :from [:bar]}]]})
["WITH query AS (SELECT foo FROM bar)"]))
+ (is (= (format {:with [[:query {:select [:foo] :from [:bar]} :materialized]]})
+ ["WITH query AS MATERIALIZED (SELECT foo FROM bar)"]))
+ (is (= (format {:with [[:query {:select [:foo] :from [:bar]} :not-materialized]]})
+ ["WITH query AS NOT MATERIALIZED (SELECT foo FROM bar)"]))
+ (is (= (format {:with [[:query {:select [:foo] :from [:bar]} :unknown]]})
+ ["WITH query AS (SELECT foo FROM bar)"]))
(is (= (format {:with [[:query1 {:select [:foo] :from [:bar]}]
[:query2 {:select [:bar] :from [:quux]}]]
:select [:query1.id :query2.name]
@@ -138,7 +193,30 @@
{:values [[1 2] [4 5 6]]}]]
:select [:*]
:from [:static]})
- ["WITH static (a, b, c) AS (VALUES (?, ?, NULL), (?, ?, ?)) SELECT * FROM static" 1 2 4 5 6])))
+ ["WITH static (a, b, c) AS (VALUES (?, ?, NULL), (?, ?, ?)) SELECT * FROM static" 1 2 4 5 6]))
+ (testing "When the expression passed to WITH clause is a string or `ident?` the syntax of WITH clause is `with expr AS ident`"
+ (is (= (format
+ {:with [[:ts_upper_bound "2019-08-01 15:23:00"]]
+ :select [:*]
+ :from [:hits]
+ :where [:= :EventDate :ts_upper_bound]})
+ ["WITH ? AS ts_upper_bound SELECT * FROM hits WHERE EventDate = ts_upper_bound" "2019-08-01 15:23:00"]))
+ (is (= (format
+ {:with [[:ts_upper_bound :2019-08-01]]
+ :select [:*]
+ :from [:hits]
+ :where [:= :EventDate :ts_upper_bound]})
+ ["WITH 2019_08_01 AS ts_upper_bound SELECT * FROM hits WHERE EventDate = ts_upper_bound"])))
+ (testing "Mixing the syntax of WITH in the resulting clause"
+ (is (= (format
+ {:with [[:ts_upper_bound "2019-08-01 15:23:00"]
+ [:stuff {:select [:*]
+ :from [:songs]}]]
+ :select [:*]
+ :from [:hits :stuff]
+ :where [:= :EventDate :ts_upper_bound]})
+ ["WITH ? AS ts_upper_bound, stuff AS (SELECT * FROM songs) SELECT * FROM hits, stuff WHERE EventDate = ts_upper_bound"
+ "2019-08-01 15:23:00"]))))
(deftest insert-into
(is (= (format {:insert-into :foo})
@@ -213,7 +291,12 @@
:columns [:baz]
:values [[[:array :?vals]]]}
{:params {:vals [1 2 3 4]}})
- ["INSERT INTO foo (baz) VALUES (ARRAY[?, ?, ?, ?])" 1 2 3 4])))
+ ["INSERT INTO foo (baz) VALUES (ARRAY[?, ?, ?, ?])" 1 2 3 4]))
+ (testing "typed array"
+ (is (= (format {:select [[[:array [] :integer]]]})
+ ["SELECT ARRAY[]::INTEGER[]"]))
+ (is (= (format {:select [[[:array [1 2] :text]]]})
+ ["SELECT ARRAY[?, ?]::TEXT[]" 1 2]))))
(deftest union-test
;; UNION and INTERSECT subexpressions should not be parenthesized.
@@ -488,8 +571,11 @@
(format)))))
(deftest truncate-test
- (is (= ["TRUNCATE `foo`"]
+ (is (= ["TRUNCATE TABLE `foo`"]
(-> {:truncate :foo}
+ (format {:dialect :mysql}))))
+ (is (= ["TRUNCATE TABLE `foo` CONTINUE IDENTITY"]
+ (-> {:truncate [:foo :continue :identity]}
(format {:dialect :mysql})))))
(deftest inlined-values-are-stringified-correctly
@@ -731,7 +817,38 @@ ORDER BY id = ? DESC
:values [{:name name
:enabled enabled}]})))))
+(deftest issue-425-default-values-test
+ (testing "default values"
+ (is (= ["INSERT INTO table (a, b, c) DEFAULT VALUES"]
+ (format {:insert-into [:table [:a :b :c]] :values :default}))))
+ (testing "values with default row"
+ (is (= ["INSERT INTO table (a, b, c) VALUES (1, 2, 3), DEFAULT, (4, 5, 6)"]
+ (format {:insert-into [:table [:a :b :c]]
+ :values [[1 2 3] :default [4 5 6]]}
+ {:inline true}))))
+ (testing "values with default column"
+ (is (= ["INSERT INTO table (a, b, c) VALUES (1, DEFAULT, 3), DEFAULT"]
+ (format {:insert-into [:table [:a :b :c]]
+ :values [[1 [:default] 3] :default]}
+ {:inline true}))))
+ (testing "map values with default row, no columns"
+ (is (= ["INSERT INTO table (a, b, c) VALUES (1, 2, 3), DEFAULT, (4, 5, 6)"]
+ (format {:insert-into :table
+ :values [{:a 1 :b 2 :c 3} :default {:a 4 :b 5 :c 6}]}
+ {:inline true}))))
+ (testing "map values with default column, no columns"
+ (is (= ["INSERT INTO table (a, b, c) VALUES (1, DEFAULT, 3), DEFAULT"]
+ (format {:insert-into :table
+ :values [{:a 1 :b [:default] :c 3} :default]}
+ {:inline true}))))
+ (testing "empty values"
+ (is (= ["INSERT INTO table (a, b, c) VALUES ()"]
+ (format {:insert-into [:table [:a :b :c]]
+ :values []})))))
+
(deftest issue-316-test
+ ;; this is a pretty naive test -- there are other tricks to perform injection
+ ;; that are not detected by HoneySQL and you should generally use :quoted true
(testing "SQL injection via keyword is detected"
(let [sort-column "foo; select * from users"]
(try
@@ -789,14 +906,34 @@ ORDER BY id = ? DESC
{:params {:y [nil]}})))
(is (= ["WHERE x IN (?)" nil]
(format {:where [:in :x :?y]}
- {:params {:y [nil]} :checking :basic}))))
+ {:params {:y [nil]} :checking :basic})))
+ (is (= ["WHERE x IN ($2)" nil nil]
+ (format {:where [:in :x :?y]}
+ {:params {:y [nil]} :numbered true})))
+ (is (= ["WHERE x IN ($2)" nil nil]
+ (format {:where [:in :x :?y]}
+ {:params {:y [nil]} :checking :basic :numbered true}))))
(testing "IN NULL is flagged in strict mode"
(is (thrown-with-msg? ExceptionInfo #"does not match"
(format {:where [:in :x [nil]]}
{:checking :strict})))
(is (thrown-with-msg? ExceptionInfo #"does not match"
(format {:where [:in :x :?y]}
- {:params {:y [nil]} :checking :strict})))))
+ {:params {:y [nil]} :checking :strict}))))
+ (testing "empty WHERE clauses ignored with none"
+ (is (= ["DELETE FROM foo"]
+ (format {:delete-from :foo})))
+ (is (= ["DELETE foo"]
+ (format {:delete :foo})))
+ (is (= ["UPDATE foo SET x = ?" 1]
+ (format {:update :foo :set {:x 1}}))))
+ (testing "empty WHERE clauses flagged in basic mode"
+ (is (thrown-with-msg? ExceptionInfo #"without a non-empty"
+ (format {:delete-from :foo} {:checking :basic})))
+ (is (thrown-with-msg? ExceptionInfo #"without a non-empty"
+ (format {:delete :foo} {:checking :basic})))
+ (is (thrown-with-msg? ExceptionInfo #"without a non-empty"
+ (format {:update :foo :set {:x 1}} {:checking :basic})))))
(deftest quoting-:%-syntax
(testing "quoting of expressions in functions shouldn't depend on syntax"
@@ -916,3 +1053,154 @@ ORDER BY id = ? DESC
(is (= ["SELECT `A\"B`"] (sut/format {:select (keyword "A\"B")} {:dialect :mysql})))
(is (= ["SELECT `A``B`"] (sut/format {:select (keyword "A`B")} {:dialect :mysql})))
(is (= ["SELECT \"A\"\"B\""] (sut/format {:select (keyword "A\"B")} {:dialect :oracle}))))
+
+(deftest issue-421-mysql-replace-into
+ (is (= ["INSERT INTO table VALUES (?, ?, ?)" 1 2 3]
+ (sut/format {:insert-into :table :values [[1 2 3]]})))
+ (is (= ["REPLACE INTO table VALUES (?, ?, ?)" 1 2 3]
+ (sut/format {:replace-into :table :values [[1 2 3]]}
+ {:dialect :mysql :quoted false}))))
+
+(deftest issue-422-quoting
+ ;; default quote if strange entity:
+ (is (= ["SELECT A, \"B C\""] (sut/format {:select [:A (keyword "B C")]})))
+ ;; default don't quote normal entity:
+ (is (= ["SELECT A, B_C"] (sut/format {:select [:A (keyword "B_C")]})))
+ ;; quote all entities when quoting enabled:
+ (is (= ["SELECT \"A\", \"B C\""] (sut/format {:select [:A (keyword "B C")]}
+ {:quoted true})))
+ ;; don't quote if quoting disabled (illegal SQL):
+ (is (= ["SELECT A, B C"] (sut/format {:select [:A (keyword "B C")]}
+ {:quoted false}))))
+
+(deftest issue-434-case-quoting
+ (is (= ["SELECT ARRAY (SELECT \"oid\" FROM \"pg_proc\" WHERE \"proname\" LIKE 'bytea%')"]
+ (sut/format {:select [[[:'ARRAY {:select :oid :from :pg_proc :where [:like :proname [:inline "bytea%"]]}]]]} :quoted true))))
+
+(deftest issue-456-format-expr
+ (is (= ["`x` + ?" 1]
+ (sut/format [:+ :x 1] {:dialect :mysql}))))
+
+(deftest issue-459-variadic-ops
+ (sut/register-op! :op)
+ (is (= ["SELECT a"] ; not unary!
+ (sut/format {:select [[[:op :a]]]})))
+ (is (= ["SELECT a OP b"]
+ (sut/format {:select [[[:op :a :b]]]})))
+ (is (= ["SELECT a OP b OP c"]
+ (sut/format {:select [[[:op :a :b :c]]]}))))
+
+(deftest issue-461-unary-ops
+ (is (= ["SELECT TRUE"]
+ (sut/format {:select [[[:and true]]]})))
+ (is (= ["SELECT TRUE"]
+ (sut/format {:select [[[:or true]]]})))
+ (is (= ["SELECT ?" 1]
+ (sut/format {:select [[[:* 1]]]})))
+ (is (= ["SELECT TRUE AND a AND b"]
+ (sut/format {:select [[[:and true :a :b]]]})))
+ (is (= ["SELECT TRUE OR a OR b"]
+ (sut/format {:select [[[:or true :a :b]]]})))
+ (is (= ["SELECT ? * ? * ?" 1 2 3]
+ (sut/format {:select [[[:* 1 2 3]]]})))
+ ;; but these three genuinely are unary:
+ (is (= ["SELECT + ?" 1]
+ (sut/format {:select [[[:+ 1]]]})))
+ (is (= ["SELECT - ?" 1]
+ (sut/format {:select [[[:- 1]]]})))
+ (is (= ["SELECT ~ ?" 1] ; bitwise negation
+ (sut/format {:select [[[(keyword "~") 1]]]})))
+ ;; and can still be used as variadic:
+ (is (= ["SELECT ? + ?" 1 2]
+ (sut/format {:select [[[:+ 1 2]]]})))
+ (is (= ["SELECT ? - ?" 1 2]
+ (sut/format {:select [[[:- 1 2]]]})))
+ (is (= ["SELECT ? ~ ?" "a" "b"] ; regex op
+ (sut/format {:select [[[(keyword "~") "a" "b"]]]}))))
+
+(deftest issue-471-interspersed-kws
+ (testing "overlay"
+ (is (= ["SELECT OVERLAY(foo PLACING ? FROM ? FOR ?)"
+ "bar" 3 4]
+ (sut/format {:select [[[:overlay :foo :!placing "bar" :!from 3 :!for 4]]]}))))
+ (testing "position"
+ (is (= ["SELECT POSITION(? IN bar)" "foo"]
+ (sut/format {:select [[[:position "foo" :!in :bar]]]}))))
+ (testing "trim"
+ (is (= ["SELECT TRIM(LEADING FROM bar)"]
+ (sut/format {:select [[[:trim :!leading :!from :bar]]]})))
+ (is (= ["SELECT TRIM(LEADING FROM bar)"]
+ (sut/format {:select [[[:trim :!leading-from :bar]]]}))))
+ (testing "extract"
+ (is (= ["SELECT EXTRACT(CENTURY FROM TIMESTAMP '2000-12-16 12:21:13')"]
+ (sut/format {:select [[[:extract :!century :!from
+ :!timestamp [:inline "2000-12-16 12:21:13"]]]]}))))
+ (testing "xmlelement"
+ (is (= ["SELECT XMLELEMENT(NAME \"foo$bar\", XMLATTRIBUTES('xyz' AS \"a&b\"))"]
+ (sut/format {:select [[[:xmlelement :!name :foo$bar
+ [:xmlattributes [:inline "xyz"] :!as :a&b]]]]})))))
+
+(deftest issue-474-dot-selection
+ (testing "basic dot selection"
+ (is (= ["SELECT a.b, c.d, a.d.x"]
+ (let [t :a c :d]
+ (sut/format {:select [[[:. t :b]] [[:. :c c]] [[:. t c :x]]]}))))
+ (is (= ["SELECT [a].[b], [c].[d], [a].[d].[x]"]
+ (let [t :a c :d]
+ (sut/format {:select [[[:. t :b]] [[:. :c c]] [[:. t c :x]]]}
+ {:dialect :sqlserver})))))
+ (testing "basic field selection from composite"
+ (is (= ["SELECT (v).*, (w).x, (Y(z)).*"]
+ (sut/format '{select (((. (nest v) *))
+ ((. (nest w) x))
+ ((. (nest (y z)) *)))})))
+ (is (= ["SELECT (`v`).*, (`w`).`x`, (Y(`z`)).*"]
+ (sut/format '{select (((. (nest v) *))
+ ((. (nest w) x))
+ ((. (nest (y z)) *)))}
+ {:dialect :mysql})))))
+
+(deftest issue-476-raw
+ (testing "single argument :raw"
+ (is (= ["@foo := 42"]
+ (sut/format [:raw "@foo := 42"])))
+ (is (= ["@foo := 42"]
+ (sut/format [:raw ["@foo := 42"]])))
+ (is (= ["@foo := 42"]
+ (sut/format [:raw ["@foo := " 42]])))
+ (is (= ["@foo := (?)" 42]
+ (sut/format [:raw ["@foo := " [42]]])))
+ (is (= ["@foo := MYFUNC(?)" 42]
+ (sut/format [:raw ["@foo := " [:myfunc 42]]]))))
+ (testing "multi-argument :raw"
+ (is (= ["@foo := 42"]
+ (sut/format [:raw "@foo := " 42])))
+ (is (= ["@foo := (?)" 42]
+ (sut/format [:raw "@foo := " [42]])))
+ (is (= ["@foo := MYFUNC(?)" 42]
+ (sut/format [:raw "@foo := " [:myfunc 42]])))))
+
+(deftest issue-483-join
+ (testing "single nested join"
+ (is (= ["SELECT * FROM tbl1 LEFT JOIN (tbl2 INNER JOIN tbl3 USING (common_column)) ON (tbl2.col2 = tbl1.col2) AND (tbl3.col3 = tbl1.col3)"]
+ (-> {:select :*
+ :from :tbl1
+ :left-join [[[:join :tbl2 {:join [:tbl3 [:using [:common_column]]]}]]
+ [:and
+ [:= :tbl2.col2 :tbl1.col2]
+ [:= :tbl3.col3 :tbl1.col3]]]}
+ (sut/format)))))
+ (testing "multiple nested join"
+ (is (= ["SELECT * FROM tbl1 LEFT JOIN (tbl2 INNER JOIN tbl3 USING (common_column) RIGHT JOIN tbl4 USING (id)) ON (tbl2.col2 = tbl1.col2) AND (tbl3.col3 = tbl1.col3)"]
+ (-> {:select :*
+ :from :tbl1
+ :left-join [[[:join :tbl2
+ {:join [:tbl3 [:using [:common_column]]]}
+ {:right-join [:tbl4 [:using :id]]}]]
+ [:and
+ [:= :tbl2.col2 :tbl1.col2]
+ [:= :tbl3.col3 :tbl1.col3]]]}
+ (sut/format)))))
+ (testing "special syntax example"
+ (is (= ["INNER JOIN (tbl1 LEFT JOIN tbl2 USING (id))"]
+ (sut/format {:join [[[:join :tbl1 {:left-join [:tbl2 [:using :id]]}]]]})))))
diff --git a/test/honey/union_test.cljc b/test/honey/union_test.cljc
new file mode 100644
index 0000000..695e9ec
--- /dev/null
+++ b/test/honey/union_test.cljc
@@ -0,0 +1,51 @@
+;; copyright (c) 2023 sean corfield, all rights reserved
+
+(ns honey.union-test
+ (:refer-clojure :exclude [format])
+ (:require [clojure.test :refer [deftest is]]
+ [honey.sql :as sut]))
+
+(deftest issue-451
+ (is (= [(str "SELECT ids.id AS id"
+ " FROM ((SELECT dimension.human_readable_field_id AS id"
+ " FROM dimension AS dimension"
+ " WHERE (dimension.field_id = ?) AND (dimension.human_readable_field_id IS NOT NULL)"
+ " LIMIT ?)"
+ " UNION"
+ " (SELECT dest.id AS id"
+ " FROM field AS source"
+ " LEFT JOIN table AS table ON source.table_id = table.id"
+ " LEFT JOIN field AS dest ON dest.table_id = table.id"
+ " WHERE (source.id = ?) AND (source.semantic_type IN (?)) AND (dest.semantic_type IN (?))"
+ " LIMIT ?)) AS ids"
+ " LIMIT ?")
+ 1
+ 1
+ 1
+ "type/PK"
+ "type/Name"
+ 1
+ 1]
+ (-> {:select [[:ids.id :id]]
+ :from [[{:union
+ [{:nest
+ {:select [[:dimension.human_readable_field_id :id]]
+ :from [[:dimension :dimension]]
+ :where [:and
+ [:= :dimension.field_id 1]
+ [:not= :dimension.human_readable_field_id nil]]
+ :limit 1}}
+ {:nest
+ {:select [[:dest.id :id]]
+ :from [[:field :source]]
+ :left-join [[:table :table] [:= :source.table_id :table.id] [:field :dest] [:= :dest.table_id :table.id]]
+ :where [:and
+ [:= :source.id 1]
+ [:in :source.semantic_type #{"type/PK"}]
+ [:in :dest.semantic_type #{"type/Name"}]]
+ :limit 1}}]}
+ :ids]]
+ :limit 1}
+ (sut/format))))
+
+)
Debdiff
[The following lists of changes regard files as different if they have different names, permissions or owners.]
Files in second set of .debs but not in first
-rw-r--r-- root/root /usr/share/java/honeysql-2.4.1026.jar -rw-r--r-- root/root /usr/share/maven-repo/com/github/seancorfield/honeysql/2.4.1026/honeysql-2.4.1026.pom lrwxrwxrwx root/root /usr/share/java/honeysql.jar -> honeysql-2.4.1026.jar lrwxrwxrwx root/root /usr/share/maven-repo/com/github/seancorfield/honeysql/2.4.1026/honeysql-2.4.1026.jar -> ../../../../../../java/honeysql-2.4.1026.jar lrwxrwxrwx root/root /usr/share/maven-repo/com/github/seancorfield/honeysql/debian/honeysql-debian.jar -> ../../../../../../java/honeysql-2.4.1026.jar
Files in first set of .debs but not in second
-rw-r--r-- root/root /usr/share/java/honeysql-2.4.962+really2.3.911.jar -rw-r--r-- root/root /usr/share/maven-repo/com/github/seancorfield/honeysql/2.4.962+really2.3.911/honeysql-2.4.962+really2.3.911.pom lrwxrwxrwx root/root /usr/share/java/honeysql.jar -> honeysql-2.4.962+really2.3.911.jar lrwxrwxrwx root/root /usr/share/maven-repo/com/github/seancorfield/honeysql/2.4.962+really2.3.911/honeysql-2.4.962+really2.3.911.jar -> ../../../../../../java/honeysql-2.4.962+really2.3.911.jar lrwxrwxrwx root/root /usr/share/maven-repo/com/github/seancorfield/honeysql/debian/honeysql-debian.jar -> ../../../../../../java/honeysql-2.4.962+really2.3.911.jar
No differences were encountered in the control files