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:

.\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:

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:

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:

$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.