# HG changeset patch # User casties # Date 1283515209 -7200 # Node ID 698ef00f27177aec5d4b43fa772168a669e096d9 # Parent ed8db63fab4f1fd41d290edea4e9aebe49e9e663 more json store diff -r ed8db63fab4f -r 698ef00f2717 RestDbJsonStore.py --- a/RestDbJsonStore.py Thu Sep 02 17:01:52 2010 +0200 +++ b/RestDbJsonStore.py Fri Sep 03 14:00:09 2010 +0200 @@ -176,486 +176,6 @@ RESPONSE.setStatus(400) return - def showTable(self,resultFormat='XML',schema='public',table=None,REQUEST=None,RESPONSE=None): - """returns PageTemplate with tables""" - logging.debug("showtable") - if REQUEST is None: - REQUEST = self.REQUEST - - # 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=schema,table=table,id=id,doc=doc) - - elif resultFormat=="KML_URL": - id = REQUEST.get('id',[]) - doc = REQUEST.get('doc',None) - return self.getKmlUrl(schema=schema,table=table,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 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("\n\n
")
-            json.dump(res, RESPONSE)
-            RESPONSE.write("
\n\n") - - 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'] - sqltype = gisToSqlTypeMap[type] - - else: - # name only - name = sqlName(f) - type = 'text' - sqltype = 'text' - - sqlFields.append({'name':name, 'type':type, 'sqltype':sqltype}) - - if self.checkTableMetaPermission("create", schema, table): - self.executeSQL('drop table if exists "%s"."%s"'%(schema,table),hasResult=False) - fieldString = ", ".join(['"%s" %s'%(f['name'],f['sqltype']) for f in sqlFields]) - sqlString = 'create table "%s"."%s" (%s)'%(schema,table,fieldString) - logging.debug("createemptytable: SQL=%s"%sqlString) - self.executeSQL(sqlString,hasResult=False) - self.setTableMetaTypes(schema,table,sqlFields) - return sqlFields - else: - logging.warning("create table not allowed!") - # throw exception? - return None - - 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: - fields = xmlFields - - sqlFields = self.createEmptyTable(schema, table, fields) - 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) - baseUrl = self.absolute_url() - return "%s/daten/%s"%(baseUrl,kml) - - 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 = %s' - res = self.executeSQL(SQL, (table,)) - gisIDattribute = res['rows'][0][0] - 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,gisIDattribute) - 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 - - 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" - - kml4Marker=kml4Marker+"\n" - kmlFileName="marker"+str(time.time())+".kml" - - #kml4Marker=str(kml4Marker).replace('&','$$') - #kml4Marker=str(kml4Marker).replace(';','__$$__') - #kml4Marker=str(kml4Marker).replace('#','__SHARP__') - isLoadReady='false' - while isLoadReady=='false': - isLoadReady=self.RESTwrite2File(self.daten,kmlFileName,kml4Marker) - - return kmlFileName - - def getGoogleMapString(self,kml): - logging.debug("getGoogleMapString") - printed= ' '%kml +"""\n
\n " \n """ - return printed - - def getPoint4GISid(self,gis_id): - j=0 - 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) - #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: - return "gis_id not to interpretable:"+str(gis_id) - else: - return coords[0] - - 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): - logging.debug("RESTwrite2File datadir=%s name=%s"%(datadir,name)) - try: - import cStringIO as StringIO - except: - import StringIO - - # make filehandle from string - textfile = StringIO.StringIO(text) - fileid=name - if fileid in datadir.objectIds(): - datadir.manage_delObjects(fileid) - fileInZope=datadir.manage_addFile(id=fileid,file=textfile) - return "Write successful" - - 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())