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