def test_result(self):
for answer, question in (("table like 'lala'", "lala"),
("table like 'lala%'", "lala%"),
("table like 'lala%' or table like 'loulou'", "lala% or loulou"),
("table like 'lala%' or table not like 'loulou'", "lala% or !loulou"),
("( table like 'lala%' or table like 'loulou' )", "(lala% or loulou)"),
("( table like 'lala%' or table like 'loulou' ) and table like 'lala'",
"(lala% or loulou) and lala"),
("( table like 'lala%' or table like 'loulou' ) and table like 'lala'",
"( lala% or loulou ) and lala"),
("( table like 'lala%' or table like 'loulou' ) and table like 'lala'",
"(lala% OR loulou ) aNd lala")):
self.assertEqual(answer,
pysqlhelpers.generateWhere("table", question))
def searchObject(db, objectType, objectName, objectOwner):
"""Searches for Oracle objects by name with wildcard if needed"""
result = {}
objectType = objectType.lower()
try:
sql = searchObjectSql[objectType][0]
keyword = searchObjectSql[objectType][1]
if len(objectName.split()) == 1:
# Single word search. Just add wildcart % if needed
whereClause = "%s like '%s'" % (keyword, addWildCardIfNeeded(objectName))
else:
whereClause = generateWhere(keyword, objectName)
objects = db.executeAll(sql % (whereClause, objectOwner, keyword))
except KeyError:
raise PysqlException(_("SQL entry not defined for searchObjectSql: %s") % objectType)
# Returns a dict with key=schemaNAme and Value=list of object
for (owner, name) in objects:
if result.has_key(owner):
result[owner].append(name)
else:
result[owner] = [name]
return result
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)