APGDIFF: Ferramenta Mediana que pode ser Útil!

Basta um momento de descuido para termos várias versões de um mesmo banco de dados em funcionamento. Identificar os pontos de alteração em esquemas de bancos de dados manualmente é mais do que cansativo: é arriscado.

Para solucionar estes problemas, existem várias ferramentas para comparação entre esquemas de banco de dados, dentre elas a "Another PostgreSQL Diff Tool", também chamada (apgdiff). É uma ferramenta livre que apresenta versão gratuita na web e atualmente está em sua versão 2.4. 

Neste post é mostrado o funcionamento básico da versão web da ferramenta, que é bem simples, e são colocadas as primeiras impressões na sua utilização.

* Funcionamento

A operação da ferramenta é bem simples:
- Inicialmente, acesse o sítio da ferramenta;
- Faça o backup dos bancos de dados que se deseja comparar. Com o utilitário pg_dump a sintaxe poderia ser: pg_dump -U postgres -v -f teste_atual.txt postgres (extraindo o banco de dados postgres para o arquivo txt teste_atual.txt)
- Uma vez que tenha extraído o backup dos dois bancos de dados a comparar, acione a opção "Create Diff online" e faça o upload dos arquivos de backup obtidos
- Acione a opção de comparação de esquemas. Abaixo, colocamos um exemplo passo a passo.

Tela inicial

Inclusão de arquivos para comparação

Diferença entre os esquemas

* Primeiras impressões

O site foi bastante rápido em suas análises, mas não detectou todas as poucas alterações realizadas.

No teste foi acrescentado um novo usuário, o que não foi detectado pela ferramenta. A chave primária da tabela incluída também não foi encontrada pela apgdiff. A ferramenta também apresentou como diferente uma chave primária que na verdade estava igual em ambos os esquemas.

A primeira impressão é que a apgdiff é interessante, mas está longe de ser perfeita. A análise dos backups mostrou que podem ser deixados de lado detalhes importantes, o que não tira sua importância como potencial ferramenta auxiliar.

O trabalho do DBA, ajudado por scripts próprios e outras ferramentas, seguramente pode se beneficiar dos recursos da apgdiff. Mas como qualquer ferramenta, esta apresenta limitações, algumas das quais identificadas neste post.

Range Types: Novo recurso do Postgresql 9.2!

Início e fim, começo e encerramento. Armazenar intervalos de valores é uma tarefa importante que já estava disponível no Postgresql, porém de modo mais dispendioso em termos de programação. Era possível por exemplo criar dois campos indicando os extremos de um intervalo sem problemas, implementar uma função ou ainda criar um tipo intervalar com o comando CREATE TYPE.

Representação Matemática de Intersecção de Intervalos de Valores

A versão 9.2 apresenta o conceito de "Range Types", que engloba um tipo de dados específico para intervalos, além de recursos para cálculos e manipulações relacionadas a estes tipos peculiares de dados. Considero este um grande avanço, que pode reduzir o esforço de implementação e aumentar o desempenho em várias situações. Pode-se por exemplo, indexar um campo intervalar.

Neste post, embora não se busque esgotar o tema, vamos ilustrar as principais possibilidades desta nova feature com exemplos.

* Tipos de Intervalos

O postgresql apresenta seis tipos de intervalos padrão, sendo alguns discretos e outros contínuos, mas você pode criar outros utilizando CREATE TYPE:

- int4range — Intervalo de inteiros de 4 bytes.
- int8range — Intervalo de "bigint" (inteiros de 8 bytes)
- numrange — Intervalo de números reais
- tsrange — Intervalo de timestamps sem time zone
- tstzrange — Intervalo de timestamps com time zone
- daterange — Intervalo de datas

* Operações Básicas para Definir Dados Intervalares

Um intervalo consiste em um conjunto de valores delimitados por um valor inicial e um final. O postgres oferece opção de se trabalhar com intervalos total e parcialmente limitados e não limitados. Os delimitadores compreendem colchetes "[]" para os intervalos fechados e parênteses "()" para os intervalos abertos.

 Intervalos abertos e fechados

Abaixo segue uma sequência de exemplos ilustrando as principais operações feitas com intervalos e os resultados obtidos.

Exemplo 1: Intervalo fechado contendo os números 1 a 5.
postgres=# SELECT '[1,5]'::numrange;
 numrange
----------
 [1,5]
(1 registro)

Exemplo 2: Intervalo fechado em campo inteiro, contendo os números de 1 a 5.
postgres=# SELECT '[1,5]'::int4range;
 int4range
-----------
 [1,6)
(1 registro)

Exemplo 3: Intervalo de 1 a 5, aberto no 5, isto é, não contendo o valor 5.
postgres=# SELECT '[1,5)'::numrange;
 numrange
----------
 [1,5)
(1 registro)

Exemplo 4: Intervalo sem limite máximo.
postgres=# SELECT '[1,]'::numrange;
 numrange
----------
 [1,)
(1 registro)

Exemplo 5: Intervalo sem limite mínimo.
postgres=# SELECT '(,1]'::numrange;
 numrange
----------
 (,1]
(1 registro)

Exemplo 6: Duas formas de expressar um intervalo sem quaisquer limites.
postgres=# SELECT '(,)'::numrange, numrange(null, null);
 numrange | numrange
----------+----------
 (,)      | (,)
(1 registro)

Exemplo 7: Intervalos de 1 a 5, abertos e fechados, criados a partir do construtor do tipo numrange.postgres=# SELECT numrange(1,5,'[)'), numrange(1,5,'(]'), numrange(1,5,'[]'), numrange(1,5,'()');
 numrange | numrange | numrange | numrange
----------+----------+----------+----------
 [1,5)    | (1,5]    | [1,5]    | (1,5)
(1 registro)

Exemplo 8: Duas formas de expressar um intervalo sem elementos.postgres=# SELECT numrange(1,1,'[)'), '[1,1)'::numrange;
 numrange | numrange
----------+----------
 empty    | empty
(1 registro)

Exemplo 9: Intervalo em campo data.
postgres=# SELECT daterange('12/01/2012',current_date,'[)');
        daterange       
-------------------------
 [2012-01-12,2012-10-17)
(1 registro)

Exemplo 10: Intervalo em campo data, exemplo 2.
postgres=# SELECT daterange(current_date -10,current_date,'[)');
        daterange       
-------------------------
 [2012-10-07,2012-10-17)
(1 registro)

Exemplo 11: Teste de pertencimento de elemento a um intervalo.
postgres=# SELECT int4range(10, 20, '[]') @> 9, int4range(10, 20,'[]') @> 15, int4range(10, 20,'[]') @> 21;
 ?column? | ?column? | ?column?
----------+----------+----------
 f        | t        | f

Exemplo 12: Recuperando Limites Superior e Inferior de um Intervalo
postgres=# SELECT lower(numrange(10, 100, '[]')), upper(numrange(10, 100, '[]'));
 lower | upper
-------+-------
    10 |   100
(1 registro)

Exemplo 13: Verificação de sobreposição de intervalos
É feita com o operador &&.

postgres=# SELECT numrange(10, 20) && numrange(25, 30), daterange('01/01/2010', '31/12/2011') && daterange('01/01/2011', '31/12/2012');
 ?column? | ?column?
----------+----------
 f        | t
(1 registro)

Exemplo 14: Intersecção de Intervalos
O teste é feito com o operador *, retornando 'empty' caso a intersecção não apresente elementos.

postgres=# SELECT int4range(10, 20,'[]') * int4range(15, 25,'[]'),daterange('2010-01-01','2012-06-30','[]') * daterange('2011-01-01','2012-12-30','[]');
 ?column? |        ?column?        
----------+-------------------------
 [15,21)  | [2011-01-01,2012-07-01)
(1 registro)

postgres=# SELECT int4range(10, 20) * int4range(25, 35);
 ?column?
----------
 empty
(1 registro)

Exemplo 15: Teste se intervalo é vazio (empty)

postgres=# SELECT isempty(numrange(10, 20)), isempty(numrange(10, 10));
 isempty | isempty
---------+---------
 f       | t
(1 registro)

Exemplo 16: Criação de Tabelas e Visões e Índices com Intervalos

postgres=# CREATE TABLE teste_range (rang_data daterange, rang_int4 int4range, rang_int8 int8range, rang_num numrange, rang_timestamp tsrange);
CREATE TABLE
postgres=# CREATE TABLE teste_range_2 (rang_data daterange PRIMARY KEY, rang_int4 int4range UNIQUE, rang_int8 int8range, rang_num numrange, rang_timestamp tsrange);
NOTA:  CREATE TABLE / PRIMARY KEY criará índice implícito "teste_range_2_pkey" na tabela "teste_range_2"
NOTA:  CREATE TABLE / UNIQUE criará índice implícito "teste_range_2_rang_int4_key" na tabela "teste_range_2"
CREATE TABLE
postgres=# CREATE OR REPLACE VIEW view_teste_range AS SELECT * FROM teste_range ORDER BY rang_data;
CREATE VIEW
postgres=# CREATE INDEX ind_teste_range ON teste_range (rang_data, rang_int4);
CREATE INDEX
postgres=# INSERT INTO teste_range (rang_data, rang_int4) VALUES (daterange('2010-01-01','2012-06-30','[]'), int4range(10, 20,'[]'));
INSERT 0 1
postgres=# select * from TESTE_RANGE;
        rang_data        | rang_int4 | rang_int8 | rang_num | rang_timestamp
-------------------------+-----------+-----------+----------+----------------
 [2010-01-01,2012-07-01) | [10,21)   |           |          |
(1 registro)

* Pontos Fortes

- Programas, funções e consultas que utilizem intervalos ficam menores.
- Opções para manipulação de intervalos são confiáveis e não demandam pluigins ou instalação de novos componentes.

* Pontos Fracos

- Perda de compatibilidade com outros SGBDs em todas as funcionalidades que utilizem intervalos.