# PL/SQL Test MCP

MCP server for IFS Cloud PL/SQL unit testing — mirrors the architecture of [integration-testing-mcp](https://github.com/dilinaweerasinghe/integration-testing-mcp) and delegates deploy/run steps to **ifs-f1-codegen-dev**.

Develop MCP to identify if a function/procedure is unit testable and generate the test.

## Workflow

```mermaid
flowchart TD
    A[checkUnitTestability] --> B{Testable?}
    B -->|No| C[annotateIgnoreUnitTest]
    B -->|Yes| D[generateUnitTest]
    D --> E[generate_code / generate_and_deploy via ifs-f1-codegen-dev]
    E --> F[Verify test passes]
    C --> E
```

## Tools

| Tool | Description |
|------|-------------|
| `checkUnitTestability` | Parse `.plsql`/`.plsvc` and assess pltst testability |
| `annotateIgnoreUnitTest` | Add `@IgnoreUnitTest <reason>` before a method |
| `generateUnitTest` | Analyze method body, generate deploy-safe mocks, data-driven FOR loops, and assertions |
| `processUnitTestCoverage` | **Batch:** annotate all non-testable methods + generate all missing auto-safe tests for one file |
| `runUnitTest` | Return `generate_and_deploy` instructions for ifs-f1-codegen-dev |
| `runPlsqlTestWorkflow` | End-to-end: check → annotate OR generate → run prep |
| `getIgnoreUnitTestRules` | List supported ignore reasons |

## Supported @IgnoreUnitTest reasons

| Reason | When to use |
|--------|-------------|
| `TrivialFunction` | Simple getter/pass-through |
| `MethodOverride` | `@Override` CRUD/framework methods |
| `DMLOperation` | INSERT/UPDATE/DELETE/MERGE in body |
| `NoOutParams` | Procedure with no OUT/IN OUT params |
| `DynamicStatement` | EXECUTE IMMEDIATE / dynamic SQL |
| `BLOBDataType` / `CLOBDataType` | Large object types |
| `PLSQLInSQL` | PL/SQL embedded in SQL views |
| `PipelinedFunction` | Pipelined table functions |

## Cursor integration

The server is published to npm as [`@vaallk/plsql-test-mcp`](https://www.npmjs.com/package/@vaallk/plsql-test-mcp). Add this to your Cursor MCP settings — no local clone or build required:

```json
{
  "mcpServers": {
    "plsql-test": {
      "command": "npx",
      "args": ["-y", "@vaallk/plsql-test-mcp@0.1.12"],
      "env": {
        "IFS_WORKSPACE": "C:\\path\\to\\your\\workspace"
      }
    }
  }
}
```

`IFS_WORKSPACE` is machine-specific — point it at your own IFS workspace root. Use `@latest` instead of a pinned version to always pick up the newest release on restart.

### Local development config

To run against a local build instead of the published package:

```json
{
  "mcpServers": {
    "plsql-test": {
      "command": "node",
      "args": ["C:/ifsapps-new/plsql-test-mcp/dist/index.js"],
      "env": {
        "IFS_WORKSPACE": "C:\\path\\to\\your\\workspace"
      }
    }
  }
}
```

## Development

```bash
cd c:/ifsapps-new/plsql-test-mcp
npm install
npm run build
npm start
```

## Deploy-safe generation (v0.1.4)

The generator avoids patterns that break `AV_*_TST` compilation:

- **No UTF-8 BOM** on written `.pltst` files
- **`VARCHAR2(2000)`** for all generated IS-section locals (bare `VARCHAR2` causes PLS-00215)
- **Pre-write validation** rejects bare VARCHAR2, BOM, and post-loop generated tests
- **Skips** `%ROWTYPE` return types and `SELECT * INTO %ROWTYPE` (manual test required)
- **Skips** CRUD modify methods using `Get_Object_By_Id___` + `Unpack___`
- **Expression SELECT columns** (`round(cast(...))`) mapped to safe mock column names
- **FOR loop column order** matches IFS convention: `expected_ | input_param_`
- **Not-found cases** stay inside the FOR loop (no post-loop statements)
- **Skips `@IgnoreUnitTest`** when the method already has a UNITTEST block
- **Special mocks** for `Get_Wp_Id`, `Has_Skills_Assigned_In_Turn`, `Get_Fault_Id_From_Record_Id`, `Get_AOS_Days`, `Get_Hm_Contract_Id_By_Barcode`

### Derived value mapping (v0.1.12)

Getters that fetch a value but return a **derived** label/status via a post-fetch `IF/ELSE` are detected so the test asserts the mapped result, not the raw fetched value:

- **Column null-check** — `SELECT col` then `IF col IS NULL THEN 'A' ELSE 'B'` (e.g. `Get_Measurment_Status` → `'Pending'`/`'Signed'`). The mock uses one non-null row and one `NULL` row.
- **Count positive** — `SELECT COUNT(*)` then `IF cnt > 0 THEN 'A' ELSE 'B'` (e.g. `Is_Part_Warnings_Exist` → `'TRUE'`/`'FALSE'`). The mock provides matching rows for the "present" key and a missing key for the "absent" case.

Both direct-`RETURN` and result-variable assignment styles are supported; `ELSIF` multi-branch mappings are left for manual tests.

### Batch coverage example

```
processUnitTestCoverage({
  sourceFile: "C:/ifsapps-new/workspace/adcom/source/adcom/database/AvFault.plsql",
  workspace: "C:/ifsapps-new/workspace",
  jiraKey: "PJZ-12345",
  confirmed: true
})
```

## Example

Analyze `McprActivityRelation.plsql`:

```
checkUnitTestability({
  sourceFile: "C:/ifsapps-new/workspace/prjrep/source/prjrep/database/McprActivityRelation.plsql",
  methodName: "Is_Circular_Link___"
})
```

Generate test:

```
generateUnitTest({
  sourceFile: ".../McprActivityRelation.plsql",
  methodName: "Is_Circular_Link___",
  workspace: "C:/ifsapps-new/workspace",
  jiraKey: "PJZ-12345",
  confirmed: true
})
```

Run via ifs-f1-codegen-dev:

```
generate_and_deploy({
  input_files: [".../McprActivityRelation.plsql", ".../McprActivityRelation.pltst"],
  environment_key: "26r1-dev-lkp",
  workspace: "C:/ifsapps-new/workspace",
  confirmed: true
})
```

## Note on plvst

IFS uses **`.pltst`** files for both `.plsql` and `.plsvc` unit tests. No separate `.plvst` extension exists in the codebase — this MCP targets `.pltst`.

## Related

- [integration-testing-mcp](https://github.com/dilinaweerasinghe/integration-testing-mcp) — reference MCP architecture
- **ifs-f1-codegen-dev** — code generation, SQLcl deploy, live DB inspect