Project

General

Profile

bug #6938

Updated by Andreas Müller almost 6 years ago

Not yet complete  

 * 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  
      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 

 == 

Back