verify-schema.sql

SQL · 47 lines · 3,239 bytes · wiki path 10 Architecture/db/SrdSupport/verify-schema.sql · download the raw file · cited from Data Model — `SRD_SUPPORT`

Same folder: 001-work-and-planning.sql · 002-decisions-and-grants.sql · 003-evidence.sql · 004-ledger.sql · 005-registry.sql · 006-memory.sql · 007-policy-and-operations.sql · 008-immutability-and-payload-guards.sql · 009-seed-platform-policy.sql · 010-runtime-execution.sql · acceptance-install-clean.sql · acceptance-upgrade.sql · grant-runtime.sql · install-clean.sql · lint_ddl.py · upgrade.sql · verify-ledger.sql

SET DEFINE OFF
SET SERVEROUTPUT ON
WHENEVER SQLERROR EXIT SQL.SQLCODE

-- verify-schema.sql — the structural acceptance of the SrdSupport script set: every table of the inventory exists,
-- every append-only table carries its immutability trigger, every JSON column is constrained, and the platform
-- policy seed is present. Fails on the first missing item.

DECLARE
    TYPE t_names IS TABLE OF VARCHAR2(64);
    v_tables  t_names := t_names(
        'CASES','SESSIONS','TASKS','TURN_CHECKPOINTS','CASE_IMPACT_KEYS','CASE_LINKS','ARRIVALS',
        'PLANS','PLAN_REVISIONS','PLAN_STEPS',
        'WRITE_SHAPES','GATES','GATE_DECISIONS','GRANTS','RISK_ACCEPTANCES','HELD_BATCHES','HELD_BATCH_ITEMS','WRITE_LEASES',
        'PAPERS','WRITE_LOG','BUILD_STATE','MISSING','PLSQL_SNAPSHOTS','CASE_KEYS','CASE_HANDLES',
        'AUDIT_LEDGER','LEDGER_SEALS','LEDGER_EXPORTS','COST_LEDGER',
        'PROMPTS','MODEL_ROUTES','PROFILES','SKILLS','EVAL_SETS','EVAL_RUNS','GOVERNANCE_EVENTS','CONNECTOR_SCOPES','CONNECTOR_HEALTH',
        'MEMORY_DOMAINS','MEMORY_ARTICLES','MEMORY_USES','EXPERIENCE_ENTRIES',
        'POLICY_VALUES','RUNTIME_CALLS','INBOX_MESSAGES','IMPACT_LOCKS','CASE_TYPES','ROLE_ASSIGNMENTS','OUTBOX_MESSAGES','RETENTION_ACTIONS');
    v_triggers t_names := t_names(
        'AUDIT_LEDGER_IMM_TRG','AUDIT_LEDGER_CHAIN_TRG','COST_LEDGER_IMM_TRG','LEDGER_SEALS_IMM_TRG','LEDGER_EXPORTS_IMM_TRG',
        'GATE_DECISIONS_IMM_TRG','WRITE_LOG_IMM_TRG','PLSQL_SNAPSHOTS_IMM_TRG','PLAN_REVISIONS_IMM_TRG','GOVERNANCE_EVENTS_IMM_TRG',
        'EVAL_RUNS_IMM_TRG','PAPERS_IMM_TRG','MEMORY_USES_IMM_TRG','RISK_ACCEPTANCES_IMM_TRG','ROLE_ASSIGNMENTS_IMM_TRG',
        'PROMPTS_GOV_TRG','SKILLS_GOV_TRG','PROFILES_GOV_TRG','MODEL_ROUTES_GOV_TRG','MEMORY_ARTICLES_GOV_TRG','WRITE_SHAPES_GOV_TRG',
        'CASE_HANDLES_IMM_TRG','RUNTIME_CALLS_GOV_TRG','INBOX_MESSAGES_IMM_TRG','TURN_CHECKPOINTS_IMM_TRG','CASE_KEYS_GOV_TRG');
    v_n INTEGER;
    v_missing VARCHAR2(4000) := '';
BEGIN
    FOR i IN 1 .. v_tables.COUNT LOOP
        SELECT COUNT(*) INTO v_n FROM USER_TABLES WHERE TABLE_NAME = v_tables(i);
        IF v_n = 0 THEN v_missing := v_missing || ' table:' || v_tables(i); END IF;
    END LOOP;
    FOR i IN 1 .. v_triggers.COUNT LOOP
        SELECT COUNT(*) INTO v_n FROM USER_TRIGGERS WHERE TRIGGER_NAME = v_triggers(i) AND STATUS = 'ENABLED';
        IF v_n = 0 THEN v_missing := v_missing || ' trigger:' || v_triggers(i); END IF;
    END LOOP;
    SELECT COUNT(*) INTO v_n FROM USER_OBJECTS WHERE OBJECT_TYPE = 'TRIGGER' AND STATUS = 'INVALID';
    IF v_n > 0 THEN v_missing := v_missing || ' invalid_triggers:' || v_n; END IF;
    SELECT COUNT(*) INTO v_n FROM "SRD_SUPPORT"."POLICY_VALUES" WHERE "SCOPE" = 'platform' AND "STATE" = 'published' AND "KEY" IN ('budget.dimension','ledger.seal_interval','writes.always_human','writes.shape_expiry_default');
    IF v_n < 4 THEN v_missing := v_missing || ' seed:platform_policy(' || v_n || '/4)'; END IF;

    IF v_missing IS NOT NULL THEN
        RAISE_APPLICATION_ERROR(-20090, 'verify-schema FAILED:' || v_missing);
    END IF;
    DBMS_OUTPUT.PUT_LINE('verify-schema: OK — ' || v_tables.COUNT || ' tables, ' || v_triggers.COUNT || ' guards, platform policy seeded');
END;
/