# HG changeset patch
# User casties
# Date 1282576478 -7200
# Node ID 2b73f868d34f79a9e5c19a84b2731f5b686ef521
# Parent cef1bfa821cbbeca20c4544179d6db16a6bca9f2
NEW - # 12: create table and upload data
https://it-dev.mpiwg-berlin.mpg.de/tracs/GIS/ticket/12
implemented checkTable
diff -r cef1bfa821cb -r 2b73f868d34f RestDbInterface.py
--- a/RestDbInterface.py Mon Aug 23 10:57:59 2010 +0200
+++ b/RestDbInterface.py Mon Aug 23 17:14:38 2010 +0200
@@ -181,31 +181,31 @@
# ReST path was stored in request
path = REQUEST.get('restdb_path',[])
- # type and format are real parameter
- format = REQUEST.get('format','HTML').upper()
- type = REQUEST.get('type',None)
+ # queryType and resultFormat are real parameter
+ resultFormat = REQUEST.get('format','HTML').upper()
+ queryType = REQUEST.get('queryType',None)
- logging.debug("index_html path=%s format=%s type=%s"%(path,format,type))
+ logging.debug("index_html path=%s resultFormat=%s queryType=%s"%(path,resultFormat,queryType))
- if type is not None:
- # non-empty type -- look for template
- pt = getattr(self.template, "%s_%s"%(format,type), None)
+ if queryType is not None:
+ # non-empty queryType -- look for template
+ pt = getattr(self.template, "%s_%s"%(resultFormat,queryType), None)
if pt is not None:
- return pt(format=format,type=type,path=path)
+ return pt(format=resultFormat,type=queryType,path=path)
if len(path) == 1:
# list of schemas
- return self.showListOfSchemas(format=format)
+ return self.showListOfSchemas(resultFormat=resultFormat)
elif len(path) == 2:
# list of tables
- return self.showListOfTables(format=format,schema=path[1])
+ return self.showListOfTables(resultFormat=resultFormat,schema=path[1])
elif len(path) == 3:
# table
if REQUEST.get("method") == "POST" and REQUEST.get("create_table_file",None) is not None:
# POST to table to check
- return self.checkTable(format=format,schema=path[1],table=path[2])
+ return self.checkTable(resultFormat=resultFormat,schema=path[1],table=path[2])
# else show table
- return self.showTable(format=format,schema=path[1],table=path[2])
+ return self.showTable(resultFormat=resultFormat,schema=path[1],table=path[2])
# don't know what to do
return str(REQUEST)
@@ -230,23 +230,24 @@
logging.debug("put with schema=%s table=%s file=%s"%(schema,tablename,file))
ret = self.createTableFromXML(schema, tablename, file)
- resultType = REQUEST.get("result_type","application/json")
- if resultType == "application/json":
+ # return the result as JSON
+ format = REQUEST.get("format","JSON")
+ if format == "JSON":
RESPONSE.setHeader("Content-Type", "application/json")
json.dump(ret, RESPONSE)
- elif resultType == "html/json":
+
+ elif format == "JSONHTML":
RESPONSE.setHeader("Content-Type", "text/html")
RESPONSE.write("\n\n")
json.dump(ret, RESPONSE)
RESPONSE.write("\n\n")
-
else:
# 400 Bad Request
RESPONSE.setStatus(400)
return
- def showTable(self,format='XML',schema='public',table=None,REQUEST=None,RESPONSE=None):
+ def showTable(self,resultFormat='XML',schema='public',table=None,REQUEST=None,RESPONSE=None):
"""returns PageTemplate with tables"""
logging.debug("showtable")
# should be cross-site accessible
@@ -254,15 +255,15 @@
RESPONSE = self.REQUEST.RESPONSE
RESPONSE.setHeader('Access-Control-Allow-Origin', '*')
# GIS gets special treatment
- if format=="GIS":
+ if resultFormat=="GIS":
id = REQUEST.get('id',[])
doc = REQUEST.get('doc',None)
return self.showGoogleMap(schema=path[1],table=path[2],id=id,doc=doc)
# everything else has its own template
- pt = getattr(self.template, '%s_schema_table'%format, None)
+ pt = getattr(self.template, '%s_schema_table'%resultFormat, None)
if pt is None:
- return "ERROR!! template %s_schema_table not found"%format
+ return "ERROR!! template %s_schema_table not found"%resultFormat
data = self.getTable(schema,table)
return pt(data=data,tablename=table)
@@ -273,7 +274,14 @@
data = self.executeSQL('select * from "%s"."%s"'%(schema,table))
return data
- def showListOfTables(self,format='XML',schema='public',REQUEST=None,RESPONSE=None):
+ def hasTable(self,schema='public',table=None,username='guest'):
+ """return if table exists"""
+ logging.debug("hastable")
+ data = self.executeSQL('select 1 from information_schema.tables where table_schema=%s and table_name=%s',(schema,table))
+ ret = bool(data['rows'])
+ return ret
+
+ def showListOfTables(self,resultFormat='XML',schema='public',REQUEST=None,RESPONSE=None):
"""returns PageTemplate with list of tables"""
logging.debug("showlistoftables")
# should be cross-site accessible
@@ -281,9 +289,9 @@
RESPONSE = self.REQUEST.RESPONSE
RESPONSE.setHeader('Access-Control-Allow-Origin', '*')
- pt = getattr(self.template, '%s_schema'%format, None)
+ pt = getattr(self.template, '%s_schema'%resultFormat, None)
if pt is None:
- return "ERROR!! template %s_schema not found"%format
+ return "ERROR!! template %s_schema not found"%resultFormat
data = self.getListOfTables(schema)
return pt(data=data,schema=schema)
@@ -300,7 +308,7 @@
data=self.executeSQL(qstr)
return data
- def showListOfSchemas(self,format='XML',REQUEST=None,RESPONSE=None):
+ def showListOfSchemas(self,resultFormat='XML',REQUEST=None,RESPONSE=None):
"""returns PageTemplate with list of schemas"""
logging.debug("showlistofschemas")
# should be cross-site accessible
@@ -308,9 +316,9 @@
RESPONSE = self.REQUEST.RESPONSE
RESPONSE.setHeader('Access-Control-Allow-Origin', '*')
- pt = getattr(self.template, '%s_index'%format, None)
+ pt = getattr(self.template, '%s_index'%resultFormat, None)
if pt is None:
- return "ERROR!! template %s_index not found"%format
+ return "ERROR!! template %s_index not found"%resultFormat
data = self.getListOfSchemas()
return pt(data=data)
@@ -337,6 +345,47 @@
self.REQUEST.RESPONSE.setHeader("Content-Type", "application/json")
json.dump(data, self.REQUEST.RESPONSE)
+ def checkTable(self,resultFormat,schema,table,REQUEST=None,RESPONSE=None):
+ """check the table.
+ returns valid data fields and table name."""
+ if REQUEST is None:
+ REQUEST = self.REQUEST
+ RESPONSE = REQUEST.RESPONSE
+
+ file = REQUEST.get("create_table_file",None)
+ res = self.checkTableFromXML(schema, table, file)
+ logging.debug("checkTable result=%s"%repr(res))
+ # return the result as JSON
+ if resultFormat == "JSON":
+ RESPONSE.setHeader("Content-Type", "application/json")
+ json.dump(res, RESPONSE)
+
+ elif resultFormat == "JSONHTML":
+ RESPONSE.setHeader("Content-Type", "text/html")
+ RESPONSE.write("\n\n")
+ json.dump(res, RESPONSE)
+ RESPONSE.write("\n\n")
+
+ else:
+ return "ERROR: invalid resultFormat"
+
+ def checkTableFromXML(self,schema,table,data,REQUEST=None,RESPONSE=None):
+ """check the table with the given XML data.
+ returns valid data fields and table name."""
+ logging.debug("checkTableFromXML schema=%s table=%s"%(schema,table))
+ # clean table name
+ tablename = sqlName(table)
+ tableExists = self.hasTable(schema, table)
+ if data is None:
+ fieldNames = []
+ else:
+ # get list of field names from upload file
+ fields = self.importExcelXML(schema,tablename,data,fieldsOnly=True)
+
+ res = {'tablename': tablename, 'table_exists': tableExists}
+ res['fields'] = fields
+ return res
+
def createEmptyTable(self,schema,table,fields):
"""create a table with the given fields
returns list of created fields"""
@@ -366,10 +415,9 @@
fieldString = ", ".join(['"%s" %s'%(f['name'],f['type']) for f in sqlFields])
sqlString = 'create table "%s"."%s" (%s)'%(schema,table,fieldString)
logging.debug("createemptytable: SQL=%s"%sqlString)
- ret = self.executeSQL(sqlString,hasResult=False)
-
+ self.executeSQL(sqlString,hasResult=False)
return sqlFields
-
+
def createTableFromXML(self,schema,table,data):
"""create or replace a table with the given XML data"""
logging.debug("createTableFromXML schema=%s table=%s data=%s"%(schema,table,data))
@@ -377,12 +425,12 @@
self.importExcelXML(schema,tablename, data)
return {"tablename": tablename}
- def importExcelXML(self,schema,table,xmldata,fieldNamesOnly=False):
+ def importExcelXML(self,schema,table,xmldata,fieldsOnly=False):
'''
Import XML file in Excel format into the table
@param table: name of the table the xml shall be imported into
@param containerTagName: XML-Tag which describes a dataset
- @param data: data to be imported
+ @param data: data to be imported (string or filehandle)
@param identify: (optional) field res. tag which identifies a entry uniquely for updating purposes.
@param RESPONSE: (optional)
'''
@@ -393,7 +441,7 @@
rowTagName = "Row"
colTagName = "Cell"
dataTagName = "Data"
- fieldNames = []
+ xmlFields = []
sqlFields = []
numFields = 0
sqlInsert = None
@@ -432,19 +480,19 @@
names=node[1].getElementsByTagNameNS(namespace, dataTagName)
for name in names:
fn = getTextFromNode(name)
- fieldNames.append(fn)
+ xmlFields.append({'name':sqlName(fn),'type':'text'})
- if fieldNamesOnly:
+ if fieldsOnly:
# return just field names
- return fieldNames
+ return xmlFields
# create table
- sqlFields = self.createEmptyTable(schema, table, fieldNames)
+ sqlFields = self.createEmptyTable(schema, table, xmlFields)
numFields = len(sqlFields)
fieldString = ", ".join(['"%s"'%f['name'] for f in sqlFields])
valString = ", ".join(["%s" for f in sqlFields])
sqlInsert = 'insert into "%s"."%s" (%s) values (%s)'%(schema,table,fieldString,valString)
- logging.debug("importexcelsql: sqlInsert=%s"%sqlInsert)
+ #logging.debug("importexcelsql: sqlInsert=%s"%sqlInsert)
else:
# following rows are data