Mercurial > hg > ZDBInterface

comparison RestDbInterface.py @ 0:09361041be51

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
first checkin
author casties
date Fri, 11 Feb 2011 15:05:23 +0100
parents
children 881fcea6a57d
comparison
equal deleted inserted replaced
-1:000000000000 0:09361041be51
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 from Products.ZSQLExtend import ZSQLExtend
11 import logging
12 import re
13 import json
14 import time
15 import psycopg2
16 # make psycopg use unicode objects
17 import psycopg2.extensions
18 psycopg2.extensions.register_type(psycopg2.extensions.UNICODE)
19 psycopg2.extensions.register_type(psycopg2.extensions.UNICODEARRAY)
20
21 from zope.interface import implements
22 from zope.publisher.interfaces import IPublishTraverse
23 from ZPublisher.BaseRequest import DefaultPublishTraverse
24
25
26 def unicodify(s,alternate='latin-1'):
27 """decode str (utf-8 or latin-1 representation) into unicode object"""
28 if not s:
29 return u""
30 if isinstance(s, str):
31 try:
32 return s.decode('utf-8')
33 except:
34 return s.decode(alternate)
35 else:
36 return s
37
38 def utf8ify(s):
39 """encode unicode object or string into byte string in utf-8 representation.
40 assumes string objects to be utf-8"""
41 if not s:
42 return ""
43 if isinstance(s, str):
44 return s
45 else:
46 return s.encode('utf-8')
47
48 def getTextFromNode(node):
49 """get the cdata content of a XML node"""
50 if node is None:
51 return ""
52
53 if isinstance(node, list):
54 nodelist = node
55 else:
56 nodelist=node.childNodes
57
58 rc = ""
59 for node in nodelist:
60 if node.nodeType == node.TEXT_NODE:
61 rc = rc + node.data
62 return rc
63
64 def sqlName(s,lc=True):
65 """returns restricted ASCII-only version of string"""
66 if s is None:
67 return ""
68
69 # all else -> "_"
70 s = re.sub(r'[^A-Za-z0-9_]','_',s)
71 if lc:
72 return s.lower()
73
74 return s
75
76
77 class RestDbInterface(Folder):
78 """Object for RESTful database queries
79 path schema: /db/{schema}/{table}/
80 omitting table gives a list of schemas
81 omitting table and schema gives a list of schemas
82 """
83 implements(IPublishTraverse)
84
85 meta_type="RESTdb"
86 manage_options=Folder.manage_options+(
87 {'label':'Config','action':'manage_editRestDbInterfaceForm'},
88 )
89
90 # management templates
91 manage_editRestDbInterfaceForm=PageTemplateFile('zpt/editRestDbInterface',globals())
92
93 # data templates
94 XML_index = PageTemplateFile('zpt/XML_index', globals())
95 XML_schema = PageTemplateFile('zpt/XML_schema', globals())
96 XML_schema_table = PageTemplateFile('zpt/XML_schema_table', globals())
97 HTML_index = PageTemplateFile('zpt/HTML_index', globals())
98 HTML_schema = PageTemplateFile('zpt/HTML_schema', globals())
99 HTML_schema_table = PageTemplateFile('zpt/HTML_schema_table', globals())
100 JSONHTML_index = PageTemplateFile('zpt/JSONHTML_index', globals())
101 JSONHTML_schema = PageTemplateFile('zpt/JSONHTML_schema', globals())
102 JSONHTML_schema_table = PageTemplateFile('zpt/JSONHTML_schema_table', globals())
103 # JSON_* templates are scripts
104 def JSON_index(self):
105 """JSON index function"""
106 self.REQUEST.RESPONSE.setHeader("Content-Type", "application/json")
107 json.dump(self.getListOfSchemas(), self.REQUEST.RESPONSE)
108
109 def JSON_schema(self,schema):
110 """JSON index function"""
111 self.REQUEST.RESPONSE.setHeader("Content-Type", "application/json")
112 json.dump(self.getListOfTables(schema), self.REQUEST.RESPONSE)
113
114 def JSON_schema_table(self,schema,table):
115 """JSON index function"""
116 self.REQUEST.RESPONSE.setHeader("Content-Type", "application/json")
117 json.dump(self.getTable(schema, table), self.REQUEST.RESPONSE)
118
119
120 def __init__(self, id, title, connection_id=None):
121 """init"""
122 self.id = id
123 self.title = title
124 # database connection id
125 self.connection_id = connection_id
126 # create template folder
127 self.manage_addFolder('template')
128
129
130 def getRestDbUrl(self):
131 """returns url to the RestDb instance"""
132 return self.absolute_url()
133
134 def getJsonString(self,object):
135 """returns a JSON formatted string from object"""
136 return json.dumps(object)
137
138 def getCursor(self,autocommit=True):
139 """returns fresh DB cursor"""
140 conn = getattr(self,"_v_database_connection",None)
141 if conn is None:
142 # create a new connection object
143 try:
144 if self.connection_id is None:
145 # try to take the first existing ID
146 connids = SQLConnectionIDs(self)
147 if len(connids) > 0:
148 connection_id = connids[0][0]
149 self.connection_id = connection_id
150 logging.debug("connection_id: %s"%repr(connection_id))
151
152 da = getattr(self, self.connection_id)
153 da.connect('')
154 # we copy the DAs database connection
155 conn = da._v_database_connection
156 #conn._register() # register with the Zope transaction system
157 self._v_database_connection = conn
158 except Exception, e:
159 raise IOError("No database connection! (%s)"%str(e))
160
161 cursor = conn.getcursor()
162 if autocommit:
163 # is there a better version to get to the connection?
164 cursor.connection.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
165
166 return cursor
167
168 def getFieldNameMap(self,fields):
169 """returns a dict mapping field names to row indexes"""
170 map = {}
171 i = 0
172 for f in fields:
173 map[f[0]] = i
174 i += 1
175
176 return map
177
178 def executeSQL(self, query, args=None, hasResult=True, autocommit=True):
179 """execute query with args on database and return all results.
180 result format: {"fields":fields, "rows":data}"""
181 logging.debug("executeSQL query=%s args=%s"%(query,args))
182 cur = self.getCursor(autocommit=autocommit)
183 if args is not None:
184 # make sure args is a list
185 if isinstance(args,basestring):
186 args = (args,)
187
188 cur.execute(query, args)
189 # description of returned fields
190 fields = cur.description
191 if hasResult:
192 # get all data in an array
193 data = cur.fetchall()
194 cur.close()
195 #logging.debug("fields: %s"%repr(fields))
196 #logging.debug("rows: %s"%repr(data))
197 return {"fields":fields, "rows":data}
198 else:
199 cur.close()
200 return None
201
202 def isAllowed(self,action,schema,table,user=None):
203 """returns if the requested action on the table is allowed"""
204 if user is None:
205 user = self.REQUEST.get('AUTHENTICATED_USER',None)
206 logging.debug("isAllowed action=%s schema=%s table=%s user=%s"%(action,schema,table,user))
207 # no default policy!
208 return True
209
210
211 def publishTraverse(self,request,name):
212 """change the traversal"""
213 # get stored path
214 path = request.get('restdb_path', [])
215 logging.debug("publishtraverse: name=%s restdb_path=%s"%(name,path))
216
217 if name in ("index_html", "PUT"):
218 # end of traversal
219 if request.get("method") == "POST" and request.get("action",None) == "PUT":
220 # fake PUT by POST with action=PUT
221 name = "PUT"
222
223 return getattr(self, name)
224 #TODO: should we check more?
225 else:
226 # traverse
227 if len(path) == 0:
228 # first segment
229 if name == 'db':
230 # virtual path -- continue traversing
231 path = [name]
232 request['restdb_path'] = path
233 else:
234 # try real path
235 tr = DefaultPublishTraverse(self, request)
236 ob = tr.publishTraverse(request, name)
237 return ob
238 else:
239 path.append(name)
240
241 # continue traversing
242 return self
243
244
245 def index_html(self,REQUEST,RESPONSE):
246 """index method"""
247 # ReST path was stored in request
248 path = REQUEST.get('restdb_path',[])
249
250 # type and format are real parameter
251 resultFormat = REQUEST.get('format','HTML').upper()
252 queryType = REQUEST.get('type',None)
253
254 logging.debug("index_html path=%s resultFormat=%s queryType=%s"%(path,resultFormat,queryType))
255
256 if queryType is not None:
257 # non-empty queryType -- look for template
258 pt = getattr(self.template, "%s_%s"%(resultFormat,queryType), None)
259 if pt is not None:
260 return pt(format=resultFormat,type=queryType,path=path)
261
262 if len(path) == 1:
263 # list of schemas
264 return self.showListOfSchemas(format=resultFormat)
265 elif len(path) == 2:
266 # list of tables
267 return self.showListOfTables(format=resultFormat,schema=path[1])
268 elif len(path) == 3:
269 # table
270 if REQUEST.get("method") == "POST" and REQUEST.get("create_table_file",None) is not None:
271 # POST to table to check
272 return self.checkTable(format=resultFormat,schema=path[1],table=path[2])
273 # else show table
274 return self.showTable(format=resultFormat,schema=path[1],table=path[2])
275
276 # don't know what to do
277 return str(REQUEST)
278
279 def showTable(self,format='XML',schema='public',table=None,REQUEST=None,RESPONSE=None):
280 """returns PageTemplate with tables"""
281 logging.debug("showtable")
282 if REQUEST is None:
283 REQUEST = self.REQUEST
284
285 # should be cross-site accessible
286 if RESPONSE is None:
287 RESPONSE = self.REQUEST.RESPONSE
288
289 RESPONSE.setHeader('Access-Control-Allow-Origin', '*')
290
291 # everything else has its own template
292 pt = getattr(self.template, '%s_schema_table'%format, None)
293 if pt is None:
294 return "ERROR!! template %s_schema_table not found"%format
295
296 #data = self.getTable(schema,table)
297 return pt(schema=schema,table=table)
298
299 def getTable(self,schema='public',table=None,sortBy=1,username='guest'):
300 """return table data"""
301 logging.debug("gettable")
302 if sortBy:
303 data = self.executeSQL('select * from "%s"."%s" order by %s'%(schema,table,sortBy))
304 else:
305 data = self.executeSQL('select * from "%s"."%s"'%(schema,table))
306 return data
307
308 def hasTable(self,schema='public',table=None,username='guest'):
309 """return if table exists"""
310 logging.debug("hastable")
311 data = self.executeSQL('select 1 from information_schema.tables where table_schema=%s and table_name=%s',(schema,table))
312 ret = bool(data['rows'])
313 return ret
314
315 def showListOfTables(self,format='XML',schema='public',REQUEST=None,RESPONSE=None):
316 """returns PageTemplate with list of tables"""
317 logging.debug("showlistoftables")
318 # should be cross-site accessible
319 if RESPONSE is None:
320 RESPONSE = self.REQUEST.RESPONSE
321 RESPONSE.setHeader('Access-Control-Allow-Origin', '*')
322
323 pt = getattr(self.template, '%s_schema'%format, None)
324 if pt is None:
325 return "ERROR!! template %s_schema not found"%format
326
327 #data = self.getListOfTables(schema)
328 return pt(schema=schema)
329
330 def getListOfTables(self,schema='public',username='guest'):
331 """return list of tables"""
332 logging.debug("getlistoftables")
333 # get list of fields and types of db table
334 #qstr="""SELECT c.relname AS tablename FROM pg_catalog.pg_class c
335 # LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
336 # WHERE c.relkind IN ('r','') AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
337 # AND pg_catalog.pg_table_is_visible(c.oid)
338 # AND c.relname ORDER BY 1"""
339 qstr = """SELECT table_name FROM information_schema.tables WHERE table_type = 'BASE TABLE'
340 AND table_schema = %s ORDER BY 1"""
341 data=self.executeSQL(qstr,(schema,))
342 return data
343
344 def showListOfSchemas(self,format='XML',REQUEST=None,RESPONSE=None):
345 """returns PageTemplate with list of schemas"""
346 logging.debug("showlistofschemas")
347 # should be cross-site accessible
348 if RESPONSE is None:
349 RESPONSE = self.REQUEST.RESPONSE
350 RESPONSE.setHeader('Access-Control-Allow-Origin', '*')
351
352 pt = getattr(self.template, '%s_index'%format, None)
353 if pt is None:
354 return "ERROR!! template %s_index not found"%format
355
356 #data = self.getListOfSchemas()
357 return pt()
358
359 def getListOfSchemas(self,username='guest'):
360 """return list of schemas"""
361 logging.debug("getlistofschemas")
362 # TODO: really look up schemas
363 data={'fields': (('schemas',),), 'rows': [('public',),]}
364 return data
365
366 def manage_editRestDbInterface(self, title=None, connection_id=None,
367 REQUEST=None):
368 """Change the object"""
369 if title is not None:
370 self.title = title
371
372 if connection_id is not None:
373 self.connection_id = connection_id
374
375 #checkPermission=getSecurityManager().checkPermission
376 REQUEST.RESPONSE.redirect('manage_main')
377
378
379 manage_addRestDbInterfaceForm=PageTemplateFile('zpt/addRestDbInterface',globals())
380
381 def manage_addRestDbInterface(self, id, title='', label='', description='',
382 createPublic=0,
383 createUserF=0,
384 REQUEST=None):
385 """Add a new object with id *id*."""
386
387 ob=RestDbInterface(str(id),title)
388 self._setObject(id, ob)
389
390 #checkPermission=getSecurityManager().checkPermission
391 REQUEST.RESPONSE.redirect('manage_main')
392
393