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