Книга: Practical Common Lisp
Inserting Values
Inserting Values
Now you're ready to define your first table operation, insert-row
, which takes a plist of names and values and a table and adds a row to the table containing the given values. The bulk of the work is done in a helper function, normalize-row
, that builds a plist with a defaulted, normalized value for each column, using the values from names-and-values
if available and the default-value
for the column if not.
(defun insert-row (names-and-values table)
(vector-push-extend (normalize-row names-and-values (schema table)) (rows table)))
(defun normalize-row (names-and-values schema)
(loop
for column in schema
for name = (name column)
for value = (or (getf names-and-values name) (default-value column))
collect name
collect (normalize-for-column value column)))
It's worth defining a separate helper function, normalize-for-column
, that takes a value and a column
object and returns the normalized value because you'll need to perform the same normalization on query arguments.
(defun normalize-for-column (value column)
(funcall (value-normalizer column) value column))
Now you're ready to combine this database code with code from previous chapters to build a database of data extracted from MP3 files. You can define a function, file->row
, that uses read-id3
from the ID3v2 library to extract an ID3 tag from a file and turns it into a plist that you can pass to insert-row
.
(defun file->row (file)
(let ((id3 (read-id3 file)))
(list
:file (namestring (truename file))
:genre (translated-genre id3)
:artist (artist id3)
:album (album id3)
:song (song id3)
:track (parse-track (track id3))
:year (parse-year (year id3))
:id3-size (size id3))))
You don't have to worry about normalizing the values since insert-row
takes care of that for you. You do, however, have to convert the string values returned by the track
and year
into numbers. The track number in an ID3 tag is sometimes stored as the ASCII representation of the track number and sometimes as a number followed by a slash followed by the total number of tracks on the album. Since you care only about the actual track number, you should use the :end
argument to PARSE-INTEGER
to specify that it should parse only up to the slash, if any.[294]
(defun parse-track (track)
(when track (parse-integer track :end (position #/ track))))
(defun parse-year (year)
(when year (parse-integer year)))
Finally, you can put all these functions together, along with walk-directory
from the portable pathnames library and mp3-p
from the ID3v2 library, to define a function that loads an MP3 database with data extracted from all the MP3 files it can find under a given directory.
(defun load-database (dir db)
(let ((count 0))
(walk-directory
dir
#'(lambda (file)
(princ #.)
(incf count)
(insert-row (file->row file) db))
:test #'mp3-p)
(format t "~&Loaded ~d files into database." count)))