Wiki Home

Dbf Cmp


Namespace: SoftwareEng
* Compares two directories of tables and displays differences
* By CarlKarsten
clear
close data
set safety off
lCmpDirs( "p:\cwdata", "P:\temp", "*.dbf"  )
return
**********************************************************************
function lCmpDirs( tcDir1, tcDir2, tcMsk )

local ;
	lcDir1, lcDir2, ;
	laDbfs(1), lnDbfs, lcDbf, ;
	lnI
	
lcDir1 = addbs( tcDir1 )
lcDir2 = addbs( tcDir2 )

lnDbfs = aDir( laDbfs, lcDir1 + tcMsk )
for lnI = 1 to lnDbfs
	lcDbf = laDbfs( lnI, 1 )
	lOneDbf( lcDir1, lcDir2, lcDbf )
endfor

return
**********************************************************************
function lOneDbf( tcDir1, tcDir2, tcTbl )

local laFlds(1), lnFlds, lnFld, lcFld
	
use ( tcDir1 + tcTbl ) alias ONE in 0
use ( tcDir2 + tcTbl ) alias TWO in 0

lCmp ( "RecCount( 'ONE' )", "RecCount( 'TWO' )", "Number of Records " + tcTbl )

lnFlds = lFlds( @laFlds, tcTbl )

select ONE
set relation to recno() into TWO
scan while lCmp( "Eof( 'ONE' )", "Eof( 'TWO' )", "End of File - " + tcTbl )

	for lnFld = 1 to lnFlds
		lcFld = trim( laFlds(lnFld,1) )
		lCmp( "ONE."+lcFld, "TWO."+lcFld, tcTbl + " Record #" + trans( recno() ) )
	endfor

endscan

use in one
use in two

return 

**********************************************************************
function lFlds( taFlds, tcTbl )
* Return the intersection between the sets of fields from each table, 
* Log the excluded fields

local ;
	lcStu1, lcStu2
	
lCmp ( "FCount( 'ONE' )", "FCount( 'TWO' )", "Number of Fields " + tcTbl )

lcStu1 = addbs( sys(2023) ) + "StruOne"
lcStu2 = addbs( sys(2023) ) + "StruTwo"

select one
copy structure extended to ( lcStu1 )
select two
copy structure extended to ( lcStu2 )

* Log Loners
lLon( lcStu1, lcStu2, dbf( 'one' ) )
lLon( lcStu2, lcStu1, dbf( 'two' ) )

* Log Type mismatchs
lTypMis( lcStu1, lcStu2, tcTbl )

* Get dups
select field_name ;
	from ( lcStu1 );
	where field_name + field_type ;
		in ( select field_name + field_type from ( lcStu2 ) ) ;
into array taFlds

use in StruOne
use in StruTwo

return _tally
**********************************************************************
function lLon( tcTblA, TcTblB, tcTbl )

select field_name, field_type, field_len, field_Dec ;
	from ( TcTblA  ) ;
	where field_name ;
		not in (select field_name from ( tcTblb ) ) ;
into cursor Loners

scan
	lLog( "Extra Field", tcTbl, ;
		lFldDef( Field_Name, Field_Type, Field_len, Field_dec ) )
endscan
return
**********************************************************************
function lTypMis( tcTbl1, TcTbl2, tcTbl )

select Tbl1.field_name, ;
	Tbl1.field_type as Typ1, Tbl2.field_type as Typ2, ;
	Tbl1.field_len as Len1, Tbl2.field_len as Len2, ;
	Tbl1.field_dec as Dec1, Tbl2.field_dec as Dec2 ;
	from ( TcTbl1 ) Tbl1 ;
		join ( TcTbl2 ) Tbl2 on Tbl1.field_name = Tbl2.Field_Name ;
	where Tbl1.Field_Type <> Tbl2.Field_Type or ;
		Tbl1.field_len <> Tbl2.field_len or ;
		Tbl1.field_dec <> Tbl2.field_dec ;
into cursor TypMis

scan
	lLog( "Type Mismatch " + tcTbl, ;
		lFldDef( Field_Name, Typ1, Len1, Dec1 ), ;
		lFldDef( Field_Name, Typ2, Len2, Dec2 ) )
endscan

**********************************************************************
function lFldDef( tcNam, tcTyp, tcLen, tcDec )
local ;
	lcRet
	
lcRet = trim( tcNam ) + " " + tcTyp
do case
	case inlist( tcTyp, "C" )
		lcRet = lcRet + "(" + trans( tcLen ) + ")"
	case inlist( tcTyp, "N" )
		lcRet = lcRet + "(" + trans( tcLen ) + "," + trans( tcDec ) + ")"
endcase
return lcRet
		


**********************************************************************
function lCmp( tcExp1, tcExp2, tcDsc )
local ;
	luVal1, luVal2, ;
	llRet
	
luVal1 = evaluate( tcExp1 )
luVal2 = evaluate( tcExp2 )

if VarType( luVal1 ) =  VarType( luVal2 )
	llRet = ( luVal1 = luVal2 )
else
	llRet = .f.
	tcDsc = tcDsc + " Data type Mismatch: " + VarType( luVal1 ) + "#" + VarType( luVal2 )
endif

if !llRet
	lLog( tcDsc, ;
	tcExp1 + " = " + trans( luVal1 ), ;
	tcExp2 + " = " + trans( luVal2 ) )
endif

return llRet
**********************************************************************
function lLog( tcLin1, tcLin2, tcLin3 )
? tcLin1
? tcLin2
? tcLin3
?

Contributors: Carl Karsten
Category Code Samples
( Topic last updated: 1999.11.09 12:50:12 PM )