Mercurial > hg > ChinaGisRestApi

comparison RestDbInterface.py @ 31:6d2055f1b4fa

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
more work on types
author casties
date Tue, 31 Aug 2010 10:54:00 +0200
parents 51db9e78bf98
children c732c2ff61d9
comparison
equal deleted inserted replaced
30:51db9e78bf98 31:6d2055f1b4fa
48 s = re.sub(r'[^A-Za-z0-9_]','_',s) 48 s = re.sub(r'[^A-Za-z0-9_]','_',s)
49 if lc: 49 if lc:
50 return s.lower() 50 return s.lower()
51 51
52 return s 52 return s
53
54 gisToSqlTypeMap = {
55 "text": "text",
56 "number": "number",
57 "id": "text",
58 "gis_id": "integer",
59 "coord_lat": "float",
60 "coord_lon": "float"
61 }
53 62
54 class RestDbInterface(Folder): 63 class RestDbInterface(Folder):
55 """Object for RESTful database queries 64 """Object for RESTful database queries
56 path schema: /db/{schema}/{table}/ 65 path schema: /db/{schema}/{table}/
57 omitting table gives a list of schemas 66 omitting table gives a list of schemas
153 return {"fields":fields, "rows":data} 162 return {"fields":fields, "rows":data}
154 else: 163 else:
155 cur.close() 164 cur.close()
156 return None 165 return None
157 166
167 def setTableMetaTypes(self,schema,table,fields):
168 """sets the GIS meta information for table"""
169 logging.debug("settablemetatypes schema=%s, table=%s, fields=%s"%(schema,table,fields))
170 # TODO: what now?
158 171
159 def publishTraverse(self,request,name): 172 def publishTraverse(self,request,name):
160 """change the traversal""" 173 """change the traversal"""
161 # get stored path 174 # get stored path
162 path = request.get('restdb_path', []) 175 path = request.get('restdb_path', [])
245 fieldsStr = REQUEST.get("create_table_fields",None) 258 fieldsStr = REQUEST.get("create_table_fields",None)
246 logging.debug("put with schema=%s table=%s file=%s fields=%s"%(schema,tablename,file,repr(fieldsStr))) 259 logging.debug("put with schema=%s table=%s file=%s fields=%s"%(schema,tablename,file,repr(fieldsStr)))
247 if fieldsStr is not None: 260 if fieldsStr is not None:
248 # unpack fields 261 # unpack fields
249 fields = [{"name":n, "type": t} for (n,t) in [f.split(":") for f in fieldsStr.split(",")]] 262 fields = [{"name":n, "type": t} for (n,t) in [f.split(":") for f in fieldsStr.split(",")]]
263
250 ret = self.createTableFromXML(schema, tablename, file, fields) 264 ret = self.createTableFromXML(schema, tablename, file, fields)
251 # return the result as JSON 265 # return the result as JSON
252 format = REQUEST.get("format","JSON") 266 format = REQUEST.get("format","JSON")
253 if format == "JSON": 267 if format == "JSON":
254 RESPONSE.setHeader("Content-Type", "application/json") 268 RESPONSE.setHeader("Content-Type", "application/json")
397 for f in fields: 411 for f in fields:
398 if isinstance(f,dict): 412 if isinstance(f,dict):
399 # {name: XX, type: YY} 413 # {name: XX, type: YY}
400 name = sqlName(f['name']) 414 name = sqlName(f['name'])
401 type = f['type'] 415 type = f['type']
416 sqltype = gisToSqlTypeMap[type]
402 417
403 else: 418 else:
404 # name only 419 # name only
405 name = sqlName(f) 420 name = sqlName(f)
406 type = 'text' 421 type = 'text'
422 sqltype = 'text'
407 423
408 sqlFields.append({'name':name, 'type':type}) 424 sqlFields.append({'name':name, 'type':type, 'sqltype':sqltype})
409 425
410 # drop table if it exists 426 self.executeSQL('drop table if exists "%s"."%s"'%(schema,table),hasResult=False)
411 try: 427 fieldString = ", ".join(['"%s" %s'%(f['name'],f['sqltype']) for f in sqlFields])
412 res = self.executeSQL('select * from "%s"."%s" where 1=0'%(schema,table))
413 logging.debug("createemptytable: table %s.%s exists"%(schema,table))
414 self.executeSQL('drop table "%s"."%s"'%(schema,table),hasResult=False)
415 except:
416 pass
417
418 fieldString = ", ".join(['"%s" %s'%(f['name'],f['type']) for f in sqlFields])
419 sqlString = 'create table "%s"."%s" (%s)'%(schema,table,fieldString) 428 sqlString = 'create table "%s"."%s" (%s)'%(schema,table,fieldString)
420 logging.debug("createemptytable: SQL=%s"%sqlString) 429 logging.debug("createemptytable: SQL=%s"%sqlString)
421 self.executeSQL(sqlString,hasResult=False) 430 self.executeSQL(sqlString,hasResult=False)
431 self.setTableMetaTypes(schema,table,sqlFields)
422 return sqlFields 432 return sqlFields
423 433
424 def createTableFromXML(self,schema,table,data, fields=None): 434 def createTableFromXML(self,schema,table,data, fields=None):
425 """create or replace a table with the given XML data""" 435 """create or replace a table with the given XML data"""
426 logging.debug("createTableFromXML schema=%s table=%s data=%s fields=%s"%(schema,table,data,fields)) 436 logging.debug("createTableFromXML schema=%s table=%s data=%s fields=%s"%(schema,table,data,fields))
485 # return just field names 495 # return just field names
486 return xmlFields 496 return xmlFields
487 497
488 # create table 498 # create table
489 if fields is None: 499 if fields is None:
490 sqlFields = self.createEmptyTable(schema, table, xmlFields) 500 fields = xmlFields
501
502 sqlFields = self.createEmptyTable(schema, table, fields)
491 numFields = len(sqlFields) 503 numFields = len(sqlFields)
492 fieldString = ", ".join(['"%s"'%f['name'] for f in sqlFields]) 504 fieldString = ", ".join(['"%s"'%f['name'] for f in sqlFields])
493 valString = ", ".join(["%s" for f in sqlFields]) 505 valString = ", ".join(["%s" for f in sqlFields])
494 sqlInsert = 'insert into "%s"."%s" (%s) values (%s)'%(schema,table,fieldString,valString) 506 sqlInsert = 'insert into "%s"."%s" (%s) values (%s)'%(schema,table,fieldString,valString)
495 #logging.debug("importexcelsql: sqlInsert=%s"%sqlInsert) 507 #logging.debug("importexcelsql: sqlInsert=%s"%sqlInsert)