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