75 lines
3.2 KiB
PL/PgSQL
75 lines
3.2 KiB
PL/PgSQL
BEGIN;
|
|
DO $$ BEGIN
|
|
IF current_database() <> 'booking_test' THEN RAISE EXCEPTION 'Wrong database'; END IF;
|
|
IF EXISTS (SELECT 1 FROM ingestion.artifacts WHERE artifact_kind='ohip_json')
|
|
OR EXISTS (SELECT 1 FROM ingestion.processing_runs WHERE result_schema_version='5.0' OR uploaded_filename='ARR.json')
|
|
THEN RAISE EXCEPTION 'Direct-source records exist; rollback refused'; END IF;
|
|
END $$;
|
|
ALTER TABLE ingestion.artifacts DROP CONSTRAINT artifacts_artifact_kind_check;
|
|
ALTER TABLE ingestion.artifacts ADD CONSTRAINT artifacts_artifact_kind_check CHECK (artifact_kind IN (
|
|
'booking_source_md', 'booking_excel', 'opera_xml', 'daily_xlsx', 'monthly_xlsx',
|
|
'channel_detail_xlsx', 'company_ten_day_xlsx', 'exception_xlsx', 'result_json',
|
|
'structured_result_json', 'manual_override_json'));
|
|
ALTER TABLE ingestion.processing_runs DROP CONSTRAINT processing_runs_terminal_shape;
|
|
ALTER TABLE ingestion.processing_runs
|
|
ADD CONSTRAINT processing_runs_terminal_shape CHECK (
|
|
(
|
|
run_status = 'accepted'
|
|
AND failure_code IS NULL
|
|
AND validated_at IS NOT NULL
|
|
AND finished_at IS NOT NULL
|
|
AND (
|
|
pipeline_type <> 'opera_daily'
|
|
OR (
|
|
business_date IS NOT NULL
|
|
AND delivered_processor_version IS NOT NULL
|
|
AND delivered_rule_set_sha256 IS NOT NULL
|
|
AND result_schema_version IS NOT NULL
|
|
AND delivery_sha256 IS NOT NULL
|
|
AND (
|
|
(
|
|
result_delivery_mode = 'artifact_callback'
|
|
AND result_artifact_id IS NOT NULL
|
|
)
|
|
OR (
|
|
result_delivery_mode = 'direct_mcp'
|
|
AND result_artifact_id IS NULL
|
|
)
|
|
)
|
|
)
|
|
)
|
|
)
|
|
OR (
|
|
run_status IN ('rejected', 'failed')
|
|
AND failure_code IS NOT NULL
|
|
AND finished_at IS NOT NULL
|
|
)
|
|
OR (run_status = 'cancelled' AND finished_at IS NOT NULL)
|
|
OR (
|
|
run_status = 'awaiting_review'
|
|
AND pipeline_type = 'opera_daily'
|
|
AND result_delivery_mode = 'artifact_callback'
|
|
AND result_artifact_id IS NOT NULL
|
|
AND business_date IS NOT NULL
|
|
AND delivered_processor_version IS NOT NULL
|
|
AND delivered_rule_set_sha256 IS NOT NULL
|
|
AND result_schema_version = '4.0'
|
|
AND delivery_sha256 IS NOT NULL
|
|
AND validated_at IS NOT NULL
|
|
AND finished_at IS NULL
|
|
)
|
|
OR run_status IN ('received', 'queued', 'running', 'validating')
|
|
);
|
|
ALTER TABLE ingestion.processing_runs DROP CONSTRAINT processing_runs_uploaded_filename_check;
|
|
ALTER TABLE ingestion.processing_runs
|
|
ADD CONSTRAINT processing_runs_uploaded_filename_check CHECK (
|
|
uploaded_filename IS NULL
|
|
OR (
|
|
char_length(uploaded_filename) BETWEEN 1 AND 255
|
|
AND uploaded_filename NOT IN ('.', '..')
|
|
AND uploaded_filename !~ '[[:cntrl:]/\\]'
|
|
AND right(lower(uploaded_filename), 4) = '.xml'
|
|
)
|
|
);
|
|
COMMIT;
|