postmodern-devel

postmodern-devel@common-lisp.net

252 discussions

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

[postmodern-devel] How to use auto-id?
by Vamsee Kanakala 11 Feb '07

11 Feb '07

Hi, I'm not sure how to use auto-id in my deftable statement. I have something like this: (deftable cities () ((id :type integer :initarg :id :accessor id) (name :type string :initarg :name :accessor name)) (:class-name city) (:auto-id t)) However, if I use something like (select-dao 'city) To get the list of all cities, I get this error: The assertion (= (LENGTH POSTMODERN::QUERY-FIELDS) POSTMODERN::N-FIELDS) failed. What am I doing wrong? TIA, Vamsee.

1 1

[postmodern-devel] 0.22 regression?
by Feng Hou 28 Jan '07

28 Jan '07

CL-USER> (deftable test-data () ((field1 :type integer) (field2 :type (or string db-null)) (field3 :type (numeric 10 3))) (:indices field1 field3)) CL-USER> (create-table 'test-data) Database error 42601: syntax error at end of input Query: CREATE TABLE test_data (field1 INTEGER NOT NULL, field2 TEXT [Condition of type DATABASE-ERROR] 3: (CL-POSTGRES:EXEC-QUERY #<DATABASE-CONNECTION {BF30B39}> "CREATE TABLE test_data (field1 INTEGER NOT NULL, field2 TEXT" CL-POSTGRES:IGNORE-ROW-READER) Locals: CL-POSTGRES::CONNECTION = #<DATABASE-CONNECTION {BF30B39}> CL-POSTGRES::QUERY = "CREATE TABLE test_data (field1 INTEGER NOT NULL, field2 TEXT" CL-POSTGRES:ROW-READER = CL-POSTGRES:IGNORE-ROW-READER

1 0

[postmodern-devel] Re: postmodern-devel Digest, Vol 2, Issue 4
by Giorgos Pontikakis 27 Jan '07

27 Jan '07

Hello Marijn, Sorry for the belated response, I had been busy through the week and I checked my email just today | I released verion .21, which should solve your first two problems. Great, I will try the new version. Thanks | Haven't quite decided on the syntax for foreign keys, maybe | :create-table can look like defclass too: | | (:create-table 'my-table | ((slot-a :type integer :foreign-key (my-other-table id) :default | 55) (slot-b :type (or null (string 24))) | (:primary-key slot-a) | (:foreign-key (slot-a slot-b) (yet-another-table a b) | (:unique-index slot-b)) ;; Not really part of CREATE TABLE, but | hey, why not? | Then deftable doesn't have to do as much transforming on the | definition and can remain simple. | | Also, I'm not sure how to handle the creation of a set of tables | that contain foreign keys -- since they have to be created in the | right order. Altering them is even more tricky. Maybe I'll default | to deferrable foreign keys, and turn on deferring when messing with | the table scheme. | | Let me know what you think. | Well, I don't know, sorry. I am an amateur and my real job does not involve computers. I have no experience in sql. I just noticed that you released 0.22. I'll try to use it and I'll report if I have anything constructive to say. Thanks again -- Giorgos

1 0

[postmodern-devel] Release 0.22
by Marijn Haverbeke 27 Jan '07

27 Jan '07

Hello list, Version .22 of Postmodern has been released, which adds constraints (uniqueness, checks, foreign keys) and default values to :create-table's syntax (not backwards compatible), and changes the approach to transactions somewhat (also a small syntax change). Marijn

1 0

Jump to page:

2024

2023

2022

2021

2020

2019

2018

2017

2016

2015

2014

2013

2012

2011

2010

2009

2008

2007

2006

postmodern-devel