DBF File Parsing with PHP - low-level file reading

[Otto]

Hello friends,

For those of you who would like to provide reports on the web but don't have a mod harbour server, you can also do this with PHP.

DBF File Parsing - Direct DBF Access

Today, I created a low-level file reading with PHP.
I can now read DBF files without a library and send them back to the server as JSON. I think this provides great flexibility.

The speed is incredible.

Best regards,
Otto

[Otto]

Here is a live preview - the DBF file has 200,000 records, and we read 50 records every click.

https://www.modharbour.club/workschedule/kundentbl.html

[Otto]

CRUD operations using PHP on DFF files are working. No libraries, only PHP/HTML/JS
DBF file has 200000 records.

https://www.modharbour.club/workschedule/kundentbl.html

[Carles]

Otto

mod, UT, RunnerXbase,... they are more powerful. No need to use php to handle dbfs

(EDITED) If you want to do a test, I will help you do a simple test with a simple reading dbf example if you want...

C.

[Otto]

Dear Charly,
Thank you.That's good to hear.
I have the dbf here: https://www.modharbour.club/workschedul ... tabase.zip

Maybe you can make the same example accessible on a server with mod so that it can be tested in real-time.

Best regards,
Otto

[Otto]

Dear Charly,

for this database, which is in the ZIP, the time is about 20 ms!

You can test it yourself.

https://www.modharbour.club/workschedule/kundentbl.html

Best regards,
Otto

[Otto]

Hello friends,
I'm not sure if in my case (max. file size 20MB, 100,000 to 200,000 records) indexes will be of much help.

It is impressive to see that the search time for a 20 MB file takes only a few milliseconds. I don't think it would be faster with an index either.
I am looking forward to your tests.

A desktop test would also be interesting. How fast can I get a list of the records displayed?

https://www.modharbour.club/workschedule/findstr2recno.php





Best regards,
Otto

[Carles]

Otto,

I'm not going to make a program because I also understand that it is not the objective. For me the issue is the speed that our harbour can have compared to other languages ​​and if it makes sense to use them. The easiest way to check a good load is simply by doing a simple function that opens a table, which reads as you do, for example, 50 records and returns a json if you want. You can use this test in the different mod, cgis, ut, runnerxbase,..., except php, but well, if you return a json, this would work well and you can compare the same code for performance, latencies, speed,... This is a real comparison.

This function is made with 10 lines of code, no more is needed.

Code: Select all  Expand view  RUN

function Test()

  - Open Table
  - Read 50 reg.

Return json
 

This makes sense to me to be able to compare

If you want to make this function, I try to run it on different systems

C.

[Otto]

Dear Charly,
Can you please download the zip file and run the test with V2?
Unfortunately, I don't have this version installed.

I am just interested in the time it takes.

Best regards,
Otto

https://www.modharbour.club/workschedule/download/database.zip

[Carles]

Hello Otto,

As I told you before, if you want we can do the test of reading, for example, 50 records from your database.dbf

Code: Select all  Expand view  RUN

function main()

    local cDbf      := hb_getenv( 'PRGPATH' ) + '/database.dbf'
    local aRows         := {}
   
    use (cDbf)

    for n := 1 to 50
   
        Aadd( aRows, {;
                'first' => FIELD->first,;
                'last' => FIELD->last,;
                'street' => FIELD->street,;
                'city' => FIELD->city,;
                'state' => FIELD->state,;
                'married' => FIELD->state;             
            })
    next
       
    AP_SetContentType( "application/json" )
   
    ?? hb_jsonEncode( aRows )
   
retu nil

I will set up some metrics for you to make comparisons between different systems, but to be as realistic as possible, we would have to do it on the same computer, whatever it may be. I'm telling you, because if you want I'll do them but I'll need the PHP example that you use to be able to compare.

This way we could create an interesting comparison between modHarbour V1, V2.1, RunnerXbase, php

If you send me the php example, tomorrow I will set up all the comparisons for you. And if not, here is the example and you can try it on all systems with your computer.

Regards.
C.

[Otto]

Daer Charly,

Thank you. I gladly accept your suggestion.
I also think we need good tests.

I am already looking forward to the results.

You just need to set the path to the database in the config.php. It should then work as it is.
Here is the ZIP:
https://www.modharbour.club/speedtests/downloads/speedtests.zip

You practically load this exact directory.
We can also compare the computers then. I have a 200/200 Mbps connection. The server is a notebook with an SSD.

https://www.modharbour.club/speedtests/kundentbl.html


Best regards,
Otto

[Carles]

Hi,

Yesterday afternoon I was able to do the tests. Here are the results



You can draw conclusions in many ways

I'm still pending the test with mod of Manu and also with fastcgi de Eric, but I didn't have more time. Maybe I'll try it another time.

I created an entry on my blog -> https://httpd2.blogspot.com/2024/06/web ... -o-no.html (In spanish but you have the option to translate to the language you want...)


Regards

C.

[Otto]

Dear Charly,

Thank you very much for your effort.
This is what I noticed during my test. I think it has less to do with PHP than with low-level access to the DBF file. And I think low-leval access is a great thing for reports.

I am currently rewriting the access, as it works in PHP, to Harbour.
One has to be so careful. A few milliseconds are quickly wasted.

I am already in the second part. I hope I can manage it and then try.
Thanks again.
I will post the unfinished code. Maybe someone has ideas.

Best regards,
Otto

PHP is very fast but it does not offer the versatility of the Harbour RDD system (and it's not Harbour either ).

Code: Select all  Expand view  RUN

#include "FiveWin.ch"

PROCEDURE Main
    LOCAL cFilePath := "x:\xwhdaten\datawin\KUNDEN.dbf"
    LOCAL cName := "Otto"
    LOCAL nStartTime := SECONDS()
    LOCAL aResult, nEndTime, nDuration
    LOCAL hRecord

    aResult := FindNameInDbf(cFilePath, cName)
    nEndTime := SECONDS()
    nDuration := (nEndTime - nStartTime) * 1000 // Dauer in Millisekunden

    ? "Suchzeit: " + STR(nDuration, 10, 2) + " ms"
    ? "Gefundene Datensätze: " + LEN(aResult[1])

    // Ausgabe der gefundenen Datensätze
    ? "Recordnummer", "Name", "Vorname", "Straße", "Ort"
    FOR EACH hRecord IN aResult[1]
        ? hRecord["recno"], hRecord["NAME"], hRecord["VORNAME"], hRecord["STRASSE"], hRecord["ORT"]
    NEXT

    RETURN

FUNCTION FindNameInDbf(cFilePath, cName)
    LOCAL nHandle := FOPEN(cFilePath)
    LOCAL cHeader := SPACE(32)
    LOCAL nHeaderSize, nRecordSize, nNumRecords
    LOCAL aFieldDescriptors := {}
    LOCAL aFieldOffsets := {}
    LOCAL nOffset := 0
    LOCAL cFieldDescriptor, cFieldName
    LOCAL nFieldLength
    LOCAL nNameOffset, nNameLength
    LOCAL aMatchingRecords := {}
    LOCAL cRecord, cLastName
    LOCAL hField, hRecordData
    LOCAL i
    LOCAL cFieldValue
    LOCAL hFieldDescriptor := {=>}

    IF nHandle == -1
        ? "Konnte die Datei nicht öffnen."
        RETURN {}
    ENDIF
    // Header lesen
    FREAD(nHandle, @cHeader, 32)
    nHeaderSize := BIN2I(SUBSTR(cHeader, 9, 2))
    nRecordSize := BIN2I(SUBSTR(cHeader, 11, 2))
    nNumRecords := BIN2I(SUBSTR(cHeader, 5, 4))
    // Felddeskriptoren lesen
    FSEEK(nHandle, 32)
    DO WHILE .T.
        cFieldDescriptor := SPACE(32)
        FREAD(nHandle, @cFieldDescriptor, 32)
        IF ASC(LEFT(cFieldDescriptor, 1)) == 13
            EXIT
        ENDIF
        cFieldName := RTRIM(SUBSTR(cFieldDescriptor, 1, 11))
        nFieldLength := ASC(SUBSTR(cFieldDescriptor, 17, 1))
        AADD(aFieldDescriptors, { "name" => cFieldName, "length" => nFieldLength })
    ENDDO
    // Feld-Offsets berechnen
    FOR i := 1 TO LEN(aFieldDescriptors)
        hFieldDescriptor := aFieldDescriptors[i]
        AADD(aFieldOffsets, { hFieldDescriptor["name"], nOffset, hFieldDescriptor["length"] })
        nOffset += hFieldDescriptor["length"]
    NEXT
    // Offset für das Feld "NAME" finden
    nNameOffset := AScan(aFieldOffsets, { |a| LEFT(a[1], 10) = "NAME" })
    nNameLength := aFieldOffsets[nNameOffset, 3]
    FSEEK(nHandle, nHeaderSize)
    FOR i := 1 TO nNumRecords
        cRecord := SPACE(nRecordSize)
        FREAD(nHandle, @cRecord, nRecordSize)
        cLastName := RTRIM(SUBSTR(cRecord, nNameOffset + 1, nNameLength))
        IF !EMPTY(cLastName) .AND. AT("vogel", LOWER(cLastName)) == 1
            hRecordData := { "recno" => i }
            nOffset := 0
            FOR EACH hField IN aFieldDescriptors
                cFieldValue := RTRIM(SUBSTR(cRecord, nOffset + 1, hField["length"]))
                hRecordData[hField["name"]] := cFieldValue
                nOffset += hField["length"]
            NEXT
            AADD(aMatchingRecords, hRecordData)
        ENDIF
    NEXT
    FCLOSE(nHandle)
    RETURN { aMatchingRecords }


 

[Carles]

Otto,

If you want it to do your reports, why don't you use the rdd and open the read-only table? You will never exceed the speed of rdd (which is coded in C) with prg level functions....

C.

[Otto]

Dear Charly,

Thank you for taking the time. And it’s great to engage with a true expert.

I haven't delved into the internal structure of the DBF format for a long time. But it is so clear that I think direct access is the best for me.

Since 1994, I've been using the same logic for my room plan as the DBF file has. I think that's why I like it. I also use the offset here.

I'm really curious about the speed. I am now getting the records back.

I could hardly imagine that it is built so simply. So much unnecessary packaging.

It is clear that when you have open files, it’s different. But with stateless, it’s great. I also worked similarly on PocketPC – there we didn’t have DBF either.

Word, Excel, Access also don’t have file locks but make parallel lock files. I do it the same way.

I have an update of the code here.

Best regards,
Otto

Code: Select all  Expand view  RUN

#include "FiveWin.ch"

PROCEDURE Main
    LOCAL cFilePath := "x:\xwhdaten\datawin\KUNDEN.dbf"
    LOCAL cName := "Otto"
    LOCAL nStartTime := SECONDS()
    LOCAL aResult, nEndTime, nDuration
    LOCAL hRecord
   
    aResult := FindNameInDbf(cFilePath, cName)
    nEndTime := SECONDS()
    nDuration := (nEndTime - nStartTime) * 1000 // Dauer in Millisekunden
   
    ? "Suchzeit: " + STR(nDuration, 10, 2) + " ms"
    ? "Gefundene Datensätze: " + LEN(aResult[1])
    // ? "Recordnummern: " + ARRAYTOSTR(aResult[2], ", ")
   
    // Ausgabe der gefundenen Datensätze
    ? "Recordnummer", "Name", "Vorname", "Straße", "Ort"
    FOR EACH hRecord IN aResult[1]
        ? hRecord["recno"], hRecord["NAME"], hRecord["VORNAME"], hRecord["STRASSE"], hRecord["ORT"]
    NEXT
   
RETURN

FUNCTION FindNameInDbf(cFilePath, cName)
    LOCAL nHandle := FOPEN(cFilePath)
    LOCAL cHeader := SPACE(32)
    LOCAL nHeaderSize, nRecordSize, nNumRecords
    LOCAL aFieldDescriptors := {}
    LOCAL aFieldOffsets := {}
    LOCAL nOffset := 0
    LOCAL cFieldDescriptor, cFieldName
    LOCAL nFieldLength
    LOCAL nNameOffset, nNameLength
    LOCAL aMatchingRecords := {}
    LOCAL aMatchingRecordNumbers := {}
    LOCAL cRecord, cLastName
    LOCAL hField, hRecordData
    LOCAL i
    LOCAL cFieldValue
    LOCAL hFieldDescriptor := {=>}
   
   
    LOCAL nFieldCount, nField, nFieldLen
   
    IF nHandle == -1
        ? "Konnte die Datei nicht öffnen."
    RETURN {}
ENDIF

// Header lesen










// Header lesen
FREAD(nHandle, @cHeader, 32)


// Byte-Interpretation der Header-Daten
nNumRecords := Bit32Unpack(SUBSTR(cHeader, 5, 4))
nHeaderSize := Bit16Unpack(SUBSTR(cHeader, 9, 2))
nRecordSize := Bit16Unpack(SUBSTR(cHeader, 11, 2))

//   ? "nNumRecords: " + STR(nNumRecords)
//  ? "nHeaderSize: " + STR(nHeaderSize)
//   ? "nRecordSize: " + STR(nRecordSize)



// Felddeskriptoren lesen
FSEEK(nHandle, 32)
DO WHILE .T.
    cFieldDescriptor := SPACE(32)
    FREAD(nHandle, @cFieldDescriptor, 32)
    IF ASC(LEFT(cFieldDescriptor, 1)) == 13
        EXIT
    ENDIF
    cFieldName := RTRIM(SUBSTR(cFieldDescriptor, 1, 11))
    nFieldLength := ASC(SUBSTR(cFieldDescriptor, 17, 1))
    AADD(aFieldDescriptors, { "name" => cFieldName, "length" => nFieldLength })
ENDDO


//ok ? valtype(aFieldDescriptors)
//ok xbrowse(aFieldDescriptors)


// Feld-Offsets berechnen
FOR i:= 1 TO len( aFieldDescriptors )
    //xbrowse(  aFieldDescriptors[i] )
    hFieldDescriptor := aFieldDescriptors[i]  
   
    //ok? hFieldDescriptor["name"]
    //ok ? nOffset
    //ok  ? hFieldDescriptor["length"]
   
    AAdd(aFieldOffsets, { hFieldDescriptor["name"], nOffset, hFieldDescriptor["length"] })
    nOffset += hFieldDescriptor["length"]
NEXT

//ok

// xbrowse(aFieldOffsets)

//? valtype(aFieldOffsets[1])
//xbrowse(aFieldOffsets[1])

//AScan( aFieldOffsets , { |a|  msginfo( ( a[1] ) )  } )
// xbrowse(aFieldOffsets )
// ?  AScan( aFieldOffsets, { |a| a[1]  } )

// I := AScan( aFieldOffsets, { |a| a[ 1 ] == "NAME" } )

//OK AScan( aFieldOffsets , { |a|  msginfo(len(a[1])) , msginfo( "-" + ( ALLTRIM( a[1] ) )  + "-" )  } )

//OK  ? AScan( aFieldOffsets, { |a| left( a[ 1 ], 10 ) = "NAME" } )


// Offset für das Feld "NAME" finden
nNameOffset := AScan( aFieldOffsets, { |a| left( a[ 1 ], 10 ) = "NAME" } )
?   aFieldOffsets[ nNameOffset, 1]
nNameLength := aFieldOffsets[ nNameOffset, 3]
? nNameLength  
// aFieldDescriptors[ASCAN(aFieldDescriptors, {|h| h["name"] == "NAME" })]["length"]

// bis hier her getestet

FSEEK(nHandle, nHeaderSize)
FOR i := 1 TO nNumRecords
    cRecord := SPACE(nRecordSize)
   
    FREAD(nHandle, @cRecord, nRecordSize)
   
    // hier wird der record ausgelesen
    // ok ? cRecord
   
   
   
    cLastName := RTRIM(SUBSTR(cRecord, nNameOffset + 1, nNameLength))
    ? cLastName
   
   
   
   
    IF !EMPTY(cLastName) .AND. AT("vogel", LOWER(cLastName)) == 1
        hRecordData := { "recno" => i }
        nOffset := 0
        FOR EACH hField IN aFieldDescriptors
            cFieldValue := RTRIM(SUBSTR(cRecord, nOffset + 1, hField["length"]))
            hRecordData[hField["name"]] := cFieldValue
            nOffset += hField["length"]
        NEXT
        AADD(aMatchingRecords, hRecordData)
        AADD(aMatchingRecordNumbers, i)
    ENDIF
NEXT

FCLOSE(nHandle)

RETURN { aMatchingRecords, aMatchingRecordNumbers }



FUNCTION Bit16Unpack(cData)
RETURN (ASC(SUBSTR(cData, 1, 1)) + (ASC(SUBSTR(cData, 2, 1)) * 256))

FUNCTION Bit32Unpack(cData)
RETURN (ASC(SUBSTR(cData, 1, 1)) + (ASC(SUBSTR(cData, 2, 1)) * 256) + (ASC(SUBSTR(cData, 3, 1)) * 65536) )