Mercurial > hg > ChinaGisRestApi

annotate 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
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
4
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents: 2
diff changeset
18 import Shared.DC.ZRDB.DA
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents: 2
diff changeset
19 from Products.ZSQLMethods.SQL import SQLConnectionIDs
2
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
20
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 class RestDbInterface(Folder):
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
23 """Object for RESTful database queries
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
24 path schema: /[XML,JSON,HTML]/{schema}/{table}/
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
25 omitting table gives a list of schemas
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
26 omitting table and schema gives a list of schemas
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 implements(IPublishTraverse)
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 meta_type="RESTdb"
4
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents: 2
diff changeset
31 manage_options=Folder.manage_options+(
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents: 2
diff changeset
32 {'label':'Config','action':'manage_editRestDbInterfaceForm'},
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents: 2
diff changeset
33 )
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 # management templates
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents: 2
diff changeset
36 manage_editRestDbInterfaceForm=PageTemplateFile('zpt/editRestDbInterface',globals())
2
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 # data templates
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
39 XML_list_schemas = PageTemplateFile('zpt/XML_list_schemas', globals())
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
40 XML_list_tables = PageTemplateFile('zpt/XML_list_tables', globals())
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
41 XML_table = PageTemplateFile('zpt/XML_table', globals())
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
42 HTML_list_schemas = PageTemplateFile('zpt/HTML_list_schemas', globals())
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
43 HTML_list_tables = PageTemplateFile('zpt/HTML_list_tables', globals())
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
44 HTML_table = PageTemplateFile('zpt/HTML_table', globals())
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
45
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
4
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents: 2
diff changeset
48 def __init__(self, id, title, connection_id=None):
2
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
49 """init"""
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
50 self.id = id
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
51 self.title = title
4
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents: 2
diff changeset
52 # database connection id
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents: 2
diff changeset
53 self.connection_id = connection_id
2
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
54 # create template folder
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
55 self.manage_addFolder('template')
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
56
4
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents: 2
diff changeset
57
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents: 2
diff changeset
58 def getCursor(self):
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents: 2
diff changeset
59 """returns fresh DB cursor"""
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents: 2
diff changeset
60 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
61 if conn is None:
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents: 2
diff changeset
62 # create a new connection object
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents: 2
diff changeset
63 try:
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents: 2
diff changeset
64 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
65 # 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
66 connids = SQLConnectionIDs(self)
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents: 2
diff changeset
67 if len(connids) > 0:
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents: 2
diff changeset
68 connection_id = connids[0][0]
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents: 2
diff changeset
69 self.connection_id = connection_id
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents: 2
diff changeset
70 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
71
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents: 2
diff changeset
72 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
73 da.connect('')
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents: 2
diff changeset
74 # 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
75 conn = da._v_database_connection
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents: 2
diff changeset
76 #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
77 self._v_database_connection = conn
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents: 2
diff changeset
78 except Exception, e:
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents: 2
diff changeset
79 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
80
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents: 2
diff changeset
81 cursor = conn.getCursor()
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents: 2
diff changeset
82 return cursor
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents: 2
diff changeset
83
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents: 2
diff changeset
84 def executeSQL(self, query, *args):
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents: 2
diff changeset
85 """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
86 result format: {"fields":fields, "rows":data}"""
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents: 2
diff changeset
87 cur = self.getCursor()
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents: 2
diff changeset
88 cur.execute(query, args)
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents: 2
diff changeset
89 # description of returned fields
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents: 2
diff changeset
90 fields = cur.description
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents: 2
diff changeset
91 # get all data in an array
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents: 2
diff changeset
92 data = cur.fetchall()
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents: 2
diff changeset
93 cur.close()
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents: 2
diff changeset
94 return {"fields":fields, "rows":data}
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents: 2
diff changeset
95
2
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
96
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
97 def publishTraverse(self,request,name):
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
98 """change the traversal"""
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
99 # get stored path
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
100 path = request.get('restdb_path', [])
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
101 logging.debug("publishtraverse: name=%s restdb_path=%s"%(name,path))
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
102
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
103 if name == "index_html":
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
104 # end of traversal
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
105 return self.index_html
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
106 #TODO: should we check more?
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
107 else:
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
108 # traverse
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
109 if len(path) == 0:
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
110 # first segment
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
111 if name in ['XML','JSON','HTML']:
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
112 # virtual path -- continue traversing
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
113 path = [name]
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
114 request['restdb_path'] = path
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
115 else:
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
116 # try real path
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
117 tr = DefaultPublishTraverse(self, request)
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
118 ob = tr.publishTraverse(request, name)
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
119 return ob
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
120 else:
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
121 path.append(name)
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 # continue traversing
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
124 return self
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 def index_html(self,REQUEST,RESPONSE):
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
127 """index method"""
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
128 path = REQUEST.get('restdb_path',[])
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
129 logging.debug("index_html path=%s"%path)
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
130 if len(path) == 1:
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
131 # list of schemas
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
132 return self.showListOfSchemas(format=path[0])
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
133 elif len(path) == 2:
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
134 # list of tables
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
135 return self.showListOfTables(format=path[0],schema=path[1])
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
136 elif len(path) == 3:
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
137 # table
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
138 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
139
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
140 # don't know what to do
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
141 return str(REQUEST)
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
142
4
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents: 2
diff changeset
143
2
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
144 def showTable(self,format='XML',schema='public',table=None):
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
145 """returns PageTemplate with tables"""
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
146 logging.debug("showtable")
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
147 pt = getattr(self.template, '%s_table'%format, None)
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
148 if pt is None:
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
149 return "ERROR!! template %s_table not found"%format
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
150
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
151 data = self.getTable(schema,table)
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
152 return pt(data=data,tablename=table)
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
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
155 def getTable(self,schema='public',table=None,username='guest'):
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
156 """return table data"""
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
157 logging.debug("gettable")
4
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents: 2
diff changeset
158 data = self.executeSQL("select * from %s"%table)
2
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
159 return data
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
160
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
161 def showListOfTables(self,format='XML',schema='public'):
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
162 """returns PageTemplate with list of tables"""
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
163 logging.debug("showlistoftables")
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
164 pt = getattr(self.template, '%s_list_tables'%format, None)
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
165 if pt is None:
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
166 return "ERROR!! template %s_list_tables not found"%format
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 data = self.getListOfTables(schema)
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
169 return pt(data=data,schema=schema)
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
170
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
171 def getListOfTables(self,schema='public',username='guest'):
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
172 """return list of tables"""
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
173 logging.debug("getlistoftables")
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
174 # get list of fields and types of db table
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
175 qstr="""select c.relname FROM pg_catalog.pg_class c
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
176 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
177 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
178 AND pg_catalog.pg_table_is_visible(c.oid)"""
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
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"
4
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents: 2
diff changeset
180 data=self.executeSQL(qstr)
2
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
181 return data
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
182
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
183 def showListOfSchemas(self,format='XML'):
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
184 """returns PageTemplate with list of schemas"""
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
185 logging.debug("showlistofschemas")
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
186 pt = getattr(self.template, '%s_list_schemas'%format, None)
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
187 if pt is None:
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
188 return "ERROR!! template %s_list_schemas not found"%format
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
189
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
190 data = self.getListOfSchemas()
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
191 return pt(data=data)
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
192
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
193 def getListOfSchemas(self,username='guest'):
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
194 """return list of schemas"""
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
195 logging.debug("getlistofschemas")
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
196 # TODO: really look up schemas
4
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents: 2
diff changeset
197 data={'fields':'schemas','data':'public'}
2
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
198 return data
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
199
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
200
4
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents: 2
diff changeset
201 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
202 REQUEST=None):
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents: 2
diff changeset
203 """Change the object"""
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents: 2
diff changeset
204 if title is not None:
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents: 2
diff changeset
205 self.title = title
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents: 2
diff changeset
206
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents: 2
diff changeset
207 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
208 self.connection_id = connection_id
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents: 2
diff changeset
209
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents: 2
diff changeset
210 #checkPermission=getSecurityManager().checkPermission
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents: 2
diff changeset
211 REQUEST.RESPONSE.redirect('manage_main')
e3ee1f358fe6 new version that doesn't use ZSQLExtend but the database connection more directly.
casties
parents: 2
diff changeset
212
2
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
213
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
214 manage_addRestDbInterfaceForm=PageTemplateFile('zpt/addRestDbInterface',globals())
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 def manage_addRestDbInterface(self, id, title='', label='', description='',
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
217 createPublic=0,
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
218 createUserF=0,
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
219 REQUEST=None):
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
220 """Add a new object with id *id*."""
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
221
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
222 ob=RestDbInterface(str(id),title)
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
223 self._setObject(id, ob)
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 #checkPermission=getSecurityManager().checkPermission
61a3764cd5fb new version RestDbInterface with working traversal and templates
casties
parents:
diff changeset
226 REQUEST.RESPONSE.redirect('manage_main')
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