GmailをSQLiteに！
引用元：https://news.ycombinator.com/item?id=43943236
なんでヘッダーをバラバラのカラムにしたの？全部まとめてheadersっていうJSONカラムにして、生成列とか式インデックスを使えばパフォーマンスも大丈夫だし、あとから必要なヘッダーだけインデックス付きで追加できて便利だよって提案。具体的なSQL例もあるよ。
てかさ、生成列いらないかも。SQLiteは式インデックスもできるから、json_extract(headers, ’$.Subject’)みたいに式に直接インデックス貼れるんだ。
CREATE INDEX subjectidx ON messages(json_extract(headers, ’$.Subject’))
これでその式を参照するクエリでインデックス使ってくれるよ。俺は生成列じゃなくて、こういうインデックスと、その式を使うVIEWを組み合わせるのが好きだな。テーブルの中身を変えなくて済むし。
あと、VIEWとインデックスならデータ自体は変わらないから、https://github.com/fsaintjacques/recordlite みたいなツールでスキーマ管理を自動化できるらしいよ。
これいいね！結構好きだな〜。
いやー、めっちゃタイミングいい情報だわ。ちょうどこういうやり方探してたんだよね。サンキュー！
一時的なクエリのためにインデックス追加するのってあんまり良くなくない？俺はいつも使うって分かってるカラムは、特にメールヘッダーみたいに安定してるやつは、分けて置く方が好きだな。
JSONカラムだとスキーマ変更は楽だけど、それって書き込みの面倒を読み込みの面倒に変えてるだけだし、こっそり失敗しちゃう可能性もあるしね。
俺もPostgreSQLで同じようなやり方よく使うんだ。
最初は必要なフィールドだけ決めてテーブル作って、それ以外のメタデータは全部JSONカラムに入れとく。で、数ヶ月経って「あ、このフィールド必要だわ」ってなったら、JSONから移したりAPIで更新したりVIEW作ったりする。
これは「全部mongoにぶち込め」とか「ファイルシステムに置いとけ」みたいなやり方と違って、あとから柔軟に対応できるし、そんなにコストもかからないからマジ助かる。
dkimカラムをNOT NULLにしてるけど、メールにDkim-Signatureヘッダーがない場合ってどうなるの？
多分、
Error: stepping, NOT NULL constraint failed: messages.dkim (19)
みたいなエラーになると思う。MySQLと違って、SQLiteはJSONのnull値にはSQLのNULLを返すみたいだからね。
うん、hun3が言った通りエラーになるね。急いで作った例だから間違ってたよ。閉じカッコも一個足りなかったのに今気づいた。やっちまった〜。
json_extractみたいな表現に直接インデックス作ることもできるよ
何年か前にGmailみたいな大量のメールを可視化するもの作ったことあるよ
Githubのリンク貼っとくね
https://github.com/terhechte/postsack
これすごいね
ディスク使用量可視化ツールみたいだけど、ディスク使用量よりメールの総量に焦点を当ててるみたい
サイズオプションもある
どの送信者が一番容量食ってるか知りたいな
あと、君のウェブサイトのSSL証明書が期限切れだよ
今はないんだ
でも簡単に追加できると思うよ
しばらくツール更新してないんだ
証明書教えてくれてありがとう
面白そうだね
readmeにあるgmvaultへのリンクが無効になってるみたいだけど、これであってる？
https://github.com/gaubert/gmvault
サンキュー
＞ readmeのgmvaultへのリンクは無効になってるみたいだけど、これであってる？https://github.com/gaubert/gmvault
面白いことに、そのページが指してるgmvault.orgドメインはGoDaddyの駐車場サイトだよ
あと、ソースファイルじゃない2つを除いて10年以上更新されてない
これ面白そうだね
前にもqdirstatで似たようなのをDIYしたことあるけど、あれは日付フォルダみたいにメールを特定の方式で整理しないといけないし、違う基準で再分類できないんだ
一方で、qdirstatの”キャッシュ”ファイルは生成がすごく簡単だから、たくさんのファイルみたいなものの可視化に使えるよ
アプリケーション固有パスワードでもサインインできなくなって、oauthクライアント取ってoauthフロー通さないといけないの、マジで残念だわ
自分のメールなのに、Googleが自分自身でアクセスするためのオープンスタンダードまで奪っちゃうんだから
無料のGmailアドレスで受け取るスパムの量（有料のフリーランス用と比べて）、あとGmailサーバーからGmailじゃない自分のアカウントで受け取るスパムの量考えると、どんどんdegooglingしたくなるわ
特に、フリーランスのメールが受信側でスパム扱いされてるって情報が増えてるから
でも、Googleエコシステムの習慣からどうやって抜け出すか、よく分かんないんだよね
気が重い感じ
少しずつやってるよ。Postfixで*@immibis.comのメール受け取れるようにした。まずは重要じゃないメーリングリストとかに使ってて、今は新しいアカウント登録にデフォルトで使ってる。失敗しても大したことないことからね。でも銀行口座とかには絶対gmail使うな。向こうは法人だからこっちのサーバーに問題あると対応してくれないだろうし。
Googleから99%脱却できれば十分じゃないかな。銀行アプリはAndroidだしね。Youtubeもまだ代替できてないし。Mozillaがプライバシー嫌いって言い出した後、FirefoxベースのZen Browserにブラウザは変えてる途中だよ。
あと、ステップ０として自分のメールドメインを買うのがいいと思うよ。
思ってるより難しくないと思うよ。俺はhttps://mailwip.comにお金払ってる。そこの創業者が設定を手伝ってくれたんだ。結局のところ、比較的簡単だったよ。今も払い続けてるのは、彼の仕事に感謝してるし、俺のメールは完璧だし、彼らが提供するログが気に入ってるからさ。
俺も数年前から自分でメールをホスティングしてるけど、今のところ配信の問題はないな。ただDNSの設定を全部ちゃんとやることと、DOとかAWSみたいな評判の悪いIP範囲は避けるようにすることだよ。
有名なMXホストを使えばこんなことにはならないよ。
ごめん、なんでアプリ固有のパスワードがオープンスタンダードで、oauthは違うって思うの？
POP3とかIMAPは、それに対応してるどんなクライアントでも動くよね。OAuthは全然違うんだ。俺が作ったOAuth連携は、いつもちょっとしたカスタム開発が必要な感じだった。
あと、自分のデータにアクセスするためのトークンを取るOAuthフローも普通に最悪だよ。自分のトークンを得るためだけに、一時的なウェブアプリ立ち上げて大量のリダイレクトを処理しないといけなかったことさえあるんだから！
プロキシ作ったらしく、IMAP/POP/SMTP そのままで OAuth 知らなくていいらしいよ。これでめっちゃ簡単になるってさ。ここで見れるって： https://github.com/simonrob/email-oauth2-proxy
OAuth 知らなくていいって言うけど、client_id/client_secret 取るのがそもそも大変なんだよね。今はアカウントごとに whitelist するか Google 認証通さないといけないし。昔は誰でもOKな client_id もあったんだけど、もう過去の話。
それってさ、プロトコルどっちが好きかって話で、オープンかどうかとは違うんじゃない？正直そっちの話だったらもっと分かりやすかったし、混乱しなかったと思うよ。
違いをはっきりさせとくね。IMAP は RFC標準プロトコルで Google の許可いらない。OAuth は標準じゃないしプロバイダーごとに違う（Outlook と Google とか）。それに Google の許可（client_id 取得とか審査）が絶対いるんだ。開発用アプリは100人までで、それもユーザーをちゃんと whitelist しないといけないよ。
もっとコメントを表示（1）
それは IETF 標準にこだわる話で、俺は違う考えだけど、それは置いといて「オープンさ」とは別次元だよ。プロトコルって公開されてて広く使われてればオープンなんだ。今回の設定（OAuth/XOAUTH2かな？）はそうじゃない？
Google に App password はもうないよ。
それ100%違うよ。App password で機能作ったし、毎日使ってるもん。
うちの Google Workspace だと App password がユーザー設定に出ないんだ。ドキュメントも非推奨って書いてあるし、IMAP docs は OAuth2 だけサポートってなってる。でも、直接ページ行けばまだ使えるかもって見たから、今から試してみるね。
Marco（https://marcoapp.io）ではApp Passwordsを使ってるよ、メールのスコープで特権的なOAuthアクセスを得るのが不必要なくらい厳しいプロセスだからね。
ここにユーザー向けにリンクしてるサポート記事があるよ：https://support.google.com/accounts/answer/185833
でもWorkspaceだとちょっと違うんだ。管理者がApp Passwordsを有効にする必要があるんだよ。
最近、自分のアプリ（[0]）にGmailを連携させようとしたんだけど、マジで時間をかけすぎたわ。Gmailのサポートはやらないことに決めたよ。
“Gmail to SQLite”の記事には認証を動かすのに6ステップって書いてあるけど、僕の場合は全然違った。
6ステップの後、
– Googleがアプリが公開されてないって言うから公開した
– Googleは自分がworkspaceユーザーじゃないからアプリはinternalにできないって言った
– 外部アプリの場合、
– アプリが検証されるまで使えないって言った
– 検証ではドメイン、住所、他の詳細を聞かれた
– スコープの正当化を聞かれた
– アプリがどう使われるか説明するビデオを要求された
– 提供したデータの検証に時間がかかると言われた
全部、設定の迷宮みたいだよ。ユーザーにGoogleが要求するハードルを越えさせるなんて、単純に無理すぎる。
リンク：
[0] https://github.com/rumca-js/Django-link-archive
GoogleがAPIキー取るのに人々に飛び越えさせるハードルは、マジでクレイジーだね。
なんでこんなにひどいの？誰か理由知ってる？
多分、誰かのメールアカウントにAPIアクセスできたらゲームオーバーだからだよ。
そして人々はバカだから、怪しいアプリにもYesってクリックしちゃうだろうね。そしたらGoogleが守ってくれなかったって非難されるんだよ。
そうしないとさ、大量の人が匿名で、めちゃくちゃ広いスコープでAPIキーを作っちゃうからだよ。
それが必然的に悪用された時、Googleのイメージが傷つく結果になるからね。
普通の昔ながらのIMAPをApp Passwordで使えばいいじゃん。
あいつらのハードルを飛び越える必要ないって。
毎年IMAPオプション（”app passwords”ってやつ）は、設定のどんどん奥深くに埋められていくんだよね。
本当にそうだよ。今有効にするの、かなり面倒になった。2FAを含む複数の要件があるんだ。
最高のオープンソースのGmailバックアップソフトって何かな？誰かセットアップしたことある？（添付ファイルもまるっとアーカイブするやつとか）
https://github.com/GAM-team/got-your-back
– オープンソース
– レジューム機能（だからバックアップ/リストアは最終的に完了するよ）
もう一つのおすすめ：https://www.mailstore.com/en/products/mailstore-home/
– オープンソースじゃない
– インデックス付きのGUI：ローカルでメール検索するのに良い
– レジュームはバックアップのみ（だから大きなリストアはだいたい失敗するよ）
俺，長いこと：https://github.com/gaubert/gmvault使ってて調子いいんだ．でもGYBは活発にメンテされてるみたいだから，乗り換えるべきかな．
ちなみに，前にgmvault使ってたんだけど（個別の圧縮解除とか，squashfsでまとめるのが面倒だった），アーカイブ検索が不便だったから，https://github.com/karteum/gmvaultdbっての作ったよ．ローカルのsqlite DBに変換できるんだ．（個人用だしシンプルにしたけど，大きくなったらスキーマ変えるかも．添付ファイルはDBじゃなくてファイルシステムに直接出したよ）
あと，https://takeout.google.com/のmboxも処理できるけど，Takeoutは古いエンコーディングのバグで情報失うことあるから，gmvaultかimapでのバックアップの方がいいかもね．
これが求めてるものと完全に一緒じゃないけど，GoogleにはTakeoutってサービスがあるよ．Gmail含め全部のデータをダウンロードできるんだ．数ヶ月ごとにこれやってローカルにバックアップしてるよ．確かgzippedなmboxファイルでくるはず．
そうなんだろうけど，俺は”continuously”やりたいんだ．心配してるのは，Googleが何か適当な理由でアカウントをロックすることだからさ．
IMAPクライアントを使う手もあるよ．オフライン／ダウンロードモードに設定すれば，全部ダウンロードしてローカルに保存できるんだ．Evolutionだと”offline mode”って名前だったと思うけど，Thunderbirdとか他のクライアントはわかんないな．
これって”imap to sqlite”とかにするべきじゃない？なんで特定のメールプロバイダに結びつけるの？
だってGmailに特化してるんだもん．OAuthとかAPIアクセス使ってるでしょ．IMAPはもっと難しいし，遅いし，Googleの帯域制限に引っかかるからさ．
Google TakeoutでGmailからmboxをエクスポートするの，けっこう速いよ．
え，何？あれ予約しないといけないし，文字通り3日待たないと始まらないんだよ．それからダウンロードできるようになるまで一日かかることもあるんだ．全然速くないってば．
そんなに待ったことないよ．いつも20分くらいでダウンロードできるし，データは15 GBだよ．
そんな経験ないなー．何かスケジュールする必要もないし，ダウンロードできるようになるまでせいぜい1時間くらいじゃない？でも，壊れてたり需要が高かったりした時もあったのかもね？
ちなみに，俺は何年もimapでGmailバックアップ試したけどダメだったんだよね．ツールも駄目．多分古すぎるメールは取れないとか？だからGoogleのAPIの方がマシなのかも（知らんけど）．まあ，今Google Takeoutでmbox形式取れるからかなり楽になったよ．でも，継続的な更新はできないんだよね．俺は結局infomaniakってとこに移行したんだけど，自分のドメイン名使ってて本当に良かったと思ったね．
俺もGoogleやめた時同じ問題あったよ．データ量少なかったから，過去検索用に内容だけ欲しくて荒っぽい解決策やったんだ．ローカルPCにThunderbirdとか入れて，両方のメアドでログイン．メールを片方からもう片方にコピーするだけ．ローテクだけど結構うまくいったよ．ヘッダー残るかは要確認だけど，俺にはどうでも良かったな．いつかGmail移行プロセスについて記事書きたいわ．
imapsync使った？https://imapsync.lamiral.info/
全文検索も有効にできるといいね
そうそう！検索会社がやってるのに，Gmailの全文検索はびっくりするほどダメだと思うんだよね．
もっとコメントを表示（2）
でも，Outlook 365の検索よりはマシかな…
iOSとmacOSのMail.appも同じくらいひどいけどね
最近AIが悪くなって、かなりひどくなったよ。今は馬鹿な同義語を使うんだ。“doctorate”って検索すると、“he’d”みたいな“D”だけのインスタンスまでハイライトし始める。（多分Ph.D.を拾おうとしてる？）しばらくは“A”で検索しても“the”までハイライトされてたんだ。
これはマシな方だよ―YahooやThunderbirdデスクトップよりは俺の経験上ね。ただ、gmailの帯域制限を突き破るのが怖くて、Thunderbirdにはフルメッセージをダウンロードしないけど。
探してるメールの件名にある、かなり珍しい単語ペアで検索したのに、的外れな結果が20件も出たよ。見つける唯一の方法は送信者と日付でフィルタリングすることだった。
面白いね…俺も昨日同じことやったんだ、ドメイン別に受信者メールをリスト化したかったから。
コードはひどいけど、ここにあるよ。
https://github.com/hugoferreira/gmail-sqlite-db
そうそう。俺もドメインと送信者でグループ化するためにやったよ。
少しPostgresをバックエンドにしたIMAPサーバー Archiveopteryxを思い出したよ。
https://github.com/aox/aox
AOXのスキーマはいつもすごく良いなと思ってたんだけど、まだちゃんと試せてないんだ。主にメールのAnalyticsと検索のために使いたかったんだ（普段使いのIMAPサーバーじゃなくて）。
Manitou-Mailを思い出したな、強力なPostgreSQLベースの普段使いのメールクライアントで、かなり堅牢でパワフルだよ。
https://www.manitou-mail.org/
その情報ありがとう。面白そうなアプローチだね。
ここでの帯域幅のコストはどうなるの？
40GB以上のGmailアカウントを持ってるんだけど、このツールを使って転送したら課金されるのかな？
Google Take Outを使えば簡単に直せると思うんだ（名前合ってるかな？）。それは無料だと思うし、ダウンロードしたファイルをパースすればいいから。
それでも、立ち上げて使うって意味ではこのツールの方が速いだろうけど。
面白いツールだね。今試してるところだよ。個人のアカウントなのにGoogleの管理パネルでいくつか面倒な手順を踏まなきゃいけなくて、たぶんOAuthの組織とか作らされたんだと思う…今はメッセージ同期してるけど、すっごく遅いんだよね。Async処理とか使うともっと速くなるかもね。
google takeoutのmbox形式もサポートしてくれたらよかったのにね。
これすごい良さそう、シンプルだし。たぶん試すよ。将来、添付ファイルのメタデータ（とか添付ファイルそのものへのアクセス）も含まれるようになる可能性はある？
いろんなプラットフォームのsqlite exporterはアーカイブに超便利だよね、もちろん他の用途にも。俺は mail2db ってやつで、使わないメアドで毎月どれくらいメール来るか調べたことがあるよ。送信者のトップ10を見て、購読解除したり再登録したりしたんだ。
最高！機能リクエスト：メールの内容を解析して購読解除リンクを抽出して、よく来る送信者から簡単に購読解除できるようにしてほしいな。
俺も Got Your Back と LinqPad で C# を適当にいじって、似たようなことやったことあるよ。自分のメール分析するためにね。
そういえば、昔、ずいぶん前に自分のGmailのXMLフィードを取得できた時期ってなかったっけ？
これって単一テーブルのDBなんでしょ？だったらcsvとかdataframeとかにエクスポートして、分析パッケージ使ってやりたいこと分析する方が良くない？正直、ユースケースを理解してないか、Gmailアカウントが実際にどれくらい大きいか考えてないだけかもだから、もしそうなら無視してくれていいよ！
いくつか思いつく理由があるよ：
・プレーンテキストの検索はO(n)だけど、適切にインデックス付けされてる（今どきFTS5で超簡単）SQLiteデータベースの検索は、最悪ケースO(log n)でベストケースはO(1)。これでdataframeとかよりSQLiteが良い理由は説明できないけど、大量のメールを扱うならプレーンテキストより良いのは確か。
・SQLiteはカスタムビューとかプログラムを作りやすい。ほとんどの主要なプログラミング言語で問題なく使えるよ。simonwのdatasette.ioも見てみて。
・SQLiteは、もしデジタル保存の深い世界に行きたいなら、アメリカのLibrary of Congressで認められてるアーカイブ形式なんだ。
個人的には、Googleが送ってくるメールのバックアップ（たぶん大きなemlファイル）をパースするスクリプトの方が良かったな。
