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

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

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