Project

General

Profile

feature request #6043

Name export for JACQ

Added by Katja Luther almost 3 years ago. Updated almost 3 years ago.

Status:
New
Priority:
Priority14
Assignee:
Category:
-
Target version:
-
Start date:
08/25/2016
Due date:
% Done:

0%

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 export New 08/26/2016 08/26/2016

History

#1 Updated by Katja Luther almost 3 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

#2 Updated by Andreas Müller almost 3 years ago

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

#3 Updated by Andreas Müller almost 3 years ago

#4 Updated by Katja Luther almost 3 years ago

Select for the export of accepted taxa:

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, utc_date() as version
       FROM TaxonNode node
        LEFT  JOIN TaxonBase tb ON node.taxon_id = tb.id 
         LEFT  JOIN TaxonNameBase 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 TaxonNode parent ON node.parent_id = parent.id
             LEFT  JOIN TaxonBase parentTaxon ON parent.taxon_id = parentTaxon.id
             LEFT  JOIN TaxonNameBase parentname On parentTaxon.name_id = parentname.id
             LEFT  JOIN AgentBase parentauthor ON parentname.combinationAuthorship_id = parentauthor.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  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 TaxonBase tb
     JOIN TaxonNameBase 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
   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  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 TaxonBase tb
    LEFT  JOIN TaxonNameBase 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 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' 

Also available in: Atom PDF

Add picture from clipboard (Maximum size: 40 MB)