Possible additional sql-ops

[Marijn Haverbeke]

These look sane. I'd unify :partition-by and :partition-by-order-by
into a single operation (:partition-by ... :order-by ...) by using
split-on-keywords. If you could submit a pull request, preferably even
with some documentation, I'll be happy to merge it in.

Best,
Marijn

On Sat, Oct 26, 2013 at 6:22 PM, Sabra Crolleton
<sabra.crolleton at gmail.com> wrote:
> I have been writing queries requiring window functions and recursive with
> functions. I finally decided to try to write some sql-ops so that I can use
> s-sql. They work for me but would appreciate comments. I did include a
> :parens op because I find that sometimes I just need an additional set of
> parens for an sql query. Below are the functions, the amendments to :select
> and :order-by and usage examples.
>
> (def-sql-op :over (form &rest args)
>   (if args `("(" ,@(sql-expand form) " OVER " ,@(sql-expand-list args) ")")
>           `("(" ,@(sql-expand form) " OVER ()) ")))
>
> (def-sql-op :partition-by (&rest args)
>
>   `("(PARTITION BY " ,@(sql-expand-list args) ")"))
>
> (def-sql-op :partition-by-order-by (form &rest fields)
>   `("(PARTITION BY " ,@(sql-expand form) " ORDER BY " ,@(sql-expand-list
> fields) ") "))
>
> (def-sql-op :parens (op) `(" (" ,@(sql-expand op) ") "))
>
> (def-sql-op :with (&rest args)
>   (let ((x (butlast args)) (y (last args)))
>     `("WITH " ,@(sql-expand-list x) ,@(sql-expand (car y)))))
>
> (def-sql-op :with-recursive1 (form1 form2)
>   `("WITH RECURSIVE " ,@(sql-expand form1) ,@(sql-expand form2)))
>
> (def-sql-op :window (form)
>   `("WINDOW " ,@(sql-expand form)))
>
>
> Amendment to :select (to pick up the new "window" arg
>
> (def-sql-op :select (&rest args)
>   (split-on-keywords ((vars *) (distinct - ?) (distinct-on * ?) (from * ?)
> (window ?) (where ?) (group-by * ?)
>                       (having ?)) (cons :vars args)
>     `("(SELECT "
>       ,@(if distinct '("DISTINCT "))
>       ,@(if distinct-on `("DISTINCT ON (" ,@(sql-expand-list distinct-on) ")
> "))
>       ,@(sql-expand-list vars)
>       ,@(if from (cons " FROM " (expand-joins from)))
>       ,@(if window (cons " WINDOW " (sql-expand-list window)))
>       ,@(if where (cons " WHERE " (sql-expand (car where))))
>       ,@(if group-by (cons " GROUP BY " (sql-expand-list group-by)))
>       ,@(if having (cons " HAVING " (sql-expand (car having))))
>       ")")))
>
> Amendment to :order-by to pick up the situation where the only arg is the
> form
>
>
> (def-sql-op :order-by (form &rest fields)
>   (if fields
>       `("(" ,@(sql-expand form) " ORDER BY " ,@(sql-expand-list fields) ")")
>       `("( ORDER BY " ,@(sql-expand form) ")")))
>
>
> Usage Examples:
> Over Examples generally following
> http://www.postgresql.org/docs/9.3/static/tutorial-window.html
>
> (query (:select 'salary (:over (:sum 'salary))
>                 :from 'empsalary))
>
> (query (:select 'depname 'empno 'salary
>                 (:over (:avg 'salary)
>                        (:partition-by 'depname))
>                 :from 'empsalary))
>
>
> (query (:select 'depname 'empno 'salary
>                 (:over (:rank)
>                        (:partition-by-order-by 'depname (:desc 'salary)))
>                 :from 'empsalary))
>
> (query (:select (:over (:sum 'salary) 'w)
>               (:over (:avg 'salary) 'w)
>               :from 'empsalary :window
>               (:as 'w (:partition-by-order-by 'depname
>                                               (:desc 'salary)))))
>
>
> With Examples
>
> (query (:with (:as 'upd
>
>                  (:parens
>                   (:update 'employees :set 'sales-count (:+ 'sales-count 1)
>                            :where (:= 'id
>                                       (:select 'sales-person
>                                                :from 'accounts
>                                                :where (:= 'name "Acme
> Corporation")))
>                            :returning '*)))
>             (:insert-into 'employees-log
>                           (:select '* 'current-timestamp :from
>                  'upd))))
>
> With-Recursive Examples following
> http://www.postgresql.org/docs/current/static/queries-with.html
>
> (query (:with-recursive
>       (:as (:t1 'n)
>            (:union-all (:values 1)
>                        (:select (:+ 'n 1)
>                                 :from 't1
>                                 :where (:< 'n 100))))
>       (:select (:sum 'n) :from 't1)))
>
> (query (:with-recursive
>       (:as (:included_parts 'sub-part 'part 'quantity)
>            (:union-all
>             (:select 'sub-part 'part 'quantity
>                      :from 'parts
>                      :where (:= 'part "our-product"))
>             (:select 'p.sub-part 'p.part 'p.quantity
>                      :from (:as 'included-parts 'pr)
>                      (:as 'parts 'p)
>                      :where (:= 'p.part 'pr.sub-part) )))
>       (:select 'sub-part (:as (:sum 'quantity) 'total-quantity)
>                :from 'included-parts
>                :group-by 'sub-part)))
>
> (query (:with-recursive
>       (:as (:search-graph 'id 'link 'data 'depth)
>            (:union-all (:select 'g.id 'g.link 'g.data 1
>                                 :from (:as 'graph 'g))
>                        (:select 'g.id 'g.link 'g.data (:+ 'sg.depth 1)
>                                 :from (:as 'graph 'g) (:as 'search-graph
> 'sg)
>                                 :where (:= 'g.id 'sg.link))))
>       (:select '* :from 'search-graph)))
>
> (query (:with-recursive
>       (:as (:search-graph 'id 'link 'data'depth 'path 'cycle)
>            (:union-all
>             (:select 'g.id 'g.link 'g.data 1
>                      (:[] 'g.f1 'g.f2) nil
>                      :from (:as 'graph 'g))
>             (:select 'g.id 'g.link 'g.data (:+ 'sg.depth 1)
>                      (:|| 'path (:row 'g.f1 'g.f2))
>                      (:= (:row 'g.f1 'g.f2)
>                          (:any* 'path))
>                      :from (:as 'graph 'g)
>                      (:as 'search-graph 'sg)
>                      :where (:and (:= 'g.id 'sg.link)
>                                   (:not 'cycle)))))
>       (:select '* :from 'search-graph)))
>
>
>