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

[Jean Louis]

* Sébastien Rey-Coyrehourcq  
[2022-10-11 08:56]:
> 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 15 contact, with 15 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 ?

That is exactly the point. Why keep well structured information such
as phone numbers, emails, addresses, in Org file, when structured
information by its type belong into structured forms like databases.

Keeping it in Org or any text files is not scalable, not usable, not
shareable. To tell people to use text files to keep information is
detrimental for people who listen to it. They can't know it
immediately, but one day they will realize that it does not fly.

Why email clients like Thunderbird have contacts features built-in?
For reason that contacts are related to communication and
information. Communication is connection in every relation between
people. Information is written because it fosters communication and
thus relations.

Org contacts try to store contacts into Org file. I don't know the
rest. I have looked inside the Org contacts code and I do not agree
with the design. It is not scalable.

>From code:

,
| To enter new contacts, you can use `org-capture' and a minimal
| template just like..
`

;; this:

;; ("c" "Contacts" entry (file "~/Org/contacts.org")
;;  "* %(org-contacts-template-name)
;; :PROPERTIES:
;; :EMAIL: %(org-contacts-template-email)
;; :END:")))
;;
;; You can also use a complex template, for example:
;;
;; ("c" "Contacts" entry (file "~/Org/contacts.org")
;;  "* %(org-contacts-template-name)
;; :PROPERTIES:
;; :EMAIL: %(org-contacts-template-email)
;; :PHONE:
;; :ALIAS:
;; :NICKNAME:
;; :IGNORE:
;; :ICON:
;; :NOTE:
;; :ADDRESS:
;; :BIRTHDAY:
;; :END:")))

 Usage:

;; How to search?
;; - You can use [M-x org-contacts] command to search.

I would not bother using that. Why? Because of experience.

- many people have many various emails, not just one. 

- by using email as reference, I quickly jump to list of conversations
  with that person

- if email is invalid, I still need the conversation retained

- I must know if email is for work or private, it needs more structure

- phones, I am sending SMS, I must know in structured way, if the
  phone belongs to provider ABC or XYZ, as that decides on routing on
  how to send SMS messages. Nobody likes spending money by using wrong
  providers. 

- fax is phone line, mobile phone and fixed lines are not same, I must
  know type of phone line;

- aliases and nicknames belong to identities,

- single note about contact is not enough, contact may be related to
  many notes.

- address must be structured, otherwise it is really difficult to use
  it programmatically:

 ID   198333
   Date created   "2022-10-11 21:28:42.565176"
  Date modified   nil
   User created   "maddox"
  User modified   "maddox"
 Person   "Joe Doe"
   Type   "Default address"
   Address Name   "Address in Germany"
Line #1   ""
Line #2   ""
Line #3   ""
   City   ""
 Region   ""
  Post code   ""
Country   "GERMANY"
 Date validated   nil
   Location   nil
Description   nil
   Inactive   nil
Default   nil

There is no option but to keep structured information in structured
databases. I have working system with PostgreSQL and decentralized
collaboration, but I am now making SQLite minimized version, so when
package is finished I will let you know.

Package preparation:
https://gnu.support/images/2022/10/2022-10-11/Screenshot-2022-10-11-22-50-03-955596051.png

Editing of entry:
https://gnu.support/images/2022/10/2022-10-11/Screenshot-2022-10-11-22-57-51-793932307.png


-- 
Jean

Take action in 

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 15 contact, with 15 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 ⇒ # 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” “r...@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” 
> “r...@gnu.org” nil) (2 “Robert” nil “Weiner” “r...@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 

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

[Robert Weiner]

You want a CRM system which to me is a very different creature and much more 
complex than a contact manager, meant to lookup fairly static information about 
people.  HyRolo was built to allow freeform contact management and to 
specifically avoid the typical, limiting field-based techniques in common use.

It would be a small extensio  to allow you to apply operations to a set of 
filtered contacts you have found.

-- Bob

> On Oct 10, 2022, at 7:39 PM, Jean Louis  wrote:
> 
> 
> 
> When anyway structured information such as people names, properties,
> their communication lines

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

[Jean Louis]

* Robert Weiner  [2022-10-11 01:31]:
> 1. Although I understand you do a great many things with your
> database-backed Hyperscope system and I work with RDBMSes every day, I
> don't really see great value in what you have shown in the context of
> contact management when compared to the already existing HyRolo or
> org-contacts.  We can easily add, delete, import and export contacts, we
> just follow very simple conventions in creating our contacts.  We can
> easily email contact files and have people apply text processing tools to
> them, so it would help if you just showed examples of something that Emacs
> really lacks before suggesting wrapping everything into a database system,
> as I know you are a very smart guy.

For org-contacts, I have nothing to say, as that is as limited as it
can be. 

When anyway structured information such as people names, properties,
their communication lines, addresses are in the separate database like
SQLite, that fact liberates information from single software like
HyRolo, and allows users to access, process information freely with
any programming languages and plethora of variety of tools for
inspection, analysis, exports, and sharing of such information.

For HyRolo, that is text. One cannot build upon it.

I am constantly sending SMS from Emacs and importing SMS from mobile
phones to contacts. When I send SMS I want to see which SMS was sent
and received by which contact at what time, by which phone number. It
is something not imaginable with text files. Similar is with
information sent to people, how am I supposed to know what information
I have sent, what not. There is no automated tracking in text files
like HyRolo. It is one example among way too many.

Adding properties to people, objects, it is much easier by using
selection that one can click onto, or use arrows, or just C-p for
previous one. Rather that, then writing with hand each time, error
prone, some skills of people in order to find people by skills.

Skill like "C programmer" I would need to add too many times and then
use find by regexp. 

All good and find, but not scalable. Of course that I have use text
files long ago to store contacts, but that was 28 years ago. I have
now 240106 entries of people and their groups.

Unspoken of marketing campaign, imagine when I paid $73 and got in 23
hours 1200+ leads for recruitment in specific sector. How would I
enter them in HyRolo? Automate some web server program to add them in
similar fashion like Org heading. It could work. But then how could I
send to those people in ordered fashion series of 3-10 training emails
that automates the process of recruitment and selection? Would HyRolo
help me track who received what at what time? There is no foundation
for such features in text files.

> 2. Years ago as part of my stab at an Emacs-based IDE, InfoDock (find it on
> Sourceforge),

Is it this one?

https://sourceforge.net/projects/infodock/

I cannot see how to start anything with it. Do you have screenshots?

> I also wrote an in-memory, file-based but fully relational database.
> The main point of which was to demonstrate direct manipulation
> querying of relational tables via simple mouse clicks/key presses on
> screen.  For simple queries, I found this very powerful and dirt
> simple for people to do.  If that were of interest, someone could
> take the existing code under infodock/id-lisp/rdb and interface it
> to SQLite pretty easily I would expect and then you would have an
> interesting Emacs interface without having to master SQL for basic
> table analysis.

Is it this one?

lib/infodock-4.0.8/i486-pc-sysv5/  
lib/infodock-4.0.8/i486-pc-sysv5/make-path 
lib/infodock-4.0.8/i486-pc-sysv5/wakeup
lib/infodock-4.0.8/i486-pc-sysv5/profile   
lib/infodock-4.0.8/i486-pc-sysv5/make-docfile  
lib/infodock-4.0.8/i486-pc-sysv5/digest-doc
lib/infodock-4.0.8/i486-pc-sysv5/sorted-doc
lib/infodock-4.0.8/i486-pc-sysv5/movemail  
lib/infodock-4.0.8/i486-pc-sysv5/cvtmail   
lib/infodock-4.0.8/i486-pc-sysv5/fakemail  
lib/infodock-4.0.8/i486-pc-sysv5/yow   
lib/infodock-4.0.8/i486-pc-sysv5/hexl  
lib/infodock-4.0.8/i486-pc-sysv5/gnuserv   
lib/infodock-4.0.8/i486-pc-sysv5/mmencode  
lib/infodock-4.0.8/i486-pc-sysv5/rcs2log   
lib/infodock-4.0.8/i486-pc-sysv5/vcdiff
lib/infodock-4.0.8/i486-pc-sysv5/gzip-el.sh
lib/infodock-4.0.8/i486-pc-sysv5/add-big-package.sh
lib/infodock-4.0.8/i486-pc-sysv5/config.values 
lib/infodock-4.0.8/i486-pc-sysv5/DOC   
bin/i386-intel-sco7/   
bin/i386-intel-sco7/etags  
bin/i386-intel-sco7/ctags  
bin/i386-intel-sco7/b2m
bin/i386-intel-sco7/gnuclient  
bin/i386-intel-sco7/ootags 
bin/i386-intel-sco7/rcs-checkin

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

[Robert Weiner]

Hi Jean:

A few thoughts.

1. Although I understand you do a great many things with your
database-backed Hyperscope system and I work with RDBMSes every day, I
don't really see great value in what you have shown in the context of
contact management when compared to the already existing HyRolo or
org-contacts.  We can easily add, delete, import and export contacts, we
just follow very simple conventions in creating our contacts.  We can
easily email contact files and have people apply text processing tools to
them, so it would help if you just showed examples of something that Emacs
really lacks before suggesting wrapping everything into a database system,
as I know you are a very smart guy.

2. Years ago as part of my stab at an Emacs-based IDE, InfoDock (find it on
Sourceforge), I also wrote an in-memory, file-based but fully relational
database.  The main point of which was to demonstrate direct manipulation
querying of relational tables via simple mouse clicks/key presses on
screen.  For simple queries, I found this very powerful and dirt simple for
people to do.  If that were of interest, someone could take the existing
code under infodock/id-lisp/rdb and interface it to SQLite pretty easily I
would expect and then you would have an interesting Emacs interface without
having to master SQL for basic table analysis.

-- rsw



On Mon, Oct 10, 2022 at 6:12 AM Jean Louis  wrote:

> * Quiliro Ordóñez  [2022-10-09 22:10]:
> > I agree.  But the end-user did not construct this program.  It was you.
> > I coould learn how to install it.  Then, I should teach the end-user to
> > use it.  But the program was not made or installed by the end-user.  I
> > might be able to teach the end-user to modify it.  But I doubt it.
>
> I will make a package so that you just install it and can start
> managing people.
>
> > What I mean is that end-users must have an easy entry point.  Of course
> > that ease must not hide innner workings because that would disempower
> > the end-user.  How do you propose it could be implemented? (if you agree
> > it should be done this way, of course)
>
> Inner workings are pretty much hidden unless user is programmer. Emacs
> is difficult, then there is underlying Emacs Lisp, then C language,
> then operating system, and stuff.
>
> Maybe you mean somthing else with inner workings?
>
> > > I have actually shown to you how it works, so you have to imagine that
> > > all that may be part of the package. Especially with SQLite databases
> > > there is nothing to configure.
> >
> > Not much to do.  Just installing SQLite and then the program you
> > propose.
>
> I did not try any SQLite package, just used the built-in functions in
> Emacs development version. Which packag did you install?
>
> --
> Jean
>
> Take action in Free Software Foundation campaigns:
> https://www.fsf.org/campaigns
>
> In support of Richard M. Stallman
> https://stallmansupport.org/
>
>

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

[Jean Louis]

* Quiliro Ordóñez  [2022-10-09 22:10]:
> I agree.  But the end-user did not construct this program.  It was you. 
> I coould learn how to install it.  Then, I should teach the end-user to
> use it.  But the program was not made or installed by the end-user.  I
> might be able to teach the end-user to modify it.  But I doubt it.

I will make a package so that you just install it and can start
managing people. 

> What I mean is that end-users must have an easy entry point.  Of course
> that ease must not hide innner workings because that would disempower
> the end-user.  How do you propose it could be implemented? (if you agree
> it should be done this way, of course) 

Inner workings are pretty much hidden unless user is programmer. Emacs
is difficult, then there is underlying Emacs Lisp, then C language,
then operating system, and stuff.

Maybe you mean somthing else with inner workings?

> > I have actually shown to you how it works, so you have to imagine that
> > all that may be part of the package. Especially with SQLite databases
> > there is nothing to configure.
> 
> Not much to do.  Just installing SQLite and then the program you
> propose.

I did not try any SQLite package, just used the built-in functions in
Emacs development version. Which packag did you install?

-- 
Jean

Take action in Free Software Foundation campaigns:
https://www.fsf.org/campaigns

In support of Richard M. Stallman
https://stallmansupport.org/

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

[Quiliro Ordóñez]

El 2022-10-09 11:59, Jean Louis escribió:
> * Quiliro Ordóñez  [2022-10-09 18:22]:
>> El 2022-10-09 05:40, Jean Louis escribió:
>>
>> > 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.
>>
>> This is a great summary about the use of database to hold information.
>> But it requires experience configuring databases for end-users.
> 
> We speak of what? User experience? We are in Emacs environment, that
> means you run your application either by using mouse click or by M-x
> or key binding.
> 
> In the provided example you as user need nothing else but
> that. Hypothetically basic actions are just add, modify, delete,
> search for contacts. You do that by key binding in Emacs, or by mouse,
> or by M-x
> 
> In the example provided user need do nothing but that. Even the file
> can be automatically stored like in ~/.emacs.d/people.sqlite or
> otherwise configured to be somewhere else.

I agree.  But the end-user did not construct this program.  It was you. 
I coould learn how to install it.  Then, I should teach the end-user to
use it.  But the program was not made or installed by the end-user.  I
might be able to teach the end-user to modify it.  But I doubt it.

What I mean is that end-users must have an easy entry point.  Of course
that ease must not hide innner workings because that would disempower
the end-user.  How do you propose it could be implemented? (if you agree
it should be done this way, of course) 

> I have actually shown to you how it works, so you have to imagine that
> all that may be part of the package. Especially with SQLite databases
> there is nothing to configure.

Not much to do.  Just installing SQLite and then the program you
propose.

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

[Jean Louis]

* Quiliro Ordóñez  [2022-10-09 18:22]:
> El 2022-10-09 05:40, Jean Louis escribió:
> 
> > 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.
> 
> This is a great summary about the use of database to hold information. 
> But it requires experience configuring databases for end-users.

We speak of what? User experience? We are in Emacs environment, that
means you run your application either by using mouse click or by M-x
or key binding.

In the provided example you as user need nothing else but
that. Hypothetically basic actions are just add, modify, delete,
search for contacts. You do that by key binding in Emacs, or by mouse,
or by M-x

In the example provided user need do nothing but that. Even the file
can be automatically stored like in ~/.emacs.d/people.sqlite or
otherwise configured to be somewhere else.

How many options does Org have? The example I have provided may be
option free. 

There is nothing to do but to add, modify, delete, search for
contacts, export, and so on; all by using M-x, key binding or mouse.

> It could work if the database was set up by someone else or if the
> fields were easy to define by the end-user by some kind of
> interface.

I have actually shown to you how it works, so you have to imagine that
all that may be part of the package. Especially with SQLite databases
there is nothing to configure.

This is what defines where the database is:

(defcustom rcd-people-sqlite-file (concat (expand-file-name 
user-emacs-directory) "rcd-people.sqlite")
  "SQLite database file location."
  :type 'file
  :group 'rcd)

We evaluate it:

rcd-people-sqlite-file ⇒ "/home/data1/protected/.emacs.d/rcd-people.sqlite"

The above variable is part of the package. There is nothing for
user to think about it. There is nothing to configure but user
is free to customize it.

Fields or columns in preset database tables are designed by
programmer.

It is not hard to have options to add column, delete column or
rename column. That is exactly what I am doing on the fly during
Emacs sessions.
  
> On the other hand, there is a movement to use plain text for everything.
> I know you disagree.

There are movements for each and everything. Why should I
disagree with movements, I let them be. 

What I do not agree is to claim strongly that it is useful to
keep plain text or clay tables with Cuneiform for every type of
information.

> But here is some information about accounting
> this way: https://plaintextaccounting.org

The huge amount of work necessary to create plain text accounting
can't be compared to database work and efforts. I guess I have
implemented accounting with database about 4 times in my life
and I use it in various contexts. Standard accounting is simple.

Accounting types (pasted straight from Emacs view of database
list):

 1  Asset
 2  Liability
 3  Equity
 4  Income
 5  Expenses

Sample account defined:
 ID   1
   Date created   "2022-02-27 18:36:28.690616"
  Date modified   "2022-02-27 18:36:39.372512"
   User created   "maddox"
  User modified   "maddox"
  Hyperdocument   "2022-02-27 Transactions for Joe Doe"
   Account Type   "Asset"
 Person   "Jean Louis"
   Currency   "UGX - Ugandan shilling"
   Name   "Jean Louis"
Description   nil
   Code   nil

It belongs to Hyperdocument which has type of accounting. There
may be multiple companies, multiple accounting sets or parents
to which accounts belong. No need to open a "new file" for each
new company.

And then there is journal entry:

-[ RECORD 1 
]--+---
journal_id | 4
journal_datecreated| 2019-06-12 00:08:04.409559
journal_datemodified   | 
journal_usercreated| maddox
journal_usermodified   | maddox
journal_cashentrytypes | 1
journal_code   | 
journal_description| food
journal_debit  | 5
journal_credit | 7
journal_amount | 4000
journal_signature  | 
journal_date   | 2019-06-10

The entry debits account ID 5, credits account ID 7.

> I learned a lot of elisp by reading your long explanation about how to
> construct a contact database directly on Emacs 29 without adding
> anything else.  Thank you very much!

Well that is really good, thank you.


-- 
Jean

Take action in Free 

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

[Quiliro Ordóñez]

El 2022-10-09 05:40, Jean Louis escribió:

> 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.

This is a great summary about the use of database to hold information. 
But it requires experience configuring databases for end-users.  It
could work if the database was set up by someone else or if the fields
were easy to define by the end-user by some kind of interface.

On the other hand, there is a movement to use plain text for everything.
 I know you disagree.  But here is some information about accounting
this way: https://plaintextaccounting.org

I learned a lot of elisp by reading your long explanation about how to
construct a contact database directly on Emacs 29 without adding
anything else.  Thank you very much!

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

[Jean Louis]

* 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" "r...@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" 
"r...@gnu.org" nil) (2 "Robert" nil "Weiner" "r...@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) ⇒ "j...@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))