Semantics of UPDATE statement

[p...@pi.net]

Currently, if a property is set on a primitive value, or a named
property is set on an array, the original value is discarded and
replaced by an empty opbject. I think this is wrong, and the update
should be silently ignored. Test cases for the proposed UPDATE
semantics:


-- Test UPDATE statement
--

.new t1.db
CREATE COLLECTION c1;
INSERT INTO c1 VALUE null;
INSERT INTO c1 VALUE true;
INSERT INTO c1 VALUE 1;
INSERT INTO c1 VALUE "abc";
INSERT INTO c1 VALUE [1,2,3,4];
INSERT INTO c1 VALUE {a: 1, b:2, c:3};

.testcase 0
SELECT FROM c1;
.json null\
true\
1\
"abc"\
[1,2,3,4]\
{"a":1,"b":2,"c":3}

-- setting named properties on primitives or
-- arrays is ignored
.testcase 1
UPDATE c1 SET c1.a = 2;
SELECT FROM c1;
.json null\
true\
1\
"abc"\
[1,2,3,4]\
{"a":2,"b":2,"c":3}

-- setting named properties on primitives or
-- arrays is ignored, also when x[y] syntax is used
.testcase 2
UPDATE c1 SET c1["a"] = 3;
SELECT FROM c1;
.json null\
true\
1\
"abc"\
[1,2,3,4]\
{"a":3,"b":2,"c":3}

-- setting number properties on primitives is
-- ignored, but honoured on arrays and objects
.testcase 3
UPDATE c1 SET c1["2"] = 7;
SELECT FROM c1;
.json null\
true\
1\
"abc"\
[1,2,7,4]\
{"a":3,"b":2,"c":3, "2":7}

-- setting number properties on arrays potentially
-- grows the array, padding skipped elements with null
.testcase 4
UPDATE c1 SET c1["7"] = 9;
SELECT FROM c1;
.json null\
true\
1\
"abc"\
[1,2,7,4,null,null,null,9]\
{"a":3,"b":2,"c":3, "2":7, "7":9}

[Bernardo Ramos]

My thoughts about the UPDATE command:

We don't need to repeat the collection name in the query:


The command below should affect only the arrays (lists):

UPDATE c1 SET [2] = 7

it should give us this result:

.json null\
true\
1\
"abc"\
[1,2,7,4]\
{"a":3,"b":2,"c":3}

And the commands below should affect only the objects, considering
what is at the left of the equal sign as the key:

UPDATE c1 SET "2" = 7
or
UPDATE c1 SET 2 = 7

Should yield:

.json null\
true\
1\
"abc"\
[1,2,3,4]\

{"a":3,"b":2,"c":3,"2":7}



UPDATE c1 SET name = "John"
or
UPDATE c1 SET "unicode_name" = "John"




And we may be able to add items to an array. Example: adding an
'address' item to an 'addresses' list:

INSERT INTO customers VALUE {id:1, name:'John', addresses:[]}

UPDATE customers SET addresses[]={address:'123, Noth Av.', city:'San
Francisco', State:'CA' , postalCode: 12345} WHERE id=1

This could be done using the property name and the empty brackets
after them []

or something else...


And to reset all the items in an array:

UPDATE customers SET addresses=[ {address:'123, Noth Av.', city:'San
Francisco', State:'CA' , postalCode: 12345} ,
{address:'another', city:'other'} ] WHERE id=1



And to remove a item from an array:

DELETE addresses[1] FROM customers WHERE id=1

will delete the second item (if using base 0) or the first item (if
using base 1) from the array/list.


To remove a property:

DELETE age FROM customers

[Bernardo Ramos]

DOCUMENT REFACTORING

Suppose that we have some documents stored in a collection, it could
be a customers collection, and that we stored only one address for
each document item in the first version of the documents/records.

But... in a new version of these records we want to create an array
containing the addresses related to that person, so we can store more
than one address to each customer.

So we could use something like this:

UPDATE customers SET addresses[] = {"type": "home", "address":
customers.address, "city": customers.city}

DELETE address FROM customers
DELETE city FROM customers

or:

DELETE address, city FROM customers


The UPDATE command should create the 'addresses' property, set it as
an array, and add the created object to it.

Then the DELETE command can be used to remove the old properties from
each document/record.



----------------------------------------------------------------


I don't know if the 'customers.' is really needed in the query. The
statement below shouldn't work?

"address": address

Another question: how the engine may to interpret the expression below
inside of a UPDATE statement?

address: address

Should it treat what is at the left of the colon as a string or it may
to pass the field/property value as the key to the created property?


----------------------------------------------------------------

DOCUMENT VERSIONING

In situations like this, it is common to use a 'version' property in
each document to store the version number for the structure in use
[1]. Because sometimes we have a lot of documents in a collection and
they can have different structure format.

Then, to update the structure, we can use:


BEGIN

UPDATE customers SET addresses[] = {"type": "home", "address":
customers.address, "city": customers.city}, version=2 WHERE version=1

DELETE address, city FROM customers WHERE version=2

COMMIT


I think that it makes more sense to use these commands when we cannot
put them inside of a transaction...


[1] http://www.infoq.com/presentations/Nokia-Lessons-Learnt-Migrating-into-a-Classic-NoSQL