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
76 lines
3.1 KiB
SQL
76 lines
3.1 KiB
SQL
-- sync_to_prod.sql
|
|
-- Syncs the latest identity changes from dev schema to public schema (Production/3002)
|
|
|
|
-- 1. Create 'identities' table in public if it doesn't exist (matches dev schema)
|
|
CREATE TABLE IF NOT EXISTS public.identities (
|
|
id SERIAL PRIMARY KEY,
|
|
name VARCHAR(255) NOT NULL UNIQUE,
|
|
embedding public.vector(768),
|
|
metadata JSONB DEFAULT '{}'
|
|
);
|
|
|
|
-- 2. Sync Identities (Audrey Hepburn, Cary Grant)
|
|
INSERT INTO public.identities (id, name, metadata)
|
|
SELECT id, name, metadata
|
|
FROM dev.identities
|
|
WHERE name IN ('Audrey Hepburn', 'Cary Grant')
|
|
ON CONFLICT (id) DO NOTHING;
|
|
|
|
-- 3. Fix Bindings Table in public (ensure identity_id column exists and identity_bindings structure matches)
|
|
-- Check if talent_id exists, rename it to identity_id if so
|
|
DO $$
|
|
BEGIN
|
|
IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema = 'public' AND table_name = 'identity_bindings' AND column_name = 'talent_id') THEN
|
|
ALTER TABLE public.identity_bindings RENAME COLUMN talent_id TO identity_id;
|
|
END IF;
|
|
END $$;
|
|
|
|
-- 4. Sync Bindings (Mapping dev columns to public schema)
|
|
-- public schema uses: identity_id, uuid, binding_type, binding_value
|
|
INSERT INTO public.identity_bindings (identity_id, uuid, binding_type, binding_value)
|
|
SELECT identity_id, '384b0ff44aaaa1f1', identity_type, identity_value
|
|
FROM dev.identity_bindings
|
|
WHERE identity_value IN ('Person_17', 'Person_4')
|
|
ON CONFLICT DO NOTHING;
|
|
|
|
-- 5. Sync Merge History
|
|
INSERT INTO public.merge_history (merge_id, target_person_id, source_person_ids, original_target_stats, original_source_stats, merged_at)
|
|
SELECT merge_id, target_person_id, source_person_ids, original_target_stats, original_source_stats, merged_at
|
|
FROM dev.merge_history
|
|
WHERE target_person_id IN ('Person_17', 'Person_4')
|
|
ON CONFLICT DO NOTHING;
|
|
|
|
-- 6. Perform Data Merges in Public (Simulating the actions taken in Dev)
|
|
|
|
-- A. Merge Person_25 -> Person_17 (Audrey Hepburn)
|
|
-- Update Appearances
|
|
UPDATE public.person_appearances
|
|
SET person_id = 'Person_17'
|
|
WHERE person_id = 'Person_25' AND video_uuid = '384b0ff44aaaa1f1';
|
|
|
|
-- Update Name and Count
|
|
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';
|
|
|
|
-- Delete Source
|
|
DELETE FROM public.person_identities
|
|
WHERE person_id = 'Person_25' AND video_uuid = '384b0ff44aaaa1f1';
|
|
|
|
-- B. Merge Person_46, Person_70, Person_3 -> Person_4 (Cary Grant)
|
|
-- Update Appearances
|
|
UPDATE public.person_appearances
|
|
SET person_id = 'Person_4'
|
|
WHERE person_id IN ('Person_46', 'Person_70', 'Person_3') AND video_uuid = '384b0ff44aaaa1f1';
|
|
|
|
-- Update Name and Count
|
|
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';
|
|
|
|
-- Delete Sources
|
|
DELETE FROM public.person_identities
|
|
WHERE person_id IN ('Person_46', 'Person_70', 'Person_3') AND video_uuid = '384b0ff44aaaa1f1';
|