TDatabase: Relating Tables
Posted: Fri May 10, 2019 5:32 pm
All of us are familiar with setting relationships between tables, both for one-to-one relationship and master-child relationship.
One-to-one relationship:
SET RELATION TO <exp> INTO <other_table_alias>
One-to-many (parent-child) relationship:
SET RELATION TO <exp> INTO <child_table_alias> SCOPED
All may not be very familiar with setting relationships between two database objects using TDatabase. The intent of this post is to explain this in detail with examples and comparing with RDD level relations.
For the purpose of this discussion, we use \fwh\samples\customer.dbf and \fwh\samples\states.dbf. We know that the field "STATE" in customer.dbf corresponds to the field "CODE" in the states.dbf.
1) ONE-TO-ONE RELATIONSHIP:
Every record in customer.dbf has one corresponding record in states.dbf where customer-state = states->code.
Syntax for RDD & TDatabase
This sample shows usage first for RDD and then TDatabase, achieving the same results:
ONE-TO-MANY RELATIONSHIP
(also called PARENT-CHILD/MASTER-CHILD)
Also called SCOPED RELATIONSHIP in DBF terminology.
In this case, one record in the master table corresponds to one or more records in the child table.
In our example, for every record in the states.dbf there are many records in the customer.dbf with the same state code. When we move from one record to another in the states.dbf, we want to retrieve only and all records in customer.dbf having the state code.
Syntax:
This sample shows usage first for RDD and then TDatabase, achieving the same results:
One-to-one relationship:
SET RELATION TO <exp> INTO <other_table_alias>
One-to-many (parent-child) relationship:
SET RELATION TO <exp> INTO <child_table_alias> SCOPED
All may not be very familiar with setting relationships between two database objects using TDatabase. The intent of this post is to explain this in detail with examples and comparing with RDD level relations.
For the purpose of this discussion, we use \fwh\samples\customer.dbf and \fwh\samples\states.dbf. We know that the field "STATE" in customer.dbf corresponds to the field "CODE" in the states.dbf.
1) ONE-TO-ONE RELATIONSHIP:
Every record in customer.dbf has one corresponding record in states.dbf where customer-state = states->code.
Syntax for RDD & TDatabase
Code: Select all
// RDD
SELECT CUSTOMER
SET RELATION TO STATE INTO STATES
// TDatabase
oCustomers:SetRelation( oStates, "STATE" )
Code: Select all
#include "fivewin.ch"
REQUEST DBFCDX
//----------------------------------------------------------------------------//
function Main()
RDDSETDEFAULT( "DBFCDX" )
SET DELETED ON
// Make sure the tables are indexed
FERASE( "STATES.CDX" )
USE STATES
FW_CdxCreate()
CLOSE STATES
FERASE( "CUSTOMER.CDX" )
USE CUSTOMER
FW_CdxCreate()
CLOSE CUSTOMER
//
// Now, the actual code
RelateRDD_OneToOne()
RelateTDatabase_OneToOne()
return nil
//----------------------------------------------------------------------------//
function RelateRDD_OneToOne()
field STATE
USE STATES NEW SHARED
SET ORDER TO TAG CODE
USE CUSTOMER NEW SHARED
SET RELATION TO STATE INTO STATES
GO TOP
XBROWSER "CUSTOMER" COLUMNS "First AS FirstName", "City", "STATES->NAME AS StateName"
CLOSE DATA
return nil
//----------------------------------------------------------------------------//
function RelateTDatabase_OneToOne()
local oStates, oCustomers
oStates := TDataBase():Open( nil, "STATES", "DBFCDX", .t. )
oStates:SetOrder( "CODE" )
oCustomers := TDataBase():Open( nil, "CUSTOMER", "DBFCDX", .t. )
oCustomers:SetRelation( oStates, "STATE" )
oCustomers:GoTop()
#if FW_VersionNo < 19040
oStates:load()
oCustomers:AddCol( "StateName", { || oStates:Load(), oStates:Name } )
#else
oCustomers:AddCol( "StateName", { || oStates:Name } ) // Simplified in FWH1904
#endif
XBROWSER oCustomers COLUMNS "First AS FirstName", "City", "StateName"
oCustomers:Close()
oStates:Close()
return nil
//----------------------------------------------------------------------------//
ONE-TO-MANY RELATIONSHIP
(also called PARENT-CHILD/MASTER-CHILD)
Also called SCOPED RELATIONSHIP in DBF terminology.
In this case, one record in the master table corresponds to one or more records in the child table.
In our example, for every record in the states.dbf there are many records in the customer.dbf with the same state code. When we move from one record to another in the states.dbf, we want to retrieve only and all records in customer.dbf having the state code.
Syntax:
Code: Select all
// RDD
SELECT STATES
SET RELATION TO CODE INTO CUSTOMER SCOPED
// TDatabase
oStates:SetRelation( oCustomers, "CODE", .T. ) // 3rd parameter .T. for scoped
Code: Select all
#include "fivewin.ch"
REQUEST DBFCDX
//----------------------------------------------------------------------------//
function Main()
RDDSETDEFAULT( "DBFCDX" )
SET DELETED ON
// Make sure the tables are indexed
FERASE( "STATES.CDX" )
USE STATES
FW_CdxCreate()
CLOSE STATES
FERASE( "CUSTOMER.CDX" )
USE CUSTOMER
FW_CdxCreate()
CLOSE CUSTOMER
//
// Now, the actual code
RelateRDD_Scoped()
RelateTDatabase_Scoped()
return nil
//----------------------------------------------------------------------------//
function RelateRDD_Scoped()
field CODE
local oDlg, oFont, oBrwStates, oBrwCust
USE CUSTOMER NEW SHARED
SET ORDER TO TAG STATE
USE STATES NEW SHARED
SET RELATION TO CODE INTO CUSTOMER SCOPED
GO TOP
DEFINE FONT oFont NAME "TAHOMA" SIZE 0,-14
DEFINE DIALOG oDlg SIZE 800,600 PIXEL TRUEPIXEL FONT oFont
@ 20,20 XBROWSE oBrwStates SIZE 250,-20 PIXEL OF oDlg ;
DATASOURCE "STATES" AUTOCOLS ;
CELL LINES NOBORDER
WITH OBJECT oBrwStates
:AutoFit()
:bChange := { || oBrwCust:GoTop() }
//
:CreateFromCode()
END
@ 20,250 XBROWSE oBrwCust SIZE -20,-20 PIXEL OF oDlg ;
DATASOURCE "CUSTOMER" COLUMNS "FIRST","CITY","STATE" ;
CELL LINES NOBORDER
WITH OBJECT oBrwCust
//
:CreateFromCode()
END
ACTIVATE DIALOG oDlg CENTERED
RELEASE FONT oFont
CLOSE DATA
return nil
//----------------------------------------------------------------------------//
function RelateTDatabase_Scoped()
local oStates, oCustomers
local oDlg, oFont, oBrwStates, oBrwCust
oCustomers := TDataBase():Open( nil, "CUSTOMER", "DBFCDX", .t. )
oCustomers:SetOrder( "STATE" )
oStates := TDataBase():Open( nil, "STATES", "DBFCDX", .t. )
oStates:SetRelation( oCustomers, "CODE", .T. )
oStates:GoTop()
oCustomers:Load()
DEFINE FONT oFont NAME "TAHOMA" SIZE 0,-14
DEFINE DIALOG oDlg SIZE 800,600 PIXEL TRUEPIXEL FONT oFont
@ 20,20 XBROWSE oBrwStates SIZE 250,-20 PIXEL OF oDlg ;
DATASOURCE oStates AUTOCOLS ;
CELL LINES NOBORDER
WITH OBJECT oBrwStates
:AutoFit()
:bChange := { || oBrwCust:GoTop() }
//
:CreateFromCode()
END
@ 20,250 XBROWSE oBrwCust SIZE -20,-20 PIXEL OF oDlg ;
DATASOURCE oCustomers COLUMNS "FIRST","CITY","STATE" ;
CELL LINES NOBORDER
WITH OBJECT oBrwCust
//
:CreateFromCode()
END
ACTIVATE DIALOG oDlg CENTERED
RELEASE FONT oFont
oStates:Close()
oCustomers:Close()
return nil
//----------------------------------------------------------------------------//