|
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))
|
|
42
|
129 #logging.debug("req=%s"%REQUEST)
|
|
|
130 #self.dav__init(REQUEST, RESPONSE)
|
|
|
131 #self.dav__simpleifhandler(REQUEST, RESPONSE)
|
|
|
132 # ReST path was stored in request
|
|
|
133 if len(path) == 3:
|
|
|
134 schema = path[1]
|
|
|
135 tablename = path[2]
|
|
|
136 file = REQUEST.get("create_table_file",None)
|
|
|
137 if file is None:
|
|
|
138 RESPONSE.setStatus(400)
|
|
|
139 return
|
|
|
140
|
|
|
141 fields = None
|
|
|
142 fieldsStr = REQUEST.get("create_table_fields",None)
|
|
|
143 logging.debug("put with schema=%s table=%s file=%s fields=%s"%(schema,tablename,file,repr(fieldsStr)))
|
|
|
144 if fieldsStr is not None:
|
|
|
145 # unpack fields
|
|
|
146 fields = [{"name":n, "type": t} for (n,t) in [f.split(":") for f in fieldsStr.split(",")]]
|
|
|
147
|
|
|
148 ret = self.createTableFromXML(schema, tablename, file, fields)
|
|
|
149 # return the result as JSON
|
|
|
150 format = REQUEST.get("format","JSON")
|
|
|
151 if format == "JSON":
|
|
|
152 RESPONSE.setHeader("Content-Type", "application/json")
|
|
|
153 json.dump(ret, RESPONSE)
|
|
|
154
|
|
|
155 elif format == "JSONHTML":
|
|
|
156 RESPONSE.setHeader("Content-Type", "text/html")
|
|
|
157 RESPONSE.write("<html>\n<body>\n<pre>")
|
|
|
158 json.dump(ret, RESPONSE)
|
|
|
159 RESPONSE.write("</pre>\n</body>\n</html>")
|
|
|
160
|
|
|
161 else:
|
|
|
162 # 400 Bad Request
|
|
|
163 RESPONSE.setStatus(400)
|
|
|
164 return
|
|
50
|
165
|
|
49
|
166 def showListOfTags(self,resultFormat,schema,table):
|
|
|
167 """returns the list of existing tags"""
|
|
50
|
168 tags = self.getListOfTags(schema, table)
|
|
|
169 if resultFormat == 'JSON':
|
|
|
170 self.REQUEST.RESPONSE.setHeader("Content-Type", "application/json")
|
|
|
171 json.dump(tags, self.REQUEST.RESPONSE)
|
|
|
172 else:
|
|
|
173 json.dump(tags, self.REQUEST.RESPONSE)
|
|
|
174
|
|
|
175 def getListOfTags(self,schema,table):
|
|
|
176 """returns the list of existing tags"""
|
|
|
177 logging.debug("getlistoftags schema=%s table=%s"%(schema,table))
|
|
|
178 sql = 'select distinct json_tag from "%s"."%s"'%(schema,table)
|
|
49
|
179 res = self.executeSQL(sql)
|
|
|
180 if len(res['rows']) > 0:
|
|
51
|
181 tags = [r[0] for r in res['rows']]
|
|
|
182 return tags
|
|
|
183
|
|
|
184 return []
|
|
|
185
|
|
|
186 def showListOfTypes(self,resultFormat,schema,table,tag):
|
|
|
187 """returns the list of existing types"""
|
|
|
188 types = self.getListOfTypes(schema, table,tag)
|
|
|
189 if resultFormat == 'JSON':
|
|
|
190 self.REQUEST.RESPONSE.setHeader("Content-Type", "application/json")
|
|
|
191 json.dump(types, self.REQUEST.RESPONSE)
|
|
|
192 else:
|
|
|
193 json.dump(types, self.REQUEST.RESPONSE)
|
|
|
194
|
|
|
195 def getListOfTypes(self,schema,table,tag):
|
|
|
196 """returns the list of existing tags"""
|
|
|
197 logging.debug("getlistoftypes schema=%s table=%s tag=%s"%(schema,table,tag))
|
|
|
198 sql = 'select distinct json_type from "%s"."%s" where json_tag = %%s'%(schema,table)
|
|
|
199 res = self.executeSQL(sql,(tag,))
|
|
|
200 if len(res['rows']) > 0:
|
|
|
201 tags = [r[0] for r in res['rows']]
|
|
49
|
202 return tags
|
|
42
|
203
|
|
49
|
204 return []
|
|
|
205
|
|
51
|
206 def showListOfItems(self,resultFormat,schema,table,tag,type):
|
|
|
207 """returns the list of existing items"""
|
|
|
208 items = self.getListOfItems(schema, table, tag, type)
|
|
|
209 if resultFormat == 'JSON':
|
|
|
210 self.REQUEST.RESPONSE.setHeader("Content-Type", "application/json")
|
|
|
211 json.dump(items, self.REQUEST.RESPONSE)
|
|
|
212 else:
|
|
|
213 json.dump(items, self.REQUEST.RESPONSE)
|
|
|
214
|
|
|
215 def getListOfItems(self,schema,table,tag,type):
|
|
|
216 """returns the list of existing tags"""
|
|
|
217 logging.debug("getlistofitems schema=%s table=%s tag=%s type=%s"%(schema,table,tag,type))
|
|
|
218 sql = 'select distinct json_item from "%s"."%s" where json_tag = %%s and json_type = %%s'%(schema,table)
|
|
|
219 res = self.executeSQL(sql,(tag,type))
|
|
|
220 if len(res['rows']) > 0:
|
|
|
221 items = [r[0] for r in res['rows']]
|
|
|
222 return items
|
|
|
223
|
|
|
224 return []
|
|
|
225
|
|
|
226 def showItem(self,resultFormat,schema,table,tag,type,item):
|
|
|
227 """returns the item"""
|
|
|
228 item = self.getItem(schema, table, tag, type, item)
|
|
|
229 if resultFormat == 'JSON':
|
|
|
230 self.REQUEST.RESPONSE.setHeader("Content-Type", "application/json")
|
|
|
231 json.dump(item, self.REQUEST.RESPONSE)
|
|
|
232 else:
|
|
|
233 json.dump(item, self.REQUEST.RESPONSE)
|
|
|
234
|
|
|
235 def getItem(self,schema,table,tag,type,item):
|
|
|
236 """returns the list of existing tags"""
|
|
|
237 logging.debug("getitem schema=%s table=%s tag=%s type=%s item=%s"%(schema,table,tag,type,item))
|
|
|
238 sql = 'select json_value from "%s"."%s" where json_tag = %%s and json_type = %%s and json_item = %%s'%(schema,table)
|
|
|
239 res = self.executeSQL(sql,(tag,type,item))
|
|
|
240 if len(res['rows']) > 0:
|
|
|
241 # just one item
|
|
|
242 item = res['rows'][0][0]
|
|
|
243 return item
|
|
|
244
|
|
|
245 return {}
|
|
|
246
|
|
50
|
247 manage_addRestDbJsonStoreForm=PageTemplateFile('zpt/addRestDbJsonStore',globals())
|
|
42
|
248
|
|
50
|
249 def manage_addRestDbJsonStore(self, id, title='', label='', description='',
|
|
42
|
250 createPublic=0,
|
|
|
251 createUserF=0,
|
|
|
252 REQUEST=None):
|
|
|
253 """Add a new object with id *id*."""
|
|
|
254
|
|
50
|
255 ob=RestDbJsonStore(str(id),title)
|
|
42
|
256 self._setObject(id, ob)
|
|
|
257
|
|
|
258 #checkPermission=getSecurityManager().checkPermission
|
|
|
259 REQUEST.RESPONSE.redirect('manage_main')
|
|
|
260
|
|
|
261
|
