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

[Sébastien Rey-Coyrehourcq]

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 <bugs@gnu.support> writes:

> * Sébastien Rey-Coyrehourcq <sebastien.rey-coyrehourcq@univ-rouen.fr> 
> [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 <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:]]*\\)\\][[$” 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.

signature.asc
Description: PGP signature