- 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>
44 lines
1.8 KiB
SQL
44 lines
1.8 KiB
SQL
-- Parallelbetrieb Legacy + Gundi: dasselbe Bild liegt doppelt in remote_cam.image.
|
|
-- Behalten wird der Legacy-Zwilling (source='entrance'): an seiner UUID haengen die
|
|
-- KI-Ergebnisse (ai_image/ai_object). Der Gundi-Zwilling wandert nach image_superseded.
|
|
--
|
|
-- Zwilling = gleiche imei, gleiche Dateigroesse, servertime hoechstens 10 min auseinander.
|
|
-- Gemessen am 2026-10-16: 4291 Paare, jedes Bild hat hoechstens einen Partner.
|
|
-- Nur Paare, deren Legacy-Seite schon migriert ist; Paare mit mehreren Kandidaten bleiben
|
|
-- unangetastet. Wiederholbar. tools/migrate_legacy.py --dedupe fuehrt die Schritte in EINER
|
|
-- Transaktion aus.
|
|
|
|
-- step: twins
|
|
CREATE TEMP TABLE _twins ON COMMIT DROP AS
|
|
WITH pairs AS (
|
|
SELECT g.image_uuid AS gundi_uuid, e.image_uuid AS legacy_uuid
|
|
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'
|
|
)
|
|
SELECT gundi_uuid, legacy_uuid
|
|
FROM (SELECT p.*,
|
|
count(*) OVER (PARTITION BY p.gundi_uuid) AS n_gundi,
|
|
count(*) OVER (PARTITION BY p.legacy_uuid) AS n_legacy
|
|
FROM pairs p) x
|
|
WHERE n_gundi = 1 AND n_legacy = 1;
|
|
|
|
-- step: archive
|
|
INSERT INTO remote_cam.image_superseded (image_uuid, superseded_by, image_row, detections)
|
|
SELECT t.gundi_uuid, t.legacy_uuid, to_jsonb(i),
|
|
(SELECT jsonb_agg(to_jsonb(d)) FROM remote_cam.detection d WHERE d.image_uuid = t.gundi_uuid)
|
|
FROM _twins t
|
|
JOIN remote_cam.image i ON i.image_uuid = t.gundi_uuid
|
|
ON CONFLICT (image_uuid) DO NOTHING;
|
|
|
|
-- step: delete
|
|
-- detection haengt per ON DELETE CASCADE am image.
|
|
DELETE FROM remote_cam.image i
|
|
USING _twins t
|
|
WHERE i.image_uuid = t.gundi_uuid;
|