Shell + Oracle + GNUPlot = gráficos legais

Recentemente estava pensando na possibilidade de visualizar de forma gráfica algumas informações de meu controle financeiro. Os dados já estavam todos lá no meu banco de dados. Bastava extraí-los de forma gráfica. Precisava de uma ferramenta para isto. Queria algo livre. Como resolver?

Bom, já havia mexido com o Jasper + iReport àlgum tempo atrás. Mas fiz pouca coisa. Vamos ver se resolvo de forma rápida. Abri, tentei, perdi a paciência e desisti!

Em função do meu mestrado em andamento, tenho usado muito o Gnuplot (vejam as páginas de exemplo para terem uma idéia do potencial da ferramenta) e tenho criado alguns gráficos bem interessantes. Além disso, sou fã de carteirinha de shell script. Os dados que precisava extrair eram facilmente resolvidos com um select ridículo. Opa, espera lá, eu posso unir as ferramentas e fazer isto! Algumas horas depois estava pronto meu script gerando o gráfico que eu queria.

Gostei da solução e resolvi compartilhar aqui. Para fins didáticos, modifiquei os dados que seleciono, permitindo o uso do schema do scott (uma base de demonstração/aprendizado disponível em bancos Oracle).

Usei o exemplo com Oracle pois meus dados estão neste banco, que uso no meu dia-a-dia. Mas pode ser facilmente adaptado para outros banco de dados (MySQL ou PostgreSQL, por exemplo).

Este é o gráfico gerado pelo script. E aqui está o código do script (se preferir, baixe diretamente):

#!/bin/sh

#

# gera_grafico_scott.sh - Gerar em formato grafico salario de um intervalo de
#                         funcionarios.
#
# 10/11/2008  Daniel Bauermann

#############
### Variaveis
GNUPLOT="/usr/bin/gnuplot"
ARQUIVO_PLOT="/tmp/graf.plot"
ARQUIVO_DAT="/tmp/graf.dat"
ARQUIVO_EPS="/tmp/graf.eps"
ARQUIVO_PDF="/tmp/graf.pdf"
ARQUIVO_TMP="/tmp/graf.tmp"
EPS2PDF="/usr/share/texmf/bin/epstopdf"
# Oracle
export ORACLE_BASE=/oracle/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/oratools
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
export PATH=$PATH:$ORACLE_HOME/bin
# Outras (usado para gerar formato numerico adequado para gnuplot)
unset LANG

##################
### Funcoes locais
# Arquivo Plot - instrucoes para geracao do grafico
ArquivoPlot() {
   cat <<!FIM
#!${GNUPLOT} -persist
reset
set xrange [0.5:=CAMPO1=.5]     # define um intervalo ligeiramente superior para fins visuais (eixo X)
set title "=CAMPO2="            # titulo de grafico (sera alterado posteriormente)
set yrange [0:*]                # intervalo do eixo Y
unset xtics                     # nao mostra linhas para valores do eixo X
set boxwidth 0.8 relative       # define tipo de grafico
set style fill solid 0.5
set key outside below
set terminal postscript eps enhanced color butt "Helvetica" 10
### Multiplos graficos
set size 1.0,1.0
set multiplot
### Grafico 1 - Soma salarios
set size 1.0,0.55
set origin 0.0,0.45
set ylabel "Soma R$"
# traca grafico usando colunas 1 e 2 arquivo dados (1=departamento 2=total)
plot "${ARQUIVO_DAT}" using 1:2 notitle with boxes, "" using 1:2:2 notitle with labels
### Grafico 2 - Media salarial
unset title
set xtics (=CAMPO3=)            # associa numeros aos nomes
set size 1.0,0.45
set origin 0.0,0.0
set ylabel "Media R$"
# traca grafico usando colunas 1 e 3 arquivo dados (1=departamento 3=media)
plot "${ARQUIVO_DAT}" using 1:3 notitle with boxes lt 2, "" using 1:3:3 notitle with labels
unset multiplot
!FIM
}

# Conecta banco, executa rotina
Oracle() {
   ### Variaveis
   USUARIO="scott/tiger@banco9i"
   sqlplus -S ${USUARIO} <<!FIM
set serveroutput on
set linesize 2000
declare
   cursor c_dados (w_inicial in number, w_final in number) is
     select dept.dname, sum(emp.sal) total, avg(nvl(emp.sal,0)) media
       from dept dept, emp emp
       where dept.deptno between w_inicial and w_final
       and dept.deptno = emp.deptno
       group by dept.dname
       order by 1;
   --
   v_departamentos number(3) := 0;
   v_titulos varchar2(2000) := '';
begin
   for reg in c_dados(to_number('$1'), to_number('$2')) loop
      -- os departamentos serao representados por numeros sequenciais
      v_departamentos:=(v_departamentos+1);
      -- armazena o numero e o numero correspondente ao departamento
      v_titulos:=v_titulos||',"'||reg.dname||'" '||to_char(v_departamentos);
      -- imprime numero, total do salario e media salaria do departamento
      dbms_output.put_line(to_char(v_departamentos)||chr(9)||
                           ltrim(to_char(reg.total, '999999990D00'))||chr(9)||
                           ltrim(to_char(reg.media, '999999990D00')));
   end loop;
   -- cria separador
   dbms_output.put_line('[corte1]');
   -- quantidade total de departamentos (usada para definir eixo X)
   dbms_output.put_line(v_departamentos);
   -- titulo do grafico
   dbms_output.put_line('Intervalo '||
                        to_char(to_number('$1'), '0000')||
                        ' a '||
                        to_char(to_number('$2'), '0000'));
   -- legendas para departamentos (substituido no arquivo plot)
   dbms_output.put_line(substr(v_titulos, 2));
   -- novo separador
   dbms_output.put_line('[corte2]');
end;
/

quit
!FIM
}

# Trata dados gerados em temporario, gerando arquivo de dados
GeraDat() {
   LINHAS=`grep -n "\[corte1\]" ${ARQUIVO_TMP} | cut -d":" -f1`
   let LINHAS=${LINHAS}-1
   head -n ${LINHAS} ${ARQUIVO_TMP} > ${ARQUIVO_DAT}
}

# Trata dados gerados em temporario, gerando arquivo plot
GeraPlot() {
   LINHAS=`grep -n "\[corte1\]" ${ARQUIVO_TMP} | cut -d":" -f1`
   let LINHAS=${LINHAS}+1
   # quantidade total de departamentos (usada para definir limites eixo X)
   CAMPO1=`head -n ${LINHAS} ${ARQUIVO_TMP} | tail -n 1`
   let LINHAS=${LINHAS}+1
   # titulo do grafico
   CAMPO2=`head -n ${LINHAS} ${ARQUIVO_TMP} | tail -n 1`
   let LINHAS=${LINHAS}+1
   # legenda para departamentos (nome e numero relativo ao departamento)
   CAMPO3=`head -n ${LINHAS} ${ARQUIVO_TMP} | tail -n 1`
   ArquivoPlot > ${ARQUIVO_TMP}
   cat ${ARQUIVO_TMP} | sed "s;=CAMPO1=;${CAMPO1};" | sed "s;=CAMPO2=;${CAMPO2};" | sed "s;=CAMPO3=;${CAMPO3};" > ${ARQUIVO_PLOT}
}

#############
### Principal
# Verifica informacao de parametros
if [ "x" == "x$1" -o "y" == "y$2" ]
then
   echo -e "\tUse:\t`basename $0` <departamento_inicial> <departamento_final>"
   exit 1
fi

# Gera dados a partir de banco de dados e faz conversoes de arquivos
Oracle $1 $2 > ${ARQUIVO_TMP}
GeraDat
GeraPlot
${GNUPLOT} ${ARQUIVO_PLOT} > ${ARQUIVO_EPS}
${EPS2PDF} ${ARQUIVO_EPS}

# Verifica onde usuario deseja salvar arquivo
AUX=$( dialog --stdout --inputbox 'Informe arquivo destino:' 0 0 ${ARQUIVO_PDF} )
if [ ${AUX} != ${ARQUIVO_PDF} ]
then
   mv ${ARQUIVO_PDF} ${AUX}
fi
xpdf -geometry 1020x720 -z page ${AUX}

# Remove arquivos
for ARQ in ${ARQUIVO_PLOT} ${ARQUIVO_DAT} ${ARQUIVO_EPS} ${ARQUIVO_TMP}
do
   if [ -f ${ARQ} ]
   then
      rm -f ${ARQ}
   fi
done

Gostaria de ter usado a dica do Tárcio Zemel para formatação do código. Mas parece que só poderei ativar isto quando eu mesmo hospedar meu WordPress.

Acredito que o código possua comentários suficientes para ser auto-explicativo. Mas qualquer dúvida é só me contatar.

Ah, o canivete suiço do shell do Aurélio, como sempre, foi uma mão na roda! Valeu Aurélio!

« Reciclar é a solução?

Doiseu Mimdoisema »