# Oracle DB MCP Tool - System Patterns

## Architecture Overview

```mermaid
graph TD
    A[VS Code MCP Client] --> B[MCP Server Python]
    B --> C[Oracle Connection Pool]
    C --> D[Oracle Database]

    B --> E[Query Handler]
    B --> F[Metadata Handler]
    B --> G[PL/SQL Handler]

    E --> H[Result Formatter]
    F --> H
    G --> H

    H --> I[JSON Response]
    I --> A
```

## Component and Boundary Contracts

### MCP Server Layer

- **Responsibility**: Protocol handling, tool registration, request routing
- **Interface**: MCP protocol messages
- **Boundaries**: Isolated from Oracle-specific implementation details

### Oracle Connection Layer

- **Responsibility**: Connection management, pooling, timeout handling
- **Interface**: Connection acquisition/release
- **Boundaries**: Encapsulates cx_Oracle specifics

### Operation Handlers

- **Responsibility**: Specific database operations (query, metadata, PL/SQL)
- **Interface**: Standardized request/response format
- **Boundaries**: Each handler focuses on one operation type

### Utility Layer

- **Responsibility**: JSON serialization, logging, error handling
- **Interface**: Helper functions for common tasks
- **Boundaries**: No direct database access

## Architecture Decision Records (ADRs)

### ADR-001: Use MCP Protocol for VS Code Integration

**Date**: 2025-11-09
**Status**: Accepted
**Context**: Need to integrate with VS Code while maintaining separation of concerns
**Decision**: Implement MCP server using Python MCP library
**Consequences**:

- Positive: Standard integration with VS Code
- Positive: Clear protocol boundaries
- Negative: Additional dependency on MCP library
  **Alternatives**:
- Direct VS Code extension (more complex)
- Language Server Protocol (not suited for database operations)

### ADR-002: Use cx_Oracle 8.3.0 for Database Connectivity

**Date**: 2025-11-09
**Status**: Accepted
**Context**: Need reliable Oracle Database connection from 32-bit Python
**Decision**: Use cx_Oracle 8.3.0 specifically
**Consequences**:

- Positive: Stable, well-tested driver
- Positive: Compatible with 32-bit Python
- Negative: Version locked to specific release
  **Alternatives**:
- Newer cx_Oracle versions (may have 32-bit compatibility issues)
- Other Oracle drivers (less tested)

### ADR-003: Implement Simple Safety Controls

**Date**: 2025-11-09
**Status**: Accepted
**Context**: Need to prevent accidental expensive operations
**Decision**: Implement timeout, max rows, and max text size limits for all database operations including metadata queries
**Consequences**:

- Positive: Prevents runaway queries and metadata enumeration
- Positive: Simple to implement and understand
- Negative: May limit legitimate large operations
- Positive: Consistent safety controls across all operation types (query, metadata, PL/SQL)
  **Rationale**: After investigation, metadata operations (list_schemas, list_objects) were not enforcing row limits, potentially causing context overflow in MCP responses. Row limits have been implemented consistently across all operations.

**Implementation Details**:

- MCP_MAX_ROWS environment variable controls maximum rows returned (default: 500)
- MCP_MAX_TEXT_SIZE environment variable controls text field truncation (default: 5000)
- MCP_TIMEOUT_S environment variable controls query timeouts (default: 30)
- All metadata operations now enforce row limits and provide truncation warnings
- Test cases validate limit enforcement and warning messages

  **Alternatives**:

- Complex query analysis (overkill for this use case)
- No limits (risky for production use)

### ADR-004: Use Environment Variables for Authentication

**Date**: 2025-11-09
**Status**: Accepted
**Context**: Need to configure database credentials without hardcoding
**Decision**: Use ORACLE_USER, ORACLE_PASS, ORACLE_DSN environment variables
**Consequences**:

- Positive: No credentials in code
- Positive: Easy to configure for different environments
- Negative: Credentials visible in process list
  **Alternatives**:

- Configuration files (risk of accidental commit)
- Secret management systems (overkill for personal use)

### ADR-005: SQL Input Sanitization for Trailing Semicolons

**Date**: 2025-11-16
**Status**: Proposed
**Context**: Users sometimes include trailing semicolons (;) in SQL queries passed to db.query and db.explain_plan tools, causing cx_Oracle execution errors (e.g., ORA-00911: invalid character). PL/SQL blocks legitimately use semicolons and should remain unaffected.
**Decision**: Automatically strip one or more trailing semicolons from the query string in OracleExecutor.query() and explain_plan() methods before execution. Log a warning if stripping occurs. Do not apply to PL/SQL operations in OraclePLSQL class.
**Consequences**:

- Positive: Improves user experience by handling common input errors without failing execution.
- Positive: Enhances safety by sanitizing input to prevent syntax errors.
- Negative: May mask legitimate trailing semicolons in rare SQL cases (e.g., compound statements), but unlikely in standard DML/DDL.
- Positive: No impact on PL/SQL, preserving correct syntax for BEGIN...END blocks.

**Alternatives**:

- Reject queries with trailing ; and return an error message (less user-friendly).
- Parse and validate full SQL syntax (overkill, requires additional dependencies like sqlparse).
- Document the issue in USAGE.md only (does not prevent errors).

**Implementation Details**:

- In query(): query = query.rstrip(';').rstrip() to remove trailing ; and whitespace.
- In explain_plan(): Apply same stripping to the input query before "EXPLAIN PLAN FOR {query}".
- Add logging: self.logger.warning(f"Stripped trailing semicolons from query: {original_query}")
- Unit test: Verify execution succeeds with/without trailing ;, and warning is logged when stripped.

**Rationale**: This is a simple, low-risk enhancement to safety controls, aligning with ADR-003 principles. It prevents common errors while maintaining transparency via logging.

### ADR-006: Enhance ping Tool and Add db_get_usage_instructions; Fix db_get_server_info

**Date**: 2025-11-17
**Status**: Proposed
**Context**: The ping tool currently only tests connectivity without providing Oracle version information, which is useful for diagnostics. db_get_server_info fails with "Failed to retrieve server info: 0" likely due to query execution error in "SELECT banner FROM v$version WHERE ROWNUM = 1" (possibly permissions or v$version access). Users need dynamic access to usage instructions without caching to allow file updates without server restart. Separate tool for usage to decouple from server info.

**Decision**:

1. Enhance ping() in OraclePool to include Oracle version query and return in response.
2. Fix db_get_server_info by adding error handling for version query (fallback to "Unknown" if fails) and ensure async executor.query succeeds.
3. Add new tool db_get_usage_instructions: No parameters, loads server/oracle/usage_description.md on each call, substitutes placeholders (oracle_version via quick query, env vars), returns formatted text. No in-memory caching; file read per request for freshness.

**Consequences**:

- Positive: ping provides more diagnostic info (version + connectivity) in one call, reducing tool invocations.
- Positive: db_get_server_info becomes reliable with fallback, combining version + usage.
- Positive: Separate usage tool allows dynamic updates (edit file, immediate effect on next call); supports LLM evolution of instructions.
- Negative: Minor perf overhead from file read/query per usage call (negligible for low-volume MCP).
- Positive: Maintains separation: ping for connectivity/version, usage for instructions, server_info for combined overview.
- Negative: If v$version query fails consistently, version remains "Unknown" – monitor logs.

**Alternatives**:

- Embed version in session_info (adds to existing tool, but ping is more focused for quick checks).
- Cache usage file (simpler perf, but requires server restart for updates; violates "no memory" req).
- Merge usage into ping (overloads ping; better separation for modularity).
- Remove db_get_server_info, use separate tools (simpler, but combined view useful for onboarding).

**Implementation Details**:

- ping(): Add cursor.execute("SELECT banner FROM v$version WHERE ROWNUM = 1"); version = result[0] if success else "Unknown"; include in return dict.
- db_get_server_info: Wrap version query in try-except; if fails, oracle_version="Unknown"; ensure await executor.query() handles errors gracefully.
- db_get_usage_instructions: New tool in list_tools() and call_tool(); read file, format with oracle_version (quick query), env vars; return {"status": "success", "usage": formatted_text}.
- Tests: Add unit tests for version fallback, file loading (mock open), substitution.
- Schema: {"type": "object", "properties": {}, "required": []}

**Rationale**: Aligns with ADR-001 (tool modularity) and ADR-003 (safety via fallbacks). Enables autonomous instruction updates without code changes. Fixes existing bug while enhancing diagnostics.

## Cross-Cutting Concerns

### Error Handling

- All database errors caught and converted to JSON error responses
- Consistent error format across all operations
- No stack traces exposed to client

### Logging

- Minimal logging to util/log.py
- Focus on connection events and errors
- No sensitive data logged

### Serialization

- Custom JSON handling for Oracle data types
- datetime and decimal converted to strings
- Long strings truncated based on max_text_size

### Security

- No SQL injection protection beyond parameter binding
- Relies on Oracle's built-in security
- No additional authentication beyond database credentials

## Related Documentation

- See [`projectbrief.md`](memory-bank/projectbrief.md) for project scope and success criteria
- See [`productContext.md`](memory-bank/productContext.md) for user requirements and UX principles
- See [`techContext.md`](memory-bank/techContext.md) for implementation details and configuration
- See [`activeContext.md`](memory-bank/activeContext.md) for current implementation status

### ADR-007: Curated MCP public tools (dotted names) with underscored aliases

- **Date:** 2025-11-23
- **Status:** Accepted
- **Context:** MCP clients (Roo / LLMs) expect a stable, machine-readable tool registry. The project documentation and usage instruction scripts use dotted tool names (e.g., `db.query`). Existing running servers and some clients historically used Python-style underscored names (e.g., `db_query`). This caused inconsistencies between docs and live server behavior after deployments.
- **Decision:** Expose a curated set of public tools using dotted names in the MCP registry to match documentation and machine-oriented instructions:
  - ping
  - db.query
  - db.list_objects
  - db.describe_object
  - db.get_ddl
  - db.explain_plan
  - db.call_proc
    Additionally register underscored aliases (db_query, db_list_objects, ...) for backwards compatibility so existing clients keep working until they adopt dotted names.
- **Rationale:**
  - Dotted names are explicit, consistent with the usage documentation ([`server/oracle/usage_description.md`](server/oracle/usage_description.md:1)) and easier for LLM instruction matching.
  - Underscored aliases avoid breaking running clients or long-lived server instances that were started before the change.
- **Consequences:**
  - Positive: Documentation now maps 1:1 to the registry; LLMs and automated tooling can select tools by documented names reliably.
  - Positive: Smooth migration path — clients may switch to dotted names without service downtime.
  - Negative: Slightly larger registry (duplicate names) — acceptable for clarity and compatibility.
  - Operational: Running server processes must be restarted to pick up the new registration. To avoid surprise, server code registers both dotted and underscored names; once clients migrate, aliases may be removed in a future ADR.
- **Implementation details:**
  - `server/mcp_server.py`: tool list description updated to list dotted names.
  - `server/main.py`: FastMCP tool registrations use explicit name= arguments for dotted names and also register alias functions with underscored names (implementation shown in code).
  - `server/oracle/usage_description.md`: authoritative list and examples updated to use dotted names.
  - Tests: Add/adjust integration tests (`test_mcp_tools.py`) to validate both dotted and underscored tool names are callable. Add CI check to assert docs and registry match (optional future improvement).
- **Alternatives considered:**
  - Only expose dotted names and require client restarts — rejected because it would break running clients.
  - Keep only underscored names — rejected because it diverges from documentation and LLM-friendly naming.
- **Next:** Keep aliases for a transition period (TBD). Remove aliases in a future ADR after clients have migrated (recommendation: keep aliases minimum 30 days and announce deprecation).
