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:

(doquery "select body from boxes where bid = " scripture-of-the-week-box-id)

Where this would execute the query:

select body from boxes where bid = ?

with the scripture-of-the-week-box-id bound appropriately. Better yet, if I could do something like this:

(doquery "select name from names where id in " name-ids " order by names " (:SQL sort-order))

where if name-ids were (23423, 125235, 34234) and sort-order were "ASC", it would execute

select name from names where id in (?, ?, ?) order by names ASC

with 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 (:SQL expr) 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?

(defmacro doquery (&body args)
  ;; stuff here
)

For those who’ve not seen Common Lisp before, the &body args bit is essentially the *args parametr in Python, or whatever the “just give me all the args they passed in” declaration in your language of choice.

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.

(defun literal-or-dovalue (x)
    (if (stringp x) x
        (util:aif (literal-sql x) util:it
            `(,dovalue ,x)))))

The util:if and 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 follows:

(defun literal-sql (x)
  (and (consp x)
       (eq (car x) :sql) (cadr x)))

This function returns the expression if the argument x is of the form (: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:

(let ((sql nil)
      (items (list)))

    (defun dovalue (item)
        (setf items (append items (ensure-list item)))
        (generate-placeholder item))
)

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.

The ensure-list function has a fairly obvious implementation:

(defun ensure-list (item)
  (if (consp item) item (list item)))

The generate-placeholder generates “?” for an atomic value and “?,?,…?” for list values using a smattering of format magic:

(defun generate-placeholder (item)
  (if (consp item)
      (format nil "~{~A~^,~}" (mapcar (lambda (x) (declare (ignore x)) "?") item))
      "?"))

All that’s left to do then is to compose the SQL string together:

(setf sql (apply #'util:strcat (mapcar #'literal-or-dovalue args)))

and execute it:

(apply #'plain-odbc:exec-query (append (list conn query) items))

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:

(defmacro doquery (&body args)
  (util:with-gensyms (sql items dovalue)
    (flet ((literal-or-dovalue (x) 
             (if (stringp x) x (util:aif (literal-sql x) util:it `(,dovalue ,x)))))
      `(let ((,sql)
             (,items (list))) 
             (flet ((,dovalue (item)
                      (setf ,items (append ,items (ensure-list item)))
                      (generate-placeholder item)))
               (setf ,sql (apply #'util:strcat (list ,@(mapcar #'literal-or-dovalue args))))

               (apply #'plain-odbc:exec-query (append (list conn query) items)))))))

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 let statement we had before to wrap the dovalue function, so we can use it more than once :). Lastly we generate the sql and exec it.