As far as programming languages go, my truly favorite language is a Lisp. Scheme or Common Lisp are fine. I have a few hesitations about Clojure (the way it does recursion is offputting to me, but perhaps a few macros might clean it up), but for what I’ve done with it when working with Riemann, it’s been a pleasure, even if it’s slow going because I don’t do it frequently enough to have the needed bits already in my head.
In one of my previous posts, I wrote about how I’d written the website software for a website I maintain in Common Lisp. There were a few macros I wrote while building it that I thought were potentially useful and illustrative of the kinds of things you can do with macros that make Lisps cool.
In this post (there may be others later), I’ll be talking about a macro I wrote to deal with database queries. When dealing with database queries, making sure that you write/generate the queries properly with respect to escaping and bind variables, and connection caching, etc. can be a real pain, and I’m not a big fan of ORMs as they never really do what I want.
What I wanted was to be able to just have a macro that would just do The Right Thing ™. Ideally, in a simple case it would be something like this:
Where this would execute the query:
scripture-of-the-week-box-id bound appropriately. Better
yet, if I could do something like this:
(23423, 125235, 34234) and
"ASC", it would execute
name-ids bound appropriately.
“Can’t you do the same thing in non-lisp languagues?” you might ask.
Not really. “Why not?” you ask. Simple: because if you
were in a non-lisp language, you couldn’t tell the difference between
the SQL string literals, and any string-resulting expressions that
might get passed in. With lisp macros, since you have access to the
expressions before they’re evaluated, we can look at the expression to
know if an argument to it was a string literal or an expression or a
(:SQL expr) construct.
In the macro as used above, if anything we have a little of the
opposite problem that results in the need of the
construct above – it makes it so the query generator doesn’t treat
the expression as something that needs to be bound, but in my
experience, that tends to be the exception rather than the rule.
So how do we go about putting something together that does this?
1 2 3
For those who’ve not seen Common Lisp before, the
&body args bit is
*args parametr in Python, or whatever the “just give
me all the args they passed in” declaration in your language of
First we need a function that can determine if the item in
args is a
literal string, a
(:SQL expr) or a value that we’ll need to stuff
into bind variables.
1 2 3 4
util:it bits are an anaphoric if construct.
Basically, it’s an
if expression where
util:it evaluates to the
value of the conditional. In this case it’s the return value of the
literal-sql function. The
literal-sql function is defined as
1 2 3
This function returns the expression if the argument
x is of the
(:SQL expr). So the effect of
literal-or-dovalue is to
return strings as is,
(:SQL expr) expressions as
expr and to turn
everything else into
(dovalue expr). We’ve not defined dovalue yet,
so lets do that:
1 2 3 4 5 6 7
For the moment, we’ll ignore the fact that the function as specified
will really only work once only due to the outer let. But what this
does, is adds the item to the list of
items which will be the list
of items we pass as bind arguments to the SQL connection, and returns
the proper placeholder for the item to embed into SQL.
ensure-list function has a fairly obvious implementation:
generate-placeholder generates “?” for an atomic value and
“?,?,…?” for list values using a smattering of
1 2 3 4
All that’s left to do then is to compose the SQL string together:
and execute it:
In the real implementation, it gets the connection from a database pool rather than having it passed as a global as we’re doing here.
So now, we macroize the whole thing and get this:
1 2 3 4 5 6 7 8 9 10 11 12
Here, we first use with-gensyms to make some names so we don’t
capture anything we don’t want to. Then we
flet to inline
literal-or-dovalue since it doesn’t need to be seen outside the macro
anywhere. Then we, per macro-invocation, set up the
we had before to wrap the
dovalue function, so we can use it more
than once :). Lastly we generate the sql and exec it.