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

Edição das 07h30min de 27 de novembro de 2020

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.

Dicionário de atributos

O dicionário a seguir apresenta o mapeamento de atributos de integração das tabela de dados do Sistema AleloMicro e correspondente denominação no sistema Darwin Core. Os nomes de identificação são apresentados na tabela com a coluna correspondente a cada um dos sistemas seguido de uma coluna para exemplo do dado ou informação esperado. Abaixo de cada tabela de conversão apresenta-se o código SQL de consulta e seleção na base de dados do Sistema AleloMicro.

Coleção, códigos identicadores

(*) 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

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

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

(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

 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)

 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

 (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

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

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

--'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 nome 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,

--dta_coleta => "eventDate"   
 dta_coleta as eventDate,

--dta_primeiro_lote => "dateIdentified  
 dta_primeiro_lote as dateIdentified

Referências