== Introduction ==

NB: I wrote this recipe some months ago. I would now prefer the approach indicated in DataAwareControlsMixin.

The recipe DataAwareControlsMixin shows a way of using a mixin with one of the wxPython controls so that the composite object is capable of interacting with a data source, ie, so that the control is data-aware.

The present recipe illustrates how to decorate a database cursor so that it can interact with such data-aware controls.

== What Objects are Involved ==

No wxPython objects are directly involved in this recipe.

== Process Overview ==

The code below creates a database cursor, decorates the cursor to make it capable of interacting with a data-aware control, and then exercises the decorated version of the cursor. As the decorated cursor is being manipulated it can notify data-aware control "listeners" about aspects of its state; the decorate cursor can also interrogate its listeners for advice about their states.

A typical listener would be a text editing control. The text editor would update its contents when informed by the cursor that the cursor has been repositioned. The cursor can also inquire of the text editor whether its content has been changed by the user, to decide whether the new contents should be posted to the database.

The heart of the decorator is its __call__ method. Notice that it is only here that the decorator adds attributes to the underlying cursor that are used in co-operating with its associated data-aware controls. Some of the attributes are curried functions, so that all state information decorates the underlying cursor, rather than an instance of the decorator class.

== Special Concerns ==

The code shows how to decorate a MySQL cursor object. However, other cursors can probably be handled in similar ways.

At this point the code is capable of interacting with a cursor that represents selected columns from a table and the table name must be declared explicitly.

Unfortunately the decorator does not hide all means of manipulating the cursor, which implies that the decorated cursor can be manipulated without its data-aware control listeners being informed. On the other hand, decoration of this kind makes all of the abilities of the underlying cursor available to the user, and these abilities could be incorporated into the decorator as necessary. In fact, one feature of this design is that, in some cases, the decorator can be subclassed without making the result very ugly.

The decorated cursor maintains a copy of the "current" record as part of the decoration--which is unfortunate. Overall, the database handling is somewhat naïve.

When this recipe is used with MySQL, in particular, the table that is accessed must have a primary index for the replaceRow method to work. (Otherwise, this method will create an additional row without deleting the original.)

== Code Sample ==

{{{
#!python
from types import FloatType,IntType,LongType,StringType

class decorateCursor:
    def replaceRow(self,cursor):
        cursor . seek ( -1, 1 )
        listenerValues={}
        for listener in cursor.listeners:
            fieldName,fieldValue=listener.reportValue()
            if fieldName :
                listenerValues[fieldName]=fieldValue
        if cursor . row :
            for fieldName in cursor.fieldNames:
                if fieldName in listenerValues:
                    cursor.row[cursor.fieldNames.index(fieldName)]=listenerValues[fieldName]
        sqlStatement="replace %s(%s) values(%s)" %(cursor.tableName,','.join(cursor.fieldNames),'%s')
        fieldValues=[]
        for fieldName in cursor.fieldNames:
            fieldValue=cursor.field(fieldName)
            if type(fieldValue) in [FloatType,IntType,LongType]:
                fieldValues.append(str(fieldValue))
            elif type(fieldValue)==StringType:
                fieldValues.append("'%s'"%fieldValue)
            elif fieldValue==None:
                fieldValues.append('NULL')
            else:
                print fieldValue,type(fieldValue)
        sqlStatement=sqlStatement%','.join(fieldValues)
        cursor.execute(sqlStatement)

    def getFieldNames(self, cursor):
        return [fieldVector[0] for fieldVector in cursor.description]

    def addListener(self,cursor,listener):
        if not listener in cursor.listeners:
            cursor.listeners.append(listener)
            listener.notify()

    def informListeners(self,cursor):
        if cursor.informing:
            for listener in cursor.listeners:
                listener.notify()

    def fetchRow(self,cursor):
        cursor.row=list(cursor.fetchone())
        cursor.informListeners ( )
        
    def gotoBOF ( self, cursor ) :
        cursor . seek ( 0 )
        cursor . fetchRow ( )

    def gotoEOF ( self, cursor ) :
        cursor . seek ( cursor . rowcount - 1 )
        cursor . fetchRow ( )

    def gotoNext ( self, cursor ) :
        if cursor . rownumber < cursor . rowcount :
            cursor . fetchRow ( )

    def gotoPrevious ( self, cursor ) :
        if cursor . rownumber > 1 :
            cursor . seek ( -2, 1 )
            cursor . fetchRow ( )

    def field(self,cursor,whichField):
        if cursor . row == None :
            return None
        if type(whichField)==IntType:
            return cursor.row[whichField]
        elif type(whichField)==StringType:
            return cursor.row[cursor.fieldNames.index(whichField)]
        else:
            raise "unrecognised field type (must be either string or integer)"

    def anyListenerChanged(self,cursor):
        return any(listener.changed() for listener in cursor.listeners)

    class simpleCurry:
        def __init__(self,func,cursor):
            self.func=func
            self.cursor=cursor

        def __call__(self,*arg):
            return self.func(self.cursor, *arg)

    def __call__(self,cursor,tableName,informing=1,readonly=0):
        cursor.row = None
        cursor.tableName=tableName
        cursor.readonly=readonly
        cursor.listeners=[]
        cursor.informing=informing
        cursor.addListener=self.simpleCurry(self.addListener,cursor)
        cursor.informListeners=self.simpleCurry(self.informListeners,cursor)
        cursor.fetchRow=self.simpleCurry(self.fetchRow,cursor)
        cursor . gotoBOF = self.simpleCurry ( self . gotoBOF, cursor )
        cursor . gotoEOF = self.simpleCurry ( self . gotoEOF, cursor )
        cursor . gotoNext = self . simpleCurry ( self . gotoNext, cursor )
        cursor . gotoPrevious = self . simpleCurry ( self . gotoPrevious, cursor )
        cursor.field=self.simpleCurry(self.field,cursor)
        cursor.anyListenerChanged=self.simpleCurry(self.anyListenerChanged,cursor)
        cursor.getFieldNames=self.simpleCurry(self.getFieldNames, cursor )
        cursor.fieldNames = cursor.getFieldNames ( )
        cursor.replaceRow=self.simpleCurry(self.replaceRow,cursor)
        return cursor

if __name__ == "__main__" :

    class dummyDataAwareControl:
        def notify(self):
            pass

        def changed(self):
            return 1

        def reportValue(self):
            return 'date','2010-01-01'

    listener1=dummyDataAwareControl()

    from MySQLdb import Connect

    connection=Connect(db='test')
    cursor=connection.cursor()
    cursor.execute('select * from logger')

    c=decorateCursor()(cursor,'logger')

    c.addListener(listener1)

    while c.rownumber < c.rowcount:
        cursor.fetchRow()
        for fieldName in c.fieldNames:
            fieldValue=c.field(fieldName)
            if type(fieldValue)==StringType:
                print "%s: '%s'"% ( fieldName, fieldValue, ), 
            else:
                print fieldName, fieldValue,
        print

    if c.anyListenerChanged():
        c.replaceRow()


}}}

=== Comments ===

I plan to add yet another recipe that illustrates how to make this decorator work with data-aware controls created using a mixin (in another recipe). Any comment or concern, please write [[Bill Bell]].