UPSERT PUT to a LINKMAP property

[Eric24]

I'm trying to determine the most efficient and least contentious way to do a sort of UPSERT on a LINKMAP property. Specifically, if I try to PUT a new item with a key value that already exists, I want the PUT to fail and leave the existing item in place (and instead return that item to the caller). By default, a PUT overwrites the key-matching item. I can prevent that with a WHERE clause, but I can't get RETURN to work (no syntax I've tried works). I can accomplish this doing a SELECT first, followed by a conditional UPDATE, but I'm hoping there's a more efficient way.

Also, this is a very high-frequency operation (many potential update or update attempts to a given record per second). What happens to a single record with a LINKMAP (or EMBEDDEDMAP) property if one operation adds a new key/record and a concurrent operation adds a new different key/record? Are both keys/records retained or does the most recent update "win", effectively loosing the key/record added in the first operation or are both retained? If so, would locking the record during the operation solve that issue?

[user.w...@gmail.com]

Hi Eric,

Can you post your query?

Which version are you using?

Regards,

Michela

[Eric24]

I'm running 2.2 GA. My attempt at doing this was various versions of this (but I haven't found any combination that works):

UPDATE #14:1 PUT Options = '22', {"Action":"Test22a","Enabled":false, "@type":"d", "@class":"VOptions"} WHERE NOT (Options CONTAINSKEY '22') RETURN AFTER Options['22']

It works up to the RETURN (i.e. if I omit this last part, the first part does indeed allow me to "blindly" do the PUT and only actually modify the record if the key doesn't exist). The idea was to get the new embedded record (or link) back after the update or return the value with the matching key that was already there, and do this as an atomic operation.

[Eric24]

Note that I also tried this (which also fails in the same way):

UPDATE #14:1 PUT Options = '22', {"Action":"Test22a","Enabled":false, "@type":"d", "@class":"VOptions"} WHERE NOT (Options CONTAINSKEY '22') RETURN AFTER @this.Options['22']

But this works:

UPDATE #14:1 SET OtherProperty = 'OtherValue' RETURN AFTER @this.Options['22']

Is it possible that the RETURN clause can't be used with a PUT operation?

[alessand...@gmail.com]

Hi Eric,

according to the documentation http://orientdb.com/docs/2.2/SQL-Update.html you shoud use where condition after return.

If the condition is not satisfied the update will not be executed so it will not return anything.

Kind regards,

Alessandro

[Eric Lenington]

Ah so. Good catch. Yes, the position of the RETURN clause does appear to matter. So making this change does eliminate the exception, but unfortunately, it doesn't produce the desired result. In this case, no record is returned except when the UPDATE succeeds. What I need is essentially the opposite: If the update doesn't succeed (because the target key already exists), I want to get the record back (if I also get back the newly inserted item when the update does succeed, that's fine, but not really necessary). With MSSQL, I can do this easily in a single atomic operation, but so far, I've not been able to come up with a solution for OrientDB. I suppose a function or batch that executes the update followed by a select would work, but I was hoping to find something more efficient.

--

---
You received this message because you are subscribed to a topic in the Google Groups "OrientDB" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/orient-database/x3idVPiMylw/unsubscribe.
To unsubscribe from this group and all its topics, send an email to orient-databa...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Ivan Mainetti]

Hello,

I think that the only way to do what you need is by using a function:

I wrote this javascript:

parameters: rid, map_name, key_name, key_value

var g=orient.getGraph();

var sel=g.command("sql","select expand("+map_name+"."+key_name+") from "+rid+" where '"+key_name+"' in "+map_name+".keys()");

if(sel.length > 0){
  return sel[0]
} else {
  var upd=g.command("sql","update "+rid+" put "+map_name+"='"+key_name+"',"+key_value+" return after @this."+map_name+"['"+key_name+"']");
  return upd[0];
}

then here's an example of usage:

select expand(function_name('#23:0','lista','key7',"{'Action':'Test22a','Enabled':false, '@type':'d', '@class':'Test'}"))

[Eric24]

Thank you for the idea. That's the direction I was going. It does work, of course, I was just hoping for a more elegant solution.

PS (to OrientDB): It would be so nice if the Javascript engine used for functions supported ES6 template strings (https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Template_literals).