# HG changeset patch
# User casties
# Date 1281953616 -7200
# Node ID 5e3edf980813533e352b9448fc3d2eda77d36058
# Parent  05933707897fe50228a2f5146d546530ea27c150
more work on xml import

diff -r 05933707897f -r 5e3edf980813 RestDbInterface.py
--- a/RestDbInterface.py	Fri Aug 13 18:34:46 2010 +0200
+++ b/RestDbInterface.py	Mon Aug 16 12:13:36 2010 +0200
@@ -8,6 +8,7 @@
 from Products.PageTemplates.PageTemplateFile import PageTemplateFile
 from Products.ZSQLExtend import ZSQLExtend
 import logging
+import re
 
 from zope.interface import implements
 from zope.publisher.interfaces import IPublishTraverse
@@ -30,6 +31,19 @@
            rc = rc + node.data
     return rc
 
+def sqlName(s,lc=False):
+    """returns 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)
+    if lc:
+        return s.lower()
+    
+    return s
 
 class RestDbInterface(Folder):
     """Object for RESTful database queries
@@ -253,6 +267,7 @@
     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):
@@ -270,12 +285,14 @@
         # drop table if it exists
         try:
             res = executeSQL('drop table "%s.%s"'%(schema,table))
+            logging.debug("createemptytable: dropped table %s.%s"%(schema,table))
         except:
             pass
         
-        fieldString = ", ".join(["%s %s"%(f['name'],f['type']) for f in sqlFields])
+        fieldString = ", ".join(['"%s" %s'%(f['name'],f['type']) for f in sqlFields])
         sqlString = 'create table "%s.%s" (%s)'%(schema,table,fieldString)
-        ret = executeSQL(sqlString)
+        loging.debug("createemptytable: SQL=%s"%sqlString)
+        ret = self.executeSQL(sqlString)
         
         return sqlFields
         
@@ -284,9 +301,9 @@
     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))
-        self.importExcelXML(table, data)
+        self.importExcelXML(schema,table, data)
         
-    def importExcelXML(self,table,data,fieldNamesOnly=False):
+    def importExcelXML(self,schema,table,data,fieldNamesOnly=False):
         '''
         Import XML file in Excel format into the table
         @param table: name of the table the xml shall be imported into
@@ -351,9 +368,10 @@
                         
                         # create table
                         sqlFields = self.createEmptyTable(schema, table, fieldNames)
-                        fieldString = ", ".join(["%s"%f['name'] for f in 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