No cenário de hoje a nossa empresa possui diversos departamentos, cada um com seus próprios sistemas, provendo as mais variadas funcionalidades, cada um com suas especificidades, sendo escritos em linguagens diferentes e acessando bancos de dados diferentes, etc.

Nós somos responsáveis por produzir um relatório que possui dados de dois departamentos diferentes cujos sistemas não se conversam.

A solução canônica hoje em dia é ter uma arquitetura baseada em serviços, eventualmente com APIs REST, onde só é preciso enviar um request e está feito o trabalho.

Mas a nossa empresa ainda não chegou lá e vamos precisar lidar com os sistemas legados.

Para resolver esse problema, vamos usar o DBI.

O que é DBI?

O DBI é uma interface pra acesso a bancos de dados que é independente do tipo de banco de dados, ou seja, não importa se é MySQL, MariaDB ou Postgres, ele vai oferecer o mesmo conjunto de métodos.

Dessa forma a gente só precisa se familiarizar com a API do DBI que vamos conseguir interagir com qualquer banco de dados.

Para isso o DBI utiliza um conjunto de "drivers", um para cada banco de dados, os módulos DBD::*, que abstraem as partes que são específicas de cada engine.

Se você for na busca do metacpan, digitar DBD:: e esperar ele sugerir uma lista de opções, vai ver uma quantidade razoável de engines suportadas pelo DBI.

O modus operandi padrão é você se conectar no banco, utilizar os métodos de acesso aos dados e por fim desconectar.

#!/usr/bin/env perl

use DBI;

$dbh  = DBI->connect($data_source, $username, $auth, \%attr);

$sth  = $dbh->prepare($statement);
$rv   = $sth->execute(@bind_values);
$rows = $sth->fetchall_arrayref( $slice, $max_rows );

$rc   = $dbh->disconnect;

A documentação do DBI é bem tranquila, especialmente se você está familiarizado com esse passo a passo de lidar com banco de dados.

O DBI também suporta a criação de transactions, ou como conhecemos, transações. Assim você pode utilizar um conjunto complexo de instruções SQL de forma atômica, claro se o banco de dados que você estiver usando permitir esse tipo de coisa.

O DBI é bastante configurável, então você pode personalizar bastante as conexões, desde o encoding padrão, o comportamento do tratamento de erro, ativar ou dessativar funções e muito mais.

Dê sempre uma olhada na documentação, tanto do DBI quanto do módulo DBD::* que você estiver utilizando para poder encontrar a sintaxe correta da opção que você estiver precisando.

O Problema

A nossa empresa fictícia fornece cursos diversos para seus clientes. O Departamento de Vendas nos pediu um relatório com todos os alunos aprovados nos nossos cursos, contendo cada curso onde esses alunos foram aprovados, de modo a poder enviar emails sugerindo outros cursos.

O problema é que a informação parece estar espalhada pela empresa, em departamentos diferentes.

O Departamento de Clientes é responsável pelo cadastro e os dados do clientes. Eles utilizam banco de dados PostgreSQL (postgres para os íntimos) e guardam numa tabela clients, dados como nome, username e email de cada cliente.

Também temos o Departamento de Cursos, que é responsável pelos cursos que a empresa oferece aos clientes. Eles utilizam banco de dados MariaDB (mariadb) e tem numa tabela course_history o nome do curso, o username do aluno e a nota do aluno no curso.

Dado que não existem serviços que possamos acessar, nosso trabalho é criar um programa que se conecte a ambos os bancos de dados^[1], obtenha os dados necessários e gere um relatório em formato CSV.

Montando o cenário

Embora esteja fora do escopo deste post explicar como configurar containers, para demonstrar o nosso cenário, utilizamos docker e docker-compose para simular os diferentes bancos de dados.

Para executar o exemplo, partimos do pressuposto que você tenha instalado e configurado na sua máquina o docker, docker-compose e um alias chamado compose apontando para o comando docker-compose.

Caso você não tenha isso tudo configurado, pode pular essa seção sem prejuízos.

Resumidamente, o compose vai criar containers executando as respectivas engines que vão funcionar como mini servidores e vai mapear as portas 4001 e 4002 da máquina local para as respectivas portas dos bancos de dados em seus respectivos containers.

Assim se você quiser se conectar a cada banco utilizando uma ferramenta como o DBeaver, por exemplo, basta apontar para essas duas portas na máquina local.

Os clientes que o nosso compose disponibiliza, por outro lado, acessam as respectivas bases de dados através da própria rede interna entre os containers que ele mesmo cria.

Pra começar, baixe o código completo direto do repositório de exemplos e navegue até o diretório 2020-09/databases.

Iniciando o Postgres

user@host$ compose up -d pgdb
Creating network "databases_blog-post" with driver "bridge"
Creating databases_pgdb_1 ... done

O comando acima vai iniciar um container com o postgres em segundo plano e executar os scripts de inicialização criando a base de dados de exemplo, usuário, dados, etc.

Note que se for o primeiro comando executado, ele também vai criar uma rede interna entre os containers, chamada "databases_blog-post".

Iniciando o MariaDB

user@host$ compose up -d mydb
Creating databases_mydb_1 ... done

O comando acima, assim como com o postgres, inicializa um container mariadb e executa os scripts de inicialização criando tabelas, usuários, dados, etc.

Recomeçando do zero

Em ambos os casos, a inicialização dos dados só acontece na primeira vez que os containers são iniciados. Se você quiser apagar tudo e começar novamente, basta executar co comando abaixo:

user@host$ compose down && docker volume rm databases_pgdata databases_mydata
Stopping databases_mydb_1 ... done
Stopping databases_pgdb_1 ... done
Removing databases_mydb_1 ... done
Removing databases_pgdb_1 ... done
Removing network databases_blog-post
databases_pgdata
databases_mydata

Acessando Postgres via CLI

user@host$ compose run pg-cli
Starting databases_pgdb_1 ... done
Password:
psql (12.4 (Debian 12.4-1.pgdg100+1))
Type "help" for help.

pgdb_sample=>

Após entrar com a senha do usuário definida no arquivo .env, teminamos no prompt do psql.

Acessando MariaDB via CLI

user@host$ compose run my-cli
Starting databases_mydb_1 ... done
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 10.5.5-MariaDB-1:10.5.5+maria~focal mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [mydb_sample]>

Assim como no caso do postgres, após entrar com a senha do usuário, caímos no prompt do mysql, o cliente de linha de comando do mariadb.

Note que os comandos acima são definidos pelo arquivo docker-compose.yml presente no projeto de exemplo.

Executando o exemplo

Como vamos acessar dois bancos diferentes e os drivers do DBI utilizam as bibliotecas nativas de cada um, é preciso ter instalados os pacotes correspondentes. No ubuntu isso pode ser feito com:

user@host$ sudo apt update && sudo apt install -y build-essential libpq-dev libmariadb-dev

Em seguida utilizamos o carton para instalar as dependências:

user@host$ carton install

E executamos o exemplo com:

user@host$ carton exec -- perl ./report.pl

O relatório

O código completo está disponível no projeto de exemplo. Abaixo comentamos cada parte separadamente.

#!/usr/bin/env perl

use v5.32;
use strict;
use warnings;

use DBI;
use Text::CSV_XS qw( csv );

O trecho acima é apenas o cabeçalho padrão de programas Perl onde indicamos entre outras coisas, que vamos utilizar o DBI para acesso ao banco e o Text::CSV_XS para gerar o arquivo CSV.

## conexão ao postgres
my $pg_host = 'localhost';
my $pg_port = '4001';
my $pg_db   = 'pgdb_sample';
my $pg_dsn  = "DBI:Pg:dbname=$pg_db;host=$pg_host;port=$pg_port";
my $pg_user = 'blabos';
my $pg_pass = 'blabos';
my $pg_dbh  = DBI->connect( $pg_dsn, $pg_user, $pg_pass );

Aqui, de forma bem vertical pra ser mais fácil de visulalizar, fazemos a conexão com o banco e obtemos um database handler que guardamos da variável $pg_dbh. É através desse handler que vamos chamar os métodos de acesso ao banco do DBI.

## conexão ao mariadb
my $my_host = '127.0.0.1';
my $my_port = '4002';
my $my_db   = 'mydb_sample';
my $my_dsn  = "DBI:MariaDB:database=$my_db;host=$my_host;port=$my_port";
my $my_user = 'blabos';
my $my_pass = 'blabos';
my $my_dbh  = DBI->connect( $my_dsn, $my_user, $my_pass );

Analogamente, conectamos no banco e obtemos o handler em $my_dbh.

Se você estiver executando o exemplo via docker-compose, por conta de uma especificidade do mariadb, é preciso utilizar o ip ao invés no nome localhost.

As diferenças ficam por conta das duas DSN que determinam qual é o driver (DBI::Pg ou DBI::MariaDB) e as opções específicas de cada um.

## pegando a lista de estudantes no postgres
my $clients = $pg_dbh->selectall_arrayref(
    'select * from "pgdb_schema"."clients" order by name',
    { Slice => {} },
);

## pegando o histórico no mariadb
my $history = $my_dbh->selectall_arrayref(
    'select * from course_history where grade > 5.0',
    { Slice => {} },
);

No trecho acima utilizamos o mesmo método selectall_arrayref() pra executar as queries nos bancos, mas cada uma a partir do handler correspondente ($pg_dbh ou $my_dbh).

Em ambos os casos, o resultado vai ser um ArrayRef onde cada elemento é um HashRef correspondente a uma linha do resultado.

Na primeira query pegamos todos os alunos da base do Departamento de Clientes e na segunda, todos os cursos cujos alunos tiveram nota maior que 5.0 na base do Departamento de Cursos.

Por fim, processamos o resultado e geramos o CSV com o código abaixo:

## gerando o relatório!
my @rows = ();
foreach my $student ( $clients->@* ) {
    my @courses =
      map  { $_->{course} }                         ## 3. pega o nome do curso
      grep { $_->{student} eq $student->{user} }    ## 2. filtra para este estudante
      $history->@*;                                 ## 1. pega todos os cursos

    ## estrutura de dados para o csv
    push @rows,
      {
        name    => $student->{name},
        email   => $student->{email},
        courses => join( ', ', @courses ),
      };

    ## apenas mostrando na tela
    say '=' x 80;
    say "Parabéns $student->{name} por sua aprovação em:";
    say '  * ' . $_ foreach @courses;
}

## uma última linha na tela
say '=' x 80;

## gerando o arquivo csv
csv( in => \@rows, out => 'report.csv' );

Iteramos pela lista de alunos e filtramos os cursos de cada um, montando os dados do CSV com as colunas name, email e course.

Depois de iterar pelos dados e popular o array @rows, geramos o arquivo report.csv no diretório corrente, utilizando a função csv() que mostramos no post anterior.

user@host cat report.csv
email,courses,name
beltro@examle.com,"COBOL for Dummies","Beltrano de Souza"
blabos@gmail.com,"Introduction to Perl, Mastering Regexes","Blabos de Blebe"
fulano.tal@email.com,"Basics of Machine Learning","Fulano de Tal"

Note como a função csv() do Text::CSV_XS é espertinha o suficiente para lidar corretamente com os casos onde há vírgulas (mais de um curso).

Conclusão

No post de hoje mostramos como usar o módulo DBI para acessar mais de um banco de dados diferente, de forma uniforme, utilizando o mesmo conjunto de métodos.

Com a API padronizada do DBI você não precisa se preocupar em aprender como lidar com bibliotecas diferentes toda vez que precisar acessar um banco de dados diferente.

Notas

[1]. Sim, sabemos que hoje em dia não é uma boa prática deixar todo mundo sair conectando diretamente no banco de dados, mas você se surpreenderia com a quantidade de casos reais onde esse exemplo fictício é uma necessidade...