Public Vars

Back

values (clj)

(source)

function

(values row-value-coll)
Accepts a single argument: a collection of row values. Each row value can be either a sequence of column values or a hash map of column name/column value pairs. Used with `insert-into`. (-> (insert-into :foo) (values [{:id 1, :name "John"} {:id 2, :name "Fred"}])) Produces: INSERT INTO foo (id, name) VALUES (?, ?), (?, ?) Parameters: 1 "John" 2 "Fred"

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-uneven-maps
  ;; we can't rely on ordering when the set of keys differs between maps:
  (let [res (format {:insert-into :foo :values [{:id 1} {:id 2, :bar "quux"}]})]
    (is (or (= res ["INSERT INTO foo (id, bar) VALUES (?, NULL), (?, ?)" 1 2 "quux"])
            (= res ["INSERT INTO foo (bar, id) VALUES (NULL, ?), (?, ?)" 1 "quux" 2]))))
  (let [res (format {:insert-into :foo :values [{:id 1, :bar "quux"} {:id 2}]})]
    (is (or (= res ["INSERT INTO foo (id, bar) VALUES (?, ?), (?, NULL)" 1 "quux" 2])
            (= res ["INSERT INTO foo (bar, id) VALUES (?, ?), (NULL, ?)" "quux" 1 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"]))))

#?(:clj
   (deftest issue-385-test
     (let [u (java.util.UUID/randomUUID)]
       (is (= [(str "VALUES ('" (str u) "')")]
              (format {:values [[u]]} {:inline true}))))))

(deftest inlined-values-are-stringified-correctly
  (is (= ["SELECT 'foo', 'It''s a quote!', bar, NULL"]
         (format {:select [[[:inline "foo"]]
                           [[:inline "It's a quote!"]]
                           [[:inline :bar]]
                           [[:inline nil]]]}))))

(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-299-test
  (let [name    "test field"
        ;; this was a bug in 1.x -- adding here to prevent regression:
        enabled [true, "); SELECT case when (SELECT current_setting('is_superuser'))='off' then pg_sleep(0.2) end; -- "]]
    (is (= ["INSERT INTO table (name, enabled) VALUES (?, (TRUE, ?))" name (second enabled)]
           (format {:insert-into :table
                    :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-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-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 upsert-where-test
  (is (= ["INSERT INTO user (phone, name) VALUES (?, ?) ON CONFLICT (phone) WHERE phone IS NOT NULL DO UPDATE SET phone = EXCLUDED.phone, name = EXCLUDED.name WHERE user.active = FALSE" "5555555" "John"]
         (sql/format
          {:insert-into :user
           :values      [{:phone "5555555" :name "John"}]
           :on-conflict   [:phone
                           {:where [:<> :phone nil]}]
           :do-update-set {:fields [:phone :name]
                           :where  [:= :user.active false]}})
         ;; nilenso version
         #_(sql/format
            {:insert-into :user
             :values      [{:phone "5555555" :name "John"}]
             ;; nested under :upsert
             :upsert      {:on-conflict   [:phone]
                           ;; but :where is at the same level as :on-conflict
                           :where         [:<> :phone nil]
                           ;; this is the same as in honeysql:
                           :do-update-set {:fields [:phone :name]
                                           :where  [:= :user.active false]}}}))))

(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 insert-into-with-alias
  (testing "insert into with alias"
    (is (= ["INSERT INTO distributors AS d (did, dname) VALUES (?, ?), (?, ?) ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname WHERE d.zipcode <> ? RETURNING d.*" 5 "Gizmo Transglobal" 6 "Associated Computing, Inc" "21201"]
           ;; honeysql supports alias in insert-into:
           (-> (insert-into :distributors :d)
               ;; nilensor required insert-into-as:
               #_(insert-into-as :distributors :d)
               (values [{:did 5 :dname "Gizmo Transglobal"}
                        {:did 6 :dname "Associated Computing, Inc"}])
               (on-conflict :did)
               ;; honeysql supports names and a where clause:
               (do-update-set :dname (where [:<> :d.zipcode "21201"]))
               ;; nilenso nested those under upsert:
               #_(upsert (-> (on-conflict :did)
                             (do-update-set :dname)
                             (where [:<> :d.zipcode "21201"])))
               (returning :d.*)
               sql/format)))))

(deftest values-except-select
  (testing "select which values are not not present in a table"
    (is (= ["VALUES (?), (?), (?) EXCEPT SELECT id FROM images" 4 5 6]
           (sql/format
            {:except
             [{:values [[4] [5] [6]]}
              {:select [:id] :from [:images]}]})))))

(deftest values-except-all-select
  (testing "select which values are not not present in a table"
    (is (= ["VALUES (?), (?), (?) EXCEPT ALL SELECT id FROM images" 4 5 6]
           (sql/format
            {:except-all
             [{:values [[4] [5] [6]]}
              {:select [:id] :from [:images]}]})))))
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 test-operators
  (testing "="
    (testing "with nil"
      (is (= ["SELECT * FROM customers WHERE name IS NULL"]
             (sql/format {:select [:*]
                          :from [:customers]
                          :where [:= :name nil]})))
      (is (= ["SELECT * FROM customers WHERE name = ?" nil]
             (sql/format {:select [:*]
                          :from [:customers]
                          :where [:= :name :?name]}
                         {:params {:name nil}})))))
  (testing "in"
    (doseq [[cname coll] [[:vector []] [:set #{}] [:list '()]]]
      (testing (str "with values from a " (name cname))
        (let [values (conj coll 1)]
          (is (= ["SELECT * FROM customers WHERE id IN (?)" 1]
                 (sql/format {:select [:*]
                              :from [:customers]
                              :where [:in :id values]})))
          (is (= ["SELECT * FROM customers WHERE id IN (?)" 1]
                 (sql/format {:select [:*]
                              :from [:customers]
                              :where [:in :id :?ids]}
                             {:params {:ids values}}))))))
    (testing "with more than one integer"
      (let [values [1 2]]
        (is (= ["SELECT * FROM customers WHERE id IN (?, ?)" 1 2]
               (sql/format {:select [:*]
                            :from [:customers]
                            :where [:in :id values]})))
        (is (= ["SELECT * FROM customers WHERE id IN (?, ?)" 1 2]
               (sql/format {:select [:*]
                            :from [:customers]
                            :where [:in :id :?ids]}
                           {:params {:ids values}})))))
    (testing "with more than one string"
      (let [values ["1" "2"]]
        (is (= ["SELECT * FROM customers WHERE id IN (?, ?)" "1" "2"]
               (sql/format {:select [:*]
                            :from [:customers]
                            :where [:in :id values]})
               (sql/format {:select [:*]
                            :from [:customers]
                            :where [:in :id :?ids]}
                           {: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 issue-293-sql
  ;; these tests are based on the README at https://github.com/nilenso/honeysql-postgres
  (is (= (-> (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)
         [(str "INSERT INTO distributors (did, dname)"
               " VALUES (?, ?), (?, ?)"
               " ON CONFLICT (did)"
               " DO UPDATE SET dname = EXCLUDED.dname"
               " RETURNING *")
          5 "Gizmo Transglobal"
          6 "Associated Computing, Inc"]))
  (is (= (-> (insert-into :distributors)
             (values [{:did 23 :dname "Foo Distributors"}])
             (on-conflict :did)
             ;; instead of do-update-set!
             (do-update-set {:dname [:|| :EXCLUDED.dname " (formerly " :distributors.dname ")"]
                             :downer :EXCLUDED.downer})
             sql/format)
         [(str "INSERT INTO distributors (did, dname)"
               " VALUES (?, ?)"
               " ON CONFLICT (did)"
               " DO UPDATE SET dname = EXCLUDED.dname || ? || distributors.dname || ?,"
               " downer = EXCLUDED.downer")
          23 "Foo Distributors" " (formerly " ")"]))
  ;; insert into / insert into as tests are below
  (is (= (-> (select :id
                     (over [[:avg :salary] (-> (partition-by :department) (order-by :designation)) :Average]
                           [[:max :salary] :w :MaxSalary]))
             (from :employee)
             (window :w (partition-by :department))
             sql/format)
         [(str "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)")]))
  ;; test nil / empty window function clause:
  (is (= (-> (select :id
                     (over [[:avg :salary] {} :Average]
                           [[:max :salary] nil :MaxSalary]))
             (from :employee)
             sql/format)
         [(str "SELECT id,"
               " AVG(salary) OVER () AS Average,"
               " MAX(salary) OVER () AS MaxSalary"
               " FROM employee")])))

(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]))))