Querying

Selecting

PHPCRSH currently supports the JCR-SQL2 query language when supported by the implementation (currently all implementations support this language).

PHPCRSH > SELECT title FROM slinpTest:article
+--------------------------------------------+-----------------------------+
| Path                                       | slinpTest:article.title     |
+--------------------------------------------+-----------------------------+
| /slinp/web/root                            | Slinp Web Content Framework |
| /slinp/web/root/home                       | Home                        |
| /slinp/web/root/articles/Faster-than-light | Faster than light           |
+--------------------------------------------+-----------------------------+
3 rows in set (0.01 sec)

PHPCRSH > SELECT title FROM slinpTest:article WHERE title="Home"
+----------------------+-------------------------+
| Path                 | slinpTest:article.title |
+----------------------+-------------------------+
| /slinp/web/root/home | Home                    |
+----------------------+-------------------------+
1 rows in set (0.04 sec)

The JCR-SQL2 lanugage supports joins, column selection and a range of operands (e.g. lowercase, uppercase, length, etc).

For more information on JCR-SQL2 refer to the articles on the official PHPCR website.

Update and Delete

In addition to standard support for SELECT queries, PHPCRSH additionally supports UPDATE and DELETE queries, these query grammers are not standard and are specific to PHPCRSH.

Note

UPDATE and DELETE operations are experimental. You are advised to test any updates before hand in a development environment and ensure that you have a backup. We can take no responsibility for lost data!

Note

UPDATE and DELETE operations are performed in userland and are based upon underlying SELECT operations, so any overhead incurred in an equivalent SELECT will be the baseline for UPDATE or DELETE overheads.

Updating

The UPDATE Grammer extends the SELECT grammer:

PHPCRSH > UPDATE [slinpTest:article] SET title="Away" WHERE title="Home"
1 row(s) affected in 0.01s

PHPCRSH > UPDATE [slinpTest:article] AS a LEFT JOIN [slinpTest:foobar] AS b ON a.uuid = b.content SET a.title="Away", b.title="Home"  WHERE a.title="Home"
1 row(s) affected in 0.01s

Functions

The UPDATE grammer also allows the use of functions (note that only UPDATE supports functions).

Functions correspond to either SET or APPLY syntax. The APPLY functions take action on the node as a whole. All functions listed below are for use with SET unless stated otherwise.

array_remove

Remove the multivalue property value matching the given value.

Usage:

PHPCRSH> UPDATE [nt:unstructured] AS a SET a.tags = array_remove(a.tags, 'Planes') WHERE a.tags = 'Planes'

Arguments:

  • propertyName: Property name (including selector) of the multivalue property
  • value: Value to match and remove

array

Provides an array value, analagous to the array keyword in PHP:

PHPCRSH> UPDATE [nt:unstructured] SET tags = array('One', 'Two', 'Three')

Arguments:

  • List of values

array_replace

Replace a given multivalue property value, or remove it by setting it to NULL.

Replace a value:

PHPCRSH> UPDATE [nt:unstructured] SET tags = array_replace(tags, 'Planes', 'Rockets')

Remove matching values:

PHPCRSH> UPDATE [nt:unstructured] SET tags = array_replace(tags, 'Planes', NULL)

Arguments:

  • propertyName: Property name (including selector) of the multivalue property
  • value: Value to replace, use NULL to remove a value
  • replacement: Replacement value

array_replace_at

Replace a given multivalue property value at the specified index.

Usage:

PHPCRSH> UPDATE [nt:unstructured] SET tags = array_replace_at(tags, 0, 'Rockets') WHERE tags = 'Planes'

Arguments:

  • propertyName: Property name (including selector) of the multivalue property
  • index: Index at which the new value should be set
  • value: Value to set

array_append

Append a value to a multivalue property.

Usage:

PHPCRSH> UPDATE [nt:unstructured] SET tags - array_append(tags, 'Planes') WHERE tags = 'Planes'

Arguments:

  • propertyName: Property name (including selector) of the multivalue property
  • value: Value to append

expr

Evaluate an expression. This function is very powerful in that it enables you to use the Symfony Expression Language to evaluate an expression, the result of which can be assigned to a property.

Within the expression you have access to the row object (RowInterface).

Set the value of a.title to the node name:

PHPCRSH> UPDATE [nt:unstructured] AS a SET a.title = expr('row.getNode().getName()')

Set the value of a.title to the value of the property :

PHPCRSH> UPDATE [nt:unstructured] AS a SET a.title = expr('row.getNode().getPropertyValueWithDefault("some_property", null)')

Increment the value of a property:

PHPCRSH> UPDATE [nt:unstructured] AS a SET a.count = expr('row.getNode().getPropertyValueWithDefault("count", 0) + 1')

Set the value of a.title from the value of a joined node:

PHPCRSH> UPDATE [nt:unstructured] AS a INNER JOIN [nt:something] AS b ON a.foo = b.bar SET a.title = expr('row.getNode("b").getPropertyValue("something")')

mixin_add

This function allows you to add a mixins to nodes. This is an APPLY function.

Usage:

PHPCRSH> UPDATE [nt:unstructured] APPLY mixin_add('mix:versionable')

mixin_remove

This function allows you to remove mixins from nodes. This is an APPLY function.

Usage:

PHPCRSH> UPDATE [nt:unstructured] APPLY mixin_remove('mix:versionable')

Deleting

Delete is as you might expect, and is essentially gramatically identical to SELECT but without the column selection:

PHPCRSH > DELETE FROM [slinpTest:article] WHERE title="Home"
1 row(s) affected in 0.01s