James Bott wrote:Marc,
Are you saying there could be 50 children of the parent, and each one of those could have 50 children? That would be 50*50 = 2,500 children of the top parent. Then I assume there could be another layer that could also have 50 children. That would be 50 * 2,500 = 125,000, Wow! And that is just for one parent!
Creating an Excel file containing all that data is extremely inefficient. That is why relational databases were born.
If it was my client, the first thing I would be asking is what are you trying to accomplish with this? It seems it would be much easier and more efficient to write a program to provide whatever answer the client needs (without storing hundreds of thousands of records, of mostly redundant data).
Looking at hundreds of thousands of rows in Excel would make my eyes bleed...
#include "fivewin.ch"
REQUEST DBFCDX
//----------------------------------------------------------------------------//
function Main()
field COUNTRY,REGION
local aData
CreateTables()
USE REGION NEW VIA "DBFCDX"
SET ORDER TO TAG CODE
GO TOP
USE COUNTRY NEW VIA "DBFCDX"
SET ORDER TO TAG CODE
SET RELATION TO REGION INTO REGION
GO TOP
USE CITY NEW VIA "DBFCDX"
SET ORDER TO TAG COUNTRY
SET RELATION TO COUNTRY INTO COUNTRY
GO TOP
SELECT CITY
FW_DbfToExcel( { "REGION->REGION AS REGION","COUNTRY->COUNTRY AS CONTRY", "CITY", "CODE AS AIRPORT" } )
return nil
//----------------------------------------------------------------------------//
static function CreateTables()
local aRegion := { { "EU", "Europe" }, { "NA", "North America" } }
local aCountry := { { "SP", "EU", "Spain" }, { "IT", "EU", "Italy" }, ;
{ "US", "NA", "USA" }, { "CA", "NA", "Canada" } }
local aCity := { { "MAD", "SP", "Madrid" }, { "AGP", "SP", "Málaga" }, ;
{ "TRN", "IT", "Turin" }, { "VCE", "IT", "Venice" }, ;
{ "JFK", "US", "New York"},{ "BOS", "US", "Boston" }, ;
{ "YOW", "CA", "Ottawa" }, { "YUL", "CA", "Montriel" } }
DBCREATE( "REGION.DBF", {{"CODE","C",2,0},{"REGION","C",15,0}}, "DBFCDX", .T., "TMP" )
FW_ArrayToDbf( aRegion )
FW_CdxCreate()
CLOSE TMP
DBCREATE( "COUNTRY.DBF", {{"CODE","C",2,0},{"REGION","C",2,0},{"COUNTRY","C",15,0}}, "DBFCDX", .T., "TMP" )
FW_ArrayToDbf( aCountry )
FW_CdxCreate()
CLOSE TMP
DBCREATE( "CITY.DBF", {{"CODE","C",3,0},{"COUNTRY","C",2,0},{"CITY","C",15,0}}, "DBFCDX", .T., "TMP" )
FW_ArrayToDbf( aCity )
FW_CdxCreate()
CLOSE TMP
return nil
//----------------------------------------------------------------------------//
Marc Venken wrote:At this point (Current version) it is still so that we can only have 1 parent to 1 child relation and not 1 parent to 2/3 childs ?
#include "fivewin.ch"
REQUEST DBFCDX
//----------------------------------------------------------------------------//
function Main()
field OUT,FTO
local aData := {}
local aRow
CreateTables()
USE MAIN NEW SHARED VIA "DBFCDX"
SET ORDER TO TAG NAME
GO TOP
do while !MAIN->( Eof() )
aRow := { MAIN->NAME, MAIN->OUT }
GetConnections( aData, aRow )
MAIN->( DBSKIP( 1 ) )
enddo
XBROWSER aData
return nil
//----------------------------------------------------------------------------//
static function GetConnections( aData, aRow )
local cSeek := ATail( aRow )
local a
local cAlias
USE CHILD NEW ALIAS ( cAlias := cGetNewAlias( "CH" ) ) SHARED VIA "DBFCDX"
SET ORDER TO TAG FROM
if ( cAlias )->( DBSEEK( cSeek ) )
do while ( cAlias )->FROM == cSeek
a := AClone( aRow )
AAdd( a, ( cAlias )->FTO )
GetConnections( aData, a )
( cAlias )->( DBSKIP( 1 ))
enddo
else
AAdd( aData, aRow )
endif
( cAlias )->( DBCLOSEAREA() )
return aRow
//----------------------------------------------------------------------------//
static function CreateTables()
local aMain := {{"INPUT1","ORA"},{"INPUT1","NANDA"},{"INPUT2","ORA"},{"INPUT3","NANDA"},{"INPUT4","NANDA"},{"INPUT5","ONDELAYA"}}
local aChild:= {{"ORA","OUT1"},{"ORA","XORA"},{"NANDA","XORA"},{"ONDELAYA","RTRIGA"},{"XORA","OUT2"},{"RTRIGA","OUT3"}}
DBCREATE( "MAIN.DBF", {{"NAME","C",10,0},{"OUT","C",10,0}}, "DBFCDX", .T., "TMP" )
FW_ArrayToDbf( aMain )
FW_CdxCreate()
CLOSE TMP
DBCREATE( "CHILD.DBF", {{"FROM","C",10,0},{"FTO","C",10,0}}, "DBFCDX", .T., "TMP" )
FW_ArrayToDbf( aChild )
FW_CdxCreate()
CLOSE TMP
// XBROWSER "MAIN.DBF"
// XBROWSER "CHILD.DBF"
CLOSE DATA
return nil
//----------------------------------------------------------------------------//
#include "fivewin.ch"
REQUEST DBFCDX
//----------------------------------------------------------------------------//
function Main()
field OUT,FTO
local aData := {}
local aRow
CreateTables()
USE MAIN NEW SHARED VIA "DBFCDX"
SET ORDER TO TAG NAME
GO TOP
do while !MAIN->( Eof() )
aRow := { MAIN->NAME, MAIN->OUT }
GetConnections( aData, aRow )
MAIN->( DBSKIP( 1 ) )
enddo
XBROWSER aData
return nil
//----------------------------------------------------------------------------//
static function GetConnections( aData, aRow )
local cSeek := ATail( aRow )
local a
local cAlias
USE CHILD NEW ALIAS ( cAlias := cGetNewAlias( "CH" ) ) SHARED VIA "DBFCDX"
SET ORDER TO TAG FROM
if ( cAlias )->( DBSEEK( cSeek ) )
do while ( cAlias )->FROM == cSeek
a := AClone( aRow )
if AScan( a, { |x| x == ( cAlias )->FTO } ) > 0
AAdd( a, ( cAlias )->FTO )
AAdd( a, "[LOOP]" )
AAdd( aData, a )
else
AAdd( a, ( cAlias )->FTO )
GetConnections( aData, a )
endif
( cAlias )->( DBSKIP( 1 ))
enddo
else
AAdd( aData, aRow )
endif
( cAlias )->( DBCLOSEAREA() )
return aRow
//----------------------------------------------------------------------------//
static function CreateTables()
local aMain := {{"INPUT1","ORA"},{"INPUT1","NANDA"},{"INPUT2","ORA"},{"INPUT3","NANDA"},{"INPUT4","NANDA"},{"INPUT5","ONDELAYA"}}
local aChild:= {{"ORA","OUT1"},{"ORA","XORA"},{"NANDA","XORA"},{"ONDELAYA","RTRIGA"},;
{"XORA","OUT2"},{"RTRIGA","OUT3"},{"OUT3","OUT4"},{"OUT3","OUT5"},{"OUT5","RTRIGA"}}
DBCREATE( "MAIN.DBF", {{"NAME","C",10,0},{"OUT","C",10,0}}, "DBFCDX", .T., "TMP" )
FW_ArrayToDbf( aMain )
FW_CdxCreate()
CLOSE TMP
DBCREATE( "CHILD.DBF", {{"FROM","C",10,0},{"FTO","C",10,0}}, "DBFCDX", .T., "TMP" )
FW_ArrayToDbf( aChild )
FW_CdxCreate()
CLOSE TMP
// XBROWSER "MAIN.DBF"
XBROWSER "CHILD.DBF"
CLOSE DATA
return nil
//----------------------------------------------------------------------------//
Return to FiveWin for Harbour/xHarbour
Users browsing this forum: Google [Bot] and 48 guests