Actions
feature request #6043
openName export for JACQ
Added by Katja Luther about 8 years ago. Updated over 3 years ago.
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
Updated by Katja Luther about 8 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
Updated by Andreas Müller almost 8 years ago
- Subject changed from name export for JACQ to Name export for JACQ
- Description updated (diff)
Updated by Andreas Müller almost 8 years ago
- Precedes feature request #6070: Implement IPNI style name export added
Updated by Katja Luther almost 8 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'
Updated by Andreas Müller over 3 years ago
Is this still required? If yes, can we add a milestone? Can we close otherwise?
Actions