exported contacts problem

exported contacts problem

[Jude DaShiell]

I have one email message with several .vcf file attachments.  Has orgmode
got any tool or tools I can use to import contacts from such a message
into an orgmode table?



--

Re: exported contacts problem

[Jean Louis]

* Jude DaShiell <jdashiel@panix.com> [2019-08-02 17:48]:
> I have one email message with several .vcf file attachments.  Has orgmode
> got any tool or tools I can use to import contacts from such a message
> into an orgmode table?

There is package `org-vcard' that you may use.

Other vcard related packages:

- bbdb-vcard to import into bbdb
- vdirel

I wish those package authors would have better and more generic
functions so that vcards can be imported first to Lisp structures,
like alists, etc. so that vcard functions can be reused. By looking
onto `org-card' it does not seem to be so, sadly.

Jean

Re: exported contacts problem

[Jean Louis]

* Jude DaShiell <jdashiel@panix.com> [2019-08-02 17:48]:
> I have one email message with several .vcf file attachments.  Has orgmode
> got any tool or tools I can use to import contacts from such a message
> into an orgmode table?

And by all means, I would never keep contact in Org file, that is for
short list fine, but for any future planning, contacts shall be in a
real database sorted by its lists.

A list could be group of people, or account name, or company name, or
organization, or interest lists.

Neither bbdb nor Org is suitable for any serious collection of
contacts. I have 192,000+ contacts, and when they are in database and
I am using PostgreSQL, it gives me most of benefits, I can sort people
into lists, groups, I can contact them, count interactions, open up
their files, emails with a fast command, edit their data, add notes,
send them faxes and SMS, maintain relations.

Jean

Re: exported contacts problem

[Jude DaShiell]

I have about 80 contacts so this is a personal contacts situation not a
business contacts situation.

On Fri, 2 Aug 2019, Jean Louis wrote:

> Date: Fri, 2 Aug 2019 12:02:36
> From: Jean Louis <bugs@gnu.support>
> To: Jude DaShiell <jdashiel@panix.com>
> Cc: emacs-orgmode@gnu.org
> Subject: Re: [O] exported contacts problem
>
> * Jude DaShiell <jdashiel@panix.com> [2019-08-02 17:48]:
> > I have one email message with several .vcf file attachments.  Has orgmode
> > got any tool or tools I can use to import contacts from such a message
> > into an orgmode table?
>
> And by all means, I would never keep contact in Org file, that is for
> short list fine, but for any future planning, contacts shall be in a
> real database sorted by its lists.
>
> A list could be group of people, or account name, or company name, or
> organization, or interest lists.
>
> Neither bbdb nor Org is suitable for any serious collection of
> contacts. I have 192,000+ contacts, and when they are in database and
> I am using PostgreSQL, it gives me most of benefits, I can sort people
> into lists, groups, I can contact them, count interactions, open up
> their files, emails with a fast command, edit their data, add notes,
> send them faxes and SMS, maintain relations.
>
> Jean
>

-- 

Re: exported contacts problem

[Eric Abrahamsen]

Jean Louis <bugs@gnu.support> writes:

> * Jude DaShiell <jdashiel@panix.com> [2019-08-02 17:48]:
>> I have one email message with several .vcf file attachments.  Has orgmode
>> got any tool or tools I can use to import contacts from such a message
>> into an orgmode table?
>
> And by all means, I would never keep contact in Org file, that is for
> short list fine, but for any future planning, contacts shall be in a
> real database sorted by its lists.
>
> A list could be group of people, or account name, or company name, or
> organization, or interest lists.
>
> Neither bbdb nor Org is suitable for any serious collection of
> contacts. I have 192,000+ contacts, and when they are in database and
> I am using PostgreSQL, it gives me most of benefits, I can sort people
> into lists, groups, I can contact them, count interactions, open up
> their files, emails with a fast command, edit their data, add notes,
> send them faxes and SMS, maintain relations.

That sounds pretty intense. Have you written a separate package for
interacting with the database?

Re: exported contacts problem

[Jean Louis]

* Eric Abrahamsen <eric@ericabrahamsen.net> [2019-08-02 23:11]:
> > Neither bbdb nor Org is suitable for any serious collection of
> > contacts. I have 192,000+ contacts, and when they are in database and
> > I am using PostgreSQL, it gives me most of benefits, I can sort people
> > into lists, groups, I can contact them, count interactions, open up
> > their files, emails with a fast command, edit their data, add notes,
> > send them faxes and SMS, maintain relations.
> 
> That sounds pretty intense. Have you written a separate package for
> interacting with the database?

I wrote few generic functions and use it with helm, it works for
me. It is not ready for public. Web interface works based on Gedafe[1]
and then I wrote access to it through console Common Lisp and then
also through Emacs Lisp.

I am using Emacs module `emacs-libpq'[2] to interact with PostgreSQL.

Let's say I am looking for somebody, Hyper-C asks me for query, I
enter something, then I can choose to open Org file relating to the
person. When dealing with various companies, things related to a
company shall be in the directory related to company. Things related
to person, for example assignments related to one person shall be in
the file belonging to that person.

That way full file can be sent to the person for review, it becomes
clear what has been done, and what is yet to be done.

Mixing assignments, TODO, agendas from all subjects, groups and
persons into one file would give me terrible confusion.

Jean


Footnotes:
[1]  https://gedafe.github.io/index.en.html

[2]  https://github.com/anse1/emacs-libpq#start-of-content

Re: exported contacts problem

[Eric Abrahamsen]

Jean Louis <bugs@gnu.support> writes:

> * Eric Abrahamsen <eric@ericabrahamsen.net> [2019-08-02 23:11]:
>> > Neither bbdb nor Org is suitable for any serious collection of
>> > contacts. I have 192,000+ contacts, and when they are in database and
>> > I am using PostgreSQL, it gives me most of benefits, I can sort people
>> > into lists, groups, I can contact them, count interactions, open up
>> > their files, emails with a fast command, edit their data, add notes,
>> > send them faxes and SMS, maintain relations.
>> 
>> That sounds pretty intense. Have you written a separate package for
>> interacting with the database?
>
> I wrote few generic functions and use it with helm, it works for
> me. It is not ready for public. Web interface works based on Gedafe[1]
> and then I wrote access to it through console Common Lisp and then
> also through Emacs Lisp.
>
> I am using Emacs module `emacs-libpq'[2] to interact with PostgreSQL.
>
> Let's say I am looking for somebody, Hyper-C asks me for query, I
> enter something, then I can choose to open Org file relating to the
> person. When dealing with various companies, things related to a
> company shall be in the directory related to company. Things related
> to person, for example assignments related to one person shall be in
> the file belonging to that person.
>
> That way full file can be sent to the person for review, it becomes
> clear what has been done, and what is yet to be done.
>
> Mixing assignments, TODO, agendas from all subjects, groups and
> persons into one file would give me terrible confusion.

Okay, thanks for that run-down, pretty interesting. I've written a
package called EBDB[1] that's meant to be sort of an update to BBDB, and
while I think someone's using it with tens of thousands of contacts,
192k records would probably exhaust it. It has pluggable data stores,
however, one of which will (eventually) be a proper external database,
so I'm always interested in how people are using this stuff.

Eric

[1] https://github.com/girzel/ebdb

Re: exported contacts problem

[Jean Louis]

* Eric Abrahamsen <eric@ericabrahamsen.net> [2019-08-03 02:27]:
> Okay, thanks for that run-down, pretty interesting. I've written a
> package called EBDB[1] that's meant to be sort of an update to BBDB, and
> while I think someone's using it with tens of thousands of contacts,
> 192k records would probably exhaust it. It has pluggable data stores,
> however, one of which will (eventually) be a proper external database,
> so I'm always interested in how people are using this stuff.
> 
> Eric
> 
> [1] https://github.com/girzel/ebdb

I have tried it. It has good concepts and integration. But I cannot
switch to it.

Please, if you intend to make vcard import, don't make vcard stuff
bundled with the ebdb, you will make repeated mistake.

Make one vcard import package that give elisp structure, something
like hash or alist, plist, whatever is better.

Then such package is best to contribute to GNU Emacs.

Then all other packages can use vcard import.

This makes much sense rather than making it single-package oriented.

I don't think that vCard complexity is necessary for contacts,
standard is invented, but is too complex. But if you are doing it,
than such feature can contribute to overall usage.

Jean

Re: exported contacts problem

[Eric Abrahamsen]

Jean Louis <bugs@gnu.support> writes:

> * Eric Abrahamsen <eric@ericabrahamsen.net> [2019-08-03 02:27]:
>> Okay, thanks for that run-down, pretty interesting. I've written a
>> package called EBDB[1] that's meant to be sort of an update to BBDB, and
>> while I think someone's using it with tens of thousands of contacts,
>> 192k records would probably exhaust it. It has pluggable data stores,
>> however, one of which will (eventually) be a proper external database,
>> so I'm always interested in how people are using this stuff.
>> 
>> Eric
>> 
>> [1] https://github.com/girzel/ebdb
>
> I have tried it. It has good concepts and integration. But I cannot
> switch to it.

No, and I think it would fall apart under your workload. But I would
like to make it better, and gradually get it closer to supporting the
sort of thing you're doing.

> Please, if you intend to make vcard import, don't make vcard stuff
> bundled with the ebdb, you will make repeated mistake.
>
> Make one vcard import package that give elisp structure, something
> like hash or alist, plist, whatever is better.

Yes, that's on my (mid-length) list of to do: a vcard parsing library
that just turns vcards into a couple pre-defined formats, or lets you
register your own consumption functions.

> Then such package is best to contribute to GNU Emacs.
>
> Then all other packages can use vcard import.
>
> This makes much sense rather than making it single-package oriented.
>
> I don't think that vCard complexity is necessary for contacts,
> standard is invented, but is too complex. But if you are doing it,
> than such feature can contribute to overall usage.

I'm also not fond of vcard, but I *am* fond of carddav, and having my
contacts synced to my phone, and shared with co-workers, etc. Otherwise
I probably wouldn't bother.

Jean Louis <bugs@gnu.support> writes:

> * Eric Abrahamsen <eric@ericabrahamsen.net> [2019-08-03 02:27]:
>> Okay, thanks for that run-down, pretty interesting. I've written a
>> package called EBDB[1] that's meant to be sort of an update to BBDB, and
>> while I think someone's using it with tens of thousands of contacts,
>> 192k records would probably exhaust it. It has pluggable data stores,
>> however, one of which will (eventually) be a proper external database,
>> so I'm always interested in how people are using this stuff.
>> 
>> Eric
>> 
>> [1] https://github.com/girzel/ebdb
>
> Not sure if you rely on vCard as some kind of "standard". It is
> attempt to globalize the contact keeping, but I don't find it good,
> not at all.
>
> It is way too complicated. It tries to encompass everything possible
> in one file, file which is probably not related to other files at all.
>
> Only relational database makes sense for complex contact management.

EBDB now provides for a sort of poor-man's relational database, with
multiple "roles" and relations, linking people to people and people to
organizations. But it's still limited and kind of awkward.

Eric

Re: exported contacts problem

[Jean Louis]

* Eric Abrahamsen <eric@ericabrahamsen.net> [2019-08-03 17:39]:
> No, and I think it would fall apart under your workload. But I would
> like to make it better, and gradually get it closer to supporting the
> sort of thing you're doing.

I have started some teaching lessons in how to create personal
information management, but it will take time until it becomes
ready. I wish GNU Emacs would have more of such applications
developed, including invoicing people, accounting and similar.

> Yes, that's on my (mid-length) list of to do: a vcard parsing library
> that just turns vcards into a couple pre-defined formats, or lets you
> register your own consumption functions.

That is really missing, as Androids are providing contact management
export in VCF format. The NextCloud contact management has it too. It
became some kind of a standard. Even it is not as good for exchange. 

> I'm also not fond of vcard, but I *am* fond of carddav, and having my
> contacts synced to my phone, and shared with co-workers, etc. Otherwise
> I probably wouldn't bother.

I was looking on Wikipedia, that is basically protocol to exchange the
vCards if I understand well. Exactly, for syncing it is necessary.

> EBDB now provides for a sort of poor-man's relational database, with
> multiple roles and relations, linking people to people and people to
> organizations. But it's still limited and kind of awkward.

That is true.

I have started long ago with PostgreSQL and it works well, and it is
extensible.

What I think it is good is to give to people possibility to extend the
personal information management without limitations. As such are the
SQL databases.

I have been using Gedafe[1] for long time. It is Generic Database
Frontend and it works over web. But for my fast writing and needs it
is not enough fast. Also not developed for long time. But imagine, you
create the table and the interface gives you search, to add, delete,
modify records and with some efforts to make custom reports. Isn't
that nice?

In that sense I have made few functions to read the database tables
and basically edit any field there is through Emasc Lisp and helm.

Now imagine if you make ebdb so that it is based on a database and
that people can freely add a new database related to basic skeleton,
and that all tables can be used and inserted.

Then we have all kinds of opportunities:

EXAMPLE A:
==========
1. Maybe somebody is managing lands, like real estate, he could make
   real estate database tables, and relate it to people in the contact
   address book. Right? Isn't that why we are keeping the address
   books, because all of the people belong to some kind of groups, be
   it family, or enemies, sibblins, customers, it could be donors, it
   could be potential leads, travelers we meet in hotels worldwide. It
   could be real estate owners, right? Somebody needs familiar
   interface to manage entries related to real estate related to
   contacts. It requires "relational" database.

2. Then Org mode could be used to link directly to the person in the
   database, and link to the report about the house from the real
   estate database. It results with real estate report in the Org
   document with pictures, that can be printed, distributed, glued on
   the display, and similar.

3. Now imagine some will have 300 or 1000 properties to list that way,
   it becomes breeze to speed up such cycles.

EXAMPLE B:
==========
1. Imagine Italian manufacturer selling Venetian masks. Customers are
   from all over the world. He could create table about "Venetian
   Masks" and relate it to contacts and organizations. Generic
   functions to edit such table, modify entries, add new entries could
   be made in Emacs Lisp. It becomes inventory management.

Any of those tables in the database are almost always related to some
people.

And we keep having separate packages for this and that, instead of
using databases more for the relational organization of information.

Jean

Footnotes:
[1]  https://gedafe.github.io/index.en.html

Re: exported contacts problem

[Neil Jerram]

[-- Attachment #1: Type: text/plain, Size: 728 bytes --]

I've tried to work on contact conversion and synchronization in the past,
aiming to merge and unify contacts that I've built up in BBDB, Google
Contacts, email systems, pre-Android phones, etc.  The problematic aspect
was different systems using different field names and structures, e.g. one
with separate First Name and Last Name, and another with a combined Name
field; different approaches to breaking up addresses; additional arbitrary
notes fields; etc.

With that in mind, I'm curious if the writers on this thread could comment
on:
1. is this situation any better now?
2. if you favour using org-contacts or org-vcard, what do you see as the
benefit of Org as your master contact format, as opposed to say BBDB or
.vcf?

[-- Attachment #2: Type: text/html, Size: 843 bytes --]

Re: exported contacts problem

[Jean Louis]

* Neil Jerram <neiljerram@gmail.com> [2019-08-03 19:49]:
> I've tried to work on contact conversion and synchronization in the past,
> aiming to merge and unify contacts that I've built up in BBDB, Google
> Contacts, email systems, pre-Android phones, etc.  The problematic aspect
> was different systems using different field names and structures, e.g. one
> with separate First Name and Last Name, and another with a combined Name
> field; different approaches to breaking up addresses; additional arbitrary
> notes fields; etc.
> 
> With that in mind, I'm curious if the writers on this thread could comment
> on:
> 1. is this situation any better now?
> 2. if you favour using org-contacts or org-vcard, what do you see as the
> benefit of Org as your master contact format, as opposed to say BBDB or
> .vcf?

Org mode is for writing hierarchically structured documents. It could
hold contacts to certain degree. Why not.

I have started keeping my contacts as bunch of business cards or paper
notes. Sooner or later it converted to the box of paper notes,
something similar as on this picture:
https://img0.etsystatic.com/000/0/5326108/il_fullxfull.298169988.jpg

Then I figured out, it is easier searchable if I keep it in text
files. That is basic system.

You could use file system hierarchy and create directory names by
groups, organizations, lists or companies.

Within each of them you could create directories by persons.

Within each directory there could be address or contact information.

Or everything in one file? That is also possible.

But the more I think about it, relational databases such as PostgreSQL
are the right tool for that. They offer all kinds of reporting,
discovery of information, all kinds of interfaces can access such
databases.

For me there is no special benefit to keep contacts in the Org
file. It is just one file, part of file system. I keep contacts in the
database and set of principle decides where is the Org file to be
found for particular person.

If person's ID is 1234, maybe such file could be found within file
system as:

~/Work/RCD/Groups/Organization-ABC/1234/1234.org

And that is the main Org file for the person. Such directory is
created automatically, I don't work on that, Emacs Lisp does it for
me.

If I create person with the ID 1234 in the database, I can click and
open Org file for the person, without knowing what is exactly the ID
of that person, or where such file is located exactly. Computer
program creates the ID, and decides where the file is placed, Emacs is
opening it for me. No thinking.

If I would keep all contacts in one Org file, that means I would have
to do a lot of file system work manually, I would need to be careful
how do I edit fields, did I make mistake, and so on, where many such
details can be handled by the database and very little programming.

My master contact or personal information management or central files
or synonym to customer relationship management is located in the
database. And I have access through Emacs to the database.

I can access contacts from Org mode by using Babel, so that is fine. I
can link to contacts or make a report about the contact in the Org
mode, as I can use SQL Babel to draw such report from Org file.

But how do I do the same other way around? It is hard. Org was not
designed for that.

Can I use scheme programming language to make a report about contacts
from an Org file? Probably not. Org has structure but it is not a
relational database[1].

Can I use Perl or Python, or Common Lisp to take information from the
BBDB, VCF or Org file? It does not work, there are no such packages in
other languages, not yet, and it does not make sense to rely in future
on contacts in Org file unless it is really just few hundreds of
personal contacts.

Even 23 hours of marketing that I have done in East Africa brought me
over 1,300 new contacts. All such contacts arrive as subscription to
mailing list. That was in 2014. Those contacts are reading my email. I
would not be able to keep all the files and stuff quickly related to
them would I be using Org file for that.

Imagine the workflow:

- create landing page

- pay marketing

- accept the entries, encrypt it on the fly. I am not keeping
  databases on Internet, never. The LISP structure get encrypted with
  the GnuPG[2] on the fly on the server, and later I simply ssh/rsync
  it to my offline computers.

- the database entry is synced to offline computer into the offline
  database. This works automatically. If I would have 1,300 contacts
  to enter in one single day, I would spend either money for workers
  or time and efforts for careful writing of it. This way their names
  get capitalized automatically, IP address can give a clue about
  their city, country, email and/or phone is entered into database

- computer is then sending them follow up emails, SMS or schedules
  meetings or phone calls, all I need to do is to make set of
  principles and few functions to handle those principles.

- now people are writing back, right? So there is live conversation,
  some of them are sending their CVs, some of them are calling,
  writing emails. So what to do now? With the Org file if they call
  you back, you need to open the Org file, to find out what is that
  phone number, who is calling, you need to locate the entry to
  understand the background of the person. He will say that he
  received your email, but 1300 other people received it too, you
  don't know who is that without quick search of the contact.

  On my side, in the shell, I am searching as:

  $ @ Peter Luca

  where sign @ is alias for "search" which searches for particular
  contacts.

  Or within Emacs, I use Hyper-c, so much close to M-c, I can enter
  few words and see the profile of the person. If there are pictures
  somewhere, I can open pictures quickly, as such are automatically
  sorted into directories appropriate to the person. I can quickly
  open Org file for the person if I wish to take notes, or I can take
  a note into the database. I could quickly open all email
  conversations and know what is this about.

I don't know how I would be doing that with Org files.

Sometimes I hire people to work together, so I give them database
access over virtual private network, and they start calling people and
following up with them, they enter information into the database, and
such information is accessible to every person working on the
database. It is collaboration feature that every SQL database has as a
built-in.

I would not be able to do it nicely with Org file. I could delegate
parts of Org file to various people and then concatenate it together
back, but I would not have a picture really what happened. I would
have to keep track of who did what at what particular time, right? You
want to know which user did what. With text files such as Org, one
need more effort to make it right.

With the database, a trigger can be installed to record the last user
that modified certain entry of the database. Trigger can indicate at
which date/time certain entry was added or modified. It becomes easier
to track events.

Instead of using Org text files as database for contacts, I can just
recommend using database for contacts whereby the database could
contain Org files related to contacts.

Jean


Footnotes:
[1]  https://www.postgresql.org

[2]  https://www.gnupg.org

Re: exported contacts problem

[Eric Abrahamsen]

Neil Jerram <neiljerram@gmail.com> writes:

> I've tried to work on contact conversion and synchronization in the past,
> aiming to merge and unify contacts that I've built up in BBDB, Google
> Contacts, email systems, pre-Android phones, etc.  The problematic aspect
> was different systems using different field names and structures, e.g. one
> with separate First Name and Last Name, and another with a combined Name
> field; different approaches to breaking up addresses; additional arbitrary
> notes fields; etc.
>
> With that in mind, I'm curious if the writers on this thread could comment
> on:
> 1. is this situation any better now?

If by "this situation" you mean fragmentation of data formats and
approaches, I don't think it's getting any better!

> 2. if you favour using org-contacts or org-vcard, what do you see as the
> benefit of Org as your master contact format, as opposed to say BBDB or
> .vcf?

I think something similar happens with Org as with happens with Emacs in
general: it's such a nice environment to be working in that people want
to move all their stuff into it. But there are some areas (contact
management, email, large datasets) where Org just isn't going to work as
well as a specialized tool.

But Org can be an excellent *interface* to those tools, mostly through
dynamic blocks. I've started using small sqlite databases to keep track
of things, and dynamic blocks as sql composers/views, and it works
great. It's very easy to play with the queries, and this is the first
time I'm actually starting to feel comfortable with sql.

I think in general Org is best used as a compositional tool for data
drawn from elsewhere.

Eric

Re: exported contacts problem

[Jean Louis]

* Eric Abrahamsen <eric@ericabrahamsen.net> [2019-08-03 23:33]:
> time I'm actually starting to feel comfortable with sql.

I am using skeleton to quickly create SQL definitions.

Now imagine `contacts', `accounts', `countries', etc. It works fast.

(define-skeleton cf-sql-table
    "Prepare the SQL table for Central Files database design"
  nil
  "
-- ------------------------------------------
-- ------------ Table " (setq table (skeleton-read "Table name: ")) "
-- ------------------------------------------
DROP SEQUENCE " table "_id_seq;

CREATE TABLE " table " (
" table "_id SERIAL NOT NULL PRIMARY KEY,
" table "_datecreated TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
" table "_datemodified TIMESTAMP,
" table "_usercreated TEXT NOT NULL DEFAULT current_user,
" table "_usermodified TEXT NOT NULL DEFAULT current_user,
" table "_name TEXT,
" table "_title TEXT,
" table "_description TEXT,
" table "_ TEXT
);
GRANT ALL ON " table " TO PUBLIC;
DROP VIEW " table "_combo;
CREATE OR REPLACE VIEW " table "_combo AS
SELECT " table "_id AS id,
" table "_name AS TEXT
FROM " table ";
GRANT SELECT ON " table "_combo TO PUBLIC;
COMMENT ON TABLE " table " IS '" (capitalize table) "';
COMMENT ON COLUMN " table "." table "_id IS 'ID';
COMMENT ON COLUMN " table "." table "_datecreated IS 'Date created';
COMMENT ON COLUMN " table "." table "_datemodified IS 'Date modified';
COMMENT ON COLUMN " table "." table "_usercreated IS 'User created';
COMMENT ON COLUMN " table "." table "_usermodified IS 'User modified';
COMMENT ON COLUMN " table "." table "_hid IS 'HID';
COMMENT ON COLUMN " table "." table "_name IS 'Name';
COMMENT ON COLUMN " table "." table "_title IS 'Title';
COMMENT ON COLUMN " table "." table "_description IS 'Description';
COMMENT ON COLUMN " table "." table "_IS '';

CREATE UNIQUE INDEX " table "_index ON " table " ( " table "_weekend );

INSERT INTO meta_fields VALUES ('" table "','" table "_description','widget','area(rows=10,cols=60)');
INSERT INTO meta_fields VALUES ('" table "','" table "_datecreated','widget','readonly');
INSERT INTO meta_fields VALUES ('" table "','" table "_datemodified','widget','readonly');
INSERT INTO meta_fields VALUES ('" table "','" table "_usercreated','widget','readonly');
INSERT INTO meta_fields VALUES ('" table "','" table "_usermodified','widget','readonly');
INSERT INTO meta_fields VALUES ('" table "','" table "_','hide_list','1');
-- INSERT INTO " table " (" table "_name) VALUES ('');
-- INSERT INTO meta_tables VALUES ('" table "', 'hide', '1');

-- Triggers
-- For Date Modified
CREATE TRIGGER " table "_moddatetime
BEFORE UPDATE ON " table "
FOR EACH ROW
EXECUTE PROCEDURE moddatetime(" table "_datemodified);

-- For User Modified
CREATE TRIGGER insert_username_" table "
BEFORE INSERT OR UPDATE ON " table "
FOR EACH ROW
EXECUTE PROCEDURE insert_username(" table "_usermodified);


-- List view
/*
DROP VIEW " table "_list;
CREATE OR REPLACE VIEW " table "_list AS
SELECT " table "_id, " table "_name
FROM " table " ORDER BY " table "_id DESC;
COMMENT ON VIEW " table "_list IS '" (capitalize table) "';
COMMENT ON COLUMN " table "_list." table "_id IS 'ID';
COMMENT ON COLUMN " table "_list." table "_name IS 'Name'; 
*/
}
);")

Re: exported contacts problem

[Tim Cross]

Think I agree. This is close to how I use org as well. For me, org pulls
it together - I have data/information in postgres, sqlite, maildirs,
filesystem, etc. Notes, todos, journal, bookmarks and documents are in
org. I use org as the way to assemble and prsent this information and
as an authoring tool, exporting to other formats when necessary. My
planning is done in org and I use org as a literate programming
envrionment for database work (I work as a DBA), elisp and scripting.

For sql it is really great as there are few good systems for working
with sql that also support version control. Using org and git, I can get
the best of both worlds.

When it comes to programming, I tend to use more 'native' environments,
especially when a REPL is involved.

My contact management requirements are small as it is just for personal
stuff. For work, I need to use the enterprise CRM because the
information is shared across the organisation and because of the complex
legislation regarding personal information management. 


Eric Abrahamsen <eric@ericabrahamsen.net> writes:

> Neil Jerram <neiljerram@gmail.com> writes:
>
>> I've tried to work on contact conversion and synchronization in the past,
>> aiming to merge and unify contacts that I've built up in BBDB, Google
>> Contacts, email systems, pre-Android phones, etc.  The problematic aspect
>> was different systems using different field names and structures, e.g. one
>> with separate First Name and Last Name, and another with a combined Name
>> field; different approaches to breaking up addresses; additional arbitrary
>> notes fields; etc.
>>
>> With that in mind, I'm curious if the writers on this thread could comment
>> on:
>> 1. is this situation any better now?
>
> If by "this situation" you mean fragmentation of data formats and
> approaches, I don't think it's getting any better!
>
>> 2. if you favour using org-contacts or org-vcard, what do you see as the
>> benefit of Org as your master contact format, as opposed to say BBDB or
>> .vcf?
>
> I think something similar happens with Org as with happens with Emacs in
> general: it's such a nice environment to be working in that people want
> to move all their stuff into it. But there are some areas (contact
> management, email, large datasets) where Org just isn't going to work as
> well as a specialized tool.
>
> But Org can be an excellent *interface* to those tools, mostly through
> dynamic blocks. I've started using small sqlite databases to keep track
> of things, and dynamic blocks as sql composers/views, and it works
> great. It's very easy to play with the queries, and this is the first
> time I'm actually starting to feel comfortable with sql.
>
> I think in general Org is best used as a compositional tool for data
> drawn from elsewhere.
>
> Eric


-- 
Tim Cross

Re: exported contacts problem

[David Masterson]

Eric Abrahamsen <eric@ericabrahamsen.net> writes:

> But Org can be an excellent *interface* to those tools, mostly through
> dynamic blocks. I've started using small sqlite databases to keep track
> of things, and dynamic blocks as sql composers/views, and it works
> great. It's very easy to play with the queries, and this is the first
> time I'm actually starting to feel comfortable with sql.
>
> I think in general Org is best used as a compositional tool for data
> drawn from elsewhere.

What do you think of RDB? It seems to be an old set of Perl scripts
derived from /rdb (an older set of Unix shell scripts) that can be used
to do basic relational database commands on textual database tables.  If
it could be translated into an Elisp package, it would fit the Emacs
model of open source and easily understandable data storage.  It can be
gotten via ftp here:

cdb.netbsd.org/pub/pkgsrc/distfiles/RDB-2.6d.tar.gz

--
David

Re: exported contacts problem

[Eric Abrahamsen]

David Masterson <dsmasterson@outlook.com> writes:

> Eric Abrahamsen <eric@ericabrahamsen.net> writes:
>
>> But Org can be an excellent *interface* to those tools, mostly through
>> dynamic blocks. I've started using small sqlite databases to keep track
>> of things, and dynamic blocks as sql composers/views, and it works
>> great. It's very easy to play with the queries, and this is the first
>> time I'm actually starting to feel comfortable with sql.
>>
>> I think in general Org is best used as a compositional tool for data
>> drawn from elsewhere.
>
> What do you think of RDB? It seems to be an old set of Perl scripts
> derived from /rdb (an older set of Unix shell scripts) that can be used
> to do basic relational database commands on textual database tables.  If
> it could be translated into an Elisp package, it would fit the Emacs
> model of open source and easily understandable data storage.  It can be
> gotten via ftp here:
>
> cdb.netbsd.org/pub/pkgsrc/distfiles/RDB-2.6d.tar.gz

I've never heard of it! But from your description it kind of sounds like
another attempt to use relational databases without actually using
relational databases :) I guess I think databases are one of those
things you should delegate to an external program. Org is already pretty
good at interfacing with them.

Re: exported contacts problem

[David Masterson]

Eric Abrahamsen <eric@ericabrahamsen.net> writes:

> David Masterson <dsmasterson@outlook.com> writes:
>
>> Eric Abrahamsen <eric@ericabrahamsen.net> writes:
>>
>>> But Org can be an excellent *interface* to those tools, mostly through
>>> dynamic blocks. I've started using small sqlite databases to keep track
>>> of things, and dynamic blocks as sql composers/views, and it works
>>> great. It's very easy to play with the queries, and this is the first
>>> time I'm actually starting to feel comfortable with sql.
>>>
>>> I think in general Org is best used as a compositional tool for data
>>> drawn from elsewhere.
>>
>> What do you think of RDB? It seems to be an old set of Perl scripts
>> derived from /rdb (an older set of Unix shell scripts) that can be used
>> to do basic relational database commands on textual database tables.  If
>> it could be translated into an Elisp package, it would fit the Emacs
>> model of open source and easily understandable data storage.  It can be
>> gotten via ftp here:
>>
>> cdb.netbsd.org/pub/pkgsrc/distfiles/RDB-2.6d.tar.gz
>
> I've never heard of it! But from your description it kind of sounds like
> another attempt to use relational databases without actually using
> relational databases :) I guess I think databases are one of those
> things you should delegate to an external program. Org is already pretty
> good at interfacing with them.

The interesting point is that it was originally done as a series of
shell scripts (in /rdb) and now is a series of Perl scripts (in RDB)
that implement the key features of relational functions using textual
tables as a relational database system in the Unix sense.  Were I an
Elisp expert (which I'm not), each script could probably be translated
into Elisp and, rather than pipe from stdin to stdout, it could load the
database into an Emacs buffer and then process the result into another
buffer.  *OR*, since RDB is open source, perhaps an Elisp front-end
could be put on it to incorporate it into Org.

Just an idea...
--
David

Re: exported contacts problem

[Jean Louis]

* Eric Abrahamsen <eric@ericabrahamsen.net> [2019-08-03 02:27]:
> Okay, thanks for that run-down, pretty interesting. I've written a
> package called EBDB[1] that's meant to be sort of an update to BBDB, and
> while I think someone's using it with tens of thousands of contacts,
> 192k records would probably exhaust it. It has pluggable data stores,
> however, one of which will (eventually) be a proper external database,
> so I'm always interested in how people are using this stuff.
> 
> Eric
> 
> [1] https://github.com/girzel/ebdb

Not sure if you rely on vCard as some kind of "standard". It is
attempt to globalize the contact keeping, but I don't find it good,
not at all.

It is way too complicated. It tries to encompass everything possible
in one file, file which is probably not related to other files at all.

Only relational database makes sense for complex contact management.

Jean

Re: exported contacts problem

[John Kitchin]

There are a few options for contacts in org-mode that I have tried.

I agree for a lot of contacts (probably more than a hundred or so), then
native org-contacts might be too slow.

In scimax I have tried a few different approaches to deal with this. The
first is all org/elisp that uses a cache to speed up looking up
contacts. See
https://github.com/jkitchin/scimax/blob/master/contacts.el. The gist of
this is you define a list of files that contacts will be in, and as long
as they haven't changed since the cache was built the cache is used, and
if they have changed the cache for that file is rebuilt. For large
files, org parsing is not that fast in this, and I have not spent any
time optimizing this. There are lots of ways to speed it up, but it
hasn't been so slow to need solving yet. I use this
code regularly, but nothing critical depends on it working. The downside
of this is you have to maintain a list of files that serve as contact
sources. I am pretty sure everything in it is compatible with
org-contacts. This method is fine and I have about 5.8K contacts in this
cache. I use this approach very regularly and have made some nice
org-speedkeys for these contacts and other interesting ideas.

The second approach is also related to caching, and uses a sqlite
database as the cache.
https://github.com/jkitchin/scimax/blob/master/org-db.el This approach
uses a hook function to update the db any time the file is updated. I
can access about 7500 contacts pretty quickly this way (evidently I have
a lot of contacts outside the list of files designated as contacts in
the first approach). I don't use this approach as much for contacts as
for searching all headlines in all my org-files, but I built a contacts
feature because I could. This approach could be expanded to a server
based database but so far sqlite has served all my needs, and it has
about 72K headlines indexed into it right now. The main benefit of this
is mostly set it and forget it; contacts get updated as you make them or
update them.

The main benefit of both of these approaches is it lets you keep org as
the primary way of creating contacts, including keeping all manner of
notes associated with them in sub-headings. You can import contacts from
anywhere just by writing code that creates the headings in org-contacts
form. Since there is a cache, you could probably also write directly to
the cache instead of making intermediate org files. The downside in my
opinion is that I consider it acceptable for the cache to mostly work
most of the time and it is not hard for them to get out of sync (and
neither difficult for them to get resynced). For my work, there is very
little consequence if I can't find a contact. You can organize your
contacts as you see fit all over your file system, which is at times
convenient, and at times hard to deal with. Duplicates, for example, are
a challenge to deal with, and sometimes contacts seem to belong in more
than one place. This is a limitation of org at the moment, there is no
way to "transclude" a heading yet.

You can create groups, these are just headings with multiple emails in
the EMAIL property, as well as use todo states and tags on the contacts
to select them with ivy/helm.

I haven't converged on what the best way to do this kind of persistent
caching. I also use it in org-ref for very large (20K) bibtex databases
and it works fine there too.

I also use mu4e, which indexes everything into a xapian database. It has
about 9K contacts in it right now, and indexes about 100K messages. So
far this works for me. It is more complicated to setup than the previous
two options though.

I wouldn't claim any of these can scale to 192K contacts, but it seems
there are paths towards it. It would certainly take an eye and dedicated
effort towards performance though.


Jean Louis <bugs@gnu.support> writes:

> * Jude DaShiell <jdashiel@panix.com> [2019-08-02 17:48]:
>> I have one email message with several .vcf file attachments.  Has orgmode
>> got any tool or tools I can use to import contacts from such a message
>> into an orgmode table?
>
> And by all means, I would never keep contact in Org file, that is for
> short list fine, but for any future planning, contacts shall be in a
> real database sorted by its lists.
>
> A list could be group of people, or account name, or company name, or
> organization, or interest lists.
>
> Neither bbdb nor Org is suitable for any serious collection of
> contacts. I have 192,000+ contacts, and when they are in database and
> I am using PostgreSQL, it gives me most of benefits, I can sort people
> into lists, groups, I can contact them, count interactions, open up
> their files, emails with a fast command, edit their data, add notes,
> send them faxes and SMS, maintain relations.
>
> Jean


--
Professor John Kitchin
Doherty Hall A207F
Department of Chemical Engineering
Carnegie Mellon University
Pittsburgh, PA 15213
412-268-7803
@johnkitchin
http://kitchingroup.cheme.cmu.edu

Re: exported contacts problem

[Tim Cross]

Are you using an emacs based email client like mu4e or gnus? What (if
anything) do you use to manage contact information now i.e. BBDB, Asking as
I am aware that some email clients have support for vcard processing and
some contact managers also have support for vcard (i.e. bbdb), so
initial processing may be easier from witin the mail client (new
versions of mu4e are moving to use gnus as the message view mode, which
has some advantced functions for dealing with vcard and ical
attachments).

There is also an org-vcard module, but last time I tried using this, I
encountered some problems (can't remember what they were now - it was a
while back, but it may have only support older vcard versions).

Jude DaShiell <jdashiel@panix.com> writes:

> I have one email message with several .vcf file attachments.  Has orgmode
> got any tool or tools I can use to import contacts from such a message
> into an orgmode table?


-- 
Tim Cross

Re: exported contacts problem

[Jude DaShiell]

I'm using nothing for contacts management by now.  I don't use moo or
gnus since I've had limited success setting up either one of them.  I
may take anotheeer throw at gnus since I have a gmail and panix account
and both accounts have usenet newsgroups capability.

On Sat, 3 Aug 2019, Tim Cross wrote:

> Date: Fri, 2 Aug 2019 16:33:37
> From: Tim Cross <theophilusx@gmail.com>
> To: emacs-orgmode@gnu.org
> Subject: Re: [O] exported contacts problem
>
>
> Are you using an emacs based email client like mu4e or gnus? What (if
> anything) do you use to manage contact information now i.e. BBDB, Asking as
> I am aware that some email clients have support for vcard processing and
> some contact managers also have support for vcard (i.e. bbdb), so
> initial processing may be easier from witin the mail client (new
> versions of mu4e are moving to use gnus as the message view mode, which
> has some advantced functions for dealing with vcard and ical
> attachments).
>
> There is also an org-vcard module, but last time I tried using this, I
> encountered some problems (can't remember what they were now - it was a
> while back, but it may have only support older vcard versions).
>
> Jude DaShiell <jdashiel@panix.com> writes:
>
> > I have one email message with several .vcf file attachments.  Has orgmode
> > got any tool or tools I can use to import contacts from such a message
> > into an orgmode table?
>
>
>

-- 

Re: exported contacts problem

[Tim Cross]

I tried gnus some time ago and use to use it when I read
newsgroups. While I find it to be an extremely powerful and capable
package, I never got comfortable with using it for mail. I once used VM
and then mew, but now mu4e, which I think is fantastic. Part of what I
like about it is the nice workflow I have with org-mode.

I've not yet worried too much about contact management though. Maybe I
don't have any friends! I do find the combination of org-mode, mu4e and
mbsync works well for my private and work email accounts (gmail, outlook
and exchange or office365). As I work on both Linux and OSX, I don't
worry about contacts and calendar invites too much as OSX just takes
care of that for me (I have apple mail configured for my accounts, but
rarely every use it). Mu4e has its own contact database built in, but it
can also be configured to use BBDB (though I've not done that). 

I did need to tweak mu4e a little to work well with emacspeak.

Tim

Jude DaShiell <jdashiel@panix.com> writes:

> I'm using nothing for contacts management by now.  I don't use moo or
> gnus since I've had limited success setting up either one of them.  I
> may take anotheeer throw at gnus since I have a gmail and panix account
> and both accounts have usenet newsgroups capability.
>
> On Sat, 3 Aug 2019, Tim Cross wrote:
>
>> Date: Fri, 2 Aug 2019 16:33:37
>> From: Tim Cross <theophilusx@gmail.com>
>> To: emacs-orgmode@gnu.org
>> Subject: Re: [O] exported contacts problem
>>
>>
>> Are you using an emacs based email client like mu4e or gnus? What (if
>> anything) do you use to manage contact information now i.e. BBDB, Asking as
>> I am aware that some email clients have support for vcard processing and
>> some contact managers also have support for vcard (i.e. bbdb), so
>> initial processing may be easier from witin the mail client (new
>> versions of mu4e are moving to use gnus as the message view mode, which
>> has some advantced functions for dealing with vcard and ical
>> attachments).
>>
>> There is also an org-vcard module, but last time I tried using this, I
>> encountered some problems (can't remember what they were now - it was a
>> while back, but it may have only support older vcard versions).
>>
>> Jude DaShiell <jdashiel@panix.com> writes:
>>
>> > I have one email message with several .vcf file attachments.  Has orgmode
>> > got any tool or tools I can use to import contacts from such a message
>> > into an orgmode table?
>>
>>
>>


-- 
Tim Cross

Re: exported contacts problem

[Jean Louis]

* Tim Cross <theophilusx@gmail.com> [2019-08-02 23:26]:
> I tried gnus some time ago and use to use it when I read
> newsgroups. While I find it to be an extremely powerful and capable
> package, I never got comfortable with using it for mail. I once used VM
> and then mew, but now mu4e, which I think is fantastic. Part of what I
> like about it is the nice workflow I have with org-mode.

mu4e is good maybe for small usage, it does not scale up[1]. With
47783 maildir folders it cannot cope. It is unusable. 

A concept to read maildirs from a database is not good one. Database
index is for searching. Maildirs shall be read directly based upon the
specification.

Package `maildir' is much faster to read maildirs.

A search with `mu' can symlink found emails into specific folder and
`maildir' package can quickly read such folder. That is about the `mu'
search.

Nothing beats `mutt'[2] for reading emails.

Jean

Footnotes:
[1]  https://github.com/djcb/mu/issues/1440
[2]  https://www.mutt.org

Re: exported contacts problem

[Tim Cross]

Maybe, but those are unusually high maildir numbers IMO. I have approx
8Gb of email messages, but only have a handful of maildirs - this is
primarily why I prefer mu4e and org as a powerful mail workflow.

I use to use the old model of sorting email into many different folders,
but it was just too time consuming. My inbox was always large and I
spent hours each week just sorting and refiling my messages.

Now I just have a couple of maildirs for each account - inbox, archived,
sent, draft and thats it. For messages I need to track, I create an
org-todo with a mu4e link and if I need to find an old message, mu
search works fine.

Now my inbox has approx 20 messages and I spend no time sorting and
refiling messages.

Mutt is OK, but does not integrate well with emacs. I live in emacs, so
I want my mail reader there as well.

Jean Louis <bugs@gnu.support> writes:

> * Tim Cross <theophilusx@gmail.com> [2019-08-02 23:26]:
>> I tried gnus some time ago and use to use it when I read
>> newsgroups. While I find it to be an extremely powerful and capable
>> package, I never got comfortable with using it for mail. I once used VM
>> and then mew, but now mu4e, which I think is fantastic. Part of what I
>> like about it is the nice workflow I have with org-mode.
>
> mu4e is good maybe for small usage, it does not scale up[1]. With
> 47783 maildir folders it cannot cope. It is unusable. 
>
> A concept to read maildirs from a database is not good one. Database
> index is for searching. Maildirs shall be read directly based upon the
> specification.
>
> Package `maildir' is much faster to read maildirs.
>
> A search with `mu' can symlink found emails into specific folder and
> `maildir' package can quickly read such folder. That is about the `mu'
> search.
>
> Nothing beats `mutt'[2] for reading emails.
>
> Jean
>
> Footnotes:
> [1]  https://github.com/djcb/mu/issues/1440
> [2]  https://www.mutt.org


-- 
Tim Cross

Re: exported contacts problem

[Jean Louis]

* Tim Cross <theophilusx@gmail.com> [2019-08-03 00:07]:
> Maybe, but those are unusually high maildir numbers IMO.

For personal purposes surely so.

Imagine when you run a campaign on some online social network, and pay
US $76 and within 23 hours there are 1,300 people in the
database. Then follows up to contact each of them, first
automatically, then following up personally, reviewing their CVs,
calling them and so on.

And then you pay US $1000 for marketing and get another 7000 potential
leads, they go into database, and among them there is US $100,000
generated over several years, and you follow up each of them.

And so the database increases over years.

Any type of contact management be it on paper or in computer shall be
(almost) indefinitely scalable. Organizations could keep their files
before 50 years on paper, in paper files.

> I have approx 8Gb of email messages, but only have a handful of
> maildirs - this is primarily why I prefer mu4e and org as a powerful
> mail workflow.

I have just 4.6 GB.

I have tried my best using mu4e, I cannot see how it is powerful, it
is slow, not efficient comparing to mutt.

mu tools are for indexing and searching emails. But it lacks direct
maildir support.

My setup is such that I am putting conversations for each email
address into their corresponding folder, for example:

~/Maildir/person-one@example.com
~/Maildir/person-two@example.com

then it becomes very easy to access such conversation related to one
person. I do use mu tools to index and search emails, but mu4e is not
fast, and is not usable.

mu alone is usable, it can search emails and link it to the folder.

There is `maildir' package, this one is not as nice, but it can
quickly read maildir folders.

So it could be trivial to make mu query from Emacs, to provide links
to certain folder and to use some functions from that package to read
emails in the Maildir folder with indexed found messages.

(require 'maildir)

(defun mu-query-maildir-list (query)
  (interactive "sQuery: ")
  (let* ((maildir "~/Maildir/search")
	 (command (format "/usr/local/bin/mu find --clearlinks --format=links --linksdir=%s \"%s\"" maildir query)))
    (message (format "Preparing the maildir for query: %s" query))
    (shell-command command)
    (maildir-list maildir)))

The above is replacement for mu4e search of email messages. Not so
complex, but it works.

> I use to use the old model of sorting email into many different folders,
> but it was just too time consuming. My inbox was always large and I
> spent hours each week just sorting and refiling my messages.

I cannot know what made it technically time consuming.

I have such setup that I just press "s" and email is saved into the
folder by its email address. If the email address is
person@example.com it is saved in ~/Maildir/person@example.com

To save email is very quick, probably less than half a second.

I would like to understand how your inbox got larger and larger if you
were saving emails into separate folders. My inbox is just as large as
unread and not handled emails. 

> Now I just have a couple of maildirs for each account - inbox, archived,
> sent, draft and thats it.

If it works for you, it works. But with 8 gigabytes of email, that is
hard to handle.

> For messages I need to track, I create an org-todo with a mu4e link
> and if I need to find an old message, mu search works fine.

That is good to hyperlink from Org to mu4e -- but remember, mu4e is
database based, not maildir based. It indexes maildirs. There will
always be some lag in using mu4e.

If emails are sorted by email address, then you can link to the
person's conversation straight and without any database in the
middle. 

> Mutt is OK, but does not integrate well with emacs.

I would rather say that ansi-term in Emacs is not the best. Mutt
integrates inside ansi-term but resizing windows or face sizes may
give some problems.

> I live in emacs, so I want my mail reader there as well.

I understand as I would like that too.

I think that good solution for efficient email handling in Emacs is to
directly access Maildirs.

Mbox and other type of email archive files or formats are not best and
can get corrupted.

Jean

Re: exported contacts problem

[Jean Louis]

I would recommend the software named `abook'[1] as it can convert between
the vcard format and CSV format, LDIF, and other formats handy to read
them into the Emacs Lisp structure. It is available in many GNU/Linux
system distributions[2].

Jean

* Tim Cross <theophilusx@gmail.com> [2019-08-02 22:34]:
> 
> Are you using an emacs based email client like mu4e or gnus? What (if
> anything) do you use to manage contact information now i.e. BBDB, Asking as
> I am aware that some email clients have support for vcard processing and
> some contact managers also have support for vcard (i.e. bbdb), so
> initial processing may be easier from witin the mail client (new
> versions of mu4e are moving to use gnus as the message view mode, which
> has some advantced functions for dealing with vcard and ical
> attachments).
> 
> There is also an org-vcard module, but last time I tried using this, I
> encountered some problems (can't remember what they were now - it was a
> while back, but it may have only support older vcard versions).
> 
> Jude DaShiell <jdashiel@panix.com> writes:
> 
> > I have one email message with several .vcf file attachments.  Has orgmode
> > got any tool or tools I can use to import contacts from such a message
> > into an orgmode table?

Footnotes:
[1]  http://abook.sourceforge.net/

[2]  https://www.gnu.org/distros/free-distros.html