''' Created on 19.5.2010 @author: casties ''' from OFS.Folder import Folder from Products.PageTemplates.PageTemplateFile import PageTemplateFile from Products.ZSQLExtend import ZSQLExtend import logging from zope.interface import implements from zope.publisher.interfaces import IPublishTraverse from ZPublisher.BaseRequest import DefaultPublishTraverse #from zope.publisher.interfaces import NotFound #from zope.app import zapi #from zope.component import queryMultiAdapter import Shared.DC.ZRDB.DA from Products.ZSQLMethods.SQL import SQLConnectionIDs class RestDbInterface(Folder): """Object for RESTful database queries path schema: /[XML,JSON,HTML]/{schema}/{table}/ omitting table gives a list of schemas omitting table and schema gives a list of schemas """ implements(IPublishTraverse) meta_type="RESTdb" manage_options=Folder.manage_options+( {'label':'Config','action':'manage_editRestDbInterfaceForm'}, ) # management templates manage_editRestDbInterfaceForm=PageTemplateFile('zpt/editRestDbInterface',globals()) # data templates XML_list_schemas = PageTemplateFile('zpt/XML_list_schemas', globals()) XML_list_tables = PageTemplateFile('zpt/XML_list_tables', globals()) XML_table = PageTemplateFile('zpt/XML_table', globals()) HTML_list_schemas = PageTemplateFile('zpt/HTML_list_schemas', globals()) HTML_list_tables = PageTemplateFile('zpt/HTML_list_tables', globals()) HTML_table = PageTemplateFile('zpt/HTML_table', globals()) def __init__(self, id, title, connection_id=None): """init""" self.id = id self.title = title # database connection id self.connection_id = connection_id # create template folder self.manage_addFolder('template') def getCursor(self): """returns fresh DB cursor""" conn = getattr(self,"_v_database_connection",None) if conn is None: # create a new connection object try: if self.connection_id is None: # try to take the first existing ID connids = SQLConnectionIDs(self) if len(connids) > 0: connection_id = connids[0][0] self.connection_id = connection_id logging.debug("connection_id: %s"%repr(connection_id)) da = getattr(self, self.connection_id) da.connect('') # we copy the DAs database connection conn = da._v_database_connection #conn._register() # register with the Zope transaction system self._v_database_connection = conn except Exception, e: raise IOError("No database connection! (%s)"%str(e)) cursor = conn.getcursor() return cursor def executeSQL(self, query, *args): """execute query with args on database and return all results. result format: {"fields":fields, "rows":data}""" cur = self.getCursor() cur.execute(query, args) # description of returned fields fields = cur.description # get all data in an array data = cur.fetchall() cur.close() return {"fields":fields, "rows":data} def publishTraverse(self,request,name): """change the traversal""" # get stored path path = request.get('restdb_path', []) logging.debug("publishtraverse: name=%s restdb_path=%s"%(name,path)) if name == "index_html": # end of traversal return self.index_html #TODO: should we check more? else: # traverse if len(path) == 0: # first segment if name in ['XML','JSON','HTML']: # virtual path -- continue traversing path = [name] request['restdb_path'] = path else: # try real path tr = DefaultPublishTraverse(self, request) ob = tr.publishTraverse(request, name) return ob else: path.append(name) # continue traversing return self def index_html(self,REQUEST,RESPONSE): """index method""" path = REQUEST.get('restdb_path',[]) logging.debug("index_html path=%s"%path) if len(path) == 1: # list of schemas return self.showListOfSchemas(format=path[0]) elif len(path) == 2: # list of tables return self.showListOfTables(format=path[0],schema=path[1]) elif len(path) == 3: # table return self.showTable(format=path[0],schema=path[1],table=path[2]) # don't know what to do return str(REQUEST) def showTable(self,format='XML',schema='public',table=None): """returns PageTemplate with tables""" logging.debug("showtable") pt = getattr(self.template, '%s_table'%format, None) if pt is None: return "ERROR!! template %s_table not found"%format data = self.getTable(schema,table) return pt(data=data,tablename=table) def getTable(self,schema='public',table=None,username='guest'): """return table data""" logging.debug("gettable") data = self.executeSQL("select * from %s"%table) return data def showListOfTables(self,format='XML',schema='public'): """returns PageTemplate with list of tables""" logging.debug("showlistoftables") pt = getattr(self.template, '%s_list_tables'%format, None) if pt is None: return "ERROR!! template %s_list_tables not found"%format data = self.getListOfTables(schema) return pt(data=data,schema=schema) def getListOfTables(self,schema='public',username='guest'): """return list of tables""" logging.debug("getlistoftables") # get list of fields and types of db table qstr="""select c.relname FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind IN ('r','') AND n.nspname NOT IN ('pg_catalog', 'pg_toast') AND pg_catalog.pg_table_is_visible(c.oid)""" #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" data=self.executeSQL(qstr) return data def showListOfSchemas(self,format='XML'): """returns PageTemplate with list of schemas""" logging.debug("showlistofschemas") pt = getattr(self.template, '%s_list_schemas'%format, None) if pt is None: return "ERROR!! template %s_list_schemas not found"%format data = self.getListOfSchemas() return pt(data=data) def getListOfSchemas(self,username='guest'): """return list of schemas""" logging.debug("getlistofschemas") # TODO: really look up schemas data={'fields':'schemas','data':'public'} return data def manage_editRestDbInterface(self, title=None, connection_id=None, REQUEST=None): """Change the object""" if title is not None: self.title = title if connection_id is not None: self.connection_id = connection_id #checkPermission=getSecurityManager().checkPermission REQUEST.RESPONSE.redirect('manage_main') manage_addRestDbInterfaceForm=PageTemplateFile('zpt/addRestDbInterface',globals()) def manage_addRestDbInterface(self, id, title='', label='', description='', createPublic=0, createUserF=0, REQUEST=None): """Add a new object with id *id*.""" ob=RestDbInterface(str(id),title) self._setObject(id, ob) #checkPermission=getSecurityManager().checkPermission REQUEST.RESPONSE.redirect('manage_main')