SQLite for contacts and relations to Org - Re: contact management in emacs

[Jean Louis <bugs@gnu.support>]

* Sébastien Rey-Coyrehourcq <sebastien.rey-coyrehourcq@univ-rouen.fr> [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 ⇒ #<sqlite db=0x55987eb7d4a0 name=/home/data1/protected/my-org-properties>

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 <table> 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/