--- a/RestDbInterface.py	Mon Aug 16 12:13:36 2010 +0200
+++ b/RestDbInterface.py	Mon Aug 16 21:24:00 2010 +0200
@@ -9,6 +9,7 @@
 from Products.ZSQLExtend import ZSQLExtend
 import logging
 import re
+import psycopg2
 
 from zope.interface import implements
 from zope.publisher.interfaces import IPublishTraverse
@@ -20,26 +21,29 @@
 from Products.ZSQLMethods.SQL import SQLConnectionIDs
 
 
-def getTextFromNode(nodename):
+def getTextFromNode(node):
     """get the cdata content of a XML node"""
-    if nodename is None:
+    if node is None:
         return ""
-    nodelist=nodename.childNodes
+    
+    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=False):
-    """returns ASCII-only version of string"""
+def sqlName(s,lc=True):
+    """returns restricted ASCII-only version of string"""
     if s is None:
         return ""
     
-    # blank -> "-"
-    s = re.sub(r' ','-',s)
     # all else -> "_"
-    s = re.sub(r'[^A-Za-z0-9_-]','_',s)
+    s = re.sub(r'[^A-Za-z0-9_]','_',s)
     if lc:
         return s.lower()
     
@@ -105,11 +109,13 @@
                 raise IOError("No database connection! (%s)"%str(e))
         
         cursor = conn.getcursor()
+        cursor.connection.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
         return cursor
     
     def executeSQL(self, query, *args):
         """execute query with args on database and return all results.
         result format: {"fields":fields, "rows":data}"""
+        logging.debug("executeSQL query=%s args=%s"%(query,args))
         cur = self.getCursor()
         cur.execute(query, args)
         # description of returned fields 
@@ -119,6 +125,15 @@
         cur.close()
         return {"fields":fields, "rows":data}
 
+    def executeOnlySQL(self, query, *args):
+        """execute query with args on database that has no results.
+        result format: {"fields":fields, "rows":data}"""
+        logging.debug("executeOnlySQL query=%s args=%s"%(query,args))
+        cur = self.getCursor()
+        cur.execute(query, args)
+        cur.close()
+        return None
+
     
     def publishTraverse(self,request,name):
         """change the traversal"""
@@ -222,7 +237,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'):
@@ -284,15 +299,16 @@
             
         # drop table if it exists
         try:
-            res = executeSQL('drop table "%s.%s"'%(schema,table))
-            logging.debug("createemptytable: dropped table %s.%s"%(schema,table))
+            res = self.executeSQL('select * from "%s"."%s" where 1=0'%(schema,table))
+            logging.debug("createemptytable: table %s.%s exists"%(schema,table))
+            self.executeOnlySQL('drop table "%s"."%s"'%(schema,table))
         except:
             pass
         
         fieldString = ", ".join(['"%s" %s'%(f['name'],f['type']) for f in sqlFields])
-        sqlString = 'create table "%s.%s" (%s)'%(schema,table,fieldString)
-        loging.debug("createemptytable: SQL=%s"%sqlString)
-        ret = self.executeSQL(sqlString)
+        sqlString = 'create table "%s"."%s" (%s)'%(schema,table,fieldString)
+        logging.debug("createemptytable: SQL=%s"%sqlString)
+        ret = self.executeOnlySQL(sqlString)
         
         return sqlFields
         
@@ -303,7 +319,7 @@
         logging.debug("createTableFromXML schema=%s table=%s data=%s"%(schema,table,data))
         self.importExcelXML(schema,table, data)
         
-    def importExcelXML(self,schema,table,data,fieldNamesOnly=False):
+    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
@@ -317,20 +333,22 @@
         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(data, str):
+        if isinstance(xmldata, str):
             logging.debug("importXML reading string data")
-            doc=parseString(data)
+            doc=parseString(xmldata)
         else:
             logging.debug("importXML reading file data")
-            doc=parse(data)
+            doc=parse(xmldata)
             
         cnt = 0
         while True:
@@ -340,17 +358,13 @@
                 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 == containerTagName.lower(): # make everything case insensitive
-                    # start of container -- do we need this?
-                    cnt = 0
-                    continue
-                
+                                
                 if tagName == rowTagName.lower():
                     # start of row
                     doc.expandNode(node[1])
@@ -368,67 +382,42 @@
                         
                         # 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)
+                        sqlInsert = 'insert into "%s"."%s" (%s) values (%s)'%(schema,table,fieldString,valString)
                         logging.debug("importexcelsql: sqlInsert=%s"%sqlInsert)
                         
                     else:
                         # following rows are data
-                        dataNodes=node[1].getElementsByTagNameNS(namespace, dataTagName)
+                        colNodes=node[1].getElementsByTagNameNS(namespace, colTagName)
                         data = []
-                        for dataNode in dataNodes:
-                            val = getTextFromNode(dataNodes)
+                        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)
                             
-                        self.executeSQL(sqlInsert, data)
-                        
-
-                    continue
-                    
-                    '''dataSet={}
-                    for col in node[1].childNodes:
-                        if col.nodeType is col.ELEMENT_NODE: 
-                            data=col.nodeName
-                            dataSet[data]=getTextFromNode(col)
-
-                    update=False
-                    
-                    if identify:
-
-                        field=dataSet[identify]
-
-                        searchStr="""select %s from %s where %s = '%s'"""%(identify,table,identify,field)
-                        logger("import xml",logging.INFO,searchStr)
-                        search=self.ZSQLSimpleSearch(searchStr)
-                        if search:
-                            update=True
-                    
-                    if update:
-                        tmp=[]
-                        for fieldName in dataSet.keys():
-                            tmp.append("""%s = %s"""%(fieldName,self.ZSQLQuote(dataSet[fieldName])))
-                        setStr=",".join(tmp)
-
-                        field=dataSet[identify]
-                  
-                        queryStr="""UPDATE %s SET %s WHERE %s = '%s' """%(table,setStr,identify,field)
-                        logger("update xml",logging.INFO,queryStr)
-                        self.ZSQLSimpleSearch(queryStr)
-                        ret+="ud: %s \n"%field
-
-                    else:
-                        fields=",".join(dataSet.keys())
-                        values=",".join([""" %s """%self.ZSQLQuote(dataSet[x]) for x in dataSet.keys()])
-                  
-                        
-                        queryStr="""INSERT INTO %s  (%s) VALUES (%s)"""%(table,fields,values)
-                        self.ZSQLSimpleSearch(queryStr)
-                        logger("update xml",logging.INFO,queryStr)
-                    '''
+                        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.executeOnlySQL(sqlInsert, *data)
                       
-        logging.debug("XML import field names: %s"%(fieldNames))
-        return ret
+        return cnt
          
         
     def manage_editRestDbInterface(self, title=None, connection_id=None,