Querying the Database

Now that you have a way to save and reload the database to go along with a convenient user interface for adding new records, you soon may have enough records that you won’t want to be dumping out the whole database just to look at what’s in it. What you need is a way to query the database. You might like, for instance, to be able to write something like this:

  1. (select :artist "Dixie Chicks")

and get a list of all the records where the artist is the Dixie Chicks. Again, it turns out that the choice of saving the records in a list will pay off.

The function **REMOVE-IF-NOT** takes a predicate and a list and returns a list containing only the elements of the original list that match the predicate. In other words, it has removed all the elements that don’t match the predicate. However, **REMOVE-IF-NOT** doesn’t really remove anything—it creates a new list, leaving the original list untouched. It’s like running grep over a file. The predicate argument can be any function that accepts a single argument and returns a boolean value—**NIL** for false and anything else for true.

For instance, if you wanted to extract all the even elements from a list of numbers, you could use **REMOVE-IF-NOT** as follows:

  1. CL-USER> (remove-if-not #'evenp '(1 2 3 4 5 6 7 8 9 10))
  2. (2 4 6 8 10)

In this case, the predicate is the function **EVENP**, which returns true if its argument is an even number. The funny notation #' is shorthand for “Get me the function with the following name.” Without the #', Lisp would treat evenp as the name of a variable and look up the value of the variable, not the function.

You can also pass **REMOVE-IF-NOT** an anonymous function. For instance, if **EVENP** didn’t exist, you could write the previous expression as the following:

  1. CL-USER> (remove-if-not #'(lambda (x) (= 0 (mod x 2))) '(1 2 3 4 5 6 7 8 9 10))
  2. (2 4 6 8 10)

In this case, the predicate is this anonymous function:

  1. (lambda (x) (= 0 (mod x 2)))

which checks that its argument is equal to 0 modulus 2 (in other words, is even). If you wanted to extract only the odd numbers using an anonymous function, you’d write this:

  1. CL-USER> (remove-if-not #'(lambda (x) (= 1 (mod x 2))) '(1 2 3 4 5 6 7 8 9 10))
  2. (1 3 5 7 9)

Note that lambda isn’t the name of the function—it’s the indicator you’re defining an anonymous function.5 Other than the lack of a name, however, a **LAMBDA** expression looks a lot like a **DEFUN**: the word lambda is followed by a parameter list, which is followed by the body of the function.

To select all the Dixie Chicks’ albums in the database using **REMOVE-IF-NOT**, you need a function that returns true when the artist field of a record is "Dixie Chicks". Remember that we chose the plist representation for the database records because the function **GETF** can extract named fields from a plist. So assuming cd is the name of a variable holding a single database record, you can use the expression (getf cd :artist) to extract the name of the artist. The function **EQUAL**, when given string arguments, compares them character by character. So (equal (getf cd :artist) "Dixie Chicks") will test whether the artist field of a given CD is equal to "Dixie Chicks". All you need to do is wrap that expression in a **LAMBDA** form to make an anonymous function and pass it to **REMOVE-IF-NOT**.

  1. CL-USER> (remove-if-not
  2.   #'(lambda (cd) (equal (getf cd :artist) "Dixie Chicks")) *db*)
  3. ((:TITLE "Home" :ARTIST "Dixie Chicks" :RATING 9 :RIPPED T)
  4.  (:TITLE "Fly" :ARTIST "Dixie Chicks" :RATING 8 :RIPPED T))

Now suppose you want to wrap that whole expression in a function that takes the name of the artist as an argument. You can write that like this:

  1. (defun select-by-artist (artist)
  2.   (remove-if-not
  3.    #'(lambda (cd) (equal (getf cd :artist) artist))
  4.    *db*))

Note how the anonymous function, which contains code that won’t run until it’s invoked in **REMOVE-IF-NOT**, can nonetheless refer to the variable artist. In this case the anonymous function doesn’t just save you from having to write a regular function—it lets you write a function that derives part of its meaning—the value of artist--from the context in which it’s embedded.

So that’s select-by-artist. However, selecting by artist is only one of the kinds of queries you might like to support. You could write several more functions, such as select-by-title, select-by-rating, select-by-title-and-artist, and so on. But they’d all be about the same except for the contents of the anonymous function. You can instead make a more general select function that takes a function as an argument.

  1. (defun select (selector-fn)
  2.   (remove-if-not selector-fn *db*))

So what happened to the #'? Well, in this case you don’t want **REMOVE-IF-NOT** to use the function named selector-fn. You want it to use the anonymous function that was passed as an argument to select in the variable selector-fn. Though, the #' comes back in the call to select.

  1. CL-USER> (select #'(lambda (cd) (equal (getf cd :artist) "Dixie Chicks")))
  2. ((:TITLE "Home" :ARTIST "Dixie Chicks" :RATING 9 :RIPPED T)
  3.  (:TITLE "Fly" :ARTIST "Dixie Chicks" :RATING 8 :RIPPED T))

But that’s really quite gross-looking. Luckily, you can wrap up the creation of the anonymous function.

  1. (defun artist-selector (artist)
  2.   #'(lambda (cd) (equal (getf cd :artist) artist)))

This is a function that returns a function and one that references a variable that—it seems—won’t exist after artist-selector returns.6 It may seem odd now, but it actually works just the way you’d want—if you call artist-selector with an argument of "Dixie Chicks", you get an anonymous function that matches CDs whose :artist field is "Dixie Chicks", and if you call it with "Lyle Lovett", you get a different function that will match against an :artist field of "Lyle Lovett". So now you can rewrite the call to select like this:

  1. CL-USER> (select (artist-selector "Dixie Chicks"))
  2. ((:TITLE "Home" :ARTIST "Dixie Chicks" :RATING 9 :RIPPED T)
  3.  (:TITLE "Fly" :ARTIST "Dixie Chicks" :RATING 8 :RIPPED T))

Now you just need some more functions to generate selectors. But just as you don’t want to have to write select-by-title, select-by-rating, and so on, because they would all be quite similar, you’re not going to want to write a bunch of nearly identical selector-function generators, one for each field. Why not write one general-purpose selector-function generator, a function that, depending on what arguments you pass it, will generate a selector function for different fields or maybe even a combination of fields? You can write such a function, but first you need a crash course in a feature called keyword parameters.

In the functions you’ve written so far, you’ve specified a simple list of parameters, which are bound to the corresponding arguments in the call to the function. For instance, the following function:

  1. (defun foo (a b c) (list a b c))

has three parameters, a, b, and c, and must be called with three arguments. But sometimes you may want to write a function that can be called with varying numbers of arguments. Keyword parameters are one way to achieve this. A version of foo that uses keyword parameters might look like this:

  1. (defun foo (&key a b c) (list a b c))

The only difference is the &key at the beginning of the argument list. However, the calls to this new foo will look quite different. These are all legal calls with the result to the right of the ==>:

  1. (foo :a 1 :b 2 :c 3)  ==> (1 2 3)
  2. (foo :c 3 :b 2 :a 1)  ==> (1 2 3)
  3. (foo :a 1 :c 3)       ==> (1 NIL 3)
  4. (foo)                 ==> (NIL NIL NIL)

As these examples show, the value of the variables a, b, and c are bound to the values that follow the corresponding keyword. And if a particular keyword isn’t present in the call, the corresponding variable is set to **NIL**. I’m glossing over a bunch of details of how keyword parameters are specified and how they relate to other kinds of parameters, but you need to know one more detail.

Normally if a function is called with no argument for a particular keyword parameter, the parameter will have the value **NIL**. However, sometimes you’ll want to be able to distinguish between a **NIL** that was explicitly passed as the argument to a keyword parameter and the default value **NIL**. To allow this, when you specify a keyword parameter you can replace the simple name with a list consisting of the name of the parameter, a default value, and another parameter name, called a supplied-p parameter. The supplied-p parameter will be set to true or false depending on whether an argument was actually passed for that keyword parameter in a particular call to the function. Here’s a version of foo that uses this feature:

  1. (defun foo (&key a (b 20) (c 30 c-p)) (list a b c c-p))

Now the same calls from earlier yield these results:

  1. (foo :a 1 :b 2 :c 3)  ==> (1 2 3 T)
  2. (foo :c 3 :b 2 :a 1)  ==> (1 2 3 T)
  3. (foo :a 1 :c 3)       ==> (1 20 3 T)
  4. (foo)                 ==> (NIL 20 30 NIL)

The general selector-function generator, which you can call where for reasons that will soon become apparent if you’re familiar with SQL databases, is a function that takes four keyword parameters corresponding to the fields in our CD records and generates a selector function that selects any CDs that match all the values given to where. For instance, it will let you say things like this:

  1. (select (where :artist "Dixie Chicks"))

or this:

  1. (select (where :rating 10 :ripped nil))

The function looks like this:

  1. (defun where (&key title artist rating (ripped nil ripped-p))
  2.   #'(lambda (cd)
  3.       (and
  4.        (if title    (equal (getf cd :title)  title)  t)
  5.        (if artist   (equal (getf cd :artist) artist) t)
  6.        (if rating   (equal (getf cd :rating) rating) t)
  7.        (if ripped-p (equal (getf cd :ripped) ripped) t))))

This function returns an anonymous function that returns the logical AND of one clause per field in our CD records. Each clause checks if the appropriate argument was passed in and then either compares it to the value in the corresponding field in the CD record or returns t, Lisp’s version of truth, if the parameter wasn’t passed in. Thus, the selector function will return t only for CDs that match all the arguments passed to where.7 Note that you need to use a three-item list to specify the keyword parameter ripped because you need to know whether the caller actually passed :ripped nil, meaning, “Select CDs whose ripped field is nil,” or whether they left out :ripped altogether, meaning “I don’t care what the value of the ripped field is.”