Mercurial > hg > ChinaGisRestApi
annotate RestDbInterface.py @ 37:b356b86773a1
Merge with falks stuff b915bce653725bd0690605119fc9b3123253e560
| author | casties |
|---|---|
| date | Wed, 01 Sep 2010 18:47:06 +0200 |
| parents | 55dbaea75e66 b915bce65372 |
| children | f6d9a3caf986 |
| 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 |
|
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
9 from Products.ZSQLExtend import ZSQLExtend |
|
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
10 import logging |
| 15 | 11 import re |
| 16 | 12 import psycopg2 |
| 18 | 13 import json |
| 36 | 14 import time |
|
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
15 |
|
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
16 from zope.interface import implements |
|
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
17 from zope.publisher.interfaces import IPublishTraverse |
|
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
18 from ZPublisher.BaseRequest import DefaultPublishTraverse |
|
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
19 #from zope.publisher.interfaces import NotFound |
|
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
20 #from zope.app import zapi |
|
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
21 #from zope.component import queryMultiAdapter |
|
4
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
22 import Shared.DC.ZRDB.DA |
|
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
23 from Products.ZSQLMethods.SQL import SQLConnectionIDs |
|
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
24 |
|
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
25 |
| 16 | 26 def getTextFromNode(node): |
| 14 | 27 """get the cdata content of a XML node""" |
| 16 | 28 if node is None: |
| 14 | 29 return "" |
| 16 | 30 |
| 31 if isinstance(node, list): | |
| 32 nodelist = node | |
| 33 else: | |
| 34 nodelist=node.childNodes | |
| 35 | |
| 14 | 36 rc = "" |
| 37 for node in nodelist: | |
| 38 if node.nodeType == node.TEXT_NODE: | |
| 39 rc = rc + node.data | |
| 40 return rc | |
| 41 | |
| 16 | 42 def sqlName(s,lc=True): |
| 43 """returns restricted ASCII-only version of string""" | |
| 15 | 44 if s is None: |
| 45 return "" | |
| 46 | |
| 47 # all else -> "_" | |
| 16 | 48 s = re.sub(r'[^A-Za-z0-9_]','_',s) |
| 15 | 49 if lc: |
| 50 return s.lower() | |
| 51 | |
| 52 return s | |
| 14 | 53 |
| 31 | 54 gisToSqlTypeMap = { |
| 55 "text": "text", | |
| 32 | 56 "number": "numeric", |
| 31 | 57 "id": "text", |
| 32 | 58 "gis_id": "text", |
| 59 "coord_lat": "numeric", | |
| 60 "coord_lon": "numeric" | |
| 31 | 61 } |
| 62 | |
|
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
63 class RestDbInterface(Folder): |
|
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
64 """Object for RESTful database queries |
| 7 | 65 path schema: /db/{schema}/{table}/ |
|
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
66 omitting table gives a list of schemas |
|
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
67 omitting table and schema gives a list of schemas |
|
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
68 """ |
|
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
69 implements(IPublishTraverse) |
|
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
70 |
|
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
71 meta_type="RESTdb" |
|
4
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
72 manage_options=Folder.manage_options+( |
|
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
73 {'label':'Config','action':'manage_editRestDbInterfaceForm'}, |
|
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
74 ) |
|
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
75 |
|
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
76 # management templates |
|
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
77 manage_editRestDbInterfaceForm=PageTemplateFile('zpt/editRestDbInterface',globals()) |
|
2
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 # data templates |
| 8 | 80 XML_index = PageTemplateFile('zpt/XML_index', globals()) |
| 81 XML_schema = PageTemplateFile('zpt/XML_schema', globals()) | |
| 82 XML_schema_table = PageTemplateFile('zpt/XML_schema_table', globals()) | |
| 83 HTML_index = PageTemplateFile('zpt/HTML_index', globals()) | |
| 84 HTML_schema = PageTemplateFile('zpt/HTML_schema', globals()) | |
| 85 HTML_schema_table = PageTemplateFile('zpt/HTML_schema_table', globals()) | |
| 22 | 86 JSONHTML_index = PageTemplateFile('zpt/JSONHTML_index', globals()) |
| 87 JSONHTML_schema = PageTemplateFile('zpt/JSONHTML_schema', globals()) | |
| 88 JSONHTML_schema_table = PageTemplateFile('zpt/JSONHTML_schema_table', globals()) | |
| 24 | 89 # JSON_* templates are scripts |
| 30 | 90 def JSON_index(self,data): |
| 91 """JSON index function""" | |
| 92 self.REQUEST.RESPONSE.setHeader("Content-Type", "application/json") | |
| 93 json.dump(data, self.REQUEST.RESPONSE) | |
|
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
94 |
| 30 | 95 def JSON_schema(self,data,schema): |
| 96 """JSON index function""" | |
| 97 self.REQUEST.RESPONSE.setHeader("Content-Type", "application/json") | |
| 98 json.dump(data, self.REQUEST.RESPONSE) | |
| 99 | |
| 100 def JSON_schema_table(self,data,tablename): | |
| 101 """JSON index function""" | |
| 102 self.REQUEST.RESPONSE.setHeader("Content-Type", "application/json") | |
| 103 json.dump(data, self.REQUEST.RESPONSE) | |
| 104 | |
|
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
105 |
|
4
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
106 def __init__(self, id, title, connection_id=None): |
|
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
107 """init""" |
|
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
108 self.id = id |
|
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
109 self.title = title |
|
4
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
110 # database connection id |
|
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
111 self.connection_id = connection_id |
|
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
112 # create template folder |
|
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
113 self.manage_addFolder('template') |
|
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
114 |
|
4
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
115 |
| 22 | 116 def getJsonString(self,object): |
| 117 """returns a JSON formatted string from object""" | |
| 118 return json.dumps(object) | |
| 119 | |
| 17 | 120 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
|
121 """returns fresh DB cursor""" |
|
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
122 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
|
123 if conn is None: |
|
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
124 # create a new connection object |
|
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
125 try: |
|
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
126 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
|
127 # 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
|
128 connids = SQLConnectionIDs(self) |
|
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
129 if len(connids) > 0: |
|
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
130 connection_id = connids[0][0] |
|
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
131 self.connection_id = connection_id |
|
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
132 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
|
133 |
|
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
134 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
|
135 da.connect('') |
|
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
136 # 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
|
137 conn = da._v_database_connection |
|
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
138 #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
|
139 self._v_database_connection = conn |
|
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
140 except Exception, e: |
|
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
141 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
|
142 |
| 5 | 143 cursor = conn.getcursor() |
| 17 | 144 if autocommit: |
| 145 # is there a better version to get to the connection? | |
| 146 cursor.connection.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT) | |
| 147 | |
|
4
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
148 return cursor |
|
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
149 |
| 17 | 150 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
|
151 """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
|
152 result format: {"fields":fields, "rows":data}""" |
| 16 | 153 logging.debug("executeSQL query=%s args=%s"%(query,args)) |
| 17 | 154 cur = self.getCursor(autocommit=autocommit) |
|
4
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
155 cur.execute(query, args) |
|
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
156 # description of returned fields |
|
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
157 fields = cur.description |
| 17 | 158 if hasResult: |
| 159 # get all data in an array | |
| 160 data = cur.fetchall() | |
| 161 cur.close() | |
| 162 return {"fields":fields, "rows":data} | |
| 163 else: | |
| 164 cur.close() | |
| 165 return None | |
| 16 | 166 |
| 33 | 167 def checkTableMetaPermission(self,action,schema,table,user=None): |
| 168 """returns if the requested action on the table is allowed""" | |
| 169 logging.debug("checktablemetapermissions action=%s schema=%s table=%s user=%s"%(action,schema,table,user)) | |
| 170 if user is None: | |
| 171 user = self.REQUEST.get('AUTHENTICATED_USER',None) | |
| 172 logging.debug("user=%s"%user) | |
| 173 # TODO: what now? | |
| 174 return True | |
|
4
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
175 |
| 31 | 176 def setTableMetaTypes(self,schema,table,fields): |
| 177 """sets the GIS meta information for table""" | |
| 178 logging.debug("settablemetatypes schema=%s, table=%s, fields=%s"%(schema,table,fields)) | |
| 35 | 179 gisIdField = None |
| 180 latField = None | |
| 181 lonField = None | |
| 182 for f in fields: | |
| 183 t = f['type'] | |
| 184 if t == 'gis_id': | |
| 185 gisIdField = f['name'] | |
| 186 elif t == 'coord_lat': | |
| 187 latField = f['name'] | |
| 188 elif t == 'coord_lon': | |
| 189 lonField = f['name'] | |
| 190 | |
| 34 | 191 res = self.executeSQL("select * from metadata where tablename=%s", table) |
| 192 #if len(res["rows"]) > 0: | |
| 31 | 193 # TODO: what now? |
|
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
194 |
|
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
195 def publishTraverse(self,request,name): |
|
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
196 """change the traversal""" |
|
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
197 # get stored path |
|
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
198 path = request.get('restdb_path', []) |
|
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
199 logging.debug("publishtraverse: name=%s restdb_path=%s"%(name,path)) |
|
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
200 |
| 13 | 201 if name in ("index_html", "PUT"): |
|
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
202 # end of traversal |
| 13 | 203 if request.get("method") == "POST" and request.get("action",None) == "PUT": |
| 204 # fake PUT by POST with action=PUT | |
| 205 name = "PUT" | |
| 206 | |
| 207 return getattr(self, name) | |
|
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
208 #TODO: should we check more? |
|
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
209 else: |
|
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
210 # traverse |
|
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
211 if len(path) == 0: |
|
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
212 # first segment |
| 8 | 213 if name == 'db': |
|
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
214 # virtual path -- continue traversing |
|
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
215 path = [name] |
|
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
216 request['restdb_path'] = path |
|
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
217 else: |
|
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
218 # try real path |
|
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
219 tr = DefaultPublishTraverse(self, request) |
|
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
220 ob = tr.publishTraverse(request, name) |
|
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
221 return ob |
|
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
222 else: |
|
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
223 path.append(name) |
|
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
224 |
|
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
225 # continue traversing |
|
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
226 return self |
|
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
227 |
|
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
228 def index_html(self,REQUEST,RESPONSE): |
|
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
229 """index method""" |
| 8 | 230 # ReST path was stored in request |
|
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
231 path = REQUEST.get('restdb_path',[]) |
| 36 | 232 |
| 8 | 233 # type and format are real parameter |
| 26 | 234 resultFormat = REQUEST.get('format','HTML').upper() |
| 27 | 235 queryType = REQUEST.get('type',None) |
| 36 | 236 |
| 26 | 237 logging.debug("index_html path=%s resultFormat=%s queryType=%s"%(path,resultFormat,queryType)) |
| 8 | 238 |
| 26 | 239 if queryType is not None: |
| 240 # non-empty queryType -- look for template | |
| 241 pt = getattr(self.template, "%s_%s"%(resultFormat,queryType), None) | |
| 8 | 242 if pt is not None: |
| 26 | 243 return pt(format=resultFormat,type=queryType,path=path) |
| 8 | 244 |
|
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
245 if len(path) == 1: |
|
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
246 # list of schemas |
| 26 | 247 return self.showListOfSchemas(resultFormat=resultFormat) |
|
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
248 elif len(path) == 2: |
|
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
249 # list of tables |
| 26 | 250 return self.showListOfTables(resultFormat=resultFormat,schema=path[1]) |
|
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
251 elif len(path) == 3: |
|
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
252 # table |
| 25 | 253 if REQUEST.get("method") == "POST" and REQUEST.get("create_table_file",None) is not None: |
| 24 | 254 # POST to table to check |
| 26 | 255 return self.checkTable(resultFormat=resultFormat,schema=path[1],table=path[2]) |
| 24 | 256 # else show table |
| 26 | 257 return self.showTable(resultFormat=resultFormat,schema=path[1],table=path[2]) |
|
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
258 |
|
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
259 # don't know what to do |
|
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
260 return str(REQUEST) |
|
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
261 |
| 12 | 262 def PUT(self, REQUEST, RESPONSE): |
| 263 """ | |
| 264 Implement WebDAV/HTTP PUT/FTP put method for this object. | |
| 265 """ | |
| 13 | 266 logging.debug("RestDbInterface PUT") |
| 267 #logging.debug("req=%s"%REQUEST) | |
| 12 | 268 #self.dav__init(REQUEST, RESPONSE) |
| 269 #self.dav__simpleifhandler(REQUEST, RESPONSE) | |
| 13 | 270 # ReST path was stored in request |
| 271 path = REQUEST.get('restdb_path',[]) | |
| 272 if len(path) == 3: | |
| 273 schema = path[1] | |
| 274 tablename = path[2] | |
| 275 file = REQUEST.get("create_table_file",None) | |
| 276 if file is None: | |
| 277 RESPONSE.setStatus(400) | |
| 278 return | |
|
4
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
279 |
| 30 | 280 fields = None |
| 281 fieldsStr = REQUEST.get("create_table_fields",None) | |
| 282 logging.debug("put with schema=%s table=%s file=%s fields=%s"%(schema,tablename,file,repr(fieldsStr))) | |
| 283 if fieldsStr is not None: | |
| 284 # unpack fields | |
| 285 fields = [{"name":n, "type": t} for (n,t) in [f.split(":") for f in fieldsStr.split(",")]] | |
| 31 | 286 |
| 28 | 287 ret = self.createTableFromXML(schema, tablename, file, fields) |
| 26 | 288 # return the result as JSON |
| 289 format = REQUEST.get("format","JSON") | |
| 290 if format == "JSON": | |
| 20 | 291 RESPONSE.setHeader("Content-Type", "application/json") |
| 292 json.dump(ret, RESPONSE) | |
| 26 | 293 |
| 294 elif format == "JSONHTML": | |
| 20 | 295 RESPONSE.setHeader("Content-Type", "text/html") |
| 296 RESPONSE.write("<html>\n<body>\n<pre>") | |
| 297 json.dump(ret, RESPONSE) | |
| 298 RESPONSE.write("</pre>\n</body>\n</html>") | |
| 13 | 299 |
| 300 else: | |
| 301 # 400 Bad Request | |
| 302 RESPONSE.setStatus(400) | |
| 303 return | |
| 12 | 304 |
| 26 | 305 def showTable(self,resultFormat='XML',schema='public',table=None,REQUEST=None,RESPONSE=None): |
|
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
306 """returns PageTemplate with tables""" |
|
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
307 logging.debug("showtable") |
| 23 | 308 # should be cross-site accessible |
| 25 | 309 if RESPONSE is None: |
| 310 RESPONSE = self.REQUEST.RESPONSE | |
| 23 | 311 RESPONSE.setHeader('Access-Control-Allow-Origin', '*') |
| 22 | 312 # GIS gets special treatment |
| 26 | 313 if resultFormat=="GIS": |
| 23 | 314 id = REQUEST.get('id',[]) |
| 315 doc = REQUEST.get('doc',None) | |
| 22 | 316 return self.showGoogleMap(schema=path[1],table=path[2],id=id,doc=doc) |
| 317 | |
| 318 # everything else has its own template | |
| 26 | 319 pt = getattr(self.template, '%s_schema_table'%resultFormat, None) |
|
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
320 if pt is None: |
| 26 | 321 return "ERROR!! template %s_schema_table not found"%resultFormat |
|
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
322 |
|
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
323 data = self.getTable(schema,table) |
|
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
324 return pt(data=data,tablename=table) |
|
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
325 |
|
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
326 def getTable(self,schema='public',table=None,username='guest'): |
|
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
327 """return table data""" |
|
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
328 logging.debug("gettable") |
| 16 | 329 data = self.executeSQL('select * from "%s"."%s"'%(schema,table)) |
|
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
330 return data |
|
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
331 |
| 26 | 332 def hasTable(self,schema='public',table=None,username='guest'): |
| 333 """return if table exists""" | |
| 334 logging.debug("hastable") | |
| 335 data = self.executeSQL('select 1 from information_schema.tables where table_schema=%s and table_name=%s',(schema,table)) | |
| 336 ret = bool(data['rows']) | |
| 337 return ret | |
| 338 | |
| 339 def showListOfTables(self,resultFormat='XML',schema='public',REQUEST=None,RESPONSE=None): | |
|
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
340 """returns PageTemplate with list of tables""" |
|
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
341 logging.debug("showlistoftables") |
| 23 | 342 # should be cross-site accessible |
| 25 | 343 if RESPONSE is None: |
| 344 RESPONSE = self.REQUEST.RESPONSE | |
| 23 | 345 RESPONSE.setHeader('Access-Control-Allow-Origin', '*') |
| 24 | 346 |
| 26 | 347 pt = getattr(self.template, '%s_schema'%resultFormat, None) |
|
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
348 if pt is None: |
| 26 | 349 return "ERROR!! template %s_schema not found"%resultFormat |
|
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
350 |
|
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
351 data = self.getListOfTables(schema) |
|
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
352 return pt(data=data,schema=schema) |
|
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
353 |
|
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
354 def getListOfTables(self,schema='public',username='guest'): |
|
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
355 """return list of tables""" |
|
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
356 logging.debug("getlistoftables") |
|
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
357 # get list of fields and types of db table |
| 22 | 358 qstr="""SELECT c.relname AS tablename FROM pg_catalog.pg_class c |
|
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
359 LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace |
|
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
360 WHERE c.relkind IN ('r','') AND n.nspname NOT IN ('pg_catalog', 'pg_toast') |
|
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
361 AND pg_catalog.pg_table_is_visible(c.oid)""" |
|
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
362 #qstr="select attname, format_type(pg_attribute.atttypid, pg_attribute.atttypmod) from pg_attribute, pg_class where attrelid = pg_class.oid and pg_attribute.attnum > 0" |
|
4
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
363 data=self.executeSQL(qstr) |
|
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
364 return data |
|
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
365 |
| 26 | 366 def showListOfSchemas(self,resultFormat='XML',REQUEST=None,RESPONSE=None): |
|
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
367 """returns PageTemplate with list of schemas""" |
|
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
368 logging.debug("showlistofschemas") |
| 23 | 369 # should be cross-site accessible |
| 25 | 370 if RESPONSE is None: |
| 371 RESPONSE = self.REQUEST.RESPONSE | |
| 23 | 372 RESPONSE.setHeader('Access-Control-Allow-Origin', '*') |
| 24 | 373 |
| 26 | 374 pt = getattr(self.template, '%s_index'%resultFormat, None) |
|
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
375 if pt is None: |
| 26 | 376 return "ERROR!! template %s_index not found"%resultFormat |
|
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
377 |
|
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
378 data = self.getListOfSchemas() |
|
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
379 return pt(data=data) |
|
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
380 |
|
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
381 def getListOfSchemas(self,username='guest'): |
|
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
382 """return list of schemas""" |
|
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
383 logging.debug("getlistofschemas") |
|
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
384 # TODO: really look up schemas |
| 9 | 385 data={'fields': (('schemas',),), 'rows': [('public',),]} |
|
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
386 return data |
| 14 | 387 |
| 26 | 388 def checkTable(self,resultFormat,schema,table,REQUEST=None,RESPONSE=None): |
| 389 """check the table. | |
| 390 returns valid data fields and table name.""" | |
| 391 if REQUEST is None: | |
| 392 REQUEST = self.REQUEST | |
| 393 RESPONSE = REQUEST.RESPONSE | |
| 394 | |
| 395 file = REQUEST.get("create_table_file",None) | |
| 396 res = self.checkTableFromXML(schema, table, file) | |
| 397 logging.debug("checkTable result=%s"%repr(res)) | |
| 398 # return the result as JSON | |
| 399 if resultFormat == "JSON": | |
| 400 RESPONSE.setHeader("Content-Type", "application/json") | |
| 401 json.dump(res, RESPONSE) | |
| 402 | |
| 403 elif resultFormat == "JSONHTML": | |
| 404 RESPONSE.setHeader("Content-Type", "text/html") | |
| 405 RESPONSE.write("<html>\n<body>\n<pre>") | |
| 406 json.dump(res, RESPONSE) | |
| 407 RESPONSE.write("</pre>\n</body>\n</html>") | |
| 408 | |
| 409 else: | |
| 410 return "ERROR: invalid resultFormat" | |
| 411 | |
| 412 def checkTableFromXML(self,schema,table,data,REQUEST=None,RESPONSE=None): | |
| 413 """check the table with the given XML data. | |
| 414 returns valid data fields and table name.""" | |
| 415 logging.debug("checkTableFromXML schema=%s table=%s"%(schema,table)) | |
| 416 # clean table name | |
| 417 tablename = sqlName(table) | |
| 418 tableExists = self.hasTable(schema, table) | |
| 419 if data is None: | |
| 420 fieldNames = [] | |
| 421 else: | |
| 422 # get list of field names from upload file | |
| 423 fields = self.importExcelXML(schema,tablename,data,fieldsOnly=True) | |
| 424 | |
| 425 res = {'tablename': tablename, 'table_exists': tableExists} | |
| 426 res['fields'] = fields | |
| 427 return res | |
| 428 | |
| 14 | 429 def createEmptyTable(self,schema,table,fields): |
| 430 """create a table with the given fields | |
| 431 returns list of created fields""" | |
| 15 | 432 logging.debug("createEmptyTable") |
| 14 | 433 sqlFields = [] |
| 434 for f in fields: | |
| 435 if isinstance(f,dict): | |
| 436 # {name: XX, type: YY} | |
| 437 name = sqlName(f['name']) | |
| 438 type = f['type'] | |
| 31 | 439 sqltype = gisToSqlTypeMap[type] |
| 14 | 440 |
| 441 else: | |
| 442 # name only | |
| 443 name = sqlName(f) | |
| 444 type = 'text' | |
| 31 | 445 sqltype = 'text' |
| 14 | 446 |
| 31 | 447 sqlFields.append({'name':name, 'type':type, 'sqltype':sqltype}) |
| 14 | 448 |
| 33 | 449 if self.checkTableMetaPermission("create", schema, table): |
| 450 self.executeSQL('drop table if exists "%s"."%s"'%(schema,table),hasResult=False) | |
| 451 fieldString = ", ".join(['"%s" %s'%(f['name'],f['sqltype']) for f in sqlFields]) | |
| 452 sqlString = 'create table "%s"."%s" (%s)'%(schema,table,fieldString) | |
| 453 logging.debug("createemptytable: SQL=%s"%sqlString) | |
| 454 self.executeSQL(sqlString,hasResult=False) | |
| 455 self.setTableMetaTypes(schema,table,sqlFields) | |
| 456 return sqlFields | |
| 457 else: | |
| 458 logging.warning("create table not allowed!") | |
| 459 # throw exception? | |
| 460 return None | |
| 26 | 461 |
| 28 | 462 def createTableFromXML(self,schema,table,data, fields=None): |
| 13 | 463 """create or replace a table with the given XML data""" |
| 28 | 464 logging.debug("createTableFromXML schema=%s table=%s data=%s fields=%s"%(schema,table,data,fields)) |
| 17 | 465 tablename = sqlName(table) |
| 28 | 466 self.importExcelXML(schema, tablename, data, fields) |
| 17 | 467 return {"tablename": tablename} |
| 14 | 468 |
| 28 | 469 def importExcelXML(self,schema,table,xmldata,fields=None,fieldsOnly=False): |
| 14 | 470 ''' |
| 471 Import XML file in Excel format into the table | |
| 472 @param table: name of the table the xml shall be imported into | |
| 473 ''' | |
| 474 from xml.dom.pulldom import parseString,parse | |
| 475 | |
| 476 namespace = "urn:schemas-microsoft-com:office:spreadsheet" | |
| 477 containerTagName = "Table" | |
| 478 rowTagName = "Row" | |
| 16 | 479 colTagName = "Cell" |
| 14 | 480 dataTagName = "Data" |
| 26 | 481 xmlFields = [] |
| 14 | 482 sqlFields = [] |
| 16 | 483 numFields = 0 |
| 14 | 484 sqlInsert = None |
| 485 | |
| 486 logging.debug("import excel xml") | |
| 487 | |
| 488 ret="" | |
| 16 | 489 if isinstance(xmldata, str): |
| 14 | 490 logging.debug("importXML reading string data") |
| 16 | 491 doc=parseString(xmldata) |
| 14 | 492 else: |
| 493 logging.debug("importXML reading file data") | |
| 16 | 494 doc=parse(xmldata) |
| 14 | 495 |
| 496 cnt = 0 | |
| 497 while True: | |
| 498 node=doc.getEvent() | |
| 499 | |
| 500 if node is None: | |
| 501 break | |
| 502 | |
| 503 else: | |
| 16 | 504 #logging.debug("tag=%s"%node[1].localName) |
| 14 | 505 if node[1].localName is not None: |
| 506 tagName = node[1].localName.lower() | |
| 507 else: | |
| 508 # ignore non-tag nodes | |
| 509 continue | |
| 16 | 510 |
| 14 | 511 if tagName == rowTagName.lower(): |
| 512 # start of row | |
| 513 doc.expandNode(node[1]) | |
| 514 cnt += 1 | |
| 515 if cnt == 1: | |
| 516 # first row -- field names | |
| 517 names=node[1].getElementsByTagNameNS(namespace, dataTagName) | |
| 518 for name in names: | |
| 519 fn = getTextFromNode(name) | |
| 26 | 520 xmlFields.append({'name':sqlName(fn),'type':'text'}) |
| 14 | 521 |
| 26 | 522 if fieldsOnly: |
| 14 | 523 # return just field names |
| 26 | 524 return xmlFields |
| 14 | 525 |
| 526 # create table | |
| 28 | 527 if fields is None: |
| 31 | 528 fields = xmlFields |
| 529 | |
| 530 sqlFields = self.createEmptyTable(schema, table, fields) | |
| 16 | 531 numFields = len(sqlFields) |
| 15 | 532 fieldString = ", ".join(['"%s"'%f['name'] for f in sqlFields]) |
| 14 | 533 valString = ", ".join(["%s" for f in sqlFields]) |
| 16 | 534 sqlInsert = 'insert into "%s"."%s" (%s) values (%s)'%(schema,table,fieldString,valString) |
| 26 | 535 #logging.debug("importexcelsql: sqlInsert=%s"%sqlInsert) |
| 14 | 536 |
| 537 else: | |
| 538 # following rows are data | |
| 16 | 539 colNodes=node[1].getElementsByTagNameNS(namespace, colTagName) |
| 14 | 540 data = [] |
| 16 | 541 hasData = False |
| 542 for colNode in colNodes: | |
| 543 dataNodes=colNode.getElementsByTagNameNS(namespace, dataTagName) | |
| 544 if len(dataNodes) > 0: | |
| 545 val = getTextFromNode(dataNodes[0]) | |
| 546 hasData = True | |
| 547 else: | |
| 548 val = None | |
| 549 | |
| 14 | 550 data.append(val) |
| 551 | |
| 16 | 552 if not hasData: |
| 553 # ignore empty rows | |
| 554 continue | |
| 555 | |
| 556 # fix number of data fields | |
| 557 if len(data) > numFields: | |
| 558 del data[numFields:] | |
| 559 elif len(data) < numFields: | |
| 560 missFields = numFields - len(data) | |
| 561 data.extend(missFields * [None,]) | |
| 562 | |
| 563 logging.debug("importexcel sqlinsert=%s data=%s"%(sqlInsert,data)) | |
| 17 | 564 self.executeSQL(sqlInsert, data, hasResult=False) |
| 14 | 565 |
| 16 | 566 return cnt |
|
21
a67b7c1f7ec5
Merge with Falks GIS stuff 78e70dfa7ad6b27d10d490f9ae8820306e4fe5d4
casties
diff
changeset
|
567 |
|
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
568 |
| 36 | 569 # Methods for GoogleMaps creation |
| 570 def showGoogleMap(self,schema='chgis',table='mpdl',id=[],doc=None): | |
| 571 logging.debug("showGoogleMap") | |
| 572 data = self.getDataForGoogleMap(schema,table,id,doc) | |
| 573 kmlFileName=self.getKMLname(data=data,table=table) | |
| 574 initializeStringForGoogleMaps="""onload=\"initialize(\'http://chinagis.mpiwg-berlin.mpg.de/chinagis/REST/daten/"""+kmlFileName+"""\')\""""#+str(data) | |
| 575 initializeStringForGoogleMaps=initializeStringForGoogleMaps.replace("None","0") | |
| 576 googleMap_page=self.htmlHead()+str(self.getGoogleMapString(kml=initializeStringForGoogleMaps)) | |
| 577 return googleMap_page | |
| 578 | |
| 579 def getKmlUrl(self,schema='chgis',table='mpdl',id=[],doc=None): | |
| 580 logging.debug("getKmlUrl") | |
| 581 data = self.getDataForGoogleMap(schema,table,id,doc) | |
| 582 kml=self.getKMLname(data=data,table=table) | |
| 583 return """http://chinagis.mpiwg-berlin.mpg.de/chinagis/REST/daten/"""+kml | |
| 584 | |
| 585 def getDataForGoogleMap(self,schema='chgis',table='mpdl',id=[],doc=None): | |
| 586 logging.debug("getDataForGoogleMap") | |
| 587 qstr="SELECT * FROM "+schema+"."+table | |
| 588 try: | |
| 589 if id!=[]: | |
| 590 qstr=qstr+" WHERE " | |
| 591 for id_item in id.split(","): | |
| 592 if table=='mpdl': | |
| 593 qstr=qstr+" mpdl_xmlsource_id = '"+id_item+ "' OR" | |
| 594 else: | |
| 595 qstr=qstr+" cast(id as text) LIKE '"+id_item+ "' OR" | |
| 596 qstr=str(qstr).rsplit(" ",1)[0] #to remove last " and " | |
| 597 data=self.ZSQLSimpleSearch(qstr) | |
| 598 return data | |
| 599 except: | |
| 600 return qstr | |
| 601 | |
| 602 def getKMLname(self,data=[],table=""): | |
| 603 logging.debug("getKMLname") | |
| 604 #session=context.REQUEST.SESSION | |
| 605 kml4Marker="<kml xmlns=\'http://www.opengis.net/kml/2.2\'><Document><Style id=\'marker_icon\'><IconStyle><scale>15</scale><Icon><href>http://chinagis.mpiwg-berlin.mpg.de/chinagis/images/dot_red.png</href></Icon></IconStyle></Style>\n" | |
| 606 initializeStringForGoogleMaps="" | |
| 607 #doLine=container.getVar('doLine') | |
| 608 # Mapping a set of points from table-based SQL-query: | |
| 609 if data!=None: | |
| 610 try: | |
| 611 SQL="""SELECT \"attribute with gis_id\" FROM public.metadata WHERE tablename LIKE '"""+table+"""'""" | |
| 612 gisIDattribute=self.ZSQLSimpleSearch(SQL) | |
| 613 except: | |
| 614 return "table not registered within metadata" | |
| 615 for dataset in data: | |
| 616 try: | |
| 617 xCoord=getattr(dataset,'longitude') | |
| 618 yCoord=getattr(dataset,'latitude') | |
| 619 except: | |
| 620 try: | |
| 621 xCoord=getattr(dataset,'x_coord') | |
| 622 yCoord=getattr(dataset,'y_coord') | |
| 623 except: | |
| 624 # try: | |
| 625 gisID=getattr(dataset,getattr(gisIDattribute[0],'attribute with gis_id')) | |
| 626 coords=self.getPoint4GISid(gisID) | |
| 627 if coords!=None: | |
| 628 xCoord=coords[0] | |
| 629 yCoord=coords[1] | |
| 630 # except: | |
| 631 # return "no coordinates found" | |
| 632 if float(xCoord)!=0: | |
| 633 if float(yCoord)!=0: | |
| 634 kml4Marker=kml4Marker+"<Placemark>" | |
| 635 kml4Marker=kml4Marker+"<description> <![CDATA[<b>" | |
| 636 for values in dataset: | |
| 637 if values != (None, None): | |
| 638 if str(values).find('name')>-1: | |
| 639 kml4Marker=kml4Marker+"<name>"+str(values[1])+"</name>\n" | |
| 640 continue | |
| 641 elif str(values).find('place')>-1: | |
| 642 kml4Marker=kml4Marker+"<name>"+str(values[1])+"</name>\n" | |
| 643 continue | |
| 644 | |
| 645 kml4Marker=kml4Marker+str(values)+": " | |
| 646 attribute_string=str(values).replace("'","__Apostroph__") | |
| 647 attribute_string=str(attribute_string).replace('"','__DoubleApostroph__') | |
| 648 attribute_string=str(attribute_string).replace(';','__$$__') | |
| 649 attribute_string=str(attribute_string).replace('&','&') | |
| 650 if str(attribute_string).find('http')>-1: | |
| 651 attribute_string='<A HREF=' + str(attribute_string) + ' target=_blank>' + str(attribute_string) + '</A>' | |
| 652 kml4Marker=kml4Marker+attribute_string+"</a><br>\n" | |
| 653 | |
| 654 kml4Marker=kml4Marker+"]]></description>\n" | |
| 655 kml4Marker=kml4Marker+"<styleURL>#marker_icon</styleURL>\n" | |
| 656 kml4Marker=kml4Marker+"<Point>" | |
| 657 | |
| 658 kml4Marker=kml4Marker+"<coordinates>"+str(xCoord)+","+str(yCoord)+",0</coordinates>\n" | |
| 659 kml4Marker=kml4Marker+"</Point>\n" | |
| 660 kml4Marker=kml4Marker+"</Placemark>\n" | |
| 661 | |
| 662 kml4Marker=kml4Marker+"</Document>\n</kml>" | |
| 663 kmlFileName="marker"+str(time.time())+".kml" | |
| 664 | |
| 665 # kml4Marker=str(kml4Marker).replace('&','$$') | |
| 666 # kml4Marker=str(kml4Marker).replace(';','__$$__') | |
| 667 # kml4Marker=str(kml4Marker).replace('#','__SHARP__') | |
| 668 isLoadReady='false' | |
| 669 while isLoadReady=='false': | |
| 670 isLoadReady=self.RESTwrite2File(self.daten,kmlFileName,kml4Marker) | |
| 671 | |
| 672 return kmlFileName | |
| 673 | |
| 674 def getGoogleMapString(self,kml): | |
| 675 logging.debug("getGoogleMapString") | |
| 676 printed= '<body %s> '%kml +"""\n <div id="map_canvas" style="width: 98%; height: 95%"> </div> \n </body>" \n </html>""" | |
| 677 return printed | |
| 678 | |
| 679 def getPoint4GISid(self,gis_id): | |
| 680 j=0 | |
| 681 coords=(0,0) | |
| 682 if gis_id != None: | |
| 683 while (True): | |
| 684 j=j+1 | |
| 685 if (j>100): # FJK: just to prevent endless loops | |
| 686 break | |
| 687 if (gis_id.isdigit()): # FJK: regular exit from while-loop | |
| 688 break | |
| 689 else: | |
| 690 gis_id=gis_id.strip('abcdefghijklmnopqrstuvwxyz_') # FJK: to strip all letters | |
| 691 gis_id=gis_id.strip() # FJK: to strip all whitespaces | |
| 692 resultpoint = [0,0] | |
| 693 results = None | |
| 694 try: | |
| 695 if int(gis_id)>0: | |
| 696 SQL="SELECT x_coord,y_coord FROM chgis.chgis_coords WHERE gis_id LIKE cast("+ str(gis_id) +" as text);" | |
| 697 results=self.ZSQLSimpleSearch(SQL) | |
| 698 #print results | |
| 699 if results != None: | |
| 700 for result in results: | |
| 701 resultpoint=[getattr(result,str('x_coord')),getattr(result,str('y_coord'))] | |
| 702 if resultpoint !=[0,0]: | |
| 703 return resultpoint | |
| 704 else: | |
| 705 coords=self.getCoordsFromREST_gisID(joinid) | |
| 706 SQL="INSERT INTO chgis.chgis_coords (gis_id,x_coord,y_coord) VALUES (" +gis_id+ "," +coords[0][1]+ "," +coords[0][0]+ "); ANALYZE chgis.chgis_coords;" | |
| 707 returnstring=self.ZSQLSimpleSearch(SQL) | |
| 708 return coords[0] | |
| 709 except: | |
| 710 return "gis_id not to interpretable:"+str(gis_id) | |
| 711 else: | |
| 712 return coords[0] | |
| 713 | |
| 714 def getCoordsFromREST_gisID(self,gis_id): | |
| 715 coordlist=[] | |
| 716 i=0 | |
| 717 while (i<5 and coordlist==[]): | |
| 718 | |
| 719 urlresponse=container.urlFunctions.zUrlopenParseString(container.urlFunctions.zUrlopenRead("http://chgis.hmdc.harvard.edu/xml/id/"+gis_id)) | |
| 720 baseDocElement=container.urlFunctions.zUrlopenDocumentElement(urlresponse) | |
| 721 childnodes=container.urlFunctions.zUrlopenChildNodes(baseDocElement) | |
| 722 itemnodes=container.urlFunctions.zUrlopenGetElementsByTagName(baseDocElement,'item') | |
| 723 | |
| 724 for i in range(0,container.urlFunctions.zUrlopenLength(itemnodes)): | |
| 725 itemnode=container.urlFunctions.zUrlopenGetItem(itemnodes,i) | |
| 726 itemspatialnodes=container.urlFunctions.zUrlopenGetElementsByTagName(itemnode,'spatial') | |
| 727 for j in range(0,container.urlFunctions.zUrlopenLength(itemspatialnodes)): | |
| 728 coord=[] | |
| 729 itemspatialnode= container.urlFunctions.zUrlopenGetItem(itemspatialnodes,j) | |
| 730 itemspatiallatnodes=container.urlFunctions.zUrlopenGetElementsByTagName(itemspatialnode,'degrees_latitude') | |
| 731 for k in range(0,container.urlFunctions.zUrlopenLength(itemspatiallatnodes)): | |
| 732 itemspatiallatnode= container.urlFunctions.zUrlopenGetItem(itemspatiallatnodes,k) | |
| 733 coord.append(container.urlFunctions.zUrlopenGetTextData(itemspatiallatnode)) | |
| 734 itemspatiallngnodes=container.urlFunctions.zUrlopenGetElementsByTagName(itemspatialnode,'degrees_longitude') | |
| 735 for k in range(0,container.urlFunctions.zUrlopenLength(itemspatiallngnodes)): | |
| 736 itemspatiallngnode= container.urlFunctions.zUrlopenGetItem(itemspatiallngnodes,k) | |
| 737 coord.append(container.urlFunctions.zUrlopenGetTextData(itemspatiallngnode)) | |
| 738 coordlist.append(coord) | |
| 739 gis_id= "_"+gis_id | |
| 740 return coordlist | |
| 741 | |
| 742 # End for GoogleMaps creation | |
| 743 | |
| 744 def RESTwrite2File(self,datadir, name,text): | |
| 745 # try: | |
| 746 fileid=name | |
| 747 if fileid in datadir.objectIds(): | |
| 748 datadir.manage_delObjects(fileid) | |
| 749 newfile=open(name,'w') | |
| 750 newfile.write(text) | |
| 751 newfile.close() | |
| 752 file4Read=open(name,'r') | |
| 753 fileInZope=datadir.manage_addFile(id=fileid,file=file4Read) | |
| 754 return "Write successful" | |
| 755 # except: | |
| 756 # return "Could not write" | |
| 757 | |
|
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
758 |
|
4
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
759 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
|
760 REQUEST=None): |
|
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
761 """Change the object""" |
|
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
762 if title is not None: |
|
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
763 self.title = title |
|
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
764 |
|
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
765 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
|
766 self.connection_id = connection_id |
|
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
767 |
|
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
768 #checkPermission=getSecurityManager().checkPermission |
|
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
769 REQUEST.RESPONSE.redirect('manage_main') |
|
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
770 |
|
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
771 |
|
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
772 manage_addRestDbInterfaceForm=PageTemplateFile('zpt/addRestDbInterface',globals()) |
|
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
773 |
|
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
774 def manage_addRestDbInterface(self, id, title='', label='', description='', |
|
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
775 createPublic=0, |
|
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
776 createUserF=0, |
|
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
777 REQUEST=None): |
|
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
778 """Add a new object with id *id*.""" |
|
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
779 |
|
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
780 ob=RestDbInterface(str(id),title) |
|
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
781 self._setObject(id, ob) |
|
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
782 |
|
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
783 #checkPermission=getSecurityManager().checkPermission |
|
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
784 REQUEST.RESPONSE.redirect('manage_main') |
|
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
785 |
| 5 | 786 |