Project

General

Profile

task #7341

[Check] CommonName import issues

Added by Andreas Müller over 1 year ago. Updated 4 months ago.

Status:
Resolved
Priority:
Highest
Category:
cdmadapter
Start date:
04/03/2018
Due date:
% Done:

90%

Estimated time:
1.00 h
Severity:
normal
Tags:

Description

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:

  • MisappliedName Common Names (see logfile) #7447
  • TBC

                 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:

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

Related issues

Related to Edit - bug #6938: Tasks before a new Euro+Med import Resolved 09/08/2017
Related to Edit - task #7346: [CHECK] Use separate area vocabulary for common names Resolved 04/06/2018
Related to Edit - bug #7447: ERS: Fix misapplied name issues for E+M common names. Resolved 06/03/2018
Related to Edit - bug #7449: E+M: Import common names with no regionFk correctly Resolved 06/03/2018

Associated revisions

Revision 69584a18 (diff)
Added by Andreas Müller over 1 year ago

ref #7341 validate common names with nameUseInSourceFk = -1

History

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

  • Description updated (diff)

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

  • Description updated (diff)

Find commonNames or facts attached to orphaned taxa:

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

only very few left, most of them facts

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

  • Description updated (diff)

#4 Updated by Andreas Müller over 1 year ago

  • Related to bug #6938: Tasks before a new Euro+Med import added

#5 Updated by Andreas Müller over 1 year ago

  • Tags set to euro+med

#8 Updated by Andreas Müller over 1 year ago

  • Priority changed from New to Highest

#9 Updated by Andreas Müller over 1 year ago

  • Status changed from New to In Progress

#10 Updated by Andreas Müller over 1 year ago

  • Related to task #7346: [CHECK] Use separate area vocabulary for common names added

#11 Updated by Andreas Müller over 1 year ago

  • Description updated (diff)
  • Estimated time set to 2.00 h

#12 Updated by Andreas Müller over 1 year ago

  • % Done changed from 0 to 90
  • Estimated time changed from 2.00 h to 25.00 h

#14 Updated by Andreas Müller over 1 year ago

  • Estimated time changed from 25.00 h to 2.00 h

#15 Updated by Andreas Müller over 1 year ago

  • Priority changed from Highest to Priority13

#18 Updated by Andreas Müller over 1 year ago

  • Related to bug #7447: ERS: Fix misapplied name issues for E+M common names. added

#19 Updated by Andreas Müller over 1 year ago

  • Description updated (diff)

#20 Updated by Andreas Müller over 1 year ago

  • Related to bug #7449: E+M: Import common names with no regionFk correctly added

#21 Updated by Andreas Müller 12 months ago

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

#22 Updated by Andreas Müller 12 months ago

  • Description updated (diff)

#23 Updated by Andreas Müller 12 months ago

  • Subject changed from [E+M] CommonName import issues to [Check] CommonName import issues
  • Priority changed from Priority13 to Highest

#24 Updated by Andreas Müller 12 months ago

  • Estimated time changed from 2.00 h to 1.00 h

#25 Updated by Andreas Müller 4 months ago

  • Status changed from In Progress to Resolved

This is generally solved. We may want to check a last time after import if facts and atomized common names are fully sychronized and if misapplied names etc are used correctly.

#26 Updated by Andreas Müller 4 months ago

  • Target version changed from Euro+Med Migration to Euro+Med post migration

Also available in: Atom PDF

Add picture from clipboard (Maximum size: 40 MB)