/src/Jackalope/Transport/DoctrineDBAL/Client.php

Large files files are truncated, but you can click here to view the full file

<?php

namespace Jackalope\Transport\DoctrineDBAL;

use PHPCR\LoginException;
use PHPCR\NodeType\NodeDefinitionInterface;
use PHPCR\NodeType\NodeTypeDefinitionInterface;
use PHPCR\NodeType\NodeTypeExistsException;
use PHPCR\NodeType\PropertyDefinitionInterface;
use PHPCR\Query\QOM\ColumnInterface;
use PHPCR\Query\QOM\JoinInterface;
use PHPCR\Query\QOM\SourceInterface;
use PHPCR\RepositoryInterface;
use PHPCR\NamespaceRegistryInterface;
use PHPCR\CredentialsInterface;
use PHPCR\PropertyType;
use PHPCR\Query\QOM\QueryObjectModelInterface;
use PHPCR\Query\QOM\SelectorInterface;
use PHPCR\Query\QueryInterface;
use PHPCR\RepositoryException;
use PHPCR\NamespaceException;
use PHPCR\NoSuchWorkspaceException;
use PHPCR\ItemExistsException;
use PHPCR\ItemNotFoundException;
use PHPCR\ReferentialIntegrityException;
use PHPCR\SimpleCredentials;
use PHPCR\Util\ValueConverter;
use PHPCR\ValueFormatException;
use PHPCR\PathNotFoundException;
use PHPCR\Query\InvalidQueryException;
use PHPCR\NodeType\ConstraintViolationException;

use PHPCR\Util\UUIDHelper;
use PHPCR\Util\QOM\Sql2ToQomQueryConverter;
use PHPCR\Util\PathHelper;

use Doctrine\DBAL\Connection;
use Doctrine\DBAL\Driver\PDOConnection;
use Doctrine\DBAL\Platforms\MySqlPlatform;
use Doctrine\DBAL\Platforms\PostgreSqlPlatform;
use Doctrine\DBAL\Platforms\SqlitePlatform;
use Doctrine\DBAL\DBALException;

use Jackalope\Node;
use Jackalope\Property;
use Jackalope\Query\Query;
use Jackalope\Transport\AddNodeOperation;
use Jackalope\Transport\MoveNodeOperation;
use Jackalope\Transport\BaseTransport;
use Jackalope\Transport\QueryInterface as QueryTransport;
use Jackalope\Transport\WritingInterface;
use Jackalope\Transport\WorkspaceManagementInterface;
use Jackalope\Transport\NodeTypeManagementInterface;
use Jackalope\Transport\TransactionInterface;
use Jackalope\Transport\StandardNodeTypes;
use Jackalope\Transport\DoctrineDBAL\Query\QOMWalker;
use Jackalope\NodeType\NodeTypeManager;
use Jackalope\NodeType\NodeType;
use Jackalope\NodeType\NodeTypeDefinition;
use Jackalope\FactoryInterface;
use Jackalope\NotImplementedException;

/**
 * Class to handle the communication between Jackalope and RDBMS via Doctrine DBAL.
 *
 * @license http://www.apache.org/licenses Apache License Version 2.0, January 2004
 * @license http://opensource.org/licenses/MIT MIT License
 *
 * @author Benjamin Eberlei <kontakt@beberlei.de>
 * @author Lukas Kahwe Smith <smith@pooteeweet.org>
 */
class Client extends BaseTransport implements QueryTransport, WritingInterface, WorkspaceManagementInterface, NodeTypeManagementInterface, TransactionInterface
{
    /**
     * The factory to instantiate objects
     * @var FactoryInterface
     */
    protected $factory;

    /**
     * @var ValueConverter
     */
    protected $valueConverter;

    /**
     * @var Connection
     */
    private $conn;

    /**
     * @var bool
     */
    private $loggedIn = false;

    /**
     * @var SimpleCredentials
     */
    private $credentials;

    /**
     * @var string
     */
    protected $workspaceName;

    /**
     * @var array
     */
    private $nodeIdentifiers = array();

    /**
     * @var NodeTypeManager
     */
    private $nodeTypeManager;

    /**
     * @var bool
     */
    protected $inTransaction = false;

    /**
     * Check if an initial request on login should be send to check if repository exists
     * This is according to the JCR specifications and set to true by default
     * @see setCheckLoginOnServer
     * @var bool
     */
    private $checkLoginOnServer = true;

    /**
     * @var array
     */
    protected $namespaces = array();

    /**
     * @var string|null
     */
    private $sequenceWorkspaceName;

    /**
     * @var string|null
     */
    private $sequenceNodeName;

    /**
     * @var string|null
     */
    private $sequenceTypeName;

    /**
     * @var array
     */
    private $referencesToUpdate = array();

    /**
     * @var array
     */
    private $referenceTables = array(
        PropertyType::REFERENCE => 'phpcr_nodes_references',
        PropertyType::WEAKREFERENCE => 'phpcr_nodes_weakreferences',
    );

    /**
     * @var array
     */
    private $referencesToDelete = array();

    /**
     * @param FactoryInterface $factory
     * @param Connection       $conn
     */
    public function __construct(FactoryInterface $factory, Connection $conn)
    {
        $this->factory = $factory;
        $this->valueConverter = $this->factory->get('PHPCR\Util\ValueConverter');
        $this->conn = $conn;
        if ($conn->getDatabasePlatform() instanceof PostgreSqlPlatform) {
            $this->sequenceWorkspaceName = 'phpcr_workspaces_id_seq';
            $this->sequenceNodeName = 'phpcr_nodes_id_seq';
            $this->sequenceTypeName = 'phpcr_type_nodes_node_type_id_seq';
        }

        // @TODO: move to "SqlitePlatform" and rename to "registerExtraFunctions"?
        if ($this->conn->getDatabasePlatform() instanceof SqlitePlatform) {
            $this->registerSqliteFunctions($this->conn->getWrappedConnection());
        }
    }

    /**
     * @TODO: move to "SqlitePlatform" and rename to "registerExtraFunctions"?
     *
     * @param PDOConnection $sqliteConnection
     *
     * @return Client
     */
    private function registerSqliteFunctions(PDOConnection $sqliteConnection)
    {
        $sqliteConnection->sqliteCreateFunction('EXTRACTVALUE', function ($string, $expression) {
            $dom = new \DOMDocument('1.0', 'UTF-8');
            $dom->loadXML($string);
            $xpath = new \DOMXPath($dom);
            $list = $xpath->evaluate($expression);

            if (!is_object($list)) {
                return $list;
            }

            // @TODO: don't know if there are expressions returning more then one row
            if ($list->length > 0) {
                return $list->item(0)->textContent;
            }

            // @TODO: don't know if return value is right
            return null;
        }, 2);

        $sqliteConnection->sqliteCreateFunction('CONCAT', function () {
            return implode('', func_get_args());
        });

        return $this;
    }

    /**
     * @return Connection
     */
    public function getConnection()
    {
        return $this->conn;
    }

    /**
     * {@inheritDoc}
     *
     */
    public function createWorkspace($name, $srcWorkspace = null)
    {
        if (null !== $srcWorkspace) {
            throw new NotImplementedException('Creating workspace as clone of existing workspace not supported');
        }

        if ($this->workspaceExists($name)) {
            throw new RepositoryException("Workspace '$name' already exists");
        }

        try {
            $this->conn->insert('phpcr_workspaces', array('name' => $name));
        } catch (\Exception $e) {
            throw new RepositoryException("Couldn't create Workspace '$name': ".$e->getMessage(), 0, $e);
        }

        $this->conn->insert('phpcr_nodes', array(
            'path'          => '/',
            'parent'        => '',
            'workspace_name'=> $name,
            'identifier'    => UUIDHelper::generateUUID(),
            'type'          => 'nt:unstructured',
            'local_name'    => '',
            'namespace'     => '',
            'props' => '<?xml version="1.0" encoding="UTF-8"?>
<sv:node xmlns:mix="http://www.jcp.org/jcr/mix/1.0" xmlns:nt="http://www.jcp.org/jcr/nt/1.0" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:jcr="http://www.jcp.org/jcr/1.0" xmlns:sv="http://www.jcp.org/jcr/sv/1.0" xmlns:rep="internal" />',
            // TODO compute proper value
            'depth'         => 0,
        ));
    }

    /**
     * {@inheritDoc}
     */
    public function deleteWorkspace($name)
    {
        if (!$this->workspaceExists($name)) {
            throw new RepositoryException("Workspace '$name' cannot be
            deleted as it does not exist");
        }

        try {
            $this->conn->delete('phpcr_workspaces', array('name' => $name));
        } catch (\Exception $e) {
            throw new RepositoryException("Couldn't delete workspace '$name': "
            .$e->getMessage(), 0, $e);
        }

        try {
            $this->conn->delete('phpcr_nodes', array('workspace_name'=>
            $name));
        } catch (\Exception $e) {
            throw new RepositoryException("Couldn't delete nodes in workspace
            '$name': ".$e->getMessage(), 0, $e);
        }

        try {
            $this->conn->delete('phpcr_binarydata', array('workspace_name'=>
            $name));
        } catch (\Exception $e) {
            throw new RepositoryException("Couldn't delete binary data in
            workspace '$name': ".$e->getMessage(), 0, $e);
        }
    }

    /**
     * {@inheritDoc}
     */
    public function login(CredentialsInterface $credentials = null, $workspaceName = null)
    {
        $this->credentials = $credentials;

        if (empty($workspaceName)) {
            $workspaceName = 'default';
        }
        $this->workspaceName = $workspaceName;

        if (!$this->checkLoginOnServer) {
            return $workspaceName;
        }

        if (!$this->workspaceExists($workspaceName)) {
            if ('default' !== $workspaceName) {
                throw new NoSuchWorkspaceException("Requested workspace: '$workspaceName'");
            }

            // create default workspace if it not exists
            $this->createWorkspace($workspaceName);
        }

        $this->loggedIn = true;

        return $workspaceName;
    }

    /**
     * {@inheritDoc}
     */
    public function logout()
    {
        if ($this->loggedIn) {
            $this->loggedIn = false;
            $this->conn->close();
            $this->conn = null;
        }
    }

    /**
     * Configure whether to check if we are logged in before doing a request.
     *
     * Will improve error reporting at the cost of some round trips.
     */
    public function setCheckLoginOnServer($bool)
    {
        $this->checkLoginOnServer = $bool;
    }

    protected function workspaceExists($workspaceName)
    {
        try {
            $query = 'SELECT 1 FROM phpcr_workspaces WHERE name = ?';
            $result = $this->conn->fetchColumn($query, array($workspaceName));
        } catch (\Exception $e) {
            if ($e instanceof DBALException || $e instanceof \PDOException) {
                if (1045 == $e->getCode()) {
                    throw new LoginException('Access denied with your credentials: '.$e->getMessage());
                }
                if ('42S02' == $e->getCode()) {
                    throw new RepositoryException('You did not properly set up the database for the repository. See README.md for more information. Message from backend: '.$e->getMessage());
                }

                throw new RepositoryException('Unexpected error talking to the backend: '.$e->getMessage());
            }

            throw $e;
        }

        return $result;
    }

    /**
     * Ensure that we are currently logged in, executing the login in case we
     * did lazy login.
     *
     * @throws RepositoryException if this transport is not logged in.
     */
    protected function assertLoggedIn()
    {
        if (!$this->loggedIn) {
            if (!$this->checkLoginOnServer && $this->workspaceName) {
                $this->checkLoginOnServer = true;
                if ($this->login($this->credentials, $this->workspaceName)) {
                    return;
                }
            }

            throw new RepositoryException('You need to be logged in for this operation');
        }
    }

    /**
     * {@inheritDoc}
     */
    public function getRepositoryDescriptors()
    {
        return array(
            RepositoryInterface::IDENTIFIER_STABILITY => RepositoryInterface::IDENTIFIER_STABILITY_INDEFINITE_DURATION,
            RepositoryInterface::REP_NAME_DESC  => 'jackalope_doctrine_dbal',
            RepositoryInterface::REP_VENDOR_DESC => 'Jackalope Community',
            RepositoryInterface::REP_VENDOR_URL_DESC => 'http://github.com/jackalope',
            RepositoryInterface::REP_VERSION_DESC => '1.0.0-DEV',
            RepositoryInterface::SPEC_NAME_DESC => 'Content Repository for PHP',
            RepositoryInterface::SPEC_VERSION_DESC => '2.1',
            RepositoryInterface::NODE_TYPE_MANAGEMENT_AUTOCREATED_DEFINITIONS_SUPPORTED => true,
            RepositoryInterface::NODE_TYPE_MANAGEMENT_INHERITANCE => RepositoryInterface::NODE_TYPE_MANAGEMENT_INHERITANCE_SINGLE,
            RepositoryInterface::NODE_TYPE_MANAGEMENT_MULTIPLE_BINARY_PROPERTIES_SUPPORTED => true,
            RepositoryInterface::NODE_TYPE_MANAGEMENT_MULTIVALUED_PROPERTIES_SUPPORTED => true,
            RepositoryInterface::NODE_TYPE_MANAGEMENT_ORDERABLE_CHILD_NODES_SUPPORTED => true,
            RepositoryInterface::NODE_TYPE_MANAGEMENT_OVERRIDES_SUPPORTED => false,
            RepositoryInterface::NODE_TYPE_MANAGEMENT_PRIMARY_ITEM_NAME_SUPPORTED => true,
            RepositoryInterface::NODE_TYPE_MANAGEMENT_PROPERTY_TYPES => true,
            RepositoryInterface::NODE_TYPE_MANAGEMENT_RESIDUAL_DEFINITIONS_SUPPORTED => false,
            RepositoryInterface::NODE_TYPE_MANAGEMENT_SAME_NAME_SIBLINGS_SUPPORTED => false,
            RepositoryInterface::NODE_TYPE_MANAGEMENT_UPDATE_IN_USE_SUPPORTED => false,
            RepositoryInterface::NODE_TYPE_MANAGEMENT_VALUE_CONSTRAINTS_SUPPORTED => false,
            RepositoryInterface::OPTION_ACCESS_CONTROL_SUPPORTED => false,
            RepositoryInterface::OPTION_ACTIVITIES_SUPPORTED => false,
            RepositoryInterface::OPTION_BASELINES_SUPPORTED => false,
            RepositoryInterface::OPTION_JOURNALED_OBSERVATION_SUPPORTED => false,
            RepositoryInterface::OPTION_LIFECYCLE_SUPPORTED => false,
            RepositoryInterface::OPTION_LOCKING_SUPPORTED => false,
            RepositoryInterface::OPTION_NODE_AND_PROPERTY_WITH_SAME_NAME_SUPPORTED => true,
            RepositoryInterface::OPTION_NODE_TYPE_MANAGEMENT_SUPPORTED => true,
            RepositoryInterface::OPTION_OBSERVATION_SUPPORTED => false,
            RepositoryInterface::OPTION_RETENTION_SUPPORTED => false,
            RepositoryInterface::OPTION_SHAREABLE_NODES_SUPPORTED => false,
            RepositoryInterface::OPTION_SIMPLE_VERSIONING_SUPPORTED => false,
            RepositoryInterface::OPTION_TRANSACTIONS_SUPPORTED => true,
            RepositoryInterface::OPTION_UNFILED_CONTENT_SUPPORTED => true,
            RepositoryInterface::OPTION_UPDATE_MIXIN_NODETYPES_SUPPORTED => true,
            RepositoryInterface::OPTION_UPDATE_PRIMARY_NODETYPE_SUPPORTED => true,
            RepositoryInterface::OPTION_VERSIONING_SUPPORTED => false,
            RepositoryInterface::OPTION_WORKSPACE_MANAGEMENT_SUPPORTED => true,
            RepositoryInterface::OPTION_XML_EXPORT_SUPPORTED => true,
            RepositoryInterface::OPTION_XML_IMPORT_SUPPORTED => true,
            RepositoryInterface::QUERY_FULL_TEXT_SEARCH_SUPPORTED => true,
            RepositoryInterface::QUERY_CANCEL_SUPPORTED => false,
            RepositoryInterface::QUERY_JOINS => RepositoryInterface::QUERY_JOINS_NONE,
            RepositoryInterface::QUERY_LANGUAGES => array(QueryInterface::JCR_SQL2, QueryInterface::JCR_JQOM),
            RepositoryInterface::QUERY_STORED_QUERIES_SUPPORTED => false,
            RepositoryInterface::WRITE_SUPPORTED => true,
        );
    }

    /**
     * {@inheritDoc}
     */
    public function getNamespaces()
    {
        if (empty($this->namespaces)) {
            $query = 'SELECT * FROM phpcr_namespaces';
            $data = $this->conn->fetchAll($query);

            $this->namespaces = array(
                NamespaceRegistryInterface::PREFIX_EMPTY => NamespaceRegistryInterface::NAMESPACE_EMPTY,
                NamespaceRegistryInterface::PREFIX_JCR => NamespaceRegistryInterface::NAMESPACE_JCR,
                NamespaceRegistryInterface::PREFIX_NT => NamespaceRegistryInterface::NAMESPACE_NT,
                NamespaceRegistryInterface::PREFIX_MIX => NamespaceRegistryInterface::NAMESPACE_MIX,
                NamespaceRegistryInterface::PREFIX_XML => NamespaceRegistryInterface::NAMESPACE_XML,
                NamespaceRegistryInterface::PREFIX_SV => NamespaceRegistryInterface::NAMESPACE_SV,
            );

            foreach ($data as $row) {
                $this->namespaces[$row['prefix']] = $row['uri'];
            }
        }

        return $this->namespaces;
    }

    /**
     * {@inheritDoc}
     */
    public function copyNode($srcAbsPath, $dstAbsPath, $srcWorkspace = null)
    {
        $this->assertLoggedIn();

        $workspaceName = $this->workspaceName;
        if (null !== $srcWorkspace) {
            if (!$this->workspaceExists($srcWorkspace)) {
                throw new NoSuchWorkspaceException("Source workspace '$srcWorkspace' does not exist.");
            }
        }

        PathHelper::assertValidAbsolutePath($dstAbsPath, true);

        $srcNodeId = $this->pathExists($srcAbsPath);
        if (!$srcNodeId) {
            throw new PathNotFoundException("Source path '$srcAbsPath' not found");
        }

        if ($this->pathExists($dstAbsPath)) {
            throw new ItemExistsException("Cannot copy to destination path '$dstAbsPath' that already exists.");
        }

        if (!$this->pathExists(PathHelper::getParentPath($dstAbsPath))) {
            throw new PathNotFoundException("Parent of the destination path '" . $dstAbsPath . "' has to exist.");
        }

        // Algorithm:
        // 1. Select all nodes with path $srcAbsPath."%" and iterate them
        // 2. create a new node with path $dstAbsPath + leftovers, with a new uuid. Save old => new uuid
        // 3. copy all properties from old node to new node
        // 4. if a reference is in the properties, either update the uuid based on the map if its inside the copied graph or keep it.
        // 5. "May drop mixin types"

        $query = 'SELECT * FROM phpcr_nodes WHERE path LIKE ? AND workspace_name = ?';
        $stmt = $this->conn->executeQuery($query, array($srcAbsPath . '%', $workspaceName));

        foreach ($stmt->fetchAll(\PDO::FETCH_ASSOC) as $row) {
            $newPath = str_replace($srcAbsPath, $dstAbsPath, $row['path']);

            $dom = new \DOMDocument('1.0', 'UTF-8');
            $dom->loadXML($row['props']);

            $propsData = array('dom' => $dom);
            // when copying a node, the copy is always a new node. set $isNewNode to true
            $newNodeId = $this->syncNode(null, $newPath, $row['type'], true, array(), $propsData);

            $query = 'INSERT INTO phpcr_binarydata (node_id, property_name, workspace_name, idx, data)'.
                '   SELECT ?, b.property_name, ?, b.idx, b.data FROM phpcr_binarydata b WHERE b.node_id = ?';

            try {
                $this->conn->executeUpdate($query, array($newNodeId, $this->workspaceName, $srcNodeId));
            } catch (DBALException $e) {
                throw new RepositoryException("Unexpected exception while copying node from $srcAbsPath to $dstAbsPath", $e->getCode(), $e);
            }
        }
    }

    /**
     * @param string $path
     *
     * @return array
     */
    private function getJcrName($path)
    {
        $name = implode('', array_slice(explode('/', $path), -1, 1));
        if (($aliasLength = strpos($name, ':')) !== false) {
            $alias = substr($name, 0, $aliasLength);
            $name = substr($name, $aliasLength + 1);
        } else {
            $alias = '';
        }

        $namespaces = $this->getNamespaces();

        if (!isset($namespaces[$alias])) {
            throw new NamespaceException('the namespace ' . $alias . ' was not registered.');
        }

        return array($namespaces[$alias], $name);
    }

    /**
     * Actually write the node into the database
     *
     * @param string  $uuid      node uuid
     * @param string  $path      absolute path of the node
     * @param string  $type      node type
     * @param boolean $isNewNode new nodes to insert (true) or existing node to update (false)
     * @param array   $props
     * @param array   $propsData
     *
     * @return boolean|string
     *
     * @throws ItemExistsException
     * @throws RepositoryException
     */
    private function syncNode($uuid, $path, $type, $isNewNode, $props = array(), $propsData = array())
    {
        // TODO: Not sure if there are always ALL props in $props, should we grab the online data here?
        // TODO: Binary data is handled very inefficiently here, UPSERT will really be necessary here as well as lazy handling

        if (!$propsData) {
            $propsData = $this->propsToXML($props);
        }

        if (null === $uuid) {
            $uuid = UUIDHelper::generateUUID();
        }

        if ($isNewNode) {
            list($namespace, $localName) = $this->getJcrName($path);

            $qb = $this->conn->createQueryBuilder();

            $qb->select(':identifier, :type, :path, :local_name, :namespace, :parent, :workspace_name, :props, :depth, COALESCE(MAX(n.sort_order), 0) + 1')
                ->from('phpcr_nodes', 'n')
                ->where('n.parent = :parent_a');

            $sql = $qb->getSql();

            try {
                $insert = "INSERT INTO phpcr_nodes (identifier, type, path, local_name, namespace, parent, workspace_name, props, depth, sort_order) " . $sql;
                $this->conn->executeUpdate($insert, array(
                    'identifier'    => $uuid,
                    'type'          => $type,
                    'path'          => $path,
                    'local_name'    => $localName,
                    'namespace'     => $namespace,
                    'parent'        => PathHelper::getParentPath($path),
                    'workspace_name'  => $this->workspaceName,
                    'props'         => $propsData['dom']->saveXML(),
                    'depth'         => PathHelper::getPathDepth($path),
                    'parent_a'      => PathHelper::getParentPath($path),
                ));
            } catch (\PDOException $e) {
                throw new ItemExistsException('Item ' . $path . ' already exists in the database');
            } catch (DBALException $e) {
                throw new ItemExistsException('Item ' . $path . ' already exists in the database');
            }

            $nodeId = $this->conn->lastInsertId($this->sequenceNodeName);
        } else {
            $nodeId = $this->pathExists($path);
            if (!$nodeId) {
                throw new RepositoryException("nodeId for $path not found");
            }
            $this->conn->update('phpcr_nodes', array('props' => $propsData['dom']->saveXML()), array('id' => $nodeId));
        }

        $this->nodeIdentifiers[$path] = $uuid;

        if (!empty($propsData['binaryData'])) {
            $this->syncBinaryData($nodeId, $propsData['binaryData']);
        }

        if (!empty($propsData['references'])) {
            $this->referencesToUpdate[$nodeId] = array('path' => $path, 'properties' => $propsData['references']);
        }

        return $nodeId;
    }

    private function syncBinaryData($nodeId, $binaryData)
    {
        foreach ($binaryData as $propertyName => $binaryValues) {
            foreach ($binaryValues as $idx => $data) {
                // TODO verify in which cases we can just update
                $params = array(
                    'node_id'       => $nodeId,
                    'property_name' => $propertyName,
                    'workspace_name'  => $this->workspaceName,
                );
                $this->conn->delete('phpcr_binarydata', $params);

                $params['idx'] = $idx;
                $params['data'] = $data;
                $types = array(
                    \PDO::PARAM_INT,
                    \PDO::PARAM_STR,
                    \PDO::PARAM_STR,
                    \PDO::PARAM_INT,
                    \PDO::PARAM_LOB
                );
                $this->conn->insert('phpcr_binarydata', $params, $types);
            }
        }
    }

    private function syncReferences()
    {
        if ($this->referencesToUpdate) {
            // do not update references that are going to be deleted anyways
            $toUpdate = array_diff_assoc($this->referencesToUpdate, $this->referencesToDelete);

            try {
                foreach ($this->referenceTables as $table) {
                    $query = "DELETE FROM $table WHERE source_id IN (?)";
                    $this->conn->executeUpdate($query, array(array_keys($toUpdate)), array(Connection::PARAM_INT_ARRAY));
                }
            } catch (DBALException $e) {
                throw new RepositoryException('Unexpected exception while cleaning up after saving', $e->getCode(), $e);
            }

            foreach ($toUpdate as $nodeId => $references) {
                foreach ($references['properties'] as $name => $data) {
                    foreach ($data['values'] as $value) {
                        try {
                            $params = array(
                                'source_id' => $nodeId,
                                'source_property_name' => $name,
                                'target_id' => $this->pathExists(self::getNodePathForIdentifier($value)),
                            );

                            $this->conn->insert($this->referenceTables[$data['type']], $params);
                        } catch (ItemNotFoundException $e) {
                            if (PropertyType::REFERENCE === $data['type']) {
                                throw new ReferentialIntegrityException(
                                    "Trying to store reference to non-existant node with path '$value' in node '{$references['path']}' property '$name'."
                                );
                            }
                        }
                    }
                }
            }
        }

        // TODO on RDBMS that support deferred FKs we could skip this step
        if ($this->referencesToDelete) {
            $params = array(array_keys($this->referencesToDelete));

            // remove all PropertyType::REFERENCE with a source_id on a deleted node
            try {
                $query = "DELETE FROM phpcr_nodes_references WHERE source_id IN (?)";
                $this->conn->executeUpdate($query, $params, array(Connection::PARAM_INT_ARRAY));
            } catch (DBALException $e) {
                throw new RepositoryException('Unexpected exception while cleaning up deleted nodes', $e->getCode(), $e);
            }

            // ensure that there are no PropertyType::REFERENCE pointing to nodes that will be deleted
            // Note: due to the outer join we cannot filter on workspace_name, but this is ok
            // since within a transaction there can never be missing referenced nodes within the current workspace
            // make sure the target node is not in the list of nodes being deleted, to allow deletion in same request
            $query = 'SELECT DISTINCT r.target_id
            FROM phpcr_nodes_references r
                LEFT OUTER JOIN phpcr_nodes n ON r.target_id = n.id
            WHERE r.target_id IN (?)';

            $stmt = $this->conn->executeQuery($query, $params, array(Connection::PARAM_INT_ARRAY));
            $missingTargets = $stmt->fetchAll(\PDO::FETCH_COLUMN);
            if ($missingTargets) {
                $paths = array();
                foreach ($missingTargets as $id) {
                    if (isset($this->referencesToDelete[$id])) {
                        $paths[] = $this->referencesToDelete[$id];
                    }
                }

                throw new ReferentialIntegrityException("Cannot delete '".implode("', '", $paths)."': A reference points to this node or a subnode");
            }

            // clean up all references
            try {
                foreach ($this->referenceTables as $table) {
                    $query = "DELETE FROM $table WHERE target_id IN (?)";
                    $this->conn->executeUpdate($query, $params, array(Connection::PARAM_INT_ARRAY));
                }
            } catch (DBALException $e) {
                throw new RepositoryException('Unexpected exception while cleaning up deleted nodes', $e->getCode(), $e);
            }
        }
    }

    public static function xmlToProps($xml, ValueConverter $valueConverter, $filter = null)
    {
        $data = new \stdClass();

        $dom = new \DOMDocument('1.0', 'UTF-8');
        $dom->loadXML($xml);

        foreach ($dom->getElementsByTagNameNS('http://www.jcp.org/jcr/sv/1.0', 'property') as $propertyNode) {
            $name = $propertyNode->getAttribute('sv:name');

            // only return the properties that pass through the filter callback
            if (null !== $filter && is_callable($filter) && false === $filter($name)) {
                continue;
            }

            $values = array();
            $type = PropertyType::valueFromName($propertyNode->getAttribute('sv:type'));
            foreach ($propertyNode->childNodes as $valueNode) {
                switch ($type) {
                    case PropertyType::NAME:
                    case PropertyType::URI:
                    case PropertyType::WEAKREFERENCE:
                    case PropertyType::REFERENCE:
                    case PropertyType::PATH:
                    case PropertyType::DECIMAL:
                    case PropertyType::STRING:
                        $values[] = $valueNode->nodeValue;
                        break;
                    case PropertyType::BOOLEAN:
                        $values[] = (bool) $valueNode->nodeValue;
                        break;
                    case PropertyType::LONG:
                        $values[] = (int) $valueNode->nodeValue;
                        break;
                    case PropertyType::BINARY:
                        $values[] = (int) $valueNode->nodeValue;
                        break;
                    case PropertyType::DATE:
                        $date = $valueNode->nodeValue;
                        if ($date) {
                            $date = new \DateTime($date);
                            $date->setTimezone(new \DateTimeZone(date_default_timezone_get()));
                            // Jackalope expects a string, might make sense to refactor to allow \DateTime instances too
                            $date = $valueConverter->convertType($date, PropertyType::STRING);
                        }
                        $values[] = $date;
                        break;
                    case PropertyType::DOUBLE:
                        $values[] = (double) $valueNode->nodeValue;
                        break;
                    default:
                        throw new \InvalidArgumentException("Type with constant $type not found.");
                }
            }

            switch ($type) {
                case PropertyType::BINARY:
                    $data->{':' . $name} = $propertyNode->getAttribute('sv:multi-valued') ? $values : $values[0];
                    break;
                default:
                    $data->{$name} = $propertyNode->getAttribute('sv:multi-valued') ? $values : $values[0];
                    $data->{':' . $name} = $type;
                    break;
            }
        }

        return $data;
    }

    /**
     * Seperate properties array into an xml and binary data.
     *
     * @param array   $properties
     * @param boolean $inlineBinaries
     *
     * @return array ('dom' => $dom, 'binaryData' => streams, 'references' => array('type' => INT, 'values' => array(UUIDs)))
     */
    private function propsToXML($properties, $inlineBinaries = false)
    {
        $namespaces = array(
            'mix' => "http://www.jcp.org/jcr/mix/1.0",
            'nt' => "http://www.jcp.org/jcr/nt/1.0",
            'xs' => "http://www.w3.org/2001/XMLSchema",
            'jcr' => "http://www.jcp.org/jcr/1.0",
            'sv' => "http://www.jcp.org/jcr/sv/1.0",
            'rep' => "internal"
        );

        $dom = new \DOMDocument('1.0', 'UTF-8');
        $rootNode = $dom->createElement('sv:node');
        foreach ($namespaces as $namespace => $uri) {
            $rootNode->setAttribute('xmlns:' . $namespace, $uri);
        }
        $dom->appendChild($rootNode);

        $binaryData = $references = array();
        foreach ($properties as $property) {
            /* @var $property Property */
            $propertyNode = $dom->createElement('sv:property');
            $propertyNode->setAttribute('sv:name', $property->getName());
            $propertyNode->setAttribute('sv:type', PropertyType::nameFromValue($property->getType()));
            $propertyNode->setAttribute('sv:multi-valued', $property->isMultiple() ? '1' : '0');

            switch ($property->getType()) {
                case PropertyType::WEAKREFERENCE:
                case PropertyType::REFERENCE:
                    $references[$property->getName()] = array(
                        'type' => $property->getType(),
                        'values' => $property->isMultiple() ? array_unique($property->getString()) : array($property->getString()),
                    );
                case PropertyType::NAME:
                case PropertyType::URI:
                case PropertyType::PATH:
                case PropertyType::STRING:
                    $values = $property->getString();
                    break;
                case PropertyType::DECIMAL:
                    $values = $property->getDecimal();
                    break;
                case PropertyType::BOOLEAN:
                    $values = array_map('intval', (array) $property->getBoolean());
                    break;
                case PropertyType::LONG:
                    $values = $property->getLong();
                    break;
                case PropertyType::BINARY:
                    if ($property->isNew() || $property->isModified()) {
                        $values = array();
                        foreach ((array) $property->getValueForStorage() as $stream) {
                            if (null === $stream) {
                                $binary = '';
                            } else {
                                $binary = stream_get_contents($stream);
                                fclose($stream);
                            }
                            $binaryData[$property->getName()][] = $binary;
                            $values[] = strlen($binary);
                        }
                    } else {
                        $values = $property->getLength();
                        if (!$property->isMultiple() && empty($values)) {
                            $values = array(0);
                        }
                    }
                    break;
                case PropertyType::DATE:
                    $values = $property->getDate();
                    if ($values instanceof \DateTime) {
                        $values = array($values);
                    }
                    foreach ((array) $values as $key => $date) {
                        if ($date instanceof \DateTime) {
                            $date->setTimezone(new \DateTimeZone('UTC'));
                        }
                        $values[$key] = $date;
                    }
                    $values = $this->valueConverter->convertType($values, PropertyType::STRING);
                    break;
                case PropertyType::DOUBLE:
                    $values = $property->getDouble();
                    break;
                default:
                    throw new RepositoryException('unknown type '.$property->getType());
            }

            foreach ((array) $values as $value) {
                $element = $propertyNode->appendChild($dom->createElement('sv:value'));
                $element->appendChild($dom->createTextNode($value));
            }

            $rootNode->appendChild($propertyNode);
        }

        return array('dom' => $dom, 'binaryData' => $binaryData, 'references' => $references);
    }

    /**
     * {@inheritDoc}
     */
    public function getAccessibleWorkspaceNames()
    {
        $query = "SELECT DISTINCT name FROM phpcr_workspaces";
        $stmt = $this->conn->executeQuery($query);

        return $stmt->fetchAll(\PDO::FETCH_COLUMN);
    }

    /**
     * {@inheritDoc}
     */
    public function getNode($path)
    {
        $this->assertLoggedIn();
        PathHelper::assertValidAbsolutePath($path);

        $values[':path'] = $path;
        $values[':pathd'] = rtrim($path,'/') . '/%';
        $values[':workspace'] = $this->workspaceName;
        $values[':fetchDepth'] = $this->fetchDepth;

        $query = 'SELECT * FROM phpcr_nodes
            WHERE (path LIKE :pathd OR path = :path)
                AND workspace_name = :workspace
                AND depth <= ((SELECT depth FROM phpcr_nodes WHERE path = :path AND workspace_name = :workspace) + :fetchDepth)
            ORDER BY sort_order ASC';

        $stmt = $this->conn->executeQuery($query, $values);
        $rows = $stmt->fetchAll(\PDO::FETCH_ASSOC);

        $nodeData = array();
        foreach ($rows as $row) {
            if ($row['path'] === $path) {
                $node = $this->getNodeData($path, $row);
            } else {
                $pathDiff = ltrim(substr($row['path'], strlen($path)),'/');
                $nodeData[$pathDiff] = $this->getNodeData($row['path'], $row);
            }
        }

        if (empty($node)) {
            throw new ItemNotFoundException("Item $path not found in workspace ".$this->workspaceName);
        }

        foreach ($nodeData as $key => $value) {
            $node->{$key} = $value;
        }

        return $node;
    }

    private function getNodeData($path, $row)
    {
        $this->nodeIdentifiers[$path] = $row['identifier'];

        $data = self::xmlToProps($row['props'], $this->valueConverter);
        $data->{'jcr:primaryType'} = $row['type'];

        $query = 'SELECT path FROM phpcr_nodes WHERE parent = ? AND workspace_name = ? ORDER BY sort_order ASC';
        $children = $this->conn->fetchAll($query, array($path, $this->workspaceName));
        foreach ($children as $child) {
            $childName = explode('/', $child['path']);
            $childName = end($childName);
            if (!isset($data->{$childName})) {
                $data->{$childName} = new \stdClass();
            }
        }

        // If the node is referenceable, return jcr:uuid.
        if (isset($data->{"jcr:mixinTypes"})) {
            foreach ((array) $data->{"jcr:mixinTypes"} as $mixin) {
                if ($this->nodeTypeManager->getNodeType($mixin)->isNodeType('mix:referenceable')) {
                    $data->{'jcr:uuid'} = $row['identifier'];
                    break;
                }
            }
        }

        return $data;
    }

    /**
     * {@inheritDoc}
     */
    public function getNodes($paths)
    {
        $this->assertLoggedIn();

        if (empty($paths)) {
            return array();
        }

        foreach ($paths as $path) {
            PathHelper::assertValidAbsolutePath($path);
        }

        $params[':workspace'] = $this->workspaceName;
        $params[':fetchDepth'] = $this->fetchDepth;

        $query = 'SELECT path AS arraykey, id, path, parent, local_name, namespace, workspace_name, identifier, type, props, depth, sort_order
            FROM phpcr_nodes WHERE workspace_name = :workspace AND (';

        $i = 0;
        foreach ($paths as $path) {
            $params[':path'.$i] = $path;
            $params[':pathd'.$i] = rtrim($path,'/') . '/%';
            $subquery = 'SELECT depth FROM phpcr_nodes WHERE path = :path'.$i.' AND workspace_name = :workspace';
            $query .= '(path LIKE :pathd'.$i.' OR path = :path'.$i.') AND depth <= ((' . $subquery . ') + :fetchDepth) OR ';
            $i++;
        }

        $query = rtrim($query, 'OR ');
        $query .= ') ORDER BY sort_order ASC';

        $stmt = $this->conn->executeQuery($query, $params);
        $all = $stmt->fetchAll(\PDO::FETCH_UNIQUE | \PDO::FETCH_GROUP);

        $nodes = array();
        foreach ($paths as $path) {
            if (isset($all[$path])) {
                $nodes[$path] = $this->getNodeData($path, $all[$path]);
            }
        }

        return $nodes;
    }

    private function pathExists($path)
    {
        $query = 'SELECT id FROM phpcr_nodes WHERE path = ? AND workspace_name = ?';
        if ($nodeId = $this->conn->fetchColumn($query, array($path, $this->workspaceName))) {
            return $nodeId;
        }

        return false;
    }

    /**
     * {@inheritDoc}
     */
    public function getNodeByIdentifier($uuid)
    {
        $this->assertLoggedIn();

        $query = 'SELECT * FROM phpcr_nodes WHERE identifier = ? AND workspace_name = ?';
        $row = $this->conn->fetchAssoc($query, array($uuid, $this->workspaceName));
        if (!$row) {
            throw new ItemNotFoundException("Item $uuid not found in workspace ".$this->workspaceName);
        }

        $path = $row['path'];
        $data = $this->getNodeData($path, $row);
        $data->{':jcr:path'} = $path;

        return $data;
    }

    /**
     * {@inheritDoc}
     */
    public function getNodesByIdentifier($identifiers)
    {
        $this->assertLoggedIn();
        if (empty($identifiers)) {
            return array();
        }

        $query = 'SELECT identifier AS arraykey, id, path, parent, local_name, namespace, workspace_name, identifier, type, props, depth, sort_order
            FROM phpcr_nodes WHERE workspace_name = ? AND identifier IN (?)';
        $params = array($this->workspaceName, $identifiers);
        $stmt = $this->conn->executeQuery($query, $params, array(\PDO::PARAM_STR, Connection::PARAM_STR_ARRAY));
        $all = $stmt->fetchAll(\PDO::FETCH_UNIQUE | \PDO::FETCH_GROUP);

        $nodes = array();
        foreach ($identifiers as $id) {
            if (isset($all[$id])) {
                $path = $all[$id]['path'];
                $nodes[$path] = $this->getNodeData($path, $all[$id]);
            }
        }

        return $nodes;
    }

    /**
     * {@inheritDoc}
     */
    public function getNodePathForIdentifier($uuid, $workspace = null)
    {
        if (null !== $workspace) {
            throw new NotImplementedException('Specifying the workspace is not yet supported.');
        }

        $this->assertLoggedIn();

        $query = "SELECT path FROM phpcr_nodes WHERE identifier = ? AND workspace_name = ?";
        $path = $this->conn->fetchColumn($query, array($uuid, $this->workspaceName));
        if (!$path) {
            throw new ItemNotFoundException("no item found with uuid ".$uuid);
        }

        return $path;
    }

    /**
     * {@inheritDoc}
     */
    public function deleteNodes(array $operations)
    {
        $this->assertLoggedIn();

        foreach ($operations as $op) {
            $this->deleteNode($op->srcPath);
        }

        return true;
    }

    /**
     * {@inheritDoc}
     */
    public function deleteNodeImmediately($path)
    {
        $this->prepareSave();
        $this->deleteNode($path);
        $this->finishSave();

        return true;
    }

    /**
     * TODO instead of calling the deletes separately, we should batch the delete query
     * but careful with the caching!
     *
     * @param string $path node path to delete
     *
     */
    protected function deleteNode($path)
    {
        if ('/' == $path) {
            throw new ConstraintViolationException('You can not delete the root node of a repository');
        }

        $nodeId = $this->pathExists($path);
        if (!$nodeId) {
            throw new ItemNotFoundException("No node found at ".$path);
        }

        $params = array($path, $path."/%", $this->workspaceName);

        // TODO on RDBMS that support deferred FKs we could skip this step
        $query = 'SELECT id, path FROM phpcr_nodes WHERE (path = ? OR path LIKE ?) AND workspace_name = ?';
        $stmt = $this->conn->executeQuery($query, $params);
        $this->referencesToDelete += $stmt->fetchAll(\PDO::FETCH_UNIQUE | \PDO::FETCH_COLUMN);

        try {
            $query = 'DELETE FROM phpcr_nodes WHERE (path = ? OR path LIKE ?) AND workspace_name = ?';
            $this->conn->executeUpdate($query, $params);
        } catch (DBALException $e) {
            throw new RepositoryException('Unexpected exception while deleting node ' . $path, $e->getCode(), $e);
        }
    }

    /**
     * {@inheritDoc}
     */
    public function deleteProperties(array $operations)
    {
        $this->assertLoggedIn();

        foreach ($operations as $op) {
            $this->deleteProperty($op->srcPath);
        }

        return true;
    }

    /**
     * {@inheritDoc}
     */
    public function deletePropertyImmediately($path)
    {
        $this->prepareSave();
        $this->deleteProperty($path);
        $this->finishSave();

        return true;
    }

    /**
     * {@inheritDoc}
     */
    protected function deleteProperty($path)
    {
        $this->assertLoggedIn();

        $nodePath = PathHelper::getParentPath($path);
        $nodeId = $this->pathExists($nodePath);
        if (!$nodeId) {
            // no we really don't know that path
            throw new ItemNotFoundException("No item found at ".$path);
        }

        $query = 'SELECT props FROM phpcr_nodes WHERE id = ?';
        $xml = $this->conn->fetchColumn($query, array($nodeId));

        $dom = new \DOMDocument('1.0', 'UTF-8');
        $dom->loadXml($xml);

        $found = false;
        $propertyName = PathHelper::getNodeName($path);
        foreach ($dom->getElementsByTagNameNS('http://www.jcp.org/jcr/sv/1.0', 'property') as $propertyNode) {
            if ($propertyName == $propertyNode->getAttribute('sv:name')) {
                $found = true;
                // would be nice to have the property object to ask for type
                // but its in state deleted, would mean lots of refactoring
                if ($propertyNode->hasAttribute('sv:type')) {
                    $type = strtolower($propertyNode->getAttribute('sv:type'));
                    if (in_array($type, array('reference', 'weakreference'))) {
                        $table = $this->referenceTables['reference' === $type ? PropertyType::REFERENCE : PropertyType::WEAKREFERENCE];
                        try {
                            $query = "DELETE FROM $table WHERE source_id = ? AND source_property_name = ?";
                            $this->conn->executeUpdate($query, array($nodeId, $propertyName));
                        } catch (DBALException $e) {
                            throw new RepositoryException('Unexpected exception while cleaning up deleted nodes', $e->getCode(), $e);
                        }
                    }
                }
                $propertyNode->parentNode->removeChild($propertyNode);
                break;
            }
        }
        if (! $found) {
            throw new ItemNotFoundException("Node $nodePath has no property $propertyName");
        }
        $xml = $dom->saveXML();

        $query = 'UPDATE phpcr_nodes SET props = ? WHERE id = ?';
        $params = array($xml, $nodeId);

        try {
            $this->conn->executeUpdate($query, $params);
        } catch (DBALException $e) {
            throw new RepositoryException("Unexpected exception while updating properties of $path", $e->getCode(), $e);
        }
    }

    /**
     * {@inheritDoc}
     */
    public function moveNodes(array $operations)
    {
        /** @var $op MoveNodeOperation */
        foreach ($operations as $op) {
            $this->moveNode($op->srcPath, $op->dstPath);
        }

        return true;
    }

    /**
     * {@inheritDoc}
     */
    public function moveNodeImmediately($srcAbsPath, $dstAbspath)
    {
        $this->prepareSave();
        $this->moveNode($srcAbsPath, $dstAbspath);
        $this->finishSave();

        return true;
    }

    /**
     * Execute moving a single node
     */
    protected function moveNode($srcAbsPath, $dstAbsPath)
    {
        $this->assertLoggedIn();
        PathHelper::assertValidAbsolutePath($dstAbsPath, true);

        $srcNodeId = $this->pathExists($srcAbsPath);
        if (!$srcNodeId) {
            throw new PathNotFoundException("Source path '$srcAbsPath' not found");
        }

        if ($this->pathExists($dstAbsPath)) {
            throw new ItemExistsException("Cannot move '$srcAbsPath' to '$dstAbsPath' because destination node already exists.");
        }

        if (!$this->pathExists(PathHelper::getParentPath($dstAbsPath))) {
            throw new PathNotFoundException("Parent of the destination path '" . $dstAbsPath . "' has to exist.");
        }

        $query = 'SELECT path, id FROM phpcr_nodes WHERE path LIKE ? OR path = ? AND workspace_name = ? '
            . $this->conn->getDatabasePlatform()->getForUpdateSQL();
        $stmt = $this->conn->executeQuery($query, array($srcAbsPath . '/%', $srcAbsPath, $this->workspaceName));

        /*
         * TODO: https://github.com/jackalope/jackalope-doctrine-dbal/pull/26/files#L0R1057
         * the other thing i wonder: can't you do the replacement inside sql instead of loading and then storing
         * the node? this will be extremely slow for a large set of nodes. i think you should use query builder here
         * rather than raw sql, to make it work on a maximum of platforms.
         *
         * can you try to do this please? if we don't figure out how to do it, at least fix the where criteria, and
         * we can ask the doctrine community how to do the substring operation.
         * http://stackoverflow.com/questions/8619421/correct-syntax-for-d…