Files

377 lines
14 KiB
Python

#!/usr/bin/env python3
"""Maildir-Volltextindex (SQLite FTS5) fuer den MCP-Mail-Server.
Hintergrund: search_mail scannte bei jeder Anfrage das komplette Maildir
(Roh-Byte-Vorfilter ueber alle Dateien). Bei Katis Gmail-Archiv (16 GB,
~30k Mails) dauerte das laenger als das Gateway-Request-Timeout → "Request
timed out". Dieser Index macht Suchen zu FTS5-SQL-Queries (Millisekunden).
Design:
- Eine SQLite-DB pro User unter /var/lib/mail-index/<user>.sqlite
- Tabelle messages (Stammdaten + body) + virtuelle Tabelle messages_fts
(FTS5, content='messages', mit Trigger-Sync via externem Content)
- Inkrementell: Datei-Pfad + mtime + size in messages; beim Update nur
neue/geaenderte Dateien neu einlesen, entfernte loeschen
- Such-Strategie: FTS5-MATCH mit Prefix-Termen; Fallback auf LIKE ueber
sender/to/subject (deckt Substrings und E-Mail-Adressen ab), zuletzt
LIKE ueber body
"""
import os
import re
import sqlite3
import sys
import time
import html as htmlmod
from email import policy
from email.parser import BytesParser
from email.utils import parsedate_to_datetime
BASE_DIR = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
sys.path.insert(0, BASE_DIR)
from common import load_config as _lc # noqa: E402
INDEX_DIR = "/var/lib/mail-index"
_SCHEMA = """
CREATE TABLE IF NOT EXISTS messages (
id INTEGER PRIMARY KEY,
path TEXT UNIQUE NOT NULL,
account TEXT NOT NULL,
folder TEXT NOT NULL,
key TEXT NOT NULL,
mtime REAL NOT NULL,
size INTEGER NOT NULL,
date_ts INTEGER DEFAULT 0,
sent_from TEXT DEFAULT '',
sent_to TEXT DEFAULT '',
subject TEXT DEFAULT '',
body TEXT DEFAULT ''
);
CREATE VIRTUAL TABLE IF NOT EXISTS messages_fts USING fts5(
sent_from, sent_to, subject, body,
content='messages', content_rowid='id',
tokenize='unicode61'
);
CREATE TRIGGER IF NOT EXISTS messages_ai AFTER INSERT ON messages BEGIN
INSERT INTO messages_fts(rowid, sent_from, sent_to, subject, body)
VALUES (new.id, new.sent_from, new.sent_to, new.subject, new.body);
END;
CREATE TRIGGER IF NOT EXISTS messages_ad AFTER DELETE ON messages BEGIN
INSERT INTO messages_fts(messages_fts, rowid, sent_from, sent_to, subject, body)
VALUES ('delete', old.id, old.sent_from, old.sent_to, old.subject, old.body);
END;
CREATE TRIGGER IF NOT EXISTS messages_au AFTER UPDATE ON messages BEGIN
INSERT INTO messages_fts(messages_fts, rowid, sent_from, sent_to, subject, body)
VALUES ('delete', old.id, old.sent_from, old.sent_to, old.subject, old.body);
INSERT INTO messages_fts(rowid, sent_from, sent_to, subject, body)
VALUES (new.id, new.sent_from, new.sent_to, new.subject, new.body);
END;
"""
def _term_list(query):
"""Nicht-alphanumerische Zeichen als Trenner; Lowercase."""
return [t for t in re.split(r"[^0-9A-Za-z_]+", query.lower()) if t]
def _decode_hdr(raw):
from email.header import decode_header
if not raw:
return ""
parts = decode_header(str(raw))
out = []
for data, charset in parts:
if isinstance(data, bytes):
for cs in (charset, "utf-8", "latin-1"):
if not cs:
continue
try:
out.append(data.decode(cs, errors="replace"))
break
except (LookupError, TypeError):
continue
else:
out.append(data.decode("latin-1", errors="replace"))
else:
out.append(str(data))
return " ".join(out)
def _extract_textparts(msg):
"""Text-Bodies sammeln: text/plain bevorzugt, sonst text/html (ohne Tags)."""
plains, htmls = [], []
if msg.is_multipart():
for part in msg.walk():
ct = part.get_content_type()
payload = part.get_payload(decode=True)
if not payload:
continue
try:
text = payload.decode(part.get_content_charset() or "utf-8", errors="replace")
except (LookupError, TypeError):
text = payload.decode("latin-1", errors="replace")
if ct == "text/plain":
plains.append(text)
elif ct == "text/html":
htmls.append(text)
else:
payload = msg.get_payload(decode=True)
if payload:
try:
plains.append(payload.decode(msg.get_content_charset() or "utf-8", errors="replace"))
except (LookupError, TypeError):
plains.append(payload.decode("latin-1", errors="replace"))
if plains:
return "\n".join(plains)
if htmls:
out = []
for h in htmls:
h = re.sub(r"<style.*?</style>", " ", h, flags=re.S | re.I)
h = re.sub(r"<script.*?</script>", " ", h, flags=re.S | re.I)
h = re.sub(r"<[^>]+>", " ", h)
out.append(htmlmod.unescape(h))
return "\n".join(out)
return ""
def _parse_file(path, root):
"""Mail-Datei einlesen → (account, folder, key, date_ts, sender, to, subject, body).
Pfadform unter root: <account>/<folder>/cur/<key>
"""
rel = os.path.relpath(path, root)
parts = rel.split(os.sep)
# erwartet: [account, folder, "cur", key] — aber Ordner koennen verschachtelt sein
key = parts[-1]
folder = os.sep.join(parts[1:-2]) if len(parts) > 3 else parts[-3]
account = parts[0]
try:
with open(path, "rb") as f:
raw = f.read()
except OSError:
return None
try:
msg = BytesParser(policy=policy.default).parsebytes(raw)
except Exception:
return None
date_str = str(msg.get("Date", ""))
date_ts = 0
if date_str:
try:
dt = parsedate_to_datetime(date_str)
if dt:
date_ts = int(dt.timestamp())
except Exception:
pass
sender = _decode_hdr(msg.get("From", ""))
to = _decode_hdr(msg.get("To", ""))
subject = _decode_hdr(msg.get("Subject", ""))
body = _extract_textparts(msg)
return account, folder, key, date_ts, sender, to, subject, body
def index_db_path(user):
return os.path.join(INDEX_DIR, f"{user}.sqlite")
def _ready_path(user):
"""Marker: Erst-Build abgeschlossen → Suche darf den Index nutzen."""
return os.path.join(INDEX_DIR, f"{user}.ready")
def connect(user, create=True):
os.makedirs(INDEX_DIR, exist_ok=True)
db = index_db_path(user)
con = sqlite3.connect(db, timeout=30)
con.execute("PRAGMA journal_mode=WAL")
con.execute("PRAGMA synchronous=NORMAL")
if create:
con.executescript(_SCHEMA)
return con
def update_user(user, root, log=print):
"""Inkrementeller Index-Update fuer einen User-Maildir-Root.
Gibt (added, updated, removed) zurueck.
"""
if not os.path.isdir(root):
log(f" [{user}] Root fehlt: {root}")
return 0, 0, 0
con = connect(user)
try:
seen = set()
added = updated = removed = 0
# cur/ + new/-Dateien aller Ordner einsammeln (followlinks: erlaubt Account-Symlinks wie nova->.com).
# new/ = ungelesene Mails (mbsync legt \Unseen dort ab); ohne new/ fehlten sie im Index.
for dirpath, dirnames, filenames in os.walk(root, followlinks=True):
if os.path.basename(dirpath) not in ("cur", "new"):
continue
for fn in filenames:
path = os.path.join(dirpath, fn)
try:
st = os.stat(path)
except OSError:
continue
mtime, size = st.st_mtime, st.st_size
seen.add(path)
row = con.execute(
"SELECT id FROM messages WHERE path=?", (path,)
).fetchone()
if row:
cur = con.execute(
"SELECT mtime, size FROM messages WHERE id=?", (row[0],)
).fetchone()
if cur and abs(cur[0] - mtime) < 0.001 and cur[1] == size:
continue # unveraendert
updated += 1
elif size == 0:
continue # leere Datei beim Sync noch nicht fertig
else:
added += 1
parsed = _parse_file(path, root)
if not parsed:
con.execute("DELETE FROM messages WHERE path=?", (path,))
continue
account, folder, key, date_ts, sender, to, subject, body = parsed
con.execute(
"""INSERT INTO messages
(path, account, folder, key, mtime, size, date_ts,
sent_from, sent_to, subject, body)
VALUES (?,?,?,?,?,?,?,?,?,?,?)
ON CONFLICT(path) DO UPDATE SET
account=excluded.account, folder=excluded.folder,
key=excluded.key, mtime=excluded.mtime, size=excluded.size,
date_ts=excluded.date_ts, sent_from=excluded.sent_from,
sent_to=excluded.sent_to, subject=excluded.subject,
body=excluded.body""",
(path, account, folder, key, mtime, size, date_ts,
sender, to, subject, body),
)
# in DB, aber Datei weg → loeschen
for (path,) in con.execute("SELECT path FROM messages").fetchall():
if path not in seen:
con.execute("DELETE FROM messages WHERE path=?", (path,))
removed += 1
con.commit()
return added, updated, removed
finally:
con.close()
def _fts_query(query):
"""FTS5-MATCH-String: alle Terme als Praefix-OR innerhalb eines Terms
(ADDED-Optimierung: mehrere Woerter muessen ALLE vorkommen → AND)."""
terms = _term_list(query)
if not terms:
return None
# jedes Wort als Prefix-Term: "term"* — escapen von Anführungszeichen
parts = []
for t in terms:
t = t.replace('"', "")
parts.append(f'"{t}"*')
return " AND ".join(parts)
def search(user, query, account="", folder="", limit=20, log=print):
"""Volltextsuche im Index. Liefert Liste von Dicts
{account, folder, key, date, sender, to, subject} — gleiches Format
wie der alte Vollscan, damit search_mail unveraendert weitermacht.
"""
db = index_db_path(user)
if not os.path.exists(db) or not os.path.exists(_ready_path(user)):
return None # Index noch nicht (vollstaendig) gebaut → Fallback im Server
con = connect(user, create=False)
try:
term_like = f"%{query.lower()}%"
base = "SELECT account, folder, key, date_ts, sent_from, sent_to, subject FROM messages"
where_clauses = []
params = []
# E-Mail-Adressen: FTS5 zerlegt sie in Einzelwoerter mit zu striktem
# AND → direkt ueber den LIKE-Pfad (exakt auf sender/to/subject).
has_email = "@" in query
if not has_email:
fts = _fts_query(query)
if fts:
where_clauses.append("id IN (SELECT rowid FROM messages_fts WHERE messages_fts MATCH ?)")
params.append(fts)
if account:
where_clauses.append("account=?")
params.append(account)
if folder:
where_clauses.append("folder LIKE ?")
params.append(f"%{folder}%")
out = []
if where_clauses:
where_sql = " WHERE " + " AND ".join(where_clauses)
rows = con.execute(
f"{base}{where_sql} ORDER BY date_ts DESC, id DESC LIMIT ?",
params + [limit],
).fetchall()
for account_, folder_, key_, date_ts, sender, to, subject in rows:
out.append({
"account": account_, "folder": folder_, "key": key_,
"date": _fmt_date(date_ts), "sender": sender, "to": to,
"subject": subject,
})
# Fallback: LIKE-Suche (deckt Substrings + E-Mail-Adressen ab)
if not out:
cols = ["sent_from LIKE ?", "sent_to LIKE ?", "subject LIKE ?"]
all_p = [term_like] * len(cols)
extra_where = []
if account:
extra_where.append("account=?")
all_p.append(account)
if folder:
extra_where.append("folder LIKE ?")
all_p.append(f"%{folder}%")
sql = f"{base} WHERE ({' OR '.join(cols)})"
if extra_where:
sql += " AND " + " AND ".join(extra_where)
sql += " ORDER BY date_ts DESC, id DESC LIMIT ?"
all_p.append(limit)
rows2 = con.execute(sql, all_p).fetchall()
for account_, folder_, key_, date_ts, sender, to, subject in rows2:
out.append({
"account": account_, "folder": folder_, "key": key_,
"date": _fmt_date(date_ts), "sender": sender, "to": to,
"subject": subject,
})
return out
finally:
con.close()
def _fmt_date(ts):
if not ts:
return ""
return time.strftime("%a, %d %b %Y %H:%M:%S %z", time.localtime(ts))
if __name__ == "__main__":
# CLI: python3 mailindex.py <user> --update|--search "<query>" [limit]
import argparse
ap = argparse.ArgumentParser()
ap.add_argument("user")
ap.add_argument("--update", action="store_true", help="inkrementeller Update")
ap.add_argument("--search", nargs="?", default=None)
ap.add_argument("--limit", type=int, default=10)
args = ap.parse_args()
cfg = _lc()
root = cfg["mail_roots"].get(args.user)
if args.update:
if not root:
print(f"kein root fuer {args.user}")
sys.exit(1)
a, u, r = update_user(args.user, root)
print(f"[{args.user}] added={a} updated={u} removed={r}")
elif args.search:
res = search(args.user, args.search, limit=args.limit)
if res is None:
print("kein index")
elif not res:
print("keine treffer")
else:
for r in res:
print(f"[{r['date']}] {r['sender']} -> {r['to']}\n {r['subject']}\n {r['account']}/{r['folder']}/{r['key']}")
else:
ap.print_help()