Mercurial > hg > ZDBInterface

comparison WritableRestDbInterface.py @ 0:09361041be51

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
first checkin
author casties
date Fri, 11 Feb 2011 15:05:23 +0100
parents
children 48ed91b29784
comparison
equal deleted inserted replaced
-1:000000000000 0:09361041be51
1 '''
2 Created on 11.2.2011
3
4 @author: casties, fknauft
5 '''
6
7 import logging
8 import re
9 import time
10 import datetime
11 import urllib
12
13 from RestDbInterface import *
14
15
16
17 class WritableRestDbInterface(RestDbInterface):
18 """Object for RESTful database queries
19 path schema: /db/{schema}/{table}/
20 omitting table gives a list of schemas
21 omitting table and schema gives a list of schemas
22 """
23
24 meta_type="rwRESTdb"
25
26
27 def PUT(self, REQUEST, RESPONSE):
28 """
29 Implement WebDAV/HTTP PUT/FTP put method for this object.
30 """
31 logging.debug("RestDbInterface PUT")
32 #logging.debug("req=%s"%REQUEST)
33 #self.dav__init(REQUEST, RESPONSE)
34 #self.dav__simpleifhandler(REQUEST, RESPONSE)
35 # ReST path was stored in request
36 path = REQUEST.get('restdb_path',[])
37 if len(path) == 3:
38 schema = path[1]
39 tablename = path[2]
40 file = REQUEST.get("create_table_file",None)
41 if file is None:
42 RESPONSE.setStatus(400)
43 return
44
45 fields = None
46 fieldsStr = REQUEST.get("create_table_fields",None)
47 logging.debug("put with schema=%s table=%s file=%s fields=%s"%(schema,tablename,file,repr(fieldsStr)))
48 if fieldsStr is not None:
49 # unpack fields
50 fields = [{"name":n, "type": t} for (n,t) in [f.split(":") for f in fieldsStr.split(",")]]
51
52 ret = self.createTableFromXML(schema, tablename, file, fields)
53 # return the result as JSON
54 format = REQUEST.get("format","JSON")
55 if format == "JSON":
56 RESPONSE.setHeader("Content-Type", "application/json")
57 json.dump(ret, RESPONSE)
58
59 elif format == "JSONHTML":
60 RESPONSE.setHeader("Content-Type", "text/html")
61 RESPONSE.write("<html>\n<body>\n<pre>")
62 json.dump(ret, RESPONSE)
63 RESPONSE.write("</pre>\n</body>\n</html>")
64
65 else:
66 # 400 Bad Request
67 RESPONSE.setStatus(400)
68 return
69
70 def checkTable(self,format,schema,table,REQUEST=None,RESPONSE=None):
71 """check the table.
72 returns valid data fields and table name."""
73 if REQUEST is None:
74 REQUEST = self.REQUEST
75 RESPONSE = REQUEST.RESPONSE
76
77 file = REQUEST.get("create_table_file",None)
78 res = self.checkTableFromXML(schema, table, file)
79 logging.debug("checkTable result=%s"%repr(res))
80 # return the result as JSON
81 if format == "JSON":
82 RESPONSE.setHeader("Content-Type", "application/json")
83 json.dump(res, RESPONSE)
84
85 elif format == "JSONHTML":
86 RESPONSE.setHeader("Content-Type", "text/html")
87 RESPONSE.write("<html>\n<body>\n<pre>")
88 json.dump(res, RESPONSE)
89 RESPONSE.write("</pre>\n</body>\n</html>")
90
91 else:
92 return "ERROR: invalid format"
93
94 def checkTableFromXML(self,schema,table,data,REQUEST=None,RESPONSE=None):
95 """check the table with the given XML data.
96 returns valid data fields and table name."""
97 logging.debug("checkTableFromXML schema=%s table=%s"%(schema,table))
98 # clean table name
99 tablename = sqlName(table)
100 tableExists = self.hasTable(schema, table)
101 if data is None:
102 fieldNames = []
103 else:
104 # get list of field names from upload file
105 fields = self.importExcelXML(schema,tablename,data,fieldsOnly=True)
106
107 res = {'tablename': tablename, 'table_exists': tableExists}
108 res['fields'] = fields
109 return res
110
111 def createEmptyTable(self,schema,table,fields):
112 """create a table with the given fields
113 returns list of created fields"""
114 logging.debug("createEmptyTable")
115
116 sqlFields = []
117 for f in fields:
118 if isinstance(f,dict):
119 # {name: XX, type: YY}
120 name = sqlName(f['name'])
121 type = f['type']
122 if hasattr(self, 'toSqlTypeMap'):
123 sqltype = self.toSqlTypeMap[type]
124 else:
125 sqltype = 'text'
126
127 else:
128 # name only
129 name = sqlName(f)
130 type = 'text'
131 sqltype = 'text'
132
133 sqlFields.append({'name':name, 'type':type, 'sqltype':sqltype})
134
135 if self.hasTable(schema,table):
136 # TODO: find owner
137 if not self.isAllowed("update", schema, table):
138 raise Unauthorized
139 self.executeSQL('drop table "%s"."%s"'%(schema,table),hasResult=False)
140 else:
141 if not self.isAllowed("create", schema, table):
142 raise Unauthorized
143
144 fieldString = ", ".join(['"%s" %s'%(f['name'],f['sqltype']) for f in sqlFields])
145 sqlString = 'create table "%s"."%s" (%s)'%(schema,table,fieldString)
146 logging.debug("createemptytable: SQL=%s"%sqlString)
147 self.executeSQL(sqlString,hasResult=False)
148 self.setTableMetaTypes(schema,table,sqlFields)
149 return sqlFields
150
151 def createTableFromXML(self,schema,table,data, fields=None):
152 """create or replace a table with the given XML data"""
153 logging.debug("createTableFromXML schema=%s table=%s data=%s fields=%s"%(schema,table,data,fields))
154 tablename = sqlName(table)
155 self.importExcelXML(schema, tablename, data, fields)
156 return {"tablename": tablename}
157
158 def importExcelXML(self,schema,table,xmldata,fields=None,fieldsOnly=False):
159 '''
160 Import XML file in Excel format into the table
161 @param table: name of the table the xml shall be imported into
162 '''
163 from xml.dom.pulldom import parseString,parse
164
165 if not (fieldsOnly or self.isAllowed("create", schema, table)):
166 raise Unauthorized
167
168 namespace = "urn:schemas-microsoft-com:office:spreadsheet"
169 containerTagName = "Table"
170 rowTagName = "Row"
171 colTagName = "Cell"
172 dataTagName = "Data"
173 xmlFields = []
174 sqlFields = []
175 numFields = 0
176 sqlInsert = None
177
178 logging.debug("import excel xml")
179
180 ret=""
181 if isinstance(xmldata, str):
182 logging.debug("importXML reading string data")
183 doc=parseString(xmldata)
184 else:
185 logging.debug("importXML reading file data")
186 doc=parse(xmldata)
187
188 cnt = 0
189 while True:
190 node=doc.getEvent()
191
192 if node is None:
193 break
194
195 else:
196 #logging.debug("tag=%s"%node[1].localName)
197 if node[1].localName is not None:
198 tagName = node[1].localName.lower()
199 else:
200 # ignore non-tag nodes
201 continue
202
203 if tagName == rowTagName.lower():
204 # start of row
205 doc.expandNode(node[1])
206 cnt += 1
207 if cnt == 1:
208 # first row -- field names
209 names=node[1].getElementsByTagNameNS(namespace, dataTagName)
210 for name in names:
211 fn = getTextFromNode(name)
212 xmlFields.append({'name':sqlName(fn),'type':'text'})
213
214 if fieldsOnly:
215 # return just field names
216 return xmlFields
217
218 # create table
219 if fields is None:
220 fields = xmlFields
221
222 sqlFields = self.createEmptyTable(schema, table, fields)
223 numFields = len(sqlFields)
224 fieldString = ", ".join(['"%s"'%f['name'] for f in sqlFields])
225 valString = ", ".join(["%s" for f in sqlFields])
226 sqlInsert = 'insert into "%s"."%s" (%s) values (%s)'%(schema,table,fieldString,valString)
227 #logging.debug("importexcelsql: sqlInsert=%s"%sqlInsert)
228
229 else:
230 # following rows are data
231 colNodes=node[1].getElementsByTagNameNS(namespace, colTagName)
232 data = []
233 hasData = False
234 for colNode in colNodes:
235 dataNodes=colNode.getElementsByTagNameNS(namespace, dataTagName)
236 if len(dataNodes) > 0:
237 val = getTextFromNode(dataNodes[0])
238 hasData = True
239 else:
240 val = None
241
242 data.append(val)
243
244 if not hasData:
245 # ignore empty rows
246 continue
247
248 # fix number of data fields
249 if len(data) > numFields:
250 del data[numFields:]
251 elif len(data) < numFields:
252 missFields = numFields - len(data)
253 data.extend(missFields * [None,])
254
255 logging.debug("importexcel sqlinsert=%s data=%s"%(sqlInsert,data))
256 self.executeSQL(sqlInsert, data, hasResult=False)
257
258 return cnt
259
260
261 manage_addWritableRestDbInterfaceForm=PageTemplateFile('zpt/addWritableRestDbInterface',globals())
262
263 def manage_addWritableRestDbInterface(self, id, title='', label='', description='',
264 createPublic=0,
265 createUserF=0,
266 REQUEST=None):
267 """Add a new object with id *id*."""
268
269 ob=WritableRestDbInterface(str(id),title)
270 self._setObject(id, ob)
271
272 #checkPermission=getSecurityManager().checkPermission
273 REQUEST.RESPONSE.redirect('manage_main')
274
275