Mercurial > hg > ZDBInterface
annotate WritableRestDbInterface.py @ 17:48ed91b29784
added stuff from RestDbGisApi to WritableRestDbInterface.
changed meta_type of RestDbInterface (remove "2").
| author | casties |
|---|---|
| date | Thu, 23 Feb 2012 08:33:48 +0100 |
| parents | 09361041be51 |
| children | 132ae1c0255a |
| 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 | |
| 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 | |
|
17
48ed91b29784
added stuff from RestDbGisApi to WritableRestDbInterface.
casties
parents:
0
diff
changeset
|
234 # TODO: check this index stuff |
|
48ed91b29784
added stuff from RestDbGisApi to WritableRestDbInterface.
casties
parents:
0
diff
changeset
|
235 lineIndex=0 |
| 0 | 236 for colNode in colNodes: |
|
17
48ed91b29784
added stuff from RestDbGisApi to WritableRestDbInterface.
casties
parents:
0
diff
changeset
|
237 lineIndex+=1 |
| 0 | 238 dataNodes=colNode.getElementsByTagNameNS(namespace, dataTagName) |
| 239 if len(dataNodes) > 0: | |
|
17
48ed91b29784
added stuff from RestDbGisApi to WritableRestDbInterface.
casties
parents:
0
diff
changeset
|
240 dataIndex=0 |
|
48ed91b29784
added stuff from RestDbGisApi to WritableRestDbInterface.
casties
parents:
0
diff
changeset
|
241 if colNode.hasAttribute(u'ss:Index'): |
|
48ed91b29784
added stuff from RestDbGisApi to WritableRestDbInterface.
casties
parents:
0
diff
changeset
|
242 dataIndex=int(colNode.getAttribute(u'ss:Index')) |
|
48ed91b29784
added stuff from RestDbGisApi to WritableRestDbInterface.
casties
parents:
0
diff
changeset
|
243 while dataIndex>lineIndex: |
|
48ed91b29784
added stuff from RestDbGisApi to WritableRestDbInterface.
casties
parents:
0
diff
changeset
|
244 data.append(None) |
|
48ed91b29784
added stuff from RestDbGisApi to WritableRestDbInterface.
casties
parents:
0
diff
changeset
|
245 lineIndex+=1 |
|
48ed91b29784
added stuff from RestDbGisApi to WritableRestDbInterface.
casties
parents:
0
diff
changeset
|
246 else: |
|
48ed91b29784
added stuff from RestDbGisApi to WritableRestDbInterface.
casties
parents:
0
diff
changeset
|
247 val = getTextFromNode(dataNodes[0]) |
|
48ed91b29784
added stuff from RestDbGisApi to WritableRestDbInterface.
casties
parents:
0
diff
changeset
|
248 hasData = True |
| 0 | 249 else: |
| 250 val = None | |
|
17
48ed91b29784
added stuff from RestDbGisApi to WritableRestDbInterface.
casties
parents:
0
diff
changeset
|
251 |
|
48ed91b29784
added stuff from RestDbGisApi to WritableRestDbInterface.
casties
parents:
0
diff
changeset
|
252 # TODO: check this |
|
48ed91b29784
added stuff from RestDbGisApi to WritableRestDbInterface.
casties
parents:
0
diff
changeset
|
253 if val!=None: |
|
48ed91b29784
added stuff from RestDbGisApi to WritableRestDbInterface.
casties
parents:
0
diff
changeset
|
254 a=val.rfind('.0') |
|
48ed91b29784
added stuff from RestDbGisApi to WritableRestDbInterface.
casties
parents:
0
diff
changeset
|
255 b=len(val) |
|
48ed91b29784
added stuff from RestDbGisApi to WritableRestDbInterface.
casties
parents:
0
diff
changeset
|
256 if a==b-2: |
|
48ed91b29784
added stuff from RestDbGisApi to WritableRestDbInterface.
casties
parents:
0
diff
changeset
|
257 val=val.rpartition('.')[0] |
| 0 | 258 data.append(val) |
| 259 | |
| 260 if not hasData: | |
| 261 # ignore empty rows | |
| 262 continue | |
| 263 | |
| 264 # fix number of data fields | |
| 265 if len(data) > numFields: | |
| 266 del data[numFields:] | |
| 267 elif len(data) < numFields: | |
| 268 missFields = numFields - len(data) | |
| 269 data.extend(missFields * [None,]) | |
| 270 | |
| 271 logging.debug("importexcel sqlinsert=%s data=%s"%(sqlInsert,data)) | |
| 272 self.executeSQL(sqlInsert, data, hasResult=False) | |
| 273 | |
| 274 return cnt | |
| 275 | |
| 276 | |
| 277 manage_addWritableRestDbInterfaceForm=PageTemplateFile('zpt/addWritableRestDbInterface',globals()) | |
| 278 | |
| 279 def manage_addWritableRestDbInterface(self, id, title='', label='', description='', | |
| 280 createPublic=0, | |
| 281 createUserF=0, | |
| 282 REQUEST=None): | |
| 283 """Add a new object with id *id*.""" | |
| 284 | |
| 285 ob=WritableRestDbInterface(str(id),title) | |
| 286 self._setObject(id, ob) | |
| 287 | |
| 288 #checkPermission=getSecurityManager().checkPermission | |
| 289 REQUEST.RESPONSE.redirect('manage_main') | |
| 290 | |
| 291 |