Mercurial > hg > ZDBInterface
diff WritableRestDbInterface.py @ 0:09361041be51
first checkin
| author | casties |
|---|---|
| date | Fri, 11 Feb 2011 15:05:23 +0100 |
| parents | |
| children | 48ed91b29784 |
line wrap: on
line diff
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/WritableRestDbInterface.py Fri Feb 11 15:05:23 2011 +0100 @@ -0,0 +1,275 @@ +''' +Created on 11.2.2011 + +@author: casties, fknauft +''' + +import logging +import re +import time +import datetime +import urllib + +from RestDbInterface import * + + + +class WritableRestDbInterface(RestDbInterface): + """Object for RESTful database queries + path schema: /db/{schema}/{table}/ + omitting table gives a list of schemas + omitting table and schema gives a list of schemas + """ + + meta_type="rwRESTdb" + + + def PUT(self, REQUEST, RESPONSE): + """ + Implement WebDAV/HTTP PUT/FTP put method for this object. + """ + logging.debug("RestDbInterface PUT") + #logging.debug("req=%s"%REQUEST) + #self.dav__init(REQUEST, RESPONSE) + #self.dav__simpleifhandler(REQUEST, RESPONSE) + # ReST path was stored in request + path = REQUEST.get('restdb_path',[]) + if len(path) == 3: + schema = path[1] + tablename = path[2] + file = REQUEST.get("create_table_file",None) + if file is None: + RESPONSE.setStatus(400) + return + + fields = None + fieldsStr = REQUEST.get("create_table_fields",None) + logging.debug("put with schema=%s table=%s file=%s fields=%s"%(schema,tablename,file,repr(fieldsStr))) + if fieldsStr is not None: + # unpack fields + fields = [{"name":n, "type": t} for (n,t) in [f.split(":") for f in fieldsStr.split(",")]] + + ret = self.createTableFromXML(schema, tablename, file, fields) + # return the result as JSON + format = REQUEST.get("format","JSON") + if format == "JSON": + RESPONSE.setHeader("Content-Type", "application/json") + json.dump(ret, RESPONSE) + + elif format == "JSONHTML": + RESPONSE.setHeader("Content-Type", "text/html") + RESPONSE.write("<html>\n<body>\n<pre>") + json.dump(ret, RESPONSE) + RESPONSE.write("</pre>\n</body>\n</html>") + + else: + # 400 Bad Request + RESPONSE.setStatus(400) + return + + def checkTable(self,format,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 format == "JSON": + RESPONSE.setHeader("Content-Type", "application/json") + json.dump(res, RESPONSE) + + elif format == "JSONHTML": + RESPONSE.setHeader("Content-Type", "text/html") + RESPONSE.write("<html>\n<body>\n<pre>") + json.dump(res, RESPONSE) + RESPONSE.write("</pre>\n</body>\n</html>") + + else: + return "ERROR: invalid format" + + 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""" + logging.debug("createEmptyTable") + + sqlFields = [] + for f in fields: + if isinstance(f,dict): + # {name: XX, type: YY} + name = sqlName(f['name']) + type = f['type'] + if hasattr(self, 'toSqlTypeMap'): + sqltype = self.toSqlTypeMap[type] + else: + sqltype = 'text' + + else: + # name only + name = sqlName(f) + type = 'text' + sqltype = 'text' + + sqlFields.append({'name':name, 'type':type, 'sqltype':sqltype}) + + if self.hasTable(schema,table): + # TODO: find owner + if not self.isAllowed("update", schema, table): + raise Unauthorized + self.executeSQL('drop table "%s"."%s"'%(schema,table),hasResult=False) + else: + if not self.isAllowed("create", schema, table): + raise Unauthorized + + fieldString = ", ".join(['"%s" %s'%(f['name'],f['sqltype']) for f in sqlFields]) + sqlString = 'create table "%s"."%s" (%s)'%(schema,table,fieldString) + logging.debug("createemptytable: SQL=%s"%sqlString) + self.executeSQL(sqlString,hasResult=False) + self.setTableMetaTypes(schema,table,sqlFields) + return sqlFields + + def createTableFromXML(self,schema,table,data, fields=None): + """create or replace a table with the given XML data""" + logging.debug("createTableFromXML schema=%s table=%s data=%s fields=%s"%(schema,table,data,fields)) + tablename = sqlName(table) + self.importExcelXML(schema, tablename, data, fields) + return {"tablename": tablename} + + def importExcelXML(self,schema,table,xmldata,fields=None,fieldsOnly=False): + ''' + Import XML file in Excel format into the table + @param table: name of the table the xml shall be imported into + ''' + from xml.dom.pulldom import parseString,parse + + if not (fieldsOnly or self.isAllowed("create", schema, table)): + raise Unauthorized + + namespace = "urn:schemas-microsoft-com:office:spreadsheet" + containerTagName = "Table" + rowTagName = "Row" + colTagName = "Cell" + dataTagName = "Data" + xmlFields = [] + sqlFields = [] + numFields = 0 + sqlInsert = None + + logging.debug("import excel xml") + + ret="" + if isinstance(xmldata, str): + logging.debug("importXML reading string data") + doc=parseString(xmldata) + else: + logging.debug("importXML reading file data") + doc=parse(xmldata) + + cnt = 0 + while True: + node=doc.getEvent() + + if node is None: + break + + else: + #logging.debug("tag=%s"%node[1].localName) + if node[1].localName is not None: + tagName = node[1].localName.lower() + else: + # ignore non-tag nodes + continue + + if tagName == rowTagName.lower(): + # start of row + doc.expandNode(node[1]) + cnt += 1 + if cnt == 1: + # first row -- field names + names=node[1].getElementsByTagNameNS(namespace, dataTagName) + for name in names: + fn = getTextFromNode(name) + xmlFields.append({'name':sqlName(fn),'type':'text'}) + + if fieldsOnly: + # return just field names + return xmlFields + + # create table + if fields is None: + fields = xmlFields + + sqlFields = self.createEmptyTable(schema, table, fields) + 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) + + else: + # following rows are data + colNodes=node[1].getElementsByTagNameNS(namespace, colTagName) + data = [] + hasData = False + for colNode in colNodes: + dataNodes=colNode.getElementsByTagNameNS(namespace, dataTagName) + if len(dataNodes) > 0: + val = getTextFromNode(dataNodes[0]) + hasData = True + else: + val = None + + data.append(val) + + if not hasData: + # ignore empty rows + continue + + # fix number of data fields + if len(data) > numFields: + del data[numFields:] + elif len(data) < numFields: + missFields = numFields - len(data) + data.extend(missFields * [None,]) + + logging.debug("importexcel sqlinsert=%s data=%s"%(sqlInsert,data)) + self.executeSQL(sqlInsert, data, hasResult=False) + + return cnt + + +manage_addWritableRestDbInterfaceForm=PageTemplateFile('zpt/addWritableRestDbInterface',globals()) + +def manage_addWritableRestDbInterface(self, id, title='', label='', description='', + createPublic=0, + createUserF=0, + REQUEST=None): + """Add a new object with id *id*.""" + + ob=WritableRestDbInterface(str(id),title) + self._setObject(id, ob) + + #checkPermission=getSecurityManager().checkPermission + REQUEST.RESPONSE.redirect('manage_main') + +