pysql.pysqlgraphics

1 #!/usr/bin/python 2 # -*- coding: utf-8 -*- 3 4 """ This module defines all high level graphical functions of pysql 5 @author: Sébastien Delcros (Sebastien.Delcros@gmail.com) 6 @author: Sébastien Renard (sebastien.renard@digitalfox.org) 7 @license: GNU GPL V3 8 """ 9 10 # pylint: disable-msg=E1101 11 12 # Python imports: 13 import os 14 import sys 15 import re 16 import subprocess 17 from math import floor, log, sqrt 18 19 # Pysql imports: 20 from pysqlqueries import datamodelSql, dependenciesSql, diskusageSql 21 from pysqlexception import PysqlException, PysqlActionDenied 22 from pysqlcolor import BOLD, CYAN, GREEN, GREY, RED, RESET 23 from pysqlconf import PysqlConf 24 from pysqloraobjects import OraObject 25 from pysqlhelpers import convert, generateWhere, getProg, removeComment, which 26 27 # High level pysql graphical functions

28 -def datamodel(db, userName, tableFilter=None, withColumns=True):

29 """Extracts the datamodel of the current user as a picture 30 The generation of the picture is powered by Graphviz (http://www.graphviz.org) 31 through the PyDot API (http://www.dkbza.org/pydot.html) 32 @param db: pysql db connection 33 @param userName: schema to be extracted 34 @param tableFilter: filter pattern (in pysql extended syntax to extract only some tables (None means all) 35 @param withColumns: Indicate whether columns are included or not in datamodel picture 36 """ 37 # Tries to import pydot module 38 try: 39 from pydot import find_graphviz, Dot, Edge, Node 40 except ImportError: 41 message = _("Function not available because pydot module is not installed.\n\t") 42 message += _("Go to http://dkbza.org/pydot.html to get it.") 43 raise PysqlException(message) 44 45 # Reads conf 46 conf = PysqlConf.getConfig() 47 format = conf.get("graph_format") # Output format of the picture 48 fontname = conf.get("graph_fontname") # Font used for table names 49 fontsize = conf.get("graph_fontsize") # Font size for table names 50 fontcolor = conf.get("graph_fontcolor") # Color of table and column names 51 tablecolor = conf.get("graph_tablecolor") # Color of tables 52 bordercolor = conf.get("graph_bordercolor") # Color of tables borders 53 linkcolor = conf.get("graph_linkcolor") # Color of links between tables 54 linklabel = conf.get("graph_linklabel") # Display constraints name or not 55 56 # Gets picture generator 57 prog = getProg(find_graphviz(), conf.get("graph_program"), "fdp") 58 59 graph = Dot(splines="compound") 60 61 # Tables, columns and constraints (temporary and external tables are excluded. So are TOAD tables) 62 if tableFilter: 63 whereClause = generateWhere("table_name", tableFilter) 64 else: 65 whereClause = "1=1" 66 tables = db.executeAll(datamodelSql["tablesFromOwner"] % (userName, whereClause)) 67 nbTables = len(tables) 68 if nbTables == 0: 69 raise PysqlException(_("No table found. Your filter clause is too restrictive or the schema is empty")) 70 tableList = ", ".join(["'%s'" % table[0] for table in tables]) # Table list formated to be used in SQL query 71 print CYAN + _("Extracting %d tables... ") % nbTables + RESET, 72 current = 0 73 for table in tables: 74 tableName = table[0] 75 content = """<<TABLE BORDER="0" CELLBORDER="1" CELLSPACING="0">""" 76 content += """\n<TR><TD PORT="%s">""" % tableName 77 content += """<FONT FACE="%s" POINT-SIZE="%f" COLOR="%s">""" % (fontname, fontsize, fontcolor) 78 content += tableName 79 content += "</FONT></TD></TR>" 80 if withColumns: 81 columns = db.executeAll(datamodelSql["columnsFromOwnerAndTable"], [userName, tableName]) 82 for column in columns: 83 columnName = column[0] 84 columnType = column[1] 85 content += """\n<TR><TD ALIGN="LEFT" PORT="%s_%s">""" % (tableName, columnName) 86 content += """<FONT FACE="%s" POINT-SIZE="%f" COLOR="%s">""" % \ 87 (fontname, fontsize - 2, fontcolor) 88 if column[2] is None: # Normal field 89 content += " " 90 else: # Primary key field 91 content += "PK%d" % int(column[2]) 92 content += " %s (%s)" % (columnName, columnType) 93 content += "</FONT></TD></TR>" 94 content += "\n</TABLE>>" 95 graph.add_node(Node(tableName, shape="none", label=content, style="filled", \ 96 fillcolor=tablecolor, color=bordercolor)) 97 current += 1 98 sys.stdout.write("\b\b\b\b\b%4.1f%%" % round(100 * float(current) / nbTables, 1)) 99 sys.stdout.flush() 100 101 print 102 # Links between tables (foreign key -> primary key) 103 # Only extract links from considered tables 104 links = db.executeAll(datamodelSql["constraintsFromOwner"] % (userName, tableList, tableList)) 105 nbLinks = len(links) 106 print (CYAN + _("Extracting %d links... ") % nbLinks + RESET), 107 current = 0 108 for link in links: 109 if linklabel == "yes": 110 graph.add_edge(Edge(src=link[1], dst=link[2], color=linkcolor)) 111 else: 112 graph.add_edge(Edge(src=link[1], dst=link[2], label=link[0], color=linkcolor, \ 113 fontcolor=linkcolor, fontname=fontname, fontsize=str(fontsize - 3))) 114 current += 1 115 sys.stdout.write("\b\b\b\b\b%4.1f%%" % round(100 * float(current) / nbLinks, 1)) 116 117 print 118 filename = db.getDSN() + "_" + userName + "." + format 119 generateImage(graph, filename, prog, format) 120 viewImage(filename)

121

122 -def dependencies(db, objectName, direction, maxDepth, maxNodes):

123 """Displays object dependencies as a picture 124 The generation of the picture is powered by Graphviz (http://www.graphviz.org) 125 through the PyDot API (http://www.dkbza.org/pydot.html) 126 @param db: pysql db connection 127 @param objectName: name of the oracle object on which dependancies are computed 128 @param direction: direction of the dependancy graph. Can be "onto", "from" or "both" 129 @param maxDepth: Override default maxDepth value. If None, use default value 130 @param maxNodes: Override default maxNodes value. If None, use default value 131 """ 132 # Tries to import pydot module 133 try: 134 from pydot import find_graphviz, Dot, Edge, Node 135 except ImportError: 136 message = _("Function not available because pydot module is not installed.\n\t") 137 message += _("Go to http://dkbza.org/pydot.html to get it.") 138 raise PysqlException(message) 139 140 # Reads conf 141 conf = PysqlConf.getConfig() 142 format = conf.get("graph_format") # Output format of the picture 143 fontname = conf.get("graph_fontname") # Font used for object names 144 fontsize = conf.get("graph_fontsize") # Font size for object names 145 146 # Gets picture generator 147 prog = getProg(find_graphviz(), conf.get("graph_program"), "dot") 148 149 graph = Dot(overlap="false", splines="true", rankdir="TB") 150 151 if direction == "onto" or direction == "from": 152 dirList = [direction] 153 elif direction == "both": 154 dirList = ["onto", "from"] 155 else: 156 dirList = [] 157 158 for currentDir in dirList: 159 depth = 0 160 objectList = [OraObject(objectName=objectName)] 161 objectList[0].guessInfos(db) 162 objectOwner = objectList[0].getOwner() 163 objectName = objectList[0].getName() 164 objectType = objectList[0].getType() 165 label = objectOwner + "." + objectName + "\\n(" + objectType + ")" 166 graph.add_node(Node(objectName, label=label, fontname=fontname, fontsize=str(fontsize), shape="diamond")) 167 nodeList = [objectName] 168 edgeList = [] 169 nextObjectList = [] 170 171 while objectList != [] and depth <= maxDepth and len(nodeList) <= maxNodes: 172 depth += 1 173 for currentObject in objectList: 174 currentObjectOwner = currentObject.getOwner() 175 currentObjectName = currentObject.getName() 176 if currentDir == "onto": 177 # Objects referencing the the current object 178 result = db.executeAll(dependenciesSql["refOnFromOwnerAndName"], \ 179 [currentObjectOwner, currentObjectName]) 180 elif currentDir == "from": 181 # Objects referenced by the the current object 182 result = db.executeAll(dependenciesSql["refByFromOwnerAndName"], \ 183 [currentObjectOwner, currentObjectName]) 184 refObjectList = [OraObject(objectOwner=i[0], objectName=i[1]) for i in result] 185 for currentRefObject in refObjectList: 186 currentRefObject.guessInfos(db) 187 currentRefObjectOwner = currentRefObject.getOwner() 188 currentRefObjectName = currentRefObject.getName() 189 currentRefObjectType = currentRefObject.getType() 190 if not currentRefObjectName in nodeList: 191 nodeList.append(currentRefObjectName) 192 # Object shape 193 if currentRefObjectType in ("TABLE", "VIEW", "SEQUENCE"): 194 shape = "box" 195 elif currentRefObjectType in ("PACKAGE", "FUNCTION", "PROCEDURE", "TRIGGER"): 196 shape = "ellipse" 197 else: 198 shape = "none" 199 # Object label 200 if currentRefObjectOwner == db.getUsername().upper(): 201 label = currentRefObjectName 202 else: 203 label = currentRefObjectOwner + "." + currentRefObjectName 204 label += "\\n(" + currentRefObjectType + ")" 205 # Adding object to graph 206 graph.add_node(Node(currentRefObjectName, label=label, fontname=fontname, \ 207 fontsize=str(fontsize), shape=shape)) 208 if not [currentObjectName, currentRefObjectName] in edgeList: 209 if currentDir == "onto": 210 edgeList.append([currentObjectName, currentRefObjectName]) 211 graph.add_edge(Edge(dst=currentObjectName, src=currentRefObjectName, \ 212 color="red")) 213 elif currentDir == "from": 214 edgeList.append([currentObjectName, currentRefObjectName]) 215 graph.add_edge(Edge(src=currentObjectName, dst=currentRefObjectName, \ 216 color="darkgreen")) 217 nextObjectList += refObjectList 218 objectList = nextObjectList 219 nextObjectList = [] 220 221 if len(nodeList) > maxNodes: 222 print RED + _("Warning: reach max node, references lookup stopped on direction %s") % currentDir + RESET 223 if depth > maxDepth: 224 print RED + _("Warning: reach max recursion limit, references lookup stopped on direction %s") % currentDir + RESET 225 226 filename = "dep_" + objectOwner + "." + objectName + "." + format 227 generateImage(graph, filename, prog, format) 228 viewImage(filename)

229 230

231 -def diskusage(db, userName, withIndexes=False, percent=True):

232 """Extracts the physical storage of the current user as a picture based on Oracle statistics 233 The generation of the picture is powered by Graphviz (http://www.graphviz.org) 234 through the PyDot API (http://www.dkbza.org/pydot.html) 235 """ 236 # Tries to import pydot module 237 try: 238 from pydot import find_graphviz, Dot, Subgraph, Cluster, Edge, Node 239 except ImportError: 240 message = _("Function not available because pydot module is not installed.\n\t") 241 message += _("Go to http://dkbza.org/pydot.html to get it.") 242 raise PysqlException(message) 243 244 # Reads conf 245 conf = PysqlConf.getConfig() 246 unit = conf.get("unit") # Unit used to format data 247 format = conf.get("graph_format") # Output format of the picture 248 fontname = conf.get("graph_fontname") # Font used for table names 249 fontsize = conf.get("graph_fontsize") # Font size for table names 250 fontcolor = conf.get("graph_fontcolor") # Color of table and column names 251 tablecolor = conf.get("graph_tablecolor") # Color of tables 252 indexcolor = conf.get("graph_indexcolor") # Color of indexes 253 bordercolor = conf.get("graph_bordercolor") # Color of borders 254 255 # Gets picture generator 256 prog = getProg(find_graphviz(), conf.get("graph_program"), "fdp") 257 258 # First step: objects library building 259 # Tablespaces 260 if userName == db.getUsername().upper(): 261 tablespaces = db.executeAll(diskusageSql["Tablespaces"]) 262 else: 263 tablespaces = db.executeAll(diskusageSql["TablespacesFromOwner"], [userName]) 264 tbsBytes = 0 265 tbsList = [] 266 for tablespace in tablespaces: 267 tablespaceName = unicode(tablespace[0]) 268 269 # Tables from current tablespace 270 if userName == db.getUsername().upper(): 271 tables = db.executeAll(diskusageSql["TablesFromTbs"], [tablespaceName]) 272 else: 273 tables = db.executeAll(diskusageSql["TablesFromOwnerAndTbs"], [userName, tablespaceName]) 274 tabList = [] 275 print CYAN + _("Extracting %3d tables from tablespace %s") % (len(tables), tablespaceName) + RESET 276 for table in tables: 277 tableName = table[0] 278 if table[1] is None: 279 print RED + _("""Warning: table "%s" removed because no statistics have been found""") \ 280 % (tablespaceName + "/" + tableName) + RESET 281 continue 282 if table[1] == 0: 283 print RED + _("""Warning: table "%s" removed because it is empty""") \ 284 % (tablespaceName + "/" + tableName) + RESET 285 continue 286 numRows = int(table[1]) 287 avgRowLen = float(table[2]) 288 bytes = int(table[3]) 289 tbsBytes += bytes 290 tabList += [[tableName, bytes, numRows, avgRowLen]] 291 292 if withIndexes: 293 # Indexes from current tablespace 294 if userName == db.getUsername().upper(): 295 indexes = db.executeAll(diskusageSql["IndexesFromTbs"], [tablespaceName]) 296 else: 297 indexes = db.executeAll(diskusageSql["IndexesFromOwnerAndTbs"], [userName, tablespaceName]) 298 idxList = [] 299 print CYAN + _("Extracting %3d indexes from tablespace %s") % (len(indexes), tablespaceName) + RESET 300 for index in indexes: 301 indexName = index[0] 302 if index[1] is None: 303 print RED + _("""Warning: index "%s" removed because no statistics have been found""") \ 304 % (tablespaceName + "/" + indexName) + RESET 305 continue 306 if index[1] == 0: 307 print RED + _("""Warning: index "%s" removed because it is empty""") \ 308 % (tablespaceName + "/" + indexName) + RESET 309 continue 310 numRows = int(index[1]) 311 distinctKeys = int(index[2]) 312 bytes = int(index[3]) 313 tabName = str(index[4]) 314 tbsBytes += bytes 315 idxList += [[indexName, bytes, numRows, distinctKeys, tabName]] 316 else: 317 print CYAN + _("Not extracting indexes from tablespace %s (ignored)") % (tablespaceName) + RESET 318 idxList = [] 319 tbsList += [[tablespaceName, tbsBytes, tabList, idxList]] 320 321 # Second step: objects drawing 322 graph = Dot(label=userName, overlap="false", splines="true") 323 324 for tbs in tbsList: 325 tbsName = tbs[0] 326 tbsBytes = tbs[1] 327 tabList = tbs[2] 328 idxList = tbs[3] 329 subGraph = Subgraph("cluster_" + tbsName, bgcolor="palegreen", \ 330 fontname=fontname, fontsize=str(fontsize - 1), \ 331 label="%s\\n(%d %s)" % (tbsName, convert(tbsBytes, unit), unit.upper())) 332 graph.add_subgraph(subGraph) 333 334 print CYAN + _("Displaying %3d tables for tablespace %s") % (len(tabList), tbsName) + RESET 335 for tab in tabList: 336 name = tab[0] 337 bytes = tab[1] 338 numRows = tab[2] # unused 339 avgRowLen = tab[3] # unused 340 341 # Mathematics at work 342 width = 0.2 343 height = 0.2 344 if percent: 345 height += 10 * round(float(bytes) / tbsBytes, 4) 346 label = "%s\\n(%.2f %s)" % (name, round(100 * float(bytes) / tbsBytes, 2), "%") 347 348 else: 349 height += round(sqrt(bytes) / 8192, 3) 350 width += round(sqrt(bytes) / 8192, 3) 351 label = "%s\\n(%3d %s)" % (name, convert(bytes, unit), unit.upper()) 352 subGraph.add_node(Node(name, label=label, shape="box", style="filled", \ 353 color="none", fillcolor=tablecolor, \ 354 fontname=fontname, fontcolor=fontcolor, fixedsize="false", \ 355 fontsize=str(fontsize - 2 - floor((len(label) - 7) / 15)), \ 356 nodesep="0.01", height=str(height), width=str(max(width, 1)))) 357 358 print CYAN + _("Displaying %3d indexes for tablespace %s") % (len(idxList), tbsName) + RESET 359 for idx in idxList: 360 name = idx[0] 361 bytes = idx[1] 362 numRows = idx[2] # unused 363 distinctKeys = idx[3] # unused 364 tabName = idx[4] # unused 365 366 # Mathematics at work again) 367 width = 0.2 368 height = 0.2 369 if percent: 370 height += 10 * round(float(bytes) / tbsBytes, 4) 371 label = "%s\\n(%.2f %s)" % (name, round(100 * float(bytes) / tbsBytes, 2), "%") 372 else: 373 height += round(sqrt(bytes) / 8192, 3) 374 width += round(sqrt(bytes) / 8192, 3) 375 label = "%s\\n(%3d %s)" % (name, convert(bytes, unit), unit.upper()) 376 377 subGraph.add_node(Node(name, label=label, shape="box", style="filled", \ 378 color="none", fillcolor=indexcolor, \ 379 fontname=fontname, fontcolor=fontcolor, fixedsize="false", \ 380 fontsize=str(fontsize - 2 - floor((len(label) - 7) / 15)), \ 381 nodesep="0.01", height=str(height), width=str(max(width, 1)))) 382 #Moving index near by its table (unused because it widens the graph) 383 #subGraph.add_edge(Edge(src=name, dst=tabName, constraint="false", style="invis")) 384 385 filename = "du_" + userName + "." + format 386 generateImage(graph, filename, prog, format) 387 viewImage(filename)

388

389 -def pkgTree(db, packageName):

390 """Creates the call tree of internal package functions and procedures""" 391 392 # Tries to import pydot module 393 try: 394 from pydot import find_graphviz, Dot, Edge, Node 395 except ImportError: 396 message = _("Function not available because pydot module is not installed.\n\t") 397 message += _("Go to http://dkbza.org/pydot.html to get it.") 398 raise PysqlException(message) 399 400 # Reads conf 401 conf = PysqlConf.getConfig() 402 format = conf.get("graph_format") # Output format of the picture 403 fontname = conf.get("graph_fontname") # Font used for functions names 404 fontsize = conf.get("graph_fontsize") # Font size for functions names 405 fontcolor = conf.get("graph_fontcolor") # Color of functions names 406 407 # Gets picture generator 408 prog = getProg(find_graphviz(), conf.get("graph_program"), "fdp") 409 410 package = OraObject(objectName=packageName) 411 package.guessInfos(db) 412 413 graph = Dot(overlap="false", splines="true") 414 415 # Lists of function or procedure 416 verbs = [] 417 418 # Tries to resolve synonym and describe the target 419 #TODO: factorise this code!! 420 if package.getType() == "SYNONYM": 421 package = package.getTarget(db) 422 if package.getType() == "SYNONYM": 423 raise PysqlException(_("Too much synonym recursion")) 424 425 if package.getType() not in ("PACKAGE", "PACKAGE BODY"): 426 raise PysqlException(_("This is not a package or package not found")) 427 428 # Gets package body content 429 package.setType(u"PACKAGE BODY") 430 print CYAN + _("Extracting package source...") + RESET 431 content = package.getSQLAsList(db) 432 433 # Removes comments 434 print CYAN + _("Parsing source and building graph...") + RESET 435 newContent = [] 436 comment = False 437 for line in content: 438 line, comment = removeComment(line, comment) 439 newContent.append(line) 440 content = newContent 441 442 # Gets procedures and functions 443 for line in content: 444 result = re.match("\s*(FUNCTION|PROCEDURE)\s+(.+?)[\s|\(]+", line, re.I) 445 if result: 446 verbs.append(re.escape(result.group(2))) 447 graph.add_node(Node(result.group(2).upper(), shape="box", label=result.group(2).upper(), \ 448 fontsize=str(fontsize), fontname=fontname, fontcolor=fontcolor)) 449 450 if not verbs: 451 raise PysqlException(_("This package does not have any readable function or procedure")) 452 453 verbs = "|".join(verbs) 454 # Gets call of functions/procedure inside each other 455 currentVerb = "" 456 for line in content: 457 # Doesn't pay attention to end lines 458 if re.match("\s*END.*;", line, re.I): 459 continue 460 # Marks the function/procedure we are parsing 461 result = re.match("\s*(FUNCTION|PROCEDURE)\s+(.+?)[\s|\(]+", line, re.I) 462 if result: 463 currentVerb = result.group(2) 464 continue # else we get a circular reference below ;-) 465 result = re.match(".*\s(%s).*" % verbs, line, re.I) 466 if result: 467 if graph.get_edge(currentVerb.upper(), result.group(1).upper()) is None: 468 graph.add_edge(Edge(src=currentVerb.upper(), dst=result.group(1).upper())) 469 470 filename = package.getName() + "_dep." + format 471 generateImage(graph, filename, prog, format) 472 viewImage(filename)

473

474 -def viewImage(imagePath):

475 """Shows Image with prefered user image viewer 476 @param imagePath: path to image file""" 477 conf = PysqlConf.getConfig() 478 viewer = conf.get("graph_viewer") 479 if viewer == "off": 480 return 481 elif viewer == "auto": 482 if os.name == "nt": 483 viewers = ("mspaint.exe",) 484 else: 485 viewers = ("gwenview", "kview", "kuickshow", "eog", "gthumb", "gimp", "firefox") 486 for viewer in viewers: 487 viewer = which(viewer) 488 if viewer is not None: 489 break 490 else: 491 viewer = which(viewer) 492 if viewer is not None: 493 subprocess.Popen([viewer, imagePath], stdout=subprocess.PIPE, stderr=subprocess.STDOUT) 494 else: 495 raise PysqlException(_("Viewer was not found"))

496

497 -def generateImage(graph, filename, prog, format):

498 """Generate graphviz image from graph 499 @param graph: pydot graph object 500 @param filename: image filename (str) 501 @param format: image format (str) 502 """ 503 print CYAN + _("Generating picture using %s filter...") % prog + RESET 504 filepath = os.getcwd() + os.sep + filename 505 import pydot 506 oldstdout = sys.stdout 507 sys.stdout = open(os.devnull, 'a') # Mutes STDOUT 508 try: 509 graph.write(filepath, prog=prog, format=format) 510 except (IOError, OSError, pydot.InvocationException), e: 511 sys.stdout = sys.__stdout__ # Restores STDOUT 512 raise PysqlException(_("Graphviz failed to generate image:\n%s") % e) 513 sys.stdout = oldstdout # Restores STDOUT 514 print GREEN + _("Image saved as ") + filepath + RESET

515

Source Code for Module pysql.pysqlgraphics