Public Vars

Back

set (clj)

(source)

function

(set col-set-map)
Accepts a hash map specifying column names and the values to be assigned to them, as part of `update`: (-> (update :foo) (set {:a 1 :b nil})) Produces: UPDATE foo SET a = ?, b = NULL

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 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 set-before-from
  ;; issue 235
  (is (=
       ["UPDATE \"films\" \"f\" SET \"kind\" = \"c\".\"test\" FROM (SELECT \"b\".\"test\" FROM \"bar\" AS \"b\" WHERE \"b\".\"id\" = ?) AS \"c\" WHERE \"f\".\"kind\" = ?" 1 "drama"]
       (->
        {:update [:films :f]
         :set    {:kind :c.test}
         :from   [[{:select [:b.test]
                    :from   [[:bar :b]]
                    :where  [:= :b.id 1]} :c]]
         :where  [:= :f.kind "drama"]}
        (format {:quoted true}))))
  ;; issue 317
  (is (=
       ["UPDATE \"films\" \"f\" SET \"kind\" = \"c\".\"test\" FROM (SELECT \"b\".\"test\" FROM \"bar\" AS \"b\" WHERE \"b\".\"id\" = ?) AS \"c\" WHERE \"f\".\"kind\" = ?" 1 "drama"]
       (->
        {:update [:films :f]
         ;; drop ns in set clause...
         :set    {:f/kind :c.test}
         :from   [[{:select [:b.test]
                    :from   [[:bar :b]]
                    :where  [:= :b.id 1]} :c]]
         :where  [:= :f.kind "drama"]}
        (format {:quoted true}))))
  (is (=
       ["UPDATE \"films\" \"f\" SET \"f\".\"kind\" = \"c\".\"test\" FROM (SELECT \"b\".\"test\" FROM \"bar\" AS \"b\" WHERE \"b\".\"id\" = ?) AS \"c\" WHERE \"f\".\"kind\" = ?" 1 "drama"]
       (->
        {:update [:films :f]
         ;; ...but keep literal dotted name
         :set    {:f.kind :c.test}
         :from   [[{:select [:b.test]
                    :from   [[:bar :b]]
                    :where  [:= :b.id 1]} :c]]
         :where  [:= :f.kind "drama"]}
        (format {:quoted true})))))

(deftest set-after-join
  (is (=
       ["UPDATE `foo` INNER JOIN `bar` ON `bar`.`id` = `foo`.`bar_id` SET `a` = ? WHERE `bar`.`b` = ?" 1 42]
       (->
        {:update :foo
         :join   [:bar [:= :bar.id :foo.bar_id]]
         :set    {:a 1}
         :where  [:= :bar.b 42]}
        (format {:dialect :mysql}))))
  ;; issue 344
  (is (=
       ["UPDATE `foo` INNER JOIN `bar` ON `bar`.`id` = `foo`.`bar_id` SET `f`.`a` = ? WHERE `bar`.`b` = ?" 1 42]
       (->
        {:update :foo
         :join   [:bar [:= :bar.id :foo.bar_id]]
         ;; do not drop ns in set clause for MySQL:
         :set    {:f/a 1}
         :where  [:= :bar.b 42]}
        (format {:dialect :mysql})))))

;; Make sure if Locale is Turkish we're not generating queries like İNNER JOIN (dot over the I) because
;; `string/upper-case` is converting things to upper-case using the default Locale. Generated query should be the same
;; regardless of system Locale. See #236
#?(:clj
   (deftest statements-generated-correctly-with-turkish-locale
     (let [format-with-locale (fn [^String language-tag]
                                (let [original-locale (java.util.Locale/getDefault)]
                                  (try
                                    (java.util.Locale/setDefault (java.util.Locale/forLanguageTag language-tag))
                                    (format {:select [:t2.name]
                                             :from   [[:table1 :t1]]
                                             :join   [[:table2 :t2] [:= :t1.fk :t2.id]]
                                             :where  [:= :t1.id 1]})
                                    (finally
                                      (java.util.Locale/setDefault original-locale)))))]
       (is (= (format-with-locale "en")
              (format-with-locale "tr"))))))

(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-321-linting
  (testing "empty IN is ignored by default"
    (is (= ["WHERE x IN ()"]
           (format {:where [:in :x []]})))
    (is (= ["WHERE x IN ()"]
           (format {:where [:in :x :?y]}
                   {:params {:y []}}))))
  (testing "empty IN is flagged in basic mode"
    (is (thrown-with-msg? ExceptionInfo #"empty collection"
                          (format {:where [:in :x []]}
                                  {:checking :basic})))
    (is (thrown-with-msg? ExceptionInfo #"empty collection"
                          (format {:where [:in :x :?y]}
                                  {:params {:y []} :checking :basic}))))
  (testing "IN NULL is ignored by default and basic"
    (is (= ["WHERE x IN (NULL)"]
           (format {:where [:in :x [nil]]})))
    (is (= ["WHERE x IN (NULL)"]
           (format {:where [:in :x [nil]]}
                   {:checking :basic})))
    (is (= ["WHERE x IN (?)" nil]
           (format {:where [:in :x :?y]}
                   {:params {:y [nil]}})))
    (is (= ["WHERE x IN (?)" nil]
           (format {:where [:in :x :?y]}
                   {:params {:y [nil]} :checking :basic})))
    (is (= ["WHERE x IN ($2)" nil nil]
           (format {:where [:in :x :?y]}
                   {:params {:y [nil]} :numbered true})))
    (is (= ["WHERE x IN ($2)" nil nil]
           (format {:where [:in :x :?y]}
                   {:params {:y [nil]} :checking :basic :numbered true}))))
  (testing "IN NULL is flagged in strict mode"
    (is (thrown-with-msg? ExceptionInfo #"does not match"
                          (format {:where [:in :x [nil]]}
                                  {:checking :strict})))
    (is (thrown-with-msg? ExceptionInfo #"does not match"
                          (format {:where [:in :x :?y]}
                                  {:params {:y [nil]} :checking :strict}))))
  (testing "empty WHERE clauses ignored with none"
    (is (= ["DELETE FROM foo"]
           (format {:delete-from :foo})))
    (is (= ["DELETE foo"]
           (format {:delete :foo})))
    (is (= ["UPDATE foo SET x = ?" 1]
           (format {:update :foo :set {:x 1}}))))
  (testing "empty WHERE clauses flagged in basic mode"
    (is (thrown-with-msg? ExceptionInfo #"without a non-empty"
                          (format {:delete-from :foo} {:checking :basic})))
    (is (thrown-with-msg? ExceptionInfo #"without a non-empty"
                          (format {:delete :foo} {:checking :basic})))
    (is (thrown-with-msg? ExceptionInfo #"without a non-empty"
                          (format {:update :foo :set {:x 1}} {:checking :basic})))))

(deftest quoting-:%-syntax
  (testing "quoting of expressions in functions shouldn't depend on syntax"
    (is (= ["SELECT SYSDATE()"]
           (format {:select [[[:sysdate]]]})
           (format {:select :%sysdate})))
    (is (= ["SELECT COUNT(*)"]
           (format {:select [[[:count :*]]]})
           (format {:select :%count.*})))
    (is (= ["SELECT AVERAGE(`foo-foo`)"]
           (format {:select [[[:average :foo-foo]]]} :dialect :mysql)
           (format {:select :%average.foo-foo} :dialect :mysql)))
    (is (= ["SELECT GREATER(`foo-foo`, `bar-bar`)"]
           (format {:select [[[:greater :foo-foo :bar-bar]]]} :dialect :mysql)
           (format {:select :%greater.foo-foo.bar-bar} :dialect :mysql)))
    (is (= ["SELECT MIXED_KEBAB(`yum-yum`)"]
           (format {:select :%mixed-kebab.yum-yum} :dialect :mysql)))
    (is (= ["SELECT MIXED_KEBAB(`yum_yum`)"]
           (format {:select :%mixed-kebab.yum-yum} :dialect :mysql :quoted-snake true)))
    ;; qualifier is always - -> _ converted:
    (is (= ["SELECT MIXED_KEBAB(`yum_yum`.`bar-bar`, `a_b`.`c-d`)"]
           (format {:select (keyword "%mixed-kebab.yum-yum/bar-bar.a-b/c-d")} :dialect :mysql)))
    ;; name is only - -> _ converted when snake_case requested:
    (is (= ["SELECT MIXED_KEBAB(`yum_yum`.`bar_bar`, `a_b`.`c_d`)"]
           (format {:select (keyword "%mixed-kebab.yum-yum/bar-bar.a-b/c-d")} :dialect :mysql :quoted-snake true)))
    (is (= ["SELECT RANSOM(`NoTe`)"]
           (format {:select [[[:ransom :NoTe]]]} :dialect :mysql)
           (format {:select :%ransom.NoTe} :dialect :mysql))))
  (testing "issue 352: literal function calls"
    (is (= ["SELECT sysdate()"]
           (format {:select [[[:'sysdate]]]})))
    (is (= ["SELECT count(*)"]
           (format {:select [[[:'count :*]]]})))
    (is (= ["SELECT Mixed_Kebab(yum_yum)"]
           (format {:select [[[:'Mixed-Kebab :yum-yum]]]})))
    (is (= ["SELECT `Mixed-Kebab`(`yum-yum`)"]
           (format {:select [[[:'Mixed-Kebab :yum-yum]]]} :dialect :mysql)))
    (is (= ["SELECT other_project.other_dataset.other_function(?, ?)" 1 2]
           (format {:select [[[:'other-project.other_dataset.other_function 1 2]]]})))
    (is (= ["SELECT \"other-project\".\"other_dataset\".\"other_function\"(?, ?)" 1 2]
           (format {:select [[[:'other-project.other_dataset.other_function 1 2]]]} :dialect :ansi)))))

(deftest fetch-offset-issue-338
  (testing "default offset (with and without limit)"
    (is (= ["SELECT foo FROM bar LIMIT ? OFFSET ?" 10 20]
           (format {:select :foo :from :bar
                    :limit 10 :offset 20})))
    (is (= ["SELECT foo FROM bar OFFSET ?" 20]
           (format {:select :foo :from :bar
                    :offset 20}))))
  (testing "default offset / fetch"
    (is (= ["SELECT foo FROM bar OFFSET ? ROWS FETCH NEXT ? ROWS ONLY" 20 10]
           (format {:select :foo :from :bar
                    :fetch 10 :offset 20})))
    (is (= ["SELECT foo FROM bar OFFSET ? ROW FETCH NEXT ? ROW ONLY" 1 1]
           (format {:select :foo :from :bar
                    :fetch 1 :offset 1})))
    (is (= ["SELECT foo FROM bar FETCH FIRST ? ROWS ONLY" 2]
           (format {:select :foo :from :bar
                    :fetch 2}))))
  (testing "SQL Server offset"
    (is (= ["SELECT [foo] FROM [bar] OFFSET ? ROWS FETCH NEXT ? ROWS ONLY" 20 10]
           (format {:select :foo :from :bar
                    :fetch 10 :offset 20}
                   {:dialect :sqlserver})))
    (is (= ["SELECT [foo] FROM [bar] OFFSET ? ROWS" 20]
           (format {:select :foo :from :bar
                    :offset 20}
                   {:dialect :sqlserver})))))
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 returning-test
  (testing "returning clause in sql generation for postgresql"
    (is (= ["DELETE FROM distributors WHERE did > 10 RETURNING *"]
           (sql/format {:delete-from :distributors
                        :where [:> :did :10]
                        :returning [:*]})))
    (is (= ["UPDATE distributors SET dname = ? WHERE did = 2 RETURNING did, dname" "Foo Bar Designs"]
           (-> (update :distributors)
               (set {:dname "Foo Bar Designs"})
               (where [:= :did :2])
               (returning :did :dname)
               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)))))
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-select
  (testing "large helper expression"
    (let [m1 (-> (with [:cte (-> (select :*)
                                 (from :example)
                                 (where [:= :example-column 0]))])
                 (select-distinct :f.* :b.baz :c.quux [:b.bla "bla-bla"]
                                  :%now [[:raw "@x := 10"]])
                 (from [:foo :f] [:baz :b])
                 (join :draq [:= :f.b :draq.x])
                 (left-join [:clod :c] [:= :f.a :c.d])
                 (right-join :bock [:= :bock.z :c.e])
                 (full-join :beck [:= :beck.x :c.y])
                 (where [:or
                         [:and [:= :f.a "bort"] [:not= :b.baz :?param1]]
                         [:and [:< 1 2] [:< 2 3]]
                         [:in :f.e [1 [:param :param2] 3]]
                         [:between :f.e 10 20]])
                 (group-by :f.a)
                 (having [:< 0 :f.e])
                 (order-by [:b.baz :desc] :c.quux [:f.a :nulls-first])
                 (limit 50)
                 (offset 10))
          m2 {:with [[:cte {:select [:*]
                            :from [:example]
                            :where [:= :example-column 0]}]]
              :select-distinct [:f.* :b.baz :c.quux [:b.bla "bla-bla"]
                                :%now [[:raw "@x := 10"]]]
              :from [[:foo :f] [:baz :b]]
              :join [:draq [:= :f.b :draq.x]]
              :left-join [[:clod :c] [:= :f.a :c.d]]
              :right-join [:bock [:= :bock.z :c.e]]
              :full-join [:beck [:= :beck.x :c.y]]
              :where [:or
                      [:and [:= :f.a "bort"] [:not= :b.baz :?param1]]
                      [:and [:< 1 2] [:< 2 3]]
                      [:in :f.e [1 [:param :param2] 3]]
                      [:between :f.e 10 20]]
              :group-by [:f.a]
              :having [:< 0 :f.e]
              :order-by [[:b.baz :desc] :c.quux [:f.a :nulls-first]]
              :limit 50
              :offset 10}]
      (testing "Various construction methods are consistent"
        (is (= m1 m2)))
      (testing "SQL data formats correctly"
        (is (= ["WITH cte AS (SELECT * FROM example WHERE example_column = ?) SELECT DISTINCT f.*, b.baz, c.quux, b.bla AS \"bla-bla\", NOW(), @x := 10 FROM foo AS f, baz AS b INNER JOIN draq ON f.b = draq.x LEFT JOIN clod AS c ON f.a = c.d RIGHT JOIN bock ON bock.z = c.e FULL JOIN beck ON beck.x = c.y WHERE ((f.a = ?) AND (b.baz <> ?)) OR ((? < ?) AND (? < ?)) OR (f.e IN (?, ?, ?)) OR f.e BETWEEN ? AND ? GROUP BY f.a HAVING ? < f.e ORDER BY b.baz DESC, c.quux ASC, f.a NULLS FIRST LIMIT ? OFFSET ?"
                0 "bort" "gabba" 1 2 2 3 1 2 3 10 20 0 50 10]
               (sql/format m1 {:params {:param1 "gabba" :param2 2}}))))
      #?(:clj (testing "SQL data prints and reads correctly"
                (is (= m1 (read-string (pr-str m1))))))
      #_(testing "SQL data formats correctly with alternate param naming"
          (is (= (sql/format m1 {:params {:param1 "gabba" :param2 2}})
                 ["WITH cte AS (SELECT * FROM example WHERE example_column = $1) SELECT DISTINCT f.*, b.baz, c.quux, b.bla \"bla-bla\", NOW(), @x := 10 FROM foo AS f, baz AS b INNER JOIN draq ON f.b = draq.x LEFT JOIN clod AS c ON f.a = c.d RIGHT JOIN bock ON bock.z = c.e FULL JOIN beck ON beck.x = c.y WHERE ((f.a = $2) AND (b.baz <> $3)) OR (($4 < $5) AND ($6 < $7)) OR (f.e IN ($8, $9, $10)) OR f.e BETWEEN $11 AND $12 GROUP BY f.a HAVING $13 < f.e ORDER BY b.baz DESC, c.quux ASC, f.a NULLS FIRST LIMIT $14 OFFSET $15"
                  0 "bort" "gabba" 1 2 2 3 1 2 3 10 20 0 50 10])))
      (testing "Locking"
        (is (= ["WITH cte AS (SELECT * FROM example WHERE example_column = ?) SELECT DISTINCT f.*, b.baz, c.quux, b.bla AS `bla-bla`, NOW(), @x := 10 FROM foo AS f, baz AS b INNER JOIN draq ON f.b = draq.x LEFT JOIN clod AS c ON f.a = c.d RIGHT JOIN bock ON bock.z = c.e FULL JOIN beck ON beck.x = c.y WHERE ((f.a = ?) AND (b.baz <> ?)) OR ((? < ?) AND (? < ?)) OR (f.e IN (?, ?, ?)) OR f.e BETWEEN ? AND ? GROUP BY f.a HAVING ? < f.e ORDER BY b.baz DESC, c.quux ASC, f.a NULLS FIRST LIMIT ? OFFSET ? LOCK IN SHARE MODE"
                0 "bort" "gabba" 1 2 2 3 1 2 3 10 20 0 50 10]
               (sql/format (assoc m1 :lock [:in-share-mode])
                           {:params {:param1 "gabba" :param2 2}
                            ;; to enable :lock
                            :dialect :mysql :quoted false}))))))
  (testing "large helper expression with simplified where"
    (let [m1 (-> (with [:cte (-> (select :*)
                                 (from :example)
                                 (where := :example-column 0))])
                 (select-distinct :f.* :b.baz :c.quux [:b.bla "bla-bla"]
                                  :%now [[:raw "@x := 10"]])
                 (from [:foo :f] [:baz :b])
                 (join :draq [:= :f.b :draq.x])
                 (left-join [:clod :c] [:= :f.a :c.d])
                 (right-join :bock [:= :bock.z :c.e])
                 (full-join :beck [:= :beck.x :c.y])
                 (where :or
                        [:and [:= :f.a "bort"] [:not= :b.baz :?param1]]
                        [:and [:< 1 2] [:< 2 3]]
                        [:in :f.e [1 [:param :param2] 3]]
                        [:between :f.e 10 20])
                 (group-by :f.a)
                 (having :< 0 :f.e)
                 (order-by [:b.baz :desc] :c.quux [:f.a :nulls-first])
                 (limit 50)
                 (offset 10))
          m2 {:with [[:cte {:select [:*]
                            :from [:example]
                            :where [:= :example-column 0]}]]
              :select-distinct [:f.* :b.baz :c.quux [:b.bla "bla-bla"]
                                :%now [[:raw "@x := 10"]]]
              :from [[:foo :f] [:baz :b]]
              :join [:draq [:= :f.b :draq.x]]
              :left-join [[:clod :c] [:= :f.a :c.d]]
              :right-join [:bock [:= :bock.z :c.e]]
              :full-join [:beck [:= :beck.x :c.y]]
              :where [:or
                      [:and [:= :f.a "bort"] [:not= :b.baz :?param1]]
                      [:and [:< 1 2] [:< 2 3]]
                      [:in :f.e [1 [:param :param2] 3]]
                      [:between :f.e 10 20]]
              :group-by [:f.a]
              :having [:< 0 :f.e]
              :order-by [[:b.baz :desc] :c.quux [:f.a :nulls-first]]
              :limit 50
              :offset 10}]
      (testing "Various construction methods are consistent"
        (is (= m1 m2)))
      (testing "SQL data formats correctly"
        (is (= ["WITH cte AS (SELECT * FROM example WHERE example_column = ?) SELECT DISTINCT f.*, b.baz, c.quux, b.bla AS \"bla-bla\", NOW(), @x := 10 FROM foo AS f, baz AS b INNER JOIN draq ON f.b = draq.x LEFT JOIN clod AS c ON f.a = c.d RIGHT JOIN bock ON bock.z = c.e FULL JOIN beck ON beck.x = c.y WHERE ((f.a = ?) AND (b.baz <> ?)) OR ((? < ?) AND (? < ?)) OR (f.e IN (?, ?, ?)) OR f.e BETWEEN ? AND ? GROUP BY f.a HAVING ? < f.e ORDER BY b.baz DESC, c.quux ASC, f.a NULLS FIRST LIMIT ? OFFSET ?"
                0 "bort" "gabba" 1 2 2 3 1 2 3 10 20 0 50 10]
               (sql/format m1 {:params {:param1 "gabba" :param2 2}}))))
      #?(:clj (testing "SQL data prints and reads correctly"
                (is (= m1 (read-string (pr-str m1))))))
      #_(testing "SQL data formats correctly with alternate param naming"
          (is (= (sql/format m1 {:params {:param1 "gabba" :param2 2}})
                 ["WITH cte AS (SELECT * FROM example WHERE example_column = $1) SELECT DISTINCT f.*, b.baz, c.quux, b.bla \"bla-bla\", NOW(), @x := 10 FROM foo AS f, baz AS b INNER JOIN draq ON f.b = draq.x LEFT JOIN clod AS c ON f.a = c.d RIGHT JOIN bock ON bock.z = c.e FULL JOIN beck ON beck.x = c.y WHERE ((f.a = $2) AND (b.baz <> $3)) OR (($4 < $5) AND ($6 < $7)) OR (f.e IN ($8, $9, $10)) OR f.e BETWEEN $11 AND $12 GROUP BY f.a HAVING $13 < f.e ORDER BY b.baz DESC, c.quux ASC, f.a NULLS FIRST LIMIT $14 OFFSET $15"
                  0 "bort" "gabba" 1 2 2 3 1 2 3 10 20 0 50 10])))
      (testing "Locking"
        (is (= ["WITH cte AS (SELECT * FROM example WHERE example_column = ?) SELECT DISTINCT f.*, b.baz, c.quux, b.bla AS `bla-bla`, NOW(), @x := 10 FROM foo AS f, baz AS b INNER JOIN draq ON f.b = draq.x LEFT JOIN clod AS c ON f.a = c.d RIGHT JOIN bock ON bock.z = c.e FULL JOIN beck ON beck.x = c.y WHERE ((f.a = ?) AND (b.baz <> ?)) OR ((? < ?) AND (? < ?)) OR (f.e IN (?, ?, ?)) OR f.e BETWEEN ? AND ? GROUP BY f.a HAVING ? < f.e ORDER BY b.baz DESC, c.quux ASC, f.a NULLS FIRST LIMIT ? OFFSET ? LOCK IN SHARE MODE"
                0 "bort" "gabba" 1 2 2 3 1 2 3 10 20 0 50 10]
               (sql/format (assoc m1 :lock [:in-share-mode])
                           {:params {:param1 "gabba" :param2 2}
                            ;; to enable :lock
                            :dialect :mysql :quoted false})))))))

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

(defn my-update [& args] (h/generic-helper-unary    :update args))
(defn my-set    [& args] (h/generic-helper-unary    :set    args))
(defn my-where  [& args] (h/generic-helper-variadic :where  args))

(deftest custom-helpers-test
  (testing "nil join"
    (is (= ["UPDATE foo SET bar = ? WHERE quux = ?" 1 2]
           (-> (my-update :foo)
               (my-set {:bar 1})
               (my-where (sql/map= {:quux 2}))
               sql/format)))))

(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-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"])))
honeysql
(ns honey.cache-test
  (:refer-clojure :exclude [format group-by])
  (:require [clojure.core.cache.wrapped :as cache]
            [clojure.test :refer [deftest is]]
            [honey.sql :as sut]
            [honey.sql.helpers
             :refer [select-distinct from join left-join right-join where
                     group-by having order-by limit offset]]))

(def big-complicated-map
  (-> (select-distinct :f.* :b.baz :c.quux [:b.bla "bla-bla"]
                       [[:now]] [[:raw "@x := 10"]])
      (from [:foo :f] [:baz :b])
      (join :draq [:= :f.b :draq.x]
            :eldr [:= :f.e :eldr.t])
      (left-join [:clod :c] [:= :f.a :c.d])
      (right-join :bock [:= :bock.z :c.e])
      (where [:or
              [:and [:= :f.a "bort"] [:not= :b.baz [:param :param1]]]
              [:and [:< 1 2] [:< 2 3]]
              [:in :f.e [1 [:param :param2] 3]]
              [:between :f.e 10 20]])
      (group-by :f.a :c.e)
      (having [:< 0 :f.e])
      (order-by [:b.baz :desc] :c.quux [:f.a :nulls-first])
      (limit 50)
      (offset 10)))