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

[postmodern-devel] Binary and unary operators
by Ivan Boldyrev 09 Jan '08

09 Jan '08

1. You are missing such operators like || and && (former is string concatenation, latter is tests overlapping geometric objects). There are also a. math: !, !!, #, >>, <<. b. geometric: ##, @@, @-@, ~=, <->, &<, &>, <<|, |>>, &<|, |&>, <^, >^, ?#, ?|, ?-|, ?||, @>, <@. c. network: <<=, >>=. ?| is both binary and unary operator, ! is postfix (but is equivalent to prefix !!, so you may define :! that expands to prefix !! or just :!! that expands to !!). There are also another operators like @, but they have functions-substitutions (@ x <=> abs(x)). psql's command \do (describe operators) also lists other operators. 2. As PSQL allows defining own operators, I think it worth exporting s-sql::def-infix-ops or creating some more flexible user macro (one might prefer to use :concat instead of :\|\|). With such facility you don't need to define such rare-used operators like @-@ and ?-|, allowing user to define them. -- Ivan Boldyrev Violets are red Roses are blue It's amazing what DNA splicing can do.

2 6

[postmodern-devel] Performance patch
by Ivan Boldyrev 07 Jan '08

07 Jan '08

My application uses quite long and complex query, and profiling showed that slowest function is SQL-COMPILE. First I reimplemented it with S-SQL::STRCAT, but then I thought: what if it will hit CALL-ARGUMENTS-LIMIT (it is only 4096 on GNU Clisp!)? So I changed implementation of S-SQL:STRCAT to get list as the only argument, not as &rest. SQL-COMPILE is much faster and consumes on my query 10 times less memory. As SQL-COMPILE is usually called on run time, not compilation time, I think it is very important. --- old/s-sql/s-sql.lisp~ 2008-01-07 20:05:17.000000000 +0600 +++ new/s-sql/s-sql.lisp 2008-01-07 23:26:03.000000000 +0600 @@ -25,7 +25,7 @@ ;; Utils -(defun strcat (&rest args) +(defun strcat (args) "Concatenate a list of strings into a single one." (let ((result (make-string (reduce #'+ args :initial-value 0 :key 'length)))) (loop :for pos = 0 :then (+ pos (length arg)) @@ -36,11 +36,11 @@ (defun implode (sep list) "Reduce a list of strings to a single string, inserting a separator between them." - (apply 'strcat - (loop :for element :on list - :collect (car element) - :if (cdr element) - :collect sep))) + (strcat + (loop :for element :on list + :collect (car element) + :if (cdr element) + :collect sep))) (defun split-on-keywords% (shape list) "Helper function for split-on-keywords. Extracts the values @@ -338,11 +338,11 @@ (let ((list (reduce-strings (sql-expand form)))) (if (= 1 (length list)) (car list) - `(strcat ,@list)))) + `(strcat (list ,@list))))) (defun sql-compile (form) (let ((*expand-runtime* t)) - (car (reduce-strings (sql-expand form))))) + (strcat (sql-expand form)))) ;; The reader syntax. @@ -378,7 +378,7 @@ (declare (type (member t :both nil) class)) (cond ((cdr args) - `("(" ,@(sql-expand-list args (strcat " " operator " ")) ")")) + `("(" ,@(sql-expand-list args (strcat (list " " operator " "))) ")")) ((eq class t) (sql-expand (first args))) ((eq class :both) -- Ivan Boldyrev Life! Don't talk to me about life.

2 1

[postmodern-devel] WITH-SAVEPOINT macro
by Ivan Boldyrev 06 Jan '08

06 Jan '08

WITH-SAVEPOINT macro is modelled after WITH-TRANSACTION. I would be glad to see it in 1.05 :) -- Ivan Boldyrev Assembly of a Japanese bicycle requires greatest peace of spirit.

2 5

[postmodern-devel] Bug report: Incorrect handling of SIMPLE-DATE types in S-SQL
by Ivan Boldyrev 05 Jan '08

05 Jan '08

This doesn't work: ,---- | (postmodern:query | (:select (:- :localtimestamp | (simple-date:encode-interval :hour 2 :minute 10)))) | | Database error 22007: invalid input syntax for type timestamp: "2 hours 10 minutes " | Query: (SELECT (localtimestamp - '2 hours 10 minutes ')) `---- This does work: ,---- | (postmodern:query | (:select (:- :localtimestamp (:raw "interval '2 hours 10 minutes'")))) `---- It was tested with PostgreSQL 8.0.13 and 8.2.4. SQL-IZE methods for SIMPLE-DATE objects should return escaped strings like "interval '2 hours 10 minutes'", not just "2 hours 10 minutes", and second value of SQL-IZE must be NIL: b ,---- | (:method ((arg simple-date:date)) | (multiple-value-bind (year month day) (simple-date:decode-date arg) | (format nil "date ~A" | (sql-escape-string (format nil "~4,'0d-~2,'0d-~2,'0d" year month day))))) `---- (Untested!) Perhaps, sql-escape-string can be omitted: the output has no dangerous characters anyway. ,---- | (:method ((arg simple-date:date)) | (multiple-value-bind (year month day) (simple-date:decode-date arg) | (format nil "date '~4,'0d-~2,'0d-~2,'0d'" year month day))) `---- -- Ivan Boldyrev Violets are red, Roses are blue. // I'm schizophrenic, And so am I.

2 2

[postmodern-devel] Inserting into related tables
by Ivan Boldyrev 05 Jan '08

05 Jan '08

I don't quite understand how to do inserts into related table. For example, we have table 'items' with field 'producer_id' and table 'producer' with 'producer_id' and 'producer_name' fields: ,---- | (deftable item () | ((producer-id :type (or integer db-null)") | ... ; Other fields | ) | (:auto-id id)) | | (postmodern:deftable producers () | ((producer-name :type (string 50))) | (:auto-id producer-id) | (:indices producer)) | | ;; Add unique index manually | (postmodern:execute | (:create-unique-index 'producers-name-uniq-idx | :on 'producers | :fields 'producer-name)) `---- When we add new item to the database, producer may exist or may be new. We have to check producer's existense and add it to table if required. The only safe way to do it seems to be ,---- | ;; We ignore error if name exists. But what about other errors? | (ignore-errors | (let ((id (postmodern:next-id 'producers))) | (postmodern:execute | (:insert-into table :set | 'producer-id id | 'producer-name name)))) | | ;; Now get ID to insert into items.producer-id. | (postmodern:query | (:select 'producer-id :from table :where (:= 'producer-name name)) | :single) `---- However, on each insertion we increment producers-producer-id-seq even if producer name already exists in the table. This is somewhat clumsy. And faulty query wrapped with ignore-errors doesn't work with transactions. Is there any other postmodern-way? -- Ivan Boldyrev XML -- new language of ML family.

2 9

[postmodern-devel] Tiny patch for Allegro Common Lisp
by Ivan Boldyrev 28 Dec '07

28 Dec '07

I have tested Postmodern with Allegro Common Lisp 8.1 Experess and found that it trivial-utf8 is not loaded and non-latin charsets do not work. The reason is that ACL doesn't put :unicode in *features*, but puts :ics (International Character Set?). The patch attached fixes that. -- Ivan Boldyrev

2 1

[postmodern-devel] development plans for discussion
by Attila Lendvai 19 Nov '07

19 Nov '07

hi! i have two changes planned: 1) use babel for string encoding/decoding ( http://common-lisp.net/~loliveira/soc07/babel/ ). the rationale is that it's a very nice cross platform unicode lib which is also very fast. Marijn, i hope you will not take it as offense! i know trivial-utf8 is your work, but i hope the speed and the de facto standardness of babel compensates. (Luis will give babel more publicity in the next weeks, but it's available for months now and i'm using it without problems). 2) use iolib for the socket code. i would like to do that because we are experiencing problems when dealing with sockets on sbcl when the system is under heavy load. another reason is that iolib is another nice piece of code, although its also more heavyweight then usocket and also posix-only (currently). i know you prefer simple libs, just like with ironclad/md5, but we need stability and this seems to be the simplest way to achive it. what do you think of these changes? -- attila

2 3

[postmodern-devel] Warnings from bordeaux-threads
by Jens Teich 17 Nov '07

17 Nov '07

I have a problem with warnings from bordeaux-threads. These warnings are no problem when I load manually on Lispworks and SBCL. But when I try to deliver a stand-alone application on Lispworks the delivery process stops at these warnings. I reported the problem to bordeaux-threads mailinglist. They are as follows: | ; Loading fasl file d:\Projekte\Lisp\bordeaux-threads\src\bordeaux-threads.ofasl | ; Loading fasl file d:\Projekte\Lisp\bordeaux-threads\src\lispworks.ofasl | Warning: (METHOD | MAKE-THREAD | (T)) being redefined in d:\Projekte\Lisp\bordeaux- | threads\src\lispworks.lisp (previously in d:\Projekte\Lisp\bordeaux- | threads\src\bordeaux-threads.lisp). | Warning: (METHOD | MAKE-LOCK | NIL) being redefined in d:\Projekte\Lisp\bordeaux- | threads\src\lispworks.lisp (previously in d:\Projekte\Lisp\bordeaux- | threads\src\bordeaux-threads.lisp). | Warning: (DEFMACRO WITH-LOCK-HELD) being redefined in d:\Projekte\Lisp\bordeaux- | threads\src\lispworks.lisp (previously in d:\Projekte\Lisp\bordeaux- | threads\src\bordeaux-threads.lisp). | Warning: (METHOD | THREAD-YIELD | NIL) being redefined in d:\Projekte\Lisp\bordeaux- | threads\src\lispworks.lisp (previously in d:\Projekte\Lisp\bordeaux- | threads\src\bordeaux-threads.lisp). Is there a way for postmodern to load without bordeux-threads? I think on Lispworks I don't need this. I tried the following (changed t and nil) | ;; Change this to manually turn threading support on or off. | (defparameter *threads* #+(or allegro armedbear cmu corman (and digitool ccl-5.1) | ecl lispworks openmcl sbcl) nil | #-(or allegro armedbear cmu corman (and digitool ccl-5.1) | ecl lispworks openmcl sbcl) t) but there is some resistance: | Loading fasl file d:\Projekte\Lisp\postmodern-1.02\postmodern\connect.ofasl | | Error: Package BORDEAUX-THREADS not found. | 1 Get another package name. | ... Jens

2 2

[postmodern-devel] views
by Joe Corneli 15 Nov '07

15 Nov '07

So, Postmodern doesn't seem to have very minimal support for views (just `list-views' and `view-exists-p'), but I assume I can still work with them? Any tips on that?

3 2

[postmodern-devel] connecting
by Joe Corneli 14 Nov '07

14 Nov '07

I can run psql from the shell -- % psql Welcome to psql 7.4.17, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands \g or terminate with semicolon to execute query \q to quit joe=# but trying to connect to PostgreSQL with Postmodern currently fails: CL-USER> (connect-toplevel "joe" "joe" "" "localhost") Backtrace: 0: (USOCKET::HANDLE-CONDITION #<SB-BSD-SOCKETS:CONNECTION-REFUSED-ERROR {A7CD821}> #<USOCKET:STREAM-USOCKET {A7B8831}>) 1: (USOCKET:SOCKET-CONNECT "localhost" 5432) 2: (CL-POSTGRES::INITIATE-CONNECTION #<DATABASE-CONNECTION {A7B84C9}>) 3: (CL-POSTGRES:OPEN-DATABASE "joe" "joe" "" "localhost" 5432) 4: (CONNECT-TOPLEVEL "joe" "joe" "" "localhost") 5: (SB-INT:SIMPLE-EVAL-IN-LEXENV (CONNECT-TOPLEVEL "joe" "joe" "" "localhost") #<NULL-LEXENV>) Can you give me any ideas on how to get this connection working? This is a fresh install of Postmodern, but I am using some old ASDF files for dependences, do you think that could be the problem?

2 2

[postmodern-devel] Capital/mixed-case table/column names?
by Matthew Lamari 06 Nov '07

06 Nov '07

This question may betray a limited understanding of Postmodern or PostGres; but. . . . If possible, how can Postmodern be used to generate queries that can interact with capitalized/mixed-case table and column names? It appears that in postgres, unless double-quoted, a string is converted to lowercase (as with lisp with uppercase). String literals are single-quoted to SQL literals, not double-quoted as appears required for names. Postgres appears to lowercase even symbols containing capitals (e.g. |AbCd|) If no mechanism for mixed-case exists, would the use of mixed-case symbols be an appropriate change to make to PostGres? Thanks, Matt

2 1

[postmodern-devel] Multiple inner joins ...
by Ralf Mattes 17 Oct '07

17 Oct '07

Hello list, I'm trying to generate a query similar to the following: SELECT foo, bar, baz FROM (SELECT foo FROM ... WHERE ...) AS tmp1 INNER JOIN (SELECT bar FROM ... WHERE ...) AS tmp2 ON (tmp1.id = tmp2.id) INNER JOIN (SELECT baz FROM ... WHERE ...) AS tmp3 ON (tmp2.id = tmp3.id) How can I express this in S-SQL syntax? TIA Ralf Mattes

2 2

[postmodern-devel] is postmodern thread safe
by Samantha Atkins 02 Oct '07

02 Oct '07

I would like to find a Lisp database interface that is. I intend to build a general ODBMS layer in lisp on top of such. To do so I need serious multi-threaded support. Think persistence layer to a web app platform. Does postmodern handle multi-threaded use? If it makes a difference my Lisp of choice is SBCL. - samantha

5 6

[postmodern-devel] Selecting dao's
by Renato Lucindo 28 Sep '07

28 Sep '07

Hi, There is some way to select a limited number of daos without using query-dao? I think in something like that: the function select-dao accepting some key parameters, like :limit, :offset, :order-by... (select-dao 'my-entity :limit 100) to be same as (query-dao 'my-entity (:limit (:select '* :from 'my-entity) 100)) I think this would be useful in many applications, and allow the developer to write code without SQL. []'s Lucindo

3 7

[postmodern-devel] postmodern:connected-p
by Renato Lucindo 24 Sep '07

24 Sep '07

Hello, The function postmodern:connected-p should fail when its argument is a variable bounded to nil? For instance, here is some code using the package :postmodern : (defun connect-to-database () (when (not (connected-p *database*)) (connect-toplevel *db-name* *db-user* *db-pass* *db-host*))) This code fails because *database* is nil at first. If the function cl-postgress:database-open-p was changed into this: (defun database-open-p (connection) "Returns a boolean indicating whether the given connection is currently connected." (and connection (connection-socket connection) (open-stream-p (connection-socket connection)))) the code above would work as expected.

2 1

[postmodern-devel] porting Postmodern to SQLite
by Ryszard Szopa 19 Sep '07

19 Sep '07

Marijn, I was talking with a friend about Submarine and Postmodern and he complained about the fact that Postmodern (and thus Submarine) are PostgreSQL specific. I agreed that this is a shame: for some smaller projects Postgres is a bit overkill and you may want to use something smaller, like SQLite. You write in your docs that "...postmodern has no intention of being portable across different SQL implementations (it embraces non-standard Postgres features)." What do you mean by that? Is there anything Postgres specific outside of CL-POSTGRES? How difficult do you think it would be to port Postmodern to SQLite? (And: how long did it take to write CL-POSTGRES?) Cheers, -- Richard -- http://szopa.tasak.gda.pl/

2 1

[postmodern-devel] with-connection problem
by Jens Teich 18 Sep '07

18 Sep '07

What's wrong here? Tested with LW and SBCL. Thanks Jens POMO> (with-connection ("web_access" "web_access" "***" "localhost") (query (:select '* :from 'dokumente-files))) (("s5s732006l9t5i0ai3a308281gwdh5 " "z_z15p02" "Bilddata/AZ/Pdf/Az15_16/baumuste" "pdf" 1914355) ...... POMO> (defparameter *db* '("web_access" "web_access" "***" "localhost")) *DB* POMO> (with-connection *db* (query (:select '* :from 'dokumente-files))) error while parsing arguments to DEFMACRO WITH-CONNECTION: bogus sublist *DB* to satisfy lambda-list (DATABASE USER PASSWORD HOST &KEY (PORT 5432) POOLED-P) [Condition of type SB-KERNEL::DEFMACRO-BOGUS-SUBLIST-ERROR]

4 9

Fw: [postmodern-devel] query with keyword :on
by Jens Teich 17 Sep '07

17 Sep '07

Marijn Haverbeke wrote: > Hello Jens (and other lispworks users), > > I finally got a chance to do some coding, and I pushed a patch that > should fix this problem -- could you try it out and tell me if it > works at all. The only lisp interpreter I had available was Franz' > telnet repl, so testing has been minimal, but the changes weren't > that drastic, so there is a not unreasonable chance that they are > correct. First short test looks very good! Thanks Jens ------------ WEBCAT 15 > (with-db (postmodern:sql (:select 'pmw.wert :from (:as 'projekt-merkmale-werte 'pmw) :inner-join (:as 'projekt-elemente 'pe) :on (:= 'pmw.guid-produkt 'pe.guid-objekt) :where (:and (:= 'pe.guid "rzt732819vlurc5827955302wwxz8b") (:= 'pmw.merkmal-id "MID1008519") (:= 'pmw.lang-id "de"))))) "(SELECT pmw.wert FROM projekt_merkmale_werte AS pmw INNER JOIN projekt_elemente AS pe ON (pmw.guid_produkt = pe.guid_objekt) WHERE ((pe.guid = 'rzt732819vlurc5827955302wwxz8b') and (pmw.merkmal_id = 'MID1008519') and (pmw.lang_id = 'de')))" WEBCAT 16 > (with-db (postmodern:query (:select 'pmw.wert :from (:as 'projekt-merkmale-werte 'pmw) :inner-join (:as 'projekt-elemente 'pe) :on (:= 'pmw.guid-produkt 'pe.guid-objekt) :where (:and (:= 'pe.guid "rzt732819vlurc5827955302wwxz8b") (:= 'pmw.merkmal-id "MID1008519") (:= 'pmw.lang-id "de"))))) NIL WEBCAT 17 > (with-db (postmodern:query (:select 'pmw.wert :from (:as 'projekt-merkmale-werte 'pmw) :inner-join (:as 'projekt-elemente 'pe) :on (:= 'pmw.guid-produkt 'pe.guid-objekt) :where (:and (:= 'pmw.merkmal-id "MID1008519") (:= 'pmw.lang-id "de"))))) (("30 N") ("30 N") ("5 N") ("5 N") ("30 N") ("5 N") ("5 N") ("30 N") (" 5 N") .....

1 0

[postmodern-devel] query with keyword :on
by Jens Teich 17 Sep '07

17 Sep '07

This is an example from the documentation which gives strange results on Lispworks 5.0.2. I'm using the latest version of postmodern (1.02) CL-USER 3 > (postmodern:sql (:select (:+ 'field-1 100) 'field-5 :from (:as 'my-table 'x) :left-join 'your-table :on (:= 'x.field-2 'your-table.field-1) :where (:not-null 'a.field-3))) "(SELECT (field_1 + 100), field_5 FROM my_table AS x LEFT JOIN your_table ON (x.field_2 = your_table.field_1), your_table, on, (x.field_2 = ................................^ your_table.field_1) WHERE not_null(a.field_3))" Jens PS my previous problem with if statement is solved, I needed a macro instead of a function

3 11

[postmodern-devel] postmodern patch for CMUCL
by Ryszard Szopa 07 Sep '07

07 Sep '07

Hi, I wanted to see how submarine works under CMUCL, and it failed all the tests. It turned out, however, that the blame is on the side of Postmodern: it couldn't connect to the database, and also failed all its tests. The problem was that in some place it was trying to compare with >= a number and nil. With some effort I managed to find the guilty piece of code. It was read-ascii-string in communicate.lisp. In that function, if byte-length is not nil, it is compared with bytes-read. Byte-length, however, was declared as fixnum. This probably caused CMUCL to optimized it away as not-NIL in any conditional expressions (imagine my surpise when evaluating (when byte-length (break "bytes-read: ~A byte-length: ~A" bytes-read byte-length)) threw a break and showed byte-length to be nil :)) Also, binding the utf-8 stuff in package.lisp was indeed an awful hack... It's been a long time since I've used grep on source code to find who calls a function (yeah, slime is addicting;). Now Postmodern passes on CMUCL all its tests except two, table and transaction. I'll take a look at them if I have time. Cheers, -- Richard -- http://szopa.tasak.gda.pl/

2 1

[postmodern-devel] Submarine announcement
by Ryszard Szopa 04 Sep '07

04 Sep '07

Hello Marijn and Postmodern users, I was a bit dissatisfied with Postmodern's DAO system and lack of support for foreign keys. At first, I wrote just a patch that added support for foreign keys, but it turned the macro DEFTABLE into something that had more than 100 lines and was pretty unmaintainable. So, I left Postmodern's sources alone and wrote a library over it, something between Postmodern and an object persistency library. I've used it in some of my projects at work, and now I decided to make it open source. In a couple of days I'll start it a common-lisp.net project and announce it to c.l.l. In the meantime, I will be very grateful for any comments. I encourage you to at least take a look at the example file: http://bender.streamtech.nl/darcs/submarine/doc/example.lisp Here are the contents of my README file: Submarine is a Common Lisp library that's somewhere between a PostgreSQL library an an object persistency system. It uses Postmodern to communicate with the database. The basic idea is that you create your classes in the metaclass DB-CLASS and submarine cares about creating SQL tables or, if the tables already exist, checking if they conform to the provided specification. Moreover, Submarine supports an intuitive way of expressing both one-to-many and many-to-many relations. Getting submarine ================= At the moment there's only a darcs repository available: darcs pull http://bender.streamtech.nl/darcs/submarine/ You will also need my mop-utilities, which may be incorporated into submarine in the close future: darcs get http://bender.streamtech.nl/darcs/mop-utils Dependencies ============ Submarine depends on Postmodern and Iterate. It uses also my library of MOP utilities, MOP-UTILS, which may become a separate library in the future. On platforms other than SBCL, mop-utils needs Closer-mop. License ======= Submarine is released under a BSD-like license. Which approximately means you can use the code in whatever way you like, except for passing it off as your own or releasing a modified version without indication that it is not the original. Relation to Postmodern ====================== Submarine started as a patch that added, among others, foreign keys support to Postmodern. This meant hacking the DEFTABLE macro and TABLE and TABLEFIELD classes. After some time I realized that my modified Postmodern macros had become far too large and heavy to be easy maintainable. So, I decided to leave Postmodern's code as it was and write a separate library. Introduction for Postmodern users ================================= Submarine tries to keep as much as possible of Postmodern's original API. I use the same terminology, and functions with similar names will probably do very similar things. My purpose was twofold. First of all, I wanted to make porting programs using Postmodern to Submarine easy. Secondly, this would allow me to use some of Marijn Haverbeke's superb documentation nearly without any changes. Main differences between Postmodern and Submarine: * You don't have to create DAO (database access object) classes and SQL tables separately. You just create classes belonging to a certain metaclass, and the library cares of the rest. * If the table with an appropriate name exists in the database, Submarine will test if it has columns with the right names and types. If a column does not exist or has a wrong type, Submarine will offer the user a possibility to fix it. Submarine will warn, but do nothing about any additional columns in the table. * Each DB-CLASS class has its own connection specification. This means that you can just access your objects, without wrapping them in a WITH-CONNECTION macro, and they will care about setting the right connection. * DAO is a basis class for DB-CLASS classes, and methods that are supposed to work on an object of any DB-CLASS class use DAO as a dispatch type. Of course, you don't have to use DAO as a base class, but it makes a lot of things easier. * Submarine supports foreign keys. If you define a DB-CLASS class with a slot whose type is another DB-CLASS, it is treated as foreign key and an appropriate constraint is added to the database. * Submarine provides a function to retrieve all the elements of a given type standing in a many-to-one relation with some DAO object. * The DEF-MANY-TO-MANY macro defines a many-to-many relation between two classes and creates methods appropriate to their retrieval (it creates a link table in the database). * DB-CLASS slots have an additional attribute TRANSIENT. If you set it to a non-NIL value, it will behave just as a STANDARD-SLOT-DEFINITION and will be ignored in database related operations. * Submarine uses MOP rather than macros to achieve its purposes. This makes it easier to maintain and to extend. At the moment there's no real documentation apart from docstrings, but there's a poor man's tutorial in the file http://bender.streamtech.nl/darcs/submarine/doc/example.lisp (this should give you a pretty good overview of the possibilities of Submarine. Bests, -- Richard -- http://szopa.tasak.gda.pl/

3 9

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

26 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] conditional query building
by Jens Teich 16 Aug '07

16 Aug '07

> (postmodern:sql (:= 's 'tr)) => "(s = tr)" > (postmodern:sql (if t (:= 's 'tr))) => Error: Undefined operator := in form (:= (QUOTE S) (QUOTE TR)). How do I make this work? I want to build more complex where clauses using cl:if. Jens

2 3

[postmodern-devel] Join Slots
by Jens Teich 13 Aug '07

13 Aug '07

I'm considering to change from clsql to postmodern because I need unicode support. In clsql the following viewclass definition is possible with join slots: (clsql:def-view-class elements () ((guid :reader element-guid :db-kind :key :type (string 36)) (lang_id :db-kind :key :type (string 2)) ... more ... 'normal' slots) (produkt :reader product-element :db-kind :join :db-info (:join-class produkte :home-key (guid_objekt lang_id) :foreign-key (guid lang_id) :set nil))) (:base-table project-elements)) so that I can access the joined records easily via (product-element instance-of-element) How do I do this with postmodern? Thanks Jens

2 1

[postmodern-devel] Can't nest QUERY within prepared-statement QUERY
by J.P. Larocque 30 Jul '07

30 Jul '07

Hi again, Thanks for the fix and the new release, Marijn. I think I found another problem. When I try to query with a prepared statement with the QUERY macro, and one of the arguments to the statement is the result of another QUERY macro (whether directly or from the result of a function), strange things crop up. In one instance, the inner QUERY was returning nil where it would return the expected integer otherwise. Here's an isolated case: (postmodern:query "select cast ($1 as int)" (let ((foo (postmodern:query "select 12" :single))) (format t "result: ~S~%" foo) foo)) This gives me the expected message "result: 12", but also signals a condition, "Database error: Incorrect number of parameters given for prepared statement ." Below is the complete macroexpansion of the above form (without LET and FORMAT). (PROGN (CL-POSTGRES:PREPARE-QUERY POSTMODERN:*DATABASE* "" "select cast ($1 as int)") (CL-POSTGRES:EXEC-PREPARED POSTMODERN:*DATABASE* "" (MAPCAR 'S-SQL:SQL-IZE (LIST (CAR (CL-POSTGRES:EXEC-QUERY POSTMODERN:*DATABASE* "select 12" 'POSTMODERN::COLUMN-ROW-READER)))) 'CL-POSTGRES:LIST-ROW-READER)) The sequence of evaluation is PREPARE-QUERY, then EXEC-QUERY (for the inner "select 12"), then EXEC-PREPARED. I guessed that doing things with the database in-between PREPARE-QUERY and EXEC-PREPARED might be the problem, so I modified QUERY to evaluate prepared-statement arguments before PREPARE-QUERY. This allows the inner query to complete before the outer one actually starts. diff -urNx '*~' postmodern-1.02/postmodern/query.lisp postmodern-1.02-modified/postmodern/query.lisp --- postmodern-1.02/postmodern/query.lisp 2007-07-25 04:10:42.000000000 -0700 +++ postmodern-1.02-modified/postmodern/query.lisp 2007-07-27 23:03:16.000000000 -0700 @@ -55,9 +55,10 @@ :else :collect arg))) (destructuring-bind (reader single-row) (cdr (assoc format *result-styles*)) (let ((base (if args - `(progn - (prepare-query *database* "" ,(real-query query)) - (exec-prepared *database* "" (mapcar 'sql-ize (list ,@args)) ',reader)) + (let ((arg-values (gensym))) + `(let ((,arg-values (mapcar 'sql-ize (list ,@args)))) + (prepare-query *database* "" ,(real-query query)) + (exec-prepared *database* "" ,arg-values ',reader))) `(exec-query *database* ,(real-query query) ',reader)))) (if single-row `(multiple-value-call 'car-of-first-value ,base) Now my first example works, as does the result of one prepared-statement QUERY's being indirectly nested in another prepared-statement QUERY. -- J.P. Larocque: <piranha(a)thoughtcrime.us>, <piranha(a)ely.ath.cx>

2 4