<html><head><base href="x-msg://35/"></head><body style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space; ">Hi Gabe,<div><br></div><div>You create 2 sources (both will point to the same database in your instance) and each will have a different SQL statement. </div><div><br></div><div>It is probably not noticed, but with an IPT you can actually do things like connecting to a postgres database for specimens, and connecting to a mysql database for images provided there are shared unique identifiers in use that effectively do cross database joins. That is pretty advanced usage, but is likely to crop up. Similarly you can upload a CSV file and mix that in with a database connection.</div><div><br></div><div>Cheers,</div><div>Tim</div><div><br><div><div>On Oct 23, 2013, at 2:34 PM, Dröge, Gabriele wrote:</div><br class="Apple-interchange-newline"><blockquote type="cite"><span class="Apple-style-span" style="border-collapse: separate; font-family: Helvetica; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: 2; text-align: -webkit-auto; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-border-horizontal-spacing: 0px; -webkit-border-vertical-spacing: 0px; -webkit-text-decorations-in-effect: none; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px; font-size: medium; "><div lang="DE" link="blue" vlink="purple"><div class="WordSection1" style="page: WordSection1; "><div style="margin-top: 0cm; margin-right: 0cm; margin-left: 0cm; margin-bottom: 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif; "><span style="font-size: 11pt; font-family: Calibri, sans-serif; color: rgb(31, 73, 125); ">Hi Tim,<o:p></o:p></span></div><div style="margin-top: 0cm; margin-right: 0cm; margin-left: 0cm; margin-bottom: 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif; "><span style="font-size: 11pt; font-family: Calibri, sans-serif; color: rgb(31, 73, 125); "><o:p> </o:p></span></div><div style="margin-top: 0cm; margin-right: 0cm; margin-left: 0cm; margin-bottom: 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif; "><span lang="EN-US" style="font-size: 11pt; font-family: Calibri, sans-serif; color: rgb(31, 73, 125); ">Ok, but I am already using such views. How can I use multiple sqls in IPT? E.g. those two you suggested. Do you want me to put them all together in the same Source Data SQL Statement field?<o:p></o:p></span></div><div style="margin-top: 0cm; margin-right: 0cm; margin-left: 0cm; margin-bottom: 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif; "><span lang="EN-US" style="font-size: 11pt; font-family: Calibri, sans-serif; color: rgb(31, 73, 125); "><o:p> </o:p></span></div><div style="margin-top: 0cm; margin-right: 0cm; margin-left: 0cm; margin-bottom: 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif; "><span lang="EN-US" style="font-size: 11pt; font-family: Calibri, sans-serif; color: rgb(31, 73, 125); ">Best,<o:p></o:p></span></div><div style="margin-top: 0cm; margin-right: 0cm; margin-left: 0cm; margin-bottom: 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif; "><span lang="EN-US" style="font-size: 11pt; font-family: Calibri, sans-serif; color: rgb(31, 73, 125); ">Gabi<o:p></o:p></span></div><div style="margin-top: 0cm; margin-right: 0cm; margin-left: 0cm; margin-bottom: 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif; "><span lang="EN-US" style="font-size: 11pt; font-family: Calibri, sans-serif; color: rgb(31, 73, 125); "><o:p> </o:p></span></div><div style="margin-top: 0cm; margin-right: 0cm; margin-left: 0cm; margin-bottom: 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif; "><span><image001.jpg></span><span lang="EN-US" style="font-size: 11pt; font-family: Calibri, sans-serif; color: rgb(31, 73, 125); "><o:p></o:p></span></div><div style="margin-top: 0cm; margin-right: 0cm; margin-left: 0cm; margin-bottom: 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif; "><span lang="EN-US" style="font-size: 11pt; font-family: Calibri, sans-serif; color: rgb(31, 73, 125); "><o:p> </o:p></span></div><div style="margin-top: 0cm; margin-right: 0cm; margin-left: 0cm; margin-bottom: 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif; "><span lang="EN-US"><o:p> </o:p></span></div><div><div style="border-right-style: none; border-bottom-style: none; border-left-style: none; border-width: initial; border-color: initial; border-top-style: solid; border-top-color: rgb(181, 196, 223); border-top-width: 1pt; padding-top: 3pt; padding-right: 0cm; padding-bottom: 0cm; padding-left: 0cm; "><div style="margin-top: 0cm; margin-right: 0cm; margin-left: 0cm; margin-bottom: 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif; "><b><span style="font-size: 10pt; font-family: Tahoma, sans-serif; ">Von:</span></b><span style="font-size: 10pt; font-family: Tahoma, sans-serif; "><span class="Apple-converted-space"> </span>Tim Robertson [GBIF] [mailto:trobertson@gbif.org]<span class="Apple-converted-space"> </span><br><b>Gesendet:</b><span class="Apple-converted-space"> </span>Mittwoch, 23. Oktober 2013 14:01<br><b>An:</b><span class="Apple-converted-space"> </span>Dröge, Gabriele<br><b>Cc:</b><span class="Apple-converted-space"> </span><a href="mailto:ipt@lists.gbif.org">ipt@lists.gbif.org</a><br><b>Betreff:</b><span class="Apple-converted-space"> </span>Re: [IPT] use of extensions; how can duplication of rows be avoided?<o:p></o:p></span></div></div></div><div style="margin-top: 0cm; margin-right: 0cm; margin-left: 0cm; margin-bottom: 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif; "><o:p> </o:p></div><div style="margin-top: 0cm; margin-right: 0cm; margin-left: 0cm; margin-bottom: 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif; ">Hi Gabi,<o:p></o:p></div><div><div style="margin-top: 0cm; margin-right: 0cm; margin-left: 0cm; margin-bottom: 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif; "><o:p> </o:p></div></div><div><div style="margin-top: 0cm; margin-right: 0cm; margin-left: 0cm; margin-bottom: 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif; ">In *typical* use there would be 1 SQL statement for the core, and then one SQL statement per extension.<o:p></o:p></div></div><div><div style="margin-top: 0cm; margin-right: 0cm; margin-left: 0cm; margin-bottom: 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif; ">There are occasions that you would break this though.<o:p></o:p></div></div><div><div style="margin-top: 0cm; margin-right: 0cm; margin-left: 0cm; margin-bottom: 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif; "><o:p> </o:p></div></div><div><div style="margin-top: 0cm; margin-right: 0cm; margin-left: 0cm; margin-bottom: 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif; ">If I were setting up an IPT on MySQL I would use views in the database, and manage those with the database database schema itself. So something along the lines of:<o:p></o:p></div></div><div><div style="margin-top: 0cm; margin-right: 0cm; margin-left: 0cm; margin-bottom: 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif; "><o:p> </o:p></div></div><div><div style="margin-top: 0cm; margin-right: 0cm; margin-left: 0cm; margin-bottom: 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif; "> CREATE VIEW view_ipt_occurrence <o:p></o:p></div></div><div><div style="margin-top: 0cm; margin-right: 0cm; margin-left: 0cm; margin-bottom: 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif; "> AS SELECT id AS id, id AS occurrenceID, name AS scientificName … FROM specimen; <o:p></o:p></div></div><div><div style="margin-top: 0cm; margin-right: 0cm; margin-left: 0cm; margin-bottom: 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif; "> <o:p></o:p></div></div><div><div style="margin-top: 0cm; margin-right: 0cm; margin-left: 0cm; margin-bottom: 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif; "> CREATE VIEW view_ipt_image AS SELECT s.id AS id, s.id AS <o:p></o:p></div></div><div><div style="margin-top: 0cm; margin-right: 0cm; margin-left: 0cm; margin-bottom: 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif; "> SELECT occurrenceID, i.image AS imageURL … FROM image i JOIN specimen s ON i.specimen_id=s.id; <o:p></o:p></div></div><div><div style="margin-top: 0cm; margin-right: 0cm; margin-left: 0cm; margin-bottom: 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif; "><o:p> </o:p></div></div><div><div style="margin-top: 0cm; margin-right: 0cm; margin-left: 0cm; margin-bottom: 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif; ">Then in the IPT on the occurrence mapping I would simply use this knowing it will auto map since the views use DwC terms as column aliases:<o:p></o:p></div></div><div><div style="margin-top: 0cm; margin-right: 0cm; margin-left: 0cm; margin-bottom: 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif; "> SELECT * FROM view_ipt_occurrence;<o:p></o:p></div></div><div><div style="margin-top: 0cm; margin-right: 0cm; margin-left: 0cm; margin-bottom: 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif; "> SELECT * FROM view_ipt_image;<o:p></o:p></div></div><div><div style="margin-top: 0cm; margin-right: 0cm; margin-left: 0cm; margin-bottom: 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif; "><o:p> </o:p></div></div><div><div style="margin-top: 0cm; margin-right: 0cm; margin-left: 0cm; margin-bottom: 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif; ">Does that help at all? <o:p></o:p></div></div><div><div style="margin-top: 0cm; margin-right: 0cm; margin-left: 0cm; margin-bottom: 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif; "><o:p> </o:p></div></div><div><div style="margin-top: 0cm; margin-right: 0cm; margin-left: 0cm; margin-bottom: 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif; ">If you send your DB schema, we will be happy to help out with SQL mappings.<o:p></o:p></div></div><div><div style="margin-top: 0cm; margin-right: 0cm; margin-left: 0cm; margin-bottom: 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif; "><o:p> </o:p></div></div><div><div style="margin-top: 0cm; margin-right: 0cm; margin-left: 0cm; margin-bottom: 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif; ">Thanks,<o:p></o:p></div></div><div><div style="margin-top: 0cm; margin-right: 0cm; margin-left: 0cm; margin-bottom: 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif; ">Tim<o:p></o:p></div></div><div><div style="margin-top: 0cm; margin-right: 0cm; margin-left: 0cm; margin-bottom: 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif; "><o:p> </o:p></div><div><div><div style="margin-top: 0cm; margin-right: 0cm; margin-left: 0cm; margin-bottom: 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif; ">On Oct 23, 2013, at 1:39 PM, Dröge, Gabriele wrote:<o:p></o:p></div></div><div style="margin-top: 0cm; margin-right: 0cm; margin-left: 0cm; margin-bottom: 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif; "><br><br><o:p></o:p></div><div><div><div style="margin-top: 0cm; margin-right: 0cm; margin-left: 0cm; margin-bottom: 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif; "><span style="font-size: 11pt; font-family: Calibri, sans-serif; ">Dear all,<o:p></o:p></span></div></div><div><div style="margin-top: 0cm; margin-right: 0cm; margin-left: 0cm; margin-bottom: 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif; "><span style="font-size: 11pt; font-family: Calibri, sans-serif; "> <o:p></o:p></span></div></div><div><div style="margin-top: 0cm; margin-right: 0cm; margin-left: 0cm; margin-bottom: 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif; "><span lang="EN-US" style="font-size: 11pt; font-family: Calibri, sans-serif; ">I have a general question on Darwin Core and the use of Darwin Core extensions.</span><span style="font-size: 11pt; font-family: Calibri, sans-serif; "><o:p></o:p></span></div></div><div><div style="margin-top: 0cm; margin-right: 0cm; margin-left: 0cm; margin-bottom: 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif; "><span lang="EN-US" style="font-size: 11pt; font-family: Calibri, sans-serif; "> </span><span style="font-size: 11pt; font-family: Calibri, sans-serif; "><o:p></o:p></span></div></div><div><div style="margin-top: 0cm; margin-right: 0cm; margin-left: 0cm; margin-bottom: 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif; "><span lang="EN-US" style="font-size: 11pt; font-family: Calibri, sans-serif; ">E.g. I want to use the Occurrence Core and Simple Images with a mysql database.</span><span style="font-size: 11pt; font-family: Calibri, sans-serif; "><o:p></o:p></span></div></div><div><div style="margin-top: 0cm; margin-right: 0cm; margin-left: 0cm; margin-bottom: 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif; "><span lang="EN-US" style="font-size: 11pt; font-family: Calibri, sans-serif; "> </span><span style="font-size: 11pt; font-family: Calibri, sans-serif; "><o:p></o:p></span></div></div><div><div style="margin-top: 0cm; margin-right: 0cm; margin-left: 0cm; margin-bottom: 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif; "><span lang="EN-US" style="font-size: 11pt; font-family: Calibri, sans-serif; ">Problem: Only a few of my occurrences have images. The Darwin Core Archive creates a large images file with all occurrence IDs and only a few have also Image urls.</span><span style="font-size: 11pt; font-family: Calibri, sans-serif; "><o:p></o:p></span></div></div><div><div style="margin-top: 0cm; margin-right: 0cm; margin-left: 0cm; margin-bottom: 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif; "><span lang="EN-US" style="font-size: 11pt; font-family: Calibri, sans-serif; "> </span><span style="font-size: 11pt; font-family: Calibri, sans-serif; "><o:p></o:p></span></div></div><div><div style="margin-top: 0cm; margin-right: 0cm; margin-left: 0cm; margin-bottom: 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif; "><span lang="EN-US" style="font-size: 11pt; font-family: Calibri, sans-serif; ">But I did expect a file with the image urls only (so much shorter) and all occurrences (with or without image) in the occurrences file.</span><span style="font-size: 11pt; font-family: Calibri, sans-serif; "><o:p></o:p></span></div></div><div><div style="margin-top: 0cm; margin-right: 0cm; margin-left: 0cm; margin-bottom: 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif; "><span lang="EN-US" style="font-size: 11pt; font-family: Calibri, sans-serif; "> </span><span style="font-size: 11pt; font-family: Calibri, sans-serif; "><o:p></o:p></span></div></div><div><div style="margin-top: 0cm; margin-right: 0cm; margin-left: 0cm; margin-bottom: 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif; "><span lang="EN-US" style="font-size: 11pt; font-family: Calibri, sans-serif; ">The same problem appears for the identification extension the other way round (duplication of occurrence rows for multiple identifications) and every other extension I’ve tested…</span><span style="font-size: 11pt; font-family: Calibri, sans-serif; "><o:p></o:p></span></div></div><div><div style="margin-top: 0cm; margin-right: 0cm; margin-left: 0cm; margin-bottom: 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif; "><span lang="EN-US" style="font-size: 11pt; font-family: Calibri, sans-serif; "> </span><span style="font-size: 11pt; font-family: Calibri, sans-serif; "><o:p></o:p></span></div></div><div><div style="margin-top: 0cm; margin-right: 0cm; margin-left: 0cm; margin-bottom: 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif; "><span lang="EN-US" style="font-size: 11pt; font-family: Calibri, sans-serif; ">But I am a bit confused how this can be done with only one big sql query (with some left joins). Do I have to use multiple resources to use multiple sql queries? Or is this a problem/bug of IPT?</span><span style="font-size: 11pt; font-family: Calibri, sans-serif; "><o:p></o:p></span></div></div><div><div style="margin-top: 0cm; margin-right: 0cm; margin-left: 0cm; margin-bottom: 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif; "><span lang="EN-US" style="font-size: 11pt; font-family: Calibri, sans-serif; "> </span><span style="font-size: 11pt; font-family: Calibri, sans-serif; "><o:p></o:p></span></div></div><div><div style="margin-top: 0cm; margin-right: 0cm; margin-left: 0cm; margin-bottom: 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif; "><span lang="EN-US" style="font-size: 11pt; font-family: Calibri, sans-serif; ">Best,</span><span style="font-size: 11pt; font-family: Calibri, sans-serif; "><o:p></o:p></span></div></div><div><div style="margin-top: 0cm; margin-right: 0cm; margin-left: 0cm; margin-bottom: 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif; "><span lang="EN-US" style="font-size: 11pt; font-family: Calibri, sans-serif; ">Gabi</span><span style="font-size: 11pt; font-family: Calibri, sans-serif; "><o:p></o:p></span></div></div><div style="margin-top: 0cm; margin-right: 0cm; margin-left: 0cm; margin-bottom: 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif; "><span style="font-size: 13.5pt; font-family: Helvetica, sans-serif; ">_______________________________________________<br>IPT mailing list<br><a href="mailto:IPT@lists.gbif.org" style="color: blue; text-decoration: underline; ">IPT@lists.gbif.org</a><br><a href="http://lists.gbif.org/mailman/listinfo/ipt" style="color: blue; text-decoration: underline; ">http://lists.gbif.org/mailman/listinfo/ipt</a><o:p></o:p></span></div></div></div><p class="MsoNormal" style="margin-top: 0cm; margin-right: 0cm; margin-left: 0cm; margin-bottom: 0.0001pt; font-size: 12pt; font-family: 'Times New Roman', serif; "></p></div></div></div></span></blockquote></div><br></div></body></html>