Project

General

Profile

bug #6938

Tasks before a new Euro+Med import

Added by Andreas Müller over 1 year ago. Updated about 20 hours ago.

Status:
In Progress
Priority:
Priority14
Category:
data
Target version:
Start date:
09/08/2017
Due date:
% Done:

50%

Estimated time:
2.00 h
Severity:
normal
Found in Version:
Tags:

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 *
FROM PTaxon pt
WHERE pt.TreeIndex NOT LIKE '533921-%' -- 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

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

Related to Edit - task #7341: [Check] CommonName import issues Resolved 04/03/2018

History

#1 Updated by Andreas Müller about 1 year ago

  • Tags set to euro+med

#2 Updated by Andreas Müller about 1 year ago

  • Related to task #7341: [Check] CommonName import issues added

#3 Updated by Andreas Müller about 1 year ago

  • Estimated time set to 2.00 h

#4 Updated by Andreas Müller 12 months ago

  • Description updated (diff)

#5 Updated by Andreas Müller 12 months ago

  • Description updated (diff)
  • Status changed from New to In Progress

#6 Updated by Andreas Müller 12 months ago

  • Description updated (diff)

#7 Updated by Andreas Müller 12 months ago

  • Description updated (diff)

#8 Updated by Andreas Müller 12 months ago

  • Description updated (diff)

#9 Updated by Andreas Müller 12 months ago

  • Description updated (diff)
  • Priority changed from New to Highest

#10 Updated by Andreas Müller 12 months ago

  • Description updated (diff)

#11 Updated by Andreas Müller 12 months ago

  • % Done changed from 0 to 20

#13 Updated by Andreas Müller 12 months ago

  • Description updated (diff)

#14 Updated by Andreas Müller 12 months ago

  • Description updated (diff)

#15 Updated by Andreas Müller 12 months ago

  • Description updated (diff)

#18 Updated by Andreas Müller 12 months ago

  • Description updated (diff)

#19 Updated by Andreas Müller 12 months ago

  • Description updated (diff)

#20 Updated by Andreas Müller 12 months ago

  • Description updated (diff)

#21 Updated by Andreas Müller 12 months ago

  • Description updated (diff)

#22 Updated by Andreas Müller 12 months ago

  • % Done changed from 20 to 50

#23 Updated by Andreas Müller 10 months ago

  • Description updated (diff)

#24 Updated by Andreas Müller 8 months ago

  • Target version changed from Euro+Med Portal Release to Euro+Med Migration

#25 Updated by Andreas Müller 8 months ago

  • Priority changed from Highest to Priority14

#26 Updated by Andreas Müller 8 months ago

  • Description updated (diff)

#28 Updated by Andreas Müller 7 months ago

  • Description updated (diff)

#29 Updated by Andreas Müller 6 days ago

  • Description updated (diff)

#30 Updated by Andreas Müller about 20 hours ago

  • Description updated (diff)

Also available in: Atom PDF

Add picture from clipboard (Maximum size: 40 MB)