Public Vars

Back

select (clj)

(source)

function

(select & exprs)
Accepts any number of column names, or column/alias pairs, or SQL expressions (optionally aliased): (select :id [:foo :bar] [[:max :quux]]) Produces: SELECT id, foo AS bar, MAX(quux) The special column name :* produces * for 'all columns'. You can also specify :t.* for 'all columns' from the table (or alias) t.

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 mysql-tests
  (is (= ["SELECT * FROM `table` WHERE `id` = ?" 1]
         (sut/format {:select [:*] :from [:table] :where [:= :id 1]}
                     {:dialect :mysql}))))

(deftest general-tests
  (is (= ["SELECT * FROM \"table\" WHERE \"id\" = ?" 1]
         (sut/format {:select [:*] :from [:table] :where [:= :id 1]} {:quoted true})))
  (is (= ["SELECT * FROM \"table\" WHERE \"id\" = ?" 1]
         (sut/format {:select [:*] :from [:table] :where (sut/map= {:id 1})} {:quoted true})))
  (is (= ["SELECT \"t\".* FROM \"table\" AS \"t\" WHERE \"id\" = ?" 1]
         (sut/format {:select [:t.*] :from [[:table :t]] :where [:= :id 1]} {:quoted true})))
  (is (= ["SELECT * FROM \"table\" GROUP BY \"foo\""]
         (sut/format {:select [:*] :from [:table] :group-by :foo} {:quoted true})))
  (is (= ["SELECT * FROM \"table\" GROUP BY \"foo\", \"bar\""]
         (sut/format {:select [:*] :from [:table] :group-by [:foo :bar]} {:quoted true})))
  (is (= ["SELECT * FROM \"table\" GROUP BY DATE(\"bar\")"]
         (sut/format {:select [:*] :from [:table] :group-by [[:date :bar]]} {:quoted true})))
  (is (= ["SELECT * FROM \"table\" ORDER BY \"foo\" ASC"]
         (sut/format {:select [:*] :from [:table] :order-by :foo} {:quoted true})))
  (is (= ["SELECT * FROM \"table\" ORDER BY \"foo\" DESC, \"bar\" ASC"]
         (sut/format {:select [:*] :from [:table] :order-by [[:foo :desc] :bar]} {:quoted true})))
  (is (= ["SELECT * FROM \"table\" ORDER BY DATE(\"expiry\") DESC, \"bar\" ASC"]
         (sut/format {:select [:*] :from [:table] :order-by [[[:date :expiry] :desc] :bar]} {:quoted true})))
  (is (= ["SELECT * FROM \"table\" WHERE DATE_ADD(\"expiry\", INTERVAL ? DAYS) < NOW()" 30]
         (sut/format {:select [:*] :from [:table] :where [:< [:date_add :expiry [:interval 30 :days]] [:now]]} {:quoted true})))
  (is (= ["SELECT * FROM \"table\" WHERE DATE_ADD(\"expiry\", INTERVAL '30 Days') < NOW()"]
         (sut/format {:select [:*] :from [:table] :where [:< [:date_add :expiry [:interval "30 Days"]] [:now]]} {:quoted true})))
  (is (= ["SELECT * FROM `table` WHERE `id` = ?" 1]
         (sut/format {:select [:*] :from [:table] :where [:= :id 1]} {:dialect :mysql})))
  (is (= ["SELECT * FROM \"table\" WHERE \"id\" IN (?, ?, ?, ?)" 1 2 3 4]
         (sut/format {:select [:*] :from [:table] :where [:in :id [1 2 3 4]]} {:quoted true}))))

(deftest general-numbered-tests
  (is (= ["SELECT * FROM \"table\" WHERE \"id\" = $1" 1]
         (sut/format {:select [:*] :from [:table] :where [:= :id 1]}
                     {:quoted true :numbered true})))
  (is (= ["SELECT * FROM \"table\" WHERE \"id\" = $1" 1]
         (sut/format {:select [:*] :from [:table] :where (sut/map= {:id 1})}
                     {:quoted true :numbered true})))
  (is (= ["SELECT \"t\".* FROM \"table\" AS \"t\" WHERE \"id\" = $1" 1]
         (sut/format {:select [:t.*] :from [[:table :t]] :where [:= :id 1]}
                     {:quoted true :numbered true})))
  (is (= ["SELECT * FROM \"table\" GROUP BY \"foo\", \"bar\""]
         (sut/format {:select [:*] :from [:table] :group-by [:foo :bar]}
                     {:quoted true :numbered true})))
  (is (= ["SELECT * FROM \"table\" GROUP BY DATE(\"bar\")"]
         (sut/format {:select [:*] :from [:table] :group-by [[:date :bar]]}
                     {:quoted true :numbered true})))
  (is (= ["SELECT * FROM \"table\" ORDER BY \"foo\" DESC, \"bar\" ASC"]
         (sut/format {:select [:*] :from [:table] :order-by [[:foo :desc] :bar]}
                     {:quoted true :numbered true})))
  (is (= ["SELECT * FROM \"table\" ORDER BY DATE(\"expiry\") DESC, \"bar\" ASC"]
         (sut/format {:select [:*] :from [:table] :order-by [[[:date :expiry] :desc] :bar]}
                     {:quoted true :numbered true})))
  (is (= ["SELECT * FROM \"table\" WHERE DATE_ADD(\"expiry\", INTERVAL $1 DAYS) < NOW()" 30]
         (sut/format {:select [:*] :from [:table] :where [:< [:date_add :expiry [:interval 30 :days]] [:now]]}
                     {:quoted true :numbered true})))
  (is (= ["SELECT * FROM \"table\" WHERE DATE_ADD(\"expiry\", INTERVAL '30 Days') < NOW()"]
         (sut/format {:select [:*] :from [:table] :where [:< [:date_add :expiry [:interval "30 Days"]] [:now]]}
                     {:quoted true :numbered true})))
  (is (= ["SELECT * FROM `table` WHERE `id` = $1" 1]
         (sut/format {:select [:*] :from [:table] :where [:= :id 1]}
                     {:dialect :mysql :numbered true})))
  (is (= ["SELECT * FROM \"table\" WHERE \"id\" IN ($1, $2, $3, $4)" 1 2 3 4]
         (sut/format {:select [:*] :from [:table] :where [:in :id [1 2 3 4]]}
                     {:quoted true :numbered true}))))

(deftest subquery-alias-263
  (is (= ["SELECT type FROM (SELECT address AS field_alias FROM Candidate) AS sub_q_alias"]
         (sut/format {:select [:type]
                      :from [[{:select [[:address :field-alias]]
                               :from [:Candidate]} :sub_q_alias]]})))
  (is (= ["SELECT type FROM (SELECT address field_alias FROM Candidate) sub_q_alias"]
         (sut/format {:select [:type]
                      :from [[{:select [[:address :field-alias]]
                               :from [:Candidate]} :sub-q-alias]]}
                     {:dialect :oracle :quoted false}))))

(deftest alias-splitting
  (is (= ["SELECT `aa`.`c` AS `a.c`, `bb`.`c` AS `b.c`, `cc`.`c` AS `c.c`"]
         (format {:select [[:aa.c "a.c"]
                           [:bb.c :b.c]
                           [:cc.c 'c.c]]}
                 {:dialect :mysql}))
      "aliases containing \".\" are quoted as necessary but not split"))

(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
  (is (= (format {:insert-into :foo})
         ["INSERT INTO foo"]))
  (is (= (format {:insert-into [:foo {:select [:bar] :from [:baz]}]})
         ["INSERT INTO foo SELECT bar FROM baz"]))
  (is (= (format {:insert-into [[:foo [:a :b :c]] {:select [:d :e :f] :from [:baz]}]})
         ["INSERT INTO foo (a, b, c) SELECT d, e, f FROM baz"]))
  (is (= (format {:insert-into [[:foo [:a :b :c]] {:select [:d :e :f] :from [:baz]}]})
         ["INSERT INTO foo (a, b, c) SELECT d, e, f FROM baz"])))

(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 exists-test
  ;; EXISTS should never have been implemented as SQL syntax: it's an operator!
  #_(is (= (format {:exists {:select [:a] :from [:foo]}})
           ["EXISTS (SELECT a FROM foo)"]))
  ;; select function call with an alias:
  (is (= (format {:select [[[:exists {:select [:a] :from [:foo]}] :x]]})
         ["SELECT EXISTS (SELECT a FROM foo) AS x"]))
  ;; select function call with no alias required:
  (is (= (format {:select [[[:exists {:select [:a] :from [:foo]}]]]})
         ["SELECT EXISTS (SELECT a FROM foo)"]))
  (is (= (format {:select [:id]
                  :from [:foo]
                  :where [:exists {:select [1]
                                   :from [:bar]
                                   :where :deleted}]})
         ["SELECT id FROM foo WHERE EXISTS (SELECT ? FROM bar WHERE deleted)" 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)"]))))

(deftest union-test
  ;; UNION and INTERSECT subexpressions should not be parenthesized.
  ;; If you need to add more complex expressions, use a subquery like this:
  ;;   SELECT foo FROM bar1
  ;;   UNION
  ;;   SELECT foo FROM (SELECT foo FROM bar2 ORDER BY baz LIMIT 2)
  ;;   ORDER BY foo ASC
  (is (= (format {:union [{:select [:foo] :from [:bar1]}
                          {:select [:foo] :from [:bar2]}]})
         ["SELECT foo FROM bar1 UNION SELECT foo FROM bar2"]))

  (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-all-test
  (is (= (format {:union-all [{:select [:foo] :from [:bar1]}
                              {:select [:foo] :from [:bar2]}]})
         ["SELECT foo FROM bar1 UNION ALL SELECT foo FROM bar2"])))

(deftest intersect-test
  (is (= (format {:intersect [{:select [:foo] :from [:bar1]}
                              {:select [:foo] :from [:bar2]}]})
         ["SELECT foo FROM bar1 INTERSECT SELECT foo FROM bar2"])))

(deftest except-test
  (is (= (format {:except [{:select [:foo] :from [:bar1]}
                           {:select [:foo] :from [:bar2]}]})
         ["SELECT foo FROM bar1 EXCEPT SELECT foo FROM bar2"])))

(deftest inner-parts-test
  (testing "The correct way to apply ORDER BY to various parts of a UNION"
    (is (= (format
            {:union
             [{:select [:amount :id :created_on]
               :from [:transactions]}
              {:select [:amount :id :created_on]
               :from [{:select [:amount :id :created_on]
                       :from [:other_transactions]
                       :order-by [[:amount :desc]]
                       :limit 5}]}]
             :order-by [[:amount :asc]]})
           ["SELECT amount, id, created_on FROM transactions UNION SELECT amount, id, created_on FROM (SELECT amount, id, created_on FROM other_transactions ORDER BY amount DESC LIMIT ?) ORDER BY amount ASC" 5]))))

(deftest compare-expressions-test
  (testing "Sequences should be fns when in value/comparison spots"
    (is (= ["SELECT foo FROM bar WHERE (col1 MOD ?) = (col2 + ?)" 4 4]
           (format {:select [:foo]
                    :from [:bar]
                    :where [:= [:mod :col1 4] [:+ :col2 4]]}))))

  (testing "Example from dharrigan"
    (is (= ["SELECT PG_TRY_ADVISORY_LOCK(1)"]
           (format {:select [:%pg_try_advisory_lock.1]}))))

  (testing "Value context only applies to sequences in value/comparison spots"
    (let [sub {:select [:%sum.amount]
               :from [:bar]
               :where [:in :id ["id-1" "id-2"]]}]
      (is (= ["SELECT total FROM foo WHERE (SELECT SUM(amount) FROM bar WHERE id IN (?, ?)) = total" "id-1" "id-2"]
             (format {:select [:total]
                      :from [:foo]
                      :where [:= sub :total]})))
      (is (= ["WITH t AS (SELECT SUM(amount) FROM bar WHERE id IN (?, ?)) SELECT total FROM foo WHERE total = t" "id-1" "id-2"]
             (format {:with [[:t sub]]
                      :select [:total]
                      :from [:foo]
                      :where [:= :total :t]}))))))

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

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

  (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 similar-regex-tests
  (testing "basic similar to"
    (is (= (format {:select :* :from :foo
                    :where [:similar-to :foo [:escape "bar" [:inline  "*"]]]})
           ["SELECT * FROM foo WHERE foo SIMILAR TO ? ESCAPE '*'" "bar"]))))

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

;; 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 join-on-true-253
  ;; used to work on honeysql 0.9.2; broke in 0.9.3
  (is (= ["SELECT foo FROM bar INNER JOIN table AS t ON TRUE"]
         (format {:select [:foo]
                  :from [:bar]
                  :join [[:table :t] true]}))))

(deftest cross-join-test
  (is (= ["SELECT * FROM foo CROSS JOIN bar"]
         (format {:select [:*]
                  :from [:foo]
                  :cross-join [:bar]})))
  (is (= ["SELECT * FROM foo AS f CROSS JOIN bar b"]
         (format {:select [:*]
                  :from [[:foo :f]]
                  :cross-join [[:bar :b]]}))))

(deftest locking-select-tests
  (testing "PostgreSQL/ANSI FOR"
    (is (= ["SELECT * FROM foo FOR UPDATE"]
           (format {:select [:*] :from :foo :for :update})))
    (is (= ["SELECT * FROM foo FOR NO KEY UPDATE"]
           (format {:select [:*] :from :foo :for :no-key-update})))
    (is (= ["SELECT * FROM foo FOR SHARE"]
           (format {:select [:*] :from :foo :for :share})))
    (is (= ["SELECT * FROM foo FOR KEY SHARE"]
           (format {:select [:*] :from :foo :for :key-share})))
    (is (= ["SELECT * FROM foo FOR UPDATE"]
           (format {:select [:*] :from :foo :for [:update]})))
    (is (= ["SELECT * FROM foo FOR NO KEY UPDATE"]
           (format {:select [:*] :from :foo :for [:no-key-update]})))
    (is (= ["SELECT * FROM foo FOR SHARE"]
           (format {:select [:*] :from :foo :for [:share]})))
    (is (= ["SELECT * FROM foo FOR KEY SHARE"]
           (format {:select [:*] :from :foo :for [:key-share]})))
    (is (= ["SELECT * FROM foo FOR UPDATE NOWAIT"]
           (format {:select [:*] :from :foo :for [:update :nowait]})))
    (is (= ["SELECT * FROM foo FOR UPDATE OF bar NOWAIT"]
           (format {:select [:*] :from :foo :for [:update :bar :nowait]})))
    (is (= ["SELECT * FROM foo FOR UPDATE WAIT"]
           (format {:select [:*] :from :foo :for [:update :wait]})))
    (is (= ["SELECT * FROM foo FOR UPDATE OF bar WAIT"]
           (format {:select [:*] :from :foo :for [:update :bar :wait]})))
    (is (= ["SELECT * FROM foo FOR UPDATE SKIP LOCKED"]
           (format {:select [:*] :from :foo :for [:update :skip-locked]})))
    (is (= ["SELECT * FROM foo FOR UPDATE OF bar SKIP LOCKED"]
           (format {:select [:*] :from :foo :for [:update :bar :skip-locked]})))
    (is (= ["SELECT * FROM foo FOR UPDATE OF bar, quux"]
           (format {:select [:*] :from :foo :for [:update [:bar :quux]]}))))
  (testing "MySQL for/lock"
    ;; these examples come from:
    (is (= ["SELECT * FROM t1 WHERE c1 = (SELECT c1 FROM t2) FOR UPDATE"] ; portable
           (format {:select [:*] :from :t1
                    :where [:= :c1 {:select [:c1] :from :t2}]
                    :for [:update]})))
    (is (= ["SELECT * FROM t1 WHERE c1 = (SELECT c1 FROM t2 FOR UPDATE) FOR UPDATE"]
           (format {:select [:*] :from :t1
                    :where [:= :c1 {:select [:c1] :from :t2 :for [:update]}]
                    :for [:update]})))
    (is (= ["SELECT * FROM foo WHERE name = 'Jones' LOCK IN SHARE MODE"] ; MySQL-specific
           (format {:select [:*] :from :foo
                    :where [:= :name [:inline "Jones"]]
                    :lock [:in-share-mode]}
                   {:dialect :mysql :quoted false})))))

(deftest issue-285
  (is (= ["
SELECT *
FROM processes
WHERE state = ?
ORDER BY id = ? DESC
" 42 123]
         (format (-> (h/select :*)
                     (h/from :processes)
                     (h/where [:= :state 42])
                     (h/order-by [[:= :id 123] :desc]))
                 {:pretty true}))))

(deftest issue-316-test
  ;; this is a pretty naive test -- there are other tricks to perform injection
  ;; that are not detected by HoneySQL and you should generally use :quoted true
  (testing "SQL injection via keyword is detected"
    (let [sort-column "foo; select * from users"]
      (try
        (-> {:select [:foo :bar]
             :from [:mytable]
             :order-by [(keyword sort-column)]}
            (format))
        (is false "; not detected in entity!")
        (catch #?(:cljs :default :default Exception) e
          (is (:disallowed (ex-data e))))))))
    ;; should not produce: ["SELECT foo, bar FROM mytable ORDER BY foo; select * from users"]

(deftest issue-401-dialect
  (testing "registering a dialect that upper-cases idents"
    (sut/register-dialect! ::MYSQL (update (sut/get-dialect :mysql) :quote comp sut/upper-case))
    (is (= ["SELECT `foo` FROM `bar`"]
           (sut/format {:select :foo :from :bar} {:dialect :mysql})))
    (is (= ["SELECT `FOO` FROM `BAR`"]
           (sut/format {:select :foo :from :bar} {:dialect ::MYSQL})))))

(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 join-without-on-using
  ;; essentially issue 326
  (testing "join does not need on or using"
    (is (= ["SELECT foo FROM bar INNER JOIN quux"]
           (format {:select :foo
                    :from :bar
                    :join [:quux]}))))
  (testing "join on select with parameters"
    (is (= ["SELECT foo FROM bar INNER JOIN (SELECT a FROM b WHERE id = ?) WHERE id = ?" 123 456]
           (format {:select :foo
                    :from :bar
                    :join [{:select :a :from :b :where [:= :id 123]}]
                    :where [:= :id 456]})))
    (is (= ["SELECT foo FROM bar INNER JOIN (SELECT a FROM b WHERE id = ?) AS x WHERE id = ?" 123 456]
           (format {:select :foo
                    :from :bar
                    :join [[{:select :a :from :b :where [:= :id 123]} :x]]
                    :where [:= :id 456]})))
    (is (= ["SELECT foo FROM bar INNER JOIN (SELECT a FROM b WHERE id = ?) AS x ON y WHERE id = ?" 123 456]
           (format {:select :foo
                    :from :bar
                    :join [[{:select :a :from :b :where [:= :id 123]} :x] :y]
                    :where [:= :id 456]})))))

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

(deftest issue-394-quoting
  (is (= ["SELECT \"A\"\"B\""] (sut/format {:select (keyword "A\"B")} {:quoted true})))
  (is (= ["SELECT \"A\"\"B\""] (sut/format {:select (keyword "A\"B")} {:dialect :ansi})))
  (is (= ["SELECT [A\"B]"]     (sut/format {:select (keyword "A\"B")} {:dialect :sqlserver})))
  (is (= ["SELECT [A]]B]"]     (sut/format {:select (keyword "A]B")} {:dialect :sqlserver})))
  (is (= ["SELECT `A\"B`"]     (sut/format {:select (keyword "A\"B")} {:dialect :mysql})))
  (is (= ["SELECT `A``B`"]     (sut/format {:select (keyword "A`B")} {:dialect :mysql})))
  (is (= ["SELECT \"A\"\"B\""] (sut/format {:select (keyword "A\"B")} {:dialect :oracle}))))

(deftest issue-407-temporal
  (is (= ["SELECT f.* FROM foo FOR SYSTEM_TIME ALL AS f WHERE f.id = ?" 1]
         (sut/format {:select :f.* :from [[:foo :f :for :system-time :all]] :where [:= :f.id 1]})))
  (is (= ["SELECT * FROM foo FOR SYSTEM_TIME ALL WHERE id = ?" 1]
         (sut/format {:select :* :from [[:foo :for :system-time :all]] :where [:= :id 1]}))))

(deftest issue-422-quoting
  ;; default quote if strange entity:
  (is (= ["SELECT A, \"B C\""] (sut/format {:select [:A (keyword "B C")]})))
  ;; default don't quote normal entity:
  (is (= ["SELECT A, B_C"]     (sut/format {:select [:A (keyword "B_C")]})))
  ;; quote all entities when quoting enabled:
  (is (= ["SELECT \"A\", \"B C\""] (sut/format {:select [:A (keyword "B C")]}
                                               {:quoted true})))
  ;; don't quote if quoting disabled (illegal SQL):
  (is (= ["SELECT A, B C"]     (sut/format {:select [:A (keyword "B C")]}
                                           {:quoted false}))))

(deftest issue-434-case-quoting
  (is (= ["SELECT ARRAY (SELECT \"oid\" FROM \"pg_proc\" WHERE \"proname\" LIKE 'bytea%')"]
         (sut/format {:select [[[:'ARRAY {:select :oid :from :pg_proc :where [:like :proname [:inline "bytea%"]]}]]]} :quoted true))))

(deftest issue-459-variadic-ops
  (sut/register-op! :op)
  (is (= ["SELECT a"] ; not unary!
         (sut/format {:select [[[:op :a]]]})))
  (is (= ["SELECT a OP b"]
         (sut/format {:select [[[:op :a :b]]]})))
  (is (= ["SELECT a OP b OP c"]
         (sut/format {:select [[[:op :a :b :c]]]}))))

(deftest issue-461-unary-ops
  (is (= ["SELECT TRUE"]
         (sut/format {:select [[[:and true]]]})))
  (is (= ["SELECT TRUE"]
         (sut/format {:select [[[:or true]]]})))
  (is (= ["SELECT ?" 1]
         (sut/format {:select [[[:* 1]]]})))
  (is (= ["SELECT TRUE AND a AND b"]
         (sut/format {:select [[[:and true :a :b]]]})))
  (is (= ["SELECT TRUE OR a OR b"]
         (sut/format {:select [[[:or true :a :b]]]})))
  (is (= ["SELECT ? * ? * ?" 1 2 3]
         (sut/format {:select [[[:* 1 2 3]]]})))
  ;; but these three genuinely are unary:
  (is (= ["SELECT + ?" 1]
         (sut/format {:select [[[:+ 1]]]})))
  (is (= ["SELECT - ?" 1]
         (sut/format {:select [[[:- 1]]]})))
  (is (= ["SELECT ~ ?" 1] ; bitwise negation
         (sut/format {:select [[[(keyword "~") 1]]]})))
  ;; and can still be used as variadic:
  (is (= ["SELECT ? + ?" 1 2]
         (sut/format {:select [[[:+ 1 2]]]})))
  (is (= ["SELECT ? - ?" 1 2]
         (sut/format {:select [[[:- 1 2]]]})))
  (is (= ["SELECT ? ~ ?" "a" "b"] ; regex op
         (sut/format {:select [[[(keyword "~") "a" "b"]]]}))))

(deftest issue-471-interspersed-kws
  (testing "overlay"
    (is (= ["SELECT OVERLAY(foo PLACING ? FROM ? FOR ?)"
            "bar" 3 4]
           (sut/format {:select [[[:overlay :foo :!placing "bar" :!from 3 :!for 4]]]}))))
  (testing "position"
    (is (= ["SELECT POSITION(? IN bar)" "foo"]
           (sut/format {:select [[[:position "foo" :!in :bar]]]}))))
  (testing "trim"
    (is (= ["SELECT TRIM(LEADING FROM bar)"]
           (sut/format {:select [[[:trim :!leading :!from :bar]]]})))
    (is (= ["SELECT TRIM(LEADING FROM bar)"]
           (sut/format {:select [[[:trim :!leading-from :bar]]]}))))
  (testing "extract"
    (is (= ["SELECT EXTRACT(CENTURY FROM TIMESTAMP '2000-12-16 12:21:13')"]
           (sut/format {:select [[[:extract :!century :!from
                                   :!timestamp [:inline "2000-12-16 12:21:13"]]]]}))))
  (testing "xmlelement"
    (is (= ["SELECT XMLELEMENT(NAME \"foo$bar\", XMLATTRIBUTES('xyz' AS \"a&b\"))"]
           (sut/format {:select [[[:xmlelement :!name :foo$bar
                                   [:xmlattributes [:inline "xyz"] :!as :a&b]]]]})))))

(deftest issue-474-dot-selection
  (testing "basic dot selection"
    (is (= ["SELECT a.b, c.d, a.d.x"]
           (let [t :a c :d]
             (sut/format {:select [[[:. t :b]] [[:. :c c]] [[:. t c :x]]]}))))
    (is (= ["SELECT [a].[b], [c].[d], [a].[d].[x]"]
           (let [t :a c :d]
             (sut/format {:select [[[:. t :b]] [[:. :c c]] [[:. t c :x]]]}
                         {:dialect :sqlserver})))))
  (testing "basic field selection from composite"
    (is (= ["SELECT (v).*, (w).x, (Y(z)).*"]
           (sut/format '{select (((. (nest v) *))
                                 ((. (nest w) x))
                                 ((. (nest (y z)) *)))})))
    (is (= ["SELECT (`v`).*, (`w`).`x`, (Y(`z`)).*"]
           (sut/format '{select (((. (nest v) *))
                                 ((. (nest w) x))
                                 ((. (nest (y z)) *)))}
                       {:dialect :mysql})))))

(deftest issue-483-join
  (testing "single nested join"
    (is (= ["SELECT * FROM tbl1 LEFT JOIN (tbl2 INNER JOIN tbl3 USING (common_column)) ON (tbl2.col2 = tbl1.col2) AND (tbl3.col3 = tbl1.col3)"]
           (-> {:select :*
                :from :tbl1
                :left-join [[[:join :tbl2 {:join [:tbl3 [:using [:common_column]]]}]]
                            [:and
                             [:= :tbl2.col2 :tbl1.col2]
                             [:= :tbl3.col3 :tbl1.col3]]]}
               (sut/format)))))
  (testing "multiple nested join"
    (is (= ["SELECT * FROM tbl1 LEFT JOIN (tbl2 INNER JOIN tbl3 USING (common_column) RIGHT JOIN tbl4 USING (id)) ON (tbl2.col2 = tbl1.col2) AND (tbl3.col3 = tbl1.col3)"]
           (-> {:select :*
                :from :tbl1
                :left-join [[[:join :tbl2
                              {:join [:tbl3 [:using [:common_column]]]}
                              {:right-join [:tbl4 [:using :id]]}]]
                            [:and
                             [:= :tbl2.col2 :tbl1.col2]
                             [:= :tbl3.col3 :tbl1.col3]]]}
               (sut/format)))))
  (testing "special syntax example"
    (is (= ["INNER JOIN (tbl1 LEFT JOIN tbl2 USING (id))"]
           (sut/format {:join [[[:join :tbl1 {:left-join [:tbl2 [:using :id]]}]]]})))))

  (is (= ["SELECT column_name AS \"some-alias\" FROM b ORDER BY \"some-alias\" ASC"]
         (sut/format {:select [[:column-name "some-alias"]]
                      :from :b
                      :order-by [[[:raw "\"some-alias\""]]]})))
  ;; likely illegal SQL, but shows quoted keyword escaping the -/_ replace:
  (is (= ["SELECT column_name AS \"some-alias\" FROM b ORDER BY some-alias ASC"]
         (sut/format {:select [[:column-name "some-alias"]]
                      :from :b
                      :order-by [[:'some-alias]]})))
  (is (= ["SELECT column_name AS \"some-alias\" FROM b ORDER BY \"some-alias\" ASC"]
         (sut/format {:select [[:column-name "some-alias"]]
                      :from :b
                      :order-by [[[:alias "some-alias"]]]})))
  (is (= ["SELECT column_name AS \"some-alias\" FROM b ORDER BY some_alias ASC"]
         (sut/format {:select [[:column-name "some-alias"]]
                      :from :b
                      :order-by [[[:alias :some-alias]]]})))
  (is (= ["SELECT column_name AS \"some-alias\" FROM b ORDER BY \"some-alias\" ASC"]
         (sut/format {:select [[:column-name "some-alias"]]
                      :from :b
                      :order-by [[[:alias :'some-alias]]]})))
  (is (= ["SELECT column_name AS \"some-alias\" FROM b ORDER BY \"some-alias\" ASC"]
         (sut/format {:select [[:column-name "some-alias"]]
                      :from :b
                      :order-by [[[:alias "some-alias"]]]})))
  (is (= ["SELECT \"column-name\" AS \"some-alias\" FROM \"b\" ORDER BY ? ASC"
          "some-alias"]
         (sut/format {:select [[:column-name "some-alias"]]
                      :from :b
                      :order-by ["some-alias"]}
                     {:quoted true})))
  (is (= ["SELECT `column-name` AS `some-alias` FROM `b` ORDER BY `some-alias` ASC"]
         (sut/format {:select [[:column-name "some-alias"]]
                      :from :b
                      :order-by [[[:alias "some-alias"]]]}
                     {:dialect :mysql}))))

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

(deftest at-time-zone-503
  (is (= ["SELECT foo AT TIME ZONE 'UTC'"]
         (sut/format {:select [[[:at-time-zone :foo "UTC"]]]})))
  (is (= ["SELECT foo AT TIME ZONE 'UTC'"]
         (sut/format {:select [[[:at-time-zone :foo :UTC]]]})))
  (is (= ["SELECT FOO(bar) AT TIME ZONE 'UTC'"]
         (sut/format {:select [[[:at-time-zone [:foo :bar] :UTC]]]}))))

(deftest issue-512
  (testing "select with metadata"
    (is (= ["SELECT DISTINCT * FROM table"]
           (sut/format {:select-distinct [:*] :from [:table]})))
    (is (= ["SELECT DISTINCT * FROM table"]
           (sut/format {:select ^{:distinct true} [:*] :from [:table]})))
    (is (= ["SELECT DISTINCT * FROM table"]
           (sut/format {:select ^:distinct [:*] :from [:table]})))))

(deftest issue-515
  (testing ":always-quoting option"
    (is (= ["SELECT foo FROM table"]
           (sut/format '{select foo from table})))
    (is (= ["SELECT \"foo\" FROM \"table\""]
           (sut/format '{select foo from table}
                       {:quoted-always #"^(foo|table)$"})))
    (is (= ["SELECT \"foo\" FROM \"table\""]
           (sut/format '{select foo from table}
                       {:quoted-always #"^(foo|table)$"
                        :quoted false})))
    (is (= ["SELECT \"foo\" FROM table"]
           (sut/format '{select foo from table}
                       {:quoted-always #"^(foo)$"
                        :quoted false})))))

(deftest issue-520
  (testing ":inline with a single argument"
    (is (= ["SELECT 42 AS x"]
           (sut/format '{select [[[inline 42] x]]}))))
  (testing ":inline with multiple arguments"
    (is (= ["SELECT DATE '2024-01-06' AS x"]
           (sut/format '{select [[[inline DATE "2024-01-06"] x]]}))))
  (testing ":inline with a parameter"
    (is (= ["SELECT 42 AS x"]
           (sut/format '{select [[[inline [param foo]] x]]}
                       {:params {'foo 42}}))))
  (testing ":inline with a sequence"
    (is (= ["SELECT ('a', 'b', 'c') AS x"]
           (sut/format '{select [[[inline ["a" "b" "c"]] x]]}))))
  (testing ":inline with a lifted sequence"
    (is (= ["SELECT ['a', 'b', 'c'] AS x"]
           (sut/format '{select [[[inline [lift ["a" "b" "c"]]] x]]})))))

(deftest issue-522
  (testing "from with metadata"
    (is (= ["SELECT * FROM table WITH (HINT)"]
           (sut/format {:select [:*] :from [^:hint [:table]]})))
    ;; hash map (metadata) is unordered:
    (is (or (= ["SELECT * FROM table WITH (ABC, DEF)"]
               (sut/format {:select [:*] :from [^:abc ^:def [:table]]}))
            (= ["SELECT * FROM table WITH (DEF, ABC)"]
               (sut/format {:select [:*] :from [^:abc ^:def [:table]]}))))
    (is (or (= ["SELECT * FROM table WITH (ABC, DEF)"]
               (sut/format {:select [:*] :from [^{:abc true :def true} [:table]]}))
            (= ["SELECT * FROM table WITH (DEF, ABC)"]
               (sut/format {:select [:*] :from [^{:abc true :def true} [:table]]}))))))

(comment
  ;; partial (incorrect!) workaround for #407:
  (sut/format {:select :f.* :from [[:foo [:f :for :system-time]]] :where [:= :f.id 1]})
  ;; correct version:
  (sut/format {:select :f.* :from [[:foo :f :for :system-time]] :where [:= :f.id 1]})
  (sut/format {:where [:= :x [:inline :DATE "2019-01-01"]]})
  )
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-view-test
  (testing "creating a view from a table"
    (is (= ["CREATE VIEW metro AS SELECT * FROM cities WHERE metroflag = ?" "Y"]
           (-> (create-view :metro)
               (select :*)
               (from :cities)
               (where [:= :metroflag "Y"])
               sql/format)))))

(deftest over-test
  (testing "simple window statement"
    (is (= ["SELECT AVG(salary) OVER w FROM employee WINDOW w AS (PARTITION BY department ORDER BY salary ASC)"]
           (sql/format {:select [[[:over [[:avg :salary] :w]]]]
                        :from   :employee
                        :window [:w {:partition-by :department
                                     :order-by     :salary}]}))))
  (testing "window function over on select statemt"
    (is (= ["SELECT id, AVG(salary) OVER (PARTITION BY department ORDER BY designation ASC) AS Average, MAX(salary) OVER w AS MaxSalary FROM employee WINDOW w AS (PARTITION BY department)"]
           ;; honeysql treats over as a function:
           (-> (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)
           ;; nilenso treated over as a clause
           #_(-> (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)))))

(deftest select-where-ilike
  (testing "select from table with ILIKE operator"
    (is (= ["SELECT * FROM products WHERE name ILIKE ?" "%name%"]
           (-> (select :*)
               (from :products)
               (where [:ilike :name "%name%"])
               sql/format)))))

(deftest select-where-not-ilike
  (testing "select from table with NOT ILIKE operator"
    (is (= ["SELECT * FROM products WHERE name NOT ILIKE ?" "%name%"]
           (-> (select :*)
               (from :products)
               (where [:not-ilike :name "%name%"])
               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 select-except-select
  (testing "select which rows are not present in another table"
    (is (= ["SELECT ip EXCEPT SELECT ip FROM ip_location"]
           (sql/format
            {:except
             [{:select [:ip]}
              {:select [:ip] :from [:ip_location]}]})))))

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

(deftest select-except-all-select
  (testing "select which rows are not present in another table"
    (is (= ["SELECT ip EXCEPT ALL SELECT ip FROM ip_location"]
           (sql/format
            {:except-all
             [{:select [:ip]}
              {:select [:ip] :from [:ip_location]}]})))))

(deftest select-distinct-on-test
  (testing "select distinct on"
    (is (= ["SELECT DISTINCT ON(\"a\", \"b\") \"c\" FROM \"products\""]
           ;; honeysql has select-distinct-on:
           (-> (select-distinct-on [:a :b] :c)
               (from :products)
               (sql/format {:quoted true}))
           ;; nilenso handled that via modifiers:
           #_(-> (select :c)
                 (from :products)
                 (modifiers :distinct-on :a :b)
                 (sql/format :quoting :ansi))))))

(deftest select-agg-order-by-test
  (testing "single expression in order by"
    (is (= ["SELECT ARRAY_AGG(a ORDER BY x) FROM products"])
        (sql/format
          {:select [[[:array_agg [:order-by :a :x]]]]
           :from   :products})))
  (testing "multiple expressions in order by"
    (is (= ["SELECT ARRAY_AGG(a ORDER BY x ASC, y DESC, z ASC) FROM products"]
           (sql/format
             {:select [[[:array_agg [:order-by :a [:x :asc] [:y :desc] :z]]]]
              :from   :products})))))
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 select-top-tests
  (testing "Basic TOP syntax"
    (is (= ["SELECT TOP(?) foo FROM bar ORDER BY quux ASC" 10]
           (sql/format {:select-top [10 :foo] :from :bar :order-by :quux})))
    (is (= ["SELECT TOP(?) foo FROM bar ORDER BY quux ASC" 10]
           (sql/format (-> (select-top 10 :foo)
                           (from :bar)
                           (order-by :quux))))))
  (testing "Expanded TOP syntax"
    (is (= ["SELECT TOP(?) PERCENT WITH TIES foo, baz FROM bar ORDER BY quux ASC" 10]
           (sql/format {:select-top [[10 :percent :with-ties] :foo :baz] :from :bar :order-by :quux})))
    (is (= ["SELECT TOP(?) PERCENT WITH TIES foo, baz FROM bar ORDER BY quux ASC" 10]
           (sql/format (-> (select-top [10 :percent :with-ties] :foo :baz)
                           (from :bar)
                           (order-by :quux)))))))

(deftest select-into-tests
  (testing "SELECT INTO"
    (is (= ["SELECT * INTO foo FROM bar"]
           (sql/format {:select :* :into :foo :from :bar})))
    (is (= ["SELECT * INTO foo IN otherdb FROM bar"]
           (sql/format {:select :* :into [:foo :otherdb] :from :bar})))
    (is (= ["SELECT * INTO foo FROM bar"]
           (sql/format (-> (select '*) (h/into 'foo) (from 'bar)))))
    (is (= ["SELECT * INTO foo IN otherdb FROM bar"]
           (sql/format (-> (select :*) (h/into :foo :otherdb) (from :bar))))))
  (testing "SELECT BULK COLLECT INTO"
    (is (= ["SELECT * BULK COLLECT INTO foo FROM bar"]
           (sql/format {:select :* :bulk-collect-into :foo :from :bar})))
    (is (= ["SELECT * BULK COLLECT INTO foo LIMIT ? FROM bar" 100]
           (sql/format {:select :* :bulk-collect-into [:foo 100] :from :bar})))
    (is (= ["SELECT * BULK COLLECT INTO foo FROM bar"]
           (sql/format (-> (select :*) (bulk-collect-into :foo) (from :bar)))))
    (is (= ["SELECT * BULK COLLECT INTO foo LIMIT ? FROM bar" 100]
           (sql/format (-> (select :*) (bulk-collect-into :foo 100) (from :bar)))))))

(deftest from-expression-tests
  (testing "FROM can be a function invocation"
    (is (= ["SELECT foo, bar FROM F(?) AS x" 1]
           (sql/format {:select [:foo :bar] :from [[[:f 1] :x]]}))))
  ;; these two examples are from https://www.postgresql.org/docs/9.3/queries-table-expressions.html#QUERIES-LATERAL
  (testing "FROM can be a LATERAL select"
    (is (= ["SELECT * FROM foo, LATERAL (SELECT * FROM bar WHERE bar.id = foo.bar_id) AS ss"]
           (sql/format {:select :*
                        :from [:foo
                               [[:lateral {:select :*
                                           :from :bar
                                           :where [:= :bar.id :foo.bar_id]}] :ss]]}))))
  (testing "FROM can be a LATERAL expression"
    (is (= [(str "SELECT p1.id, p2.id, v1, v2"
                 " FROM polygons AS p1, polygons AS p2,"
                 " LATERAL VERTICES(p1.poly) AS v1,"
                 " LATERAL VERTICES(p2.poly) AS v2"
                 " WHERE ((v1 <-> v2) < ?) AND (p1.id <> p2.id)") 10]
           (sql/format {:select [:p1.id :p2.id :v1 :v2]
                        :from [[:polygons :p1] [:polygons :p2]
                               [[:lateral [:vertices :p1.poly]] :v1]
                               [[:lateral [:vertices :p2.poly]] :v2]]
                        :where [:and [:< [:<-> :v1 :v2] 10] [:!= :p1.id :p2.id]]})))
    (is (= [(str "SELECT m.name"
                 " FROM manufacturers AS m"
                 " LEFT JOIN LATERAL GET_PRODUCT_NAMES(m.id) AS pname ON TRUE"
                 " WHERE pname IS NULL")]
           (sql/format {:select :m.name
                        :from [[:manufacturers :m]]
                        :left-join [[[:lateral [:get_product_names :m.id]] :pname] true]
                        :where [:= :pname nil]})))))

(deftest join-by-test
  (testing "Natural JOIN orders"
    (is (= ["SELECT * FROM foo 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"]
           (sql/format {:select [:*] :from [:foo]
                        :full-join  [:beck [:= :beck.x :c.y]]
                        :right-join [:bock [:= :bock.z :c.e]]
                        :left-join  [[:clod :c] [:= :f.a :c.d]]
                        :join       [:draq [:= :f.b :draq.x]]}))))
  (testing "Specific JOIN orders"
    (is (= ["SELECT * FROM foo FULL JOIN beck ON beck.x = c.y RIGHT JOIN bock ON bock.z = c.e LEFT JOIN clod AS c ON f.a = c.d INNER JOIN draq ON f.b = draq.x"]
           (sql/format {:select [:*] :from [:foo]
                        :join-by [:full  [:beck [:= :beck.x :c.y]]
                                  :right [:bock [:= :bock.z :c.e]]
                                  :left  [[:clod :c] [:= :f.a :c.d]]
                                  :join  [:draq [:= :f.b :draq.x]]]})))
    (is (= ["SELECT * FROM foo FULL JOIN beck ON beck.x = c.y RIGHT JOIN bock ON bock.z = c.e LEFT JOIN clod AS c ON f.a = c.d INNER JOIN draq ON f.b = draq.x"]
           (-> (select :*)
               (from :foo)
               (join-by :full-join  [:beck [:= :beck.x :c.y]]
                        :right-join [:bock [:= :bock.z :c.e]]
                        :left-join  [[:clod :c] [:= :f.a :c.d]]
                        :inner-join [:draq [:= :f.b :draq.x]])
               (sql/format)))))
  (testing "Specific JOIN orders with join clauses"
    (is (= ["SELECT * FROM foo FULL JOIN beck ON beck.x = c.y RIGHT JOIN bock ON bock.z = c.e LEFT JOIN clod AS c ON f.a = c.d INNER JOIN draq ON f.b = draq.x"]
           (sql/format {:select [:*] :from [:foo]
                        :join-by [{:full-join [:beck [:= :beck.x :c.y]]}
                                  {:right-join [:bock [:= :bock.z :c.e]]}
                                  {:left-join [[:clod :c] [:= :f.a :c.d]]}
                                  {:join [:draq [:= :f.b :draq.x]]}]})))
    (is (= ["SELECT * FROM foo FULL JOIN beck ON beck.x = c.y RIGHT JOIN bock ON bock.z = c.e LEFT JOIN clod AS c ON f.a = c.d INNER JOIN draq ON f.b = draq.x"]
           (-> (select :*)
               (from :foo)
               (join-by (full-join :beck [:= :beck.x :c.y])
                        (right-join :bock [:= :bock.z :c.e])
                        (left-join [:clod :c] [:= :f.a :c.d])
                        (join :draq [:= :f.b :draq.x]))
               (sql/format))))))

(deftest test-cast
  (is (= ["SELECT foo, CAST(bar AS INTEGER)"]
         (sql/format {:select [:foo [[:cast :bar :integer]]]})))
  (is (= ["SELECT foo, CAST(bar AS INTEGER)"]
         (sql/format {:select [:foo [[:cast :bar 'integer]]]})))
  (is (= ["SELECT foo, CAST(bar AS DOUBLE PRECISION)"] ;; Postgres example
         (sql/format {:select [:foo [[:cast :bar :double-precision]]]})))
  (is (= ["SELECT \"foo\", CAST(\"bar\" AS INTEGER)"]
         (sql/format {:select [:foo [[:cast :bar :integer]]]} {:quoted true})))
  (is (= ["SELECT `foo`, CAST(`bar` AS INTEGER)"]
         (sql/format {:select [:foo [[:cast :bar :integer]]]} {:dialect :mysql})))
  (is (= ["SELECT `foo`, CAST(`bar` AS CHAR(10))"]
         (sql/format {:select [:foo [[:cast :bar [:char 10]]]]} {:dialect :mysql
                                                                 :inline true}))))

(deftest test-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 test-case
  (is (= ["SELECT CASE WHEN foo < ? THEN ? WHEN (foo > ?) AND ((foo MOD ?) = ?) THEN foo / ? ELSE ? END FROM bar"
          0 -1 0 2 0 2 0]
         (sql/format
          {:select [[[:case
                      [:< :foo 0] -1
                      [:and [:> :foo 0] [:= [:mod :foo 2] 0]] [:/ :foo 2]
                      :else 0]]]
           :from [:bar]})))
  (let [param1 1
        param2 2
        param3 "three"]
    (is (= ["SELECT CASE WHEN foo = ? THEN ? WHEN foo = bar THEN ? WHEN bar = ? THEN bar * ? ELSE ? END FROM baz"
            param1 0 param2 0 param3 "param4"]
           (sql/format
            {:select [[[:case
                        [:= :foo :?param1] 0
                        [:= :foo :bar] [:param :param2]
                        [:= :bar 0] [:* :bar :?param3]
                        :else "param4"]]]
             :from [:baz]}
            {:params
             {:param1 param1
              :param2 param2
              :param3 param3}})))))

(deftest test-raw
  (is (= ["SELECT 1 + 1 FROM foo"]
         (-> (select [[:raw "1 + 1"]])
             (from :foo)
             sql/format))))

(deftest test-call
  (is (= ["SELECT MIN(?) FROM ?" "time" "table"]
         (-> (select [[:min "time"]])
             (from "table")
             sql/format))))

(deftest join-test
  (testing "nil join"
    (is (= ["SELECT * FROM foo INNER JOIN x ON foo.id = x.id INNER JOIN y"]
           (-> (select :*)
               (from :foo)
               (join :x [:= :foo.id :x.id] :y nil)
               sql/format)))))

(deftest join-using-test
  (testing "nil join"
    (is (= ["SELECT * FROM foo INNER JOIN x USING (id) INNER JOIN y USING (foo, bar)"]
           (-> (select :*)
               (from :foo)
               (join :x [:using :id] :y [:using :foo :bar])
               sql/format)))))

(deftest inline-test
  (is (= ["SELECT * FROM foo WHERE id = 5"]
         (-> (select :*)
             (from :foo)
             (where [:= :id [:inline 5]])
             sql/format)))
  ;; testing for = NULL always fails in SQL -- this test is just to show
  ;; that an #inline nil should render as NULL (so make sure you only use
  ;; it in contexts where a literal NULL is acceptable!)
  (is (= ["SELECT * FROM foo WHERE id = NULL"]
         (-> (select :*)
             (from :foo)
             (where [:= :id [:inline nil]])
             sql/format))))

(deftest where-no-params-test
  (testing "where called with just the map as parameter - see #228"
    (let [sqlmap (-> (select :*)
                     (from :table)
                     (where [:= :foo :bar]))]
      (is (= ["SELECT * FROM table WHERE foo = bar"]
             (sql/format (apply merge sqlmap [])))))))

(deftest where-test
  (is (= ["SELECT * FROM table WHERE (foo = bar) AND (quuz = xyzzy)"]
         (-> (select :*)
             (from :table)
             (where [:= :foo :bar] [:= :quuz :xyzzy])
             sql/format)))
  (is (= ["SELECT * FROM table WHERE (foo = bar) AND (quuz = xyzzy)"]
         (-> (select :*)
             (from :table)
             (where [:= :foo :bar])
             (where [:= :quuz :xyzzy])
             sql/format))))

(deftest where-nil-params-test
  (testing "where called with nil parameters - see #246"
    (is (= ["SELECT * FROM table WHERE (foo = bar) AND (quuz = xyzzy)"]
           (-> (select :*)
               (from :table)
               (where nil [:= :foo :bar] nil [:= :quuz :xyzzy] nil)
               sql/format)))
    (is (= ["SELECT * FROM table"]
           (-> (select :*)
               (from :table)
               (where)
               sql/format)))
    (is (= ["SELECT * FROM table"]
           (-> (select :*)
               (from :table)
               (where nil nil nil nil)
               sql/format)))))

(deftest cross-join-test
  (is (= ["SELECT * FROM foo CROSS JOIN bar"]
         (-> (select :*)
             (from :foo)
             (cross-join :bar)
             sql/format)))
  (is (= ["SELECT * FROM foo AS f CROSS JOIN bar b"]
         (-> (select :*)
             (from [:foo :f])
             (cross-join [:bar :b])
             sql/format))))

(defn- stack-overflow-282 [num-ids]
  (let [ids (range num-ids)]
    (sql/format (reduce
                 where
                 {:select [[:id :id]]
                  :from   [:collection]
                  :where  [:= :personal_owner_id nil]}
                 (clojure.core/for [id ids]
                                   [:not-like :location [:raw (str "'/" id "/%'")]])))))

(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-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 merge-where-no-params-test
  (doseq [[k [f merge-f]] {"WHERE"  [where where]
                           "HAVING" [having having]}]
    (testing "merge-where called with just the map as parameter - see #228"
      (let [sqlmap (-> (select :*)
                       (from :table)
                       (f [:= :foo :bar]))]
        (is (= [(str "SELECT * FROM table " k " foo = bar")]
               (sql/format (apply merge-f sqlmap []))))))))

(deftest merge-where-test
  (doseq [[k sql-keyword f merge-f] [[:where "WHERE" where where]
                                     [:having "HAVING" having having]]]
    (is (= [(str "SELECT * FROM table " sql-keyword " (foo = bar) AND (quuz = xyzzy)")]
           (-> (select :*)
               (from :table)
               (f [:= :foo :bar] [:= :quuz :xyzzy])
               sql/format)))
    (is (= [(str "SELECT * FROM table " sql-keyword " (foo = bar) AND (quuz = xyzzy)")]
           (-> (select :*)
               (from :table)
               (f [:= :foo :bar])
               (merge-f [:= :quuz :xyzzy])
               sql/format)))
    (testing "Should work when first arg isn't a map"
      (is (= {k [:and [:x] [:y]]}
             (merge-f [:x] [:y]))))
    (testing "Shouldn't use conjunction if there is only one clause in the result"
      (is (= {k [:x]}
             (merge-f {} [:x]))))
    (testing "Should be able to specify the conjunction type"
      (is (= {k [:or [:x] [:y]]}
             (merge-f {}
                      :or
                      [:x] [:y]))))
    (testing "Should ignore nil clauses"
      (is (= {k [:or [:x] [:y]]}
             (merge-f {}
                      :or
                      [:x] nil [:y]))))))

(deftest filter-within-order-by-test
  (testing "PostgreSQL filter, within group, order-by as special syntax"
    (is (= (sql/format {:select [[[:filter :%count.* {:where [:> :i 5]}] :a]
                                 [[:filter ; two pairs -- alias is on last pair
                                   [:avg :x [:order-by :y [:a :desc]]] {:where [:< :i 10]}
                                   [:sum :q] {:where [:= :x nil]}] :b]
                                 [[:within-group [:foo :y] {:order-by :x}]]]})
           [(str "SELECT COUNT(*) FILTER (WHERE i > ?) AS a,"
                 " AVG(x, y ORDER BY a DESC) FILTER (WHERE i < ?),"
                 " SUM(q) FILTER (WHERE x IS NULL) AS b,"
                 " FOO(y) WITHIN GROUP (ORDER BY x ASC)")
            5 10])))
  (testing "PostgreSQL filter, within group, order-by as helpers"
    (is (= (sql/format (select [(filter :%count.* (where :> :i 5)) :a]
                               [(filter ; two pairs -- alias is on last pair
                                 ;; order by must remain special syntax here:
                                 [:avg :x [:order-by :y [:a :desc]]] (where :< :i 10)
                                 [:sum :q] (where := :x nil)) :b]
                               [(within-group [:foo :y] (order-by :x))]))
           [(str "SELECT COUNT(*) FILTER (WHERE i > ?) AS a,"
                 " AVG(x, y ORDER BY a DESC) FILTER (WHERE i < ?),"
                 " SUM(q) FILTER (WHERE x IS NULL) AS b,"
                 " FOO(y) WITHIN GROUP (ORDER BY x ASC)")
            5 10]))))

(deftest issue-324
  (testing "insert-into accepts statement"
    (is (= (-> (with [:a])
               (insert-into [:quux [:x :y]]
                            {:select [:id] :from [:table]}))
           {:with [[:a]],
            :insert-into [[:quux [:x :y]]
                          {:select [:id], :from [:table]}]}))))

(deftest issue-431
  (testing "where false should not be ignored"
    (is (= {:where false}
           (where false)))
    (is (= ["SELECT * FROM table WHERE FALSE"]
           (sql/format {:select [:*] :from [:table] :where false})))))
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)))

(deftest cache-tests
  (let [cache (cache/basic-cache-factory {})]
    (is (zero? (cache-size cache)))
    (is (= ["SELECT * FROM table WHERE id = ?" 1]
           (sut/format {:select [:*] :from [:table] :where [:= :id 1]}
                       {:cache cache})
           (sut/format {:select [:*] :from [:table] :where [:= :id 1]}
                       {:cache cache})))
    (is (= 1 (cache-size cache)))
    (is (= (sut/format {:select [:*] :from [:table] :where [:= :id 2]})
           (sut/format {:select [:*] :from [:table] :where [:= :id 2]}
                       {:cache cache})))
    (is (= 2 (cache-size cache)))
    (is (= (sut/format big-complicated-map {:params {:param1 "gabba" :param2 2}})
           (sut/format big-complicated-map {:cache cache :params {:param1 "gabba" :param2 2}})
           (sut/format big-complicated-map {:cache cache :params {:param1 "gabba" :param2 2}})))
    (is (= 3 (cache-size cache)))
    (is (= (sut/format big-complicated-map {:params {:param1 "foo" :param2 42}})
           (sut/format big-complicated-map {:cache cache :params {:param1 "foo" :param2 42}})
           (sut/format big-complicated-map {:cache cache :params {:param1 "foo" :param2 42}})))
    (is (= 3 (cache-size cache)))
    (println "Uncached, simple, embedded")
    (time (dotimes [_ 100000]
            (sut/format {:select [:*] :from [:table] :where [:= :id (rand-int 10)]})))
    (println "Cached, simple, embedded")
    (time (dotimes [_ 100000]
            (sut/format {:select [:*] :from [:table] :where [:= :id (rand-int 10)]} {:cache cache})))
    (is (= 11 (cache-size cache)))
    (println "Uncached, complex, mixed")
    (time (dotimes [_ 10000]
            (sut/format big-complicated-map {:params {:param1 "gabba" :param2 (rand-int 10)}})))
    (println "Cached, complex, mixed")
    (time (dotimes [_ 10000]
            (sut/format big-complicated-map {:cache cache :params {:param1 "gabba" :param2 (rand-int 10)}})))
    (is (= 11 (cache-size cache))))
  (let [cache (cache/basic-cache-factory {})]
    (is (zero? (cache-size cache)))
    (is (= ["SELECT * FROM table WHERE id = ?" 1]
           (sut/format {:select [:*] :from [:table] :where [:= :id :?id]}
                       {:cache cache :params {:id 1}})
           (sut/format {:select [:*] :from [:table] :where [:= :id :?id]}
                       {:cache cache :params {:id 1}})))
    (is (= 1 (cache-size cache)))
    (is (= (sut/format {:select [:*] :from [:table] :where [:= :id :?id]}
                       {:params {:id 2}})
           (sut/format {:select [:*] :from [:table] :where [:= :id :?id]}
                       {:cache cache :params {:id 2}})))
    (is (= 1 (cache-size cache)))
    ;; different parameter names create different cache entries:
    (is (= (sut/format {:select [:*] :from [:table] :where [:= :id :?x]}
                       {:cache cache :params {:x 2}})
           (sut/format {:select [:*] :from [:table] :where [:= :id :?y]}
                       {:cache cache :params {:y 2}})))
    (is (= 3 (cache-size cache)))
    ;; swapping parameter names creates different cache entries:
    (is (= (sut/format {:select [:*] :from [:table] :where [:and [:= :id :?x] [:= :foo :?y]]}
                       {:cache cache :params {:x 2 :y 3}})
           (sut/format {:select [:*] :from [:table] :where [:and [:= :id :?y] [:= :foo :?x]]}
                       {:cache cache :params {:x 3 :y 2}})))
    (is (= 5 (cache-size cache)))))