Back
columns (clj)
(source)function
(columns & cols)
To be used with `insert-into` to specify the list of
column names for the insert operation. Accepts any number
of column names:
(-> (insert-into :foo)
(columns :a :b :c)
(values [[1 2 3] [2 4 6]]))
Produces:
INSERT INTO foo (a, b, c) VALUES (?, ?, ?), (?, ?, ?)
Parameters: 1 2 3 2 4 6
Examples
honeysql
(ns honey.sql-test
(:refer-clojure :exclude [format])
(:require [clojure.string :as str]
[clojure.test :refer [deftest is testing]]
[honey.sql :as sut :refer [format]]
[honey.sql.helpers :as h])
#?(:clj (:import (clojure.lang ExceptionInfo))))
(deftest values-alias
(is (= ["SELECT vals.a FROM (VALUES (?, ?, ?)) AS vals (a, b, c)" 1 2 3]
(format {:select [:vals.a]
:from [[{:values [[1 2 3]]} [:vals {:columns [:a :b :c]}]]]}))))
(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]
:from [:query1 :query2]})
["WITH query1 AS (SELECT foo FROM bar), query2 AS (SELECT bar FROM quux) SELECT query1.id, query2.name FROM query1, query2"]))
(is (= (format {:with-recursive [[:query {:select [:foo] :from [:bar]}]]})
["WITH RECURSIVE query AS (SELECT foo FROM bar)"]))
(is (= (format {:with [[[:static {:columns [:a :b :c]}] {:values [[1 2 3] [4 5]]}]]})
["WITH static (a, b, c) AS (VALUES (?, ?, ?), (?, ?, NULL))" 1 2 3 4 5]))
(is (= (format
{:with [[[:static {:columns [:a :b :c]}]
{: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]))
(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-namespaced
;; un-namespaced: works as expected:
(is (= (format {:insert-into :foo :values [{:foo/id 1}]})
["INSERT INTO foo (id) VALUES (?)" 1]))
(is (= (format {:insert-into :foo :columns [:foo/id] :values [[2]]})
["INSERT INTO foo (id) VALUES (?)" 2]))
(is (= (format {:insert-into :foo :values [{:foo/id 1}]}
{:namespace-as-table? true})
["INSERT INTO foo (id) VALUES (?)" 1]))
(is (= (format {:insert-into :foo :columns [:foo/id] :values [[2]]}
{:namespace-as-table? true})
["INSERT INTO foo (id) VALUES (?)" 2])))
(deftest insert-into-functions
;; needs [[:raw ..]] because it's the columns case:
(is (= (format {:insert-into [[[:raw "My-Table Name"]] {:select [:bar] :from [:baz]}]})
["INSERT INTO My-Table Name SELECT bar FROM baz"]))
;; this variant only needs [:raw ..]
(is (= (format {:insert-into [[:raw "My-Table Name"]] :values [{:foo/id 1}]})
["INSERT INTO My-Table Name (id) VALUES (?)" 1]))
(is (= (format {:insert-into [:foo :bar] :values [{:foo/id 1}]})
["INSERT INTO foo AS bar (id) VALUES (?)" 1])))
(deftest array-test
(is (= (format {:insert-into :foo
:columns [:baz]
:values [[[:array [1 2 3 4]]]]})
["INSERT INTO foo (baz) VALUES (ARRAY[?, ?, ?, ?])" 1 2 3 4]))
(is (= (format {:insert-into :foo
:columns [:baz]
:values [[[:array ["one" "two" "three"]]]]})
["INSERT INTO foo (baz) VALUES (ARRAY[?, ?, ?])" "one" "two" "three"]))
#_ ;; requested feature -- does not work yet
(is (= (format {:insert-into :foo
:columns [:baz]
:values [[[:array :?vals]]]}
{:params {:vals [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])))
(testing "array subquery"
(is (= (format {:select [[[:array {:select [:foo] :from [:bar]}]]]})
["SELECT ARRAY(SELECT foo FROM bar)"]))
(is (= (format {:select [[[:array {:select ^{:as :struct} [:foo :bar] :from [:bar]}]]]})
["SELECT ARRAY(SELECT AS STRUCT foo, bar FROM bar)"]))
;; documented subquery workaround:
(is (= (format {:select [[[:'ARRAY {:select [:foo] :from [:bar]}]]]})
["SELECT ARRAY (SELECT foo FROM bar)"]))))
(testing "union complex values"
(is (= (format {:union [{:select [:foo] :from [:bar1]}
{:select [:foo] :from [:bar2]}]
:with [[[:bar {:columns [:spam :eggs]}]
{:values [[1 2] [3 4] [5 6]]}]]})
["WITH bar (spam, eggs) AS (VALUES (?, ?), (?, ?), (?, ?)) SELECT foo FROM bar1 UNION SELECT foo FROM bar2"
1 2 3 4 5 6]))))
(deftest union-with-cte
(is (= (format {:union [{:select [:foo] :from [:bar1]}
{:select [:foo] :from [:bar2]}]
:with [[[:bar {:columns [:spam :eggs]}]
{:values [[1 2] [3 4] [5 6]]}]]})
["WITH bar (spam, eggs) AS (VALUES (?, ?), (?, ?), (?, ?)) SELECT foo FROM bar1 UNION SELECT foo FROM bar2" 1 2 3 4 5 6])))
(deftest union-all-with-cte
(is (= (format {:union-all [{:select [:foo] :from [:bar1]}
{:select [:foo] :from [:bar2]}]
:with [[[:bar {:columns [:spam :eggs]}]
{:values [[1 2] [3 4] [5 6]]}]]})
["WITH bar (spam, eggs) AS (VALUES (?, ?), (?, ?), (?, ?)) SELECT foo FROM bar1 UNION ALL SELECT foo FROM bar2" 1 2 3 4 5 6])))
(deftest parameterizer-none
(testing "array parameter"
(is (= (format {:insert-into :foo
:columns [:baz]
:values [[[:array [1 2 3 4]]]]}
{:inline true})
["INSERT INTO foo (baz) VALUES (ARRAY[1, 2, 3, 4])"])))
(testing "union complex values -- fail: parameterizer"
(is (= (format {:union [{:select [:foo] :from [:bar1]}
{:select [:foo] :from [:bar2]}]
:with [[[:bar {:columns [:spam :eggs]}]
{:values [[1 2] [3 4] [5 6]]}]]}
{:inline true})
["WITH bar (spam, eggs) AS (VALUES (1, 2), (3, 4), (5, 6)) SELECT foo FROM bar1 UNION SELECT foo FROM bar2"]))))
(deftest inline-was-parameterizer-none
(testing "array parameter"
(is (= (format {:insert-into :foo
:columns [:baz]
:values [[[:array (mapv vector
(repeat :inline)
[1 2 3 4])]]]})
["INSERT INTO foo (baz) VALUES (ARRAY[1, 2, 3, 4])"])))
(testing "union complex values"
(is (= (format {:union [{:select [:foo] :from [:bar1]}
{:select [:foo] :from [:bar2]}]
:with [[[:bar {:columns [:spam :eggs]}]
{:values (mapv #(mapv vector (repeat :inline) %)
[[1 2] [3 4] [5 6]])}]]})
["WITH bar (spam, eggs) AS (VALUES (1, 2), (3, 4), (5, 6)) SELECT foo FROM bar1 UNION SELECT foo FROM bar2"]))))
(deftest insert-example-tests
;; these examples are taken from https://www.postgresql.org/docs/13/sql-insert.html
(is (= ["
INSERT INTO films
VALUES ('UA502', 'Bananas', 105, '1971-07-13', 'Comedy', '82 minutes')
"]
(format {:insert-into :films
:values [[[:inline "UA502"] [:inline "Bananas"] [:inline 105]
[:inline "1971-07-13"] [:inline "Comedy"]
[:inline "82 minutes"]]]}
{:pretty true})))
(is (= ["
INSERT INTO films
VALUES (?, ?, ?, ?, ?, ?)
" "UA502", "Bananas", 105, "1971-07-13", "Comedy", "82 minutes"]
(format {:insert-into :films
:values [["UA502" "Bananas" 105 "1971-07-13" "Comedy" "82 minutes"]]}
{:pretty true})))
(is (= ["
INSERT INTO films (code, title, did, date_prod, kind)
VALUES (?, ?, ?, ?, ?)
" "T_601", "Yojimo", 106, "1961-06-16", "Drama"]
(format {:insert-into :films
:columns [:code :title :did :date_prod :kind]
:values [["T_601", "Yojimo", 106, "1961-06-16", "Drama"]]}
{:pretty true})))
(is (= ["
INSERT INTO films
VALUES (?, ?, ?, DEFAULT, ?, ?)
" "UA502", "Bananas", 105, "Comedy", "82 minutes"]
(format {:insert-into :films
:values [["UA502" "Bananas" 105 [:default] "Comedy" "82 minutes"]]}
{:pretty true})))
(is (= ["
INSERT INTO films (code, title, did, date_prod, kind)
VALUES (?, ?, ?, DEFAULT, ?)
" "T_601", "Yojimo", 106, "Drama"]
(format {:insert-into :films
:columns [:code :title :did :date_prod :kind]
:values [["T_601", "Yojimo", 106, [:default], "Drama"]]}
{:pretty true}))))
(deftest on-conflict-tests
;; these examples are taken from https://www.postgresqltutorial.com/postgresql-upsert/
(is (= ["
INSERT INTO customers (name, email)
VALUES ('Microsoft', 'hotline@microsoft.com')
ON CONFLICT ON CONSTRAINT customers_name_key
DO NOTHING
"]
(format {:insert-into :customers
:columns [:name :email]
:values [[[:inline "Microsoft"], [:inline "hotline@microsoft.com"]]]
:on-conflict {:on-constraint :customers_name_key}
:do-nothing true}
{:pretty true})))
(is (= ["
INSERT INTO customers (name, email)
VALUES ('Microsoft', 'hotline@microsoft.com')
ON CONFLICT
ON CONSTRAINT customers_name_key
DO NOTHING
"]
(format {:insert-into :customers
:columns [:name :email]
:values [[[:inline "Microsoft"], [:inline "hotline@microsoft.com"]]]
:on-conflict []
:on-constraint :customers_name_key
:do-nothing true}
{:pretty true})))
(is (= ["
INSERT INTO customers (name, email)
VALUES ('Microsoft', 'hotline@microsoft.com')
ON CONFLICT (name)
DO NOTHING
"]
(format {:insert-into :customers
:columns [:name :email]
:values [[[:inline "Microsoft"], [:inline "hotline@microsoft.com"]]]
:on-conflict :name
:do-nothing true}
{:pretty true})))
(is (= ["
INSERT INTO customers (name, email)
VALUES ('Microsoft', 'hotline@microsoft.com')
ON CONFLICT (name)
DO NOTHING
"]
(format {:insert-into :customers
:columns [:name :email]
:values [[[:inline "Microsoft"], [:inline "hotline@microsoft.com"]]]
:on-conflict [:name]
:do-nothing true}
{:pretty true})))
(is (= ["
INSERT INTO customers (name, email)
VALUES ('Microsoft', 'hotline@microsoft.com')
ON CONFLICT ((foo + ?), name, (TRIM(email)))
DO NOTHING
" 1]
(format {:insert-into :customers
:columns [:name :email]
:values [[[:inline "Microsoft"], [:inline "hotline@microsoft.com"]]]
:on-conflict [[:+ :foo 1] :name [:trim :email]]
:do-nothing true}
{:pretty true})))
(is (= ["
INSERT INTO customers (name, email)
VALUES ('Microsoft', 'hotline@microsoft.com')
ON CONFLICT (name)
DO UPDATE SET email = EXCLUDED.email || ';' || customers.email
"]
(format {:insert-into :customers
:columns [:name :email]
:values [[[:inline "Microsoft"], [:inline "hotline@microsoft.com"]]]
:on-conflict :name
:do-update-set {:email [:|| :EXCLUDED.email [:inline ";"] :customers.email]}}
{:pretty true}))))
(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-496-overriding
(is (= ["INSERT INTO table (a, b) OVERRIDING SYSTEM VALUE VALUES (?, ?)" 1 2]
(sut/format {:insert-into [{:overriding-value :system} :table]
:columns [:a :b]
:values [[1 2]]})))
(is (= ["INSERT INTO table (a, b) OVERRIDING USER VALUE VALUES (?, ?)" 1 2]
(sut/format {:insert-into [{:overriding-value :user} :table [:a :b]]
:values [[1 2]]})))
(is (= ["INSERT INTO table (a, b) OVERRIDING SYSTEM VALUE VALUES (?, ?)" 1 2]
(sut/format {:insert-into [{:overriding-value :system} :table]
:values [{:a 1 :b 2}]}))))
(deftest output-clause-post-501
(sut/register-clause! :output :select :values)
(is (= ["INSERT INTO foo (bar) OUTPUT inserted.* VALUES (?)" 1]
(sut/format {:insert-into :foo :output [:inserted.*] :values [{:bar 1}]})))
(is (= ["INSERT INTO foo (bar) OUTPUT inserted.* VALUES (?)" 1]
(sut/format {:insert-into :foo :columns [:bar] :output [:inserted.*] :values [[1]]}))))
honeysql
(ns honey.sql.postgres-test
(:refer-clojure :exclude [update partition-by set])
(:require [clojure.test :refer [deftest is testing]]
;; pull in all the PostgreSQL helpers that the nilenso
;; library provided (as well as the regular HoneySQL ones):
[honey.sql.helpers :as sqlh :refer
[upsert on-conflict do-nothing on-constraint
returning do-update-set
;; not needed because do-update-set can do this directly
#_do-update-set!
alter-table rename-column drop-column
add-column partition-by
;; not needed because insert-into can do this directly
#_insert-into-as
create-table rename-table drop-table
window create-view over with-columns
create-extension drop-extension
select-distinct-on
;; already part of HoneySQL
insert-into values where select
from order-by update set]]
[honey.sql :as sql]))
(deftest upsert-test
(testing "upsert sql generation for postgresql"
(is (= ["INSERT INTO distributors (did, dname) VALUES (?, ?), (?, ?) ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname RETURNING *" 5 "Gizmo Transglobal" 6 "Associated Computing, Inc"]
;; preferred in honeysql:
(-> (insert-into :distributors)
(values [{:did 5 :dname "Gizmo Transglobal"}
{:did 6 :dname "Associated Computing, Inc"}])
(on-conflict :did)
(do-update-set :dname)
(returning :*)
sql/format)))
(is (= ["INSERT INTO distributors (did, dname) VALUES (?, ?), (?, ?) ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname RETURNING *" 5 "Gizmo Transglobal" 6 "Associated Computing, Inc"]
;; identical to nilenso version:
(-> (insert-into :distributors)
(values [{:did 5 :dname "Gizmo Transglobal"}
{:did 6 :dname "Associated Computing, Inc"}])
(upsert (-> (on-conflict :did)
(do-update-set :dname)))
(returning :*)
sql/format)))
(is (= ["INSERT INTO distributors (did, dname) VALUES (?, ?) ON CONFLICT (did) DO NOTHING" 7 "Redline GmbH"]
;; preferred in honeysql:
(-> (insert-into :distributors)
(values [{:did 7 :dname "Redline GmbH"}])
(on-conflict :did)
do-nothing
sql/format)))
(is (= ["INSERT INTO distributors (did, dname) VALUES (?, ?) ON CONFLICT (did) DO NOTHING" 7 "Redline GmbH"]
;; identical to nilenso version:
(-> (insert-into :distributors)
(values [{:did 7 :dname "Redline GmbH"}])
(upsert (-> (on-conflict :did)
do-nothing))
sql/format)))
(is (= ["INSERT INTO distributors (did, dname) VALUES (?, ?) ON CONFLICT ON CONSTRAINT distributors_pkey DO NOTHING" 9 "Antwerp Design"]
;; preferred in honeysql:
(-> (insert-into :distributors)
(values [{:did 9 :dname "Antwerp Design"}])
(on-conflict (on-constraint :distributors_pkey))
do-nothing
sql/format)))
(is (= ["INSERT INTO distributors (did, dname) VALUES (?, ?) ON CONFLICT (did) ON CONSTRAINT distributors_pkey DO NOTHING" 9 "Antwerp Design"]
;; with both name and clause:
(-> (insert-into :distributors)
(values [{:did 9 :dname "Antwerp Design"}])
(on-conflict :did (on-constraint :distributors_pkey))
do-nothing
sql/format)))
(is (= ["INSERT INTO distributors (did, dname) VALUES (?, ?) ON CONFLICT (did, dname) ON CONSTRAINT distributors_pkey DO NOTHING" 9 "Antwerp Design"]
;; with multiple names and a clause:
(-> (insert-into :distributors)
(values [{:did 9 :dname "Antwerp Design"}])
(on-conflict :did :dname (on-constraint :distributors_pkey))
do-nothing
sql/format)))
(is (= ["INSERT INTO distributors (did, dname) VALUES (?, ?) ON CONFLICT ON CONSTRAINT distributors_pkey DO NOTHING" 9 "Antwerp Design"]
;; almost identical to nilenso version:
(-> (insert-into :distributors)
(values [{:did 9 :dname "Antwerp Design"}])
;; in nilenso, this was (on-conflict-constraint :distributors_pkey)
(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"}
{:did 11 :dname "Foo Bar Works"}]
;; in nilenso, these two were a submap under :upsert
:on-conflict :did
:do-update-set :dname})))
(is (= ["INSERT INTO distributors (did, dname) VALUES (?, ?) ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname || ? || d.dname || ?" 23 "Foo Distributors" " (formerly " ")"]
(-> (insert-into :distributors)
(values [{:did 23 :dname "Foo Distributors"}])
(on-conflict :did)
;; nilenso:
#_(do-update-set! [:dname "EXCLUDED.dname || ' (formerly ' || d.dname || ')'"])
;; honeysql
(do-update-set {:dname [:|| :EXCLUDED.dname " (formerly " :d.dname ")"]})
sql/format)))
(is (= ["INSERT INTO distributors (did, dname) SELECT ?, ? ON CONFLICT ON CONSTRAINT distributors_pkey DO NOTHING" 1 "whatever"]
;; honeysql version:
(-> (insert-into :distributors
[:did :dname]
(select 1 "whatever"))
(on-conflict (on-constraint :distributors_pkey))
do-nothing
sql/format)
;; nilenso version:
#_(-> (insert-into :distributors)
(columns :did :dname)
(query-values (select 1 "whatever"))
(upsert (-> (on-conflict-constraint :distributors_pkey)
do-nothing))
sql/format)))))
(deftest create-table-test
;; the nilenso versions of these tests required sql/call for function-like syntax
(testing "create table with two columns"
(is (= ["CREATE TABLE cities (city VARCHAR(80) PRIMARY KEY, location POINT)"]
(-> (create-table :cities)
(with-columns [[:city [:varchar 80] [:primary-key]]
[: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)"]
(-> (create-table :weather)
(with-columns [[:city [:varchar :80] [:references :cities :city]]
[:temp_lo :int]
[:temp_hi :int]
[:prcp :real]
[: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))"]
(-> (create-table :films)
(with-columns [[:code [:char 5]]
[:title [:varchar 40]]
[:did :integer]
[:date_prod :date]
[:kind [:varchar 10]]
[[: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))"]
(-> (create-table :films)
(with-columns [[:code [:char 5] [:constraint :firstkey] [:primary-key]]
[:title [:varchar 40] [:not nil]]
[:did :integer [:not nil]]
[:date_prod :date]
[: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)"]
(-> (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)))"]
(-> (create-table :products)
(with-columns [[:product_no :integer]
[:name :text]
[:price :numeric [:check [:> :price 0]]]
[:discounted_price :numeric]
[[: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 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))))))
honeysql
(ns honey.sql.helpers-test
(:refer-clojure :exclude [filter for group-by partition-by set update])
(:require [clojure.test :refer [deftest is testing]]
[honey.sql :as sql]
[honey.sql.helpers :as h
:refer [add-column add-index alter-table columns create-table create-table-as create-view
create-materialized-view drop-view drop-materialized-view
create-index
bulk-collect-into
cross-join do-update-set drop-column drop-index drop-table
filter from full-join
group-by having insert-into
join-by join lateral left-join limit offset on-conflict
on-duplicate-key-update
order-by over partition-by refresh-materialized-view
rename-column rename-table returning right-join
select select-distinct select-top select-distinct-top
values where window with with-columns
with-data within-group]]))
(deftest test-value
(is (= ["INSERT INTO foo (bar) VALUES (?)" {:baz "my-val"}]
(->
(insert-into :foo)
(columns :bar)
(values [[[:lift {:baz "my-val"}]]])
sql/format)))
(is (= ["INSERT INTO foo (a, b, c) VALUES (?, ?, ?), (?, ?, ?)"
"a" "b" "c" "a" "b" "c"]
(-> (insert-into :foo)
(values [(array-map :a "a" :b "b" :c "c")
(hash-map :a "a" :b "b" :c "c")])
sql/format))))
(deftest issue-293-basic-ddl
(is (= (sql/format {:create-view :metro :select [:*] :from [:cities] :where [:= :metroflag "y"]})
["CREATE VIEW metro AS SELECT * FROM cities WHERE metroflag = ?" "y"]))
(is (= (sql/format {:create-table :films
:with-columns [[:id :int :unsigned :auto-increment]
[:name [:varchar 50] [:not nil]]]})
["CREATE TABLE films (id INT UNSIGNED AUTO_INCREMENT, name VARCHAR(50) NOT NULL)"]))
(is (= (sql/format (-> (create-view :metro)
(select :*)
(from :cities)
(where [:= :metroflag "y"])))
["CREATE VIEW metro AS SELECT * FROM cities WHERE metroflag = ?" "y"]))
(is (= (sql/format (-> (create-table-as :metro :if-not-exists)
(select :*)
(from :cities)
(where [:= :metroflag "y"])
(with-data false)))
["CREATE TABLE IF NOT EXISTS metro AS SELECT * FROM cities WHERE metroflag = ? WITH NO DATA" "y"]))
(is (= (sql/format (-> (create-table-as :metro :or-replace)
(select :*)
(from :cities)
(where [:= :metroflag "y"])
(with-data false)))
["CREATE OR REPLACE TABLE metro AS SELECT * FROM cities WHERE metroflag = ? WITH NO DATA" "y"]))
(is (= (sql/format (-> (create-table-as :temp :metro :if-not-exists)
(select :*)
(from :cities)
(where [:= :metroflag "y"])
(with-data false)))
["CREATE TEMP TABLE IF NOT EXISTS metro AS SELECT * FROM cities WHERE metroflag = ? WITH NO DATA" "y"]))
(is (= (sql/format (-> (create-table-as :temp :metro :or-replace)
(select :*)
(from :cities)
(where [:= :metroflag "y"])
(with-data false)))
["CREATE OR REPLACE TEMP TABLE metro AS SELECT * FROM cities WHERE metroflag = ? WITH NO DATA" "y"]))
(is (= (sql/format (-> (create-materialized-view :metro :if-not-exists)
(select :*)
(from :cities)
(where [:= :metroflag "y"])
(with-data false)))
["CREATE MATERIALIZED VIEW IF NOT EXISTS metro AS SELECT * FROM cities WHERE metroflag = ? WITH NO DATA" "y"]))
(is (= (sql/format (-> (create-table-as :metro :if-not-exists
(columns :foo :bar :baz)
[:tablespace [:entity :quux]])
(select :*)
(from :cities)
(where [:= :metroflag "y"])
(with-data false)))
[(str "CREATE TABLE IF NOT EXISTS metro"
" (foo, bar, baz) TABLESPACE quux"
" AS SELECT * FROM cities WHERE metroflag = ? WITH NO DATA") "y"]))
(is (= (sql/format (-> (create-table-as :metro :or-replace
(columns :foo :bar :baz)
[:tablespace [:entity :quux]])
(select :*)
(from :cities)
(where [:= :metroflag "y"])
(with-data false)))
[(str "CREATE OR REPLACE TABLE metro"
" (foo, bar, baz) TABLESPACE quux"
" AS SELECT * FROM cities WHERE metroflag = ? WITH NO DATA") "y"]))
(is (= (sql/format (-> (create-materialized-view :metro :if-not-exists
(columns :foo :bar :baz)
[:tablespace [:entity :quux]])
(select :*)
(from :cities)
(where [:= :metroflag "y"])
(with-data false)))
[(str "CREATE MATERIALIZED VIEW IF NOT EXISTS metro"
" (foo, bar, baz) TABLESPACE quux"
" AS SELECT * FROM cities WHERE metroflag = ? WITH NO DATA") "y"]))
(is (= (sql/format {:create-materialized-view [:metro :if-not-exists]
:select [:*]
:from :cities
:where [:= :metroflag "y"]
:with-data true})
["CREATE MATERIALIZED VIEW IF NOT EXISTS metro AS SELECT * FROM cities WHERE metroflag = ? WITH DATA" "y"]))
(is (= (sql/format {:create-materialized-view [:metro :if-not-exists
(columns :foo :bar :baz)
[:tablespace [:entity :quux]]]
:select [:*]
:from :cities
:where [:= :metroflag "y"]
:with-data false})
[(str "CREATE MATERIALIZED VIEW IF NOT EXISTS metro"
" (foo, bar, baz) TABLESPACE quux"
" AS SELECT * FROM cities WHERE metroflag = ? WITH NO DATA") "y"]))
(is (= (sql/format (-> (create-table :films)
(with-columns
[:id :int :unsigned :auto-increment]
[:name [:varchar 50] [:not nil]])))
["CREATE TABLE films (id INT UNSIGNED AUTO_INCREMENT, name VARCHAR(50) NOT NULL)"]))
(is (= (sql/format (-> (create-table :films :if-not-exists)
(with-columns
[:id :int :unsigned :auto-increment]
[:name [:varchar 50] [:not nil]])))
["CREATE TABLE IF NOT EXISTS films (id INT UNSIGNED AUTO_INCREMENT, name VARCHAR(50) NOT NULL)"]))
(is (= (sql/format (-> {:create-table :films
:with-columns
[[:id :int :unsigned :auto-increment]
[:name [:varchar 50] [:not nil]]]}))
["CREATE TABLE films (id INT UNSIGNED AUTO_INCREMENT, name VARCHAR(50) NOT NULL)"]))
(is (= (sql/format (-> {:create-table [:films :if-not-exists]
:with-columns
[[:id :int :unsigned :auto-increment]
[:name [:varchar 50] [:not nil]]]}))
["CREATE TABLE IF NOT EXISTS films (id INT UNSIGNED AUTO_INCREMENT, name VARCHAR(50) NOT NULL)"]))
(is (= (sql/format {:drop-table :foo})
["DROP TABLE foo"]))
(is (= (sql/format {:drop-table [:if-exists :foo]})
["DROP TABLE IF EXISTS foo"]))
(is (= (sql/format {:drop-view [:if-exists :foo]})
["DROP VIEW IF EXISTS foo"]))
(is (= (sql/format {:drop-materialized-view [:if-exists :foo]})
["DROP MATERIALIZED VIEW IF EXISTS foo"]))
(is (= (sql/format {:refresh-materialized-view [:concurrently :foo]
:with-data true})
["REFRESH MATERIALIZED VIEW CONCURRENTLY foo WITH DATA"]))
(is (= (sql/format '{drop-table (if-exists foo)})
["DROP TABLE IF EXISTS foo"]))
(is (= (sql/format {:drop-table [:foo :bar]})
["DROP TABLE foo, bar"]))
(is (= (sql/format {:drop-table [:if-exists :foo :bar]})
["DROP TABLE IF EXISTS foo, bar"]))
(is (= (sql/format {:drop-table [:if-exists :foo :bar [:cascade]]})
["DROP TABLE IF EXISTS foo, bar CASCADE"]))
(is (= (sql/format (drop-table :foo))
["DROP TABLE foo"]))
(is (= (sql/format (drop-table :if-exists :foo))
["DROP TABLE IF EXISTS foo"]))
(is (= (sql/format (-> (refresh-materialized-view :concurrently :foo)
(with-data true)))
["REFRESH MATERIALIZED VIEW CONCURRENTLY foo WITH DATA"]))
(is (= (sql/format (drop-table :foo :bar))
["DROP TABLE foo, bar"]))
(is (= (sql/format (drop-table :if-exists :foo :bar [:cascade]))
["DROP TABLE IF EXISTS foo, bar CASCADE"])))
(deftest issue-293-insert-into-data
;; insert into as (and other tests) based on :insert-into
;; examples in the clause reference docs:
;; first case -- table specifier:
(is (= (sql/format {:insert-into :transport
:values [[1 "Car"] [2 "Boat"] [3 "Bike"]]})
["INSERT INTO transport VALUES (?, ?), (?, ?), (?, ?)" 1 "Car" 2 "Boat" 3 "Bike"]))
(is (= (sql/format {:insert-into :transport
:columns [:id :name]
:values [[1 "Car"] [2 "Boat"] [3 "Bike"]]})
["INSERT INTO transport (id, name) VALUES (?, ?), (?, ?), (?, ?)" 1 "Car" 2 "Boat" 3 "Bike"]))
;; with an alias:
(is (= (sql/format {:insert-into [:transport :t]
:values [[1 "Car"] [2 "Boat"] [3 "Bike"]]})
["INSERT INTO transport AS t VALUES (?, ?), (?, ?), (?, ?)" 1 "Car" 2 "Boat" 3 "Bike"]))
(is (= (sql/format {:insert-into [:transport :t]
:columns [:id :name]
:values [[1 "Car"] [2 "Boat"] [3 "Bike"]]})
["INSERT INTO transport AS t (id, name) VALUES (?, ?), (?, ?), (?, ?)" 1 "Car" 2 "Boat" 3 "Bike"]))
;; second case -- table specifier and columns:
(is (= (sql/format {:insert-into [:transport [:id :name]]
:values [[1 "Car"] [2 "Boat"] [3 "Bike"]]})
["INSERT INTO transport (id, name) VALUES (?, ?), (?, ?), (?, ?)" 1 "Car" 2 "Boat" 3 "Bike"]))
;; with an alias:
(is (= (sql/format {:insert-into [[:transport :t] [:id :name]]
:values [[1 "Car"] [2 "Boat"] [3 "Bike"]]})
["INSERT INTO transport AS t (id, name) VALUES (?, ?), (?, ?), (?, ?)" 1 "Car" 2 "Boat" 3 "Bike"]))
;; third case -- table/column specifier and query:
(is (= (sql/format '{insert-into (transport {select (id, name) from (cars)})})
["INSERT INTO transport SELECT id, name FROM cars"]))
;; with columns:
(is (= (sql/format '{insert-into ((transport (id, name)) {select (*) from (cars)})})
["INSERT INTO transport (id, name) SELECT * FROM cars"]))
;; with an alias:
(is (= (sql/format '{insert-into ((transport t) {select (id, name) from (cars)})})
["INSERT INTO transport AS t SELECT id, name FROM cars"]))
;; with columns:
(is (= (sql/format '{insert-into ((transport (id, name)) {select (*) from (cars)})})
["INSERT INTO transport (id, name) SELECT * FROM cars"]))
;; with an alias and columns:
(is (= (sql/format '{insert-into (((transport t) (id, name)) {select (*) from (cars)})})
["INSERT INTO transport AS t (id, name) SELECT * FROM cars"])))
(deftest issue-293-insert-into-helpers
;; and the same set of tests using the helper functions instead:
(is (= (sql/format (-> (insert-into :transport)
(values [[1 "Car"] [2 "Boat"] [3 "Bike"]])))
["INSERT INTO transport VALUES (?, ?), (?, ?), (?, ?)" 1 "Car" 2 "Boat" 3 "Bike"]))
(is (= (sql/format (-> (insert-into :transport)
(columns :id :name)
(values [[1 "Car"] [2 "Boat"] [3 "Bike"]])))
["INSERT INTO transport (id, name) VALUES (?, ?), (?, ?), (?, ?)" 1 "Car" 2 "Boat" 3 "Bike"]))
;; with an alias:
(is (= (sql/format (-> (insert-into :transport :t)
(values [[1 "Car"] [2 "Boat"] [3 "Bike"]])))
["INSERT INTO transport AS t VALUES (?, ?), (?, ?), (?, ?)" 1 "Car" 2 "Boat" 3 "Bike"]))
(is (= (sql/format (-> (insert-into :transport :t)
(columns :id :name)
(values [[1 "Car"] [2 "Boat"] [3 "Bike"]])))
["INSERT INTO transport AS t (id, name) VALUES (?, ?), (?, ?), (?, ?)" 1 "Car" 2 "Boat" 3 "Bike"]))
;; second case -- table specifier and columns:
(is (= (sql/format (-> (insert-into :transport [:id :name])
(values [[1 "Car"] [2 "Boat"] [3 "Bike"]])))
["INSERT INTO transport (id, name) VALUES (?, ?), (?, ?), (?, ?)" 1 "Car" 2 "Boat" 3 "Bike"]))
;; with an alias:
(is (= (sql/format (-> (insert-into [:transport :t] [:id :name])
(values [[1 "Car"] [2 "Boat"] [3 "Bike"]])))
["INSERT INTO transport AS t (id, name) VALUES (?, ?), (?, ?), (?, ?)" 1 "Car" 2 "Boat" 3 "Bike"]))
;; third case -- table/column specifier and query:
(is (= (sql/format (insert-into :transport '{select (id, name) from (cars)}))
["INSERT INTO transport SELECT id, name FROM cars"]))
;; with columns:
(is (= (sql/format (insert-into [:transport [:id :name]] '{select (*) from (cars)}))
["INSERT INTO transport (id, name) SELECT * FROM cars"]))
;; with an alias:
(is (= (sql/format (insert-into '(transport t) '{select (id, name) from (cars)}))
["INSERT INTO transport AS t SELECT id, name FROM cars"]))
;; with columns:
(is (= (sql/format (insert-into '(transport (id, name)) '{select (*) from (cars)}))
["INSERT INTO transport (id, name) SELECT * FROM cars"]))
;; with an alias and columns:
(is (= (sql/format (insert-into ['(transport t) '(id, name)] '{select (*) from (cars)}))
["INSERT INTO transport AS t (id, name) SELECT * FROM cars"]))
;; three arguments with columns:
(is (= (sql/format (insert-into :transport [:id :name] '{select (*) from (cars)}))
["INSERT INTO transport (id, name) SELECT * FROM cars"]))
;; three arguments with an alias and columns:
(is (= (sql/format (insert-into '(transport t) '(id, name) '{select (*) from (cars)}))
["INSERT INTO transport AS t (id, name) SELECT * FROM cars"])))
(deftest mysql-on-duplicate-key-update
(testing "From https://www.mysqltutorial.org/mysql-insert-or-update-on-duplicate-key-update"
(is (= (sql/format (-> (insert-into :device)
(columns :name)
(values [["Printer"]])
(on-duplicate-key-update {:name "Printer"})))
["INSERT INTO device (name) VALUES (?) ON DUPLICATE KEY UPDATE name = ?"
"Printer" "Printer"]))
(is (= (sql/format (-> (insert-into :device)
(columns :id :name)
(values [[4 "Printer"]])
(on-duplicate-key-update {:name "Central Printer"})))
["INSERT INTO device (id, name) VALUES (?, ?) ON DUPLICATE KEY UPDATE name = ?"
4 "Printer" "Central Printer"]))
(is (= (sql/format (-> (insert-into :table)
(columns :c1)
(values [[42]])
(on-duplicate-key-update {:c1 [:+ [:values :c1] 1]})))
["INSERT INTO table (c1) VALUES (?) ON DUPLICATE KEY UPDATE c1 = VALUES(c1) + ?"
42 1]))))
babashka/babashka
(ns honey.sql-test
(:refer-clojure :exclude [format])
(:require [clojure.string :as str]
[clojure.test :refer [deftest is testing]]
[honey.sql :as sut :refer [format]]
[honey.sql.helpers :as h])
#?(:clj (:import (clojure.lang ExceptionInfo))))
(deftest values-alias
(is (= ["SELECT vals.a FROM (VALUES (?, ?, ?)) AS vals (a, b, c)" 1 2 3]
(format {:select [:vals.a]
:from [[{:values [[1 2 3]]} [:vals {:columns [:a :b :c]}]]]}))))
(deftest test-cte
(is (= (format {:with [[:query {:select [:foo] :from [:bar]}]]})
["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]
:from [:query1 :query2]})
["WITH query1 AS (SELECT foo FROM bar), query2 AS (SELECT bar FROM quux) SELECT query1.id, query2.name FROM query1, query2"]))
(is (= (format {:with-recursive [[:query {:select [:foo] :from [:bar]}]]})
["WITH RECURSIVE query AS (SELECT foo FROM bar)"]))
(is (= (format {:with [[[:static {:columns [:a :b :c]}] {:values [[1 2 3] [4 5]]}]]})
["WITH static (a, b, c) AS (VALUES (?, ?, ?), (?, ?, NULL))" 1 2 3 4 5]))
(is (= (format
{:with [[[:static {:columns [:a :b :c]}]
{: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])))
(deftest insert-into-namespaced
;; un-namespaced: works as expected:
(is (= (format {:insert-into :foo :values [{:foo/id 1}]})
["INSERT INTO foo (id) VALUES (?)" 1]))
(is (= (format {:insert-into :foo :columns [:foo/id] :values [[2]]})
["INSERT INTO foo (id) VALUES (?)" 2]))
(is (= (format {:insert-into :foo :values [{:foo/id 1}]}
{:namespace-as-table? true})
["INSERT INTO foo (id) VALUES (?)" 1]))
(is (= (format {:insert-into :foo :columns [:foo/id] :values [[2]]}
{:namespace-as-table? true})
["INSERT INTO foo (id) VALUES (?)" 2])))
(deftest array-test
(is (= (format {:insert-into :foo
:columns [:baz]
:values [[[:array [1 2 3 4]]]]})
["INSERT INTO foo (baz) VALUES (ARRAY[?, ?, ?, ?])" 1 2 3 4]))
(is (= (format {:insert-into :foo
:columns [:baz]
:values [[[:array ["one" "two" "three"]]]]})
["INSERT INTO foo (baz) VALUES (ARRAY[?, ?, ?])" "one" "two" "three"])))
(testing "union complex values"
(is (= (format {:union [{:select [:foo] :from [:bar1]}
{:select [:foo] :from [:bar2]}]
:with [[[:bar {:columns [:spam :eggs]}]
{:values [[1 2] [3 4] [5 6]]}]]})
["WITH bar (spam, eggs) AS (VALUES (?, ?), (?, ?), (?, ?)) SELECT foo FROM bar1 UNION SELECT foo FROM bar2"
1 2 3 4 5 6]))))
(deftest union-with-cte
(is (= (format {:union [{:select [:foo] :from [:bar1]}
{:select [:foo] :from [:bar2]}]
:with [[[:bar {:columns [:spam :eggs]}]
{:values [[1 2] [3 4] [5 6]]}]]})
["WITH bar (spam, eggs) AS (VALUES (?, ?), (?, ?), (?, ?)) SELECT foo FROM bar1 UNION SELECT foo FROM bar2" 1 2 3 4 5 6])))
(deftest union-all-with-cte
(is (= (format {:union-all [{:select [:foo] :from [:bar1]}
{:select [:foo] :from [:bar2]}]
:with [[[:bar {:columns [:spam :eggs]}]
{:values [[1 2] [3 4] [5 6]]}]]})
["WITH bar (spam, eggs) AS (VALUES (?, ?), (?, ?), (?, ?)) SELECT foo FROM bar1 UNION ALL SELECT foo FROM bar2" 1 2 3 4 5 6])))
(deftest parameterizer-none
(testing "array parameter"
(is (= (format {:insert-into :foo
:columns [:baz]
:values [[[:array [1 2 3 4]]]]}
{:inline true})
["INSERT INTO foo (baz) VALUES (ARRAY[1, 2, 3, 4])"])))
(testing "union complex values -- fail: parameterizer"
(is (= (format {:union [{:select [:foo] :from [:bar1]}
{:select [:foo] :from [:bar2]}]
:with [[[:bar {:columns [:spam :eggs]}]
{:values [[1 2] [3 4] [5 6]]}]]}
{:inline true})
["WITH bar (spam, eggs) AS (VALUES (1, 2), (3, 4), (5, 6)) SELECT foo FROM bar1 UNION SELECT foo FROM bar2"]))))
(deftest inline-was-parameterizer-none
(testing "array parameter"
(is (= (format {:insert-into :foo
:columns [:baz]
:values [[[:array (mapv vector
(repeat :inline)
[1 2 3 4])]]]})
["INSERT INTO foo (baz) VALUES (ARRAY[1, 2, 3, 4])"])))
(testing "union complex values"
(is (= (format {:union [{:select [:foo] :from [:bar1]}
{:select [:foo] :from [:bar2]}]
:with [[[:bar {:columns [:spam :eggs]}]
{:values (mapv #(mapv vector (repeat :inline) %)
[[1 2] [3 4] [5 6]])}]]})
["WITH bar (spam, eggs) AS (VALUES (1, 2), (3, 4), (5, 6)) SELECT foo FROM bar1 UNION SELECT foo FROM bar2"]))))
(deftest insert-example-tests
;; these examples are taken from https://www.postgresql.org/docs/13/sql-insert.html
(is (= ["
INSERT INTO films
VALUES ('UA502', 'Bananas', 105, '1971-07-13', 'Comedy', '82 minutes')
"]
(format {:insert-into :films
:values [[[:inline "UA502"] [:inline "Bananas"] [:inline 105]
[:inline "1971-07-13"] [:inline "Comedy"]
[:inline "82 minutes"]]]}
{:pretty true})))
(is (= ["
INSERT INTO films
VALUES (?, ?, ?, ?, ?, ?)
" "UA502", "Bananas", 105, "1971-07-13", "Comedy", "82 minutes"]
(format {:insert-into :films
:values [["UA502" "Bananas" 105 "1971-07-13" "Comedy" "82 minutes"]]}
{:pretty true})))
(is (= ["
INSERT INTO films
(code, title, did, date_prod, kind)
VALUES (?, ?, ?, ?, ?)
" "T_601", "Yojimo", 106, "1961-06-16", "Drama"]
(format {:insert-into :films
:columns [:code :title :did :date_prod :kind]
:values [["T_601", "Yojimo", 106, "1961-06-16", "Drama"]]}
{:pretty true})))
(is (= ["
INSERT INTO films
VALUES (?, ?, ?, DEFAULT, ?, ?)
" "UA502", "Bananas", 105, "Comedy", "82 minutes"]
(format {:insert-into :films
:values [["UA502" "Bananas" 105 [:default] "Comedy" "82 minutes"]]}
{:pretty true})))
(is (= ["
INSERT INTO films
(code, title, did, date_prod, kind)
VALUES (?, ?, ?, DEFAULT, ?)
" "T_601", "Yojimo", 106, "Drama"]
(format {:insert-into :films
:columns [:code :title :did :date_prod :kind]
:values [["T_601", "Yojimo", 106, [:default], "Drama"]]}
{:pretty true}))))
(deftest on-conflict-tests
;; these examples are taken from https://www.postgresqltutorial.com/postgresql-upsert/
(is (= ["
INSERT INTO customers
(name, email)
VALUES ('Microsoft', 'hotline@microsoft.com')
ON CONFLICT ON CONSTRAINT customers_name_key
DO NOTHING
"]
(format {:insert-into :customers
:columns [:name :email]
:values [[[:inline "Microsoft"], [:inline "hotline@microsoft.com"]]]
:on-conflict {:on-constraint :customers_name_key}
:do-nothing true}
{:pretty true})))
(is (= ["
INSERT INTO customers
(name, email)
VALUES ('Microsoft', 'hotline@microsoft.com')
ON CONFLICT
ON CONSTRAINT customers_name_key
DO NOTHING
"]
(format {:insert-into :customers
:columns [:name :email]
:values [[[:inline "Microsoft"], [:inline "hotline@microsoft.com"]]]
:on-conflict []
:on-constraint :customers_name_key
:do-nothing true}
{:pretty true})))
(is (= ["
INSERT INTO customers
(name, email)
VALUES ('Microsoft', 'hotline@microsoft.com')
ON CONFLICT (name)
DO NOTHING
"]
(format {:insert-into :customers
:columns [:name :email]
:values [[[:inline "Microsoft"], [:inline "hotline@microsoft.com"]]]
:on-conflict :name
:do-nothing true}
{:pretty true})))
(is (= ["
INSERT INTO customers
(name, email)
VALUES ('Microsoft', 'hotline@microsoft.com')
ON CONFLICT (name)
DO NOTHING
"]
(format {:insert-into :customers
:columns [:name :email]
:values [[[:inline "Microsoft"], [:inline "hotline@microsoft.com"]]]
:on-conflict [:name]
:do-nothing true}
{:pretty true})))
(is (= ["
INSERT INTO customers
(name, email)
VALUES ('Microsoft', 'hotline@microsoft.com')
ON CONFLICT (name, email)
DO NOTHING
"]
(format {:insert-into :customers
:columns [:name :email]
:values [[[:inline "Microsoft"], [:inline "hotline@microsoft.com"]]]
:on-conflict [:name :email]
:do-nothing true}
{:pretty true})))
(is (= ["
INSERT INTO customers
(name, email)
VALUES ('Microsoft', 'hotline@microsoft.com')
ON CONFLICT (name)
DO UPDATE SET email = EXCLUDED.email || ';' || customers.email
"]
(format {:insert-into :customers
:columns [:name :email]
:values [[[:inline "Microsoft"], [:inline "hotline@microsoft.com"]]]
:on-conflict :name
:do-update-set {:email [:|| :EXCLUDED.email [:inline ";"] :customers.email]}}
{:pretty true}))))
seancorfield/honeysql
(ns honey.sql.postgres-test
(:refer-clojure :exclude [update partition-by set])
(:require [clojure.test :refer [deftest is testing]]
;; pull in all the PostgreSQL helpers that the nilenso
;; library provided (as well as the regular HoneySQL ones):
[honey.sql.helpers :as sqlh :refer
[upsert on-conflict do-nothing on-constraint
returning do-update-set
;; not needed because do-update-set can do this directly
#_do-update-set!
alter-table rename-column drop-column
add-column partition-by
;; not needed because insert-into can do this directly
#_insert-into-as
create-table rename-table drop-table
window create-view over with-columns
create-extension drop-extension
select-distinct-on
;; already part of HoneySQL
insert-into values where select
from order-by update set]]
[honey.sql :as sql]))
(deftest upsert-test
(testing "upsert sql generation for postgresql"
(is (= ["INSERT INTO distributors (did, dname) VALUES (?, ?), (?, ?) ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname RETURNING *" 5 "Gizmo Transglobal" 6 "Associated Computing, Inc"]
;; preferred in honeysql:
(-> (insert-into :distributors)
(values [{:did 5 :dname "Gizmo Transglobal"}
{:did 6 :dname "Associated Computing, Inc"}])
(on-conflict :did)
(do-update-set :dname)
(returning :*)
sql/format)))
(is (= ["INSERT INTO distributors (did, dname) VALUES (?, ?), (?, ?) ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname RETURNING *" 5 "Gizmo Transglobal" 6 "Associated Computing, Inc"]
;; identical to nilenso version:
(-> (insert-into :distributors)
(values [{:did 5 :dname "Gizmo Transglobal"}
{:did 6 :dname "Associated Computing, Inc"}])
(upsert (-> (on-conflict :did)
(do-update-set :dname)))
(returning :*)
sql/format)))
(is (= ["INSERT INTO distributors (did, dname) VALUES (?, ?) ON CONFLICT (did) DO NOTHING" 7 "Redline GmbH"]
;; preferred in honeysql:
(-> (insert-into :distributors)
(values [{:did 7 :dname "Redline GmbH"}])
(on-conflict :did)
do-nothing
sql/format)))
(is (= ["INSERT INTO distributors (did, dname) VALUES (?, ?) ON CONFLICT (did) DO NOTHING" 7 "Redline GmbH"]
;; identical to nilenso version:
(-> (insert-into :distributors)
(values [{:did 7 :dname "Redline GmbH"}])
(upsert (-> (on-conflict :did)
do-nothing))
sql/format)))
(is (= ["INSERT INTO distributors (did, dname) VALUES (?, ?) ON CONFLICT ON CONSTRAINT distributors_pkey DO NOTHING" 9 "Antwerp Design"]
;; preferred in honeysql:
(-> (insert-into :distributors)
(values [{:did 9 :dname "Antwerp Design"}])
(on-conflict (on-constraint :distributors_pkey))
do-nothing
sql/format)))
(is (= ["INSERT INTO distributors (did, dname) VALUES (?, ?) ON CONFLICT (did) ON CONSTRAINT distributors_pkey DO NOTHING" 9 "Antwerp Design"]
;; with both name and clause:
(-> (insert-into :distributors)
(values [{:did 9 :dname "Antwerp Design"}])
(on-conflict :did (on-constraint :distributors_pkey))
do-nothing
sql/format)))
(is (= ["INSERT INTO distributors (did, dname) VALUES (?, ?) ON CONFLICT (did, dname) ON CONSTRAINT distributors_pkey DO NOTHING" 9 "Antwerp Design"]
;; with multiple names and a clause:
(-> (insert-into :distributors)
(values [{:did 9 :dname "Antwerp Design"}])
(on-conflict :did :dname (on-constraint :distributors_pkey))
do-nothing
sql/format)))
(is (= ["INSERT INTO distributors (did, dname) VALUES (?, ?) ON CONFLICT ON CONSTRAINT distributors_pkey DO NOTHING" 9 "Antwerp Design"]
;; almost identical to nilenso version:
(-> (insert-into :distributors)
(values [{:did 9 :dname "Antwerp Design"}])
;; in nilenso, this was (on-conflict-constraint :distributors_pkey)
(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"}
{:did 11 :dname "Foo Bar Works"}]
;; in nilenso, these two were a submap under :upsert
:on-conflict :did
:do-update-set :dname})))
(is (= ["INSERT INTO distributors (did, dname) VALUES (?, ?) ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname || ? || d.dname || ?" 23 "Foo Distributors" " (formerly " ")"]
(-> (insert-into :distributors)
(values [{:did 23 :dname "Foo Distributors"}])
(on-conflict :did)
;; nilenso:
#_(do-update-set! [:dname "EXCLUDED.dname || ' (formerly ' || d.dname || ')'"])
;; honeysql
(do-update-set {:dname [:|| :EXCLUDED.dname " (formerly " :d.dname ")"]})
sql/format)))
(is (= ["INSERT INTO distributors (did, dname) SELECT ?, ? ON CONFLICT ON CONSTRAINT distributors_pkey DO NOTHING" 1 "whatever"]
;; honeysql version:
(-> (insert-into :distributors
[:did :dname]
(select 1 "whatever"))
(on-conflict (on-constraint :distributors_pkey))
do-nothing
sql/format)
;; nilenso version:
#_(-> (insert-into :distributors)
(columns :did :dname)
(query-values (select 1 "whatever"))
(upsert (-> (on-conflict-constraint :distributors_pkey)
do-nothing))
sql/format)))))
(deftest create-table-test
;; the nilenso versions of these tests required sql/call for function-like syntax
(testing "create table with two columns"
(is (= ["CREATE TABLE cities (city VARCHAR(80) PRIMARY KEY, location POINT)"]
(-> (create-table :cities)
(with-columns [[:city [:varchar 80] [:primary-key]]
[: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)"]
(-> (create-table :weather)
(with-columns [[:city [:varchar :80] [:references :cities :city]]
[:temp_lo :int]
[:temp_hi :int]
[:prcp :real]
[: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))"]
(-> (create-table :films)
(with-columns [[:code [:char 5]]
[:title [:varchar 40]]
[:did :integer]
[:date_prod :date]
[:kind [:varchar 10]]
[[: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))"]
(-> (create-table :films)
(with-columns [[:code [:char 5] [:constraint :firstkey] [:primary-key]]
[:title [:varchar 40] [:not nil]]
[:did :integer [:not nil]]
[:date_prod :date]
[: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)"]
(-> (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)))"]
(-> (create-table :products)
(with-columns [[:product_no :integer]
[:name :text]
[:price :numeric [:check [:> :price 0]]]
[:discounted_price :numeric]
[[: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 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))))))
seancorfield/honeysql
(ns honey.sql-test
(:refer-clojure :exclude [format])
(:require [clojure.string :as str]
[clojure.test :refer [deftest is testing]]
[honey.sql :as sut :refer [format]]
[honey.sql.helpers :as h])
#?(:clj (:import (clojure.lang ExceptionInfo))))
(deftest values-alias
(is (= ["SELECT vals.a FROM (VALUES (?, ?, ?)) AS vals (a, b, c)" 1 2 3]
(format {:select [:vals.a]
:from [[{:values [[1 2 3]]} [:vals {:columns [:a :b :c]}]]]}))))
(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]
:from [:query1 :query2]})
["WITH query1 AS (SELECT foo FROM bar), query2 AS (SELECT bar FROM quux) SELECT query1.id, query2.name FROM query1, query2"]))
(is (= (format {:with-recursive [[:query {:select [:foo] :from [:bar]}]]})
["WITH RECURSIVE query AS (SELECT foo FROM bar)"]))
(is (= (format {:with [[[:static {:columns [:a :b :c]}] {:values [[1 2 3] [4 5]]}]]})
["WITH static (a, b, c) AS (VALUES (?, ?, ?), (?, ?, NULL))" 1 2 3 4 5]))
(is (= (format
{:with [[[:static {:columns [:a :b :c]}]
{: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]))
(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-namespaced
;; un-namespaced: works as expected:
(is (= (format {:insert-into :foo :values [{:foo/id 1}]})
["INSERT INTO foo (id) VALUES (?)" 1]))
(is (= (format {:insert-into :foo :columns [:foo/id] :values [[2]]})
["INSERT INTO foo (id) VALUES (?)" 2]))
(is (= (format {:insert-into :foo :values [{:foo/id 1}]}
{:namespace-as-table? true})
["INSERT INTO foo (id) VALUES (?)" 1]))
(is (= (format {:insert-into :foo :columns [:foo/id] :values [[2]]}
{:namespace-as-table? true})
["INSERT INTO foo (id) VALUES (?)" 2])))
(deftest insert-into-functions
;; needs [[:raw ..]] because it's the columns case:
(is (= (format {:insert-into [[[:raw "My-Table Name"]] {:select [:bar] :from [:baz]}]})
["INSERT INTO My-Table Name SELECT bar FROM baz"]))
;; this variant only needs [:raw ..]
(is (= (format {:insert-into [[:raw "My-Table Name"]] :values [{:foo/id 1}]})
["INSERT INTO My-Table Name (id) VALUES (?)" 1]))
(is (= (format {:insert-into [:foo :bar] :values [{:foo/id 1}]})
["INSERT INTO foo AS bar (id) VALUES (?)" 1])))
(deftest array-test
(is (= (format {:insert-into :foo
:columns [:baz]
:values [[[:array [1 2 3 4]]]]})
["INSERT INTO foo (baz) VALUES (ARRAY[?, ?, ?, ?])" 1 2 3 4]))
(is (= (format {:insert-into :foo
:columns [:baz]
:values [[[:array ["one" "two" "three"]]]]})
["INSERT INTO foo (baz) VALUES (ARRAY[?, ?, ?])" "one" "two" "three"]))
#_ ;; requested feature -- does not work yet
(is (= (format {:insert-into :foo
:columns [:baz]
:values [[[:array :?vals]]]}
{:params {:vals [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])))
(testing "array subquery"
(is (= (format {:select [[[:array {:select [:foo] :from [:bar]}]]]})
["SELECT ARRAY(SELECT foo FROM bar)"]))
(is (= (format {:select [[[:array {:select ^{:as :struct} [:foo :bar] :from [:bar]}]]]})
["SELECT ARRAY(SELECT AS STRUCT foo, bar FROM bar)"]))
;; documented subquery workaround:
(is (= (format {:select [[[:'ARRAY {:select [:foo] :from [:bar]}]]]})
["SELECT ARRAY (SELECT foo FROM bar)"]))))
(testing "union complex values"
(is (= (format {:union [{:select [:foo] :from [:bar1]}
{:select [:foo] :from [:bar2]}]
:with [[[:bar {:columns [:spam :eggs]}]
{:values [[1 2] [3 4] [5 6]]}]]})
["WITH bar (spam, eggs) AS (VALUES (?, ?), (?, ?), (?, ?)) SELECT foo FROM bar1 UNION SELECT foo FROM bar2"
1 2 3 4 5 6]))))
(deftest union-with-cte
(is (= (format {:union [{:select [:foo] :from [:bar1]}
{:select [:foo] :from [:bar2]}]
:with [[[:bar {:columns [:spam :eggs]}]
{:values [[1 2] [3 4] [5 6]]}]]})
["WITH bar (spam, eggs) AS (VALUES (?, ?), (?, ?), (?, ?)) SELECT foo FROM bar1 UNION SELECT foo FROM bar2" 1 2 3 4 5 6])))
(deftest union-all-with-cte
(is (= (format {:union-all [{:select [:foo] :from [:bar1]}
{:select [:foo] :from [:bar2]}]
:with [[[:bar {:columns [:spam :eggs]}]
{:values [[1 2] [3 4] [5 6]]}]]})
["WITH bar (spam, eggs) AS (VALUES (?, ?), (?, ?), (?, ?)) SELECT foo FROM bar1 UNION ALL SELECT foo FROM bar2" 1 2 3 4 5 6])))
(deftest parameterizer-none
(testing "array parameter"
(is (= (format {:insert-into :foo
:columns [:baz]
:values [[[:array [1 2 3 4]]]]}
{:inline true})
["INSERT INTO foo (baz) VALUES (ARRAY[1, 2, 3, 4])"])))
(testing "union complex values -- fail: parameterizer"
(is (= (format {:union [{:select [:foo] :from [:bar1]}
{:select [:foo] :from [:bar2]}]
:with [[[:bar {:columns [:spam :eggs]}]
{:values [[1 2] [3 4] [5 6]]}]]}
{:inline true})
["WITH bar (spam, eggs) AS (VALUES (1, 2), (3, 4), (5, 6)) SELECT foo FROM bar1 UNION SELECT foo FROM bar2"]))))
(deftest inline-was-parameterizer-none
(testing "array parameter"
(is (= (format {:insert-into :foo
:columns [:baz]
:values [[[:array (mapv vector
(repeat :inline)
[1 2 3 4])]]]})
["INSERT INTO foo (baz) VALUES (ARRAY[1, 2, 3, 4])"])))
(testing "union complex values"
(is (= (format {:union [{:select [:foo] :from [:bar1]}
{:select [:foo] :from [:bar2]}]
:with [[[:bar {:columns [:spam :eggs]}]
{:values (mapv #(mapv vector (repeat :inline) %)
[[1 2] [3 4] [5 6]])}]]})
["WITH bar (spam, eggs) AS (VALUES (1, 2), (3, 4), (5, 6)) SELECT foo FROM bar1 UNION SELECT foo FROM bar2"]))))
(deftest insert-example-tests
;; these examples are taken from https://www.postgresql.org/docs/13/sql-insert.html
(is (= ["
INSERT INTO films
VALUES ('UA502', 'Bananas', 105, '1971-07-13', 'Comedy', '82 minutes')
"]
(format {:insert-into :films
:values [[[:inline "UA502"] [:inline "Bananas"] [:inline 105]
[:inline "1971-07-13"] [:inline "Comedy"]
[:inline "82 minutes"]]]}
{:pretty true})))
(is (= ["
INSERT INTO films
VALUES (?, ?, ?, ?, ?, ?)
" "UA502", "Bananas", 105, "1971-07-13", "Comedy", "82 minutes"]
(format {:insert-into :films
:values [["UA502" "Bananas" 105 "1971-07-13" "Comedy" "82 minutes"]]}
{:pretty true})))
(is (= ["
INSERT INTO films (code, title, did, date_prod, kind)
VALUES (?, ?, ?, ?, ?)
" "T_601", "Yojimo", 106, "1961-06-16", "Drama"]
(format {:insert-into :films
:columns [:code :title :did :date_prod :kind]
:values [["T_601", "Yojimo", 106, "1961-06-16", "Drama"]]}
{:pretty true})))
(is (= ["
INSERT INTO films
VALUES (?, ?, ?, DEFAULT, ?, ?)
" "UA502", "Bananas", 105, "Comedy", "82 minutes"]
(format {:insert-into :films
:values [["UA502" "Bananas" 105 [:default] "Comedy" "82 minutes"]]}
{:pretty true})))
(is (= ["
INSERT INTO films (code, title, did, date_prod, kind)
VALUES (?, ?, ?, DEFAULT, ?)
" "T_601", "Yojimo", 106, "Drama"]
(format {:insert-into :films
:columns [:code :title :did :date_prod :kind]
:values [["T_601", "Yojimo", 106, [:default], "Drama"]]}
{:pretty true}))))
(deftest on-conflict-tests
;; these examples are taken from https://www.postgresqltutorial.com/postgresql-upsert/
(is (= ["
INSERT INTO customers (name, email)
VALUES ('Microsoft', 'hotline@microsoft.com')
ON CONFLICT ON CONSTRAINT customers_name_key
DO NOTHING
"]
(format {:insert-into :customers
:columns [:name :email]
:values [[[:inline "Microsoft"], [:inline "hotline@microsoft.com"]]]
:on-conflict {:on-constraint :customers_name_key}
:do-nothing true}
{:pretty true})))
(is (= ["
INSERT INTO customers (name, email)
VALUES ('Microsoft', 'hotline@microsoft.com')
ON CONFLICT
ON CONSTRAINT customers_name_key
DO NOTHING
"]
(format {:insert-into :customers
:columns [:name :email]
:values [[[:inline "Microsoft"], [:inline "hotline@microsoft.com"]]]
:on-conflict []
:on-constraint :customers_name_key
:do-nothing true}
{:pretty true})))
(is (= ["
INSERT INTO customers (name, email)
VALUES ('Microsoft', 'hotline@microsoft.com')
ON CONFLICT (name)
DO NOTHING
"]
(format {:insert-into :customers
:columns [:name :email]
:values [[[:inline "Microsoft"], [:inline "hotline@microsoft.com"]]]
:on-conflict :name
:do-nothing true}
{:pretty true})))
(is (= ["
INSERT INTO customers (name, email)
VALUES ('Microsoft', 'hotline@microsoft.com')
ON CONFLICT (name)
DO NOTHING
"]
(format {:insert-into :customers
:columns [:name :email]
:values [[[:inline "Microsoft"], [:inline "hotline@microsoft.com"]]]
:on-conflict [:name]
:do-nothing true}
{:pretty true})))
(is (= ["
INSERT INTO customers (name, email)
VALUES ('Microsoft', 'hotline@microsoft.com')
ON CONFLICT ((foo + ?), name, (TRIM(email)))
DO NOTHING
" 1]
(format {:insert-into :customers
:columns [:name :email]
:values [[[:inline "Microsoft"], [:inline "hotline@microsoft.com"]]]
:on-conflict [[:+ :foo 1] :name [:trim :email]]
:do-nothing true}
{:pretty true})))
(is (= ["
INSERT INTO customers (name, email)
VALUES ('Microsoft', 'hotline@microsoft.com')
ON CONFLICT (name)
DO UPDATE SET email = EXCLUDED.email || ';' || customers.email
"]
(format {:insert-into :customers
:columns [:name :email]
:values [[[:inline "Microsoft"], [:inline "hotline@microsoft.com"]]]
:on-conflict :name
:do-update-set {:email [:|| :EXCLUDED.email [:inline ";"] :customers.email]}}
{:pretty true}))))
(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-496-overriding
(is (= ["INSERT INTO table (a, b) OVERRIDING SYSTEM VALUE VALUES (?, ?)" 1 2]
(sut/format {:insert-into [{:overriding-value :system} :table]
:columns [:a :b]
:values [[1 2]]})))
(is (= ["INSERT INTO table (a, b) OVERRIDING USER VALUE VALUES (?, ?)" 1 2]
(sut/format {:insert-into [{:overriding-value :user} :table [:a :b]]
:values [[1 2]]})))
(is (= ["INSERT INTO table (a, b) OVERRIDING SYSTEM VALUE VALUES (?, ?)" 1 2]
(sut/format {:insert-into [{:overriding-value :system} :table]
:values [{:a 1 :b 2}]}))))
(deftest output-clause-post-501
(sut/register-clause! :output :select :values)
(is (= ["INSERT INTO foo (bar) OUTPUT inserted.* VALUES (?)" 1]
(sut/format {:insert-into :foo :output [:inserted.*] :values [{:bar 1}]})))
(is (= ["INSERT INTO foo (bar) OUTPUT inserted.* VALUES (?)" 1]
(sut/format {:insert-into :foo :columns [:bar] :output [:inserted.*] :values [[1]]}))))
lambdaisland/plenish
(ns repl-sessions.jdbc-poke
(:require [next.jdbc :as jdbc]
[next.jdbc.result-set :as rs]
[honey.sql :as honey]
[honey.sql.helpers :as hh]))
(jdbc/execute!
ds
(honey/format
(-> (hh/create-table "foo" :if-not-exists)
(hh/with-columns [[:db__id :bigint [:primary-key]]]))))