|
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
|
|
3
|
30 meta_type="RESTdb2"
|
|
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:
|
|
|
184 data = self.executeSQL('select * from "%s"."%s" order by %s'%(schema,table,sortBy))
|
|
|
185 else:
|
|
|
186 data = self.executeSQL('select * from "%s"."%s"'%(schema,table))
|
|
|
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
|
