changeset 73:695b6612d4c6

getKmlUrl eingebaut
author fknauft
date Wed, 01 Sep 2010 17:42:13 +0200
parents a67b7c1f7ec5
children 6e19a9af00e6
files RestDbInterface.py
diffstat 1 files changed, 60 insertions(+), 251 deletions(-) [+]
line wrap: on
line diff
--- a/RestDbInterface.py	Wed Aug 18 16:42:07 2010 +0200
+++ b/RestDbInterface.py	Wed Sep 01 17:42:13 2010 +0200
@@ -8,9 +8,6 @@
 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
@@ -23,34 +20,6 @@
 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}/
@@ -85,9 +54,11 @@
         self.connection_id = connection_id
         # create template folder
         self.manage_addFolder('template')
+        # create data folder
+        #self.manage_addFolder('daten')
 
 
-    def getCursor(self,autocommit=True):
+    def getCursor(self):
         """returns fresh DB cursor"""
         conn = getattr(self,"_v_database_connection",None)
         if conn is None:
@@ -111,28 +82,19 @@
                 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=None, hasResult=True, autocommit=True):
+    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(autocommit=autocommit)
+        cur = self.getCursor()
         cur.execute(query, args)
         # description of returned fields 
         fields = cur.description
-        if hasResult:
-            # get all data in an array
-            data = cur.fetchall()
-            cur.close()
-            return {"fields":fields, "rows":data}
-        else:
-            cur.close()
-            return None
+        # get all data in an array
+        data = cur.fetchall()
+        cur.close()
+        return {"fields":fields, "rows":data}
 
     
     def publishTraverse(self,request,name):
@@ -141,13 +103,9 @@
         path = request.get('restdb_path', [])
         logging.debug("publishtraverse: name=%s restdb_path=%s"%(name,path))
         
-        if name in ("index_html", "PUT"):
+        if name == "index_html":
             # end of traversal
-            if request.get("method") == "POST" and request.get("action",None) == "PUT":
-                # fake PUT by POST with action=PUT
-                name = "PUT"
-                
-            return getattr(self, name)
+            return self.index_html
             #TODO: should we check more?
         else:
             # traverse
@@ -182,6 +140,7 @@
         doc = REQUEST.get('doc',None)
        
         logging.debug("index_html path=%s format=%s type=%s"%(path,format,type))
+        RESPONSE.setHeader('Access-Control-Allow-Origin', '*')
 
         if type is not None:
             # non-empty type -- look for template
@@ -199,48 +158,14 @@
             # GIS
             if format=="GIS":
                 return self.showGoogleMap(schema=path[1],table=path[2],id=id,doc=doc)
+            if format=="KML_URL":
+                return self.getKmlUrl(schema=path[1],table=path[2],id=id,doc=doc)
             # table
             return self.showTable(format=format,schema=path[1],table=path[2])
         
         # 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"""
@@ -256,7 +181,7 @@
     def getTable(self,schema='public',table=None,username='guest'):
         """return table data"""
         logging.debug("gettable")
-        data = self.executeSQL('select * from "%s"."%s"'%(schema,table))
+        data = self.executeSQL("select * from %s"%table)
         return data
 
     def showListOfTables(self,format='XML',schema='public'):
@@ -297,154 +222,22 @@
         # 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")
         data = self.getDataForGoogleMap(schema,table,id,doc)
+        kmlFileName=self.getKMLname(data=data,table=table)
+        initializeStringForGoogleMaps="""onload=\"initialize(\'http://chinagis.mpiwg-berlin.mpg.de/chinagis/REST/daten/"""+kmlFileName+"""\')\""""#+str(data)
+        initializeStringForGoogleMaps=initializeStringForGoogleMaps.replace("None","0")
+        googleMap_page=self.htmlHead()+str(self.getGoogleMapString(kml=initializeStringForGoogleMaps))
+        return googleMap_page
+
+    def getKmlUrl(self,schema='chgis',table='mpdl',id=[],doc=None):
+        logging.debug("getKmlUrl")
+        data = self.getDataForGoogleMap(schema,table,id,doc)
         kml=self.getKMLname(data=data,table=table)
-        googleMap_page=self.htmlHead()+str(self.getGoogleMapString(kml=kml))
-        return googleMap_page
+        return """http://chinagis.mpiwg-berlin.mpg.de/chinagis/REST/daten/"""+kml
 
     def getDataForGoogleMap(self,schema='chgis',table='mpdl',id=[],doc=None):
         logging.debug("getDataForGoogleMap")
@@ -529,13 +322,11 @@
         #    kml4Marker=str(kml4Marker).replace('&','$$')
         #    kml4Marker=str(kml4Marker).replace(';','__$$__')
         #    kml4Marker=str(kml4Marker).replace('#','__SHARP__')
-            initializeStringForGoogleMaps="""onload=\"initialize(\'http://chinagis.mpiwg-berlin.mpg.de/chinagis/REST/daten/"""+kmlFileName+"""\')\""""#+str(data)
-            initializeStringForGoogleMaps=initializeStringForGoogleMaps.replace("None","0")
             isLoadReady='false'
             while isLoadReady=='false':
               isLoadReady=self.RESTwrite2File(self.daten,kmlFileName,kml4Marker)
 
-        return initializeStringForGoogleMaps
+        return kmlFileName
 
     def getGoogleMapString(self,kml):
         logging.debug("getGoogleMapString")
@@ -544,18 +335,20 @@
     
     def getPoint4GISid(self,gis_id):
         j=0
-        while (True):
-          j=j+1
-          if (j>100):                                         # FJK: just to prevent endless loops
-            break
-          if (gis_id.isdigit()):                              # FJK: regular exit from while-loop
-            break
-          else:
-           gis_id=gis_id.strip('abcdefghijklmnopqrstuvwxyz_') # FJK: to strip all letters 
-           gis_id=gis_id.strip()                              # FJK: to strip all whitespaces
-        resultpoint = [0,0]
-        results = None
-        try:
+        coords=(0,0)
+        if gis_id != None:
+         while (True):
+           j=j+1
+           if (j>100):                                         # FJK: just to prevent endless loops
+             break
+           if (gis_id.isdigit()):                              # FJK: regular exit from while-loop
+             break
+           else:
+             gis_id=gis_id.strip('abcdefghijklmnopqrstuvwxyz_') # FJK: to strip all letters 
+             gis_id=gis_id.strip()                              # FJK: to strip all whitespaces
+         resultpoint = [0,0]
+         results = None
+         try:
             if int(gis_id)>0:
                 SQL="SELECT x_coord,y_coord FROM chgis.chgis_coords WHERE gis_id LIKE cast("+ str(gis_id) +" as text);"
                 results=self.ZSQLSimpleSearch(SQL)
@@ -567,11 +360,13 @@
                     return resultpoint
             else:
                 coords=self.getCoordsFromREST_gisID(joinid)
-                SQL="INSERT INTO  chgis.chgis_coords (gis_id,x_coord,y_coord) VALUES (" +gis_id+ "," +coords[0][1]+ "," +coords[0][0]+ "); ANALYZE chgis.chgis_coords;"
+                SQL="INSERT INTO chgis.chgis_coords (gis_id,x_coord,y_coord) VALUES (" +gis_id+ "," +coords[0][1]+ "," +coords[0][0]+ "); ANALYZE chgis.chgis_coords;"
                 returnstring=self.ZSQLSimpleSearch(SQL)
                 return coords[0]
-        except:
-            error="gis_id not to interpretable:"+str(gis_id)
+         except:
+            return "gis_id not to interpretable:"+str(gis_id)
+        else:
+            return coords[0]
 
     def getCoordsFromREST_gisID(self,gis_id):
         coordlist=[]
@@ -616,7 +411,7 @@
             return "Write successful"
 #        except:
 #            return "Could not write"
-         
+
         
     def manage_editRestDbInterface(self, title=None, connection_id=None,
                      REQUEST=None):
@@ -646,3 +441,17 @@
         REQUEST.RESPONSE.redirect('manage_main')
 
         
+
+#       constructors = (
+#          REST_test.manage_addRESTclassForm,
+#          REST_test.manage_addRESTclass
+#          )
+        
+
+        context.registerClass(
+        RestDbInterface.RestDbInterface,
+        constructors = (
+          RestDbInterface.manage_addRestDbInterfaceForm,
+          RestDbInterface.manage_addRestDbInterface
+          )
+        )