partition-by (clj)

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

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

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