Ask HN: open-source project to ingest email into a databae | Hacker News Reader

Ask HN: open-source project to ingest email into a databae

2 pointsnodesocket3 comments

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.

View on HN

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

The schema is not really the challenge, more about constantly polling POP and iMAP and parsing emails.

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!

Reply on news.ycombinator.com