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

[postmodern-devel] postmodern, pg, cl-rdbms
by Attila Lendvai 27 Aug '07

27 Aug '07

hello list, i've recently found postmodern on cl.net, and i was wondering that there seems to be a massive amount of duplication of efforts among the following projects: - pg is a long-time postgresql socket based lib: http://common-lisp.net/project/pg/ - cl-rdbms (our fresh clsql replacement, http://common-lisp.net/project/cl-rdbms/ ) which aims to be a backend independent sql lib, just like clsql, with sexp sql syntax ( see http://common-lisp.net/cgi-bin/darcsweb/darcsweb.cgi?r=cl-rdbms-cl-rdbms;a=… for a quick impression ). it has a single backend for now, based on pg. - and postmodern which is something like a mixture of pg and cl-rdbms we should bring out the best of these... my first impression of postmodern is that it's more modern and better organized code then pg which is good. and to also write actual suggestions besides to this rant, i'd use http://common-lisp.net/project/local-time/ as the date/time abstraction data type. it supports timezones and has a string parser/printer. and fyi, trivial-sockets seems to be a dead project. i've tried to contact the author several times with patches and there wasn't any response; #lisp is on the same opinion. usocket at least welcomes patches, but it's not really important if t-s can already do everything needed. any toughts? -- - attila "- The truth is that I've been too considerate, and so became unintentionally cruel... - I understand. - No, you don't understand! We don't speak the same language!" (Ingmar Bergman - Smultronstället)

3 10

[postmodern-devel] Bug in :limit, patch for s-sql.lisp
by Vamsee Kanakala 31 Mar '07

31 Mar '07

Hi, Looks like :limit works only partly - according to the documentation, "It limits the number of results to the amount given as the second argument, or when three arguments are given it returns the results between the two given numbers". However, if I give the 3rd value, say like this: (query (:limit (:order-by (:select 'id 'name :from 'cities) 'name) 10 20)) I get an error like this: Database error 42601: LIMIT #,# syntax is not supported Use separate LIMIT and OFFSET clauses. Query: (((SELECT id, name FROM cities) ORDER BY name) LIMIT 10, 20) Looks like :offset is not implemented yet. I have attached a patch to fix these issues, kindly let me know the preferred way of sending patches (I used darcs diff --unified) so that I can help out where I can. Thanks much, Vamsee.

2 1

Re: [postmodern-devel] Help with query replacement
by Vamsee Kanakala 31 Mar '07

31 Mar '07

Marijn Haverbeke wrote: > > Unfortunately I don't quite understand what you are trying to do, but > it seems that in the simple case something like this (untested) might > work... > Sorry, I forgot that using the word paging without the context can be confusing. By paging, I meant paging in the web pages - if I have 100 results to a query, I would like to show that to the user in chunks of 20 rows over 5 pages, to avoid huge requests and make the user's life easier. To avoid putting this 100-row result in the session and then paging over it (can be quite inefficient if there are lots of results), I want to retrieve the count of results, and then retrieve small piece of them according to page numbers with :offset and :limit. So something like this: (query (:select 'id 'name :from 'cities)) Would turn into: (query-dao 'city (:limit (:offset (:order-by (:select 'id 'name :from 'cities) 'name) offset) limit)) But to calculate how many pages I have to create links to, I need to have the number of results before the query actually runs. So, number of results divided by limit should give me the number of pages this result set would run into. But to avoid writing a similar query twice, I'm trying to change the first query into this (the aim is to write a macro which does this automatically), to get the number of rows: (query (:select :raw("count(*)") :from 'cities) :single) Also, the element :count was not added to postmodern - I have checked the source too. So, I have to use :raw for now - please correct me if I'm wrong. > (defun query-subst (query) > (let ((from (position :from query))) > `(:select (:count '*) ,@(nthcdr from query))) > Perhaps you can already see that this does not help - position cannot look into trees (works only with sequences) and the select statement can be arbitrarily long. I think my problem can be simply put this way: I need to replace everything between :select and :from with a single (:count *) or (:raw "count(*)"). Perhaps I'm being finicky - I'm just looking to cut some extra lines of code and move the entire paging-related code into one macro. Thanks much, Vamsee.

2 1

[postmodern-devel] Help with query replacement
by Vamsee Kanakala 27 Mar '07

27 Mar '07

Hi, I'm trying add paging support to my web pages, so it's important that I know the number of results a query will generate, so that I can create the appropriate paging code. I'm trying to change something like this: (query-dao 'city (:select 'id 'name :from 'cities)) to (query (:select (:raw "count(*)") :from 'cities)) So that I can get the number of results first and then run the actual query with limit and offset. I thought it best not to change postmodern code itself, to avoid hassles when a new version comes out. However, parsing the above query with a recursive function to replace 'id and 'name with (:raw "count(*)") is proving to be beyond my lisp-fu. Any easy way to achieve this? My terrible hack to do this: (defun query-subst (list) (if (null list) (list) (if (atom (car list)) (progn (cond ((eql (car list) :select) (setf skip t)) ((eql (car list) :from) (setf skip nil))) (if (or (null skip) (eql (car list) :select)) (cons (car list) (query-subst (cdr list))) (if (null count-set) (progn (setf count-set t) (list :raw "count(*)")) (query-subst (cddr list))))) (cons (query-subst (car list)) (query-subst (cdr list)))))) But this doesn't really do my job, because my query ends up like this: (query (:select (:raw "count(*)") nil :from 'cities)) Any way to avoid those extra nils? Probably c.l.l is a better place to post this, but wanted to know if there's a less complicated way to do this. Thanks, Vamsee.

1 0

[postmodern-devel] fragile connection when creating new table?
by Joe Corneli 09 Mar '07

09 Mar '07

Hi, I'm using postmodern under OpenMCL. Connected to an empty database, I tried defining and creating a new table. Doing this directly from the psql prompt gave a bunch of messages, but doing it from within Lisp caused serious trouble. Compare: 1. jcorneli=# create table strs (strid SERIAL PRIMARY KEY, string TEXT NOT NULL UNIQUE); NOTICE: CREATE TABLE will create implicit sequence "strs_strid_seq" for serial column "strs.strid" NOTICE: CREATE TABLE will create implicit sequence "strs_strid_seq" for serial column "strs.strid" NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "strs_pkey" for table "strs" NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "strs_pkey" for table "strs" NOTICE: CREATE TABLE / UNIQUE will create implicit index "strs_string_key" for table "strs" NOTICE: CREATE TABLE / UNIQUE will create implicit index "strs_string_key" for table "strs" CREATE TABLE 2. ? (deftable strs () ((str_id :type serial :primary-key t) (string :type text :unique t)) (:indices str_id)) ? (create-table 'strs) ; Warning: Postgres warning: CREATE TABLE will create implicit sequence "strs_str_id_seq" for serial column "strs.str_id" ; While executing: CL-POSTGRES::GET-WARNING > Error in process listener(1): Postgresql protocol error: Unexpected message received: 1 > While executing: CL-POSTGRES::SEND-QUERY > Type :POP to abort. Type :? for other options. 1 > :POP > Error in process listener(1): #<TCP-STREAM :CLOSED #x857FB3E> is closed > While executing: CCL::STREAM-IOBLOCK > Type :POP to abort. Type :? for other options. 1 > :POP This looks like a bug to me.

2 3

[postmodern-devel] A new elephant backend
by Henrik Hjelte 09 Mar '07

09 Mar '07

First, thanks for postmodern, it's a great library! I just used it to make a new backend for the elephant object-store, db-postmodern. Cross-posting from elephant-devel: It uses postgresql for storage, and the postmodern lisp socket interface instead of clsql. The implementation is actually quite different from the clsql backend. db-postmodern uses prepared queries and stored procedures for performance reasons. The db-postmodern cursor is based on a sql cursor. Each btree has its own database table. db-postmodern is slower than the berkeley db backend, I guess about four or five times. It now passes all tests and the migration test, but should of course be regarded as an alpha. It is only tested on sbcl, but should work on all platforms that postmodern supports. About postmodern: http://common-lisp.net/project/postmodern/ db-postmodern mainly uses the lower level cl-postgres driver in the postmodern project. During development I used a darcs mirror of elephant, and I have temporarily put it at common-lisp.net. This darcs branch is intended for people wanting to try db-postmodern, and it is not an automatic mirror of the cvs elephant. Download: To get elephant containing db-postmodern, darcs get http://common-lisp.net/project/grand-prix/darcs/elephant Best wishes, Henrik Hjelte

1 0

Re: [postmodern-devel] bug in cl-postgres/protocol.lisp?
by Bart Botta 07 Mar '07

07 Mar '07

(oops, forgot to send the message quoted below to the list) After looking into the time/interval errors a bit more, it looks like at least part of the problem with intervals is that the format of intervals changed with postgres 8.1 (adding the day field between ms and month), so I think I will just upgrade postgres rather than worry about any other hidden incompatibilities. -- Bart On 3/5/07, Bart Botta <00003b(a)gmail.com> wrote: > The new version fixes the problem. I checked the tests, and they fail > the same way with version 0.23. > > WARNING: > Postgres warning: CREATE TABLE / PRIMARY KEY will create implicit > index "test_data_pkey" for table "test_data" > WARNING: > Postgres warning: CREATE TABLE / UNIQUE will create implicit index > "test_data_c_key" for table "test_data" > ... > Did 26 checks. > Pass: 22 (84%) > Skip: 0 ( 0%) > Fail: 4 (15%) > > Failure Details: > -------------------------------- > TABLE []: > Unexpected Error: #<CL-POSTGRES::PROTOCOL-ERROR {C7477A9}> > Postgresql protocol error: Unexpected message received: 1.. > -------------------------------- > -------------------------------- > TABLE []: > Unexpected Error: #<SIMPLE-ERROR {C749909}> > #<SB-SYS:FD-STREAM for "a constant string" {C73C7B9}> is closed... > -------------------------------- > > > Version 0.23 and the new version also fail the time-types test, > > -------------------------------- > TIME-TYPES []: > (ENCODE-TIMESTAMP 2040 3 19 12 15 0 2) evaluated to #<TIMESTAMP > 19-03-2040T12:15:00,002>, which is not TIME= to #<TIMESTAMP > 01-01-2000T00:00:00>.. > -------------------------------- > -------------------------------- > TIME-TYPES []: > Unexpected Error: #<CL-POSTGRES::PROTOCOL-ERROR {C5FE3F9}> > Postgresql protocol error: Unexpected message received: ^K.. > -------------------------------- > > > The cl-postgres tests lock up in the date-query test, > (postmodern:query "select '2 years -4 days'::interval") > by itself causes > > arithmetic error FLOATING-POINT-OVERFLOW signalled > [Condition of type FLOATING-POINT-OVERFLOW] > > Backtrace: > ... > 7: (SB-KERNEL:TWO-ARG-* 0.0d0 1000) > 8: (CL-POSTGRES::INTERPRET-MILLISECS 18446743728109551616) > > > -- > Bart >

2 2

[postmodern-devel] bug in cl-postgres/protocol.lisp?
by Bart Botta 03 Mar '07

03 Mar '07

Using version 7.4.7 of PostgreSQL, SBCL 1.0, postmodern 0.23, the deftable/create-table example breaks due to WARNING: Postgres warning: CREATE TABLE / PRIMARY KEY will create implicit index "test_data_pkey" for table "test_data" which causes cl-postgres::send-query to drop into the debugger with Postgresql protocol error: Unexpected message received: 1 [Condition of type CL-POSTGRES::PROTOCOL-ERROR] If I read the code and postgres protocol docs correctly, this is due to the way message-case handles NoticeResponse (#\N) messages, specifically that it only reads 1 of #\E, #\N, or the expected message, so when it receives both #\N and the expected message, the caller gets out of sync. Removing (#.(char-code #\N) (get-warning socket)) from the case and adding (when (= #.(char-code #\N) ,char-name) (get-warning socket) (setf ,char-name (read-uint1 ,socket-name)) (setf ,size-name (read-uint4 ,socket-name))) before the (case ,char-name ...) in message-case fixes the problem for me, not sure if it is completely correct though, possibly should loop in case there are multiple warnings? (Also, shouldn't that (get-warning socket) and the get-error call use ,socket-name instead of socket?) -- Bart

2 1