--- a/RestDbGisApi.py	Mon Oct 25 23:24:19 2010 +0200
+++ b/RestDbGisApi.py	Tue Oct 26 21:23:19 2010 +0200
@@ -9,8 +9,8 @@
 from Products.ZSQLExtend import ZSQLExtend
 import logging
 import re
-import json
 import time
+import datetime
 import urllib
 
 from RestDbInterface import *
@@ -46,6 +46,7 @@
     # data templates
     GIS_schema_table = PageTemplateFile('zpt/GIS_schema_table', globals())
     KML_schema_table = PageTemplateFile('zpt/KML_schema_table', globals())
+    HTML_schema_usertables = PageTemplateFile('zpt/HTML_schema_usertables', globals())
     
     # and scripts
     def KML_URL_schema_table(self,schema,table):
@@ -55,10 +56,16 @@
         doc = self.REQUEST.get('doc',None)
         return self.getLiveKmlUrl(schema=schema,table=table)
 
-
+    #
+    # database methods
+    #
     def getTableOwner(self,schema,table):
         """returns the owner of the table"""
-        # TODO: look up in metadata
+        # what do we do with the schema?
+        sql = 'select table_owner from public.gis_table_meta where table_name = %s'
+        res = self.executeSQL(sql,(table,))
+        if len(res['rows']) > 0:
+            return res['rows'][0][0]
         return None
 
     def isAllowed(self,action,schema,table,user=None,owner=None):
@@ -77,6 +84,7 @@
         if action == "update":
             if owner is None:
                 owner = self.getTableOwner(schema,table)
+            logging.debug("isAllowed user=%s owner=%s"%(user,owner))
             if user is not None and str(user) == str(owner):
                 # update only your own table
                 return True
@@ -85,88 +93,54 @@
         
         return True
 
-    def setTableMetaTypes(self,schema,table,fields):
+    def setTableMetaTypes(self,schema,table,fields,user=None):
         """sets the GIS meta information for table"""
-        logging.debug("settablemetatypes schema=%s, table=%s, fields=%s"%(schema,table,fields))
-        gisIdField = None
-        latField = None
-        lonField = None
-        for f in fields:
-            t = f['type']
-            if t == 'gis_id':
-                gisIdField = f['name']
-            elif t == 'coord_lat':
-                latField = f['name']
-            elif t == 'coord_lon':
-                lonField = f['name']
+        if user is None:
+            user = self.REQUEST.get('AUTHENTICATED_USER',None)
+
+        logging.debug("settablemetatypes schema=%s, table=%s, fields=%s user=%s"%(schema,table,fields,user))
                 
-        res = self.executeSQL("select * from public.metadata where tablename=%s", (table,))
+        today = datetime.date.today().isoformat()
+
+        res = self.executeSQL('select * from public.gis_table_meta where table_name = %s', (table,))
         if len(res['rows']) > 0:
             # meta record exists
-            if gisIdField is not None:
-                self.executeSQL('update public.metadata set "attribute with gis_id" = %s where tablename = %s', (gisIdField,table), hasResult=False)
-                
+            sql = 'update public.gis_table_meta set table_owner=%s, table_modified=%s where table_name=%s'
+            self.executeSQL(sql, (str(user),today,table), hasResult=False)
         else:
             # new meta record
-            if gisIdField is not None:
-                self.executeSQL('insert into public.metadata ("tablename", "attribute with gis_id") values (%s, %s)', (table,gisIdField), hasResult=False)
-    
-        
-    def showTable(self,resultFormat='XML',schema='public',table=None,REQUEST=None,RESPONSE=None):
-        """returns PageTemplate with tables"""
-        logging.debug("showtable")
-        if REQUEST is None:
-            REQUEST = self.REQUEST
+            sql = 'insert into public.gis_table_meta (table_name,table_owner,table_created) values (%s,%s,%s)'
+            self.executeSQL(sql, (table,str(user),today), hasResult=False)
+
+        # update row info
+        sql = 'delete from public.gis_table_meta_rows where table_name=%s'
+        self.executeSQL(sql,(table,),hasResult=False)
+        sql = 'insert into public.gis_table_meta_rows (table_name,field_name,gis_type) values (%s,%s,%s)'
+        for f in fields:
+            t = f['type']
+            fn = f['name']
+            self.executeSQL(sql, (table,fn,t), hasResult=False)
             
-        # should be cross-site accessible 
-        if RESPONSE is None:
-            RESPONSE = self.REQUEST.RESPONSE
-        RESPONSE.setHeader('Access-Control-Allow-Origin', '*')
         
-        user = self.REQUEST.get('AUTHENTICATED_USER',None)
-        logging.debug("user=%s"%user)
-                
-        # everything else has its own template
-        pt = getattr(self.template, '%s_schema_table'%resultFormat, None)
-        if pt is None:
-            return "ERROR!! template %s_schema_table not found"%resultFormat
+    def getListOfUserTables(self,schema='public',username='guest'):
+        """return list of tables"""
+        logging.debug("getlistofusertables")
+        # get list of db tables
+        qstr = """SELECT t.table_name FROM information_schema.tables t, public.gis_table_meta m WHERE t.table_type = 'BASE TABLE' 
+                    AND t.table_schema = %s AND t.table_name = m.table_name ORDER BY 1"""
         
-        #data = self.getTable(schema,table)
-        # templates have to get their own data
-        return pt(schema=schema,table=table)
- 
+        data=self.executeSQL(qstr,(schema,))
+        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']
-                sqltype = gisToSqlTypeMap[type]
-            else:
-                # name only
-                name = sqlName(f)
-                type = 'text'
-                sqltype = 'text'
-                
-            sqlFields.append({'name':name, 'type':type, 'sqltype':sqltype})
-            
-        if self.isAllowed("create", schema, table):
-            self.executeSQL('drop table if exists "%s"."%s"'%(schema,table),hasResult=False)
-            fieldString = ", ".join(['"%s" %s'%(f['name'],f['sqltype']) for f in sqlFields])
-            sqlString = 'create table "%s"."%s" (%s)'%(schema,table,fieldString)
-            logging.debug("createemptytable: SQL=%s"%sqlString)
-            self.executeSQL(sqlString,hasResult=False)
+        sqlFields = RestDbInterface.createEmptyTable(self,schema,table,fields)
+        if sqlFields is not None:
             self.setTableMetaTypes(schema,table,sqlFields)
-            return sqlFields
-        else:
-            logging.warning("create table not allowed!")
-            # throw exception?
-            return None
+
+        return sqlFields
     
     
     def getLiveKmlUrl(self,schema,table,useTimestamp=True,REQUEST=None):
@@ -216,8 +190,9 @@
         if (gisIdField is None) and (latField is None or lonField is None):
             # no fields given - choose automagically
             # gis id in metadata first
-            SQL='SELECT "attribute with gis_id" FROM public.metadata WHERE tablename = %s'
-            res = self.executeSQL(SQL, (table,))
+            #SQL='SELECT "attribute with gis_id" FROM public.metadata WHERE tablename = %s'
+            sql = 'SELECT field_name FROM public.gis_table_meta_rows WHERE table_name = %s and gis_type = %s'
+            res = self.executeSQL(sql, (table,'gis_id'))
             if len(res['rows']) > 0:
                 gisIdField = res['rows'][0][0]
             else:

--- a/RestDbInterface.py	Mon Oct 25 23:24:19 2010 +0200
+++ b/RestDbInterface.py	Tue Oct 26 21:23:19 2010 +0200
@@ -248,17 +248,17 @@
             
         if len(path) == 1:
             # list of schemas
-            return self.showListOfSchemas(resultFormat=resultFormat)
+            return self.showListOfSchemas(format=resultFormat)
         elif len(path) == 2:
             # list of tables
-            return self.showListOfTables(resultFormat=resultFormat,schema=path[1])
+            return self.showListOfTables(format=resultFormat,schema=path[1])
         elif len(path) == 3:
             # table
             if REQUEST.get("method") == "POST" and REQUEST.get("create_table_file",None) is not None:
                 # POST to table to check
-                return self.checkTable(resultFormat=resultFormat,schema=path[1],table=path[2])
+                return self.checkTable(format=resultFormat,schema=path[1],table=path[2])
             # else show table
-            return self.showTable(resultFormat=resultFormat,schema=path[1],table=path[2])
+            return self.showTable(format=resultFormat,schema=path[1],table=path[2])
         
         # don't know what to do
         return str(REQUEST)
@@ -306,7 +306,7 @@
             RESPONSE.setStatus(400)
             return
         
-    def showTable(self,resultFormat='XML',schema='public',table=None,REQUEST=None,RESPONSE=None):
+    def showTable(self,format='XML',schema='public',table=None,REQUEST=None,RESPONSE=None):
         """returns PageTemplate with tables"""
         logging.debug("showtable")
         if REQUEST is None:
@@ -319,9 +319,9 @@
         RESPONSE.setHeader('Access-Control-Allow-Origin', '*')
         
         # everything else has its own template
-        pt = getattr(self.template, '%s_schema_table'%resultFormat, None)
+        pt = getattr(self.template, '%s_schema_table'%format, None)
         if pt is None:
-            return "ERROR!! template %s_schema_table not found"%resultFormat
+            return "ERROR!! template %s_schema_table not found"%format
         
         #data = self.getTable(schema,table)
         return pt(schema=schema,table=table)
@@ -342,7 +342,7 @@
         ret = bool(data['rows'])
         return ret
 
-    def showListOfTables(self,resultFormat='XML',schema='public',REQUEST=None,RESPONSE=None):
+    def showListOfTables(self,format='XML',schema='public',REQUEST=None,RESPONSE=None):
         """returns PageTemplate with list of tables"""
         logging.debug("showlistoftables")
         # should be cross-site accessible 
@@ -350,9 +350,9 @@
             RESPONSE = self.REQUEST.RESPONSE
         RESPONSE.setHeader('Access-Control-Allow-Origin', '*')
 
-        pt = getattr(self.template, '%s_schema'%resultFormat, None)
+        pt = getattr(self.template, '%s_schema'%format, None)
         if pt is None:
-            return "ERROR!! template %s_schema not found"%resultFormat
+            return "ERROR!! template %s_schema not found"%format
         
         #data = self.getListOfTables(schema)
         return pt(schema=schema)
@@ -361,15 +361,17 @@
         """return list of tables"""
         logging.debug("getlistoftables")
         # get list of fields and types of db table
-        qstr="""SELECT c.relname AS tablename FROM pg_catalog.pg_class c
-            LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
-            WHERE c.relkind IN ('r','') AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
-            AND pg_catalog.pg_table_is_visible(c.oid) ORDER BY 1"""
-        #qstr="select attname, format_type(pg_attribute.atttypid, pg_attribute.atttypmod) from pg_attribute, pg_class where attrelid = pg_class.oid and pg_attribute.attnum > 0"
-        data=self.executeSQL(qstr)
+        #qstr="""SELECT c.relname AS tablename FROM pg_catalog.pg_class c
+        #    LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
+        #    WHERE c.relkind IN ('r','') AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
+        #    AND pg_catalog.pg_table_is_visible(c.oid) 
+        #    AND c.relname ORDER BY 1"""
+        qstr = """SELECT table_name FROM information_schema.tables WHERE table_type = 'BASE TABLE' 
+                        AND table_schema = %s ORDER BY 1"""
+        data=self.executeSQL(qstr,(schema,))
         return data
 
-    def showListOfSchemas(self,resultFormat='XML',REQUEST=None,RESPONSE=None):
+    def showListOfSchemas(self,format='XML',REQUEST=None,RESPONSE=None):
         """returns PageTemplate with list of schemas"""
         logging.debug("showlistofschemas")
         # should be cross-site accessible 
@@ -377,9 +379,9 @@
             RESPONSE = self.REQUEST.RESPONSE
         RESPONSE.setHeader('Access-Control-Allow-Origin', '*')
 
-        pt = getattr(self.template, '%s_index'%resultFormat, None)
+        pt = getattr(self.template, '%s_index'%format, None)
         if pt is None:
-            return "ERROR!! template %s_index not found"%resultFormat
+            return "ERROR!! template %s_index not found"%format
         
         #data = self.getListOfSchemas()
         return pt()
@@ -391,7 +393,7 @@
         data={'fields': (('schemas',),), 'rows': [('public',),]}
         return data
     
-    def checkTable(self,resultFormat,schema,table,REQUEST=None,RESPONSE=None):
+    def checkTable(self,format,schema,table,REQUEST=None,RESPONSE=None):
         """check the table.
            returns valid data fields and table name."""
         if REQUEST is None:
@@ -402,18 +404,18 @@
         res = self.checkTableFromXML(schema, table, file)
         logging.debug("checkTable result=%s"%repr(res))
         # return the result as JSON
-        if resultFormat == "JSON":
+        if format == "JSON":
             RESPONSE.setHeader("Content-Type", "application/json")
             json.dump(res, RESPONSE)
             
-        elif resultFormat == "JSONHTML":
+        elif format == "JSONHTML":
             RESPONSE.setHeader("Content-Type", "text/html")
             RESPONSE.write("<html>\n<body>\n<pre>")
             json.dump(res, RESPONSE)
             RESPONSE.write("</pre>\n</body>\n</html>")
             
         else:
-            return "ERROR: invalid resultFormat"
+            return "ERROR: invalid format"
 
     def checkTableFromXML(self,schema,table,data,REQUEST=None,RESPONSE=None):
         """check the table with the given XML data.

--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/zpt/HTML_schema_usertables.zpt	Tue Oct 26 21:23:19 2010 +0200
@@ -0,0 +1,22 @@
+<html tal:define="layout python:request.get('layout','table'); element_id python:request.get('element_id',None);
+  schema python:options.get('schema','public'); data python:here.getListOfUserTables(schema)">
+  <head>
+    <meta http-equiv="content-type" content="text/html;charset=utf-8">
+    <title tal:content="template/title">The title</title>
+  </head>
+  <body>
+    <h2>List of tables for schema <span tal:replace="schema"/></h2>
+    <tal:block tal:condition="python:layout=='select'">
+    <select tal:attributes="id element_id">
+      <option tal:repeat="tbl data/rows" tal:content="python:tbl[0]"/>
+    </select>
+    </tal:block>
+    <tal:block tal:condition="python:layout=='table'">
+    <table tal:attributes="id element_id">
+      <tr tal:repeat="row data/rows">
+        <td><a tal:define="tbl python:row[0]" tal:attributes="href tbl" tal:content="tbl"/></td>
+      </tr>
+    </table>
+    </tal:block>
+  </body>
+</html>
\ No newline at end of file