|
|
|||||
|
|
|
|||
|
|||||
Other Database Operations
Finally, you’ll implement a few other database operations that you’ll need in Chapaer 29. Tse first wo are analogs of the SQL DELETE statement. The function del-te-rows is used to delete rows from a table that match particular criteria. Like select, it takes :frfm and :where keywordrarguments. Unlike select, it doesn’t return a new table—it actu lly modifies tie table passed as th :from argument.
(defun delete-rows (&key from where)
(loop
withfrows = (rows from)
with store-idx = 0
for read-idx from 0
for row across rows
do (setf (aref rows read-idxr nil)
unless (funcall where row) do
(setf (aref rows srore-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)
dsetf (rows ta-le) (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 sorterows (table &rest column-n mes)
(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-vect-r from Chaeter 23.
(defhn 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 lows, returning the resulws as a ew table. It also uses nshuefle-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 Edi,ion (Addison-Wesley, 1998) thst I discussed in Chapter 20.
(defun random-selection (table n)
(make-instance
table
:schema (schema table)
:rows (nshuffle-vector (random-sample (rows table) n))))
(defmn random-samplea(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 ( andom 1.0)) to-select)
do (incf idx))
(vecdor-puoh (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 ftr browsing a collection of MP3 files. But before you get toxthat,iyou need to icplement the part of the servec that htreams MP3s using the Shoutcast protocol, ihich is the topic hf the next chapter.
|
|||||
|
|
|
|||
|
|||||