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

[postmodern-devel] Selecting schema with dao-class
by Slobodan Milnović 11 Dec '10

11 Dec '10

Hi, I'm trying to find any documentation about selecting database schema with dao-class, for example something like this: (defclass my-class () ((some-column :column t :accessor some-column :initarg some-column) ..... itd)) (:metaclass postmodern:dao-class) (:table-name (:dot 'scheme-name 'table-name)) (:keys some-column)) But it seems (:dot 'scheme-name 'table-name) format is supported only in queries? How can I select an schema in database?

2 2

[postmodern-devel] Minimum overhead per query
by Andy Chambers 09 Dec '10

09 Dec '10

Hi, We're finding that even very simple queries (e.g. "select 1;") take around 220ms to return a result. The same query is reported as taking ~120ms on pgAdmin which suggests a postmodern overhead of around 100ms per query. Does this sound right to others on the list? Is there any way to reduce this overhead for applications that may generate lots of small simple queries? Cheers, Andy -- Andy Chambers

3 2

[postmodern-devel] Quoted lists with QUERY or SQL
by Brit Butler 30 Nov '10

30 Nov '10

Hello, I'm working on a Postmodern backend for weblocks. To conform to their Store API<http://bitbucket.org/S11001001/weblocks-dev/src/f14b59da2238/src/store/stor…> I need to implement a count-persistent-objects method. I would like to be able to write something like: (defmethod count-persistent-objects ((store list) class-name &key where &allow-other-keys) (let ((sql-expr `(:select (:count '*) :from ,class-name ,@(when where (list :where where))))) (with-connection store (query sql-expr :single)))) However a simple test of this results in a type error: The value of CL-POSTGRES::QUERY is (:SELECT (:COUNT '*) :FROM QUOTES), which is not of type STRING. Is there a reason that query doesn't support evaluated lists? If so, how would you recommend this be done? Regards, Brit Butler

2 2

[postmodern-devel] Question on returning multi-dimensional arrays
by Sabra Crolleton 28 Nov '10

28 Nov '10

Hello all, I'm having trouble trying to get a array result out of a database. Assume (query "create table test2 ( id integer, name text, stuff1 integer[] , stuff2 integer[][])") (query "insert into test2 values (5,'t5',array[21,78,93,24],array[[83,92],[16,27]])") If I'm at a psql command line, then select stuff2[1:2][1] from test2 where id=5; returns {{83},{16}} If I try to use postmodern, eg: (query "select stuff2[1:2][1] from test2 where id=5") I get an error message about junk in the result: junk in string "{83" [Condition of type SB-INT:SIMPLE-PARSE-ERROR] I'm likely missing something obvious. Everything is fine if I'm only trying to get a single item out of the array, but ideally I'd like to just pull the entire array out directly into a variable. Any suggestions or pointers would be appreciated. Sabra

2 1

[postmodern-devel] Multiple connections and connection pools
by Slobodan Milnović 25 Nov '10

25 Nov '10

Hi, I have a bit of a problem understanding how multiple connections and connection pools work. What I would like to do is to open an connection to the database, read several rows from one table with doquery, and for each read row analyze data, and if conditions are met, write a row to another table. I'd rather not use memory to store the results from the first query because I am a bit memory constrained. I have tried the following (just the simplification of the actual code), for which I get the message "Database error: This connection is still processing another query." (defvar *db-parameters* (list "databasename" "databaseuser" "databasepassword" "dbhostaddress" :pooled-p t)) (defmacro with-db (&body body) `(postmodern:with-connection *db-parameters* ,@body)) (defun process-some-stuff () (with-db (postmodern:doquery (read some stuff from a table) (several variables) (analyze row) (if some conditions (with-db (postmodern:query (insert stuff into another table))))))) Also, I have tried using two postmodern:connection and then after each with-db, but the error is the same. I understand that when I use ":pooled-p t" postmodern will use some kind of pool, but I have'nt been able to understand how to use it properly, nor did I manage to find any kind of solution on the internet. The test.lisp in postmodern source directory did test the pool, but each time the connection was imediatelly closed after openning it, and there were no examples like in my situation. Perhaps the nesting of the querries is not allowed in postmodern?

2 3

[postmodern-devel] Parameterized sql strings
by achambers＠mcna.net 16 Nov '10

16 Nov '10

Hi, Am I correct in thinking that there is currently no way to parameterize raw SQL strings to be sent through postmodern? I realize most SQL can be expressed as S-SQL but it doesn't seem to support COPY syntax yet. I added the function `sql-fmt' for this purpose and am submitting it to the list in case it's useful for others.... (defun sql-fmt (fmt &rest args) (apply 'format nil fmt (mapcar 'sql-ize args))) In my repo [1], I added this to the :export list of both s-sql and postmodern packages. Please pull the change or let me know if you need this changed in any way. [1]: https://github.com/cddr/Postmodern/commits/master Cheers, Andy

3 3

[postmodern-devel] Problems with doquery and simple-date
by Slobodan Milnović 16 Nov '10

16 Nov '10

Hi, There is probably some simple reason why these won't work, but I'm not as good in lisp to understand it. This works: CL-USER> (simple-date:decode-timestamp (caar (postmodern:query (:limit (:select (:dot 'sem 'avl 'timestamp) :from (:dot 'sem 'avl)) 1)))) 2010 1 12 8 35 50 0 This doesn't: CL-USER> (postmodern:doquery (:limit (:select (:dot 'sem 'avl 'timestamp) :from (:dot 'sem 'avl)) 1) (ivo) (simple-date:decode-timestamp ivo)) NIL I don't understand the difference, especially since for other stuff doquery works. Please advise.

2 2

[postmodern-devel] Retrieving completely populated DAOs based on SQL string query
by Mitch Berkson 05 Oct '10

05 Oct '10

I'd like to retrieve fully populated DAOs based on a SQL string query - preferably without naming all the columns. Using query-dao, I can get DAOs which have the columns I enumerate populated. For example, the following will retrieve DAOs with just the id slot populated. (query-dao 'foo-db-name "SELECT DISTINCT foo.id FROM foo, etc.") Alternatively, I can use select-dao to return complete DAOs, but I don't know how to write the test in the form of a SQL string to limit the DAOs that will be returned. So it would help me to know how to write a SQL string which can be used as a test for select-dao. Or a way to have query-dao retrieve fully populated DAOs. Thanks for any help. Mitch

2 4

[postmodern-devel] Postgres 9
by bitur mail 20 Aug '10

20 Aug '10

Hi Marijn, I was wondering if there would be any changes necessary to Postmodern with the upcoming release of Postres 9, or if it would work just as well with the new version? Thank you, Derrick

2 1

[postmodern-devel] Error with the :PLIST result style
by Nicolas Martyanoff 31 Jul '10

31 Jul '10

Hi, I tried using the :PLIST result style with the following query: (postmodern:query (:select '* :from 'accounts) :plist) Infortunately, it doesn't work because this form expands to: (T (CL-POSTGRES:EXEC-QUERY POSTMODERN:*DATABASE* (S-SQL:SQL (:SELECT '* :FROM 'ACCOUNTS)) 'POSTMODERN::SYMBOL-PLIST-ROW-READER)) Where T isn't a function. Using other result styles works as intended. The source of the problem seems to be that in *RESULT-STYLES* (postmodern/query.lisp), :PLIST and :PLISTS are respectively associated with T and NIL, while all other result styles use SINGLE-ROW or ALL-ROWS. Am I doing something wrong or is this really a bug ? Regards, -- Nicolas Martyanoff http://codemore.org khaelin(a)gmail.com

2 2