Mercurial > hg > ChinaGisRestApi

comparison RestDbInterface.py @ 4:e3ee1f358fe6

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
new version that doesn't use ZSQLExtend but the database connection more directly. new templates to go with that (returned data structures are different)
author casties
date Mon, 07 Jun 2010 14:02:17 +0000
parents 61a3764cd5fb
children 7539a9e69365
comparison
equal deleted inserted replaced
3:0e2d167d0983 4:e3ee1f358fe6
13 from zope.publisher.interfaces import IPublishTraverse 13 from zope.publisher.interfaces import IPublishTraverse
14 from ZPublisher.BaseRequest import DefaultPublishTraverse 14 from ZPublisher.BaseRequest import DefaultPublishTraverse
15 #from zope.publisher.interfaces import NotFound 15 #from zope.publisher.interfaces import NotFound
16 #from zope.app import zapi 16 #from zope.app import zapi
17 #from zope.component import queryMultiAdapter 17 #from zope.component import queryMultiAdapter
18 import Shared.DC.ZRDB.DA
19 from Products.ZSQLMethods.SQL import SQLConnectionIDs
18 20
19 21
20 class RestDbInterface(Folder): 22 class RestDbInterface(Folder):
21 """Object for RESTful database queries 23 """Object for RESTful database queries
22 path schema: /[XML,JSON,HTML]/{schema}/{table}/ 24 path schema: /[XML,JSON,HTML]/{schema}/{table}/
24 omitting table and schema gives a list of schemas 26 omitting table and schema gives a list of schemas
25 """ 27 """
26 implements(IPublishTraverse) 28 implements(IPublishTraverse)
27 29
28 meta_type="RESTdb" 30 meta_type="RESTdb"
31 manage_options=Folder.manage_options+(
32 {'label':'Config','action':'manage_editRestDbInterfaceForm'},
33 )
34
35 # management templates
36 manage_editRestDbInterfaceForm=PageTemplateFile('zpt/editRestDbInterface',globals())
29 37
30 # data templates 38 # data templates
31 XML_list_schemas = PageTemplateFile('zpt/XML_list_schemas', globals()) 39 XML_list_schemas = PageTemplateFile('zpt/XML_list_schemas', globals())
32 XML_list_tables = PageTemplateFile('zpt/XML_list_tables', globals()) 40 XML_list_tables = PageTemplateFile('zpt/XML_list_tables', globals())
33 XML_table = PageTemplateFile('zpt/XML_table', globals()) 41 XML_table = PageTemplateFile('zpt/XML_table', globals())
35 HTML_list_tables = PageTemplateFile('zpt/HTML_list_tables', globals()) 43 HTML_list_tables = PageTemplateFile('zpt/HTML_list_tables', globals())
36 HTML_table = PageTemplateFile('zpt/HTML_table', globals()) 44 HTML_table = PageTemplateFile('zpt/HTML_table', globals())
37 45
38 46
39 47
40 def __init__(self, id, title): 48 def __init__(self, id, title, connection_id=None):
41 """init""" 49 """init"""
42 self.id = id 50 self.id = id
43 self.title = title 51 self.title = title
52 # database connection id
53 self.connection_id = connection_id
44 # create template folder 54 # create template folder
45 self.manage_addFolder('template') 55 self.manage_addFolder('template')
56
57
58 def getCursor(self):
59 """returns fresh DB cursor"""
60 conn = getattr(self,"_v_database_connection",None)
61 if conn is None:
62 # create a new connection object
63 try:
64 if self.connection_id is None:
65 # try to take the first existing ID
66 connids = SQLConnectionIDs(self)
67 if len(connids) > 0:
68 connection_id = connids[0][0]
69 self.connection_id = connection_id
70 logging.debug("connection_id: %s"%repr(connection_id))
71
72 da = getattr(self, self.connection_id)
73 da.connect('')
74 # we copy the DAs database connection
75 conn = da._v_database_connection
76 #conn._register() # register with the Zope transaction system
77 self._v_database_connection = conn
78 except Exception, e:
79 raise IOError("No database connection! (%s)"%str(e))
80
81 cursor = conn.getCursor()
82 return cursor
83
84 def executeSQL(self, query, *args):
85 """execute query with args on database and return all results.
86 result format: {"fields":fields, "rows":data}"""
87 cur = self.getCursor()
88 cur.execute(query, args)
89 # description of returned fields
90 fields = cur.description
91 # get all data in an array
92 data = cur.fetchall()
93 cur.close()
94 return {"fields":fields, "rows":data}
46 95
47 96
48 def publishTraverse(self,request,name): 97 def publishTraverse(self,request,name):
49 """change the traversal""" 98 """change the traversal"""
50 # get stored path 99 # get stored path
89 return self.showTable(format=path[0],schema=path[1],table=path[2]) 138 return self.showTable(format=path[0],schema=path[1],table=path[2])
90 139
91 # don't know what to do 140 # don't know what to do
92 return str(REQUEST) 141 return str(REQUEST)
93 142
94 143
95 def showTable(self,format='XML',schema='public',table=None): 144 def showTable(self,format='XML',schema='public',table=None):
96 """returns PageTemplate with tables""" 145 """returns PageTemplate with tables"""
97 logging.debug("showtable") 146 logging.debug("showtable")
98 pt = getattr(self.template, '%s_table'%format, None) 147 pt = getattr(self.template, '%s_table'%format, None)
99 if pt is None: 148 if pt is None:
104 153
105 154
106 def getTable(self,schema='public',table=None,username='guest'): 155 def getTable(self,schema='public',table=None,username='guest'):
107 """return table data""" 156 """return table data"""
108 logging.debug("gettable") 157 logging.debug("gettable")
109 qstr="select * from %s"%table 158 data = self.executeSQL("select * from %s"%table)
110 data=self.ZSQLSimpleSearch(qstr)
111 return data 159 return data
112 160
113 def showListOfTables(self,format='XML',schema='public'): 161 def showListOfTables(self,format='XML',schema='public'):
114 """returns PageTemplate with list of tables""" 162 """returns PageTemplate with list of tables"""
115 logging.debug("showlistoftables") 163 logging.debug("showlistoftables")
127 qstr="""select c.relname FROM pg_catalog.pg_class c 175 qstr="""select c.relname FROM pg_catalog.pg_class c
128 LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace 176 LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
129 WHERE c.relkind IN ('r','') AND n.nspname NOT IN ('pg_catalog', 'pg_toast') 177 WHERE c.relkind IN ('r','') AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
130 AND pg_catalog.pg_table_is_visible(c.oid)""" 178 AND pg_catalog.pg_table_is_visible(c.oid)"""
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" 179 #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"
132 data=self.ZSQLSimpleSearch(qstr) 180 data=self.executeSQL(qstr)
133 return data 181 return data
134 182
135 def showListOfSchemas(self,format='XML'): 183 def showListOfSchemas(self,format='XML'):
136 """returns PageTemplate with list of schemas""" 184 """returns PageTemplate with list of schemas"""
137 logging.debug("showlistofschemas") 185 logging.debug("showlistofschemas")
144 192
145 def getListOfSchemas(self,username='guest'): 193 def getListOfSchemas(self,username='guest'):
146 """return list of schemas""" 194 """return list of schemas"""
147 logging.debug("getlistofschemas") 195 logging.debug("getlistofschemas")
148 # TODO: really look up schemas 196 # TODO: really look up schemas
149 data=['public'] 197 data={'fields':'schemas','data':'public'}
150 return data 198 return data
151 199
152 200
201 def manage_editRestDbInterface(self, title=None, connection_id=None,
202 REQUEST=None):
203 """Change the object"""
204 if title is not None:
205 self.title = title
206
207 if connection_id is not None:
208 self.connection_id = connection_id
209
210 #checkPermission=getSecurityManager().checkPermission
211 REQUEST.RESPONSE.redirect('manage_main')
212
153 213
154 manage_addRestDbInterfaceForm=PageTemplateFile('zpt/addRestDbInterface',globals()) 214 manage_addRestDbInterfaceForm=PageTemplateFile('zpt/addRestDbInterface',globals())
155 215
156 def manage_addRestDbInterface(self, id, title='', label='', description='', 216 def manage_addRestDbInterface(self, id, title='', label='', description='',
157 createPublic=0, 217 createPublic=0,