89 lines
2.7 KiB
PL/PgSQL
89 lines
2.7 KiB
PL/PgSQL
-- Allow durable monthly publication artifacts in the existing private OSS.
|
|
-- Historical local artifacts remain valid and readable.
|
|
|
|
BEGIN;
|
|
|
|
DO $$
|
|
BEGIN
|
|
IF current_database() <> 'booking_test' THEN
|
|
RAISE EXCEPTION
|
|
'ARR monthly OSS artifact migration is allowed only in booking_test';
|
|
END IF;
|
|
IF to_regclass('reporting.monthly_runs') IS NULL
|
|
OR to_regclass('ingestion.artifacts') IS NULL
|
|
OR to_regprocedure('reporting.validate_monthly_run_publication()') IS NULL THEN
|
|
RAISE EXCEPTION
|
|
'ARR migration 012 must be applied first';
|
|
END IF;
|
|
END;
|
|
$$;
|
|
|
|
CREATE OR REPLACE FUNCTION reporting.validate_monthly_run_publication()
|
|
RETURNS trigger
|
|
LANGUAGE plpgsql
|
|
AS $$
|
|
DECLARE
|
|
workbook_kind text;
|
|
workbook_provider text;
|
|
result_kind text;
|
|
result_provider text;
|
|
manifest_count integer;
|
|
manifest_rows bigint;
|
|
manifest_min integer;
|
|
manifest_max integer;
|
|
lineage_count integer;
|
|
BEGIN
|
|
IF NEW.report_status NOT IN ('active', 'superseded') THEN
|
|
RETURN NEW;
|
|
END IF;
|
|
|
|
SELECT artifact_kind, storage_provider
|
|
INTO workbook_kind, workbook_provider
|
|
FROM ingestion.artifacts
|
|
WHERE id = NEW.workbook_artifact_id;
|
|
|
|
SELECT artifact_kind, storage_provider
|
|
INTO result_kind, result_provider
|
|
FROM ingestion.artifacts
|
|
WHERE id = NEW.result_artifact_id;
|
|
|
|
IF workbook_kind IS DISTINCT FROM 'monthly_xlsx'
|
|
OR COALESCE(workbook_provider, '') NOT IN ('oss', 's3', 'local')
|
|
OR result_kind IS DISTINCT FROM 'result_json'
|
|
OR COALESCE(result_provider, '') NOT IN ('oss', 's3', 'local') THEN
|
|
RAISE EXCEPTION
|
|
'published monthly run must reference OSS or controlled local monthly artifacts';
|
|
END IF;
|
|
|
|
SELECT count(*), COALESCE(sum(row_count), 0), min(worksheet_order), max(worksheet_order)
|
|
INTO manifest_count, manifest_rows, manifest_min, manifest_max
|
|
FROM reporting.monthly_channel_manifest
|
|
WHERE report_id = NEW.id;
|
|
|
|
IF manifest_count <> NEW.channel_count
|
|
OR manifest_rows <> NEW.row_count
|
|
OR manifest_min <> 1
|
|
OR manifest_max <> manifest_count THEN
|
|
RAISE EXCEPTION
|
|
'published monthly run requires a continuous reconciled channel manifest';
|
|
END IF;
|
|
|
|
SELECT count(*)
|
|
INTO lineage_count
|
|
FROM reporting.monthly_run_daily_versions
|
|
WHERE report_id = NEW.id;
|
|
|
|
IF lineage_count < 1 THEN
|
|
RAISE EXCEPTION
|
|
'published monthly run requires daily-version lineage';
|
|
END IF;
|
|
|
|
RETURN NEW;
|
|
END;
|
|
$$;
|
|
|
|
COMMENT ON FUNCTION reporting.validate_monthly_run_publication() IS
|
|
'Published monthly artifacts may be private OSS/S3 or legacy controlled-local objects.';
|
|
|
|
COMMIT;
|