# CLAUDE.md

This file provides guidance to Claude Code (claude.ai/code) when working with code in this repository.

## Purpose

The goal of this project is to identify and develop SQL queries that extract the Galaxy object hierarchy from the **System Platform Galaxy Repository** database in order to build a tag structure for an OPC UA server.

Specifically, we need to:
- Build the hierarchy of **areas** and **automation objects** (using contained names for human-readable browsing)
- Translate contained names to **tag_names** for read/write operations (e.g., `TestMachine_001.DelmiaReceiver` in the hierarchy becomes `DelmiaReceiver_001` when addressing tag values)

See `layout.md` for details on the hierarchy vs tag name relationship.

## Key Files

### Documentation
- `connectioninfo.md` — Database connection details and sqlcmd usage
- `layout.md` — Galaxy object hierarchy, contained_name vs tag_name translation, and target OPC UA structure
- `build_layout_plan.md` — Step-by-step plan for extracting hierarchy, attaching attributes, and monitoring for changes
- `data_type_mapping.md` — Galaxy mx_data_type to OPC UA DataType mapping, including array handling (ValueRank, ArrayDimensions)

### Queries
- `queries/hierarchy.sql` — Deployed object hierarchy with browse names and parent relationships
- `queries/attributes.sql` — User-defined (dynamic) 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` to detect deployment changes

### Schema Reference
- `schema.md` — Full schema reference for all tables and views in the ZB database
- `ddl/tables/` — Individual CREATE TABLE definitions
- `ddl/views/` — Individual view definitions

## Working with the Galaxy Repository Database

The Galaxy Repository is the backing SQL Server database for Wonderware/AVEVA System Platform (Galaxy: ZB, localhost, Windows Auth). Key tables used by the queries:

- **gobject** — Object instances, hierarchy (contained_by_gobject_id, area_gobject_id), deployment state (deployed_package_id)
- **template_definition** — Object type categories (category_id distinguishes areas, engines, user-defined objects, etc.)
- **dynamic_attribute** — User-defined attributes on templates, inherited by instances via derived_from_gobject_id chain
- **attribute_definition** — System/primitive attributes
- **primitive_instance** — Links objects to their primitive components and attribute definitions
- **galaxy** — Single-row table with time_of_last_deploy for change detection

Use `sqlcmd -S localhost -d ZB -E -Q "..."` to run queries. See `connectioninfo.md` for details.

## Conventions

- Store all connection parameters in `connectioninfo.md`, not scattered across scripts.
- Keep SQL query examples and extraction notes as Markdown files in this repo.
- If scripts are added (Python, PowerShell, etc.), document their usage and dependencies alongside them.