Mercurial > hg > ChinaGisRestApi
changeset 73:695b6612d4c6
getKmlUrl eingebaut
| author | fknauft |
|---|---|
| date | Wed, 01 Sep 2010 17:42:13 +0200 |
| parents | a67b7c1f7ec5 |
| children | 6e19a9af00e6 |
| files | RestDbInterface.py |
| diffstat | 1 files changed, 60 insertions(+), 251 deletions(-) [+] |
line wrap: on
line diff
--- a/RestDbInterface.py Wed Aug 18 16:42:07 2010 +0200 +++ b/RestDbInterface.py Wed Sep 01 17:42:13 2010 +0200 @@ -8,9 +8,6 @@ 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 @@ -23,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}/ @@ -85,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: @@ -111,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): @@ -141,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 @@ -182,6 +140,7 @@ doc = REQUEST.get('doc',None) logging.debug("index_html path=%s format=%s type=%s"%(path,format,type)) + RESPONSE.setHeader('Access-Control-Allow-Origin', '*') if type is not None: # non-empty type -- look for template @@ -199,48 +158,14 @@ # GIS if format=="GIS": return self.showGoogleMap(schema=path[1],table=path[2],id=id,doc=doc) + if format=="KML_URL": + return self.getKmlUrl(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) - resultType = REQUEST.get("result_type","application/json") - if resultType == "application/json": - RESPONSE.setHeader("Content-Type", "application/json") - json.dump(ret, RESPONSE) - elif resultType == "html/json": - RESPONSE.setHeader("Content-Type", "text/html") - RESPONSE.write("<html>\n<body>\n<pre>") - json.dump(ret, RESPONSE) - RESPONSE.write("</pre>\n</body>\n</html>") - - - else: - # 400 Bad Request - RESPONSE.setStatus(400) - return - def showTable(self,format='XML',schema='public',table=None): """returns PageTemplate with tables""" @@ -256,7 +181,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'): @@ -297,154 +222,22 @@ # 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' - - 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) - - 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") - - 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() - - if node is None: - break - - 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 - - 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 - # Methods for GoogleMaps creation def showGoogleMap(self,schema='chgis',table='mpdl',id=[],doc=None): logging.debug("showGoogleMap") data = self.getDataForGoogleMap(schema,table,id,doc) + kmlFileName=self.getKMLname(data=data,table=table) + initializeStringForGoogleMaps="""onload=\"initialize(\'http://chinagis.mpiwg-berlin.mpg.de/chinagis/REST/daten/"""+kmlFileName+"""\')\""""#+str(data) + initializeStringForGoogleMaps=initializeStringForGoogleMaps.replace("None","0") + googleMap_page=self.htmlHead()+str(self.getGoogleMapString(kml=initializeStringForGoogleMaps)) + return googleMap_page + + def getKmlUrl(self,schema='chgis',table='mpdl',id=[],doc=None): + logging.debug("getKmlUrl") + 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 + return """http://chinagis.mpiwg-berlin.mpg.de/chinagis/REST/daten/"""+kml def getDataForGoogleMap(self,schema='chgis',table='mpdl',id=[],doc=None): logging.debug("getDataForGoogleMap") @@ -529,13 +322,11 @@ # 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 + return kmlFileName def getGoogleMapString(self,kml): logging.debug("getGoogleMapString") @@ -544,18 +335,20 @@ 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: + coords=(0,0) + if gis_id != None: + 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) @@ -567,11 +360,13 @@ return resultpoint else: 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;" + 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) + except: + return "gis_id not to interpretable:"+str(gis_id) + else: + return coords[0] def getCoordsFromREST_gisID(self,gis_id): coordlist=[] @@ -616,7 +411,7 @@ return "Write successful" # except: # return "Could not write" - + def manage_editRestDbInterface(self, title=None, connection_id=None, REQUEST=None): @@ -646,3 +441,17 @@ REQUEST.RESPONSE.redirect('manage_main') + +# constructors = ( +# REST_test.manage_addRESTclassForm, +# REST_test.manage_addRESTclass +# ) + + + context.registerClass( + RestDbInterface.RestDbInterface, + constructors = ( + RestDbInterface.manage_addRestDbInterfaceForm, + RestDbInterface.manage_addRestDbInterface + ) + )