Mercurial > hg > ChinaGisRestApi

annotate RestDbInterface.py @ 2:61a3764cd5fb

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
new version RestDbInterface with working traversal and templates
author casties
date Fri, 21 May 2010 11:48:30 +0000
parents
children e3ee1f358fe6
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
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
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
11
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
12 from zope.interface import implements
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
13 from zope.publisher.interfaces import IPublishTraverse
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
14 from ZPublisher.BaseRequest import DefaultPublishTraverse
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
15 #from zope.publisher.interfaces import NotFound
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
16 #from zope.app import zapi
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
17 #from zope.component import queryMultiAdapter
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
18
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
19
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
20 class RestDbInterface(Folder):
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
21 """Object for RESTful database queries
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
22 path schema: /[XML,JSON,HTML]/{schema}/{table}/
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
23 omitting table gives a list of schemas
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
24 omitting table and schema gives a list of schemas
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
25 """
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
26 implements(IPublishTraverse)
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
27
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
28 meta_type="RESTdb"
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
29
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
30 # data templates
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
31 XML_list_schemas = PageTemplateFile('zpt/XML_list_schemas', globals())
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
32 XML_list_tables = PageTemplateFile('zpt/XML_list_tables', globals())
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
33 XML_table = PageTemplateFile('zpt/XML_table', globals())
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
34 HTML_list_schemas = PageTemplateFile('zpt/HTML_list_schemas', globals())
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
35 HTML_list_tables = PageTemplateFile('zpt/HTML_list_tables', globals())
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
36 HTML_table = PageTemplateFile('zpt/HTML_table', globals())
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
37
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
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
40 def __init__(self, id, title):
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
41 """init"""
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
42 self.id = id
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
43 self.title = title
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
44 # create template folder
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
45 self.manage_addFolder('template')
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 def publishTraverse(self,request,name):
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
49 """change the traversal"""
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
50 # get stored path
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
51 path = request.get('restdb_path', [])
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
52 logging.debug("publishtraverse: name=%s restdb_path=%s"%(name,path))
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
53
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
54 if name == "index_html":
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
55 # end of traversal
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
56 return self.index_html
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
57 #TODO: should we check more?
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
58 else:
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
59 # traverse
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
60 if len(path) == 0:
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
61 # first segment
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
62 if name in ['XML','JSON','HTML']:
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
63 # virtual path -- continue traversing
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
64 path = [name]
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
65 request['restdb_path'] = path
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
66 else:
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
67 # try real path
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
68 tr = DefaultPublishTraverse(self, request)
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
69 ob = tr.publishTraverse(request, name)
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
70 return ob
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
71 else:
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
72 path.append(name)
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
73
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
74 # continue traversing
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
75 return self
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
76
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
77 def index_html(self,REQUEST,RESPONSE):
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
78 """index method"""
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
79 path = REQUEST.get('restdb_path',[])
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
80 logging.debug("index_html path=%s"%path)
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
81 if len(path) == 1:
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
82 # list of schemas
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
83 return self.showListOfSchemas(format=path[0])
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
84 elif len(path) == 2:
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
85 # list of tables
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
86 return self.showListOfTables(format=path[0],schema=path[1])
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
87 elif len(path) == 3:
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
88 # table
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
89 return self.showTable(format=path[0],schema=path[1],table=path[2])
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
90
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
91 # don't know what to do
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
92 return str(REQUEST)
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
93
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
94
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
95 def showTable(self,format='XML',schema='public',table=None):
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
96 """returns PageTemplate with tables"""
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
97 logging.debug("showtable")
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
98 pt = getattr(self.template, '%s_table'%format, None)
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
99 if pt is None:
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
100 return "ERROR!! template %s_table not found"%format
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
101
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
102 data = self.getTable(schema,table)
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
103 return pt(data=data,tablename=table)
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
104
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
105
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
106 def getTable(self,schema='public',table=None,username='guest'):
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
107 """return table data"""
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
108 logging.debug("gettable")
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
109 qstr="select * from %s"%table
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
110 data=self.ZSQLSimpleSearch(qstr)
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
111 return data
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
112
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
113 def showListOfTables(self,format='XML',schema='public'):
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
114 """returns PageTemplate with list of tables"""
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
115 logging.debug("showlistoftables")
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
116 pt = getattr(self.template, '%s_list_tables'%format, None)
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
117 if pt is None:
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
118 return "ERROR!! template %s_list_tables not found"%format
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
119
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
120 data = self.getListOfTables(schema)
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
121 return pt(data=data,schema=schema)
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
122
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
123 def getListOfTables(self,schema='public',username='guest'):
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
124 """return list of tables"""
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
125 logging.debug("getlistoftables")
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
126 # get list of fields and types of db table
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
127 qstr="""select c.relname FROM pg_catalog.pg_class c
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
128 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
129 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
130 AND pg_catalog.pg_table_is_visible(c.oid)"""
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
131 #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"
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
132 data=self.ZSQLSimpleSearch(qstr)
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
133 return data
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
134
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
135 def showListOfSchemas(self,format='XML'):
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
136 """returns PageTemplate with list of schemas"""
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
137 logging.debug("showlistofschemas")
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
138 pt = getattr(self.template, '%s_list_schemas'%format, None)
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
139 if pt is None:
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
140 return "ERROR!! template %s_list_schemas not found"%format
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
141
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
142 data = self.getListOfSchemas()
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
143 return pt(data=data)
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
144
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
145 def getListOfSchemas(self,username='guest'):
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
146 """return list of schemas"""
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
147 logging.debug("getlistofschemas")
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
148 # TODO: really look up schemas
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
149 data=['public']
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
150 return data
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
151
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
152
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
153
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
154 manage_addRestDbInterfaceForm=PageTemplateFile('zpt/addRestDbInterface',globals())
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
155
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
156 def manage_addRestDbInterface(self, id, title='', label='', description='',
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
157 createPublic=0,
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
158 createUserF=0,
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
159 REQUEST=None):
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
160 """Add a new object with id *id*."""
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
161
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
162 ob=RestDbInterface(str(id),title)
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
163 self._setObject(id, ob)
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
164
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
165 #checkPermission=getSecurityManager().checkPermission
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
166 REQUEST.RESPONSE.redirect('manage_main')
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
167
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
168