'''
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')