honeysql-h2

LISTAGG ( [ DISTINCT | ALL ] expression [, separator]
  [ ON OVERFLOW { ERROR | TRUNCATE [filler] { WITH | WITHOUT } COUNT } ] )
  WITHIN GROUP (ORDER BY ...)
  [ FILTER (WHERE ...) ]
  [ OVER (...) ]

;; Basic LISTAGG
(sql/format {:select [[[:listagg :name ", "] :names]]
             :from [:users]})
;; => ["SELECT LISTAGG(name, ', ') AS names FROM users"]

;; With DISTINCT
(sql/format {:select [[[:listagg-distinct :name ", "] :names]]
             :from [:users]})
;; => ["SELECT LISTAGG(DISTINCT name, ', ') AS names FROM users"]

;; With WITHIN GROUP (ordering)
(sql/format {:select [[[:within-group
                        [:listagg :name ", "]
                        {:order-by [:name]}]
                       :names]]
             :from [:users]})
;; => ["SELECT LISTAGG(name, ', ') WITHIN GROUP (ORDER BY name ASC) AS names FROM users"]

;; With FILTER clause
(sql/format {:select [[[:filter
                        [:within-group [:listagg :name ", "] {:order-by [:name]}]
                        {:where [:= :active true]}]
                       :names]]
             :from [:users]})
;; => ["SELECT LISTAGG(name, ', ') WITHIN GROUP (ORDER BY name ASC)
;;      FILTER (WHERE active = TRUE) AS names FROM users"]

;; ON OVERFLOW ERROR (default H2 behavior)
[:listagg :name ", " :error]

;; ON OVERFLOW TRUNCATE
[:listagg :name ", " [:truncate]]

;; ON OVERFLOW TRUNCATE with custom filler
[:listagg :name ", " [:truncate "..."]]

;; ON OVERFLOW TRUNCATE with filler and count indicator
[:listagg :name ", " [:truncate "..." :with-count]]
;; => LISTAGG(name, ', ' ON OVERFLOW TRUNCATE '...' WITH COUNT)

[:listagg :name ", " [:truncate "..." :without-count]]
;; => LISTAGG(name, ', ' ON OVERFLOW TRUNCATE '...' WITHOUT COUNT)

(require '[honeysql-h2.core :refer [listagg]])

;; Simple usage
(listagg :name)
;; => [:listagg :name]

;; With separator
(listagg :name :separator ", ")
;; => [:listagg :name ", "]

;; With DISTINCT
(listagg :name :separator ", " :distinct true)
;; => [:listagg-distinct :name ", "]

;; With ordering
(listagg :name :separator ", " :within-group {:order-by [:name]})
;; => [:within-group [:listagg :name ", "] {:order-by [:name]}]

;; Full example with all options
(listagg :name
         :separator ", "
         :distinct true
         :on-overflow [:truncate "..." :with-count]
         :within-group {:order-by [:name]}
         :filter [:= :active true])

;; Use in a query
(sql/format {:select [[(listagg :name
                                :separator ", "
                                :within-group {:order-by [:name]}
                                :filter [:> :age 18])
                       :adult_names]]
             :from [:users]
             :group-by [:department]})

;; Using the helper
(listagg :name
         :separator ", "
         :within-group {:order-by [:name]}
         :over {:partition-by [:department]})

;; Direct HoneySQL syntax (note the nested vector structure)
(sql/format {:select [[[:over
                        [[[:within-group [:listagg :name ", "] {:order-by [:name]}]
                          {:partition-by [:department]}]]]
                       :dept_names]]
             :from [:employees]})

-- Create
CREATE SEQUENCE [IF NOT EXISTS] name [AS type] [START WITH n] [INCREMENT BY n]
  [MINVALUE n | NO MINVALUE] [MAXVALUE n | NO MAXVALUE] [CYCLE | NO CYCLE] [CACHE n | NO CACHE]

-- Alter
ALTER SEQUENCE [IF EXISTS] name [RESTART [WITH n]] [INCREMENT BY n] ...

-- Drop
DROP SEQUENCE [IF EXISTS] name

-- Value expressions
NEXT VALUE FOR sequence_name
CURRENT VALUE FOR sequence_name

;; Basic sequence
(sql/format {:create-sequence :my_seq})
;; => ["CREATE SEQUENCE my_seq"]

;; With IF NOT EXISTS
(sql/format {:create-sequence [:my_seq {:if-not-exists true}]})
;; => ["CREATE SEQUENCE IF NOT EXISTS my_seq"]

;; With all options
(sql/format {:create-sequence [:my_seq {:if-not-exists true
                                         :as :bigint
                                         :start-with 100
                                         :increment-by 10
                                         :minvalue 1
                                         :maxvalue 1000
                                         :cycle true
                                         :cache 32}]})
;; => ["CREATE SEQUENCE IF NOT EXISTS my_seq AS BIGINT START WITH 100
;;      INCREMENT BY 10 MINVALUE 1 MAXVALUE 1000 CYCLE CACHE 32"]

;; Restart with value
(sql/format {:alter-sequence [:my_seq {:restart-with 500}]})
;; => ["ALTER SEQUENCE my_seq RESTART WITH 500"]

;; With IF EXISTS
(sql/format {:alter-sequence [:my_seq {:if-exists true :restart-with 1}]})
;; => ["ALTER SEQUENCE IF EXISTS my_seq RESTART WITH 1"]

;; Modify increment
(sql/format {:alter-sequence [:my_seq {:increment-by 5}]})
;; => ["ALTER SEQUENCE my_seq INCREMENT BY 5"]

;; Basic drop
(sql/format {:drop-sequence :my_seq})
;; => ["DROP SEQUENCE my_seq"]

;; With IF EXISTS
(sql/format {:drop-sequence [:my_seq {:if-exists true}]})
;; => ["DROP SEQUENCE IF EXISTS my_seq"]

;; In SELECT
(sql/format {:select [[[:next-value-for :my_seq] :id]]})
;; => ["SELECT NEXT VALUE FOR my_seq AS id"]

(sql/format {:select [[[:current-value-for :my_seq] :current_id]]})
;; => ["SELECT CURRENT VALUE FOR my_seq AS current_id"]

;; In INSERT
(sql/format {:insert-into :users
             :values [{:id [:next-value-for :my_seq] :name "Alice"}]})
;; => ["INSERT INTO users (id, name) VALUES (NEXT VALUE FOR my_seq, ?)" "Alice"]

;; Schema-qualified sequences
(sql/format {:select [[[:next-value-for :myschema.my_seq] :id]]})
;; => ["SELECT NEXT VALUE FOR myschema.my_seq AS id"]

(require '[honeysql-h2.core :as h2])

;; CREATE SEQUENCE
(h2/create-sequence :my_seq)
;; => {:create-sequence :my_seq}

(h2/create-sequence :my_seq {:start-with 100 :increment-by 10})
;; => {:create-sequence [:my_seq {:start-with 100 :increment-by 10}]}

;; ALTER SEQUENCE
(h2/alter-sequence :my_seq {:restart-with 500})
;; => {:alter-sequence [:my_seq {:restart-with 500}]}

;; DROP SEQUENCE
(h2/drop-sequence :my_seq)
;; => {:drop-sequence :my_seq}

(h2/drop-sequence :my_seq {:if-exists true})
;; => {:drop-sequence [:my_seq {:if-exists true}]}

;; Value expressions for use in queries
(h2/nextval :my_seq)
;; => [:next-value-for :my_seq]

(h2/currval :my_seq)
;; => [:current-value-for :my_seq]

;; Full workflow example
(sql/format (h2/create-sequence :user_id_seq {:start-with 1000}))
;; => ["CREATE SEQUENCE user_id_seq START WITH 1000"]

(sql/format {:insert-into :users
             :values [{:id (h2/nextval :user_id_seq) :name "Bob"}]})
;; => ["INSERT INTO users (id, name) VALUES (NEXT VALUE FOR user_id_seq, ?)" "Bob"]