Mercurial > hg > ChinaGisRestApi
comparison RestDbJsonStore.py @ 47:698ef00f2717
more json store
author | casties |
---|---|
date | Fri, 03 Sep 2010 14:00:09 +0200 |
parents | 562717546168 |
children | 2ba80c8bb47f |
comparison
equal
deleted
inserted
replaced
45:ed8db63fab4f | 47:698ef00f2717 |
---|---|
174 else: | 174 else: |
175 # 400 Bad Request | 175 # 400 Bad Request |
176 RESPONSE.setStatus(400) | 176 RESPONSE.setStatus(400) |
177 return | 177 return |
178 | 178 |
179 def showTable(self,resultFormat='XML',schema='public',table=None,REQUEST=None,RESPONSE=None): | |
180 """returns PageTemplate with tables""" | |
181 logging.debug("showtable") | |
182 if REQUEST is None: | |
183 REQUEST = self.REQUEST | |
184 | |
185 # should be cross-site accessible | |
186 if RESPONSE is None: | |
187 RESPONSE = self.REQUEST.RESPONSE | |
188 | |
189 RESPONSE.setHeader('Access-Control-Allow-Origin', '*') | |
190 | |
191 # GIS gets special treatment | |
192 if resultFormat=="GIS": | |
193 id = REQUEST.get('id',[]) | |
194 doc = REQUEST.get('doc',None) | |
195 return self.showGoogleMap(schema=schema,table=table,id=id,doc=doc) | |
196 | |
197 elif resultFormat=="KML_URL": | |
198 id = REQUEST.get('id',[]) | |
199 doc = REQUEST.get('doc',None) | |
200 return self.getKmlUrl(schema=schema,table=table,id=id,doc=doc) | |
201 | |
202 # everything else has its own template | |
203 pt = getattr(self.template, '%s_schema_table'%resultFormat, None) | |
204 if pt is None: | |
205 return "ERROR!! template %s_schema_table not found"%resultFormat | |
206 | |
207 data = self.getTable(schema,table) | |
208 return pt(data=data,tablename=table) | |
209 | |
210 def getTable(self,schema='public',table=None,username='guest'): | |
211 """return table data""" | |
212 logging.debug("gettable") | |
213 data = self.executeSQL('select * from "%s"."%s"'%(schema,table)) | |
214 return data | |
215 | |
216 def hasTable(self,schema='public',table=None,username='guest'): | |
217 """return if table exists""" | |
218 logging.debug("hastable") | |
219 data = self.executeSQL('select 1 from information_schema.tables where table_schema=%s and table_name=%s',(schema,table)) | |
220 ret = bool(data['rows']) | |
221 return ret | |
222 | |
223 def showListOfTables(self,resultFormat='XML',schema='public',REQUEST=None,RESPONSE=None): | |
224 """returns PageTemplate with list of tables""" | |
225 logging.debug("showlistoftables") | |
226 # should be cross-site accessible | |
227 if RESPONSE is None: | |
228 RESPONSE = self.REQUEST.RESPONSE | |
229 RESPONSE.setHeader('Access-Control-Allow-Origin', '*') | |
230 | |
231 pt = getattr(self.template, '%s_schema'%resultFormat, None) | |
232 if pt is None: | |
233 return "ERROR!! template %s_schema not found"%resultFormat | |
234 | |
235 data = self.getListOfTables(schema) | |
236 return pt(data=data,schema=schema) | |
237 | |
238 def getListOfTables(self,schema='public',username='guest'): | |
239 """return list of tables""" | |
240 logging.debug("getlistoftables") | |
241 # get list of fields and types of db table | |
242 qstr="""SELECT c.relname AS tablename FROM pg_catalog.pg_class c | |
243 LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace | |
244 WHERE c.relkind IN ('r','') AND n.nspname NOT IN ('pg_catalog', 'pg_toast') | |
245 AND pg_catalog.pg_table_is_visible(c.oid)""" | |
246 #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" | |
247 data=self.executeSQL(qstr) | |
248 return data | |
249 | |
250 def showListOfSchemas(self,resultFormat='XML',REQUEST=None,RESPONSE=None): | |
251 """returns PageTemplate with list of schemas""" | |
252 logging.debug("showlistofschemas") | |
253 # should be cross-site accessible | |
254 if RESPONSE is None: | |
255 RESPONSE = self.REQUEST.RESPONSE | |
256 RESPONSE.setHeader('Access-Control-Allow-Origin', '*') | |
257 | |
258 pt = getattr(self.template, '%s_index'%resultFormat, None) | |
259 if pt is None: | |
260 return "ERROR!! template %s_index not found"%resultFormat | |
261 | |
262 data = self.getListOfSchemas() | |
263 return pt(data=data) | |
264 | |
265 def getListOfSchemas(self,username='guest'): | |
266 """return list of schemas""" | |
267 logging.debug("getlistofschemas") | |
268 # TODO: really look up schemas | |
269 data={'fields': (('schemas',),), 'rows': [('public',),]} | |
270 return data | |
271 | |
272 def checkTable(self,resultFormat,schema,table,REQUEST=None,RESPONSE=None): | |
273 """check the table. | |
274 returns valid data fields and table name.""" | |
275 if REQUEST is None: | |
276 REQUEST = self.REQUEST | |
277 RESPONSE = REQUEST.RESPONSE | |
278 | |
279 file = REQUEST.get("create_table_file",None) | |
280 res = self.checkTableFromXML(schema, table, file) | |
281 logging.debug("checkTable result=%s"%repr(res)) | |
282 # return the result as JSON | |
283 if resultFormat == "JSON": | |
284 RESPONSE.setHeader("Content-Type", "application/json") | |
285 json.dump(res, RESPONSE) | |
286 | |
287 elif resultFormat == "JSONHTML": | |
288 RESPONSE.setHeader("Content-Type", "text/html") | |
289 RESPONSE.write("<html>\n<body>\n<pre>") | |
290 json.dump(res, RESPONSE) | |
291 RESPONSE.write("</pre>\n</body>\n</html>") | |
292 | |
293 else: | |
294 return "ERROR: invalid resultFormat" | |
295 | |
296 def checkTableFromXML(self,schema,table,data,REQUEST=None,RESPONSE=None): | |
297 """check the table with the given XML data. | |
298 returns valid data fields and table name.""" | |
299 logging.debug("checkTableFromXML schema=%s table=%s"%(schema,table)) | |
300 # clean table name | |
301 tablename = sqlName(table) | |
302 tableExists = self.hasTable(schema, table) | |
303 if data is None: | |
304 fieldNames = [] | |
305 else: | |
306 # get list of field names from upload file | |
307 fields = self.importExcelXML(schema,tablename,data,fieldsOnly=True) | |
308 | |
309 res = {'tablename': tablename, 'table_exists': tableExists} | |
310 res['fields'] = fields | |
311 return res | |
312 | |
313 def createEmptyTable(self,schema,table,fields): | |
314 """create a table with the given fields | |
315 returns list of created fields""" | |
316 logging.debug("createEmptyTable") | |
317 sqlFields = [] | |
318 for f in fields: | |
319 if isinstance(f,dict): | |
320 # {name: XX, type: YY} | |
321 name = sqlName(f['name']) | |
322 type = f['type'] | |
323 sqltype = gisToSqlTypeMap[type] | |
324 | |
325 else: | |
326 # name only | |
327 name = sqlName(f) | |
328 type = 'text' | |
329 sqltype = 'text' | |
330 | |
331 sqlFields.append({'name':name, 'type':type, 'sqltype':sqltype}) | |
332 | |
333 if self.checkTableMetaPermission("create", schema, table): | |
334 self.executeSQL('drop table if exists "%s"."%s"'%(schema,table),hasResult=False) | |
335 fieldString = ", ".join(['"%s" %s'%(f['name'],f['sqltype']) for f in sqlFields]) | |
336 sqlString = 'create table "%s"."%s" (%s)'%(schema,table,fieldString) | |
337 logging.debug("createemptytable: SQL=%s"%sqlString) | |
338 self.executeSQL(sqlString,hasResult=False) | |
339 self.setTableMetaTypes(schema,table,sqlFields) | |
340 return sqlFields | |
341 else: | |
342 logging.warning("create table not allowed!") | |
343 # throw exception? | |
344 return None | |
345 | |
346 def createTableFromXML(self,schema,table,data, fields=None): | |
347 """create or replace a table with the given XML data""" | |
348 logging.debug("createTableFromXML schema=%s table=%s data=%s fields=%s"%(schema,table,data,fields)) | |
349 tablename = sqlName(table) | |
350 self.importExcelXML(schema, tablename, data, fields) | |
351 return {"tablename": tablename} | |
352 | |
353 def importExcelXML(self,schema,table,xmldata,fields=None,fieldsOnly=False): | |
354 ''' | |
355 Import XML file in Excel format into the table | |
356 @param table: name of the table the xml shall be imported into | |
357 ''' | |
358 from xml.dom.pulldom import parseString,parse | |
359 | |
360 namespace = "urn:schemas-microsoft-com:office:spreadsheet" | |
361 containerTagName = "Table" | |
362 rowTagName = "Row" | |
363 colTagName = "Cell" | |
364 dataTagName = "Data" | |
365 xmlFields = [] | |
366 sqlFields = [] | |
367 numFields = 0 | |
368 sqlInsert = None | |
369 | |
370 logging.debug("import excel xml") | |
371 | |
372 ret="" | |
373 if isinstance(xmldata, str): | |
374 logging.debug("importXML reading string data") | |
375 doc=parseString(xmldata) | |
376 else: | |
377 logging.debug("importXML reading file data") | |
378 doc=parse(xmldata) | |
379 | |
380 cnt = 0 | |
381 while True: | |
382 node=doc.getEvent() | |
383 | |
384 if node is None: | |
385 break | |
386 | |
387 else: | |
388 #logging.debug("tag=%s"%node[1].localName) | |
389 if node[1].localName is not None: | |
390 tagName = node[1].localName.lower() | |
391 else: | |
392 # ignore non-tag nodes | |
393 continue | |
394 | |
395 if tagName == rowTagName.lower(): | |
396 # start of row | |
397 doc.expandNode(node[1]) | |
398 cnt += 1 | |
399 if cnt == 1: | |
400 # first row -- field names | |
401 names=node[1].getElementsByTagNameNS(namespace, dataTagName) | |
402 for name in names: | |
403 fn = getTextFromNode(name) | |
404 xmlFields.append({'name':sqlName(fn),'type':'text'}) | |
405 | |
406 if fieldsOnly: | |
407 # return just field names | |
408 return xmlFields | |
409 | |
410 # create table | |
411 if fields is None: | |
412 fields = xmlFields | |
413 | |
414 sqlFields = self.createEmptyTable(schema, table, fields) | |
415 numFields = len(sqlFields) | |
416 fieldString = ", ".join(['"%s"'%f['name'] for f in sqlFields]) | |
417 valString = ", ".join(["%s" for f in sqlFields]) | |
418 sqlInsert = 'insert into "%s"."%s" (%s) values (%s)'%(schema,table,fieldString,valString) | |
419 #logging.debug("importexcelsql: sqlInsert=%s"%sqlInsert) | |
420 | |
421 else: | |
422 # following rows are data | |
423 colNodes=node[1].getElementsByTagNameNS(namespace, colTagName) | |
424 data = [] | |
425 hasData = False | |
426 for colNode in colNodes: | |
427 dataNodes=colNode.getElementsByTagNameNS(namespace, dataTagName) | |
428 if len(dataNodes) > 0: | |
429 val = getTextFromNode(dataNodes[0]) | |
430 hasData = True | |
431 else: | |
432 val = None | |
433 | |
434 data.append(val) | |
435 | |
436 if not hasData: | |
437 # ignore empty rows | |
438 continue | |
439 | |
440 # fix number of data fields | |
441 if len(data) > numFields: | |
442 del data[numFields:] | |
443 elif len(data) < numFields: | |
444 missFields = numFields - len(data) | |
445 data.extend(missFields * [None,]) | |
446 | |
447 logging.debug("importexcel sqlinsert=%s data=%s"%(sqlInsert,data)) | |
448 self.executeSQL(sqlInsert, data, hasResult=False) | |
449 | |
450 return cnt | |
451 | |
452 | |
453 # Methods for GoogleMaps creation | |
454 def showGoogleMap(self,schema='chgis',table='mpdl',id=[],doc=None): | |
455 logging.debug("showGoogleMap") | |
456 data = self.getDataForGoogleMap(schema,table,id,doc) | |
457 kmlFileName=self.getKMLname(data=data,table=table) | |
458 initializeStringForGoogleMaps="""onload=\"initialize(\'http://chinagis.mpiwg-berlin.mpg.de/chinagis/REST/daten/"""+kmlFileName+"""\')\""""#+str(data) | |
459 initializeStringForGoogleMaps=initializeStringForGoogleMaps.replace("None","0") | |
460 googleMap_page=self.htmlHead()+str(self.getGoogleMapString(kml=initializeStringForGoogleMaps)) | |
461 return googleMap_page | |
462 | |
463 def getKmlUrl(self,schema='chgis',table='mpdl',id=[],doc=None): | |
464 logging.debug("getKmlUrl") | |
465 data = self.getDataForGoogleMap(schema,table,id,doc) | |
466 kml=self.getKMLname(data=data,table=table) | |
467 baseUrl = self.absolute_url() | |
468 return "%s/daten/%s"%(baseUrl,kml) | |
469 | |
470 def getDataForGoogleMap(self,schema='chgis',table='mpdl',id=[],doc=None): | |
471 logging.debug("getDataForGoogleMap") | |
472 qstr="SELECT * FROM "+schema+"."+table | |
473 try: | |
474 if id!=[]: | |
475 qstr=qstr+" WHERE " | |
476 for id_item in id.split(","): | |
477 if table=='mpdl': | |
478 qstr=qstr+" mpdl_xmlsource_id = '"+id_item+ "' OR" | |
479 else: | |
480 qstr=qstr+" cast(id as text) LIKE '"+id_item+ "' OR" | |
481 qstr=str(qstr).rsplit(" ",1)[0] #to remove last " and " | |
482 data=self.ZSQLSimpleSearch(qstr) | |
483 return data | |
484 except: | |
485 return qstr | |
486 | |
487 def getKMLname(self,data=[],table=""): | |
488 logging.debug("getKMLname") | |
489 #session=context.REQUEST.SESSION | |
490 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" | |
491 initializeStringForGoogleMaps="" | |
492 #doLine=container.getVar('doLine') | |
493 # Mapping a set of points from table-based SQL-query: | |
494 if data!=None: | |
495 try: | |
496 SQL='SELECT "attribute with gis_id" FROM public.metadata WHERE tablename = %s' | |
497 res = self.executeSQL(SQL, (table,)) | |
498 gisIDattribute = res['rows'][0][0] | |
499 except: | |
500 return "table not registered within metadata" | |
501 | |
502 for dataset in data: | |
503 try: | |
504 xCoord=getattr(dataset,'longitude') | |
505 yCoord=getattr(dataset,'latitude') | |
506 except: | |
507 try: | |
508 xCoord=getattr(dataset,'x_coord') | |
509 yCoord=getattr(dataset,'y_coord') | |
510 except: | |
511 #try: | |
512 gisID=getattr(dataset,gisIDattribute) | |
513 coords=self.getPoint4GISid(gisID) | |
514 if coords!=None: | |
515 xCoord=coords[0] | |
516 yCoord=coords[1] | |
517 # except: | |
518 # return "no coordinates found" | |
519 | |
520 if float(xCoord)!=0: | |
521 if float(yCoord)!=0: | |
522 kml4Marker=kml4Marker+"<Placemark>" | |
523 kml4Marker=kml4Marker+"<description> <![CDATA[<b>" | |
524 for values in dataset: | |
525 if values != (None, None): | |
526 if str(values).find('name')>-1: | |
527 kml4Marker=kml4Marker+"<name>"+str(values[1])+"</name>\n" | |
528 continue | |
529 elif str(values).find('place')>-1: | |
530 kml4Marker=kml4Marker+"<name>"+str(values[1])+"</name>\n" | |
531 continue | |
532 | |
533 kml4Marker=kml4Marker+str(values)+": " | |
534 attribute_string=str(values).replace("'","__Apostroph__") | |
535 attribute_string=str(attribute_string).replace('"','__DoubleApostroph__') | |
536 attribute_string=str(attribute_string).replace(';','__$$__') | |
537 attribute_string=str(attribute_string).replace('&','&') | |
538 if str(attribute_string).find('http')>-1: | |
539 attribute_string='<A HREF=' + str(attribute_string) + ' target=_blank>' + str(attribute_string) + '</A>' | |
540 kml4Marker=kml4Marker+attribute_string+"</a><br>\n" | |
541 | |
542 kml4Marker=kml4Marker+"]]></description>\n" | |
543 kml4Marker=kml4Marker+"<styleURL>#marker_icon</styleURL>\n" | |
544 kml4Marker=kml4Marker+"<Point>" | |
545 | |
546 kml4Marker=kml4Marker+"<coordinates>"+str(xCoord)+","+str(yCoord)+",0</coordinates>\n" | |
547 kml4Marker=kml4Marker+"</Point>\n" | |
548 kml4Marker=kml4Marker+"</Placemark>\n" | |
549 | |
550 kml4Marker=kml4Marker+"</Document>\n</kml>" | |
551 kmlFileName="marker"+str(time.time())+".kml" | |
552 | |
553 #kml4Marker=str(kml4Marker).replace('&','$$') | |
554 #kml4Marker=str(kml4Marker).replace(';','__$$__') | |
555 #kml4Marker=str(kml4Marker).replace('#','__SHARP__') | |
556 isLoadReady='false' | |
557 while isLoadReady=='false': | |
558 isLoadReady=self.RESTwrite2File(self.daten,kmlFileName,kml4Marker) | |
559 | |
560 return kmlFileName | |
561 | |
562 def getGoogleMapString(self,kml): | |
563 logging.debug("getGoogleMapString") | |
564 printed= '<body %s> '%kml +"""\n <div id="map_canvas" style="width: 98%; height: 95%"> </div> \n </body>" \n </html>""" | |
565 return printed | |
566 | |
567 def getPoint4GISid(self,gis_id): | |
568 j=0 | |
569 coords=(0,0) | |
570 if gis_id != None: | |
571 while (True): | |
572 j=j+1 | |
573 if (j>100): # FJK: just to prevent endless loops | |
574 break | |
575 if (gis_id.isdigit()): # FJK: regular exit from while-loop | |
576 break | |
577 else: | |
578 gis_id=gis_id.strip('abcdefghijklmnopqrstuvwxyz_') # FJK: to strip all letters | |
579 gis_id=gis_id.strip() # FJK: to strip all whitespaces | |
580 resultpoint = [0,0] | |
581 results = None | |
582 try: | |
583 if int(gis_id)>0: | |
584 SQL="SELECT x_coord,y_coord FROM chgis.chgis_coords WHERE gis_id LIKE cast("+ str(gis_id) +" as text);" | |
585 results=self.ZSQLSimpleSearch(SQL) | |
586 #print results | |
587 if results != None: | |
588 for result in results: | |
589 resultpoint=[getattr(result,str('x_coord')),getattr(result,str('y_coord'))] | |
590 if resultpoint !=[0,0]: | |
591 return resultpoint | |
592 else: | |
593 coords=self.getCoordsFromREST_gisID(joinid) | |
594 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;" | |
595 returnstring=self.ZSQLSimpleSearch(SQL) | |
596 return coords[0] | |
597 except: | |
598 return "gis_id not to interpretable:"+str(gis_id) | |
599 else: | |
600 return coords[0] | |
601 | |
602 def getCoordsFromREST_gisID(self,gis_id): | |
603 coordlist=[] | |
604 i=0 | |
605 while (i<5 and coordlist==[]): | |
606 | |
607 urlresponse=container.urlFunctions.zUrlopenParseString(container.urlFunctions.zUrlopenRead("http://chgis.hmdc.harvard.edu/xml/id/"+gis_id)) | |
608 baseDocElement=container.urlFunctions.zUrlopenDocumentElement(urlresponse) | |
609 childnodes=container.urlFunctions.zUrlopenChildNodes(baseDocElement) | |
610 itemnodes=container.urlFunctions.zUrlopenGetElementsByTagName(baseDocElement,'item') | |
611 | |
612 for i in range(0,container.urlFunctions.zUrlopenLength(itemnodes)): | |
613 itemnode=container.urlFunctions.zUrlopenGetItem(itemnodes,i) | |
614 itemspatialnodes=container.urlFunctions.zUrlopenGetElementsByTagName(itemnode,'spatial') | |
615 for j in range(0,container.urlFunctions.zUrlopenLength(itemspatialnodes)): | |
616 coord=[] | |
617 itemspatialnode= container.urlFunctions.zUrlopenGetItem(itemspatialnodes,j) | |
618 itemspatiallatnodes=container.urlFunctions.zUrlopenGetElementsByTagName(itemspatialnode,'degrees_latitude') | |
619 for k in range(0,container.urlFunctions.zUrlopenLength(itemspatiallatnodes)): | |
620 itemspatiallatnode= container.urlFunctions.zUrlopenGetItem(itemspatiallatnodes,k) | |
621 coord.append(container.urlFunctions.zUrlopenGetTextData(itemspatiallatnode)) | |
622 itemspatiallngnodes=container.urlFunctions.zUrlopenGetElementsByTagName(itemspatialnode,'degrees_longitude') | |
623 for k in range(0,container.urlFunctions.zUrlopenLength(itemspatiallngnodes)): | |
624 itemspatiallngnode= container.urlFunctions.zUrlopenGetItem(itemspatiallngnodes,k) | |
625 coord.append(container.urlFunctions.zUrlopenGetTextData(itemspatiallngnode)) | |
626 coordlist.append(coord) | |
627 gis_id= "_"+gis_id | |
628 return coordlist | |
629 | |
630 # End for GoogleMaps creation | |
631 | |
632 def RESTwrite2File(self,datadir, name,text): | |
633 logging.debug("RESTwrite2File datadir=%s name=%s"%(datadir,name)) | |
634 try: | |
635 import cStringIO as StringIO | |
636 except: | |
637 import StringIO | |
638 | |
639 # make filehandle from string | |
640 textfile = StringIO.StringIO(text) | |
641 fileid=name | |
642 if fileid in datadir.objectIds(): | |
643 datadir.manage_delObjects(fileid) | |
644 fileInZope=datadir.manage_addFile(id=fileid,file=textfile) | |
645 return "Write successful" | |
646 | |
647 def manage_editRestDbInterface(self, title=None, connection_id=None, | |
648 REQUEST=None): | |
649 """Change the object""" | |
650 if title is not None: | |
651 self.title = title | |
652 | |
653 if connection_id is not None: | |
654 self.connection_id = connection_id | |
655 | |
656 #checkPermission=getSecurityManager().checkPermission | |
657 REQUEST.RESPONSE.redirect('manage_main') | |
658 | |
659 | 179 |
660 manage_addRestDbInterfaceForm=PageTemplateFile('zpt/addRestDbInterface',globals()) | 180 manage_addRestDbInterfaceForm=PageTemplateFile('zpt/addRestDbInterface',globals()) |
661 | 181 |
662 def manage_addRestDbInterface(self, id, title='', label='', description='', | 182 def manage_addRestDbInterface(self, id, title='', label='', description='', |
663 createPublic=0, | 183 createPublic=0, |