Project

General

Profile

Actions

feature request #6043

open

Name export for JACQ

Added by Katja Luther over 7 years ago. Updated about 3 years ago.

Status:
New
Priority:
Priority14
Assignee:
Category:
-
Target version:
-
Start date:
Due date:
% Done:

0%

Estimated time:
Severity:
normal

Description

write a name export for JACQ to serve the following spec which corresponds with the according IPNI export:

genus;species-epithet;species-author;infra_rank;infra-epithet;infra-author;ID;version


Related issues

Precedes EDIT - feature request #6070: Implement IPNI style name exportNewKatja Luther

Actions
Actions #1

Updated by Katja Luther over 7 years ago

For a first fast result created an sql query:

SELECT 
    tnb.genusOrUninomial as genus, tnb.specificEpithet as speciesepithet, (CASE WHEN (tnb.infraSpecificEpithet IS NULL)THEN  author.nomenclaturalTitle ELSE parentauthor.titleCache END) as speciesauthor ,(CASE WHEN (tnb.infraSpecificEpithet IS NULL)THEN  NULL ELSE rep.abbreviatedLabel END) as infrarank,tnb.infraSpecificEpithet as infraepithet,(CASE WHEN (tnb.infraSpecificEpithet IS NULL)THEN  NULL ELSE author.nomenclaturalTitle END) as infraauthor, tnb.id as ID, '1.0' as version
       FROM TaxonBase tb
         LEFT OUTER JOIN TaxonNameBase tnb ON tb.name_id = tnb.id
            LEFT OUTER JOIN Reference r ON tnb.nomenclaturalreference_id = r.id
             LEFT OUTER JOIN AgentBase author ON tnb.combinationAuthorship_id = author.id
             LEFT OUTER JOIN TaxonNode node ON node.taxon_id = tb.id 
             LEFT OUTER JOIN TaxonNode parent ON node.parent_id = parent.id
             LEFT OUTER JOIN TaxonBase parentTaxon ON parent.taxon_id = parentTaxon.id
             LEFT OUTER JOIN TaxonNameBase parentname On parentTaxon.name_id = parentname.id
             LEFT OUTER JOIN AgentBase parentauthor ON parentname.combinationAuthorship_id = parentauthor.id
                LEFT OUTER JOIN DefinedTermBase def ON tnb.rank_id = def.id
                LEFT OUTER JOIN DefinedTermBase_Representation def_rep ON def_rep.DefinedTermBase_id = def.id
                LEFT OUTER JOIN Representation rep ON def_rep.representations_id = rep.id
                where tnb.rank_id < 791 and tb.DTYPE like 'Taxon'
                ORDER BY tb.DTYPE, tnb.rank_id ,tb.titleCache
Actions #2

Updated by Andreas Müller over 7 years ago

  • Subject changed from name export for JACQ to Name export for JACQ
  • Description updated (diff)
Actions #3

Updated by Andreas Müller over 7 years ago

Actions #4

Updated by Katja Luther over 7 years ago

Select for the export of accepted taxa:

SELECT tnb.uuid,
    tnb.genusOrUninomial as genus, tnb.specificEpithet as speciesepithet, 
     (CASE WHEN (tnb.infraSpecificEpithet IS NULL)THEN  author.nomenclaturalTitle ELSE parentauthor.titleCache END) as speciesauthor ,
     (CASE WHEN (tnb.infraSpecificEpithet IS NULL)THEN  ex_author.nomenclaturalTitle ELSE parent_ex_author.titleCache END) as ex_speciesauthor ,
     (CASE WHEN (tnb.infraSpecificEpithet IS NULL)THEN  basionym_author.nomenclaturalTitle ELSE parent_basionym_author.titleCache END) as basionym_speciesauthor ,
     (CASE WHEN (tnb.infraSpecificEpithet IS NULL)THEN  ex_basionym_author.nomenclaturalTitle ELSE parent_ex_basionym_author.titleCache END) as ex_basionym_speciesauthor ,
     (CASE WHEN (tnb.infraSpecificEpithet IS NULL)THEN  NULL ELSE rep.abbreviatedLabel END) as infrarank,
     tnb.infraSpecificEpithet as infraepithet,
     (CASE WHEN (tnb.infraSpecificEpithet IS NULL)THEN  NULL ELSE author.nomenclaturalTitle END) as infraauthor, 
     tnb.id as ID, utc_date() as version
       FROM TaxonNode node
        LEFT  JOIN TaxonBase tb ON node.taxon_id = tb.id 
         LEFT  JOIN TaxonName tnb ON tb.name_id = tnb.id
            LEFT  JOIN Reference r ON tnb.nomenclaturalreference_id = r.id
             LEFT  JOIN AgentBase author ON tnb.combinationAuthorship_id = author.id
                 LEFT  JOIN AgentBase ex_author ON tnb.exCombinationAuthorship_id = ex_author.id
                 LEFT  JOIN AgentBase basionym_author ON tnb.basionymAuthorship_id =basionym_author.id
                 LEFT  JOIN AgentBase ex_basionym_author ON tnb.exBasionymAuthorship_id =ex_basionym_author.id
             LEFT  JOIN TaxonNode parent ON node.parent_id = parent.id
             LEFT  JOIN TaxonBase parentTaxon ON parent.taxon_id = parentTaxon.id
             LEFT  JOIN TaxonName parentname On parentTaxon.name_id = parentname.id
             LEFT  JOIN AgentBase parentauthor ON parentname.combinationAuthorship_id = parentauthor.id
             LEFT  JOIN AgentBase parent_ex_author ON parentname.exCombinationAuthorship_id = parent_ex_author.id
             LEFT  JOIN AgentBase parent_basionym_author ON parentname.basionymAuthorship_id = parent_basionym_author.id
                 LEFT  JOIN AgentBase parent_ex_basionym_author ON parentname.exBasionymAuthorship_id = parent_ex_basionym_author.id
                LEFT  JOIN DefinedTermBase def ON tnb.rank_id = def.id
                LEFT  JOIN DefinedTermBase_Representation def_rep ON def_rep.DefinedTermBase_id = def.id
                LEFT  JOIN Representation rep ON def_rep.representations_id = rep.id
                where tnb.rank_id < 791 and tb.DTYPE like 'Taxon'
                ORDER BY tnb.titleCache

Select for misapplied names:

SELECT tnb.genusOrUninomial as genus, tb.titleCache, tnb.specificEpithet as speciesepithet, 
(CASE WHEN (tnb.infraSpecificEpithet IS NULL)THEN  author.nomenclaturalTitle ELSE NULL END) as speciesauthor ,
(CASE WHEN (tnb.infraSpecificEpithet IS NULL)THEN  ex_author.nomenclaturalTitle ELSE NULL END) as ex_speciesauthor ,
(CASE WHEN (tnb.infraSpecificEpithet IS NULL)THEN  basionym_author.nomenclaturalTitle ELSE NULL END) as basionym_speciesauthor ,
(CASE WHEN (tnb.infraSpecificEpithet IS NULL)THEN  ex_basionym_author.nomenclaturalTitle ELSE NULL END) as ex_basionym_speciesauthor ,
(CASE WHEN (tnb.infraSpecificEpithet IS NULL)THEN  NULL ELSE rep.abbreviatedLabel END) as infrarank,
tnb.infraSpecificEpithet as infraepithet,
(CASE WHEN (tnb.infraSpecificEpithet IS NULL)THEN  NULL ELSE author.nomenclaturalTitle END) as infraauthor,
(CASE WHEN (tnb.infraSpecificEpithet IS NULL)THEN  NULL ELSE ex_author.nomenclaturalTitle END) as ex_infraauthor,
(CASE WHEN (tnb.infraSpecificEpithet IS NULL)THEN  NULL ELSE basionym_author.nomenclaturalTitle END) as basionym_infraauthor,
(CASE WHEN (tnb.infraSpecificEpithet IS NULL)THEN  NULL ELSE ex_basionym_author.nomenclaturalTitle END) as ex_basionym_infraauthor, 
tnb.id as ID, utc_date() as version
FROM TaxonBase tb
   JOIN TaxonName tnb ON tb.name_id = tnb.id
   LEFT JOIN Reference r ON tnb.nomenclaturalreference_id = r.id
   LEFT JOIN AgentBase author ON tnb.combinationAuthorship_id = author.id
   LEFT JOIN AgentBase ex_author ON tnb.exCombinationAuthorship_id = ex_author.id
   LEFT JOIN AgentBase basionym_author ON tnb.basionymAuthorship_id = basionym_author.id
   LEFT JOIN AgentBase ex_basionym_author ON tnb.exBasionymAuthorship_id = ex_basionym_author.id
   JOIN TaxonRelationship tr on tr.relatedFrom_id = tb.id and tr.type_id = 941
   JOIN DefinedTermBase def ON tnb.rank_id = def.id
   JOIN DefinedTermBase_Representation def_rep ON def_rep.DefinedTermBase_id = def.id
   JOIN Representation rep ON def_rep.representations_id = rep.id
   where tnb.rank_id < 791 and tb.DTYPE like 'Taxon' 
   ORDER by tnb.titleCache

select for synonyms:

SELECT tnb.genusOrUninomial as genus, tnb.specificEpithet as speciesepithet, 
(CASE WHEN (tnb.infraSpecificEpithet IS NULL)THEN  author.nomenclaturalTitle ELSE NULL END) as speciesauthor ,
(CASE WHEN (tnb.infraSpecificEpithet IS NULL)THEN  ex_author.nomenclaturalTitle ELSE NULL END) as ex_speciesauthor ,
(CASE WHEN (tnb.infraSpecificEpithet IS NULL)THEN  basionym_author.nomenclaturalTitle ELSE NULL END) as basionym_speciesauthor ,
(CASE WHEN (tnb.infraSpecificEpithet IS NULL)THEN  ex_basionym_author.nomenclaturalTitle ELSE NULL END) as ex_basionym_speciesauthor ,
(CASE WHEN (tnb.infraSpecificEpithet IS NULL)THEN  NULL ELSE rep.abbreviatedLabel END) as infrarank,
tnb.infraSpecificEpithet as infraepithet,
(CASE WHEN (tnb.infraSpecificEpithet IS NULL)THEN  NULL ELSE author.nomenclaturalTitle END) as infraauthor, 
(CASE WHEN (tnb.infraSpecificEpithet IS NULL)THEN  NULL ELSE ex_author.nomenclaturalTitle END) as infra_ex_speciesauthor ,
(CASE WHEN (tnb.infraSpecificEpithet IS NULL)THEN  NULL ELSE basionym_author.nomenclaturalTitle END) as infra_basionym_speciesauthor ,
(CASE WHEN (tnb.infraSpecificEpithet IS NULL)THEN  NULL ELSE ex_basionym_author.nomenclaturalTitle END) as infra_ex_basionym_speciesauthor ,
tnb.id as ID, 
utc_date() as version
FROM TaxonBase tb
    LEFT  JOIN TaxonName tnb ON tb.name_id = tnb.id
   LEFT  JOIN Reference r ON tnb.nomenclaturalreference_id = r.id
   LEFT  JOIN AgentBase author ON tnb.combinationAuthorship_id = author.id
   LEFT  JOIN AgentBase ex_author ON tnb.exCombinationAuthorship_id = ex_author.id
   LEFT  JOIN AgentBase basionym_author ON tnb.basionymAuthorship_id = basionym_author.id
   LEFT  JOIN AgentBase ex_basionym_author ON tnb.exBasionymAuthorship_id = ex_basionym_author.id
    LEFT  JOIN DefinedTermBase def ON tnb.rank_id = def.id
   LEFT  JOIN DefinedTermBase_Representation def_rep ON def_rep.DefinedTermBase_id = def.id
   LEFT  JOIN Representation rep ON def_rep.representations_id = rep.id
   where tnb.rank_id < 791 and tb.DTYPE like 'Synonym'  
Actions #5

Updated by Andreas Müller about 3 years ago

Is this still required? If yes, can we add a milestone? Can we close otherwise?

Actions

Also available in: Atom PDF