FiveTech Software tech support forums

Posted: **Thu Jul 21, 2016 7:52 am**

On many occassions we need to create parent-child relationships between tables and browse them. In case of RDD, using scoped relations make this very easy.

(cParentAlias)->( OrdSetRelation( cChild, bRelation, cRelation ) )
or
SET RELATION TO <expr> INTO <child> SCOPED.
This relation is equivalent to setting Scope on the child. This can also be achieved by setting filter on the child. We need to keep updating the scope or filter everytime we move the record pointer of the parent.

In case of ADO, the "DATA SHAPING" feature makes this job a *breeze*. This Data Shaping is a feature of ADO but not a native feature of any SQL. So, there is no way to achieve the same result using normal SQL.

RowSet object has methods to do this job extremely easy and as easy as ADO datashaping and even more flexible than that.

We agree that there are definitely othe ways of doing this and we all had done this many times. But this feature takes away all the tedium, makes the process very simple, less error prone, stable with easy to maintain code.

The entire process requires usage of only two methods and one data:

1) oParent:AddChild( ... ) // establish parent child relationship
2) oParent:SyncChild() // Call whenever we want to sync the child with parent.

DATA oParent:oChild //refers to the child rowset.

//Example usage in Parent-child browse:

Code: Select all

oParentBrw:bChange := { || oParentRs:SyncChild(), oChildBrw:Refresh() }

Note: We did not make SyncChild() automatic when parent's record pointer is moved for performance reasons. we need to call the SyncChild() method explicitly.

This is all the work we need to do as a programmer.

Two alternative methods are provided, similar to DataShaping of ADO.

Method-1:
Read only that subset of child table's records from the server that match the defined relationship and keep reading and refreshing whenever the parent skips and resync is called.

PROS: Not necessary to read entire child table to start with.
CONS: Each resync takes time to read the new data from the server.

Method-2:

Read the entire (or expected portion of ) child table into a rowset.
Filter only the records respecting the relation when oParent:SyncChild() is called.

PROS: Syncing the child is fast.
CONS: Initially the entire (or a major portion) of the child table needs to be read.

On localhost and local area networks and high speed WANS both offer the same performance.

Specifying the parent and child relationship: We show examples here based on two tables states and customer with structures same as fwh\samples\customer.dbf and fwh\samples\states.dbf. Field "state" in customer table is related to field "code" in states table.

METHOD-1

First open rowset of parent:
oStates := oCn:RowSet( "states" )

There are 3 alternative syntaxes:
a) oStates:AddChild( "select * from customer where state = ?", { "code" } )
b) oStates:AddChild( "select * from customer where state = states.code" )
c) This applies where a forein-key relationship is defined when creating customer table (explained later)
oStates:AddChild( "customer" )
or oStates:AddChile( "select <fieldlist> from customer" )

METHOD-2:

Open both rowsets of parent and child>
oStates := oCn:RowSet( "states" )
oCust := oCn:RowSet( "customer" )

Alternative syntaxes:
a) oStates:AddChild( oCust, "state == ?", { "code" } )
b) oStates:AddChild( oCust, "state == states.code" )
c) oStates:OrdSetRelation( oCust, "state == states.code" ) // comaptibility with DBF syntax
d) This applies where a forein-key relationship is defined when creating customer table (explained later)
oStates:AddChild( oCust )

Notes:
i) Alternate syntaxes (a) and (b) in Method-1 are functionally identical. Same way alternative syntaxes (a),(b) and (c) in Method-2 are identical. Which syntax to use depends only on the individual taste of the programmer.
ii) In Method-1, the relation uses SQL syntax of Where clause. In Method-2, the condition uses DBF syntax for filters.
iii) Present limitation is that one parent can have only one child. But a child can have another child and so on.
iv) Calling AddChild() again with another table/rowset cancels previous relationship and sets a new child and new relationship

FOREIGN KEYS:
Defining foreign key relationships is an essential part of good database design, next only to having primary keys for every table.
Connection Object's CreateTable() method makes it very easy to define foreign key relationships.

Code: Select all

oCn:CreateTable( "customer", { ;
      { "first", 'C', 20, 0 }, ;
      // other fields
      { "state", "REFERENCES states(code)" }, ;
      // remaining fields } )

It is desirable to define this relationship at the time of creation of the table than adding the constraint later, because column definition should exactly match the column definition in the parent table. CreateTable() method takes the trouble of picking up the column definition from the referred table and incorporate in the child table so that any errors are avoided.

When a FK relationship is already defined, as is the case with all well designed databases, setting parent child relationships is very easy.

Eg:
oStates:AddChild( "customer" ) // customer table
oStates:AddChild( oCust ) // RowSet where customer table is the only table or main table.

RowSet object searches the database for the relevant FK relationship and builds appropriate Where clause or Filter condition. This approach is the best.

Some samples:
Main function:

Code: Select all

#include "fivewin.ch"

static ocn

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

function Main()

   local oStates

   if ConnectToServer()

      oStates  := oCn:RowSet( "states" )
      oStates:AddChild( "select * from customer where state = states.code" )  // syntax 1

      BrowseParentChild( oStates )

      ocn:close()

   endif

return nil

Supporting functions for connecting to server and browsing the results

Code: Select all

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

function ConnectToServer()

   local cHost      := "localhost"
   local cUser      := "root"
   local cPassword  := nil
   local cDb        := nil

   EDITVARS cHost, cUser, cPassword, cDB

   FWCONNECT oCn HOST cHost USER cUser PASSWORD cPassword DATABASE cDB

   if oCn == nil
      ? "Connect Fail"
      return .f.
   endif

   if Empty( oCn:CurrentDB() )
      oCn:CreateDB( "fwh" )
      oCn:SelectDB( "fwh" )
   endif

   if ! oCn:TableExists( "customer" )
      oCn:ImportFromDBF( "\fwh\samples\customer.dbf" )
   endif
   if ! oCn:TableExists( "states" )
      oCn:ImportFromDBF( "\fwh\samples\states.dbf" )
   endif

return .t.

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

function BrowseParentChild( oStates )

   local oBrwParent, oBrwChild, oDlg, oFont

   DEFINE FONT oFont NAME "TAHOMA" SIZE 0,-14
   DEFINE DIALOG oDlg SIZE 900,400 PIXEL TRUEPIXEL ;
      FONT oFont ;
      TITLE "FWH MYSQL PARENT CHILD ROWSET"

   @ 20,20 XBROWSE oBrwParent SIZE 250,-20 PIXEL OF oDlg ;
      DATASOURCE oStates ;
      COLUMNS "Code", "Name" ;
      CELL LINES NOBORDER

   WITH OBJECT oBrwParent
      :bChange    := { || oStates:SyncChild(), oBrwChild:GoTop(), oBrwChild:Refresh() }
      :CreateFromCode()
   END

   @ 20,270 XBROWSE oBrwChild SIZE -20,-20 PIXEL OF oDlg ;
      DATASOURCE oStates:oChild ;
      COLUMNS "First", "City", "State", "Salary" ;
      CELL LINES NOBORDER

   WITH OBJECT oBrwChild
      :CreateFromCode()
   END

   ACTIVATE DIALOG oDlg CENTERED
   RELEASE FONT oFont

return nil

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

For the sake of record let us have here all alternatives

Code: Select all

      oStates  := oCn:RowSet( "states" )

      // MEHOD-1
      oStates:AddChild( "select * from customer where state = states.code" )  // syntax 1
      //oStates:AddChild( "select * from customer where state = ?", { "code" } ) // syntax 2

      // METHOD-2
      //oStates:AddChild( oCn:RowSet( "customer" ), "state = states.code" )  // syntax 1
      //oStates:AddChild( oCn:RowSet( "customer" ), "state = ?", { "code" } )  // syntax 2

Result:

Next let us look at making parent child relationships between different subsets of the same table.
Here is a table of codes. We want to set relationship between different levels of codes:

Main Program:

Code: Select all

#include "fivewin.ch"

static ocn

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

function Main()

   local oCodes

   if ConnectToServer()

      oCodes   := oCn:RowSet( "select * from testcodes where level = 1" )
      oCodes:AddChild( "select * from testcodes where level = 2 and substring( code, 1, 1 ) = ?", { "code" } )
      oCodes:oChild:AddChild( "select * from testcodes where level = 3 and substring( code, 1, 2 ) = ?", { "code" } )

      BrowseCodes( oCodes )

      ocn:close()
   endif

return nil

Supporting functions for connecting to server, browse, etc

Code: Select all

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

function ConnectToServer()

   local cHost      := "localhost"
   local cUser      := "root"
   local cPassword  := ""
   local cDb        := ""

   EDITVARS cHost, cUser, cPassword, cDB

   FWCONNECT oCn HOST cHost USER cUser PASSWORD cPassword DATABASE cDB

   if oCn == nil
      ? "Connect Fail"
      return .f.
   endif

   if Empty( oCn:CurrentDB() )
      oCn:CreateDB( "fwh" )
      oCn:SelectDB( "fwh" )
   endif

   CreateTable()

return .t.

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

function BrowseCodes( oCodes )

   local oDlg, oFont, oBrwCodes, oBrwGroups, oBrwSubGr

   DEFINE FONT oFont NAME "TAHOMA" SIZE 0,-14
   DEFINE DIALOG oDlg SIZE 790,250 PIXEL TRUEPIXEL ;
      FONT oFont ;
      TITLE "FWH MYSQL PARENT CHILD GRAND CHILD ROWSET"

   @ 20,20 XBROWSE oBrwCodes SIZE 250,-20 PIXEL OF oDlg ;
      DATASOURCE oCodes ;
      COLUMNS "Code", "Name" ;
      CELL LINES NOBORDER

   WITH OBJECT oBrwCodes
      :SetGroupHeader( "MAJOR CODES" )
      :bChange    := { || oCodes:SyncChild(), oBrwGroups:GoTop(), oBrwGroups:Refresh() }
      :CreateFromCode()
   END

   @ 20,270 XBROWSE oBrwGroups SIZE 250,-20 PIXEL OF oDlg ;
      DATASOURCE oCodes:oChild ;
      COLUMNS "Code", "Name" ;
      CELL LINES NOBORDER

   WITH OBJECT oBrwGroups
      :SetGroupHeader( "GROUP CODES" )
      :bChange    := { || oCodes:oChild:SyncChild(), oBrwSubGr:GoTop(), oBrwSubGr:Refresh() }
      :CreateFromCode()
   END

   @ 20,520 XBROWSE oBrwSubGr SIZE 250,-20 PIXEL OF oDlg ;
      DATASOURCE oCodes:oChild:oChild ;
      COLUMNS "Code", "Name" ;
      CELL LINES NOBORDER

   WITH OBJECT oBrwSubGr
      :SetGroupHeader( "SUB GROUPS" )
      :CreateFromCode()
   END

   ACTIVATE DIALOG oDlg CENTERED
   RELEASE FONT oFont

return nil

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

function CreateTable()

   local aData := {}
   local i,j,k,c,c2

   for i := ASC('A') to ASC('E')
      c  := CHR(i)
      AAdd( aData, { c, 1, RandomStr() } )
      for j := ASC('A') to ASC('E')
         c2 := c + CHR( j )
         AAdd( aData, { c2, 2, RandomStr() } )
         for k := ASC('A') to ASC('E')
            AAdd( aData, { c2 + CHR(k), 3, RandomStr() } )
         next
      next
   next

   oCn:DropTable( "testcodes" )
   oCn:CreateTable( "testcodes", { ;
      { "code",  "C",  3, 0, "PRI" }, ;
      { "level", "N",  1, 0 }, ;
      { "name",  "C", 20, 0 } }, .f. )

   oCn:Insert( "testcodes", "code,level,name", aData )

return nil

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

function RandomStr()

   local cStr  := Chr( HB_RandomInt( ASC( 'A' ), ASC( 'Z' ) ) )
   local nLen  := HB_RandomInt( 5, 19 )
   local n, c

   for n := 1 to nLen
      if HB_RandomInt( 1, 10 ) == 3
         c  := ' '
      else
         c  := Chr( HB_RandomInt( Asc( 'a' ), Asc( 'z' ) ) )
      endif
      cStr  += c
   next

return cStr

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

Result:

Note: This feature is available in the latest version about to be released soon.

Posted: **Wed Jun 20, 2018 6:10 am**

Mr. Rao,

How to do it with one-parent having two-children.. ?

I understand having a parent->a child->a grand child relationship.. but how to do it properly with single parent having two or more children using Mariadb RowSets?

Posted: **Wed Sep 19, 2018 5:56 pm**

fraxzi wrote:Mr. Rao,

How to do it with one-parent having two-children.. ?

I understand having a parent->a child->a grand child relationship.. but how to do it properly with single parent having two or more children using Mariadb RowSets?

+1

Posted: **Thu Sep 20, 2018 8:13 am**

Hi Mr. Rao,

Is it possible?

Thanks

Posted: **Thu Sep 20, 2018 8:39 am**

We provided this feature for single child rowset only.

If you want to manage two or more child rowsets you need to do it yourself, the same way as you would with tmysql or tdolphin.

Open three tables ( master and two child ) separately and setup browses for the three rowsets. In the browse of the master rowset, set oBrw:bChange to apply suitable filters on the two child rowsets and refresh the two child browses.

Posted: **Thu Sep 20, 2018 8:58 am**

nageswaragunupudi wrote:We provided this feature for single child rowset only.

If you want to manage two or more child rowsets you need to do it yourself, the same way as you would with tmysql or tdolphin.

Open three tables ( master and two child ) separately and setup browses for the three rowsets. In the browse of the master rowset, set oBrw:bChange to apply suitable filters on the two child rowsets and refresh the two child browses.

Can you please give an example?

Thank you.

Posted: **Thu Sep 20, 2018 5:25 pm**

Code: Select all

// parent and 2 childs

#include "fivewin.ch"

function Main()

   local oCn
   local oRsState, oRsCity, oRsCust
   local oBrwState, oBrwCity, oBrwCust
   local oDlg, oFont

   oCn   := FW_DemoDB()

   MsgRun( "Reading tables", "Please Wait...", ;
   <||
      oRsState := oCn:RowSet( "SELECT * FROM states ORDER BY name" )
      oRsCity  := oCn:RowSet( "SELECT id, state, city FROM customer ORDER BY state, city" )
      oRsCust  := oCn:RowSet( "SELECT id, state, CONCAT_WS( ', ', first, last ) AS Name FROM customer ORDER by state,Name" )
      oRsCity:SetFilter( "STATE = ?", { oRsState:code } )
      oRsCust:SetFilter( "STATE = ?", { oRsState:code } )
      return nil
   > )

   DEFINE FONT oFont NAME "Segoe UI" SIZE 0,-10 PTS
   DEFINE DIALOG oDlg SIZE 700,600 PIXEL TRUEPIXEL FONT oFont ;
      TITLE "MARIADB - PARENT WITH TWO CHILD TABLES"
   RELEASE FONT oFont

   @ 20,20 XBROWSE oBrwState SIZE 300,-20 PIXEL OF oDlg DATASOURCE oRsState ;
      COLUMNS "NAME","CODE" CELL LINES NOBORDER

   WITH OBJECT oBrwState
      :SetGroupHeader( "PARENT", 1, 2 )
      :nStretchCol   := 1
      :lHScroll      := .f.
      :bChange       := <||
                        oRsCity:ReFilter( { oRsState:code } )
                        oRsCust:ReFilter( { oRsState:code } )
                        oBrwCity:Refresh()
                        oBrwCust:Refresh()
                        return nil
                        >
      :CreateFromCode()
   END

   @ 20,300 XBROWSE oBrwCity SIZE 400,260 PIXEL OF oDlg DATASOURCE oRsCity ;
      COLUMNS "STATE","CITY" CELL LINES NOBORDER

   WITH OBJECT oBrwCity
      :SetGroupHeader( "CHILD-1", 1, 2 )
      :nStretchCol   := 2
      :lHScroll      := .f.
      :CreateFromCode()
   END

   @ 280,300 XBROWSE oBrwCust SIZE 400,-20 PIXEL OF oDlg DATASOURCE oRsCust ;
      COLUMNS "STATE","NAME" CELL LINES NOBORDER

   WITH OBJECT oBrwCust
      :SetGroupHeader( "CHILD-2", 1, 2 )
      :nStretchCol   := 2
      :lHScroll      := .f.
      :CreateFromCode()
   END

   ACTIVATE DIALOG oDlg CENTERED
   oCn:Close()

return nil

Posted: **Thu Sep 20, 2018 8:52 pm**

Thank you Mr. Rao.

Posted: **Tue Sep 25, 2018 12:41 am**

Hi Rao,

Thanks for the sample .. like the old-ways.
I wish in the future there would be like oRs:AddChild( 'table_1' ), oRs:AddChild( 'table_2' ) ...

Anyhow, it's the same results with your sample.

Posted: **Tue Sep 25, 2018 6:03 am**

fraxzi wrote:Hi Rao,

Thanks for the sample .. like the old-ways.
I wish in the future there would be like oRs:AddChild( 'table_1' ), oRs:AddChild( 'table_2' ) ...

Anyhow, it's the same results with your sample.

Old ways. Still, the power of filters in fwhmarialib makes this very fast which is not possible with other libraries.

Posted: **Wed Sep 26, 2018 12:13 am**

Old ways. Still, the power of filters in fwhmarialib makes this very fast which is not possible with other libraries.

Indeed! amazing power of fwhmarialib filters ... this is now my new BFF ...

Great job Mr. Rao!! ... How to be you ...

Posted: **Tue Oct 16, 2018 6:17 am**

Hi Mr. Rao,

if I do oParent:Save() ... will it save oParent:oChild and oParent:oChild:oChild as well??

Posted: **Tue Oct 16, 2018 10:25 am**

fraxzi wrote:Hi Mr. Rao,

if I do oParent:Save() ... will it save oParent:oChild and oParent:oChild:oChild as well??

No.

FiveTech Software tech support forums

FWH: Parent Child RowSets (MySql/MariaDB)

FWH: Parent Child RowSets (MySql/MariaDB)

Re: FWH: Parent Child RowSets (MySql/MariaDB)

Re: FWH: Parent Child RowSets (MySql/MariaDB)

Re: FWH: Parent Child RowSets (MySql/MariaDB)

Re: FWH: Parent Child RowSets (MySql/MariaDB)

Re: FWH: Parent Child RowSets (MySql/MariaDB)

Re: FWH: Parent Child RowSets (MySql/MariaDB)

Re: FWH: Parent Child RowSets (MySql/MariaDB)

Re: FWH: Parent Child RowSets (MySql/MariaDB)

Re: FWH: Parent Child RowSets (MySql/MariaDB)

Re: FWH: Parent Child RowSets (MySql/MariaDB)

Re: FWH: Parent Child RowSets (MySql/MariaDB)

Re: FWH: Parent Child RowSets (MySql/MariaDB)