Mercurial > hg > ChinaGisRestApi

comparison RestDbGisApi.py @ 61:e81d034b28a5

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
more permission handling and table metadata
author casties
date Tue, 26 Oct 2010 21:23:19 +0200
parents 9fdadb60529f
children 3905385c8854
comparison
equal deleted inserted replaced
60:9fdadb60529f 61:e81d034b28a5
7 from OFS.Folder import Folder 7 from OFS.Folder import Folder
8 from Products.PageTemplates.PageTemplateFile import PageTemplateFile 8 from Products.PageTemplates.PageTemplateFile import PageTemplateFile
9 from Products.ZSQLExtend import ZSQLExtend 9 from Products.ZSQLExtend import ZSQLExtend
10 import logging 10 import logging
11 import re 11 import re
12 import json
13 import time 12 import time
13 import datetime
14 import urllib 14 import urllib
15 15
16 from RestDbInterface import * 16 from RestDbInterface import *
17 17
18 18
44 meta_type="RESTgis" 44 meta_type="RESTgis"
45 45
46 # data templates 46 # data templates
47 GIS_schema_table = PageTemplateFile('zpt/GIS_schema_table', globals()) 47 GIS_schema_table = PageTemplateFile('zpt/GIS_schema_table', globals())
48 KML_schema_table = PageTemplateFile('zpt/KML_schema_table', globals()) 48 KML_schema_table = PageTemplateFile('zpt/KML_schema_table', globals())
49 HTML_schema_usertables = PageTemplateFile('zpt/HTML_schema_usertables', globals())
49 50
50 # and scripts 51 # and scripts
51 def KML_URL_schema_table(self,schema,table): 52 def KML_URL_schema_table(self,schema,table):
52 """KML_URL table function""" 53 """KML_URL table function"""
53 self.REQUEST.RESPONSE.setHeader("Content-Type", "text/plain") 54 self.REQUEST.RESPONSE.setHeader("Content-Type", "text/plain")
54 id = self.REQUEST.get('id',[]) 55 id = self.REQUEST.get('id',[])
55 doc = self.REQUEST.get('doc',None) 56 doc = self.REQUEST.get('doc',None)
56 return self.getLiveKmlUrl(schema=schema,table=table) 57 return self.getLiveKmlUrl(schema=schema,table=table)
57 58
58 59 #
60 # database methods
61 #
59 def getTableOwner(self,schema,table): 62 def getTableOwner(self,schema,table):
60 """returns the owner of the table""" 63 """returns the owner of the table"""
61 # TODO: look up in metadata 64 # what do we do with the schema?
65 sql = 'select table_owner from public.gis_table_meta where table_name = %s'
66 res = self.executeSQL(sql,(table,))
67 if len(res['rows']) > 0:
68 return res['rows'][0][0]
62 return None 69 return None
63 70
64 def isAllowed(self,action,schema,table,user=None,owner=None): 71 def isAllowed(self,action,schema,table,user=None,owner=None):
65 """returns if the requested action on the table is allowed""" 72 """returns if the requested action on the table is allowed"""
66 if user is None: 73 if user is None:
75 return False 82 return False
76 83
77 if action == "update": 84 if action == "update":
78 if owner is None: 85 if owner is None:
79 owner = self.getTableOwner(schema,table) 86 owner = self.getTableOwner(schema,table)
87 logging.debug("isAllowed user=%s owner=%s"%(user,owner))
80 if user is not None and str(user) == str(owner): 88 if user is not None and str(user) == str(owner):
81 # update only your own table 89 # update only your own table
82 return True 90 return True
83 else: 91 else:
84 return False 92 return False
85 93
86 return True 94 return True
87 95
88 def setTableMetaTypes(self,schema,table,fields): 96 def setTableMetaTypes(self,schema,table,fields,user=None):
89 """sets the GIS meta information for table""" 97 """sets the GIS meta information for table"""
90 logging.debug("settablemetatypes schema=%s, table=%s, fields=%s"%(schema,table,fields)) 98 if user is None:
91 gisIdField = None 99 user = self.REQUEST.get('AUTHENTICATED_USER',None)
92 latField = None 100
93 lonField = None 101 logging.debug("settablemetatypes schema=%s, table=%s, fields=%s user=%s"%(schema,table,fields,user))
102
103 today = datetime.date.today().isoformat()
104
105 res = self.executeSQL('select * from public.gis_table_meta where table_name = %s', (table,))
106 if len(res['rows']) > 0:
107 # meta record exists
108 sql = 'update public.gis_table_meta set table_owner=%s, table_modified=%s where table_name=%s'
109 self.executeSQL(sql, (str(user),today,table), hasResult=False)
110 else:
111 # new meta record
112 sql = 'insert into public.gis_table_meta (table_name,table_owner,table_created) values (%s,%s,%s)'
113 self.executeSQL(sql, (table,str(user),today), hasResult=False)
114
115 # update row info
116 sql = 'delete from public.gis_table_meta_rows where table_name=%s'
117 self.executeSQL(sql,(table,),hasResult=False)
118 sql = 'insert into public.gis_table_meta_rows (table_name,field_name,gis_type) values (%s,%s,%s)'
94 for f in fields: 119 for f in fields:
95 t = f['type'] 120 t = f['type']
96 if t == 'gis_id': 121 fn = f['name']
97 gisIdField = f['name'] 122 self.executeSQL(sql, (table,fn,t), hasResult=False)
98 elif t == 'coord_lat':
99 latField = f['name']
100 elif t == 'coord_lon':
101 lonField = f['name']
102
103 res = self.executeSQL("select * from public.metadata where tablename=%s", (table,))
104 if len(res['rows']) > 0:
105 # meta record exists
106 if gisIdField is not None:
107 self.executeSQL('update public.metadata set "attribute with gis_id" = %s where tablename = %s', (gisIdField,table), hasResult=False)
108
109 else:
110 # new meta record
111 if gisIdField is not None:
112 self.executeSQL('insert into public.metadata ("tablename", "attribute with gis_id") values (%s, %s)', (table,gisIdField), hasResult=False)
113
114
115 def showTable(self,resultFormat='XML',schema='public',table=None,REQUEST=None,RESPONSE=None):
116 """returns PageTemplate with tables"""
117 logging.debug("showtable")
118 if REQUEST is None:
119 REQUEST = self.REQUEST
120 123
121 # should be cross-site accessible 124
122 if RESPONSE is None: 125 def getListOfUserTables(self,schema='public',username='guest'):
123 RESPONSE = self.REQUEST.RESPONSE 126 """return list of tables"""
124 RESPONSE.setHeader('Access-Control-Allow-Origin', '*') 127 logging.debug("getlistofusertables")
125 128 # get list of db tables
126 user = self.REQUEST.get('AUTHENTICATED_USER',None) 129 qstr = """SELECT t.table_name FROM information_schema.tables t, public.gis_table_meta m WHERE t.table_type = 'BASE TABLE'
127 logging.debug("user=%s"%user) 130 AND t.table_schema = %s AND t.table_name = m.table_name ORDER BY 1"""
128 131
129 # everything else has its own template 132 data=self.executeSQL(qstr,(schema,))
130 pt = getattr(self.template, '%s_schema_table'%resultFormat, None) 133 return data
131 if pt is None: 134
132 return "ERROR!! template %s_schema_table not found"%resultFormat
133
134 #data = self.getTable(schema,table)
135 # templates have to get their own data
136 return pt(schema=schema,table=table)
137
138 135
139 def createEmptyTable(self,schema,table,fields): 136 def createEmptyTable(self,schema,table,fields):
140 """create a table with the given fields 137 """create a table with the given fields
141 returns list of created fields""" 138 returns list of created fields"""
142 logging.debug("createEmptyTable") 139 sqlFields = RestDbInterface.createEmptyTable(self,schema,table,fields)
143 sqlFields = [] 140 if sqlFields is not None:
144 for f in fields:
145 if isinstance(f,dict):
146 # {name: XX, type: YY}
147 name = sqlName(f['name'])
148 type = f['type']
149 sqltype = gisToSqlTypeMap[type]
150 else:
151 # name only
152 name = sqlName(f)
153 type = 'text'
154 sqltype = 'text'
155
156 sqlFields.append({'name':name, 'type':type, 'sqltype':sqltype})
157
158 if self.isAllowed("create", schema, table):
159 self.executeSQL('drop table if exists "%s"."%s"'%(schema,table),hasResult=False)
160 fieldString = ", ".join(['"%s" %s'%(f['name'],f['sqltype']) for f in sqlFields])
161 sqlString = 'create table "%s"."%s" (%s)'%(schema,table,fieldString)
162 logging.debug("createemptytable: SQL=%s"%sqlString)
163 self.executeSQL(sqlString,hasResult=False)
164 self.setTableMetaTypes(schema,table,sqlFields) 141 self.setTableMetaTypes(schema,table,sqlFields)
165 return sqlFields 142
166 else: 143 return sqlFields
167 logging.warning("create table not allowed!")
168 # throw exception?
169 return None
170 144
171 145
172 def getLiveKmlUrl(self,schema,table,useTimestamp=True,REQUEST=None): 146 def getLiveKmlUrl(self,schema,table,useTimestamp=True,REQUEST=None):
173 if REQUEST is None: 147 if REQUEST is None:
174 REQUEST = self.REQUEST 148 REQUEST = self.REQUEST
214 fieldMap = self.getFieldNameMap(data['fields']) 188 fieldMap = self.getFieldNameMap(data['fields'])
215 189
216 if (gisIdField is None) and (latField is None or lonField is None): 190 if (gisIdField is None) and (latField is None or lonField is None):
217 # no fields given - choose automagically 191 # no fields given - choose automagically
218 # gis id in metadata first 192 # gis id in metadata first
219 SQL='SELECT "attribute with gis_id" FROM public.metadata WHERE tablename = %s' 193 #SQL='SELECT "attribute with gis_id" FROM public.metadata WHERE tablename = %s'
220 res = self.executeSQL(SQL, (table,)) 194 sql = 'SELECT field_name FROM public.gis_table_meta_rows WHERE table_name = %s and gis_type = %s'
195 res = self.executeSQL(sql, (table,'gis_id'))
221 if len(res['rows']) > 0: 196 if len(res['rows']) > 0:
222 gisIdField = res['rows'][0][0] 197 gisIdField = res['rows'][0][0]
223 else: 198 else:
224 logging.warning("no entry in metadata table for table %s"%table) 199 logging.warning("no entry in metadata table for table %s"%table)
225 # try field names 200 # try field names