Data Type Extension in DBF

[ivanil.m...@gmail.com]

I believe this material will prove useful to many harbor users; at least here in Brazil, I realize that few programmers are aware of the extension of the harbor in terms of data type in DBF;
I rummaged through the harbor core repository and google for some documentation or usage examples and didn't really find anything that could be used;
I decided to create an example which is attached; some types are easily recognized, however others generate doubts and I had no vision of applicability in the real world;
On the other hand, the version control, modification date fields take the DBF to another level.
Kindly ask that if anyone has some solid knowledge on this issue and can enrich the attached example, they will be very welcome;

thankful;

Ivanil Marcelino

************

Function TesteDBF()
/*
    Harbour File Size Limits
     
        Max record size: 2^16-1 = 65535 byts ( 64 MB )
        Max number of recors : 2^32-1 = 4,294,967,295 ( 4 Bilion )
        Max .dbf file size : 2^48 = 256 TB
        Max DBT memo file size : 2 TB
        Max FPT memo file size : 256 GB
        Max SMT memo file size : 128 GB
        Max NTX file size (standard) : 4GB
        Max NTX file size (incresead ) : 4TB
        Max CDX file size : 4GB
    ...
    *
     Harbour extended Field Types
     
     Type Short
     Code Name Width (Bytes) Description
     ---- ------- ----------------- -------------------------------------------------------------------
     D Date 3, 4 or 8 Date
     M Memo 4 or 8 Memo
     + AutoInc 4 Auto increment
     = ModTime 8 Last modified date & time of this record
     ^ RowVers 8 Row version number; modification count of this record
     @ DayTime 8 Date & Time
     I Integer 1, 2, 3, 4 or 8 Signed Integer ( Width : )" },;
     T Time 4 or 8 Only time (if width is 4 ) or Date & Time (if width is 8 ) (?)
     V Variant 3, 4, 6 or more Variable type Field
     Y Currency 8 64 bit integer with implied 4 decimal
     B Double 8 Floating point / 64 bit binary
*/

    Local aMat:={}
    DbCloseAll()

   
    //D Date 3, 4 or 8 Date
    AADD(aMat,{"Data3","D",3,0})
    AADD(aMat,{"Data4","D",4,0})
    AADD(aMat,{"Data8","D",8,0})
   
    //M Memo 4 ou 8 Memo
    AADD(aMat,{"Memo4","M",4,0})
    AADD(aMat,{"Memo8","M",8,0})
   
    //+ AutoInc 4 Auto increment
    AADD(aMat,{"incre","+",10,0})
   
    //= ModTime 8 Last modified date & time of this record
    AADD(aMat,{"ModTime","=",8,0})
   
    //^ RowVers 8 Row version number; modification count of this record
    AADD(aMat,{"RowVers","^",10,0})
   
    //@ DayTime 8 Date & Time
    AADD(aMat,{"Daytime","@",8,0})
   
    //I Integer 1, 2, 3, 4 or 8 Signed Integer ( Width : )" },;
    AADD(aMat,{"Int1","I",1,0})
    AADD(aMat,{"Int2","I",2,0})
    AADD(aMat,{"Int3","I",3,0})
    AADD(aMat,{"Int4","I",4,0})
    AADD(aMat,{"Int8","I",8,0})
   
    //T Time 4 or 8 Only time (if width is 4 ) or Date & Time (if width is 8 ) (?)
    AADD(aMat,{"Hora4","T",4,0})
    AADD(aMat,{"Hora8","T",8,0})
   
    //V Variant 3, 4, 6 or more Variable type Field
    AADD(aMat,{"Vari3","V",3,0})
    AADD(aMat,{"Vari4","V",4,0})
    AADD(aMat,{"Vari6","V",6,0})
    AADD(aMat,{"Variv","V",60,0})
   
    //Y Currency 8 64 bit integer with implied 4 decimal
    AADD(aMat,{"Moeda","Y",8,0})
   
    //B Double 8 Floating point / 64 bit binary
    AADD(aMat,{"Float","B",8,0})
   
    DbCreate(":mem",aMat,"DBFCDX")
    Use ":mem" alias test
    Append Blank

    *****************************************************************************
    //D Date 3, 4 or 8 Date
    *What is the applicability of {3,4,8}?  In the tests below, the values obtained were the same.
    *****************************************************************************
    ?test->Data3:=Date(),test->Data3
    //Print="31/12/2021 ,31/12/2021"

    ?test->Data4:=Date()+1,test->Data4
    //Print=01/01/2022 ,01/01/2022"

    ?Test->Data8:=Date()+2,Test->Data8
    //Print=Test->Data8:=Date()+2,Test->Data8 =>        02/01/2022 ,02/01/2022
    *****************************************************************************

    *****************************************************************************
    //M Memo 4 or 8 Memo
    //What is the limitation between 4 and 8, usually 10 was used;
    *****************************************************************************
   
    ?Test->Memo4 := "Testando Memo4",Test->Memo4
    //Print=Testando Memo4 ,Testando Memo4

    ?Test->Memo8 := "Testando Memo8",Test->Memo8
    //Print=Testando Memo8 ,Testando Memo8
    *****************************************************************************
   
    //Non-editable - Incremental
    ?test->Incre
    //Print=1

    //Non-editable, saves the date and time of the last registry change;
    ?Test->ModTime
    //Print="  /  /    "
   
    //Not editable, saves version of registry change
    ?Test->RowVers
    //Print=0

    ?Test->Daytime := DateTime(),Test->Daytime
    //Print=31/12/2021 15:53:07.657 ,31/12/2021 15:53:07.657"

    /********************************************************************************
    What is the limit for each value {1;2;3;4;8} ?
    Is there any difference between using this type of value with the old {"field","N",n,0} which is also integer?    
    */
    ?Test->Int1 := 10,Test->Int1
    //Print=10 ,10

    ?Test->Int2 := 120.50,Test->Int2
    //Print=120.50 ,120

    ?Test->Int3 := 550.52,Test->Int3
    //Print=550.52 ,550

    ?Test->Int4 := 1221.55,Test->Int4
    //Print=1221.55 ,1221

    ?Test->Int8 := 88888.88,Test->Int8
    //Print=88888.88 ,88888"
    ****************************************************************************************

    ?Test->Hora4 := Datetime(),Test->Hora4
    //Print=31/12/2021 15:53:07.664 ,  /  /     15:53:07.664"

    ?Test->Hora8 := Datetime(),Test->Hora8
    //Print=31/12/2021 15:53:07.665 ,31/12/2021 15:53:07.665

    /*****************************************************************************************
    This guy confused me a lot; from the tests I could see that when it is 3 it assumes date; 4 assumes number and 8 assumes text;
    What is the applicability of this field in the real world?
    */
    ?Test->VAri3 := DAtetime(),Test->VAri3 //se 3 é data
    //Print=31/12/2021 15:53:07.667 ,31/12/2021

    ?Test->Vari4 := 155.80,Test->Vari4 //se 4 numerico
    //Print=155.80 ,155

    ?Test->Vari6 := "isto pode ser um texto?",Test->Vari6 //6 ou mais é string
    //Print=isto pode ser um texto? ,isto pode ser um texto?

    ?Test->VariV := "Isto tambem por ser um texto, qual a diferença de um para o outro?",Test->VariV
    //Print=Isto tambem por ser um texto, qual a diferença de um para o outro? ,Isto tambem por ser um texto, qual a diferença de um para o outro?
    ****************************************************************************************

    ?Test->Moeda := 155.9789845,Test->Moeda
    //Print=155.9789845 ,155.9790
   
    ?Test->Float := 158454545555454545499.15,Test->Float
    //Print=158454545555454600000.00 ,********************

    //vamos ver os campos não editavel
    Test->(DbCommit())
   
    ?Test->Int1 := 11,Test->Int1
    //Print=11 ,11

    ?"Alteração do registro",Test->ModTime
    //Print=Alteração do registro ,31/12/2021 15:53:07.673

    ?"Versãoo?",Test->RowVers  
    //Print=Versãoo? ,1
   
    Test->(DbCommit())
   
    ?Test->Int1 := 12,Test->Int1
    //12 ,12

    ?"Alteração do registro",Test->ModTime
    //Print=Alteração do registro ,31/12/2021 15:53:07.685

    ?"Versãoo?",Test->RowVers    
    //Print=Versãoo? ,2


    Return .t.
    

[Miroslav Georgiev]

Do anyone has an idea for example use case(s) of:

 ^ RowVers 8 Row version number; modification count of this record

For example I can compare 2 versions of the same DBF over time and generate list of different records. This can be used for online/offline database sync for example.

But this requires to re-read(and compare) both "old" and "new" file every time, for all DBF files, so it has limitations.

for what else this Extended Type can be used?

[carloskds]

On one occasion, I used this type of data field, to verify if someone accessed the dfb with external tools (visual foxpro) and made modifications to certain records which were supposed to be created only once, so the value of the field It would always be 1, and indeed, in some cases that number was higher than 1 which indicated to me that they got their hands on the data, and then add a trigger to the dbf in the update event, to store the changes they made, and The monitoring of changes was presented to management, resulting in the accountant forcing certain data to coincide. :-)

[carloskds]

This type of field, alone, is increased by 1 each time the record data is modified.