Hi Jean Louis, First i want to thank you for this very detailled explanation, this is very valuable for someone that start in elisp like me. Secondly, i found the idea of using sqlite for contacts very interesting, and i think a lot on this solution on my side the last weeks. With the new emacs interface to sql, it was easy i suppose to create a “both-way” syncing tool between org-contact (to easily maintain info, linking to notes, agenda, etc.) and a database that anyone could reuse, move, send also using mobile phone. I use org-roam that use both of the two world, database and org, in the light of recent discussion, why we cannot do the same things for org-contact ? Linking / syncing properties with unique hash/id also stored in a database. If you have 150000 contact, with 150000 unique ID, and if you want to create an org-contact file/propertie into an org document, that’s probably easy to inject and maintain some sort of syncing (like org-roam do) between the info in database and the info into some properties block no ? Best regards, SR Jean Louis writes: > * Sébastien Rey-Coyrehourcq [2022-09-09 ven. 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 > > 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:]]*\\)\\][[$” 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 [] 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: ” > ;; (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: > > > 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.