UPDATE query on multivalue field is scalar.

[dantleech]

When performing an UPDATE query:

UPDATE [nt:unstructured] SET phpcr:classparents = 'Foo' where phpcr:classparents = 'Bar'

The multivalue field phpcr:classparents will be replaced with a scalar "Bar", regardless of any existing keys and there is no way of specifying multiple values or indexes.

We need to decide upon a syntax for specifying both multiple values and indexes:

Setting a multivalue:

UPDATE [nt:unstructured] SET multivalue = ['Foo', 'Bar', Baz'];

Update a property value by index or value:

# update index 0 of multivalue
UPDATE [nt:unstructured] SET multivalue[0] = 'Foo';

# update the only the member with the value "bar"
UPDATE [nt:unstructured] SET multivalue = 'foo' where multivalue='bar';

Delete a property value by index or value

UPDATE [nt:unstructured] SET multivalue[0] = NULL;
UPDATE [nt:unstructured] SET multivalue = NULL WHERE multivalue="bar";

Note: NULL ewill currently resolve to a string.. this is a bug

Add multivalue property

UPDATE [nt:unstructured] SET multivalue[] = 'bar';

[dantleech]

@dbu @lsmith77 what do you think? would the above SQL strings be valid and conflict free?

[lsmith77]

the syntax seems sane ..
I see the value of all of this .. but do have a weird feeling for us to just expand the SQL2 syntax like this .. but oh well :)

[dbu]

as long as its only in the shell for now, i think its ok to play with this. i don't think a property value could legally have a [ in it, so the syntax without space should be non-ambigous.

i see several possible operations:

• add a property value (possibly in the middle?)
• change a property value by index
• change a property value by value (your above example would be that - if i have mixed documents the first parent is not always the same)
• delete a property value by index
• delete a property value by value

[dantleech]

I try and make it clear that this is a non-standard phpcr-shell specific grammer whenever I can, but it makes a lot of sense in the shell I think.

I have updated the description with proposal based on @dbu 's comments.

[dantleech]

Postgres supports arrays: http://www.postgresql.org/docs/9.1/static/arrays.html

f.e.

UPDATE sal_emp SET pay_by_quarter[4] = 15000
UPDATE sal_emp SET pay_by_quarter = ARRAY[25000,25000,27000,27000]
UPDATE sal_emp SET pay_by_quarter = '{25000,25000,27000,27000}'
UPDATE sal_emp SET pay_by_quarter[1:2] = '{27000,27000}' (array slice)

I think you would replace an array element by value with a function:

UPDATE foo SET array = array_replace(array, 'Foo', 'Bar')

[dbu]

oh, good idea to look at postgres. this doc seems to list everything possible in postgres 9.3 with arrays: http://www.postgresql.org/docs/9.3/static/functions-array.html

seems like array_replace is the right thing. +1 for adopting the postgres syntax. for phpcr, this all happens in php userland code, right? that would mean supporting all those things is just mapping them to the php functions, which sound very similar.

[dantleech]

Fixed in #90