bug #6938
openTasks before a new Euro+Med import
90%
Description
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)
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;
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
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)
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
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
UPDATE PTaxon SET IndexNameString=dbo.f_core_TreeIndex2NameString(TreeIndex)
but not really necessary for the export
===
check for corrupt hierarchies:
SELECT n.fullNameCache, n.NameCache, pt.*
FROM PTaxon pt INNER JOIN Name n ON n.NameId = pt.PTNameFk
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 OR pt.PTRefFk <> 8000000)
AND (pt.PTRefFk % 100000 = 0)
AND DoubtfulFlag <> 'i'
-- AND IsExcludedMarker = 0
ORDER BY PTRefFk,Treeindex, StatusFK
;
SELECT n.fullNameCache, n.NameCache, pt.*
FROM PTaxon pt INNER JOIN Name n ON n.NameId = pt.PTNameFk
WHERE ptRefFk <> 500000 AND (pt.PTNameFk IN (
SELECT pt.PTNameFk
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 OR pt.PTRefFk <> 8000000)
AND (pt.PTRefFk % 100000 = 0)
AND DoubtfulFlag <> 'i'
) OR n.nameCache IN ( SELECT n.NameCache
FROM PTaxon pt INNER JOIN Name n ON n.NameId = pt.PTNameFk
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 OR pt.PTRefFk <> 8000000)
AND (pt.PTRefFk % 100000 = 0)
AND DoubtfulFlag <> 'i')
)
ORDER BY n.nameCache, PTNameFK, PTRefFk
===
remove incative taxon relationships not needed anymore
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)))
===
Related issues
Updated by Andreas Müller about 6 years ago
- Related to task #7341: [Check] CommonName import issues added
Updated by Andreas Müller almost 6 years ago
- Description updated (diff)
- Status changed from New to In Progress
Updated by Andreas Müller almost 6 years ago
- Description updated (diff)
- Priority changed from New to Highest
Updated by Andreas Müller over 5 years ago
- Target version changed from Euro+Med Portal Release to Euro+Med Migration
Updated by Andreas Müller over 5 years ago
- Priority changed from Highest to Priority14
Updated by Andreas Müller almost 5 years ago
- Status changed from In Progress to Resolved
- % Done changed from 50 to 90
should be resolved, I did run all the steps (1 mail to ERS is still open, about corrupt hierarchies for Polygonum etc.)