Public Vars

Back

table (clj)

(source)

function

(table name)
Accepts a single table name and produces TABLE name This is equivalent to: SELECT * FROM name

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 insert-into-namespaced
  ;; un-namespaced: works as expected:
  (is (= (format {:insert-into :foo :values [{:foo/id 1}]})
         ["INSERT INTO foo (id) VALUES (?)" 1]))
  (is (= (format {:insert-into :foo :columns [:foo/id] :values [[2]]})
         ["INSERT INTO foo (id) VALUES (?)" 2]))
  (is (= (format {:insert-into :foo :values [{:foo/id 1}]}
                 {:namespace-as-table? true})
         ["INSERT INTO foo (id) VALUES (?)" 1]))
  (is (= (format {:insert-into :foo :columns [:foo/id] :values [[2]]}
                 {:namespace-as-table? true})
         ["INSERT INTO foo (id) VALUES (?)" 2])))

(deftest delete-test
  (is (= ["DELETE `t1`, `t2` FROM `table1` AS `t1` INNER JOIN `table2` AS `t2` ON `t1`.`fk` = `t2`.`id` WHERE `t1`.`bar` = ?" 42]
         (-> {:delete [:t1 :t2]
              :from [[:table1 :t1]]
              :join [[:table2 :t2] [:= :t1.fk :t2.id]]
              :where [:= :t1.bar 42]}
             (format {:dialect :mysql})))))

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

(deftest 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 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-299-test
  (let [name    "test field"
        ;; this was a bug in 1.x -- adding here to prevent regression:
        enabled [true, "); SELECT case when (SELECT current_setting('is_superuser'))='off' then pg_sleep(0.2) end; -- "]]
    (is (= ["INSERT INTO table (name, enabled) VALUES (?, (TRUE, ?))" name (second enabled)]
           (format {:insert-into :table
                    :values [{:name name
                              :enabled enabled}]})))))

(deftest issue-425-default-values-test
  (testing "default values"
    (is (= ["INSERT INTO table (a, b, c) DEFAULT VALUES"]
           (format {:insert-into [:table [:a :b :c]] :values :default}))))
  (testing "values with default row"
    (is (= ["INSERT INTO table (a, b, c) VALUES (1, 2, 3), DEFAULT, (4, 5, 6)"]
           (format {:insert-into [:table [:a :b :c]]
                    :values [[1 2 3] :default [4 5 6]]}
                   {:inline true}))))
  (testing "values with default column"
    (is (= ["INSERT INTO table (a, b, c) VALUES (1, DEFAULT, 3), DEFAULT"]
           (format {:insert-into [:table [:a :b :c]]
                    :values [[1 [:default] 3] :default]}
                   {:inline true}))))
  (testing "map values with default row, no columns"
    (is (= ["INSERT INTO table (a, b, c) VALUES (1, 2, 3), DEFAULT, (4, 5, 6)"]
           (format {:insert-into :table
                    :values [{:a 1 :b 2 :c 3} :default {:a 4 :b 5 :c 6}]}
                   {:inline true}))))
  (testing "map values with default column, no columns"
    (is (= ["INSERT INTO table (a, b, c) VALUES (1, DEFAULT, 3), DEFAULT"]
           (format {:insert-into :table
                    :values [{:a 1 :b [:default] :c 3} :default]}
                   {:inline true}))))
  (testing "empty values"
    (is (= ["INSERT INTO table (a, b, c) VALUES ()"]
           (format {:insert-into [:table [:a :b :c]]
                    :values []})))))

(deftest issue-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-421-mysql-replace-into
  (is (= ["INSERT INTO table VALUES (?, ?, ?)" 1 2 3]
         (sut/format {:insert-into :table :values [[1 2 3]]})))
  (is (= ["REPLACE INTO table VALUES (?, ?, ?)" 1 2 3]
         (sut/format {:replace-into :table :values [[1 2 3]]}
                     {:dialect :mysql :quoted false}))))

(deftest issue-496-overriding
  (is (= ["INSERT INTO table (a, b) OVERRIDING SYSTEM VALUE VALUES (?, ?)" 1 2]
         (sut/format {:insert-into [{:overriding-value :system} :table]
                      :columns [:a :b]
                      :values [[1 2]]})))
  (is (= ["INSERT INTO table (a, b) OVERRIDING USER VALUE VALUES (?, ?)" 1 2]
         (sut/format {:insert-into [{:overriding-value :user} :table [:a :b]]
                      :values [[1 2]]})))
  (is (= ["INSERT INTO table (a, b) OVERRIDING SYSTEM VALUE VALUES (?, ?)" 1 2]
         (sut/format {:insert-into [{:overriding-value :system} :table]
                      :values [{:a 1 :b 2}]}))))

(deftest 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-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]]}))))))
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 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 drop-table-test
  (testing "drop table sql generation for a single table"
    (is (= ["DROP TABLE cities"]
           (sql/format (drop-table :cities)))))
  (testing "drop table sql generation for multiple tables"
    (is (= ["DROP TABLE cities, towns, vilages"]
           (sql/format (drop-table :cities :towns :vilages))))))

(deftest create-table-test
  ;; the nilenso versions of these tests required sql/call for function-like syntax
  (testing "create table with two columns"
    (is (= ["CREATE TABLE cities (city VARCHAR(80) PRIMARY KEY, location POINT)"]
           (-> (create-table :cities)
               (with-columns [[:city [:varchar 80] [:primary-key]]
                              [:location :point]])
               sql/format))))
  (testing "create table with foreign key reference"
    (is (= ["CREATE TABLE weather (city VARCHAR(80) REFERENCES cities(city), temp_lo INT, temp_hi INT, prcp REAL, date DATE)"]
           (-> (create-table :weather)
               (with-columns [[:city [:varchar :80] [:references :cities :city]]
                              [:temp_lo :int]
                              [:temp_hi :int]
                              [:prcp :real]
                              [:date :date]])
               sql/format))))
  (testing "creating table with table level constraint"
    (is (= ["CREATE TABLE films (code CHAR(5), title VARCHAR(40), did INTEGER, date_prod DATE, kind VARCHAR(10), CONSTRAINT code_title PRIMARY KEY(code, title))"]
           (-> (create-table :films)
               (with-columns [[:code [:char 5]]
                              [:title [:varchar 40]]
                              [:did :integer]
                              [:date_prod :date]
                              [:kind [:varchar 10]]
                              [[:constraint :code_title] [:primary-key :code :title]]])
               sql/format))))
  (testing "creating table with column level constraint"
    (is (= ["CREATE TABLE films (code CHAR(5) CONSTRAINT firstkey PRIMARY KEY, title VARCHAR(40) NOT NULL, did INTEGER NOT NULL, date_prod DATE, kind VARCHAR(10))"]
           (-> (create-table :films)
               (with-columns [[:code [:char 5] [:constraint :firstkey] [:primary-key]]
                              [:title [:varchar 40] [:not nil]]
                              [:did :integer [:not nil]]
                              [:date_prod :date]
                              [:kind [:varchar 10]]])
               sql/format))))
  (testing "creating table with columns with default values"
    (is (= ["CREATE TABLE distributors (did INTEGER PRIMARY KEY DEFAULT NEXTVAL('serial'), name VARCHAR(40) NOT NULL)"]
           (-> (create-table :distributors)
               (with-columns [[:did :integer [:primary-key] [:default [:nextval "serial"]]]
                              [:name [:varchar 40] [:not nil]]])
               sql/format))))
  (testing "creating table with column checks"
    (is (= ["CREATE TABLE products (product_no INTEGER, name TEXT, price NUMERIC CHECK(price > 0), discounted_price NUMERIC, CHECK((discounted_price > 0) AND (price > discounted_price)))"]
           (-> (create-table :products)
               (with-columns [[:product_no :integer]
                              [:name :text]
                              [:price :numeric [:check [:> :price 0]]]
                              [:discounted_price :numeric]
                              [[:check [:and [:> :discounted_price 0] [:> :price :discounted_price]]]]])
               sql/format)))))

(deftest references-issue-386
  (is (= ["CREATE TABLE IF NOT EXISTS user (id VARCHAR(255) NOT NULL PRIMARY KEY, company_id INT NOT NULL, name VARCHAR(255) NOT NULL, password VARCHAR(255) NOT NULL, created_time DATETIME DEFAULT CURRENT_TIMESTAMP, updated_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY(company_id) REFERENCES company(id))"]
         (-> {:create-table [:user :if-not-exists]
              :with-columns
              [[:id [:varchar 255] [:not nil] [:primary-key]]
               [:company-id :int [:not nil]]
               [:name [:varchar 255] [:not nil]]
               [:password [:varchar 255] [:not nil]]
               [:created-time :datetime [:default :CURRENT_TIMESTAMP]]
               [:updated-time :datetime [:default :CURRENT_TIMESTAMP]
                :on :update :CURRENT_TIMESTAMP]
               [[:foreign-key :company-id] [:references :company :id]]]}
             (sql/format)))))

(deftest create-table-issue-437
  (is (= ["CREATE TABLE bar (did UUID DEFAULT GEN_RANDOM_UUID(), foo_id VARCHAR NOT NULL, PRIMARY KEY(did, foo_id), FOREIGN KEY(foo_id) REFERENCES foo(id) ON DELETE CASCADE)"]
         (-> (create-table :bar)
             (with-columns
               [[:did :uuid [:default [:gen_random_uuid]]]
                [:foo-id :varchar [:not nil]]
                [[:primary-key :did :foo-id]]
                [[:foreign-key :foo-id]
                 [:references :foo :id]
                 :on-delete :cascade]])
             (sql/format)))))

(deftest alter-table-test
  (testing "alter table add column generates the required sql"
    (is (= ["ALTER TABLE employees ADD COLUMN address TEXT"]
           (-> (alter-table :employees)
               (add-column :address :text)
               sql/format))))
  (testing "alter table drop column generates the required sql"
    (is (= ["ALTER TABLE employees DROP COLUMN address"]
           (-> (alter-table :employees)
               (drop-column :address)
               sql/format))))
  (testing "alter table rename column generates the requred sql"
    (is (= ["ALTER TABLE employees RENAME COLUMN address TO homeaddress"]
           (-> (alter-table :employees)
               (rename-column :address :homeaddress)
               sql/format))))
  (testing "alter table rename table generates the required sql"
    (is (= ["ALTER TABLE employees RENAME TO managers"]
           (-> (alter-table :employees)
               (rename-table :managers)
               sql/format)))))

(deftest create-table-if-not-exists
  (testing "create a table if not exists"
    (is (= ["CREATE TABLE IF NOT EXISTS tablename"]
           (-> (create-table :tablename :if-not-exists)
               sql/format)))))

(deftest drop-table-if-exists
  (testing "drop a table if it exists"
    (is (= ["DROP TABLE IF EXISTS t1, t2, t3"]
           (-> (drop-table :if-exists :t1 :t2 :t3)
               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 issue-453-constraint
  (testing "standalone constraint"
    (is (= ["CREATE TABLE bar (a INTEGER, b INTEGER, CONSTRAINT foo_natural_key UNIQUE (a, b))"]
           (-> {:create-table [:bar]
                :with-columns
                [[:a :integer]
                 [:b :integer]
                 [[:constraint :foo_natural_key] :unique [:composite :a :b]]]}
               (sql/format)))))
  (testing "inline constraint"
    (is (= ["CREATE TABLE foo (a INTEGER CONSTRAINT a_pos CHECK(a > 0), b INTEGER, CONSTRAINT a_bigger CHECK(b < a))"]
           (-> '{create-table foo
                 with-columns
                 ((a integer (constraint a_pos) (check (> a 0)))
                  (b integer)
                  ((constraint a_bigger) (check (< b a))))}
               (sql/format))))))
honeysql
(ns honey.sql.helpers-test
  (:refer-clojure :exclude [filter for group-by partition-by set update])
  (:require [clojure.test :refer [deftest is testing]]
            [honey.sql :as sql]
            [honey.sql.helpers :as h
             :refer [add-column add-index alter-table columns create-table create-table-as create-view
                     create-materialized-view drop-view drop-materialized-view
                     create-index
                     bulk-collect-into
                     cross-join do-update-set drop-column drop-index drop-table
                     filter from full-join
                     group-by having insert-into
                     join-by join lateral left-join limit offset on-conflict
                     on-duplicate-key-update
                     order-by over partition-by refresh-materialized-view
                     rename-column rename-table returning right-join
                     select select-distinct select-top select-distinct-top
                     values where window with with-columns
                     with-data within-group]]))

(deftest 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 test-call
  (is (= ["SELECT MIN(?) FROM ?" "time" "table"]
         (-> (select [[:min "time"]])
             (from "table")
             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 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-alter-table
  (is (= (sql/format (-> (alter-table :fruit)
                         (add-column :id :int [:not nil])))
         ["ALTER TABLE fruit ADD COLUMN id INT NOT NULL"]))
  (is (= (sql/format (-> (alter-table :fruit)
                         (add-column :id :int [:not nil])
                         (add-column :a1 :int nil)
                         (add-column :be :text [:not nil])))
         ["ALTER TABLE fruit ADD COLUMN id INT NOT NULL, ADD COLUMN a1 INT NULL, ADD COLUMN be TEXT NOT NULL"]))
  (is (= (sql/format (alter-table :fruit
                                  (add-column :id :int [:not nil])
                                  (drop-column :ident)
                                  (drop-column :if-exists :another)))
         ["ALTER TABLE fruit ADD COLUMN id INT NOT NULL, DROP COLUMN ident, DROP COLUMN IF EXISTS another"]))
  (is (= (sql/format (alter-table :fruit
                                  (drop-column :a :b :if-exists :c :d)
                                  (drop-column :if-exists :e)))
         ["ALTER TABLE fruit DROP COLUMN a, DROP COLUMN b, DROP COLUMN IF EXISTS c, DROP COLUMN d, DROP COLUMN IF EXISTS e"])))

(deftest issue-293-insert-into-data
  ;; insert into as (and other tests) based on :insert-into
  ;; 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 mysql-on-duplicate-key-update
  (testing "From https://www.mysqltutorial.org/mysql-insert-or-update-on-duplicate-key-update"
    (is (= (sql/format (-> (insert-into :device)
                           (columns :name)
                           (values [["Printer"]])
                           (on-duplicate-key-update {:name "Printer"})))
           ["INSERT INTO device (name) VALUES (?) ON DUPLICATE KEY UPDATE name = ?"
            "Printer" "Printer"]))
    (is (= (sql/format (-> (insert-into :device)
                           (columns :id :name)
                           (values [[4 "Printer"]])
                           (on-duplicate-key-update {:name "Central Printer"})))
           ["INSERT INTO device (id, name) VALUES (?, ?) ON DUPLICATE KEY UPDATE name = ?"
            4 "Printer" "Central Printer"]))
    (is (= (sql/format (-> (insert-into :table)
                           (columns :c1)
                           (values [[42]])
                           (on-duplicate-key-update {:c1 [:+ [:values :c1] 1]})))
           ["INSERT INTO table (c1) VALUES (?) ON DUPLICATE KEY UPDATE c1 = VALUES(c1) + ?"
            42 1]))))

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

(deftest test-create-index
  (testing "create index, commonly supported features"
    (is (= ["CREATE INDEX my_column_idx ON my_table (my_column)"]
           (sql/format {:create-index [:my-column-idx [:my-table :my-column]]})))
    (is (= ["CREATE INDEX my_column_idx ON my_table (my_column)"]
           (sql/format (create-index :my-column-idx [:my-table :my-column]))))
    (is (= ["CREATE UNIQUE INDEX my_column_idx ON my_table (my_column)"]
           (sql/format (create-index [:unique :my-column-idx] [:my-table :my-column]))))
    (is (= ["CREATE INDEX my_column_idx ON my_table (my_column, my_other_column)"]
           (sql/format (create-index :my-column-idx [:my-table :my-column :my-other-column])))))
  (testing "PostgreSQL extensions (IF NOT EXISTS and expressions)"
    (is (= ["CREATE INDEX IF NOT EXISTS my_column_idx ON my_table (my_column)"]
           (sql/format (create-index [:my-column-idx :if-not-exists] [:my-table :my-column]))))
    (is (= ["CREATE UNIQUE INDEX IF NOT EXISTS my_column_idx ON my_table (my_column)"]
           (sql/format (create-index [:unique :my-column-idx :if-not-exists] [:my-table :my-column]))))
    (is (= ["CREATE INDEX my_column_idx ON my_table (LOWER(my_column))"]
           (sql/format (create-index :my-column-idx [:my-table :%lower.my-column]))))))
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]]))

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