Tuesday, September 19, 2006

New ErlSQL Feature: Lisp-style Operator Expansion

When you first write a library, you really don't know how useful it can be. You often discover new ways of enhancing it and making it more powerful only when you start using it. This has been the case with almost every library I wrote: Smerl, ErlyDB, and now ErlSQL.

While I was using ErlSQL to hack ErlyDB's internals, I realized what a pain it is to use the SQL way of writing repeated expressions that use the same binary operator, e.g. {{{a,'=',b}, 'and', {c,'=',d}}, 'and', {e,'=',f}}. Lisp handles such situations much more elegantly than SQL (I know, it's hard to believe that anything could be more elegant than SQL, but what do you know :) ). In Lisp, you would write (and (= a b) (= c d) (= e f)), which spares you having to write the 'AND' operator for each new element in the list.

(Please forgive me if I made any egregious Lisp syntax errors in this example. I haven't touched Lisp since college and my knowledge of it is very rusty :) )

The Lisp way is often much more concise than the SQL way, so I implemented a Lisp-style operator expansion feature in ErlSQL.

Here are a few examples of how to use this feature:

{where,{a,'=',{'+',[1,2,3]}}}} ->
"SELECT * FROM foo WHERE (a = 1 + 2 + 3)"

{where,{'=',[{'+',[a,b,c]},{'+',[d,e,f]}]}}} ->
"SELECT * FROM foo WHERE a + b + c = d + e + f"

{'and',[{a,'=',b},{c,'=',d},{e,'=',f}]}}} ->
"SELECT * FROM foo WHERE (a = b) AND (c = d) AND (e = f)"

Any expression which is a tuple where the first item is an operator and the second item is a list will be expanded by ErlSQL in a similar fashion.


Yariv said...

Demius, unfortunately the commas are necessary according to Erlang's syntax rules. In some cases, Lisp code looks better than Erlang code, and DSELs is apparently one of them :) Sometimes I wish Erlang were a bit more Lispy, but then again I often find Erlang code more readable than Lisp. It's a give-and-take. But I agree -- in this particular example, Lisp wins hands on.

Markus said...

Many Lisps allow "=" with an arbitrary number of arguments, i.e.
(= a b c d ...)
evaluates to #t iff all arguments are equal.

Yariv said...

Hi Polli, your library sounds very cool. Did you open source it by any chance? I've never heard of a small Lispy language capable of programming Java GUIs, which in general are far from Lispy. On the mailing list, there's has been some discussion about using parse-transforms, but I haven't looked into it myself. It sounds like an approach worth exploring, though.

Yariv said...

It sounds like you made something interesting, but I wouldn't want you to spend much time refining it because I probably wouldn't have immediate use for it. However, other people may find it useful. It's your call.

Dennis said...

What lisp syntax? :)

LoRd said...

Hi Yariv,

I am really have a problem to write a SQL func for mnesia. Can you give me an SQL example for mnesia using GROUP BY statement.?

many thanks

Marcelo Cantos said...

Sorry, for coming late to the party, but it seems to me that mixing tuples and lists isn't helping the cause. This can be remedied by converting every 2-tuple {A,B} to a list [A|B].

original: {where,{'=',[{'+',[a,b,c]},{'+',[d,e,f]}]}}}
pure lists: [where,'=',['+',a,b,c],['+',d,e,f]]

Still not on par with Lisp, but quite an improvement, I think. Also, if a 2-tuple is identical to a cons cell, the storage and computational efficiency could well be the same.

Yariv said...

It is a bit late :) In designing this feature, I decided to follow Erlang's convention of using tuples where the number of elements is fixed, and lists where it's variable. I'm not too worried about the efficiency, and I recognize it may be a bit confusing to read, but if you remind yourself that think tuple = fixed and list = variable, it will be easier to digest.

Vadim Zingertal said...

This all very good and fancy, but can you write Correlated Subquery in this ErlSQL of yours? :)
I hope you still remmeber what that is - we haven't talked fo awhile, you know.
I know you work with Peter now - good for both of you. I'm in Palo Alto these days, so let's get together for lunch or dinner or whatever.
Give me a call (510-207-2651) or email when (if) you have time.

- Vadim.

Vadim Zingertal said...

BTW, you have a type on this page:

My Projects
ErlyWeb: The Erlang Twist on Web Fram[e]works