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