# HG changeset patch # User fknauft # Date 1282136425 -7200 # Node ID 78e70dfa7ad6b27d10d490f9ae8820306e4fe5d4 # Parent 060797795a4d228c6f68e8065dcac25baab7099e GoogleMaps related functions diff -r 060797795a4d -r 78e70dfa7ad6 RestDbInterface.py --- a/RestDbInterface.py Wed Aug 18 11:54:18 2010 +0200 +++ b/RestDbInterface.py Wed Aug 18 15:00:25 2010 +0200 @@ -8,9 +8,7 @@ from Products.PageTemplates.PageTemplateFile import PageTemplateFile from Products.ZSQLExtend import ZSQLExtend import logging -import re -import psycopg2 -import json +import time from zope.interface import implements from zope.publisher.interfaces import IPublishTraverse @@ -22,34 +20,6 @@ from Products.ZSQLMethods.SQL import SQLConnectionIDs -def getTextFromNode(node): - """get the cdata content of a XML node""" - if node is None: - return "" - - if isinstance(node, list): - nodelist = node - else: - nodelist=node.childNodes - - rc = "" - for node in nodelist: - if node.nodeType == node.TEXT_NODE: - rc = rc + node.data - return rc - -def sqlName(s,lc=True): - """returns restricted ASCII-only version of string""" - if s is None: - return "" - - # all else -> "_" - s = re.sub(r'[^A-Za-z0-9_]','_',s) - if lc: - return s.lower() - - return s - class RestDbInterface(Folder): """Object for RESTful database queries path schema: /db/{schema}/{table}/ @@ -84,9 +54,11 @@ self.connection_id = connection_id # create template folder self.manage_addFolder('template') + # create data folder + #self.manage_addFolder('daten') - def getCursor(self,autocommit=True): + def getCursor(self): """returns fresh DB cursor""" conn = getattr(self,"_v_database_connection",None) if conn is None: @@ -110,28 +82,19 @@ raise IOError("No database connection! (%s)"%str(e)) cursor = conn.getcursor() - if autocommit: - # is there a better version to get to the connection? - cursor.connection.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT) - return cursor - def executeSQL(self, query, args=None, hasResult=True, autocommit=True): + def executeSQL(self, query, *args): """execute query with args on database and return all results. result format: {"fields":fields, "rows":data}""" - logging.debug("executeSQL query=%s args=%s"%(query,args)) - cur = self.getCursor(autocommit=autocommit) + cur = self.getCursor() cur.execute(query, args) # description of returned fields fields = cur.description - if hasResult: - # get all data in an array - data = cur.fetchall() - cur.close() - return {"fields":fields, "rows":data} - else: - cur.close() - return None + # get all data in an array + data = cur.fetchall() + cur.close() + return {"fields":fields, "rows":data} def publishTraverse(self,request,name): @@ -140,13 +103,9 @@ path = request.get('restdb_path', []) logging.debug("publishtraverse: name=%s restdb_path=%s"%(name,path)) - if name in ("index_html", "PUT"): + if name == "index_html": # end of traversal - if request.get("method") == "POST" and request.get("action",None) == "PUT": - # fake PUT by POST with action=PUT - name = "PUT" - - return getattr(self, name) + return self.index_html #TODO: should we check more? else: # traverse @@ -171,9 +130,15 @@ """index method""" # ReST path was stored in request path = REQUEST.get('restdb_path',[]) + # type and format are real parameter format = REQUEST.get('format','HTML').upper() type = REQUEST.get('type',None) + + # id and doc are used for GoogleMaps content + id = REQUEST.get('id',[]) + doc = REQUEST.get('doc',None) + logging.debug("index_html path=%s format=%s type=%s"%(path,format,type)) if type is not None: @@ -189,40 +154,15 @@ # list of tables return self.showListOfTables(format=format,schema=path[1]) elif len(path) == 3: + # GIS + if format=="GIS": + return self.showGoogleMap(schema=path[1],table=path[2],id=id,doc=doc) # table return self.showTable(format=format,schema=path[1],table=path[2]) # don't know what to do return str(REQUEST) - def PUT(self, REQUEST, RESPONSE): - """ - Implement WebDAV/HTTP PUT/FTP put method for this object. - """ - logging.debug("RestDbInterface PUT") - #logging.debug("req=%s"%REQUEST) - #self.dav__init(REQUEST, RESPONSE) - #self.dav__simpleifhandler(REQUEST, RESPONSE) - # ReST path was stored in request - path = REQUEST.get('restdb_path',[]) - if len(path) == 3: - schema = path[1] - tablename = path[2] - file = REQUEST.get("create_table_file",None) - if file is None: - RESPONSE.setStatus(400) - return - - logging.debug("put with schema=%s table=%s file=%s"%(schema,tablename,file)) - ret = self.createTableFromXML(schema, tablename, file) - # set content type to json(?) - json.dump(ret, RESPONSE) - - else: - # 400 Bad Request - RESPONSE.setStatus(400) - return - def showTable(self,format='XML',schema='public',table=None): """returns PageTemplate with tables""" @@ -238,7 +178,7 @@ def getTable(self,schema='public',table=None,username='guest'): """return table data""" logging.debug("gettable") - data = self.executeSQL('select * from "%s"."%s"'%(schema,table)) + data = self.executeSQL("select * from %s"%table) return data def showListOfTables(self,format='XML',schema='public'): @@ -279,147 +219,186 @@ # TODO: really look up schemas data={'fields': (('schemas',),), 'rows': [('public',),]} return data - - def createEmptyTable(self,schema,table,fields): - """create a table with the given fields - returns list of created fields""" - logging.debug("createEmptyTable") - sqlFields = [] - for f in fields: - if isinstance(f,dict): - # {name: XX, type: YY} - name = sqlName(f['name']) - type = f['type'] - - else: - # name only - name = sqlName(f) - type = 'text' + + # Methods for GoogleMaps creation + def showGoogleMap(self,schema='chgis',table='mpdl',id=[],doc=None): + logging.debug("showGoogleMap") + data = self.getDataForGoogleMap(schema,table,id,doc) + kml=self.getKMLname(data=data,table=table) + googleMap_page=self.htmlHead()+str(self.getGoogleMapString(kml=kml)) + return googleMap_page + + def getDataForGoogleMap(self,schema='chgis',table='mpdl',id=[],doc=None): + logging.debug("getDataForGoogleMap") + qstr="SELECT * FROM "+schema+"."+table + try: + if id!=[]: + qstr=qstr+" WHERE " + for id_item in id.split(","): + if table=='mpdl': + qstr=qstr+" mpdl_xmlsource_id = '"+id_item+ "' OR" + else: + qstr=qstr+" cast(id as text) LIKE '"+id_item+ "' OR" + qstr=str(qstr).rsplit(" ",1)[0] #to remove last " and " + data=self.ZSQLSimpleSearch(qstr) + return data + except: + return qstr + + def getKMLname(self,data=[],table=""): + logging.debug("getKMLname") + #session=context.REQUEST.SESSION + kml4Marker="\n" + initializeStringForGoogleMaps="" + #doLine=container.getVar('doLine') + # Mapping a set of points from table-based SQL-query: + if data!=None: + try: + SQL="""SELECT \"attribute with gis_id\" FROM public.metadata WHERE tablename LIKE '"""+table+"""'""" + gisIDattribute=self.ZSQLSimpleSearch(SQL) + except: + return "table not registered within metadata" + for dataset in data: + try: + xCoord=getattr(dataset,'longitude') + yCoord=getattr(dataset,'latitude') + except: + try: + xCoord=getattr(dataset,'x_coord') + yCoord=getattr(dataset,'y_coord') + except: +# try: + gisID=getattr(dataset,getattr(gisIDattribute[0],'attribute with gis_id')) + coords=self.getPoint4GISid(gisID) + if coords!=None: + xCoord=coords[0] + yCoord=coords[1] +# except: +# return "no coordinates found" + if float(xCoord)!=0: + if float(yCoord)!=0: + kml4Marker=kml4Marker+"" + kml4Marker=kml4Marker+" " + for values in dataset: + if values != (None, None): + if str(values).find('name')>-1: + kml4Marker=kml4Marker+""+str(values[1])+"\n" + continue + elif str(values).find('place')>-1: + kml4Marker=kml4Marker+""+str(values[1])+"\n" + continue - sqlFields.append({'name':name, 'type':type}) - - # drop table if it exists - try: - res = self.executeSQL('select * from "%s"."%s" where 1=0'%(schema,table)) - logging.debug("createemptytable: table %s.%s exists"%(schema,table)) - self.executeSQL('drop table "%s"."%s"'%(schema,table),hasResult=False) - except: - pass - - fieldString = ", ".join(['"%s" %s'%(f['name'],f['type']) for f in sqlFields]) - sqlString = 'create table "%s"."%s" (%s)'%(schema,table,fieldString) - logging.debug("createemptytable: SQL=%s"%sqlString) - ret = self.executeSQL(sqlString,hasResult=False) + kml4Marker=kml4Marker+str(values)+": " + attribute_string=str(values).replace("'","__Apostroph__") + attribute_string=str(attribute_string).replace('"','__DoubleApostroph__') + attribute_string=str(attribute_string).replace(';','__$$__') + attribute_string=str(attribute_string).replace('&','&') + if str(attribute_string).find('http')>-1: + attribute_string='' + str(attribute_string) + '' + kml4Marker=kml4Marker+attribute_string+"
\n" + + kml4Marker=kml4Marker+"]]>\n" + kml4Marker=kml4Marker+"#marker_icon\n" + kml4Marker=kml4Marker+"" + + kml4Marker=kml4Marker+""+str(xCoord)+","+str(yCoord)+",0\n" + kml4Marker=kml4Marker+"\n" + kml4Marker=kml4Marker+"\n" - return sqlFields - - def createTableFromXML(self,schema,table,data): - """create or replace a table with the given XML data""" - logging.debug("createTableFromXML schema=%s table=%s data=%s"%(schema,table,data)) - tablename = sqlName(table) - self.importExcelXML(schema,tablename, data) - return {"tablename": tablename} - - def importExcelXML(self,schema,table,xmldata,fieldNamesOnly=False): - ''' - Import XML file in Excel format into the table - @param table: name of the table the xml shall be imported into - @param containerTagName: XML-Tag which describes a dataset - @param data: data to be imported - @param identify: (optional) field res. tag which identifies a entry uniquely for updating purposes. - @param RESPONSE: (optional) - ''' - from xml.dom.pulldom import parseString,parse - - namespace = "urn:schemas-microsoft-com:office:spreadsheet" - containerTagName = "Table" - rowTagName = "Row" - colTagName = "Cell" - dataTagName = "Data" - fieldNames = [] - sqlFields = [] - numFields = 0 - sqlInsert = None - - logging.debug("import excel xml") + kml4Marker=kml4Marker+"\n" + kmlFileName="marker"+str(time.time())+".kml" - ret="" - if isinstance(xmldata, str): - logging.debug("importXML reading string data") - doc=parseString(xmldata) - else: - logging.debug("importXML reading file data") - doc=parse(xmldata) - - cnt = 0 - while True: - node=doc.getEvent() + # kml4Marker=str(kml4Marker).replace('&','$$') + # kml4Marker=str(kml4Marker).replace(';','__$$__') + # kml4Marker=str(kml4Marker).replace('#','__SHARP__') + initializeStringForGoogleMaps="""onload=\"initialize(\'http://chinagis.mpiwg-berlin.mpg.de/chinagis/REST/daten/"""+kmlFileName+"""\')\""""#+str(data) + initializeStringForGoogleMaps=initializeStringForGoogleMaps.replace("None","0") + isLoadReady='false' + while isLoadReady=='false': + isLoadReady=self.RESTwrite2File(self.daten,kmlFileName,kml4Marker) + + return initializeStringForGoogleMaps - if node is None: - break - + def getGoogleMapString(self,kml): + logging.debug("getGoogleMapString") + printed= ' '%kml +"""\n
\n " \n """ + return printed + + def getPoint4GISid(self,gis_id): + j=0 + while (True): + j=j+1 + if (j>100): # FJK: just to prevent endless loops + break + if (gis_id.isdigit()): # FJK: regular exit from while-loop + break + else: + gis_id=gis_id.strip('abcdefghijklmnopqrstuvwxyz_') # FJK: to strip all letters + gis_id=gis_id.strip() # FJK: to strip all whitespaces + resultpoint = [0,0] + results = None + try: + if int(gis_id)>0: + SQL="SELECT x_coord,y_coord FROM chgis.chgis_coords WHERE gis_id LIKE cast("+ str(gis_id) +" as text);" + results=self.ZSQLSimpleSearch(SQL) + #print results + if results != None: + for result in results: + resultpoint=[getattr(result,str('x_coord')),getattr(result,str('y_coord'))] + if resultpoint !=[0,0]: + return resultpoint else: - #logging.debug("tag=%s"%node[1].localName) - if node[1].localName is not None: - tagName = node[1].localName.lower() - else: - # ignore non-tag nodes - continue - - if tagName == rowTagName.lower(): - # start of row - doc.expandNode(node[1]) - cnt += 1 - if cnt == 1: - # first row -- field names - names=node[1].getElementsByTagNameNS(namespace, dataTagName) - for name in names: - fn = getTextFromNode(name) - fieldNames.append(fn) - - if fieldNamesOnly: - # return just field names - return fieldNames - - # create table - sqlFields = self.createEmptyTable(schema, table, fieldNames) - numFields = len(sqlFields) - fieldString = ", ".join(['"%s"'%f['name'] for f in sqlFields]) - valString = ", ".join(["%s" for f in sqlFields]) - sqlInsert = 'insert into "%s"."%s" (%s) values (%s)'%(schema,table,fieldString,valString) - logging.debug("importexcelsql: sqlInsert=%s"%sqlInsert) - - else: - # following rows are data - colNodes=node[1].getElementsByTagNameNS(namespace, colTagName) - data = [] - hasData = False - for colNode in colNodes: - dataNodes=colNode.getElementsByTagNameNS(namespace, dataTagName) - if len(dataNodes) > 0: - val = getTextFromNode(dataNodes[0]) - hasData = True - else: - val = None + coords=self.getCoordsFromREST_gisID(joinid) + SQL="INSERT INTO chgis.chgis_coords (gis_id,x_coord,y_coord) VALUES (" +gis_id+ "," +coords[0][1]+ "," +coords[0][0]+ "); ANALYZE chgis.chgis_coords;" + returnstring=self.ZSQLSimpleSearch(SQL) + return coords[0] + except: + error="gis_id not to interpretable:"+str(gis_id) - data.append(val) - - if not hasData: - # ignore empty rows - continue - - # fix number of data fields - if len(data) > numFields: - del data[numFields:] - elif len(data) < numFields: - missFields = numFields - len(data) - data.extend(missFields * [None,]) - - logging.debug("importexcel sqlinsert=%s data=%s"%(sqlInsert,data)) - self.executeSQL(sqlInsert, data, hasResult=False) - - return cnt + def getCoordsFromREST_gisID(self,gis_id): + coordlist=[] + i=0 + while (i<5 and coordlist==[]): + urlresponse=container.urlFunctions.zUrlopenParseString(container.urlFunctions.zUrlopenRead("http://chgis.hmdc.harvard.edu/xml/id/"+gis_id)) + baseDocElement=container.urlFunctions.zUrlopenDocumentElement(urlresponse) + childnodes=container.urlFunctions.zUrlopenChildNodes(baseDocElement) + itemnodes=container.urlFunctions.zUrlopenGetElementsByTagName(baseDocElement,'item') + + for i in range(0,container.urlFunctions.zUrlopenLength(itemnodes)): + itemnode=container.urlFunctions.zUrlopenGetItem(itemnodes,i) + itemspatialnodes=container.urlFunctions.zUrlopenGetElementsByTagName(itemnode,'spatial') + for j in range(0,container.urlFunctions.zUrlopenLength(itemspatialnodes)): + coord=[] + itemspatialnode= container.urlFunctions.zUrlopenGetItem(itemspatialnodes,j) + itemspatiallatnodes=container.urlFunctions.zUrlopenGetElementsByTagName(itemspatialnode,'degrees_latitude') + for k in range(0,container.urlFunctions.zUrlopenLength(itemspatiallatnodes)): + itemspatiallatnode= container.urlFunctions.zUrlopenGetItem(itemspatiallatnodes,k) + coord.append(container.urlFunctions.zUrlopenGetTextData(itemspatiallatnode)) + itemspatiallngnodes=container.urlFunctions.zUrlopenGetElementsByTagName(itemspatialnode,'degrees_longitude') + for k in range(0,container.urlFunctions.zUrlopenLength(itemspatiallngnodes)): + itemspatiallngnode= container.urlFunctions.zUrlopenGetItem(itemspatiallngnodes,k) + coord.append(container.urlFunctions.zUrlopenGetTextData(itemspatiallngnode)) + coordlist.append(coord) + gis_id= "_"+gis_id + return coordlist + +# End for GoogleMaps creation + + def RESTwrite2File(self,datadir, name,text): +# try: + fileid=name + if fileid in datadir.objectIds(): + datadir.manage_delObjects(fileid) + newfile=open(name,'w') + newfile.write(text) + newfile.close() + file4Read=open(name,'r') + fileInZope=datadir.manage_addFile(id=fileid,file=file4Read) + return "Write successful" +# except: +# return "Could not write" + def manage_editRestDbInterface(self, title=None, connection_id=None, REQUEST=None):