geutebruck/geutebruck-api/VSS_AND_SDK_FINDINGS.md

Volume Shadow Copy & SDK Database Query Research

Key Discoveries

1. GeViDB.mdb is Proprietary "MDB1" Format

Evidence:

• File header: 4D 44 42 31 00 10 00 00... ("MDB1")
• NOT Microsoft Access format (which would start with "Standard Jet DB" or "Standard ACE DB")
• Cannot be accessed via OLEDB (Microsoft.ACE.OLEDB or Jet providers)

Impact: Direct database file access is IMPOSSIBLE. Must use Geute brück SDK queries.

---

2. Volume Shadow Copy (VSS) Successfully Copies Locked Database

Result: ✅ VSS CAN copy the file while GeViServer is running

Script: tools\Read-LockedDatabase.ps1

Process:

1. Creates Windows shadow copy of C: drive
2. Creates symbolic link to shadow copy
3. Uses .NET FileStream to copy database (64 MB copied successfully)
4. BUT: The copied file is still in MDB1 format (proprietary)

Conclusion: VSS works for copying, but doesn't solve the format problem. We still can't read the proprietary database.

---

3. Correct Approach: SDK Database Queries

Found example: C:\GEVISOFT\Examples\VS2010NET\CS_SimpleDatabaseClient

SDK Query Pattern:

// 1. Create query (returns handle)
GeViMessage dbAnswer;
myDB.SendQuery(new GeViDBQ_CreateAlarmQuery(), out dbAnswer);
GeViDBA_QueryHandle handle = (GeViDBA_QueryHandle)dbAnswer;

// 2. Get first record
myDB.SendQuery(new GeViDBQ_GetFirst(handle.sHandle), out dbAnswer);

// 3. Iterate through results
while (dbAnswer is GeViDBA_AlarmEntry alarmEntry) {
    // Process alarm
    string name = alarmEntry.sAlarmTypeName;
    long pk = alarmEntry.sPK;

    // Get next
    myDB.SendQuery(new GeViDBQ_GetNext(handle.sHandle, pk), out dbAnswer);
}

// 4. Close query
myDB.SendQuery(new GeViDBQ_CloseQuery(handle.sHandle), out _);

For Actions:

// Query all actions (0 = all, or specific alarm GlobalID)
myDB.SendQuery(new GeViDBQ_CreateActionQuery(0), out dbAnswer);
// ... same iteration pattern

---

Implementation Status

✅ Completed

1. C# SDK Database Query Implementation

   • File: SDK/AlarmQueryService.cs
   • Methods:
     • QueryAllAlarmsAsync() - Gets all alarms via SDK
     • QueryAllActionsAsync() - Gets all actions via SDK
     • GetAllActionMappingsAsync() - Matches alarms with actions
   • Compiles successfully ✅
   • Services running ✅

2. VSS Research

   • Script created: tools\Read-LockedDatabase.ps1
   • Tested successfully - can copy locked database
   • Identified proprietary format issue

3. MDB1 Format Investigation

   • Identified as Geutebruck proprietary format
   • Confirmed SDK is the ONLY way to query it

⚠️ Remaining Gap: Python API Not Calling SDK Bridge

Problem: Python API service queries PostgreSQL (test data), NOT the SDK Bridge.

Current Flow:

API Request → Python FastAPI → PostgreSQL (test data) → Response

Needed Flow:

API Request → Python FastAPI → gRPC → C# SDK Bridge → GeViServer SDK → Response

Missing Components:

1. Protobuf definition for ActionMappings service (.proto file)
2. gRPC service implementation in C# SDK Bridge
3. gRPC client call in Python service
4. Stub initialization in sdk_bridge_client.py

---

Files Modified/Created

SDK Bridge (C#):

• ✅ SDK/AlarmQueryService.cs - SDK database queries (COMPLETE)
• ✅ SDK/ActionMappingHandler.cs - Uses AlarmQueryService (COMPLETE)
• ⏸️ Services/ActionMappingGrpcService.cs - NOT YET CREATED

Python API:

• ⏸️ services/action_mapping_service.py - Still using PostgreSQL
• ⏸️ clients/sdk_bridge_client.py - No action mappings stub

Protobuf:

• ⏸️ Protos/action_mapping.proto - NOT YET CREATED

Tools:

• ✅ tools/Read-LockedDatabase.ps1 - VSS script (COMPLETE)
• ✅ tools/Check-DatabaseHeader.ps1 - Format checker (COMPLETE)

---

Next Steps to Complete Integration

Step 1: Create Protobuf Definition

File: src/sdk-bridge/Protos/action_mapping.proto

syntax = "proto3";

package action_mapping;

service ActionMappingService {
  rpc GetActionMappings(GetActionMappingsRequest) returns (GetActionMappingsResponse);
}

message GetActionMappingsRequest {
  bool enabled_only = 1;
}

message ActionMapping {
  string id = 1;
  string name = 2;
  string description = 3;
  string input_action = 4;
  repeated string output_actions = 5;
  bool enabled = 6;
}

message GetActionMappingsResponse {
  repeated ActionMapping mappings = 1;
  int32 total_count = 2;
  int32 enabled_count = 3;
  int32 disabled_count = 4;
}

Step 2: Generate gRPC Code

# Build project (triggers proto code generation)
dotnet build GeViScopeBridge.csproj
python -m grpc_tools.protoc -I. --python_out=. --grpc_python_out=. action_mapping.proto

Step 3: Implement gRPC Service (C#)

File: Services/ActionMappingGrpcService.cs

public class ActionMappingGrpcService : ActionMappingService.ActionMappingServiceBase
{
    private readonly ActionMappingHandler _handler;

    public override async Task<GetActionMappingsResponse> GetActionMappings(
        GetActionMappingsRequest request, ServerCallContext context)
    {
        var mappings = await _handler.EnumerateActionMappingsAsync(request.EnabledOnly);

        return new GetActionMappingsResponse
        {
            Mappings = { mappings.Select(m => new ActionMapping
            {
                Id = m.Id,
                Name = m.Name,
                Description = m.Description,
                InputAction = m.InputAction,
                OutputActions = { m.OutputActions },
                Enabled = m.Enabled
            })},
            TotalCount = mappings.Count,
            EnabledCount = mappings.Count(m => m.Enabled),
            DisabledCount = mappings.Count(m => !m.Enabled)
        };
    }
}

Step 4: Update Python Service

File: services/action_mapping_service.py

async def list_action_mappings(self, enabled_only=False, ...):
    # Call SDK Bridge via gRPC instead of PostgreSQL
    response = await sdk_bridge_client.get_action_mappings(enabled_only=enabled_only)

    return ActionMappingListResponse(
        mappings=[ActionMappingResponse(**m) for m in response.mappings],
        total_count=response.total_count,
        enabled_count=response.enabled_count,
        disabled_count=response.disabled_count
    )

---

Testing the Solution

Once integration is complete:

# 1. Restart services
.\restart-services.ps1

# 2. Test API endpoint
.\test-action-mappings.ps1

Expected Result: Live data from GeViServer database (not test data)

---

Summary

Component	Status	Notes
MDB1 Format Investigation	✅ Complete	Proprietary format, SDK required
VSS Script	✅ Complete	Works but doesn't solve format issue
C# SDK Queries	✅ Complete	AlarmQueryService implemented
gRPC Protobuf	⏸️ Pending	Need to create .proto file
C# gRPC Service	⏸️ Pending	Need to expose SDK queries via gRPC
Python gRPC Client	⏸️ Pending	Need to call SDK Bridge
End-to-End Test	⏸️ Pending	Waiting for gRPC integration

Bottom Line: The SDK database query approach is CORRECT and IMPLEMENTED in C#. We just need to expose it via gRPC so the Python API can call it instead of using PostgreSQL test data.