--- a/RestDbInterface.py	Wed Aug 18 15:00:25 2010 +0200
+++ b/RestDbInterface.py	Wed Aug 18 16:42:07 2010 +0200
@@ -8,6 +8,9 @@
 from Products.PageTemplates.PageTemplateFile import PageTemplateFile
 from Products.ZSQLExtend import ZSQLExtend
 import logging
+import re
+import psycopg2
+import json
 import time
 
 from zope.interface import implements
@@ -20,6 +23,34 @@
 from Products.ZSQLMethods.SQL import SQLConnectionIDs
 
 
+def getTextFromNode(node):
+    """get the cdata content of a XML node"""
+    if node is None:
+        return ""
+    
+    if isinstance(node, list):
+        nodelist = node
+    else:
+        nodelist=node.childNodes
+
+    rc = ""
+    for node in nodelist:
+        if node.nodeType == node.TEXT_NODE:
+           rc = rc + node.data
+    return rc
+
+def sqlName(s,lc=True):
+    """returns restricted ASCII-only version of string"""
+    if s is None:
+        return ""
+    
+    # all else -> "_"
+    s = re.sub(r'[^A-Za-z0-9_]','_',s)
+    if lc:
+        return s.lower()
+    
+    return s
+
 class RestDbInterface(Folder):
     """Object for RESTful database queries
     path schema: /db/{schema}/{table}/
@@ -54,11 +85,9 @@
         self.connection_id = connection_id
         # create template folder
         self.manage_addFolder('template')
-        # create data folder
-        #self.manage_addFolder('daten')
 
 
-    def getCursor(self):
+    def getCursor(self,autocommit=True):
         """returns fresh DB cursor"""
         conn = getattr(self,"_v_database_connection",None)
         if conn is None:
@@ -82,19 +111,28 @@
                 raise IOError("No database connection! (%s)"%str(e))
         
         cursor = conn.getcursor()
+        if autocommit:
+            # is there a better version to get to the connection?
+            cursor.connection.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
+            
         return cursor
     
-    def executeSQL(self, query, *args):
+    def executeSQL(self, query, args=None, hasResult=True, autocommit=True):
         """execute query with args on database and return all results.
         result format: {"fields":fields, "rows":data}"""
-        cur = self.getCursor()
+        logging.debug("executeSQL query=%s args=%s"%(query,args))
+        cur = self.getCursor(autocommit=autocommit)
         cur.execute(query, args)
         # description of returned fields 
         fields = cur.description
-        # get all data in an array
-        data = cur.fetchall()
-        cur.close()
-        return {"fields":fields, "rows":data}
+        if hasResult:
+            # get all data in an array
+            data = cur.fetchall()
+            cur.close()
+            return {"fields":fields, "rows":data}
+        else:
+            cur.close()
+            return None
 
     
     def publishTraverse(self,request,name):
@@ -103,9 +141,13 @@
         path = request.get('restdb_path', [])
         logging.debug("publishtraverse: name=%s restdb_path=%s"%(name,path))
         
-        if name == "index_html":
+        if name in ("index_html", "PUT"):
             # end of traversal
-            return self.index_html
+            if request.get("method") == "POST" and request.get("action",None) == "PUT":
+                # fake PUT by POST with action=PUT
+                name = "PUT"
+                
+            return getattr(self, name)
             #TODO: should we check more?
         else:
             # traverse
@@ -163,6 +205,42 @@
         # don't know what to do
         return str(REQUEST)
 
+    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
+
+            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":
+                RESPONSE.setHeader("Content-Type", "application/json")
+                json.dump(ret, RESPONSE)
+            elif resultType == "html/json":
+                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 showTable(self,format='XML',schema='public',table=None):
         """returns PageTemplate with tables"""
@@ -178,7 +256,7 @@
     def getTable(self,schema='public',table=None,username='guest'):
         """return table data"""
         logging.debug("gettable")
-        data = self.executeSQL("select * from %s"%table)
+        data = self.executeSQL('select * from "%s"."%s"'%(schema,table))
         return data
 
     def showListOfTables(self,format='XML',schema='public'):
@@ -219,7 +297,147 @@
         # TODO: really look up schemas
         data={'fields': (('schemas',),), 'rows': [('public',),]}
         return data
+    
+    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']
+            
+            else:
+                # name only
+                name = sqlName(f)
+                type = 'text'
+                
+            sqlFields.append({'name':name, 'type':type})
+            
+        # drop table if it exists
+        try:
+            res = self.executeSQL('select * from "%s"."%s" where 1=0'%(schema,table))
+            logging.debug("createemptytable: table %s.%s exists"%(schema,table))
+            self.executeSQL('drop table "%s"."%s"'%(schema,table),hasResult=False)
+        except:
+            pass
+        
+        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)
+        
+        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))
+        tablename = sqlName(table)
+        self.importExcelXML(schema,tablename, data)
+        return {"tablename": tablename}
+        
+    def importExcelXML(self,schema,table,xmldata,fieldNamesOnly=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 identify: (optional) field res. tag which identifies a entry uniquely for updating purposes.
+        @param RESPONSE: (optional)
+        '''
+        from xml.dom.pulldom import parseString,parse
+        
+        namespace = "urn:schemas-microsoft-com:office:spreadsheet"
+        containerTagName = "Table"
+        rowTagName = "Row"
+        colTagName = "Cell"
+        dataTagName = "Data"
+        fieldNames = []
+        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)
+                            fieldNames.append(fn)
+                            
+                        if fieldNamesOnly:
+                            # return just field names
+                            return fieldNames
+                        
+                        # create table
+                        sqlFields = self.createEmptyTable(schema, table, fieldNames)
+                        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
+    
     # Methods for GoogleMaps creation
     def showGoogleMap(self,schema='chgis',table='mpdl',id=[],doc=None):
         logging.debug("showGoogleMap")
@@ -398,7 +616,7 @@
             return "Write successful"
 #        except:
 #            return "Could not write"
-
+         
         
     def manage_editRestDbInterface(self, title=None, connection_id=None,
                      REQUEST=None):