# -*- coding: utf8 -*- ''' TABLE-TREE MODULE for DB table-structure representation ************************** *** TABLE-TREE MODULE *** for *** DB table-structure *** representation ************************** $RCSfile: tablegraph.py,v $ $Revision: 1248 $ $Author: j.holetschek $ $Date: 2012-12-10 12:44:40 +0100 (Mo, 10. Dez 2012) $ Class representing a database structure in the form of a graph-tree. It knows about the tables, their relations. The function "getJoinTableList" returns a list of tables and their relation defined by a relationObject to produce dynamic joins of tables. ''' from string import join from biocase.wrapper.graph.graph import tree, graph, CircleExistingError from biocase.wrapper.graph.matrix import ColNotExistingError from biocase.wrapper.errorclasses import CMFError, MissingRootTableError, TableConfigError, TableConfigCircleError from biocase.tools.various_functions import intersects import logging log = logging.getLogger("pywrapper.graph") # ----------------------------------------------------------- # ----------------------------------------------------------- # ----------------------------------------------------------- class foreignKeyClass: '''Class to store all data about a foreign key potentially including several attributes.''' def __init__(self): self.target = None self.attrList = [] # list of DBAttrObjs def addKey(self, dbAttrObj): self.attrList.append(dbAttrObj) def getLastKey(self): if len(self.attrList) > 0: return self.attrList[-1] else: return None def __repr__(self): return "(ForeignKeyObject: target=%s, attr=%s)" %(self.target, str(self.attrList)) class tableAliasClass: '''This class holds all information regarding one table in a DB. self.alias = aliasname or None self.table = tablename or None self.pk = pks, list of DBAttrObjs self.fks = fks, list of foreignKeyClass objects ''' def __init__(self): self.alias = None self.table = None self.pk = [] # list of DBAttrObjs self.fks = [] # list of foreignKeyClass objects def addFK(self, target=None, DBAttrObjList=[]): fkObj = foreignKeyClass() fkObj.target = target fkObj.attrList = DBAttrObjList self.fks.append(fkObj) def getLastFk(self): if len(self.fks)>0: return self.fks[-1] else: return None def __repr__(self): return "(TableAliasObject: alias=%s, table=%s, pks=%s, fks=%s)" %(self.alias, self.table, str(self.pk), str(self.fks)) # ----------------------------------------------------------- # ----------------------------------------------------------- # ----------------------------------------------------------- class tableGraphClass: '''This class holds the table structure of a DB in a easy usable way to create dynamic SQL. It relies on one root table that is not defined in the table configuration, but in every CMF file. The DB structure is represented as a tree, with list of lists whereby a single table is a tuple made of 3 values: [0]=table-alias name, [1]=list of child tuples, [2]=LOP/COP object defining the relation to the parental tuple (for building joins). If the DB table structure is a closed graph, different spanning trees are possible and an error is produced, as no circle relations are allowed in the DB setup.''' # ------------------------------------ def __init__(self): # purely from PSF self.graph = None # the original configured graph including all possible unconnected tables, circles, etc. self.alias2tableObj = {} # dict key=alias, value=table info object self.alias2tablename = {} # simple dict aliasname -> tablename # ----- OUTPUT ----- def __repr__(self): '''return simple string output of the object and the graph.''' return "GRAPH: %s +++ ALIAS2TABLE: %s"%(str(self.graph), str(self.alias2tablename) ) # ------------------------------------ def getTableAliasList(self): '''Returns a list of all table alias objects or None.''' aliases = self.alias2tableObj.keys() aliases.sort() retlist = [] for a in aliases: retlist.append(self.alias2tableObj[a]) return retlist # ------------------------------------ def getTableAliasObj(self, alias): '''Returns the table alias object for a given alias or None.''' return self.alias2tableObj.get(alias, None) # ------------------------------------ def getTableAliasObjects(self): '''Returns all table alias objects.''' return self.alias2tableObj.values() # ------------------------------------ def getTablePKsDict(self): '''return a dictionary of all tablealiases with their primary keys as a list of DBAttrObjs.''' pkDict={} for tabInfo in self.alias2tableObj.values(): pkDict[tabInfo.alias] = tabInfo.pk return pkDict # ------------------------------------ def delAlias(self, alias): '''Remove a tablealias from the object. Currently the graph is not changed, just the dictionaries are modified. SO the written PSF file does not contain the alias anymore.''' del self.alias2tableObj[alias] del self.alias2tablename[alias] # ------------------------------------ def buildGraph(self, tableAliasList): '''Create the graph matrix from a list of tableAliasObjects.''' graphObj = graph() # add nodes (the aliases) to graph graphObj.addNodes([tao.alias for tao in tableAliasList]) # first store table alias info self.alias2tableObj = {} self.alias2tablename = {} for tao in tableAliasList: self.alias2tableObj[tao.alias] = tao self.alias2tablename[tao.alias] = tao.table # add edges to graph for tao in tableAliasList: for fkObj in tao.fks: relObj = self._makeWhereObj( fkObj.attrList, self.alias2tableObj[fkObj.target].pk ) graphObj.addEdge((tao.alias, fkObj.target), relObj) self.graph = graphObj def getGraphMatrix(self): '''Method returning a clones matrix of the graph''' return self.graph def getGraphEdgesList(self): '''Method returning a list of node-tuples for the graph as its edges''' return self.graph.listEdges().values() def getGraphIsolatedNodesList(self): '''Method returning a list of node names which are isolated, that is they dont have any edge connected to them.''' return self.graph.listIsolatedNodes() # ------------------------------------ def _makeWhereObj(self, fks, pk): '''translates a relation (fks & pks DBAttrObj lists) into a whereobject .''' from biocase.wrapper.sql.operators import logicalOperator, comparisonOperator clop = None if len(fks) == len(pk): if len(fks) > 1: clop = comparisonOperator('equals', fks[0], pk[0]) for i in range(len(fks)-1): cop = comparisonOperator('equals', fks[i+1], pk[i+1] ) clop = logicalOperator('and', [clop, cop]) elif len(fks) == 1: clop = comparisonOperator('equals', fks[0], pk[0]) else: # no relational where object! # maybe intermediate status when newly configuring? clop = None return clop # ------------------------------------ def clearGraph(self): '''Remove nonsense data from tablegraph.''' for alias, tao in self.alias2tableObj.items(): # clear whole table if not len(tao.pk) > 0: del self.alias2tableObj[alias] else: # clear FKs tmp = [] for fkObj in tao.fks: # fk attributes defined and target pointing to an existing alias? if len(fkObj.attrList) > 0 and self.alias2tableObj.has_key(fkObj.target): tmp.append(fkObj) tao.fks = tmp # ------------------------------------ def circlesExist(self): '''Returns 1 if the tablegraph contains circular structures.''' return self.graph.hasClosedWalk() # ------------------------------------ # ------------------------------------ # METHODS FOR THE TREE ONLY !!! # ------------------------------------ # ------------------------------------ def getTableTree(self, rootTableAliasName, staticTableAliasList=[]): '''function to create and return a tabletree object with - a given root table-alias name and - an optional list of static table aliases. Throws an error if a circular graph was given. ''' tableTreeObj = tableTreeClass() self._setRoot(tableTreeObj, rootTableAliasName) for staticTA in staticTableAliasList: try: self._addStaticTable(tableTreeObj, staticTA) except CMFError: pass return tableTreeObj # ------------------------------------ def _setRoot(self, tableTreeObj, rootTableAlias): '''function to initialize a tabletree object with a given root table-alias name. Throws an error if a circular graph was given. ''' # test supplied aliases if rootTableAlias == None: raise MissingRootTableError() elif self.getTableAliasObj(rootTableAlias) == None: log.warn("Root table %s is not listed in the provider configuration."%(rootTableAlias)) raise TableConfigError() # generate a tabletree object try: tableTreeObj.tree = self.graph.makeTree(rootTableAlias) tableTreeObj.rootTableAlias = rootTableAlias except CircleExistingError: tableTreeObj.rootTableAlias = None raise TableConfigCircleError(rootTableAlias) if len(self.alias2tableObj[rootTableAlias].pk) > 1: log.error("Your DB root table %s contains more than one primary key. This is not allowed. Please assign a single, unique primary key for your 'root' records" % (rootTableAlias)) raise CMFError() else: tableTreeObj.rootTableAliasPK = self.alias2tableObj[rootTableAlias].pk[0] # ------------------------------------ def _addStaticTable(self, tableTreeObj, st): '''Takes a table alias and tries to create a subtree of the graph with this table as its root. Then adds this tree to the list of static tables.''' try: tree = self.graph.makeTree(st) except CircleExistingError: raise TableConfigCircleError(st) except ColNotExistingError: log.warn("The static table '%s' does not exist in your providersetup."%st) raise CMFError() # check if table aliases in this tree are unique to this tree ! oldTables = tableTreeObj.tree.listNodes() for t in tableTreeObj.staticTables.values(): oldTables += t.listNodes() shared = intersects(oldTables, tree.listNodes()) if len(shared) > 0: # table used before ! log.warn("The following tables of your static table tree '%s' are existing at least twice and are ignored. Please reconfigure your providersetup by adding a new alias for these tables: %s"%(unicode(st), "'"+join(shared, "', '")+"'" )) raise CMFError() tableTreeObj.staticTables[st] = tree # ----------------------------------------------------------- # ----------------------------------------------------------- # ----------------------------------------------------------- class tableTreeClass: '''This class holds the table structure of a DB for a specific CMF in a easy usable way to create dynamic SQL. It relies on one root table that is not defined in the table configuration, but in every CMF file. The DB structure is represented as a tree, with list of lists whereby a single table is a tuple made of 3 values: [0]=table-alias name, [1]=list of child tuples, [2]=LOP/COP object defining the relation to the parental tuple (for building joins). ''' # ------------------------------------ def __init__(self): # CMF specific ... self.tree = None # the rooted tree graph self.rootTableAlias = None # the root tablealias name of the rooted tree graph self.rootTableAliasPK= None # the primary key DBAttrObj of the root table self.staticTables = {} # dict tablealias -> tree object # ----- OUTPUT ----- def __repr__(self): '''return simple string output of the object and the graph.''' return "ROOT: %s TREE: %s +++ STATIC TABLE TREES: %s"%(str(self.rootTableAlias), str(self.tree), str(self.staticTables) ) # ------------------------------------ def getJoinTableListOfLists(self, tables=[]): '''returns a list of table-lists, that have to be joined via a full outer join. That is the root table tree and all relevant static table tree dericed lists. ''' TreeJoinLists = [] # check if wanted table exists in any static table tree: for tree in self.staticTables.values(): intersection = intersects(tables, tree.listNodes()) if len(intersection) > 0: TreeJoinLists.append(self.getJoinTableList(intersection, tree)) for tbl in intersection: tables.remove(tbl) # add table list for non static tables: TreeJoinLists += [self.getJoinTableList(tables, self.tree)] return TreeJoinLists # ------------------------------------ def getJoinTableList(self, tables=[], tree=None): '''returns a list of (table-alias names, whereObj) tuples to be used in a join chain. Pass any number of table-alias names to this funtion.''' if tree == None: return [] # get list of depth-ordered, unique nodes: nodes = tree.listOrderedNodesConnectedViaRoot(tables) # add relation objects (edge-values) to list: result=[] for i in range(len(nodes)): node = nodes[i] if i == 0: result.append( (node,None) ) else: # only one of the parental nodes is connected to this node(no circles exist) upperNodes=nodes[0:i] for edge in tree.listAdjacentEdges(node): if edge[1] in upperNodes: # this edge connects node to parents. # test if left tablealias in where object is this node. swap if otherwise whereObj = tree.getEdge(edge[0])[0] if whereObj.arg1.table <> node: log.debug("Swapped COP args at node %s to build correct Left Joins"%(node)) tmpArg = whereObj.arg1 whereObj.arg1 = whereObj.arg2 whereObj.arg2 = tmpArg result.append( (node, whereObj) ) break return result