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