Mercurial > hg > ChinaGisRestApi
annotate RestDbInterface.py @ 73:695b6612d4c6
getKmlUrl eingebaut
author | fknauft |
---|---|
date | Wed, 01 Sep 2010 17:42:13 +0200 |
parents | a67b7c1f7ec5 |
children | 6e19a9af00e6 |
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 |
19 | 11 import time |
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
12 |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
13 from zope.interface import implements |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
14 from zope.publisher.interfaces import IPublishTraverse |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
15 from ZPublisher.BaseRequest import DefaultPublishTraverse |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
16 #from zope.publisher.interfaces import NotFound |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
17 #from zope.app import zapi |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
18 #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
|
19 import Shared.DC.ZRDB.DA |
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
20 from Products.ZSQLMethods.SQL import SQLConnectionIDs |
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
21 |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
22 |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
23 class RestDbInterface(Folder): |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
24 """Object for RESTful database queries |
7 | 25 path schema: /db/{schema}/{table}/ |
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
26 omitting table gives a list of schemas |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
27 omitting table and schema gives a list of schemas |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
28 """ |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
29 implements(IPublishTraverse) |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
30 |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
31 meta_type="RESTdb" |
4
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
32 manage_options=Folder.manage_options+( |
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
33 {'label':'Config','action':'manage_editRestDbInterfaceForm'}, |
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
34 ) |
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
35 |
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
36 # management templates |
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
37 manage_editRestDbInterfaceForm=PageTemplateFile('zpt/editRestDbInterface',globals()) |
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
38 |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
39 # data templates |
8 | 40 XML_index = PageTemplateFile('zpt/XML_index', globals()) |
41 XML_schema = PageTemplateFile('zpt/XML_schema', globals()) | |
42 XML_schema_table = PageTemplateFile('zpt/XML_schema_table', globals()) | |
43 HTML_index = PageTemplateFile('zpt/HTML_index', globals()) | |
44 HTML_schema = PageTemplateFile('zpt/HTML_schema', globals()) | |
45 HTML_schema_table = PageTemplateFile('zpt/HTML_schema_table', globals()) | |
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
46 |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
47 |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
48 |
4
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
49 def __init__(self, id, title, connection_id=None): |
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
50 """init""" |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
51 self.id = id |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
52 self.title = title |
4
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
53 # database connection id |
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
54 self.connection_id = connection_id |
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
55 # create template folder |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
56 self.manage_addFolder('template') |
73 | 57 # create data folder |
58 #self.manage_addFolder('daten') | |
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
59 |
4
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
60 |
73 | 61 def getCursor(self): |
4
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
62 """returns fresh DB cursor""" |
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
63 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
|
64 if conn is None: |
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
65 # create a new connection object |
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
66 try: |
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
67 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
|
68 # 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
|
69 connids = SQLConnectionIDs(self) |
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
70 if len(connids) > 0: |
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
71 connection_id = connids[0][0] |
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
72 self.connection_id = connection_id |
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
73 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
|
74 |
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
75 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
|
76 da.connect('') |
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
77 # 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
|
78 conn = da._v_database_connection |
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
79 #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
|
80 self._v_database_connection = conn |
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
81 except Exception, e: |
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
82 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
|
83 |
5 | 84 cursor = conn.getcursor() |
4
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
85 return cursor |
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
86 |
73 | 87 def executeSQL(self, query, *args): |
4
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
88 """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
|
89 result format: {"fields":fields, "rows":data}""" |
73 | 90 cur = self.getCursor() |
4
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
91 cur.execute(query, args) |
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
92 # description of returned fields |
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
93 fields = cur.description |
73 | 94 # get all data in an array |
95 data = cur.fetchall() | |
96 cur.close() | |
97 return {"fields":fields, "rows":data} | |
16 | 98 |
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
99 |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
100 def publishTraverse(self,request,name): |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
101 """change the traversal""" |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
102 # get stored path |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
103 path = request.get('restdb_path', []) |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
104 logging.debug("publishtraverse: name=%s restdb_path=%s"%(name,path)) |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
105 |
73 | 106 if name == "index_html": |
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
107 # end of traversal |
73 | 108 return self.index_html |
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
109 #TODO: should we check more? |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
110 else: |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
111 # traverse |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
112 if len(path) == 0: |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
113 # first segment |
8 | 114 if name == 'db': |
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
115 # virtual path -- continue traversing |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
116 path = [name] |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
117 request['restdb_path'] = path |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
118 else: |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
119 # try real path |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
120 tr = DefaultPublishTraverse(self, request) |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
121 ob = tr.publishTraverse(request, name) |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
122 return ob |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
123 else: |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
124 path.append(name) |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
125 |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
126 # continue traversing |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
127 return self |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
128 |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
129 def index_html(self,REQUEST,RESPONSE): |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
130 """index method""" |
8 | 131 # ReST path was stored in request |
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
132 path = REQUEST.get('restdb_path',[]) |
19 | 133 |
8 | 134 # type and format are real parameter |
135 format = REQUEST.get('format','HTML').upper() | |
136 type = REQUEST.get('type',None) | |
19 | 137 |
138 # id and doc are used for GoogleMaps content | |
139 id = REQUEST.get('id',[]) | |
140 doc = REQUEST.get('doc',None) | |
141 | |
8 | 142 logging.debug("index_html path=%s format=%s type=%s"%(path,format,type)) |
73 | 143 RESPONSE.setHeader('Access-Control-Allow-Origin', '*') |
8 | 144 |
145 if type is not None: | |
146 # non-empty type -- look for template | |
147 pt = getattr(self.template, "%s_%s"%(format,type), None) | |
148 if pt is not None: | |
149 return pt(format=format,type=type,path=path) | |
150 | |
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
151 if len(path) == 1: |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
152 # list of schemas |
8 | 153 return self.showListOfSchemas(format=format) |
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
154 elif len(path) == 2: |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
155 # list of tables |
8 | 156 return self.showListOfTables(format=format,schema=path[1]) |
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
157 elif len(path) == 3: |
19 | 158 # GIS |
159 if format=="GIS": | |
160 return self.showGoogleMap(schema=path[1],table=path[2],id=id,doc=doc) | |
73 | 161 if format=="KML_URL": |
162 return self.getKmlUrl(schema=path[1],table=path[2],id=id,doc=doc) | |
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
163 # table |
8 | 164 return self.showTable(format=format,schema=path[1],table=path[2]) |
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
165 |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
166 # don't know what to do |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
167 return str(REQUEST) |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
168 |
4
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
169 |
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
170 def showTable(self,format='XML',schema='public',table=None): |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
171 """returns PageTemplate with tables""" |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
172 logging.debug("showtable") |
9 | 173 pt = getattr(self.template, '%s_schema_table'%format, None) |
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
174 if pt is None: |
9 | 175 return "ERROR!! template %s_schema_table not found"%format |
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
176 |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
177 data = self.getTable(schema,table) |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
178 return pt(data=data,tablename=table) |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
179 |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
180 |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
181 def getTable(self,schema='public',table=None,username='guest'): |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
182 """return table data""" |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
183 logging.debug("gettable") |
73 | 184 data = self.executeSQL("select * from %s"%table) |
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
185 return data |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
186 |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
187 def showListOfTables(self,format='XML',schema='public'): |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
188 """returns PageTemplate with list of tables""" |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
189 logging.debug("showlistoftables") |
9 | 190 pt = getattr(self.template, '%s_schema'%format, None) |
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
191 if pt is None: |
9 | 192 return "ERROR!! template %s_schema not found"%format |
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
193 |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
194 data = self.getListOfTables(schema) |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
195 return pt(data=data,schema=schema) |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
196 |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
197 def getListOfTables(self,schema='public',username='guest'): |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
198 """return list of tables""" |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
199 logging.debug("getlistoftables") |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
200 # get list of fields and types of db table |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
201 qstr="""select c.relname FROM pg_catalog.pg_class c |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
202 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
|
203 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
|
204 AND pg_catalog.pg_table_is_visible(c.oid)""" |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
205 #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
|
206 data=self.executeSQL(qstr) |
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
207 return data |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
208 |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
209 def showListOfSchemas(self,format='XML'): |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
210 """returns PageTemplate with list of schemas""" |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
211 logging.debug("showlistofschemas") |
9 | 212 pt = getattr(self.template, '%s_index'%format, None) |
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
213 if pt is None: |
9 | 214 return "ERROR!! template %s_index not found"%format |
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
215 |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
216 data = self.getListOfSchemas() |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
217 return pt(data=data) |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
218 |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
219 def getListOfSchemas(self,username='guest'): |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
220 """return list of schemas""" |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
221 logging.debug("getlistofschemas") |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
222 # TODO: really look up schemas |
9 | 223 data={'fields': (('schemas',),), 'rows': [('public',),]} |
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
224 return data |
19 | 225 |
226 # Methods for GoogleMaps creation | |
227 def showGoogleMap(self,schema='chgis',table='mpdl',id=[],doc=None): | |
228 logging.debug("showGoogleMap") | |
229 data = self.getDataForGoogleMap(schema,table,id,doc) | |
73 | 230 kmlFileName=self.getKMLname(data=data,table=table) |
231 initializeStringForGoogleMaps="""onload=\"initialize(\'http://chinagis.mpiwg-berlin.mpg.de/chinagis/REST/daten/"""+kmlFileName+"""\')\""""#+str(data) | |
232 initializeStringForGoogleMaps=initializeStringForGoogleMaps.replace("None","0") | |
233 googleMap_page=self.htmlHead()+str(self.getGoogleMapString(kml=initializeStringForGoogleMaps)) | |
234 return googleMap_page | |
235 | |
236 def getKmlUrl(self,schema='chgis',table='mpdl',id=[],doc=None): | |
237 logging.debug("getKmlUrl") | |
238 data = self.getDataForGoogleMap(schema,table,id,doc) | |
19 | 239 kml=self.getKMLname(data=data,table=table) |
73 | 240 return """http://chinagis.mpiwg-berlin.mpg.de/chinagis/REST/daten/"""+kml |
19 | 241 |
242 def getDataForGoogleMap(self,schema='chgis',table='mpdl',id=[],doc=None): | |
243 logging.debug("getDataForGoogleMap") | |
244 qstr="SELECT * FROM "+schema+"."+table | |
245 try: | |
246 if id!=[]: | |
247 qstr=qstr+" WHERE " | |
248 for id_item in id.split(","): | |
249 if table=='mpdl': | |
250 qstr=qstr+" mpdl_xmlsource_id = '"+id_item+ "' OR" | |
251 else: | |
252 qstr=qstr+" cast(id as text) LIKE '"+id_item+ "' OR" | |
253 qstr=str(qstr).rsplit(" ",1)[0] #to remove last " and " | |
254 data=self.ZSQLSimpleSearch(qstr) | |
255 return data | |
256 except: | |
257 return qstr | |
258 | |
259 def getKMLname(self,data=[],table=""): | |
260 logging.debug("getKMLname") | |
261 #session=context.REQUEST.SESSION | |
262 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" | |
263 initializeStringForGoogleMaps="" | |
264 #doLine=container.getVar('doLine') | |
265 # Mapping a set of points from table-based SQL-query: | |
266 if data!=None: | |
267 try: | |
268 SQL="""SELECT \"attribute with gis_id\" FROM public.metadata WHERE tablename LIKE '"""+table+"""'""" | |
269 gisIDattribute=self.ZSQLSimpleSearch(SQL) | |
270 except: | |
271 return "table not registered within metadata" | |
272 for dataset in data: | |
273 try: | |
274 xCoord=getattr(dataset,'longitude') | |
275 yCoord=getattr(dataset,'latitude') | |
276 except: | |
277 try: | |
278 xCoord=getattr(dataset,'x_coord') | |
279 yCoord=getattr(dataset,'y_coord') | |
280 except: | |
281 # try: | |
282 gisID=getattr(dataset,getattr(gisIDattribute[0],'attribute with gis_id')) | |
283 coords=self.getPoint4GISid(gisID) | |
284 if coords!=None: | |
285 xCoord=coords[0] | |
286 yCoord=coords[1] | |
287 # except: | |
288 # return "no coordinates found" | |
289 if float(xCoord)!=0: | |
290 if float(yCoord)!=0: | |
291 kml4Marker=kml4Marker+"<Placemark>" | |
292 kml4Marker=kml4Marker+"<description> <![CDATA[<b>" | |
293 for values in dataset: | |
294 if values != (None, None): | |
295 if str(values).find('name')>-1: | |
296 kml4Marker=kml4Marker+"<name>"+str(values[1])+"</name>\n" | |
297 continue | |
298 elif str(values).find('place')>-1: | |
299 kml4Marker=kml4Marker+"<name>"+str(values[1])+"</name>\n" | |
300 continue | |
14 | 301 |
19 | 302 kml4Marker=kml4Marker+str(values)+": " |
303 attribute_string=str(values).replace("'","__Apostroph__") | |
304 attribute_string=str(attribute_string).replace('"','__DoubleApostroph__') | |
305 attribute_string=str(attribute_string).replace(';','__$$__') | |
306 attribute_string=str(attribute_string).replace('&','&') | |
307 if str(attribute_string).find('http')>-1: | |
308 attribute_string='<A HREF=' + str(attribute_string) + ' target=_blank>' + str(attribute_string) + '</A>' | |
309 kml4Marker=kml4Marker+attribute_string+"</a><br>\n" | |
310 | |
311 kml4Marker=kml4Marker+"]]></description>\n" | |
312 kml4Marker=kml4Marker+"<styleURL>#marker_icon</styleURL>\n" | |
313 kml4Marker=kml4Marker+"<Point>" | |
314 | |
315 kml4Marker=kml4Marker+"<coordinates>"+str(xCoord)+","+str(yCoord)+",0</coordinates>\n" | |
316 kml4Marker=kml4Marker+"</Point>\n" | |
317 kml4Marker=kml4Marker+"</Placemark>\n" | |
14 | 318 |
19 | 319 kml4Marker=kml4Marker+"</Document>\n</kml>" |
320 kmlFileName="marker"+str(time.time())+".kml" | |
14 | 321 |
19 | 322 # kml4Marker=str(kml4Marker).replace('&','$$') |
323 # kml4Marker=str(kml4Marker).replace(';','__$$__') | |
324 # kml4Marker=str(kml4Marker).replace('#','__SHARP__') | |
325 isLoadReady='false' | |
326 while isLoadReady=='false': | |
327 isLoadReady=self.RESTwrite2File(self.daten,kmlFileName,kml4Marker) | |
328 | |
73 | 329 return kmlFileName |
14 | 330 |
19 | 331 def getGoogleMapString(self,kml): |
332 logging.debug("getGoogleMapString") | |
333 printed= '<body %s> '%kml +"""\n <div id="map_canvas" style="width: 98%; height: 95%"> </div> \n </body>" \n </html>""" | |
334 return printed | |
335 | |
336 def getPoint4GISid(self,gis_id): | |
337 j=0 | |
73 | 338 coords=(0,0) |
339 if gis_id != None: | |
340 while (True): | |
341 j=j+1 | |
342 if (j>100): # FJK: just to prevent endless loops | |
343 break | |
344 if (gis_id.isdigit()): # FJK: regular exit from while-loop | |
345 break | |
346 else: | |
347 gis_id=gis_id.strip('abcdefghijklmnopqrstuvwxyz_') # FJK: to strip all letters | |
348 gis_id=gis_id.strip() # FJK: to strip all whitespaces | |
349 resultpoint = [0,0] | |
350 results = None | |
351 try: | |
19 | 352 if int(gis_id)>0: |
353 SQL="SELECT x_coord,y_coord FROM chgis.chgis_coords WHERE gis_id LIKE cast("+ str(gis_id) +" as text);" | |
354 results=self.ZSQLSimpleSearch(SQL) | |
355 #print results | |
356 if results != None: | |
357 for result in results: | |
358 resultpoint=[getattr(result,str('x_coord')),getattr(result,str('y_coord'))] | |
359 if resultpoint !=[0,0]: | |
360 return resultpoint | |
14 | 361 else: |
19 | 362 coords=self.getCoordsFromREST_gisID(joinid) |
73 | 363 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;" |
19 | 364 returnstring=self.ZSQLSimpleSearch(SQL) |
365 return coords[0] | |
73 | 366 except: |
367 return "gis_id not to interpretable:"+str(gis_id) | |
368 else: | |
369 return coords[0] | |
16 | 370 |
19 | 371 def getCoordsFromREST_gisID(self,gis_id): |
372 coordlist=[] | |
373 i=0 | |
374 while (i<5 and coordlist==[]): | |
13 | 375 |
19 | 376 urlresponse=container.urlFunctions.zUrlopenParseString(container.urlFunctions.zUrlopenRead("http://chgis.hmdc.harvard.edu/xml/id/"+gis_id)) |
377 baseDocElement=container.urlFunctions.zUrlopenDocumentElement(urlresponse) | |
378 childnodes=container.urlFunctions.zUrlopenChildNodes(baseDocElement) | |
379 itemnodes=container.urlFunctions.zUrlopenGetElementsByTagName(baseDocElement,'item') | |
380 | |
381 for i in range(0,container.urlFunctions.zUrlopenLength(itemnodes)): | |
382 itemnode=container.urlFunctions.zUrlopenGetItem(itemnodes,i) | |
383 itemspatialnodes=container.urlFunctions.zUrlopenGetElementsByTagName(itemnode,'spatial') | |
384 for j in range(0,container.urlFunctions.zUrlopenLength(itemspatialnodes)): | |
385 coord=[] | |
386 itemspatialnode= container.urlFunctions.zUrlopenGetItem(itemspatialnodes,j) | |
387 itemspatiallatnodes=container.urlFunctions.zUrlopenGetElementsByTagName(itemspatialnode,'degrees_latitude') | |
388 for k in range(0,container.urlFunctions.zUrlopenLength(itemspatiallatnodes)): | |
389 itemspatiallatnode= container.urlFunctions.zUrlopenGetItem(itemspatiallatnodes,k) | |
390 coord.append(container.urlFunctions.zUrlopenGetTextData(itemspatiallatnode)) | |
391 itemspatiallngnodes=container.urlFunctions.zUrlopenGetElementsByTagName(itemspatialnode,'degrees_longitude') | |
392 for k in range(0,container.urlFunctions.zUrlopenLength(itemspatiallngnodes)): | |
393 itemspatiallngnode= container.urlFunctions.zUrlopenGetItem(itemspatiallngnodes,k) | |
394 coord.append(container.urlFunctions.zUrlopenGetTextData(itemspatiallngnode)) | |
395 coordlist.append(coord) | |
396 gis_id= "_"+gis_id | |
397 return coordlist | |
398 | |
399 # End for GoogleMaps creation | |
400 | |
401 def RESTwrite2File(self,datadir, name,text): | |
402 # try: | |
403 fileid=name | |
404 if fileid in datadir.objectIds(): | |
405 datadir.manage_delObjects(fileid) | |
406 newfile=open(name,'w') | |
407 newfile.write(text) | |
408 newfile.close() | |
409 file4Read=open(name,'r') | |
410 fileInZope=datadir.manage_addFile(id=fileid,file=file4Read) | |
411 return "Write successful" | |
412 # except: | |
413 # return "Could not write" | |
73 | 414 |
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
415 |
4
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
416 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
|
417 REQUEST=None): |
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
418 """Change the object""" |
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
419 if title is not None: |
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
420 self.title = title |
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
421 |
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
422 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
|
423 self.connection_id = connection_id |
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
424 |
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
425 #checkPermission=getSecurityManager().checkPermission |
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
426 REQUEST.RESPONSE.redirect('manage_main') |
e3ee1f358fe6
new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents:
2
diff
changeset
|
427 |
2
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
428 |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
429 manage_addRestDbInterfaceForm=PageTemplateFile('zpt/addRestDbInterface',globals()) |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
430 |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
431 def manage_addRestDbInterface(self, id, title='', label='', description='', |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
432 createPublic=0, |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
433 createUserF=0, |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
434 REQUEST=None): |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
435 """Add a new object with id *id*.""" |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
436 |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
437 ob=RestDbInterface(str(id),title) |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
438 self._setObject(id, ob) |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
439 |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
440 #checkPermission=getSecurityManager().checkPermission |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
441 REQUEST.RESPONSE.redirect('manage_main') |
61a3764cd5fb
new version RestDbInterface with working traversal and templates
casties
parents:
diff
changeset
|
442 |
5 | 443 |
73 | 444 |
445 # constructors = ( | |
446 # REST_test.manage_addRESTclassForm, | |
447 # REST_test.manage_addRESTclass | |
448 # ) | |
449 | |
450 | |
451 context.registerClass( | |
452 RestDbInterface.RestDbInterface, | |
453 constructors = ( | |
454 RestDbInterface.manage_addRestDbInterfaceForm, | |
455 RestDbInterface.manage_addRestDbInterface | |
456 ) | |
457 ) |