101 lines
6.6 KiB
Markdown
101 lines
6.6 KiB
Markdown
# NTP API
|
|
|
|
.NET 10 Web API over an **existing Oracle 11g** database, built to host the rewritten backend logic.
|
|
|
|
## Why this stack
|
|
|
|
| Choice | Reason |
|
|
|---|---|
|
|
| `Oracle.ManagedDataAccess.Core` **3.21.x** (ODP.NET 21c) | Last ODP.NET line that supports DB **11.2.0.4**. The 23.x line needs 19c+. Pinned in `Directory.Packages.props` — only bump within 3.21.x. |
|
|
| **Dapper** (not EF Core) | Current EF Core Oracle providers generate 12c+ SQL (OFFSET/FETCH, identity). Dapper keeps SQL explicit and 11g-safe, and fits a database-first legacy schema. |
|
|
| Clean layers (Api → Application → Infrastructure → Domain) | Business logic is ported into `Application` services, independent of Oracle. When the DB is upgraded, only `Infrastructure` changes. |
|
|
|
|
## Layout
|
|
|
|
```
|
|
src/
|
|
NTP.Api/ Controllers (thin), ProblemDetails + ORA-code mapping, versioning, OpenAPI, health
|
|
NTP.Application/ Services (business logic), DTOs, FluentValidation, repository interfaces, paging
|
|
NTP.Domain/ Entities mapped to existing tables
|
|
NTP.Infrastructure/ ODP.NET + Dapper: DbSession (connection/transaction per request), repositories, health check
|
|
db/sample-schema.sql Scratch table for the Shipment example only
|
|
```
|
|
|
|
## Run it
|
|
|
|
```bash
|
|
# Requires .NET 10 SDK and network access to the Oracle server
|
|
cd src/NTP.Api
|
|
cp appsettings.Local.example.json appsettings.Local.json # then edit it
|
|
dotnet run
|
|
```
|
|
|
|
- Swagger UI: `https://localhost:7180/swagger` (Development only)
|
|
- OpenAPI JSON: `/openapi/v1.json`
|
|
- Health: `/health/live`, `/health/ready` (runs `SELECT 1 FROM DUAL`)
|
|
- The app fails fast at startup if no connection is configured, and logs the target DB (password masked).
|
|
|
|
> Environments (Development / QA / Production), the settings each server must provide and the web build modes are described in [docs/ENVIRONMENTS.md](docs/ENVIRONMENTS.md).
|
|
|
|
## Configuring the database connection
|
|
|
|
Configure them in `src/NTP.Api/appsettings*.json` (secrets only in git-ignored files or env vars).
|
|
|
|
The API uses **two** Oracle connections (the two legacy ColdFusion datasources):
|
|
|
|
| Key | Schema / legacy datasource | Used by |
|
|
|---|---|---|
|
|
| `ConnectionStrings:NAFITHTCS` (primary) | `NAFITHTCS` / `nafithtcs` | `IDbSession`, `OracleRepositoryBase`, `IUnitOfWork`: PERMITS, NTP_TRUCKS, EVENTS, GRAINS*, UTT, LOCATIONS, STATUS ... |
|
|
| `ConnectionStrings:NtpSchema` (required) | `NTP` / `NTP` | `INtpDbSession`, `NtpRepositoryBase`, `INtpUnitOfWork`: NTPUSERS, ROLES, LOGS, COMPANIES, NATIONALITY ... |
|
|
|
|
Example (placeholders only; real passwords go in git-ignored `appsettings.Local.json` or env vars `ConnectionStrings__NAFITHTCS` / `ConnectionStrings__NtpSchema`):
|
|
`"NAFITHTCS": "User Id=NAFITHTCS;Password=<password>;Data Source=<host>:1521/<service>"`, `"NtpSchema": "User Id=NTP;Password=<password>;Data Source=<host>:1521/<service>"`.
|
|
|
|
The primary connection is resolved in this order (first match wins):
|
|
|
|
| # | Where | Example |
|
|
|---|---|---|
|
|
| 1 | `ConnectionStrings:NAFITHTCS` (then legacy `ConnectionStrings:NTP`) | `"NAFITHTCS": "User Id=NAFITHTCS;Password=<password>;Data Source=db-host:1521/ORCL"` |
|
|
| 2 | `Oracle:ConnectionString` | same format, kept with the other Oracle settings |
|
|
| 3 | `Oracle:Host`, `Port`, `ServiceName` (or `Sid`), `UserId`, `Password` | built for you, plus pool settings (`MinPoolSize`, `MaxPoolSize`, `ConnectionTimeoutSeconds`, `StatementCacheSize`) |
|
|
|
|
Files are layered, later overriding earlier:
|
|
`appsettings.json` → `appsettings.{Environment}.json` → user-secrets (Development) → environment variables → `appsettings.Local.json` (git-ignored).
|
|
|
|
- **Local dev:** copy `appsettings.Local.example.json` to `appsettings.Local.json` and put your password there.
|
|
- **Server / IIS / Docker:** keep the non-secret parts in `appsettings.Production.json`; supply the password with an env var, e.g. `Oracle__Password=...` or the whole string with `ConnectionStrings__NAFITHTCS=...` plus `ConnectionStrings__NtpSchema=...`.
|
|
- **SID listeners:** set `Sid` instead of `ServiceName`, or use `Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=db-host)(PORT=1521))(CONNECT_DATA=(SID=ORCL)))`.
|
|
- Never commit real passwords to `appsettings.json`.
|
|
|
|
## Migrating a table / module (recipe)
|
|
|
|
1. **Entity** — `Domain/Entities/<Name>.cs`, one property per column (`ORDER_DATE` → `OrderDate`).
|
|
2. **Repository interface** — `Application/Abstractions/I<Name>Repository.cs`.
|
|
3. **Repository** — copy `ShipmentRepository.cs`; explicit column lists, `:Param` binds, `OracleSql.Paginate` for lists, `<SEQ>.NEXTVAL ... RETURNING ... INTO` for inserts.
|
|
4. **Service** — port the old business logic into `Application/<Module>/<Name>Service.cs`; wrap multi-statement writes in `unitOfWork.ExecuteInTransactionAsync`.
|
|
5. **Validators + DTOs** — keep max lengths in sync with `VARCHAR2` sizes.
|
|
6. **Controller** — `Controllers/V1/<Name>Controller.cs`, no logic.
|
|
7. **Register** — one line each in `Application/DependencyInjection.cs` and `Infrastructure/DependencyInjection.cs`.
|
|
|
|
Existing PL/SQL packages can be called from a repository with
|
|
`CommandType.StoredProcedure`; ref-cursor outputs need `OracleDynamicParameters` (ask for the helper when needed).
|
|
|
|
## Oracle 11g rules baked in
|
|
|
|
- **Paging**: `ROWNUM` wrapper, not `OFFSET/FETCH`. Always order by a unique tie-breaker (PK).
|
|
- **IDs**: sequences (or the legacy trigger), never identity columns.
|
|
- **Booleans**: `NUMBER(1)`; write with `OracleSql.ToNumber(bool)` — ODP.NET 21 cannot bind `bool`.
|
|
- **One result set per command**: list endpoints do COUNT + page as two queries.
|
|
- **Empty string = NULL** in Oracle; don't rely on `''` vs `NULL` distinctions from the old code.
|
|
- **VARCHAR2 lengths are bytes** by default (`NLS_LENGTH_SEMANTICS=BYTE`) — Arabic text uses 2 bytes/char in AL32UTF8.
|
|
- **Dates**: `SYSDATE` is DB-server local time; the API returns `DateTime` with `Kind=Unspecified`.
|
|
- **Optimistic concurrency** via `ROW_VERSION`; drop those clauses if a legacy table has no such column.
|
|
- **Errors**: ORA codes map to HTTP (ORA-00001 → 409, ORA-02292 → 409, ORA-12899 → 400, network ORA-12541/12514/03113 → 503, …) in `GlobalExceptionHandler`.
|
|
|
|
## Next steps to align with NafithServices
|
|
|
|
- JWT auth (same issuer/audience as the gateway), then add `[Authorize]`.
|
|
- OpenTelemetry tracing/metrics (add `OpenTelemetry.Instrumentation.AspNetCore`; ODP.NET 21 has no OTel instrumentation, so wrap repository calls in an `ActivitySource` if DB spans are required).
|
|
- Ocelot route for `/api/v1/...` and Dockerfile.
|
|
- Integration tests against a disposable Oracle XE container (note: XE 11g images are no longer distributed — use a shared test schema on the 11g server instead).
|