bug #6938
Updated by Andreas Müller almost 5 years ago
Not yet complete see also #5082 ==== check that the import is adapted such that only referenced authors and teams are imported === check that import is run with a maven compiled code (aspectJ switched on) === run [s_em_MoveCommonNames] to move common names to structured common names === remove duplicate parent relationships (see also http://wiki.bgbm.org/bdinotes/index.php/EuroMed#Data_publication) ~~~ sql SELECT * FROM ( SELECT COUNT(pt2.RIdentifier) AS HowMany, pt.PTNameFk, pt.PTRefFk , n.FullNameCache FROM PTaxon pt INNER JOIN RelPTaxon rel ON pt.PTNameFk = rel.PTNameFk1 AND pt.PTRefFk = rel.PTRefFk1 INNER JOIN PTaxon pt2 ON rel.PTNameFk2 = pt2.PTNameFk AND rel.PTRefFk2 = pt2.PTRefFk INNER JOIN Name n ON pt.PTNameFk = n.NameID WHERE (rel.RelQualifierFk = 1) AND (pt.PTRefFk % 100000 = 0) AND (pt.PTRefFk >= 7000000) GROUP BY pt.RIdentifier, pt.PTNameFk, pt.PTRefFk, n.FullNameCache) AS DuplicateParentRelation WHERE HowMany > 1 ORDER BY HowMany DESC; ~~~ ~~~ sql SELECT n2.fullNameCache, rel.* FROM PTaxon pt INNER JOIN RelPTaxon rel ON pt.PTNameFk = rel.PTNameFk1 AND pt.PTRefFk = rel.PTRefFk1 INNER JOIN Name n2 ON rel.PTNameFk2 = n2.NameId WHERE rel.PTNameFk1 = xxx AND rel.PTRefFk1 = 7xxx ~~~ ==== remove selfreferencing relations ~~~ sql DELETE RelName WHERE NameFk1 = NameFk2 AND RelNameQualifierFk <> 62; DELETE RelPTaxon WHERE PTNameFk1 = PTNameFk2 and PTRefFk1 = PTRefFk2 ~~~ ==== Taxa without parents (still needs improvements with publish flag and other issues like valueless taxa) ~~~ sql SELECT n.fullNameCache, pt.* FROM PTaxon pt INNER JOIN Name n ON pt.PTNameFk = n.NameId WHERE pt.RIdentifier NOT IN (SELECT p.RIdentifier FROM PTaxon p INNER JOIN RelPTaxon rel ON p.PTNameFk = rel.PTNameFk1 AND p.PTRefFk = rel.PTRefFk1 WHERE (rel.RelQualifierFk = 1) ) AND (pt.PTRefFk % 100000 = 0) AND (pt.PTRefFk >= 7000000) AND pt.StatusFk NOT IN (2,6, 3,4) ~~~ ==== Run Queries in Y:\BDI\PESI\sh\EuroPlusMed\Credits_Publish\Update_before_publication.sql for data integrity ==== Run Commands in Y:\BDI\PESI\sh\EuroPlusMed\Credits_Publish\SetPublishAndCredits.sql to set the publish flag correctly and to set last scrutiny === The export starting point xxx requires that the Treeindex is set correctly in BM. For this run ~~~ sql UPDATE PTaxon SET TreeIndex=NULL; UPDATE PTaxon SET TreeIndex=dbo.f_core_getTreeIndex(RIdentifier) WHERE (StatusFk IN (1,5)) ~~~ This differs a bit from http://wiki.bgbm.org/bdinotes/index.php/EuroMed#Data_publication as there unpublished taxa do not need a treeindex. We may also run ~~~ sql UPDATE PTaxon SET IndexNameString=dbo.f_core_TreeIndex2NameString(TreeIndex) ~~~ but not really necessary for the export === check for corrupt hierarchies: ~~~ sql SELECT * FROM PTaxon pt WHERE pt.TreeIndex NOT LIKE '533921-%' AND pt.TreeIndex NOT LIKE '556619-%' -- AND pt.StatusFk NOT IN (2,3,4,6) AND pt.PTRefFk >= 7000000 AND PublishFlag = 1 AND IsExcludedMarker = 0 ORDER BY Treeindex, PTRefFk, StatusFK ~~~ === remove incative taxon relationships not needed anymore ~~~ sql SELECT RelPTaxonId, PTNameFk1, PTRefFk1, PTNameFk2, PTRefFk2, RelQualifierFk, RelRefFk, Created_When, Updated_When, Created_Who, Updated_Who, Notes, Provisional FROM RelPTaxon WHERE (RelPTaxonId IN (SELECT rel.RelPTaxonId FROM RelPTaxon AS rel LEFT OUTER JOIN PTaxon AS pt1 ON rel.PTNameFk1 = pt1.PTNameFk AND rel.PTRefFk1 = pt1.PTRefFk LEFT OUTER JOIN PTaxon AS pt2 ON rel.PTNameFk2 = pt2.PTNameFk AND rel.PTRefFk2 = pt2.PTRefFk WHERE (rel.RelQualifierFk = - 99) AND (pt1.DoubtfulFlag <> 'i' OR pt1.DoubtfulFlag IS NULL) AND (pt2.DoubtfulFlag <> 'i' OR pt2.DoubtfulFlag IS NULL))) ~~~ ===