How to recompute references in phpcr.

Client.php

<?php
namespace Jackalope\Transport\DoctrineDBAL;

class Client extends BaseTransport implements QueryTransport, WritingInterface, WorkspaceManagementInterface, NodeTypeManagementInterface, TransactionInterface
{
    // Make this a public static array to be accessible outside the class
     public static array $referenceTables = [
         PropertyType::REFERENCE => 'phpcr_nodes_references',
         PropertyType::WEAKREFERENCE => 'phpcr_nodes_weakreferences',
     ];
}

RecomputeReferences.php

<?php

declare(strict_types=1);

namespace Jackalope\Tools\Console\Command;

use Doctrine\DBAL\Connection;
use Jackalope\Tools\Console\Helper\DoctrineDbalHelper;
use Jackalope\Transport\DoctrineDBAL\Client;
use Jackalope\Transport\DoctrineDBAL\XmlParser\ReferenceIterator;
use PHPCR\PropertyType;
use Symfony\Component\Console\Command\Command;
use Symfony\Component\Console\Input\InputInterface;
use Symfony\Component\Console\Input\InputOption;
use Symfony\Component\Console\Output\OutputInterface;

class RecomputeReferences extends Command
{
    private Connection $connection;

    protected function configure(): void
    {
        $this
            ->setName('jackalope:recomputeReferences')
            ->setDescription('Recomputes the references and weak references tables.')
            ->setDefinition([
            new InputOption('clean', null, InputOption::VALUE_NONE, 'If this option is set then the table will be cleared before recomputing.'),
            new InputOption('workspace', null, InputOption::VALUE_OPTIONAL, 'Workspace to use (if ommited all workspaces will be recalculated'),
            ])
        ;
    }

    protected function execute(InputInterface $input, OutputInterface $output): int
    {
        $dbalHelper = $this->getHelper('jackalope-doctrine-dbal');
        \assert($dbalHelper instanceof DoctrineDbalHelper);
        $this->connection = $dbalHelper->getConnection();

        if ($input->getOption('clean')) {
            // Truncate references tables
            $databasePlatform = $this->connection->getDriver()->getDatabasePlatform();
            $this->connection->executeStatement($databasePlatform->getTruncateTableSQL('phpcr_nodes_references'));
            $this->connection->executeStatement($databasePlatform->getTruncateTableSQL('phpcr_nodes_weak_references'));
        }

        // Get all the nodes from the selected workspaces
        $sql = 'SELECT id, props FROM phpcr_nodes ';
        $params = [];
        if ($input->getOption('workspace') !== null) {
            $sql .= 'workspace = :workspace';
            $params['workspace'] = $input->getOption('workspace');
        }

        foreach ($this->connection->executeQuery($sql, $params)->iterateAssociative() as $result) {
            $this->processNode($result);
        }

        return Command::SUCCESS;
    }

    /**
     * @param array<string> $nodeUuids
     * @return array<string, int>
    */
    private function mapNodeUuids(array $nodeUuids): array {
        $nodeMap = [];
        $sql ='SELECT id, identifier FROM phpcr_nodes WHERE identifier IN ('.implode(',', array_map(fn ($x) => "'$x'", $nodeUuids)).');';
        foreach ($this->connection->executeQuery($sql)->fetchAssociative() as $node) {
            $nodeMap[$node['identifier']] = $node['id'];
        }
        return $nodeMap;
    }

    private function processNode(array $result): void {
        $referencesToGenerate = [];
        foreach (ReferenceIterator::iterateReferences($result['props']) as $propertyName => $referenceData) {
            $referencesToGenerate[] = [
                'sourceId' =>$result['id'],
                'sourceProperty' => $propertyName,
                'targetUuid' => $referenceData['node']->nodeValue
            ];
        }

        $nodeMap = $this->mapNodeUuids(array_column($referencesToGenerate, 'targetUuid'));
        foreach ($referencesToGenerate as $referenceToGenerate) {
            $table = Client::$referenceTables[PropertyType::nameFromValue($referenceToGenerate['type'])];
            $targetId =$nodeMap[$referenceToGenerate['targetUuid']] ?? null;
            if ($targetId === null && $referenceData['type'] === PropertyType::WEAKREFERENCE) {continue;}
            else if ($targetId === null) {
                throw new \InvalidArgumentException();
            }
            $this->connection->executeStatement(<<<SQL
                INSERT INTO $table (source_id, source_propery_name, target_id)
                VALUES (:sourceId, :sourceProperty, :targetId)
                ON DUPLICATE KEY UPDATE target_id = :targetId;
                SQL, [
                    'sourceId' => $referenceToGenerate['sourceId'],
                    'sourceProperty' => $referenceToGenerate['sourceProperty'],
                    'targetId' => $targetId,
                ]);
        }
    }
}

ReferenceIterator.php

<?php

declare(strict_types=1);

namespace Jackalope\Transport\DoctrineDBAL\XmlParser;

use Generator;
use PHPCR\PropertyType;
use DOMElement;

class ReferenceIterator
{
    /**
     * @return Generator<string, array{type: string, values: array<mixed>, node: DOMElement}>
     */
    public static function iterateReferences(string $dom): Generator
    {
        $xpath = new \DOMXPath($dom);
        $referenceElements = $xpath->query(
            './/sv:property[@sv:type="reference" or @sv:type="Reference" or @sv:type="weakreference" or @sv:type="WeakReference"]'
        );
        foreach($referenceElements as $element) {
            \assert($element instanceof \DOMElement);
            $propName = $element->getAttribute('sv:name');
            $values = [];
            foreach ($xpath->query('./sv:value', $element) as $valueEl) {
                $values[] = $valueEl->nodeValue;
            }

            yield $propName => [
                'type' => PropertyType::valueFromName($element->getAttribute('sv:type')),
                'values' => $values,
                'node' => $element,
            ];
        }
    }
}