# HG changeset patch
# User casties
# Date 1281717286 -7200
# Node ID 05933707897fe50228a2f5146d546530ea27c150
# Parent  e2c73c0775339d80775059c2f5823971e1d1e3dc
NEW - # 12: create table and upload data
https://it-dev.mpiwg-berlin.mpg.de/tracs/GIS/ticket/12

diff -r e2c73c077533 -r 05933707897f RestDbInterface.py
--- a/RestDbInterface.py	Thu Aug 12 20:04:57 2010 +0200
+++ b/RestDbInterface.py	Fri Aug 13 18:34:46 2010 +0200
@@ -19,6 +19,18 @@
 from Products.ZSQLMethods.SQL import SQLConnectionIDs
 
 
+def getTextFromNode(nodename):
+    """get the cdata content of a XML node"""
+    if nodename is None:
+        return ""
+    nodelist=nodename.childNodes
+    rc = ""
+    for node in nodelist:
+        if node.nodeType == node.TEXT_NODE:
+           rc = rc + node.data
+    return rc
+
+
 class RestDbInterface(Folder):
     """Object for RESTful database queries
     path schema: /db/{schema}/{table}/
@@ -237,10 +249,168 @@
         # 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"""
+        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 = executeSQL('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)
+        ret = executeSQL(sqlString)
+        
+        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))
+        self.importExcelXML(table, data)
+        
+    def importExcelXML(self,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
+        @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"
+        dataTagName = "Data"
+        fieldNames = []
+        sqlFields = []
+        sqlInsert = None
+        
+        logging.debug("import excel xml")
+        
+        ret=""
+        if isinstance(data, str):
+            logging.debug("importXML reading string data")
+            doc=parseString(data)
+        else:
+            logging.debug("importXML reading file data")
+            doc=parse(data)
+            
+        cnt = 0
+        while True:
+            node=doc.getEvent()
+
+            if node is None:
+                break
+            
+            else:
+                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])
+                    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)
+                        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)
+                        
+                    else:
+                        # following rows are data
+                        dataNodes=node[1].getElementsByTagNameNS(namespace, dataTagName)
+                        data = []
+                        for dataNode in dataNodes:
+                            val = getTextFromNode(dataNodes)
+                            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)
+                    '''
+                      
+        logging.debug("XML import field names: %s"%(fieldNames))
+        return ret
          
         
     def manage_editRestDbInterface(self, title=None, connection_id=None,