#!C:\Programme2\Python26\python.exe # -*- coding: UTF-8 -*- ''' $RCSfile: dbstructure.py,v $ $Revision$ $Author: markus $ $Date$ The BioCASE querytool ''' import os, sys, string, time # ***** include the biocase.lib directory in the python sys path for importing ***** execfile( os.path.abspath( os.path.join( os.path.dirname( __file__ ), os.pardir, os.pardir, 'lib', 'biocase', 'adjustpath.py' ) )) execfile( os.path.abspath( os.path.join( os.path.dirname( __file__ ), os.pardir, os.pardir, 'lib', 'biocase', 'appinit.py' ) )) from biocase.configtool.general import * from biocase.cmfinfo import getCMFTemplateList from biocase.wrapper.graph.tablegraph import tableAliasClass from biocase.wrapper.typeconverter import DBAttributeClass from biocase.tools.various_functions import flatten DB_ATTR_DATA_TYPES_HASH = {'text':'text','integer':'int','float':'float','date':'date'} # ------------------------------------------------------------------------------------ def displayMetaForm(tmpl, psfObj): pass # ------------------------------------------------------------------------------------ def displayNoMetaForm(tmpl, psfObj): # enter tablealias form-data into template newTypesList = genOptionList(DB_ATTR_DATA_TYPES_HASH, 'newtype_opt', 'newtype_val', 'int') newAliasList = genOptionList([str(ta.alias) for ta in psfObj.getTableAliasList()], 'newalias_opt', 'newalias_val', '--none--') i = range(2000) i.reverse() ti = range(500) ti.reverse() expandList = [ { 'tablelist.tables': getTableListOps(str(ta.table)), 't_num':str(ti.pop()), 't_alias':str(ta.alias), 't_name':str(ta.table), 'tablelist.newtypelist':newTypesList, 'tablelist.pklist':[ {'t_pk_num':str(i.pop()), 't_pk_attr':str(pk.attr), 'tablelist.pklist.aliastypelist':genOptionList(DB_ATTR_DATA_TYPES_HASH, 't_pk_type_opt', 't_pk_type_val', pk.type), 'tablelist.pklist.columns':getColumnListOps(str(ta.table), str(pk.attr), [_pk.attr for _pk in ta.pk], False) #, 'foo': pk.attr } for pk in ta.pk ], 'tablelist.newPrimaryKey':getNewPrimaryKeyOps(str(ta.table), [_pk.attr for _pk in ta.pk]), 'tablelist.newfklist':filter(lambda o: str(o.get('newalias_opt')) != str(ta.alias) and None == find(lambda a: str(a.target) == str(o.get('newalias_opt')), ta.fks), newAliasList), 'tablelist.fklist':[ { #'tablelist.fklist.newFkColumn':getPrimaryKeysFromAliasOps(find(lambda tableAlias: tableAlias.alias == fkObj.target, psfObj.getTableAliasList())), 'tablelist.fklist.newFkColumn':getColumnListOps(str(ta.table), "", [fk.attr for fk in fkObj.attrList], True), 't_fks_num':str(i.pop()), 't_fk_target':str(fkObj.target), 'tablelist.fklist.newtypelist':newTypesList, 'tablelist.fklist.fk2list':[ {'t_fk_num':str(i.pop()), 't_fk_attr':str(attrObj.attr), 'tablelist.fklist.fk2list.fktypelist': genOptionList(DB_ATTR_DATA_TYPES_HASH, 't_fk_type_opt', 't_fk_type_val', attrObj.type) } for attrObj in fkObj.attrList ] } for fkObj in ta.fks #if len(fkObj.attrList) > 0 and fkObj.target is not None ] } for ta in psfObj.getTableAliasList() #if len(ta.pk) > 0 ] #log.error(str(expandList)) #log.error(str(type(expandList))) tmpl.expand('Content', 'tablelist', expandList) #tmpl.expand('Content', 'tablelist', foo) # create graph aliases = psfObj.getTableAliasList() #log.error("aliases: " + str(aliases)) try: psfObj.buildGraph(aliases) except: #pass #psfStatus = "Inconsistent." log.error(str(sys.exc_info()[0])) log.error(str(sys.exc_info()[1])) log.error(str(sys.exc_info()[2])) return psfObj # ------------------------------------------------------------------------------------ def parseMetaForm(form, psfObj): pass # ------------------------------------------------------------------------------------ def parseNoMetaForm(form, psfObj): global message, psfStatus #log.error("hepp") f_keys = form.keys() f_keys.sort() aliases = {} entryToDelete = -1 # DBAttributeClass for f in f_keys: val = form[f].value #log.error("f= " + str(f)) #log.error("val= " + str(val)) if f == '__deleteEntry': #log.error("Hepp, deleteEntry") entryToDelete = val if f[:3] == '__t' and (val <> '--new--' and val <> '--none--' and val <> '--select--'): # table form part if f == '__taliasnew': # new alias tao = tableAliasClass() tao.alias = form[f].value aliases['new'] = tao elif f == '__ttablenew': # check if necessary values were supplied, eg __taliasnew is existing: if aliases.has_key('new'): aliases['new'].table = form[f].value elif string.find(f,'_',3) > 0: # existing table tblID = f[3:string.find(f,'_',3)] tblData = f[string.find(f,'_',3)+1:] if tblData == 'alias': tao = tableAliasClass() tao.alias = val tao._tmpFks = {} aliases[tblID] = tao elif tblData[:3] == 'fks': # FKs if string.find(tblData,'_',3) > 0: # existing fks fksID = tblData[3:string.find(tblData,'_',3)] fksData = tblData[string.find(tblData,'_',3)+1:] if fksData[:2] == 'fk': # existing foreign key if string.find(fksData,'_',2) > 0: # existing fk attribute fkID = fksData[2:string.find(fksData,'_',2)] fkData = fksData[string.find(fksData,'_',2)+1:] if fkData == 'attr': dbo = DBAttributeClass() dbo.attr = val if not aliases[tblID]._tmpFks.has_key(fksID): aliases[tblID]._tmpFks[fksID] = [] aliases[tblID]._tmpFks[fksID].append(dbo) elif fkData == 'type': aliases[tblID]._tmpFks[fksID][-1].type = val elif fkData == 'zdel': del aliases[tblID]._tmpFks[fksID][-1] elif fksData == 'fknew': # new fk dbo = DBAttributeClass() dbo.attr = val if not aliases[tblID]._tmpFks.has_key(fksID): aliases[tblID]._tmpFks[fksID] = [] aliases[tblID]._tmpFks[fksID].append(dbo) elif fksData == 'fktypenew' and form['__t'+tblID+'_fks'+fksID+'_fknew'].value <> '--new--': aliases[tblID]._tmpFks[fksID][-1].type = val elif fksData == 'target': if aliases[tblID]._tmpFks.has_key(fksID): _fks = aliases[tblID]._tmpFks[fksID] del aliases[tblID]._tmpFks[fksID] else: _fks = [] aliases[tblID].addFK(val, _fks) elif tblData == 'fksnew': # new fks aliases[tblID].addFK(val,[]) elif tblData[:2] == 'pk': # PK if string.find(tblData,'_',2) > 0: # existing pk pkID = tblData[2:string.find(tblData,'_',2)] pkData = tblData[string.find(tblData,'_',2)+1:] #log.error("pkData = " + str(pkData)); if pkData == 'attr': dbo = DBAttributeClass() dbo.attr = val aliases[tblID].pk.append(dbo) elif pkData == 'type': aliases[tblID].pk[-1].type = val elif pkData == 'zdel': del aliases[tblID].pk[-1] elif tblData == 'pknew': # new pk dbo = DBAttributeClass() dbo.attr = val aliases[tblID].pk.append(dbo) elif tblData == 'pktypenew' and form['__t'+tblID+'_pknew'].value <> '--new--': aliases[tblID].pk[-1].type = val elif tblData == 'table': aliases[tblID].table = val # create graph aliases = aliases.values() #log.error("aliases: " + str(aliases)) try: psfObj.buildGraph(aliases) except: #pass #psfStatus = "Inconsistent." log.error(str(sys.exc_info()[0])) log.error(str(sys.exc_info()[1])) log.error(str(sys.exc_info()[2])) return psfObj # ------------------------------------------------------------------------------------ def getGraphPic(psfObj): '''Create a new jpg image for the db structure graph in the PSF and return the filename. If the graphviz dot binary cannot be found, return None instead.''' if cfg.server.graphviz_dot is not None and os.path.isfile(cfg.server.graphviz_dot): import pydot #edges=[(1,2), (1,3), (1,4), (3,4)] edges=psfObj.tablegraph.getGraphEdgesList() log.debug( "Edges: %s" % str(edges)) log.debug("Isolated Nodes: %s" % psfObj.tablegraph.getGraphIsolatedNodesList()) # create dot graph g=pydot.graph_from_edges(edges, node_prefix="", directed=True) # add isolated nodes to graph for n in psfObj.tablegraph.getGraphIsolatedNodesList(): node = pydot.Node(n) g.add_node(node) g.ratio='compress' #g.size='6,6' g.fontsize=9 for node in g.get_node_list(): aliasObj = psfObj.getTableAliasObj(node.name) if aliasObj is None: continue # the shape of the node node.shape = 'Mrecord' # ellipse, box, circle, record, plaintext, point node.fontsize = 9 FKeys = flatten([[key.toSimpleString() for key in fk.attrList] for fk in aliasObj.fks]) if len(FKeys) > 0: FKeysString = " | {Fk | {%s} }" % string.join(FKeys, ' | ') else: FKeysString = "" node.label = "{%s | {Pk | {%s} }%s }" %(node.name.upper(), string.join([pk.toSimpleString() for pk in aliasObj.pk], " | "), FKeysString) # use dot binary to create the image g.progs = {'dot':cfg.server.graphviz_dot} t = time.time() picname = 'graphpic/dbstructure.' + str(t) + ".jpg" picdir = os.path.join( os.path.dirname( __file__ ), 'graphpic' ) for file in os.listdir(picdir): try: fa = file.split('.') if t - float(fa[1]) > 3600: #log.error("removing " + file) os.remove(os.path.join( os.path.dirname( __file__ ), 'graphpic', file)) except: pass g.write_jpeg(picname, prog='dot') return picname else: return None # ------------------------------------------------------------------------------------ def getPSFTemplateList(): psflist=[] tmpldir = cfg.dsaTemplateLocator if os.path.isdir(tmpldir): for dir in os.listdir(tmpldir): if os.path.isdir( os.path.join(tmpldir,dir) ) and dir <> 'CVS': psflist.append(dir) return psflist def getTables(): if doMock: return ['table1', 'table2', 'table3'] tables = dbmodObj.getTables() if tables == None: # todo: replace dropdown with text field return [] #log.error("tables = " + str(tables)) return tables def getTableListOps(selectedTable): list = [] tables = dsaObj.getDbmodObj().getTables() if tables is not None: for t in tables: #{'table_opt': 'bar', 'table_val': 'value="bar" selected'}, if selectedTable == t: list.append({'table_opt': t, 'table_val': 'value="' + t + '" selected'}); else: list.append({'table_opt': t, 'table_val': 'value="' + t + '"'}); return list def getColumns(table): if doMock: return ['col1', 'col2', 'col3'] columns = dbmodObj.getColumns(table) #log.error("columns: " + str(columns)) if columns == None: # todo: replace dropdown with text field foo = "bar" return [] return columns def getColumnListOps(table, selectedColumn, excludedColumns, addNewOpt): #log.error("excludedcolumns: " + str(excludedColumns)) list = [] for c in getColumns(table): if selectedColumn == c: list.append({'column_opt': c, 'column_val': 'value="' + c + '" selected'}) else: if excludedColumns.count(c) == 0: list.append({'column_opt': c, 'column_val': 'value="' + c + '"'}) if addNewOpt: list.insert(0, {'column_opt': "--new--", 'column_val': 'value="--new--"'}) return list def getNewPrimaryKeyOps(table, excludedColumns): r = getColumnListOps(table, "", excludedColumns, False) r.insert(0, {'column_opt': "--new--", 'column_val': 'value="--new--"'}) return r def getPrimaryKeysFromAliasOps(pks): list = [] for c in pks.pk: list.append({'column_opt': c.attr, 'column_val': 'value="' + c.attr + '"'}) list.insert(0, {'column_opt': "--new--", 'column_val': 'value="--new--"'}) return list def find(f, seq): for item in seq: if f(item): return item ############################################################################################################ # # MAIN # #=========================================================================================================== doMock = False; # check authentication! if dsa is None: execfile( os.path.abspath( os.path.join( os.path.dirname( __file__ ), 'main.cgi' ) )) sys.exit() # check authentication! authorize(form, dsa=dsa) #print ('Content-Type: text\n\n') #if "HTTP_COOKIE" in os.environ: # print os.environ["HTTP_COOKIE"] #else: # print "HTTP_COOKIE not set!" # # get psfObj # psfObj = dsaObj.getPSFObj(tmp=False) psfStatus = 'Not implemented yet.' # # CAN WE USE THE DB METADATA RETRIEVAL? defines the template to be used # dbmodObj = dsaObj.getDbmodObj() # load template tmpl = PageMacro('Content', PageMacro.DELMODE) tmpl.load('Content', os.path.join(templateDir, '_dbstructure.html')) # general user message message = [''] # page setup according to if database metadata is available if dbmodObj.hasMetaInfo(): tmpl["dbMeta"] = "true"; else: tmpl["dbMeta"] = "false"; for et in dbmodObj.verifyTables([str(ta.table) for ta in psfObj.getTableAliasList()]): message.append("Warning: Table '" + str(et) + "' does not exist in database.") # # look for new form values # if action in ('refresh','save', 'add'): # handle unsaved values if action == 'add': #we have unsaved values tmpl["dirty"] = "true" if action == 'refresh': tmpl["dirty"] = form.getvalue("dirty") if action == 'save': tmpl["dirty"] = "false" # parse form values parseNoMetaForm(form, psfObj) # process other action if action == 'save': # write to real PSF if not psfObj.cleanup(): message.append('There was an error in your PSF file! Some data might have been removed.') psfObj.writePSFile() else: # write to tmp PSF psfObj.writePSFile( filename=dsaObj.getAbsTempPSFilePath() ) elif(action=='load'): # load a psf template message.append('The loading of PSFiles has not yet been implemented. Sorry.') else: # revert to file or first time in this page. read from original PSF # this is the default anyway (to check connection). so dont do anything... pass # # DISPLAY: fill template # # general tmpl['dsa'] = dsa tmpl['wrapper_url'] = dsaObj.getBioCASeAccessPoint() # dbconnection status tmpl['connection'] = dsaObj.getDBConnectionStatus() # PSF status tmpl['status'] = psfStatus # TABLE ALIASES # psf structure data displayNoMetaForm(tmpl, psfObj) # list of available PSF templates tmplList = getPSFTemplateList() dropDown = getDropDownOptionHtml(['--select--']+tmplList, '--select--') tmpl['template_optionlist'] = dropDown _tables = getTables() if _tables == None: _tables = [] tmpl['tables_opt'] = getDropDownOptionHtml(['--select--']+_tables, '--select--') dbGraphPic = getGraphPic(psfObj) if dbGraphPic: tmpl.expand('Content', 'dbGraphpicBlock', [{'dbgraphpic_fn':dbGraphPic}]) # User message tmpl['message'] = string.join(message, '
') # # print HTML ! # printOverHTTP( tmpl )