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