Support for indexing?

[btraas]

Is there currently any support for indexing (CDX files)? Writing to tables works fine, other than it doesn't update its corresponding .cdx file, which is used by our legacy application.

Are there any plans to support this soon?

[luads]

I'm don't know how the CDX files work, so I don't plan to implement it soon. If you want to help implementing that, I would appreciate that. I could try to help after a first release, but currently I don't have enough time to start that.

[btraas]

No problem. I'll let you know if I figure it out

[jchimene]

I don't think think the author has thought through index creation. My current attempt will be to link https://github.com/jakubkulhan/btree to this structure. Using a btree object of {key:XXXXX, recno:NN} should do the trick. I'd like to wrap this in a handler class to automatically open the data and an array of index files. My first cut will be to support only one index file.

[gadget00]

how did this went?? @jchimene where you able to go through any of this??

I'm having the situation where the index files don't get updated when I write a new record or update one in the DBF and when I open the legacy foxpro app the new records are not shown, until I 'reindex' the DBF

[btraas]

+1 @gadget00, we're doing the same. Would be great to do in this library (or even an external command line program). Let me know if you find anything

[gadget00]

@btraas so far I haven’t found a solution for this. How can we make a “reindex” function for this?? I just saw that there is a “pack()” function that saves the changes and re-counts the rows like the original VFP. There must be a way to trigger the reindex as well

[btraas]

@gadget00 poking through the code I didn't see any support for indexing, creating CDX files etc.

We ended up buying the Indexman command-line utility ($30) to reindex DBFs, which uses Microsoft's FoxPro DLLs to reindex. It's windows-only but I bet it works fine with wine.

https://dbfdoctor.com/en/news/IndexMan/

[gadget00]

@btraas can you give me an example of how to use that utility? I downloaded and installed it, and also read the help options but can't figure out how I'm supposed to use it. Thanks again

[btraas]

@gadget00 I use it like this:

C:\Program Files (x86)\IndexMan\IndexMan.exe <dbfFile> <cdxFile> [-nodeleted]

And wrapped in a C# service that can be triggered by our web server.

[DierteK]

@gadget00 I use it like this:

C:\Program Files (x86)\IndexMan\IndexMan.exe <dbfFile> <cdxFile> [-nodeleted]

And wrapped in a C# service that can be triggered by our web server.

I have tried it here also with IndexMan with the parameters -reindex -nodeleted.
If during the execution of IndexMan the FoxPro application runs also, no more entries are displayed within the application. It looks like an empty database.

Only after a new start of the FoxPro appication all rows (also the new ones) are shown :-(

[gadget00]

Well, it's been 10 years and no way of solving this via php-xbase itself so far. Happy Anniversary to this issue XD

[JohnnyQ352]

This might be a start for this:

/**

• CDX Generator Library for PHP-XBase
• This library provides functions to create a CDX (Compound Index) file for a DBF file
• using PHP-XBase.
  */

namespace CDXGenerator;

use XBase\Table;
use XBase\IndexConverter\IndexConverterFactory;
use XBase\IndexConverter\IndexConverterInterface;

class CDXGenerator
{
/**
* Creates a CDX index file for the given DBF file.
*
* @param string $dbfPath Path to the DBF file.
* @param array $indexDefinitions An array of index definitions.
* Each definition should be an associative array:
* [
* 'expression' => 'expression to index',
* 'name' => 'index name',
* 'unique' => bool, // optional, default false
* 'descending' => bool, // optional, default false
* 'ignoreCase' => bool, // optional, default false
* 'condition' => 'optional condition for filtering records'
* ]
* @param string|null $cdxPath Optional path to save the CDX file. If null, it will be saved next to the DBF.
* @return bool True on success, false on failure.
* @throws \Exception If an error occurs during the process.
*/
public static function createCDX(string $dbfPath, array $indexDefinitions, ?string $cdxPath = null): bool
{
try {
$table = new Table($dbfPath);

        if ($cdxPath === null) {
            $cdxPath = pathinfo($dbfPath, PATHINFO_DIRNAME) . '/' . pathinfo($dbfPath, PATHINFO_FILENAME) . '.cdx';
        }

        $converter = IndexConverterFactory::create($table, 'cdx');

        if (!$converter instanceof IndexConverterInterface) {
            throw new \Exception('CDX converter not found.');
        }

        foreach ($indexDefinitions as $definition) {
            $expression = $definition['expression'] ?? '';
            $name = $definition['name'] ?? '';
            $unique = $definition['unique'] ?? false;
            $descending = $definition['descending'] ?? false;
            $ignoreCase = $definition['ignoreCase'] ?? false;
            $condition = $definition['condition'] ?? null;

            $converter->addIndex($expression, $name, $unique, $descending, $ignoreCase, $condition);
        }

        $converter->save($cdxPath);

        return true;

    } catch (\Throwable $e) {
        error_log("CDX creation failed: " . $e->getMessage());
        return false;
    }
}

}

// Example usage:
/*
$dbfPath = 'path/to/your/data.dbf';
$indexDefinitions = [
[
'expression' => 'UPPER(TRIM(lastname))',
'name' => 'lastname_idx',
'unique' => false,
'ignoreCase' => true,
],
[
'expression' => 'id',
'name' => 'id_idx',
'unique' => true,
],
[
'expression' => 'date',
'name' => 'date_idx',
'descending' => true,
],
[
'expression' => 'city',
'name' => 'city_idx',
'condition' => 'state = "CA"'
]
];

if (CDXGenerator::createCDX($dbfPath, $indexDefinitions)) {
echo "CDX file created successfully.\n";
} else {
echo "CDX file creation failed.\n";
}
*/
?>

It might not be eveything, but it might be a good start for this added feature.

Johnny

[gadget00]

@JohnnyQ352 wow have you used this code in a project?? Does the IndexConverterFactory object does exist in the code or that would be a class to be created, so it all wraps up and creates the .CDX file?