64 lines
2.3 KiB
MySQL
64 lines
2.3 KiB
MySQL
|
-- =============================================================
|
||
|
|
-- BRONZE CONSTRAINTS AUDIT
|
||
|
|
-- Target Database: tracksolid_dwh
|
||
|
|
-- Purpose: Assert that every ON CONFLICT target used by Workflow 2
|
||
|
|
-- (dwh_load_bronze) is backed by a PRIMARY KEY or UNIQUE
|
||
|
|
-- constraint in the bronze schema. Fails loudly if a future
|
||
|
|
-- DDL edit removes a key the ingestion pipeline depends on.
|
||
|
|
-- Applies after: 260423_dwh_ddl_v1.sql
|
||
|
|
-- Idempotent: pure assertion, no DDL changes.
|
||
|
|
-- =============================================================
|
||
|
|
|
||
|
|
BEGIN;
|
||
|
|
|
||
|
|
DO $$
|
||
|
|
DECLARE
|
||
|
|
missing TEXT := '';
|
||
|
|
expected RECORD;
|
||
|
|
BEGIN
|
||
|
|
-- Each row asserts: bronze.<table> has a PK/UNIQUE matching <cols>.
|
||
|
|
-- If the pipeline's ON CONFLICT clause ever diverges from this list,
|
||
|
|
-- update both here and the n8n load workflow in lockstep.
|
||
|
|
FOR expected IN
|
||
|
|
SELECT * FROM (VALUES
|
||
|
|
('devices', 'imei'),
|
||
|
|
('live_positions', 'imei'),
|
||
|
|
('position_history', 'imei,gps_time'),
|
||
|
|
('trips', 'id'),
|
||
|
|
('alarms', 'id'),
|
||
|
|
('parking_events', 'id'),
|
||
|
|
('device_events', 'id'),
|
||
|
|
('ingestion_log', 'id')
|
||
|
|
) AS t(table_name, cols)
|
||
|
|
LOOP
|
||
|
|
IF NOT EXISTS (
|
||
|
|
SELECT 1
|
||
|
|
FROM pg_constraint c
|
||
|
|
JOIN pg_class r ON r.oid = c.conrelid
|
||
|
|
JOIN pg_namespace n ON n.oid = r.relnamespace
|
||
|
|
CROSS JOIN LATERAL (
|
||
|
|
SELECT string_agg(a.attname, ',' ORDER BY k.ord) AS keycols
|
||
|
|
FROM unnest(c.conkey) WITH ORDINALITY AS k(attnum, ord)
|
||
|
|
JOIN pg_attribute a
|
||
|
|
ON a.attrelid = c.conrelid AND a.attnum = k.attnum
|
||
|
|
) AS cols
|
||
|
|
WHERE n.nspname = 'bronze'
|
||
|
|
AND r.relname = expected.table_name
|
||
|
|
AND c.contype IN ('p','u')
|
||
|
|
AND cols.keycols = expected.cols
|
||
|
|
) THEN
|
||
|
|
missing := missing
|
||
|
|
|| format(E'\n - bronze.%s missing PK/UNIQUE on (%s)',
|
||
|
|
expected.table_name, expected.cols);
|
||
|
|
END IF;
|
||
|
|
END LOOP;
|
||
|
|
|
||
|
|
IF length(missing) > 0 THEN
|
||
|
|
RAISE EXCEPTION E'Bronze constraint audit FAILED:%s', missing;
|
||
|
|
END IF;
|
||
|
|
|
||
|
|
RAISE NOTICE 'Bronze constraint audit OK: all 8 ON CONFLICT targets backed by PK/UNIQUE.';
|
||
|
|
END$$;
|
||
|
|
|
||
|
|
COMMIT;
|