A view from the hill

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:

(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.foreign_id] [other.id]]]
			:order-by '([other.name] [some.foreign_id] 
				          [year] [some.name])
			:database db))
          (setq dbresult
		(select 'some-model 'other-model
			:where [and [null [some.criteria]]
			            [= [some.foreign_id] [other.id]]]
			:order-by '([other.name] [some.foreign_id] 
				          [year] [some.name])
			:database db))
      (when dbresult
	(loop for (some other) in dbresult
	      collect some)))))

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:

(let (where-arg)
  (if (exact-comp-needed)
      (setq where-arg '[= [column] someval])
      (setq where-arg '[like [column] someval]))
  (select 'model :where where-arg))

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:

(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))

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:

(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))

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.

This is just a minor rambling inspired by a recent thread on cll about the ups and downs of using Rails. What I find really overwhelming is the lack of proofs that other (typically Lisp-based) frameworks really have so much benefit over the established more traditional frameworks (e.g. Rails, Zope, plain PHP, ...). All I can see is starter documents, tutorials etc. that show how to program yet another blog or reddit clone in some particular framework. I miss fair comparisons and detailed discussions why and where exactly that one particular way of doing things has benefits. And especially with regard to those reddit clone prototypes: AFAICT, reddit was far beyond implementing some half-baked prototype (in Lisp) when they decided to switch (to Python). Yes, for demo purposes reinventing the wheel with some unround edges might be acceptable, but in reality only rolling wheels will be sold and this is where the challenge is.

I believe that while it's nice that frameworks help with implementing your 500 line application, they really need to show their value with large applications. Where is a discussion of a large on-line shopping system in, say, UCW and CL-SQL, with secure shopping over SSL, login handling, input validation, distribution over multiple servers etc.? Now, I'm not suggesting that UCW, Weblocks etc. can't be used to build such websites, but it would be nice to see a much more in-depth explanation/discussion/comparison of how to do it. That would also help increase the credibility of Lisp or at least, of lispers discussing (other) web frameworks.

Every now and then, I'm still blown away by the elegant power that the repl (read-eval-print-loop) of Common Lisp provides for everyday tasks. A recent example: I needed to fix a broken column definition in one of my Postgres databases. I'm not a DB pro, so I will just drop the column. But of course, we want to retain the old data, so here we go (using CL-SQL):

SOMEDB> (connect +db-spec+)
#
SOMEDB> (defvar *olddata* (select [id] [data] :from [sometable]))
*OLDDATA*
SOMEDB> (execute-command "ALTER TABLE sometable DROP COLUMN data;")
NIL
SOMEDB> (execute-command "ALTER TABLE sometable ADD COLUMN data varchar;")
NIL
SOMEDB> (loop for (id value) in *olddata*
                  do (update-records [sometable] 
                                 :attributes '(data) 
                                 :values (list value) 
                                 :where [= [id] id]))
NIL
SOMEDB> (disconnect)

That's it. First we open a connection, store away the olddata (which will be returned as a list of tuples) in a global variable, modify the table and finally restore the data. Now, what I find really nice about this is that I can operate on the data as if I had an intersection of psql, shell and a real programming language, which is pretty much the point of this blog post. I think that Ruby probably provides a similar environment with irb. And, hey, today I learned that XEmacs comes with an interface to postgres, so I might have been able to do it from within my favourite editor, too ...

Today, I was bitten by this problem: Illegal :ASCII character starting at byte position 16. I had some data I wanted to exchange with my Postgres DB but somehow, my Emacs consistently lost his connection to SBCL when I hit data with special chars (German umlauts encoded in ISO Latin 1). Investigating further, it became clear that SBCL had the problem without Slime, too. I was rather surprised then to see the solution in the thread linked to above: It was indeed a slime setting that fixed the problem. On a second look, I don't encounter the exact same problem, as I have trouble with encoded data in the database, not with the initial connect. But the solution suggested by Nikodemus Siivola helps me as well. There have been at last two other threads on the mailing list with regard to Unicode and Postgres, the last one is from April this year. Somebody from the slime camp could probably explain what that slime-setting does to SBCL that it affects SBCL connection to Postgres via CL-SQL. Perhaps I'll go and have a look. Ah, the joys of open source.

Update:I was under a wrong impression. I previously had set SB-IMPL::*default-external-format* to :LATIN-1, a suggestion from the same thread. The slime setting only handles the communication issue with Emacs, but setting the variable on the sbcl side fixes the real problem.

Finally, I wanted to give the not-so-new-by-now SBCL 1.0 a whirl. Google led me to Peter van Eynde's Dapper packages, which you can install if you add the last of the following lines to your /etc/apt/sources.list:

root@elendil:~# grep cl-dapper /etc/apt/sources.list
deb http://people.debian.org/~pvaneynd/cl-dapper-packages ./

Unfortunately, on Dapper this breaks CL-SQL or more exactly the version 3.53 it is shipping with. SBCL, or more exactly PCL barfs

When attempting to set the slot's value to (OR NULL T) (SETF of SLOT-VALUE),
the slot TYPE is missing from the object
#<CLSQL-SYS::VIEW-CLASS-DIRECT-SLOT-DEFINITION CLSQL-SYS::VIEW-DATABASE>.
[Condition of type SIMPLE-ERROR]

I haven't seen any complaint about this on the CL-SQL mailing list, so I guess I'm the only one using such an old CL-SQL version with the shiny new SBCL. So, currently I have a broken CMUCL on Debian Sarge and a broken CL-SQL on Ubuntu Dapper. Sigh.

Update:Manually switching to CLSQL solved the problem. Just in case you have relied on Debian or Ubuntu to supply you with the right packages, I found replacing them to be dead easy. I just 'apt-get remove'd cl-sql, but left cl-uffi and cl-md5 in place. Then, I untar'ed the source archive of CLSQL to /usr/local/src/clsql/. One link from there to /usr/share/common-lisp/source and a 'cd /usr/share/common-lisp/systems/ ; for i in ../source/*.asd; ln -s $i . ; done' later, SBCL 1.0 loaded CLSQL 3.8 like a charm.

Troels Henriksen writes in his blog about library use in the Common Lisp community. It's really funny that he cites UCW as a good example of library usage, whereas I have encountered several problems exactly because of UCWs library dependencies. Another example would be CL-SQL .

Now, in Troels comment section, some unknown poster suggested looking at the integrated XEmacs packaging system. I'm not convinced that this solves the real problem, though, as it doesn't deal with version issues at all besides providing a feature to update to a newer version. But incompatibilites between versions is the major obstacle identified (correctly) by Troels. Revisit UCW: UCW includes (or at least it did when I last checked some months ago) several forked-off versions of some libraries it depends on.

I'm also not convinced that fixing or enhancing the package tools is the right way to go. I think that the major issue is that many CL libraries are far from the stableness that e.g. CL-PPCRE provides. Otherwise, e.g. UCW wouldn't need to provide a box set of it's own versions of libraries, and believe me, I'm very grateful for that box set. I think that the only real solution is working on the libraries.

I also believe there is another reason for many programmers not to rely on external libraries: The fear of bloat. CL traditionally has a very bad reputation with regard to execution speed and memory usage. Now, right after loading UCW in a fresh SBCL, I see the following in top:

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
11861 schauer 15 0 894m 79m 15m S 0.0 7.8 0:13.22 sbcl

This is a lot of memory which might be a reason why many programmers might feel it's easier to start developing their own application-specific solution.

Another reason might be that Lisp programmers often try to accomplish a solution in terms of the problem domain and this might sometimes conflict with the approaches taken in the libraries. To some degree, this is the Not-Invented-Here issue in cloak (eek, I don't like it this way, I'll go my way instead), but it also touches a lot on issues of style which can't be argued about.

UCW is a beast to set up. Don't even think about getting it to run on a naive Debian Sarge installation with the shipped lisp libraries. Fortunately, they do provide a boxset in the meantime, which eases the pain a considerable bit.

But even with the new tutorial,that also ships with the boxset, I had to dig quite a bit around how I make my new test application known to ucw. /etc/ucw/applications.d? No. path-to-ucw-boxset/ucw_dev/etc/conf.lisp? No. path-to-ucw-boxset/ucw_dev/etc/start.lisp? No. Finally, adding to path-to-ucw-boxset/start.lisp did the trick, as should have been obvious to me as I fire up UCW by sbcl --load path-to-ucw-boxset/start.lisp. Oh well. And I haven't even started to move the UCW configuration to use mod_lisp.

And of course, the boxset messes around with your ASDF settings, which it has to, otherwise it would run with the different versions of it's more common libraries. You'll not only need to add your private apps system file to where the boxset expects it (or mess around with the setting of the default-registry in (one of the) conf file) but you'll also need to additionally add (links) for all additional CL libraries (say CL-SQL) your app depends on. That's not funny but a royal PITA as it means you'll do very stupid Unix sysadmin tasks instead of fun web programming with UCW.

I was fiddling with CL-SQL again and came across some minor inconveniences and one major one. To the minor inconveniences: there is no sane way to handle constraints with the OODDL, i.e. def-view-class. For instance, how do you specify a foreign-key? Yes, of course, there is :db-type :join but that is for something else. And :db-constraints just takes a rather small set of keywords like :unique. Seems like the only way to go is to call CREATE TABLE which accepts a string argument (for direct SQL commands) to :constraints. Next, what happens if your view-class wants to use a sequence? Sure, you can create a sequence just fine and also use it flawlessly with an existing table. But there is no :type serial argument, you have to go and specify a direct SQL statement with :db-type serial.

Now, the major complaint I have, though, is a different one. create-view-from-class is a misnomer. It doesn't create views at all. In fact, there is no way to create a DB-view from a CLSQL-view. Perhaps, this isn't to surprising after all, as for a view you have to specify a SQL select statement which can get rather complex, like SQL joins even, right? But wait, there is :db-type :join which actually just does that. Given the power of :db-type :join, I don't really understand what the problem is behind the lack of a real create-view-from-class. Ultimately, I believe this might also be a compatibility issue (I don't know, though). Perhaps the developers fear that no user of CLSQL would update from create-view-from-class to create-table-from-class or some such. Or they believe that no one will ever be setting up their DB from within Lisp.

Getting started with CL-SQL was actually a pretty easy experience. I stumbled over one problem though, and it has a name, even. The packages shipping with Sarge are outdated. So outdated, that I couldn't get my files compiled that would otherwise work fine from the REPL or even load successfully as source files. And, hey, most of the time they would even compile with ASDF, unless I was using load-system from Slime.

Actually, that wasn't the first time I had trouble with Common Lisp packages on Debian. Getting UCW to work actually required a far newer version of sbcl than that old 0.8.12 shipping with Sarge. I built one myself, however, thanks to the work of Rene van Bevern (why isn't he on Planet Lisp?), a recent version (0.9.11) is available from backports.org, so that shouldn't be a problem anymore. New versions of CL-SQL are not available, though, so I copied the newer version of the files shipping with Breezy, which finally fixed the problem.