Add add-command

This command adds a new bookmark to the database and associates any
given tags with the bookmark, adding them, if necessary, as well.

1 files changed, 36 insertions, 1 deletions

diff --git a/clark.lisp b/clark.lisp
index b7bb4ff..f2c2bc7 100644
--- a/clark.lisp
+++ b/clark.lisp
@@ -8,13 +8,28 @@
 (defconstant *version* "0.1.0"
   "Clark's version.")
 
+(defun add-command (args)
+  "Add a new bookmark to the database."
+  (with-transaction *db*
+    (destructuring-bind (url name description &rest tags) args
+      (insert-bookmark url name description)
+      (add-tags tags))))
+
+(defun add-tags (tags)
+  "Add tags to the bookmark_tag table and possibly to tag."
+  (let ((bookmark-id (last-insert-rowid *db*)))
+    (map nil (lambda (tag)
+               (let ((tag-id (handler-case (insert-tag tag)
+                               (sqlite-error () (get-tag-id tag)))))
+                 (insert-bookmark-tag bookmark-id tag-id))) tags)))
+
 (defun check-db (name)
   "Connect to the database, possibly creating it."
   (let ((db-exists (probe-file name)))
     (setf *db* (connect name))
     (unless db-exists
       (execute-non-query *db* "CREATE TABLE bookmark (url VARCHAR(255) UNIQUE, date INTEGER, name VARCHAR(255), description TEXT)")
-      (execute-non-query *db* "CREATE TABLE tag (name VARCHAR(255) UNIQUE);")
+      (execute-non-query *db* "CREATE TABLE tag (name VARCHAR(255) UNIQUE)")
       (execute-non-query *db* "CREATE TABLE bookmark_tag (bookmark_id INTEGER REFERENCES bookmark(rowid), tag_id INTEGER REFERENCES tag(rowid), PRIMARY KEY (bookmark_id, tag_id))"))))
 
 (defun get-bookmarks ()
@@ -29,6 +44,10 @@ The result contains the url and the name of the bookmark."
                      (statement-column-value statement 1))
        finally (finalize-statement statement))))
 
+(defun get-tag-id (name)
+  "Get the rowid of tag NAME."
+  (execute-single *db* "SELECT rowid FROM tag WHERE name = ?" name))
+
 (defun help-command (args)
   "Show a help message."
   (format t (concatenate
@@ -37,9 +56,25 @@ The result contains the url and the name of the bookmark."
              "~%"
              "Possible commands:~%"
              "~%"
+             "add      Add a bookmark to the database~%"
              "help     Display this help and exit~%"
              "version  Output version information and exit~%")))
 
+(defun insert-bookmark (url name description)
+  "Insert URL, NAME and DESCRIPTION into the bookmark table."
+  (execute-non-query *db* "INSERT INTO bookmark VALUES (?, ?, ?, ?)"
+                     url name (get-universal-time) description))
+
+(defun insert-bookmark-tag (bookmark-id tag-id)
+  "Insert BOOKMARK-ID and TAG-ID into the bookmark_tag table."
+  (execute-non-query *db* "INSERT INTO bookmark_tag VALUES (?, ?)"
+                     bookmark-id tag-id))
+
+(defun insert-tag (name)
+  "Insert tag NAME into the database and return its rowid."
+    (execute-non-query *db* "INSERT INTO tag VALUES (?)" name)
+    (last-insert-rowid *db*))
+
 (defun make-command-name (base)
   "Turn BASE into the name of a possible command."
   (intern (concatenate 'string (string-upcase base) "-COMMAND")