"""
    Query Tools
    ===== =====

_RelationalJoinsMixin
    Methods to allow specifying relational type joins.

_RelationalLogicMixin
    Methods to perform relational type joins.
"""
# $Id: QueryTools.py,v 1.11 2002/08/18 22:46:07 adrian Exp $
#
# $Log: QueryTools.py,v $
# Revision 1.11  2002/08/18 22:46:07  adrian
# Indexes were a bust - reworked join logic. OUTER joins not supported in this version
#
# Revision 1.10  2002/08/18 15:27:16  adrian
# Changed (Fixed) ZDC.
# * It now uses temporary, in memory, indexes
# * It now reverses joins to ensure consistancy
# * It now generates "virtual SQL" so you can see what you are asking.
#
# Revision 1.9  2002/05/20 15:10:57  ahungate
# *** empty log message ***
#
# Revision 1.8  2002/05/17 13:27:29  ahungate
# *** empty log message ***
#
# Revision 1.7  2002/05/17 07:20:11  adrian
# *** empty log message ***
#
# Revision 1.6  2002/05/16 16:16:17  ahungate
# *** empty log message ***
#
# Revision 1.5  2002/05/15 21:52:11  adrian
# Converted all string exceptions to object exceptions.
#
# Revision 1.4  2002/05/15 16:30:52  ahungate
# *** empty log message ***
#
# Revision 1.3  2002/05/14 18:59:02  adrian
# *** empty log message ***
#
# Revision 1.2  2002/05/09 14:27:06  ahungate
# Added "outer join" functionality to ZDataCombiner
#
# Revision 1.1.1.1  2002/02/03 17:07:16  root
#
#
# Revision 1.2  2002/01/21 00:58:08  adrian
# Rebased all queries and datacombiner on the Aqueduct DA
# Added argument support
# Added Drill-Down support to ZReportTool
#
# Revision 1.1  2002/01/15 20:26:04  adrian
# Factored out common code in all-QT and ZDC
# Added support for comparing against constants in all-QT
# Added support for multi-column joins in all-QT
# Added sorting to ZRT
# Added no-print group type to ZRT
# You now set the connection ID when creating a ZVQ
# added property support to ZVQ
# Corrected display of calculated columns in ZRT
#
#
__version__ = '$Revision: 1.11 $'[11:-2]

import Globals
import string

class DatabaseError(StandardError):
    pass

def _getTableName(columnName):
    """ Split the table name from a FQ column name """
    if string.find(columnName, '.')==-1:
        return None
    return columnName[:string.find(columnName, '.')]

def _getColumnName(columnName):
    """ Split the column name from a FQ column name """
    if string.find(columnName, '.')==-1:
        return columnName
    return columnName[string.find(columnName, '.')+1:]

def _qualifyColumns(row, table, seed={}):
    """ Return the given row with all the column names qualified
        with the given table name """
    retVal = {}
    for key in seed.keys():
        retVal[key] = seed[key]
    for col in row.keys():
        if not _getTableName(col) and table:
            newCol = "%s.%s" % (table, col, )
        else:
            newCol = "%s" % (col, )
        retVal[newCol] = row[col]
    return retVal

class _RelationalJoinsMixin:
    """ Add/Remove Joins """
    joins = []
    manage_Joins = Globals.HTMLFile('www/queryJoins', globals())
    manage_options = (
        (
            {'label': 'Joins', 'action': 'manage_Joins',
             'help': ('ZDataQueryKit', 'QueryJoins.stx'), },
        )
    )
    _relationActions = {
# Inner Joins (Records exist in both datasources/tables)
        '='     : {'reverse': '=',      'type': 'INNER',    'test': lambda x,y: x==y,  },
        '!='    : {'reverse': '!=',     'type': 'INNER',    'test': lambda x,y: x!=y,  },
        '>'     : {'reverse': '<',      'type': 'INNER',    'test': lambda x,y: x>y,   },
        '<'     : {'reverse': '>',      'type': 'INNER',    'test': lambda x,y: x<y,   },
        '>='    : {'reverse': '<=',     'type': 'INNER',    'test': lambda x,y: x>=y,  },
        '<='    : {'reverse': '>=',     'type': 'INNER',    'test': lambda x,y: x<=y,  },
# Left Joins (Right may not exist)
        '=*'    : {'reverse': '*=',     'type': 'LEFT',     'test': lambda x,y: x==y,  },
        '!=*'   : {'reverse': '*!=',    'type': 'LEFT',     'test': lambda x,y: x!=y,  },
        '>*'    : {'reverse': '*<',     'type': 'LEFT',     'test': lambda x,y: x>y,   },
        '<*'    : {'reverse': '*>',     'type': 'LEFT',     'test': lambda x,y: x<y,   },
        '>=*'   : {'reverse': '*<=',    'type': 'LEFT',     'test': lambda x,y: x>=y,  },
        '<=*'   : {'reverse': '*>=',    'type': 'LEFT',     'test': lambda x,y: x<=y,  },
# Right Joins (Left may not exist)
        '*='    : {'reverse': '=*',     'type': 'RIGHT',    'test': lambda x,y: x==y,  },
        '*!='   : {'reverse': '!=*',    'type': 'RIGHT',    'test': lambda x,y: x!=y,  },
        '*>'    : {'reverse': '<*',     'type': 'RIGHT',    'test': lambda x,y: x>y,   },
        '*<'    : {'reverse': '>*',     'type': 'RIGHT',    'test': lambda x,y: x<y,   },
        '*>='   : {'reverse': '<=*',    'type': 'RIGHT',    'test': lambda x,y: x>=y,  },
        '*<='   : {'reverse': '>=*',    'type': 'RIGHT',    'test': lambda x,y: x<=y,  },
    }

    def relationActions(self):
        """ Return the list of valid relation actions """
        return self._relationActions.keys()

    def _RJM_Reset(self):
        """ Reset the joins in the mix """
        self.joins = []

    def _RJM_RemoveTable(self, table):
        """ Remove all joins that reference this table or parameter """
        joins = []
        for item in self.joins:
            if table not in (
                    _getTableName(item['leftColumn']),
                    _getTableName(item['rightColumn']),
                    item['leftColumn'],
                    item['rightColumn']
                ):
                joins.append(item)
        self.joins = joins

    def _RJM_removeStar(self, rel):
        """ Remove an optional star (outer join specifier) from the relation """
        retVal = rel
        if rel[0] == '*':
            retVal = rel[1:]
        if rel[-1] == '*':
            retVal = rel[:-1]
        return retVal

    def _reverseJoin(self, join):
        """ Reverse the columns in a join """
        tmp = join['leftColumn']
        join['leftColumn'] = join['rightColumn']
        join['rightColumn'] = tmp
        join['relation'] = self._relationActions[join['relation']]['reverse']

    def _InnerOuter(self, rel):
        """ return the join type string """
        return self._relationActions[rel]['type']

    def manage_addJoin(self, leftColumn, leftValue, relation, rightColumn, rightValue, REQUEST=None):
        """ Add a new table join """
        joins = self.joins
        if leftColumn == '-':
            leftColumn = leftValue
        if rightColumn == '-':
            rightColumn = rightValue
        joins.append({'leftColumn': str(leftColumn), 'relation': str(relation), 'rightColumn': str(rightColumn)})
        self.joins = joins
        self._updateTemplate()
        if REQUEST:
            message = "Join created."
            return self.manage_Joins(self, REQUEST, manage_tabs_message=message)

    def manage_delJoin(self, REQUEST=None):
        """ Delete one or more table joins """
        joins = self.joins
        for item in range(len(self.joins)-1, -1, -1):
            if REQUEST.get('dl_%d' % item):
                del joins[item]
        self.joins = joins
        self._updateTemplate()
        if REQUEST:
            message = "Join(s) deleted."
            return self.manage_Joins(self, REQUEST, manage_tabs_message=message)

class _RelationalLogicMixin(
        _RelationalJoinsMixin,
    ):
    """ Perform joins on data
        Unlike SQL, refuse to combine tables that are
        not referenced in a join. """

    def _cartProd(self, ds1, ds2, join):
        """ Compute the Cartisian Product of ds1 and ds2 filtered by join.
            join should be a function (Or lambda) that accepts a row as a parameter.
        """
        retVal = []
        for row2 in ds2:
            if ds1:
                for row1 in ds1:
                    row = row1.copy()
                    row.update(row2)
                    if not join or join(row):
                        retVal.append(row)
            else:
                row = row2.copy()
                if not join or join(row):
                    retVal.append(row)
        return retVal

    def _join(self, joins, datasources):
        """ Run the combination - This is not suitable for large datasets """
        ds = {}
# Retreive all records and qualify all columns
        for dsn in datasources.keys():
            ds[dsn] = map(lambda row, dsn=dsn: _qualifyColumns(row, dsn), datasources[dsn]._getData(self))
# Init locals
        lList = []              # List of "Left Tables"
        rList = {}              # List of "Right Tables"
# Process joins
        for j1 in joins:
###
### XXX - This stop OUTER JOINS working - Need to fix the code and then remove this
###
            if self._InnerOuter(j1['relation']) != 'INNER':
                raise DatabaseError("OUTER joins are not supported yet, sorry")
# Check for RIGHT outer join - Reverse join
            if self._InnerOuter(j1['relation']) == 'RIGHT':
                self._reverseJoin(j1)
            for j2 in joins:
# Check for inconsistent pairings
#  table pairs specified with the tables transposed - Reverse join
                if (_getTableName(j1['leftColumn']) == _getTableName(j2['rightColumn']) and
                    _getTableName(j1['rightColumn']) == _getTableName(j2['leftColumn'])):
                    self._reverseJoin(j2)
# Check for inconsistent INNER/OUTER pairings - RAISE
                if (_getTableName(j1['leftColumn']) == _getTableName(j2['leftColumn']) and
                    _getTableName(j1['rightColumn']) == _getTableName(j2['rightColumn']) and
                    self._InnerOuter(j1['relation']) != self._InnerOuter(j2['relation'])):
                    raise DatabaseError("Inconsistent INNER/OUTER joins - [%s %s %s - %s] [%s %s %s - %s]" % (
                        j1['leftColumn'], j1['relation'], j1['rightColumn'], self._InnerOuter(j1['relation']),
                        j2['leftColumn'], j2['relation'], j2['rightColumn'], self._InnerOuter(j2['relation'])
                        ))
# Group by pairings
        joinsDone = []
        innerGroups = []
        outerGroups = []
        for j1 in joins:
            if not j1 in joinsDone:
                thisGroupType = self._InnerOuter(j1['relation'])
                thisGroupList = []
                thisGroup = [_getTableName(j1['leftColumn']), _getTableName(j1['rightColumn'])]
                rList[_getTableName(j1['leftColumn'])] = 1
                rList[_getTableName(j1['rightColumn'])] = 1
                for j2 in joins:
# Add join to current group
                    if (_getTableName(j1['leftColumn']) == _getTableName(j2['leftColumn']) and
                        _getTableName(j1['rightColumn']) == _getTableName(j2['rightColumn']) and
                        not j2 in joinsDone):
                        thisGroupList.append(j2)
                        joinsDone.append(j2)
                        rList[_getTableName(j2['leftColumn'])] = 1
                        rList[_getTableName(j2['rightColumn'])] = 1
                thisGroupCodeList = []
                for join in thisGroupList:
                    thisGroupCodeList.append("_relationActions['%s']['test'](row['%s'], row['%s'])" %(join['relation'], join['leftColumn'], join['rightColumn'], ))
                codeString = string.join(thisGroupCodeList, ' and ')
                thisGroupCode = compile(codeString, 'Relational Logic', 'eval')
                code = lambda row, thisGroupCode=thisGroupCode, _relationActions=self._relationActions: eval(thisGroupCode, globals(), {'row': row, '_relationActions': _relationActions})
                thisGroup.extend((code, thisGroupType))
                if thisGroupType == 'INNER':
                    innerGroups.append(thisGroup)
                else:
                    outerGroups.append(thisGroup)
        joinGroups = innerGroups + outerGroups
        rList = rList.keys()
        for dsn in ds.keys():
            if dsn not in rList:
                lList.append(dsn)

        tList = []
        out = []
        for joinGroup in joinGroups:
            if joinGroup[0] not in tList:
                out = self._cartProd(out, ds[joinGroup[0]], None)
                tList.append(joinGroup[0])
            if joinGroup[1] not in tList:
                out = self._cartProd(out, ds[joinGroup[1]], joinGroup[2])
            else:
                out = self._cartProd(None, out, joinGroup[2])
            tList.append(joinGroup[1])

        for dsn in lList:
            if dsn not in tList:
                out = self._cartProd(out, ds[dsn], None)
                tList.append(dsn)

        return out
