EDIT: Issues
https://dev.e-taxonomy.eu/redmine/
https://dev.e-taxonomy.eu/redmine/redmine/favicon.ico?1469191485
2019-05-15T07:17:15Z
EDIT Project Management
Redmine
feature request #8276 (New): [DISCUSS] References need bibliographicAuthorCache
https://dev.e-taxonomy.eu/redmine/issues/8276
2019-05-15T07:17:15Z
Andreas Müller
<p>The wanted bibliographic citation for an author may differ from the standard citation. Therefore we may need a cache and a protectedFlag for the wanted bibliographic citation.</p>
<p>But the problem is, that this prevents from formatting the reference in different ways.</p>
feature request #7799 (Resolved): AM: Parse authorteams
https://dev.e-taxonomy.eu/redmine/issues/7799
2018-09-29T10:04:50Z
Andreas Müller
<p>many of them are single persons and also the resulting persons/teams could be parsed probably</p>
task #7798 (Resolved): [AM, ERS]: Import (freetext) nameInSource correctly
https://dev.e-taxonomy.eu/redmine/issues/7798
2018-09-29T09:40:29Z
Andreas Müller
<p>For occurrence sources and maybe also for common names and facts.</p>
<p>Occurrences:</p>
<ol>
<li>case: freetext exists parallel to link => if nameCache or fullnameCache are exactly equal then neglect freetext, otherwise store it parallel to name link; a list has been sent to ERS to clean up these cases (see <a class="issue tracker-6 status-3 priority-10 priority-lowest" title="task: [AM, ERS]: Import (freetext) nameInSource correctly (Resolved)" href="https://dev.e-taxonomy.eu/redmine/issues/7798#note-4">#7798#note-4</a>)</li>
<li><p>freetext is only name</p>
<p>a. name can not be found for any Name.nameCache => we could create new TaxonName for this, but will not as it polutes the DB, we better try to clean up theses cases over time (see <a class="issue tracker-6 status-3 priority-10 priority-lowest" title="task: [AM, ERS]: Import (freetext) nameInSource correctly (Resolved)" href="https://dev.e-taxonomy.eu/redmine/issues/7798#note-6">#7798#note-6</a>)<br>
b. >1 name is mentioned, separated by '/'. We better clone these sources (now or later)<br>
c. a name exists in DB, we try to find the best matching name by first searching in the synonymy and, if not exists use the single matching name, if >1 matching names exists, log the case and use the first matching name (prefer name without author)</p></li>
</ol>
<p>Most important for import: try to reduce the number of names that only come via occurrence import. These are</p>
<ul>
<li>"Orphaned name: A similar name" : names that do not belong to any taxon but a matching name was found in "synonymy"
* Decide for invalid designations (why do they show up?) and orthographic variants </li>
<li>"Orphaned name: A similar misapplied name" : like above but for misapplications.
* Decide on all</li>
<li>TBC</li>
</ul>
<p>open issues:</p>
<ul>
<li>test code</li>
<li>test for facts and common names</li>
</ul>
task #7796 (Resolved): [CHECK] Handle Bibliography Name Facts correctly
https://dev.e-taxonomy.eu/redmine/issues/7796
2018-09-28T18:01:49Z
Andreas Müller
<p>There are 13568 name facts of type "Bibliography" (Cat=3).</p>
<p>These are currently attached to the according names as TaxonNameDescription with feature "Citation".</p>
<p>The citations seem to come from ILDIS but the names are also used in 7600000 so they often appear twice.</p>
<p>The Citations currently do not show up in the BM data portal. In CDM they show up behind the name formatted same as second nomenclatural references.</p>
<p>Decisions to take:</p>
<ul>
<li>Should they show up in the data portal => probably not (see <a class="issue tracker-6 status-3 priority-12 priority-high14" title="task: [CHECK] Handle Bibliography Name Facts correctly (Resolved)" href="https://dev.e-taxonomy.eu/redmine/issues/7796#note-8">#7796#note-8</a>)</li>
<li>Depending on this, keep them as TaxonName Citations or make them TaxonName extensions. The later may not show up in the portal by default.</li>
</ul>
<p>Implementations:</p>
<ul>
<li><del>If keeping them as TaxonNameDescriptions make these available by TaxEditor again</del></li>
<li>If extensions, implement extension import (with own feature)</li>
</ul>
<p>Decisition: we keep them as citations</p>
bug #7786 (Resolved): ERS: Try to solve those occurrence sources for which the reference number c...
https://dev.e-taxonomy.eu/redmine/issues/7786
2018-09-21T15:35:02Z
Andreas Müller
<p>Some of them only have a trailing whitespace problem and can be found by </p>
<pre>SELECT *
FROM emOccurrenceSource
WHERE SourceNumber like '% %'
</pre>
<p>remove whitespaces</p>
<p>====</p>
<p>for others the sourceNumber can not be found in Referenc.IdInSource</p>
<pre>bei den OccurrenceSources sind ein paar kaputt gegangen, vermutlich weil die Identifier hier nicht als Zahlen sondern als Text gespeichert werden, aus welchem Grund auch immer.
Ich versuche das gerade zu säubern. Gehe ich daher Recht in der Annahme, dass
• Die 2. Quelle von Polygonum propinquum Ledeb. In Rf(E) „ Tzvelev, N. N. - Konspekt flory Vostochnoi Evropy 1 2012.“ ist, genauso wie für Rf(C), Rf(S) und Uk(K) ?
• Entsprechend von Persicaria lapathifolia subsp. brittingeri (Opiz) Soják für Rf(C) / SourceNumber = 22016
Außerdem: Welches ist die Quelle von
• Bromus scoparius L. in Ge – SourceNumber 132003 nicht gefunden
• Fallopia dumetorum (L.) Holub in Rf(C) - SourceNumber = 12231 nicht gefunden, 122311, 122312, 122315 für andere Common Names vorhanden
• Von Solanum sinaicum in Ir -> SourceNumber = 13042 . Vielleicht Danin, A. - Wild plants of Eretz Israel and their distribution 1998. ? / 130421, gibt aber auch andere 13042x
• Turnera ulmifolia L. , alle Areas außer Ca(T), Ca(F); / SourceNumber = 71575
• Evtl. selbe Source für Consolida ajacis (L.) Schur für Ca(P) , Ca(H) / SourceNumber= 71575
• Rumex conglomeratus Murray für It (2x) / SourceNumber = 0087 und = 7
• Ceratochloa cathartica (Vahl) Herter für Cr / SourceNumber = 130017
• Vitis rupestris Scheele und Vitis labrusca L. für Hu / SourceNumber = 5
• Juncus tingitanus Maire & Weiller für Ma / SourceNumber = 28024
• Reynoutria japonica Houtt. für Lt / SourceNumber = 12206 , evtl. Tzvelev, N. N. - Flora of Russia. The European part and bordering regions 9 2006. ?? / 122016
Nicht notwendig vermutlich
• Ammannia pubiflora (Koehne) Sosn. In EM / SourceNumber = 130432
</pre><pre>SELECT n.FullNameCache, ar.EMCode, n.NameCache, oc.PTRefFk, ocs.Source, ocs.SourceNumber, ocs.OccurrenceSourceId, oc.OccurrenceId, oc.PTNameFk, oc.PTRefFk AS Expr1, oc.AreaFk, oc.StatusUnknown,
oc.Native, oc.Introduced, oc.Cultivated, oc.WorldDistCompl, oc.SummaryStatus, oc.CalculatedFlag, oc.PTDesignationRefDetailFk, oc.PTDesignationRefFk, oc.Preferred, oc.UnresolvedPOSS, oc.As_Taxon,
oc.Sources, oc.Created_When, oc.Created_Who, oc.Updated_When, oc.Updated_Who, oc.Notes, oc.Occurrence, oc.NativeStatus, oc.IntroducedStatus, oc.CultivatedStatus, oc._oldOccId
FROM emOccurrence AS oc INNER JOIN
emOccurrenceSource AS ocs ON ocs.OccurrenceFk = oc.OccurrenceId INNER JOIN
Name AS n ON n.NameId = oc.PTNameFk LEFT OUTER JOIN
emArea AS ar ON ar.AreaId = oc.AreaFk
WHERE (ocs.SourceNumber LIKE '57610') OR
(oc.PTNameFk IN (0))
ORDER BY ocs.SourceNumber
</pre>
<p>Dann gibt es noch:</p>
<pre>
Juncus acutiflorus Hoffm.
Juncus acutus L.
Juncus anceps Laharpe
Juncus articulatus L.
Juncus bufonius L.
Juncus bulbosus L.
Juncus compressus Jacq.
Juncus foliosus Desf.
Juncus gerardi Loisel.
Juncus heterophyllus Dufour
Juncus conglomeratus L.
Juncus effusus L.
Juncus maritimus Lam.
Juncus pygmaeus Thuill.
Juncus squarrosus L.
Juncus striatus E. Mey.
Juncus subulatus Forssk.
Juncus tenageia L. f.
Juncus tingitanus Maire & Weiller
Alle für Area Ma / SourceNumber = 57610
Und:
FullNameCache EMCode
Arum creticum Boiss. & Heldr. Cr
Arum elongatum Steven Tu(A)
Arum elongatum Steven Bu
Arum cyrenaicum Hruby Li
Arum balansanum R. R. Mill Tu(A)
Arum dioscoridis Sm. Tu(A)
Arum dioscoridis Sm. Cy
Arum creticum Boiss. & Heldr. AE(G)
Arum cyrenaicum Hruby Cr
Arum dioscoridis Sm. AE(G)
Arum creticum Boiss. & Heldr. Tu(A)
Arum apulum (Carano) P. C. Boyce It
Arum concinnatum Schott Gr
Arum concinnatum Schott AE(G)
Arum concinnatum Schott Cr
Arum elongatum Steven Gr
Arum dioscoridis Sm. Ir
Arum besserianum Schott Uk(U)
Arum concinnatum Schott Tu(A)
Arum dioscoridis Sm. Le
Arum dioscoridis Sm. Sy
Alle für SourceNumber = 71748
</pre>
<p>Diese IdInSource gibt es. => Zu SourceNumber2Ref hinzugefügt => Testen obs jetzt tut</p>
<p>Sources mit leerer SourceNumber: 76</p>
<pre>SELECT n.FullNameCache, ar.EMCode, n.NameCache, oc.PTRefFk, ocs.Source, ocs.SourceNumber, ocs.OccurrenceSourceId, oc.OccurrenceId, oc.PTNameFk, oc.PTRefFk AS Expr1, oc.AreaFk, oc.StatusUnknown,
oc.Native, oc.Introduced, oc.Cultivated, oc.WorldDistCompl, oc.SummaryStatus, oc.CalculatedFlag, oc.PTDesignationRefDetailFk, oc.PTDesignationRefFk, oc.Preferred, oc.UnresolvedPOSS, oc.As_Taxon,
oc.Sources, oc.Created_When, oc.Created_Who, oc.Updated_When, oc.Updated_Who, oc.Notes, oc.Occurrence, oc.NativeStatus, oc.IntroducedStatus, oc.CultivatedStatus, oc._oldOccId
FROM emOccurrence AS oc INNER JOIN
emOccurrenceSource AS ocs ON ocs.OccurrenceFk = oc.OccurrenceId INNER JOIN
Name AS n ON n.NameId = oc.PTNameFk LEFT OUTER JOIN
emArea AS ar ON ar.AreaId = oc.AreaFk
WHERE (ocs.SourceNumber LIKE '') AND occurrenceFk IN ( SELECT occurrenceId FROM v_cdm_exp_occurrenceAll )
ORDER BY PTRefFk, FullNameCache, ocs.SourceNumber
</pre>
feature request #7769 (Resolved): E+M: Handle postulated parental species as taxon extensions
https://dev.e-taxonomy.eu/redmine/issues/7769
2018-09-18T13:28:14Z
Andreas Müller
<p>Postulated parental species are Name.Notes in BM. They should become Extensions of respective type, to be shown behind the taxon in data portal.</p>
<p>ERS:</p>
<p>ja, diese notes sollen angezeigt werden. In der Tat ist es eher eine Taxoninformation.</p>
<p>Label: postulated parental species<br>
Description: For intermediate, so-called "collective" species in the genus Pilosella, a combination of the postulated parental basic species is given.</p>
<p>In der Med-Checklist 2 steht diese Einformation in eckigen Klammern direkt hinter dem Taxonnamen der collective species, also z.B.:</p>
<p>Pilosella flagellaris coll.[Pilosella caespitosa <> officinarum]</p>
<p>So könnte es im Portal angezeigt werden. Unter eigenen Überschift würde ich es nicht führen, wenn nicht wie oben, dann eher als Fußnote.</p>
task #7455 (Resolved): [Check] Check all markers for correctnes
https://dev.e-taxonomy.eu/redmine/issues/7455
2018-06-07T11:21:51Z
Andreas Müller
<p>Check if all existing markers are still up-to-date with the current E+M import strategy.</p>
<pre>SELECT flag, dtb.titleCache, count(*) as n
FROM Marker m INNEr JOIN DefinedTermBase dtb ON dtb.id = m.markerType_id
GROUP BY flag, titleCache
ORDER BY n, titleCache, flag;
SELECT deb.DTYPE, deb.feature_id, dtb.titleCache
FROM DescriptionElementBase deb
INNER JOIN DescriptionElementBase_Marker MN ON MN.DescriptionElementBase_id = deb.id
INNER JOIN Marker m ON m.id = MN.markers_id
INNEr JOIN DefinedTermBase dtb ON dtb.id = m.markerType_id
ORDER BY deb.DTYPE, dtb.titleCache
</pre>
<p>The most important decision is the publish flag on distributions. Is it needed?</p>
<p><img src="https://dev.e-taxonomy.eu/redmine/attachments/download/1386/picture714-1.png" alt="" /></p>
bug #7449 (Resolved): E+M: Import common names with no regionFk correctly
https://dev.e-taxonomy.eu/redmine/issues/7449
2018-06-03T20:02:01Z
Andreas Müller
<p>Currently it looks they are not imported at all as there is an iteration over the set of regions and for each region a common name is created.</p>
<p>Note: also check if multiple regionFks are correctly imported.</p>
bug #7447 (Resolved): ERS: Fix misapplied name issues for E+M common names.
https://dev.e-taxonomy.eu/redmine/issues/7447
2018-06-03T17:04:23Z
Andreas Müller
<p>Fix misapplied name issues for E+M common names.</p>
<p>There are 2 issues:</p>
<ol>
<li>BerlinModelCommonNamesImport:341 - Misapplied name for common name was not found related to the accepted taxon. Created new relationship. CommonNameId: xxxx</li>
<li>BerlinModelCommonNamesImport:316 - MisappliedName not found for misappliedTaxonId 415470; commonNameId: 44</li>
<li>~<del>Also attaching the cn to both taxa and source handling does not yet fully work and should be fixed within this ticket.</del>~ => I did not find a problem here, I checked for 2 records, in both cases 2 common names were created with sources attached and correct nameInSource, so this seems to be fxied</li>
</ol>
<p>===</p>
<p>For 1 only the relation between the misapplied name and the accepted taxon does not exist. It is unclear why because they in generell seem to exist but are not found by the algorithm which looks into the set of existing misapplied names and tests if the expected misName is contained.</p>
<pre><code class="sql syntaxhl"><span class="k">SELECT</span> <span class="n">n</span><span class="p">.</span><span class="n">nameId</span><span class="p">,</span> <span class="n">accn</span><span class="p">.</span><span class="n">NameCache</span> <span class="n">acc</span><span class="p">,</span> <span class="n">n</span><span class="p">.</span><span class="n">FullNameCache</span> <span class="n">sourceName</span><span class="p">,</span> <span class="n">cn</span><span class="p">.</span><span class="n">MisNameRefFk</span> <span class="n">misRef</span><span class="p">,</span> <span class="n">misRef</span><span class="p">.</span><span class="n">RefCache</span><span class="p">,</span>
<span class="n">pt_n</span><span class="p">.</span><span class="n">RIdentifier</span><span class="p">,</span> <span class="n">pt_n</span><span class="p">.</span><span class="n">PTNameFk</span><span class="p">,</span> <span class="n">pt_n</span><span class="p">.</span><span class="n">PTRefFk</span><span class="p">,</span>
<span class="n">cn</span><span class="p">.</span><span class="o">*</span>
<span class="k">FROM</span> <span class="n">emCommonName</span> <span class="n">cn</span>
<span class="k">LEFT</span> <span class="k">OUTER</span> <span class="k">JOIN</span> <span class="n">Name</span> <span class="n">accn</span> <span class="k">ON</span> <span class="n">accn</span><span class="p">.</span><span class="n">nameId</span> <span class="o">=</span> <span class="n">cn</span><span class="p">.</span><span class="n">PTNameFk</span>
<span class="k">LEFT</span> <span class="k">OUTER</span> <span class="k">JOIN</span> <span class="n">Name</span> <span class="n">n</span> <span class="k">ON</span> <span class="n">n</span><span class="p">.</span><span class="n">nameId</span> <span class="o">=</span> <span class="n">cn</span><span class="p">.</span><span class="n">NameInSourceFk</span>
<span class="k">LEFT</span> <span class="k">OUTER</span> <span class="k">JOIN</span> <span class="n">Reference</span> <span class="n">misRef</span> <span class="k">ON</span> <span class="n">misRef</span><span class="p">.</span><span class="n">RefId</span> <span class="o">=</span> <span class="n">cn</span><span class="p">.</span><span class="n">MisNameRefFk</span>
<span class="k">LEFT</span> <span class="k">OUTER</span> <span class="k">JOIN</span> <span class="n">PTaxon</span> <span class="n">pt_n</span> <span class="k">ON</span> <span class="n">cn</span><span class="p">.</span><span class="n">NameInSourceFk</span> <span class="o">=</span> <span class="n">pt_n</span><span class="p">.</span><span class="n">PTNameFk</span> <span class="k">AND</span> <span class="n">cn</span><span class="p">.</span><span class="n">MisNameRefFk</span> <span class="o">=</span> <span class="n">pt_n</span><span class="p">.</span><span class="n">PTRefFk</span>
<span class="k">WHERE</span> <span class="n">CommonNameId</span> <span class="k">IN</span> <span class="p">(</span>
<span class="mi">2280</span><span class="p">,</span><span class="mi">5358</span><span class="p">,</span><span class="mi">7869</span><span class="p">,</span><span class="mi">8056</span><span class="p">,</span><span class="mi">8057</span><span class="p">,</span><span class="mi">8230</span><span class="p">,</span><span class="mi">8237</span><span class="p">,</span><span class="mi">8238</span><span class="p">,</span><span class="mi">8287</span><span class="p">,</span><span class="mi">8304</span><span class="p">,</span><span class="mi">8310</span><span class="p">,</span><span class="mi">8354</span><span class="p">,</span><span class="mi">8372</span><span class="p">,</span><span class="mi">8396</span><span class="p">,</span><span class="mi">8410</span><span class="p">,</span><span class="mi">8473</span><span class="p">,</span><span class="mi">8506</span><span class="p">,</span><span class="mi">8536</span><span class="p">,</span><span class="mi">8543</span><span class="p">,</span><span class="mi">8550</span><span class="p">,</span>
<span class="mi">8789</span><span class="p">,</span><span class="mi">8859</span><span class="p">,</span><span class="mi">8860</span><span class="p">,</span><span class="mi">9118</span><span class="p">,</span><span class="mi">9126</span><span class="p">,</span><span class="mi">9191</span><span class="p">,</span><span class="mi">9470</span><span class="p">,</span><span class="mi">9499</span><span class="p">,</span><span class="mi">9523</span><span class="p">,</span><span class="mi">9541</span><span class="p">,</span><span class="mi">9610</span><span class="p">,</span><span class="mi">9816</span><span class="p">,</span><span class="mi">9865</span><span class="p">,</span><span class="mi">9914</span><span class="p">,</span><span class="mi">10051</span><span class="p">,</span><span class="mi">10075</span><span class="p">,</span><span class="mi">10081</span><span class="p">,</span><span class="mi">10090</span><span class="p">,</span><span class="mi">10094</span><span class="p">,</span>
<span class="mi">10785</span><span class="p">,</span><span class="mi">10925</span><span class="p">,</span><span class="mi">11968</span><span class="p">,</span><span class="mi">12003</span><span class="p">,</span><span class="mi">12130</span><span class="p">,</span><span class="mi">12150</span><span class="p">,</span><span class="mi">12184</span><span class="p">,</span><span class="mi">12280</span><span class="p">,</span><span class="mi">12331</span><span class="p">,</span><span class="mi">12396</span><span class="p">,</span><span class="mi">12445</span><span class="p">,</span><span class="mi">12446</span><span class="p">,</span><span class="mi">12447</span><span class="p">,</span><span class="mi">12453</span><span class="p">,</span><span class="mi">12469</span><span class="p">,</span><span class="mi">12492</span><span class="p">,</span>
<span class="mi">12495</span><span class="p">,</span><span class="mi">12498</span><span class="p">,</span><span class="mi">12506</span><span class="p">,</span><span class="mi">12510</span><span class="p">,</span><span class="mi">12514</span><span class="p">,</span><span class="mi">12658</span><span class="p">,</span><span class="mi">13041</span><span class="p">,</span><span class="mi">13325</span><span class="p">,</span><span class="mi">13404</span><span class="p">,</span><span class="mi">13406</span><span class="p">,</span><span class="mi">13556</span><span class="p">,</span><span class="mi">13667</span><span class="p">,</span><span class="mi">13721</span><span class="p">,</span><span class="mi">14122</span><span class="p">,</span><span class="mi">14175</span><span class="p">,</span><span class="mi">14655</span><span class="p">,</span>
<span class="mi">17314</span><span class="p">,</span><span class="mi">17315</span><span class="p">,</span><span class="mi">17317</span><span class="p">,</span><span class="mi">17320</span><span class="p">,</span><span class="mi">17380</span><span class="p">,</span><span class="mi">17427</span><span class="p">,</span><span class="mi">17428</span><span class="p">,</span><span class="mi">17432</span><span class="p">,</span><span class="mi">17562</span><span class="p">,</span><span class="mi">17585</span><span class="p">,</span><span class="mi">17630</span><span class="p">,</span><span class="mi">137620</span><span class="p">,</span><span class="mi">141872</span><span class="p">,</span><span class="mi">142653</span><span class="p">,</span><span class="mi">142957</span><span class="p">,</span><span class="mi">143006</span><span class="p">,</span>
<span class="mi">143018</span><span class="p">,</span><span class="mi">143064</span><span class="p">,</span><span class="mi">143072</span><span class="p">,</span><span class="mi">143301</span><span class="p">,</span><span class="mi">143809</span><span class="p">,</span><span class="mi">144062</span><span class="p">,</span><span class="mi">144975</span><span class="p">,</span><span class="mi">145189</span><span class="p">,</span><span class="mi">145295</span><span class="p">,</span><span class="mi">145319</span><span class="p">,</span><span class="mi">145327</span><span class="p">,</span><span class="mi">146211</span><span class="p">,</span><span class="mi">146539</span><span class="p">,</span><span class="mi">146981</span><span class="p">,</span><span class="mi">148786</span><span class="p">,</span><span class="mi">148802</span><span class="p">,</span>
<span class="mi">149389</span><span class="p">,</span><span class="mi">149662</span><span class="p">,</span><span class="mi">149774</span><span class="p">,</span><span class="mi">149808</span><span class="p">,</span><span class="mi">150389</span><span class="p">,</span><span class="mi">150751</span><span class="p">,</span><span class="mi">150831</span><span class="p">,</span><span class="mi">150841</span><span class="p">,</span><span class="mi">150959</span><span class="p">,</span><span class="mi">150996</span><span class="p">,</span><span class="mi">151006</span><span class="p">)</span>
<span class="k">ORDER</span> <span class="k">BY</span> <span class="n">cn</span><span class="p">.</span><span class="n">CommonNameId</span><span class="p">,</span> <span class="n">pt_n</span><span class="p">.</span><span class="n">RIdentifier</span>
</code></pre>
<p>For 2 the relation ship realy does not exist. Need to ask ERS if we shouldn't add it.</p>
<pre><code class="sql syntaxhl"><span class="k">SELECT</span> <span class="n">accn</span><span class="p">.</span><span class="n">NameCache</span> <span class="n">acc</span><span class="p">,</span> <span class="n">cn</span><span class="p">.</span><span class="n">PTRefFk</span><span class="p">,</span> <span class="n">n</span><span class="p">.</span><span class="n">nameId</span> <span class="n">misNameId</span><span class="p">,</span> <span class="n">n</span><span class="p">.</span><span class="n">FullNameCache</span> <span class="n">misappliedName</span><span class="p">,</span> <span class="n">cn</span><span class="p">.</span><span class="n">MisNameRefFk</span> <span class="n">misRef</span><span class="p">,</span> <span class="n">misRef</span><span class="p">.</span><span class="n">RefCache</span><span class="p">,</span>
<span class="n">pt_n</span><span class="p">.</span><span class="n">RIdentifier</span><span class="p">,</span> <span class="n">pt_n</span><span class="p">.</span><span class="n">PTNameFk</span><span class="p">,</span> <span class="n">pt_n</span><span class="p">.</span><span class="n">PTRefFk</span><span class="p">,</span>
<span class="n">cn</span><span class="p">.</span><span class="o">*</span>
<span class="k">FROM</span> <span class="n">emCommonName</span> <span class="n">cn</span>
<span class="k">LEFT</span> <span class="k">OUTER</span> <span class="k">JOIN</span> <span class="n">Name</span> <span class="n">accn</span> <span class="k">ON</span> <span class="n">accn</span><span class="p">.</span><span class="n">nameId</span> <span class="o">=</span> <span class="n">cn</span><span class="p">.</span><span class="n">PTNameFk</span>
<span class="k">LEFT</span> <span class="k">OUTER</span> <span class="k">JOIN</span> <span class="n">Name</span> <span class="n">n</span> <span class="k">ON</span> <span class="n">n</span><span class="p">.</span><span class="n">nameId</span> <span class="o">=</span> <span class="n">cn</span><span class="p">.</span><span class="n">NameInSourceFk</span>
<span class="k">LEFT</span> <span class="k">OUTER</span> <span class="k">JOIN</span> <span class="n">Reference</span> <span class="n">misRef</span> <span class="k">ON</span> <span class="n">misRef</span><span class="p">.</span><span class="n">RefId</span> <span class="o">=</span> <span class="n">cn</span><span class="p">.</span><span class="n">MisNameRefFk</span>
<span class="k">LEFT</span> <span class="k">OUTER</span> <span class="k">JOIN</span> <span class="n">PTaxon</span> <span class="n">pt_n</span> <span class="k">ON</span> <span class="n">cn</span><span class="p">.</span><span class="n">NameInSourceFk</span> <span class="o">=</span> <span class="n">pt_n</span><span class="p">.</span><span class="n">PTNameFk</span> <span class="k">AND</span> <span class="n">cn</span><span class="p">.</span><span class="n">MisNameRefFk</span> <span class="o">=</span> <span class="n">pt_n</span><span class="p">.</span><span class="n">PTRefFk</span>
<span class="k">WHERE</span> <span class="n">CommonNameId</span> <span class="k">IN</span> <span class="p">(</span>
<span class="mi">44</span><span class="p">,</span><span class="mi">9861</span><span class="p">,</span><span class="mi">9882</span><span class="p">,</span><span class="mi">12178</span><span class="p">,</span><span class="mi">17318</span><span class="p">,</span><span class="mi">17327</span><span class="p">,</span><span class="mi">17446</span><span class="p">,</span><span class="mi">17447</span><span class="p">,</span><span class="mi">151113</span><span class="p">)</span>
<span class="k">ORDER</span> <span class="k">BY</span> <span class="n">CommonNameId</span><span class="p">,</span> <span class="n">pt_n</span><span class="p">.</span><span class="n">RIdentifier</span><span class="p">;</span>
<span class="k">SELECT</span> <span class="o">*</span>
<span class="k">FROM</span> <span class="n">PTaxon</span> <span class="n">pt</span>
<span class="k">WHERE</span> <span class="n">pt</span><span class="p">.</span><span class="n">RIdentifier</span> <span class="k">IN</span> <span class="p">(</span><span class="mi">415470</span><span class="p">);</span>
<span class="k">SELECT</span> <span class="o">*</span>
<span class="k">FROM</span> <span class="n">RelPTaxon</span> <span class="n">rel</span>
<span class="k">WHERE</span> <span class="n">rel</span><span class="p">.</span><span class="n">PTNameFk1</span> <span class="o">=</span> <span class="mi">7521920</span> <span class="k">AND</span> <span class="n">rel</span><span class="p">.</span><span class="n">PTRefFk1</span> <span class="o">=</span> <span class="mi">6593</span><span class="p">;</span>
</code></pre>
task #7446 (New): Check if all extensions are needed
https://dev.e-taxonomy.eu/redmine/issues/7446
2018-06-03T13:41:33Z
Andreas Müller
<p>about 560.000 Extensions are currently imported. </p>
<p>We need to check if they are all needed or if some of them are duplicates. Also check if they should better run into alternative identifiers.</p>
<p>Once done we need to check if they should be shown on the dataportal.</p>
<pre><code class="sql syntaxhl"><span class="k">SELECT</span> <span class="n">e</span><span class="p">.</span><span class="n">type_id</span><span class="p">,</span> <span class="n">et</span><span class="p">.</span><span class="n">titleCache</span><span class="p">,</span> <span class="n">ab</span><span class="p">.</span><span class="n">DTYPE</span><span class="p">,</span> <span class="n">r</span><span class="p">.</span><span class="n">refType</span><span class="p">,</span> <span class="n">dtb</span><span class="p">.</span><span class="n">DTYPE</span><span class="p">,</span> <span class="k">count</span><span class="p">(</span><span class="o">*</span><span class="p">)</span> <span class="k">as</span> <span class="n">n</span>
<span class="k">FROM</span> <span class="n">Extension</span> <span class="n">e</span> <span class="k">INNER</span> <span class="k">JOIN</span> <span class="n">DefinedTermBase</span> <span class="n">et</span> <span class="k">ON</span> <span class="n">et</span><span class="p">.</span><span class="n">id</span> <span class="o">=</span> <span class="n">e</span><span class="p">.</span><span class="n">type_id</span>
<span class="k">LEFT</span> <span class="k">OUTER</span> <span class="k">JOIN</span> <span class="n">AgentBase_Extension</span> <span class="n">abMN</span> <span class="k">ON</span> <span class="n">abMN</span><span class="p">.</span><span class="n">extensions_id</span> <span class="o">=</span> <span class="n">e</span><span class="p">.</span><span class="n">id</span>
<span class="k">LEFT</span> <span class="k">OUTER</span> <span class="k">JOIN</span> <span class="n">AgentBase</span> <span class="n">ab</span> <span class="k">ON</span> <span class="n">ab</span><span class="p">.</span><span class="n">id</span> <span class="o">=</span> <span class="n">abMN</span><span class="p">.</span><span class="n">AgentBase_id</span>
<span class="k">LEFT</span> <span class="k">OUTER</span> <span class="k">JOIN</span> <span class="n">DefinedTermBase_Extension</span> <span class="n">dtbMN</span> <span class="k">ON</span> <span class="n">dtbMN</span><span class="p">.</span><span class="n">extensions_id</span> <span class="o">=</span> <span class="n">e</span><span class="p">.</span><span class="n">id</span>
<span class="k">LEFT</span> <span class="k">OUTER</span> <span class="k">JOIN</span> <span class="n">DefinedTermBase</span> <span class="n">dtb</span> <span class="k">ON</span> <span class="n">dtb</span><span class="p">.</span><span class="n">id</span> <span class="o">=</span> <span class="n">dtbMN</span><span class="p">.</span><span class="n">DefinedTermBase_id</span>
<span class="k">LEFT</span> <span class="k">OUTER</span> <span class="k">JOIN</span> <span class="n">Reference_Extension</span> <span class="n">rMN</span> <span class="k">ON</span> <span class="n">rMN</span><span class="p">.</span><span class="n">extensions_id</span> <span class="o">=</span> <span class="n">e</span><span class="p">.</span><span class="n">id</span>
<span class="k">LEFT</span> <span class="k">OUTER</span> <span class="k">JOIN</span> <span class="n">Reference</span> <span class="n">r</span> <span class="k">ON</span> <span class="n">r</span><span class="p">.</span><span class="n">id</span> <span class="o">=</span> <span class="n">rMN</span><span class="p">.</span><span class="n">Reference_id</span>
<span class="k">GROUP</span> <span class="k">BY</span> <span class="n">type_id</span><span class="p">,</span> <span class="n">et</span><span class="p">.</span><span class="n">titleCache</span><span class="p">,</span> <span class="n">ab</span><span class="p">.</span><span class="n">DTYPE</span><span class="p">,</span> <span class="n">r</span><span class="p">.</span><span class="n">refType</span><span class="p">,</span> <span class="n">dtb</span><span class="p">.</span><span class="n">DTYPE</span>
<span class="k">ORDER</span> <span class="k">BY</span> <span class="n">et</span><span class="p">.</span><span class="n">titleCache</span><span class="p">,</span> <span class="n">ab</span><span class="p">.</span><span class="n">DTYPE</span><span class="p">,</span> <span class="n">r</span><span class="p">.</span><span class="n">refType</span><span class="p">,</span> <span class="n">dtb</span><span class="p">.</span><span class="n">DTYPE</span><span class="p">,</span> <span class="n">n</span>
</code></pre><pre><code class="sql syntaxhl"><span class="k">SELECT</span> <span class="n">e</span><span class="p">.</span><span class="n">type_id</span><span class="p">,</span> <span class="n">e</span><span class="p">.</span><span class="n">value</span><span class="p">,</span> <span class="n">et</span><span class="p">.</span><span class="n">titleCache</span><span class="p">,</span> <span class="n">tb</span><span class="p">.</span><span class="n">DTYPE</span><span class="p">,</span> <span class="n">r</span><span class="p">.</span><span class="n">refType</span><span class="p">,</span> <span class="n">r</span><span class="p">.</span><span class="n">id</span><span class="p">,</span> <span class="n">r</span><span class="p">.</span><span class="n">titleCache</span><span class="p">,</span> <span class="n">dtb</span><span class="p">.</span><span class="n">DTYPE</span>
<span class="k">FROM</span> <span class="n">Extension</span> <span class="n">e</span> <span class="k">INNER</span> <span class="k">JOIN</span> <span class="n">DefinedTermBase</span> <span class="n">et</span> <span class="k">ON</span> <span class="n">et</span><span class="p">.</span><span class="n">id</span> <span class="o">=</span> <span class="n">e</span><span class="p">.</span><span class="n">type_id</span>
<span class="k">LEFT</span> <span class="k">OUTER</span> <span class="k">JOIN</span> <span class="n">AgentBase_Extension</span> <span class="n">abMN</span> <span class="k">ON</span> <span class="n">abMN</span><span class="p">.</span><span class="n">extensions_id</span> <span class="o">=</span> <span class="n">e</span><span class="p">.</span><span class="n">id</span>
<span class="k">LEFT</span> <span class="k">OUTER</span> <span class="k">JOIN</span> <span class="n">AgentBase</span> <span class="n">ab</span> <span class="k">ON</span> <span class="n">ab</span><span class="p">.</span><span class="n">id</span> <span class="o">=</span> <span class="n">abMN</span><span class="p">.</span><span class="n">AgentBase_id</span>
<span class="k">LEFT</span> <span class="k">OUTER</span> <span class="k">JOIN</span> <span class="n">DefinedTermBase_Extension</span> <span class="n">dtbMN</span> <span class="k">ON</span> <span class="n">dtbMN</span><span class="p">.</span><span class="n">extensions_id</span> <span class="o">=</span> <span class="n">e</span><span class="p">.</span><span class="n">id</span>
<span class="k">LEFT</span> <span class="k">OUTER</span> <span class="k">JOIN</span> <span class="n">DefinedTermBase</span> <span class="n">dtb</span> <span class="k">ON</span> <span class="n">dtb</span><span class="p">.</span><span class="n">id</span> <span class="o">=</span> <span class="n">dtbMN</span><span class="p">.</span><span class="n">DefinedTermBase_id</span>
<span class="k">LEFT</span> <span class="k">OUTER</span> <span class="k">JOIN</span> <span class="n">Reference_Extension</span> <span class="n">rMN</span> <span class="k">ON</span> <span class="n">rMN</span><span class="p">.</span><span class="n">extensions_id</span> <span class="o">=</span> <span class="n">e</span><span class="p">.</span><span class="n">id</span>
<span class="k">LEFT</span> <span class="k">OUTER</span> <span class="k">JOIN</span> <span class="n">Reference</span> <span class="n">r</span> <span class="k">ON</span> <span class="n">r</span><span class="p">.</span><span class="n">id</span> <span class="o">=</span> <span class="n">rMN</span><span class="p">.</span><span class="n">Reference_id</span>
<span class="k">LEFT</span> <span class="k">OUTER</span> <span class="k">JOIN</span> <span class="n">TaxonBase_Extension</span> <span class="n">tbMN</span> <span class="k">ON</span> <span class="n">tbMN</span><span class="p">.</span><span class="n">extensions_id</span> <span class="o">=</span> <span class="n">e</span><span class="p">.</span><span class="n">id</span>
<span class="k">LEFT</span> <span class="k">OUTER</span> <span class="k">JOIN</span> <span class="n">TaxonBase</span> <span class="n">tb</span> <span class="k">ON</span> <span class="n">tb</span><span class="p">.</span><span class="n">id</span> <span class="o">=</span> <span class="n">tbMN</span><span class="p">.</span><span class="n">TaxonBase_id</span>
<span class="k">LEFT</span> <span class="k">OUTER</span> <span class="k">JOIN</span> <span class="n">TaxonName_Extension</span> <span class="n">nMN</span> <span class="k">ON</span> <span class="n">nMN</span><span class="p">.</span><span class="n">extensions_id</span> <span class="o">=</span> <span class="n">e</span><span class="p">.</span><span class="n">id</span>
<span class="k">LEFT</span> <span class="k">OUTER</span> <span class="k">JOIN</span> <span class="n">TaxonName</span> <span class="n">n</span> <span class="k">ON</span> <span class="n">n</span><span class="p">.</span><span class="n">id</span> <span class="o">=</span> <span class="n">nMN</span><span class="p">.</span><span class="n">TaxonName_id</span>
<span class="k">WHERE</span> <span class="n">et</span><span class="p">.</span><span class="n">titleCache</span> <span class="k">like</span> <span class="s1">'%Nomenclatural Standard%'</span>
<span class="k">ORDER</span> <span class="k">BY</span> <span class="n">e</span><span class="p">.</span><span class="n">value</span><span class="p">,</span> <span class="n">r</span><span class="p">.</span><span class="n">id</span>
</code></pre>
<p>Done:</p>
<ul>
<li>common name reference language deduplicated</li>
</ul>
<p>TODO:</p>
<ul>
<li>remove experts and species experts, check if same as sec ref now and adapt PESI import to use sec in case of E+M</li>
<li>Berlin Model IdInSource currently only for Synonyms, where do they come from, can they be moved to alternative identifiers, why do accepted taxa not have this id, is the semantics always the same, aren't there similar fields in other BM tables?</li>
<li>very few references have DateString extensions, this should be unified with similar information in Reference.notes, RefDetail notes, and other fields with similar information</li>
<li>Nomenclatural Standard can often be handled as BPH or TL/2 (alternative) identifier. Check how "-" should be handled (remove or does it mean no BPH or TL/2 entry exists)? what to do with BPH/S? Few exceptions exist.</li>
<li>Check what Source_Acc means for names (74.702x, 4x for taxa)</li>
<li>Handle IsoCode and TDWG code extension as term relationship, once this relationship exists</li>
</ul>
bug #7406 (Resolved): [CHECK] Check if all original source names of occurrences are correctly imp...
https://dev.e-taxonomy.eu/redmine/issues/7406
2018-05-09T14:14:20Z
Andreas Müller
<p>For Bellis perennis var. hybrida this is currently not the case (see distribution for Bellis perennis in Sicily (ref: Giardina, G., Raimondo, F. M. & Spadaro, V.: A catalogue of plants growing in Sicily Bocconea 20. 2008, 70220)</p>
<pre><code class="sql syntaxhl"><span class="k">SELECT</span> <span class="n">s</span><span class="p">.</span><span class="o">*</span><span class="p">,</span> <span class="n">n</span><span class="p">.</span><span class="o">*</span>
<span class="k">FROM</span> <span class="n">emOccurrenceSource</span> <span class="n">s</span>
<span class="k">INNER</span> <span class="k">JOIN</span> <span class="n">Name</span> <span class="n">n</span> <span class="k">ON</span> <span class="n">s</span><span class="p">.</span><span class="n">OldNameFk</span> <span class="o">=</span> <span class="n">n</span><span class="p">.</span><span class="n">NameID</span>
<span class="k">WHERE</span> <span class="n">s</span><span class="p">.</span><span class="n">OldName</span> <span class="o">=</span> <span class="s1">'Bellis perennis var. hybrida'</span> <span class="k">AND</span> <span class="n">SourceNumber</span> <span class="o">=</span> <span class="s1">'70220'</span> <span class="k">OR</span> <span class="n">s</span><span class="p">.</span><span class="n">OldNameFk</span> <span class="o">=</span> <span class="mi">117931</span>
</code></pre>
task #7319 (Resolved): [CHECK] Import synonyms correctly in E+M
https://dev.e-taxonomy.eu/redmine/issues/7319
2018-03-19T18:24:56Z
Andreas Müller
<p>Since CDM 4.1 synonyms may belong only to exactly 1 taxon (#5803, <a class="issue tracker-5 status-5 priority-11 priority-default closed parent" title="feature request: Remove synonym relationships (Closed)" href="https://dev.e-taxonomy.eu/redmine/issues/5974">#5974</a>). Therefore pro parte/partial synonyms should be handled as different concept with pro parte synonym concept relationships instead.</p>
<p>In E+M there are multiple synonyms having more than 1 accepted taxon. Those being pro parte/partial synonyms should be handled as described above and be converted to accepted taxa, while ordinary synonyms with >1 accepted taxa should be corrected in data.</p>
<p>Possible candidates can be found via</p>
<pre><code class="sql syntaxhl"><span class="k">SELECT</span> <span class="n">ta</span><span class="p">.</span><span class="n">PTNameFk</span> <span class="k">AS</span> <span class="n">synNameID</span><span class="p">,</span> <span class="n">ta</span><span class="p">.</span><span class="n">PTRefFk</span><span class="p">,</span> <span class="n">syn</span><span class="p">.</span><span class="n">StatusFk</span><span class="p">,</span> <span class="n">st</span><span class="p">.</span><span class="n">Status</span> <span class="n">synStatus</span><span class="p">,</span> <span class="n">synName</span><span class="p">.</span><span class="n">FullNameCache</span><span class="p">,</span>
<span class="n">rel1</span><span class="p">.</span><span class="n">RelQualifierFk</span> <span class="n">relTyp1</span><span class="p">,</span> <span class="n">relStatus1</span><span class="p">.</span><span class="n">RelPTQualifier</span> <span class="n">relTyp1_</span><span class="p">,</span> <span class="n">acc1</span><span class="p">.</span><span class="n">StatusFk</span> <span class="n">st1</span><span class="p">,</span> <span class="n">acc1</span><span class="p">.</span><span class="n">RIdentifier</span> <span class="n">taxon1</span><span class="p">,</span> <span class="n">accName1</span><span class="p">.</span><span class="n">NameId</span> <span class="n">name1</span><span class="p">,</span> <span class="n">acc1</span><span class="p">.</span><span class="n">PTRefFk</span> <span class="n">ref1</span><span class="p">,</span> <span class="n">accName1</span><span class="p">.</span><span class="n">FullNameCache</span><span class="p">,</span>
<span class="n">rel2</span><span class="p">.</span><span class="n">RelQualifierFk</span> <span class="n">relTyp2</span><span class="p">,</span> <span class="n">relStatus2</span><span class="p">.</span><span class="n">RelPTQualifier</span> <span class="n">relTyp2_</span><span class="p">,</span> <span class="n">acc2</span><span class="p">.</span><span class="n">StatusFk</span> <span class="n">st2</span><span class="p">,</span> <span class="n">acc2</span><span class="p">.</span><span class="n">RIdentifier</span> <span class="n">taxon2</span><span class="p">,</span> <span class="n">accName2</span><span class="p">.</span><span class="n">NameId</span> <span class="n">name2</span><span class="p">,</span> <span class="n">acc2</span><span class="p">.</span><span class="n">PTRefFk</span> <span class="n">ref2</span><span class="p">,</span> <span class="n">accName2</span><span class="p">.</span><span class="n">FullNameCache</span>
<span class="k">FROM</span> <span class="n">dbo</span><span class="p">.</span><span class="n">v_cdm_exp_taxaAll</span> <span class="n">ta</span> <span class="k">LEFT</span> <span class="k">OUTER</span> <span class="k">JOIN</span> <span class="n">PTaxon</span> <span class="n">syn</span> <span class="k">ON</span> <span class="n">syn</span><span class="p">.</span><span class="n">RIdentifier</span> <span class="o">=</span> <span class="n">ta</span><span class="p">.</span><span class="n">RIdentifier</span>
<span class="k">LEFT</span> <span class="k">OUTER</span> <span class="k">JOIN</span> <span class="n">Status</span> <span class="n">st</span> <span class="k">ON</span> <span class="n">st</span><span class="p">.</span><span class="n">StatusId</span> <span class="o">=</span> <span class="n">syn</span><span class="p">.</span><span class="n">StatusFk</span>
<span class="k">LEFT</span> <span class="k">OUTER</span> <span class="k">JOIN</span> <span class="n">Name</span> <span class="n">synName</span> <span class="k">ON</span> <span class="n">syn</span><span class="p">.</span><span class="n">PTNameFk</span> <span class="o">=</span> <span class="n">synName</span><span class="p">.</span><span class="n">NameId</span>
<span class="k">LEFT</span> <span class="k">OUTER</span> <span class="k">JOIN</span> <span class="n">RelPTaxon</span> <span class="n">rel1</span> <span class="k">ON</span> <span class="n">rel1</span><span class="p">.</span><span class="n">PTNameFk1</span> <span class="o">=</span> <span class="n">syn</span><span class="p">.</span><span class="n">PTNameFk</span> <span class="k">AND</span> <span class="n">rel1</span><span class="p">.</span><span class="n">PTRefFk1</span> <span class="o">=</span> <span class="n">syn</span><span class="p">.</span><span class="n">PTRefFk</span>
<span class="k">LEFT</span> <span class="k">OUTER</span> <span class="k">JOIN</span> <span class="n">PTaxon</span> <span class="n">acc1</span> <span class="k">ON</span> <span class="n">rel1</span><span class="p">.</span><span class="n">PTNameFk2</span> <span class="o">=</span> <span class="n">acc1</span><span class="p">.</span><span class="n">PTNameFk</span> <span class="k">AND</span> <span class="n">rel1</span><span class="p">.</span><span class="n">PTRefFk2</span> <span class="o">=</span> <span class="n">acc1</span><span class="p">.</span><span class="n">PTRefFk</span>
<span class="k">LEFT</span> <span class="k">OUTER</span> <span class="k">JOIN</span> <span class="n">RelPTQualifier</span> <span class="n">relStatus1</span> <span class="k">ON</span> <span class="n">rel1</span><span class="p">.</span><span class="n">RelQualifierFk</span> <span class="o">=</span> <span class="n">relStatus1</span><span class="p">.</span><span class="n">RelPTQualifierId</span>
<span class="k">LEFT</span> <span class="k">OUTER</span> <span class="k">JOIN</span> <span class="n">Name</span> <span class="n">accName1</span> <span class="k">ON</span> <span class="n">accName1</span><span class="p">.</span><span class="n">NameId</span> <span class="o">=</span> <span class="n">acc1</span><span class="p">.</span><span class="n">PTNameFk</span>
<span class="k">LEFT</span> <span class="k">OUTER</span> <span class="k">JOIN</span> <span class="n">RelPTaxon</span> <span class="n">rel2</span> <span class="k">ON</span> <span class="n">rel2</span><span class="p">.</span><span class="n">PTNameFk1</span> <span class="o">=</span> <span class="n">syn</span><span class="p">.</span><span class="n">PTNameFk</span> <span class="k">AND</span> <span class="n">rel2</span><span class="p">.</span><span class="n">PTRefFk1</span> <span class="o">=</span> <span class="n">syn</span><span class="p">.</span><span class="n">PTRefFk</span>
<span class="k">LEFT</span> <span class="k">OUTER</span> <span class="k">JOIN</span> <span class="n">PTaxon</span> <span class="n">acc2</span> <span class="k">ON</span> <span class="n">rel2</span><span class="p">.</span><span class="n">PTNameFk2</span> <span class="o">=</span> <span class="n">acc2</span><span class="p">.</span><span class="n">PTNameFk</span> <span class="k">AND</span> <span class="n">rel2</span><span class="p">.</span><span class="n">PTRefFk2</span> <span class="o">=</span> <span class="n">acc2</span><span class="p">.</span><span class="n">PTRefFk</span>
<span class="k">LEFT</span> <span class="k">OUTER</span> <span class="k">JOIN</span> <span class="n">RelPTQualifier</span> <span class="n">relStatus2</span> <span class="k">ON</span> <span class="n">rel2</span><span class="p">.</span><span class="n">RelQualifierFk</span> <span class="o">=</span> <span class="n">relStatus2</span><span class="p">.</span><span class="n">RelPTQualifierId</span>
<span class="k">LEFT</span> <span class="k">OUTER</span> <span class="k">JOIN</span> <span class="n">Name</span> <span class="n">accName2</span> <span class="k">ON</span> <span class="n">accName2</span><span class="p">.</span><span class="n">NameId</span> <span class="o">=</span> <span class="n">acc2</span><span class="p">.</span><span class="n">PTNameFk</span>
<span class="k">WHERE</span> <span class="n">syn</span><span class="p">.</span><span class="n">StatusFk</span> <span class="k">NOT</span> <span class="k">IN</span> <span class="p">(</span> <span class="mi">1</span><span class="p">)</span> <span class="k">AND</span> <span class="n">acc1</span><span class="p">.</span><span class="n">RIdentifier</span> <span class="o"><</span> <span class="n">acc2</span><span class="p">.</span><span class="n">RIdentifier</span> <span class="k">AND</span> <span class="n">rel1</span><span class="p">.</span><span class="n">RelQualifierFk</span> <span class="o"><></span> <span class="o">-</span><span class="mi">99</span> <span class="k">AND</span> <span class="n">rel2</span><span class="p">.</span><span class="n">RelQualifierFk</span> <span class="o"><></span> <span class="o">-</span><span class="mi">99</span>
<span class="k">ORDER</span> <span class="k">BY</span> <span class="n">syn</span><span class="p">.</span><span class="n">StatusFk</span><span class="p">,</span> <span class="n">relTyp1</span><span class="p">,</span> <span class="n">st1</span><span class="p">,</span> <span class="n">relTyp2</span><span class="p">,</span> <span class="n">st2</span><span class="p">,</span> <span class="n">syn</span><span class="p">.</span><span class="n">PTRefFk</span><span class="p">,</span> <span class="n">synNameID</span>
</code></pre>
<p>with </p>
<pre><code class="sql syntaxhl"> <span class="k">AND</span> <span class="k">NOT</span> <span class="p">(</span><span class="n">acc1</span><span class="p">.</span><span class="n">StatusFk</span> <span class="o"><></span> <span class="mi">1</span> <span class="k">OR</span> <span class="n">acc2</span><span class="p">.</span><span class="n">StatusFk</span> <span class="o"><></span> <span class="mi">1</span><span class="p">)</span> <span class="c1">-- rel to accepted only</span>
<span class="k">AND</span> <span class="k">NOT</span> <span class="p">(</span><span class="n">syn</span><span class="p">.</span><span class="n">StatusFk</span> <span class="o">=</span> <span class="mi">4</span> <span class="k">AND</span> <span class="n">rel1</span><span class="p">.</span><span class="n">RelQualifierFk</span> <span class="o">=</span> <span class="mi">4</span> <span class="k">AND</span> <span class="n">rel2</span><span class="p">.</span><span class="n">RelQualifierFk</span> <span class="o">=</span> <span class="mi">4</span><span class="p">)</span> <span class="c1">-- no pure pro parte synonyms</span>
</code></pre>
<p>inconsistent pro parte:</p>
<pre><code class="sql syntaxhl"><span class="k">AND</span>
<span class="p">(</span>
<span class="p">(</span><span class="n">syn</span><span class="p">.</span><span class="n">StatusFk</span> <span class="k">NOT</span> <span class="k">IN</span> <span class="p">(</span><span class="mi">3</span><span class="p">,</span><span class="mi">4</span><span class="p">)</span> <span class="k">AND</span> <span class="p">(</span> <span class="n">rel1</span><span class="p">.</span><span class="n">RelQualifierFk</span> <span class="k">NOT</span> <span class="k">IN</span> <span class="p">(</span><span class="mi">2</span><span class="p">,</span><span class="mi">6</span><span class="p">,</span><span class="mi">7</span><span class="p">)</span> <span class="k">OR</span> <span class="n">rel2</span><span class="p">.</span><span class="n">RelQualifierFk</span> <span class="k">NOT</span> <span class="k">IN</span> <span class="p">(</span><span class="mi">2</span><span class="p">,</span><span class="mi">6</span><span class="p">,</span><span class="mi">7</span><span class="p">)))</span>
<span class="k">OR</span>
<span class="p">(</span><span class="n">syn</span><span class="p">.</span><span class="n">StatusFk</span> <span class="k">IN</span> <span class="p">(</span><span class="mi">3</span><span class="p">,</span><span class="mi">4</span><span class="p">)</span> <span class="k">AND</span> <span class="p">(</span> <span class="n">rel1</span><span class="p">.</span><span class="n">RelQualifierFk</span> <span class="k">IN</span> <span class="p">(</span><span class="mi">2</span><span class="p">,</span><span class="mi">6</span><span class="p">,</span><span class="mi">7</span><span class="p">)</span> <span class="k">OR</span> <span class="n">rel2</span><span class="p">.</span><span class="n">RelQualifierFk</span> <span class="k">IN</span> <span class="p">(</span><span class="mi">2</span><span class="p">,</span><span class="mi">6</span><span class="p">,</span><span class="mi">7</span><span class="p">)))</span>
<span class="p">)</span>
</code></pre>
bug #6938 (Resolved): Tasks before a new Euro+Med import
https://dev.e-taxonomy.eu/redmine/issues/6938
2017-09-08T16:13:16Z
Andreas Müller
<p>Not yet complete </p>
<p>see also #5082</p>
<p>====</p>
<p>check that the import is adapted such that only referenced authors and teams are imported</p>
<p>===</p>
<p>check that import is run with a maven compiled code (aspectJ switched on)</p>
<p>===</p>
<p>run [s_em_MoveCommonNames] to move common names to structured common names</p>
<p>===</p>
<p>remove duplicate parent relationships (see also <a href="http://wiki.bgbm.org/bdinotes/index.php/EuroMed#Data_publication">http://wiki.bgbm.org/bdinotes/index.php/EuroMed#Data_publication</a>)</p>
<pre><code class="sql syntaxhl"><span class="k">SELECT</span> <span class="o">*</span>
<span class="k">FROM</span> <span class="p">(</span>
<span class="k">SELECT</span> <span class="k">COUNT</span><span class="p">(</span><span class="n">pt2</span><span class="p">.</span><span class="n">RIdentifier</span><span class="p">)</span> <span class="k">AS</span> <span class="n">HowMany</span><span class="p">,</span> <span class="n">pt</span><span class="p">.</span><span class="n">PTNameFk</span><span class="p">,</span> <span class="n">pt</span><span class="p">.</span><span class="n">PTRefFk</span> <span class="p">,</span> <span class="n">n</span><span class="p">.</span><span class="n">FullNameCache</span>
<span class="k">FROM</span> <span class="n">PTaxon</span> <span class="n">pt</span>
<span class="k">INNER</span> <span class="k">JOIN</span> <span class="n">RelPTaxon</span> <span class="n">rel</span> <span class="k">ON</span> <span class="n">pt</span><span class="p">.</span><span class="n">PTNameFk</span> <span class="o">=</span> <span class="n">rel</span><span class="p">.</span><span class="n">PTNameFk1</span> <span class="k">AND</span> <span class="n">pt</span><span class="p">.</span><span class="n">PTRefFk</span> <span class="o">=</span> <span class="n">rel</span><span class="p">.</span><span class="n">PTRefFk1</span>
<span class="k">INNER</span> <span class="k">JOIN</span> <span class="n">PTaxon</span> <span class="n">pt2</span> <span class="k">ON</span> <span class="n">rel</span><span class="p">.</span><span class="n">PTNameFk2</span> <span class="o">=</span> <span class="n">pt2</span><span class="p">.</span><span class="n">PTNameFk</span> <span class="k">AND</span> <span class="n">rel</span><span class="p">.</span><span class="n">PTRefFk2</span> <span class="o">=</span> <span class="n">pt2</span><span class="p">.</span><span class="n">PTRefFk</span>
<span class="k">INNER</span> <span class="k">JOIN</span> <span class="n">Name</span> <span class="n">n</span> <span class="k">ON</span> <span class="n">pt</span><span class="p">.</span><span class="n">PTNameFk</span> <span class="o">=</span> <span class="n">n</span><span class="p">.</span><span class="n">NameID</span>
<span class="k">WHERE</span> <span class="p">(</span><span class="n">rel</span><span class="p">.</span><span class="n">RelQualifierFk</span> <span class="o">=</span> <span class="mi">1</span><span class="p">)</span> <span class="k">AND</span> <span class="p">(</span><span class="n">pt</span><span class="p">.</span><span class="n">PTRefFk</span> <span class="o">%</span> <span class="mi">100000</span> <span class="o">=</span> <span class="mi">0</span><span class="p">)</span> <span class="k">AND</span> <span class="p">(</span><span class="n">pt</span><span class="p">.</span><span class="n">PTRefFk</span> <span class="o">>=</span> <span class="mi">7000000</span><span class="p">)</span>
<span class="k">GROUP</span> <span class="k">BY</span> <span class="n">pt</span><span class="p">.</span><span class="n">RIdentifier</span><span class="p">,</span> <span class="n">pt</span><span class="p">.</span><span class="n">PTNameFk</span><span class="p">,</span> <span class="n">pt</span><span class="p">.</span><span class="n">PTRefFk</span><span class="p">,</span> <span class="n">n</span><span class="p">.</span><span class="n">FullNameCache</span><span class="p">)</span> <span class="k">AS</span> <span class="n">DuplicateParentRelation</span>
<span class="k">WHERE</span> <span class="n">HowMany</span> <span class="o">></span> <span class="mi">1</span>
<span class="k">ORDER</span> <span class="k">BY</span> <span class="n">HowMany</span> <span class="k">DESC</span><span class="p">;</span>
</code></pre><pre><code class="sql syntaxhl"><span class="k">SELECT</span> <span class="n">n2</span><span class="p">.</span><span class="n">fullNameCache</span><span class="p">,</span> <span class="n">rel</span><span class="p">.</span><span class="o">*</span>
<span class="k">FROM</span> <span class="n">PTaxon</span> <span class="n">pt</span>
<span class="k">INNER</span> <span class="k">JOIN</span> <span class="n">RelPTaxon</span> <span class="n">rel</span> <span class="k">ON</span> <span class="n">pt</span><span class="p">.</span><span class="n">PTNameFk</span> <span class="o">=</span> <span class="n">rel</span><span class="p">.</span><span class="n">PTNameFk1</span> <span class="k">AND</span> <span class="n">pt</span><span class="p">.</span><span class="n">PTRefFk</span> <span class="o">=</span> <span class="n">rel</span><span class="p">.</span><span class="n">PTRefFk1</span>
<span class="k">INNER</span> <span class="k">JOIN</span> <span class="n">Name</span> <span class="n">n2</span> <span class="k">ON</span> <span class="n">rel</span><span class="p">.</span><span class="n">PTNameFk2</span> <span class="o">=</span> <span class="n">n2</span><span class="p">.</span><span class="n">NameId</span>
<span class="k">WHERE</span> <span class="n">rel</span><span class="p">.</span><span class="n">PTNameFk1</span> <span class="o">=</span> <span class="n">xxx</span> <span class="k">AND</span> <span class="n">rel</span><span class="p">.</span><span class="n">PTRefFk1</span> <span class="o">=</span> <span class="mi">7</span><span class="n">xxx</span>
</code></pre>
<p>====</p>
<p>remove selfreferencing relations</p>
<pre><code class="sql syntaxhl"><span class="k">DELETE</span> <span class="n">RelName</span> <span class="k">WHERE</span> <span class="n">NameFk1</span> <span class="o">=</span> <span class="n">NameFk2</span> <span class="k">AND</span> <span class="n">RelNameQualifierFk</span> <span class="o"><></span> <span class="mi">62</span><span class="p">;</span>
<span class="k">DELETE</span> <span class="n">RelPTaxon</span> <span class="k">WHERE</span> <span class="n">PTNameFk1</span> <span class="o">=</span> <span class="n">PTNameFk2</span> <span class="k">and</span> <span class="n">PTRefFk1</span> <span class="o">=</span> <span class="n">PTRefFk2</span>
</code></pre>
<p>====</p>
<p>Taxa without parents (still needs improvements with publish flag and other issues like valueless taxa)</p>
<pre><code class="sql syntaxhl"><span class="k">SELECT</span> <span class="n">n</span><span class="p">.</span><span class="n">fullNameCache</span><span class="p">,</span> <span class="n">pt</span><span class="p">.</span><span class="o">*</span>
<span class="k">FROM</span> <span class="n">PTaxon</span> <span class="n">pt</span>
<span class="k">INNER</span> <span class="k">JOIN</span> <span class="n">Name</span> <span class="n">n</span> <span class="k">ON</span> <span class="n">pt</span><span class="p">.</span><span class="n">PTNameFk</span> <span class="o">=</span> <span class="n">n</span><span class="p">.</span><span class="n">NameId</span>
<span class="k">WHERE</span> <span class="n">pt</span><span class="p">.</span><span class="n">RIdentifier</span> <span class="k">NOT</span> <span class="k">IN</span>
<span class="p">(</span><span class="k">SELECT</span> <span class="n">p</span><span class="p">.</span><span class="n">RIdentifier</span>
<span class="k">FROM</span> <span class="n">PTaxon</span> <span class="n">p</span>
<span class="k">INNER</span> <span class="k">JOIN</span> <span class="n">RelPTaxon</span> <span class="n">rel</span> <span class="k">ON</span> <span class="n">p</span><span class="p">.</span><span class="n">PTNameFk</span> <span class="o">=</span> <span class="n">rel</span><span class="p">.</span><span class="n">PTNameFk1</span> <span class="k">AND</span> <span class="n">p</span><span class="p">.</span><span class="n">PTRefFk</span> <span class="o">=</span> <span class="n">rel</span><span class="p">.</span><span class="n">PTRefFk1</span>
<span class="k">WHERE</span> <span class="p">(</span><span class="n">rel</span><span class="p">.</span><span class="n">RelQualifierFk</span> <span class="o">=</span> <span class="mi">1</span><span class="p">)</span>
<span class="p">)</span>
<span class="k">AND</span> <span class="p">(</span><span class="n">pt</span><span class="p">.</span><span class="n">PTRefFk</span> <span class="o">%</span> <span class="mi">100000</span> <span class="o">=</span> <span class="mi">0</span><span class="p">)</span> <span class="k">AND</span> <span class="p">(</span><span class="n">pt</span><span class="p">.</span><span class="n">PTRefFk</span> <span class="o">>=</span> <span class="mi">7000000</span><span class="p">)</span> <span class="k">AND</span> <span class="n">pt</span><span class="p">.</span><span class="n">StatusFk</span> <span class="k">NOT</span> <span class="k">IN</span> <span class="p">(</span><span class="mi">2</span><span class="p">,</span><span class="mi">6</span><span class="p">,</span> <span class="mi">3</span><span class="p">,</span><span class="mi">4</span><span class="p">)</span>
</code></pre>
<p>====</p>
<p>Run Queries in Y:\BDI\PESI\sh\EuroPlusMed\Credits_Publish\Update_before_publication.sql for data integrity</p>
<p>====</p>
<p>Run Commands in Y:\BDI\PESI\sh\EuroPlusMed\Credits_Publish\SetPublishAndCredits.sql to set the publish flag correctly and to set last scrutiny</p>
<p>===</p>
<p>The export starting point xxx requires that the Treeindex is set correctly in BM.</p>
<p>For this run </p>
<pre><code class="sql syntaxhl"><span class="k">UPDATE</span> <span class="n">PTaxon</span> <span class="k">SET</span> <span class="n">TreeIndex</span><span class="o">=</span><span class="k">NULL</span><span class="p">;</span>
<span class="k">UPDATE</span> <span class="n">PTaxon</span> <span class="k">SET</span> <span class="n">TreeIndex</span><span class="o">=</span><span class="n">dbo</span><span class="p">.</span><span class="n">f_core_getTreeIndex</span><span class="p">(</span><span class="n">RIdentifier</span><span class="p">)</span> <span class="k">WHERE</span> <span class="p">(</span><span class="n">StatusFk</span> <span class="k">IN</span> <span class="p">(</span><span class="mi">1</span><span class="p">,</span><span class="mi">5</span><span class="p">))</span>
</code></pre>
<p>This differs a bit from <a href="http://wiki.bgbm.org/bdinotes/index.php/EuroMed#Data_publication">http://wiki.bgbm.org/bdinotes/index.php/EuroMed#Data_publication</a> as there unpublished taxa do not need a treeindex.</p>
<p>We may also run </p>
<pre><code class="sql syntaxhl"><span class="k">UPDATE</span> <span class="n">PTaxon</span> <span class="k">SET</span> <span class="n">IndexNameString</span><span class="o">=</span><span class="n">dbo</span><span class="p">.</span><span class="n">f_core_TreeIndex2NameString</span><span class="p">(</span><span class="n">TreeIndex</span><span class="p">)</span>
</code></pre>
<p>but not really necessary for the export</p>
<p>===</p>
<p>check for corrupt hierarchies:</p>
<pre><code class="sql syntaxhl"><span class="k">SELECT</span> <span class="n">n</span><span class="p">.</span><span class="n">fullNameCache</span><span class="p">,</span> <span class="n">n</span><span class="p">.</span><span class="n">NameCache</span><span class="p">,</span> <span class="n">pt</span><span class="p">.</span><span class="o">*</span>
<span class="k">FROM</span> <span class="n">PTaxon</span> <span class="n">pt</span> <span class="k">INNER</span> <span class="k">JOIN</span> <span class="n">Name</span> <span class="n">n</span> <span class="k">ON</span> <span class="n">n</span><span class="p">.</span><span class="n">NameId</span> <span class="o">=</span> <span class="n">pt</span><span class="p">.</span><span class="n">PTNameFk</span>
<span class="k">WHERE</span> <span class="n">pt</span><span class="p">.</span><span class="n">TreeIndex</span> <span class="k">NOT</span> <span class="k">LIKE</span> <span class="s1">'533921-%'</span> <span class="k">AND</span> <span class="n">pt</span><span class="p">.</span><span class="n">TreeIndex</span> <span class="k">NOT</span> <span class="k">LIKE</span> <span class="s1">'556619-%'</span> <span class="c1">-- AND pt.StatusFk NOT IN (2,3,4,6)</span>
<span class="k">AND</span> <span class="n">pt</span><span class="p">.</span><span class="n">PTRefFk</span> <span class="o">>=</span> <span class="mi">7000000</span>
<span class="k">AND</span> <span class="p">(</span><span class="n">PublishFlag</span> <span class="o">=</span> <span class="mi">1</span> <span class="k">OR</span> <span class="n">pt</span><span class="p">.</span><span class="n">PTRefFk</span> <span class="o"><></span> <span class="mi">8000000</span><span class="p">)</span>
<span class="k">AND</span> <span class="p">(</span><span class="n">pt</span><span class="p">.</span><span class="n">PTRefFk</span> <span class="o">%</span> <span class="mi">100000</span> <span class="o">=</span> <span class="mi">0</span><span class="p">)</span>
<span class="k">AND</span> <span class="n">DoubtfulFlag</span> <span class="o"><></span> <span class="s1">'i'</span>
<span class="c1">-- AND IsExcludedMarker = 0</span>
<span class="k">ORDER</span> <span class="k">BY</span> <span class="n">PTRefFk</span><span class="p">,</span><span class="n">Treeindex</span><span class="p">,</span> <span class="n">StatusFK</span>
<span class="p">;</span>
<span class="k">SELECT</span> <span class="n">n</span><span class="p">.</span><span class="n">fullNameCache</span><span class="p">,</span> <span class="n">n</span><span class="p">.</span><span class="n">NameCache</span><span class="p">,</span> <span class="n">pt</span><span class="p">.</span><span class="o">*</span>
<span class="k">FROM</span> <span class="n">PTaxon</span> <span class="n">pt</span> <span class="k">INNER</span> <span class="k">JOIN</span> <span class="n">Name</span> <span class="n">n</span> <span class="k">ON</span> <span class="n">n</span><span class="p">.</span><span class="n">NameId</span> <span class="o">=</span> <span class="n">pt</span><span class="p">.</span><span class="n">PTNameFk</span>
<span class="k">WHERE</span> <span class="n">ptRefFk</span> <span class="o"><></span> <span class="mi">500000</span> <span class="k">AND</span> <span class="p">(</span><span class="n">pt</span><span class="p">.</span><span class="n">PTNameFk</span> <span class="k">IN</span> <span class="p">(</span>
<span class="k">SELECT</span> <span class="n">pt</span><span class="p">.</span><span class="n">PTNameFk</span>
<span class="k">FROM</span> <span class="n">PTaxon</span> <span class="n">pt</span>
<span class="k">WHERE</span> <span class="n">pt</span><span class="p">.</span><span class="n">TreeIndex</span> <span class="k">NOT</span> <span class="k">LIKE</span> <span class="s1">'533921-%'</span> <span class="k">AND</span> <span class="n">pt</span><span class="p">.</span><span class="n">TreeIndex</span> <span class="k">NOT</span> <span class="k">LIKE</span> <span class="s1">'556619-%'</span> <span class="c1">-- AND pt.StatusFk NOT IN (2,3,4,6)</span>
<span class="k">AND</span> <span class="n">pt</span><span class="p">.</span><span class="n">PTRefFk</span> <span class="o">>=</span> <span class="mi">7000000</span>
<span class="k">AND</span> <span class="p">(</span><span class="n">PublishFlag</span> <span class="o">=</span> <span class="mi">1</span> <span class="k">OR</span> <span class="n">pt</span><span class="p">.</span><span class="n">PTRefFk</span> <span class="o"><></span> <span class="mi">8000000</span><span class="p">)</span>
<span class="k">AND</span> <span class="p">(</span><span class="n">pt</span><span class="p">.</span><span class="n">PTRefFk</span> <span class="o">%</span> <span class="mi">100000</span> <span class="o">=</span> <span class="mi">0</span><span class="p">)</span>
<span class="k">AND</span> <span class="n">DoubtfulFlag</span> <span class="o"><></span> <span class="s1">'i'</span>
<span class="p">)</span> <span class="k">OR</span> <span class="n">n</span><span class="p">.</span><span class="n">nameCache</span> <span class="k">IN</span> <span class="p">(</span> <span class="k">SELECT</span> <span class="n">n</span><span class="p">.</span><span class="n">NameCache</span>
<span class="k">FROM</span> <span class="n">PTaxon</span> <span class="n">pt</span> <span class="k">INNER</span> <span class="k">JOIN</span> <span class="n">Name</span> <span class="n">n</span> <span class="k">ON</span> <span class="n">n</span><span class="p">.</span><span class="n">NameId</span> <span class="o">=</span> <span class="n">pt</span><span class="p">.</span><span class="n">PTNameFk</span>
<span class="k">WHERE</span> <span class="n">pt</span><span class="p">.</span><span class="n">TreeIndex</span> <span class="k">NOT</span> <span class="k">LIKE</span> <span class="s1">'533921-%'</span> <span class="k">AND</span> <span class="n">pt</span><span class="p">.</span><span class="n">TreeIndex</span> <span class="k">NOT</span> <span class="k">LIKE</span> <span class="s1">'556619-%'</span> <span class="c1">-- AND pt.StatusFk NOT IN (2,3,4,6)</span>
<span class="k">AND</span> <span class="n">pt</span><span class="p">.</span><span class="n">PTRefFk</span> <span class="o">>=</span> <span class="mi">7000000</span>
<span class="k">AND</span> <span class="p">(</span><span class="n">PublishFlag</span> <span class="o">=</span> <span class="mi">1</span> <span class="k">OR</span> <span class="n">pt</span><span class="p">.</span><span class="n">PTRefFk</span> <span class="o"><></span> <span class="mi">8000000</span><span class="p">)</span>
<span class="k">AND</span> <span class="p">(</span><span class="n">pt</span><span class="p">.</span><span class="n">PTRefFk</span> <span class="o">%</span> <span class="mi">100000</span> <span class="o">=</span> <span class="mi">0</span><span class="p">)</span>
<span class="k">AND</span> <span class="n">DoubtfulFlag</span> <span class="o"><></span> <span class="s1">'i'</span><span class="p">)</span>
<span class="p">)</span>
<span class="k">ORDER</span> <span class="k">BY</span> <span class="n">n</span><span class="p">.</span><span class="n">nameCache</span><span class="p">,</span> <span class="n">PTNameFK</span><span class="p">,</span> <span class="n">PTRefFk</span>
</code></pre>
<p>===</p>
<p>remove incative taxon relationships not needed anymore</p>
<pre><code class="sql syntaxhl"><span class="k">SELECT</span> <span class="n">RelPTaxonId</span><span class="p">,</span> <span class="n">PTNameFk1</span><span class="p">,</span> <span class="n">PTRefFk1</span><span class="p">,</span> <span class="n">PTNameFk2</span><span class="p">,</span> <span class="n">PTRefFk2</span><span class="p">,</span> <span class="n">RelQualifierFk</span><span class="p">,</span> <span class="n">RelRefFk</span><span class="p">,</span> <span class="n">Created_When</span><span class="p">,</span> <span class="n">Updated_When</span><span class="p">,</span> <span class="n">Created_Who</span><span class="p">,</span> <span class="n">Updated_Who</span><span class="p">,</span> <span class="n">Notes</span><span class="p">,</span> <span class="n">Provisional</span>
<span class="k">FROM</span> <span class="n">RelPTaxon</span>
<span class="k">WHERE</span> <span class="p">(</span><span class="n">RelPTaxonId</span> <span class="k">IN</span>
<span class="p">(</span><span class="k">SELECT</span> <span class="n">rel</span><span class="p">.</span><span class="n">RelPTaxonId</span>
<span class="k">FROM</span> <span class="n">RelPTaxon</span> <span class="k">AS</span> <span class="n">rel</span> <span class="k">LEFT</span> <span class="k">OUTER</span> <span class="k">JOIN</span>
<span class="n">PTaxon</span> <span class="k">AS</span> <span class="n">pt1</span> <span class="k">ON</span> <span class="n">rel</span><span class="p">.</span><span class="n">PTNameFk1</span> <span class="o">=</span> <span class="n">pt1</span><span class="p">.</span><span class="n">PTNameFk</span> <span class="k">AND</span> <span class="n">rel</span><span class="p">.</span><span class="n">PTRefFk1</span> <span class="o">=</span> <span class="n">pt1</span><span class="p">.</span><span class="n">PTRefFk</span> <span class="k">LEFT</span> <span class="k">OUTER</span> <span class="k">JOIN</span>
<span class="n">PTaxon</span> <span class="k">AS</span> <span class="n">pt2</span> <span class="k">ON</span> <span class="n">rel</span><span class="p">.</span><span class="n">PTNameFk2</span> <span class="o">=</span> <span class="n">pt2</span><span class="p">.</span><span class="n">PTNameFk</span> <span class="k">AND</span> <span class="n">rel</span><span class="p">.</span><span class="n">PTRefFk2</span> <span class="o">=</span> <span class="n">pt2</span><span class="p">.</span><span class="n">PTRefFk</span>
<span class="k">WHERE</span> <span class="p">(</span><span class="n">rel</span><span class="p">.</span><span class="n">RelQualifierFk</span> <span class="o">=</span> <span class="o">-</span> <span class="mi">99</span><span class="p">)</span> <span class="k">AND</span> <span class="p">(</span><span class="n">pt1</span><span class="p">.</span><span class="n">DoubtfulFlag</span> <span class="o"><></span> <span class="s1">'i'</span> <span class="k">OR</span>
<span class="n">pt1</span><span class="p">.</span><span class="n">DoubtfulFlag</span> <span class="k">IS</span> <span class="k">NULL</span><span class="p">)</span> <span class="k">AND</span> <span class="p">(</span><span class="n">pt2</span><span class="p">.</span><span class="n">DoubtfulFlag</span> <span class="o"><></span> <span class="s1">'i'</span> <span class="k">OR</span>
<span class="n">pt2</span><span class="p">.</span><span class="n">DoubtfulFlag</span> <span class="k">IS</span> <span class="k">NULL</span><span class="p">)))</span>
</code></pre>
<p>===</p>
bug #3992 (Resolved): [CHECK] Use last scrutiny instead of sec reference
https://dev.e-taxonomy.eu/redmine/issues/3992
2014-01-24T10:15:09Z
Andreas Müller
<p>In E+M the last scrutiny field may better represent the meaning of the secundum reference than the PTRefFK field does. We may have to adapt E+M imports this way.</p>
<p>First discuss with Eckhard.</p>
<p>See also <a class="issue tracker-5 status-5 priority-10 priority-lowest closed" title="feature request: [DISCUSS] How to implement citation suggestion to each taxon (Closed)" href="https://dev.e-taxonomy.eu/redmine/issues/3990">#3990</a></p>
bug #3804 (Resolved): E+M import Person titles contain Object.toSting() representations
https://dev.e-taxonomy.eu/redmine/issues/3804
2013-10-17T14:35:57Z
Andreas Kohlbecker
<p>for example the user dr (Dominik Röpert) is now named as <code>Person#0<2e86dcfe-25fb-417c-ba5d-6b5739b11df5></code> in the last import this was correct</p>