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

[postmodern-devel] query
by Haris 29 Jan '11

29 Jan '11

I made query like this: (sql (:select '* :from 'kupci :where (:and (:raw (if 'ime (sql (:like 'ime (concat-string "%" (parameter "ime") "%"))) t)) (:raw (if 'prezime (sql (:like 'prezime (concat-string "%" (parameter "prezime") %"))) t))))) but it doesn't work. For example, if (:like ...) for 'ime field passes and 'prezime field is null, record will not be selected. Is the test (if 'prezime ...) ok to test that the field is not null ? Or do I have to do something like (if (/= 'prezime nil) ...) ?

2 1

[postmodern-devel] query
by Haris 28 Jan '11

28 Jan '11

Hi. I made a query like this: (sql (:select '* :from 'kupci :where (:and (:like 'ime (concat-string "%" (parameter "ime") "%")) (:like 'prezime (concat-string "%" (parameter "prezime") "%")))) I want not to apply "like" if the field in database entry is empty (null). Is there some predefined way to do this or I have to program it myself ? (parameter is a hunchentoot parameter after submitting a form) Thanks

2 1

[postmodern-devel] Date format of alist(s)
by Phil Marneweck 27 Jan '11

27 Jan '11

Hi Postmodern is returning dates like this (:DATE-OF-TERMINATION .255611203200) all of a sudden instead of simpledate objects or date string. What did I change to get this behavior or is it a setting in postgresql that has changed? Regards

3 5

[postmodern-devel] plists
by Haris 26 Jan '11

26 Jan '11

git clone http://marijnhaverbeke.nl/git/postmodern :plists works ok now. Thanks

1 0

[postmodern-devel] syncing the socket
by Andy Chambers 26 Jan '11

26 Jan '11

Hi, My bulk copier is somewhat working now but the next time I try to issue a query on the same connection (using cl-postgres:exec-query), I'm getting "Unexpected message received: Z". I thought that putting a "try-to-sync" after sending my CopyDone message should prevent this but apparently it doesn't. Any ideas what's going on? -- Andy Chambers

2 1

[postmodern-devel] plists
by Haris 26 Jan '11

26 Jan '11

I tried git clone https://github.com/marijnh/Postmodern/commit/1627b8a8f8d19181112ba9d6b9eb54… but it doesn't work. What exactly should I write to get this from git ?

2 1

[postmodern-devel] plists
by Haris 26 Jan '11

26 Jan '11

Hi. I tried (query "select * from table1;" :plists) but I get: Error: Undefined operator NIL in form (NIL (CL-POSTGRES:EXEC-QUERY *DATABASE* "select * from table1;" (QUOTE POSTMODERN::SYMBOL-PLIST-ROW-READER))) What does that mean ? I'm using Lispworks/Windows. I also tried (query "select * from table1;" :alists) and that works ok. > I was living in Zagreb ... I'm from Zagreb too.

4 4

[postmodern-devel] plists
by Haris 25 Jan '11

25 Jan '11

Hi. I didn't quite understand the :plists argument. I want for select to return me lists of plists. I tried this: (query "select * from table1;" :plists t) but it doesn't work ? P.S. What's with the Croatia example ? I'm from Croatia.

2 1

[postmodern-devel] Minor bug in the Simple Date reference manual
by Sebastian Tennant 25 Jan '11

25 Jan '11

Hi Marijn, There's a very minor bug in the Simple Date reference manual: http://marijnhaverbeke.nl/postmodern/simple-date.html The HTML title tag reads "Postmodern reference manual" whereas I belive it should read "Simple Date reference manual". It's noticeable if/when you create browser bookmarks for the reference manual of each component part of Postmodern. Seb -- Emacs' AlsaPlayer - Music Without Jolts Lightweight, full-featured and mindful of your idyllic happiness. http://home.gna.org/eap

2 1

[postmodern-devel] Can interval be zero?
by Slobodan Milnović 25 Jan '11

25 Jan '11

Hi, I have an situation where I have an interval of "zero" time that I would like to put with the other data in the table. When I try with the (simple-date:encode-interval :millisecond 0) I get the object > (simple-date:encode-interval :millisecond 0) > #<SIMPLE-DATE:INTERVAL P> But, when I try to insert this object into the table with the insert-dao, I get the following error (I have left out other fields, for other non-zero values of interval the insert works fine). Database error 22007: invalid input syntax for type interval: "" Query: INSERT INTO mytable (someinterval) VALUES (interval E'') RETURNING somevalue [Condition of type CL-POSTGRES-ERROR:INVALID-DATETIME-FORMAT] I'm using latest postmodern from the git repository https://github.com/marijnh/Postmodern.git For now I'll use (simple-date:encode-interval :millisecond 1), but I'm wondering if this is an issue with postmodern or postgresql?

3 5

[postmodern-devel] Bulk Copying
by Andy Chambers 25 Jan '11

25 Jan '11

Hi All, I'm trying to add support for bulk copying to postmodern. Does anyone have any ideas on how it should be done. Here's my suggestion... 1) Add message primitives (we seem to already have copy-done-message) (define-message copy-data-message #\d (data) (string data)) (define-message copy-fail-message #\f (reason) (string reason)) 2) Add a "bulk-copier" class with slots for db ; the connection we're using table ; the table we're copying from/to from/to ; whether we're copying from or to a table header ; whether to include a header row in output/input ;; plus slots for the CSV options delimiter null-str quote-str escape-str 3) A function open-copier creates an instance of the above class and sends the corresponding copy statement to Postgres before returning the object. 4) A function copy-row takes a row (i.e. a simple list of Lisp values), and writes them to the stream attached to the copier. 5) A function copy-done lets Postgres know we're finished and commits the change to the database. Anyone got any hints for debugging this low-level stuff? I seem to be able to open a copier (and send the copy statement to pg) but when I send the complete-command message in copy-done, my code is hanging. -- Andy Chambers

2 1

[postmodern-devel] id field
by Haris 24 Jan '11

24 Jan '11

Then I don't need primary key ? If I choose my primary key to be an integer then I have to keep track of numbers and automatically insert number incremented by one ? I worked long ago with Access and it automatically inserts number incremented by one as id. I guess than this is part of Access program and not the underlaying database ?

2 1

[postmodern-devel] id field
by Haris 24 Jan '11

24 Jan '11

Hi. When I try to insert some data in table with (query "insert into ...") it asks me also to enter 'id' field which is a primary key. Shouldn't primary key field be automatically filed ? I made my table with pgadmin 3. Thanks

2 1

[postmodern-devel] Syntax help requested
by Sabra Crolleton 17 Jan '11

17 Jan '11

Hello everyone, I was doing some maintenance and decided to write some meta-functions in s-sql and ran into an issue with "any". In this particular situation I was trying to apply it to pg_index.indkey which is an int2vector. I wanted to get the indexed columns and their attributes from a table (Not the indexes, the indexed columns) The following sql string seems to work, but I wanted to put it into s-sql form. (defun list-indexed-column-and-attributes (table-name) "List the indexed columns and their attributes in a table" (when (table-exists-p table-name) (query "SELECT pg_attribute.attname, format_type(pg_attribute.atttypid, pg_attribute.atttypmod) FROM pg_index, pg_class, pg_attribute WHERE pg_class.oid = $1::regclass AND indrelid = pg_class.oid AND pg_attribute.attrelid = pg_class.oid AND pg_attribute.attnum = any(pg_index.indkey)" table-name))) I can't seem to figure out how to generate the s-sql equivalent of "any(pg_index.indkey)". I ended up with the following, completely avoiding "any". It seems to work, but because postgresql seems to be able to have up to 32 indexed columns per table, it looks clumsy. Any suggestions as to how I can do this better? Sabra (defun list-indexed-column-and- attributes-pm (table-name) "List the indexed columns and their attributes in a table" (query (:select 'pg_attribute.attname (:format_type 'pg_attribute.atttypid 'pg_attribute.atttypmod) :from 'pg_index 'pg_class 'pg_attribute :where (:and (:= 'pg_class.oid (:type table-name :regclass)) (:= 'indrelid 'pg_class.oid) (:= 'pg_attribute.attrelid 'pg_class.oid) (:or (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 0)) (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 1)) (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 2)) (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 3)) (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 4)) (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 5)) (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 6)) (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 7)) (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 8)) (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 9)) (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 10)) (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 11)) (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 12)) (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 13)) (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 14)) (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 15)) (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 16)) (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 17)) (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 18)) (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 19)) (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 20)) (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 21)) (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 22)) (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 23)) (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 24)) (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 25)) (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 26)) (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 27)) (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 28)) (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 29)) (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 30)) (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 31)) (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 22)))))))

2 2

[postmodern-devel] Point type
by Slobodan Milnović 02 Jan '11

02 Jan '11

Hi, What would be the best representation of the point type? So far, I have been using following for query: (:[] 'point-column 0) (:[] 'point-column 1) and this for update: 'point-column (:type (concatenate 'string (write-to-string x-coordinate) "," (write-to-string y-coordinate)) point) It works, but seems a bit of a kludge. And how about more complex data types that use points like these http://www.postgresql.org/docs/8.4/static/datatype-geometric.html ?

2 1

[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