# SankhyaMCP

MCP (Model Context Protocol) server for the Sankhya ERP Oracle database. Exposes safe SQL execution over multiple named Oracle connections to AI agents via the MCP protocol.

## Overview

- Dynamic multi-connection Oracle support — define any number of named connections via environment variables
- Read-only tool (`execute_query`) enforces SELECT/WITH/EXPLAIN only
- Writable tool (`execute_unsafe_query`) for DML/DDL — blocked on read-only connections
- CSV output for SELECT queries; success message for DML/DDL
- Never raises — all errors returned as `"Erro[...]: ..."` strings
- Built with [FastMCP](https://github.com/jlowin/fastmcp) and managed with [uv](https://docs.astral.sh/uv/)

## Prerequisites

- Python 3.11+
- `uv` — [install guide](https://docs.astral.sh/uv/getting-started/installation/)
- No Oracle Client required (uses `oracledb` thin mode)
- Oracle database accessible from the machine running the server

## Setup

1. Clone the repository and install dependencies:

```bash
uv sync
```

2. Configure environment variables in `.vscode/mcp.json` (copy from the template in this repo — the file is git-ignored):

```json
{
  "servers": {
    "sankhya-mcp": {
      "type": "stdio",
      "command": "${workspaceFolder}/.venv/Scripts/python.exe",
      "args": ["${workspaceFolder}/mcp_server.py"],
      "env": {
        "ORACLE_CONNECTIONS": "teste,producao",
        "ORACLE_TESTE_USER": "your_user",
        "ORACLE_TESTE_PASSWORD": "your_password",
        "ORACLE_TESTE_DSN": "host:port/service_name",
        "ORACLE_PRODUCAO_USER": "your_user",
        "ORACLE_PRODUCAO_PASSWORD": "your_password",
        "ORACLE_PRODUCAO_DSN": "host:port/service_name",
        "ORACLE_PRODUCAO_READONLY": "true",
        "ORACLE_DEFAULT_CONNECTION": "teste"
      }
    }
  }
}
```

3. Open the project in VS Code — the MCP panel will detect the server automatically.

## Configuring Connections

Connections are defined entirely via environment variables — no code changes required.

### 1. Declare connection names

```
ORACLE_CONNECTIONS=teste,producao,SUP
```

Comma-separated list of names. If omitted, the server falls back to the built-in defaults (`teste` and `producao`).

### 2. Define credentials for each connection

For each name `{NAME}` declared above, set:

| Variable | Required | Description |
|---|---|---|
| `ORACLE_{NAME}_USER` | yes | Oracle username |
| `ORACLE_{NAME}_PASSWORD` | yes | Oracle password |
| `ORACLE_{NAME}_DSN` | yes | `host:port/service_name` |
| `ORACLE_{NAME}_SCHEMA` | no | Schema prefix for `ALTER SESSION SET CURRENT_SCHEMA` (default: `{NAME}` uppercased) |
| `ORACLE_{NAME}_DESCRIPTION` | no | Human-readable description shown in `list_schemas` |
| `ORACLE_{NAME}_READONLY` | no | Set `"true"` to block DML/DDL on this connection |

### 3. Set the default connection (optional)

```
ORACLE_DEFAULT_CONNECTION=SUP
```

If omitted, the first connection in `ORACLE_CONNECTIONS` is used.

### Full example (`mcp.json` env block)

```json
"env": {
  "ORACLE_CONNECTIONS": "teste,SUP",
  "ORACLE_TESTE_USER": "user_teste",
  "ORACLE_TESTE_PASSWORD": "pass_teste",
  "ORACLE_TESTE_DSN": "host:1521/ORCL",
  "ORACLE_TESTE_SCHEMA": "TESTE",
  "ORACLE_TESTE_DESCRIPTION": "Base de teste",
  "ORACLE_SUP_USER": "user_sup",
  "ORACLE_SUP_PASSWORD": "pass_sup",
  "ORACLE_SUP_DSN": "host:1521/SUPORTE",
  "ORACLE_SUP_SCHEMA": "SUPORTE",
  "ORACLE_SUP_DESCRIPTION": "Base de suporte",
  "ORACLE_SUP_READONLY": "true",
  "ORACLE_DEFAULT_CONNECTION": "teste"
}
```

## Available MCP Tools

### `list_schemas`

Lists all available Oracle connections. No parameters. Returns CSV.

**Columns:** `name`, `schema`, `description`, `readonly`, `is_default`

**Example response:**
```
name,schema,description,readonly,is_default
teste,TESTE,Base de teste,False,True
producao,SANKHYA,Base de produção,True,False
```

### `execute_query`

Executes a **read-only** SQL query (SELECT, CTEs, EXPLAIN PLAN). DML/DDL is rejected.

| Parameter | Type | Default | Description |
|---|---|---|---|
| `query` | `str` | required | SQL statement (SELECT / WITH / EXPLAIN PLAN) |
| `connection` | `str` | `null` | Connection name from `list_schemas`, or `null` for default |
| `params` | `dict` | `null` | Bind parameters (`:name` style) |
| `max_rows` | `int` | `500` | Maximum rows returned |
| `offset` | `int` | `0` | Rows to skip before returning results |

Returns CSV (with header row) for SELECT queries. Returns `"Erro[...]: ..."` on failure.

### `execute_unsafe_query`

Executes any SQL including INSERT, UPDATE, DELETE, DDL, and PL/SQL blocks. Blocked on connections where `readonly=True`.

Same parameters as `execute_query`. Requires explicit user confirmation before use.

Returns `"Sucesso: Comando executado (sem retorno)."` for DML/DDL with no result set, or CSV if the statement returns rows.

## Example Queries

Always prefix table names with the connection's schema (or rely on `ALTER SESSION SET CURRENT_SCHEMA`):

```sql
-- List recent sales orders
SELECT NUNOTA, DTNEG, CODPARC, VLRNOTA
FROM TGFCAB
WHERE DTNEG >= SYSDATE - 30
ORDER BY DTNEG DESC

-- Check a partner with bind param
SELECT CODPARC, NOMEPARC, CGC_CPF
FROM TGFPAR
WHERE CODPARC = :codparc
```

Using the MCP tools:

```
list_schemas                                        → discover connections
execute_query("SELECT ...", "teste")                → read from test DB
execute_query("SELECT ...", "teste", offset=500)    → paginate results
execute_unsafe_query("INSERT ...", "teste")         → write to test DB
```

## Project Structure

```
mcp_server.py       # FastMCP entry point
src/
  oracle/
    config.py       # ConnectionConfig dataclass + env loading
    query.py        # execute_query() + _is_safe_query() guard
    schemas.py      # list_schemas_info() + list_schemas_csv()
    tools.py        # register_tools(mcp) — wires MCP tools
    __init__.py     # re-exports
tests/
  test_oracle.py    # pytest unit tests
```

## Notes

- `Referencias/` contains reference implementations used during development. It is git-ignored and not part of this project.
- DML/DDL should only target writable connections (`readonly=false`).
- For large result sets, use `offset` + `max_rows` to paginate.