====== Runbook: Manage DSN ======

<WRAP round info>
**Duration:** ~10 minutes \\
**Role:** Gateway Operator \\
**Prerequisite:** appsettings.json access
</WRAP>

DSN (Data Source Name) defines preconfigured database connections.

----

===== Workflow =====

<mermaid>
flowchart TD
    A[DSN Request] --> B{New or existing?}
    B -->|New| C[Edit appsettings.json]
    B -->|Existing| D[Test connection]
    C --> E[Restart Gateway]
    E --> D
    D --> F{Test successful?}
    F -->|Yes| G[Document]
    F -->|No| H[Check connection string]
    H --> C

    style G fill:#e8f5e9
    style H fill:#ffebee
</mermaid>

----

===== 1. Show Existing DSN =====

<code bash>
# List all configured DSN (API)
curl -s http://localhost:5000/api/v1/dsn | jq

# Or check directly in config
cat appsettings.json | jq '.Gateway.Databases'
</code>

----

===== 2. Add New DSN =====

**Edit appsettings.json:**

<code javascript>
{
  "Gateway": {
    "Databases": {
      "demo": {
        "Provider": "sqlite",
        "ConnectionString": "Data Source=data/demo.db"
      },
      "production": {
        "Provider": "sqlserver",
        "ConnectionString": "Server=sql01;Database=ProdDB;User Id=app;Password=***;TrustServerCertificate=True"
      },
      "reporting": {
        "Provider": "postgresql",
        "ConnectionString": "Host=pg01;Database=reports;Username=reader;Password=***"
      }
    }
  }
}
</code>

**Supported Providers:**

| Provider | ConnectionString Example |
|----------|--------------------------|
| ''sqlserver'' | ''Server=host;Database=db;User Id=user;Password=pwd'' |
| ''sqlite'' | ''Data Source=path/file.db'' |
| ''postgresql'' | ''Host=host;Database=db;Username=user;Password=pwd'' |
| ''mysql'' | ''Server=host;Database=db;User=user;Password=pwd'' |

----

===== 3. Restart Gateway =====

<code bash>
# Windows
Stop-Process -Name "WvdS.WebAPI.Data.Gateway.Api" -Force
Start-Process -FilePath ".\WvdS.WebAPI.Data.Gateway.Api.exe"

# Linux (systemd)
sudo systemctl restart data-gateway
</code>

----

===== 4. Test DSN =====

<code bash>
# List tables of new DSN
curl -s http://localhost:5000/api/v1/dsn/production/tables | jq

# Fetch first data
curl -s "http://localhost:5000/api/v1/dsn/production/tables/Customers?\$top=5" | jq
</code>

----

===== 5. Remove DSN =====

  - Delete DSN entry from ''appsettings.json''
  - Restart Gateway
  - Verify DSN is no longer available:

<code bash>
curl -s http://localhost:5000/api/v1/dsn/old_dsn/tables
# Expected response: 404 Not Found
</code>

----

===== Checklist =====

| # | Check | Done |
|---|-------|------|
| 1 | Provider correct (sqlserver/sqlite/...) | [ ] |
| 2 | Connection string valid | [ ] |
| 3 | Password not visible in log | [ ] |
| 4 | Gateway restarted | [ ] |
| 5 | API test successful | [ ] |

----

===== Troubleshooting =====

| Problem | Cause | Solution |
|---------|-------|----------|
| ''Database not found'' | Wrong database | Check database name |
| ''Login failed'' | Wrong password | Check credentials |
| ''Connection refused'' | Firewall blocking | Open port 1433/5432/3306 |
| ''Provider not supported'' | Wrong provider name | sqlserver, sqlite, postgresql, mysql |
| ''JSON parse error'' | Syntax error | Validate JSON (jsonlint.com) |

----

===== Security Notes =====

<WRAP round important>
**Never log passwords in plain text!**

Use environment variables for credentials:
<code javascript>
"ConnectionString": "Server=sql01;Database=ProdDB;User Id=${DB_USER};Password=${DB_PASS}"
</code>
</WRAP>

----

===== Related Runbooks =====

  * [[.:server-starten|Start Server]] - After config change
  * [[.:health-check|Health Check]] - Check connection
  * [[..:..:administrator:konfiguration:datenbanken|Database Configuration]] - Detailed reference

----

<< [[.:server-starten|<- Start Server]] | [[.:health-check|-> Health Check]] >>

----
//Wolfgang van der Stille @ EMSR DATA d.o.o. - Data Gateway Professional//

{{tag>operator runbook dsn database configuration}}