gabriel / musehub public
test_database.py python
191 lines 7.7 KB Cold
Raw
sha256:efd76c389b3e14781ef11c102e3a52f41e9f076a0c5bcc4a1dc7118cc0352a9b fix(db): bound per-process connection pools to the database… Opus 5.5 minor ⚠ breaking 11 days ago
1 """Tests for checklist section 6.1 — Database performance.
2
3 Covers:
4 - Composite indexes exist on high-traffic query columns (ORM model inspection)
5 - Migration 0022 adds all expected composite indexes
6 - Slow query threshold is configured
7 - Slow query listener is registered on the engine (logs WARNING on slow statements)
8 - Query runbook document exists
9 """
10 from __future__ import annotations
11
12 import pathlib
13
14 import pytest
15
16 _REPO_ROOT = pathlib.Path(__file__).parent.parent
17
18
19 # ---------------------------------------------------------------------------
20 # ORM composite index declarations
21 # ---------------------------------------------------------------------------
22
23 def _index_names(model_class: type) -> set[str]:
24 """Return the set of index names declared on a model's table."""
25 return {idx.name for idx in model_class.__table__.indexes}
26
27
28 def test_musehub_repos_has_owner_visibility_composite() -> None:
29 from musehub.db.musehub_repo_models import MusehubRepo
30 assert "ix_musehub_repos_owner_visibility" in _index_names(MusehubRepo), (
31 "MusehubRepo must have a composite index on (owner, visibility) "
32 "for the explore-page public-repo query."
33 )
34
35
36 def test_musehub_commits_has_repo_branch_composite() -> None:
37 # repo_id moved to MusehubCommitRef; repo-scoped queries filter via that table.
38 from musehub.db.musehub_repo_models import MusehubCommitRef
39 assert "ix_musehub_commit_refs_repo_id" in _index_names(MusehubCommitRef), (
40 "MusehubCommitRef must have an index on repo_id for HEAD lookup queries "
41 "(replaces the old ix_musehub_commits_repo_branch composite)."
42 )
43
44
45 def test_musehub_commits_has_repo_timestamp_composite() -> None:
46 # timestamp ordering is on MusehubCommit; repo scoping is via MusehubCommitRef.
47 from musehub.db.musehub_repo_models import MusehubCommit
48 assert "ix_musehub_commits_timestamp" in _index_names(MusehubCommit), (
49 "MusehubCommit must have an index on timestamp for the recent-commits feed "
50 "(replaces the old ix_musehub_commits_repo_timestamp composite)."
51 )
52
53
54
55 def test_musehub_issues_has_repo_state_composite() -> None:
56 from musehub.db.musehub_social_models import MusehubIssue
57 assert "ix_musehub_issues_repo_state" in _index_names(MusehubIssue), (
58 "MusehubIssue must have a composite index on (repo_id, state) "
59 "for the open/closed issue list."
60 )
61
62
63 def test_musehub_issues_has_repo_number_composite() -> None:
64 from musehub.db.musehub_social_models import MusehubIssue
65 assert "ix_musehub_issues_repo_number" in _index_names(MusehubIssue), (
66 "MusehubIssue must have a composite index on (repo_id, number) "
67 "for direct issue URL lookup."
68 )
69
70
71 def test_musehub_proposals_has_repo_state_composite() -> None:
72 from musehub.db.musehub_social_models import MusehubProposal
73 assert "ix_musehub_proposals_repo_state" in _index_names(MusehubProposal), (
74 "MusehubProposal must have a composite index on (repo_id, state) "
75 "for the open/closed/merged proposal list."
76 )
77
78
79 def test_musehub_proposals_has_repo_number_composite() -> None:
80 from musehub.db.musehub_social_models import MusehubProposal
81 assert "ix_musehub_proposals_repo_number" in _index_names(MusehubProposal), (
82 "MusehubProposal must have a composite index on (repo_id, proposal_number)."
83 )
84
85
86 def test_musehub_intel_results_has_repo_type_composite() -> None:
87 from musehub.db.musehub_intel_models import MusehubIntelResult
88 assert "ix_musehub_intel_results_repo_type" in _index_names(MusehubIntelResult), (
89 "MusehubIntelResult must have a composite index on (repo_id, intel_type) "
90 "for O(1) result lookup."
91 )
92
93
94 # ---------------------------------------------------------------------------
95 # Migration 0022 references all composite indexes
96 # ---------------------------------------------------------------------------
97
98 def test_migration_0022_creates_composite_indexes() -> None:
99 """Consolidated migration 0001 upgrade() must create all expected composite indexes."""
100 import inspect
101 from alembic.config import Config
102 from alembic.script import ScriptDirectory
103
104 cfg = Config(str(_REPO_ROOT / "alembic.ini"))
105 cfg.set_main_option("script_location", str(_REPO_ROOT / "alembic"))
106 sd = ScriptDirectory.from_config(cfg)
107
108 rev = next((r for r in sd.walk_revisions() if r.revision == "0001"), None)
109 assert rev is not None, "Revision 0001 not found — consolidated schema migration is missing."
110 assert rev.module is not None
111
112 up_src = inspect.getsource(getattr(rev.module, "upgrade"))
113 expected_indexes = [
114 "ix_musehub_commits_repo_branch",
115 "ix_musehub_commits_repo_timestamp",
116 "ix_musehub_issues_repo_state",
117 "ix_musehub_issues_repo_number",
118 "ix_musehub_proposals_repo_state",
119 "ix_musehub_proposals_repo_number",
120 "ix_musehub_intel_results_repo_type",
121 ]
122 missing = [idx for idx in expected_indexes if idx not in up_src]
123 assert not missing, f"Migration 0001 upgrade() is missing these indexes: {missing}"
124
125
126 def test_slow_query_threshold_configured() -> None:
127 """slow_query_threshold_ms must be set in config."""
128 from musehub.config import settings
129 assert settings.slow_query_threshold_ms >= 0, (
130 "slow_query_threshold_ms must be a non-negative integer. "
131 "Set to 0 to disable, or a positive value to enable slow query logging."
132 )
133
134
135 def test_slow_query_threshold_is_100ms_or_less_by_default() -> None:
136 """Default slow query threshold must be ≤ 100 ms (matches checklist requirement)."""
137 from musehub.config import settings
138 assert settings.slow_query_threshold_ms <= 100, (
139 f"Default slow_query_threshold_ms={settings.slow_query_threshold_ms} "
140 "exceeds the 100 ms checklist requirement."
141 )
142
143
144 # ---------------------------------------------------------------------------
145 # Slow query listener — registered on the engine
146 # ---------------------------------------------------------------------------
147
148 def test_slow_query_listener_registered_in_database_py() -> None:
149 """database.py must register before_cursor_execute and after_cursor_execute listeners."""
150 db_src = (_REPO_ROOT / "musehub" / "db" / "database.py").read_text()
151 assert "before_cursor_execute" in db_src, (
152 "database.py must register a 'before_cursor_execute' event listener "
153 "to time query execution."
154 )
155 assert "after_cursor_execute" in db_src, (
156 "database.py must register an 'after_cursor_execute' event listener "
157 "to log slow queries."
158 )
159 assert "SLOW QUERY" in db_src, (
160 "database.py must log 'SLOW QUERY' warnings for slow statements."
161 )
162
163
164 # ---------------------------------------------------------------------------
165 # Query runbook
166 # ---------------------------------------------------------------------------
167
168 def test_db_query_runbook_exists() -> None:
169 """docs/db-query-runbook.md must exist with the top-10 query analysis."""
170 runbook = _REPO_ROOT / "docs" / "db-query-runbook.md"
171 assert runbook.exists(), (
172 "docs/db-query-runbook.md is missing. "
173 "This file documents the EXPLAIN ANALYZE results for the top-10 queries."
174 )
175
176
177 def test_db_query_runbook_covers_top_queries() -> None:
178 """Runbook must document all 10 high-traffic query patterns."""
179 runbook = (_REPO_ROOT / "docs" / "db-query-runbook.md").read_text()
180 required = [
181 "musehub_repos",
182 "musehub_commits",
183 "musehub_issues",
184 "musehub_proposals",
185 "musehub_intel_results",
186 "musehub_objects",
187 "EXPLAIN",
188 "SLOW QUERY",
189 ]
190 missing = [kw for kw in required if kw not in runbook]
191 assert not missing, f"db-query-runbook.md is missing coverage for: {missing}"
File History 8 commits
sha256:efd76c389b3e14781ef11c102e3a52f41e9f076a0c5bcc4a1dc7118cc0352a9b fix(db): bound per-process connection pools to the database… Opus 5.5 minor ⚠ 11 days ago
sha256:b8884c81af5e135510894a537784c16158ee9c2c05d074f978611bbcb611ca10 fix: port security checks inline, remove repo_id from Museh… Sonnet 4.6 minor ⚠ 181 days ago
sha256:2e9f6b5cc193e07b2c23d17fb71851db72e6160ba472013f5bccab8351264e79 refactor: migrate all musehub_models shim call sites to dom… Sonnet 4.6 minor ⚠ 188 days ago
sha256:610bebdf8c345da9ee65454fc6f3b19060ef5fb36e008d675b6d036104deb1c3 chore: fix all param_object typing violations — achieve 0 ratchet Sonnet 4.6 minor ⚠ 201 days ago
sha256:73de8cc7d51b87bc0fa9ee5a10e2ca742e363db7aa070a77abed6fcbc487eab0 fix: compute content-addressed IDs for all ORM inserts in t… Sonnet 4.6 minor ⚠ 203 days ago
sha256:0c53e64d8f7d1cd3bc244c843734393417f9a0d35aa86f705b705fd088d4e9e4 refactor: domain-agnostic intel system + MWP wire protocol … Sonnet 4.6 minor ⚠ 209 days ago
sha256:6b76f58affa66ef710e49b882cf808fc6cd75418e246ebb3c44ec0fae2ef50ea feat(phase5): drop musehub_objects.repo_id — replace with o… Human minor ⚠ 217 days ago
sha256:1023a5ce87c517287e801d82ad4243278310d0420cd8d628b253ebdff5caa80b refactor(tests): strip _sectionN suffixes from all test filenames Human major ⚠ 218 days ago