Table Relationships (Python)

Table Relationships (Python)
 1# This script shows how to access information about both internal and custom table
 2# relationships within the impact database
 3ot = impact.gui.output_toolbox
 4
 5def DescribeRelationship(relationship):
 6    if relationship.internal_relationship:
 7        typeString = "Internal"
 8    else:
 9        typeString = "User Defined"
10
11    if relationship.cascading_deletes:
12        typeString = typeString + ", CascadingDeletes"
13
14    if relationship.is_valid:
15        validString = "Valid (" + relationship.child_table.name + "." + relationship.child_column.name + " --> " + relationship.parent_table.name + "." + relationship.parent_column.name + ")"
16    else:
17        validString = "Invalid"
18
19    ot.add(relationship.formatted_name + " (" + typeString + "): " + validString)
20
21def DescribeRelationships(name, relationships):
22    ot.add("**** " + name + " (" + str(relationships.count) + ") ****")
23
24    for i in range(1, relationships.count + 1):
25        DescribeRelationship(relationships.item(i))
26    # alternative syntax
27    # for each relationship in relationships
28    # DescribeRelationship relationship
29    # next
30
31ad = impact.active_database
32
33ot.clear()  # all database relationships
34relationships = ad.settings.table_relationships
35
36DescribeRelationships("All Database Table Relationships", relationships)
37
38# all relationships for impact.drawings
39table = None
40try:
41    table = ad.settings.tables.item("impact.drawings")
42except Exception as exc:
43    impact.gui.output_toolbox.add(f"Failed to create object via ad.settings.tables.item(): {exc}")
44
45DescribeRelationships(table.name + " Table Relationships", table.relationships)
46
47# all relationships for CUSTOMER.CS_KEY
48column = None
49try:
50    column = ad.settings.tables.item("CUSTOMER").columns.item("CS_KEY")
51except Exception as exc:
52    impact.gui.output_toolbox.add(f"Failed to create object via columns.item(): {exc}")
53
54DescribeRelationships(column.name + " Column Relationships", column.relationships)
55
56# find relationship by a key
57ot.add("**** Find Relationship by Key ****")
58relationship = None
59try:
60    relationship = relationships.item("impact.drawings.D_CUSTOMER->CUSTOMER.CS_KEY")
61except Exception as exc:
62    impact.gui.output_toolbox.add(f"Failed to create object via relationships.item(): {exc}")
63
64DescribeRelationship(relationship)
65
66# access IDatabaseTable and IDatabaseColumn from relationship
67ot.add("ChildTable Name: " + relationship.child_table.name)
68ot.add("ChildColumn Name: " + relationship.child_column.name)
69ot.add("ParentTable Name: " + relationship.parent_table.name)
70ot.add("ParentColumn Name: " + relationship.parent_column.name)
71
72# find the (optional) display column for an advanced query custom lookup
73ot.add("**** Find Relationship display column ****")
74relationship = None
75try:
76    relationship = relationships.item("impact.drawings.D_CONTACT->CONTACTS.CC_KEY")
77except Exception as exc:
78    impact.gui.output_toolbox.add(f"Failed to create object via relationships.item(): {exc}")
79
80if not relationship.isNone():
81
82    ot.add(relationship.formatted_name + ": " + relationship.display_column)