Issues involving dirty cell, recalculation, and xvolatileresult in Libreoffice Calc

Hi All,

Recently, I am working on a Calc extension which is for dynamically showing
streaming financial data. I observed a conflict involving dirty cell,
recalculation, and xvolatilresult in Calc. From my view, it will be a
potential error or at least affect the performance of Calc if many changes
are made with the spreadsheet.

I am not very aware of the mechanism of dirty cell / auto recalculation with
XVolatileResult. From my observation, I guess that a XVolatileResult object
which is associated with a set of arguments can access to a list of
registered cells and update all of them if changes happen. If I change a
cell value which is part of the arguments, the formulas in the affected
(dirty) cells are recalculated automatically. This schema works well with
non-volatile result. However, with XVolatileResult, it raises some issues.
Briefly, the affected cells are still kept in the original list, and at the
same time register a new list associated with a new XVolatileResult object.
Both the original and new XVolatileResult objects update the dirty cells
when changes happen. These unnecessary and wrong callbacks definitely lower
Calc's performance.

I would like to describe the issues with a XVolatileResult example which is
listed in the Libreoffice SDK
(sdk->examples->DeveloperGuide->Spreadsheet->ExampleAddIn.java). You can
also check out the code from this link:
http://c-cpp.r3dcode.com/files/LibreOffice/3/4.5.2/sdk/odk/examples/DevelopersGuide/Spreadsheet/ExampleAddIn.java

Firstly, I added some trivial System.out.println() in this java file.
    public void addResultListener(com.sun.star.sheet.XResultListener
aListener)
    {
        aListeners.addElement( aListener );
        /*System.out.println("Adding a Listener. Total is " + aListeners.size());*/

        // immediately notify of initial value
        aListener.modified( getResult() );
    }

    public void removeResultListener(com.sun.star.sheet.XResultListener
aListener)
    {
        aListeners.removeElement( aListener );
        /*System.out.println("removing a listener. Total is " +
aListeners.size());*/
    }

    public void incrementValue()
    {
        ++nValue;
        com.sun.star.sheet.ResultEvent aEvent = getResult();
        /*System.out.println("In incrementValue " + aEvent.Value);*/

        java.util.Enumeration aEnum = aListeners.elements();
        while (aEnum.hasMoreElements())
           
((com.sun.star.sheet.XResultListener)aEnum.nextElement()).modified(
                aEvent);
        /*System.out.println("Done incrementValue " + aEvent.Value);*/
    }

class ExampleAddInThread extends Thread
{
    private java.util.Hashtable aCounters;

    public ExampleAddInThread( java.util.Hashtable aResults )
    {
        aCounters = aResults;
    }

    public void run()
    {
        while ( true )
        {
            try
            {
                sleep(500);
            }
            catch( InterruptedException exception )
            {
            }
            
            /*System.out.println("will call incrementValue");*/
            // increment all counters
            java.util.Enumeration aEnum = aCounters.elements();
            while (aEnum.hasMoreElements())
                ((ExampleAddInResult)aEnum.nextElement()).incrementValue();
        }
    }
}

public com.sun.star.sheet.XVolatileResult getCounter(String aName)
        {
           / *System.out.println("calling getCounter with " + aName);*/
            if ( aResults == null )
            {
                // create the table of results, and start a thread to
increment
                // all counters
                aResults = new java.util.Hashtable();
                ExampleAddInThread aThread = new ExampleAddInThread(
aResults );
                aThread.start();
            }

            ExampleAddInResult aResult = (ExampleAddInResult)
aResults.get(aName);
            if ( aResult == null )
            {
                aResult = new ExampleAddInResult(aName);
                aResults.put( aName, aResult );
            }
            return aResult;
        }

Secondly, I compiled this extension and installed it with LibreOffice. Then,
since I was working in Windows XP, I ran from command line console the
command: soffice.exe 2>&1 > log.txt . Afterwards, a LibreOffice window was
opened and the standard system out was written in log.txt.

Thirdly, I launched a spreadsheet and did the following cases:
(1) inserted "1" in A1 and "=counter(A1)" in A2. Here counter is an addin
function provided by ExampleAddIn.java. The corresponding log is
/calling getCounter with 1
Adding a Listener. Total is 1
will call incrementValue
In incrementValue 1
calling getCounter with 1
Done incrementValue 1
/

Everything is good. To avoid confusion, I deleted all the dynamic count
numbers in the log file.

(2) changed "1" to "2" in A1. The corresponding log is
/calling getCounter with 2
Adding a Listener. Total is 1
will call incrementValue
In incrementValue 2
calling getCounter with 2
Done incrementValue 2
*In incrementValue 1
calling getCounter with 2
Done incrementValue 1*/

This log showes two issues relative to argument "1" (a) should not update
the result with argument "1" because "1" is already replaced by "2". (b)
when updating the result with argument "1", should not calling getCounter of
argument "2" (unnecessary and wrong callback). Soft change (dirty cell and
auto recalculation) did not work well. In addition, is it good to remove the
XVolatileResult object associated with argument "1" from the hash table
since it is not used in the spreadsheet, but still run in the back?

(3) inserted "1" in B1 and "=counter(B1)" in B2. The corresponding log is
/calling getCounter with 1
will call incrementValue
In incrementValue 2
calling getCounter with 2
Done incrementValue 2
I*n incrementValue 1
calling getCounter with 2
calling getCounter with 1
Done incrementValue 1*/

This log continues to show the issues relative to argument "1":  the second
"calling getCounter with 2" should be stopped.

(4) deleted the formula ("=counter(A1)") in A2 . The corresponding log is
/will call incrementValue
In incrementValue 2
Done incrementValue 2
In incrementValue 1
calling getCounter with 1
Done incrementValue 1/

This log is reasonable now. two callbacks relative to argument '2" were
dropped. It showed that hard change works well with XVolatileResult.

(5) inserted the formula ("=counter(A1)") back in A2. The corresponding log
is
/will call incrementValue
In incrementValue 2
calling getCounter with 2
Done incrementValue 2
In incrementValue 1
calling getCounter with 1
Done incrementValue 1/

This log is reasonable too. The callback relative to argument "2" is
resumed. Hard change works well with XVolatileResult.

(6) close the spreadsheet window. The corresponding log is
/will call incrementValue
In incrementValue 2
removing a listener. Total is 0
removing a listener. Total is 0
Done incrementValue 2
In incrementValue 1
Done incrementValue 1/

From the above log, I found that the removal of listeners happened in the
middle of counts update. So there is a race condition.

In summary, from the above testing cases, I found that soft change (dirty
cell and auto recalculation) raises issues with XVolatileResult. Hard change
(delete and retype the formula) works well with XVolatileResult. There are a
potential race condition in the example (I submitted a bug with this issue).
Is it a good strategy that unused XVolatileResult objects are removed from
the hash table? Based on these conflicts, is it possible that LibreOffice
can make some changes on the soft change (dirty cell) notification process?

Since it is a rare scenario (seldom using XVolatileResult right now), please
let me know if you could not reproduce it or you would like to discuss more
in this topic. 

I will also submit a bug report in the LibreOffice website.

Thanks,
Wendi





--
View this message in context: 
http://nabble.documentfoundation.org/Issues-involving-dirty-cell-recalculation-and-xvolatileresult-in-Libreoffice-Calc-tp3998876.html
Sent from the Dev mailing list archive at Nabble.com.