89 lines
3.5 KiB
PL/PgSQL
89 lines
3.5 KiB
PL/PgSQL
-- ============================================================================
|
|
-- MIGRATION : Ajout de la colonne gares_desservies (JSONB) dans la table horaires
|
|
-- Date : 2026-03-01
|
|
-- Description : Ajoute une colonne JSONB "gares_desservies" dans la table horaires
|
|
-- afin de stocker un snapshot dénormalisé des gares desservies.
|
|
-- Cette colonne est alimentée à chaque création/modification d'horaire
|
|
-- et est synchronisée avec la table relationnelle horaires_gares_desservies.
|
|
-- ============================================================================
|
|
|
|
-- Ajout de la colonne si elle n'existe pas encore
|
|
ALTER TABLE public.horaires
|
|
ADD COLUMN IF NOT EXISTS gares_desservies JSONB DEFAULT '[]'::jsonb;
|
|
|
|
-- Commentaire explicatif sur la colonne
|
|
COMMENT ON COLUMN public.horaires.gares_desservies IS
|
|
'Snapshot JSON des gares desservies (copie dénormalisée synchronisée avec horaires_gares_desservies). '
|
|
'Format : [{"gare_id": "uuid", "gare_nom": "Nom Gare", "heure_arrivee": "HH:MM", "heure_depart": "HH:MM", "ordre": 1}, ...]';
|
|
|
|
-- ============================================================================
|
|
-- Rétro-remplissage : synchroniser la colonne JSON depuis la table relationnelle
|
|
-- pour tous les horaires existants ayant déjà des gares desservies.
|
|
-- ============================================================================
|
|
UPDATE public.horaires h
|
|
SET gares_desservies = (
|
|
SELECT COALESCE(
|
|
jsonb_agg(
|
|
jsonb_build_object(
|
|
'gare_id', gd.gare_id,
|
|
'gare_nom', gd.gare_nom,
|
|
'heure_arrivee', gd.heure_arrivee,
|
|
'heure_depart', gd.heure_depart,
|
|
'ordre', gd.ordre
|
|
) ORDER BY gd.ordre
|
|
),
|
|
'[]'::jsonb
|
|
)
|
|
FROM public.horaires_gares_desservies gd
|
|
WHERE gd.horaire_id = h.id
|
|
)
|
|
WHERE EXISTS (
|
|
SELECT 1 FROM public.horaires_gares_desservies gd WHERE gd.horaire_id = h.id
|
|
);
|
|
|
|
-- ============================================================================
|
|
-- (Optionnel) Trigger pour maintenir automatiquement la colonne JSON à jour
|
|
-- à chaque INSERT / UPDATE / DELETE dans horaires_gares_desservies.
|
|
-- Décommenter si vous souhaitez une synchronisation automatique côté base.
|
|
-- ============================================================================
|
|
|
|
-- CREATE OR REPLACE FUNCTION public.sync_horaire_gares_desservies_json()
|
|
-- RETURNS trigger LANGUAGE plpgsql AS $$
|
|
-- DECLARE
|
|
-- v_horaire_id UUID;
|
|
-- BEGIN
|
|
-- IF TG_OP = 'DELETE' THEN
|
|
-- v_horaire_id := OLD.horaire_id;
|
|
-- ELSE
|
|
-- v_horaire_id := NEW.horaire_id;
|
|
-- END IF;
|
|
--
|
|
-- UPDATE public.horaires
|
|
-- SET gares_desservies = (
|
|
-- SELECT COALESCE(
|
|
-- jsonb_agg(
|
|
-- jsonb_build_object(
|
|
-- 'gare_id', gd.gare_id,
|
|
-- 'gare_nom', gd.gare_nom,
|
|
-- 'heure_arrivee', gd.heure_arrivee,
|
|
-- 'heure_depart', gd.heure_depart,
|
|
-- 'ordre', gd.ordre
|
|
-- ) ORDER BY gd.ordre
|
|
-- ),
|
|
-- '[]'::jsonb
|
|
-- )
|
|
-- FROM public.horaires_gares_desservies gd
|
|
-- WHERE gd.horaire_id = v_horaire_id
|
|
-- )
|
|
-- WHERE id = v_horaire_id;
|
|
--
|
|
-- RETURN NULL;
|
|
-- END;
|
|
-- $$;
|
|
--
|
|
-- DROP TRIGGER IF EXISTS tr_sync_gares_desservies_json ON public.horaires_gares_desservies;
|
|
-- CREATE TRIGGER tr_sync_gares_desservies_json
|
|
-- AFTER INSERT OR UPDATE OR DELETE ON public.horaires_gares_desservies
|
|
-- FOR EACH ROW EXECUTE FUNCTION public.sync_horaire_gares_desservies_json();
|
|
|