Auto increment with XQuery Update?

Question

Does XQuery Update support auto increment attributes, just like auto increment fields in SQL?

I'm using BaseX as my database.

No, I had to do this in the application instead of in the database. — Kim Stebel
...and then use some other database to generate the unique id's for you? As an XQuery updating statement cannot be combined with returning some value, I even fail to implement some "database sequence" in an XML database... — Arjan
See Christian's comment to my answer. (I might rewrite my answer at a later time, if he does not post something himself here.) — Arjan
My solution was a lot simpler, since the application I was writing was the only one with direct access to BaseX, so I could keep the current ID in my application. — Kim Stebel

Arjan Arjan · Accepted Answer · 2012-09-30T14:28:43

Given an answer from Christian Grün on the BaseX mailing list, this is doable when the node one is adding is defined in the XQuery Update statement, and hence can be enhanced using an {enclosed expression} before inserting it:

You might specify the attribute counter within your XML file/database and increment it every time when you insert an element. A simple example:

input.xml:
<root count="0"/>
insert.xq:
let $root := doc('input.xml')/root
let $count := $root/@count
return (
  insert node <node id='{ $count }'/> into $root,
  replace value of node $count with $count + 1  
)

I've not been able to achieve the same with an external org.w3c.dom.Document created in Java, and added to the XML database using XQJ and declare variable $doc external. Here, one might be tempted to update the "auto-increment" data after adding the document. However, the processing model defines that changes are not visible until all the commands have been queued (the Pending Update List). Hence a new document or node is, by definition, simply not visible for updates in the same FLWOR expression. So:

db:add('db1', '<counters comment-id="0"/>', 'counters')

...followed by repetitive executions of the following, will NOT work:

let $doc := document{ <note id=""><text>Hello world</text></note> }
let $count := /counters/@comment-id 
return (
  db:add('db1', $doc, 'dummy'), 
  replace value of node $count with $count + 1
  for $note in /note[@id=''] 
    return replace value of node $note/@id with $count (: WRONG! :)
)

Above, the last inserted document will always have <note id="">, and will not be updated until the next document is added. (Also, it would not work when somehow multiple documents with <note id=""> would exist.)

Though in the example above one could successfully delete the for $note in ... part and use:

let $doc := document{ <note id="{ $count }"><text>Hello world</text></note> }

...I had no luck setting <note id="{ $count }"> in the Document in the Java code, as that enclosed expression would not be replaced then.

Finally, some state for similar solutions:

[...] perform badly as it will lock out concurrent updates. You should consider using xdmp:random() to generate a 64 bit random number for your unique identifier.

In our case, the id would also be used in URLs; not too nice then.

See also XRX/Autoincrement File ID and Using XQuery to return Document IDs.

Auto increment with XQuery Update?

2 Answers