/
relationship_create_ver01_grt.py
146 lines (116 loc) · 5.48 KB
/
relationship_create_ver01_grt.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
# Original code is http://wb.mysql.com/?p=777
# import the wb module
from wb import *
# import the grt module
import grt
# import the mforms module for GUI stuff
import mforms
# define this Python module as a GRT module
ModuleInfo = DefineModule(name= "AutoRelationshipUtils", author= "Oracle Corp.", version="1.0")
def get_fk_candidate_list(schema, fk_name_format, match_types):
candidate_list = []
possible_fks = {}
# create the list of possible foreign keys out of the list of tables
for table in schema.tables:
if table.primaryKey and len(table.primaryKey.columns) == 1: # composite FKs not supported
format_args = {'table':table.name.rstrip('s'), 'pk':table.primaryKey.columns[0].name}
fkname = fk_name_format % format_args
possible_fks[fkname] = table
# go through all tables in schema again, this time to find columns that seem to be a fk
for table in schema.tables:
for column in table.columns:
if possible_fks.has_key(column.name):
ref_table = possible_fks[column.name]
ref_column = ref_table.primaryKey.columns[0].referencedColumn
if ref_column == column:
continue
if match_types and ref_column.formattedType != column.formattedType:
continue
candidate_list.append((table, column, ref_table, ref_column))
return candidate_list
class RelationshipCreator(mforms.Form):
def __init__(self, catalog):
mforms.Form.__init__(self, None, mforms.FormNone)
self.catalog = catalog
self.set_title("Create Relationships for Tables")
box = mforms.newBox(False)
self.set_content(box)
box.set_padding(12)
box.set_spacing(12)
label = mforms.newLabel(
"""This will automatically create foreign keys for tables that match
a certain column naming pattern, allowing you to visualize relationships
between MyISAM tables.
To use, fill the Column Pattern field with the naming convention used for
columns that are meant to be used as foreign keys. The %(table)s and %(pk)s
variable names will be substituted with the referenced table values.""")
box.add(label, False, True)
hbox = mforms.newBox(True)
hbox.set_spacing(12)
box.add(hbox, False, True)
label = mforms.newLabel("Column Pattern:")
hbox.add(label, False, True)
self.pattern = mforms.newTextEntry()
hbox.add(self.pattern, True, True)
self.matchType = mforms.newCheckBox()
self.matchType.set_text("Match column types")
hbox.add(self.matchType, False, True)
self.matchType.set_active(True)
search = mforms.newButton()
search.set_text("Preview Matches")
search.add_clicked_callback(self.findMatches)
hbox.add(search, False, True)
self.pattern.set_value("%(table)s_id")
self.candidateTree = mforms.newTreeView(mforms.TreeShowHeader)
self.candidateTree.add_column(mforms.StringColumnType, "From Table", 100, False)
self.candidateTree.add_column(mforms.StringColumnType, "Column", 100, False)
self.candidateTree.add_column(mforms.StringColumnType, "Type", 100, False)
self.candidateTree.add_column(mforms.StringColumnType, "To Table", 100, False)
self.candidateTree.add_column(mforms.StringColumnType, "Column", 100, False)
self.candidateTree.add_column(mforms.StringColumnType, "Type", 100, False)
self.candidateTree.end_columns()
box.add(self.candidateTree, True, True)
hbox = mforms.newBox(True)
hbox.set_spacing(12)
self.matchCount = mforms.newLabel("")
hbox.add(self.matchCount, False, True)
self.cancelButton = mforms.newButton()
self.cancelButton.set_text("Cancel")
hbox.add_end(self.cancelButton, False, True)
self.okButton = mforms.newButton()
self.okButton.set_text("Create FKs")
hbox.add_end(self.okButton, False, True)
self.okButton.add_clicked_callback(self.createFKs)
box.add(hbox, False, True)
self.set_size(700, 600)
def findMatches(self):
candidates = []
for schema in self.catalog.schemata:
candidates += get_fk_candidate_list(schema, self.pattern.get_string_value(), self.matchType.get_active())
self.candidateTree.clear_rows()
for table, column, ref_table, ref_column in candidates:
row = self.candidateTree.add_row()
self.candidateTree.set_string(row, 0, table.name)
self.candidateTree.set_string(row, 1, column.name)
self.candidateTree.set_string(row, 2, column.formattedType)
self.candidateTree.set_string(row, 3, ref_table.name)
self.candidateTree.set_string(row, 4, ref_column.name)
self.candidateTree.set_string(row, 5, ref_column.formattedType)
self.matchCount.set_text("%i matches found" % len(candidates))
def createFKs(self):
candidates = []
for schema in self.catalog.schemata:
candidates += get_fk_candidate_list(schema, self.pattern.get_string_value(), self.matchType.get_active())
for table, column, ref_table, ref_column in candidates:
fk = table.createForeignKey('FK_'+ref_column.name)
fk.referencedTable = ref_table
fk.columns.append(column)
fk.referencedColumns.append(ref_column)
def run(self):
self.run_modal(self.okButton, self.cancelButton)
@ModuleInfo.plugin("wb.catalog.util.autoCreateRelationships", caption= "Create Relationships from Columns", input= [wbinputs.currentCatalog()], pluginMenu= "Catalog", type="standalone")
@ModuleInfo.export(grt.INT, grt.classes.db_Catalog)
def autoCreateRelationships(catalog):
form = RelationshipCreator(catalog)
form.run()
return 0