Table Relationships

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