Updating Existing Records—Another Use for WHERE

Now that you’ve got nice generalized select and where functions, you’re in a good position to write the next feature that every database needs—a way to update particular records. In SQL the update command is used to update a set of records matching a particular where clause. That seems like a good model, especially since you’ve already got a where-clause generator. In fact, the update function is mostly just the application of a few ideas you’ve already seen: using a passed-in selector function to choose the records to update and using keyword arguments to specify the values to change. The main new bit is the use of a function **MAPCAR** that maps over a list, *db* in this case, and returns a new list containing the results of calling a function on each item in the original list.

(defun update (selector-fn &key title artist rating (ripped nil ripped-p))
  (setf *db*
        (mapcar
         #'(lambda (row)
             (when (funcall selector-fn row)
               (if title    (setf (getf row :title) title))
               (if artist   (setf (getf row :artist) artist))
               (if rating   (setf (getf row :rating) rating))
               (if ripped-p (setf (getf row :ripped) ripped)))
             row) *db*)))

One other new bit here is the use of **SETF** on a complex form such as (getf row :title). I’ll discuss **SETF** in greater detail in Chapter 6, but for now you just need to know that it’s a general assignment operator that can be used to assign lots of “places” other than just variables. (It’s a coincidence that **SETF** and **GETF** have such similar names—they don’t have any special relationship.) For now it’s enough to know that after (setf (getf row :title) title), the plist referenced by row will have the value of the variable title following the property name :title. With this update function if you decide that you really dig the Dixie Chicks and that all their albums should go to 11, you can evaluate the following form:

CL-USER> (update (where :artist "Dixie Chicks") :rating 11)
NIL

And it is so.

CL-USER> (select (where :artist "Dixie Chicks"))
((:TITLE "Home" :ARTIST "Dixie Chicks" :RATING 11 :RIPPED T)
 (:TITLE "Fly" :ARTIST "Dixie Chicks" :RATING 11 :RIPPED T))

You can even more easily add a function to delete rows from the database.

(defun delete-rows (selector-fn)
  (setf *db* (remove-if selector-fn *db*)))

The function **REMOVE-IF** is the complement of **REMOVE-IF-NOT**; it returns a list with all the elements that do match the predicate removed. Like **REMOVE-IF-NOT**, it doesn’t actually affect the list it’s passed but by saving the result back into *db*, delete-rows8 actually changes the contents of the database.9