lint_ddl.py

Python · 93 lines · 4,439 bytes · wiki path 10 Architecture/db/SrdSupport/lint_ddl.py · 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 · upgrade.sql · verify-ledger.sql · verify-schema.sql

# -*- coding: utf-8 -*-
r"""
Offline lint for the SrdSupport DbScripts (no database needed).

    python lint_ddl.py

Checks, per upgrade.sql order:
  1. every q'~ ... ~' literal is closed and every BEGIN/END; / block is terminated by '/';
  2. every FOREIGN KEY ... REFERENCES "SRD_SUPPORT"."T" names a table created in the same or an earlier script;
  3. every trigger's ON table exists;
  4. verify-schema.sql lists exactly the created tables and the created triggers;
  5. constraint and index names are unique across the set (Oracle: one namespace per schema).
Exit 0 = clean, 1 = findings.
"""
import os, re, sys

HERE = os.path.dirname(os.path.abspath(__file__))
order = [l.strip()[2:] for l in open(os.path.join(HERE, "upgrade.sql"), encoding="utf-8") if l.strip().startswith("@@")]

CREATE_RE = re.compile(r'CREATE TABLE\s+"SRD_SUPPORT"\."([A-Z_]+)"')
REF_RE = re.compile(r'REFERENCES\s+"SRD_SUPPORT"\."([A-Z_]+)"')
TRG_RE = re.compile(r'CREATE OR REPLACE TRIGGER\s+"SRD_SUPPORT"\."([A-Z_]+)"\s+BEFORE\s+[A-Z ]+\s+ON\s+"SRD_SUPPORT"\."([A-Z_]+)"', re.S)
CONS_RE = re.compile(r'CONSTRAINT\s+"([A-Z_0-9]+)"')
IDX_RE = re.compile(r'CREATE (?:UNIQUE )?INDEX\s+"SRD_SUPPORT"\."([A-Z_0-9]+)"')
ALTER_FK_RE = re.compile(r'ALTER TABLE\s+"SRD_SUPPORT"\."([A-Z_]+)"\s+ADD CONSTRAINT\s+"([A-Z_0-9]+)"')

created, triggers, findings = [], [], []
names = {}

for f in order:
    p = os.path.join(HERE, f)
    txt = open(p, encoding="utf-8").read()
    # 1. quoting and block termination: split on '/' lines; every chunk is exactly one PL/SQL unit
    opens, closes = txt.count("q'~"), txt.count("~'")
    if opens != closes:
        findings.append(f"{f}: q'~ literals open={opens} close={closes}")
    chunks = [c for c in re.split(r"^/\s*$", txt, flags=re.M)]
    if chunks and chunks[-1].strip():
        findings.append(f"{f}: trailing PL/SQL unit without a '/' terminator")
    for i, c in enumerate(chunks[:-1]):
        body = re.sub(r"^(SET |WHENEVER |--).*$", "", c, flags=re.M)
        units = len(re.findall(r"^CREATE OR REPLACE TRIGGER\b", body, re.M))
        if units == 0:
            units = len(re.findall(r"^DECLARE\b", body, re.M))
        if units == 0:
            units = len(re.findall(r"^BEGIN\b", body, re.M))
        if units != 1:
            findings.append(f"{f}: chunk {i+1} holds {units} PL/SQL units (expected 1)")
        if not re.search(r"^END;\s*$", body, re.M):
            findings.append(f"{f}: chunk {i+1} has no 'END;'")
    # 2. tables and references
    here_created = CREATE_RE.findall(txt)
    for t in here_created:
        if t in created:
            findings.append(f"{f}: table {t} created twice")
    created.extend(here_created)
    for t in REF_RE.findall(txt):
        if t not in created:
            findings.append(f"{f}: REFERENCES {t} before it is created")
    # 3. triggers
    for trg, tbl in TRG_RE.findall(txt):
        if trg not in triggers: triggers.append(trg)
        if tbl not in created:
            findings.append(f"{f}: trigger {trg} on unknown table {tbl}")
    # 5. name uniqueness
    for n in CONS_RE.findall(txt) + IDX_RE.findall(txt) + [c for _, c in ALTER_FK_RE.findall(txt)]:
        if n in names and names[n] != f + ":" + n:
            findings.append(f"{f}: name {n} already used in {names[n]}")
        names.setdefault(n, f + ":" + n)
    for n in CONS_RE.findall(txt) + IDX_RE.findall(txt):
        if len(n) > 128:
            findings.append(f"{f}: identifier too long: {n}")

# 4. verify-schema inventory
vs = open(os.path.join(HERE, "verify-schema.sql"), encoding="utf-8").read()
listed_tables = set(re.findall(r"'([A-Z_]+)'", vs.split("v_triggers")[0]))
listed_triggers = set(re.findall(r"'([A-Z_]+)'", vs.split("v_triggers")[1].split("v_n INTEGER")[0]))
for t in created:
    if t not in listed_tables:
        findings.append(f"verify-schema.sql: table {t} not listed")
for t in listed_tables - set(created):
    findings.append(f"verify-schema.sql: lists table {t} that no script creates")
for t in triggers:
    if t not in listed_triggers:
        findings.append(f"verify-schema.sql: trigger {t} not listed")
for t in listed_triggers - set(triggers):
    findings.append(f"verify-schema.sql: lists trigger {t} that no script creates")

print(f"scripts: {len(order)}  tables: {len(created)}  triggers: {len(triggers)}  names: {len(names)}")
if findings:
    print("\n".join("  ! " + x for x in findings))
    sys.exit(1)
print("lint_ddl: OK")