Do you have any experience in Postmodern as an interface to Postgres SQL?
Can you compare it to the CL-SQL family?
TIA
--
Lisp: Powering `Impossible Thoughts since 1958
--
Posted via a free Usenet account from http://www.teranews.com
On Thu, 2 Aug 2007 08:06:11 -0700, <········@CloudDancer.com> wrote:
> Do you have any experience in Postmodern as an interface to Postgres SQL?
> Can you compare it to the CL-SQL family?
http://common-lisp.net/project/postmodern/
> TIA
--
Lisp: Powering `Impossible Thoughts since 1958
--
Posted via a free Usenet account from http://www.teranews.com
On Aug 2, 5:06 pm, GP lisper <········@CloudDancer.com> wrote:
> Do you have any experience in Postmodern as an interface to Postgres SQL?
> Can you compare it to the CL-SQL family?
I used quite a lot CL-SQL some time ago, now I'm using Postmodern. In
general I wasn't very happy about CL-SQL: it was difficult to install,
I had some problems with DO-QUERY and I didn't like the strange syntax
with brackets. Postmodern seems to work better for me and I've even
written a number of hacks for it.
On the other hand, I may be biased: I've learned quite a lot since the
time I used CL-SQL (specially about CLOS) and Postmodern was developed
in the company I work right now.
Bests.
-- Richard
On Aug 2, 5:06 pm, GP lisper <········@CloudDancer.com> wrote:
> Do you have any experience in Postmodern as an interface to Postgres SQL?
> Can you compare it to the CL-SQL family?
I don't have any experience using it, but I have taken a look at it,
and it looks pretty reasonable. I've always avoided CL-SQL, which
looks like a bit of a disaster to me, and I've never gotten it to
install correctly. I have had very positive experiences with pg
(common-lisp.net/projects/pg). Postmodern takes the same approach: a
fairly small, targeted library that provides socket-level access to
Postgres.
Comparing Postmodern to pg, the main differences that pop out are the
friendlier license of Postmodern (BSD rather than LGPL), pg's support
for BLOBs, and pg's support for older versions of the Postgres
protocol. I'm certainly planning on trying out Postmodern the next
time I start a project that needs to access Postgres.
Postmodern also has a silly s-expression syntax for SQL that isn't
especially composable or lispy or even structured. It doesn't look
like a parse tree of SQL, it looks like SQL. That said, it looks
modest and so I'd imagine it lives up to its promises. I'd put this
in the "might be useful, but it's not going to swing things either
way" category.
On Sat, 04 Aug 2007 08:26:21 -0000, <········@gmail.com> wrote:
> On Aug 2, 5:06 pm, GP lisper <········@CloudDancer.com> wrote:
>> Do you have any experience in Postmodern as an interface to Postgres SQL?
>> Can you compare it to the CL-SQL family?
>
> I don't have any experience using it, but I have taken a look at it,
> and it looks pretty reasonable. I've always avoided CL-SQL, which
> looks like a bit of a disaster to me, and I've never gotten it to
> install correctly.
It never fails to astonish me that cl-sql can easily build itself from
scratch, but then needs to be told how to find itself...and that
'finding' nonsense isn't a pointer to a top-level directory, but
pointers to each object. pure windoze-brain-think introduced for
'portability', but in a fashion to cause the problems mentioned above.
(require :cl-sql) always finds the asdf file, but somehow this ability
fails to pass on to lower levels (some side-effect here seems smart).
> Comparing Postmodern to pg, the main differences that pop out are the
> friendlier license of Postmodern (BSD rather than LGPL), pg's support
> for BLOBs, and pg's support for older versions of the Postgres
Look at http://common-lisp.net/project/postmodern/cl-postgres.html
under "Row Readers", bytea is supposed to be some upgrade to BLOB.
> protocol. I'm certainly planning on trying out Postmodern the next
> time I start a project that needs to access Postgres.
>
> Postmodern also has a silly s-expression syntax for SQL that isn't
> especially composable or lispy or even structured. It doesn't look
> like a parse tree of SQL, it looks like SQL. That said, it looks
> modest and so I'd imagine it lives up to its promises. I'd put this
> in the "might be useful, but it's not going to swing things either
> way" category.
politically-correct-syntax is not very relevant to me, 'getting
results' beats it hands down. But the 3.7.X version of cl-sql I use
eats lists, ( 1 2 3 4) comes back (1,2,3,4) [perl has no problems],
and 3.8.X is incompatible or broken with regard to deployed software.
The problems are increasing. I need to write custom code too often,
so I've gotten to the "This library was written because the CLSQL
approach did not really work for me" anymore stage. CL-SQL was great
at first (painless drop-in, lets go code), but it seems time to move
on.
--
Lisp: Powering `Impossible Thoughts since 1958
--
Posted via a free Usenet account from http://www.teranews.com
GP lisper <········@CloudDancer.com> writes:
>> Comparing Postmodern to pg, the main differences that pop out are the
>> friendlier license of Postmodern (BSD rather than LGPL), pg's support
>> for BLOBs, and pg's support for older versions of the Postgres
>
> Look at http://common-lisp.net/project/postmodern/cl-postgres.html
> under "Row Readers", bytea is supposed to be some upgrade to BLOB.
That doesn't much make sense.
bytea is indeed a perfectly good way of storing Binary Large OBjects;
it offers the ability to stow something big and binary in a column.
But the functionality is *very* different from usual BLOB handling.
The "Large Object" interface essentially models the Unix filesystem
interface, where you get analogues to open(), read(), write(),
lseek(), and so forth.
<http://www.postgresql.org/docs/7.4/static/lo-interfaces.html>
Now, mapping that onto applications tends to be *so* inconvenient and
*so* non-transparent that the "bytea" handling, which involves plain
SQL, tends to look spectacularly better. (Mind you, remap the
interface with a little bit of CL macrology and it might actually look
somewhat nice...)
But to call bytea an "upgrade" to BLOB seems something like, I don't
know, calling OCaml an upgrade from Algol. They're too different for
it to be clear that what is being described is in fact an upgrade...
--
select 'cbbrowne' || ·@' || 'linuxdatabases.info';
http://linuxfinances.info/info/
Thank you for onlining with ITS --
Be sure to patronize us again for your next fix.
On Sat, 04 Aug 2007 18:23:42 GMT, <········@ca.afilias.info> wrote:
> GP lisper <········@CloudDancer.com> writes:
>>> Comparing Postmodern to pg, the main differences that pop out are the
>>> friendlier license of Postmodern (BSD rather than LGPL), pg's support
>>> for BLOBs, and pg's support for older versions of the Postgres
>
> But to call bytea an "upgrade" to BLOB seems something like, I don't
> know, calling OCaml an upgrade from Algol. They're too different for
> it to be clear that what is being described is in fact an upgrade...
Not my words, just what I googled, your info was far better.
--
Lisp: Powering `Impossible Thoughts since 1958
--
Posted via a free Usenet account from http://www.teranews.com
On Aug 4, 7:01 pm, GP lisper <········@CloudDancer.com> wrote:
> On Sat, 04 Aug 2007 08:26:21 -0000, <········@gmail.com> wrote:
>
> > Postmodern also has a silly s-expression syntax for SQL that isn't
> > especially composable or lispy or even structured. It doesn't look
> > like a parse tree of SQL, it looks like SQL. That said, it looks
> > modest and so I'd imagine it lives up to its promises. I'd put this
> > in the "might be useful, but it's not going to swing things either
> > way" category.
>
> politically-correct-syntax is not very relevant to me, 'getting
> results' beats it hands down.
If you don't see any getting-thing-done value in composable syntax,
might I recommend you try Visual Basic instead of Lisp. This s-sql
syntax is no different than using a format wrapper to generate sql
strings: say you were supplied a query by another function and you now
what to restrict it to only give you tuples where (date > yesterday)
and you want them ordered by date. Tough. You have to parse the SQL
yourself to insert the right tokens in the right places. Syntax
matters. Without structure, you end up with Tcl.
But SQL-generation schemes are not the main thing I look for in a
rdbms-access library.
On Sun, 05 Aug 2007 09:55:28 -0000, <········@gmail.com> wrote:
> On Aug 4, 7:01 pm, GP lisper <········@CloudDancer.com> wrote:
>> On Sat, 04 Aug 2007 08:26:21 -0000, <········@gmail.com> wrote:
>>
>> > Postmodern also has a silly s-expression syntax for SQL that isn't
>> > especially composable or lispy or even structured. It doesn't look
>> > like a parse tree of SQL, it looks like SQL. That said, it looks
>> > modest and so I'd imagine it lives up to its promises. I'd put this
>> > in the "might be useful, but it's not going to swing things either
>> > way" category.
>>
>> politically-correct-syntax is not very relevant to me, 'getting
>> results' beats it hands down.
>
> If you don't see any getting-thing-done value in composable syntax,
> might I recommend you try Visual Basic instead of Lisp. This s-sql
> syntax is no different than using a format wrapper to generate sql
> strings: say you were supplied a query by another function and you now
> what to restrict it to only give you tuples where (date > yesterday)
> and you want them ordered by date. Tough. You have to parse the SQL
> yourself to insert the right tokens in the right places. Syntax
> matters. Without structure, you end up with Tcl.
>
> But SQL-generation schemes are not the main thing I look for in a
> rdbms-access library.
Perhaps the view up there is too high for you to see that you missed
my point entirely. Only your last paragraph hit it.
--
Lisp: Powering `Impossible Thoughts since 1958
--
Posted via a free Usenet account from http://www.teranews.com
"Thomas F. Burdick" <········@gmail.com> writes:
> On Aug 2, 5:06 pm, GP lisper <········@CloudDancer.com> wrote:
>> Do you have any experience in Postmodern as an interface to Postgres SQL?
>> Can you compare it to the CL-SQL family?
>
> I don't have any experience using it, but I have taken a look at it,
> and it looks pretty reasonable. I've always avoided CL-SQL, which
> looks like a bit of a disaster to me, and I've never gotten it to
> install correctly. I have had very positive experiences with pg
> (common-lisp.net/projects/pg). Postmodern takes the same approach: a
> fairly small, targeted library that provides socket-level access to
> Postgres.
>
> Comparing Postmodern to pg, the main differences that pop out are the
> friendlier license of Postmodern (BSD rather than LGPL),
[seeing as how PostgreSQL itself is BSD-licensed, there's irony or
something there...]
> pg's support for BLOBs,
[usually this will be a "could care less" thing; BLOB use isn't *too*
common]
> and pg's support for older versions of the Postgres protocol.
It's worth observing that the versions of PostgreSQL that are thereby
not supported are *very* old. Version 7.4, which *is* supported, was
released in 2003, and it would be irresponsible to deploy 7.3
(unsupported by Postmodern) for new applications. At work, we only
had one 7.3 instance ever deployed, and it got eliminated a couple of
years ago.
As a data point associated with PostgreSQL, directly, I'm one of the
developers for the Slony-I replication system; we decided to stop
supporting 7.3 last year.
> I'm certainly planning on trying out Postmodern the next time I
> start a project that needs to access Postgres.
Ditto...
--
let name="cbbrowne" and tld="cbbrowne.com" in name ^ ·@" ^ tld;;
http://linuxfinances.info/info/finances.html
Every program is a part of some other program and rarely fits.
-- Alan J. Perlis
On Aug 4, 8:13 pm, Christopher Browne <········@ca.afilias.info>
wrote:
> "Thomas F. Burdick" <········@gmail.com> writes:
>
> > and pg's support for older versions of the Postgres protocol.
>
> It's worth observing that the versions of PostgreSQL that are thereby
> not supported are *very* old. Version 7.4, which *is* supported, was
> released in 2003, and it would be irresponsible to deploy 7.3
> (unsupported by Postmodern) for new applications. At work, we only
> had one 7.3 instance ever deployed, and it got eliminated a couple of
> years ago.
No disagreement from me on this point, but if you need to connect to
an old installation of 7.1 running on an Ultra 2 or some old Dell
running FreeBSD 3.3, Postmodern isn't for you. That's hardly the
common case, though.
Thomas F. Burdick wrote:
> Postmodern also has a silly s-expression syntax for SQL that isn't
> especially composable or lispy or even structured. It doesn't look
> like a parse tree of SQL, it looks like SQL.
Ah now, I've been playing with query languages lately as well. I wanted
something that would translate, say,
(to-sql '(limit (order relation attribute :desc) 5 15)))
into
"SELECT * FROM RELATION ORDER BY ATTRIBUTE DESC OFFSET 5 LIMIT 10 "
or
(to-sql '(project (att1 (as att2 name) att3) relation))
=> "SELECT ATT1,ATT2 AS NAME,ATT3 FROM RELATION "
but also, not to stop when it reaches the limits of what can be
expressed in a single sql statement. Let us suppose that for some odd
reason you wanted to apply the offset and limit *before* you ordered the
data -
(to-sql '(order (limit relation 5 15) attribute :desc) )
=>
"SELECT * FROM (SELECT * FROM RELATION OFFSET 5 LIMIT 10 ) RELATION
ORDER BY ATTRIBUTE DESC "
- when assembling sql it knows when it's "filled up" the current
statement and has to create a subselect.
Couple more examples:
(to-sql '(join rel1 rel2 predicate)) ; we only do inner joins currently
=> "SELECT * FROM (REL1 JOIN REL2 ON PREDICATE) "
(to-sql '(rename (join rel1 rel2 predicate) newname)) ; rename
=> "SELECT * FROM (REL1 JOIN REL2 ON PREDICATE) NEWNAME "
and 'group by': I've never been entirely happy with the sql syntax for
this because it doesn't make it clear which columns in the output are
controlling the grouping and which are the aggregates that get
summarized across each group. (When I say "group" I am using the SQL
meaning, not the relational algebra meaning, which aiui is different).
So we split them: aggregates in the first argument and "dividers", for
want of a better word, in the second -
(7ql:to-sql '(group ((as (sum quantity) quantity) (as (sum (* quantity
cost)) value))
(event-id event-description)
order-line))
;; received
"SELECT SUM(QUANTITY) AS QUANTITY,SUM((QUANTITY * COST)) AS
VALUE,EVENT_ID,EVENT_DESCRIPTION FROM ORDER_LINE GROUP BY
EVENT_ID,EVENT_DESCRIPTION "
This is all done by a library called SEPTEQL (because it's the successor
to SEXQL, my previous attempt along the same lines). As yet unpublished
because I will probably want to change syntax incompatibly after using
it a bit more myself (ORDER doesn't accept multiple columns, for one
thing), but in the meantime if anyone wants to play I can bung them a
tarball on strict "don't expect it to stay like that" terms.
Yet more examples: http://ww.telent.net/tmp/septeql-tests.txt
-dan
From: Steven E. Harris
Subject: Re: Composable rdbms queries
Date:
Message-ID: <7ylkcoa6t0.fsf@panix.com>
Daniel Barlow <···@coruskate.net> writes:
> Ah now, I've been playing with query languages lately as well.
Though you did state your goal is to produce SQL, I'm curious if you've
read about Tutorial D. Your alternate syntax reminded me of it.
--
Steven E. Harris
On Mon, 06 Aug 2007 13:39:07 +0100, <···@coruskate.net> wrote:
> Thomas F. Burdick wrote:
>> Postmodern also has a silly s-expression syntax for SQL that isn't
>> especially composable or lispy or even structured. It doesn't look
>> like a parse tree of SQL, it looks like SQL.
>
> Ah now, I've been playing with query languages lately as well. I wanted
> something that would translate, say,
>
> (to-sql '(limit (order relation attribute :desc) 5 15)))
>
> into
>
> "SELECT * FROM RELATION ORDER BY ATTRIBUTE DESC OFFSET 5 LIMIT 10 "
>
> or
> (to-sql '(project (att1 (as att2 name) att3) relation))
>=> "SELECT ATT1,ATT2 AS NAME,ATT3 FROM RELATION "
...
> This is all done by a library called SEPTEQL (because it's the successor
> to SEXQL, my previous attempt along the same lines). As yet unpublished
> because I will probably want to change syntax incompatibly after using
> it a bit more myself (ORDER doesn't accept multiple columns, for one
> thing), but in the meantime if anyone wants to play I can bung them a
> tarball on strict "don't expect it to stay like that" terms.
>
> Yet more examples: http://ww.telent.net/tmp/septeql-tests.txt
:-)
Somedays on c.l.l., you get back something that you _really_ wanted.
--
Lisp: Powering `Impossible Thoughts since 1958
--
Posted via a free Usenet account from http://www.teranews.com
Daniel Barlow wrote:
> Thomas F. Burdick wrote:
>> Postmodern also has a silly s-expression syntax for SQL that isn't
>> especially composable or lispy or even structured. It doesn't look
>> like a parse tree of SQL, it looks like SQL.
>
> Ah now, I've been playing with query languages lately as well. I wanted
> something that would translate, say,
>
> (to-sql '(limit (order relation attribute :desc) 5 15)))
>
> into
>
> "SELECT * FROM RELATION ORDER BY ATTRIBUTE DESC OFFSET 5 LIMIT 10 "
I have been down this path too, but I never seemed to be able to settle
on something I really liked that worked in all cases. The other problem
is that if I wanted to use some complicated SQL from another source or
developed something interactively in a SQL tool, I then felt compelled
to convert it to my Lispy syntax.
For now I have given up and written some tools to make SQL look better
embedded in Lisp. I have a fairly simple reader macro that converts SQL
statements into format statements. It even does SQL syntax coloring for
LispWorks. Here is a simple example:
(defmethod property-view-id ((class (eql 'pg-table)) id)
(transpose-columns
(query #$ select relname as "Name",
n.nspname as "Schema",
c.oid as "OID",
pg_get_userbyid(relowner) as "Owner",
relacl as "ACL",
d.description as "Comment"
from pg_class c
left join pg_namespace n on n.oid = c.relnamespace
left join pg_description d on d.objoid = c.oid
where c.oid = `id;
:parse nil)))
I'll post some code for the reader macro and some better examples when I
get a chance.
John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL
John DeSoi wrote:
> I have been down this path too, but I never seemed to be able to settle
> on something I really liked that worked in all cases. The other problem
> is that if I wanted to use some complicated SQL from another source or
> developed something interactively in a SQL tool, I then felt compelled
> to convert it to my Lispy syntax.
Heh, I feel no such compulsion. The virtue of a sexp-based syntax is
that you can manipulate the query programmatically: you can have canned
sql as well and there's no need to open the tin.
Anyway, for the curious there's now a Darcs thingy for Septeql and a
tarball at http://src.telent.net/septeql/
-dan
On Aug 2, 5:06 pm, GP lisper <········@CloudDancer.com> wrote:
> Do you have any experience in Postmodern as an interface to Postgres SQL?
> Can you compare it to the CL-SQL family?
Note that the maintainer is travelling for a while. I expect pulling
from Darcs is better than going by the official release.
http://common-lisp.net/pipermail/postmodern-devel/2007-July/000064.html
I wish more of the internals were opened up, or better ability to
customize things. But the sourcecode looks pretty trivial to alter.
For instance, having all tables inherit from standard-dao requires
altering only 3 lines of code, AFAICT. It's probably similarly trivial
to allow the user to specify other desired superclasses.
Tayssir