Subject: Re: Problem with CLSQL on Mac
From: rpw3@rpw3.org (Rob Warnock)
Date: Sun, 11 Jun 2006 23:09:48 -0500
Newsgroups: comp.lang.lisp
Message-ID: <k-6dnUZZMf6RdBHZnZ2dnUVZ_vadnZ2d@speakeasy.net>
billc <billclem@gmail.com> wrote:
+---------------
| Rob Warnock wrote:
| > I started using PostgreSQL with CMUCL about four years ago,
| > *not* with CLSQL, but using Eric Marsden's "PG" library...
| > [which] speaks the PostgreSQL socket protocol directly.
...
| You can also use the Postgres socket-level interface with CLSQL,
| bypassing the need to use the native-level libraries.
+---------------

Good to know, thanks!

Note that there were other reasons I used PG rather than CLSQL, the
main one being that neither the "functional" nor the "object-oriented"
interfaces provided in CLSQL seemed to match very well the needs of
the first big app I had to write, which required that *both* the
columns to SELECT *and* the match (WHERE) criteria had to be computed
at run-time[1] from data the user had input from an HTML form. That
is, the CLSQL:LOCALLY-ENABLE-SQL-READER-SYNTAX was useless unless I
wanted to build up a string and (EVAL (READ-FROM-STRING string)),
and if I wanted to do *that* I might as well just pass the equivalent
string directly to PostgreSQL [which is what I did]. Likewise, since
a majority of queries only needed a few of the *many* columns of each
table, the object-oriented interface would have resulted in much
unneeded data being read from the database into "objects", only
to be ignored (also putting more pressure on GC).

Had I been writing an app that modelled "business processes" with
queries known at compile time, I suspect the tradeoffs would have
been more favorable to CLSQL.


-Rob

[1] The LET* whose result is the desired query string is 45(!) lines
    of CL, finally ending with the following grotesque binding [in my
    defense, this was the first significant CL app I'd ever written]:

	(query (apply #'concatenate 'string
		      "SELECT "
		      (when editing-p "seq, vseq, ")
		      (when (eq query-use-type :comment)
			"coalesce(count(user_id),'0') AS \"items\", ")
		      adjusted-selections
		      " FROM contact_log"
		      (when (eq query-use-type :comment)
			" LEFT OUTER JOIN comments ON seq = user_id ")
		      " WHERE vact AND "
		      (if (eq query-use-type :comment)
			(append predicates
				(list " GROUP BY seq, vseq, "
				      adjusted-selections
				      " ORDER BY last, first"))
			predicates)))

    Only if the client user checked the "View Comments" button is
    the JOIN needed, but in that case we also need the "GROUP BY"
    since the "comments" table is a 1:N relation, and all we want
    here is the count of the number of comments about each "user_id".
    But that in turn requires that the "adjusted-selections" get
    merged into the "GROUP BY". *Blettch!*

-----
Rob Warnock			<rpw3@rpw3.org>
627 26th Avenue			<URL:http://rpw3.org/>
San Mateo, CA 94403		(650)572-2607