Project

General

Profile

task #7341

Updated by Andreas Müller almost 6 years ago

Run script to create common names from facts: #6938 

 There are some issues with common name import for E+M: 

  * Common names are generally stored as facts with category 12 in BM, but also in the emCommonName extension 
  * Common names are imported from the extension as it is better atomized and also for historical reasons 

 This creates problems: 

  * emCommonName extension data are not moved to other taxa when a taxon is send to synonymy 
  * new common names created as facts are not automatically created but the according script needs to be run 
  * facts being deleted are not deleted as emCommonName extension 

 Other problems: 
  * TBC 


 ~~~ SQL 

				  SELECT    factTaxon.PTNameFk as factNameFk,    cn.PTNameFk commonNameFk,cn.NameInSourceFk nameInSource, 
				     cn.CommonName, f.fact, cn.RefFk AS commonNameRefId, cn.Status commonNameStatus, synName.FullNameCache synonymName, tax.PTRefFk synonymRefFk, synStatus.StatusAbbrev synonymStatus, 				
					 accName.FullNameCache acceptedName, acc.PTRefFk acceptedRefFk, factName.FullNameCache factName, factTaxon.PTRefFk factRefFk, factTaxonStatus.StatusAbbrev factTaxonStatus, f.FactId fact,    cn.CommonNameId, 				
					 rel.RelPTaxonId, rel.RelQualifierFk, acc.RIdentifier accTaxonId, factTaxon.RIdentifier factTaxonId, accName.NameId accNameId, factTaxon.PTNameFk as factNameId,    				
					 f.FactId,    cn.CommonNameId, cn.CommonName, tax.RIdentifier AS taxonId, cn.PTNameFk, cn.RefFk AS refId, cn.Status, cn.RegionFks, cn.MisNameRefFk,   				
	                 cn.NameInSourceFk, cn.Created_When, cn.Updated_When, cn.Created_Who, cn.Updated_Who, cn.Note AS Notes, languageCommonName.Language,   			
	                 languageCommonName.LanguageOriginal, languageCommonName.ISO639_1, languageCommonName.ISO639_2,     			
	                 emLangRef.RefFk AS languageRefRefFk, emLangRef.ReferenceShort, emLangRef.ReferenceLong,    			
	                 emLangRef.LanguageFk, languageReferenceLanguage.Language AS refLanguage, languageReferenceLanguage.ISO639_2 AS refLanguageIso639_2,   			
	                 misappliedTaxon.RIdentifier AS misappliedTaxonId     			
   
				  FROM     PTaxon AS misappliedTaxon RIGHT OUTER JOIN   
					               emLanguage AS languageReferenceLanguage RIGHT OUTER JOIN   
			                       emLanguageReference AS emLangRef ON languageReferenceLanguage.LanguageId = emLangRef.LanguageFk RIGHT OUTER JOIN   
			                       emCommonName AS cn INNER JOIN   
			                       PTaxon AS tax ON cn.PTNameFk = tax.PTNameFk AND cn.PTRefFk = tax.PTRefFk ON    
			                       emLangRef.ReferenceId = cn.LanguageRefFk LEFT OUTER JOIN   
			                      emLanguage AS languageCommonName ON cn.LanguageFk = languageCommonName.LanguageId ON misappliedTaxon.PTNameFk = cn.NameInSourceFk AND    
			                      misappliedTaxon.PTRefFk = cn.MisNameRefFk   

								 LEFT OUTER JOIN Fact f ON cn.CommonNameId = f.ExtensionFk 
								 LEFT OUTER JOIN PTaxon factTaxon ON factTaxon.PTNameFk = f.PTNameFk AND factTaxon.PTRefFk = f.PTRefFk 
								 LEFT OUTER JOIN Name factName ON factTaxon.PTNameFk = factName.NameId 
								 LEFT OUTER JOIN Status factTaxonStatus ON factTaxonStatus.StatusId = factTaxon.StatusFk 
								 LEFT OUTER JOIN RelPTaxon rel ON rel.PTNameFk1 = tax.PTNameFk AND rel.PTRefFk1 = tax.PTRefFk AND rel.RelQualifierFk IN (2,6,7) 
								 LEFT OUTER JOIN PTaxon acc ON rel.PTNameFk2 = acc.PTNameFk AND rel.PTRefFk2 = acc.PTRefFk  
								 LEFT OUTER JOIN Name accName ON accName.NameId = acc.PTNameFk 
								 LEFT OUTER JOIN Name synName ON synName.NameId = tax.PTNameFk 
						     	 LEFT OUTER JOIN Status synStatus ON synStatus.StatusId = tax.StatusFk 
				 WHERE commonNameId IN ( SELECT commonNameId FROM v_cdm_exp_commonNamesAll ) 
 AND ( 1=1 
 -- acc.RIdentifier <> factTaxon.RIdentifier  
    -- acc.RIdentifier IS NULL AND factTaxon.RIdentifier <>    tax.RIdentifier 
    --     AND synName.NameCache = factName.NameCache 
   AND factTaxon.StatusFk = 6 
  ) 
  ORDER BY factTaxon.PTNameFk, accTaxonId, RelPTaxonId, factId 
 ~~~ 




 Find commonNames or facts attached to orphaned taxa: 

 ~~~sql 
 SELECT n.fullNameCache, pt.PTNameFk, pt.PTRefFk, count(*) as n    -- , f.* , pt.* 
 FROM Fact/emCommonName f INNER JOIN PTaxon pt ON pt.PTNameFk = f.PTNameFk AND pt.PTRefFk = f.PTRefFk 
 INNER JOIN Name n ON n.nameID =    pt.PTNameFk 
 WHERE pt.StatusFk = 6 AND FactCategoryFk = 12 //replace category for emCommonName 
 GROUP BY n.fullNameCache, pt.PTNameFk, pt.PTRefFk 
 ORDER BY n DESC 
 ~~~ 

Back