Книга: Practical Common Lisp

Other Database Operations

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.

Оглавление книги