diary at Telent Netowrks

s-expressions for SQL#

Tue, 28 Sep 2004 00:00:25 +0000

s-expressions for SQL.

(This text also appeared on the Clump mailing list; if you read it here you can skip it there, or vice versa)

Did you know that

SELECT foo,bar 
   from table1 
   join (select * from table2 where some_criterion limit 10) table2
   on table1.table2id=table2.id
is (according to the postgres manual, at least) valid SQL? After finding a need for such a query on Saturday, and that my existing fairly ad-hoc sexpr-to-sql translator wouldn't cope with having entire 'select' statements where it was expecting table names, I set out to design something that would. And that doesn't use custom reader macros, which I dislike (they confuse my editor, and there always seems to be something else that wants to redefine them incompatibly)

By working through the Postgres documentation and constructing nested forms based on the order it claimed pgsql does things in, I arrived at a syntax which is probably not completely unlike the parse tree that a SQL processor would generate. Thus

(select (foo bar) 
        (join table1 
              (alias (limit (select (*) (where table2 some-criterion))
                            :end 10) table2)
              :on (= table1.table2id table2.id)))
will translate to the sql form above. The immediate objective is to make it much easier to programmatically compose queries.

I have provisionally called this beast SEXQL, because the name SQL has been taken already and SEXPQL is awkward to pronounce. The implementation is still work-in-progress and I haven't been through the whole sql language thoroughly; I've just added the tokens I have a need for as I go along.

You can see it, such as it is, at http://cvs.telent.net/cgi-bin/viewcvs.cgi/sexql/sexql.lisp?view=markup. Feedback welcome, preferably on clump