Ticket #1099: dCursorMixin.py

# -*- coding: utf-8 -*-
# dabo/db/dCursorMixin

import types
import datetime
import inspect
import random
import sys
import re
import array
# Make sure that the user's installation supports Decimal.
_USE_DECIMAL = True
try:
    from decimal import Decimal
except ImportError:
    _USE_DECIMAL = False

import dabo
import dabo.dConstants as kons
from dabo.dLocalize import _
import dabo.dException as dException
from dabo.dObject import dObject
from dNoEscQuoteStr import dNoEscQuoteStr
from dabo.db import dTable
from dabo.db.dDataSet import dDataSet
from dabo.lib import dates


class dCursorMixin(dObject):
    """Dabo's cursor class, representing the lowest tier."""
    _call_initProperties = False
    
    
    def debug(self):
        return self._bizobj.DataSource == "FotoInf"
        
        
        
    def __init__(self, sql="", *args, **kwargs):
        self._convertStrToUnicode = True
        self._initProperties()
        if sql and isinstance(sql, basestring) and len(sql) > 0:
            self.UserSQL = sql

        #self.super()
        #super(dCursorMixin, self).__init__()
        ## pkm: Neither of the above are correct. We need to explicitly
        ##      call dObject's __init__, otherwise the cursor object with
        ##      which we are mixed-in will take the __init__.
        dObject.__init__(self, *args, **kwargs)

        # Just in case this is used outside of the context of a bizobj
        if not hasattr(self, "superCursor") or self.superCursor is None:
            myBases = self.__class__.__bases__
            for base in myBases:
                # Find the first base class that doesn't have the 'autoPopulatePK'
                # attribute. Designate that class as the superCursor class.
                if hasattr(base, "fetchall"):
                    self.superCursor = base
                    break


    def _initProperties(self):
        # Holds the dict used for adding new blank records
        self._blank = {}
        # Writable version of the dbapi 'description' attribute
        self.descriptionClean = None
        # Last executed sql params
        self.lastParams = None
        # Column on which the result set is sorted
        self.sortColumn = ""
        # Order of the sorting. Should be either ASC, DESC or empty for no sort
        self.sortOrder = ""
        # Is the sort case-sensitive?
        self.sortCase = True
        # Holds the keys in the original, unsorted order for unsorting the dataset
        self.__unsortedRows = []
        # Holds the name of fields to be skipped when updating the backend, such
        # as calculated or derived fields, or fields that are otherwise not to be updated.
        self.__nonUpdateFields = None
        # User-editable list of non-updated fields
        self.nonUpdateFields = []
        # Flag that is set when the user explicitly sets the Key Field
        self._keyFieldSet = False
        # Cursor that manages this cursor's SQL. Default to self;
        # in some cases, such as a single bizobj managing several cursors,
        # it will be a separate object.
        self.sqlManager = self
        # Attribute that holds the data of the cursor
        self._records = dDataSet()
        # Attribute that holds the current row number
        self.__rownumber = -1
        # Data structure info
        self._dataStructure = None
        self._table = ""
        self._keyField = ""
        self._userSQL = None
        self._virtualFields = {}
        
        self._autoPopulatePK = True
        self._autoQuoteNames = True

        self.__tmpPK = -1       # temp PK value for new records.
        # Holds the data types for each field
        self._types = {}

        # Holds reference to auxiliary cursor that handles queries that
        # are not supposed to affect the record set.
        self.__auxCursor = None
        # Marks the cursor as an auxiliary cursor
        self._isAuxiliary = False

        # Reference to the object with backend-specific behaviors
        self.__backend = None

        # Reference to the bizobj that 'owns' this cursor, if any,
        self._bizobj = None

        # set properties for the SQL Builder functions
        self.clearSQL()
        self.hasSqlBuilder = True

        # props for building the auxiliary cursor
        self._cursorFactoryFunc = None
        self._cursorFactoryClass = None

        # mementos and new records, keyed on record object ids:
        self._mementos = {}
        self._newRecords = {}

        # Flag preference cursors so that they don't fill up the logs
        self._isPrefCursor = False

        self.initProperties()


    def setCursorFactory(self, func, cls):
        self._cursorFactoryFunc = func
        self._cursorFactoryClass = cls


    def clearSQL(self):
        self._fieldClause = ""
        self._fromClause = ""
        self._joinClause = ""
        self._whereClause = ""
        self._childFilterClause = ""
        self._groupByClause = ""
        self._orderByClause = ""
        self._limitClause = ""
        self._defaultLimit = 1000


    def getSortColumn(self):
        return self.sortColumn


    def getSortOrder(self):
        return self.sortOrder


    def getSortCase(self):
        return self.sortCase


    def pkExpression(self, rec=None):
        """Returns the PK expression for the passed record."""
        if rec is None:
            rec = self._records[self.RowNumber]
        if isinstance(self.KeyField, tuple):
            pk = tuple([rec[kk] for kk in self.KeyField])
        else:
            pk = rec[self.KeyField]
        return pk


    def _correctFieldType(self, field_val, field_name, _fromRequery=False):
        """Correct the type of the passed field_val, based on self.DataStructure.

        This is called by self.execute(), and contains code to convert all strings
        to unicode, as well as to correct any datatypes that don't match what
        self.DataStructure reports. The latter can happen with SQLite, for example,
        which only knows about a quite limited number of types.
        """
        ret = field_val
        showError = False
        if _fromRequery:
            pythonType = self._types.get(field_name, type(field_val))
            daboType = dabo.db.getDaboType(pythonType)

            if pythonType in (type(None), None) or isinstance(field_val, pythonType):
                # No conversion needed.
                return ret

            if pythonType in (unicode,):
                # Unicode conversion happens below.
                pass
            elif field_val is None:
                # Fields of any type can be None (NULL).
                return field_val
            elif pythonType in (datetime.datetime, ) and isinstance(field_val, basestring):
                ret = dates.getDateTimeFromString(field_val)
                if ret is None:
                    ret = field_val
                else:
                    return ret
            elif pythonType in (datetime.date,) and isinstance(field_val, basestring):
                ret = dates.getDateFromString(field_val)
                if ret is None:
                    ret = field_val
                else:
                    return ret
            elif _USE_DECIMAL and pythonType in (Decimal,):
                ds = self.DataStructure
                ret = None
                _field_val = field_val
                if type(field_val) in (float,):
                    # Can't convert to decimal directly from float
                    _field_val = str(_field_val)
                # Need to convert to the correct scale:
                scale = None
                for s in ds:
                    if s[0] == field_name:
                        if len(s) > 5:
                            scale = s[5]
                if scale is None:
                    scale = 2
                return Decimal(_field_val).quantize(Decimal("0.%s" % (scale * "0",)))
            else:
                try:
                    return pythonType(field_val)
                except Exception, e:
                    dabo.infoLog.write(_("_correctFieldType() failed for field: '%s'; value: '%s'; type: '%s'") %
                            (field_name, field_val, type(field_val)))

        # Do the unicode conversion last:
        if isinstance(field_val, str) and self._convertStrToUnicode:
            try:
                return unicode(field_val, self.Encoding)
            except UnicodeDecodeError, e:
                # Try some common encodings:
                ok = False
                for enc in ("utf-8", "latin-1", "iso-8859-1"):
                    if enc != self.Encoding:
                        try:
                            ret = unicode(field_val, enc)
                            ok = True
                        except UnicodeDecodeError:
                            continue
                        if ok:
                            # change self.Encoding and log the message
                            self.Encoding = enc
                            dabo.errorLog.write(_("Field %(fname)s: Incorrect unicode encoding set; using '%(enc)s' instead")
                                % {'fname':field_name, 'enc':enc} )
                            return ret
                            break
                else:
                    raise UnicodeDecodeError, e
#       elif isinstance(field_val, array.array):
#           # Usually blob data
#           ret = field_val.tostring()

            dabo.errorLog.write(_("%s couldn't be converted to %s (field %s)")
                    % (repr(field_val), pythonType, field_name))
        return ret


    def execute(self, sql, params=(), _fromRequery=False, errorClass=None):
        """ Execute the sql, and populate the DataSet if it is a select statement."""
        # The idea here is to let the super class do the actual work in
        # retrieving the data. However, many cursor classes can only return
        # row information as a list, not as a dictionary. This method will
        # detect that, and convert the results to a dictionary.
        if not isinstance(sql, unicode):
            sql = unicode(sql, self.Encoding)
        # Some backends, notably Firebird, require that fields be specially marked.
        sql = self.processFields(sql)

        try:
            if params is None or len(params) == 0:
                res = self.superCursor.execute(self, sql)
                if not self.IsPrefCursor:
                    dabo.dbActivityLog.write("SQL: %s" % sql.replace("\n", " "))
            else:
                res = self.superCursor.execute(self, sql, params)
                if not self.IsPrefCursor:
                    dabo.dbActivityLog.write("SQL: %s, PARAMS: %s" % (sql.replace("\n", " "), ", ".join(params)))
        except Exception, e:
            # There can be cases where errors are expected. In those cases, the 
            # calling routine will pass the class of the expected error, and will
            # handle it appropriately.
            if errorClass is not None and isinstance(e, errorClass):
                raise errorClass, e
            dabo.dbActivityLog.write("FAILED SQL: %s, PARAMS: %s" % (sql.replace("\n", " "), ", ".join(params)))
            # If this is due to a broken connection, let the user know.
            # Different backends have different messages, but they
            # should all contain the string 'connect' in them.
            if "connect" in str(e).lower():
                raise dException.ConnectionLostException, e
            if "access" in str(e).lower():
                raise dException.DBNoAccessException(e)
            else:
                raise dException.DBQueryException(e, sql)

        # Some backend programs do odd things to the description
        # This allows each backend to handle these quirks individually.
        self.BackendObject.massageDescription(self)

        if _fromRequery:
            self._storeFieldTypes()

        if sql.strip().split()[0].lower() not in  ("select", "pragma"):
            # No need to massage the data for DML commands
            self._records = dDataSet(tuple())
            return res

        try:
            _records = self.fetchall()
        except Exception, e:
            _records = tuple()
            dabo.errorLog.write("Error fetching records: %s" % e)

        if _records and not self.BackendObject._alreadyCorrectedFieldTypes:
            if isinstance(_records[0], (tuple, list)):
                # Need to convert each row to a Dict, since the backend didn't do it.
                tmpRows = []
                fldNames = [f[0] for f in self.FieldDescription]
                for row in _records:
                    dic = {}
                    for idx, fldName in enumerate(fldNames):
                        dic[fldName] = self._correctFieldType(field_val=row[idx],
                                field_name=fldName, _fromRequery=_fromRequery)
                    tmpRows.append(dic)
                _records = tmpRows
            else:
                # Already a DictCursor, but we still need to correct the field types.
                for row in _records:
                    for fld, val in row.items():
                        row[fld] = self._correctFieldType(field_val=val, field_name=fld,
                                _fromRequery=_fromRequery)

        self._records = dDataSet(_records)

        if self.RowCount > 0:
            self.RowNumber = max(0, self.RowNumber)
            maxrow = max(0, (self.RowCount-1) )
            self.RowNumber = min(self.RowNumber, maxrow)

        return res


    def executeSafe(self, sql):
        """Execute the passed SQL using an auxiliary cursor.
        This is considered 'safe', because it won't harm the contents
        of the main cursor.
        """
        ac = self.AuxCursor
        self._syncAuxProperties()
        return ac.execute(sql)


    def _syncAuxProperties(self):
        """Make sure that the auxiliary cursor has the same property
        settings as the main cursor.
        """
        if self._isAuxiliary:
            # Redundant!
            return
        ac = self.AuxCursor
        ac.AutoPopulatePK = self.AutoPopulatePK
        ac.AutoQuoteNames = self.AutoQuoteNames
        ac.DataStructure = self.DataStructure
        ac.IsPrefCursor = self.IsPrefCursor
        ac.KeyField = self.KeyField
        ac.Table = self.Table


    def requery(self, params=None):
        self._lastSQL = self.CurrentSQL
        self.lastParams = params
        self._savedStructureDescription = []
        
        if self.debug():
            print "CURSOR REQUERY - USERSQL: ", self.UserSQL, self

        self.execute(self.CurrentSQL, params, _fromRequery=True)

        # clear mementos and new record flags:
        self._mementos = {}
        self._newRecords = {}

        # Check for any derived fields that should not be included in
        # any updates.
        self.__setNonUpdateFields()

        # Clear the unsorted list, and then apply the current sort
        self.__unsortedRows = []
        if self.sortColumn:
            try:
                self.sort(self.sortColumn, self.sortOrder)
            except dException.NoRecordsException, e:
                # No big deal
                pass
        return True


    def _storeFieldTypes(self, target=None):
        """Stores the data type for each column in the result set."""
        if target is None:
            target = self
        target._types = {}
        for field in self.DataStructure:
            field_alias, field_type = field[0], field[1]
            target._types[field_alias] = dabo.db.getPythonType(field_type)


    def sort(self, col, dir=None, caseSensitive=True):
        """ Sort the result set on the specified column in the specified order.

        If the sort direction is not specified, sort() cycles among Ascending,
        Descending and no sort order.
        """
        currCol = self.sortColumn
        currOrd = self.sortOrder
        currCase = self.sortCase

        # Check to make sure that we have data
        if self.RowCount < 1:
            raise dException.NoRecordsException, _("No rows to sort.")

        # Make sure that the specified column is a column in the result set
        if not self._records[0].has_key(col) and not self.VirtualFields.has_key(col):
            raise dException.dException, _("Invalid column specified for sort: ") + col

        newCol = col
        if col == currCol:
            # Not changing the column; most likely they are flipping
            # the sort order.
            if (dir is None) or not dir:
                # They didn't specify the sort. Cycle through the sort orders
                if currOrd == "ASC":
                    newOrd = "DESC"
                elif currOrd == "DESC":
                    newOrd = ""
                else:
                    newOrd = "ASC"
            else:
                if dir.upper() in ("ASC", "DESC", ""):
                    newOrd = dir.upper()
                else:
                    raise dException.dException, _("Invalid Sort direction specified: ") + dir

        else:
            # Different column specified.
            if (dir is None) or not dir:
                # Start in ASC order
                newOrd = "ASC"
            else:
                if dir.upper() in ("ASC", "DESC", ""):
                    newOrd = dir.upper()
                else:
                    raise dException.dException, _("Invalid Sort direction specified: ") + dir

        self.__sortRows(newCol, newOrd, caseSensitive)
        # Save the current sort values
        self.sortColumn = newCol
        self.sortOrder = newOrd
        self.sortCase = caseSensitive


    def __sortRows(self, col, ord, caseSensitive):
        """ Sort the rows of the cursor.

        At this point, we know we have a valid column and order. We need to
        preserve the unsorted order if we haven't done that yet; then we sort
        the data according to the request.
        """
        kf = self.KeyField
        if not kf:
            return

        if not self.__unsortedRows:
            # Record the PK values
            for row in self._records:
                if self._compoundKey:
                    key = tuple([row[k] for k in kf])
                    self.__unsortedRows.append(key)
                else:
                    self.__unsortedRows.append(row[self.KeyField])

        # First, preserve the PK of the current row so that we can reset
        # the RowNumber property to point to the same row in the new order.
        try:
            if self._compoundKey:
                currRow = self._records[self.RowNumber]
                currRowKey = tuple([currRow[k] for k in kf])
            else:
                currRowKey = self._records[self.RowNumber][self.KeyField]
        except IndexError:
            # Row no longer exists, such as after a Requery that returns
            # fewer rows.
            currRowKey = None
        # Create the list to hold the rows for sorting
        sortList = []
        if not ord:
            # Restore the rows to their unsorted order
            for row in self._records:
                if self._compoundKey:
                    key = tuple([row[k] for k in kf])
                    sortList.append([self.__unsortedRows.index(key), row])
                else:
                    sortList.append([self.__unsortedRows.index(row[self.KeyField]), row])
        else:
            for row, rec in enumerate(self._records):
                sortList.append([self.getFieldVal(col, row), rec])
        # At this point we have a list consisting of lists. Each of these member
        # lists contain the sort value in the zeroth element, and the row as
        # the first element.
        # First, see if we are comparing strings
        compString = isinstance(sortList[0][0], basestring)
        sortfunc = None

        # can't compare NoneType to some types: sort None lower than anything else:
        def noneSort(vv, ww):
            xx, yy = vv[0], ww[0]
            if xx is None and yy is None:
                return 0
            elif xx is None and yy is not None:
                return -1
            elif xx is not None and yy is None:
                return 1
            else:
                return cmp(xx, yy)

        def caseInsensitiveSort(vv, ww):
            vv, ww = vv[0], ww[0]
            if vv is None:
                vv = ""
            if ww is None:
                ww = ""
            return cmp(vv.lower(), ww.lower())

        if compString and not caseSensitive:
            sortfunc = caseInsensitiveSort
        else:
            sortfunc = noneSort
        sortList.sort(sortfunc)

        # Unless DESC was specified as the sort order, we're done sorting
        if ord == "DESC":
            sortList.reverse()
        # Extract the rows into a new list, then convert them back to the _records tuple
        newRows = []
        for elem in sortList:
            newRows.append(elem[1])
        self._records = dDataSet(newRows)

        # restore the RowNumber
        if currRowKey:
            for ii in range(0, self.RowCount):
                row = self._records[ii]
                if self._compoundKey:
                    key = tuple([row[k] for k in kf])
                    found = (key == currRowKey)
                else:
                    found = row[self.KeyField] == currRowKey