Saturday, 19 January 2008

Some useful CLSQL helper functions

Quite often, you need to query the database for when all values equal something or the other. For example, you might look for an authenticated user as follows (in SQL):


SELECT USERS.* FROM USERS WHERE USER_LOGIN='sohail' AND USER_PASSWORD='myhashedpassword'

Of course, in the year 2008, no one writes their own SQL anymore (or so Rails propaganda would have you believe!) So you use something like CLSQL and end up writing something like:

(clsql:select 'user :where [ and [ = [ user-login ] "sohail" ] [ user-password ] 'myhashedpassword' ])

Certainly more Lispy but still quite annoying. The Rails and Django guys have you write something that looks like:

ModelObject.find(id=5)

I've written a couple of helper functions for CLSQL (reproduced below) that let you write:

CL-USER> (find-one 'user :user-login "sohail" :user-password "myhashedpassword")
#<USER {B47D0C9}>
CL-USER> (find-all 'user :user-site-id 3)
(#<USER {B729209}> #<USER {B72AD81}>)

Makes it a lot easier to write bespoke queries without resorting to CLSQL's bracket syntax or SQL itself.

The code is here (would appreciate any comments as to how to clean it up a bit!):

(defun find-all (type &rest args &key (clause-op 'and) &allow-other-keys)
(if (> (length args) 0) ;; if there are any filters to apply
(let ((expressions (loop for (k v) on args by #'cddr
collect
(let ((op '=)
(value v))
(make-instance 'clsql-sys:sql-relational-exp
:operator op
:sub-expressions
(list (make-instance 'clsql-sys:sql-ident-attribute :name k :qualifier nil :type nil)
value))))))
(clsql:select type :flatp t
:where (make-instance 'clsql-sys:sql-relational-exp
:operator clause-op
:sub-expressions expressions)))
(clsql:select type :flatp t)))


(defun find-one (&rest args)
(let ((result (apply #'find-all args)))
(if (= 1 (length result))
(first result)
(error "More than one result returned when only one expected!"))))

Enjoy!

2 comments:

olli said...

It seems to me that 'user in your select query must be a functional/object representation of a table and not the table name itself. This needs some clarification (IMHO), as I tried your code with
(find-all '"email-table" :name "Jim")
and it didn't work. Well, it wasn't supposed to work this way, was it?

Sohail Somani said...

Olli, you're right. It was not meant to work that way.