Mercurial > hg > ChinaGisRestApi

comparison RestDbJsonStore.py @ 47:698ef00f2717

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
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('&','&amp;')
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,