8f05a7c188
- Update ASR, face, OCR, pose processors - Add release pre-flight check script - Add synonym generation, chunk processing scripts - Add face recognition, stamp search utilities
55 lines
2.4 KiB
SQL
55 lines
2.4 KiB
SQL
-- final_sync_public.sql
|
|
|
|
-- 1. Ensure Identities exist in public (using video_identities table)
|
|
DO $$
|
|
BEGIN
|
|
-- Check/Add Audrey Hepburn
|
|
IF NOT EXISTS (SELECT 1 FROM public.video_identities WHERE name = 'Audrey Hepburn' AND uuid = '384b0ff44aaaa1f1') THEN
|
|
INSERT INTO public.video_identities (uuid, name, metadata)
|
|
VALUES ('384b0ff44aaaa1f1', 'Audrey Hepburn', '{"role": "Reggie Lampert"}');
|
|
END IF;
|
|
END $$;
|
|
|
|
-- 2. Sync Appearances (Copy from dev to public)
|
|
-- Since public.person_appearances is empty, we can just copy.
|
|
INSERT INTO public.person_appearances (person_id, video_uuid, start_time, end_time, duration, confidence)
|
|
SELECT person_id, video_uuid, start_time, end_time, duration, confidence
|
|
FROM dev.person_appearances
|
|
WHERE video_uuid = '384b0ff44aaaa1f1'
|
|
AND person_id IN ('Person_17', 'Person_4'); -- Only sync the main ones we merged
|
|
|
|
-- 3. Update Person Counts and Names
|
|
-- Audrey Hepburn (Person_17)
|
|
UPDATE public.person_identities
|
|
SET name = 'Audrey Hepburn',
|
|
appearance_count = (SELECT count(*) FROM public.person_appearances WHERE person_id = 'Person_17' AND video_uuid = '384b0ff44aaaa1f1')
|
|
WHERE person_id = 'Person_17' AND video_uuid = '384b0ff44aaaa1f1';
|
|
|
|
-- Cary Grant (Person_4)
|
|
UPDATE public.person_identities
|
|
SET name = 'Cary Grant',
|
|
appearance_count = (SELECT count(*) FROM public.person_appearances WHERE person_id = 'Person_4' AND video_uuid = '384b0ff44aaaa1f1')
|
|
WHERE person_id = 'Person_4' AND video_uuid = '384b0ff44aaaa1f1';
|
|
|
|
-- 4. Sync Bindings
|
|
-- First, get the ID of the new Audrey Hepburn identity
|
|
DO $$
|
|
DECLARE
|
|
audrey_id INT;
|
|
cary_id INT;
|
|
BEGIN
|
|
-- Get IDs
|
|
SELECT id INTO audrey_id FROM public.video_identities WHERE name = 'Audrey Hepburn' AND uuid = '384b0ff44aaaa1f1' LIMIT 1;
|
|
SELECT id INTO cary_id FROM public.video_identities WHERE name = 'Cary Grant' AND uuid = '384b0ff44aaaa1f1' LIMIT 1;
|
|
|
|
-- Bind Person_17 to Audrey
|
|
INSERT INTO public.identity_bindings (identity_id, uuid, binding_type, binding_value)
|
|
VALUES (audrey_id, '384b0ff44aaaa1f1', 'person', 'Person_17')
|
|
ON CONFLICT (uuid, binding_type, binding_value) DO UPDATE SET identity_id = audrey_id;
|
|
|
|
-- Bind Person_4 to Cary
|
|
INSERT INTO public.identity_bindings (identity_id, uuid, binding_type, binding_value)
|
|
VALUES (cary_id, '384b0ff44aaaa1f1', 'person', 'Person_4')
|
|
ON CONFLICT (uuid, binding_type, binding_value) DO UPDATE SET identity_id = cary_id;
|
|
END $$;
|