Compare structure of DBF on 2 locations

[Marc Venken]

Hello,

In order to upgrade my program I want to be able to check the structure of dbf's on 2 locations.

While upgrading, I change structures now and then, but I would be handy if I could run a routine that will give the differences from each file (same name), but
sometimes different structures.

Anybody that has this routine already made ?

It is not needed to update the structures or so... only show the difference.

[nageswaragunupudi]

You can use this to compare structures of two dbfs and know if they are same or not.
For this purpose, you need not even open (with USE) the dbfs.

Code: Select all  Expand view

aStruct1 := FW_DBFSTRUCT( <firstdbfwithfullpath> )
aStruct2 := FW_DBFSTRUCT( <seconddbfwithfullpath> )

if FW_ArrayAsList( aStruct1 ) == FW_ArrayAsList( aStruct2 )
   // Same structure
else
  // different
  <your routine to compare aStruct1 with aStruct2>
endif
 

[hmpaquito]

Code: Select all  Expand view

aStruct1 := FW_DBFSTRUCT( <firstdbfwithfullpath> )
aStruct2 := FW_DBFSTRUCT( <seconddbfwithfullpath> )

// Add this, please
aStruct1:= aSort(aStruct1, nil, nil, {|x, y| x[1] < y[1]})
aStruct2:= aSort(aStruct2, nil, nil, {|x, y| x[1] < y[1]})


if FW_ArrayAsList( aStruct1 ) == FW_ArrayAsList( aStruct2 )
   // Same structure
else
  // different
  <your routine to compare aStruct1 with aStruct2>
endif

[Horizon]

You can use this to compare structures of two dbfs and know if they are same or not.
For this purpose, you need not even open (with USE) the dbfs.

Code: Select all  Expand view

aStruct1 := FW_DBFSTRUCT( <firstdbfwithfullpath> )
aStruct2 := FW_DBFSTRUCT( <seconddbfwithfullpath> )

if FW_ArrayAsList( aStruct1 ) == FW_ArrayAsList( aStruct2 )
   // Same structure
else
  // different
  <your routine to compare aStruct1 with aStruct2>
endif
 

Hi Mr. Rao,

Is this valid for MariaDB? Also, Can you forward me to write //different part for MariaDB?

[nageswaragunupudi]

This code is only for DBF files.

Can you forward me to write //different part for MariaDB?

I do not understand your question. Can you please clarify?

[Marc Venken]

Can XBrowser show 2 array of data at one time ?

Xbrowser(aData1,adata2) // See the names (even the structure of the 2 arrays next to each other)

I have seen great stuff with just 1 or 2 lines with Xbrowser

Maybe combine the Arrays to 1 bigger and show this with Xbrowser

It is only for viewing, no changes are to be made.

[Horizon]

This code is only for DBF files.

Can you forward me to write //different part for MariaDB?

I do not understand your question. Can you please clarify?

I mean How to change structure in MariaDB with data?

Thanks.

[nageswaragunupudi]

oCn:RenameColumn( cTable, cOldName, cNewName )
oCn:AddColumn( cTable, aColSpec )
oCn:AlterColumn( cTable, aColSpec )

aColSpec ( dbf column spec )
Eg:
{ "firstname", "C", 30, 0 }

{ "salary", "N", 10, 2 }

etc

[Horizon]

Hi Mr. Rao,

1) Is there any method to delete Column?

Code: Select all  Expand view

oCn:AddColumn( cTable, aColSpec )
oCn:AlterColumn( cTable, aColSpec )

2) Has aColSpec more than one field? for example {{"name", "C", 100,0}, {"telefon","C",20,0}

3) Is AlterColumn means can change type, len, dec for a specified fieldname?

[nageswaragunupudi]

1) Is there any method to delete Column?

No.

2) Has aColSpec more than one field? for example {{"name", "C", 100,0}, {"telefon","C",20,0}

Only one column.

3) Is AlterColumn means can change type, len, dec for a specified fieldname?

Yes.

[Horizon]

Hi Mr. Rao,

I try to write Alter_Table function according to my knowledge. This function compares cTableName's structure and given new structure and it decided to which colspec is added, changed or deleted.

There is not problem adding or deleting for aColSpec.

But When I changing in Numeric variables type and length is not mine that is described my aNew_Structure. It behaves it like in CreateTable function.

for example, my Numeric, 5 converted to INT, 11. This function always think this variable's spec is changed and start to run AlterTable().

How Can I solve this problem. Is there any function to convert this behavior?

Code: Select all  Expand view

    ...
    ...            
    lUpdate_Yap := Alter_Table(oCn, cTableName, aNew_Structure)
    ...
    ...


*-----------------------------------------------------------------------------------------------
FUNCTION Alter_Table(oCn, cTableName, aNew, lShow)
LOCAL l, lResult := .f., aCompare_Result, cDel_SQL, cOld
DEFAULT lShow := .F.

    IF oCn<>nil .AND. oCn:TableExists(cTableName)
        aOld := oCn:TableStructure(cTableName)
        aCompare_Result := Compare_Two_Table(aOld, aNew)

        if lShow
            xbrowser aOld slnum TITLE cTableName+" - aOld STRUCTURE"
            xbrowser aNew SLNUM TITLE cTableName+" - aNew STRUCTURE"
            xbrowser aCompare_Result slnum
        ENDIF
       
        IF !EMPTY(aCompare_Result[1])       // EKLENECEK VAR
            FOR l:=1 TO LEN(aCompare_Result[1])
                oCn:AddColumn( cTableName, aCompare_Result[1][l] )                     
            NEXT
            lResult:=.T.
        ENDIF
       
        IF !EMPTY(aCompare_Result[2])       // İÇERİĞİ DEĞİŞECEK VAR
            FOR l:=1 TO LEN(aCompare_Result[2])
                oCn:AlterColumn( cTableName, aCompare_Result[2][l] )
            NEXT
            lResult:=.T.
        ENDIF
       
        IF !EMPTY(aCompare_Result[3])       // SİLİNECEK VAR
            FOR l:=1 TO LEN(aCompare_Result[3])
                cDel_SQL := "ALTER TABLE "+cTableName+" DROP COLUMN IF EXISTS "+aCompare_Result[3][l,1]
                oCn:Execute(cDel_SQL)
            NEXT
            lResult:=.T.
        ENDIF
    ENDIF   
RETURN lResult
*------------------------------------------------------------------------------------------------
FUNCTION Compare_Two_Table(aOld, aNew)
LOCAL aInsert:={}, aChange := {}, aDelete := {}, i

    FOR i:=1 TO LEN(aOld)
        IF aOld[i,2]="m"
            aOld[i,3]=10
        ENDIF
    NEXT   
               
    FOR i:=1 TO LEN(aNew)
        nAt := AScan(aOld, {|a|a[1]=aNew[i,1]})
        IF nAt = 0  // Yani bizim table da aradığımız yok ise yenidir.
            aAdd(aInsert, aNew[i])
        ELSE
            IF aOld[nAt,2]<>aNew[i,2] .OR. aOld[nAt,3]<>aNew[i,3] .OR. aOld[nAt,4]<>aNew[i,4]
                aAdd(aChange, aNew[i])
            ENDIF
        ENDIF   
    NEXT
   
    FOR i:=1 TO LEN(aOld)
        nAt := AScan(aNew, {|a|a[1]=aOld[i,1]})
        IF nAt = 0  // Yani bizim table da aradığımız yok ise yenidir.
            aAdd(aDelete, aOld[i])
        ENDIF   
    NEXT   

RETURN {aInsert, aChange, aDelete}

Edit : Please go this link http://forums.fivetechsupport.com/viewtopic.php?p=232576#p232576