77 lines
3.0 KiB
PL/PgSQL
77 lines
3.0 KiB
PL/PgSQL
-- =============================================================================
|
|
-- 05_airports_zones.sql — gestion manuelle des zones d'exclusion
|
|
--
|
|
-- Étend adsb.airports pour accueillir, à côté des aérodromes de référence, des
|
|
-- zones ajoutées à la main depuis l'interface (typiquement une zone découverte
|
|
-- par l'analyse et que l'on souhaite écarter des recherches suivantes).
|
|
--
|
|
-- Idempotent : peut être relancé sans risque.
|
|
--
|
|
-- Usage :
|
|
-- docker exec -i wordpress-postgres psql -U pgz_admin -d wpz_postgres \
|
|
-- -v ON_ERROR_STOP=1 < 05_airports_zones.sql
|
|
-- =============================================================================
|
|
|
|
\set ON_ERROR_STOP on
|
|
|
|
-- Origine de la fiche : distingue ce qui vient du référentiel de ce que
|
|
-- l'utilisateur a ajouté. Permet de réinitialiser les aérodromes sans perdre
|
|
-- les zones personnelles, et inversement.
|
|
ALTER TABLE adsb.airports
|
|
ADD COLUMN IF NOT EXISTS source text NOT NULL DEFAULT 'reference';
|
|
|
|
ALTER TABLE adsb.airports
|
|
ADD COLUMN IF NOT EXISTS cree_le timestamptz NOT NULL DEFAULT now();
|
|
|
|
ALTER TABLE adsb.airports
|
|
ADD COLUMN IF NOT EXISTS modifie_le timestamptz NOT NULL DEFAULT now();
|
|
|
|
-- Contrainte de valeurs, ajoutée seulement si absente
|
|
DO $$
|
|
BEGIN
|
|
IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'airports_source_chk') THEN
|
|
ALTER TABLE adsb.airports
|
|
ADD CONSTRAINT airports_source_chk
|
|
CHECK (source IN ('reference', 'manuelle'));
|
|
END IF;
|
|
IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'airports_rayon_chk') THEN
|
|
ALTER TABLE adsb.airports
|
|
ADD CONSTRAINT airports_rayon_chk
|
|
CHECK (rayon_km > 0 AND rayon_km <= 100);
|
|
END IF;
|
|
IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'airports_coord_chk') THEN
|
|
ALTER TABLE adsb.airports
|
|
ADD CONSTRAINT airports_coord_chk
|
|
CHECK (lat BETWEEN -90 AND 90 AND lon BETWEEN -180 AND 180);
|
|
END IF;
|
|
END $$;
|
|
|
|
COMMENT ON COLUMN adsb.airports.source IS
|
|
'reference = aérodrome du référentiel livré ; manuelle = zone ajoutée depuis '
|
|
'l''interface (zone découverte, terrain privé, hélisurface…)';
|
|
|
|
-- Le type accepte désormais des valeurs propres aux zones manuelles.
|
|
-- Pas de contrainte CHECK sur `type` : la liste doit rester ouverte, on ne sait
|
|
-- pas d'avance ce que l'utilisateur va découvrir.
|
|
COMMENT ON COLUMN adsb.airports.type IS
|
|
'aeroport | aerodrome | heliport | base | ulm | helisurface | prive | inconnu | …';
|
|
|
|
-- Mise à jour automatique de modifie_le
|
|
CREATE OR REPLACE FUNCTION adsb.airports_touch() RETURNS trigger AS $$
|
|
BEGIN
|
|
NEW.modifie_le := now();
|
|
RETURN NEW;
|
|
END $$ LANGUAGE plpgsql;
|
|
|
|
DROP TRIGGER IF EXISTS airports_touch_trg ON adsb.airports;
|
|
CREATE TRIGGER airports_touch_trg
|
|
BEFORE UPDATE ON adsb.airports
|
|
FOR EACH ROW EXECUTE FUNCTION adsb.airports_touch();
|
|
|
|
-- Les fiches existantes viennent du référentiel
|
|
UPDATE adsb.airports SET source = 'reference' WHERE source IS NULL;
|
|
|
|
SELECT source, count(*) FILTER (WHERE actif) AS actives,
|
|
count(*) AS total
|
|
FROM adsb.airports GROUP BY source ORDER BY source;
|