Mercurial > hg > ChinaGisRestApi
diff RestDbGisApi.py @ 61:e81d034b28a5
more permission handling and table metadata
author | casties |
---|---|
date | Tue, 26 Oct 2010 21:23:19 +0200 |
parents | 9fdadb60529f |
children | 3905385c8854 |
line wrap: on
line diff
--- 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: