|
42
|
1 '''
|
|
|
2 Created on 2.9.2010
|
|
|
3
|
|
|
4 @author: casties
|
|
|
5 '''
|
|
|
6
|
|
|
7 from OFS.Folder import Folder
|
|
|
8 from Products.PageTemplates.PageTemplateFile import PageTemplateFile
|
|
|
9 import logging
|
|
|
10 import re
|
|
|
11 import psycopg2
|
|
|
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 import Shared.DC.ZRDB.DA
|
|
|
19 from Products.ZSQLMethods.SQL import SQLConnectionIDs
|
|
|
20
|
|
43
|
21 from RestDbInterface import *
|
|
42
|
22
|
|
|
23
|
|
|
24 class RestDbJsonStore(RestDbInterface):
|
|
46
|
25 """Object for RESTful access to JSON objects
|
|
|
26 path schema: /db/{schema}/{table}/{tag}/{type}/{item}
|
|
42
|
27 """
|
|
|
28 implements(IPublishTraverse)
|
|
|
29
|
|
46
|
30 meta_type="RESTjson"
|
|
42
|
31 manage_options=Folder.manage_options+(
|
|
|
32 {'label':'Config','action':'manage_editRestDbJsonStoreForm'},
|
|
|
33 )
|
|
|
34
|
|
|
35 # management templates
|
|
|
36 manage_editRestDbJsonStoreForm=PageTemplateFile('zpt/editRestDbJsonStore',globals())
|
|
|
37
|
|
|
38 # JSON_* templates are scripts
|
|
|
39 def JSON_index(self,data):
|
|
|
40 """JSON index function"""
|
|
|
41 self.REQUEST.RESPONSE.setHeader("Content-Type", "application/json")
|
|
|
42 json.dump(data, self.REQUEST.RESPONSE)
|
|
|
43
|
|
|
44
|
|
|
45 def __init__(self, id, title, connection_id=None):
|
|
|
46 """init"""
|
|
|
47 self.id = id
|
|
|
48 self.title = title
|
|
|
49 # database connection id
|
|
|
50 self.connection_id = connection_id
|
|
|
51
|
|
|
52 def getJsonString(self,object):
|
|
|
53 """returns a JSON formatted string from object"""
|
|
|
54 return json.dumps(object)
|
|
|
55
|
|
|
56
|
|
|
57 def publishTraverse(self,request,name):
|
|
|
58 """change the traversal"""
|
|
|
59 # get stored path
|
|
|
60 path = request.get('restdb_path', [])
|
|
|
61 logging.debug("publishtraverse: name=%s restdb_path=%s"%(name,path))
|
|
|
62
|
|
|
63 if name in ("index_html", "PUT"):
|
|
|
64 # end of traversal
|
|
|
65 if request.get("method") == "POST" and request.get("action",None) == "PUT":
|
|
|
66 # fake PUT by POST with action=PUT
|
|
|
67 name = "PUT"
|
|
|
68
|
|
|
69 return getattr(self, name)
|
|
|
70 #TODO: should we check more?
|
|
|
71 else:
|
|
|
72 # traverse
|
|
|
73 if len(path) == 0:
|
|
|
74 # first segment
|
|
|
75 if name == 'db':
|
|
|
76 # virtual path -- continue traversing
|
|
|
77 path = [name]
|
|
|
78 request['restdb_path'] = path
|
|
|
79 else:
|
|
|
80 # try real path
|
|
|
81 tr = DefaultPublishTraverse(self, request)
|
|
|
82 ob = tr.publishTraverse(request, name)
|
|
|
83 return ob
|
|
|
84 else:
|
|
|
85 path.append(name)
|
|
|
86
|
|
|
87 # continue traversing
|
|
|
88 return self
|
|
|
89
|
|
|
90 def index_html(self,REQUEST,RESPONSE):
|
|
|
91 """index method"""
|
|
|
92 # ReST path was stored in request
|
|
|
93 path = REQUEST.get('restdb_path',[])
|
|
|
94
|
|
|
95 # type and format are real parameter
|
|
|
96 resultFormat = REQUEST.get('format','HTML').upper()
|
|
|
97 queryType = REQUEST.get('type',None)
|
|
|
98
|
|
|
99 logging.debug("index_html path=%s resultFormat=%s queryType=%s"%(path,resultFormat,queryType))
|
|
|
100
|
|
|
101 if queryType is not None:
|
|
|
102 # non-empty queryType -- look for template
|
|
|
103 pt = getattr(self.template, "%s_%s"%(resultFormat,queryType), None)
|
|
|
104 if pt is not None:
|
|
|
105 return pt(format=resultFormat,type=queryType,path=path)
|
|
|
106
|
|
46
|
107 if len(path) == 3:
|
|
|
108 # list of tags
|
|
|
109 return self.showListOfTags(resultFormat=resultFormat,schema=path[1],table=path[2])
|
|
|
110 elif len(path) == 4:
|
|
49
|
111 # list of types
|
|
|
112 return self.showListOfTypes(resultFormat=resultFormat,schema=path[1],table=path[2],tag=path[3])
|
|
|
113 elif len(path) == 5:
|
|
|
114 # list of types
|
|
|
115 return self.showListOfItems(resultFormat=resultFormat,schema=path[1],table=path[2],tag=path[3],type=path[4])
|
|
|
116 elif len(path) == 6:
|
|
|
117 # show item
|
|
|
118 return self.showItem(resultFormat=resultFormat,schema=path[1],table=path[2],tag=path[3],type=path[4],item=path[5])
|
|
42
|
119
|
|
|
120 # don't know what to do
|
|
|
121 return str(REQUEST)
|
|
|
122
|
|
|
123 def PUT(self, REQUEST, RESPONSE):
|
|
|
124 """
|
|
|
125 Implement WebDAV/HTTP PUT/FTP put method for this object.
|
|
|
126 """
|
|
49
|
127 path = REQUEST.get('restdb_path',[])
|
|
|
128 logging.debug("RestDbInterface PUT (path=%s)"%repr(path))
|
|
52
|
129 logging.debug("PUT request=%s"%REQUEST)
|
|
42
|
130 #logging.debug("req=%s"%REQUEST)
|
|
|
131 #self.dav__init(REQUEST, RESPONSE)
|
|
|
132 #self.dav__simpleifhandler(REQUEST, RESPONSE)
|
|
|
133 # ReST path was stored in request
|
|
52
|
134 if len(path) == 6:
|
|
42
|
135 schema = path[1]
|
|
52
|
136 table = path[2]
|
|
|
137 tag = path[3]
|
|
|
138 type = path[4]
|
|
|
139 item = path[5]
|
|
|
140 # maybe the data was form-encoded
|
|
|
141 value = REQUEST.get('json_string', None)
|
|
|
142 if value is None:
|
|
|
143 # then maybe in the body (the hard way...)
|
|
|
144 REQUEST.stdin.seek(0)
|
|
|
145 value = REQUEST.stdin.read()
|
|
42
|
146
|
|
52
|
147 logging.debug("put with schema=%s table=%s tag=%s type=%s item=%s value=%s"%(schema,table,tag,type,item,value))
|
|
|
148 res = self.storeItem(schema,table,tag,type,item,value)
|
|
42
|
149
|
|
|
150 else:
|
|
|
151 # 400 Bad Request
|
|
|
152 RESPONSE.setStatus(400)
|
|
|
153 return
|
|
50
|
154
|
|
49
|
155 def showListOfTags(self,resultFormat,schema,table):
|
|
52
|
156 """shows the list of existing tags"""
|
|
50
|
157 tags = self.getListOfTags(schema, table)
|
|
|
158 if resultFormat == 'JSON':
|
|
|
159 self.REQUEST.RESPONSE.setHeader("Content-Type", "application/json")
|
|
|
160 json.dump(tags, self.REQUEST.RESPONSE)
|
|
|
161 else:
|
|
|
162 json.dump(tags, self.REQUEST.RESPONSE)
|
|
|
163
|
|
|
164 def getListOfTags(self,schema,table):
|
|
|
165 """returns the list of existing tags"""
|
|
|
166 logging.debug("getlistoftags schema=%s table=%s"%(schema,table))
|
|
|
167 sql = 'select distinct json_tag from "%s"."%s"'%(schema,table)
|
|
49
|
168 res = self.executeSQL(sql)
|
|
|
169 if len(res['rows']) > 0:
|
|
51
|
170 tags = [r[0] for r in res['rows']]
|
|
|
171 return tags
|
|
|
172
|
|
|
173 return []
|
|
|
174
|
|
|
175 def showListOfTypes(self,resultFormat,schema,table,tag):
|
|
52
|
176 """shows the list of existing types"""
|
|
51
|
177 types = self.getListOfTypes(schema, table,tag)
|
|
|
178 if resultFormat == 'JSON':
|
|
|
179 self.REQUEST.RESPONSE.setHeader("Content-Type", "application/json")
|
|
|
180 json.dump(types, self.REQUEST.RESPONSE)
|
|
|
181 else:
|
|
|
182 json.dump(types, self.REQUEST.RESPONSE)
|
|
|
183
|
|
|
184 def getListOfTypes(self,schema,table,tag):
|
|
52
|
185 """returns the list of existing types"""
|
|
51
|
186 logging.debug("getlistoftypes schema=%s table=%s tag=%s"%(schema,table,tag))
|
|
|
187 sql = 'select distinct json_type from "%s"."%s" where json_tag = %%s'%(schema,table)
|
|
|
188 res = self.executeSQL(sql,(tag,))
|
|
|
189 if len(res['rows']) > 0:
|
|
|
190 tags = [r[0] for r in res['rows']]
|
|
49
|
191 return tags
|
|
42
|
192
|
|
49
|
193 return []
|
|
|
194
|
|
51
|
195 def showListOfItems(self,resultFormat,schema,table,tag,type):
|
|
52
|
196 """shows the list of existing items"""
|
|
54
|
197 recursive = self.REQUEST.get("recursive", False)
|
|
|
198 if recursive:
|
|
|
199 items = self.getListOfItemsAndValues(schema, table, tag, type)
|
|
|
200 # items contain JSON-strings
|
|
|
201 its = ",".join(['{"key":"%s","val":%s}'%(i[0],i[1]) for i in items])
|
|
70
|
202 its = "[%s]"%its
|
|
54
|
203 if resultFormat == 'JSON':
|
|
|
204 self.REQUEST.RESPONSE.setHeader("Content-Type", "application/json")
|
|
70
|
205 # we assume utf-8
|
|
|
206 self.REQUEST.RESPONSE.write(utf8ify(its))
|
|
54
|
207 else:
|
|
70
|
208 # we assume utf-8
|
|
|
209 self.REQUEST.RESPONSE.write(utf8ify(its))
|
|
54
|
210
|
|
51
|
211 else:
|
|
54
|
212 items = self.getListOfItems(schema, table, tag, type)
|
|
|
213 if resultFormat == 'JSON':
|
|
|
214 self.REQUEST.RESPONSE.setHeader("Content-Type", "application/json")
|
|
|
215 json.dump(items, self.REQUEST.RESPONSE)
|
|
|
216 else:
|
|
|
217 json.dump(items, self.REQUEST.RESPONSE)
|
|
|
218
|
|
51
|
219
|
|
|
220 def getListOfItems(self,schema,table,tag,type):
|
|
52
|
221 """returns the list of existing items"""
|
|
51
|
222 logging.debug("getlistofitems schema=%s table=%s tag=%s type=%s"%(schema,table,tag,type))
|
|
|
223 sql = 'select distinct json_item from "%s"."%s" where json_tag = %%s and json_type = %%s'%(schema,table)
|
|
54
|
224
|
|
51
|
225 res = self.executeSQL(sql,(tag,type))
|
|
|
226 if len(res['rows']) > 0:
|
|
|
227 items = [r[0] for r in res['rows']]
|
|
|
228 return items
|
|
|
229
|
|
|
230 return []
|
|
|
231
|
|
54
|
232 def getListOfItemsAndValues(self,schema,table,tag,type):
|
|
|
233 """returns the list of existing items and their values"""
|
|
|
234 logging.debug("getlistofitemsandvalues schema=%s table=%s tag=%s type=%s"%(schema,table,tag,type))
|
|
|
235 sql = 'select json_item, json_value from "%s"."%s" where json_tag = %%s and json_type = %%s'%(schema,table)
|
|
|
236
|
|
|
237 res = self.executeSQL(sql,(tag,type))
|
|
|
238 if len(res['rows']) > 0:
|
|
|
239 return res['rows']
|
|
|
240
|
|
|
241 return []
|
|
|
242
|
|
51
|
243 def showItem(self,resultFormat,schema,table,tag,type,item):
|
|
52
|
244 """shows the item"""
|
|
51
|
245 item = self.getItem(schema, table, tag, type, item)
|
|
52
|
246 # item is a string
|
|
51
|
247 if resultFormat == 'JSON':
|
|
|
248 self.REQUEST.RESPONSE.setHeader("Content-Type", "application/json")
|
|
52
|
249 self.REQUEST.RESPONSE.write(item)
|
|
51
|
250 else:
|
|
52
|
251 self.REQUEST.RESPONSE.write(item)
|
|
51
|
252
|
|
|
253 def getItem(self,schema,table,tag,type,item):
|
|
52
|
254 """returns the item"""
|
|
51
|
255 logging.debug("getitem schema=%s table=%s tag=%s type=%s item=%s"%(schema,table,tag,type,item))
|
|
|
256 sql = 'select json_value from "%s"."%s" where json_tag = %%s and json_type = %%s and json_item = %%s'%(schema,table)
|
|
|
257 res = self.executeSQL(sql,(tag,type,item))
|
|
|
258 if len(res['rows']) > 0:
|
|
|
259 # just one item
|
|
|
260 item = res['rows'][0][0]
|
|
|
261 return item
|
|
|
262
|
|
52
|
263 return "{}"
|
|
|
264
|
|
|
265 def storeItem(self,schema,table,tag,type,item,value):
|
|
|
266 """sets the item to value"""
|
|
|
267 logging.debug("storeitem schema=%s table=%s tag=%s type=%s item=%s value=%s"%(schema,table,tag,type,item,value))
|
|
|
268 # see if the item exists
|
|
|
269 sql = 'select 1 from "%s"."%s" where json_tag = %%s and json_type = %%s and json_item = %%s'%(schema,table)
|
|
|
270 res = self.executeSQL(sql,(tag,type,item))
|
|
|
271 if len(res['rows']) > 0:
|
|
|
272 # then update
|
|
|
273 sql = 'update "%s"."%s" set json_value = %%s where json_tag = %%s and json_type = %%s and json_item = %%s'%(schema,table)
|
|
|
274
|
|
|
275 else:
|
|
|
276 # then insert
|
|
|
277 sql = 'insert into "%s"."%s" (json_value, json_tag, json_type, json_item) values (%%s,%%s,%%s,%%s)'%(schema,table)
|
|
|
278
|
|
|
279 self.executeSQL(sql,(value,tag,type,item), hasResult=False)
|
|
|
280 return "Ok"
|
|
51
|
281
|
|
50
|
282 manage_addRestDbJsonStoreForm=PageTemplateFile('zpt/addRestDbJsonStore',globals())
|
|
42
|
283
|
|
50
|
284 def manage_addRestDbJsonStore(self, id, title='', label='', description='',
|
|
42
|
285 createPublic=0,
|
|
|
286 createUserF=0,
|
|
|
287 REQUEST=None):
|
|
|
288 """Add a new object with id *id*."""
|
|
|
289
|
|
50
|
290 ob=RestDbJsonStore(str(id),title)
|
|
42
|
291 self._setObject(id, ob)
|
|
|
292
|
|
|
293 #checkPermission=getSecurityManager().checkPermission
|
|
|
294 REQUEST.RESPONSE.redirect('manage_main')
|
|
|
295
|
|
|
296
|
