From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from mp12.migadu.com ([2001:41d0:8:6d80::]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits)) by ms5.migadu.com with LMTPS id rkYSH9qlQmNewAAAbAwnHQ (envelope-from ) for ; Sun, 09 Oct 2022 12:43:38 +0200 Received: from aspmx1.migadu.com ([2001:41d0:8:6d80::]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits)) by mp12.migadu.com with LMTPS id MFHPHdqlQmPqDQEAauVa8A (envelope-from ) for ; Sun, 09 Oct 2022 12:43:38 +0200 Received: from lists.gnu.org (lists.gnu.org [209.51.188.17]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (No client certificate requested) by aspmx1.migadu.com (Postfix) with ESMTPS id 12FAA24030 for ; Sun, 9 Oct 2022 12:43:37 +0200 (CEST) Received: from localhost ([::1]:44646 helo=lists1p.gnu.org) by lists.gnu.org with esmtp (Exim 4.90_1) (envelope-from ) id 1ohTmS-0001FO-62 for larch@yhetil.org; Sun, 09 Oct 2022 06:43:36 -0400 Received: from eggs.gnu.org ([2001:470:142:3::10]:54244) by lists.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.90_1) (envelope-from ) id 1ohTlS-0001Eu-9h for emacs-orgmode@gnu.org; Sun, 09 Oct 2022 06:42:34 -0400 Received: from stw1.rcdrun.com ([217.170.207.13]:39033) by eggs.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.90_1) (envelope-from ) id 1ohTlG-0007N9-T3 for emacs-orgmode@gnu.org; Sun, 09 Oct 2022 06:42:33 -0400 Received: from localhost ([::ffff:197.239.6.155]) (AUTH: PLAIN admin, TLS: TLS1.3,256bits,ECDHE_RSA_AES_256_GCM_SHA384) by stw1.rcdrun.com with ESMTPSA id 00000000000BBD14.000000006342A584.00000AAB; Sun, 09 Oct 2022 03:42:11 -0700 Date: Sun, 9 Oct 2022 13:40:16 +0300 From: Jean Louis To: =?utf-8?Q?S=C3=A9bastien?= Rey-Coyrehourcq Cc: Alan Schmitt , emacs-orgmode Subject: SQLite for contacts and relations to Org - Re: contact management in emacs Message-ID: Mail-Followup-To: =?utf-8?Q?S=C3=A9bastien?= Rey-Coyrehourcq , Alan Schmitt , emacs-orgmode References: <87lfb9bwff.fsf@m4x.org> <090c6ca8-62e1-edcd-d348-688281c4840d@univ-rouen.fr> MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Disposition: inline Content-Transfer-Encoding: 8bit In-Reply-To: <090c6ca8-62e1-edcd-d348-688281c4840d@univ-rouen.fr> User-Agent: Mutt/2.2.7+37 (a90f69b) (2022-09-02) Received-SPF: pass client-ip=217.170.207.13; envelope-from=bugs@gnu.support; helo=stw1.rcdrun.com X-Spam_score_int: -17 X-Spam_score: -1.8 X-Spam_bar: - X-Spam_report: (-1.8 / 5.0 requ) BAYES_00=-1.9, RCVD_IN_SBL=0.141, SPF_HELO_PASS=-0.001, SPF_PASS=-0.001, T_FILL_THIS_FORM_SHORT=0.01 autolearn=no autolearn_force=no X-Spam_action: no action X-BeenThere: emacs-orgmode@gnu.org X-Mailman-Version: 2.1.29 Precedence: list List-Id: "General discussions about Org-mode." List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: emacs-orgmode-bounces+larch=yhetil.org@gnu.org Sender: "Emacs-orgmode" X-Migadu-Flow: FLOW_IN X-Migadu-Country: US ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=yhetil.org; s=key1; t=1665312217; h=from:from:sender:sender:reply-to:subject:subject:date:date: message-id:message-id:to:to:cc:cc:mime-version:mime-version: content-type:content-type: content-transfer-encoding:content-transfer-encoding: in-reply-to:in-reply-to:references:references:list-id:list-help: list-unsubscribe:list-subscribe:list-post; bh=oMjy76cCZH0WViSctxjsy1SyW1iMNpy9a75KQAE2etk=; b=SmC4JfG5zKv9A0k5XhB8JlaiVobM1u37XxvWtxkNz1RSf2+D03cnxSKP1jvV579aAKteQ0 NlppxlNrG09r3MmLinKPak9i9YkQu9wqFiVq6LbxL31cqXj1pPQc2M3JcFJHK+P5PA6vpM ueSXQOi9I/5EODqNF7h0dO2Q4I48m9p58MemM6Gvklmt/Hp2v7Xy9cDpKKEoZS9x8mCJSk Y/gbLPriKpBy/JapXx2C06bJgV1i+CZeGtKtqqZL3x9KYzcvOeXolHxvtTc7IBl9wQR5kg 5yS+DXcgNOBHfTZRTqcNQDIMqkE5p7dkjX7Ye2U46qIv73cfS0SjqpOf4m12RQ== ARC-Seal: i=1; s=key1; d=yhetil.org; t=1665312217; a=rsa-sha256; cv=none; b=SMDSnkhZtF9jm0RYC78jebFdok2HqQRRm80JlFpGBHdl5RFmTeS8iwm1HYC7nw3QFr2/WT zOqUUnuIMPUmXCUFaKdL1FUFyRhJ5Rp8JD7P86qgP4HPnWRdbg0BTijQQZS70yI8g7p/9T evEBW2cXak7uX5hJFyT0HXNXhAlVg3k7CmOPQJK/LLJIZFlZRRnyGlrXwZE2Xh9iUwMyZe SLgtcEGIE6E9oIPn85zMBpwksgx0hvGyegGtE0n/bI5Sie+t6zZT0E7ElCuqeJ8gcmfhav m3PROYSXrMb30kzC1kCWmqNOrW5vyTUs77Y/LIe7imXxUCAXmIHR8Eb3wUTAVw== ARC-Authentication-Results: i=1; aspmx1.migadu.com; dkim=none; dmarc=none; spf=pass (aspmx1.migadu.com: domain of "emacs-orgmode-bounces+larch=yhetil.org@gnu.org" designates 209.51.188.17 as permitted sender) smtp.mailfrom="emacs-orgmode-bounces+larch=yhetil.org@gnu.org" X-Migadu-Spam-Score: -2.48 Authentication-Results: aspmx1.migadu.com; dkim=none; dmarc=none; spf=pass (aspmx1.migadu.com: domain of "emacs-orgmode-bounces+larch=yhetil.org@gnu.org" designates 209.51.188.17 as permitted sender) smtp.mailfrom="emacs-orgmode-bounces+larch=yhetil.org@gnu.org" X-Migadu-Queue-Id: 12FAA24030 X-Spam-Score: -2.48 X-Migadu-Scanner: scn0.migadu.com X-TUID: 3DSJ4rBHxqO/ * Sébastien Rey-Coyrehourcq [2022-09-09 19:12]: > Hi, > > After some search today on the web, like Alan i try to compare all > possibility to manage my contact + mail using emacs, mu4e, and org. I will describe you how to manage contacts with Emacs in the reverse example reverse of what org-contacts tries to do for you. Org contacts try to keep people in Org file. Emacs 29 has a built-in SQLite database which allows every Emacs user to keep information in ordered, structured manner in the relational database. For people who do not use latest development Emacs version, there are SQLite Emacs packages that allow immediate usage of file based single-user access database, see https://www.sqlite.org In Emacs 29, here is how I create the SQLite database, or open the existing one: (setq my-db (sqlite-open "~/my-people")) This is database handle: my-db ⇒ # This is how to create a table of `people' in the database. The structure of the table `people' is thus pretty much individual, one can record any kind of entries and be sure if the the entry is there or not. It does not allow vague guessing with text files like in Org or Hyrolo in Hyperbole. This is example table, one can build upon it. This one contains emails only. (sqlite-execute my-db "CREATE TABLE people1 (people_id INTEGER PRIMARY KEY, people_firstname TEXT, people_middlenames TEXT, people_name TEXT NOT NULL DEFAULT '>>>UNKNOWN<<<', people_email TEXT, people_description TEXT)") This is how to get all queries from the table: (sqlite-select my-db "SELECT * FROM people") ⇒ nil This is how to insert new entry programmatically: (sqlite-execute my-db "INSERT INTO people (people_firstname, people_name, people_email) VALUES (?, ?, ?) RETURNING people_id" '("Robert" "Weiner" "rsw@gnu.org")) Get information back from database, programmatically. Do with information what you wish. (sqlite-select my-db "SELECT * FROM people") ⇒ ((1 "Robert" nil "Weiner" "rsw@gnu.org" nil) (2 "Robert" nil "Weiner" "rsw@gnu.org" nil)) Delete everything in the database. (sqlite-execute my-db "DELETE FROM people") ⇒ 0 ;; Backup? Backup database by simple copy of the file "~/my-people" (sqlite-select my-db "SELECT * FROM people") ⇒ nil Make nicer function to add new people: (defun my-people-add () (interactive) (let* ((first-name (read-string "First name: ")) (middle-names (read-string "Middles names: ")) (name-or-last-name (let ((result)) (while (not result) (setq result (apply 'read-string '("Last name: ")))) result)) (email (read-string "Email: ")) (description (read-string "Description: "))) (sqlite-execute my-db "INSERT INTO people (people_firstname, people_middlenames, people_name, people_email, people_description) VALUES (?, ?, ?, ?, ?) RETURNING people_id" (list first-name middle-names name-or-last-name description)))) Run the function by using (my-people-add) or M-x my-people-add Verify that person was added: (sqlite-select my-db "SELECT * FROM people") ⇒ ((1 "Joe" "" "Doe" nil nil)) Entries are returned as list, get the first list entry: (caar (sqlite-select my-db "SELECT people_id FROM people WHERE people_id = 1")) ⇒ 1 My database structure is ALWAYS as so that for TABLE I always have primary key named TABLE_id. That principle allows to simplify things. Then I can make generic function to fetch from database DB, from TABLE and COLUMN by using ID: (defun sqlite-db-get-entry (table column id db) "Return value for the TABLE, COLUMN, ID from the database PG." (let* ((sql (format "SELECT %s FROM %s WHERE %s_id = %s" column table table id)) (value (caar (sqlite-select db sql)))) value)) (sqlite-db-get-entry "people" "people_id" 1 my-db) ⇒ 1 (sqlite-db-get-entry "people" "people_name" 1 my-db) ⇒ "Doe" (sqlite-db-get-entry "people" "people_email" 1 my-db) ⇒ "joe@example.com" Make it exportable to Org: (defun my-people-to-org-single (id) (let* ((first-name (sqlite-db-get-entry "people" "people_firstname" id my-db)) (middle-names (sqlite-db-get-entry "people" "people_middlenames" id my-db)) (last-name (sqlite-db-get-entry "people" "people_lastname" id my-db)) (email (sqlite-db-get-entry "people" "people_email" id my-db)) (description (sqlite-db-get-entry "people" "people_description" id my-db)) (heading (list first-name middle-names last-name)) (heading (mapcar (lambda (e) (cond ((seq-empty-p e) nil) (t e))) heading)) (heading (delq nil heading)) (heading (concat "* " (string-join heading " ") "\n\n")) (text (with-temp-buffer (when (and email (> (length email) 0)) (insert (concat "E-mail: " email "\n\n"))) (when (and description (> (length description) 0)) (insert description)) (buffer-string)))) (concat heading text "\n\n"))) (my-people-to-org-single 1) ⇒ "* Joseph Joe E-mail: joe@example.com This is just a sample description " What if we have multiple people like "Joe Doe"? Then each will have its unique ID by which we have to be able to choose them. One approach is to add on the name a bracketed ID number, like: "Joe Doe [1]" "Joe Doe [2]" Another approach is to add various other attributes to make the entry distinguishable in completions: "Joe Doe, Ukraine [1]" "Joe Doe, Russia [2]" "Joe Doe, USA [3]" "Joe Doe, Emacs Users [4]" To cut the ID from the bracket on the end, following function does it: (defun rcd-get-bracketed-id-end (s) "Return the ID number in string S from within first brackets on its end. For example it would return 123 from `Some string [123]'" (let* ((match (string-match "\\[\\([[:digit:]]*\\)\\][[:space:]]*$" s))) (when match (string-to-number (substring-no-properties s (match-beginning 1) (match-end 1)))))) Let us make generic function that expects bracketed ID and completes by SQL. This function could be better of course, this is example: (defun my-completing-id-by-sql-end (prompt sql db) "Return the ID as by finding [ID:123] on the end. Argument PROMPT will be displayed to user. Argument SQL is supplied query. Argument PG is database handle." (let* ((selection (sqlite-select db sql)) (choice (completing-read prompt selection))) (rcd-get-bracketed-id-end choice))) Let us now use that generic function to get a completion list: (defun my-people-completion-list () (let ((sql "SELECT coalesce(CASE WHEN people_firstname IS NOT NULL AND length(people_firstname) > 0 THEN people_firstname || ', ' END, '') || coalesce(CASE WHEN people_middlenames IS NOT NULL AND length(people_middlenames) > 0 THEN people_middlenames || ', ' END, '') || people_name || ' [' || people_id || ']' FROM people")) (sqlite-select my-db sql))) And here is how completion list looks like: (my-people-completion-list) ⇒ (("Joe, Doe [1]")) Here is the function to choose person among people and return it's ID: (defun my-people-complete () "Return person's ID." (let ((my-people (my-people-completion-list))) (cond (my-people (rcd-get-bracketed-id-end (completing-read "Choose person: " my-people))) (t (error "No people in database"))))) (my-people-complete) ⇒ 1 Let us get list of all columns in SQLite table: (defun my-sqlite-table-column-list (table) "From: https://stackoverflow.com/questions/604939/how-can-i-get-the-list-of-a-columns-in-a-table-for-a-sqlite-database" ;; (sqlite-select my-db "SELECT name FROM PRAGMA_TABLE_INFO(?)" (list table))) (mapcar 'cadr (sqlite-select my-db " SELECT m.name as tableName, p.name as columnName FROM sqlite_master m LEFT OUTER JOIN pragma_table_info((m.name)) p ON m.name <> p.name WHERE m.type IN ('table', 'view') AND m.name NOT LIKE 'sqlite_%' AND m.name = ? ORDER BY tableName, columnName" (list table)))) (my-sqlite-table-column-list "people") ⇒ ("people_description" "people_email" "people_firstname" "people_id" "people_middlenames" "people_name") Let us make function to get full person's name. - people_firstname can be empty, what if it is empty, it should not be displaed - people_middlename should be in the middle, but could be empty - people_name represents either last name or company name or group name, business name, list of people, etc. It must be there. (defun my-people-name (id) (caar (sqlite-select my-db "SELECT coalesce(CASE WHEN people_firstname IS NOT NULL AND length(people_firstname) > 0 THEN people_firstname || ' ' END, '') || coalesce(CASE WHEN people_middlenames IS NOT NULL AND length(people_middlenames) > 0 THEN people_middlenames || ' ' END, '') || people_name FROM people WHERE people_id = ?" (list id)))) (my-people-name 1) ⇒ "Joseph Joe Doe" Now function to edit people by their ID programmatically or interactively by choice: (defun my-people-edit (&optional id) (interactive) (let ((id (or id (my-people-complete))) (columns (my-sqlite-table-column-list "people"))) (cond (id (let ((column-to-edit (completing-read "Which column to edit? " columns nil t))) (cond ((member column-to-edit columns) (let* ((initial-input (sqlite-db-get-entry "people" column-to-edit id my-db)) (edited-entry (read-string (format "Edit `%s': " column-to-edit) initial-input nil initial-input))) (unless (string= initial-input edited-entry) (sqlite-execute my-db (format "UPDATE people SET %s = ? WHERE people_id = ?" column-to-edit) (list edited-entry id))) (my-people-edit id))) (t (message "No column choosen"))))) (t (message "No person selected for editing"))))) (sqlite-select my-db "SELECT * FROM people") ⇒ ((1 "Joseph" "Joe" "Doe Junior" "joe@example.com" "This is just a sample description")) ;; Delete entry Now let us make possibility to delete single entries: (defun my-people-delete (&optional id) (interactive) (let* ((id (or id (my-people-complete))) (name (my-people-name id))) (when (y-or-n-p (format "Do you wish to delete entry `%s'? " name)) (sqlite-execute my-db "DELETE FROM people WHERE people_id = ?" (list id)) (message "Deleted entry `%s'" name)))) (sqlite-select my-db "SELECT * FROM people") ⇒ nil Add again: (my-people-add) Select from database again: (sqlite-select my-db "SELECT * FROM people") ⇒ ((1 "Joseph" "joe" "Doe" nil nil)) Edit the entry (my-people-edit) or M-x my-people-edit (sqlite-select my-db "SELECT * FROM people") ⇒ ((1 "Joseph" "Joe" "Doe" "joe@example.com" "This is just a sample description")) now we come to export single person to Org entry: (my-people-to-org 1) ⇒ "* Joseph Joe E-mail: joe@example.com This is just a sample description" Now we can make full export to Org of all people in the database: (defun my-people-to-org () (let ((people (flatten-list (sqlite-select my-db "SELECT people_id FROM people")))) (with-temp-buffer (while people (insert (my-people-to-org-single (pop people)))) (buffer-string)))) (my-people-to-org) ⇒ "* Joseph Joe E-mail: joe@example.com This is just a sample description * Jane " Then we can export everything to Org file: (defun my-people-export-to-org-file () (interactive) (let ((file (read-file-name "Export people to which Org file? "))) (with-temp-file file (insert (my-people-to-org))))) M-x my-people-export-to-org-file now when one is aware that people may be managed very easy in the SQLite database, one shall examine the modes of SQLite: .mode MODE ?TABLE? Set output mode where MODE is one of: csv Comma-separated values column Left-aligned columns. (See .width) html HTML code insert SQL insert statements for TABLE line One value per line list Values delimited by .separator string tabs Tab-separated values tcl TCL list elements It also means that importing CSV files into SQLite is easy. Exporting from SQLite to Org file is also easy. Exporting SQL is easy: .mode insert sqlite> select * from people; INSERT INTO "table" VALUES(1,'Joseph','Joe','Doe','joe@example.com','This is just a sample description'); INSERT INTO "table" VALUES(2,'Jane','','Dine','',NULL); INSERT INTO "table" VALUES(3,'Robert',NULL,'Weiner','rsw@gnu.org',NULL); Or one value per line: .mode line select * from people; people_id = 1 people_firstname = Joseph people_middlenames = Joe people_name = Doe people_email = joe@example.com people_description = This is just a sample description people_id = 2 people_firstname = Jane people_middlenames = people_name = Dine people_email = people_description = people_id = 3 people_firstname = Robert people_middlenames = people_name = Weiner people_email = rsw@gnu.org people_description = Now we come to relation of Org to people ID. How about this: * My heading :PROPERTIES: :PEOPLE-ID-RELATED: 1 :END: Now you know that ID is in the file my-people.sqlite and that ID is 1. That is to stay so, immutable. Let us make the report for person: (define-derived-mode my-people-view-mode org-mode "My People View Mode" "This is read only view mode for people") (defun my-people-org-report (id) (let* ((name (my-people-name id)) (buffer (get-buffer-create name)) (org (my-people-to-org-single id))) (pop-to-buffer (get-buffer-create "My people report")) (read-only-mode 0) (erase-buffer) (insert org) (goto-char (point-min)) (my-people-view-mode) (keymap-set my-people-view-mode-map "q" #'quit-window) (keymap-set my-people-view-mode-map "e" `(lambda () (interactive) (my-people-edit ,id))) (read-only-mode 1))) The above allows to open the new Org buffer where entry from database is shown: (my-people-org-report 1) Then I can see this: * Joseph Joe E-mail: joe@example.com This is just a sample description now back to heading in your real Org file: * My heading :PROPERTIES: :PEOPLE-ID-RELATED: 1 :END: If person ID 1 is related to this heading, then I can make function to quickly see the entry of the person: (defun my-people-org-set-people-id-name (id) (let ((name (my-people-name id))) (org-set-property "PEOPLE-ID-NAME" name))) (defun my-people-org-related-view () (interactive) (let ((people-id-related (cdr (assoc "PEOPLE-ID-RELATED" (org-entry-properties))))) (cond (people-id-related (progn (my-people-org-set-people-id-name people-id-related) (my-people-org-report people-id-related))) (t (message "No related contact found in this Org heading"))))) When there is related contact one jumps into generated Org file by C-c r and then with "e" one can already edit the contact in general way by using Emacs minibuffer. And what if we jump to related contact, maybe we still need contact name in properties, so it is generated automatically and directly from the database. * My heading :PROPERTIES: :PEOPLE-ID-RELATED: 1 :PEOPLE-ID-NAME: Joseph Joe Doe :PEOPLE-ID-EMAIL: joe@example.com :END: What if we need email in properties? (defun my-people-org-set-people-id-email (id) (let ((email (sqlite-db-get-entry "people" "people_email" id my-db))) (org-set-property "PEOPLE-ID-EMAIL" email))) then (defun my-people-org-related-view () (interactive) (let ((people-id-related (cdr (assoc "PEOPLE-ID-RELATED" (org-entry-properties))))) (cond (people-id-related (progn (my-people-org-set-people-id-email people-id-related) (my-people-org-set-people-id-name people-id-related) (my-people-org-report people-id-related))) (t (message "No related contact found in this Org heading"))))) And how to add related person straight from database? (defun my-people-org-related-person (&optional id) (interactive) (let ((id (or id (my-people-complete)))) (when id (org-set-property "PEOPLE-ID-RELATED" (format "%s" id))))) This all assumes that it is just one person related to heading. We could make any number of people related to heading, but let us keep it to this example. At this moment maybe you are writing Org headline that is related to SCHOOL ABC: Here is the flow: 1. Add new entry in people table by using M-x my-people-add 2. Make new headline in Org: ** Cleaning project 3. Run function M-x my-people-org-related-person ** Cleaning project :PROPERTIES: :PEOPLE-ID-RELATED: 4 :PEOPLE-ID-EMAIL: myschool@example.com :PEOPLE-ID-NAME: SCHOOL ABC :END: At this point you may examine the entry for the ABC SCHOOL by clicking C-c r and it will update Org properties. By using this principle it could update anything in Org but also in any other type of file. By using SQLite or PostgreSQL you can use plethora of OTHER PROGRAMS to manage entries in your database. For example in sqlitebrowser: https://gnu.support/images/sqlite/2022/2022-10-09/2022-10-09-13:34:30.png Now your contacts are separated from Org. Only by using SQLite browser you may freely enter new contacts in the database and edit them by your wish. There are many various tools to manage databases. I am editing contacts by using tabulated list mode in Emacs: ID 6 Date created "2021-06-25 15:50:35.1712+03" Date modified "2022-10-06 12:37:31.877754+03" Prefix nil First name "Joe" Middle names nil Last name (People List Name) "Doe" Suffix nil Hyperdocument Set #1 nil Profile Image nil ID Document nil Lead source "thispersondoesnotexist.com/" Title nil Department nil Date of birth or Begin Date nil End date nil Don't contact nil Description nil Modified by username "maddox" Created by username "maddox" Introduced by "Vicente Ramirez" Other names nil Tokens "'data':2 'enter':1 'lastnam':5 'people.people':4" Rank 0 Assigned to nil People type "Individual Person" Reports to nil Communication Language nil SIC Code nil Industry nil Tag #1 nil Tag #2 nil Tag #3 nil Active "Active" FROM Identity nil Internal information nil But I can as well edit my contacts by using external tools and exchange information with other software. Do you wish to share your contacts? That is easy, one can simple send the full file to somebody. By followed the thought patterns described one shall understand that contacts may be separated from single mode (Org) and separated from single software (Emacs) and that it will not minimize or limit you but rather widen the capacities and usefulness for human. -- Jean Take action in Free Software Foundation campaigns: https://www.fsf.org/campaigns In support of Richard M. Stallman https://stallmansupport.org/