Mercurial > hg > ZDBInterface
annotate RestDbInterface.py @ 18:60fea3a6c695
better fix for psycopg 2.4. better quoting of sql arguments.
| author | casties |
|---|---|
| date | Thu, 23 Feb 2012 21:17:14 +0100 |
| parents | 48ed91b29784 |
| children |
| rev | line source |
|---|---|
| 0 | 1 ''' |
| 2 Created on 19.5.2010 | |
| 3 | |
| 4 @author: casties | |
| 5 ''' | |
| 6 | |
| 7 from OFS.Folder import Folder | |
| 8 from Products.PageTemplates.PageTemplateFile import PageTemplateFile | |
| 9 from AccessControl import getSecurityManager, Unauthorized | |
| 10 import logging | |
| 11 import re | |
| 12 import json | |
| 13 import time | |
| 14 | |
| 15 from zope.interface import implements | |
| 16 from zope.publisher.interfaces import IPublishTraverse | |
| 17 from ZPublisher.BaseRequest import DefaultPublishTraverse | |
| 18 | |
| 3 | 19 from DBInterface import * |
| 0 | 20 |
| 21 | |
| 3 | 22 class RestDbInterface(DBInterface, Folder): |
| 0 | 23 """Object for RESTful database queries |
| 24 path schema: /db/{schema}/{table}/ | |
| 25 omitting table gives a list of schemas | |
| 26 omitting table and schema gives a list of schemas | |
| 27 """ | |
| 28 implements(IPublishTraverse) | |
| 29 | |
|
17
48ed91b29784
added stuff from RestDbGisApi to WritableRestDbInterface.
casties
parents:
3
diff
changeset
|
30 meta_type="RESTdb" |
| 0 | 31 manage_options=Folder.manage_options+( |
| 32 {'label':'Config','action':'manage_editRestDbInterfaceForm'}, | |
| 33 ) | |
| 34 | |
| 35 # management templates | |
| 36 manage_editRestDbInterfaceForm=PageTemplateFile('zpt/editRestDbInterface',globals()) | |
| 37 | |
| 38 # data templates | |
| 39 XML_index = PageTemplateFile('zpt/XML_index', globals()) | |
| 40 XML_schema = PageTemplateFile('zpt/XML_schema', globals()) | |
| 41 XML_schema_table = PageTemplateFile('zpt/XML_schema_table', globals()) | |
| 42 HTML_index = PageTemplateFile('zpt/HTML_index', globals()) | |
| 43 HTML_schema = PageTemplateFile('zpt/HTML_schema', globals()) | |
| 44 HTML_schema_table = PageTemplateFile('zpt/HTML_schema_table', globals()) | |
| 45 JSONHTML_index = PageTemplateFile('zpt/JSONHTML_index', globals()) | |
| 46 JSONHTML_schema = PageTemplateFile('zpt/JSONHTML_schema', globals()) | |
| 47 JSONHTML_schema_table = PageTemplateFile('zpt/JSONHTML_schema_table', globals()) | |
| 48 # JSON_* templates are scripts | |
| 49 def JSON_index(self): | |
| 50 """JSON index function""" | |
| 51 self.REQUEST.RESPONSE.setHeader("Content-Type", "application/json") | |
| 52 json.dump(self.getListOfSchemas(), self.REQUEST.RESPONSE) | |
| 53 | |
| 54 def JSON_schema(self,schema): | |
| 55 """JSON index function""" | |
| 56 self.REQUEST.RESPONSE.setHeader("Content-Type", "application/json") | |
| 57 json.dump(self.getListOfTables(schema), self.REQUEST.RESPONSE) | |
| 58 | |
| 59 def JSON_schema_table(self,schema,table): | |
| 60 """JSON index function""" | |
| 61 self.REQUEST.RESPONSE.setHeader("Content-Type", "application/json") | |
| 62 json.dump(self.getTable(schema, table), self.REQUEST.RESPONSE) | |
| 63 | |
| 64 | |
| 65 def __init__(self, id, title, connection_id=None): | |
| 66 """init""" | |
| 67 self.id = id | |
| 68 self.title = title | |
| 69 # database connection id | |
| 70 self.connection_id = connection_id | |
| 71 # create template folder | |
| 72 self.manage_addFolder('template') | |
| 73 | |
| 74 | |
| 75 def getRestDbUrl(self): | |
| 76 """returns url to the RestDb instance""" | |
| 77 return self.absolute_url() | |
| 78 | |
| 79 def getJsonString(self,object): | |
| 80 """returns a JSON formatted string from object""" | |
| 81 return json.dumps(object) | |
| 82 | |
| 83 | |
| 84 def isAllowed(self,action,schema,table,user=None): | |
| 85 """returns if the requested action on the table is allowed""" | |
| 86 if user is None: | |
| 87 user = self.REQUEST.get('AUTHENTICATED_USER',None) | |
| 88 logging.debug("isAllowed action=%s schema=%s table=%s user=%s"%(action,schema,table,user)) | |
| 89 # no default policy! | |
| 90 return True | |
| 91 | |
| 92 | |
| 93 def publishTraverse(self,request,name): | |
| 94 """change the traversal""" | |
| 95 # get stored path | |
| 96 path = request.get('restdb_path', []) | |
| 97 logging.debug("publishtraverse: name=%s restdb_path=%s"%(name,path)) | |
| 98 | |
| 99 if name in ("index_html", "PUT"): | |
| 100 # end of traversal | |
| 101 if request.get("method") == "POST" and request.get("action",None) == "PUT": | |
| 102 # fake PUT by POST with action=PUT | |
| 103 name = "PUT" | |
| 104 | |
| 105 return getattr(self, name) | |
| 106 #TODO: should we check more? | |
| 107 else: | |
| 108 # traverse | |
| 109 if len(path) == 0: | |
| 110 # first segment | |
| 111 if name == 'db': | |
| 112 # virtual path -- continue traversing | |
| 113 path = [name] | |
| 114 request['restdb_path'] = path | |
| 115 else: | |
| 116 # try real path | |
| 117 tr = DefaultPublishTraverse(self, request) | |
| 118 ob = tr.publishTraverse(request, name) | |
| 119 return ob | |
| 120 else: | |
| 121 path.append(name) | |
| 122 | |
| 123 # continue traversing | |
| 124 return self | |
| 125 | |
| 126 def index_html(self,REQUEST,RESPONSE): | |
| 127 """index method""" | |
| 128 # ReST path was stored in request | |
| 129 path = REQUEST.get('restdb_path',[]) | |
| 130 | |
| 131 # type and format are real parameter | |
| 132 resultFormat = REQUEST.get('format','HTML').upper() | |
| 133 queryType = REQUEST.get('type',None) | |
| 134 | |
| 135 logging.debug("index_html path=%s resultFormat=%s queryType=%s"%(path,resultFormat,queryType)) | |
| 136 | |
| 137 if queryType is not None: | |
| 138 # non-empty queryType -- look for template | |
| 139 pt = getattr(self.template, "%s_%s"%(resultFormat,queryType), None) | |
| 140 if pt is not None: | |
| 141 return pt(format=resultFormat,type=queryType,path=path) | |
| 142 | |
| 143 if len(path) == 1: | |
| 144 # list of schemas | |
| 145 return self.showListOfSchemas(format=resultFormat) | |
| 146 elif len(path) == 2: | |
| 147 # list of tables | |
| 148 return self.showListOfTables(format=resultFormat,schema=path[1]) | |
| 149 elif len(path) == 3: | |
| 150 # table | |
| 151 if REQUEST.get("method") == "POST" and REQUEST.get("create_table_file",None) is not None: | |
| 152 # POST to table to check | |
| 153 return self.checkTable(format=resultFormat,schema=path[1],table=path[2]) | |
| 154 # else show table | |
| 155 return self.showTable(format=resultFormat,schema=path[1],table=path[2]) | |
| 156 | |
| 157 # don't know what to do | |
| 158 return str(REQUEST) | |
| 159 | |
| 160 def showTable(self,format='XML',schema='public',table=None,REQUEST=None,RESPONSE=None): | |
| 161 """returns PageTemplate with tables""" | |
| 162 logging.debug("showtable") | |
| 163 if REQUEST is None: | |
| 164 REQUEST = self.REQUEST | |
| 165 | |
| 166 # should be cross-site accessible | |
| 167 if RESPONSE is None: | |
| 168 RESPONSE = self.REQUEST.RESPONSE | |
| 169 | |
| 170 RESPONSE.setHeader('Access-Control-Allow-Origin', '*') | |
| 171 | |
| 172 # everything else has its own template | |
| 173 pt = getattr(self.template, '%s_schema_table'%format, None) | |
| 174 if pt is None: | |
| 175 return "ERROR!! template %s_schema_table not found"%format | |
| 176 | |
| 177 #data = self.getTable(schema,table) | |
| 178 return pt(schema=schema,table=table) | |
| 179 | |
| 180 def getTable(self,schema='public',table=None,sortBy=1,username='guest'): | |
| 181 """return table data""" | |
| 182 logging.debug("gettable") | |
| 183 if sortBy: | |
|
18
60fea3a6c695
better fix for psycopg 2.4. better quoting of sql arguments.
casties
parents:
17
diff
changeset
|
184 data = self.executeSQL('select * from "%s"."%s" order by %%s'%(sqlName(schema),sqlName(table)),(sortBy,)) |
| 0 | 185 else: |
|
18
60fea3a6c695
better fix for psycopg 2.4. better quoting of sql arguments.
casties
parents:
17
diff
changeset
|
186 data = self.executeSQL('select * from "%s"."%s"'%(sqlName(schema),sqlName(table))) |
| 0 | 187 return data |
| 188 | |
| 189 def hasTable(self,schema='public',table=None,username='guest'): | |
| 190 """return if table exists""" | |
| 191 logging.debug("hastable") | |
| 192 data = self.executeSQL('select 1 from information_schema.tables where table_schema=%s and table_name=%s',(schema,table)) | |
| 193 ret = bool(data['rows']) | |
| 194 return ret | |
| 195 | |
| 196 def showListOfTables(self,format='XML',schema='public',REQUEST=None,RESPONSE=None): | |
| 197 """returns PageTemplate with list of tables""" | |
| 198 logging.debug("showlistoftables") | |
| 199 # should be cross-site accessible | |
| 200 if RESPONSE is None: | |
| 201 RESPONSE = self.REQUEST.RESPONSE | |
| 202 RESPONSE.setHeader('Access-Control-Allow-Origin', '*') | |
| 203 | |
| 204 pt = getattr(self.template, '%s_schema'%format, None) | |
| 205 if pt is None: | |
| 206 return "ERROR!! template %s_schema not found"%format | |
| 207 | |
| 208 #data = self.getListOfTables(schema) | |
| 209 return pt(schema=schema) | |
| 210 | |
| 211 def getListOfTables(self,schema='public',username='guest'): | |
| 212 """return list of tables""" | |
| 213 logging.debug("getlistoftables") | |
| 214 # get list of fields and types of db table | |
| 215 #qstr="""SELECT c.relname AS tablename FROM pg_catalog.pg_class c | |
| 216 # LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace | |
| 217 # WHERE c.relkind IN ('r','') AND n.nspname NOT IN ('pg_catalog', 'pg_toast') | |
| 218 # AND pg_catalog.pg_table_is_visible(c.oid) | |
| 219 # AND c.relname ORDER BY 1""" | |
| 220 qstr = """SELECT table_name FROM information_schema.tables WHERE table_type = 'BASE TABLE' | |
| 221 AND table_schema = %s ORDER BY 1""" | |
| 222 data=self.executeSQL(qstr,(schema,)) | |
| 223 return data | |
| 224 | |
| 225 def showListOfSchemas(self,format='XML',REQUEST=None,RESPONSE=None): | |
| 226 """returns PageTemplate with list of schemas""" | |
| 227 logging.debug("showlistofschemas") | |
| 228 # should be cross-site accessible | |
| 229 if RESPONSE is None: | |
| 230 RESPONSE = self.REQUEST.RESPONSE | |
| 231 RESPONSE.setHeader('Access-Control-Allow-Origin', '*') | |
| 232 | |
| 233 pt = getattr(self.template, '%s_index'%format, None) | |
| 234 if pt is None: | |
| 235 return "ERROR!! template %s_index not found"%format | |
| 236 | |
| 237 #data = self.getListOfSchemas() | |
| 238 return pt() | |
| 239 | |
| 240 def getListOfSchemas(self,username='guest'): | |
| 241 """return list of schemas""" | |
| 242 logging.debug("getlistofschemas") | |
| 243 # TODO: really look up schemas | |
| 244 data={'fields': (('schemas',),), 'rows': [('public',),]} | |
| 245 return data | |
| 246 | |
| 247 def manage_editRestDbInterface(self, title=None, connection_id=None, | |
| 248 REQUEST=None): | |
| 249 """Change the object""" | |
| 250 if title is not None: | |
| 251 self.title = title | |
| 252 | |
| 253 if connection_id is not None: | |
| 254 self.connection_id = connection_id | |
| 255 | |
| 256 #checkPermission=getSecurityManager().checkPermission | |
| 257 REQUEST.RESPONSE.redirect('manage_main') | |
| 258 | |
| 259 | |
| 260 manage_addRestDbInterfaceForm=PageTemplateFile('zpt/addRestDbInterface',globals()) | |
| 261 | |
| 262 def manage_addRestDbInterface(self, id, title='', label='', description='', | |
| 263 createPublic=0, | |
| 264 createUserF=0, | |
| 265 REQUEST=None): | |
| 266 """Add a new object with id *id*.""" | |
| 267 | |
| 268 ob=RestDbInterface(str(id),title) | |
| 269 self._setObject(id, ob) | |
| 270 | |
| 271 #checkPermission=getSecurityManager().checkPermission | |
| 272 REQUEST.RESPONSE.redirect('manage_main') | |
| 273 | |
| 274 |