gnosis-db: Update schemata to version 4.

* Refactor schemata structure
* Add tags & links schemata
  * tags schema holds all unique tags
  * links hold all unique links from gnosis id to a link.

1 files changed, 88 insertions, 78 deletions

diff --git a/gnosis.el b/gnosis.el
index 028e5f1..bbe2d6c 100644
--- a/gnosis.el
+++ b/gnosis.el
@@ -2359,6 +2359,22 @@ Defaults to current date."
 	 (new-tags (append current-tags (list tag))))
     (gnosis-update 'notes `(= tags ',new-tags) `(= id ,id))))
 
+(defun gnosis-search-note (&optional query)
+  "Search for note QUERY.
+
+Return note ids for notes that match QUERY."
+  (cl-assert (or (stringp query) (eq query nil)))
+  (let* ((query (or query (read-string "Search for note: ")))
+         (words (split-string query))
+         (clause-main `(and ,@(mapcar (lambda (word)
+					`(like main ,(format "%%%s%%" word)))
+                                      words)))
+	 (clause-answer `(and ,@(mapcar (lambda (word)
+					  `(like answer ,(format "%%%s%%" word)))
+					words))))
+    (append (gnosis-select 'id 'notes clause-main t)
+	    (gnosis-select 'id 'notes clause-answer t))))
+
 (cl-defun gnosis-export-note (id &optional (export-for-deck nil))
   "Export fields for note with value of id ID.
 
@@ -2403,67 +2419,74 @@ to improve readability."
 		       (t (format "\n%s %s" (symbol-name field) (prin1-to-string value))))))))
 
 ;;; Database Schemas
-(defvar gnosis-db-schema-decks '([(id integer :primary-key :autoincrement)
-				  (name text :not-null)]))
-
-(defvar gnosis-db-schema-notes '([(id integer :primary-key :autoincrement)
-				  (type text :not-null)
-				  (main text :not-null)
-				  (options text :not-null)
-				  (answer text :not-null)
-				  (tags text :default untagged)
-				  (deck-id integer :not-null)]
-				 (:foreign-key [deck-id] :references decks [id]
-					       :on-delete :cascade)))
-
-(defvar gnosis-db-schema-review '([(id integer :primary-key :not-null) ;; note-id
-				   (gnosis integer :not-null)
-				   (amnesia integer :not-null)]
-				  (:foreign-key [id] :references notes [id]
-						:on-delete :cascade)))
-
-(defvar gnosis-db-schema-review-log '([(id integer :primary-key :not-null) ;; note-id
-				       (last-rev integer :not-null)  ;; Last review date
-				       (next-rev integer :not-null)  ;; Next review date
-				       (c-success integer :not-null) ;; Consecutive successful reviews
-				       (t-success integer :not-null) ;; Total successful reviews
-				       (c-fails integer :not-null)   ;; Consecutive failed reviewss
-				       (t-fails integer :not-null)   ;; Total failed reviews
-				       (suspend integer :not-null)   ;; Binary value, 1=suspended
-				       (n integer :not-null)]        ;; Number of reviews
-				      (:foreign-key [id] :references notes [id]
-						    :on-delete :cascade)))
-
-(defvar gnosis-db-schema-activity-log '([(date text :not-null)
-					 (reviewed-total integer :not-null)
-					 (reviewed-new integer :not-null)]))
-
-(defvar gnosis-db-schema-extras '([(id integer :primary-key :not-null)
-				   (extra-notes string)
-				   (images string)
-				   ;; Extra image path to show after review
-				   (extra-image string)]
-				  ;; Note that the value of the images
-				  ;; above is PATH inside
-				  ;; `gnosis-images-dir'
-				  (:foreign-key [id] :references notes [id]
-						:on-delete :cascade)))
-
-(defun gnosis-search-note (&optional query)
-  "Search for note QUERY.
-
-Return note ids for notes that match QUERY."
-  (cl-assert (or (stringp query) (eq query nil)))
-  (let* ((query (or query (read-string "Search for note: ")))
-         (words (split-string query))
-         (clause-main `(and ,@(mapcar (lambda (word)
-					`(like main ,(format "%%%s%%" word)))
-                                      words)))
-	 (clause-answer `(and ,@(mapcar (lambda (word)
-					  `(like answer ,(format "%%%s%%" word)))
-					words))))
-    (append (gnosis-select 'id 'notes clause-main t)
-	    (gnosis-select 'id 'notes clause-answer t))))
+(defconst gnosis-db--schemata
+  '((decks
+     ([(id integer :primary-key :autoincrement)
+       (name text :not-null)]))
+    (notes
+     ([(id integer :primary-key :autoincrement)
+       (type text :not-null)
+       (main text :not-null)
+       (options text :not-null)
+       (answer text :not-null)
+       (tags text :default untagged)
+       (deck-id integer :not-null)]
+      (:foreign-key [deck-id] :references decks [id]
+		    :on-delete :cascade)))
+    (review
+     ([(id integer :primary-key :not-null) ;; note-id
+       (gnosis integer :not-null)
+       (amnesia integer :not-null)]
+      (:foreign-key [id] :references notes [id]
+		    :on-delete :cascade)))
+    (review-log
+     ([(id integer :primary-key :not-null) ;; note-id
+       (last-rev integer :not-null)  ;; Last review date
+       (next-rev integer :not-null)  ;; Next review date
+       (c-success integer :not-null) ;; Consecutive successful reviews
+       (t-success integer :not-null) ;; Total successful reviews
+       (c-fails integer :not-null)   ;; Consecutive failed reviewss
+       (t-fails integer :not-null)   ;; Total failed reviews
+       (suspend integer :not-null)   ;; Binary value, 1=suspended
+       (n integer :not-null)]        ;; Number of reviews
+      (:foreign-key [id] :references notes [id]
+		    :on-delete :cascade)))
+    (activity-log
+     ([(date text :not-null)
+       (reviewed-total integer :not-null)
+       (reviewed-new integer :not-null)]))
+    (extras
+     ([(id integer :primary-key :not-null)
+       (extra-notes string)
+       (images string)
+       ;; Extra image path to show after review
+       (extra-image string)]
+      ;; Note that the value of the images
+      ;; above is PATH inside
+      ;; `gnosis-images-dir'
+      (:foreign-key [id] :references notes [id]
+		    :on-delete :cascade)))
+    (tags
+     ([(tag text :primary-key)]
+      (:unique [tag])))
+    (links
+     ([(id text)
+       (node text)]
+      (:foreign-key [id] :references notes [id] :on-delete :cascade)
+      (:unique [id node])))))
+
+(defun gnosis-db-update-v4 ()
+  "Update to databse version v4.
+
+Add tags & links tables"
+  ;; TODO: Add links
+  (let ((tags (gnosis-get-tags--unique)))
+    (pcase-dolist (`(,table ,schema) (seq-filter (lambda (schema)
+						   (member (car schema) '(tags links)))
+						 gnosis-db--schemata))
+      (emacsql gnosis-db [:create-table $i1 $S2] table schema))
+    (cl-loop for tag in tags
+	     do (gnosis--insert-into 'tags `[,tag]))))
 
 (defun gnosis-db-update-v2 ()
   "Update to first gnosis-db version."
@@ -2501,28 +2524,15 @@ Return note ids for notes that match QUERY."
     (unless (length> (emacsql gnosis-db [:select name :from sqlite-master :where (= type table)])
 		     3)
       (emacsql-with-transaction gnosis-db
-	;; Enable foreign keys
-	(emacsql gnosis-db [:pragma (= foreign-keys 1)])
-	;; Gnosis version
-	(emacsql gnosis-db [:pragma (= user-version gnosis-db-version)])
-	;; Create decks table
-	(gnosis--create-table 'decks gnosis-db-schema-decks)
-	;; Create notes table
-	(gnosis--create-table 'notes gnosis-db-schema-notes)
-	;; Create review table
-	(gnosis--create-table 'review gnosis-db-schema-review)
-	;; Create review-log table
-	(gnosis--create-table 'review-log gnosis-db-schema-review-log)
-	;; Create extras table
-	(gnosis--create-table 'extras gnosis-db-schema-extras)
-	;; Create activity-log table
-	(gnosis--create-table 'activity-log gnosis-db-schema-activity-log)))
+	(pcase-dolist (`(,table ,schema) gnosis-db--schemata)
+	  (emacsql gnosis-db [:create-table $i1 $S2] table schema))
+        (emacsql gnosis-db [:pragma (= user-version org-gnosis-db-version)])))
     ;; Update database schema for version
+    ;; TODO: Adjust for new version
     (cond ((= gnosis-curr-version 2)
 	   (gnosis-db-update-v3)))))
 
 (gnosis-db-init)
-
 ;;;; Gnosis Demo ;;;;
 ;;;;;;;;;;;;;;;;;;;;;