Back
update (clj)
(source)function
(update table)
Accepts either a table name or a table/alias pair.
(-> (update :table) (set {:id 1 :cost 32.1}))
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 set-before-from
;; issue 235
(is (=
["UPDATE \"films\" \"f\" SET \"kind\" = \"c\".\"test\" FROM (SELECT \"b\".\"test\" FROM \"bar\" AS \"b\" WHERE \"b\".\"id\" = ?) AS \"c\" WHERE \"f\".\"kind\" = ?" 1 "drama"]
(->
{:update [:films :f]
:set {:kind :c.test}
:from [[{:select [:b.test]
:from [[:bar :b]]
:where [:= :b.id 1]} :c]]
:where [:= :f.kind "drama"]}
(format {:quoted true}))))
;; issue 317
(is (=
["UPDATE \"films\" \"f\" SET \"kind\" = \"c\".\"test\" FROM (SELECT \"b\".\"test\" FROM \"bar\" AS \"b\" WHERE \"b\".\"id\" = ?) AS \"c\" WHERE \"f\".\"kind\" = ?" 1 "drama"]
(->
{:update [:films :f]
;; drop ns in set clause...
:set {:f/kind :c.test}
:from [[{:select [:b.test]
:from [[:bar :b]]
:where [:= :b.id 1]} :c]]
:where [:= :f.kind "drama"]}
(format {:quoted true}))))
(is (=
["UPDATE \"films\" \"f\" SET \"f\".\"kind\" = \"c\".\"test\" FROM (SELECT \"b\".\"test\" FROM \"bar\" AS \"b\" WHERE \"b\".\"id\" = ?) AS \"c\" WHERE \"f\".\"kind\" = ?" 1 "drama"]
(->
{:update [:films :f]
;; ...but keep literal dotted name
:set {:f.kind :c.test}
:from [[{:select [:b.test]
:from [[:bar :b]]
:where [:= :b.id 1]} :c]]
:where [:= :f.kind "drama"]}
(format {:quoted true})))))
(deftest set-after-join
(is (=
["UPDATE `foo` INNER JOIN `bar` ON `bar`.`id` = `foo`.`bar_id` SET `a` = ? WHERE `bar`.`b` = ?" 1 42]
(->
{:update :foo
:join [:bar [:= :bar.id :foo.bar_id]]
:set {:a 1}
:where [:= :bar.b 42]}
(format {:dialect :mysql}))))
;; issue 344
(is (=
["UPDATE `foo` INNER JOIN `bar` ON `bar`.`id` = `foo`.`bar_id` SET `f`.`a` = ? WHERE `bar`.`b` = ?" 1 42]
(->
{:update :foo
:join [:bar [:= :bar.id :foo.bar_id]]
;; do not drop ns in set clause for MySQL:
:set {:f/a 1}
:where [:= :bar.b 42]}
(format {:dialect :mysql})))))
(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 on-conflict-tests
;; these examples are taken from https://www.postgresqltutorial.com/postgresql-upsert/
(is (= ["
INSERT INTO customers (name, email)
VALUES ('Microsoft', 'hotline@microsoft.com')
ON CONFLICT ON CONSTRAINT customers_name_key
DO NOTHING
"]
(format {:insert-into :customers
:columns [:name :email]
:values [[[:inline "Microsoft"], [:inline "hotline@microsoft.com"]]]
:on-conflict {:on-constraint :customers_name_key}
:do-nothing true}
{:pretty true})))
(is (= ["
INSERT INTO customers (name, email)
VALUES ('Microsoft', 'hotline@microsoft.com')
ON CONFLICT
ON CONSTRAINT customers_name_key
DO NOTHING
"]
(format {:insert-into :customers
:columns [:name :email]
:values [[[:inline "Microsoft"], [:inline "hotline@microsoft.com"]]]
:on-conflict []
:on-constraint :customers_name_key
:do-nothing true}
{:pretty true})))
(is (= ["
INSERT INTO customers (name, email)
VALUES ('Microsoft', 'hotline@microsoft.com')
ON CONFLICT (name)
DO NOTHING
"]
(format {:insert-into :customers
:columns [:name :email]
:values [[[:inline "Microsoft"], [:inline "hotline@microsoft.com"]]]
:on-conflict :name
:do-nothing true}
{:pretty true})))
(is (= ["
INSERT INTO customers (name, email)
VALUES ('Microsoft', 'hotline@microsoft.com')
ON CONFLICT (name)
DO NOTHING
"]
(format {:insert-into :customers
:columns [:name :email]
:values [[[:inline "Microsoft"], [:inline "hotline@microsoft.com"]]]
:on-conflict [:name]
:do-nothing true}
{:pretty true})))
(is (= ["
INSERT INTO customers (name, email)
VALUES ('Microsoft', 'hotline@microsoft.com')
ON CONFLICT ((foo + ?), name, (TRIM(email)))
DO NOTHING
" 1]
(format {:insert-into :customers
:columns [:name :email]
:values [[[:inline "Microsoft"], [:inline "hotline@microsoft.com"]]]
:on-conflict [[:+ :foo 1] :name [:trim :email]]
:do-nothing true}
{:pretty true})))
(is (= ["
INSERT INTO customers (name, email)
VALUES ('Microsoft', 'hotline@microsoft.com')
ON CONFLICT (name)
DO UPDATE SET email = EXCLUDED.email || ';' || customers.email
"]
(format {:insert-into :customers
:columns [:name :email]
:values [[[:inline "Microsoft"], [:inline "hotline@microsoft.com"]]]
:on-conflict :name
:do-update-set {:email [:|| :EXCLUDED.email [:inline ";"] :customers.email]}}
{:pretty true}))))
(deftest issue-401-dialect
(testing "registering a dialect that upper-cases idents"
(sut/register-dialect! ::MYSQL (update (sut/get-dialect :mysql) :quote comp sut/upper-case))
(is (= ["SELECT `foo` FROM `bar`"]
(sut/format {:select :foo :from :bar} {:dialect :mysql})))
(is (= ["SELECT `FOO` FROM `BAR`"]
(sut/format {:select :foo :from :bar} {:dialect ::MYSQL})))))
(deftest issue-321-linting
(testing "empty IN is ignored by default"
(is (= ["WHERE x IN ()"]
(format {:where [:in :x []]})))
(is (= ["WHERE x IN ()"]
(format {:where [:in :x :?y]}
{:params {:y []}}))))
(testing "empty IN is flagged in basic mode"
(is (thrown-with-msg? ExceptionInfo #"empty collection"
(format {:where [:in :x []]}
{:checking :basic})))
(is (thrown-with-msg? ExceptionInfo #"empty collection"
(format {:where [:in :x :?y]}
{:params {:y []} :checking :basic}))))
(testing "IN NULL is ignored by default and basic"
(is (= ["WHERE x IN (NULL)"]
(format {:where [:in :x [nil]]})))
(is (= ["WHERE x IN (NULL)"]
(format {:where [:in :x [nil]]}
{:checking :basic})))
(is (= ["WHERE x IN (?)" nil]
(format {:where [:in :x :?y]}
{:params {:y [nil]}})))
(is (= ["WHERE x IN (?)" nil]
(format {:where [:in :x :?y]}
{:params {:y [nil]} :checking :basic})))
(is (= ["WHERE x IN ($2)" nil nil]
(format {:where [:in :x :?y]}
{:params {:y [nil]} :numbered true})))
(is (= ["WHERE x IN ($2)" nil nil]
(format {:where [:in :x :?y]}
{:params {:y [nil]} :checking :basic :numbered true}))))
(testing "IN NULL is flagged in strict mode"
(is (thrown-with-msg? ExceptionInfo #"does not match"
(format {:where [:in :x [nil]]}
{:checking :strict})))
(is (thrown-with-msg? ExceptionInfo #"does not match"
(format {:where [:in :x :?y]}
{:params {:y [nil]} :checking :strict}))))
(testing "empty WHERE clauses ignored with none"
(is (= ["DELETE FROM foo"]
(format {:delete-from :foo})))
(is (= ["DELETE foo"]
(format {:delete :foo})))
(is (= ["UPDATE foo SET x = ?" 1]
(format {:update :foo :set {:x 1}}))))
(testing "empty WHERE clauses flagged in basic mode"
(is (thrown-with-msg? ExceptionInfo #"without a non-empty"
(format {:delete-from :foo} {:checking :basic})))
(is (thrown-with-msg? ExceptionInfo #"without a non-empty"
(format {:delete :foo} {:checking :basic})))
(is (thrown-with-msg? ExceptionInfo #"without a non-empty"
(format {:update :foo :set {:x 1}} {:checking :basic})))))
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 upsert-test
(testing "upsert sql generation for postgresql"
(is (= ["INSERT INTO distributors (did, dname) VALUES (?, ?), (?, ?) ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname RETURNING *" 5 "Gizmo Transglobal" 6 "Associated Computing, Inc"]
;; preferred in honeysql:
(-> (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)))
(is (= ["INSERT INTO distributors (did, dname) VALUES (?, ?), (?, ?) ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname RETURNING *" 5 "Gizmo Transglobal" 6 "Associated Computing, Inc"]
;; identical to nilenso version:
(-> (insert-into :distributors)
(values [{:did 5 :dname "Gizmo Transglobal"}
{:did 6 :dname "Associated Computing, Inc"}])
(upsert (-> (on-conflict :did)
(do-update-set :dname)))
(returning :*)
sql/format)))
(is (= ["INSERT INTO distributors (did, dname) VALUES (?, ?) ON CONFLICT (did) DO NOTHING" 7 "Redline GmbH"]
;; preferred in honeysql:
(-> (insert-into :distributors)
(values [{:did 7 :dname "Redline GmbH"}])
(on-conflict :did)
do-nothing
sql/format)))
(is (= ["INSERT INTO distributors (did, dname) VALUES (?, ?) ON CONFLICT (did) DO NOTHING" 7 "Redline GmbH"]
;; identical to nilenso version:
(-> (insert-into :distributors)
(values [{:did 7 :dname "Redline GmbH"}])
(upsert (-> (on-conflict :did)
do-nothing))
sql/format)))
(is (= ["INSERT INTO distributors (did, dname) VALUES (?, ?) ON CONFLICT ON CONSTRAINT distributors_pkey DO NOTHING" 9 "Antwerp Design"]
;; preferred in honeysql:
(-> (insert-into :distributors)
(values [{:did 9 :dname "Antwerp Design"}])
(on-conflict (on-constraint :distributors_pkey))
do-nothing
sql/format)))
(is (= ["INSERT INTO distributors (did, dname) VALUES (?, ?) ON CONFLICT (did) ON CONSTRAINT distributors_pkey DO NOTHING" 9 "Antwerp Design"]
;; with both name and clause:
(-> (insert-into :distributors)
(values [{:did 9 :dname "Antwerp Design"}])
(on-conflict :did (on-constraint :distributors_pkey))
do-nothing
sql/format)))
(is (= ["INSERT INTO distributors (did, dname) VALUES (?, ?) ON CONFLICT (did, dname) ON CONSTRAINT distributors_pkey DO NOTHING" 9 "Antwerp Design"]
;; with multiple names and a clause:
(-> (insert-into :distributors)
(values [{:did 9 :dname "Antwerp Design"}])
(on-conflict :did :dname (on-constraint :distributors_pkey))
do-nothing
sql/format)))
(is (= ["INSERT INTO distributors (did, dname) VALUES (?, ?) ON CONFLICT ON CONSTRAINT distributors_pkey DO NOTHING" 9 "Antwerp Design"]
;; almost identical to nilenso version:
(-> (insert-into :distributors)
(values [{:did 9 :dname "Antwerp Design"}])
;; in nilenso, this was (on-conflict-constraint :distributors_pkey)
(upsert (-> (on-conflict (on-constraint :distributors_pkey))
do-nothing))
sql/format)))
(is (= ["INSERT INTO foo (id, data) VALUES (?, ?) ON CONFLICT (id) DO UPDATE SET into = ((STATE(?), MODIFIED(NOW()))) WHERE state = ?" 1 42 "enabled" "disabled"]
(sql/format (-> (insert-into :foo)
(values [{:id 1 :data 42}])
(upsert (-> (on-conflict :id)
(do-update-set [:state "enabled"]
[:modified [:now]])
(where [:= :state "disabled"])))))))
(is (= ["INSERT INTO foo (id, data) VALUES (?, ?) ON CONFLICT (id) DO UPDATE SET state = ?, modified = NOW() WHERE state = ?" 1 42 "enabled" "disabled"]
(sql/format (-> (insert-into :foo)
(values [{:id 1 :data 42}])
(upsert (-> (on-conflict :id)
(do-update-set {:state "enabled"
:modified [:now]})
(where [:= :state "disabled"])))))))
(is (= ["INSERT INTO distributors (did, dname) VALUES (?, ?), (?, ?) ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname" 10 "Pinp Design" 11 "Foo Bar Works"]
(sql/format {:insert-into :distributors
:values [{:did 10 :dname "Pinp Design"}
{:did 11 :dname "Foo Bar Works"}]
;; in nilenso, these two were a submap under :upsert
:on-conflict :did
:do-update-set :dname})))
(is (= ["INSERT INTO distributors (did, dname) VALUES (?, ?) ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname || ? || d.dname || ?" 23 "Foo Distributors" " (formerly " ")"]
(-> (insert-into :distributors)
(values [{:did 23 :dname "Foo Distributors"}])
(on-conflict :did)
;; nilenso:
#_(do-update-set! [:dname "EXCLUDED.dname || ' (formerly ' || d.dname || ')'"])
;; honeysql
(do-update-set {:dname [:|| :EXCLUDED.dname " (formerly " :d.dname ")"]})
sql/format)))
(is (= ["INSERT INTO distributors (did, dname) SELECT ?, ? ON CONFLICT ON CONSTRAINT distributors_pkey DO NOTHING" 1 "whatever"]
;; honeysql version:
(-> (insert-into :distributors
[:did :dname]
(select 1 "whatever"))
(on-conflict (on-constraint :distributors_pkey))
do-nothing
sql/format)
;; nilenso version:
#_(-> (insert-into :distributors)
(columns :did :dname)
(query-values (select 1 "whatever"))
(upsert (-> (on-conflict-constraint :distributors_pkey)
do-nothing))
sql/format)))))
(deftest upsert-where-test
(is (= ["INSERT INTO user (phone, name) VALUES (?, ?) ON CONFLICT (phone) WHERE phone IS NOT NULL DO UPDATE SET phone = EXCLUDED.phone, name = EXCLUDED.name WHERE user.active = FALSE" "5555555" "John"]
(sql/format
{:insert-into :user
:values [{:phone "5555555" :name "John"}]
:on-conflict [:phone
{:where [:<> :phone nil]}]
:do-update-set {:fields [:phone :name]
:where [:= :user.active false]}})
;; nilenso version
#_(sql/format
{:insert-into :user
:values [{:phone "5555555" :name "John"}]
;; nested under :upsert
:upsert {:on-conflict [:phone]
;; but :where is at the same level as :on-conflict
:where [:<> :phone nil]
;; this is the same as in honeysql:
:do-update-set {:fields [:phone :name]
:where [:= :user.active false]}}}))))
(deftest returning-test
(testing "returning clause in sql generation for postgresql"
(is (= ["DELETE FROM distributors WHERE did > 10 RETURNING *"]
(sql/format {:delete-from :distributors
:where [:> :did :10]
:returning [:*]})))
(is (= ["UPDATE distributors SET dname = ? WHERE did = 2 RETURNING did, dname" "Foo Bar Designs"]
(-> (update :distributors)
(set {:dname "Foo Bar Designs"})
(where [:= :did :2])
(returning :did :dname)
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 insert-into-with-alias
(testing "insert into with alias"
(is (= ["INSERT INTO distributors AS d (did, dname) VALUES (?, ?), (?, ?) ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname WHERE d.zipcode <> ? RETURNING d.*" 5 "Gizmo Transglobal" 6 "Associated Computing, Inc" "21201"]
;; honeysql supports alias in insert-into:
(-> (insert-into :distributors :d)
;; nilensor required insert-into-as:
#_(insert-into-as :distributors :d)
(values [{:did 5 :dname "Gizmo Transglobal"}
{:did 6 :dname "Associated Computing, Inc"}])
(on-conflict :did)
;; honeysql supports names and a where clause:
(do-update-set :dname (where [:<> :d.zipcode "21201"]))
;; nilenso nested those under upsert:
#_(upsert (-> (on-conflict :did)
(do-update-set :dname)
(where [:<> :d.zipcode "21201"])))
(returning :d.*)
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]]))
(defn my-update [& args] (h/generic-helper-unary :update args))
(defn my-set [& args] (h/generic-helper-unary :set args))
(defn my-where [& args] (h/generic-helper-variadic :where args))
(deftest custom-helpers-test
(testing "nil join"
(is (= ["UPDATE foo SET bar = ? WHERE quux = ?" 1 2]
(-> (my-update :foo)
(my-set {:bar 1})
(my-where (sql/map= {:quux 2}))
sql/format)))))
(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 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]))))