# grdb

SQL exploration of the AVEVA / Wonderware System Platform **Galaxy Repository** database, used to extract the Galaxy area / object hierarchy and translate contained-names ↔ tag-names for an OPC UA server.

## Hard constraints

- Requires SQL Server access to the Galaxy Repository DB (default: `localhost`, database `ZB`, Windows Auth). See [`connectioninfo.md`](connectioninfo.md) for the `sqlcmd` invocation.
- **Development verification / debugging only.** Production read/write paths must go through GRAccess COM via [`../graccesscli/`](../graccesscli/README.md), not direct SQL against this DB.
- The `contained_name` ↔ `tag_name` distinction is critical and asymmetric:
  - Hierarchy browsing uses `contained_name` (e.g. `TestMachine_001.DelmiaReceiver`).
  - Runtime read/write uses the system-assigned `tag_name` (e.g. `DelmiaReceiver_001`).
  - Translation is mandatory when going from a hierarchy path to a tag reference. See [`layout.md`](layout.md).

## Layout

```text
grdb/
  CLAUDE.md             # agent guide: purpose, key tables, conventions
  connectioninfo.md     # DB connection details and sqlcmd usage
  schema.md             # full schema reference for ZB tables and views
  data_type_mapping.md  # mx_data_type → OPC UA DataType, ValueRank, ArrayDimensions
  layout.md             # hierarchy model, contained_name vs tag_name, target OPC UA shape
  build_layout_plan.md  # step-by-step plan: hierarchy → attributes → change detection
  queries/              # reusable SQL (hierarchy, attributes, change detection)
  ddl/                  # captured CREATE TABLE / view definitions (tables/, views/)
  parse_tables.py       # Python helper for parsing captured table definitions
  hierarchy.jpeg        # visual reference: Galaxy area/object hierarchy
  tags.JPG              # visual reference: tag-name view
```

## Resource index

| Task | Go to |
| --- | --- |
| Agent guide: purpose, key tables, conventions | [`CLAUDE.md`](CLAUDE.md) |
| Connect to the Galaxy DB / `sqlcmd` usage | [`connectioninfo.md`](connectioninfo.md) |
| Full table / view schema reference | [`schema.md`](schema.md) |
| `mx_data_type` → OPC UA DataType, array handling | [`data_type_mapping.md`](data_type_mapping.md) |
| Hierarchy model + contained-name ↔ tag-name translation | [`layout.md`](layout.md) |
| Step-by-step extraction plan (hierarchy → attributes → polling) | [`build_layout_plan.md`](build_layout_plan.md) |
| Deployed object hierarchy with browse names and parents | [`queries/hierarchy.sql`](queries/hierarchy.sql) |
| User-defined (dynamic) attributes with types / array dims | [`queries/attributes.sql`](queries/attributes.sql) |
| All attributes (system + user-defined) with types / array dims | [`queries/attributes_extended.sql`](queries/attributes_extended.sql) |
| Detect deployment changes via `galaxy.time_of_last_deploy` | [`queries/change_detection.sql`](queries/change_detection.sql) |
| Captured DDL — tables | [`ddl/tables/`](ddl/tables) |
| Captured DDL — views | [`ddl/views/`](ddl/views) |
| Parse captured table DDL programmatically | [`parse_tables.py`](parse_tables.py) |
| Hierarchy / tag visual references | [`hierarchy.jpeg`](hierarchy.jpeg), [`tags.JPG`](tags.JPG) |

## Maintenance

Documentation rules live in [`../DOCS-GUIDE.md`](../DOCS-GUIDE.md); the root task → tool index lives in [`../CLAUDE.md`](../CLAUDE.md). When adding, renaming, or removing any doc, query, or DDL file in this folder, update the resource index above in the same change.