Matching Functions

The :where argument to select can be any function that takes a row object and returns true if it should be included in the results. In practice, however, you’ll rarely need the full power of arbitrary code to express query criteria. So you should provide two functions, matching and in, that will build query functions that allow you to express the common kinds of queries and that take care of using the proper equality predicates and value normalizers for each column.

The workhouse query-function constructor will be matching, which returns a function that will match rows with specific column values. You saw how it was used in the earlier examples of select. For instance, this call to matching:

(matching *mp3s* :artist "Green Day")

returns a function that matches rows whose :artist value is “Green Day”. You can also pass multiple names and values; the returned function matches when all the columns match. For example, the following returns a closure that matches rows where the artist is “Green Day” and the album is “American Idiot”:

(matching *mp3s* :artist "Green Day" :album "American Idiot")

You have to pass matching the table object because it needs access to the table’s schema in order to get at the equality predicates and value normalizer functions for the columns it matches against.

You build up the function returned by matching out of smaller functions, each responsible for matching one column’s value. To build these functions, you should define a function, column-matcher, that takes a column object and an unnormalized value you want to match and returns a function that accepts a single row and returns true when the value of the given column in the row matches the normalized version of the given value.

(defun column-matcher (column value)
  (let ((name (name column))
        (predicate (equality-predicate column))
        (normalized (normalize-for-column value column)))
    #'(lambda (row) (funcall predicate (getf row name) normalized))))

You then build a list of column-matching functions for the names and values you care about with the following function, column-matchers:

(defun column-matchers (schema names-and-values)
  (loop for (name value) on names-and-values by #'cddr
     when value collect
       (column-matcher (find-column name schema) value)))

Now you can implement matching. Again, note that you do as much work as possible outside the closure in order to do it only once rather than once per row in the table.

(defun matching (table &rest names-and-values)
  "Build a where function that matches rows with the given column values."
  (let ((matchers (column-matchers (schema table) names-and-values)))
    #'(lambda (row)
        (every #'(lambda (matcher) (funcall matcher row)) matchers))))

This function is a bit of a twisty maze of closures, but it’s worth contemplating for a moment to get a flavor of the possibilities of programming with functions as first-class objects.

The job of matching is to return a function that will be invoked on each row in a table to determine whether it should be included in the new table. So, matching returns a closure with one parameter, row.

Now recall that the function **EVERY** takes a predicate function as its first argument and returns true if, and only if, that function returns true each time it’s applied to an element of the list passed as **EVERY**‘s second argument. However, in this case, the list you pass to **EVERY** is itself a list of functions, the column matchers. What you want to know is that every column matcher, when invoked on the row you’re currently testing, returns true. So, as the predicate argument to **EVERY**, you pass yet another closure that **FUNCALL**s the column matcher, passing it the row.

Another matching function that you’ll occasionally find useful is in, which returns a function that matches rows where a particular column is in a given set of values. You’ll define in to take two arguments: a column name and a table that contains the values you want to match. For instance, suppose you wanted to find all the songs in the MP3 database that have names the same as a song performed by the Dixie Chicks. You can write that where clause using in and a subselect like this:4

(select
  :columns '(:artist :song)
  :from *mp3s*
  :where (in :song 
             (select
               :columns :song
               :from *mp3s*
               :where (matching *mp3s* :artist "Dixie Chicks"))))

Although the queries are more complex, the definition of in is much simpler than that of matching.

(defun in (column-name table)
  (let ((test (equality-predicate (find-column column-name (schema table))))
        (values (map 'list #'(lambda (r) (getf r column-name)) (rows table))))
    #'(lambda (row)
        (member (getf row column-name) values :test test))))