Other Database Operations

Finally, you’ll implement a few other database operations that you’ll need in Chapter 29. The first two are analogs of the SQL DELETE statement. The function delete-rows is used to delete rows from a table that match particular criteria. Like select, it takes :from and :where keyword arguments. Unlike select, it doesn’t return a new table—it actually modifies the table passed as the :from argument.

(defun delete-rows (&key from where)
  (loop
     with rows = (rows from)
     with store-idx = 0
     for read-idx from 0
     for row across rows
     do (setf (aref rows read-idx) nil)
     unless (funcall where row) do
       (setf (aref rows store-idx) row)
       (incf store-idx)
     finally (setf (fill-pointer rows) store-idx)))

In the interest of efficiency, you might want to provide a separate function for deleting all the rows from a table.

(defun delete-all-rows (table)
  (setf (rows table) (make-rows *default-table-size*)))

The remaining table operations don’t really map to normal relational database operations but will be useful in the MP3 browser application. The first is a function to sort the rows of a table in place.

(defun sort-rows (table &rest column-names)
  (setf (rows table) (sort (rows table) (row-comparator column-names (schema table))))
  table)

On the flip side, in the MP3 browser application, you’ll need a function that shuffles a table’s rows in place using the function nshuffle-vector from Chapter 23.

(defun shuffle-table (table)
  (nshuffle-vector (rows table))
  table)

And finally, again for the purposes of the MP3 browser, you should provide a function that selects n random rows, returning the results as a new table. It also uses nshuffle-vector along with a version of random-sample based on Algorithm S from Donald Knuth’s The Art of Computer Programming, Volume 2: Seminumerical Algorithms, Third Edition (Addison-Wesley, 1998) that I discussed in Chapter 20.

(defun random-selection (table n)
  (make-instance
   'table
   :schema (schema table)
   :rows (nshuffle-vector (random-sample (rows table) n))))
(defun random-sample (vector n)
  "Based on Algorithm S from Knuth. TAOCP, vol. 2. p. 142"
  (loop with selected = (make-array n :fill-pointer 0)
     for idx from 0
     do
       (loop
          with to-select = (- n (length selected))
          for remaining = (- (length vector) idx)
          while (>= (* remaining (random 1.0)) to-select)
          do (incf idx))
       (vector-push (aref vector idx) selected)
     when (= (length selected) n) return selected))

With this code you’ll be ready, in Chapter 29, to build a Web interface for browsing a collection of MP3 files. But before you get to that, you need to implement the part of the server that streams MP3s using the Shoutcast protocol, which is the topic of the next chapter.

1The general theory behind interning objects is that if you’re going to compare a particular value many times, it’s worth it to pay the cost of interning it. The value-normalizer runs once when you insert a value into the table and, as you’ll see, once at the beginning of each query. Since a query can involve invoking the equality-predicate once per row in the table, the amortized cost of interning the values will quickly approach zero.

2As always, the first causality of concise exposition in programming books is proper error handling; in production code you’d probably want to define your own error type, such as the following, and signal it instead:

(error 'illegal-column-value :value value :column column)

Then you’d want to think about where you can add restarts that might be able to recover from this condition. And, finally, in any given application you could establish condition handlers that would choose from among those restarts.

3If any MP3 files have malformed data in the track and year frames, **PARSE-INTEGER** could signal an error. One way to deal with that is to pass **PARSE-INTEGER** the :junk-allowed argument of **T**, which will cause it to ignore any non-numeric junk following the number and to return **NIL** if no number can be found in the string. Or, if you want practice at using the condition system, you could define an error and signal it from these functions when the data is malformed and also establish a few restarts to allow these functions to recover.

4This query will also return all the songs performed by the Dixie Chicks. If you want to limit it to songs by artists other than the Dixie Chicks, you need a more complex :where function. Since the :where argument can be any function, it’s certainly possible; you could remove the Dixie Chicks’ own songs with this query:

(let* ((dixie-chicks (matching *mp3s* :artist "Dixie Chicks"))
       (same-song (in :song (select :columns :song :from *mp3s* :where dixie-chicks)))
       (query #'(lambda (row) (and (not (funcall dixie-chicks row)) (funcall same-song row)))))
  (select :columns '(:artist :song) :from *mp3s* :where query))

This obviously isn’t quite as convenient. If you were going to write an application that needed to do lots of complex queries, you might want to consider coming up with a more expressive query language.

5The version of **LOOP** implemented at M.I.T. before Common Lisp was standardized included a mechanism for extending the **LOOP** grammar to support iteration over new data structures. Some Common Lisp implementations that inherited their **LOOP** implementation from that code base may still support that facility, which would make do-rows and map-rows less necessary.