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>
107 lines
4.0 KiB
SQL
107 lines
4.0 KiB
SQL
-- Split der Legacy-Daten ins Fachmodell: jede remote_cam.metadata-Zeile ergibt
|
|
-- ein image und ein camera_status; remote_cam.category (Score >= 0.5) wird zu detection.
|
|
--
|
|
-- Erwartet die Temp-Tabelle _mig_batch(metadata_uuid text), die tools/migrate_legacy.py
|
|
-- je Batch fuellt. Wiederholbar: bereits migrierte Zeilen werden uebersprungen.
|
|
-- Alttabellen werden nur gelesen.
|
|
|
|
-- step: image
|
|
INSERT INTO remote_cam.image (
|
|
image_uuid, imei, servertime, ocr_ts, ocr_state, ocr_error, camera_ts,
|
|
camera_name, camera_make, lat, lon, size_bytes, processed_key, thumbnail_key,
|
|
source, source_ref, imported_at, updated_at
|
|
)
|
|
SELECT
|
|
m.metadata_uuid::uuid,
|
|
m.imei,
|
|
m.servertime,
|
|
m.ocr_ts,
|
|
CASE WHEN m.ocr_ts IS NULL THEN 'FAIL' ELSE 'OK' END,
|
|
CASE WHEN m.ocr_ts IS NULL THEN 'legacy: no ocr_ts' END,
|
|
m."ImageCreationDate",
|
|
m."CameraName",
|
|
m."CameraModel",
|
|
CASE WHEN m."Latitude" = 0 AND m."Longitude" = 0 THEN NULL ELSE m."Latitude" END,
|
|
CASE WHEN m."Latitude" = 0 AND m."Longitude" = 0 THEN NULL ELSE m."Longitude" END,
|
|
m."SizeInByte",
|
|
j.processed_jpg_key,
|
|
j.thumbnail_key,
|
|
'entrance',
|
|
m.metadata_uuid,
|
|
COALESCE(m.import_ts, now()),
|
|
now()
|
|
FROM _mig_batch b
|
|
JOIN remote_cam.metadata m ON m.metadata_uuid = b.metadata_uuid
|
|
LEFT JOIN remote_cam.import_job j ON j.image_uuid = m.metadata_uuid
|
|
WHERE m.imei IS NOT NULL AND m.servertime IS NOT NULL
|
|
ON CONFLICT (image_uuid) DO NOTHING;
|
|
|
|
-- step: camera_status
|
|
INSERT INTO remote_cam.camera_status (
|
|
imei, reported_at, received_at, iccid, init_key, camera_name, camera_model,
|
|
firmware_version, camera_language, customer_timezone, mcc, mnc, lac, camera_network,
|
|
battery_pct, signal_strength, temperature_c, coordinate_switch, work_period,
|
|
work_start, work_end, image_format, image_night, thumbnail_size, pir_interval, time_scan,
|
|
lat, lon, settings_changed_at, source, source_ref
|
|
)
|
|
SELECT
|
|
m.imei,
|
|
m.servertime,
|
|
COALESCE(m.import_ts, now()),
|
|
m.iccid,
|
|
m."INITKey",
|
|
m."CameraName",
|
|
m."CameraModel",
|
|
m."FirmwareVersion",
|
|
m."CameraLanguage",
|
|
m."CustomerTimezone",
|
|
m."MobileCountryCode",
|
|
m."MobileNetworkCode",
|
|
m."LocationAreaCode",
|
|
m."CameraNetwork",
|
|
m."BatteryStatus",
|
|
m."SignalStrength",
|
|
m."Temperature",
|
|
m."CoordinateSwitch",
|
|
m."WorkPeriod",
|
|
m."WorkStart",
|
|
m."WorkEnd",
|
|
m."ImageFormat",
|
|
m."ImageNight",
|
|
m."ThumbnailSize",
|
|
m."PIRInterval",
|
|
m."TimeScan",
|
|
CASE WHEN m."Latitude" = 0 AND m."Longitude" = 0 THEN NULL ELSE m."Latitude" END,
|
|
CASE WHEN m."Latitude" = 0 AND m."Longitude" = 0 THEN NULL ELSE m."Longitude" END,
|
|
m."DateTimeLastSettings",
|
|
'entrance',
|
|
m.metadata_uuid
|
|
FROM _mig_batch b
|
|
JOIN remote_cam.metadata m ON m.metadata_uuid = b.metadata_uuid
|
|
WHERE m.imei IS NOT NULL AND m.servertime IS NOT NULL
|
|
ON CONFLICT (source_ref) WHERE source = 'entrance' DO NOTHING;
|
|
|
|
-- step: detection
|
|
-- remote_cam.category ist ein Score-Vektor ueber alle Klassen, detection haelt nur Erkanntes:
|
|
-- uebernommen werden Klassen ab Score 0.5, ohne '' und 'none' (leeres Bild = keine Detection).
|
|
-- Ausnahme Datenschutz: war 'human' oder 'machine' die Top-Kategorie des Bildes, wird sie auch
|
|
-- unter 0.5 uebernommen -- das Portal sperrt solche Bilder fuer Nicht-Admins (wie bisher ueber
|
|
-- die Top-Kategorie). Tiebreaker wie die fruehere image_top_category: Score, dann Name.
|
|
INSERT INTO remote_cam.detection (image_uuid, category, confidence, model_version, source)
|
|
SELECT i.image_uuid, c.category, c.score, c.version, 'entrance'
|
|
FROM _mig_batch b
|
|
JOIN remote_cam.category c ON c.image_uuid = b.metadata_uuid
|
|
JOIN remote_cam.image i ON i.image_uuid = b.metadata_uuid::uuid
|
|
WHERE 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)))))
|
|
AND NOT EXISTS (
|
|
SELECT 1 FROM remote_cam.detection d
|
|
WHERE d.image_uuid = i.image_uuid AND d.source = 'entrance'
|
|
);
|