Mercurial > hg > ChinaGisRestApi
view RestDbInterface.py @ 28:9e4f9cfd1edc
start adding field structure to xml upload
author | casties |
---|---|
date | Fri, 27 Aug 2010 19:19:38 +0200 |
parents | a2e4ca3f1cff |
children | 0b9f8cca6744 |
line wrap: on
line source
''' Created on 19.5.2010 @author: casties ''' from OFS.Folder import Folder 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 from ZPublisher.BaseRequest import DefaultPublishTraverse #from zope.publisher.interfaces import NotFound #from zope.app import zapi #from zope.component import queryMultiAdapter import Shared.DC.ZRDB.DA 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}/ omitting table gives a list of schemas omitting table and schema gives a list of schemas """ implements(IPublishTraverse) meta_type="RESTdb" manage_options=Folder.manage_options+( {'label':'Config','action':'manage_editRestDbInterfaceForm'}, ) # management templates manage_editRestDbInterfaceForm=PageTemplateFile('zpt/editRestDbInterface',globals()) # data templates XML_index = PageTemplateFile('zpt/XML_index', globals()) XML_schema = PageTemplateFile('zpt/XML_schema', globals()) XML_schema_table = PageTemplateFile('zpt/XML_schema_table', globals()) HTML_index = PageTemplateFile('zpt/HTML_index', globals()) HTML_schema = PageTemplateFile('zpt/HTML_schema', globals()) HTML_schema_table = PageTemplateFile('zpt/HTML_schema_table', globals()) JSONHTML_index = PageTemplateFile('zpt/JSONHTML_index', globals()) JSONHTML_schema = PageTemplateFile('zpt/JSONHTML_schema', globals()) JSONHTML_schema_table = PageTemplateFile('zpt/JSONHTML_schema_table', globals()) # JSON_* templates are scripts def __init__(self, id, title, connection_id=None): """init""" self.id = id self.title = title # database connection id self.connection_id = connection_id # create template folder self.manage_addFolder('template') def getJsonString(self,object): """returns a JSON formatted string from object""" return json.dumps(object) def getCursor(self,autocommit=True): """returns fresh DB cursor""" conn = getattr(self,"_v_database_connection",None) if conn is None: # create a new connection object try: if self.connection_id is None: # try to take the first existing ID connids = SQLConnectionIDs(self) if len(connids) > 0: connection_id = connids[0][0] self.connection_id = connection_id logging.debug("connection_id: %s"%repr(connection_id)) da = getattr(self, self.connection_id) da.connect('') # we copy the DAs database connection conn = da._v_database_connection #conn._register() # register with the Zope transaction system self._v_database_connection = conn except Exception, e: 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): """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.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 def publishTraverse(self,request,name): """change the traversal""" # get stored path path = request.get('restdb_path', []) logging.debug("publishtraverse: name=%s restdb_path=%s"%(name,path)) if name in ("index_html", "PUT"): # 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) #TODO: should we check more? else: # traverse if len(path) == 0: # first segment if name == 'db': # virtual path -- continue traversing path = [name] request['restdb_path'] = path else: # try real path tr = DefaultPublishTraverse(self, request) ob = tr.publishTraverse(request, name) return ob else: path.append(name) # continue traversing return self def index_html(self,REQUEST,RESPONSE): """index method""" # ReST path was stored in request path = REQUEST.get('restdb_path',[]) # type and format are real parameter resultFormat = REQUEST.get('format','HTML').upper() queryType = REQUEST.get('type',None) logging.debug("index_html path=%s resultFormat=%s queryType=%s"%(path,resultFormat,queryType)) if queryType is not None: # non-empty queryType -- look for template pt = getattr(self.template, "%s_%s"%(resultFormat,queryType), None) if pt is not None: return pt(format=resultFormat,type=queryType,path=path) if len(path) == 1: # list of schemas return self.showListOfSchemas(resultFormat=resultFormat) elif len(path) == 2: # list of tables return self.showListOfTables(resultFormat=resultFormat,schema=path[1]) elif len(path) == 3: # table if REQUEST.get("method") == "POST" and REQUEST.get("create_table_file",None) is not None: # POST to table to check return self.checkTable(resultFormat=resultFormat,schema=path[1],table=path[2]) # else show table return self.showTable(resultFormat=resultFormat,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 fields = REQUEST.get("fields",None) logging.debug("put with schema=%s table=%s file=%s fields=%s"%(schema,tablename,file,repr(fields))) ret = self.createTableFromXML(schema, tablename, file, fields) # return the result as JSON format = REQUEST.get("format","JSON") if format == "JSON": RESPONSE.setHeader("Content-Type", "application/json") json.dump(ret, RESPONSE) elif format == "JSONHTML": 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,resultFormat='XML',schema='public',table=None,REQUEST=None,RESPONSE=None): """returns PageTemplate with tables""" logging.debug("showtable") # should be cross-site accessible if RESPONSE is None: RESPONSE = self.REQUEST.RESPONSE RESPONSE.setHeader('Access-Control-Allow-Origin', '*') # GIS gets special treatment if resultFormat=="GIS": id = REQUEST.get('id',[]) doc = REQUEST.get('doc',None) return self.showGoogleMap(schema=path[1],table=path[2],id=id,doc=doc) # everything else has its own template pt = getattr(self.template, '%s_schema_table'%resultFormat, None) if pt is None: return "ERROR!! template %s_schema_table not found"%resultFormat data = self.getTable(schema,table) return pt(data=data,tablename=table) def getTable(self,schema='public',table=None,username='guest'): """return table data""" logging.debug("gettable") data = self.executeSQL('select * from "%s"."%s"'%(schema,table)) return data def hasTable(self,schema='public',table=None,username='guest'): """return if table exists""" logging.debug("hastable") data = self.executeSQL('select 1 from information_schema.tables where table_schema=%s and table_name=%s',(schema,table)) ret = bool(data['rows']) return ret def showListOfTables(self,resultFormat='XML',schema='public',REQUEST=None,RESPONSE=None): """returns PageTemplate with list of tables""" logging.debug("showlistoftables") # should be cross-site accessible if RESPONSE is None: RESPONSE = self.REQUEST.RESPONSE RESPONSE.setHeader('Access-Control-Allow-Origin', '*') pt = getattr(self.template, '%s_schema'%resultFormat, None) if pt is None: return "ERROR!! template %s_schema not found"%resultFormat data = self.getListOfTables(schema) return pt(data=data,schema=schema) def getListOfTables(self,schema='public',username='guest'): """return list of tables""" logging.debug("getlistoftables") # get list of fields and types of db table qstr="""SELECT c.relname AS tablename FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind IN ('r','') AND n.nspname NOT IN ('pg_catalog', 'pg_toast') AND pg_catalog.pg_table_is_visible(c.oid)""" #qstr="select attname, format_type(pg_attribute.atttypid, pg_attribute.atttypmod) from pg_attribute, pg_class where attrelid = pg_class.oid and pg_attribute.attnum > 0" data=self.executeSQL(qstr) return data def showListOfSchemas(self,resultFormat='XML',REQUEST=None,RESPONSE=None): """returns PageTemplate with list of schemas""" logging.debug("showlistofschemas") # should be cross-site accessible if RESPONSE is None: RESPONSE = self.REQUEST.RESPONSE RESPONSE.setHeader('Access-Control-Allow-Origin', '*') pt = getattr(self.template, '%s_index'%resultFormat, None) if pt is None: return "ERROR!! template %s_index not found"%resultFormat data = self.getListOfSchemas() return pt(data=data) def getListOfSchemas(self,username='guest'): """return list of schemas""" logging.debug("getlistofschemas") # TODO: really look up schemas data={'fields': (('schemas',),), 'rows': [('public',),]} return data def JSON_index(self,data): """JSON index function""" self.REQUEST.RESPONSE.setHeader("Content-Type", "application/json") json.dump(data, self.REQUEST.RESPONSE) def JSON_schema(self,data,schema): """JSON index function""" self.REQUEST.RESPONSE.setHeader("Content-Type", "application/json") json.dump(data, self.REQUEST.RESPONSE) def JSON_schema_table(self,data,tablename): """JSON index function""" self.REQUEST.RESPONSE.setHeader("Content-Type", "application/json") json.dump(data, self.REQUEST.RESPONSE) def checkTable(self,resultFormat,schema,table,REQUEST=None,RESPONSE=None): """check the table. returns valid data fields and table name.""" if REQUEST is None: REQUEST = self.REQUEST RESPONSE = REQUEST.RESPONSE file = REQUEST.get("create_table_file",None) res = self.checkTableFromXML(schema, table, file) logging.debug("checkTable result=%s"%repr(res)) # return the result as JSON if resultFormat == "JSON": RESPONSE.setHeader("Content-Type", "application/json") json.dump(res, RESPONSE) elif resultFormat == "JSONHTML": RESPONSE.setHeader("Content-Type", "text/html") RESPONSE.write("<html>\n<body>\n<pre>") json.dump(res, RESPONSE) RESPONSE.write("</pre>\n</body>\n</html>") else: return "ERROR: invalid resultFormat" def checkTableFromXML(self,schema,table,data,REQUEST=None,RESPONSE=None): """check the table with the given XML data. returns valid data fields and table name.""" logging.debug("checkTableFromXML schema=%s table=%s"%(schema,table)) # clean table name tablename = sqlName(table) tableExists = self.hasTable(schema, table) if data is None: fieldNames = [] else: # get list of field names from upload file fields = self.importExcelXML(schema,tablename,data,fieldsOnly=True) res = {'tablename': tablename, 'table_exists': tableExists} res['fields'] = fields return res 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) self.executeSQL(sqlString,hasResult=False) return sqlFields def createTableFromXML(self,schema,table,data, fields=None): """create or replace a table with the given XML data""" logging.debug("createTableFromXML schema=%s table=%s data=%s fields=%s"%(schema,table,data,fields)) tablename = sqlName(table) self.importExcelXML(schema, tablename, data, fields) return {"tablename": tablename} def importExcelXML(self,schema,table,xmldata,fields=None,fieldsOnly=False): ''' Import XML file in Excel format into the table @param table: name of the table the xml shall be imported into ''' from xml.dom.pulldom import parseString,parse namespace = "urn:schemas-microsoft-com:office:spreadsheet" containerTagName = "Table" rowTagName = "Row" colTagName = "Cell" dataTagName = "Data" xmlFields = [] 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) xmlFields.append({'name':sqlName(fn),'type':'text'}) if fieldsOnly: # return just field names return xmlFields # create table if fields is None: sqlFields = self.createEmptyTable(schema, table, xmlFields) 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) 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="<kml xmlns=\'http://www.opengis.net/kml/2.2\'><Document><Style id=\'marker_icon\'><IconStyle><scale>15</scale><Icon><href>http://chinagis.mpiwg-berlin.mpg.de/chinagis/images/dot_red.png</href></Icon></IconStyle></Style>\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+"<Placemark>" kml4Marker=kml4Marker+"<description> <![CDATA[<b>" for values in dataset: if values != (None, None): if str(values).find('name')>-1: kml4Marker=kml4Marker+"<name>"+str(values[1])+"</name>\n" continue elif str(values).find('place')>-1: kml4Marker=kml4Marker+"<name>"+str(values[1])+"</name>\n" continue 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='<A HREF=' + str(attribute_string) + ' target=_blank>' + str(attribute_string) + '</A>' kml4Marker=kml4Marker+attribute_string+"</a><br>\n" kml4Marker=kml4Marker+"]]></description>\n" kml4Marker=kml4Marker+"<styleURL>#marker_icon</styleURL>\n" kml4Marker=kml4Marker+"<Point>" kml4Marker=kml4Marker+"<coordinates>"+str(xCoord)+","+str(yCoord)+",0</coordinates>\n" kml4Marker=kml4Marker+"</Point>\n" kml4Marker=kml4Marker+"</Placemark>\n" kml4Marker=kml4Marker+"</Document>\n</kml>" kmlFileName="marker"+str(time.time())+".kml" # 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 def getGoogleMapString(self,kml): logging.debug("getGoogleMapString") printed= '<body %s> '%kml +"""\n <div id="map_canvas" style="width: 98%; height: 95%"> </div> \n </body>" \n </html>""" 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: 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) 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): """Change the object""" if title is not None: self.title = title if connection_id is not None: self.connection_id = connection_id #checkPermission=getSecurityManager().checkPermission REQUEST.RESPONSE.redirect('manage_main') manage_addRestDbInterfaceForm=PageTemplateFile('zpt/addRestDbInterface',globals()) def manage_addRestDbInterface(self, id, title='', label='', description='', createPublic=0, createUserF=0, REQUEST=None): """Add a new object with id *id*.""" ob=RestDbInterface(str(id),title) self._setObject(id, ob) #checkPermission=getSecurityManager().checkPermission REQUEST.RESPONSE.redirect('manage_main')