Mercurial > hg > ChinaGisRestApi

comparison RestDbInterface.py @ 73:695b6612d4c6

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
getKmlUrl eingebaut
author fknauft
date Wed, 01 Sep 2010 17:42:13 +0200
parents a67b7c1f7ec5
children 6e19a9af00e6
comparison
equal deleted inserted replaced
21:a67b7c1f7ec5 73:695b6612d4c6
6 6
7 from OFS.Folder import Folder 7 from OFS.Folder import Folder
8 from Products.PageTemplates.PageTemplateFile import PageTemplateFile 8 from Products.PageTemplates.PageTemplateFile import PageTemplateFile
9 from Products.ZSQLExtend import ZSQLExtend 9 from Products.ZSQLExtend import ZSQLExtend
10 import logging 10 import logging
11 import re
12 import psycopg2
13 import json
14 import time 11 import time
15 12
16 from zope.interface import implements 13 from zope.interface import implements
17 from zope.publisher.interfaces import IPublishTraverse 14 from zope.publisher.interfaces import IPublishTraverse
18 from ZPublisher.BaseRequest import DefaultPublishTraverse 15 from ZPublisher.BaseRequest import DefaultPublishTraverse
21 #from zope.component import queryMultiAdapter 18 #from zope.component import queryMultiAdapter
22 import Shared.DC.ZRDB.DA 19 import Shared.DC.ZRDB.DA
23 from Products.ZSQLMethods.SQL import SQLConnectionIDs 20 from Products.ZSQLMethods.SQL import SQLConnectionIDs
24 21
25 22
26 def getTextFromNode(node):
27 """get the cdata content of a XML node"""
28 if node is None:
29 return ""
30
31 if isinstance(node, list):
32 nodelist = node
33 else:
34 nodelist=node.childNodes
35
36 rc = ""
37 for node in nodelist:
38 if node.nodeType == node.TEXT_NODE:
39 rc = rc + node.data
40 return rc
41
42 def sqlName(s,lc=True):
43 """returns restricted ASCII-only version of string"""
44 if s is None:
45 return ""
46
47 # all else -> "_"
48 s = re.sub(r'[^A-Za-z0-9_]','_',s)
49 if lc:
50 return s.lower()
51
52 return s
53
54 class RestDbInterface(Folder): 23 class RestDbInterface(Folder):
55 """Object for RESTful database queries 24 """Object for RESTful database queries
56 path schema: /db/{schema}/{table}/ 25 path schema: /db/{schema}/{table}/
57 omitting table gives a list of schemas 26 omitting table gives a list of schemas
58 omitting table and schema gives a list of schemas 27 omitting table and schema gives a list of schemas
83 self.title = title 52 self.title = title
84 # database connection id 53 # database connection id
85 self.connection_id = connection_id 54 self.connection_id = connection_id
86 # create template folder 55 # create template folder
87 self.manage_addFolder('template') 56 self.manage_addFolder('template')
88 57 # create data folder
89 58 #self.manage_addFolder('daten')
90 def getCursor(self,autocommit=True): 59
60
61 def getCursor(self):
91 """returns fresh DB cursor""" 62 """returns fresh DB cursor"""
92 conn = getattr(self,"_v_database_connection",None) 63 conn = getattr(self,"_v_database_connection",None)
93 if conn is None: 64 if conn is None:
94 # create a new connection object 65 # create a new connection object
95 try: 66 try:
109 self._v_database_connection = conn 80 self._v_database_connection = conn
110 except Exception, e: 81 except Exception, e:
111 raise IOError("No database connection! (%s)"%str(e)) 82 raise IOError("No database connection! (%s)"%str(e))
112 83
113 cursor = conn.getcursor() 84 cursor = conn.getcursor()
114 if autocommit:
115 # is there a better version to get to the connection?
116 cursor.connection.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
117
118 return cursor 85 return cursor
119 86
120 def executeSQL(self, query, args=None, hasResult=True, autocommit=True): 87 def executeSQL(self, query, *args):
121 """execute query with args on database and return all results. 88 """execute query with args on database and return all results.
122 result format: {"fields":fields, "rows":data}""" 89 result format: {"fields":fields, "rows":data}"""
123 logging.debug("executeSQL query=%s args=%s"%(query,args)) 90 cur = self.getCursor()
124 cur = self.getCursor(autocommit=autocommit)
125 cur.execute(query, args) 91 cur.execute(query, args)
126 # description of returned fields 92 # description of returned fields
127 fields = cur.description 93 fields = cur.description
128 if hasResult: 94 # get all data in an array
129 # get all data in an array 95 data = cur.fetchall()
130 data = cur.fetchall() 96 cur.close()
131 cur.close() 97 return {"fields":fields, "rows":data}
132 return {"fields":fields, "rows":data}
133 else:
134 cur.close()
135 return None
136 98
137 99
138 def publishTraverse(self,request,name): 100 def publishTraverse(self,request,name):
139 """change the traversal""" 101 """change the traversal"""
140 # get stored path 102 # get stored path
141 path = request.get('restdb_path', []) 103 path = request.get('restdb_path', [])
142 logging.debug("publishtraverse: name=%s restdb_path=%s"%(name,path)) 104 logging.debug("publishtraverse: name=%s restdb_path=%s"%(name,path))
143 105
144 if name in ("index_html", "PUT"): 106 if name == "index_html":
145 # end of traversal 107 # end of traversal
146 if request.get("method") == "POST" and request.get("action",None) == "PUT": 108 return self.index_html
147 # fake PUT by POST with action=PUT
148 name = "PUT"
149
150 return getattr(self, name)
151 #TODO: should we check more? 109 #TODO: should we check more?
152 else: 110 else:
153 # traverse 111 # traverse
154 if len(path) == 0: 112 if len(path) == 0:
155 # first segment 113 # first segment
180 # id and doc are used for GoogleMaps content 138 # id and doc are used for GoogleMaps content
181 id = REQUEST.get('id',[]) 139 id = REQUEST.get('id',[])
182 doc = REQUEST.get('doc',None) 140 doc = REQUEST.get('doc',None)
183 141
184 logging.debug("index_html path=%s format=%s type=%s"%(path,format,type)) 142 logging.debug("index_html path=%s format=%s type=%s"%(path,format,type))
143 RESPONSE.setHeader('Access-Control-Allow-Origin', '*')
185 144
186 if type is not None: 145 if type is not None:
187 # non-empty type -- look for template 146 # non-empty type -- look for template
188 pt = getattr(self.template, "%s_%s"%(format,type), None) 147 pt = getattr(self.template, "%s_%s"%(format,type), None)
189 if pt is not None: 148 if pt is not None:
197 return self.showListOfTables(format=format,schema=path[1]) 156 return self.showListOfTables(format=format,schema=path[1])
198 elif len(path) == 3: 157 elif len(path) == 3:
199 # GIS 158 # GIS
200 if format=="GIS": 159 if format=="GIS":
201 return self.showGoogleMap(schema=path[1],table=path[2],id=id,doc=doc) 160 return self.showGoogleMap(schema=path[1],table=path[2],id=id,doc=doc)
161 if format=="KML_URL":
162 return self.getKmlUrl(schema=path[1],table=path[2],id=id,doc=doc)
202 # table 163 # table
203 return self.showTable(format=format,schema=path[1],table=path[2]) 164 return self.showTable(format=format,schema=path[1],table=path[2])
204 165
205 # don't know what to do 166 # don't know what to do
206 return str(REQUEST) 167 return str(REQUEST)
207 168
208 def PUT(self, REQUEST, RESPONSE):
209 """
210 Implement WebDAV/HTTP PUT/FTP put method for this object.
211 """
212 logging.debug("RestDbInterface PUT")
213 #logging.debug("req=%s"%REQUEST)
214 #self.dav__init(REQUEST, RESPONSE)
215 #self.dav__simpleifhandler(REQUEST, RESPONSE)
216 # ReST path was stored in request
217 path = REQUEST.get('restdb_path',[])
218 if len(path) == 3:
219 schema = path[1]
220 tablename = path[2]
221 file = REQUEST.get("create_table_file",None)
222 if file is None:
223 RESPONSE.setStatus(400)
224 return
225
226 logging.debug("put with schema=%s table=%s file=%s"%(schema,tablename,file))
227 ret = self.createTableFromXML(schema, tablename, file)
228 resultType = REQUEST.get("result_type","application/json")
229 if resultType == "application/json":
230 RESPONSE.setHeader("Content-Type", "application/json")
231 json.dump(ret, RESPONSE)
232 elif resultType == "html/json":
233 RESPONSE.setHeader("Content-Type", "text/html")
234 RESPONSE.write("<html>\n<body>\n<pre>")
235 json.dump(ret, RESPONSE)
236 RESPONSE.write("</pre>\n</body>\n</html>")
237
238
239 else:
240 # 400 Bad Request
241 RESPONSE.setStatus(400)
242 return
243
244 169
245 def showTable(self,format='XML',schema='public',table=None): 170 def showTable(self,format='XML',schema='public',table=None):
246 """returns PageTemplate with tables""" 171 """returns PageTemplate with tables"""
247 logging.debug("showtable") 172 logging.debug("showtable")
248 pt = getattr(self.template, '%s_schema_table'%format, None) 173 pt = getattr(self.template, '%s_schema_table'%format, None)
254 179
255 180
256 def getTable(self,schema='public',table=None,username='guest'): 181 def getTable(self,schema='public',table=None,username='guest'):
257 """return table data""" 182 """return table data"""
258 logging.debug("gettable") 183 logging.debug("gettable")
259 data = self.executeSQL('select * from "%s"."%s"'%(schema,table)) 184 data = self.executeSQL("select * from %s"%table)
260 return data 185 return data
261 186
262 def showListOfTables(self,format='XML',schema='public'): 187 def showListOfTables(self,format='XML',schema='public'):
263 """returns PageTemplate with list of tables""" 188 """returns PageTemplate with list of tables"""
264 logging.debug("showlistoftables") 189 logging.debug("showlistoftables")
295 """return list of schemas""" 220 """return list of schemas"""
296 logging.debug("getlistofschemas") 221 logging.debug("getlistofschemas")
297 # TODO: really look up schemas 222 # TODO: really look up schemas
298 data={'fields': (('schemas',),), 'rows': [('public',),]} 223 data={'fields': (('schemas',),), 'rows': [('public',),]}
299 return data 224 return data
300 225
301 def createEmptyTable(self,schema,table,fields):
302 """create a table with the given fields
303 returns list of created fields"""
304 logging.debug("createEmptyTable")
305 sqlFields = []
306 for f in fields:
307 if isinstance(f,dict):
308 # {name: XX, type: YY}
309 name = sqlName(f['name'])
310 type = f['type']
311
312 else:
313 # name only
314 name = sqlName(f)
315 type = 'text'
316
317 sqlFields.append({'name':name, 'type':type})
318
319 # drop table if it exists
320 try:
321 res = self.executeSQL('select * from "%s"."%s" where 1=0'%(schema,table))
322 logging.debug("createemptytable: table %s.%s exists"%(schema,table))
323 self.executeSQL('drop table "%s"."%s"'%(schema,table),hasResult=False)
324 except:
325 pass
326
327 fieldString = ", ".join(['"%s" %s'%(f['name'],f['type']) for f in sqlFields])
328 sqlString = 'create table "%s"."%s" (%s)'%(schema,table,fieldString)
329 logging.debug("createemptytable: SQL=%s"%sqlString)
330 ret = self.executeSQL(sqlString,hasResult=False)
331
332 return sqlFields
333
334 def createTableFromXML(self,schema,table,data):
335 """create or replace a table with the given XML data"""
336 logging.debug("createTableFromXML schema=%s table=%s data=%s"%(schema,table,data))
337 tablename = sqlName(table)
338 self.importExcelXML(schema,tablename, data)
339 return {"tablename": tablename}
340
341 def importExcelXML(self,schema,table,xmldata,fieldNamesOnly=False):
342 '''
343 Import XML file in Excel format into the table
344 @param table: name of the table the xml shall be imported into
345 @param containerTagName: XML-Tag which describes a dataset
346 @param data: data to be imported
347 @param identify: (optional) field res. tag which identifies a entry uniquely for updating purposes.
348 @param RESPONSE: (optional)
349 '''
350 from xml.dom.pulldom import parseString,parse
351
352 namespace = "urn:schemas-microsoft-com:office:spreadsheet"
353 containerTagName = "Table"
354 rowTagName = "Row"
355 colTagName = "Cell"
356 dataTagName = "Data"
357 fieldNames = []
358 sqlFields = []
359 numFields = 0
360 sqlInsert = None
361
362 logging.debug("import excel xml")
363
364 ret=""
365 if isinstance(xmldata, str):
366 logging.debug("importXML reading string data")
367 doc=parseString(xmldata)
368 else:
369 logging.debug("importXML reading file data")
370 doc=parse(xmldata)
371
372 cnt = 0
373 while True:
374 node=doc.getEvent()
375
376 if node is None:
377 break
378
379 else:
380 #logging.debug("tag=%s"%node[1].localName)
381 if node[1].localName is not None:
382 tagName = node[1].localName.lower()
383 else:
384 # ignore non-tag nodes
385 continue
386
387 if tagName == rowTagName.lower():
388 # start of row
389 doc.expandNode(node[1])
390 cnt += 1
391 if cnt == 1:
392 # first row -- field names
393 names=node[1].getElementsByTagNameNS(namespace, dataTagName)
394 for name in names:
395 fn = getTextFromNode(name)
396 fieldNames.append(fn)
397
398 if fieldNamesOnly:
399 # return just field names
400 return fieldNames
401
402 # create table
403 sqlFields = self.createEmptyTable(schema, table, fieldNames)
404 numFields = len(sqlFields)
405 fieldString = ", ".join(['"%s"'%f['name'] for f in sqlFields])
406 valString = ", ".join(["%s" for f in sqlFields])
407 sqlInsert = 'insert into "%s"."%s" (%s) values (%s)'%(schema,table,fieldString,valString)
408 logging.debug("importexcelsql: sqlInsert=%s"%sqlInsert)
409
410 else:
411 # following rows are data
412 colNodes=node[1].getElementsByTagNameNS(namespace, colTagName)
413 data = []
414 hasData = False
415 for colNode in colNodes:
416 dataNodes=colNode.getElementsByTagNameNS(namespace, dataTagName)
417 if len(dataNodes) > 0:
418 val = getTextFromNode(dataNodes[0])
419 hasData = True
420 else:
421 val = None
422
423 data.append(val)
424
425 if not hasData:
426 # ignore empty rows
427 continue
428
429 # fix number of data fields
430 if len(data) > numFields:
431 del data[numFields:]
432 elif len(data) < numFields:
433 missFields = numFields - len(data)
434 data.extend(missFields * [None,])
435
436 logging.debug("importexcel sqlinsert=%s data=%s"%(sqlInsert,data))
437 self.executeSQL(sqlInsert, data, hasResult=False)
438
439 return cnt
440
441 # Methods for GoogleMaps creation 226 # Methods for GoogleMaps creation
442 def showGoogleMap(self,schema='chgis',table='mpdl',id=[],doc=None): 227 def showGoogleMap(self,schema='chgis',table='mpdl',id=[],doc=None):
443 logging.debug("showGoogleMap") 228 logging.debug("showGoogleMap")
444 data = self.getDataForGoogleMap(schema,table,id,doc) 229 data = self.getDataForGoogleMap(schema,table,id,doc)
230 kmlFileName=self.getKMLname(data=data,table=table)
231 initializeStringForGoogleMaps="""onload=\"initialize(\'http://chinagis.mpiwg-berlin.mpg.de/chinagis/REST/daten/"""+kmlFileName+"""\')\""""#+str(data)
232 initializeStringForGoogleMaps=initializeStringForGoogleMaps.replace("None","0")
233 googleMap_page=self.htmlHead()+str(self.getGoogleMapString(kml=initializeStringForGoogleMaps))
234 return googleMap_page
235
236 def getKmlUrl(self,schema='chgis',table='mpdl',id=[],doc=None):
237 logging.debug("getKmlUrl")
238 data = self.getDataForGoogleMap(schema,table,id,doc)
445 kml=self.getKMLname(data=data,table=table) 239 kml=self.getKMLname(data=data,table=table)
446 googleMap_page=self.htmlHead()+str(self.getGoogleMapString(kml=kml)) 240 return """http://chinagis.mpiwg-berlin.mpg.de/chinagis/REST/daten/"""+kml
447 return googleMap_page
448 241
449 def getDataForGoogleMap(self,schema='chgis',table='mpdl',id=[],doc=None): 242 def getDataForGoogleMap(self,schema='chgis',table='mpdl',id=[],doc=None):
450 logging.debug("getDataForGoogleMap") 243 logging.debug("getDataForGoogleMap")
451 qstr="SELECT * FROM "+schema+"."+table 244 qstr="SELECT * FROM "+schema+"."+table
452 try: 245 try:
527 kmlFileName="marker"+str(time.time())+".kml" 320 kmlFileName="marker"+str(time.time())+".kml"
528 321
529 # kml4Marker=str(kml4Marker).replace('&','$$') 322 # kml4Marker=str(kml4Marker).replace('&','$$')
530 # kml4Marker=str(kml4Marker).replace(';','__$$__') 323 # kml4Marker=str(kml4Marker).replace(';','__$$__')
531 # kml4Marker=str(kml4Marker).replace('#','__SHARP__') 324 # kml4Marker=str(kml4Marker).replace('#','__SHARP__')
532 initializeStringForGoogleMaps="""onload=\"initialize(\'http://chinagis.mpiwg-berlin.mpg.de/chinagis/REST/daten/"""+kmlFileName+"""\')\""""#+str(data)
533 initializeStringForGoogleMaps=initializeStringForGoogleMaps.replace("None","0")
534 isLoadReady='false' 325 isLoadReady='false'
535 while isLoadReady=='false': 326 while isLoadReady=='false':
536 isLoadReady=self.RESTwrite2File(self.daten,kmlFileName,kml4Marker) 327 isLoadReady=self.RESTwrite2File(self.daten,kmlFileName,kml4Marker)
537 328
538 return initializeStringForGoogleMaps 329 return kmlFileName
539 330
540 def getGoogleMapString(self,kml): 331 def getGoogleMapString(self,kml):
541 logging.debug("getGoogleMapString") 332 logging.debug("getGoogleMapString")
542 printed= '<body %s> '%kml +"""\n <div id="map_canvas" style="width: 98%; height: 95%"> </div> \n </body>" \n </html>""" 333 printed= '<body %s> '%kml +"""\n <div id="map_canvas" style="width: 98%; height: 95%"> </div> \n </body>" \n </html>"""
543 return printed 334 return printed
544 335
545 def getPoint4GISid(self,gis_id): 336 def getPoint4GISid(self,gis_id):
546 j=0 337 j=0
547 while (True): 338 coords=(0,0)
548 j=j+1 339 if gis_id != None:
549 if (j>100): # FJK: just to prevent endless loops 340 while (True):
550 break 341 j=j+1
551 if (gis_id.isdigit()): # FJK: regular exit from while-loop 342 if (j>100): # FJK: just to prevent endless loops
552 break 343 break
553 else: 344 if (gis_id.isdigit()): # FJK: regular exit from while-loop
554 gis_id=gis_id.strip('abcdefghijklmnopqrstuvwxyz_') # FJK: to strip all letters 345 break
555 gis_id=gis_id.strip() # FJK: to strip all whitespaces 346 else:
556 resultpoint = [0,0] 347 gis_id=gis_id.strip('abcdefghijklmnopqrstuvwxyz_') # FJK: to strip all letters
557 results = None 348 gis_id=gis_id.strip() # FJK: to strip all whitespaces
558 try: 349 resultpoint = [0,0]
350 results = None
351 try:
559 if int(gis_id)>0: 352 if int(gis_id)>0:
560 SQL="SELECT x_coord,y_coord FROM chgis.chgis_coords WHERE gis_id LIKE cast("+ str(gis_id) +" as text);" 353 SQL="SELECT x_coord,y_coord FROM chgis.chgis_coords WHERE gis_id LIKE cast("+ str(gis_id) +" as text);"
561 results=self.ZSQLSimpleSearch(SQL) 354 results=self.ZSQLSimpleSearch(SQL)
562 #print results 355 #print results
563 if results != None: 356 if results != None:
565 resultpoint=[getattr(result,str('x_coord')),getattr(result,str('y_coord'))] 358 resultpoint=[getattr(result,str('x_coord')),getattr(result,str('y_coord'))]
566 if resultpoint !=[0,0]: 359 if resultpoint !=[0,0]:
567 return resultpoint 360 return resultpoint
568 else: 361 else:
569 coords=self.getCoordsFromREST_gisID(joinid) 362 coords=self.getCoordsFromREST_gisID(joinid)
570 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;" 363 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;"
571 returnstring=self.ZSQLSimpleSearch(SQL) 364 returnstring=self.ZSQLSimpleSearch(SQL)
572 return coords[0] 365 return coords[0]
573 except: 366 except:
574 error="gis_id not to interpretable:"+str(gis_id) 367 return "gis_id not to interpretable:"+str(gis_id)
368 else:
369 return coords[0]
575 370
576 def getCoordsFromREST_gisID(self,gis_id): 371 def getCoordsFromREST_gisID(self,gis_id):
577 coordlist=[] 372 coordlist=[]
578 i=0 373 i=0
579 while (i<5 and coordlist==[]): 374 while (i<5 and coordlist==[]):
614 file4Read=open(name,'r') 409 file4Read=open(name,'r')
615 fileInZope=datadir.manage_addFile(id=fileid,file=file4Read) 410 fileInZope=datadir.manage_addFile(id=fileid,file=file4Read)
616 return "Write successful" 411 return "Write successful"
617 # except: 412 # except:
618 # return "Could not write" 413 # return "Could not write"
619 414
620 415
621 def manage_editRestDbInterface(self, title=None, connection_id=None, 416 def manage_editRestDbInterface(self, title=None, connection_id=None,
622 REQUEST=None): 417 REQUEST=None):
623 """Change the object""" 418 """Change the object"""
624 if title is not None: 419 if title is not None:
644 439
645 #checkPermission=getSecurityManager().checkPermission 440 #checkPermission=getSecurityManager().checkPermission
646 REQUEST.RESPONSE.redirect('manage_main') 441 REQUEST.RESPONSE.redirect('manage_main')
647 442
648 443
444
445 # constructors = (
446 # REST_test.manage_addRESTclassForm,
447 # REST_test.manage_addRESTclass
448 # )
449
450
451 context.registerClass(
452 RestDbInterface.RestDbInterface,
453 constructors = (
454 RestDbInterface.manage_addRestDbInterfaceForm,
455 RestDbInterface.manage_addRestDbInterface
456 )
457 )