Mudanças entre as edições de "AleloMicro-SiBBR: dicionário de dados"

De AleloWiki
Ir para: navegação, pesquisa
(→‎meio_cultura_isolamento)
 
(137 revisões intermediárias por 2 usuários não estão sendo mostradas)
Linha 1: Linha 1:
−
A Plataforma Alelo Recursos Genéticos, da qual faz parte o Sistema AleloMicro, participa de projetos e iniciativas de compartilhamento de dados e informações de recursos genéticos de microrganismos em favor da valorização dos acervos e da atividade de conservação de recursos genéticos com objetivo de promover o desenvolvimento da pesquisa voltada a alimentação e agricultura. A integração das bases de dados do [[Sistema AleloMicro]] com o sistema [[SiBBr]] foi constituída a partir da ferramenta IPT (''Integrated Publishing Toolkit'') para estrutruração de conjunto de dados definidos a partir do reconhecimento dos padrões admitidos no desenho de informações do sistema ''Darwin Core''. A documentação a seguir apresenta o dicionário de conversão de tabela de dados de ambos os sistemas.
+
{| style="float:right; margin:0 24px; width:30%;"
 +
|{{MenuAleloMicro-SiBBR}}
 +
|}
  
 +
A Plataforma Alelo Recursos Genéticos, da qual faz parte o [[Sistema AleloMicro]], participa de projetos e iniciativas de compartilhamento de dados e informações de recursos genéticos de microrganismos com objetivo de promover o desenvolvimento da pesquisa voltada a alimentação e agricultura. A integração das bases de dados do [[Sistema AleloMicro]] com o sistema [[SiBBr]] foi constituída a partir da ferramenta IPT (''Integrated Publishing Toolkit'') para estrutruração de conjunto de dados definidos a partir do reconhecimento dos termos admitidos no desenho de informações do padrão ''[[Darwin Core]]''.
  
−
===nme_escopo===
+
==Dicionário de identificação de campos de tabela==
  
−
===nme_instituicao===
+
O dicionário a seguir apresenta o mapeamento de registros em tabelas de dados para para integração do [[Sistema AleloMicro]] e correspondente denominação no sistema ''Darwin Core''. Os nomes de identificação de cada registro são apresentados em tabela com a coluna correspondente a cada um dos sistemas seguido de uma coluna para exemplo do dado ou saída esperada. Abaixo de cada tabela descritiva apresentam-se os códigos SQL de consulta e seleção na base de dados do [[Sistema AleloMicro]].
  
−
===nme_colecao===
+
===Coleção, códigos identicadores===
  
−
===subcolecao===
 
  
−
SELECT 'Embrapa Recursos Genéticos e Biotecnologia'  
+
{| class="wikitable" style="width:100%"
−
    AS nme_instituicao, nme_colecao, sgl_subcolecao || '-' ||
+
!AleloMicro
−
    nme_subcolecao AS subcolecao, sgl_escopo || ' ' ||  
+
!''Darwin Core''
−
    LPAD(CAST(idt_linhagem AS VARCHAR), 6, '0') || '/' ||  pfx_subcolecao || ' ' ||
+
!saída (ex.)
−
CAST(num_subcolecao AS VARCHAR) AS numero_catalogo,
+
|-
−
    'BRA/' || sgl_subcolecao || '/' || sgl_escopo || ' ' ||
+
|sgl_subcolecao; '-'; nme_cubcolecao;
 +
|''subcolecao''
 +
|CFI - Fungos de Invertebrados
 +
|-
 +
|sgl_escopo; ' '; idt_linhagem; '/'; pfx_subcolecao; ' '; num_subcolecao
 +
|''catalogNumber''
 +
|BRM 000001/CG 3
 +
|-
 +
|'BRA/'; sgl_subcolecao; '/'; sgl_escopo; idt_linhagem; '/'; pfx_subcolecao; ' '; num_subcolecao
 +
|''occurredID''
 +
|BRA/CFI/BRM 000001/CG 3
 +
|-
 +
|PreservedSpecimen
 +
|''basisofrecord''
 +
| (*)
 +
|-
 +
|}
 +
(*) ''campo não disponível''.
  
−
===brm===
+
SELECT
−
 
+
  'Embrapa Recursos Genéticos e Biotecnologia' as instituionCode,
−
===codigo_subcolecao===
+
  nme_colecao as collectionCode,
−
 
+
 
−
  LPAD(CAST(idt_linhagem AS VARCHAR), 6, '0') || '/' ||   
+
  sgl_subcolecao || '-' || nme_subcolecao AS subcolecao,
−
    pfx_subcolecao || ' ' || CAST(num_subcolecao AS VARCHAR)  
+
 
−
AS idt_ocorrencia,  
+
  sgl_escopo || ' ' || LPAD(CAST(idt_linhagem AS VARCHAR), 6, '0') || '/' ||  pfx_subcolecao || ' ' ||
−
    'PreservedSpecimen' as basisofrecord,
+
  CAST(num_subcolecao AS VARCHAR) AS catalogNumber,
−
 
+
 
−
===familia===
+
  'BRA/' || sgl_subcolecao || '/' || sgl_escopo || ' ' || LPAD(CAST(idt_linhagem AS VARCHAR), 6, '0') || '/' ||   
−
 
+
pfx_subcolecao || ' ' || CAST(num_subcolecao AS VARCHAR) AS occurrenceID,
−
===genero===
+
 
 +
  'PreservedSpecimen' as basisofrecord,
  
−
===especie===
+
===Taxonomia da linhagem===
  
−
===subcategoria_taxonomia===
+
{| class="wikitable" style="width:100%"
 +
!AleloMicro
 +
!''Darwin Core''
 +
!saída (ex.)
 +
|-
 +
|niv.idt_nivel: 3=class; 4=order; 5=family; 6=genus; 7=species; 8=subspecies
 +
|''taxonRank''
 +
|
 +
|-
 +
|"reino": (case when niv.idt_nivel>0; taxtata8.nme_taxonomia; ',')
 +
|''kingdom''
 +
|
 +
|-
 +
|"filo": (case when niv.idt_nivel>1; taxTata7.nme_taxonomia; ',')
 +
|''phylum''
 +
|
 +
|-
 +
|"classe": (case when niv.idt_nivel>2; taxTata6.nme_taxonomia; ',';)
 +
|''class''
 +
|
 +
|-
 +
|"ordem": (case when niv.idt_nivel>3, taxTata.nme_taxonomia; ',';)
 +
|''order''
 +
|
 +
|-
 +
|"família": (case when when niv.idt_nivel>4; taxBisa.nme_taxonomia; ',';)
 +
|''family''
 +
|
 +
|-
 +
|"gênero": (case when niv.idt_nivel>5, taxAvo.nme_taxonomia; ',';)
 +
|''genus''
 +
|
 +
|-
 +
|"espécie": (case when niv.idt_nivel>6; taxPai.nme_taxonomia; ',';)
 +
|''specificEpithet''
 +
|
 +
|-
 +
|"subespécie": (case when niv.idt_nivel>7; tax.nme_taxonomia; ',';)
 +
|''infraspecificEpithet''
 +
|
 +
|-
 +
|}
  
−
  case when niv.idt_nivel = 3 then 'class' else  
+
  --"Nivel taxonomia" => niv.idt_nivel: 3=class; 4=order; 5=family; 6=genus; 7=species; 8=subspecies 
 +
  case when niv.idt_nivel = 3 then 'class' else  
 
     case when niv.idt_nivel = 4 then 'order' else
 
     case when niv.idt_nivel = 4 then 'order' else
 
       case when niv.idt_nivel = 5 then 'family' else
 
       case when niv.idt_nivel = 5 then 'family' else
Linha 44: Linha 106:
 
       end
 
       end
 
     end  
 
     end  
−
   end as rank_taxonomico,
+
   end as taxonRank,
  
−
====reino====  
+
  --"reino": niv.idt_nivel>0; taxtata8.nme_taxonomia; ',' => "kingdom"  
−
    
+
   case when niv.idt_nivel>0 then
−
  case when niv.idt_nivel>0 then
 
 
   split_part(
 
   split_part(
 
   (case when taxTata8.nme_taxonomia is null then '' else (taxTata8.nme_taxonomia || ',') end ||  
 
   (case when taxTata8.nme_taxonomia is null then '' else (taxTata8.nme_taxonomia || ',') end ||  
Linha 59: Linha 120:
 
   case when tax.nme_taxonomia is null then '' else tax.nme_taxonomia end),
 
   case when tax.nme_taxonomia is null then '' else tax.nme_taxonomia end),
 
   ',', 1)   
 
   ',', 1)   
−
  else '' end as reino,
+
  else '' end as kingdom,
  
−
====filo====
+
--"filo": niv.idt_nivel>1; taxTata7.nme_taxonomia; ',' => "phylum"
−
 
+
  case when niv.idt_nivel>1 then
−
  case when niv.idt_nivel>1 then
 
 
   split_part(
 
   split_part(
 
   (case when taxTata8.nme_taxonomia is null then '' else (taxTata8.nme_taxonomia || ',') end ||  
 
   (case when taxTata8.nme_taxonomia is null then '' else (taxTata8.nme_taxonomia || ',') end ||  
Linha 74: Linha 134:
 
   case when tax.nme_taxonomia is null then '' else tax.nme_taxonomia end),
 
   case when tax.nme_taxonomia is null then '' else tax.nme_taxonomia end),
 
   ',', 2)   
 
   ',', 2)   
−
  else '' end as filo,
+
  else '' end as phylum,
−
 
 
−
====classe====
 
  
−
  case when niv.idt_nivel>2 then
+
--"classe" niv.idt_nivel>2; taxTata6.nme_taxonomia; ','; => "class" 
 +
  case when niv.idt_nivel>2 then
 
   split_part(
 
   split_part(
 
   (case when taxTata8.nme_taxonomia is null then '' else (taxTata8.nme_taxonomia || ',') end ||  
 
   (case when taxTata8.nme_taxonomia is null then '' else (taxTata8.nme_taxonomia || ',') end ||  
Linha 89: Linha 148:
 
   case when tax.nme_taxonomia is null then '' else tax.nme_taxonomia end),
 
   case when tax.nme_taxonomia is null then '' else tax.nme_taxonomia end),
 
   ',', 3)   
 
   ',', 3)   
−
  else '' end as classe,
+
  else '' end as class,
  
−
====ordem====
+
--"ordem": niv.idt_nivel>3, taxTata.nme_taxonomia; ','; => "order"
−
+
  case when niv.idt_nivel>3 then
−
  case when niv.idt_nivel>3 then
 
 
   split_part(
 
   split_part(
 
   (case when taxTata8.nme_taxonomia is null then '' else (taxTata8.nme_taxonomia || ',') end ||  
 
   (case when taxTata8.nme_taxonomia is null then '' else (taxTata8.nme_taxonomia || ',') end ||  
Linha 104: Linha 162:
 
   case when tax.nme_taxonomia is null then '' else tax.nme_taxonomia end),
 
   case when tax.nme_taxonomia is null then '' else tax.nme_taxonomia end),
 
   ',', 4)   
 
   ',', 4)   
−
  else '' end as ordem,
+
else '' end as order,
  
−
====familia====
+
--"família": niv.idt_nivel>4; taxBisa.nme_taxonomia; ','; => "family"
−
    
+
   case when niv.idt_nivel>4 then
−
  case when niv.idt_nivel>4 then
 
 
   split_part(
 
   split_part(
 
   (case when taxTata8.nme_taxonomia is null then '' else (taxTata8.nme_taxonomia || ',') end ||  
 
   (case when taxTata8.nme_taxonomia is null then '' else (taxTata8.nme_taxonomia || ',') end ||  
Linha 119: Linha 176:
 
   case when tax.nme_taxonomia is null then '' else tax.nme_taxonomia end),
 
   case when tax.nme_taxonomia is null then '' else tax.nme_taxonomia end),
 
   ',', 5)   
 
   ',', 5)   
−
  else '' end as familia,
+
else '' end as family,
  
−
====genero====
+
--"gênero": niv.idt_nivel>5, taxAvo.nme_taxonomia; ','; => "genus"
−
    
+
   case when niv.idt_nivel>5 then
−
 
 
−
  case when niv.idt_nivel>5 then
 
 
   split_part(
 
   split_part(
 
   (case when taxTata8.nme_taxonomia is null then '' else (taxTata8.nme_taxonomia || ',') end ||  
 
   (case when taxTata8.nme_taxonomia is null then '' else (taxTata8.nme_taxonomia || ',') end ||  
Linha 135: Linha 190:
 
   case when tax.nme_taxonomia is null then '' else tax.nme_taxonomia end),
 
   case when tax.nme_taxonomia is null then '' else tax.nme_taxonomia end),
 
   ',', 6)   
 
   ',', 6)   
−
  else '' end as genero,
+
else '' end as genus,
  
−
====especie====
+
--"espécie": niv.idt_nivel>6; taxPai.nme_taxonomia; ','; => "specificEpithet"
−
 
+
  case when  
−
  case when  
 
 
   (case when niv.idt_nivel>6 then
 
   (case when niv.idt_nivel>6 then
 
   split_part(
 
   split_part(
Linha 164: Linha 218:
 
   ',', 7)   
 
   ',', 7)   
 
   else '' end)
 
   else '' end)
−
  end as especie,
+
end as specificEpithet,
  
−
====subcategoria==== 
+
--"subespécie": niv.idt_nivel>7; tax.nme_taxonomia; ','; => "infraspecificEpithet"
−
 
+
  case when niv.idt_nivel>7 then
−
  case when niv.idt_nivel>7 then
 
 
   split_part(
 
   split_part(
 
   (case when taxTata8.nme_taxonomia is null then '' else (taxTata8.nme_taxonomia || ',') end ||  
 
   (case when taxTata8.nme_taxonomia is null then '' else (taxTata8.nme_taxonomia || ',') end ||  
Linha 179: Linha 232:
 
   case when tax.nme_taxonomia is null then '' else tax.nme_taxonomia end),
 
   case when tax.nme_taxonomia is null then '' else tax.nme_taxonomia end),
 
   ',', 8)   
 
   ',', 8)   
−
  else '' end as subcategoria,
+
else '' end as infraspecificEpithet,
−
 
 
−
===hosp_familia===
 
−
 
 
−
===hosp_genero===
 
  
−
===hosp_especie===
+
===Taxonomia do hospedeiro===
  
−
===hosp_subcategoria===
+
{| class="wikitable" style="width:100%"
 +
!AleloMicro
 +
!''Darwin Core''
 +
!ex.
 +
|-
 +
|CASE WHEN nivAvoH.nme_nivel='Gênero' => taxAvoH.nme_taxonomia
 +
|
 +
|
 +
|-
 +
|CASE WHEN nivPaiH.nme_nivel='Espécie' => taxPaiH.nme_taxonomia
 +
|
 +
|
 +
|-
 +
|CASE WHEN nivH.nme_nivel='Subcategoria' => taxH.nme_taxonomia
 +
|
 +
|
 +
|-
 +
|"Gênero Espécie Subcategoria"
 +
|''hospedeiro''
 +
|
 +
|-
 +
|}
  
 
  replace(
 
  replace(
 
     (CASE WHEN (taxH.nme_taxonomia ILIKE '%Não%') THEN '' ELSE
 
     (CASE WHEN (taxH.nme_taxonomia ILIKE '%Não%') THEN '' ELSE
−
     CASE WHEN nivAvoH.nme_nivel='Gênero' THEN taxAvoH.nme_taxonomia ELSE CASE WHEN nivPaiH.nme_nivel='Gênero' THEN  
+
     CASE WHEN nivAvoH.nme_nivel='Gênero' THEN taxAvoH.nme_taxonomia ELSE CASE WHEN nivPaiH.nme_nivel='Gênero' THEN taxPaiH.nme_taxonomia ELSE '' END END  
−
taxPaiH.nme_taxonomia ELSE '' END END  
 
 
   END ||
 
   END ||
 
   CASE WHEN (taxH.nme_taxonomia ILIKE '%Não%') THEN '' ELSE
 
   CASE WHEN (taxH.nme_taxonomia ILIKE '%Não%') THEN '' ELSE
−
     CASE WHEN nivPaiH.nme_nivel='Espécie' THEN (' ' || taxPaiH.nme_taxonomia) ELSE CASE WHEN  
+
     CASE WHEN nivPaiH.nme_nivel='Espécie' THEN (' ' || taxPaiH.nme_taxonomia) ELSE CASE WHEN nivH.nme_nivel='Espécie' THEN (' ' || taxH.nme_taxonomia) ELSE '' END END
−
nivH.nme_nivel='Espécie' THEN (' ' || taxH.nme_taxonomia) ELSE '' END END
 
 
   END ||
 
   END ||
 
   CASE WHEN (taxH.nme_taxonomia ILIKE '%Não%') THEN '' ELSE
 
   CASE WHEN (taxH.nme_taxonomia ILIKE '%Não%') THEN '' ELSE
Linha 202: Linha 270:
 
   end), ' sp.', '')  AS hospedeiro,
 
   end), ' sp.', '')  AS hospedeiro,
  
−
===codigos_outras_instituicoes===
+
===Código em outras instituições===
−
 
 
−
===nome_pais===
 
−
 
 
−
===nome_estado===
 
  
−
===nome_municipio===
+
{| class="wikitable" style="width:100%"
 +
!AleloMicro
 +
!''Darwin Core''
 +
!ex.
 +
|-
 +
|num_codigo (mic.tbl_codigos AS cod)
 +
|
 +
|
 +
|-
 +
|nme_instituicao (sis.tbl_instituicoes AS insCod)
 +
|
 +
|
 +
|-
 +
|(cod) insCod, '; '
 +
|''otherCatalogNumbers''
 +
|(uv-032) Universidade Federal de Viçosa;
 +
|-
 +
|}
  
 
  (SELECT STRING_AGG('(' || num_codigo || ') ' || insCod.nme_instituicao, '; ')
 
  (SELECT STRING_AGG('(' || num_codigo || ') ' || insCod.nme_instituicao, '; ')
−
       FROM mic.tbl_codigos AS cod JOIN sis.tbl_instituicoes AS insCod ON insCod.idt_instituicao=cod.cod_instituicao
+
       FROM mic.tbl_codigos AS cod JOIN sis.tbl_instituicoes AS insCod ON  
 +
insCod.idt_instituicao=cod.cod_instituicao
 
       WHERE cod_linhagem=idt_linhagem)
 
       WHERE cod_linhagem=idt_linhagem)
−
     AS CODIGOS_OUTRAS_INSTITUICOES,
+
     AS otherCatalogNumbers,
−
  CASE WHEN (nme_pais ILIKE '%Não%') THEN '' ELSE nme_pais END AS nome_pais,
 
−
  CASE WHEN (nme_estado ILIKE '%Não%') THEN '' ELSE nme_estado END AS nome_estado,
 
−
  CASE WHEN (sts_municipio_publico AND nme_municipio ILIKE '%Não%') THEN '' ELSE nme_municipio END AS
 
−
nome_municipio,
 
  
−
===altitude===
+
===Procedência da linhagem===
  
−
===latitude===
+
{| class="wikitable" style="width:100%"
 +
!AleloMicro
 +
!''Darwin Core''
 +
!ex.
 +
|-
 +
|nme_pais
 +
|''country''
 +
|Brasil
 +
|-
 +
|nme_estado
 +
|''stateProvince''
 +
|Ceará
 +
|-
 +
|nme_municipio
 +
|''county''
 +
|Sobral
 +
|-
 +
|}
  
−
===longitude===
+
  CASE WHEN (nme_pais ILIKE '%Não%') THEN '' ELSE nme_pais END AS country, 
 +
  CASE WHEN (nme_estado ILIKE '%Não%') THEN '' ELSE nme_estado END AS stateProvince, 
 +
  CASE WHEN (sts_municipio_publico AND nme_municipio ILIKE '%Não%') THEN '' ELSE nme_municipio END AS county,
  
−
===substrato===
+
===Geolocalização (substrato)===
  
−
  CASE WHEN sts_geo_publico THEN num_altitude ELSE NULL END AS altitude,
+
{| class="wikitable" style="width:100%"
−
  CASE WHEN sts_geo_publico THEN num_latitude ELSE NULL END AS latitude ,
+
!AleloMicro
−
  CASE WHEN sts_geo_publico THEN num_longitude ELSE NULL END AS longitude,
+
!''Darwin Core''
 +
!ex.
 +
|-
 +
|num_altitude
 +
|''minimumElevationInMeters''
 +
|232
 +
|-
 +
|num_altitude
 +
|''maximumElevationInMeters''
 +
|1024
 +
|-
 +
|num_latitude
 +
|''decimalLatitude''
 +
|36.22
 +
|-
 +
|num_longitude
 +
|''decimalLongitude''
 +
|48.36
 +
|-
 +
|subs.nme_descritor
 +
|''substrato''
 +
|
 +
|-
 +
|}
  
 +
  CASE WHEN sts_geo_publico THEN num_altitude ELSE NULL END AS minimumElevationInMeters,
 +
  CASE WHEN sts_geo_publico THEN num_altitude ELSE NULL END AS maximumElevationInMeters, 
 +
  CASE WHEN sts_geo_publico THEN num_latitude ELSE NULL END AS decimalLatitude, 
 +
  CASE WHEN sts_geo_publico THEN num_longitude ELSE NULL END AS decimalLongitude,
 
   subs.nme_descritor as substrato,
 
   subs.nme_descritor as substrato,
  
−
===bioma_brasileiro===
+
===Habitat===
 +
 
 +
{| class="wikitable" style="width:100%"
 +
!AleloWiki
 +
!''Darwin Core''
 +
!ex.
 +
|-
 +
|nme_grupo_descritor='Bioma Brasileiro'
 +
|''habitat''
 +
|"Caatinga"
 +
|-
 +
|}
  
 
   (SELECT STRING_AGG(nme_descritor, '; ') FROM mic.tba_linhagens_descritores AS lc
 
   (SELECT STRING_AGG(nme_descritor, '; ') FROM mic.tba_linhagens_descritores AS lc
Linha 239: Linha 374:
 
   JOIN mic.tbr_descritores AS dc ON dc.idt_descritor=lc.cod_descritor
 
   JOIN mic.tbr_descritores AS dc ON dc.idt_descritor=lc.cod_descritor
 
   WHERE nme_grupo_descritor='Bioma Brasileiro' AND left(gd.txt_filtro,1)='d' AND cod_linhagem=lin.idt_linhagem) AS  
 
   WHERE nme_grupo_descritor='Bioma Brasileiro' AND left(gd.txt_filtro,1)='d' AND cod_linhagem=lin.idt_linhagem) AS  
−
  Bioma_BR_habitat,
+
  habitat,
  
−
===categoria_linhagem===
+
===Métodos e protocolos de amostragem===
  
−
===metodo_identificacao===
+
{| class="wikitable" style="width:100%"
 +
!AleloWiki
 +
!''Darwin Core''
 +
!ex.
 +
|-
 +
|nme_descritor WHERE nme_grupo_descritor='Método de Identificação'
 +
|
 +
|
 +
|-
 +
|nme_descritor WHERE nme_grupo_descritor='Método de Isolamento'
 +
|
 +
|
 +
|-
 +
|nme_descritor WHERE nme_grupo_descritor='Meio de Cultura de Isolamento'
 +
|
 +
|
 +
|-
 +
|'Método de Identificação'; 'Método de Isolamento'; 'Meio de Cultura de Isolamento';
 +
|''samplingProtocol''
 +
|
 +
|-
 +
|}
  
−
===metodo_isolamento===
+
--'Método de Identificação' => ... "samplingProtocol"
−
 
+
  left(case when
−
===meio_cultura_isolamento===
+
    (SELECT STRING_AGG(nme_descritor, ' | ') FROM mic.tba_linhagens_descritores AS lc
 +
    JOIN mic.tbr_grupos_descritores AS gd ON gd.idt_grupo_descritor=lc.cod_grupo_descritor
 +
    JOIN mic.tbr_descritores AS dc ON dc.idt_descritor=lc.cod_descritor
 +
    WHERE nme_grupo_descritor='Método de Identificação' AND left(gd.txt_filtro,1)='d' AND
 +
cod_linhagem=lin.idt_linhagem) is null
 +
  then '' else
 +
    (SELECT STRING_AGG(nme_descritor, ' | ') FROM mic.tba_linhagens_descritores AS lc
 +
    JOIN mic.tbr_grupos_descritores AS gd ON gd.idt_grupo_descritor=lc.cod_grupo_descritor
 +
    JOIN mic.tbr_descritores AS dc ON dc.idt_descritor=lc.cod_descritor
 +
    WHERE nme_grupo_descritor='Método de Identificação' AND left(gd.txt_filtro,1)='d' AND
 +
cod_linhagem=lin.idt_linhagem) || ' | '
 +
  end   
 +
  ||
  
 +
--'Método de Isolamento' => ... "samplingProtocol"
 
   case when  
 
   case when  
 
     (SELECT STRING_AGG(nme_descritor, ' | ') FROM mic.tba_linhagens_descritores AS lc
 
     (SELECT STRING_AGG(nme_descritor, ' | ') FROM mic.tba_linhagens_descritores AS lc
Linha 264: Linha 433:
 
   ||
 
   ||
  
 +
--'Meio de Cultura de Isolamento' => ... "samplingProtocol"
 
   case when
 
   case when
 
     (SELECT STRING_AGG(nme_descritor, ' | ') FROM mic.tba_linhagens_descritores AS lc
 
     (SELECT STRING_AGG(nme_descritor, ' | ') FROM mic.tba_linhagens_descritores AS lc
Linha 277: Linha 447:
 
  cod_linhagem=lin.idt_linhagem) || ' | '
 
  cod_linhagem=lin.idt_linhagem) || ' | '
 
   end, -3)   
 
   end, -3)   
−
   AS sampling_protocol,
+
   AS samplingProtocol,
 +
 
 +
===Pessoas, coletores, identicadores===
 +
 
 +
{| class="wikitable" style="width:100%"
 +
!AleloMicro
 +
!''Darwin Core''
 +
!ex.
 +
|-
 +
|nme_grupo_descritor: nme_usuario (pessoas)
 +
|''pessoas''
 +
|
 +
|-
 +
|nme_grupo_descritor='Coletor'
 +
|''coletores_recordby''
 +
|
 +
|-
 +
|nme_grupo_descritor='Identificador'
 +
|''identifiedBy''
 +
|
 +
|-
 +
|}
 +
 
 +
--Pessoas => "Pessoas"
 +
(SELECT STRING_AGG(nme_grupo_descritor || ': ' || nme_usuario, '; ')
 +
  FROM mic.tba_linhagens_descritores AS lc
 +
  JOIN mic.tbr_grupos_descritores AS gd ON gd.idt_grupo_descritor=lc.cod_grupo_descritor
 +
  JOIN usr.tbl_usuarios AS us ON lc.cod_usuario=us.idt_usuario
 +
  WHERE left(gd.txt_filtro,1)='p' AND cod_linhagem=lin.idt_linhagem) AS pessoas,
 +
 
 +
--Coletor => "coletores_recordby"
 +
  (SELECT STRING_AGG(nme_usuario, ' | ')
 +
  FROM mic.tba_linhagens_descritores AS lc
 +
  JOIN mic.tbr_grupos_descritores AS gd ON gd.idt_grupo_descritor=lc.cod_grupo_descritor
 +
  JOIN usr.tbl_usuarios AS us ON lc.cod_usuario=us.idt_usuario
 +
  WHERE nme_grupo_descritor='Coletor' AND left(gd.txt_filtro,1)='p' AND cod_linhagem=lin.idt_linhagem) AS
 +
coletores_recordby,
 +
 
 +
--Identificador => "identifiedBy"
 +
  (SELECT STRING_AGG(nme_usuario, ' | ')
 +
  FROM mic.tba_linhagens_descritores AS lc
 +
  JOIN mic.tbr_grupos_descritores AS gd ON gd.idt_grupo_descritor=lc.cod_grupo_descritor
 +
  JOIN usr.tbl_usuarios AS us ON lc.cod_usuario=us.idt_usuario
 +
  WHERE nme_grupo_descritor='Identificador' AND left(gd.txt_filtro,1)='p' AND cod_linhagem=lin.idt_linhagem) AS identifiedBy,
 +
 
 +
===Categoria e data da coleta===
 +
 
 +
{| class="wikitable" style="width:100%"
 +
!AleloMicro
 +
!''Darwin Core''
 +
!ex.
 +
|-
 +
|nme_descritor (WHERE nme_grupo_descritor='Categoria da Linhagem')
 +
|
 +
|
 +
|-
 +
|nme_descritor (WHERE nme_grupo_descritor='Isolador')
 +
|
 +
|
 +
|-
 +
|'Categoria da Linhagem'; 'Isolador'
 +
|''identificationRemarks''
 +
|
 +
|-
 +
|dta_coleta
 +
|''eventDate''
 +
|
 +
|-
 +
|dta_primeiro_lote
 +
|''dateIdentified''
 +
|
 +
|-
 +
|}
 +
 
 +
--'Categoria da Linhagem' => ... "identificationRemarks"
 +
 +
  left(
 +
  case when
 +
    (SELECT STRING_AGG(nme_descritor, '; ') FROM mic.tba_linhagens_descritores AS lc
 +
    JOIN mic.tbr_grupos_descritores AS gd ON gd.idt_grupo_descritor=lc.cod_grupo_descritor
 +
    JOIN mic.tbr_descritores AS dc ON dc.idt_descritor=lc.cod_descritor
 +
    WHERE nme_grupo_descritor='Categoria da Linhagem' AND left(gd.txt_filtro,1)='d' AND
 +
cod_linhagem=lin.idt_linhagem) is null
 +
  then '' else
 +
    'Categoria da Linhagem: ' || (SELECT STRING_AGG(nme_descritor, '; ') FROM mic.tba_linhagens_descritores AS lc
 +
    JOIN mic.tbr_grupos_descritores AS gd ON gd.idt_grupo_descritor=lc.cod_grupo_descritor
 +
    JOIN mic.tbr_descritores AS dc ON dc.idt_descritor=lc.cod_descritor
 +
    WHERE nme_grupo_descritor='Categoria da Linhagem' AND left(gd.txt_filtro,1)='d' AND
 +
cod_linhagem=lin.idt_linhagem) || ' | '
 +
  end
 +
  ||
 +
 
 +
--'Isolador' => ... "identificationRemarks"
 +
 +
  case when
 +
    (SELECT STRING_AGG(nme_usuario, '; ')
 +
    FROM mic.tba_linhagens_descritores AS lc
 +
    JOIN mic.tbr_grupos_descritores AS gd ON gd.idt_grupo_descritor=lc.cod_grupo_descritor
 +
    JOIN usr.tbl_usuarios AS us ON lc.cod_usuario=us.idt_usuario
 +
    WHERE nme_grupo_descritor='Isolador' AND left(gd.txt_filtro,1)='p' AND cod_linhagem=lin.idt_linhagem) is null
 +
  then '' else
 +
    'Isolador: ' || (SELECT STRING_AGG(nme_usuario, '; ')
 +
    FROM mic.tba_linhagens_descritores AS lc
 +
    JOIN mic.tbr_grupos_descritores AS gd ON gd.idt_grupo_descritor=lc.cod_grupo_descritor
 +
    JOIN usr.tbl_usuarios AS us ON lc.cod_usuario=us.idt_usuario
 +
    WHERE nme_grupo_descritor='Isolador' AND left(gd.txt_filtro,1)='p' AND cod_linhagem=lin.idt_linhagem) || ' | '
 +
  end, -3) 
 +
  AS identificationRemarks,
 +
 
 +
--Datas de coleta e identificação
 +
  dta_coleta as eventDate,
 +
  dta_primeiro_lote as dateIdentified
 +
 
 +
==Critérios de busca e impressão==
 +
 
 +
====Coleções====
 +
 
 +
  FROM mic.tbr_escopos JOIN mic.tbl_subcolecoes AS sub ON idt_escopo=cod_escopo
 +
  JOIN sis.tbl_instituicoes as insSub ON insSub.idt_instituicao=sub.cod_instituicao
 +
  JOIN mic.tbl_colecoes ON idt_colecao=cod_colecao JOIN mic.tbl_linhagens AS lin ON
 +
idt_subcolecao=lin.cod_subcolecao
 +
  JOIN mic.tbl_acessos AS ac ON idt_linhagem=cod_linhagem
 +
 
 +
====Taxonomia da linhagem====
 +
 
 +
  LEFT JOIN tax.tbl_taxonomias AS tax ON tax.idt_taxonomia = lin.cod_taxonomia
 +
  LEFT JOIN tax.tbl_taxonomias AS taxPai ON taxPai.idt_taxonomia = tax.cod_pai
 +
  LEFT JOIN tax.tbl_taxonomias AS taxAvo ON taxAvo.idt_taxonomia = taxPai.cod_pai
 +
  LEFT JOIN tax.tbl_taxonomias AS taxBisa ON taxBisa.idt_taxonomia = taxAvo.cod_pai
 +
  LEFT JOIN tax.tbl_taxonomias AS taxTata ON case when taxBisa.cod_pai is null then false else
 +
taxTata.idt_taxonomia = taxBisa.cod_pai end
 +
  LEFT JOIN tax.tbl_taxonomias AS taxTata6 ON case when taxTata.cod_pai is null then false else
 +
taxTata6.idt_taxonomia = taxTata.cod_pai end
 +
  LEFT JOIN tax.tbl_taxonomias AS taxTata7 ON case when taxTata.cod_pai is null then false else
 +
taxTata7.idt_taxonomia = taxTata6.cod_pai end
 +
  LEFT JOIN tax.tbl_taxonomias AS taxTata8 ON case when taxTata.cod_pai is null then false else
 +
taxTata8.idt_taxonomia = taxTata7.cod_pai end
 +
 
 +
  LEFT JOIN tax.tbr_niveis AS niv ON niv.idt_nivel=tax.cod_nivel
 +
 
 +
====Taxonomia do hospedeiro====
 +
 
 +
  LEFT JOIN tax.tbl_taxonomias AS taxH ON taxH.idt_taxonomia = lin.cod_hospedeiro
 +
  LEFT JOIN tax.tbl_taxonomias AS taxPaiH ON taxPaiH.idt_taxonomia = taxH.cod_pai
 +
  LEFT JOIN tax.tbl_taxonomias AS taxAvoH ON taxAvoH.idt_taxonomia = taxPaiH.cod_pai
 +
  LEFT JOIN tax.tbl_taxonomias AS taxBisaH ON taxBisaH.idt_taxonomia = taxAvoH.cod_pai
 +
 
 +
  LEFT JOIN tax.tbr_niveis AS nivH ON nivH.idt_nivel=taxH.cod_nivel
 +
  LEFT JOIN tax.tbr_niveis AS nivPaiH ON nivPaiH.idt_nivel=taxPaiH.cod_nivel
 +
  LEFT JOIN tax.tbr_niveis AS nivAvoH ON nivAvoH.idt_nivel=taxAvoH.cod_nivel
 +
  LEFT JOIN tax.tbr_niveis AS nivBisaH ON nivBisaH.idt_nivel=taxBisaH.cod_nivel
 +
 
 +
====Procedência====
 +
 
 +
  LEFT JOIN sis.tbl_municipios mun ON mun.idt_municipio = lin.cod_municipio
 +
  LEFT JOIN sis.tbl_estados est ON est.idt_estado = mun.cod_estado
 +
  LEFT JOIN sis.tbr_paises pais ON pais.idt_pais = est.cod_pais
 +
 
 +
====Descritores====
  
−
===coletores===
+
  LEFT join mic.tbr_descritores AS subs on lin.cod_substrato = subs.idt_descritor AND subs.cod_grupo_descritor = 4
  
−
===identificadores===
+
====Escopo e país====
  
−
===isoladores===
+
  WHERE idt_colecao=3 AND pais.idt_pais=28 AND ac.sts_publico and sub.sts_publica and
 +
  tax.cod_nivel>3 AND (NOT tax.nme_taxonomia ILIKE '%Não%');
  
 
==Referências==
 
==Referências==

Edição atual tal como às 11h15min de 4 de dezembro de 2020

AleloMicro-SiBBR
Configuração
Dicionário de dados

A Plataforma Alelo Recursos Genéticos, da qual faz parte o Sistema AleloMicro, participa de projetos e iniciativas de compartilhamento de dados e informações de recursos genéticos de microrganismos com objetivo de promover o desenvolvimento da pesquisa voltada a alimentação e agricultura. A integração das bases de dados do Sistema AleloMicro com o sistema SiBBr foi constituída a partir da ferramenta IPT (Integrated Publishing Toolkit) para estrutruração de conjunto de dados definidos a partir do reconhecimento dos termos admitidos no desenho de informações do padrão Darwin Core.

Dicionário de identificação de campos de tabela

O dicionário a seguir apresenta o mapeamento de registros em tabelas de dados para para integração do Sistema AleloMicro e correspondente denominação no sistema Darwin Core. Os nomes de identificação de cada registro são apresentados em tabela com a coluna correspondente a cada um dos sistemas seguido de uma coluna para exemplo do dado ou saída esperada. Abaixo de cada tabela descritiva apresentam-se os códigos SQL de consulta e seleção na base de dados do Sistema AleloMicro.

Coleção, códigos identicadores

AleloMicro Darwin Core saída (ex.)
sgl_subcolecao; '-'; nme_cubcolecao; subcolecao CFI - Fungos de Invertebrados
sgl_escopo; ' '; idt_linhagem; '/'; pfx_subcolecao; ' '; num_subcolecao catalogNumber BRM 000001/CG 3
'BRA/'; sgl_subcolecao; '/'; sgl_escopo; idt_linhagem; '/'; pfx_subcolecao; ' '; num_subcolecao occurredID BRA/CFI/BRM 000001/CG 3
PreservedSpecimen basisofrecord (*)

(*) campo não disponível. 

SELECT 
 'Embrapa Recursos Genéticos e Biotecnologia' as instituionCode,
 nme_colecao as collectionCode,
 
 sgl_subcolecao || '-' || nme_subcolecao AS subcolecao,
 
 sgl_escopo || ' ' || LPAD(CAST(idt_linhagem AS VARCHAR), 6, '0') || '/' ||  pfx_subcolecao || ' ' || 
CAST(num_subcolecao AS VARCHAR) AS catalogNumber,
 
 'BRA/' || sgl_subcolecao || '/' || sgl_escopo || ' ' || LPAD(CAST(idt_linhagem AS VARCHAR), 6, '0') || '/' ||  
pfx_subcolecao || ' ' || CAST(num_subcolecao AS VARCHAR) AS occurrenceID,
 
 'PreservedSpecimen' as basisofrecord,

Taxonomia da linhagem

AleloMicro Darwin Core saída (ex.)
niv.idt_nivel: 3=class; 4=order; 5=family; 6=genus; 7=species; 8=subspecies taxonRank
"reino": (case when niv.idt_nivel>0; taxtata8.nme_taxonomia; ',') kingdom
"filo": (case when niv.idt_nivel>1; taxTata7.nme_taxonomia; ',') phylum
"classe": (case when niv.idt_nivel>2; taxTata6.nme_taxonomia; ',';) class
"ordem": (case when niv.idt_nivel>3, taxTata.nme_taxonomia; ',';) order
"família": (case when when niv.idt_nivel>4; taxBisa.nme_taxonomia; ',';) family
"gênero": (case when niv.idt_nivel>5, taxAvo.nme_taxonomia; ',';) genus
"espécie": (case when niv.idt_nivel>6; taxPai.nme_taxonomia; ',';) specificEpithet
"subespécie": (case when niv.idt_nivel>7; tax.nme_taxonomia; ',';) infraspecificEpithet 
--"Nivel taxonomia" => niv.idt_nivel: 3=class; 4=order; 5=family; 6=genus; 7=species; 8=subspecies  
 case when niv.idt_nivel = 3 then 'class' else 
   case when niv.idt_nivel = 4 then 'order' else
     case when niv.idt_nivel = 5 then 'family' else
       case when niv.idt_nivel = 6 then 'genus' else
         case when niv.idt_nivel = 7 then 'species' else
           case when niv.idt_nivel = 8 then 'subspecies' else  end
         end
       end
     end
   end 
 end as taxonRank,

 --"reino": niv.idt_nivel>0; taxtata8.nme_taxonomia; ',' => "kingdom"  
 case when niv.idt_nivel>0 then
  split_part(
  (case when taxTata8.nme_taxonomia is null then  else (taxTata8.nme_taxonomia || ',') end || 
  case when taxTata7.nme_taxonomia is null then  else (taxTata7.nme_taxonomia || ',') end || 
  case when taxTata6.nme_taxonomia is null then  else (taxTata6.nme_taxonomia || ',') end || 
  case when taxTata.nme_taxonomia is null then  else (taxTata.nme_taxonomia || ',') end || 
  case when taxBisa.nme_taxonomia is null then  else (taxBisa.nme_taxonomia || ',') end || 
  case when taxAvo.nme_taxonomia is null then  else (taxAvo.nme_taxonomia || ',') end || 
  case when taxPai.nme_taxonomia is null then  else (taxPai.nme_taxonomia || ',') end || 
  case when tax.nme_taxonomia is null then  else tax.nme_taxonomia end),
  ',', 1)  
 else  end as kingdom,

--"filo": niv.idt_nivel>1; taxTata7.nme_taxonomia; ',' => "phylum"
 case when niv.idt_nivel>1 then
  split_part(
  (case when taxTata8.nme_taxonomia is null then  else (taxTata8.nme_taxonomia || ',') end || 
  case when taxTata7.nme_taxonomia is null then  else (taxTata7.nme_taxonomia || ',') end || 
  case when taxTata6.nme_taxonomia is null then  else (taxTata6.nme_taxonomia || ',') end || 
  case when taxTata.nme_taxonomia is null then  else (taxTata.nme_taxonomia || ',') end || 
  case when taxBisa.nme_taxonomia is null then  else (taxBisa.nme_taxonomia || ',') end || 
  case when taxAvo.nme_taxonomia is null then  else (taxAvo.nme_taxonomia || ',') end || 
  case when taxPai.nme_taxonomia is null then  else (taxPai.nme_taxonomia || ',') end || 
  case when tax.nme_taxonomia is null then  else tax.nme_taxonomia end),
  ',', 2)  
 else  end as phylum,

--"classe" niv.idt_nivel>2; taxTata6.nme_taxonomia; ','; => "class"  
 case when niv.idt_nivel>2 then
  split_part(
  (case when taxTata8.nme_taxonomia is null then  else (taxTata8.nme_taxonomia || ',') end || 
  case when taxTata7.nme_taxonomia is null then  else (taxTata7.nme_taxonomia || ',') end || 
  case when taxTata6.nme_taxonomia is null then  else (taxTata6.nme_taxonomia || ',') end || 
  case when taxTata.nme_taxonomia is null then  else (taxTata.nme_taxonomia || ',') end || 
  case when taxBisa.nme_taxonomia is null then  else (taxBisa.nme_taxonomia || ',') end || 
  case when taxAvo.nme_taxonomia is null then  else (taxAvo.nme_taxonomia || ',') end || 
  case when taxPai.nme_taxonomia is null then  else (taxPai.nme_taxonomia || ',') end || 
  case when tax.nme_taxonomia is null then  else tax.nme_taxonomia end),
  ',', 3)  
 else  end as class,

--"ordem": niv.idt_nivel>3, taxTata.nme_taxonomia; ','; => "order" 
 case when niv.idt_nivel>3 then
  split_part(
  (case when taxTata8.nme_taxonomia is null then  else (taxTata8.nme_taxonomia || ',') end || 
  case when taxTata7.nme_taxonomia is null then  else (taxTata7.nme_taxonomia || ',') end || 
  case when taxTata6.nme_taxonomia is null then  else (taxTata6.nme_taxonomia || ',') end || 
  case when taxTata.nme_taxonomia is null then  else (taxTata.nme_taxonomia || ',') end || 
  case when taxBisa.nme_taxonomia is null then  else (taxBisa.nme_taxonomia || ',') end || 
  case when taxAvo.nme_taxonomia is null then  else (taxAvo.nme_taxonomia || ',') end || 
  case when taxPai.nme_taxonomia is null then  else (taxPai.nme_taxonomia || ',') end || 
  case when tax.nme_taxonomia is null then  else tax.nme_taxonomia end),
  ',', 4)  
else  end as order,

--"família": niv.idt_nivel>4; taxBisa.nme_taxonomia; ','; => "family"
 case when niv.idt_nivel>4 then
  split_part(
  (case when taxTata8.nme_taxonomia is null then  else (taxTata8.nme_taxonomia || ',') end || 
  case when taxTata7.nme_taxonomia is null then  else (taxTata7.nme_taxonomia || ',') end || 
  case when taxTata6.nme_taxonomia is null then  else (taxTata6.nme_taxonomia || ',') end || 
  case when taxTata.nme_taxonomia is null then  else (taxTata.nme_taxonomia || ',') end || 
  case when taxBisa.nme_taxonomia is null then  else (taxBisa.nme_taxonomia || ',') end || 
  case when taxAvo.nme_taxonomia is null then  else (taxAvo.nme_taxonomia || ',') end || 
  case when taxPai.nme_taxonomia is null then  else (taxPai.nme_taxonomia || ',') end || 
  case when tax.nme_taxonomia is null then  else tax.nme_taxonomia end),
  ',', 5)  
else  end as family,

--"gênero": niv.idt_nivel>5, taxAvo.nme_taxonomia; ','; => "genus"
 case when niv.idt_nivel>5 then
  split_part(
  (case when taxTata8.nme_taxonomia is null then  else (taxTata8.nme_taxonomia || ',') end || 
  case when taxTata7.nme_taxonomia is null then  else (taxTata7.nme_taxonomia || ',') end || 
  case when taxTata6.nme_taxonomia is null then  else (taxTata6.nme_taxonomia || ',') end || 
  case when taxTata.nme_taxonomia is null then  else (taxTata.nme_taxonomia || ',') end || 
  case when taxBisa.nme_taxonomia is null then  else (taxBisa.nme_taxonomia || ',') end || 
  case when taxAvo.nme_taxonomia is null then  else (taxAvo.nme_taxonomia || ',') end || 
  case when taxPai.nme_taxonomia is null then  else (taxPai.nme_taxonomia || ',') end || 
  case when tax.nme_taxonomia is null then  else tax.nme_taxonomia end),
  ',', 6)  
else  end as genus,

--"espécie": niv.idt_nivel>6; taxPai.nme_taxonomia; ','; => "specificEpithet"
 case when 
  (case when niv.idt_nivel>6 then
  split_part(
  (case when taxTata8.nme_taxonomia is null then  else (taxTata8.nme_taxonomia || ',') end || 
  case when taxTata7.nme_taxonomia is null then  else (taxTata7.nme_taxonomia || ',') end || 
  case when taxTata6.nme_taxonomia is null then  else (taxTata6.nme_taxonomia || ',') end || 
  case when taxTata.nme_taxonomia is null then  else (taxTata.nme_taxonomia || ',') end || 
  case when taxBisa.nme_taxonomia is null then  else (taxBisa.nme_taxonomia || ',') end || 
  case when taxAvo.nme_taxonomia is null then  else (taxAvo.nme_taxonomia || ',') end || 
  case when taxPai.nme_taxonomia is null then  else (taxPai.nme_taxonomia || ',') end || 
  case when tax.nme_taxonomia is null then  else tax.nme_taxonomia end),
  ',', 7)  
  else  end) = 'sp.' then  else 
  (case when niv.idt_nivel>6 then
  split_part(
  (case when taxTata8.nme_taxonomia is null then  else (taxTata8.nme_taxonomia || ',') end || 
  case when taxTata7.nme_taxonomia is null then  else (taxTata7.nme_taxonomia || ',') end || 
  case when taxTata6.nme_taxonomia is null then  else (taxTata6.nme_taxonomia || ',') end || 
  case when taxTata.nme_taxonomia is null then  else (taxTata.nme_taxonomia || ',') end || 
  case when taxBisa.nme_taxonomia is null then  else (taxBisa.nme_taxonomia || ',') end || 
  case when taxAvo.nme_taxonomia is null then  else (taxAvo.nme_taxonomia || ',') end || 
  case when taxPai.nme_taxonomia is null then  else (taxPai.nme_taxonomia || ',') end || 
  case when tax.nme_taxonomia is null then  else tax.nme_taxonomia end),
  ',', 7)  
  else  end)
end as specificEpithet,

--"subespécie": niv.idt_nivel>7; tax.nme_taxonomia; ','; => "infraspecificEpithet"
 case when niv.idt_nivel>7 then
  split_part(
  (case when taxTata8.nme_taxonomia is null then  else (taxTata8.nme_taxonomia || ',') end || 
  case when taxTata7.nme_taxonomia is null then  else (taxTata7.nme_taxonomia || ',') end || 
  case when taxTata6.nme_taxonomia is null then  else (taxTata6.nme_taxonomia || ',') end || 
  case when taxTata.nme_taxonomia is null then  else (taxTata.nme_taxonomia || ',') end || 
  case when taxBisa.nme_taxonomia is null then  else (taxBisa.nme_taxonomia || ',') end || 
  case when taxAvo.nme_taxonomia is null then  else (taxAvo.nme_taxonomia || ',') end || 
  case when taxPai.nme_taxonomia is null then  else (taxPai.nme_taxonomia || ',') end || 
  case when tax.nme_taxonomia is null then  else tax.nme_taxonomia end),
  ',', 8)  
else  end as infraspecificEpithet,

Taxonomia do hospedeiro

AleloMicro Darwin Core ex.
CASE WHEN nivAvoH.nme_nivel='Gênero' => taxAvoH.nme_taxonomia
CASE WHEN nivPaiH.nme_nivel='Espécie' => taxPaiH.nme_taxonomia
CASE WHEN nivH.nme_nivel='Subcategoria' => taxH.nme_taxonomia
"Gênero Espécie Subcategoria" hospedeiro 
replace(
   (CASE WHEN (taxH.nme_taxonomia ILIKE '%Não%') THEN  ELSE
   CASE WHEN nivAvoH.nme_nivel='Gênero' THEN taxAvoH.nme_taxonomia ELSE CASE WHEN nivPaiH.nme_nivel='Gênero' THEN taxPaiH.nme_taxonomia ELSE  END END 
 END ||
 CASE WHEN (taxH.nme_taxonomia ILIKE '%Não%') THEN  ELSE
   CASE WHEN nivPaiH.nme_nivel='Espécie' THEN (' ' || taxPaiH.nme_taxonomia) ELSE CASE WHEN nivH.nme_nivel='Espécie' THEN (' ' || taxH.nme_taxonomia) ELSE  END END
 END ||
 CASE WHEN (taxH.nme_taxonomia ILIKE '%Não%') THEN  ELSE
   CASE WHEN nivH.nme_nivel='Subcategoria' THEN (' ' || taxH.nme_taxonomia) ELSE  END
 end), ' sp.', )  AS hospedeiro,

Código em outras instituições

AleloMicro Darwin Core ex.
num_codigo (mic.tbl_codigos AS cod)
nme_instituicao (sis.tbl_instituicoes AS insCod)
(cod) insCod, '; ' otherCatalogNumbers (uv-032) Universidade Federal de Viçosa; 
(SELECT STRING_AGG('(' || num_codigo || ') ' || insCod.nme_instituicao, '; ')
     FROM mic.tbl_codigos AS cod JOIN sis.tbl_instituicoes AS insCod ON 
insCod.idt_instituicao=cod.cod_instituicao
     WHERE cod_linhagem=idt_linhagem)
    AS otherCatalogNumbers,

Procedência da linhagem

AleloMicro Darwin Core ex.
nme_pais country Brasil
nme_estado stateProvince Ceará
nme_municipio county Sobral 
 CASE WHEN (nme_pais ILIKE '%Não%') THEN  ELSE nme_pais END AS country,  
 CASE WHEN (nme_estado ILIKE '%Não%') THEN  ELSE nme_estado END AS stateProvince,  
 CASE WHEN (sts_municipio_publico AND nme_municipio ILIKE '%Não%') THEN  ELSE nme_municipio END AS county,

Geolocalização (substrato)

AleloMicro Darwin Core ex.
num_altitude minimumElevationInMeters 232
num_altitude maximumElevationInMeters 1024
num_latitude decimalLatitude 36.22
num_longitude decimalLongitude 48.36
subs.nme_descritor substrato 
 CASE WHEN sts_geo_publico THEN num_altitude ELSE NULL END AS minimumElevationInMeters,
 CASE WHEN sts_geo_publico THEN num_altitude ELSE NULL END AS maximumElevationInMeters,  
 CASE WHEN sts_geo_publico THEN num_latitude ELSE NULL END AS decimalLatitude,  
 CASE WHEN sts_geo_publico THEN num_longitude ELSE NULL END AS decimalLongitude,
 subs.nme_descritor as substrato,

Habitat

AleloWiki Darwin Core ex.
nme_grupo_descritor='Bioma Brasileiro' habitat "Caatinga" 
 (SELECT STRING_AGG(nme_descritor, '; ') FROM mic.tba_linhagens_descritores AS lc
 JOIN mic.tbr_grupos_descritores AS gd ON gd.idt_grupo_descritor=lc.cod_grupo_descritor
 JOIN mic.tbr_descritores AS dc ON dc.idt_descritor=lc.cod_descritor
 WHERE nme_grupo_descritor='Bioma Brasileiro' AND left(gd.txt_filtro,1)='d' AND cod_linhagem=lin.idt_linhagem) AS 
habitat,

Métodos e protocolos de amostragem

AleloWiki Darwin Core ex.
nme_descritor WHERE nme_grupo_descritor='Método de Identificação'
nme_descritor WHERE nme_grupo_descritor='Método de Isolamento'
nme_descritor WHERE nme_grupo_descritor='Meio de Cultura de Isolamento'
'Método de Identificação'; 'Método de Isolamento'; 'Meio de Cultura de Isolamento'; samplingProtocol 
--'Método de Identificação' => ... "samplingProtocol"
 left(case when 
   (SELECT STRING_AGG(nme_descritor, ' | ') FROM mic.tba_linhagens_descritores AS lc
    JOIN mic.tbr_grupos_descritores AS gd ON gd.idt_grupo_descritor=lc.cod_grupo_descritor
    JOIN mic.tbr_descritores AS dc ON dc.idt_descritor=lc.cod_descritor
    WHERE nme_grupo_descritor='Método de Identificação' AND left(gd.txt_filtro,1)='d' AND 
cod_linhagem=lin.idt_linhagem) is null
 then  else 
    (SELECT STRING_AGG(nme_descritor, ' | ') FROM mic.tba_linhagens_descritores AS lc
    JOIN mic.tbr_grupos_descritores AS gd ON gd.idt_grupo_descritor=lc.cod_grupo_descritor
    JOIN mic.tbr_descritores AS dc ON dc.idt_descritor=lc.cod_descritor
    WHERE nme_grupo_descritor='Método de Identificação' AND left(gd.txt_filtro,1)='d' AND 
cod_linhagem=lin.idt_linhagem) || ' | '
 end    
 ||

--'Método de Isolamento' => ... "samplingProtocol"
 case when 
   (SELECT STRING_AGG(nme_descritor, ' | ') FROM mic.tba_linhagens_descritores AS lc
   JOIN mic.tbr_grupos_descritores AS gd ON gd.idt_grupo_descritor=lc.cod_grupo_descritor
   JOIN mic.tbr_descritores AS dc ON dc.idt_descritor=lc.cod_descritor
   WHERE nme_grupo_descritor='Método de Isolamento' AND left(gd.txt_filtro,1)='d' AND 
cod_linhagem=lin.idt_linhagem) is null 
 then  else
   (SELECT STRING_AGG(nme_descritor, ' | ') FROM mic.tba_linhagens_descritores AS lc
   JOIN mic.tbr_grupos_descritores AS gd ON gd.idt_grupo_descritor=lc.cod_grupo_descritor
   JOIN mic.tbr_descritores AS dc ON dc.idt_descritor=lc.cod_descritor
   WHERE nme_grupo_descritor='Método de Isolamento' AND left(gd.txt_filtro,1)='d' AND 
cod_linhagem=lin.idt_linhagem) || ' | '
 end  
 ||

--'Meio de Cultura de Isolamento' => ... "samplingProtocol"
 case when
   (SELECT STRING_AGG(nme_descritor, ' | ') FROM mic.tba_linhagens_descritores AS lc
   JOIN mic.tbr_grupos_descritores AS gd ON gd.idt_grupo_descritor=lc.cod_grupo_descritor
   JOIN mic.tbr_descritores AS dc ON dc.idt_descritor=lc.cod_descritor
   WHERE nme_grupo_descritor='Meio de Cultura de Isolamento' AND left(gd.txt_filtro,1)='d' AND 
cod_linhagem=lin.idt_linhagem) is null 
 then  else 
   (SELECT STRING_AGG(nme_descritor, ' | ') FROM mic.tba_linhagens_descritores AS lc
   JOIN mic.tbr_grupos_descritores AS gd ON gd.idt_grupo_descritor=lc.cod_grupo_descritor
   JOIN mic.tbr_descritores AS dc ON dc.idt_descritor=lc.cod_descritor
   WHERE nme_grupo_descritor='Meio de Cultura de Isolamento' AND left(gd.txt_filtro,1)='d' AND 
cod_linhagem=lin.idt_linhagem) || ' | '
 end, -3)  
 AS samplingProtocol,

Pessoas, coletores, identicadores

AleloMicro Darwin Core ex.
nme_grupo_descritor: nme_usuario (pessoas) pessoas
nme_grupo_descritor='Coletor' coletores_recordby
nme_grupo_descritor='Identificador' identifiedBy 
--Pessoas => "Pessoas"
(SELECT STRING_AGG(nme_grupo_descritor || ': ' || nme_usuario, '; ')
 FROM mic.tba_linhagens_descritores AS lc
 JOIN mic.tbr_grupos_descritores AS gd ON gd.idt_grupo_descritor=lc.cod_grupo_descritor
 JOIN usr.tbl_usuarios AS us ON lc.cod_usuario=us.idt_usuario
 WHERE left(gd.txt_filtro,1)='p' AND cod_linhagem=lin.idt_linhagem) AS pessoas,

--Coletor => "coletores_recordby"
 (SELECT STRING_AGG(nme_usuario, ' | ')
 FROM mic.tba_linhagens_descritores AS lc
 JOIN mic.tbr_grupos_descritores AS gd ON gd.idt_grupo_descritor=lc.cod_grupo_descritor
 JOIN usr.tbl_usuarios AS us ON lc.cod_usuario=us.idt_usuario
 WHERE nme_grupo_descritor='Coletor' AND left(gd.txt_filtro,1)='p' AND cod_linhagem=lin.idt_linhagem) AS 
coletores_recordby,

--Identificador => "identifiedBy"
 (SELECT STRING_AGG(nme_usuario, ' | ')
 FROM mic.tba_linhagens_descritores AS lc
 JOIN mic.tbr_grupos_descritores AS gd ON gd.idt_grupo_descritor=lc.cod_grupo_descritor
 JOIN usr.tbl_usuarios AS us ON lc.cod_usuario=us.idt_usuario
 WHERE nme_grupo_descritor='Identificador' AND left(gd.txt_filtro,1)='p' AND cod_linhagem=lin.idt_linhagem) AS identifiedBy,

Categoria e data da coleta

AleloMicro Darwin Core ex.
nme_descritor (WHERE nme_grupo_descritor='Categoria da Linhagem')
nme_descritor (WHERE nme_grupo_descritor='Isolador')
'Categoria da Linhagem'; 'Isolador' identificationRemarks
dta_coleta eventDate
dta_primeiro_lote dateIdentified 
--'Categoria da Linhagem' => ... "identificationRemarks"

 left(
 case when 
   (SELECT STRING_AGG(nme_descritor, '; ') FROM mic.tba_linhagens_descritores AS lc
   JOIN mic.tbr_grupos_descritores AS gd ON gd.idt_grupo_descritor=lc.cod_grupo_descritor
   JOIN mic.tbr_descritores AS dc ON dc.idt_descritor=lc.cod_descritor
   WHERE nme_grupo_descritor='Categoria da Linhagem' AND left(gd.txt_filtro,1)='d' AND 
cod_linhagem=lin.idt_linhagem) is null 
 then  else
   'Categoria da Linhagem: ' || (SELECT STRING_AGG(nme_descritor, '; ') FROM mic.tba_linhagens_descritores AS lc
   JOIN mic.tbr_grupos_descritores AS gd ON gd.idt_grupo_descritor=lc.cod_grupo_descritor
   JOIN mic.tbr_descritores AS dc ON dc.idt_descritor=lc.cod_descritor
   WHERE nme_grupo_descritor='Categoria da Linhagem' AND left(gd.txt_filtro,1)='d' AND 
cod_linhagem=lin.idt_linhagem) || ' | '
 end 
 ||

--'Isolador' => ... "identificationRemarks"

 case when 
   (SELECT STRING_AGG(nme_usuario, '; ')
   FROM mic.tba_linhagens_descritores AS lc
   JOIN mic.tbr_grupos_descritores AS gd ON gd.idt_grupo_descritor=lc.cod_grupo_descritor
   JOIN usr.tbl_usuarios AS us ON lc.cod_usuario=us.idt_usuario
   WHERE nme_grupo_descritor='Isolador' AND left(gd.txt_filtro,1)='p' AND cod_linhagem=lin.idt_linhagem) is null 
 then  else
   'Isolador: ' || (SELECT STRING_AGG(nme_usuario, '; ')
   FROM mic.tba_linhagens_descritores AS lc
   JOIN mic.tbr_grupos_descritores AS gd ON gd.idt_grupo_descritor=lc.cod_grupo_descritor
   JOIN usr.tbl_usuarios AS us ON lc.cod_usuario=us.idt_usuario
   WHERE nme_grupo_descritor='Isolador' AND left(gd.txt_filtro,1)='p' AND cod_linhagem=lin.idt_linhagem) || ' | '
 end, -3)  
 AS identificationRemarks,

--Datas de coleta e identificação 
 dta_coleta as eventDate, 
 dta_primeiro_lote as dateIdentified

Critérios de busca e impressão

Coleções

 FROM mic.tbr_escopos JOIN mic.tbl_subcolecoes AS sub ON idt_escopo=cod_escopo
 JOIN sis.tbl_instituicoes as insSub ON insSub.idt_instituicao=sub.cod_instituicao
 JOIN mic.tbl_colecoes ON idt_colecao=cod_colecao JOIN mic.tbl_linhagens AS lin ON 
idt_subcolecao=lin.cod_subcolecao
 JOIN mic.tbl_acessos AS ac ON idt_linhagem=cod_linhagem

Taxonomia da linhagem

 LEFT JOIN tax.tbl_taxonomias AS tax ON tax.idt_taxonomia = lin.cod_taxonomia
 LEFT JOIN tax.tbl_taxonomias AS taxPai ON taxPai.idt_taxonomia = tax.cod_pai
 LEFT JOIN tax.tbl_taxonomias AS taxAvo ON taxAvo.idt_taxonomia = taxPai.cod_pai
 LEFT JOIN tax.tbl_taxonomias AS taxBisa ON taxBisa.idt_taxonomia = taxAvo.cod_pai
 LEFT JOIN tax.tbl_taxonomias AS taxTata ON case when taxBisa.cod_pai is null then false else 
taxTata.idt_taxonomia = taxBisa.cod_pai end
 LEFT JOIN tax.tbl_taxonomias AS taxTata6 ON case when taxTata.cod_pai is null then false else 
taxTata6.idt_taxonomia = taxTata.cod_pai end
 LEFT JOIN tax.tbl_taxonomias AS taxTata7 ON case when taxTata.cod_pai is null then false else 
taxTata7.idt_taxonomia = taxTata6.cod_pai end
 LEFT JOIN tax.tbl_taxonomias AS taxTata8 ON case when taxTata.cod_pai is null then false else 
taxTata8.idt_taxonomia = taxTata7.cod_pai end

 LEFT JOIN tax.tbr_niveis AS niv ON niv.idt_nivel=tax.cod_nivel

Taxonomia do hospedeiro

 LEFT JOIN tax.tbl_taxonomias AS taxH ON taxH.idt_taxonomia = lin.cod_hospedeiro
 LEFT JOIN tax.tbl_taxonomias AS taxPaiH ON taxPaiH.idt_taxonomia = taxH.cod_pai
 LEFT JOIN tax.tbl_taxonomias AS taxAvoH ON taxAvoH.idt_taxonomia = taxPaiH.cod_pai
 LEFT JOIN tax.tbl_taxonomias AS taxBisaH ON taxBisaH.idt_taxonomia = taxAvoH.cod_pai

 LEFT JOIN tax.tbr_niveis AS nivH ON nivH.idt_nivel=taxH.cod_nivel
 LEFT JOIN tax.tbr_niveis AS nivPaiH ON nivPaiH.idt_nivel=taxPaiH.cod_nivel
 LEFT JOIN tax.tbr_niveis AS nivAvoH ON nivAvoH.idt_nivel=taxAvoH.cod_nivel
 LEFT JOIN tax.tbr_niveis AS nivBisaH ON nivBisaH.idt_nivel=taxBisaH.cod_nivel

Procedência

 LEFT JOIN sis.tbl_municipios mun ON mun.idt_municipio = lin.cod_municipio
 LEFT JOIN sis.tbl_estados est ON est.idt_estado = mun.cod_estado
 LEFT JOIN sis.tbr_paises pais ON pais.idt_pais = est.cod_pais

Descritores

 LEFT join mic.tbr_descritores AS subs on lin.cod_substrato = subs.idt_descritor AND subs.cod_grupo_descritor = 4

Escopo e país

 WHERE idt_colecao=3 AND pais.idt_pais=28 AND ac.sts_publico and sub.sts_publica and
  tax.cod_nivel>3 AND (NOT tax.nme_taxonomia ILIKE '%Não%');

Referências

Disponível em "https://alelowiki.cenargen.embrapa.br/index.php?title=AleloMicro-SiBBR:_dicionário_de_dados&oldid=20711"