postmodern-devel October 2013

postmodern-devel@common-lisp.net

2 participants
2 discussions

[postmodern-devel] A question about connection pooling, and the many ways to connect.
by Jim Barrows 12 Nov '13

12 Nov '13

I'm a java developer, and I think I'm expecting something to happen that's not. In Java I setup a pooled connection, and just use it. The connections pool themselves. I had thought that by doing: (setf *database* (connect "PomBanDone" "pombandone" "pombandone" "localhost" :pooled-p T))) that I would get connection pooling. However now that I'm getting a better understanding of Lisp and I think Postmodern, that connections aren't getting pooled. I think I have to use (with-connection (spec &body body) in order to make the connection pooling work. Is that the case? If not, why would I want to use with-connection? Thanks! -- James A Barrows

3 2

Possible additional sql-ops
by Sabra Crolleton 26 Oct '13

26 Oct '13

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

2 1

2024

2023

2022

2021

2020

2019

2018

2017

2016

2015

2014

2013

2012

2011

2010

2009

2008

2007

2006

postmodern-devel October 2013