(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 Expand view RUN
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 Expand view RUN
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 Expand view RUN
#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 Expand view RUN
//----------------------------------------------------------------------------//
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 Expand view RUN
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 Expand view RUN
#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 Expand view RUN
//----------------------------------------------------------------------------//
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.
