Xbrowsing a Tree without loading in Memory

[reinaldocrespo]

Mr. Rao and Everyone on this forum;

Hi.

Here is an attempt to explain a feature that -IMO- xbrowse should be enhanced with.

The idea is to make xbrowse smart enough to be able to browse a master table and a transaction table the same way a tree is currently xbrowsed. Here is how it would work:

Let's imagine we are xbrowsing a customers table that happens to contain 1M records. Each record on this table directly translates to a row on the xbrowse. The xbrwose displays 6 columns. Each column relates directly to a field on the customers table. Let's call this table "Customers". The fields on this table are:

1. Customer ID -Primary-Unique Key
2. Customer Name
3. Customer address
4. Customer Telephone
5. Customer CellPhone
6. Customer Fax

Currently we create this browse roughly like this:

Code: Select all  Expand view

REDEFINE XBROWSE obrw ALIAS (customers)->cAlias ID 100 OF oDlg AUTOCOLS
 

Let's further suppose we have another table, namely "Sales" table. The table contains an entry for each purchase that each customer has ever done. The table is composed of records and each record is made up of 5 fields:

1. Customer ID - (Foreign Key into customers table)
2. SalesDate
3. ItemId
4. ItemDescrip
5. Amount

As you are xbrowsing the Customers table, at some keyboard or mouse action, imagine that all the sales records on the sales table are inserted for show on the xbrowse just underneath the customer in question and before the next customer. Obviously each customer record is not homogenous to the rows to be inserted on the xbrowse from the sales table. That behavior is known to many on this forum as xbrowsing a LinkList (or tree). As in:

Code: Select all  Expand view

oBrw:SetTree( ::buildTree() )
 

The problem with xbrowsing a tree is that the tree must be created in memory just before xbrowsing it. If the customers table is a large table, as it would be on any real world application, then it is impossible and impractical to load the complete table to memory on a linked list.

Currently, I simply popup a dialog with another xbrowse to display detail transactions. Other people here -I have seen- keep two separate xbrowses next to each other. That's ok, but not nearly as elegant as xbrowsing a tree.

If this is already possible with xbrowse, then would anyone here please show how? If it is not, as I'm assuming, then perhaps Mr. Rao would see this feature a worth implementing?


Reinaldo.

[Otto]

Reinaldo,
I understand the problem now.
What I suggested means you first have to make a JOIN and this takes as long or longer as reading all into memory.

Now thinking more about a solution I can imagine following

Make a relation between customer and sales

And then create a HiPer-SEEK index over all customers.
If you click on a customer you add the corresponding sales items to the HiPer seek index.
Then these records should be shown inside the xBrowse.
I never used HiPerSeek index and therefore I don’t know if you can use it with xBrowse and how HiPerSeek really works.

This in practice would be to add 2 databases in one xBrowse through relation and adding the needed indexes.
Do you think this could work.

Best regards,
Otto

[Otto]

Reinaldo,
in the meantime I got an answer from Patrizio.
Maybe OrdKeyAdd() is the solution. If this would work HiPer Seek index is not necessary.
I will do some tests this evening.
Best regards,
Otto

[reinaldocrespo]

That is an interesting idea to workaround the fact that xbrowse can't, at the moment, xbrowse two tables. I'm not sure I can start testing it right away, but it is an interesting idea worth trying.

Thank you very much!


Reinaldo.

[Otto]

Reinaldo,
I bet also on your place will be Christmas in a week.
Best regards,
Otto

[nageswaragunupudi]

Mr. Reinaldo

You can do this with appropriate replacements for the navigation codeblocks.

[fraxzi]

Dear Mr. RAO,

Can you please post an example?

Thanks!

[nageswaragunupudi]

Instead of the approach I suggested above, I am posting a sample using LinkList, but without reading the data into the memory. I have used states.dbf and customer.dbf in the fwh\samples folder as Parent and Child dbfs respectively.

I suggest you compile and test the program and then you may adopt to your dbfs. Please change the paths of the dbf files if needed. Also delete customer.cdx before execution.

Please note that this program assumes that other users are not adding or deleting any records during execution of the program.

I shall be glad to know the performance on larger tables.

Code: Select all  Expand view

#include "FiveWin.Ch"
#include "adodef.ch"
#include "ord.ch"
#include "xbrowse.ch"

//----------------------------------------------------------------------------//

REQUEST DBFCDX

//----------------------------------------------------------------------------//

function Main()

   OpenData()
   BrowseTree( MakeTree() )

return (0)

//----------------------------------------------------------------------------//

init procedure PrgInit

   SET DATE ITALIAN
   SET CENTURY ON
   SET TIME FORMAT TO "HH:MM:SS"
   SET EPOCH TO YEAR(DATE())-50

   SET DELETED ON
   SET EXCLUSIVE OFF

   RDDSETDEFAULT( "DBFCDX" )

   XbrNumFormat( 'E', .t. )
   SetKinetic( .f. )
   SetGetColorFocus()

return

//----------------------------------------------------------------------------//

exit procedure PrgExit

   SET RESOURCES TO

return

//----------------------------------------------------------------------------//

static function OpenData()

   field STATE

   if ! File( "C:\FWH\SAMPLES\CUSTOMER.CDX" )
      USE C:\FWH\SAMPLES\CUSTOMER EXCLUSIVE
      INDEX ON STATE TAG STATE
      USE
   endif

   USE C:\FWH\SAMPLES\STATES NEW ALIAS PARNT
   USE C:\FWH\SAMPLES\CUSTOMER NEW ALIAS CHILD
   SET ORDER TO TAG STATE
   GO TOP

return nil

//----------------------------------------------------------------------------//

static function MakeTree()

   local oTree
   local nRecs    := PARNT->( OrdKeyCount() )
   local n

   TREE oTree

   for n := 1 to nRecs

      TREEITEM STR( n, 2 ) CARGO { -n, .f. }

   next n

   ENDTREE

return oTree

//----------------------------------------------------------------------------//

static function BrowseTree( oTree )

   local oWnd, oBrw, oFont, oBold
   local aBmp     := { "c:\fwh\bitmaps\16x16\folder3.bmp", ;
                       "c:\fwh\bitmaps\16x16\folder.bmp",  ;
                       "c:\fwh\bitmaps\16array.bmp" }

   DEFINE FONT oFont NAME "TAHOMA" SIZE 0,-14
   DEFINE FONT oBold NAME "TAHOMA" SIZE 0,-14 BOLD

   DEFINE WINDOW oWnd
   oWnd:SetFont( oFont )

   @ 0, 0 XBROWSE oBrw OF oWnd CELL LINES

   WITH OBJECT oBrw
      :SetTree( oTree, aBmp, { || OnSkip( oBrw ) } )
      //
      WITH OBJECT :aCols[ 1 ]
         :cHeader       := "State/City"
         :bStrData      := ;
         :bEditValue    := { || If( oBrw:oTreeItem:nLevel == 1, PARNT->NAME, CHILD->CITY ) }
         :oDataFont     := { || If( oBrw:oTreeItem:nLevel == 1, oBold, oFont ) }
         :bBmpData      := { || BmpData( oBrw ) }
         :bLDClickData  := { || OnDblClick( oBrw ) }
      END
      //
      WITH OBJECT :AddCol()
         :cHeader    := "Name"
         :bEditValue := { || If( oBrw:oTreeItem:nLevel == 1, Space( 30 ), CHILD->FIRST - ( " " + CHILD->LAST ) ) }
      END
      //
      WITH OBJECT :AddCol()
         :cHeader       := "Salary"
         :bEditValue    := { || If( oBrw:oTreeItem:nLevel == 1, 0, CHILD->SALARY ) }
         :cEditPicture  := NumPict( 12, 2 )
      END
      //
      WITH OBJECT :AddCol()
         :cHeader       := "HireDate"
         :bEditValue    := { || If( oBrw:oTreeItem:nLevel == 1, CTOD( '' ), CHILD->HIREDATE ) }
         :cEditPicture  := "dd-mmm-yyyy"
      END
      //
      :lVThumbTrack     := .t.
      :lDisplayZeros    := .f.
      :bKeyChar         := { |nKey| If( nKey == 32 , OnDblClick( oBrw ), nil ) }
      //
      :CreateFromCode()
   END

   oWnd:oClient   := oBrw

   ACTIVATE WINDOW oWnd MAXIMIZED

return nil

//----------------------------------------------------------------------------//

static function OnSkip( oBrw )

   WITH OBJECT oBrw:oTreeItem
      if :nLevel == 1
         if :Cargo[ 1 ] < 0
            PARNT->( OrdKeyGoTo( - :Cargo[ 1 ] ) )
            :Cargo[ 1 ]    := PARNT->( RecNo() )
         else
            PARNT->( DbGoTo( :Cargo[ 1 ] ) )
         endif
      else
         CHILD->( DbGoTo( :Cargo ) )
      endif
   END

return nil

//----------------------------------------------------------------------------//

static function BmpData( oBrw )

   local nBmp  := 1

   WITH OBJECT oBrw:oTreeItem
      if :nLevel == 1
         if :oTree == nil
            nBmp  := If( :Cargo[ 2 ], 3, 2 )
         else
            nBmp  := If( :lOpened, 1, 2 )
         endif
      else
         nBmp     := 3
      endif
   END

return nBmp

//----------------------------------------------------------------------------//

static function OnDblClick( oBrw )

   local oItem    := oBrw:oTreeItem

   if oItem:nLevel == 1
      if oItem:oTree == nil .and. ! oItem:Cargo[ 2 ]
         AddSubTree( oItem )
         oItem:Cargo[ 2 ] := .t.
      endif
      if oItem:oTree != nil
         oItem:Toggle()
      endif
      oBrw:Refresh()
   endif

return nil


//----------------------------------------------------------------------------//

static function AddSubTree( oItem )

   local cSeek    := PARNT->CODE
   local nSaveRec := CHILD->( RecNo() )
   local oTree

   if CHILD->( DbSeek( cSeek ) )
      TREE oTree
      do while ! CHILD->( eof() ) .and. CHILD->STATE == cSeek
         TREEITEM CHILD->CITY CARGO CHILD->( RecNo() )
         CHILD->( DbSkip( 1 ) )
      enddo
      ENDTREE
      oItem:SetTree( oTree )
   endif

   CHILD->( DbGoTo( nSaveRec ) )

return nil

//----------------------------------------------------------------------------//
 

Screenshot:

[Silvio.Falconi]

Application
===========
Path and name: C:\Work\Errori\tre_nice\test.Exe (32 bits)
Size: 2,164,224 bytes
Compiler version: xHarbour build 1.2.1 Intl. (SimpLex) (Rev. 6715)
FiveWin Version: FWHX 12.03
Windows version: 6.1, Build 7600

Time from start: 0 hours 0 mins 5 secs
Error occurred at: 05-01-2013, 23:27:45
Error description: Error BASE/1068 Argument error: array access
Args:
[ 1] = N 267
[ 2] = N 2

Stack Calls
===========
Called from: test.prg => ONDBLCLICK( 191 )
Called from: test.prg => (b)BROWSETREE( 113 )
Called from: .\source\classes\XBROWSE.PRG => TXBROWSE:LDBLCLICK( 3559 )
Called from: => TWINDOW:HANDLEEVENT( 0 )
Called from: .\source\classes\CONTROL.PRG => TCONTROL:HANDLEEVENT( 1700 )
Called from: .\source\classes\XBROWSE.PRG => TXBROWSE:HANDLEEVENT( 11632 )
Called from: .\source\classes\WINDOW.PRG => _FWH( 3153 )
Called from: => WINRUN( 0 )
Called from: .\source\classes\WINDOW.PRG => TWINDOW:ACTIVATE( 980 )
Called from: test.prg => BROWSETREE( 142 )
Called from: test.prg => MAIN( 16 )

System

[nageswaragunupudi]

Thanks.
OnDblClick function needed a fix.
I revised the source posted above.
Please copy the new code and try again.

[Silvio.Falconi]

Nice Job...
Have you an Idea How make this : ( I saw on Italian app : easyfact of Danea Software)



a Widow or a dialog :to right there is the xbrowse and the left there is customer datas
I f I select a customer change also the customer data