Feb 22

This post is mainly a reference post about a particular topic whose solution wasn’t immediately obvious to me from the docs to CL-SQL. Using CL-SQL with (enable-sql-reader-syntax), I had written a routine that looks basically likes this:

[geshi lang=lisp] (defun data-by-some-criteria (criteria &key (dbspec +db-spec+) (dbtype +db-type+)) (with-database (db dbspec :database-type dbtype :if-exists :old) (let (dbresult) (if criteria (setq dbresult (select ‘some-model ‘other-model :where [and [= [some.criteria] criteria] [= [some.foreignid] [other.id]]] :order-by ‘([other.name] [some.foreignid] [year] [some.name]) :database db)) (setq dbresult (select ‘some-model ‘other-model :where [and [null [some.criteria]] [= [some.foreignid] [other.id]]] :order-by ‘([other.name] [some.foreignid] [year] [some.name]) :database db)) (when dbresult (loop for (some other) in dbresult collect some))))) [/geshi]

This is ugly because the only difference between those two select statements is the check for the criteria, but I had no idea how to combine the two select statements into one, because it’s not possible to embed lisp code (apart from symbols) into an sql-expression (i.e. the type of arguments for :where or :order etc.). With the next requirement things would become far worse: The order-by statement needs to get more flexible so that it is possible to sort results by year first. Given the approach shown above this would result in at least four select statements, which is horrible. So, naturally I wanted a single select statement with programmatically obtained :where and :order-by sql expressions.

Step 1: It occured to me that it should be possible to have the arguments in a variable and simply refer to the variable. E.g., using a more simple example: [geshi lang=lisp] (let (where-arg) (if (exact-comp-needed) (setq where-arg ‘[= [column] someval]) (setq where-arg ‘[like [column] someval])) (select ‘model :where where-arg)) [/geshi]

So I could now have my two different where-args and two different order-args and use a single select statement. Main problem solved.

Step 2: But for the :where arg in my original problem, only a small fraction of the sql-expression differs. So how do I avoid hard coding the entire value of where-arg? How can I combine some variable part of an sql-expression with some fixed parts? I.e, ultimately I want something like:

[geshi lang=lisp] (let (comp-op where-arg) (if (exact-comp-needed) (setq comp-op ‘=) (setq comp-op ‘like)) (setq where-arg ‘[ <put comp-op here> [column1] someval]) (select ‘model :where where-arg)) [/geshi]

But with CL-SQL modifying the reader, there seems to be no way to make <put comp-op here> work. I didn’t knew how to get the usual variable evaluation into the sql-expression, or how to escape from CL-SQL’s sql-reader-syntax to normal lisp evaluation.

Somewhere in the back of my head where was that itch that CL-SQL might offer some low-level access to sql expressions. And indeed it does. There are two useful functions, sql-expression and sql-operation. sql-operation “returns an SQL expression constructed from the supplied SQL operator or function operator and its arguments args” (from the cl-sql docs), and we can supply the operator and its arguments from lisp — which is exactly what I want.

Now, the nice thing is that it’s easy to mix partly handcrafted sql expressions with CL-SQL special sql syntax constructs that will be automatically handled by the reader (if you enable it only via enable-sql-reader-syntax, of course). I.e., for <put comp-op here> we can use sql-operation, but the rest stays essentially the same:

[geshi lang=lisp] (let (where-arg) (if (exact-comp-needed) (setq where-arg (sql-operation ‘= [column1] someval)) (setq where-arg (sql-operation ‘like [column1] someval))) (select ‘model ‘other-model :where where-arg)) [/geshi]

Now, coming back to my original problem, based on this approach I can split out the common part of the :where and :order arguments and combine those with the varying parts as needed and hand them down to a single select statement. Problem solved.

Posted by Holger Schauer

Defined tags for this entry:
Nov 9

Some time ago, I was looking at splitting text with Elisp, Perl, Ruby and Common Lisp. Yesterday, when I again had to do quite the same thing, it occurred to me that the Common Lisp solution was unnecessary complex/long. I’m not a Perl guru, but I believe the following is probably hard to beat even with Perl:


CL-USER> (format t "~{<li>~A</li>~%~}" (cl-ppcre:split "\|""Kim Wilde|Transvision Vamp|Ideal|Siouxsie and the Banshees|Nena|Iggy Pop"))
<li>Kim Wilde</li><li>Transvision Vamp</li><li>Ideal</li><li>Siouxsie and the Banshees</li><li>Nena</li><li>Iggy Pop</li>
NIL

For the uninitiated, it’s not the cl-ppcre library which is interesting here but the built-in iteration facilities of format. See the Hyperspec on the control-flow features of format for details. Now, I usually tend to avoid the mini languages that come with Common Lisp like the one of format or loop when writing real programs, but when using Lisp as a glorified shell they come in very handy.

Posted by Holger Schauer

Defined tags for this entry:
Jul 4
Recently, there was a discussion about "the rise of functional languages" over on <a href="http://lwn.net/">Linux weekly news</a>, in which one of the participant claimed that one of the major reasons why nobody uses functional languages in industrial settings would be the lack of explicit resource handling (where a resource is some supposedly "alien" object in the system, say a database handle or something like that).  What he was referring to was the inability to run code on allocating/deallocating a piece of resource. Of course, some people pointed him to various solutions, in particular I recurred to the usual WITH-* style-macros in which one would nest the access to the data while at the same time hiding all what one would do on allocation/de-allocation. His reply went something along the lines that such objects may need to be long-lived (thus a WITH-macro is inappropriate) and that the only resort would be the garbage collector and that there simply is no way of running code at a guaranteed (de-allocation) time. I have to admit that I have no idea how I could code around that problem in Common Lisp (garbage collection even isn't a defined term in the ANSI specification of CL, and I'm very sure I haven't seen any mention of allocation/deallocation in it).

Now, some months later, there is a discussion in comp.lang.lisp on the topic of “portable finalizers” and Rainer Joswig pointed to this chapter in the Lisp machine manual which talks about explicit resource handling the lisp machine. From the excerpt, I can’t judge whether resources are first-class CLOS objects and hence the functions to handle them are generic functions, but if so that would actually allow running code on deallocating a resource, of course with the price of having to handle allocation/deallocation manually. I really wonder if any of todays CL implementations offers the same or at least similar functionality?

Posted by Holger Schauer

Defined tags for this entry:

(Page 2 of 2, totaling 18 entries)