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

[postmodern-devel] dynamically built queries
by Ralf Mattes 04 Feb '08

04 Feb '08

Hello list, I need to create queries dynamically and seem to have run into a silly showstopper. I want to create queries like ... SELECT tmp1.name, tmp1.value as "{foo}bar" from baz as tmp1; ... where both the table name alias ("tmp1") as well as the field name alias ("{foo}bar") are _Strings_. Now, if i do: (sql-compile '(:dot "tmp1" value)) --> "E'tmp1'.value" which is definitely _not_ what i want. The same for: (sql-compile '(:as "tmp1" baz)) --> "E'tmp1' AS baz" If possible at all I'd like to avoid the (make-symbol "tmp1") and similar workarrounds. Any ideas? TIA Ralf Mattes

3 3

[postmodern-devel] Release 1.05
by Marijn Haverbeke 16 Jan '08

16 Jan '08

Hello list, Thanks to Ivan's relentless bug finding and feature requesting, version 1.05 of Postmodern is now a reality. See project page (http://common-lisp.net/project/postmodern/) or darcs log (http://common-lisp.net/cgi-bin/darcsweb/darcsweb.cgi?r=postmodern-postmoder…) for a change list. Enjoy, Marijn

2 2

[postmodern-devel] deftable class-name and unique indices problems
by William Robinson 14 Jan '08

14 Jan '08

Hi. Thanks for the library. I was struggling a bit with pomo's DAO support. A few points: I think it is worth reinforcing in the deftable documentation that a class-name NEEDS to be specified for the data-access methods to function. Can we have/is there a facility for creating unique multi-column indices in deftable? Some more examples for deftable would certainly not go amiss, particularly with repect to usage of :auto-id, :indices and :defer-id. Thanks Bill

2 1

[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