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