leopold.stroblandClaude Opus 4.8
bdcb17285c
- Poll the public download page for "Aktuelle Version <nr> vom <date>", report changes through the normal change feed and notification mail, and show the current version as a card on the overview. Runs on its own interval (default 30 min); the first run only records a baseline. - Archive epirentServer.zip / epirentClient.zip per version number so we keep a download cache and history: streamed to disk with SHA-256 and size, fetched in a background thread so ticket polling is unaffected, with a free-space check before downloading (~1.1 GB per version). - Retention is configurable via EPIRENT_DOWNLOAD_KEEP, where 0 means unlimited; a successful archive is reported as a change and by mail. - New Downloads page listing the archived versions with checksums and links to fetch the cached files. - Notification lines now omit the arrow when there is no previous value and drop the "#" for entries without a ticket. Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
137 lines
3.8 KiB
Python
137 lines
3.8 KiB
Python
"""SQLite-Zugriff: Schema, Verbindung, Hilfsfunktionen.
|
|
|
|
Die Datenbank wird vom Poller-Dienst geschrieben und vom Web-Dienst gelesen.
|
|
WAL-Modus erlaubt gleichzeitiges Lesen/Schreiben ohne Sperrkonflikte.
|
|
"""
|
|
import sqlite3
|
|
from contextlib import contextmanager
|
|
|
|
from . import config
|
|
|
|
SCHEMA = """
|
|
CREATE TABLE IF NOT EXISTS tickets (
|
|
id INTEGER PRIMARY KEY,
|
|
number TEXT,
|
|
title TEXT,
|
|
state TEXT,
|
|
state_id INTEGER,
|
|
priority TEXT,
|
|
priority_id INTEGER,
|
|
owner TEXT,
|
|
owner_id INTEGER,
|
|
"group" TEXT,
|
|
customer TEXT,
|
|
article_count INTEGER,
|
|
zammad_created_at TEXT,
|
|
zammad_updated_at TEXT,
|
|
last_contact_at TEXT,
|
|
last_contact_agent_at TEXT,
|
|
last_contact_customer_at TEXT,
|
|
close_at TEXT,
|
|
last_close_at TEXT,
|
|
raw_json TEXT,
|
|
first_seen_at TEXT,
|
|
last_synced_at TEXT,
|
|
archived INTEGER NOT NULL DEFAULT 0
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS changes (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
ticket_id INTEGER NOT NULL,
|
|
ticket_number TEXT,
|
|
ticket_title TEXT,
|
|
field TEXT NOT NULL,
|
|
label TEXT,
|
|
old_value TEXT,
|
|
new_value TEXT,
|
|
detail TEXT,
|
|
detected_at TEXT NOT NULL,
|
|
seen INTEGER NOT NULL DEFAULT 0,
|
|
notified INTEGER NOT NULL DEFAULT 0
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_changes_detected ON changes(detected_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_changes_ticket ON changes(ticket_id);
|
|
CREATE INDEX IF NOT EXISTS idx_changes_seen ON changes(seen);
|
|
|
|
CREATE TABLE IF NOT EXISTS articles (
|
|
article_id INTEGER PRIMARY KEY,
|
|
ticket_id INTEGER NOT NULL,
|
|
sender TEXT,
|
|
frm TEXT,
|
|
subject TEXT,
|
|
body TEXT,
|
|
content_type TEXT,
|
|
internal INTEGER,
|
|
created_at TEXT,
|
|
attachments TEXT,
|
|
fetched_at TEXT
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_articles_ticket ON articles(ticket_id, created_at);
|
|
|
|
CREATE TABLE IF NOT EXISTS versions (
|
|
source TEXT PRIMARY KEY,
|
|
version TEXT,
|
|
released_at TEXT,
|
|
checked_at TEXT,
|
|
updated_at TEXT
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS downloads (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
version TEXT NOT NULL,
|
|
filename TEXT NOT NULL,
|
|
url TEXT,
|
|
size INTEGER,
|
|
sha256 TEXT,
|
|
remote_modified TEXT,
|
|
downloaded_at TEXT,
|
|
UNIQUE(version, filename)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_downloads_version ON downloads(version);
|
|
|
|
CREATE TABLE IF NOT EXISTS sync_runs (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
started_at TEXT,
|
|
finished_at TEXT,
|
|
ticket_count INTEGER,
|
|
change_count INTEGER,
|
|
status TEXT,
|
|
error TEXT
|
|
);
|
|
"""
|
|
|
|
|
|
def connect() -> sqlite3.Connection:
|
|
conn = sqlite3.connect(config.DB_PATH, timeout=30)
|
|
conn.row_factory = sqlite3.Row
|
|
conn.execute("PRAGMA journal_mode=WAL;")
|
|
conn.execute("PRAGMA busy_timeout=10000;")
|
|
conn.execute("PRAGMA foreign_keys=ON;")
|
|
return conn
|
|
|
|
|
|
@contextmanager
|
|
def get_conn():
|
|
conn = connect()
|
|
try:
|
|
yield conn
|
|
conn.commit()
|
|
finally:
|
|
conn.close()
|
|
|
|
|
|
def init_db() -> None:
|
|
with get_conn() as conn:
|
|
conn.executescript(SCHEMA)
|
|
_migrate(conn)
|
|
|
|
|
|
def _migrate(conn) -> None:
|
|
"""Nachträgliche Schema-Anpassungen für bestehende Datenbanken."""
|
|
cols = {r["name"] for r in conn.execute("PRAGMA table_info(tickets)")}
|
|
if "archived" not in cols:
|
|
conn.execute("ALTER TABLE tickets ADD COLUMN archived INTEGER NOT NULL DEFAULT 0")
|