|
42
|
1 '''
|
|
|
2 Created on 2.9.2010
|
|
|
3
|
|
|
4 @author: casties
|
|
|
5 '''
|
|
|
6
|
|
|
7 from OFS.Folder import Folder
|
|
|
8 from Products.PageTemplates.PageTemplateFile import PageTemplateFile
|
|
|
9 import logging
|
|
|
10 import re
|
|
|
11 import psycopg2
|
|
|
12 import json
|
|
|
13 import time
|
|
|
14
|
|
|
15 from zope.interface import implements
|
|
|
16 from zope.publisher.interfaces import IPublishTraverse
|
|
|
17 from ZPublisher.BaseRequest import DefaultPublishTraverse
|
|
|
18 import Shared.DC.ZRDB.DA
|
|
|
19 from Products.ZSQLMethods.SQL import SQLConnectionIDs
|
|
|
20
|
|
43
|
21 from RestDbInterface import *
|
|
42
|
22
|
|
|
23
|
|
|
24 class RestDbJsonStore(RestDbInterface):
|
|
|
25 """Object for RESTful database queries
|
|
|
26 path schema: /db/{schema}/{table}/
|
|
|
27 omitting table gives a list of schemas
|
|
|
28 omitting table and schema gives a list of schemas
|
|
|
29 """
|
|
|
30 implements(IPublishTraverse)
|
|
|
31
|
|
|
32 meta_type="RESTdbJSONStore"
|
|
|
33 manage_options=Folder.manage_options+(
|
|
|
34 {'label':'Config','action':'manage_editRestDbJsonStoreForm'},
|
|
|
35 )
|
|
|
36
|
|
|
37 # management templates
|
|
|
38 manage_editRestDbJsonStoreForm=PageTemplateFile('zpt/editRestDbJsonStore',globals())
|
|
|
39
|
|
|
40 # JSON_* templates are scripts
|
|
|
41 def JSON_index(self,data):
|
|
|
42 """JSON index function"""
|
|
|
43 self.REQUEST.RESPONSE.setHeader("Content-Type", "application/json")
|
|
|
44 json.dump(data, self.REQUEST.RESPONSE)
|
|
|
45
|
|
|
46 def JSON_schema(self,data,schema):
|
|
|
47 """JSON index function"""
|
|
|
48 self.REQUEST.RESPONSE.setHeader("Content-Type", "application/json")
|
|
|
49 json.dump(data, self.REQUEST.RESPONSE)
|
|
|
50
|
|
|
51 def JSON_schema_table(self,data,tablename):
|
|
|
52 """JSON index function"""
|
|
|
53 self.REQUEST.RESPONSE.setHeader("Content-Type", "application/json")
|
|
|
54 json.dump(data, self.REQUEST.RESPONSE)
|
|
|
55
|
|
|
56
|
|
|
57 def __init__(self, id, title, connection_id=None):
|
|
|
58 """init"""
|
|
|
59 self.id = id
|
|
|
60 self.title = title
|
|
|
61 # database connection id
|
|
|
62 self.connection_id = connection_id
|
|
|
63
|
|
|
64 def getJsonString(self,object):
|
|
|
65 """returns a JSON formatted string from object"""
|
|
|
66 return json.dumps(object)
|
|
|
67
|
|
|
68
|
|
|
69 def publishTraverse(self,request,name):
|
|
|
70 """change the traversal"""
|
|
|
71 # get stored path
|
|
|
72 path = request.get('restdb_path', [])
|
|
|
73 logging.debug("publishtraverse: name=%s restdb_path=%s"%(name,path))
|
|
|
74
|
|
|
75 if name in ("index_html", "PUT"):
|
|
|
76 # end of traversal
|
|
|
77 if request.get("method") == "POST" and request.get("action",None) == "PUT":
|
|
|
78 # fake PUT by POST with action=PUT
|
|
|
79 name = "PUT"
|
|
|
80
|
|
|
81 return getattr(self, name)
|
|
|
82 #TODO: should we check more?
|
|
|
83 else:
|
|
|
84 # traverse
|
|
|
85 if len(path) == 0:
|
|
|
86 # first segment
|
|
|
87 if name == 'db':
|
|
|
88 # virtual path -- continue traversing
|
|
|
89 path = [name]
|
|
|
90 request['restdb_path'] = path
|
|
|
91 else:
|
|
|
92 # try real path
|
|
|
93 tr = DefaultPublishTraverse(self, request)
|
|
|
94 ob = tr.publishTraverse(request, name)
|
|
|
95 return ob
|
|
|
96 else:
|
|
|
97 path.append(name)
|
|
|
98
|
|
|
99 # continue traversing
|
|
|
100 return self
|
|
|
101
|
|
|
102 def index_html(self,REQUEST,RESPONSE):
|
|
|
103 """index method"""
|
|
|
104 # ReST path was stored in request
|
|
|
105 path = REQUEST.get('restdb_path',[])
|
|
|
106
|
|
|
107 # type and format are real parameter
|
|
|
108 resultFormat = REQUEST.get('format','HTML').upper()
|
|
|
109 queryType = REQUEST.get('type',None)
|
|
|
110
|
|
|
111 logging.debug("index_html path=%s resultFormat=%s queryType=%s"%(path,resultFormat,queryType))
|
|
|
112
|
|
|
113 if queryType is not None:
|
|
|
114 # non-empty queryType -- look for template
|
|
|
115 pt = getattr(self.template, "%s_%s"%(resultFormat,queryType), None)
|
|
|
116 if pt is not None:
|
|
|
117 return pt(format=resultFormat,type=queryType,path=path)
|
|
|
118
|
|
|
119 if len(path) == 1:
|
|
|
120 # list of schemas
|
|
|
121 return self.showListOfSchemas(resultFormat=resultFormat)
|
|
|
122 elif len(path) == 2:
|
|
|
123 # list of tables
|
|
|
124 return self.showListOfTables(resultFormat=resultFormat,schema=path[1])
|
|
|
125 elif len(path) == 3:
|
|
|
126 # table
|
|
|
127 if REQUEST.get("method") == "POST" and REQUEST.get("create_table_file",None) is not None:
|
|
|
128 # POST to table to check
|
|
|
129 return self.checkTable(resultFormat=resultFormat,schema=path[1],table=path[2])
|
|
|
130 # else show table
|
|
|
131 return self.showTable(resultFormat=resultFormat,schema=path[1],table=path[2])
|
|
|
132
|
|
|
133 # don't know what to do
|
|
|
134 return str(REQUEST)
|
|
|
135
|
|
|
136 def PUT(self, REQUEST, RESPONSE):
|
|
|
137 """
|
|
|
138 Implement WebDAV/HTTP PUT/FTP put method for this object.
|
|
|
139 """
|
|
|
140 logging.debug("RestDbInterface PUT")
|
|
|
141 #logging.debug("req=%s"%REQUEST)
|
|
|
142 #self.dav__init(REQUEST, RESPONSE)
|
|
|
143 #self.dav__simpleifhandler(REQUEST, RESPONSE)
|
|
|
144 # ReST path was stored in request
|
|
|
145 path = REQUEST.get('restdb_path',[])
|
|
|
146 if len(path) == 3:
|
|
|
147 schema = path[1]
|
|
|
148 tablename = path[2]
|
|
|
149 file = REQUEST.get("create_table_file",None)
|
|
|
150 if file is None:
|
|
|
151 RESPONSE.setStatus(400)
|
|
|
152 return
|
|
|
153
|
|
|
154 fields = None
|
|
|
155 fieldsStr = REQUEST.get("create_table_fields",None)
|
|
|
156 logging.debug("put with schema=%s table=%s file=%s fields=%s"%(schema,tablename,file,repr(fieldsStr)))
|
|
|
157 if fieldsStr is not None:
|
|
|
158 # unpack fields
|
|
|
159 fields = [{"name":n, "type": t} for (n,t) in [f.split(":") for f in fieldsStr.split(",")]]
|
|
|
160
|
|
|
161 ret = self.createTableFromXML(schema, tablename, file, fields)
|
|
|
162 # return the result as JSON
|
|
|
163 format = REQUEST.get("format","JSON")
|
|
|
164 if format == "JSON":
|
|
|
165 RESPONSE.setHeader("Content-Type", "application/json")
|
|
|
166 json.dump(ret, RESPONSE)
|
|
|
167
|
|
|
168 elif format == "JSONHTML":
|
|
|
169 RESPONSE.setHeader("Content-Type", "text/html")
|
|
|
170 RESPONSE.write("<html>\n<body>\n<pre>")
|
|
|
171 json.dump(ret, RESPONSE)
|
|
|
172 RESPONSE.write("</pre>\n</body>\n</html>")
|
|
|
173
|
|
|
174 else:
|
|
|
175 # 400 Bad Request
|
|
|
176 RESPONSE.setStatus(400)
|
|
|
177 return
|
|
|
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
|
|
|
660 manage_addRestDbInterfaceForm=PageTemplateFile('zpt/addRestDbInterface',globals())
|
|
|
661
|
|
|
662 def manage_addRestDbInterface(self, id, title='', label='', description='',
|
|
|
663 createPublic=0,
|
|
|
664 createUserF=0,
|
|
|
665 REQUEST=None):
|
|
|
666 """Add a new object with id *id*."""
|
|
|
667
|
|
|
668 ob=RestDbInterface(str(id),title)
|
|
|
669 self._setObject(id, ob)
|
|
|
670
|
|
|
671 #checkPermission=getSecurityManager().checkPermission
|
|
|
672 REQUEST.RESPONSE.redirect('manage_main')
|
|
|
673
|
|
|
674
|
