jackalope / jackalope-doctrine-dbal

Doctrine DBAL transport implementation for Jackalope

Jackalope

A powerful implementation of the PHPCR API.

You can use Jackalope with different storage backends. For now, we support:

• relational databases with the DoctrineDBAL backend. Works with any database supported by doctrine (mysql, postgres, ...) and has no dependency on java or jackrabbit. For the moment, it is less feature complete. See jackalope-doctrine-dbal
• Jackrabbit server backend supports many features and requires you to simply install a .jar file for the data store component. See jackalope-jackrabbit

Discuss on [email protected] or visit #jackalope on irc.freenode.net

License

This code is dual licensed under the MIT license and the Apache License Version 2.0. Please see the file LICENSE in this folder.

Install instructions

Go to the repository of the storage backend you want and follow the instructions there.

• jackalope-doctrine-dbal
• jackalope-jackrabbit

Implementation notes

See doc/architecture.md for an introduction how Jackalope is built. Have a look at the source files and generate the phpdoc.

Contributors

• Christian Stocker [email protected]
• David Buchmann [email protected]
• Tobias Ebnöther [email protected]
• Roland Schilter [email protected]
• Uwe Jäger [email protected]
• Lukas Kahwe Smith [email protected]
• Benjamin Eberlei [email protected]
• Daniel Barsotti [email protected]
• and many others

jackalope-doctrine-dbal's Issues

fix multi-valued queries

fix the bug reveiled in phpcr/phpcr-api-tests#81

Improve the performance of move node

Currently, two DB queries are needed inside move nodes:

1. Select the nodes concerned by the move
2. Update the concerned nodes

It would be better to do it in one step with the query builder and SQL string manipulation (do not forget to also update the depth value). See:
https://github.com/jackalope/jackalope-doctrine-dbal/blob/master/src/Jackalope/Transport/DoctrineDBAL/Client.php#L1425

QOMWalker:walkOperand always returns quoted string

QOMWalker:walkOperand always returns quoted string if $operand is instance of LiteralInterface, but it's not good in case of comparison. So, for instance, i'd like to select all items that have price greater than 100. In this case a part of sql query should look like (EXTRACTVALUE(n.props, '//sv:property[@sv:name="price"]/sv:value[1]') > 100, but actually it is (EXTRACTVALUE(n.props, '//sv:property[@sv:name="price"]/sv:value[1]') > '100' so mysql treat and compare it as string.

In my personal project I solved this porblem as follows:

...

if (ctype_digit($literal)){
  return $literal;
}

return $this->conn->quote($namespace.$literal);
...

But I'm pretty shure that there is a more appropriate solution.

sqlite does not support full outer join

probably not worth it to emulate it:
http://en.wikipedia.org/wiki/Join%5F%28SQL%29#Full%5Fouter%5Fjoin

so i would propose to fix the tests to skip PHPCR\Tests\Query\QuerySql2OperationsTest::testQueryRightJoin for sqlite.

add support for full text search ie. CONTAINS()

conceptually this should be similar to http://docs.doctrine-project.org/projects/doctrine1/en/latest/en/manual/searching.html
i.e. using an inverted index (http://en.wikipedia.org/wiki/Inverted_index) where we would split all text by word, drop that into a table and use that as a pre filter to reduce the number of rows that are considered for the XPaths.

for specs of CONTAINS() see http://www.day.com/specs/jcr/1.0/8.5.4.5_CONTAINS.html
its ok if initially we do not support the full "lucene" style query syntax

see also http://www.h2database.com/jcr/grammar.html for the grammar of SQL2

also not sure .. but maybe this could help to get started http://code.google.com/p/inverted-index/

properly error report join selectors without alias

follow up for #125

testWorkspaceCopy throws a RepositoryException

CopyMethodsTest::testWorkspaceCopy of phpcr-api-tests throws a RepositoryException:

PHPCR\RepositoryException : something weird
#0 /Users/adriennicolet/PhpstormProjects/jackalope-doctrine-dbal/vendor/jackalope/jackalope/src/Jackalope/Property.php(145): PHPCR\PropertyType::convertType('2009-04-27T13:0...', 1)

reference to the phpcr-api-tests:
https://github.com/phpcr/phpcr-api-tests/blob/master/tests/10_Writing/CopyMethodsTest.php#L27

Add a node with unregistered namespace doesn't throw an exception

Should throw a Repository exception

Cannot run test suite with PHPUnit 3.7.1 (installed via Composer)

I am unable to run tests using PHPUnit 3.7.1 installed via Composer. It simply returns to the command prompt pretty much immediately.

altern8@datsik:~/workspaces/jackalope-doctrine-dbal$ phpunit
altern8@datsik:~/workspaces/jackalope-doctrine-dbal$ 

Can be duplicated by doing the following:

mkdir ~/phpunit-3.7.1-composer
cd ~/phpunit-3.7.1-composer
composer init -n --require="phpunit/phpunit:3.7.1" --require="phpunit/dbunit:1.2.*@dev"
composer install

Using dbunit 1.2 dev because there is an error in the last stable release's composer.json. :(

Then use it as:

~/phpunit-3.7.1-composer/vendor/bin/phpunit

Removing possibility to select nodeName

For PR #115 I added the possibility to select the nodename (jcr:name would return the local_name column).
But the tests I added for that (see phpcr/phpcr-api-tests#102) showed that jackrabbit doesn't support the selection of the nodename via jcr:name.

According to the JCR2.0 SQL-2 Grammer a nodename is a dynamic operand, and can not be used as column.

For this I changed the tests I created for the left/right joins, so they use the basename of the path instead of the jcr:name column.
Because of that change, those tests will now fail for jackalope/doctrine-dbal. I will create a PR for that. (Probably today)

But is this really correct? Or am I missing something and should jcr:name return the nodename?

/cc @dbu @lsmith77

Node type definition does not allow to set the property with name 'phpcr:classparents' and value 'array'

Hi @lsmith77
I have switched our setup from jackrabbit to postgres and i now have problems loading the fixtures data.
Cause of the error message is a node like this

/**
 * Image file child
 *
 * @PHPCRODM\Child(name="image.jpg", cascade="persist")
 */
protected $image;

If i comment this line the fixtures will load. If you need help reproducing this - checkout the branch cmf_base_jackalop_doctrine_dbal

date literal in query invalid

Creating this query using the QueryBuilder:

$qb = $this->dm->createQueryBuilder();
$qb->from()
    ->document('Symfony\Cmf\Bundle\BlockBundle\Doctrine\Phpcr\SimpleBlock', 'b')
  ->end()
  ->where()
    ->lt()->field('b.createdAt')->literal(new \DateTime())
  ->end()
;

generates this sql-2 query:

SELECT * FROM [nt:unstructured] AS b 
WHERE (b.[jcr:created] < CAST('2013-11-11T14:50:35.000+01:00' AS DATE)
AND (b.[phpcr:class] = 'Symfony\Cmf\Bundle\BlockBundle\Doctrine\Phpcr\SimpleBlock' OR b.[phpcr:classparents] = 'Symfony\Cmf\Bundle\BlockBundle\Doctrine\Phpcr\SimpleBlock'))

Running the query using app/console doctrine:phpcr:workspace:query tells the query is in invalid (PHPCR\Query\InvalidQueryException), and in code returns no results.

Running the same query when using jackalope-jackrabbit is succeeding.

Generate properties as required by node types

As mentioned in issue #21, the property jcr:created isn't generated with jackalope-doctrine-dbal. This makes DeleteMethodsTest::testNodeRemovePropertyConstraintViolation test fail.

Implement Join

Re-activate QuerySql2OperationsTest testQueryJoin and testQueryJoinReference and implement the join operation in the QOMWalker and the result handling.

Some import tests fail

The test case 11_Import of phpcr-api-tests get failures for 3 test methods inside jackalope-doctrine-dbal:

• testImportXMLUuidReplaceExistingWorkspace
• testImportXMLUuidReplaceExistingSession
• testImportXMLUuidReplaceRoot

1) PHPCR\Tests\Import\ImportRepositoryContentTest::testImportXMLUuidReplaceExistingSession
PHPCR\ReferentialIntegrityException: Trying to store reference to non-existant node with path '842e61c0-09ab-42a9-87c0-308ccc90e6f4' in node /tests_general_base/numberPropertyNode/jcr:content property ref

/path/to/jackalope-doctrine-dbal/src/Jackalope/Transport/DoctrineDBAL/Client.php:556
/path/to/jackalope-doctrine-dbal/src/Jackalope/Transport/DoctrineDBAL/Client.php:489
/path/to/jackalope-doctrine-dbal/src/Jackalope/Transport/DoctrineDBAL/Client.php:1166
/path/to/jackalope-doctrine-dbal/src/Jackalope/Transport/DoctrineDBAL/Client.php:1171
/path/to/jackalope-doctrine-dbal/src/Jackalope/Transport/DoctrineDBAL/Client.php:1171
/path/to/jackalope-doctrine-dbal/vendor/jackalope/jackalope/src/Jackalope/ObjectManager.php:764
/path/to/jackalope-doctrine-dbal/vendor/jackalope/jackalope/src/Jackalope/Session.php:379
/path/to/jackalope-doctrine-dbal/vendor/phpcr/phpcr-api-tests/inc/BaseCase.php:141
/path/to/jackalope-doctrine-dbal/vendor/phpcr/phpcr-api-tests/tests/11_Import/ImportRepositoryContentTest.php:265
/path/to/jackalope-doctrine-dbal/vendor/phpcr/phpcr-api-tests/tests/11_Import/ImportRepositoryContentTest.php:236
/usr/local/php5-20120203-085139/bin/phpunit:46

2) PHPCR\Tests\Import\ImportRepositoryContentTest::testImportXMLUuidReplaceExistingWorkspace
PHPCR\ReferentialIntegrityException: Trying to store reference to non-existant node with path '842e61c0-09ab-42a9-87c0-308ccc90e6f4' in node /tests_general_base/numberPropertyNode/jcr:content property ref

/path/to/jackalope-doctrine-dbal/src/Jackalope/Transport/DoctrineDBAL/Client.php:556
/path/to/jackalope-doctrine-dbal/src/Jackalope/Transport/DoctrineDBAL/Client.php:489
/path/to/jackalope-doctrine-dbal/src/Jackalope/Transport/DoctrineDBAL/Client.php:1166
/path/to/jackalope-doctrine-dbal/src/Jackalope/Transport/DoctrineDBAL/Client.php:1171
/path/to/jackalope-doctrine-dbal/src/Jackalope/Transport/DoctrineDBAL/Client.php:1171
/path/to/jackalope-doctrine-dbal/vendor/jackalope/jackalope/src/Jackalope/ObjectManager.php:764
/path/to/jackalope-doctrine-dbal/vendor/jackalope/jackalope/src/Jackalope/Session.php:379
/path/to/jackalope-doctrine-dbal/vendor/phpcr/phpcr-api-tests/inc/BaseCase.php:141
/path/to/jackalope-doctrine-dbal/vendor/phpcr/phpcr-api-tests/tests/11_Import/ImportRepositoryContentTest.php:265
/path/to/jackalope-doctrine-dbal/vendor/phpcr/phpcr-api-tests/tests/11_Import/ImportRepositoryContentTest.php:242
/usr/local/php5-20120203-085139/bin/phpunit:46

3) PHPCR\Tests\Import\ImportRepositoryContentTest::testImportXMLUuidReplaceRoot
PHPCR\ReferentialIntegrityException: Cannot delete /tests_general_base: A reference points to this node or a subnode

/path/to/jackalope-doctrine-dbal/src/Jackalope/Transport/DoctrineDBAL/Client.php:919
/path/to/jackalope-doctrine-dbal/vendor/jackalope/jackalope/src/Jackalope/ObjectManager.php:729
/path/to/jackalope-doctrine-dbal/vendor/jackalope/jackalope/src/Jackalope/Session.php:379
/path/to/jackalope-doctrine-dbal/vendor/phpcr/phpcr-api-tests/inc/BaseCase.php:141
/path/to/jackalope-doctrine-dbal/vendor/phpcr/phpcr-api-tests/tests/11_Import/ImportRepositoryContentTest.php:305
/usr/local/php5-20120203-085139/bin/phpunit:46

Call to undefined method Jackalope\Query\QOM\Join::getNodeTypeName()

The following query apparently works (or rather should work) under other implementations.

./phpcr phpcr:query '
    SELECT dst.*
      FROM [nt:unstructured] AS src
      JOIN [nt:unstructured] AS dst ON src.[reference] = dst.[jcr:uuid]
     WHERE ISCHILDNODE(src, "/artwork")
       AND dst.identity = "foo"' 

Instead I am greeted with:

PHP Fatal error: Call to undefined method Jackalope\Query\QOM\Join::getNodeTypeName() in vendor/jackalope/jackalope-doctrine-dbal/src/Jackalope/Transport/DoctrineDBAL/Client.php on line 1758

discussion link

look into concurrency issues with SQLite

https://github.com/jackalope/jackalope-doctrine-dbal/pull/46/files#r1183118
http://www.doctrine-project.org/jira/browse/DBAL-173

bug when removing interlinked nodes

as noted in phpcr/phpcr-utils#33 when we try to delete nodes with references, the deletion does not happen atomic, that is we get a referencial integrity exception even if the target of the reference is going to be deleted as well.

PHPCR\ReferentialIntegrityException: Cannot delete '/tests_general_base/idExample', '/tests_general_base/idExample', '/tests_general_base/multiValueProperty', '/a', '/b': A reference points to this node or a subnode

to reproduce checkout the phpcr-utils branch delete-hard-references and the phpcr-api-tests branch phpcr-utils-test

add optional support for using ElasticSearch/Solr for full text search

nt:unstructured node inside nt:folder node should not be allowed

According to the phpcr-api-tests and to the JCR specifications, adding a nt:unstructured node to a nt:folder node should throw a ConstraintViolationException.

phpcr-api-tests reference:
https://github.com/phpcr/phpcr-api-tests/blob/master/tests/10_Writing/AddMethodsTest.php#L162

JCR specifications about nt:folder:
http://www.day.com/specs/jcr/1.0/6.7.22.8_nt_folder.html

skipped test in jackalope-doctrine-dbal:
https://github.com/jackalope/jackalope-doctrine-dbal/blob/master/tests/inc/DoctrineDBALImplementationLoader.php#L58

look into caching

it should be easy to setup basic caching to prevent repeated fetches of workspace id's and supported property types etc.

issues with binary data

trying to upload a new image in the sandbox gave me an exception:
"message": "SQLSTATE[HY000]: General error: 1366 Incorrect string value: '\xE6\x00\xDF\x00\x80\x00...' for column 'data' at row 1",

caused by the following query:
"INSERT INTO phpcr_binarydata (node_id, property_name, workspace_id, idx, data) VALUES (?, ?, ?, ?, ?)"

now i am not sure why the current code uses a LONGTEXT for binary data instead of a LONGBLOB and wonder if we couldn't just pass a stream to the DBAL and it would handle things from there.

/cc @beberlei

Issue introduced with commit: e7f28fa8f89e85d3f04f01451bb45f2a43ce6a06 on 2013-06-28

commit e7f28fa
Merge: df8469b f198670
Author: David Buchmann [email protected]
Date: Fri Jun 28 05:36:07 2013 -0700

Merge pull request #126 from wjzijderveld/88-multivalue-comparison

Fixes #88 multivalue comparison

---

Queries for retrieval no longer return results, I have re-ran symfony re-initialization tasks, and data is in the DB, here's an example SELECT query before and after, reverting this change fixes the problem immediately:

SELECT n0.id AS id, n0.path AS path, n0.parent AS parent, n0.local_name AS local_name, n0.namespace AS namespace, n0.workspace_name AS workspace_name, n0.identifier AS identifier, n0.type AS type, n0.props AS props, n0.depth AS depth, n0.sort_order AS sort_order FROM phpcr_nodes n0 WHERE n0.workspace_name = ? AND n0.type IN ('nt:unstructured', 'rep:root') AND (EXTRACTVALUE(n0.props, '//sv:property[@sv:name="topicType"]/sv:value[1]') = 'Topic Front' AND (EXTRACTVALUE(n0.props, '//sv:property[@sv:name="phpcr:class"]/sv:value[1]') = 'BCG\CmsBundle\Document\Topic' OR EXTRACTVALUE(n0.props, '//sv:property[@sv:name="phpcr:classparents"]/sv:value[1]') = 'BCG\CmsBundle\Document\Topic'))

ELECT n0.id AS id, n0.path AS path, n0.parent AS parent, n0.local_name AS local_name, n0.namespace AS namespace, n0.workspace_name AS workspace_name, n0.identifier AS identifier, n0.type AS type, n0.props AS props, n0.depth AS depth, n0.sort_order AS sort_order FROM phpcr_nodes n0 WHERE n0.workspace_name = ? AND n0.type IN ('nt:unstructured', 'rep:root') AND (EXTRACTVALUE(n0.props, 'count(//sv:property[@sv:name="topicType"]/sv:value[text()="Topic Front"]) > 0') AND (EXTRACTVALUE(n0.props, 'count(//sv:property[@sv:name="phpcr:class"]/sv:value[text()="BCG\CmsBundle\Document\Topic"]) > 0') OR EXTRACTVALUE(n0.props, 'count(//sv:property[@sv:name="phpcr:classparents"]/sv:value[text()="BCG\CmsBundle\Document\Topic"]) > 0')))

use instanceof platform instead of getName

follow-up of #46

refactor the platform specific code bits to use instanceof rather than getName. look for getDatabasePlatform()->getName() in the code

Failure to connect Postgresql

trying app/console doctrine:phpcr:register-system-node-types, this error appears:

[PDOException]                                                                                 
SQLSTATE[42P01]: Undefined table: 7 ERROR:  relation "phpcr_type_nodes_id_seq" does not exist

this is because sequenceTypeName is set to phpcr_type_nodes_id_seq in Transport/DoctrineDBAL/Client.php
changing it to phpcr_type_nodes_node_type_id_seq should solve the issue.

License inconsistent

The LICENSE-file contains the Apache-license, but packagist/composer.json sais "MIT".

implement SQL2 CONTAINS

see phpcr/phpcr-api-tests#109

[sqlite] Too many SQL variables

When doing

./app/console phpcr:node:dump /cms/media

Where media contains more than ~1000 nodes, we get an error like "Too many SQL parameters"

• http://www.sqlite.org/limits.html

Apparently Sqlite has a configured limit of 999 parameters, and the query is doing something like "SELECT .. WHERE id IN (?, ?, ? ,? ,? ,? ,? ,?,[...]) I think.

refactor nodetype validation into base jackalope

code here should go to Jackalope\NodeType to be available in all implementations. caec879

Error inserting data into DB

when i run doctrine:phpcr:register-system-node-types, i get:

[PDOException]                                                                                
SQLSTATE[22P02]: Invalid text representation: 7 ERROR:  invalid input syntax for integer: ""  

the strange statement sent to database is (see the VALUES part)

INSERT INTO phpcr_type_props (node_type_id, name, protected, mandatory, auto_created, on_parent_version, multiple, fulltext_searchable, query_orderable, required_type, query_operators, default_value) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12)

here is the exception trace:

at ./vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php:754
PDOStatement->execute() at ./vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php:754
Doctrine\DBAL\Connection->executeUpdate() at ./vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php:525
Doctrine\DBAL\Connection->insert() at ./vendor/jackalope/jackalope-doctrine-dbal/src/Jackalope/Transport/DoctrineDBAL/Client.php:1507
Jackalope\Transport\DoctrineDBAL\Client->registerNodeTypes() at ./vendor/jackalope/jackalope/src/Jackalope/ObjectManager.php:597
Jackalope\ObjectManager->registerNodeTypes() at ./vendor/jackalope/jackalope/src/Jackalope/NodeType/NodeTypeManager.php:353
Jackalope\NodeType\NodeTypeManager->registerNodeTypes() at ./vendor/jackalope/jackalope/src/Jackalope/NodeType/NodeTypeManager.php:318
Jackalope\NodeType\NodeTypeManager->registerNodeType() at ./vendor/doctrine/phpcr-odm/lib/Doctrine/ODM/PHPCR/Tools/Console/Command/RegisterSystemNodeTypesCommand.php:77
Doctrine\ODM\PHPCR\Tools\Console\Command\RegisterSystemNodeTypesCommand->execute() at ./vendor/doctrine/phpcr-bundle/Doctrine/Bundle/PHPCRBundle/Command/RegisterSystemNodeTypesCommand.php:50
Doctrine\Bundle\PHPCRBundle\Command\RegisterSystemNodeTypesCommand->execute() at ./vendor/symfony/symfony/src/Symfony/Component/Console/Command/Command.php:239
Symfony\Component\Console\Command\Command->run() at ./vendor/symfony/symfony/src/Symfony/Component/Console/Application.php:193
Symfony\Component\Console\Application->doRun() at ./vendor/symfony/symfony/src/Symfony/Bundle/FrameworkBundle/Console/Application.php:77
Symfony\Bundle\FrameworkBundle\Console\Application->doRun() at ./vendor/symfony/symfony/src/Symfony/Component/Console/Application.php:106
Symfony\Component\Console\Application->run() at ./app/console:22

testAddPropertyWrongType throws a RepositoryException

AddMethodsTest::testAddPropertyWrongType should throw either a ValueFormatException or ConstraintViolationException. But it seems to fail before, with a RepositoryException:

PHPCR\RepositoryException : Property jcr:data is mandatory, but is not present while saving nt:resource at /tests_write_manipulation_add/testAddPropertyWrongType/file/jcr:content

reference to the phpcr-api-tests:
https://github.com/phpcr/phpcr-api-tests/blob/master/tests/10_Writing/AddMethodsTest.php#L235

improve sqlite init

when using composer create-project to create a new project with the sqlite database, we should also create the database. we could try it for all platforms actually. there is code in the DoctrineBundle for that which we might copy: https://github.com/doctrine/DoctrineBundle/blob/master/Command/CreateDatabaseDoctrineCommand.php

we should however check if the database already exists before attempting to create it.

btw, i had problems running jackalope:init:dbal with sqlite. nothing happens when running the command. then i added a $connection->close() line after $connection->exec() but then i get an exception about "no such table: main.phpcr_nodes"

testTransactionCommit fails

TransactionMethodsTest::testTransactionCommit in phpcr-api-tests is failing.

Failed asserting that true is false on the following line:
https://github.com/phpcr/phpcr-api-tests/blob/master/tests/21_Transactions/TransactionMethodsTest.php#L48

It seems that an uncommitted transaction is saved anyway by $session->save();

invalid search query generated

if i search for an uploaded image, i am getting an invalid sql query:

SELECT n0.id AS id, n0.path AS path, n0.parent AS parent, n0.local_name AS local_name, n0.namespace AS namespace, n0.workspace_name AS workspace_name, n0.identifier AS identifier, n0.type AS type, n0.props AS props, n0.depth AS depth, n0.sort_order AS sort_order, 

#this one seems invalid
n1.id AS nt:unstructured_id, n1.path AS nt:unstructured_path, n1.parent AS nt:unstructured_parent, 

n1.local_name AS nt:unstructured_local_name, n1.namespace AS nt:unstructured_namespace, n1.workspace_name AS nt:unstructured_workspace_name, n1.identifier AS nt:unstructured_identifier, n1.type AS nt:unstructured_type, n1.props AS nt:unstructured_props, n1.depth AS nt:unstructured_depth, n1.sort_order AS nt:unstructured_sort_order 
FROM phpcr_nodes n0 
WHERE n0.workspace_name = ? AND n0.type IN ('nt:unstructured', 'rep:root') AND (n0.path LIKE '/cms/content/static/%' AND (EXTRACTVALUE(n1.props, '//sv:property[@sv:name=\"phpcr:class\"]/sv:value[1]') = 'Symfony\\\\Cmf\\\\Bundle\\\\CreateBundle\\\\Document\\\\Image' AND EXTRACTVALUE(n1.props, '//sv:property[@sv:name=\"caption\"]/sv:value[1]') LIKE 'male%'))

@see symfony-cmf/create-bundle#58

SQL injections?

https://github.com/jackalope/jackalope-doctrine-dbal/blob/master/src/Jackalope/Transport/DoctrineDBAL/Query/QOMWalker.php#L226

What happens when a node name is entered by a not-so-trustworthy user?

Workspace::copy creates wrong UID

I think I found a bug regarding the copy operation:

If I copy a node within the same workspace (default) then the node and all subgraphfs get copied successfully. A new UID is assigned to the newly created nodes in the "identifier" column of "phpcr_nodes". The Problem is, that the property within the "props" column is the identifier of the origin node, causing later calls to Node::getIdentifier() to return the wrong id (of the copied node).

Create Workspace error handling on postgres

#79 wants to expose the dbal error unless it really is the duplicate workspace problem. it works fine with mysql and sqlite, but not with postgres.

@lsmith77 says: ts indeed not possible to rely on sqlstate error codes .. the mapping done inside pdo is not reliable. so we may need to do a platform check in there.

@Burgov: would you be ok to check this out? i guess postgres uses a different status code in that situation... so the trick would be to check whether we are on postgres or mysql (there are examples in the Client class how to determine the platform)

issues with XML entities

Warning: DOMDocument::loadXML(): Entity 'nbsp' not defined in Entity, line: 3 in /vagrant/symfony/vendor/jackalope/jackalope-doctrine-dbal/src/Jackalope/Transport/DoctrineDBAL/Client.php line 836

colon (:) in literal causes odd behavior.

If I had this code:

      $res = $dm->getRepository('TestBundle:Test')
        ->findBy(
          array(
            'title'    => $title,
          )
        )
        ->first();

If $title contained a colon, this would throw an exception, this is due to the code around line 332 of:

https://github.com/jackalope/jackalope-doctrine-dbal/blob/master/src/Jackalope/Transport/DoctrineDBAL/Query/QOMWalker.php

It assumes because I have a colon in the compared variable that I meant to do something with namespaces. I would think it should just check for the namespace route, and continue gracefully if that namespace does not exist, otherwise I have to prepend all of these variables with a blank namespace ':' just so it will operate without throwing an exception.

throw error when trying to overwrite a system node type

see the TODO around https://github.com/jackalope/jackalope-doctrine-dbal/blob/master/src/Jackalope/Transport/DoctrineDBAL/Client.php#L1613

see also discussion at the end of jackalope/jackalope#160

fix test suite reset with SQLite for DBAL master

the schema RepositorySchema::reset() causes:

PHP Fatal error:  Uncaught exception 'Doctrine\DBAL\DBALException' with message 'Sqlite platform does not support alter foreign key.' in /Users/lsmith/htdocs/cmf-sandbox/vendor/jackalope/jackalope-doctrine-dbal/vendor/doctrine/dbal/lib/Doctrine/DBAL/Platforms/SqlitePlatform.php:646

testAddNodeInParallel isn't succeeding

AddMethodsTest::testAddNodeInParallel of phpcr-api-tests should throw an ItemExistsException.

reference to the test:
https://github.com/phpcr/phpcr-api-tests/blob/master/tests/10_Writing/AddMethodsTest.php#L193

skipped test:
https://github.com/jackalope/jackalope-doctrine-dbal/blob/master/tests/inc/DoctrineDBALImplementationLoader.php#L59

add support for fetch depth

with Jackrabbit we can set a fetch depth to automatically fetch children up to a certain depth at once

testRemoveNodeConstraintViolation doesn't throw a ConstraintViolationException

DeleteMethodsTest::testRemoveNodeConstraintViolation should throw a ConstraintViolationException.

reference to the phpcr-api-tests:
https://github.com/phpcr/phpcr-api-tests/blob/master/tests/10_Writing/DeleteMethodsTest.php#L62

created_at updated_at to help with cache invalidation

Do you think there is any scope for adding created_at and updated_at to the schema to help with cache invalidation?

Im not sure whether this is something to go in here, jackalope, or even cmf, but one way or another it would be immensely helpful to be able to find out when a specific subset of nodes was last updated.

Any thoughts? I was going to fall back on SHOW TABLE STATUS LIKE 'tablename' to get the last update time as a crude solution, but for InnoDB unfortunately the update time is always Null. There is a ticket open for it to be added but it has been open since 2005...

cleanup date property storage issues

this is a follow-up to #90

according to this post http://forum.magnolia-cms.com/forum/thread.html?threadId=57b4de22-c096-4ffb-8a70-00528a1b5c35 jackrabbit internally uses UTC timezone. this seems to be incorrect, at least for the jackrabbit remoting. i wrote some test code with the los angeles timezone (so neither UTC nor my system timezone), and storing that in jackrabbit and loading it from a fresh session still remembers that timezone. the only mention of time zone i can find in the whole JCR 2.0 spec is http://www.day.com/specs/jcr/2.0/3_Repository_Model.html#3.6.4.3%20From%20DATE%20To - but as Calendar is timezone aware, i guess timezone preservation is implicitly expected.

now https://github.com/phpcr/phpcr/blob/master/src/PHPCR/PropertyType.php#L475 formats the date in whatever timezone it happens to be in. i think this is the correct thing to do there.

it looks to me like the date used for ordering would need to be UTC, but we have to store the timezone separately somewhere to restore it. can we even do that?

whats more, i stumbled over this http://docs.jboss.org/exojcr/1.12.13-GA/developer/en-US/html/ch-jcr-query-usecases.html#JCR.DatePropertyComparison reminding us that for search we need to handle timezones as well. which would be another strong case for using UTC internally and converting whatever date we get in the query. however, the JCR 2.0 spec does not even mention the TIMESTAMP keyword. however http://stackoverflow.com/questions/8822495/jcr-sql2-query-with-dynamic-date-comparison suggests that there is just a different syntax now, using "CAST".

so all in all, i fear we still have quite a mess with dates in jackalope-doctrine-dbal.

it will need some more digging, and possibly consulting with the jcr people to be sure we fully grasp the issues, and then make sure we are spec compliant.

/cc @lsmith77 @wjzijderveld

testNodeRemovePropertyConstraintViolation doesn't throw a ConstraintViolationException

DeleteMethodsTest::testNodeRemovePropertyConstraintViolation should throw a ConstraintViolationException.

reference to the phpcr-api-tests:
https://github.com/phpcr/phpcr-api-tests/blob/master/tests/10_Writing/DeleteMethodsTest.php#L225

This issue may be related to issue 20

index on path, workspace_name is not used in Client::getNode

Jackalope\Transport\DoctrineDBAL\Client::getNode is doing a LIKE query on phpcr_nodes.path, but EXPLAIN shows it is not using the index on (path, workspace_name).

I traced this to the forward slashes in :pathd, adding (escaping) '\' before these forward slashes makes MySQL use its index. significantly improving performance on path queries.

see:
https://github.com/ACSI-IT/jackalope-doctrine-dbal/commit/95f73f01ab0cdc94121a94c4b9425621a5479ade

Not sure if this is the cleanest solution, could you provide some feedback.
Let me know if you want me to create a PR for this commit.

Axel

selector name issues

@wjzijderveld: after merging #115 when running a search in the cmf-sandbox it generates an SQL like this:

SELECT n0.id AS id, n0.path AS path, n0.parent AS parent, n0.local_name AS local_name, n0.namespace AS namespace, n0.workspace_name AS workspace_name, n0.identifier AS identifier, n0.type AS type, n0.props AS props, n0.depth AS depth, n0.sort_order AS sort_order, n1.id AS [nt:unstructured]_id, n1.path AS [nt:unstructured]_path, n1.parent AS [nt:unstructured]_parent, n1.local_name AS [nt:unstructured]_local_name, n1.namespace AS [nt:unstructured]_namespace, n1.workspace_name AS [nt:unstructured]_workspace_name, n1.identifier AS [nt:unstructured]_identifier, n1.type AS [nt:unstructured]_type, n1.props AS [nt:unstructured]_props, n1.depth AS [nt:unstructured]_depth, n1.sort_order AS [nt:unstructured]_sort_order FROM phpcr_nodes n0 WHERE n0.workspace_name = ? AND n0.type IN ('nt:unstructured', 'rep:root') AND ((n0.path LIKE '/cms/content/%' AND (EXTRACTVALUE(n0.props, '//sv:property[@sv:name="title"]/sv:value[1]') LIKE '%cmf%' OR EXTRACTVALUE(n0.props, '//sv:property[@sv:name="body"]/sv:value[1]') LIKE '%cmf%')) AND n1.namespace || (CASE n1.namespace WHEN '' THEN '' ELSE ':' END) || n1.local_name = 'http://www.doctrine-project.org/projects/phpcr_odm/phpcr_locale:en') LIMIT 10

the query is generated from this code:
https://github.com/symfony-cmf/SearchBundle/blob/master/Controller/PhpcrSearchController.php#L151

Handling of ampersand in query and/or storage

There appears to be some discrepancy between how ampersands are stored using the jackalope-doctrine-dbal. The buggy behavior could be examined in the following example:

$dm     = $this->getDocumentManager();

// Save the test
$test = new Test(); // This is a Document
$test->setName("This & That");
$dm->persist($test);
$dm->flush();


$repo = $dm->getRepository('ABCTestBundle:Test');


// Result is empty here
$result = $repo->findBy(array('name' => 'This & That'));

This is due to the way it is stored in phpcr_nodes, examining this particular node in the database, you see it is stored as "This & That". As a HTML entity that is not needed in this context. It should be able to be queried as stored, whether that be not translating it into & or translating it in the query, I don't know.

[Edit: Fixed htmlentity that was displaying as the html entity in this issue report)