open-source project to ingest email into a databae

Question

Any recommended open-source project to continuously ingest e-mail (iMAP, POP) into a database? The specific database engine is not important but prefer JSON.

jll29 · Accepted Answer

How about starting with something like this:

  CREATE TABLE email(
    id       INTEGER AUTOINCREMENT NOT NULL PRIMARY KEY,
    s_id     INTEGER,
    r_id     INTEGER,
    subject  VARCHAR,
    header   VARCHAR,
    body     VARCHAR
  );

  CREATE TABLE contacts(
    id      INTEGER AUTOINCREMENT NOT NULL PRIMARY KEY,
    email.  VARCHAR NOT NULL,
    name    VARCHAR
  );

  CREATE TABLE attachments(
    id       INTEGER AUTOINCREMENT NOT NULL PRIMARY KEY,
    email_id INTEGER NOT NULL,
    attachm  BLOG NOT NULL
  );

in SQLite? (But in my opinion, storing in e.g. Lucene as full-text indexed text collection makes more sense for fast & flexible search.)

EDIT: Python, PHP and Perl have libraries to read MBOX format and write SQLite easily (= elss than a screen of code).

beardyw · Answer

I've been playing with emails just recently and what struck me most was the hellish inconsistency of the emails themselves. You will find "body" is a rabbit hole all of its own. Good luck!