- detection from legacy category only for scores >= 0.5, without ''/none - category_label + view detection_labeled unify ICU and legacy labels - schema/003 + migrate_legacy --dedupe removes Gundi twins of legacy images (same imei, same size, <= 10 min), archived in image_superseded - migrate_legacy --plan: read-only preview of the migration - Gundi S3 prefix icu/gundi-test/ -> icu/gundi/ Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
781 lines
30 KiB
Python
781 lines
30 KiB
Python
"""Push-Pfad (Gundi v2): Mapping ins Fachmodell, Ingest (Stufe 1), OCR (Stufe 2), Sweep."""
|
|
import os
|
|
import re
|
|
import subprocess
|
|
import tempfile
|
|
import threading
|
|
import time
|
|
import uuid as uuidlib
|
|
from concurrent.futures import ThreadPoolExecutor
|
|
from dataclasses import dataclass
|
|
from datetime import datetime, timezone
|
|
from pathlib import Path
|
|
from typing import Optional
|
|
|
|
import psycopg
|
|
import yaml
|
|
from botocore.config import Config as BotoClientConfig
|
|
from dateutil import tz
|
|
from psycopg.types.json import Json
|
|
|
|
from main import (
|
|
AppConfig,
|
|
S3,
|
|
exif_write_with_exiftool,
|
|
is_transient_error,
|
|
make_thumbnail_bytes,
|
|
ocr_extract_timestamp,
|
|
to_exif_datetime,
|
|
)
|
|
|
|
SCHEMA_FILE = Path(__file__).resolve().parent / "schema" / "001_domain_model.sql"
|
|
SCHEMA_LOCK_KEY = 7_311_000_001
|
|
UUID_STRICT_RE = re.compile(r"^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$")
|
|
|
|
# ICU sendet "icu_cameratrap_rep" (Gundi/EarthRanger-Standard waere "cameratrap_rep"); beide gelten als Bild-Event.
|
|
EVENT_CAMERATRAP_TYPES = ("cameratrap_rep", "icu_cameratrap_rep")
|
|
EVENT_STATUS = "camera_status_report"
|
|
MIN_API_KEY_LEN = 24
|
|
|
|
|
|
# -----------------------
|
|
# Config
|
|
# -----------------------
|
|
@dataclass
|
|
class GundiConfig:
|
|
enabled: bool
|
|
api_key: str
|
|
bind_host: str = "0.0.0.0"
|
|
bind_port: int = 8090
|
|
inbox_prefix: str = "icu/gundi/inbox/"
|
|
processed_prefix: str = "icu/gundi/processed/"
|
|
thumb_prefix: str = "icu/gundi/thumbnails/"
|
|
workers: int = 4
|
|
ocr_workers: int = 2
|
|
max_request_bytes: int = 32 * 1024 * 1024
|
|
abandon_after_hours: int = 24
|
|
sweep_seconds: int = 60
|
|
stale_after_seconds: int = 300
|
|
max_attempts: int = 3
|
|
# S3 im Request-Pfad (Bild-Upload): schnell scheitern -> 503 + Retry-After statt minutenlang haengen
|
|
upload_connect_timeout_seconds: int = 5
|
|
upload_read_timeout_seconds: int = 30
|
|
upload_max_attempts: int = 2 # inkl. erstem Versuch
|
|
# Backlog-Alarm: so lange darf ein Eintrag in RECEIVED/AWAITING/OCR-PENDING haengen
|
|
alarm_after_seconds: int = 1800
|
|
|
|
|
|
def load_gundi_config(path: str) -> GundiConfig:
|
|
with open(path, "r", encoding="utf-8") as f:
|
|
raw = yaml.safe_load(f) or {}
|
|
g = raw.get("gundi") or {}
|
|
defaults = GundiConfig(enabled=False, api_key="")
|
|
|
|
def prefix(key: str) -> str:
|
|
value = str(g.get(key, getattr(defaults, key)))
|
|
return value if value.endswith("/") else value + "/"
|
|
|
|
gcfg = GundiConfig(
|
|
enabled=bool(g.get("enabled", False)),
|
|
api_key=os.environ.get("GUNDI_API_KEY", ""),
|
|
bind_host=str(g.get("bind_host", defaults.bind_host)),
|
|
bind_port=int(g.get("bind_port", defaults.bind_port)),
|
|
inbox_prefix=prefix("inbox_prefix"),
|
|
processed_prefix=prefix("processed_prefix"),
|
|
thumb_prefix=prefix("thumb_prefix"),
|
|
workers=max(1, int(g.get("workers", defaults.workers))),
|
|
ocr_workers=max(1, int(g.get("ocr_workers", defaults.ocr_workers))),
|
|
max_request_bytes=int(g.get("max_request_bytes", defaults.max_request_bytes)),
|
|
abandon_after_hours=int(g.get("abandon_after_hours", defaults.abandon_after_hours)),
|
|
sweep_seconds=int(g.get("sweep_seconds", defaults.sweep_seconds)),
|
|
stale_after_seconds=int(g.get("stale_after_seconds", defaults.stale_after_seconds)),
|
|
max_attempts=max(1, int(g.get("max_attempts", defaults.max_attempts))),
|
|
upload_connect_timeout_seconds=max(1, int(g.get("upload_connect_timeout_seconds", defaults.upload_connect_timeout_seconds))),
|
|
upload_read_timeout_seconds=max(1, int(g.get("upload_read_timeout_seconds", defaults.upload_read_timeout_seconds))),
|
|
upload_max_attempts=max(1, int(g.get("upload_max_attempts", defaults.upload_max_attempts))),
|
|
alarm_after_seconds=max(60, int(g.get("alarm_after_seconds", defaults.alarm_after_seconds))),
|
|
)
|
|
if gcfg.enabled and len(gcfg.api_key) < MIN_API_KEY_LEN:
|
|
raise SystemExit(
|
|
f"GUNDI_API_KEY fehlt oder ist kuerzer als {MIN_API_KEY_LEN} Zeichen "
|
|
"(gundi.enabled ist aktiv). Bitte in der .env setzen."
|
|
)
|
|
return gcfg
|
|
|
|
|
|
def connect(dsn: str) -> psycopg.Connection:
|
|
return psycopg.connect(dsn, connect_timeout=10)
|
|
|
|
|
|
def apply_schema(dsn: str) -> None:
|
|
sql = SCHEMA_FILE.read_text(encoding="utf-8")
|
|
with connect(dsn) as conn, conn.cursor() as cur:
|
|
cur.execute("SELECT pg_advisory_xact_lock(%s)", (SCHEMA_LOCK_KEY,))
|
|
cur.execute(sql)
|
|
|
|
|
|
def utc_now_str() -> str:
|
|
return datetime.now(timezone.utc).replace(microsecond=0).isoformat().replace("+00:00", "Z")
|
|
|
|
|
|
# -----------------------
|
|
# Validierung + Mapping
|
|
# -----------------------
|
|
class EventError(ValueError):
|
|
pass
|
|
|
|
|
|
def _num(v) -> Optional[float]:
|
|
if isinstance(v, bool) or not isinstance(v, (int, float)):
|
|
return None
|
|
return float(v)
|
|
|
|
|
|
def _int(v) -> Optional[int]:
|
|
if isinstance(v, bool):
|
|
return None
|
|
if isinstance(v, int):
|
|
return v
|
|
if isinstance(v, float) and v.is_integer():
|
|
return int(v)
|
|
if isinstance(v, str) and re.fullmatch(r"-?\d+", v.strip()):
|
|
return int(v.strip())
|
|
return None
|
|
|
|
|
|
def _bool(v) -> Optional[bool]:
|
|
if isinstance(v, bool):
|
|
return v
|
|
if isinstance(v, str) and v.strip().lower() in ("true", "false"):
|
|
return v.strip().lower() == "true"
|
|
return None
|
|
|
|
|
|
def _str(v) -> Optional[str]:
|
|
if v is None:
|
|
return None
|
|
s = str(v).strip()
|
|
return s or None
|
|
|
|
|
|
def parse_ts(value) -> datetime:
|
|
if not isinstance(value, str) or not value.strip():
|
|
raise EventError("recorded_at fehlt")
|
|
s = value.strip()
|
|
if s.endswith("Z"):
|
|
s = s[:-1] + "+00:00"
|
|
try:
|
|
dt = datetime.fromisoformat(s)
|
|
except ValueError:
|
|
raise EventError(f"recorded_at ungueltig: {value!r}") from None
|
|
if dt.tzinfo is None:
|
|
dt = dt.replace(tzinfo=timezone.utc)
|
|
return dt.astimezone(timezone.utc)
|
|
|
|
|
|
def coords(location) -> tuple[Optional[float], Optional[float]]:
|
|
"""ICU meldet unbekannte Koordinaten als 0.0/0.0 -> NULL."""
|
|
if not isinstance(location, dict):
|
|
return None, None
|
|
lat, lon = _num(location.get("lat")), _num(location.get("lon"))
|
|
if lat is None or lon is None or (lat == 0 and lon == 0):
|
|
return None, None
|
|
return lat, lon
|
|
|
|
|
|
def validate_event(ev) -> dict:
|
|
if not isinstance(ev, dict):
|
|
raise EventError("Event muss ein JSON-Objekt sein")
|
|
event_type = ev.get("event_type")
|
|
if not isinstance(event_type, str) or not event_type:
|
|
raise EventError("event_type fehlt")
|
|
source = ev.get("source")
|
|
if not isinstance(source, str) or not source.strip():
|
|
raise EventError("source fehlt")
|
|
details = ev.get("event_details")
|
|
if details is None:
|
|
details = {}
|
|
if not isinstance(details, dict):
|
|
raise EventError("event_details muss ein Objekt sein")
|
|
if event_type in EVENT_CAMERATRAP_TYPES:
|
|
dets = details.get("detections", [])
|
|
if not isinstance(dets, list) or not all(isinstance(d, dict) for d in dets):
|
|
raise EventError("event_details.detections muss eine Liste von Objekten sein")
|
|
lat, lon = coords(ev.get("location"))
|
|
return {
|
|
"event_type": event_type,
|
|
"source": source.strip(),
|
|
"recorded_at": parse_ts(ev.get("recorded_at")),
|
|
"lat": lat,
|
|
"lon": lon,
|
|
"details": details,
|
|
}
|
|
|
|
|
|
def camera_status_row(env: dict, object_id: str, raw: dict) -> dict:
|
|
d = env["details"]
|
|
return {
|
|
"imei": env["source"],
|
|
"reported_at": env["recorded_at"],
|
|
"iccid": _str(d.get("iccid")),
|
|
"init_key": _str(d.get("init_key")),
|
|
"camera_name": _str(d.get("camera_name")),
|
|
"camera_model": _str(d.get("camera_model")),
|
|
"firmware_version": _str(d.get("firmware_version")),
|
|
"camera_language": _str(d.get("camera_language")),
|
|
"customer_timezone": _str(d.get("customer_timezone")),
|
|
"mcc": _str(d.get("mobile_country_code")),
|
|
"mnc": _str(d.get("mobile_network_code")),
|
|
"lac": _str(d.get("location_area_code")),
|
|
"cell_id": _str(d.get("cell_id")),
|
|
"battery_pct": _int(d.get("battery_status")),
|
|
"signal_strength": _int(d.get("signal_strength")),
|
|
"temperature_c": _int(d.get("temperature")),
|
|
"coordinate_switch": _bool(d.get("coordinate_switch")),
|
|
"work_period": _bool(d.get("work_period")),
|
|
"work_start": _str(d.get("work_start")),
|
|
"work_end": _str(d.get("work_end")),
|
|
"image_format": _str(d.get("image_format")),
|
|
"image_night": _str(d.get("image_night")),
|
|
"thumbnail_size": _str(d.get("thumbnail_size")),
|
|
"pir_interval": _str(d.get("pir_interval")),
|
|
"time_scan": _str(d.get("time_scan")),
|
|
"lat": env["lat"],
|
|
"lon": env["lon"],
|
|
"source": "gundi",
|
|
"source_ref": object_id,
|
|
"raw": Json(raw),
|
|
}
|
|
|
|
|
|
def detection_rows(env: dict, image_uuid: str) -> list[dict]:
|
|
rows = []
|
|
for det in env["details"].get("detections") or []:
|
|
category = _str(det.get("category"))
|
|
if category is None:
|
|
continue
|
|
pct = _num(det.get("confidence_percent"))
|
|
confidence = None if pct is None else min(max(pct / 100.0, 0.0), 1.0)
|
|
bbox = [_int(det.get(k)) for k in ("x_px", "y_px", "width_px", "height_px")]
|
|
if all(v in (None, 0) for v in bbox):
|
|
bbox = [None, None, None, None]
|
|
rows.append({
|
|
"image_uuid": image_uuid,
|
|
"category": category,
|
|
"confidence": confidence,
|
|
"bbox_x": bbox[0],
|
|
"bbox_y": bbox[1],
|
|
"bbox_w": bbox[2],
|
|
"bbox_h": bbox[3],
|
|
"model_version": "icu",
|
|
"source": "gundi",
|
|
})
|
|
return rows
|
|
|
|
|
|
def image_row(env: dict, object_id: str, raw: dict, size_bytes: int, processed_key: str, thumb_key: str) -> dict:
|
|
d = env["details"]
|
|
return {
|
|
"image_uuid": object_id,
|
|
"imei": env["source"],
|
|
"servertime": env["recorded_at"],
|
|
"ocr_state": "PENDING",
|
|
"camera_name": _str(d.get("cameratraprep_camera-name")),
|
|
"camera_make": _str(d.get("cameratraprep_camera-make")),
|
|
"lat": env["lat"],
|
|
"lon": env["lon"],
|
|
"size_bytes": size_bytes,
|
|
"processed_key": processed_key,
|
|
"thumbnail_key": thumb_key,
|
|
"source": "gundi",
|
|
"source_ref": object_id,
|
|
"raw": Json(raw),
|
|
}
|
|
|
|
|
|
def _insert(cur, table: str, row: dict, suffix: str = "") -> int:
|
|
cols = list(row)
|
|
cur.execute(
|
|
f"INSERT INTO {table} ({', '.join(cols)}) VALUES ({', '.join(['%s'] * len(cols))}) {suffix}",
|
|
[row[c] for c in cols],
|
|
)
|
|
return cur.rowcount
|
|
|
|
|
|
# -----------------------
|
|
# Receiver-seitige Persistenz (synchron im Request)
|
|
# -----------------------
|
|
def store_events(dsn: str, items: list[tuple[dict, dict]]) -> list[dict]:
|
|
"""Ein Request = eine Transaktion. Antwort erst nach Commit."""
|
|
results = []
|
|
with connect(dsn) as conn, conn.cursor() as cur:
|
|
for env, raw in items:
|
|
object_id = str(uuidlib.uuid4())
|
|
if env["event_type"] == EVENT_STATUS:
|
|
state = "INGESTED"
|
|
elif env["event_type"] in EVENT_CAMERATRAP_TYPES:
|
|
state = "AWAITING_ATTACHMENT"
|
|
else:
|
|
state = "IGNORED"
|
|
_insert(cur, "remote_cam.gundi_inbox", {
|
|
"object_id": object_id,
|
|
"event_type": env["event_type"],
|
|
"source": env["source"],
|
|
"recorded_at": env["recorded_at"],
|
|
"payload": Json(raw),
|
|
"state": state,
|
|
})
|
|
if env["event_type"] == EVENT_STATUS:
|
|
_insert(
|
|
cur, "remote_cam.camera_status", camera_status_row(env, object_id, raw),
|
|
"ON CONFLICT (imei, reported_at) WHERE source = 'gundi' DO NOTHING",
|
|
)
|
|
results.append({
|
|
"object_id": object_id,
|
|
"created_at": utc_now_str(),
|
|
"event_type": env["event_type"],
|
|
"source": env["source"],
|
|
"state": state,
|
|
})
|
|
return results
|
|
|
|
|
|
def attach_image(dsn: str, s3: S3, gcfg: GundiConfig, object_id: str, jpg: bytes) -> str:
|
|
"""Legt das JPEG durable in der Inbox ab. Liefert 'stored' oder 'duplicate'.
|
|
|
|
Der S3-Upload laeuft OHNE offene DB-Transaktion/Zeilensperre: bei einer S3-Stoerung
|
|
blockiert der Request weder eine DB-Verbindung noch den Datensatz. Der Key ist pro
|
|
object_id fest, ein paralleler/wiederholter Upload ueberschreibt mit gleichem Inhalt.
|
|
"""
|
|
with connect(dsn) as conn, conn.cursor() as cur:
|
|
cur.execute(
|
|
"SELECT event_type, state FROM remote_cam.gundi_inbox WHERE object_id = %s",
|
|
(object_id,),
|
|
)
|
|
row = cur.fetchone()
|
|
if not row or row[0] not in EVENT_CAMERATRAP_TYPES:
|
|
raise LookupError(object_id)
|
|
if row[1] in ("RECEIVED", "INGESTED"):
|
|
return "duplicate"
|
|
|
|
key = f"{gcfg.inbox_prefix}{object_id}.jpg"
|
|
s3.put_bytes(key, jpg, "image/jpeg")
|
|
|
|
with connect(dsn) as conn, conn.cursor() as cur:
|
|
cur.execute(
|
|
"""
|
|
UPDATE remote_cam.gundi_inbox
|
|
SET state = 'RECEIVED', attachment_key = %s, attachment_received_at = now(),
|
|
attempts = 0, last_error = NULL, updated_at = now()
|
|
WHERE object_id = %s AND state NOT IN ('RECEIVED', 'INGESTED')
|
|
""",
|
|
(key, object_id),
|
|
)
|
|
if cur.rowcount == 0:
|
|
return "duplicate"
|
|
return "stored"
|
|
|
|
|
|
# -----------------------
|
|
# EXIF
|
|
# -----------------------
|
|
def exif_set_digitized(jpg: bytes, servertime: datetime, *, timeout_seconds: int) -> bytes:
|
|
"""Stufe 1: nur Serverzeit. DateTimeOriginal folgt nach dem OCR (Stufe 2)."""
|
|
dtd = to_exif_datetime(servertime.astimezone(timezone.utc))
|
|
with tempfile.TemporaryDirectory() as td:
|
|
path = os.path.join(td, "in.jpg")
|
|
with open(path, "wb") as f:
|
|
f.write(jpg)
|
|
subprocess.run(
|
|
[
|
|
"exiftool", "-overwrite_original",
|
|
f"-DateTimeDigitized={dtd}", "-OffsetTimeDigitized=+00:00",
|
|
f"-ModifyDate={dtd}", "-OffsetTime=+00:00",
|
|
path,
|
|
],
|
|
check=True, stdout=subprocess.PIPE, stderr=subprocess.PIPE, timeout=timeout_seconds,
|
|
)
|
|
with open(path, "rb") as f:
|
|
return f.read()
|
|
|
|
|
|
# -----------------------
|
|
# Pipeline
|
|
# -----------------------
|
|
class Pipeline:
|
|
def __init__(self, cfg: AppConfig, gcfg: GundiConfig):
|
|
self.cfg = cfg
|
|
self.gcfg = gcfg
|
|
# Hintergrund (ingest/ocr): geduldig, Poller-Timeouts/-Retries; Request-Pfad (Upload): schnell.
|
|
# Adressierung/Signatur bleiben wie bisher (nur Timeouts und Retries werden gesetzt).
|
|
self.s3 = S3(cfg, self._s3_config(
|
|
cfg, cfg.s3_connect_timeout_seconds, cfg.s3_read_timeout_seconds,
|
|
cfg.s3_max_attempts, cfg.s3_retry_mode,
|
|
))
|
|
self.s3_request = S3(cfg, self._s3_config(
|
|
cfg, gcfg.upload_connect_timeout_seconds, gcfg.upload_read_timeout_seconds,
|
|
gcfg.upload_max_attempts, "standard",
|
|
))
|
|
self._ingest_pool = ThreadPoolExecutor(gcfg.workers, thread_name_prefix="ingest")
|
|
self._ocr_pool = ThreadPoolExecutor(gcfg.ocr_workers, thread_name_prefix="ocr")
|
|
self._stop = threading.Event()
|
|
# Gleiche IDs nicht mehrfach einreihen (sonst staut der Sweep bei S3-Ausfall alle 60 s neu auf)
|
|
self._inflight: set[tuple[str, str]] = set()
|
|
self._inflight_lock = threading.Lock()
|
|
self._last_alarm = 0.0
|
|
|
|
@staticmethod
|
|
def _s3_config(cfg: AppConfig, connect_timeout: int, read_timeout: int,
|
|
max_attempts: int, retry_mode: str) -> BotoClientConfig:
|
|
return BotoClientConfig(
|
|
max_pool_connections=cfg.s3_max_pool_connections,
|
|
connect_timeout=connect_timeout,
|
|
read_timeout=read_timeout,
|
|
retries={"mode": retry_mode, "total_max_attempts": max_attempts},
|
|
)
|
|
|
|
def start(self) -> None:
|
|
threading.Thread(target=self._sweep_loop, name="sweep", daemon=True).start()
|
|
|
|
def stop(self) -> None:
|
|
self._stop.set()
|
|
self._ingest_pool.shutdown(wait=False, cancel_futures=True)
|
|
self._ocr_pool.shutdown(wait=False, cancel_futures=True)
|
|
|
|
def submit_ingest(self, object_id: str) -> None:
|
|
self._submit(self._ingest_pool, self.ingest, object_id)
|
|
|
|
def submit_ocr(self, image_uuid: str) -> None:
|
|
self._submit(self._ocr_pool, self.ocr, image_uuid)
|
|
|
|
def _submit(self, pool: ThreadPoolExecutor, fn, arg: str) -> None:
|
|
token = (fn.__name__, arg)
|
|
with self._inflight_lock:
|
|
if token in self._inflight:
|
|
return
|
|
self._inflight.add(token)
|
|
try:
|
|
pool.submit(self._guarded, token, fn, arg)
|
|
except Exception:
|
|
with self._inflight_lock:
|
|
self._inflight.discard(token)
|
|
raise
|
|
|
|
def _guarded(self, token: tuple[str, str], fn, arg: str) -> None:
|
|
try:
|
|
fn(arg)
|
|
except Exception as e:
|
|
print(f"[GUNDI] {fn.__name__} {arg} unerwarteter Fehler: {type(e).__name__}: {e}", flush=True)
|
|
finally:
|
|
with self._inflight_lock:
|
|
self._inflight.discard(token)
|
|
|
|
# --- Stufe 1 ---------------------------------------------------------
|
|
def ingest(self, object_id: str) -> None:
|
|
start = time.perf_counter()
|
|
try:
|
|
with connect(self.cfg.pg_dsn) as conn, conn.cursor() as cur:
|
|
cur.execute(
|
|
"""
|
|
SELECT payload, attachment_key FROM remote_cam.gundi_inbox
|
|
WHERE object_id = %s AND state = 'RECEIVED'
|
|
FOR UPDATE SKIP LOCKED
|
|
""",
|
|
(object_id,),
|
|
)
|
|
row = cur.fetchone()
|
|
if not row:
|
|
return
|
|
payload, attachment_key = row
|
|
env = validate_event(payload)
|
|
jpg = self.s3.get_bytes(attachment_key)
|
|
|
|
exif_note = "ok"
|
|
try:
|
|
processed = exif_set_digitized(
|
|
jpg, env["recorded_at"], timeout_seconds=self.cfg.exiftool_timeout_seconds
|
|
)
|
|
except Exception as e:
|
|
processed = jpg
|
|
exif_note = f"fail({type(e).__name__})"
|
|
thumb = make_thumbnail_bytes(jpg, self.cfg.thumb_max)
|
|
|
|
processed_key = f"{self.gcfg.processed_prefix}{object_id}.jpg"
|
|
thumb_key = f"{self.gcfg.thumb_prefix}{object_id}.jpg"
|
|
self.s3.put_bytes(processed_key, processed, "image/jpeg")
|
|
self.s3.put_bytes(thumb_key, thumb, "image/jpeg")
|
|
|
|
n_det = 0
|
|
inserted = _insert(
|
|
cur, "remote_cam.image",
|
|
image_row(env, object_id, payload, len(jpg), processed_key, thumb_key),
|
|
"ON CONFLICT (image_uuid) DO NOTHING",
|
|
)
|
|
if inserted:
|
|
for det in detection_rows(env, object_id):
|
|
n_det += _insert(cur, "remote_cam.detection", det)
|
|
cur.execute(
|
|
"""
|
|
UPDATE remote_cam.gundi_inbox
|
|
SET state = 'INGESTED', last_error = NULL, updated_at = now()
|
|
WHERE object_id = %s
|
|
""",
|
|
(object_id,),
|
|
)
|
|
except Exception as e:
|
|
self._record_ingest_failure(object_id, e)
|
|
return
|
|
|
|
try:
|
|
self.s3.delete_keys([attachment_key])
|
|
except Exception as e:
|
|
print(f"[GUNDI] inbox-cleanup {object_id} fehlgeschlagen: {e}", flush=True)
|
|
self.submit_ocr(object_id)
|
|
ms = int((time.perf_counter() - start) * 1000)
|
|
print(
|
|
f"[GUNDI] ingest object_id={object_id} imei={env['source']} detections={n_det} "
|
|
f"exif={exif_note} ms={ms}",
|
|
flush=True,
|
|
)
|
|
|
|
def _record_ingest_failure(self, object_id: str, err: Exception) -> None:
|
|
msg = f"{type(err).__name__}: {err}"[:1000]
|
|
if is_transient_error(err):
|
|
# S3/DB gestoert, nicht der Datensatz: Versuch nicht zaehlen (sonst FAILED nach ~15 min
|
|
# Ausfall). updated_at neu setzen -> der Sweep probiert es nach stale_after_seconds wieder.
|
|
with connect(self.cfg.pg_dsn) as conn, conn.cursor() as cur:
|
|
cur.execute(
|
|
"""
|
|
UPDATE remote_cam.gundi_inbox
|
|
SET last_error = %s, updated_at = now()
|
|
WHERE object_id = %s AND state = 'RECEIVED'
|
|
""",
|
|
(msg, object_id),
|
|
)
|
|
print(f"[GUNDI] ingest object_id={object_id} STOERUNG (S3/DB), kein Versuch gezaehlt, Sweep wiederholt: {msg}", flush=True)
|
|
return
|
|
with connect(self.cfg.pg_dsn) as conn, conn.cursor() as cur:
|
|
cur.execute(
|
|
"""
|
|
UPDATE remote_cam.gundi_inbox
|
|
SET attempts = attempts + 1,
|
|
state = CASE WHEN attempts + 1 >= %s THEN 'FAILED' ELSE state END,
|
|
last_error = %s, updated_at = now()
|
|
WHERE object_id = %s AND state = 'RECEIVED'
|
|
RETURNING attempts, state
|
|
""",
|
|
(self.gcfg.max_attempts, msg, object_id),
|
|
)
|
|
row = cur.fetchone()
|
|
if row:
|
|
print(f"[GUNDI] ingest object_id={object_id} FEHLER attempt={row[0]} state={row[1]} error={msg}", flush=True)
|
|
|
|
# --- Stufe 2 ---------------------------------------------------------
|
|
def ocr(self, image_uuid: str) -> None:
|
|
start = time.perf_counter()
|
|
try:
|
|
with connect(self.cfg.pg_dsn) as conn, conn.cursor() as cur:
|
|
cur.execute(
|
|
"""
|
|
SELECT imei, servertime, processed_key FROM remote_cam.image
|
|
WHERE image_uuid = %s AND ocr_state = 'PENDING'
|
|
FOR UPDATE SKIP LOCKED
|
|
""",
|
|
(image_uuid,),
|
|
)
|
|
row = cur.fetchone()
|
|
if not row:
|
|
return
|
|
imei, servertime, processed_key = row
|
|
tzinfo, tzname = self._camera_tz(cur, imei, servertime)
|
|
jpg = self.s3.get_bytes(processed_key)
|
|
ocr_ts = ocr_extract_timestamp(
|
|
jpg, self.cfg.ocr_crop_w_frac, self.cfg.ocr_crop_h_frac, tzinfo
|
|
)
|
|
|
|
exif_err = None
|
|
try:
|
|
patched = exif_write_with_exiftool(
|
|
jpg, ocr_ts, servertime, timeout_seconds=self.cfg.exiftool_timeout_seconds
|
|
)
|
|
self.s3.put_bytes(processed_key, patched, "image/jpeg")
|
|
except Exception as e:
|
|
exif_err = f"exif: {type(e).__name__}: {e}"[:1000]
|
|
|
|
cur.execute(
|
|
"""
|
|
UPDATE remote_cam.image
|
|
SET ocr_ts = %s, ocr_state = 'OK', ocr_attempts = ocr_attempts + 1,
|
|
ocr_error = %s, updated_at = now()
|
|
WHERE image_uuid = %s
|
|
""",
|
|
(ocr_ts, exif_err, image_uuid),
|
|
)
|
|
except Exception as e:
|
|
self._record_ocr_failure(image_uuid, e)
|
|
return
|
|
|
|
drift = int((servertime - ocr_ts).total_seconds())
|
|
ms = int((time.perf_counter() - start) * 1000)
|
|
print(
|
|
f"[GUNDI] ocr image_uuid={image_uuid} ocr_ts={ocr_ts.isoformat()} tz={tzname} "
|
|
f"servertime-ocr={drift}s exif={'ok' if exif_err is None else 'fail'} ms={ms}",
|
|
flush=True,
|
|
)
|
|
|
|
@staticmethod
|
|
def _camera_tz(cur, imei: str, servertime: datetime):
|
|
cur.execute(
|
|
"""
|
|
SELECT customer_timezone FROM remote_cam.camera_status
|
|
WHERE imei = %s AND customer_timezone IS NOT NULL AND reported_at <= %s
|
|
ORDER BY reported_at DESC LIMIT 1
|
|
""",
|
|
(imei, servertime),
|
|
)
|
|
row = cur.fetchone()
|
|
if not row:
|
|
cur.execute(
|
|
"""
|
|
SELECT customer_timezone FROM remote_cam.camera_status
|
|
WHERE imei = %s AND customer_timezone IS NOT NULL
|
|
ORDER BY reported_at ASC LIMIT 1
|
|
""",
|
|
(imei,),
|
|
)
|
|
row = cur.fetchone()
|
|
name = row[0] if row else "UTC"
|
|
zone = tz.gettz(name)
|
|
if zone is None:
|
|
return timezone.utc, "UTC"
|
|
return zone, name
|
|
|
|
def _record_ocr_failure(self, image_uuid: str, err: Exception) -> None:
|
|
msg = f"{type(err).__name__}: {err}"[:1000]
|
|
if is_transient_error(err):
|
|
with connect(self.cfg.pg_dsn) as conn, conn.cursor() as cur:
|
|
cur.execute(
|
|
"""
|
|
UPDATE remote_cam.image
|
|
SET ocr_error = %s, updated_at = now()
|
|
WHERE image_uuid = %s AND ocr_state = 'PENDING'
|
|
""",
|
|
(msg, image_uuid),
|
|
)
|
|
print(f"[GUNDI] ocr image_uuid={image_uuid} STOERUNG (S3/DB), kein Versuch gezaehlt, Sweep wiederholt: {msg}", flush=True)
|
|
return
|
|
with connect(self.cfg.pg_dsn) as conn, conn.cursor() as cur:
|
|
cur.execute(
|
|
"""
|
|
UPDATE remote_cam.image
|
|
SET ocr_attempts = ocr_attempts + 1,
|
|
ocr_state = CASE WHEN ocr_attempts + 1 >= %s THEN 'FAIL' ELSE ocr_state END,
|
|
ocr_error = %s, updated_at = now()
|
|
WHERE image_uuid = %s AND ocr_state = 'PENDING'
|
|
RETURNING ocr_attempts, ocr_state
|
|
""",
|
|
(self.gcfg.max_attempts, msg, image_uuid),
|
|
)
|
|
row = cur.fetchone()
|
|
if row:
|
|
print(f"[GUNDI] ocr image_uuid={image_uuid} FEHLER attempt={row[0]} state={row[1]} error={msg}", flush=True)
|
|
|
|
# --- Sweep -----------------------------------------------------------
|
|
def _sweep_loop(self) -> None:
|
|
delay = 0
|
|
while not self._stop.wait(delay):
|
|
delay = self.gcfg.sweep_seconds
|
|
try:
|
|
self.sweep()
|
|
except Exception as e:
|
|
print(f"[GUNDI] sweep Fehler: {type(e).__name__}: {e}", flush=True)
|
|
|
|
def sweep(self) -> None:
|
|
stale = self.gcfg.stale_after_seconds
|
|
with connect(self.cfg.pg_dsn) as conn, conn.cursor() as cur:
|
|
cur.execute(
|
|
"""
|
|
SELECT object_id::text FROM remote_cam.gundi_inbox
|
|
WHERE state = 'RECEIVED' AND updated_at < now() - make_interval(secs => %s)
|
|
ORDER BY updated_at LIMIT 200
|
|
""",
|
|
(stale,),
|
|
)
|
|
ingest_ids = [r[0] for r in cur.fetchall()]
|
|
cur.execute(
|
|
"""
|
|
SELECT image_uuid::text FROM remote_cam.image
|
|
WHERE ocr_state = 'PENDING' AND source = 'gundi'
|
|
AND updated_at < now() - make_interval(secs => %s)
|
|
ORDER BY updated_at LIMIT 200
|
|
""",
|
|
(stale,),
|
|
)
|
|
ocr_ids = [r[0] for r in cur.fetchall()]
|
|
cur.execute(
|
|
"""
|
|
UPDATE remote_cam.gundi_inbox
|
|
SET state = 'ABANDONED', updated_at = now()
|
|
WHERE state = 'AWAITING_ATTACHMENT'
|
|
AND received_at < now() - make_interval(hours => %s)
|
|
""",
|
|
(self.gcfg.abandon_after_hours,),
|
|
)
|
|
abandoned = cur.rowcount
|
|
for object_id in ingest_ids:
|
|
self.submit_ingest(object_id)
|
|
for image_uuid in ocr_ids:
|
|
self.submit_ocr(image_uuid)
|
|
if ingest_ids or ocr_ids or abandoned:
|
|
print(
|
|
f"[GUNDI] sweep ingest_retry={len(ingest_ids)} ocr_retry={len(ocr_ids)} abandoned={abandoned}",
|
|
flush=True,
|
|
)
|
|
self.check_backlog()
|
|
|
|
def check_backlog(self) -> None:
|
|
"""Meldet haengende Eintraege und NEUE Fehlschlaege (letzte 24 h) als [GUNDI][ALARM], hoechstens alle 5 min.
|
|
|
|
Aeltere FAILED/FAIL bleiben in der DB (gundi_requeue), loesen aber keinen Dauer-Alarm aus.
|
|
"""
|
|
limit = self.gcfg.alarm_after_seconds
|
|
with connect(self.cfg.pg_dsn) as conn, conn.cursor() as cur:
|
|
cur.execute(
|
|
"""
|
|
SELECT
|
|
count(*) FILTER (WHERE state = 'RECEIVED'
|
|
AND updated_at < now() - make_interval(secs => %s)),
|
|
count(*) FILTER (WHERE state = 'AWAITING_ATTACHMENT'
|
|
AND received_at < now() - make_interval(secs => %s)),
|
|
count(*) FILTER (WHERE state = 'FAILED' AND updated_at > now() - interval '24 hours')
|
|
FROM remote_cam.gundi_inbox
|
|
WHERE received_at > now() - interval '14 days'
|
|
""",
|
|
(limit, limit),
|
|
)
|
|
received_stuck, awaiting_old, failed = cur.fetchone()
|
|
cur.execute(
|
|
"""
|
|
SELECT
|
|
count(*) FILTER (WHERE ocr_state = 'PENDING'
|
|
AND updated_at < now() - make_interval(secs => %s)),
|
|
count(*) FILTER (WHERE ocr_state = 'FAIL' AND updated_at > now() - interval '24 hours')
|
|
FROM remote_cam.image
|
|
WHERE source = 'gundi' AND servertime > now() - interval '14 days'
|
|
""",
|
|
(limit,),
|
|
)
|
|
ocr_stuck, ocr_failed = cur.fetchone()
|
|
if not (received_stuck or awaiting_old or failed or ocr_stuck or ocr_failed):
|
|
return
|
|
now = time.monotonic()
|
|
if now - self._last_alarm < 300:
|
|
return
|
|
self._last_alarm = now
|
|
print(
|
|
f"[GUNDI][ALARM] backlog received_haengt={received_stuck} awaiting_attachment_alt={awaiting_old} "
|
|
f"failed_24h={failed} ocr_haengt={ocr_stuck} ocr_fail_24h={ocr_failed} (haengt = laenger als {limit}s) "
|
|
"- Details/Zuruecksetzen: tools/gundi_requeue.py",
|
|
flush=True,
|
|
)
|