over (clj)

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

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

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

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

(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-282
  (is (= [(str "SELECT id AS id FROM collection"
               " WHERE (personal_owner_id IS NULL)"
               " AND (location NOT LIKE '/0/%')"
               " AND (location NOT LIKE '/1/%')")]
         (stack-overflow-282 2))))

(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 merge-where-combine-clauses-test
  (doseq [[k f] {:where  where
                 :having 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]))))))))