def __init__(self, connectString, mode=""):
# Instance attributs
self.connection = None
self.cursor = None
# Read Conf
self.conf = PysqlConf.getConfig()
# Keep connection string to allow future connection
self.connectString = connectString.encode(self.conf.getCodec(), "ignore")
# Connect to Oracle
try:
if mode == "sysoper":
try:
self.connection = connect(self.connectString, mode=SYSOPER)
except (DatabaseError), e:
print CYAN + _("Connected to an idle instance") + RESET
self.connection = connect(self.connectString, mode=SYSOPER | PRELIM_AUTH)
elif mode == "sysdba":
try:
self.connection = connect(self.connectString, mode=SYSDBA)
except (DatabaseError), e:
print CYAN + _("Connected to an idle instance") + RESET
self.connection = connect(self.connectString, mode=SYSDBA | PRELIM_AUTH)
def setup():
# Set PYDEVDEBUG to 1 in order to desactivate shell colors
os.environ["PYDEVDEBUG"]="1"
# Setup gettext to avoid error with the global _() function
gettext.install("pysql", "", unicode=1)
# Add pysql module path
sys.path.append(abspath(join(dirname(__file__), pardir, "src", "pysql")))
# Set locale
# Loads config (first time)
from pysqlconf import PysqlConf
conf=PysqlConf.getConfig()
# Sets the locale
import pysqlmain
pysqlmain.setLocale(conf)
def viewImage(imagePath):
"""Shows Image with prefered user image viewer
@param imagePath: path to image file"""
conf = PysqlConf.getConfig()
viewer = conf.get("graph_viewer")
if viewer == "off":
return
elif viewer == "auto":
if os.name == "nt":
viewers = ("mspaint.exe",)
else:
viewers = ("gwenview", "kview", "kuickshow", "eog", "gthumb", "gimp", "firefox")
for viewer in viewers:
viewer = which(viewer)
if viewer is not None:
break
else:
viewer = which(viewer)
if viewer is not None:
subprocess.Popen([viewer, imagePath], stdout=subprocess.PIPE, stderr=subprocess.STDOUT)
else:
raise PysqlException(_("Viewer was not found"))
def main():
"""Pysql main function"""
rc = 0 # Return code
# Options & args stuf
(options, argv) = parseOptions()
# i18n stuff
if os.name == "nt":
# Windows stuff is never like everybody...
i18nPath = join(dirname(sys.argv[0]), "share", "locale")
else:
# Unix std path
i18nPath = join(dirname(sys.argv[0]), pardir, "share", "locale")
# Loads message catalog
gettext.install("pysql", i18nPath, unicode=1)
# Loads config (first time)
conf = PysqlConf.getConfig()
# Sets the locale
setLocale(conf)
try:
if options.update:
try:
pysqlupdate.checkForUpdate(options.proxyHost, options.proxyUser, options.proxyPassword)
except KeyboardInterrupt:
print _("Aborting update")
elif options.version:
printComponentsVersion()
else:
try:
import cx_Oracle
except ImportError:
# Untranslatable error message (i18n still not initialized at this step)
print RED + BOLD + "cx_Oracle module cannot be loaded." + RESET
print
print "Please, ensure you correctly install it from: " + CYAN + "http://cx-oracle.sf.net" + RESET
print "And that have the according Oracle client installation."
print "Get it from the Oracle site : http://www.oracle.com"
print
print "(press enter key to exit)"
sys.stdin.readline()
sys.exit(1)
# Now we can import PysqlShell (and subsequent modules that depends on cx_Oracle)
from pysqlshell import PysqlShell
# Default is to launch pysql in standard mode (local client)
shell = PysqlShell(silent=options.silent, argv=argv)
if options.oneTryLogin and shell.db == None:
rc = 1
else:
shell.loop()
rc = shell.rc
# Bye
if os.name == "nt" and not options.version:
# Don't exit too fast for windows users, else they don't see error sum up
print _("(press any key to exit)")
sys.stdin.readline()
except StandardError, e:
# Just a hook for a more pleasant error handling
print "\n==> Unrecoverable error during initialisation. Exiting <=="
printStackTrace()
print _("(press enter key to exit)")
sys.stdin.readline()
def desc(db, objectName, printDetails=True, printStats=False, sort=False):
"""Describes an object
@param objectName: object to be described
@return: header and resultset of definition as a tuple (header, definition)
==> This function should be split in two parts: one in pysqlOraObjects for object self description
the other one here as a describe function that encapsulate pysqlOraObjects manipulation"""
header = []
result = []
# Reads conf
conf = PysqlConf.getConfig()
unit = conf.get("unit") # Unit used to format data
# Look for object type if given
matchResult = re.match("(.*) \((.+)\)", objectName)
if matchResult:
oraObject = OraObject(objectName=matchResult.group(1),
objectType=matchResult.group(2))
else:
oraObject = OraObject(objectName=objectName)
# Gets the object type and owner
oraObjectSet = oraObject.guessInfos(db, interactive=True)
if len(oraObjectSet) == 1:
oraObject = oraObjectSet.pop()
elif len(oraObjectSet) > 1:
print CYAN + _("Got multiple result:") + "\n-" + RESET,
print "\n- ".join([str(x) for x in oraObjectSet])
# Looking for own object
ownOraObjects = [o for o in oraObjectSet if o.getOwner() == db.getUsername().upper()]
publicOraObjects = [o for o in oraObjectSet if o.getOwner() == "PUBLIC"]
if len(ownOraObjects) == 1:
oraObject = ownOraObjects.pop()
print BOLD + RED + _("Defaulting to own object: %s") % oraObject + RESET
# Looking for public objects
elif len(publicOraObjects) == 1:
oraObject = publicOraObjects.pop()
print BOLD + RED + _("Defaulting to public object: %s") % oraObject + RESET
else:
# No result
return ([], [])
# Object or type unknown?
if oraObject.getType() == "":
return ([], [])
# Tries to resolve synonym and describe the target
if oraObject.getType() == "SYNONYM":
oraObject = oraObject.getTarget(db)
if oraObject.getType() == "SYNONYM":
# cannot desc, too much synonym recursion
return ([], [])
# Guess object status
oraObject.guessStatus(db)
# Displays some information about the object
if printDetails:
print CYAN + _("Name") + "\t: " + oraObject.getName() + RESET
print CYAN + _("Type") + "\t: " + oraObject.getType() + RESET
print CYAN + _("Owner") + "\t: " + oraObject.getOwner() + RESET
if oraObject.getStatus() in ("INVALID", "OFFLINE", "UNUSED"):
print CYAN + _("Status") + "\t\t: " + BOLD + RED + oraObject.getStatus() + RESET
else:
print CYAN + _("Status") + "\t\t: " + oraObject.getStatus() + RESET
if oraObject.getType() in ("TABLE", "TABLE PARTITION", "INDEX", "INDEX PARTITION"):
try:
print CYAN + _("Tablespace") + "\t: " + oraObject.getTablespace(db) + RESET
except PysqlException:
print CYAN + _("Tablespace") + "\t: " + _("<unable to get tablepsace name>") + RESET
try:
print CYAN + _("Partitioned?") + "\t: " + (oraObject.isPartitioned(db) and _("Yes") or _("No")) + RESET
except PysqlException:
print CYAN + _("Partitioned?") + "\t: " + _("<unable to get partition information>") + RESET
if oraObject.getType() in ("TABLE", "TABLE PARTITION", "VIEW", "MATERIALIZED VIEW"):
try:
print CYAN + _("Comment") + "\t: " + oraObject.getComment(db) + RESET
except PysqlException:
print CYAN + _("Comment") + "\t: " + _("<unable to get comment>") + RESET
if oraObject.getType() not in ("DATA FILE", "TABLESPACE", "USER"):
try:
print CYAN + _("Created on") + "\t: " + oraObject.getCreated(db) + RESET
except PysqlException:
print CYAN + _("Created on") + "\t: " + _("<unable to get date of creation>") + RESET
try:
print CYAN + _("Last DDL on") + "\t: " + oraObject.getLastDDL(db) + RESET
except PysqlException:
print CYAN + _("Last DDL on") + "\t: " + _("<unable to get date of last DDL modification>") + RESET
# Displays some statistics about the object
if printStats:
if oraObject.getType() in ("TABLE", "TABLE PARTITION"):
try:
print ORANGE + _("Last analyzed on") + ": " + str(oraObject.getLastAnalyzed(db)) + RESET
except PysqlException:
print CYAN + _("Last analyzed on") + "\t: " + _("<unable to get date of last statistics computation>") + RESET
try:
print ORANGE + _("Nb rows") + "\t\t: " + str(oraObject.getNumRows(db)) + RESET
except PysqlException:
print CYAN + _("Nb rows") + "\t: " + _("<unable to get number of rows>") + RESET
try:
print ORANGE + _("Nb used blocks") + "\t: " + str(oraObject.getUsedBlocks(db)) + RESET
except PysqlException:
print ORANGE + _("Nb used blocks") + "\t: " + _("<unable to get number of used blocks>") + RESET
try:
print ORANGE + _("Avg row length") + "\t: " + str(oraObject.getAvgRowLength(db)) + RESET
except PysqlException:
print CYAN + _("Avg row length") + "\t: " + _("<unable to get average row length") + RESET
# Evaluates object type (among the 24 defined)
if oraObject.getType() in ("TABLE" , "TABLE PARTITION"):
header = [_("Name"), _("Type"), _("Null?"), _("Comments"), _("Indexes")]
columns = oraObject.getTableColumns(db, sort)
# Gets indexed columns of the table
indexedColumns = oraObject.getIndexedColumns(db)
# Format index this way: index_name(index_position)
#TODO: handle database encoding instead of using just str()
indexedColumns = [[i[0], i[1] + "(" + str(i[2]) + ")"] for i in indexedColumns]
for column in columns:
column = list(column) # change tuple to list
indexInfo = [i[1] for i in indexedColumns if i[0] == column[0]]
column.append(", ".join(indexInfo))
result.append(column)
elif oraObject.getType() in ("VIEW", "MATERIALIZED VIEW"):
header = [_("Name"), _("Type"), _("Null?"), _("Comments")]
result = oraObject.getTableColumns(db)
elif oraObject.getType() == "CONSUMER GROUP":
raise PysqlNotImplemented()
elif oraObject.getType() == "CONTEXT":
raise PysqlNotImplemented()
elif oraObject.getType() == "DATA FILE":
header = [_("Tablespace"), _("Size (%s)") % unit.upper(), _("Free (%s)") % unit.upper(), _("%Used")]
size = convert(oraObject.getAllocatedBytes(db), unit)
free = convert(oraObject.getFreeBytes(db), unit)
if size != 0:
used = 100 - float(100 * free) / size
else:
used = 0
if printDetails:
print CYAN + _("Tablespace: ") + oraObject.getTablespace(db).getName() + RESET
result = [[oraObject.getTablespace(db).getName(), round(size, 2), round(free, 2), round(used, 2)]]
elif oraObject.getType() == "DATABASE LINK":
header = [_("Target")]
result = [[oraObject.getRemoteUser(db) + "@" + oraObject.getRemoteHost(db)]]
elif oraObject.getType() == "DIRECTORY":
header = [_("Path")]
result = [[oraObject.getPath(db)]]
elif oraObject.getType() == "EVALUATION CONTEXT":
raise PysqlNotImplemented()
elif oraObject.getType()in("FUNCTION", "PACKAGE", "PROCEDURE"):
header = [_("#"), _("Source")]
result = oraObject.getSource(db)
elif oraObject.getType() == "INDEX":
header = [_("Property"), _("Value")]
result = oraObject.getProperties(db)
elif oraObject.getType() == "INDEX PARTITION":
raise PysqlNotImplemented()
elif oraObject.getType() == "INDEXTYPE":
raise PysqlNotImplemented()
elif oraObject.getType() == "JAVA CLASS":
raise PysqlNotImplemented()
elif oraObject.getType() == "JAVA DATA":
raise PysqlNotImplemented()
elif oraObject.getType() == "JAVA RESOURCE":
raise PysqlNotImplemented()
elif oraObject.getType() == "LIBRARY":
raise PysqlNotImplemented()
elif oraObject.getType() == "OPERATOR":
raise PysqlNotImplemented()
elif oraObject.getType() == "PACKAGE BODY":
raise PysqlNotImplemented()
elif oraObject.getType() == "SEQUENCE":
header = [_("Last"), _("Min"), _("Max"), _("Step")]
result = [[oraObject.getLast(db), oraObject.getMin(db), oraObject.getMax(db), oraObject.getStep(db)]]
elif oraObject.getType() == "TABLE PARTITION":
raise PysqlNotImplemented()
elif oraObject.getType() == "TABLESPACE":
oraObject.updateDatafileList(db)
header = [_("Datafile"), _("Size (%s)") % unit.upper(), _("Free (%s)") % unit.upper(), _("%Used")]
result = [[]]
totalSize = 0
totalFree = 0
totalUsed = 0
for datafile in oraObject.getDatafiles():
name = datafile.getName()
size = convert(datafile.getAllocatedBytes(db), unit)
free = convert(datafile.getFreeBytes(db), unit)
if size != 0:
used = 100 - float(100 * free) / size
else:
used = 0
result.append([name, round(size, 2), round(free, 2), round(used, 2)])
totalSize += size
totalFree += free
if totalSize != 0:
totalUsed = 100 - float(100 * totalFree) / totalSize
else:
totalUsed = 0
if len(oraObject.getDatafiles()) > 1:
result[0] = ["> " + _("TOTAL"), round(totalSize, 2), round(totalFree, 2), round(totalUsed, 2)]
else:
result.pop(0)
elif oraObject.getType() == "TRIGGER":
oraObject.updateTable(db)
header = [_("Status"), _("Table"), _("Type"), _("Event"), _("Body")]
result = [[oraObject.getStatus(db), oraObject.getTable(db).getFullName(),
oraObject.getTriggerType(db), oraObject.getEvent(db),
oraObject.getBody(db).replace("\n", " ")]]
elif oraObject.getType() == "USER":
oraObject.updateTablespaceList(db)
header = [_("Tablespace"), _("Default?"), _("#Tables"), _("#Indexes")]
result = [[]]
totalTables = 0
totalIndexes = 0
defaultTbs = oraObject.getDefaultTablespace(db)
#tempTbs = oraObject.getTempTablespace(db)
for tablespace in oraObject.getTablespaces():
name = tablespace.getName()
nbTables = oraObject.getNbTables(db, tablespace=tablespace.getName())
nbIndexes = oraObject.getNbIndexes(db, tablespace=tablespace.getName())
if name == defaultTbs:
defstr = u"*"
else:
defstr = u""
result.append([name, defstr, nbTables, nbIndexes])
totalTables += nbTables
totalIndexes += nbIndexes
if len(oraObject.getTablespaces()) > 1:
result[0] = ["> " + _("TOTAL"), u"", totalTables, totalIndexes]
else:
result.pop(0)
else:
raise PysqlException(_("Type not handled: %s") % oraObject.getType())
return (header, result)
def pkgTree(db, packageName):
"""Creates the call tree of internal package functions and procedures"""
# Tries to import pydot module
try:
from pydot import find_graphviz, Dot, Edge, Node
except ImportError:
message = _("Function not available because pydot module is not installed.\n\t")
message += _("Go to http://dkbza.org/pydot.html to get it.")
raise PysqlException(message)
# Reads conf
conf = PysqlConf.getConfig()
format = conf.get("graph_format") # Output format of the picture
fontname = conf.get("graph_fontname") # Font used for functions names
fontsize = conf.get("graph_fontsize") # Font size for functions names
fontcolor = conf.get("graph_fontcolor") # Color of functions names
# Gets picture generator
prog = getProg(find_graphviz(), conf.get("graph_program"), "fdp")
package = OraObject(objectName=packageName)
package.guessInfos(db)
graph = Dot(overlap="false", splines="true")
# Lists of function or procedure
verbs = []
# Tries to resolve synonym and describe the target
#TODO: factorise this code!!
if package.getType() == "SYNONYM":
package = package.getTarget(db)
if package.getType() == "SYNONYM":
raise PysqlException(_("Too much synonym recursion"))
if package.getType() not in ("PACKAGE", "PACKAGE BODY"):
raise PysqlException(_("This is not a package or package not found"))
# Gets package body content
package.setType(u"PACKAGE BODY")
print CYAN + _("Extracting package source...") + RESET
content = package.getSQLAsList(db)
# Removes comments
print CYAN + _("Parsing source and building graph...") + RESET
newContent = []
comment = False
for line in content:
line, comment = removeComment(line, comment)
newContent.append(line)
content = newContent
# Gets procedures and functions
for line in content:
result = re.match("\s*(FUNCTION|PROCEDURE)\s+(.+?)[\s|\(]+", line, re.I)
if result:
verbs.append(re.escape(result.group(2)))
graph.add_node(Node(result.group(2).upper(), shape="box", label=result.group(2).upper(), \
fontsize=str(fontsize), fontname=fontname, fontcolor=fontcolor))
if not verbs:
raise PysqlException(_("This package does not have any readable function or procedure"))
verbs = "|".join(verbs)
# Gets call of functions/procedure inside each other
currentVerb = ""
for line in content:
# Doesn't pay attention to end lines
if re.match("\s*END.*;", line, re.I):
continue
# Marks the function/procedure we are parsing
result = re.match("\s*(FUNCTION|PROCEDURE)\s+(.+?)[\s|\(]+", line, re.I)
if result:
currentVerb = result.group(2)
continue # else we get a circular reference below ;-)
result = re.match(".*\s(%s).*" % verbs, line, re.I)
if result:
if graph.get_edge(currentVerb.upper(), result.group(1).upper()) is None:
graph.add_edge(Edge(src=currentVerb.upper(), dst=result.group(1).upper()))
filename = package.getName() + "_dep." + format
generateImage(graph, filename, prog, format)
viewImage(filename)
def datamodel(db, userName, tableFilter=None, withColumns=True):
"""Extracts the datamodel of the current user as a picture
The generation of the picture is powered by Graphviz (http://www.graphviz.org)
through the PyDot API (http://www.dkbza.org/pydot.html)
@param db: pysql db connection
@param userName: schema to be extracted
@param tableFilter: filter pattern (in pysql extended syntax to extract only some tables (None means all)
@param withColumns: Indicate whether columns are included or not in datamodel picture
"""
# Tries to import pydot module
try:
from pydot import find_graphviz, Dot, Edge, Node
except ImportError:
message = _("Function not available because pydot module is not installed.\n\t")
message += _("Go to http://dkbza.org/pydot.html to get it.")
raise PysqlException(message)
# Reads conf
conf = PysqlConf.getConfig()
format = conf.get("graph_format") # Output format of the picture
fontname = conf.get("graph_fontname") # Font used for table names
fontsize = conf.get("graph_fontsize") # Font size for table names
fontcolor = conf.get("graph_fontcolor") # Color of table and column names
tablecolor = conf.get("graph_tablecolor") # Color of tables
bordercolor = conf.get("graph_bordercolor") # Color of tables borders
linkcolor = conf.get("graph_linkcolor") # Color of links between tables
linklabel = conf.get("graph_linklabel") # Display constraints name or not
# Gets picture generator
prog = getProg(find_graphviz(), conf.get("graph_program"), "fdp")
graph = Dot(splines="compound")
# Tables, columns and constraints (temporary and external tables are excluded. So are TOAD tables)
if tableFilter:
whereClause = generateWhere("table_name", tableFilter)
else:
whereClause = "1=1"
tables = db.executeAll(datamodelSql["tablesFromOwner"] % (userName, whereClause))
nbTables = len(tables)
if nbTables == 0:
raise PysqlException(_("No table found. Your filter clause is too restrictive or the schema is empty"))
tableList = ", ".join(["'%s'" % table[0] for table in tables]) # Table list formated to be used in SQL query
print CYAN + _("Extracting %d tables... ") % nbTables + RESET,
current = 0
for table in tables:
tableName = table[0]
content = """<<TABLE BORDER="0" CELLBORDER="1" CELLSPACING="0">"""
content += """\n<TR><TD PORT="%s">""" % tableName
content += """<FONT FACE="%s" POINT-SIZE="%f" COLOR="%s">""" % (fontname, fontsize, fontcolor)
content += tableName
content += "</FONT></TD></TR>"
if withColumns:
columns = db.executeAll(datamodelSql["columnsFromOwnerAndTable"], [userName, tableName])
for column in columns:
columnName = column[0]
columnType = column[1]
content += """\n<TR><TD ALIGN="LEFT" PORT="%s_%s">""" % (tableName, columnName)
content += """<FONT FACE="%s" POINT-SIZE="%f" COLOR="%s">""" % \
(fontname, fontsize - 2, fontcolor)
if column[2] is None: # Normal field
content += " "
else: # Primary key field
content += "PK%d" % int(column[2])
content += " %s (%s)" % (columnName, columnType)
content += "</FONT></TD></TR>"
content += "\n</TABLE>>"
graph.add_node(Node(tableName, shape="none", label=content, style="filled", \
fillcolor=tablecolor, color=bordercolor))
current += 1
sys.stdout.write("\b\b\b\b\b%4.1f%%" % round(100 * float(current) / nbTables, 1))
sys.stdout.flush()
print
# Links between tables (foreign key -> primary key)
# Only extract links from considered tables
links = db.executeAll(datamodelSql["constraintsFromOwner"] % (userName, tableList, tableList))
nbLinks = len(links)
print (CYAN + _("Extracting %d links... ") % nbLinks + RESET),
current = 0
for link in links:
if linklabel == "yes":
graph.add_edge(Edge(src=link[1], dst=link[2], color=linkcolor))
else:
graph.add_edge(Edge(src=link[1], dst=link[2], label=link[0], color=linkcolor, \
fontcolor=linkcolor, fontname=fontname, fontsize=str(fontsize - 3)))
current += 1
sys.stdout.write("\b\b\b\b\b%4.1f%%" % round(100 * float(current) / nbLinks, 1))
print
filename = db.getDSN() + "_" + userName + "." + format
generateImage(graph, filename, prog, format)
viewImage(filename)
def diskusage(db, userName, withIndexes=False, percent=True):
"""Extracts the physical storage of the current user as a picture based on Oracle statistics
The generation of the picture is powered by Graphviz (http://www.graphviz.org)
through the PyDot API (http://www.dkbza.org/pydot.html)
"""
# Tries to import pydot module
try:
from pydot import find_graphviz, Dot, Subgraph, Cluster, Edge, Node
except ImportError:
message = _("Function not available because pydot module is not installed.\n\t")
message += _("Go to http://dkbza.org/pydot.html to get it.")
raise PysqlException(message)
# Reads conf
conf = PysqlConf.getConfig()
unit = conf.get("unit") # Unit used to format data
format = conf.get("graph_format") # Output format of the picture
fontname = conf.get("graph_fontname") # Font used for table names
fontsize = conf.get("graph_fontsize") # Font size for table names
fontcolor = conf.get("graph_fontcolor") # Color of table and column names
tablecolor = conf.get("graph_tablecolor") # Color of tables
indexcolor = conf.get("graph_indexcolor") # Color of indexes
bordercolor = conf.get("graph_bordercolor") # Color of borders
# Gets picture generator
prog = getProg(find_graphviz(), conf.get("graph_program"), "fdp")
# First step: objects library building
# Tablespaces
if userName == db.getUsername().upper():
tablespaces = db.executeAll(diskusageSql["Tablespaces"])
else:
tablespaces = db.executeAll(diskusageSql["TablespacesFromOwner"], [userName])
tbsBytes = 0
tbsList = []
for tablespace in tablespaces:
tablespaceName = unicode(tablespace[0])
# Tables from current tablespace
if userName == db.getUsername().upper():
tables = db.executeAll(diskusageSql["TablesFromTbs"], [tablespaceName])
else:
tables = db.executeAll(diskusageSql["TablesFromOwnerAndTbs"], [userName, tablespaceName])
tabList = []
print CYAN + _("Extracting %3d tables from tablespace %s") % (len(tables), tablespaceName) + RESET
for table in tables:
tableName = table[0]
if table[1] is None:
print RED + _("""Warning: table "%s" removed because no statistics have been found""") \
% (tablespaceName + "/" + tableName) + RESET
continue
if table[1] == 0:
print RED + _("""Warning: table "%s" removed because it is empty""") \
% (tablespaceName + "/" + tableName) + RESET
continue
numRows = int(table[1])
avgRowLen = float(table[2])
bytes = int(table[3])
tbsBytes += bytes
tabList += [[tableName, bytes, numRows, avgRowLen]]
if withIndexes:
# Indexes from current tablespace
if userName == db.getUsername().upper():
indexes = db.executeAll(diskusageSql["IndexesFromTbs"], [tablespaceName])
else:
indexes = db.executeAll(diskusageSql["IndexesFromOwnerAndTbs"], [userName, tablespaceName])
idxList = []
print CYAN + _("Extracting %3d indexes from tablespace %s") % (len(indexes), tablespaceName) + RESET
for index in indexes:
indexName = index[0]
if index[1] is None:
print RED + _("""Warning: index "%s" removed because no statistics have been found""") \
% (tablespaceName + "/" + indexName) + RESET
continue
if index[1] == 0:
print RED + _("""Warning: index "%s" removed because it is empty""") \
% (tablespaceName + "/" + indexName) + RESET
continue
numRows = int(index[1])
distinctKeys = int(index[2])
bytes = int(index[3])
tabName = str(index[4])
tbsBytes += bytes
idxList += [[indexName, bytes, numRows, distinctKeys, tabName]]
else:
print CYAN + _("Not extracting indexes from tablespace %s (ignored)") % (tablespaceName) + RESET
idxList = []
tbsList += [[tablespaceName, tbsBytes, tabList, idxList]]
# Second step: objects drawing
graph = Dot(label=userName, overlap="false", splines="true")
for tbs in tbsList:
tbsName = tbs[0]
tbsBytes = tbs[1]
tabList = tbs[2]
idxList = tbs[3]
subGraph = Subgraph("cluster_" + tbsName, bgcolor="palegreen", \
fontname=fontname, fontsize=str(fontsize - 1), \
label="%s\\n(%d %s)" % (tbsName, convert(tbsBytes, unit), unit.upper()))
graph.add_subgraph(subGraph)
print CYAN + _("Displaying %3d tables for tablespace %s") % (len(tabList), tbsName) + RESET
for tab in tabList:
name = tab[0]
bytes = tab[1]
numRows = tab[2] # unused
avgRowLen = tab[3] # unused
# Mathematics at work
width = 0.2
height = 0.2
if percent:
height += 10 * round(float(bytes) / tbsBytes, 4)
label = "%s\\n(%.2f %s)" % (name, round(100 * float(bytes) / tbsBytes, 2), "%")
else:
height += round(sqrt(bytes) / 8192, 3)
width += round(sqrt(bytes) / 8192, 3)
label = "%s\\n(%3d %s)" % (name, convert(bytes, unit), unit.upper())
subGraph.add_node(Node(name, label=label, shape="box", style="filled", \
color="none", fillcolor=tablecolor, \
fontname=fontname, fontcolor=fontcolor, fixedsize="false", \
fontsize=str(fontsize - 2 - floor((len(label) - 7) / 15)), \
nodesep="0.01", height=str(height), width=str(max(width, 1))))
print CYAN + _("Displaying %3d indexes for tablespace %s") % (len(idxList), tbsName) + RESET
for idx in idxList:
name = idx[0]
bytes = idx[1]
numRows = idx[2] # unused
distinctKeys = idx[3] # unused
tabName = idx[4] # unused
# Mathematics at work again)
width = 0.2
height = 0.2
if percent:
height += 10 * round(float(bytes) / tbsBytes, 4)
label = "%s\\n(%.2f %s)" % (name, round(100 * float(bytes) / tbsBytes, 2), "%")
else:
height += round(sqrt(bytes) / 8192, 3)
width += round(sqrt(bytes) / 8192, 3)
label = "%s\\n(%3d %s)" % (name, convert(bytes, unit), unit.upper())
subGraph.add_node(Node(name, label=label, shape="box", style="filled", \
color="none", fillcolor=indexcolor, \
fontname=fontname, fontcolor=fontcolor, fixedsize="false", \
fontsize=str(fontsize - 2 - floor((len(label) - 7) / 15)), \
nodesep="0.01", height=str(height), width=str(max(width, 1))))
#Moving index near by its table (unused because it widens the graph)
#subGraph.add_edge(Edge(src=name, dst=tabName, constraint="false", style="invis"))
filename = "du_" + userName + "." + format
generateImage(graph, filename, prog, format)
viewImage(filename)
def dependencies(db, objectName, direction, maxDepth, maxNodes):
"""Displays object dependencies as a picture
The generation of the picture is powered by Graphviz (http://www.graphviz.org)
through the PyDot API (http://www.dkbza.org/pydot.html)
@param db: pysql db connection
@param objectName: name of the oracle object on which dependancies are computed
@param direction: direction of the dependancy graph. Can be "onto", "from" or "both"
@param maxDepth: Override default maxDepth value. If None, use default value
@param maxNodes: Override default maxNodes value. If None, use default value
"""
# Tries to import pydot module
try:
from pydot import find_graphviz, Dot, Edge, Node
except ImportError:
message = _("Function not available because pydot module is not installed.\n\t")
message += _("Go to http://dkbza.org/pydot.html to get it.")
raise PysqlException(message)
# Reads conf
conf = PysqlConf.getConfig()
format = conf.get("graph_format") # Output format of the picture
fontname = conf.get("graph_fontname") # Font used for object names
fontsize = conf.get("graph_fontsize") # Font size for object names
# Gets picture generator
prog = getProg(find_graphviz(), conf.get("graph_program"), "dot")
graph = Dot(overlap="false", splines="true", rankdir="TB")
if direction == "onto" or direction == "from":
dirList = [direction]
elif direction == "both":
dirList = ["onto", "from"]
else:
dirList = []
for currentDir in dirList:
depth = 0
objectList = [OraObject(objectName=objectName)]
objectList[0].guessInfos(db)
objectOwner = objectList[0].getOwner()
objectName = objectList[0].getName()
objectType = objectList[0].getType()
label = objectOwner + "." + objectName + "\\n(" + objectType + ")"
graph.add_node(Node(objectName, label=label, fontname=fontname, fontsize=str(fontsize), shape="diamond"))
nodeList = [objectName]
edgeList = []
nextObjectList = []
while objectList != [] and depth <= maxDepth and len(nodeList) <= maxNodes:
depth += 1
for currentObject in objectList:
currentObjectOwner = currentObject.getOwner()
currentObjectName = currentObject.getName()
if currentDir == "onto":
# Objects referencing the the current object
result = db.executeAll(dependenciesSql["refOnFromOwnerAndName"], \
[currentObjectOwner, currentObjectName])
elif currentDir == "from":
# Objects referenced by the the current object
result = db.executeAll(dependenciesSql["refByFromOwnerAndName"], \
[currentObjectOwner, currentObjectName])
refObjectList = [OraObject(objectOwner=i[0], objectName=i[1]) for i in result]
for currentRefObject in refObjectList:
currentRefObject.guessInfos(db)
currentRefObjectOwner = currentRefObject.getOwner()
currentRefObjectName = currentRefObject.getName()
currentRefObjectType = currentRefObject.getType()
if not currentRefObjectName in nodeList:
nodeList.append(currentRefObjectName)
# Object shape
if currentRefObjectType in ("TABLE", "VIEW", "SEQUENCE"):
shape = "box"
elif currentRefObjectType in ("PACKAGE", "FUNCTION", "PROCEDURE", "TRIGGER"):
shape = "ellipse"
else:
shape = "none"
# Object label
if currentRefObjectOwner == db.getUsername().upper():
label = currentRefObjectName
else:
label = currentRefObjectOwner + "." + currentRefObjectName
label += "\\n(" + currentRefObjectType + ")"
# Adding object to graph
graph.add_node(Node(currentRefObjectName, label=label, fontname=fontname, \
fontsize=str(fontsize), shape=shape))
if not [currentObjectName, currentRefObjectName] in edgeList:
if currentDir == "onto":
edgeList.append([currentObjectName, currentRefObjectName])
graph.add_edge(Edge(dst=currentObjectName, src=currentRefObjectName, \
color="red"))
elif currentDir == "from":
edgeList.append([currentObjectName, currentRefObjectName])
graph.add_edge(Edge(src=currentObjectName, dst=currentRefObjectName, \
color="darkgreen"))
nextObjectList += refObjectList
objectList = nextObjectList
nextObjectList = []
if len(nodeList) > maxNodes:
print RED + _("Warning: reach max node, references lookup stopped on direction %s") % currentDir + RESET
if depth > maxDepth:
print RED + _("Warning: reach max recursion limit, references lookup stopped on direction %s") % currentDir + RESET
filename = "dep_" + objectOwner + "." + objectName + "." + format
generateImage(graph, filename, prog, format)
viewImage(filename)
