From 1a7496b5e74776f88ff7867de10d89f476d85d21 Mon Sep 17 00:00:00 2001 From: Thanos Apollo Date: Sun, 15 Dec 2024 02:04:04 +0200 Subject: 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. --- gnosis.el | 166 +++++++++++++++++++++++++++++++++----------------------------- 1 file 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 ;;;; ;;;;;;;;;;;;;;;;;;;;; -- cgit v1.2.3