geutebruck/geutebruck-api/GEVISOFT_LIVE_DATA_INTEGRATION.md

# GeViSoft Live Data Integration

## The Problem

**What you want**: Read and edit action mappings from the LIVE GeViSoft instance running on localhost

**Current situation**:
- Our API uses a separate PostgreSQL database
- GeViSoft stores its configuration in `C:\GEVISOFT\DATABASE\GeViDB.mdb` (Access database)
- The GeViSoft SDK doesn't provide direct CRUD APIs for action mappings

## The Solution

We need to access GeViDB.mdb directly to read and write action mappings.

---

## Step 1: Install Microsoft Access Database Engine

### Why?
GeViDB.mdb is a Microsoft Access database. To read/write it from .NET and Python, we need the Access Database Engine (ACE).

### Download & Install

**Option A - 64-bit** (if your apps are 64-bit):
https://www.microsoft.com/en-us/download/details.aspx?id=54920

**Option B - 32-bit** (if GeViSoft is 32-bit):
https://www.microsoft.com/en-us/download/details.aspx?id=13255

**Installation:**
```
AccessDatabaseEngine_X64.exe /quiet
```

Or just run the installer normally.

---

## Step 2: Explore GeViDB.mdb Structure

Once installed, run:
```powershell
.\explore-gevidb.ps1
```

This will show:
- All tables in GeViDB.mdb
- Tables related to alarms/actions/mappings
- Record counts

### Expected Tables

Based on GeViSoft documentation, look for tables like:
- `Alarms` - Alarm definitions
- `AlarmActions` - Actions triggered by alarms
- `Events` - Event definitions
- `ActionRules` or similar - Action mapping rules

---

## Step 3: Architecture Options

### Option A: Direct Database Access (Recommended)

**Pros**:
- Read LIVE data from GeViSoft
- Edit GeViSoft configuration directly
- Changes visible immediately in GeViSet

**Cons**:
- Need to understand GeViDB.mdb schema
- Must handle GeViSoft's database locking
- No SDK validation

**Implementation**:
1. Create C# service in SDK Bridge to read/write GeViDB.mdb
2. Expose gRPC methods for CRUD operations
3. Update Python API to call SDK Bridge instead of PostgreSQL

### Option B: Hybrid Approach

**Pros**:
- Cache GeViSoft data in our PostgreSQL for fast reads
- Sync changes back to GeViDB.mdb
- Audit trail in our database

**Cons**:
- Complex synchronization logic
- Potential for data inconsistency
- More code to maintain

**Implementation**:
1. Periodic sync from GeViDB.mdb to PostgreSQL
2. Write-through: save to both databases
3. Conflict resolution strategy

### Option C: SDK-Only Approach

**Pros**:
- Use official SDK (safer)
- No direct database access

**Cons**:
- SDK doesn't support action mapping CRUD
- Would need to reverse-engineer GeViSet
- Very limited

---

## Step 4: Understanding GeViDB.mdb Schema

### Common Tables (from documentation)

**Alarms Table**:
- Stores alarm definitions
- Fields: ID, Name, Description, InputActions, OutputActions, etc.

**Example Query**:
```sql
SELECT * FROM Alarms WHERE AlarmType = 'ActionMapping'
```

**Finding Action Mappings**:
Action mappings in GeViSoft are typically stored as **Alarms** with specific types. An alarm can:
- Be triggered by an action (Input Action)
- Execute actions when triggered (Output Actions)

---

## Step 5: Implementation Plan

### Phase 1: Database Access Layer (C# in SDK Bridge)

Create `GeViDBAccessor.cs`:
```csharp
public class GeViDBAccessor
{
    private readonly string _connectionString;

    public GeViDBAccessor()
    {
        _connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;" +
                           @"Data Source=C:\GEVISOFT\DATABASE\GeViDB.mdb;" +
                           "Persist Security Info=False;";
    }

    public List<ActionMapping> GetAllActionMappings()
    {
        // Query Alarms table for action-mapping type alarms
        using var conn = new OleDbConnection(_connectionString);
        conn.Open();

        var cmd = new OleDbCommand("SELECT * FROM Alarms WHERE Type = ?", conn);
        cmd.Parameters.AddWithValue("?", "ActionMapping"); // Or whatever the type is

        // Parse results into ActionMapping objects
        // ...
    }

    public void CreateActionMapping(ActionMapping mapping)
    {
        // INSERT into Alarms table
        // ...
    }

    public void UpdateActionMapping(string id, ActionMapping mapping)
    {
        // UPDATE Alarms table
        // ...
    }

    public void DeleteActionMapping(string id)
    {
        // DELETE from Alarms table
        // ...
    }
}
```

### Phase 2: Update gRPC Services

Modify `ActionMappingService.cs` to use `GeViDBAccessor` instead of in-memory storage.

### Phase 3: Test Integration

1. Read existing alarms from GeViSet
2. Verify they appear in API
3. Create new mapping via API
4. Verify it appears in GeViSet
5. Edit via API
6. Verify changes in GeViSet

---

## Step 6: Database Locking Considerations

**Issue**: GeViServer may lock GeViDB.mdb while running

**Solutions**:
1. **Read-only mode**: Open database in read-only mode for queries
2. **Stop GeViServer**: Temporarily stop for writes (not ideal)
3. **Database copy**: Copy .mdb, read from copy (data may be stale)
4. **Use SDK commands**: Send actions via SDK to trigger GeViServer to modify its own database

**Recommended**:
- Read directly from GeViDB.mdb (usually works even when locked)
- For writes, send SDK actions to GeViServer instead of direct DB writes
- Let GeViServer update its own database

---

## Quick Test Script

After installing Access Database Engine, test connection:

```powershell
$conn = New-Object -ComObject ADODB.Connection
$conn.Open("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\GEVISOFT\DATABASE\GeViDB.mdb")

$rs = $conn.Execute("SELECT * FROM Alarms")
while (-not $rs.EOF) {
    Write-Host $rs.Fields.Item("Name").Value
    $rs.MoveNext()
}

$rs.Close()
$conn.Close()
```

---

## Alternative: GeViSet XML Export

GeViSet might support XML export of configuration. Check:

```
C:\GEVISOFT\GeViSet.exe /?
```

If it supports command-line export, you could:
1. Export configuration to XML
2. Parse XML to extract action mappings
3. Generate XML for new mappings
4. Import back into GeViSet

---

## Next Steps

**I can implement this once you:**

1. **Install Access Database Engine** (link above)
2. **Run `explore-gevidb.ps1`** to see the table structure
3. **Tell me which tables contain action mappings** (likely `Alarms`)

Then I'll:
1. Create GeViDBAccessor in C# SDK Bridge
2. Update gRPC services to use it
3. Update Python API to call SDK Bridge
4. Test full integration with live GeViSoft data

**Result**: Your API will read and edit LIVE action mappings from GeViSoft!

---

## Summary

| Current | Target |
|---------|--------|
| API reads from PostgreSQL | API reads from GeViDB.mdb |
| No GeViSoft data | Shows LIVE GeViSoft mappings |
| Changes not in GeViSet | Changes sync to GeViSet |
| Separate system | Integrated with GeViSoft |

**Action Required**: Install Access Database Engine, then I can implement the integration.