# OPC UA Server Layout — Build Plan

## Overview

Extract the Galaxy object hierarchy and tag definitions from the ZB (Galaxy Repository) database to construct an OPC UA server address space. The root node is hardcoded as **ZB**.

## Step 1: Build the Browse Tree

Run `queries/hierarchy.sql` to get all deployed automation objects and their parent-child relationships.

For each row returned:
- `parent_gobject_id = 0` → child of the root ZB node
- `is_area = 1` → create as an OPC UA folder node (organizational)
- `is_area = 0` → create as an OPC UA object node (container for tags)
- Use `browse_name` as the OPC UA BrowseName/DisplayName
- Store `gobject_id` and `tag_name` for attribute lookup and tag reference translation

Build the tree by matching each row's `parent_gobject_id` to another row's `gobject_id`. The result is:

```
ZB (root, hardcoded)
└── DEV (folder, is_area=1)
    ├── DevAppEngine (object)
    ├── DevPlatform (object)
    └── TestArea (folder, is_area=1)
        ├── DevTestObject (object)
        └── TestMachine_001 (object)
            ├── DelmiaReceiver (object, browse_name from contained_name)
            └── MESReceiver (object, browse_name from contained_name)
```

## Step 2: Attach Attributes as Tag Nodes

Run `queries/attributes.sql` to get all user-defined attributes for deployed objects.

For each attribute row:
- Match to the browse tree via `gobject_id`
- Create an OPC UA variable node under the matching object node
- Use `attribute_name` as the BrowseName/DisplayName
- Use `full_tag_reference` as the runtime tag path for read/write operations
- Map `mx_data_type` to OPC UA built-in types:

| mx_data_type | Description | OPC UA Type |
|--------------|-------------|-------------|
| 1 | Boolean | Boolean |
| 2 | Integer | Int32 |
| 3 | Float | Float |
| 4 | Double | Double |
| 5 | String | String |
| 6 | Time | DateTime |
| 7 | ElapsedTime | Double (seconds) or Duration |

- If `is_array = 1`, create the variable as an array with rank 1 and dimension from `array_dimension`

## Step 3: Monitor for Changes

Poll `queries/change_detection.sql` on a regular interval (e.g., every 30 seconds).

```
SELECT time_of_last_deploy FROM galaxy;
```

Compare the returned `time_of_last_deploy` to the last known value:
- **No change** → do nothing
- **Changed** → a deployment occurred; re-run Steps 1 and 2 to rebuild the address space

This handles objects being deployed, undeployed, added, or removed.

## Connection Details

See `connectioninfo.md` for database connection parameters and sqlcmd usage.

```
sqlcmd -S localhost -d ZB -E -Q "YOUR QUERY HERE"
```

## Query Files

| File | Purpose |
|------|---------|
| `queries/hierarchy.sql` | Deployed object hierarchy with browse names and parent relationships |
| `queries/attributes.sql` | User-defined attributes with data types and array dimensions |
| `queries/attributes_extended.sql` | All attributes (system + user-defined) with data types and array dimensions |
| `queries/change_detection.sql` | Poll galaxy.time_of_last_deploy for deployment changes |