Profile "" VARCHAR(100) VARCHAR(100) VARCHAR(255) VARCHAR(255) VARCHAR(255) VARCHAR(2048) SMALLINT INTEGER BIGINT BLOB BLOB DOUBLE PRECISION DOUBLE PRECISION DOUBLE PRECISION DATE TIME TIMESTAMP INTEGER DEFAULT 0 CHECK ("" IN (0, 1)) VARCHAR(255) NOT NULL -- ========================================================================================== ANSI-SQL-92 compliant schema for Generated by CIMTool https://cimtool.ucaiug.io Compound types are represented as separate 1:1 tables and enforced via a combination of UNIQUE on columns that reference Compound type tables and FK column constraint definitions to those tables. Though Compounds do not have an "mRID" and do not inherently represent persistent objects in the CIM (i.e. they do not inherit from IdentifiedObject) they are given a surrogate identifier "id" distinct from "mRID" for persistence purposes. Non-IdentifiedObject classes (e.g. CurveData, RegularTimePoint, NonlinearShuntCompensatorPoint, TapChangerTablePoint) also receive a surrogate "id" PRIMARY KEY for the same reason — they are collection-member or point-data classes with no natural single-column PK derivable from the profile catalog XML. A heuristic UNIQUE constraint is additionally emitted across all their required (NOT NULL) non-surrogate columns. See the cimtool:has-mrid-ancestor function documentation for full details. Tables that represent Compound types in the CIM are not normalized for the following reasons: Normalization, particularly to 3NF, typically requires: No repeating groups. No partial or transitive dependencies. Every non-key attribute must depend only on the whole key. But compound classes in the CIM domain: Do not have surrogate or natural primary keys derived from business rules. Are logically value objects: any change to any attribute means the value is distinct. Have implicit semantic atomicity — they're treated as indivisible chunks. Compound Value Object Design Concession CIM Compound types are treated as value objects — each compound row has exactly one owner: the specific parent column on the specific parent row that caused it to be created. Compound rows are NEVER shared between parent columns or between parent rows, even when the attribute content is identical. For example, if an Organisation has both a postalAddress and a streetAddress that happen to hold the same physical address, two distinct StreetAddress rows must still be inserted — each with its own unique UUID surrogate "id" — one owned exclusively by postalAddress and one owned exclusively by streetAddress. This is a deliberate design concession. It forgoes 3rd Normal Form for compound data in exchange for a simple, consistent ownership model where each compound row lives and dies with its single parent column instance. Consumers inserting instance data for compounds MUST always generate a unique UUID for each compound row and use that UUID as the FK value in the parent column. Reuse of a compound UUID across multiple parent columns or parent rows will violate the cascade delete constraints defined below and corrupt the ownership semantics of the schema. Compound Surrogate Key (UUID) Generation Each compound row requires a unique surrogate identifier assigned to its "id" column at insert time. This UUID serves as both the primary key of the compound row and the foreign key value stored in the parent column (e.g. Organisation.postalAddress). Because each compound row has exactly one owner, the UUID must be generated fresh for every compound insert — it must never be reused across parent columns or parent rows. UUID generation is intentionally left to application-level code. ANSI SQL-92 defines no standard mechanism for generating UUIDs or other unique identifiers as column defaults, and all available approaches are vendor-specific (e.g. gen_random_uuid() in PostgreSQL, NEWID() in SQL Server, UUID() in MySQL). Embedding any of these would break the cross-RDBMS portability that this generated schema is designed to preserve. Application code should generate UUIDs using the standard library appropriate to the implementation language before issuing any insert statements, for example: Java: java.util.UUID.randomUUID().toString() C#: System.Guid.NewGuid().ToString() Python: str(uuid.uuid4()) Node.js: crypto.randomUUID() The UUID should be assigned to the compound row's "id" column and simultaneously written into the parent row's compound FK column within the same transaction, ensuring consistency. Note that a custom backend-specific SQL builder could be implemented alongside this one that targets a particular RDBMS and includes native UUID generation as a column default (e.g. DEFAULT gen_random_uuid()). Such a builder would sacrifice portability in exchange for removing the UUID generation burden from application code entirely. If utilizing the jpa-rdfs or csharp-ef-rdfs CIMTool builders, this concern is addressed directly in the code they generate. UUID assignment for compound surrogate keys is handled automatically as a cross-cutting concern within those builders, and no additional application-level UUID generation code is required for compound inserts when using those generated artifacts. -- ========================================================================================== -- ------------------------------------------------------------------------------ -- Inheritance constraint definitions ------------------------------------------------------------------------------ ------------------------------------------------------------------------------ -- Standard foreign key constraint definitions ------------------------------------------------------------------------------ ------------------------------------------------------------------------------ -- Heuristic uniqueness constraints for non-IdentifiedObject classes ------------------------------------------------------------------------------ The following UNIQUE constraints are emitted for CIM classes that do not inherit from IdentifiedObject and therefore carry a surrogate "id" PK rather than a natural "mRID" PK. Because the profile catalog XML does not encode which subset of columns forms the true natural composite key (that information exists only in the IEC specification prose), these constraints span all required (NOT NULL) non-surrogate columns as a heuristic guard. The constraint may be wider than the true natural key but is never incorrect — a wider UNIQUE prevents valid duplicate insertion; a missing UNIQUE permits silent semantic corruption. ------------------------------------------------------------------------------ -- Cascade delete foreign key constraint definitions (for Compounds) ------------------------------------------------------------------------------ The following cascade delete constraints implement the compound value object ownership model described in the file header. Each constraint takes the form: ALTER TABLE <CompoundType> ADD CONSTRAINT ... FOREIGN KEY ("id") REFERENCES <ParentTable> (<compoundColumn>) ON DELETE CASCADE; This reverse-reference pattern means: when a parent row is deleted, any compound child row whose "id" matches the parent's compound column value is automatically deleted. Because each compound row has exactly one owner (enforced by the unique UUID insertion directive above), this cascade correctly and completely cleans up all compound child rows when their parent is deleted — no application-level programmatic deletion is required. Known DBMS constraint engine limitation: the DBMS enforces each cascade FK constraint globally across all rows in the compound table. It has no awareness of the per-row ownership convention. Consumers who violate the unique UUID insertion directive — for example by reusing a compound row UUID in more than one parent column — will encounter FK constraint violations at insert time, since the DBMS will check the reused UUID against every cascade constraint on the compound table simultaneously. This is by design: the constraint violation serves as the enforcement mechanism for the ownership model. ------------------------------------------------------------------------------ -- Foreign key column indexes for optimized queries and joins ------------------------------------------------------------------------------ CREATE TABLE "id" "id" "id" "mRID" PRIMARY KEY CREATE TABLE ( "name" VARCHAR(100) PRIMARY KEY ); INSERT INTO ( "name" ) VALUES ( '' ); -- FK column reference to the table representing the "" compound -- FK column reference to table representing the "" class UNIQUE -- Inheritance subclass-superclass constraint for table "" mRID id ALTER TABLE ADD FOREIGN KEY ( "" ) REFERENCES ( "" ); -- Foreign keys for table "" -- Cascade deletes for compounds referenced in table "" ALTER TABLE ADD FOREIGN KEY ( ) REFERENCES ( "id" ); ( "id" ); ( "mRID" ); ALTER TABLE ADD CONSTRAINT FOREIGN KEY ( "id" ) REFERENCES ( ) ON DELETE CASCADE; CREATE INDEX ON ( ); -- FK column reference to table representing the "" enumeration VARCHAR(100) ALTER TABLE ADD FOREIGN KEY ( ) REFERENCES ( "name" ); -- Heuristic uniqueness constraint for non-IdentifiedObject table "" ALTER TABLE ADD UNIQUE ();