# Oracle MCP Utility

This project exposes Oracle-backed query and business-object utilities through three entry points:

- `mcp_server.py`: stdio MCP server for local MCP clients
- `mcp_http_server.py`: HTTP/SSE MCP server for remote MCP clients such as Cline
- `http_server.py`: REST wrapper for Postman and `curl`

It is designed to reuse the same underlying tool implementations across MCP and REST transports, with configuration-driven SQL mappings and business-object metadata.

## What This Project Does

The service connects to Oracle using `python-oracledb` and supports:

- Executing registered SQL statements by key
- Executing ad hoc SQL with optional bind parameters
- Returning business-object data such as joint venture invoicing partner details
- Exposing discovery metadata for supported business units and business objects
- Serving the same capabilities over stdio MCP, streamable HTTP, SSE, and REST

## Project Structure

- `mcp_server.py`: Core FastMCP server and tool definitions
- `mcp_http_server.py`: MCP-over-HTTP or SSE transport entrypoint
- `http_server.py`: REST API wrapper around the same MCP tool functions
- `db_oracle.py`: Oracle connection and query execution helper
- `db_config.json`: Default Oracle connection settings
- `requirements.txt`: Python dependencies
- `config/registry.json`: Combined registry for tools, business units, business objects, and SQL mappings
- `config/sql_registry.json`: Friendly SQL key to file-path mapping
- `config/business_units.json`: Business unit configuration
- `config/business_objects.json`: Business object metadata
- `sql/`: SQL files used by the registered tools
- `payloads/`: Sample request payloads
- `scripts/setup_sse_local.sh`: Linux VM setup and service management script
- `logs/`: Runtime log files

## Main Tools

The core MCP server exposes these tools:

- `executeSql`: Execute a SQL file registered in `config/sql_registry.json`
- `oracle_query`: Execute arbitrary SQL with optional bind parameters and connection overrides
- `getDetails`: Resolve configured business objects such as `jointVentureInvoicingPartner`
- `getDistribution`: Return rows from the distributions query
- `jointVentureInvoicingPartners`: Convenience partner query endpoint
- `getCapabilities`: Return discovery metadata for supported business objects and business units

## Requirements

- Python 3.10 or newer recommended
- Network access to the target Oracle database
- Optional Linux VM for long-running HTTP or SSE deployment
- Optional `systemd` if you want to run the service as a managed Linux service

Install dependencies in a virtual environment:

```bash
python3 -m venv venv
source venv/bin/activate
pip install --upgrade pip
pip install -r requirements.txt
```

For Windows:

```powershell
python -m venv venv
.\venv\Scripts\Activate.ps1
pip install --upgrade pip
pip install -r requirements.txt
```

## Configuration

The default Oracle connection is read from `db_config.json`.

Expected shape:

```json
{
  "username": "fusion",
  "password": "fusion",
  "dsn": "(DESCRIPTION=...)",
  "tns_admin": ""
}
```

Notes:

- Per-request `dsn`, `username`, `password`, and `tns_admin` values can override the defaults.
- `python-oracledb` runs in thin mode by default, which is Linux-friendly and does not require Oracle Instant Client.
- If you use a TNS alias, set `tns_admin` so Oracle Net can resolve it.
- Keep real credentials out of source control in shared environments.

## Running the Servers

### 1. Stdio MCP server

Use this when your MCP client launches the process directly.

```bash
python3 mcp_server.py
```

### 2. HTTP or SSE MCP server

Use this for remote MCP clients such as Cline.

Streamable HTTP:

```bash
python3 mcp_http_server.py --host 0.0.0.0 --port 8000
```

Typical endpoint:

```text
http://<server-ip>:8000/mcp
```

SSE mode:

```bash
python3 mcp_http_server.py --host 0.0.0.0 --port 8000 --sse
```

Typical endpoint:

```text
http://<server-ip>:8000/sse
```

### 3. REST wrapper for Postman or curl

```bash
python3 http_server.py --host 0.0.0.0 --port 5001
```

## REST Endpoints

The HTTP wrapper and MCP HTTP entrypoint expose helper REST endpoints such as:

- `GET /health` on `http_server.py`
- `GET /tools`
- `GET /capabilities`
- `POST /oracle_query`
- `POST /executeSql`
- `POST /getDetails`
- `GET` or `POST /getDistribution`
- `POST /jointVentureInvoicingPartners`

## Example Requests

Discover capabilities first:

```bash
curl -s http://localhost:8000/capabilities
```

Execute a registered SQL query:

```bash
curl -X POST http://localhost:8000/executeSql \
  -H "Content-Type: application/json" \
  -d '{
    "sqlKey": "vendors_by_bu",
    "params": {
      "business_unit": "HEPP Petroleum USA BU1"
    }
  }'
```

Run an ad hoc SQL query:

```bash
curl -X POST http://localhost:5001/oracle_query \
  -H "Content-Type: application/json" \
  -d '{
    "sql": "select * from fusion.jv_distributions where rownum <= 1",
    "params": {}
  }'
```

Query a business object:

```bash
curl -X POST http://localhost:8000/getDetails \
  -H "Content-Type: application/json" \
  -d '{
    "BusinessObject": "jointVentureInvoicingPartner",
    "BusinessUnit": "HEPP Petroleum USA BU1"
  }'
```

## Linux Setup Script

For Linux VM deployment, use:

```bash
chmod +x scripts/setup_sse_local.sh
scripts/setup_sse_local.sh install
```

The script can:

- Create a virtual environment
- Install requirements
- Start the MCP server in SSE or HTTP mode
- Optionally start the REST wrapper
- Run services with `systemd` or `nohup`
- Show status, stop, and uninstall services

Common commands:

```bash
scripts/setup_sse_local.sh install
scripts/setup_sse_local.sh start
scripts/setup_sse_local.sh status
scripts/setup_sse_local.sh stop
scripts/setup_sse_local.sh uninstall
```

## SQL and Metadata Configuration

The project uses configuration files to avoid hardcoding query mappings:

- `config/registry.json`: consolidated source for tools, SQL paths, business units, and business objects
- `config/sql_registry.json`: alternate SQL key registry
- `sql/vendors_by_bu.sql` and `sql/accounts_by_bu.sql`: partner lookup queries
- `sql/distributions.sql`: distribution query

The `getCapabilities` tool is a good first call for clients that need to discover valid business units and supported business objects dynamically.

## Logging

- `mcp_server.py` writes a log file into the system temp directory as `fa_utility_mcp_server.log`
- `http_server.py` and `mcp_http_server.py` log request activity to standard output
- `logs/` is used by the Linux setup script for service logs

## Development Notes

- `mcp_server.py` uses FastMCP and falls back between import paths to support different package layouts.
- `http_server.py` and `mcp_http_server.py` import tool functions directly from `mcp_server.py` so behavior stays consistent.
- The project is structured so new SQL files and business objects can be added with minimal code changes.

## Suggested Next Improvements

- Move secrets from `db_config.json` into environment variables or a secrets manager
- Add automated tests for each endpoint and business-object path
- Add request and response schema validation
- Add a sample `.env.example` file
- Add deployment notes for reverse proxy and TLS termination if this is exposed beyond a private network