Mercurial > hg > ChinaGisRestApi
annotate RestDbInterface.py @ 70:9ec7e32e8ad3
working on maps
nicer HTML (with xhtml DTD)
better unicode handling for psycopg
author | casties |
---|---|
date | Tue, 23 Nov 2010 17:16:25 +0100 |
parents | e81d034b28a5 |
children | 6f7f8dee6cd2 |
rev | line source |
---|---|
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
1 ''' |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
2 Created on 19.5.2010 |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
3 |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
4 @author: casties |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
5 ''' |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
6 |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
7 from OFS.Folder import Folder |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
8 from Products.PageTemplates.PageTemplateFile import PageTemplateFile |
60 | 9 from AccessControl import getSecurityManager, Unauthorized |
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
10 from Products.ZSQLExtend import ZSQLExtend |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
11 import logging |
15 | 12 import re |
18 | 13 import json |
36 | 14 import time |
70 | 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) | |
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
20 |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
21 from zope.interface import implements |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
22 from zope.publisher.interfaces import IPublishTraverse |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
23 from ZPublisher.BaseRequest import DefaultPublishTraverse |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
24 #from zope.publisher.interfaces import NotFound |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
25 #from zope.app import zapi |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
26 #from zope.component import queryMultiAdapter |
4
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
27 import Shared.DC.ZRDB.DA |
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
28 from Products.ZSQLMethods.SQL import SQLConnectionIDs |
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
29 |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
30 |
70 | 31 def unicodify(s,alternate='latin-1'): |
32 """decode str (utf-8 or latin-1 representation) into unicode object""" | |
33 if not s: | |
34 return u"" | |
35 if isinstance(s, str): | |
36 try: | |
37 return s.decode('utf-8') | |
38 except: | |
39 return s.decode(alternate) | |
40 else: | |
41 return s | |
42 | |
43 def utf8ify(s): | |
44 """encode unicode object or string into byte string in utf-8 representation. | |
45 assumes string objects to be utf-8""" | |
46 if not s: | |
47 return "" | |
48 if isinstance(s, str): | |
49 return s | |
50 else: | |
51 return s.encode('utf-8') | |
52 | |
16 | 53 def getTextFromNode(node): |
14 | 54 """get the cdata content of a XML node""" |
16 | 55 if node is None: |
14 | 56 return "" |
16 | 57 |
58 if isinstance(node, list): | |
59 nodelist = node | |
60 else: | |
61 nodelist=node.childNodes | |
62 | |
14 | 63 rc = "" |
64 for node in nodelist: | |
65 if node.nodeType == node.TEXT_NODE: | |
66 rc = rc + node.data | |
67 return rc | |
68 | |
16 | 69 def sqlName(s,lc=True): |
70 """returns restricted ASCII-only version of string""" | |
15 | 71 if s is None: |
72 return "" | |
73 | |
74 # all else -> "_" | |
16 | 75 s = re.sub(r'[^A-Za-z0-9_]','_',s) |
15 | 76 if lc: |
77 return s.lower() | |
78 | |
79 return s | |
14 | 80 |
31 | 81 |
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
82 class RestDbInterface(Folder): |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
83 """Object for RESTful database queries |
7 | 84 path schema: /db/{schema}/{table}/ |
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
85 omitting table gives a list of schemas |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
86 omitting table and schema gives a list of schemas |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
87 """ |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
88 implements(IPublishTraverse) |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
89 |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
90 meta_type="RESTdb" |
4
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
91 manage_options=Folder.manage_options+( |
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
92 {'label':'Config','action':'manage_editRestDbInterfaceForm'}, |
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
93 ) |
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
94 |
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
95 # management templates |
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
96 manage_editRestDbInterfaceForm=PageTemplateFile('zpt/editRestDbInterface',globals()) |
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
97 |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
98 # data templates |
8 | 99 XML_index = PageTemplateFile('zpt/XML_index', globals()) |
100 XML_schema = PageTemplateFile('zpt/XML_schema', globals()) | |
101 XML_schema_table = PageTemplateFile('zpt/XML_schema_table', globals()) | |
102 HTML_index = PageTemplateFile('zpt/HTML_index', globals()) | |
103 HTML_schema = PageTemplateFile('zpt/HTML_schema', globals()) | |
104 HTML_schema_table = PageTemplateFile('zpt/HTML_schema_table', globals()) | |
22 | 105 JSONHTML_index = PageTemplateFile('zpt/JSONHTML_index', globals()) |
106 JSONHTML_schema = PageTemplateFile('zpt/JSONHTML_schema', globals()) | |
107 JSONHTML_schema_table = PageTemplateFile('zpt/JSONHTML_schema_table', globals()) | |
24 | 108 # JSON_* templates are scripts |
45 | 109 def JSON_index(self): |
30 | 110 """JSON index function""" |
111 self.REQUEST.RESPONSE.setHeader("Content-Type", "application/json") | |
45 | 112 json.dump(self.getListOfSchemas(), self.REQUEST.RESPONSE) |
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
113 |
45 | 114 def JSON_schema(self,schema): |
30 | 115 """JSON index function""" |
116 self.REQUEST.RESPONSE.setHeader("Content-Type", "application/json") | |
45 | 117 json.dump(self.getListOfTables(schema), self.REQUEST.RESPONSE) |
30 | 118 |
44 | 119 def JSON_schema_table(self,schema,table): |
30 | 120 """JSON index function""" |
121 self.REQUEST.RESPONSE.setHeader("Content-Type", "application/json") | |
44 | 122 json.dump(self.getTable(schema, table), self.REQUEST.RESPONSE) |
30 | 123 |
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
124 |
4
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
125 def __init__(self, id, title, connection_id=None): |
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
126 """init""" |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
127 self.id = id |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
128 self.title = title |
4
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
129 # database connection id |
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
130 self.connection_id = connection_id |
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
131 # create template folder |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
132 self.manage_addFolder('template') |
44 | 133 |
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
134 |
44 | 135 def getRestDbUrl(self): |
136 """returns url to the RestDb instance""" | |
137 return self.absolute_url() | |
138 | |
22 | 139 def getJsonString(self,object): |
140 """returns a JSON formatted string from object""" | |
141 return json.dumps(object) | |
142 | |
17 | 143 def getCursor(self,autocommit=True): |
4
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
144 """returns fresh DB cursor""" |
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
145 conn = getattr(self,"_v_database_connection",None) |
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
146 if conn is None: |
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
147 # create a new connection object |
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
148 try: |
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
149 if self.connection_id is None: |
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
150 # try to take the first existing ID |
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
151 connids = SQLConnectionIDs(self) |
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
152 if len(connids) > 0: |
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
153 connection_id = connids[0][0] |
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
154 self.connection_id = connection_id |
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
155 logging.debug("connection_id: %s"%repr(connection_id)) |
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
156 |
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
157 da = getattr(self, self.connection_id) |
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
158 da.connect('') |
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
159 # we copy the DAs database connection |
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
160 conn = da._v_database_connection |
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
161 #conn._register() # register with the Zope transaction system |
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
162 self._v_database_connection = conn |
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
163 except Exception, e: |
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
164 raise IOError("No database connection! (%s)"%str(e)) |
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
165 |
5 | 166 cursor = conn.getcursor() |
17 | 167 if autocommit: |
168 # is there a better version to get to the connection? | |
169 cursor.connection.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT) | |
170 | |
4
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
171 return cursor |
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
172 |
55 | 173 def getFieldNameMap(self,fields): |
174 """returns a dict mapping field names to row indexes""" | |
175 map = {} | |
176 i = 0 | |
177 for f in fields: | |
178 map[f[0]] = i | |
179 i += 1 | |
180 | |
181 return map | |
182 | |
17 | 183 def executeSQL(self, query, args=None, hasResult=True, autocommit=True): |
4
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
184 """execute query with args on database and return all results. |
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
185 result format: {"fields":fields, "rows":data}""" |
16 | 186 logging.debug("executeSQL query=%s args=%s"%(query,args)) |
17 | 187 cur = self.getCursor(autocommit=autocommit) |
53 | 188 if args is not None: |
189 # make sure args is a list | |
190 if isinstance(args,basestring): | |
191 args = (args,) | |
192 | |
4
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
193 cur.execute(query, args) |
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
194 # description of returned fields |
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
195 fields = cur.description |
17 | 196 if hasResult: |
197 # get all data in an array | |
198 data = cur.fetchall() | |
199 cur.close() | |
70 | 200 logging.debug("fields: %s"%repr(fields)) |
201 logging.debug("rows: %s"%repr(data)) | |
17 | 202 return {"fields":fields, "rows":data} |
203 else: | |
204 cur.close() | |
205 return None | |
16 | 206 |
60 | 207 def isAllowed(self,action,schema,table,user=None): |
208 """returns if the requested action on the table is allowed""" | |
209 if user is None: | |
210 user = self.REQUEST.get('AUTHENTICATED_USER',None) | |
211 logging.debug("isAllowed action=%s schema=%s table=%s user=%s"%(action,schema,table,user)) | |
212 # no default policy! | |
213 return True | |
214 | |
215 | |
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
216 def publishTraverse(self,request,name): |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
217 """change the traversal""" |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
218 # get stored path |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
219 path = request.get('restdb_path', []) |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
220 logging.debug("publishtraverse: name=%s restdb_path=%s"%(name,path)) |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
221 |
13 | 222 if name in ("index_html", "PUT"): |
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
223 # end of traversal |
13 | 224 if request.get("method") == "POST" and request.get("action",None) == "PUT": |
225 # fake PUT by POST with action=PUT | |
226 name = "PUT" | |
227 | |
228 return getattr(self, name) | |
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
229 #TODO: should we check more? |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
230 else: |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
231 # traverse |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
232 if len(path) == 0: |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
233 # first segment |
8 | 234 if name == 'db': |
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
235 # virtual path -- continue traversing |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
236 path = [name] |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
237 request['restdb_path'] = path |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
238 else: |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
239 # try real path |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
240 tr = DefaultPublishTraverse(self, request) |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
241 ob = tr.publishTraverse(request, name) |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
242 return ob |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
243 else: |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
244 path.append(name) |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
245 |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
246 # continue traversing |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
247 return self |
44 | 248 |
249 | |
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
250 def index_html(self,REQUEST,RESPONSE): |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
251 """index method""" |
8 | 252 # ReST path was stored in request |
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
253 path = REQUEST.get('restdb_path',[]) |
36 | 254 |
8 | 255 # type and format are real parameter |
26 | 256 resultFormat = REQUEST.get('format','HTML').upper() |
27 | 257 queryType = REQUEST.get('type',None) |
36 | 258 |
26 | 259 logging.debug("index_html path=%s resultFormat=%s queryType=%s"%(path,resultFormat,queryType)) |
8 | 260 |
26 | 261 if queryType is not None: |
262 # non-empty queryType -- look for template | |
263 pt = getattr(self.template, "%s_%s"%(resultFormat,queryType), None) | |
8 | 264 if pt is not None: |
26 | 265 return pt(format=resultFormat,type=queryType,path=path) |
8 | 266 |
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
267 if len(path) == 1: |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
268 # list of schemas |
61 | 269 return self.showListOfSchemas(format=resultFormat) |
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
270 elif len(path) == 2: |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
271 # list of tables |
61 | 272 return self.showListOfTables(format=resultFormat,schema=path[1]) |
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
273 elif len(path) == 3: |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
274 # table |
25 | 275 if REQUEST.get("method") == "POST" and REQUEST.get("create_table_file",None) is not None: |
24 | 276 # POST to table to check |
61 | 277 return self.checkTable(format=resultFormat,schema=path[1],table=path[2]) |
24 | 278 # else show table |
61 | 279 return self.showTable(format=resultFormat,schema=path[1],table=path[2]) |
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
280 |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
281 # don't know what to do |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
282 return str(REQUEST) |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
283 |
12 | 284 def PUT(self, REQUEST, RESPONSE): |
285 """ | |
286 Implement WebDAV/HTTP PUT/FTP put method for this object. | |
287 """ | |
13 | 288 logging.debug("RestDbInterface PUT") |
289 #logging.debug("req=%s"%REQUEST) | |
12 | 290 #self.dav__init(REQUEST, RESPONSE) |
291 #self.dav__simpleifhandler(REQUEST, RESPONSE) | |
13 | 292 # ReST path was stored in request |
293 path = REQUEST.get('restdb_path',[]) | |
294 if len(path) == 3: | |
295 schema = path[1] | |
296 tablename = path[2] | |
297 file = REQUEST.get("create_table_file",None) | |
298 if file is None: | |
299 RESPONSE.setStatus(400) | |
300 return | |
4
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
301 |
30 | 302 fields = None |
303 fieldsStr = REQUEST.get("create_table_fields",None) | |
304 logging.debug("put with schema=%s table=%s file=%s fields=%s"%(schema,tablename,file,repr(fieldsStr))) | |
305 if fieldsStr is not None: | |
306 # unpack fields | |
307 fields = [{"name":n, "type": t} for (n,t) in [f.split(":") for f in fieldsStr.split(",")]] | |
31 | 308 |
28 | 309 ret = self.createTableFromXML(schema, tablename, file, fields) |
26 | 310 # return the result as JSON |
311 format = REQUEST.get("format","JSON") | |
312 if format == "JSON": | |
20 | 313 RESPONSE.setHeader("Content-Type", "application/json") |
314 json.dump(ret, RESPONSE) | |
26 | 315 |
316 elif format == "JSONHTML": | |
20 | 317 RESPONSE.setHeader("Content-Type", "text/html") |
318 RESPONSE.write("<html>\n<body>\n<pre>") | |
319 json.dump(ret, RESPONSE) | |
320 RESPONSE.write("</pre>\n</body>\n</html>") | |
13 | 321 |
322 else: | |
323 # 400 Bad Request | |
324 RESPONSE.setStatus(400) | |
325 return | |
12 | 326 |
61 | 327 def showTable(self,format='XML',schema='public',table=None,REQUEST=None,RESPONSE=None): |
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
328 """returns PageTemplate with tables""" |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
329 logging.debug("showtable") |
38 | 330 if REQUEST is None: |
331 REQUEST = self.REQUEST | |
332 | |
23 | 333 # should be cross-site accessible |
25 | 334 if RESPONSE is None: |
335 RESPONSE = self.REQUEST.RESPONSE | |
38 | 336 |
23 | 337 RESPONSE.setHeader('Access-Control-Allow-Origin', '*') |
38 | 338 |
22 | 339 # everything else has its own template |
61 | 340 pt = getattr(self.template, '%s_schema_table'%format, None) |
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
341 if pt is None: |
61 | 342 return "ERROR!! template %s_schema_table not found"%format |
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
343 |
44 | 344 #data = self.getTable(schema,table) |
345 return pt(schema=schema,table=table) | |
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
346 |
58 | 347 def getTable(self,schema='public',table=None,sortBy=1,username='guest'): |
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
348 """return table data""" |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
349 logging.debug("gettable") |
58 | 350 if sortBy: |
351 data = self.executeSQL('select * from "%s"."%s" order by %s'%(schema,table,sortBy)) | |
352 else: | |
353 data = self.executeSQL('select * from "%s"."%s"'%(schema,table)) | |
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
354 return data |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
355 |
26 | 356 def hasTable(self,schema='public',table=None,username='guest'): |
357 """return if table exists""" | |
358 logging.debug("hastable") | |
359 data = self.executeSQL('select 1 from information_schema.tables where table_schema=%s and table_name=%s',(schema,table)) | |
360 ret = bool(data['rows']) | |
361 return ret | |
362 | |
61 | 363 def showListOfTables(self,format='XML',schema='public',REQUEST=None,RESPONSE=None): |
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
364 """returns PageTemplate with list of tables""" |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
365 logging.debug("showlistoftables") |
23 | 366 # should be cross-site accessible |
25 | 367 if RESPONSE is None: |
368 RESPONSE = self.REQUEST.RESPONSE | |
23 | 369 RESPONSE.setHeader('Access-Control-Allow-Origin', '*') |
24 | 370 |
61 | 371 pt = getattr(self.template, '%s_schema'%format, None) |
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
372 if pt is None: |
61 | 373 return "ERROR!! template %s_schema not found"%format |
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
374 |
45 | 375 #data = self.getListOfTables(schema) |
376 return pt(schema=schema) | |
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
377 |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
378 def getListOfTables(self,schema='public',username='guest'): |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
379 """return list of tables""" |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
380 logging.debug("getlistoftables") |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
381 # get list of fields and types of db table |
61 | 382 #qstr="""SELECT c.relname AS tablename FROM pg_catalog.pg_class c |
383 # LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace | |
384 # WHERE c.relkind IN ('r','') AND n.nspname NOT IN ('pg_catalog', 'pg_toast') | |
385 # AND pg_catalog.pg_table_is_visible(c.oid) | |
386 # AND c.relname ORDER BY 1""" | |
387 qstr = """SELECT table_name FROM information_schema.tables WHERE table_type = 'BASE TABLE' | |
388 AND table_schema = %s ORDER BY 1""" | |
389 data=self.executeSQL(qstr,(schema,)) | |
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
390 return data |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
391 |
61 | 392 def showListOfSchemas(self,format='XML',REQUEST=None,RESPONSE=None): |
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
393 """returns PageTemplate with list of schemas""" |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
394 logging.debug("showlistofschemas") |
23 | 395 # should be cross-site accessible |
25 | 396 if RESPONSE is None: |
397 RESPONSE = self.REQUEST.RESPONSE | |
23 | 398 RESPONSE.setHeader('Access-Control-Allow-Origin', '*') |
24 | 399 |
61 | 400 pt = getattr(self.template, '%s_index'%format, None) |
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
401 if pt is None: |
61 | 402 return "ERROR!! template %s_index not found"%format |
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
403 |
45 | 404 #data = self.getListOfSchemas() |
405 return pt() | |
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
406 |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
407 def getListOfSchemas(self,username='guest'): |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
408 """return list of schemas""" |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
409 logging.debug("getlistofschemas") |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
410 # TODO: really look up schemas |
9 | 411 data={'fields': (('schemas',),), 'rows': [('public',),]} |
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
412 return data |
14 | 413 |
61 | 414 def checkTable(self,format,schema,table,REQUEST=None,RESPONSE=None): |
26 | 415 """check the table. |
416 returns valid data fields and table name.""" | |
417 if REQUEST is None: | |
418 REQUEST = self.REQUEST | |
419 RESPONSE = REQUEST.RESPONSE | |
420 | |
421 file = REQUEST.get("create_table_file",None) | |
422 res = self.checkTableFromXML(schema, table, file) | |
423 logging.debug("checkTable result=%s"%repr(res)) | |
424 # return the result as JSON | |
61 | 425 if format == "JSON": |
26 | 426 RESPONSE.setHeader("Content-Type", "application/json") |
427 json.dump(res, RESPONSE) | |
428 | |
61 | 429 elif format == "JSONHTML": |
26 | 430 RESPONSE.setHeader("Content-Type", "text/html") |
431 RESPONSE.write("<html>\n<body>\n<pre>") | |
432 json.dump(res, RESPONSE) | |
433 RESPONSE.write("</pre>\n</body>\n</html>") | |
434 | |
435 else: | |
61 | 436 return "ERROR: invalid format" |
26 | 437 |
438 def checkTableFromXML(self,schema,table,data,REQUEST=None,RESPONSE=None): | |
439 """check the table with the given XML data. | |
440 returns valid data fields and table name.""" | |
441 logging.debug("checkTableFromXML schema=%s table=%s"%(schema,table)) | |
442 # clean table name | |
443 tablename = sqlName(table) | |
444 tableExists = self.hasTable(schema, table) | |
445 if data is None: | |
446 fieldNames = [] | |
447 else: | |
448 # get list of field names from upload file | |
449 fields = self.importExcelXML(schema,tablename,data,fieldsOnly=True) | |
450 | |
451 res = {'tablename': tablename, 'table_exists': tableExists} | |
452 res['fields'] = fields | |
453 return res | |
454 | |
14 | 455 def createEmptyTable(self,schema,table,fields): |
456 """create a table with the given fields | |
457 returns list of created fields""" | |
15 | 458 logging.debug("createEmptyTable") |
60 | 459 |
14 | 460 sqlFields = [] |
461 for f in fields: | |
462 if isinstance(f,dict): | |
463 # {name: XX, type: YY} | |
464 name = sqlName(f['name']) | |
465 type = f['type'] | |
70 | 466 if hasattr(self, 'toSqlTypeMap'): |
467 sqltype = self.toSqlTypeMap[type] | |
468 else: | |
469 sqltype = 'text' | |
14 | 470 |
471 else: | |
472 # name only | |
473 name = sqlName(f) | |
474 type = 'text' | |
31 | 475 sqltype = 'text' |
14 | 476 |
31 | 477 sqlFields.append({'name':name, 'type':type, 'sqltype':sqltype}) |
14 | 478 |
60 | 479 if self.hasTable(schema,table): |
480 # TODO: find owner | |
481 if not self.isAllowed("update", schema, table): | |
482 raise Unauthorized | |
483 self.executeSQL('drop table "%s"."%s"'%(schema,table),hasResult=False) | |
484 else: | |
485 if not self.isAllowed("create", schema, table): | |
486 raise Unauthorized | |
487 | |
43 | 488 fieldString = ", ".join(['"%s" %s'%(f['name'],f['sqltype']) for f in sqlFields]) |
489 sqlString = 'create table "%s"."%s" (%s)'%(schema,table,fieldString) | |
490 logging.debug("createemptytable: SQL=%s"%sqlString) | |
491 self.executeSQL(sqlString,hasResult=False) | |
492 self.setTableMetaTypes(schema,table,sqlFields) | |
493 return sqlFields | |
26 | 494 |
28 | 495 def createTableFromXML(self,schema,table,data, fields=None): |
13 | 496 """create or replace a table with the given XML data""" |
28 | 497 logging.debug("createTableFromXML schema=%s table=%s data=%s fields=%s"%(schema,table,data,fields)) |
17 | 498 tablename = sqlName(table) |
28 | 499 self.importExcelXML(schema, tablename, data, fields) |
17 | 500 return {"tablename": tablename} |
14 | 501 |
28 | 502 def importExcelXML(self,schema,table,xmldata,fields=None,fieldsOnly=False): |
14 | 503 ''' |
504 Import XML file in Excel format into the table | |
505 @param table: name of the table the xml shall be imported into | |
506 ''' | |
507 from xml.dom.pulldom import parseString,parse | |
508 | |
60 | 509 if not (fieldsOnly or self.isAllowed("create", schema, table)): |
510 raise Unauthorized | |
511 | |
14 | 512 namespace = "urn:schemas-microsoft-com:office:spreadsheet" |
513 containerTagName = "Table" | |
514 rowTagName = "Row" | |
16 | 515 colTagName = "Cell" |
14 | 516 dataTagName = "Data" |
26 | 517 xmlFields = [] |
14 | 518 sqlFields = [] |
16 | 519 numFields = 0 |
14 | 520 sqlInsert = None |
521 | |
522 logging.debug("import excel xml") | |
523 | |
524 ret="" | |
16 | 525 if isinstance(xmldata, str): |
14 | 526 logging.debug("importXML reading string data") |
16 | 527 doc=parseString(xmldata) |
14 | 528 else: |
529 logging.debug("importXML reading file data") | |
16 | 530 doc=parse(xmldata) |
14 | 531 |
532 cnt = 0 | |
533 while True: | |
534 node=doc.getEvent() | |
535 | |
536 if node is None: | |
537 break | |
538 | |
539 else: | |
16 | 540 #logging.debug("tag=%s"%node[1].localName) |
14 | 541 if node[1].localName is not None: |
542 tagName = node[1].localName.lower() | |
543 else: | |
544 # ignore non-tag nodes | |
545 continue | |
16 | 546 |
14 | 547 if tagName == rowTagName.lower(): |
548 # start of row | |
549 doc.expandNode(node[1]) | |
550 cnt += 1 | |
551 if cnt == 1: | |
552 # first row -- field names | |
553 names=node[1].getElementsByTagNameNS(namespace, dataTagName) | |
554 for name in names: | |
555 fn = getTextFromNode(name) | |
26 | 556 xmlFields.append({'name':sqlName(fn),'type':'text'}) |
14 | 557 |
26 | 558 if fieldsOnly: |
14 | 559 # return just field names |
26 | 560 return xmlFields |
14 | 561 |
562 # create table | |
28 | 563 if fields is None: |
31 | 564 fields = xmlFields |
565 | |
566 sqlFields = self.createEmptyTable(schema, table, fields) | |
16 | 567 numFields = len(sqlFields) |
15 | 568 fieldString = ", ".join(['"%s"'%f['name'] for f in sqlFields]) |
14 | 569 valString = ", ".join(["%s" for f in sqlFields]) |
16 | 570 sqlInsert = 'insert into "%s"."%s" (%s) values (%s)'%(schema,table,fieldString,valString) |
26 | 571 #logging.debug("importexcelsql: sqlInsert=%s"%sqlInsert) |
14 | 572 |
573 else: | |
574 # following rows are data | |
16 | 575 colNodes=node[1].getElementsByTagNameNS(namespace, colTagName) |
14 | 576 data = [] |
16 | 577 hasData = False |
578 for colNode in colNodes: | |
579 dataNodes=colNode.getElementsByTagNameNS(namespace, dataTagName) | |
580 if len(dataNodes) > 0: | |
581 val = getTextFromNode(dataNodes[0]) | |
582 hasData = True | |
583 else: | |
584 val = None | |
585 | |
14 | 586 data.append(val) |
587 | |
16 | 588 if not hasData: |
589 # ignore empty rows | |
590 continue | |
591 | |
592 # fix number of data fields | |
593 if len(data) > numFields: | |
594 del data[numFields:] | |
595 elif len(data) < numFields: | |
596 missFields = numFields - len(data) | |
597 data.extend(missFields * [None,]) | |
598 | |
599 logging.debug("importexcel sqlinsert=%s data=%s"%(sqlInsert,data)) | |
17 | 600 self.executeSQL(sqlInsert, data, hasResult=False) |
14 | 601 |
16 | 602 return cnt |
38 | 603 |
4
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
604 def manage_editRestDbInterface(self, title=None, connection_id=None, |
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
605 REQUEST=None): |
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
606 """Change the object""" |
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
607 if title is not None: |
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
608 self.title = title |
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
609 |
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
610 if connection_id is not None: |
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
611 self.connection_id = connection_id |
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
612 |
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
613 #checkPermission=getSecurityManager().checkPermission |
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
614 REQUEST.RESPONSE.redirect('manage_main') |
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
615 |
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
616 |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
617 manage_addRestDbInterfaceForm=PageTemplateFile('zpt/addRestDbInterface',globals()) |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
618 |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
619 def manage_addRestDbInterface(self, id, title='', label='', description='', |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
620 createPublic=0, |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
621 createUserF=0, |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
622 REQUEST=None): |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
623 """Add a new object with id *id*.""" |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
624 |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
625 ob=RestDbInterface(str(id),title) |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
626 self._setObject(id, ob) |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
627 |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
628 #checkPermission=getSecurityManager().checkPermission |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
629 REQUEST.RESPONSE.redirect('manage_main') |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
630 |
5 | 631 |