From 7fd20d720daef154214fdc49577ba73b3db1790b Mon Sep 17 00:00:00 2001 From: Tom Willemse Date: Tue, 4 Jun 2013 01:12:38 +0200 Subject: Move most queries to queries.lisp --- lisp/clark.lisp | 74 +++++---------------------------------- lisp/queries.lisp | 103 ++++++++++++++++++++++++++++++++++++++++++++++++------ 2 files changed, 100 insertions(+), 77 deletions(-) diff --git a/lisp/clark.lisp b/lisp/clark.lisp index d9477e8..64cf477 100644 --- a/lisp/clark.lisp +++ b/lisp/clark.lisp @@ -87,9 +87,7 @@ the help command." "Remove all tags from the bookmark URL." (when url-or-id (if (integerp url-or-id) - (execute-non-query - *db* (sql delete from "bookmark_tag" where "bookmark_id" = ?) - url-or-id) + (delete-tags url-or-id) (clear-tags (get-bookmark-id url-or-id))))) (defun ensure-db-exists (name) @@ -97,30 +95,9 @@ the help command." (let ((db-exists (probe-file name))) (setf *db* (connect name)) (unless db-exists - (execute-non-query - *db* (sql create table "bookmark" ("url" varchar (255) unique\, - "date" integer\, - "name" varchar (255)\, - "description" text))) - (execute-non-query - *db* (sql create table "tag" ("name" varchar (255) unique))) - (execute-non-query - *db* (sql 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 () - "Get a list of all bookmarks. - -The result contains the url, name and the description of the bookmark." - (execute-to-list - *db* (sql select "url, name, description" from "bookmark"))) - -(defun get-bookmark-id (url) - "Get the id of the bookmark for URL." - (execute-single - *db* (sql select "rowid" from "bookmark" where "url" = ?) url)) + (create-table-bookmark) + (create-table-tag) + (create-table-bookmark_tag)))) (defun get-db-location () "Get the location of the database." @@ -142,11 +119,6 @@ The result contains the url, name and the description of the bookmark." (unless xdg "/.config") '("/clark/rc.lisp"))))) -(defun get-tag-id (name) - "Get the rowid of tag NAME." - (execute-single - *db* (sql select "rowid" from "tag" where "name" = ?) name)) - (defun help-message () (format t (concatenate 'string @@ -167,23 +139,6 @@ The result contains the url, name and the description of the bookmark." (concatenate 'string "Use `clark help ' to get more " "information on a command."))) -(defun insert-bookmark (url name description) - "Insert URL, NAME and DESCRIPTION into the bookmark table." - (execute-non-query - *db* (sql insert into "bookmark" values (?\, ?\, ?\, ?)) - url (get-universal-time) name description)) - -(defun insert-bookmark-tag (bookmark-id tag-id) - "Insert BOOKMARK-ID and TAG-ID into the bookmark_tag table." - (execute-non-query - *db* (sql 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* (sql insert into "tag" values (?)) name) - (last-insert-rowid *db*)) - (defun load-db () "Load the database." (let ((db-location (get-db-location))) @@ -223,7 +178,7 @@ The executable name should already have been removed." (signal err)))) (with-error-and-help 1 "help" "Unknown command: ~A~%" (car args)))) - (map nil #'print-bookmark (get-bookmarks)))) + (map nil #'print-bookmark (bookmark-list)))) (defun print-bookmark (bm) "Print information about bookmark BM. @@ -274,10 +229,7 @@ option will replace the previous value for that part." Check if URL exists in the database. Prints `yes' when found and `no' otherwise." - (format - t "~:[no~;yes~]~%" - (execute-single - *db* (sql select "rowid" from "bookmark" where "url" = ?) url))) + (format t "~:[no~;yes~]~%" (bookmark-exists-p url))) (defcommand help (&optional command) "Show help message." @@ -301,8 +253,7 @@ otherwise." Remove URL from the database." (clear-tags url) - (execute-non-query - *db* (sql delete from "bookmark" where "url" = ?) url)) + (delete-bookmark url)) (defcommand search (str) "Search through bookmarks." @@ -310,16 +261,7 @@ Remove URL from the database." Search the database for STR. Matches are made for substrings of a bookmark's name or an exact match for a tag." - (map nil #'print-bookmark - (execute-to-list - *db* (sql select "url, name, description" - from "bookmark" - where "name" like ? - or ? in (select "name" - from "tag" - join "bookmark_tag" on ("tag_id = tag.rowid") - where "bookmark_id" = "bookmark.rowid")) - (format nil "%~A%" str) str))) + (map nil #'print-bookmark (bookmark-search str))) (defcommand set-tags (url &rest tags) "Set a bookmark's tags." diff --git a/lisp/queries.lisp b/lisp/queries.lisp index 1df24c5..4651ed6 100644 --- a/lisp/queries.lisp +++ b/lisp/queries.lisp @@ -19,12 +19,92 @@ (in-package :org.ryuslash.clark) -(defun url-list-no-tag () - "Get a list of all URLs stored." - (mapcar #'car - (execute-to-list - *db* (sql select "url" - from "bookmark")))) +(defun bookmark-exists-p (url) + "Check if URL can be found in the database." + (execute-single + *db* (sql select "rowid" from "bookmark" where "url" = ?) url)) + +(defun bookmark-list () + "Get a list of all stored bookmarks." + (execute-to-list + *db* (sql select "url, name, description" from "bookmark"))) + +(defun bookmark-search (name-or-tag) + "Get bookmarks by NAME-OR-TAG." + (execute-to-list + *db* (sql select "url, name, description" + from "bookmark" + where "name" like ? + or ? in (select "name" + from "tag" + join "bookmark_tag" on ("tag_id = tag.rowid") + where "bookmark_id" = "bookmark.rowid")) + (format nil "%~A%" name-or-tag) name-or-tag)) + +(defun create-table-bookmark () + "Create the bookmark table." + (execute-non-query + *db* (sql create table "bookmark" + ("url" varchar (255) unique\, + "date" integer\, + "name" varchar (255)\, + "description" text)))) + +(defun create-table-bookmark_tag () + "Create the bookmark_tag table." + (execute-non-query + *db* (sql create table "bookmark_tag" + ("bookmark_id" integer references "bookmark(rowid)"\, + "tag_id" integer references "tag(rowid)"\, + primary key ("bookmark_id"\, "tag_id"))))) + +(defun create-table-tag () + "Create the tag table." + (execute-non-query + *db* (sql create table "tag" ("name" varchar (255) unique)))) + +(defun delete-bookmark (url) + "Delete URL from collection." + (execute-non-query + *db* (sql delete from "bookmark" where "url" = ?) url)) + +(defun delete-tags (id) + "Clear the tags for bookmark with id ID." + (execute-non-query *db* (sql delete from "bookmark_tag" + where "bookmark_id" = ?) id)) + +(defun get-bookmark-id (url) + "Get the id of the bookmark for URL." + (execute-single + *db* (sql select "rowid" from "bookmark" where "url" = ?) url)) + +(defun get-tag-id (name) + "Get the rowid of tag NAME." + (execute-single + *db* (sql select "rowid" from "tag" where "name" = ?) name)) + +(defun insert-bookmark (url name description) + "Insert URL, NAME and DESCRIPTION into the bookmark table." + (execute-non-query + *db* (sql insert into "bookmark" values (?\, ?\, ?\, ?)) + url (get-universal-time) name description)) + +(defun insert-bookmark-tag (bookmark-id tag-id) + "Insert BOOKMARK-ID and TAG-ID into the bookmark_tag table." + (execute-non-query + *db* (sql 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* (sql insert into "tag" values (?)) name) + (last-insert-rowid *db*)) + +(defun url-list (&optional tag) + "Get a list of URLs, possibly with tag TAG." + (if tag + (url-list-for-tag tag) + (url-list-no-tag))) (defun url-list-for-tag (tag) "Get a list of all the stored URLs with tag TAG." @@ -39,8 +119,9 @@ where "bookmark_id" = "bookmark.rowid")) tag))) -(defun url-list (&optional tag) - "Get a list of URLs, possibly with tag TAG." - (if tag - (url-list-for-tag tag) - (url-list-no-tag))) +(defun url-list-no-tag () + "Get a list of all URLs stored." + (mapcar #'car + (execute-to-list + *db* (sql select "url" + from "bookmark")))) -- cgit v1.2.3-54-g00ecf