Hi Dave,

Like you, because of my RDBMS background, I had to feel the ‘document’ paradigm in order to obtain good performance results.

I suggest you do not try to update your catalog document in place using XQuery Update facility.

A simple solution is to add each daily-update, and annotate it with a my-sort-value value you can sort on (the date in your use case). When looking for a given SKU, use an index to obtain the list of entry elements, ordered by descending my-sort-value, and take the first item. When you hit performance or storage limits, create a collection containing only the latest version of each entry element.

But as your data is not really document oriented, did you consider using JSON repositories like couchbase ? You could choose to store each entry element in a separate document with SKU as key. Coupled with just a little preprocessing step - that could be written easly in XQuery - transforming your entries element in a JSON array of entry objects, you will obtain incredible performance. Couchbase as a ‘SQL like’ language to query your bucket of documents called N1QL.

Best regards, Fabrice

De : BaseX-Talk [mailto:basex-talk-bounces@mailman.uni-konstanz.de] De la part de Dave Kopecek Envoyé : dimanche 14 octobre 2018 00:10 À : basex-talk@mailman.uni-konstanz.de Objet : Re: [basex-talk] Database Updates

Here are some tweaked examples & better problem definition:

Given a database created from catalog.xml below, what's the best way to update & add records received daily as in file daily-update.xml?

For every <ENTRY> node in daily-update.xml -- If ENTRY/SKU found in catalog.xml replace the entire <ENTRY> node in catalog.xml with the one from daily-update.xml -- If ENTRY/SKU not found in catalog.xml add the <ENTRY> node from daily-update.xml to catalog.xml

catalog.xml <?xml version="1.0" encoding="UTF-8"?> <CATALOG> <ENTRIES> <ENTRY> <SKU>1</SKU> <TITLE>Empire Burlesque</TITLE> <ARTIST>Dylan</ARTIST> <PRICE>10.90</PRICE> </ENTRY> <ENTRY> <SKU>2</SKU> <TITLE>Hide your heart</TITLE> <ARTIST>Bonnie Tyler</ARTIST> <PRICE>9.90</PRICE> </ENTRY> </ENTRIES> </CATALOG>

daily-update.xml <?xml version="1.0" encoding="UTF-8"?> <CATALOG> <ENTRIES> <ENTRY> <SKU>1</SKU> <TITLE>Empire Burlesque</TITLE> <ARTIST>Bob Dylan</ARTIST> <PRICE>29.99</PRICE> <NEWTHING>some value</NEWTHING> </ENTRY> <ENTRY> <SKU>5</SKU> <TITLE>Tupelo Honey</TITLE> <ARTIST>Van Morrison</ARTIST> <PRICE>8.20</PRICE> </ENTRY> </ENTRIES> </CATALOG>

catalog.xml after daily update: <CATALOG> <ENTRIES> <ENTRY> <SKU>1</SKU> <TITLE>Empire Burlesque</TITLE> <ARTIST>Bob Dylan</ARTIST> <PRICE>29.99</PRICE> <NEWTHING>some value</NEWTHING> </ENTRY> <ENTRY> <SKU>2</SKU> <TITLE>Hide your heart</TITLE> <ARTIST>Bonnie Tyler</ARTIST> <PRICE>9.90</PRICE> </ENTRY> <ENTRY> <SKU>5</SKU> <TITLE>Tupelo Honey</TITLE> <ARTIST>Van Morrison</ARTIST> <PRICE>8.20</PRICE> </ENTRY> </ENTRIES> </CATALOG>

Thanks, -Dave

On Fri, Aug 17, 2018 at 11:27 AM Dave Kopecek <dave.kopecek@gmail.commailto:dave.kopecek@gmail.com> wrote: Hi All,

Given a database created from catalog.xml below. What's the best way to update & add records received daily as in file daily-update.xml ?

Coming from relational DBs & new to this. I ultimately need to script/automate this. Hoping there's some magic command I'm missing & looking for best way to approach the problem.

Thanks, -Dave

catalog.xml <?xml version="1.0" encoding="UTF-8"?> <CATALOG> <ENTRIES> <ENTRY> <SKU>1</SKU> <TITLE>Empire Burlesque</TITLE> <ARTIST>Dylan</ARTIST> <PRICE>10.90</PRICE> </ENTRY> <ENTRY> <SKU>2</SKU> <TITLE>Hide your heart</TITLE> <ARTIST>Bonnie Tyler</ARTIST> <PRICE>9.90</PRICE> </ENTRY> </ENTRIES> </CATALOG>

daily-update.xml <?xml version="1.0" encoding="UTF-8"?> <CATALOG> <ENTRIES> <ENTRY> <SKU>1</SKU> <PRICE>19.90</PRICE> <NEWTHING>I wasn't here before the update</NEWTHING> </ENTRY> <ENTRY> <SKU>1971</SKU> <TITLE>Tupelo Honey</TITLE> <ARTIST>Van Morrison</ARTIST> <PRICE>8.20</PRICE> </ENTRY> </ENTRIES> </CATALOG>

-- DAVE KOPECEK OFFICE 607-431-8565 CELL 607-267-3449 6 CROSS STREET, DELHI NY 13753

Christian,

Thank you! That was exactly what I was looking for & somehow far simpler than I thought it needed to be.

-Dave

On Mon, Oct 15, 2018 at 9:07 AM Christian Grün christian.gruen@gmail.com wrote:

Hi Dave,

here is one way to do it:

declare context item := db:open('catalog'); declare variable $update := 'daily-update.xml';

for $new in doc($update)//ENTRY let $old := //ENTRY[SKU = $new/SKU] return if($old) then ( replace node $old with $new ) else ( insert node $new into /CATALOG/ENTRIES )

Hope this helps, Christian

On Sun, Oct 14, 2018 at 12:10 AM Dave Kopecek dave.kopecek@gmail.com wrote:

...

Here are some tweaked examples & better problem definition:

Given a database created from catalog.xml below, what's the best way to

update & add records received daily as in file daily-update.xml?

...

For every <ENTRY> node in daily-update.xml -- If ENTRY/SKU found in catalog.xml replace the entire <ENTRY> node in

catalog.xml with the one from daily-update.xml

...

-- If ENTRY/SKU not found in catalog.xml add the <ENTRY> node from

daily-update.xml to catalog.xml

...

catalog.xml

<?xml version="1.0" encoding="UTF-8"?>

<CATALOG> <ENTRIES> <ENTRY> <SKU>1</SKU> <TITLE>Empire Burlesque</TITLE> <ARTIST>Dylan</ARTIST> <PRICE>10.90</PRICE> </ENTRY> <ENTRY> <SKU>2</SKU> <TITLE>Hide your heart</TITLE> <ARTIST>Bonnie Tyler</ARTIST> <PRICE>9.90</PRICE> </ENTRY> </ENTRIES> </CATALOG>

daily-update.xml

<?xml version="1.0" encoding="UTF-8"?>

<CATALOG> <ENTRIES> <ENTRY> <SKU>1</SKU> <TITLE>Empire Burlesque</TITLE> <ARTIST>Bob Dylan</ARTIST> <PRICE>29.99</PRICE> <NEWTHING>some value</NEWTHING> </ENTRY> <ENTRY> <SKU>5</SKU> <TITLE>Tupelo Honey</TITLE> <ARTIST>Van Morrison</ARTIST> <PRICE>8.20</PRICE> </ENTRY> </ENTRIES> </CATALOG>

catalog.xml after daily update:

<CATALOG> <ENTRIES> <ENTRY> <SKU>1</SKU> <TITLE>Empire Burlesque</TITLE> <ARTIST>Bob Dylan</ARTIST> <PRICE>29.99</PRICE> <NEWTHING>some value</NEWTHING> </ENTRY> <ENTRY> <SKU>2</SKU> <TITLE>Hide your heart</TITLE> <ARTIST>Bonnie Tyler</ARTIST> <PRICE>9.90</PRICE> </ENTRY> <ENTRY> <SKU>5</SKU> <TITLE>Tupelo Honey</TITLE> <ARTIST>Van Morrison</ARTIST> <PRICE>8.20</PRICE> </ENTRY> </ENTRIES> </CATALOG>

Thanks, -Dave

On Fri, Aug 17, 2018 at 11:27 AM Dave Kopecek dave.kopecek@gmail.com

wrote:

...

...

Hi All,

Given a database created from catalog.xml below. What's the best way to

update & add records received daily as in file daily-update.xml ?

...

...

Coming from relational DBs & new to this. I ultimately need to

script/automate this. Hoping there's some magic command I'm missing & looking for best way to approach the problem.

...

...

Thanks, -Dave

catalog.xml

<?xml version="1.0" encoding="UTF-8"?>

<CATALOG> <ENTRIES> <ENTRY> <SKU>1</SKU> <TITLE>Empire Burlesque</TITLE> <ARTIST>Dylan</ARTIST> <PRICE>10.90</PRICE> </ENTRY> <ENTRY> <SKU>2</SKU> <TITLE>Hide your heart</TITLE> <ARTIST>Bonnie Tyler</ARTIST> <PRICE>9.90</PRICE> </ENTRY> </ENTRIES> </CATALOG>

daily-update.xml

<?xml version="1.0" encoding="UTF-8"?>

<CATALOG> <ENTRIES> <ENTRY> <SKU>1</SKU> <PRICE>19.90</PRICE> <NEWTHING>I wasn't here before the update</NEWTHING> </ENTRY> <ENTRY> <SKU>1971</SKU> <TITLE>Tupelo Honey</TITLE> <ARTIST>Van Morrison</ARTIST> <PRICE>8.20</PRICE> </ENTRY> </ENTRIES> </CATALOG>

-- DAVE KOPECEK OFFICE 607-431-8565 CELL 607-267-3449 6 CROSS STREET, DELHI NY 13753