- postmodern-devel - mailman3.common-lisp.net

doquery for DAOs
by Eli Naeher 26 Dec '13

26 Dec '13

Hello, I am working on implementing something like doquery that operates on DAOs--that is, something that will allow me to iterate over all the DAOs matching a certain test without consing up all the instances at once. Before I spend too much time on this, I thought I'd see if anyone else has done something like this already, or if there is a simple way to do this with the existing doquery that I have overlooked. Thank you, -Eli

2 1

Parsing for double precision not working yet
by Daniel Brunner 28 Nov '13

28 Nov '13

[NB: Marjin, I already posted this issue on Github] Hello! There is a problem with columns with double precision. Test case: --8<-- (pomo:query "create table ftest (value double precision)") (pomo:query "insert into ftest values ($1)" (coerce 100.10 'double-float)) --8<-- This fails with: --8<-- Database error 22P02: invalid input syntax for type double precision: "100.0999984741211D0" [Condition of type CL-POSTGRES-ERROR:DATA-EXCEPTION] --8<-- A short patch in cl-postgres/sql-string.lisp could solve that problem: --8<-- diff --git a/cl-postgres/sql-string.lisp b/cl-postgres/sql-string.lisp index 3157d30..4dff4be 100644 --- a/cl-postgres/sql-string.lisp +++ b/cl-postgres/sql-string.lisp @@ -84,6 +84,8 @@ whether the string should be escaped before being put into a query.") (princ-to-string arg)) (:method ((arg float)) (format nil "~f" arg)) + (:method ((arg double-float)) + (format nil "~,,,,,,'EE" arg)) (:method ((arg ratio)) ;; Possible optimization: we could probably build up the same binary structure postgres --8<-- Best wishes, Daniel

2 1

[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

What is the easiest way to get the col-type of a slot?
by Sabra Crolleton 08 Sep '13

08 Sep '13

Feeling a bit brain dead at the moment. I am trying to do a sanity check on user-input before the data gets put in a dao-class. That requires simply checking the col-type for the slot, comparing it to the type for the data and, if they do not match, rejecting the data. I can do it looping through postmodern:dao-column-slots using postmodern::column-type, but is there a way of doing it with exported functions? Sabra

2 1

cl-postgres convert nil to false
by Julien Danjou 29 Jul '13

29 Jul '13

Hi there, I'm dealing with a problem and I'd like some enlightenment. I've a table with text columns that can't be NULL. In my Lisp program, sometimes I'm assigning `nil' as value for this column, either via a direct query execution or via the DAO. What I would expect in this case, is to PostgreSQL to raise an error. But this does not happen. What happens is that (cl-postgres:to-sql-string nil) returns "false", and that the query is built to: INSERT INTO mytable (mytextfield) VALUES (false); PostgreSQL implicit type conversion kicks in and insert the string 'false' into the field, which does not trigger an error since this it not NULL. The obvious fix here is to patch all my code to convert nil to :null, but this is a pain. I don't want to do the type checking I'm doing with PostgreSQL one more time in my application. What I'd like is for cl-postgres to consider nil as being NULL. The boolean false value could be mapped as :false, which would also make sure no code is inserting false just because a value is actually nil. The change is pretty trivial and I'm running with it right now, which allowed me to debug a lot of implicit nil conversion to 'false' in text fields. I recognize this is something that would break compatibility, so that might be enabled via a defparameter or something. Does that sounds like a good idea, or do I miss an obvious problem? -- Julien Danjou ;; Free Software hacker ; freelance consultant ;; http://julien.danjou.info

2 2

Timezone handling
by Julien Danjou 25 Jul '13

25 Jul '13

Hi there, I'm struggling with timezone handling in Postmodern. I'm using timestamp with time zone column types, and I'm looking for the correct way to feed postmodern such information on insert. Basically, I don't see any class that would fit. I've tried to grep through the code but didn't find much about timestamp/timezone handling. Concretely, I'm having UTC timestamp that I want to insert into those timestamp-with-time-zone columns, but I don't know how to tell Postmodern that it should postfix them with something like +00 so PG understand it should not default them to its default timezone. On the same topic, I thought that one easy solution would be to set the session timezone to UTC. It's easy enough to come with a macro doing a SET TIMEZONE='UTC' before each request, but honestly, I'd like to avoid that and do it only at connection time. Now, I've read through cl-postgres code, and I was hoping being able to plug my code at connection time, but it seems everything is defined as function and not as method, which seems weird to me, and not really pluggable in the end. Any hint appreciated! Cheers, -- Julien Danjou ;; Free Software hacker ; freelance consultant ;; http://julien.danjou.info

3 6

nyef's postmodern gripes
by Zach Beane 25 Jul '13

25 Jul '13

Postmodern fits my needs very well, but whenever it comes up in discussion, nyef has dropped vague disparagement. I pestered him to write down some specifics, so here they are: http://paste.lisp.org/display/138060 Zach

3 2

nyef's postmodern gripes
by Sabra Crolleton 20 Jul '13

20 Jul '13

Is there a better way to write "common table expressions " (with and with recursive) than: (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-recursive (&rest args) (let ((x (butlast args)) (y (last args))) `("WITH RECURSIVE " ,@(sql-expand-list x) ,@(sql-expand (car y))))) They work for me, but I have the feeling there is a better way to do it. Actual use would be something like the following (using the example from the postgresql website): (defun test2 () (query (:with-recursive (:as 'regional-sales (:select 'region (:as (:sum 'amount) 'total-sales) :from 'orders :group-by 'region)) (:as 'top-regions (:select 'region :from 'regional-sales :where (:> 'total-sales (:select (:/ (:sum 'total-sales) 10) :from 'regional-sales)))) (:select 'region 'product (:as (:sum 'quantity) 'product-units) (:as (:sum 'amount) 'product-sales) :from 'orders :where (:in 'region (:select 'region :from 'top-regions)) :group-by 'region 'product)))) Sabra

1 0

[PATCH] Add missing closing parent in example
by Julien Danjou 27 Jun '13

27 Jun '13

--- doc/s-sql.html | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/doc/s-sql.html b/doc/s-sql.html index a6b0bad..7a8476a 100644 --- a/doc/s-sql.html +++ b/doc/s-sql.html @@ -595,7 +595,7 @@ (age :type integer) (address :type (or db-null string) :references (important-addresses :cascade :cascade)) (fatal-weakness :type text :default "None") - (identifying-color :type (string 20) :unique t) + (identifying-color :type (string 20) :unique t)) (:foreign-key (identifying-color) (colors name)) (:constraint enemy-age-check :check (:> 'age 12)))</pre> -- 1.8.3.1

2 1