Codebase intelligence for AI and humans: code health scores, auto-generated docs, git analytics, dead code detection, and architectural decisions via MCP.
Python aicode-complexitycode-healthcode-intelligencecode-qualitydead-codedeveloper-toolsdocumentationgit-analyticsmcpopen-sourcepythonrefactoringstatic-analysistechnical-debt
"""SQL health markers: routine CCN + statement smells.
Acceptance rule from the marker plan: zero false fires on the negative set.
Every smell test has a paired negative asserting silence.
"""
from __future__ import annotations
from types import SimpleNamespace
from repowise.core.analysis.health.biomarkers.base import FileContext
from repowise.core.analysis.health.biomarkers.registry import detect_all
from repowise.core.analysis.health.models import Severity
from repowise.core.analysis.health.scoring import (
biomarker_dimension,
dimensions_for,
score_file,
)
from repowise.core.analysis.health.sql_complexity import (
_body_ccn,
_statement_smells,
walk_sql_file,
)
def _fi(name: str = "schema.sql") -> SimpleNamespace:
return SimpleNamespace(abs_path=f"/repo/{name}", path=name, language="sql")
def _walk(sql: str):
return walk_sql_file(_fi(), sql.encode("utf-8"))
def _smell_kinds(sql: str) -> list[str]:
return sorted(h.kind for h in _statement_smells(sql, None))
# ---------------------------------------------------------------------------
# Routine CCN (text tier)
# ---------------------------------------------------------------------------
class TestBodyCcn:
def test_flat_body_is_one(self) -> None:
assert _body_ccn("BEGIN RETURN 1; END") == 1
def test_plpgsql_if_elsif(self) -> None:
body = "BEGIN IF a THEN RETURN 1; ELSIF b THEN RETURN 2; END IF; END"
# 1 + IF + ELSIF (END IF's token discounted)
assert _body_ccn(body) == 3
def test_while_loop_not_double_counted(self) -> None:
body = "BEGIN WHILE x < 10 LOOP SET x = x + 1; END LOOP; END"
# PL/pgSQL WHILE..LOOP nets to a single decision point.
assert _body_ccn(body) == 2
def test_tsql_while(self) -> None:
body = "BEGIN WHILE @x < 10 BEGIN SET @x = @x + 1 END END"
assert _body_ccn(body) == 2
def test_boolean_operators_count(self) -> None:
body = "BEGIN IF a > 0 AND b > 0 OR c THEN RETURN 1; END IF; END"
assert _body_ccn(body) == 4
def test_between_and_discounted(self) -> None:
body = "BEGIN SELECT 1 WHERE x BETWEEN 1 AND 10; END"
assert _body_ccn(body) == 1
def test_comments_and_strings_ignored(self) -> None:
body = "BEGIN -- IF this OR that\n SELECT 'WHILE AND OR'; /* WHEN */ END"
assert _body_ccn(body) == 1
class TestWalkRoutines:
def test_postgres_function_metrics(self) -> None:
sql = (
"CREATE FUNCTION add_em(a INT, b INT) RETURNS INT AS $$\n"
"BEGIN\n"
" IF a > 0 AND b > 0 THEN\n"
" RETURN a + b;\n"
" END IF;\n"
" RETURN 0;\n"
"END;\n"
"$$ LANGUAGE plpgsql;\n"
)
fcx = _walk(sql)
assert [f.name for f in fcx.functions] == ["add_em"]
fn = fcx.functions[0]
assert fn.ccn == 3 # IF + AND
assert fn.start_line == 1
assert fn.nloc > 0
assert fcx.file_nloc > 0
def test_tsql_procedure_metrics(self) -> None:
sql = (
"CREATE PROCEDURE dbo.process_orders @status INT\n"
"AS\nBEGIN\n"
" IF @status = 1\n"
" BEGIN\n"
" UPDATE orders SET state = 'x' WHERE id = @status;\n"
" END\n"
" WHILE @status < 10\n"
" BEGIN\n"
" SET @status = @status + 1;\n"
" END\n"
"END\n"
)
fcx = _walk(sql)
assert [f.name for f in fcx.functions] == ["process_orders"]
assert fcx.functions[0].ccn == 3 # IF + WHILE
def test_high_ccn_emits_hit(self) -> None:
branches = "".join(f" IF x = {i} THEN RETURN {i}; END IF;\n" for i in range(12))
sql = f"CREATE FUNCTION f() RETURNS INT AS $$\nBEGIN\n{branches}END;\n$$;"
fcx = _walk(sql)
hits = [h for h in fcx.perf_hits if h.kind == "sql_high_complexity"]
assert len(hits) == 1
assert int(hits[0].detail) == 13
assert hits[0].function == "f"
def test_simple_routine_emits_no_hit(self) -> None:
sql = "CREATE FUNCTION f() RETURNS INT AS $$ SELECT 1 $$;"
assert [h for h in _walk(sql).perf_hits if h.kind == "sql_high_complexity"] == []
def test_plain_ddl_yields_no_functions(self) -> None:
fcx = _walk("CREATE TABLE users (id INT);")
assert fcx.functions == []
# ---------------------------------------------------------------------------
# Statement smells (AST tier): every positive has a paired negative
# ---------------------------------------------------------------------------
class TestSelectStar:
def test_fires_in_view(self) -> None:
assert _smell_kinds("CREATE VIEW v AS SELECT * FROM t") == ["sql_select_star"]
def test_silent_in_ad_hoc_script(self) -> None:
assert _smell_kinds("SELECT * FROM t") == []
def test_silent_for_qualified_star(self) -> None:
assert _smell_kinds("CREATE VIEW v AS SELECT t.* FROM t") == []
def test_silent_for_count_star(self) -> None:
assert _smell_kinds("CREATE VIEW v AS SELECT COUNT(*) FROM t") == []
def test_one_finding_per_view(self) -> None:
sql = "CREATE VIEW v AS SELECT * FROM (SELECT * FROM t) sub"
assert _smell_kinds(sql) == ["sql_select_star"]
def test_fires_on_plain_table_in_view(self) -> None:
assert _smell_kinds("CREATE VIEW v AS SELECT * FROM users") == ["sql_select_star"]
def test_silent_inside_exists(self) -> None:
sql = (
"CREATE VIEW v AS SELECT id FROM users u "
"WHERE EXISTS (SELECT * FROM orders o WHERE o.user_id = u.id)"
)
assert _smell_kinds(sql) == []
def test_silent_inside_not_exists(self) -> None:
sql = (
"CREATE VIEW v AS SELECT id FROM users u "
"WHERE NOT EXISTS (SELECT * FROM orders o WHERE o.user_id = u.id)"
)
assert _smell_kinds(sql) == []
def test_silent_over_explicit_cte(self) -> None:
sql = "CREATE VIEW v AS WITH c AS (SELECT id, name FROM users) SELECT * FROM c"
assert _smell_kinds(sql) == []
def test_silent_over_explicit_subquery(self) -> None:
sql = "CREATE VIEW v AS SELECT * FROM (SELECT id, name FROM users) sub"
assert _smell_kinds(sql) == []
def test_fires_when_cte_itself_selects_star(self) -> None:
sql = "CREATE VIEW v AS WITH c AS (SELECT * FROM users) SELECT * FROM c"
assert _smell_kinds(sql) == ["sql_select_star"]
def test_fires_on_qualified_table_sharing_a_cte_name(self) -> None:
sql = "CREATE VIEW v AS WITH users AS (SELECT id FROM t) SELECT * FROM other.users"
assert _smell_kinds(sql) == ["sql_select_star"]
def test_fires_when_star_joins_base_table(self) -> None:
sql = (
"CREATE VIEW v AS WITH c AS (SELECT id FROM users) "
"SELECT * FROM c JOIN orders o ON o.user_id = c.id"
)
assert _smell_kinds(sql) == ["sql_select_star"]
class TestUpdateDeleteWithoutWhere:
def test_update_fires(self) -> None:
hits = _statement_smells("UPDATE users SET active = 0", None)
assert [h.kind for h in hits] == ["sql_update_delete_without_where"]
assert "update users" in hits[0].detail
def test_delete_fires(self) -> None:
assert _smell_kinds("DELETE FROM sessions") == ["sql_update_delete_without_where"]
def test_silent_on_temp_table_from_same_file(self) -> None:
sql = "CREATE TEMP TABLE tmp (id INT); DELETE FROM tmp; UPDATE tmp SET id = 0"
assert _smell_kinds(sql) == []
def test_fires_on_regular_table_created_in_same_file(self) -> None:
sql = "CREATE TABLE logs (id INT); DELETE FROM logs"
assert _smell_kinds(sql) == ["sql_update_delete_without_where"]
def test_silent_with_where(self) -> None:
assert _smell_kinds("UPDATE users SET active = 0 WHERE id = 1") == []
assert _smell_kinds("DELETE FROM sessions WHERE expired = 1") == []
def test_silent_on_dialect_garble(self) -> None:
# A backtick-quoted MySQL UPDATE read under the permissive default
# dialect garbles the tokenization (found live on umami: the statement
# HAS a WHERE that the broken parse loses). An unclean target name
# means the parse is untrustworthy: no signal.
sql = "UPDATE `website_event`\nSET x = 1\nWHERE url_query IS NOT NULL;"
assert _smell_kinds(sql) == []
class TestCartesianJoin:
def test_comma_join_without_where_fires(self) -> None:
assert _smell_kinds("SELECT a.x, b.y FROM a, b") == ["sql_cartesian_join"]
def test_comma_join_with_where_silent(self) -> None:
assert _smell_kinds("SELECT a.x FROM a, b WHERE a.id = b.id") == []
def test_explicit_cross_join_silent(self) -> None:
assert _smell_kinds("SELECT a.x FROM a CROSS JOIN b") == []
def test_keyed_join_silent(self) -> None:
assert _smell_kinds("SELECT a.x FROM a JOIN b ON a.id = b.id") == []
assert _smell_kinds("SELECT a.x FROM a JOIN b USING (id)") == []
def test_multi_target_select_into_is_silent(self) -> None:
"""Issue #1502: ``SELECT ... INTO a, b`` parses the trailing target as
a comma-join, so it must not be flagged as a cartesian join."""
assert _smell_kinds("select a, b into x, y from t where c = 1;") == []
assert _smell_kinds("select a, b into x, y from t;") == []
def test_single_target_select_into_is_silent(self) -> None:
assert _smell_kinds("select a into x from t;") == []
def test_multi_target_into_does_not_mask_real_cartesian(self) -> None:
"""The INTO escape must not silence a genuine comma-join elsewhere."""
assert _smell_kinds(
"select a into x from t;\nselect * from orders o, customers c;"
) == ["sql_cartesian_join"]
class TestTemplatedFilesSilent:
def test_dbt_model_yields_no_smells(self) -> None:
sql = "select * from {{ ref('stg_orders') }}\nwhere x = 1"
assert _statement_smells(sql, None) == []
def test_jinja_block_yields_no_smells(self) -> None:
sql = "{% set x = 1 %}\nUPDATE t SET y = 2"
assert _statement_smells(sql, None) == []
class TestMalformedDegradation:
def test_garbage_yields_empty_complexity(self) -> None:
fcx = _walk("CREATE (((( garbage ;;;")
assert fcx.functions == []
assert fcx.perf_hits == []
assert fcx.file_nloc >= 1
# ---------------------------------------------------------------------------
# Detector lift + scoring dimensions
# ---------------------------------------------------------------------------
def _ctx_for(sql: str) -> FileContext:
fcx = _walk(sql)
return FileContext(
file_path="schema.sql",
language="sql",
nloc=fcx.file_nloc,
has_test_file=False,
module=None,
perf_hits=fcx.perf_hits,
)
class TestDetectors:
def test_smells_become_findings(self) -> None:
ctx = _ctx_for(
"CREATE VIEW v AS SELECT * FROM t;\n"
"UPDATE users SET active = 0;\n"
"SELECT a.x FROM a, b;\n"
)
kinds = sorted(r.biomarker_type for r in detect_all(ctx))
assert kinds == [
"sql_cartesian_join",
"sql_select_star",
"sql_update_delete_without_where",
]
def test_high_complexity_severity_scales(self) -> None:
branches = "".join(f"IF x = {i} THEN RETURN {i}; END IF;\n" for i in range(25))
ctx = _ctx_for(f"CREATE FUNCTION f() RETURNS INT AS $$\nBEGIN\n{branches}END $$;")
results = [r for r in detect_all(ctx) if r.biomarker_type == "sql_high_complexity"]
assert len(results) == 1
assert results[0].severity == Severity.HIGH
def test_clean_sql_yields_nothing(self) -> None:
ctx = _ctx_for(
"CREATE TABLE users (id INT PRIMARY KEY);\n"
"CREATE VIEW v AS SELECT id FROM users WHERE id > 0;\n"
"DELETE FROM users WHERE id = 1;\n"
)
assert [r for r in detect_all(ctx) if r.biomarker_type.startswith("sql_")] == []
class TestScoringDimensions:
def test_every_sql_marker_excludes_defect(self) -> None:
for name in (
"sql_high_complexity",
"sql_select_star",
"sql_update_delete_without_where",
"sql_cartesian_join",
):
assert "defect" not in dimensions_for(name), name
def test_homes(self) -> None:
assert biomarker_dimension("sql_high_complexity") == "maintainability"
assert biomarker_dimension("sql_select_star") == "maintainability"
assert biomarker_dimension("sql_update_delete_without_where") == "maintainability"
assert biomarker_dimension("sql_cartesian_join") == "performance"
def test_defect_score_untouched_by_sql_findings(self) -> None:
ctx = _ctx_for(
"CREATE VIEW v AS SELECT * FROM t;\n"
"UPDATE users SET active = 0;\n"
"SELECT a.x FROM a, b;\n"
)
results = detect_all(ctx)
assert results # sanity: the smells fired
scores, _ = score_file(results)
assert scores["defect"] == 10.0
assert scores["maintainability"] < 10.0
assert scores["performance"] < 10.0