|
0
|
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
|
