Gmail to SQLite

I'm curious as to why you choose to break out specific headers in the schema.

For example, you have recipients, subject, and sender as JSON fields, when you could have just a headers field with all of them, and even add the rest of the headers in the message.

If it's performance related, you can still have headers as a single json blob and then use generated columns for the specific fields.

For example

  CREATE TABLE IF NOT EXISTS "messages" (
    "id" INTEGER NOT NULL PRIMARY KEY, -- internal id
    "message_id" TEXT NOT NULL, -- Gmail message id
    "thread_id" TEXT NOT NULL, -- Gmail thread id
    "headers" JSON NOT NULL, -- JSON object of { "header": value },
    "subject" TEXT GENERATED ALWAYS AS (json_extract("headers", '$.Subject')) VIRTUAL NOT NULL)
    ...
  );
  CREATE INDEX subjectidx on messages(subject);

I've found this model really powerful, as it allows users to just alter table to add indexed generated columns as they need for their specific queries. For example, if I wanted to query dkim status, it's as simple as

  ALTER TABLE messages ADD dkim TEXT GENERATED ALWAYS AS (json_extract("headers", '$."Dkim-Signature"')) VIRTUAL NOT NULL);
  CREATE INDEX dkimidx on messages(dkim);
  SELECT dkim, COUNT(0) FROM messages GROUP BY dkim;

or whatever you want.

  CREATE INDEX subjectidx ON messages(json_extract(headers, '$.Subject'))

  Error: stepping, NOT NULL constraint failed: messages.dkim (19)

I build something to visualize huge amounts of email (such as from Gmail) some years ago:

https://github.com/terhechte/postsack

I really lament that you cannot sign in even with an application specific password any more and you need to get an oauth client and go through an oauth flow. It’s my email, but Google takes away an open standard even for myself to access it.

Shouldn't this be "imap to sqlite" or something? Why tie it to one specific email provider?

What's the best open-source GMail backup software that exists? Someone has setup something like that? (also archiving attachments, etc)

Would be nice to enable fulltext search as well

this is great if only there was a tool for whatsapp to sqlite it would make my data so much more useful

I would have preferred a script that parses the mail backup Google sends you.

I think it's a big eml file.

Would love a comparison to gbackup-rs[0].

To me having to install a tool through Python is a show-stopper.

[0] https://github.com/djipko/gbackup-rs

Let us stop using GMail:

* Google collects vast amounts of personal data, specifically through receiving all of your email and analyizing it.

* It builds elaborate user profiles and uses them to target you with ads designed to better influence you.

* Its hold on information (from different sources) has made it excessively powerful economically, and thus also politically.

* Google/Alphabet has long started to affect legislation, including through direct registered lobbying: ~15 Million USD in 2024 (opensecrets.org).

* It has been known to pass, and likely still passes, the information it collects - including copies of your email correspondence - on to the US government (Edward Snowden leaks).

and finally:

* There are multiple email providers, many of them quite good - both for pay and gratis. Naturally most of the gratis ones have their own interests, but nothing like Google.