Main Page » Mailing List » August 2018

Database Updates

Friday, 17 August 2018 · 6 messages · last reply 25 Oct 2018
CG
Christian Grünin reply to Dave Kopecek, 17 Aug 2018, 19:2717 Aug 2018 17:30 #
How are your resulting database contents supposed to look like after your update?
Quoted text · Dave Kopecek · 52 lines
Dave Kopecek <dave.kopecek at gmail.com> schrieb am Fr., 17. Aug. 2018, 17:27: > 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> > >
DK
Dave Kopecek17 Aug 2018 19:27 #
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>
DK
Dave Kopecek14 Oct 2018 02:10 #
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
Quoted text · Dave Kopecek · 53 lines
On Fri, Aug 17, 2018 at 11:27 AM Dave Kopecek <dave.kopecek at 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
FE
Fabrice ETANCHAUD15 Oct 2018 09:50 #
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
Quoted text · Dave Kopecek · 116 lines
De : BaseX-Talk [mailto:basex-talk-bounces at mailman.uni-konstanz.de] De la part de Dave Kopecek Envoyé : dimanche 14 octobre 2018 00:10 À : basex-talk at 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 at gmail.com<mailto:dave.kopecek at 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
CG
Christian Grünin reply to Dave Kopecek, 14 Oct 2018, 02:1015 Oct 2018 15:07 #
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
Quoted text · Dave Kopecek · 131 lines
On Sun, Oct 14, 2018 at 12:10 AM Dave Kopecek <dave.kopecek at 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 at 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
DK
Dave Kopecek25 Oct 2018 22:17 #
Christian, Thank you! That was exactly what I was looking for & somehow far simpler than I thought it needed to be. -Dave
Quoted text · Christian Grün · 161 lines
On Mon, Oct 15, 2018 at 9:07 AM Christian Grün <christian.gruen at 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 at 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 at 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 >
-- *DAVE KOPECEK* OFFICE 607-431-8565 CELL *607-267-3449* 6 CROSS STREET, DELHI NY 13753

Written for BaseX 9.0. BaseX may have changed since: please check the current documentation.

⚡Generated with XQuery