(ns honeysql.core-test (:refer-clojure :exclude [format update]) (:require [#?@(:clj [clojure.test :refer] :cljs [cljs.test :refer-macros]) [deftest testing is]] [honeysql.core :as sql] [honeysql.format :as sql-f] [honeysql.helpers :refer [select modifiers from join left-join right-join full-join cross-join where group having order-by limit offset values columns insert-into with merge-where merge-having]] honeysql.format-test)) ;; BB_TEST_PATCH: must explicitly set data readers #?(:clj (do (require '[honeysql.types]) (set! *data-readers* {'sql/call honeysql.types/read-sql-call 'sql/inline honeysql.types/read-sql-inline 'sql/raw honeysql.types/read-sql-raw 'sql/param honeysql.types/read-sql-param 'sql/array honeysql.types/read-sql-array 'sql/regularize honeysql.format/regularize}))) ;; TODO: more tests (deftest test-select (let [m1 (-> (with [:cte (-> (select :*) (from :example) (where [:= :example-column 0]))]) (select :f.* :b.baz :c.quux [:b.bla :bla-bla] :%now (sql/raw "@x := 10")) ;;(un-select :c.quux) (modifiers :distinct) (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]] [:< 1 2 3] [:in :f.e [1 (sql/param :param2) 3]] [:between :f.e 10 20]]) ;;(merge-where [:not= nil :b.bla]) (group :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 [:f.* :b.baz :c.quux [:b.bla :bla-bla] :%now (sql/raw "@x := 10")] ;;:un-select :c.quux :modifiers :distinct :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]] [:< 1 2 3] [:in :f.e [1 (sql/param :param2) 3]] [:between :f.e 10 20]] ;;:merge-where [:not= nil :b.bla] :group-by :f.a :having [:< 0 :f.e] :order-by [[:b.baz :desc] :c.quux [:f.a :nulls-first]] :limit 50 :offset 10} m3 (sql/build m2) m4 (apply sql/build (apply concat m2))] (testing "Various construction methods are consistent" (is (= m1 m3 m4))) (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 f, baz b INNER JOIN draq ON f.b = draq.x LEFT JOIN clod 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, f.a NULLS FIRST LIMIT ? OFFSET ? " 0 "bort" "gabba" 1 2 2 3 1 2 3 10 20 0 50 10] (sql/format m1 {: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} :parameterizer :postgresql) ["WITH cte AS (SELECT * FROM example WHERE example_column = $1) SELECT DISTINCT f.*, b.baz, c.quux, b.bla AS bla_bla, now(), @x := 10 FROM foo f, baz b INNER JOIN draq ON f.b = draq.x LEFT JOIN clod 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, 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 f, baz b INNER JOIN draq ON f.b = draq.x LEFT JOIN clod 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, f.a NULLS FIRST LIMIT ? OFFSET ? FOR UPDATE " 0 "bort" "gabba" 1 2 2 3 1 2 3 10 20 0 50 10] (sql/format (assoc m1 :lock {:mode :update}) {:param1 "gabba" :param2 2})))))) (deftest test-cast (is (= ["SELECT foo, CAST(bar AS integer)"] (sql/format {:select [:foo (sql/call :cast :bar :integer)]}))) (is (= ["SELECT foo, CAST(bar AS integer)"] (sql/format {:select [:foo (sql/call :cast :bar 'integer)]})))) (deftest test-value (is (= ["INSERT INTO foo (bar) VALUES (?)" {:baz "my-val"}] (-> (insert-into :foo) (columns :bar) (values [[(sql-f/value {:baz "my-val"})]]) sql/format))) (is (= ["INSERT INTO foo (a, b, c) VALUES (?, ?, ?), (?, ?, ?)" "a" "b" "c" "a" "b" "c"] (-> (insert-into :foo) (values [(array-map :a "a" :b "b" :c "c") (hash-map :a "a" :b "b" :c "c")]) sql/format)))) (deftest test-operators (testing "=" (testing "with nil" (is (= ["SELECT * FROM customers WHERE name IS NULL"] (sql/format {:select [:*] :from [:customers] :where [:= :name nil]}))) (is (= ["SELECT * FROM customers WHERE name = ?" nil] (sql/format {:select [:*] :from [:customers] :where [:= :name :?name]} {: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]} {: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]} {: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]} {:ids values}))))))) (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 [(sql/call :case [:< :foo 0] -1 [:and [:> :foo 0] [:= (sql/call :mod :foo 2) 0]] (sql/call :/ :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 [(sql/call :case [:= :foo :?param1] 0 [:= :foo :bar] (sql/param :param2) [:= :bar 0] (sql/call :* :bar :?param3) :else "param4")] :from [:baz]} {:param1 param1 :param2 param2 :param3 param3}))))) (deftest test-raw (is (= ["SELECT 1 + 1 FROM foo"] (-> (select (sql/raw "1 + 1")) (from :foo) sql/format)))) (deftest test-call (is (= ["SELECT min(?) FROM ?" "time" "table"] (-> (select (sql/call :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 (sql/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 (sql/inline nil)]) sql/format)))) (deftest merge-where-no-params-test (doseq [[k [f merge-f]] {"WHERE" [where merge-where] "HAVING" [having merge-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 merge-where] [:having "HAVING" having merge-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 merge-where-build-clause-test (doseq [k [:where :having]] (testing (str "Should be able to build a " k " clause with sql/build") (is (= {k [:and [:a] [:x] [:y]]} (sql/build k [:a] (keyword (str "merge-" (name k))) [:and [:x] [:y]])))))) (deftest merge-where-combine-clauses-test (doseq [[k f] {:where merge-where :having merge-having}] (testing (str "Combine new " k " clauses into the existing clause when appropriate. (#282)") (testing "No existing clause" (is (= {k [:and [:x] [:y]]} (f {} [:x] [:y])))) (testing "Existing clause is not a conjunction." (is (= {k [:and [:a] [:x] [:y]]} (f {k [:a]} [:x] [:y])))) (testing "Existing clause IS a conjunction." (testing "New clause(s) are not conjunctions" (is (= {k [:and [:a] [:b] [:x] [:y]]} (f {k [:and [:a] [:b]]} [:x] [:y])))) (testing "New clauses(s) ARE conjunction(s)" (is (= {k [:and [:a] [:b] [:x] [:y]]} (f {k [:and [:a] [:b]]} [:and [:x] [:y]]))) (is (= {k [:and [:a] [:b] [:x] [:y]]} (f {k [:and [:a] [:b]]} [:and [:x]] [:y]))) (is (= {k [:and [:a] [:b] [:x] [:y]]} (f {k [:and [:a] [:b]]} [:and [:x]] [:and [:y]]))))) (testing "if existing clause isn't the same conjunction, don't merge into it" (testing "existing conjunction is `:or`" (is (= {k [:and [:or [:a] [:b]] [:x] [:y]]} (f {k [:or [:a] [:b]]} [:x] [:y])))) (testing "pass conjunction type as a param (override default of :and)" (is (= {k [:or [:and [:a] [:b]] [:x] [:y]]} (f {k [:and [:a] [:b]]} :or [:x] [:y])))))))) (deftest where-nil-params-test (doseq [[_ sql-keyword f] [[:where "WHERE" where] [:having "HAVING" having]]] (testing (str sql-keyword " called with nil parameters - see #246") (is (= [(str "SELECT * FROM table " sql-keyword " (foo = bar AND quuz = xyzzy)")] (-> (select :*) (from :table) (f nil [:= :foo :bar] nil [:= :quuz :xyzzy] nil) sql/format))) (is (= ["SELECT * FROM table"] (-> (select :*) (from :table) (f) sql/format))) (is (= ["SELECT * FROM table"] (-> (select :*) (from :table) (f 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 f CROSS JOIN bar b"] (-> (select :*) (from [:foo :f]) (cross-join [:bar :b]) sql/format)))) #?(:cljs (cljs.test/run-all-tests))