Files
domandClaude Opus 5.5 39b22f0124 Keep top human/machine legacy categories below 0.5 for privacy blocking
The portal blocks images whose top ICU category is human or machine for non-admins. With the 0.5 threshold, 103 such images would have lost their detection and become visible. Those categories are now migrated when they were the image's top category, regardless of score.

Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
2026-10-16 22:15:12 +02:00

248 lines
11 KiB
Python

#!/usr/bin/env python3
"""Migriert remote_cam.metadata/category batchweise ins Fachmodell (image, camera_status, detection).
python -m tools.migrate_legacy # PG_DSN aus der Umgebung
python -m tools.migrate_legacy --dsn postgresql://... --batch 20000
python -m tools.migrate_legacy --verify-only
python -m tools.migrate_legacy --plan # nur lesen: was wuerde migriert/entfernt
python -m tools.migrate_legacy --dedupe --dry-run
python -m tools.migrate_legacy --dedupe # Gundi-Zwillinge entfernen (schema/003)
Wiederholbar. Jeder Batch ist eine eigene Transaktion, --dedupe laeuft in einer.
"""
import argparse
import os
import re
import sys
import time
from pathlib import Path
import psycopg
SCHEMA_DIR = Path(__file__).resolve().parent.parent / "schema"
SCHEMA_LOCK_KEY = 7_311_000_001
def load_steps(filename: str = "002_migrate_legacy.sql") -> list[tuple[str, str]]:
sql = (SCHEMA_DIR / filename).read_text(encoding="utf-8")
parts = re.split(r"^-- step: (\w+)\s*$", sql, flags=re.MULTILINE)
return [(parts[i], parts[i + 1].strip()) for i in range(1, len(parts), 2)]
def apply_schema(conn: psycopg.Connection) -> None:
sql = (SCHEMA_DIR / "001_domain_model.sql").read_text(encoding="utf-8")
with conn.transaction(), conn.cursor() as cur:
cur.execute("SELECT pg_advisory_xact_lock(%s)", (SCHEMA_LOCK_KEY,))
cur.execute(sql)
def migrate(conn: psycopg.Connection, batch_size: int) -> None:
steps = load_steps()
totals = {name: 0 for name, _ in steps}
with conn.cursor() as cur:
cur.execute("SELECT count(*) FROM remote_cam.metadata")
total_rows = cur.fetchone()[0]
cur.execute(
"CREATE TEMP TABLE IF NOT EXISTS _mig_batch (metadata_uuid text PRIMARY KEY) ON COMMIT DELETE ROWS"
)
conn.commit()
last = ""
seen = 0
t0 = time.perf_counter()
while True:
with conn.transaction(), conn.cursor() as cur:
cur.execute(
"""
INSERT INTO _mig_batch
SELECT metadata_uuid FROM remote_cam.metadata
WHERE metadata_uuid > %s ORDER BY metadata_uuid LIMIT %s
""",
(last, batch_size),
)
n = cur.rowcount
if n == 0:
break
cur.execute("SELECT max(metadata_uuid) FROM _mig_batch")
last = cur.fetchone()[0]
added = []
for name, sql in steps:
cur.execute(sql)
totals[name] += cur.rowcount
added.append(f"{name}+{cur.rowcount}")
seen += n
rate = seen / max(time.perf_counter() - t0, 1e-6)
print(f" {seen:>8}/{total_rows} {' '.join(added)} ({rate:.0f} Zeilen/s)", flush=True)
print("Neu angelegt: " + ", ".join(f"{k}={v}" for k, v in totals.items()), flush=True)
# Welche Zeilen aus remote_cam.category (Alias c) zu detection werden -- wie in 002.
DETECTION_FILTER = (
"c.category IS NOT NULL AND c.category NOT IN ('', 'none') AND (c.score >= 0.5 "
"OR (c.category IN ('human', 'machine') AND NOT EXISTS (SELECT 1 FROM remote_cam.category c2 "
"WHERE c2.image_uuid = c.image_uuid "
"AND (c2.score > c.score OR (c2.score = c.score AND c2.category < c.category)))))"
)
# Lesende Vorschau; die Bedingungen spiegeln 002 (detection-Filter) und 003 (Zwillinge).
PLAN_QUERIES = [
("image(entrance) neu",
"SELECT count(*) FROM remote_cam.metadata m WHERE m.imei IS NOT NULL AND m.servertime IS NOT NULL "
"AND NOT EXISTS (SELECT 1 FROM remote_cam.image i WHERE i.image_uuid = m.metadata_uuid::uuid)"),
("camera_status(entrance) neu",
"SELECT count(*) FROM remote_cam.metadata m WHERE m.imei IS NOT NULL AND m.servertime IS NOT NULL "
"AND NOT EXISTS (SELECT 1 FROM remote_cam.camera_status s WHERE s.source = 'entrance' "
"AND s.source_ref = m.metadata_uuid)"),
("detection(entrance) neu",
"SELECT count(*) FROM remote_cam.category c JOIN remote_cam.metadata m ON m.metadata_uuid = c.image_uuid "
"WHERE m.imei IS NOT NULL AND m.servertime IS NOT NULL AND " + DETECTION_FILTER + " "
"AND NOT EXISTS (SELECT 1 FROM remote_cam.detection d WHERE d.image_uuid = m.metadata_uuid::uuid "
"AND d.source = 'entrance')"),
("Gundi-Zwillinge (nach Migration zu entfernen)",
"""
WITH pairs AS (
SELECT g.image_uuid AS gundi_uuid, m.metadata_uuid AS legacy_uuid
FROM remote_cam.image g
JOIN remote_cam.metadata m
ON m.imei = g.imei AND m."SizeInByte" = g.size_bytes
AND m.servertime BETWEEN g.servertime - interval '10 minutes' AND g.servertime + interval '10 minutes'
WHERE g.source = 'gundi')
SELECT count(*) FROM (
SELECT count(*) OVER (PARTITION BY gundi_uuid) AS n_g, count(*) OVER (PARTITION BY legacy_uuid) AS n_l
FROM pairs) x
WHERE n_g = 1 AND n_l = 1
"""),
("Gundi-Bilder ohne Zwilling (bleiben)",
"""
SELECT count(*) FROM remote_cam.image g WHERE g.source = 'gundi' AND NOT EXISTS (
SELECT 1 FROM remote_cam.metadata m
WHERE m.imei = g.imei AND m."SizeInByte" = g.size_bytes
AND m.servertime BETWEEN g.servertime - interval '10 minutes' AND g.servertime + interval '10 minutes')
"""),
]
def plan(conn: psycopg.Connection) -> None:
with conn.cursor() as cur:
cur.execute("SET TRANSACTION READ ONLY")
for label, sql in PLAN_QUERIES:
cur.execute(sql)
print(f" {label}: {cur.fetchone()[0]}", flush=True)
conn.rollback()
def dedupe(conn: psycopg.Connection, dry_run: bool) -> None:
steps = dict(load_steps("003_dedupe_gundi_twins.sql"))
with conn.cursor() as cur:
cur.execute(steps["twins"])
cur.execute("SELECT count(*) FROM _twins")
n = cur.fetchone()[0]
cur.execute(
"SELECT min(i.servertime), max(i.servertime) FROM _twins t "
"JOIN remote_cam.image i ON i.image_uuid = t.gundi_uuid"
)
lo, hi = cur.fetchone()
print(f" Gundi-Zwillinge: {n} (servertime {lo} .. {hi})", flush=True)
if dry_run:
conn.rollback()
print(" Trockenlauf: nichts geaendert.", flush=True)
return
cur.execute(steps["archive"])
archived = cur.rowcount
cur.execute(steps["delete"])
deleted = cur.rowcount
if deleted != n:
conn.rollback()
raise RuntimeError(f"geloescht={deleted} != Zwillinge={n}; zurueckgerollt")
conn.commit()
print(f" archiviert={archived} geloescht={deleted}", flush=True)
def verify(conn: psycopg.Connection) -> bool:
checks = [
("metadata == image(entrance)",
"SELECT (SELECT count(*) FROM remote_cam.metadata), "
"(SELECT count(*) FROM remote_cam.image WHERE source = 'entrance')"),
("metadata == camera_status(entrance)",
"SELECT (SELECT count(*) FROM remote_cam.metadata), "
"(SELECT count(*) FROM remote_cam.camera_status WHERE source = 'entrance')"),
("category (Score >= 0.5 bzw. Top human/machine, ohne ''/none) == detection(entrance)",
"SELECT (SELECT count(*) FROM remote_cam.category c JOIN remote_cam.metadata m "
"ON m.metadata_uuid = c.image_uuid WHERE m.imei IS NOT NULL AND m.servertime IS NOT NULL "
"AND " + DETECTION_FILTER + "), "
"(SELECT count(*) FROM remote_cam.detection WHERE source = 'entrance')"),
("keine Gundi-Bilder mit Legacy-Zwilling",
"SELECT 0, count(*) FROM remote_cam.image g JOIN remote_cam.image e "
"ON e.source = 'entrance' AND e.imei = g.imei AND e.size_bytes = g.size_bytes "
"AND e.servertime BETWEEN g.servertime - interval '10 minutes' AND g.servertime + interval '10 minutes' "
"WHERE g.source = 'gundi'"),
("metadata ohne ocr_ts == image FAIL",
"SELECT (SELECT count(*) FROM remote_cam.metadata WHERE ocr_ts IS NULL), "
"(SELECT count(*) FROM remote_cam.image WHERE source = 'entrance' AND ocr_state = 'FAIL')"),
("keine 0/0-Koordinaten (image, camera_status)",
"SELECT 0, (SELECT count(*) FROM remote_cam.image WHERE lat = 0 AND lon = 0) + "
"(SELECT count(*) FROM remote_cam.camera_status WHERE lat = 0 AND lon = 0)"),
("Stichprobe: image_with_status == metadata (200 Zufallszeilen, Abweichungen)",
"""
SELECT 0, count(*) FROM (
SELECT m.* FROM remote_cam.metadata m TABLESAMPLE SYSTEM (1) LIMIT 200
) m
JOIN remote_cam.image_with_status v ON v.image_uuid = m.metadata_uuid::uuid
WHERE v.status_reported_at IS DISTINCT FROM m.servertime
OR v.battery_pct IS DISTINCT FROM m."BatteryStatus"
OR v.firmware_version IS DISTINCT FROM m."FirmwareVersion"
OR v.status_camera_name IS DISTINCT FROM m."CameraName"
OR v.signal_strength IS DISTINCT FROM m."SignalStrength"
OR v.temperature_c IS DISTINCT FROM m."Temperature"
OR v.customer_timezone IS DISTINCT FROM m."CustomerTimezone"
OR v.ocr_ts IS DISTINCT FROM m.ocr_ts
"""),
]
ok_all = True
with conn.cursor() as cur:
for label, sql in checks:
cur.execute(sql)
expected, actual = cur.fetchone()
ok = expected == actual
ok_all &= ok
print(f"[{'OK ' if ok else 'FAIL'}] {label}: erwartet={expected} ist={actual}", flush=True)
conn.rollback()
return ok_all
def main() -> int:
ap = argparse.ArgumentParser()
ap.add_argument("--dsn", default=os.environ.get("PG_DSN"))
ap.add_argument("--batch", type=int, default=20000)
ap.add_argument("--verify-only", action="store_true")
ap.add_argument("--plan", action="store_true", help="nur lesen: Vorschau der Mengen")
ap.add_argument("--dedupe", action="store_true", help="Gundi-Zwillinge entfernen (schema/003)")
ap.add_argument("--dry-run", action="store_true", help="mit --dedupe: nur zaehlen")
args = ap.parse_args()
if not args.dsn:
print("--dsn oder PG_DSN fehlt", file=sys.stderr)
return 2
with psycopg.connect(args.dsn, connect_timeout=10) as conn:
if args.plan:
print("Vorschau (nur lesend):", flush=True)
plan(conn)
return 0
if args.dedupe:
if not args.dry_run:
apply_schema(conn)
print("Gundi-Zwillinge:", flush=True)
dedupe(conn, args.dry_run)
return 0
if not args.verify_only:
apply_schema(conn)
print("Schema angewendet. Migriere ...", flush=True)
migrate(conn, args.batch)
print("Verifikation:", flush=True)
return 0 if verify(conn) else 1
if __name__ == "__main__":
raise SystemExit(main())