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.

Answer 1

This would depend on the implementation of the underlying data-store, because the auto-increment attribute is on the column definition in relational databases.

Probably, "yes".

Answer 2

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.