Building and maintaining a database in Emacs?

Question

I was reading the Simple Database section of Peter Siebel's book Practical Common Lisp with the idea of maintaining a small database of around 50,000 records. I thought doing this in Emacs might be an interesting, and useful, exercise. Emacs Lisp is somewhat compatible with CL except for a few notable differences. The format function used in the above example being one major difference.

Here's the code that contains everything needed to construct, save, and load the database in CL. Can this be modified to work well in Emacs? I omitted the select and where functions but I'd like to include them. Maybe there is a better Emacs way of building and maintaining a database? Personally, I'm using this as an exercise to learn CL and solve an existing problem.

;; Simple Common Lisp database
;; http://www.gigamonkeys.com/book/practical-a-simple-database.html
;;
(defvar *db* nil)

(defun make-cd (title artist rating ripped)
  (list :title title :artist artist :rating rating :ripped ripped))

(defun add-record (cd) (push cd *db*))

(defun dump-db ()
  (dolist (cd *db*)
    (format t "~{~a:~10t~a~%~}~%" cd)))

(defun save-db (filename)
  (with-open-file (out filename
                   :direction :output
                   :if-exists :supersede)
    (with-standard-io-syntax
      (print *db* out))))

(defun load-db (filename)
  (with-open-file (in filename)
    (with-standard-io-syntax
      (setf *db* (read in)))))
; ===
;
; Add some records
;
(add-record (make-cd "Roses" "Kathy Mattea" 7 t))
(add-record (make-cd "Fly" "Dixie Chicks" 8 t))
(add-record (make-cd "Home" "Dixie Chicks" 9 t))

; (dump-db)
; (save-db "cd.db")
; (load-db "cd.db")

Answer 1

When I tried to write a ebook library for Emacs, I stored the records in a list, saving it to disk from time to time. When the list length exceeded about five thousand records, the performance suffered.

Here are some functions from the code:

(defun bread-library-load-db ()
 "Loads the list of books from disk file to the variable bread-library-db"
      (if (file-exists-p bread-library-file)
          (with-temp-buffer
        (insert-file-contents bread-library-file)
        (setq bread-library-db (read (current-buffer))))
        (setq bread-library-db '())))

(defun bread-library-add-book (file)
  "Attempts  to   get  metadata  from  file,   then  prompts  for
confirmation (or  modification) of these metadata,  then adds the
book to the database and saves it.  Intended use: from dired."
  (if (assoc file bread-library-db)
      (error "File is already in the database")
    (progn
      (let ((metadata (bread-get-metadata file)))
    (let ((filename (nth 0 metadata))
          (author (read-from-minibuffer 
            "Author: "
            (nth 1 metadata)))
          (title (read-from-minibuffer 
           "Title: "
           (nth 2 metadata)))
          (genre (read-from-minibuffer "Genre: " (nth 3 metadata)))
          (tags (read-from-minibuffer "Tags (separated and surrounded by colons): " ":"))
          (desc (nth 4 metadata)))
      (setq bread-library-db (cons 
         (list filename author title tags "TOREAD" genre nil desc)
         bread-library-db))))
      (bread-library-save-db bread-library-db))))

(defun bread-library-save-db (db)
    "Save the library database to a file."
    (message "Saving Bread library database...")
    (with-temp-buffer
      (insert "; 1.path 2.author 3.title 4.tags 5.state 6.genre 7.priority 8.description")
      (print db (current-buffer))
      (write-file bread-library-file))
    (message "Saving Bread library database...done"))

Answer 2

Here's a tutorial on using Emacs as a front-end for a SQLite DB: Emacs and SQLite Tutorial

It should give you an idea on how to get data into and out of the DB.

Answer 3

Here's my solution:

(defvar *db* nil)

(setq *db* ())

(defun make-cd (title artist rating ripped)
  (list :title title :artist artist :rating rating :ripped ripped))

(defun add-record (cd) (push cd *db*))

(defun init ()
  (progn
    (add-record (make-cd "Roses" "Kathy Mattea" 7 t))
    (add-record (make-cd "Fly" "Dixie Chicks" 8 t))
    (add-record (make-cd "Home" "Dixie Chicks" 9 t))
    ))

(defun save-db (filename)
  (with-temp-buffer
    (print *db* (current-buffer))
    (write-file filename))
  (message "Saving database...done")
  )

(defun load-db (filename)
  (with-temp-buffer
    (insert-file-contents filename)
        (setq *db* (read (current-buffer)))))

(defun dump-db ()
  (dolist (cd *db*)
    (print cd)))


;; Test in M-x lisp-interaction-mode
;;(init)
;;(save-db "cd.db")
;*db*
;(add-record (make-cd "Born To Run" "Bruce Springsteen" 10 t))
;(add-record (make-cd "The River" "Bruce Springsteen" 10 t))
;(add-record (make-cd "Nebraska" "Bruce Springsteen" 10 t))
;(add-record (make-cd "Human Touch" "Bruce Springsteen" 10 nil))
;;(save-db "cd.db")
;(setq *db* ())
;;(load-db "cd.db")
;*db*