1252 lines
40 KiB
Go
1252 lines
40 KiB
Go
package backend
|
|
|
|
import (
|
|
"database/sql"
|
|
"encoding/json"
|
|
"errors"
|
|
"fmt"
|
|
"os"
|
|
"path/filepath"
|
|
"runtime"
|
|
"strings"
|
|
|
|
_ "modernc.org/sqlite"
|
|
)
|
|
|
|
var DB *sql.DB
|
|
|
|
// Account = eine Umzugs-Zeile: Quelle (alt) -> Ziel (neu), frei gemapptes
|
|
// Zieladress-Postfach. Wird im Browser gepflegt (Tabelle accounts).
|
|
type Account struct {
|
|
ID int64 `json:"id"`
|
|
Name string `json:"name"` // z.B. "hans-peter"
|
|
// Quelle (alter Provider). Security: "tls" (implizit, 993/995),
|
|
// "starttls" (Klartext-Port 143/110 + Upgrade), "none" (reiner Klartext).
|
|
// SrcInsecure = ungueltige/selbstsignierte Zerts akzeptieren (alte Hoster).
|
|
SrcHost string `json:"src_host"`
|
|
SrcPort int `json:"src_port"`
|
|
SrcSecurity string `json:"src_security"`
|
|
SrcInsecure bool `json:"src_insecure"`
|
|
SrcUser string `json:"src_user"` // hans-peter@dr-gold.de
|
|
SrcPass string `json:"src_pass"`
|
|
SrcProto string `json:"src_proto"` // "imap" (default) oder "pop3" (Fallback-Quelle)
|
|
// Ziel (neuer Provider). Meist "tls"; "none"/"starttls" ebenso moeglich.
|
|
DstHost string `json:"dst_host"`
|
|
DstPort int `json:"dst_port"`
|
|
DstSecurity string `json:"dst_security"`
|
|
DstInsecure bool `json:"dst_insecure"`
|
|
DstUser string `json:"dst_user"` // archiv-hans-peter@dr-gold.com
|
|
DstPass string `json:"dst_pass"`
|
|
MboxDir string `json:"mbox_dir"` // optionaler Name aus archive_mailboxes unter Cfg.MboxRoot
|
|
Active bool `json:"active"`
|
|
}
|
|
|
|
type AppUser struct {
|
|
ID int64
|
|
Username string
|
|
DisplayName string
|
|
Role string
|
|
Active bool
|
|
PasswordHash string
|
|
}
|
|
|
|
// ConnectDB oeffnet die SQLite-DB (modernc, kein cgo) und legt das Schema an.
|
|
//
|
|
// Schema (TODO Codex):
|
|
//
|
|
// accounts(...) -- die Umzugs-Zeilen (oben), inkl.
|
|
// src_security/src_insecure etc.
|
|
// archive_mailboxes(name) -- lokale/importierte Archiv-mboxen,
|
|
// unabhaengig von Quell/Ziel-Konten.
|
|
// folder_map(account_id, src, dst) -- optionales manuelles Umbenennen
|
|
// (Vorrang vor der Rollen-Automatik
|
|
// aus 11-folders.go)
|
|
// copied(account_id, folder, message_id) -- Idempotenz-/Delta-Cache: schon
|
|
// kopierte Message-IDs, UNIQUE.
|
|
// Zweiter Lauf kopiert nur das Delta.
|
|
// jobs(account_id, started, finished, -- Lauf-Historie fuer den Fortschritt
|
|
// total, done, errors, state) im Browser.
|
|
func ConnectDB(initDB bool) error {
|
|
if Cfg.DBPath == "" {
|
|
Cfg.DBPath = "mail-graveyard.db"
|
|
}
|
|
if err := ensureDBFile(Cfg.DBPath, initDB); err != nil {
|
|
return err
|
|
}
|
|
db, err := sql.Open("sqlite", Cfg.DBPath)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
// SQLite pragmas are connection-local. Keep a single process-local
|
|
// connection so every query uses the busy timeout configured here. The web
|
|
// app and watcher are separate processes; WAL + busy_timeout coordinates
|
|
// their writes without failing immediately with SQLITE_BUSY.
|
|
db.SetMaxOpenConns(1)
|
|
db.SetMaxIdleConns(1)
|
|
if _, err := db.Exec(`PRAGMA busy_timeout=10000; PRAGMA journal_mode=WAL; PRAGMA foreign_keys=ON;`); err != nil {
|
|
_ = db.Close()
|
|
return err
|
|
}
|
|
if err := secureSQLiteFiles(Cfg.DBPath); err != nil {
|
|
_ = db.Close()
|
|
return err
|
|
}
|
|
schema := []string{
|
|
`CREATE TABLE IF NOT EXISTS accounts(
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
name TEXT NOT NULL UNIQUE,
|
|
src_host TEXT NOT NULL,
|
|
src_port INTEGER NOT NULL,
|
|
src_security TEXT NOT NULL DEFAULT 'tls',
|
|
src_insecure INTEGER NOT NULL DEFAULT 0,
|
|
src_user TEXT NOT NULL,
|
|
src_pass TEXT NOT NULL,
|
|
src_proto TEXT NOT NULL DEFAULT 'imap',
|
|
dst_host TEXT NOT NULL,
|
|
dst_port INTEGER NOT NULL,
|
|
dst_security TEXT NOT NULL DEFAULT 'tls',
|
|
dst_insecure INTEGER NOT NULL DEFAULT 0,
|
|
dst_user TEXT NOT NULL,
|
|
dst_pass TEXT NOT NULL,
|
|
mbox_dir TEXT NOT NULL DEFAULT '',
|
|
active INTEGER NOT NULL DEFAULT 1
|
|
)`,
|
|
`CREATE TABLE IF NOT EXISTS archive_mailboxes(
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
name TEXT NOT NULL UNIQUE,
|
|
created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
)`,
|
|
`CREATE TABLE IF NOT EXISTS folder_map(
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
account_id INTEGER NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
|
|
src_folder TEXT NOT NULL,
|
|
dst_folder TEXT NOT NULL,
|
|
UNIQUE(account_id, src_folder)
|
|
)`,
|
|
`CREATE TABLE IF NOT EXISTS copied(
|
|
account_id INTEGER NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
|
|
folder TEXT NOT NULL,
|
|
message_id TEXT NOT NULL,
|
|
body_sha256 TEXT NOT NULL DEFAULT '',
|
|
mbox_done INTEGER NOT NULL DEFAULT 0,
|
|
target_done INTEGER NOT NULL DEFAULT 0,
|
|
copied_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
UNIQUE(account_id, folder, message_id, body_sha256)
|
|
)`,
|
|
`CREATE TABLE IF NOT EXISTS mbox_index(
|
|
account_id INTEGER NOT NULL,
|
|
folder TEXT NOT NULL,
|
|
seq INTEGER NOT NULL,
|
|
message_id TEXT NOT NULL,
|
|
body_sha256 TEXT NOT NULL DEFAULT '',
|
|
subject TEXT NOT NULL DEFAULT '',
|
|
from_addr TEXT NOT NULL DEFAULT '',
|
|
date TEXT NOT NULL DEFAULT '',
|
|
file_offset INTEGER NOT NULL,
|
|
frame_len INTEGER NOT NULL,
|
|
inner_offset INTEGER NOT NULL DEFAULT 0,
|
|
inner_len INTEGER NOT NULL,
|
|
UNIQUE(account_id, folder, seq)
|
|
)`,
|
|
`CREATE TABLE IF NOT EXISTS mbox_index_state(
|
|
account_id INTEGER NOT NULL,
|
|
folder TEXT NOT NULL,
|
|
indexed_bytes INTEGER NOT NULL DEFAULT 0,
|
|
UNIQUE(account_id, folder)
|
|
)`,
|
|
`CREATE TABLE IF NOT EXISTS jobs(
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
account_id INTEGER NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
|
|
started TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
finished TEXT,
|
|
total INTEGER NOT NULL DEFAULT 0,
|
|
done INTEGER NOT NULL DEFAULT 0,
|
|
errors INTEGER NOT NULL DEFAULT 0,
|
|
state TEXT NOT NULL DEFAULT 'running'
|
|
)`,
|
|
`CREATE TABLE IF NOT EXISTS app_users(
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
username TEXT NOT NULL UNIQUE,
|
|
display_name TEXT NOT NULL DEFAULT '',
|
|
password_hash TEXT NOT NULL,
|
|
role TEXT NOT NULL DEFAULT 'user',
|
|
active INTEGER NOT NULL DEFAULT 1,
|
|
created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
)`,
|
|
`CREATE TABLE IF NOT EXISTS app_sessions(
|
|
token TEXT PRIMARY KEY,
|
|
user_id INTEGER NOT NULL REFERENCES app_users(id) ON DELETE CASCADE,
|
|
expires_at TEXT NOT NULL,
|
|
created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
)`,
|
|
`CREATE TABLE IF NOT EXISTS app_password_resets(
|
|
token_hash TEXT PRIMARY KEY,
|
|
user_id INTEGER NOT NULL REFERENCES app_users(id) ON DELETE CASCADE,
|
|
expires_at TEXT NOT NULL,
|
|
used_at TEXT,
|
|
created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
)`,
|
|
}
|
|
for _, stmt := range schema {
|
|
if _, err := db.Exec(stmt); err != nil {
|
|
_ = db.Close()
|
|
return err
|
|
}
|
|
}
|
|
DB = db
|
|
if err := ensureAppUserColumns(); err != nil {
|
|
_ = db.Close()
|
|
return err
|
|
}
|
|
if err := ensureCopiedStageColumns(); err != nil {
|
|
_ = db.Close()
|
|
return err
|
|
}
|
|
if err := ensureMessageIdentitySchema(); err != nil {
|
|
_ = db.Close()
|
|
return err
|
|
}
|
|
if err := seedArchiveMailboxesFromFS(); err != nil {
|
|
_ = db.Close()
|
|
return err
|
|
}
|
|
return nil
|
|
}
|
|
|
|
// ensureMessageIdentitySchema upgrades the old Message-ID-only bookkeeping in
|
|
// one transaction. No logical rows are discarded: old keys remain as rows with
|
|
// an empty body hash and continue to act as compatibility aliases. The mbox
|
|
// index is deliberately invalidated so its hashes are rebuilt from the archive
|
|
// before the next migration pass makes a copy decision.
|
|
func ensureMessageIdentitySchema() error {
|
|
copiedColumns, err := tableColumns("copied")
|
|
if err != nil {
|
|
return err
|
|
}
|
|
indexColumns, err := tableColumns("mbox_index")
|
|
if err != nil {
|
|
return err
|
|
}
|
|
upgradeCopied := !copiedColumns["body_sha256"]
|
|
upgradeIndex := !indexColumns["body_sha256"]
|
|
if !upgradeCopied && !upgradeIndex {
|
|
_, err := DB.Exec(`CREATE INDEX IF NOT EXISTS idx_copied_identity ON copied(account_id, folder, message_id, body_sha256);
|
|
CREATE INDEX IF NOT EXISTS idx_mbox_index_identity ON mbox_index(account_id, folder, message_id, body_sha256);`)
|
|
return err
|
|
}
|
|
tx, err := DB.Begin()
|
|
if err != nil {
|
|
return err
|
|
}
|
|
defer tx.Rollback()
|
|
if upgradeCopied {
|
|
statements := []string{
|
|
`ALTER TABLE copied RENAME TO copied_pre_identity`,
|
|
`CREATE TABLE copied(
|
|
account_id INTEGER NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
|
|
folder TEXT NOT NULL,
|
|
message_id TEXT NOT NULL,
|
|
body_sha256 TEXT NOT NULL DEFAULT '',
|
|
mbox_done INTEGER NOT NULL DEFAULT 0,
|
|
target_done INTEGER NOT NULL DEFAULT 0,
|
|
copied_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
UNIQUE(account_id, folder, message_id, body_sha256)
|
|
)`,
|
|
`INSERT INTO copied(account_id, folder, message_id, body_sha256, mbox_done, target_done, copied_at)
|
|
SELECT account_id, folder, message_id, '', mbox_done, target_done, copied_at FROM copied_pre_identity`,
|
|
`DROP TABLE copied_pre_identity`,
|
|
}
|
|
for _, stmt := range statements {
|
|
if _, err := tx.Exec(stmt); err != nil {
|
|
return err
|
|
}
|
|
}
|
|
}
|
|
if upgradeIndex {
|
|
statements := []string{
|
|
`ALTER TABLE mbox_index RENAME TO mbox_index_pre_identity`,
|
|
`CREATE TABLE mbox_index(
|
|
account_id INTEGER NOT NULL,
|
|
folder TEXT NOT NULL,
|
|
seq INTEGER NOT NULL,
|
|
message_id TEXT NOT NULL,
|
|
body_sha256 TEXT NOT NULL DEFAULT '',
|
|
subject TEXT NOT NULL DEFAULT '',
|
|
from_addr TEXT NOT NULL DEFAULT '',
|
|
date TEXT NOT NULL DEFAULT '',
|
|
file_offset INTEGER NOT NULL,
|
|
frame_len INTEGER NOT NULL,
|
|
inner_offset INTEGER NOT NULL DEFAULT 0,
|
|
inner_len INTEGER NOT NULL,
|
|
UNIQUE(account_id, folder, seq)
|
|
)`,
|
|
`INSERT INTO mbox_index(account_id, folder, seq, message_id, body_sha256, subject, from_addr, date, file_offset, frame_len, inner_offset, inner_len)
|
|
SELECT account_id, folder, seq, message_id, '', subject, from_addr, date, file_offset, frame_len, inner_offset, inner_len FROM mbox_index_pre_identity`,
|
|
`DROP TABLE mbox_index_pre_identity`,
|
|
`UPDATE mbox_index_state SET indexed_bytes=-1`,
|
|
}
|
|
for _, stmt := range statements {
|
|
if _, err := tx.Exec(stmt); err != nil {
|
|
return err
|
|
}
|
|
}
|
|
}
|
|
if _, err := tx.Exec(`CREATE INDEX IF NOT EXISTS idx_copied_identity ON copied(account_id, folder, message_id, body_sha256)`); err != nil {
|
|
return err
|
|
}
|
|
if _, err := tx.Exec(`CREATE INDEX IF NOT EXISTS idx_mbox_index_identity ON mbox_index(account_id, folder, message_id, body_sha256)`); err != nil {
|
|
return err
|
|
}
|
|
return tx.Commit()
|
|
}
|
|
|
|
func tableColumns(table string) (map[string]bool, error) {
|
|
rows, err := DB.Query(`PRAGMA table_info(` + table + `)`)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
defer rows.Close()
|
|
columns := map[string]bool{}
|
|
for rows.Next() {
|
|
var cid, notNull, pk int
|
|
var name, typ string
|
|
var defaultValue any
|
|
if err := rows.Scan(&cid, &name, &typ, ¬Null, &defaultValue, &pk); err != nil {
|
|
return nil, err
|
|
}
|
|
columns[name] = true
|
|
}
|
|
return columns, rows.Err()
|
|
}
|
|
|
|
func ensureCopiedStageColumns() error {
|
|
columns := map[string]bool{}
|
|
rows, err := DB.Query(`PRAGMA table_info(copied)`)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
for rows.Next() {
|
|
var cid int
|
|
var name, typ string
|
|
var notNull int
|
|
var defaultValue any
|
|
var pk int
|
|
if err := rows.Scan(&cid, &name, &typ, ¬Null, &defaultValue, &pk); err != nil {
|
|
_ = rows.Close()
|
|
return err
|
|
}
|
|
columns[name] = true
|
|
}
|
|
if err := rows.Err(); err != nil {
|
|
_ = rows.Close()
|
|
return err
|
|
}
|
|
if err := rows.Close(); err != nil {
|
|
return err
|
|
}
|
|
// Existing copied rows were created by the old all-or-nothing pipeline and
|
|
// therefore represent both stages as complete. DEFAULT 1 preserves that
|
|
// truth while all new writes below set their stage values explicitly.
|
|
if !columns["mbox_done"] {
|
|
if _, err := DB.Exec(`ALTER TABLE copied ADD COLUMN mbox_done INTEGER NOT NULL DEFAULT 1`); err != nil {
|
|
return err
|
|
}
|
|
}
|
|
if !columns["target_done"] {
|
|
if _, err := DB.Exec(`ALTER TABLE copied ADD COLUMN target_done INTEGER NOT NULL DEFAULT 1`); err != nil {
|
|
return err
|
|
}
|
|
}
|
|
return nil
|
|
}
|
|
|
|
func ensureDBFile(path string, initDB bool) error {
|
|
info, err := os.Stat(path)
|
|
if err == nil {
|
|
if info.IsDir() {
|
|
return fmt.Errorf("DB %q ist ein Verzeichnis", path)
|
|
}
|
|
return chmodPrivateFile(path)
|
|
}
|
|
if !errors.Is(err, os.ErrNotExist) {
|
|
return err
|
|
}
|
|
if !initDB {
|
|
return fmt.Errorf("DB %q existiert nicht -- mit --init-db neu anlegen oder db_path pruefen", path)
|
|
}
|
|
f, err := os.OpenFile(path, os.O_RDWR|os.O_CREATE|os.O_EXCL, 0600)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
if err := f.Close(); err != nil {
|
|
return err
|
|
}
|
|
return chmodPrivateFile(path)
|
|
}
|
|
|
|
func secureSQLiteFiles(path string) error {
|
|
directory := filepath.Dir(path)
|
|
entries, err := os.ReadDir(directory)
|
|
if err != nil {
|
|
return fmt.Errorf("scan SQLite directory %q: %w", directory, err)
|
|
}
|
|
for _, entry := range entries {
|
|
if entry.Type()&os.ModeSymlink != 0 || !isSQLiteDataFile(entry.Name()) {
|
|
continue
|
|
}
|
|
info, err := entry.Info()
|
|
if err != nil {
|
|
return fmt.Errorf("inspect SQLite file %q: %w", filepath.Join(directory, entry.Name()), err)
|
|
}
|
|
if !info.Mode().IsRegular() {
|
|
continue
|
|
}
|
|
candidate := filepath.Join(directory, entry.Name())
|
|
if err := chmodPrivateFile(candidate); err != nil {
|
|
return fmt.Errorf("secure SQLite file %q: %w", candidate, err)
|
|
}
|
|
}
|
|
return nil
|
|
}
|
|
|
|
func chmodPrivateFile(path string) error {
|
|
if err := os.Chmod(path, 0o600); err != nil {
|
|
return err
|
|
}
|
|
if runtime.GOOS == "windows" {
|
|
return nil
|
|
}
|
|
info, err := os.Stat(path)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
if permissions := info.Mode().Perm(); permissions&0o077 != 0 {
|
|
return fmt.Errorf("permissions=%#o after chmod 0600", permissions)
|
|
}
|
|
return nil
|
|
}
|
|
|
|
func isSQLiteDataFile(name string) bool {
|
|
name = strings.ToLower(name)
|
|
return strings.HasSuffix(name, ".db") || strings.HasSuffix(name, ".db-wal") || strings.HasSuffix(name, ".db-shm")
|
|
}
|
|
|
|
func ensureAppUserColumns() error {
|
|
hasDisplayName := false
|
|
rows, err := DB.Query(`PRAGMA table_info(app_users)`)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
for rows.Next() {
|
|
var cid int
|
|
var name, typ string
|
|
var notNull int
|
|
var defaultValue any
|
|
var pk int
|
|
if err := rows.Scan(&cid, &name, &typ, ¬Null, &defaultValue, &pk); err != nil {
|
|
_ = rows.Close()
|
|
return err
|
|
}
|
|
if name == "display_name" {
|
|
hasDisplayName = true
|
|
}
|
|
}
|
|
if err := rows.Err(); err != nil {
|
|
_ = rows.Close()
|
|
return err
|
|
}
|
|
if err := rows.Close(); err != nil {
|
|
return err
|
|
}
|
|
if !hasDisplayName {
|
|
if _, err := DB.Exec(`ALTER TABLE app_users ADD COLUMN display_name TEXT NOT NULL DEFAULT ''`); err != nil {
|
|
return err
|
|
}
|
|
}
|
|
return nil
|
|
}
|
|
|
|
func CountAppUsers() (int, error) {
|
|
var count int
|
|
err := DB.QueryRow(`SELECT count(*) FROM app_users`).Scan(&count)
|
|
return count, err
|
|
}
|
|
|
|
func ListAppUsers() ([]AppUser, error) {
|
|
rows, err := DB.Query(`SELECT id, username, display_name, password_hash, role, active FROM app_users ORDER BY username`)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
defer rows.Close()
|
|
var out []AppUser
|
|
for rows.Next() {
|
|
u, err := scanAppUser(rows)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
out = append(out, u)
|
|
}
|
|
return out, rows.Err()
|
|
}
|
|
|
|
func GetAppUser(username string) (AppUser, error) {
|
|
row := DB.QueryRow(`SELECT id, username, display_name, password_hash, role, active FROM app_users WHERE username=?`, username)
|
|
return scanAppUser(row)
|
|
}
|
|
|
|
func GetAppUserByID(id int64) (AppUser, error) {
|
|
row := DB.QueryRow(`SELECT id, username, display_name, password_hash, role, active FROM app_users WHERE id=?`, id)
|
|
return scanAppUser(row)
|
|
}
|
|
|
|
func SaveAppUser(username, displayName, passwordHash, role string, active bool) error {
|
|
displayName = strings.TrimSpace(displayName)
|
|
role = normalizeRole(role)
|
|
if passwordHash == "" {
|
|
_, err := DB.Exec(`UPDATE app_users SET display_name=?, role=?, active=?, updated_at=CURRENT_TIMESTAMP WHERE username=?`,
|
|
displayName, role, boolInt(active), username)
|
|
return err
|
|
}
|
|
_, err := DB.Exec(`INSERT INTO app_users(username, display_name, password_hash, role, active)
|
|
VALUES(?,?,?,?,?)
|
|
ON CONFLICT(username) DO UPDATE SET
|
|
display_name=excluded.display_name,
|
|
password_hash=excluded.password_hash,
|
|
role=excluded.role,
|
|
active=excluded.active,
|
|
updated_at=CURRENT_TIMESTAMP`,
|
|
username, displayName, passwordHash, role, boolInt(active))
|
|
return err
|
|
}
|
|
|
|
func UpdateAppUser(oldUsername, username, displayName, passwordHash, role string, active bool) error {
|
|
oldUsername = strings.TrimSpace(oldUsername)
|
|
username = strings.TrimSpace(username)
|
|
displayName = strings.TrimSpace(displayName)
|
|
role = normalizeRole(role)
|
|
if oldUsername == "" || oldUsername == username {
|
|
return SaveAppUser(username, displayName, passwordHash, role, active)
|
|
}
|
|
tx, err := DB.Begin()
|
|
if err != nil {
|
|
return err
|
|
}
|
|
defer tx.Rollback()
|
|
var existing int
|
|
err = tx.QueryRow(`SELECT 1 FROM app_users WHERE username=?`, username).Scan(&existing)
|
|
if err == nil {
|
|
return fmt.Errorf("Login/E-Mail existiert bereits.")
|
|
}
|
|
if !errors.Is(err, sql.ErrNoRows) {
|
|
return err
|
|
}
|
|
if passwordHash == "" {
|
|
_, err = tx.Exec(`UPDATE app_users SET username=?, display_name=?, role=?, active=?, updated_at=CURRENT_TIMESTAMP WHERE username=?`,
|
|
username, displayName, role, boolInt(active), oldUsername)
|
|
} else {
|
|
_, err = tx.Exec(`UPDATE app_users SET username=?, display_name=?, password_hash=?, role=?, active=?, updated_at=CURRENT_TIMESTAMP WHERE username=?`,
|
|
username, displayName, passwordHash, role, boolInt(active), oldUsername)
|
|
}
|
|
if err != nil {
|
|
return err
|
|
}
|
|
_, err = tx.Exec(`DELETE FROM app_sessions WHERE user_id IN (SELECT id FROM app_users WHERE username=?)`, username)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
return tx.Commit()
|
|
}
|
|
|
|
func DeleteAppUser(username string) error {
|
|
_, err := DB.Exec(`DELETE FROM app_users WHERE username=?`, username)
|
|
return err
|
|
}
|
|
|
|
func CreateSession(token string, userID int64, expiresAt string) error {
|
|
_, err := DB.Exec(`INSERT INTO app_sessions(token, user_id, expires_at) VALUES(?,?,?)`, token, userID, expiresAt)
|
|
return err
|
|
}
|
|
|
|
func DeleteSession(token string) error {
|
|
_, err := DB.Exec(`DELETE FROM app_sessions WHERE token=?`, token)
|
|
return err
|
|
}
|
|
|
|
func SessionUser(token string) (AppUser, error) {
|
|
row := DB.QueryRow(`SELECT u.id, u.username, u.display_name, u.password_hash, u.role, u.active
|
|
FROM app_sessions s JOIN app_users u ON u.id=s.user_id
|
|
WHERE s.token=? AND s.expires_at > CURRENT_TIMESTAMP AND u.active=1`, token)
|
|
return scanAppUser(row)
|
|
}
|
|
|
|
func CleanupSessions() error {
|
|
_, err := DB.Exec(`DELETE FROM app_sessions WHERE expires_at <= CURRENT_TIMESTAMP`)
|
|
return err
|
|
}
|
|
|
|
func CreatePasswordReset(tokenHash string, userID int64, expiresAt string) error {
|
|
_, err := DB.Exec(`INSERT INTO app_password_resets(token_hash, user_id, expires_at) VALUES(?,?,?)`, tokenHash, userID, expiresAt)
|
|
return err
|
|
}
|
|
|
|
func PasswordResetUser(tokenHash string) (AppUser, error) {
|
|
row := DB.QueryRow(`SELECT u.id, u.username, u.display_name, u.password_hash, u.role, u.active
|
|
FROM app_password_resets r JOIN app_users u ON u.id=r.user_id
|
|
WHERE r.token_hash=? AND r.used_at IS NULL AND r.expires_at > CURRENT_TIMESTAMP AND u.active=1`, tokenHash)
|
|
return scanAppUser(row)
|
|
}
|
|
|
|
func UsePasswordReset(tokenHash, passwordHash string) error {
|
|
tx, err := DB.Begin()
|
|
if err != nil {
|
|
return err
|
|
}
|
|
defer tx.Rollback()
|
|
var userID int64
|
|
err = tx.QueryRow(`SELECT user_id FROM app_password_resets WHERE token_hash=? AND used_at IS NULL AND expires_at > CURRENT_TIMESTAMP`, tokenHash).Scan(&userID)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
if _, err := tx.Exec(`UPDATE app_users SET password_hash=?, updated_at=CURRENT_TIMESTAMP WHERE id=?`, passwordHash, userID); err != nil {
|
|
return err
|
|
}
|
|
if _, err := tx.Exec(`UPDATE app_password_resets SET used_at=CURRENT_TIMESTAMP WHERE token_hash=?`, tokenHash); err != nil {
|
|
return err
|
|
}
|
|
if _, err := tx.Exec(`DELETE FROM app_sessions WHERE user_id=?`, userID); err != nil {
|
|
return err
|
|
}
|
|
return tx.Commit()
|
|
}
|
|
|
|
func CleanupPasswordResets() error {
|
|
_, err := DB.Exec(`DELETE FROM app_password_resets WHERE used_at IS NOT NULL OR expires_at <= CURRENT_TIMESTAMP`)
|
|
return err
|
|
}
|
|
|
|
func ListAccounts() ([]Account, error) {
|
|
rows, err := DB.Query(`SELECT id,name,src_host,src_port,src_security,src_insecure,src_user,src_pass,src_proto,
|
|
dst_host,dst_port,dst_security,dst_insecure,dst_user,dst_pass,mbox_dir,active
|
|
FROM accounts ORDER BY name`)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
defer rows.Close()
|
|
var out []Account
|
|
for rows.Next() {
|
|
a, err := scanAccount(rows)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
out = append(out, a)
|
|
}
|
|
return out, rows.Err()
|
|
}
|
|
|
|
func GetAccount(name string) (Account, error) {
|
|
row := DB.QueryRow(`SELECT id,name,src_host,src_port,src_security,src_insecure,src_user,src_pass,src_proto,
|
|
dst_host,dst_port,dst_security,dst_insecure,dst_user,dst_pass,mbox_dir,active
|
|
FROM accounts WHERE name=?`, name)
|
|
return scanAccount(row)
|
|
}
|
|
|
|
func SaveAccount(a Account) error {
|
|
normalizeAccount(&a)
|
|
_, err := DB.Exec(`INSERT INTO accounts(name,src_host,src_port,src_security,src_insecure,src_user,src_pass,src_proto,
|
|
dst_host,dst_port,dst_security,dst_insecure,dst_user,dst_pass,mbox_dir,active)
|
|
VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
|
|
ON CONFLICT(name) DO UPDATE SET
|
|
src_host=excluded.src_host, src_port=excluded.src_port, src_security=excluded.src_security,
|
|
src_insecure=excluded.src_insecure, src_user=excluded.src_user, src_pass=excluded.src_pass,
|
|
src_proto=excluded.src_proto, dst_host=excluded.dst_host, dst_port=excluded.dst_port,
|
|
dst_security=excluded.dst_security, dst_insecure=excluded.dst_insecure,
|
|
dst_user=excluded.dst_user, dst_pass=excluded.dst_pass, mbox_dir=excluded.mbox_dir,
|
|
active=excluded.active`,
|
|
a.Name, a.SrcHost, a.SrcPort, a.SrcSecurity, boolInt(a.SrcInsecure), a.SrcUser, a.SrcPass, a.SrcProto,
|
|
a.DstHost, a.DstPort, a.DstSecurity, boolInt(a.DstInsecure), a.DstUser, a.DstPass, a.MboxDir, boolInt(a.Active))
|
|
return err
|
|
}
|
|
|
|
func DeleteAccount(name string) error {
|
|
_, err := DB.Exec(`DELETE FROM accounts WHERE name=?`, name)
|
|
return err
|
|
}
|
|
|
|
func FolderMap(accountID int64) (map[string]string, error) {
|
|
rows, err := DB.Query(`SELECT src_folder, dst_folder FROM folder_map WHERE account_id=?`, accountID)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
defer rows.Close()
|
|
out := map[string]string{}
|
|
for rows.Next() {
|
|
var src, dst string
|
|
if err := rows.Scan(&src, &dst); err != nil {
|
|
return nil, err
|
|
}
|
|
out[src] = dst
|
|
}
|
|
return out, rows.Err()
|
|
}
|
|
|
|
func ListArchiveMailboxes() ([]string, error) {
|
|
rows, err := DB.Query(`SELECT name FROM archive_mailboxes ORDER BY lower(name), name`)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
defer rows.Close()
|
|
var out []string
|
|
for rows.Next() {
|
|
var name string
|
|
if err := rows.Scan(&name); err != nil {
|
|
return nil, err
|
|
}
|
|
out = append(out, name)
|
|
}
|
|
return out, rows.Err()
|
|
}
|
|
|
|
func SaveArchiveMailbox(name string) error {
|
|
name = strings.TrimSpace(name)
|
|
if name == "" {
|
|
return fmt.Errorf("Name der Archiv-Mailbox fehlt.")
|
|
}
|
|
_, err := DB.Exec(`INSERT OR IGNORE INTO archive_mailboxes(name) VALUES(?)`, name)
|
|
return err
|
|
}
|
|
|
|
func DeleteArchiveMailbox(name string) error {
|
|
_, err := DB.Exec(`DELETE FROM archive_mailboxes WHERE name=?`, strings.TrimSpace(name))
|
|
return err
|
|
}
|
|
|
|
func seedArchiveMailboxesFromFS() error {
|
|
if Cfg.MboxRoot == "" {
|
|
return nil
|
|
}
|
|
entries, err := os.ReadDir(Cfg.MboxRoot)
|
|
if os.IsNotExist(err) {
|
|
return nil
|
|
}
|
|
if err != nil {
|
|
return err
|
|
}
|
|
for _, entry := range entries {
|
|
if entry.IsDir() {
|
|
if err := SaveArchiveMailbox(entry.Name()); err != nil {
|
|
return err
|
|
}
|
|
}
|
|
}
|
|
return nil
|
|
}
|
|
|
|
func AlreadyCopied(accountID int64, folder, messageID string) (bool, error) {
|
|
state, err := GetCopyState(accountID, folder, messageID)
|
|
return state.MboxDone && state.TargetDone, err
|
|
}
|
|
|
|
func MarkCopied(accountID int64, folder, messageID string) error {
|
|
return MarkIdentityCopied(accountID, folder, MessageIdentity{MessageID: messageID})
|
|
}
|
|
|
|
type CopyState struct {
|
|
MboxDone bool
|
|
TargetDone bool
|
|
}
|
|
|
|
func GetCopyState(accountID int64, folder, messageID string) (CopyState, error) {
|
|
return GetCopyIdentityState(accountID, folder, MessageIdentity{MessageID: messageID})
|
|
}
|
|
|
|
func MarkMboxCopied(accountID int64, folder, messageID string) error {
|
|
return MarkIdentityMboxCopied(accountID, folder, MessageIdentity{MessageID: messageID})
|
|
}
|
|
|
|
func MarkTargetCopied(accountID int64, folder, messageID string) error {
|
|
return MarkIdentityTargetCopied(accountID, folder, MessageIdentity{MessageID: messageID})
|
|
}
|
|
|
|
func GetCopyIdentityState(accountID int64, folder string, identity MessageIdentity) (CopyState, error) {
|
|
return GetCopyIdentityStateWithFolderAlias(accountID, folder, folder, identity)
|
|
}
|
|
|
|
func GetCopyIdentityStateWithFolderAlias(accountID int64, folder, legacyFolder string, identity MessageIdentity) (CopyState, error) {
|
|
if identity.MessageID == "" && identity.BodySHA256 == "" {
|
|
return CopyState{}, nil
|
|
}
|
|
folders := []string{folder}
|
|
if legacyFolder != "" && legacyFolder != folder {
|
|
folders = append(folders, legacyFolder)
|
|
}
|
|
aliases := []string{identity.MessageID, identity.LegacyMessageID}
|
|
aliases = append(aliases, identity.LegacyMessageIDs...)
|
|
if identity.MessageID == "" && identity.BodySHA256 != "" {
|
|
aliases = append(aliases, "sha256:"+identity.BodySHA256)
|
|
}
|
|
uniqueAliases := make([]string, 0, len(aliases))
|
|
seenAliases := map[string]bool{}
|
|
for _, alias := range aliases {
|
|
alias = strings.TrimSpace(alias)
|
|
if alias == "" || seenAliases[alias] {
|
|
continue
|
|
}
|
|
seenAliases[alias] = true
|
|
uniqueAliases = append(uniqueAliases, alias)
|
|
}
|
|
// Precise rows always win. For Message-ID-less mail the hash is the whole
|
|
// identity, irrespective of which legacy key happened to be stored beside it.
|
|
// For mail with an ID, historical normalizations are precise aliases only
|
|
// when the canonical body hash is identical.
|
|
var mboxDone, targetDone int
|
|
for _, candidateFolder := range folders {
|
|
if identity.MessageID == "" {
|
|
var mbox, target int
|
|
err := DB.QueryRow(`SELECT COALESCE(MAX(mbox_done),0), COALESCE(MAX(target_done),0) FROM copied
|
|
WHERE account_id=? AND folder=? AND body_sha256=?`, accountID, candidateFolder, identity.BodySHA256).Scan(&mbox, &target)
|
|
if err != nil {
|
|
return CopyState{}, err
|
|
}
|
|
mboxDone |= mbox
|
|
targetDone |= target
|
|
continue
|
|
}
|
|
for _, alias := range uniqueAliases {
|
|
var mbox, target int
|
|
err := DB.QueryRow(`SELECT COALESCE(MAX(mbox_done),0), COALESCE(MAX(target_done),0) FROM copied
|
|
WHERE account_id=? AND folder=? AND message_id=? AND body_sha256=?`,
|
|
accountID, candidateFolder, alias, identity.BodySHA256).Scan(&mbox, &target)
|
|
if err != nil {
|
|
return CopyState{}, err
|
|
}
|
|
mboxDone |= mbox
|
|
targetDone |= target
|
|
}
|
|
}
|
|
if mboxDone != 0 || targetDone != 0 {
|
|
return CopyState{MboxDone: mboxDone != 0, TargetDone: targetDone != 0}, nil
|
|
}
|
|
// Once a Message-ID has precise archive identities, a different body with
|
|
// that same ID is new and must not be hidden by the old wildcard row.
|
|
if len(uniqueAliases) != 0 && identity.BodySHA256 != "" {
|
|
var precise int
|
|
for _, candidateFolder := range folders {
|
|
for _, alias := range uniqueAliases {
|
|
var count int
|
|
if err := DB.QueryRow(`SELECT count(*) FROM copied WHERE account_id=? AND folder=? AND message_id=? AND body_sha256<>''`,
|
|
accountID, candidateFolder, alias).Scan(&count); err != nil {
|
|
return CopyState{}, err
|
|
}
|
|
precise += count
|
|
}
|
|
}
|
|
if precise > 0 {
|
|
return CopyState{}, nil
|
|
}
|
|
}
|
|
for _, alias := range uniqueAliases {
|
|
for _, candidateFolder := range folders {
|
|
var mbox, target int
|
|
err := DB.QueryRow(`SELECT COALESCE(MAX(mbox_done),0), COALESCE(MAX(target_done),0) FROM copied
|
|
WHERE account_id=? AND folder=? AND message_id=? AND body_sha256=''`, accountID, candidateFolder, alias).Scan(&mbox, &target)
|
|
if err != nil {
|
|
return CopyState{}, err
|
|
}
|
|
mboxDone |= mbox
|
|
targetDone |= target
|
|
}
|
|
}
|
|
return CopyState{MboxDone: mboxDone != 0, TargetDone: targetDone != 0}, nil
|
|
}
|
|
|
|
func MarkIdentityCopied(accountID int64, folder string, identity MessageIdentity) error {
|
|
return markCopyIdentity(accountID, folder, identity, true, true)
|
|
}
|
|
|
|
func MarkIdentityMboxCopied(accountID int64, folder string, identity MessageIdentity) error {
|
|
return markCopyIdentity(accountID, folder, identity, true, false)
|
|
}
|
|
|
|
func MarkIdentityTargetCopied(accountID int64, folder string, identity MessageIdentity) error {
|
|
return markCopyIdentity(accountID, folder, identity, false, true)
|
|
}
|
|
|
|
func markCopyIdentity(accountID int64, folder string, identity MessageIdentity, mboxDone, targetDone bool) error {
|
|
if identity.MessageID == "" && identity.BodySHA256 == "" {
|
|
return nil
|
|
}
|
|
_, err := DB.Exec(`INSERT INTO copied(account_id, folder, message_id, body_sha256, mbox_done, target_done)
|
|
VALUES(?,?,?,?,?,?)
|
|
ON CONFLICT(account_id, folder, message_id, body_sha256) DO UPDATE SET
|
|
mbox_done=MAX(copied.mbox_done, excluded.mbox_done),
|
|
target_done=MAX(copied.target_done, excluded.target_done),
|
|
copied_at=CURRENT_TIMESTAMP`, accountID, folder, identity.MessageID, identity.BodySHA256, boolInt(mboxDone), boolInt(targetDone))
|
|
return err
|
|
}
|
|
|
|
type MboxIndexEntry struct {
|
|
AccountID int64
|
|
Folder string
|
|
Seq int
|
|
MessageID string
|
|
BodySHA256 string
|
|
Subject string
|
|
From string
|
|
Date string
|
|
FileOffset int64
|
|
FrameLen int64
|
|
InnerOffset int64
|
|
InnerLen int64
|
|
}
|
|
|
|
func SaveMboxIndex(e MboxIndexEntry) error {
|
|
if DB == nil || (e.MessageID == "" && e.BodySHA256 == "") {
|
|
return nil
|
|
}
|
|
var seq int
|
|
if e.Seq > 0 {
|
|
seq = e.Seq
|
|
} else {
|
|
_ = DB.QueryRow(`SELECT COALESCE(MAX(seq), -1) + 1 FROM mbox_index WHERE account_id=? AND folder=?`, e.AccountID, e.Folder).Scan(&seq)
|
|
}
|
|
_, err := DB.Exec(`INSERT OR REPLACE INTO mbox_index(account_id, folder, seq, message_id, body_sha256, subject, from_addr, date, file_offset, frame_len, inner_offset, inner_len)
|
|
VALUES(?,?,?,?,?,?,?,?,?,?,?,?)`,
|
|
e.AccountID, e.Folder, seq, e.MessageID, e.BodySHA256, e.Subject, e.From, e.Date, e.FileOffset, e.FrameLen, e.InnerOffset, e.InnerLen)
|
|
return err
|
|
}
|
|
|
|
func ReplaceMboxIndex(accountID int64, folder string, entries []MboxIndexEntry, indexedBytes int64) error {
|
|
if DB == nil || accountID == 0 {
|
|
return nil
|
|
}
|
|
tx, err := DB.Begin()
|
|
if err != nil {
|
|
return err
|
|
}
|
|
defer tx.Rollback()
|
|
folderAliases := []string{folder}
|
|
aliasRows, err := tx.Query(`SELECT folder FROM copied WHERE account_id=? UNION SELECT folder FROM mbox_index WHERE account_id=?`, accountID, accountID)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
for aliasRows.Next() {
|
|
var candidate string
|
|
if err := aliasRows.Scan(&candidate); err != nil {
|
|
_ = aliasRows.Close()
|
|
return err
|
|
}
|
|
if candidate != folder && safeMboxName(candidate) == folder {
|
|
folderAliases = append(folderAliases, candidate)
|
|
}
|
|
}
|
|
if err := aliasRows.Err(); err != nil {
|
|
_ = aliasRows.Close()
|
|
return err
|
|
}
|
|
if err := aliasRows.Close(); err != nil {
|
|
return err
|
|
}
|
|
type storedIndexIdentity struct {
|
|
messageID string
|
|
bodySHA256 string
|
|
}
|
|
storedIdentityBySeq := map[int]storedIndexIdentity{}
|
|
for _, candidateFolder := range folderAliases {
|
|
rows, err := tx.Query(`SELECT seq, message_id, body_sha256 FROM mbox_index WHERE account_id=? AND folder=?`, accountID, candidateFolder)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
for rows.Next() {
|
|
var seq int
|
|
var identity storedIndexIdentity
|
|
if err := rows.Scan(&seq, &identity.messageID, &identity.bodySHA256); err != nil {
|
|
_ = rows.Close()
|
|
return err
|
|
}
|
|
if _, exists := storedIdentityBySeq[seq]; !exists || candidateFolder == folder {
|
|
storedIdentityBySeq[seq] = identity
|
|
}
|
|
}
|
|
if err := rows.Err(); err != nil {
|
|
_ = rows.Close()
|
|
return err
|
|
}
|
|
if err := rows.Close(); err != nil {
|
|
return err
|
|
}
|
|
}
|
|
if _, err := tx.Exec(`DELETE FROM mbox_index WHERE account_id=? AND folder=?`, accountID, folder); err != nil {
|
|
return err
|
|
}
|
|
for i, entry := range entries {
|
|
entry.AccountID = accountID
|
|
entry.Folder = folder
|
|
entry.Seq = i
|
|
storedIdentity := storedIdentityBySeq[i]
|
|
// The IMAP envelope and a later RFC-822 header parse can represent the
|
|
// same malformed Message-ID differently. If the canonical body at the
|
|
// same archive position is identical, retain the already committed ID so
|
|
// a rebuild is an exact metadata restore. Never carry it across a body
|
|
// change, because that could attach an old identity to different content.
|
|
if storedIdentity.bodySHA256 != "" && storedIdentity.bodySHA256 == entry.BodySHA256 {
|
|
entry.MessageID = storedIdentity.messageID
|
|
}
|
|
if entry.MessageID == "" && entry.BodySHA256 == "" {
|
|
continue
|
|
}
|
|
if _, err := tx.Exec(`INSERT INTO mbox_index(account_id, folder, seq, message_id, body_sha256, subject, from_addr, date, file_offset, frame_len, inner_offset, inner_len)
|
|
VALUES(?,?,?,?,?,?,?,?,?,?,?,?)`,
|
|
entry.AccountID, entry.Folder, entry.Seq, entry.MessageID, entry.BodySHA256, entry.Subject, entry.From, entry.Date,
|
|
entry.FileOffset, entry.FrameLen, entry.InnerOffset, entry.InnerLen); err != nil {
|
|
return err
|
|
}
|
|
// A full rebuild recovers records which reached the old append-first
|
|
// pipeline but whose index/copied writes lost a SQLITE_BUSY race. In that
|
|
// pipeline the target append happened before the mbox append, so recovered
|
|
// records are complete in both stages. Marking them prevents a later watch
|
|
// run from appending duplicates to either destination.
|
|
state, found, err := copyStateForReindex(tx, entry.AccountID, folderAliases, entry, storedIdentity.messageID)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
if !found {
|
|
state = CopyState{MboxDone: true, TargetDone: true}
|
|
}
|
|
if _, err := tx.Exec(`INSERT INTO copied(account_id, folder, message_id, body_sha256, mbox_done, target_done)
|
|
VALUES(?,?,?,?,?,?)
|
|
ON CONFLICT(account_id, folder, message_id, body_sha256) DO UPDATE SET mbox_done=1`,
|
|
entry.AccountID, entry.Folder, entry.MessageID, entry.BodySHA256, 1, boolInt(state.TargetDone)); err != nil {
|
|
return err
|
|
}
|
|
}
|
|
if _, err := tx.Exec(`INSERT INTO mbox_index_state(account_id, folder, indexed_bytes)
|
|
VALUES(?,?,?)
|
|
ON CONFLICT(account_id, folder) DO UPDATE SET indexed_bytes=excluded.indexed_bytes`,
|
|
accountID, folder, indexedBytes); err != nil {
|
|
return err
|
|
}
|
|
return tx.Commit()
|
|
}
|
|
|
|
// RemoveMboxIndexFolderAliases removes only superseded index metadata such as
|
|
// "INBOX.Newbies " after the actual archive file has been rebuilt under its
|
|
// canonical filesystem name "INBOX.Newbies". copied compatibility aliases and
|
|
// all mbox files remain untouched.
|
|
func RemoveMboxIndexFolderAliases(accountID int64, canonicalFolders map[string]bool) error {
|
|
rows, err := DB.Query(`SELECT folder FROM mbox_index WHERE account_id=? UNION SELECT folder FROM mbox_index_state WHERE account_id=?`, accountID, accountID)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
var stale []string
|
|
for rows.Next() {
|
|
var folder string
|
|
if err := rows.Scan(&folder); err != nil {
|
|
_ = rows.Close()
|
|
return err
|
|
}
|
|
if !canonicalFolders[folder] && canonicalFolders[safeMboxName(folder)] {
|
|
stale = append(stale, folder)
|
|
}
|
|
}
|
|
if err := rows.Err(); err != nil {
|
|
_ = rows.Close()
|
|
return err
|
|
}
|
|
if err := rows.Close(); err != nil {
|
|
return err
|
|
}
|
|
if len(stale) == 0 {
|
|
return nil
|
|
}
|
|
tx, err := DB.Begin()
|
|
if err != nil {
|
|
return err
|
|
}
|
|
defer tx.Rollback()
|
|
for _, folder := range stale {
|
|
if _, err := tx.Exec(`DELETE FROM mbox_index WHERE account_id=? AND folder=?`, accountID, folder); err != nil {
|
|
return err
|
|
}
|
|
if _, err := tx.Exec(`DELETE FROM mbox_index_state WHERE account_id=? AND folder=?`, accountID, folder); err != nil {
|
|
return err
|
|
}
|
|
}
|
|
return tx.Commit()
|
|
}
|
|
|
|
func copyStateForReindex(tx *sql.Tx, accountID int64, folders []string, entry MboxIndexEntry, legacyMessageID string) (CopyState, bool, error) {
|
|
type candidate struct{ messageID, bodyHash string }
|
|
candidates := []candidate{{entry.MessageID, entry.BodySHA256}}
|
|
if legacyMessageID != "" {
|
|
candidates = append(candidates, candidate{legacyMessageID, ""})
|
|
}
|
|
if entry.MessageID != "" {
|
|
candidates = append(candidates, candidate{entry.MessageID, ""})
|
|
} else if entry.BodySHA256 != "" {
|
|
candidates = append(candidates, candidate{"sha256:" + entry.BodySHA256, ""})
|
|
}
|
|
seen := map[candidate]bool{}
|
|
var state CopyState
|
|
found := false
|
|
for _, c := range candidates {
|
|
if seen[c] {
|
|
continue
|
|
}
|
|
seen[c] = true
|
|
for _, folder := range folders {
|
|
var count, mboxDone, targetDone int
|
|
if err := tx.QueryRow(`SELECT count(*), COALESCE(MAX(mbox_done),0), COALESCE(MAX(target_done),0)
|
|
FROM copied WHERE account_id=? AND folder=? AND message_id=? AND body_sha256=?`,
|
|
accountID, folder, c.messageID, c.bodyHash).Scan(&count, &mboxDone, &targetDone); err != nil {
|
|
return CopyState{}, false, err
|
|
}
|
|
if count > 0 {
|
|
found = true
|
|
state.MboxDone = state.MboxDone || mboxDone != 0
|
|
state.TargetDone = state.TargetDone || targetDone != 0
|
|
}
|
|
}
|
|
}
|
|
return state, found, nil
|
|
}
|
|
|
|
func UpdateMboxIndexState(accountID int64, folder string, indexedBytes int64) error {
|
|
if DB == nil || accountID == 0 {
|
|
return nil
|
|
}
|
|
_, err := DB.Exec(`INSERT INTO mbox_index_state(account_id, folder, indexed_bytes)
|
|
VALUES(?,?,?)
|
|
ON CONFLICT(account_id, folder) DO UPDATE SET indexed_bytes=excluded.indexed_bytes`,
|
|
accountID, folder, indexedBytes)
|
|
return err
|
|
}
|
|
|
|
func MboxIndexedBytes(accountID int64, folder string) (int64, error) {
|
|
if DB == nil || accountID == 0 {
|
|
return 0, sql.ErrNoRows
|
|
}
|
|
var n int64
|
|
err := DB.QueryRow(`SELECT indexed_bytes FROM mbox_index_state WHERE account_id=? AND folder=?`, accountID, folder).Scan(&n)
|
|
if errors.Is(err, sql.ErrNoRows) {
|
|
return 0, nil
|
|
}
|
|
return n, err
|
|
}
|
|
|
|
func ListMboxIndex(accountID int64, folder string) ([]MboxIndexEntry, error) {
|
|
if DB == nil || accountID == 0 {
|
|
return nil, sql.ErrNoRows
|
|
}
|
|
rows, err := DB.Query(`SELECT account_id, folder, seq, message_id, body_sha256, subject, from_addr, date, file_offset, frame_len, inner_offset, inner_len
|
|
FROM mbox_index WHERE account_id=? AND folder=? ORDER BY seq`, accountID, folder)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
defer rows.Close()
|
|
var out []MboxIndexEntry
|
|
for rows.Next() {
|
|
var e MboxIndexEntry
|
|
if err := rows.Scan(&e.AccountID, &e.Folder, &e.Seq, &e.MessageID, &e.BodySHA256, &e.Subject, &e.From, &e.Date, &e.FileOffset, &e.FrameLen, &e.InnerOffset, &e.InnerLen); err != nil {
|
|
return nil, err
|
|
}
|
|
out = append(out, e)
|
|
}
|
|
return out, rows.Err()
|
|
}
|
|
|
|
func GetMboxIndex(accountID int64, folder string, seq int) (MboxIndexEntry, error) {
|
|
if DB == nil || accountID == 0 {
|
|
return MboxIndexEntry{}, sql.ErrNoRows
|
|
}
|
|
row := DB.QueryRow(`SELECT account_id, folder, seq, message_id, body_sha256, subject, from_addr, date, file_offset, frame_len, inner_offset, inner_len
|
|
FROM mbox_index WHERE account_id=? AND folder=? AND seq=?`, accountID, folder, seq)
|
|
var e MboxIndexEntry
|
|
err := row.Scan(&e.AccountID, &e.Folder, &e.Seq, &e.MessageID, &e.BodySHA256, &e.Subject, &e.From, &e.Date, &e.FileOffset, &e.FrameLen, &e.InnerOffset, &e.InnerLen)
|
|
return e, err
|
|
}
|
|
|
|
func SeedAccountFromFile(path string) error {
|
|
b, err := os.ReadFile(path)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
var a Account
|
|
if err := json.Unmarshal(b, &a); err != nil {
|
|
return err
|
|
}
|
|
return SaveAccount(a)
|
|
}
|
|
|
|
type accountScanner interface {
|
|
Scan(dest ...any) error
|
|
}
|
|
|
|
type appUserScanner interface {
|
|
Scan(dest ...any) error
|
|
}
|
|
|
|
func scanAccount(s accountScanner) (Account, error) {
|
|
var a Account
|
|
var srcInsecure, dstInsecure, active int
|
|
err := s.Scan(&a.ID, &a.Name, &a.SrcHost, &a.SrcPort, &a.SrcSecurity, &srcInsecure, &a.SrcUser, &a.SrcPass, &a.SrcProto,
|
|
&a.DstHost, &a.DstPort, &a.DstSecurity, &dstInsecure, &a.DstUser, &a.DstPass, &a.MboxDir, &active)
|
|
a.SrcInsecure = srcInsecure != 0
|
|
a.DstInsecure = dstInsecure != 0
|
|
a.Active = active != 0
|
|
return a, err
|
|
}
|
|
|
|
func normalizeAccount(a *Account) {
|
|
if a.SrcProto == "" {
|
|
a.SrcProto = "imap"
|
|
}
|
|
if a.SrcSecurity == "" {
|
|
a.SrcSecurity = "tls"
|
|
}
|
|
if a.DstSecurity == "" {
|
|
a.DstSecurity = "tls"
|
|
}
|
|
if a.SrcPort == 0 {
|
|
if a.SrcSecurity == "tls" {
|
|
a.SrcPort = 993
|
|
} else {
|
|
a.SrcPort = 143
|
|
}
|
|
}
|
|
if a.DstPort == 0 {
|
|
if a.DstSecurity == "tls" {
|
|
a.DstPort = 993
|
|
} else {
|
|
a.DstPort = 143
|
|
}
|
|
}
|
|
a.MboxDir = strings.TrimSpace(a.MboxDir)
|
|
}
|
|
|
|
func scanAppUser(s appUserScanner) (AppUser, error) {
|
|
var u AppUser
|
|
var active int
|
|
err := s.Scan(&u.ID, &u.Username, &u.DisplayName, &u.PasswordHash, &u.Role, &active)
|
|
u.Role = normalizeRole(u.Role)
|
|
u.Active = active != 0
|
|
return u, err
|
|
}
|
|
|
|
func normalizeRole(role string) string {
|
|
switch strings.ToLower(strings.TrimSpace(role)) {
|
|
case "admin":
|
|
return "admin"
|
|
case "verwalter":
|
|
return "verwalter"
|
|
default:
|
|
return "user"
|
|
}
|
|
}
|
|
|
|
func boolInt(v bool) int {
|
|
if v {
|
|
return 1
|
|
}
|
|
return 0
|
|
}
|