task #7341
Updated by Andreas Müller about 6 years ago
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 ~~~