Gmail to SQLite

• Gmailで受信したメールデータを体系的に管理・分析するために、SQLiteデータベースへ変換するPythonベースのコマンドラインツール
• オープンソースとして開発されており、個人でも企業でも自由に拡張・カスタマイズ可能
• 一般的なメール管理と比べて、必要な検索語や条件による高速なクエリと詳細分析が可能
• データ移行が容易で、大量のメールの効率的なバックアップとアーカイブに優れている
• 同種のオープンソースと比べて依存関係が少なく、シンプルな設定や自動インデックス作成など、構成のしやすさに優れている

[GN⁺]

Hacker Newsのコメント

• 気になるのは、なぜスキーマで特定のヘッダーを別に切り出しているのかという点 たとえば recipients、subject、sender のようなフィールドも、全部 headers という1つの json 項目にまとめて入れられるのに、あえて分離する理由が気になる もし性能のためなら、headers を json blob のままにしておいて、必要なフィールドだけ生成列として取り出して使うこともできる 自分としては、こうすれば ALTER TABLE でユーザーが必要なクエリごとにインデックス付き生成列を自由に追加できるので、本当に強力なモデルだと思う たとえば dkim status のクエリが必要なら ALTER TABLE で追加して、インデックスも簡単に作れる 好きなようにフィールドを拡張できるので、いろいろな用途に向いている

  • 実際のところ、生成列すら不要 SQLite は式に対して直接インデックスを作れる なので、たとえば subject のインデックスを json_extract でそのまま作ればよくて、このインデックスを必要なときにクエリで使える 自分は、こういう形でインデックスだけ別に作ってビューで使うほうが、メインテーブルを ALTER で変更するより有用だと感じる

  • 単発のクエリのためだけにインデックスを追加するのは、あまり良い習慣ではない気がする 普通は、今後確実に一貫して使う列だけをきちんと別に抜き出しておくほうを好む。特にメールヘッダーのように構造が安定している場合はなおさら ヘッダーを1つの json に押し込めれば後でスキーマを変えやすいかもしれないが、結局は書き込み時の苦労を読み取りクエリ側でまたやることになるし、場合によっては静かに失敗するケースまで許してしまう

  • 自分も Postgres で似たパターンをよく使う まず本当に必要だと分かっているフィールドだけ列に出して、追加メタデータは json 列にまとめて入れる 2か月も経てば、実際に必要なフィールドを json から埋め直したり、API の互換性を保つように変更したり、ビューを作ったりと自由に調整できる こうすると、最初に Mongo やファイルシステムなどへ何も考えず突っ込んで後で後悔するような成長痛を避けるのにとても役立った

  • dkim 列を NOT NULL にしていたようだけど、メールに Dkim-Signature ヘッダー自体がまったく無い場合はどう処理されるのか気になる

• 最近、自分のアプリで Gmail 連携を試してみた この過程に本当に多くの時間を費やしたが、結局 Gmail サポートは断念した Gmail to SQLite では認証情報の手続きが6段階で終わるというが、実際にはそうではなかった 6段階を終えると、Google がアプリが publish（公開）されていないといった内容をまた通知してきて、publish すると今度は Workspace ユーザーではないので内部用アプリにはできないと知らせてきた 外部アプリに変えると、さらに別の認証手続き（ドメイン・住所・詳細情報の要求、権限利用の理由、動画による説明、審査にかかる時間など）を追加で求められる Google が要求するこういう複雑な手順を一般ユーザーにやらせるのはあまりにもひどいと思う 実体験して困惑した

  • 昔ながらに IMAP 用のアプリパスワードを発行して使えばいい Google が求める面倒な手順は避けたほうがいい

  • Google で API キーを1つ取得するために踏まされる手順が本当に狂気じみて面倒 いったいなぜここまでこうなっているのか、理由を知っている人がいるのか気になる

• 数年前に、Gmail のような大容量メールを可視化するツールを作ったことがある: https://github.com/terhechte/postsack

  • これは本当にすごい ディスク使用量可視化ツールのような感じだけれど、メールのボリュームそのものに焦点を当てているように見える もし送信者ごとに、誰が自分のストレージを最も多く使っているか確認できるサイズ表示オプションがあるのか気になる ちなみに、ウェブサイトの SSL 証明書は期限切れになっている

  • 面白そう readme の gmvault リンクがもう動かないようだけど、正しいリンクはこれなのか https://github.com/gaubert/gmvault 気になる

  • 本当に興味深そう 自分も qdirstat で似たものを DIY で作ってみたことがあるが、この場合はメールのフォルダー構成や日付順に整理する必要があり、さまざまな基準で再構成するのが難しい ちなみに qdirstat のキャッシュファイルは本当に作りやすいので、複数ファイルのようなデータを可視化するときに便利に活用できる

• もはやアプリパスワードだけでログインできず、oauth クライアント登録など複雑な手順を踏まなければならないのが本当に残念 自分のメールなのに、Google に自分でアクセスできるオープンスタンダードを奪われてしまったように感じる

  • 無料の Gmail アドレスで受けるスパムは、フリーランス向けの有料アドレスより圧倒的に多いし、Gmail サーバー発のスパムも自分の非 Gmail アカウントのほうによく届く とくにフリーランス用メールが相手のメールシステムでしょっちゅうスパム扱いされるのを感じるようになってから、ますます Google エコシステムから離れたくなっている ただ、Google 依存のルーティンからどう抜け出せばいいのか見当がつかず、負担に感じる

  • よく分からない アプリパスワードさえあれば IMAP へのフルアクセス権を得られる

  • アプリ専用パスワードはオープンスタンダードだと見なし、oauth はオープンスタンダードだと見なさない理由が気になる

• 本当にすばらしい 新機能の要望: メール本文から配信停止リンクを抽出して、よく届く送信元ごとに簡単に配信停止できる機能があるとうれしい

• 自分も昨日まったく同じことをやってみた。ドメインごとに受信メール数を一覧化したかったから コード品質は低いけれど、ここにある: https://github.com/hugoferreira/gmail-sqlite-db

  • 自分もまったく同じく、ドメイン・送信者ごとにグルーピングしようとしてみた

• こんなことができるとは知らなかった、ありがとう

• Archiveopteryx（Postgres ベースの IMAP サーバー）を少し思い出した: https://github.com/aox/aox AOX のスキーマはいつ見てもとても良さそうだったが、実際にきちんと使ってみる機会がまだなかった 主にメール分析や検索用途で使ってみたかった

• ここで帯域コストがどれくらいかかるのか気になる 自分は Gmail の容量だけで 40GB を超えているので、このツールを使うと転送量で請求が来るのか気になる もちろん Google Takeout（無料でメール全体をダウンロード）を使えばファイルをパースするだけなので解決は簡単だが それでも、このツールのほうがずっと速く簡単に始められそう

• これは "imap to sqlite" のような名前であるべきではないかと思う なぜ特定のメールプロバイダーだけに限定するのか気になる

  • 理由は、このツールが Gmail に特化しているから OAuth と Google の API アクセスを活用しているため IMAP 方式ははるかに複雑で遅く、Google の帯域制限にも引っかかる

  • 参考までに、何年ものあいだ自分の gmail アカウントのバックアップを imap で試みてきたが（しかも gmail 向け専用ツールでも）、一度も成功したことがない うまく動く同期ツールでも1か月ほど回ると、結局どこかのメールを取得できずに止まってしまった おそらくコールドストレージ状態だからではないかと思う だから Google 固有 API を使えばもっと良いのではないかと考える 今では Google Takeout から mbox を直接受け取れて、問題なく素早く完全バックアップできるので本当に助かっている（半日ほどで完了） 欠点は、継続的な自動更新ができないこと ちなみに、自分はすでに別のメールサービス（Infomaniak）へ移行していて、あらかじめ独自ドメインを持っていたのは本当に正解だったと思う