History Tables, denovo.

Boa noite pessoal.

Uns posts atrás falei sobre uma função que permitisse e criasse automaticamente as regras e tabelas para armazenamento de registros históricos no PostGIS.

Bem, aqui vão algumas questões: O PostgreSQL nos permite lidar com este tipo de situação de duas formas: Rules e/ou Triggers.

No caso do nosso projetinho, foram utilizadas rules para lidar com o que precisamos fazer. Bem, o que deve ter um histórico de feições?

Primeiramente ele deve guardar todas as feições inseridas, atualizadas e deletadas, bem como a data, hora, nome do usuário que realizou a alteração, e uma coisa interessante, qual é a versão atual daquela feição. Desta forma podemos rastrear quais foram as alterações históricas de cada versão e caso preciso, achar a versão mais apropriada e restaurá-la para o banco de dados principal.

Além disso, temos um registro histórico de fato. Sabemos todas as alterações de uma feição. É possível entender como um lote mudou, por exemplo, ou como um animal rastreado por GPS está se movimentando. Outra coisa bacana, podemos saber como está evoluindo a cobertura vegetal de uma determinada região. Aumentou? Diminuiu? Não existe ambiguidade ou imperícia. Tudo pode ser restaurado e analisado de acordo com uma dimensão extra, o tempo.

Bem, vou postar aqui a estrutura de uma tabela histórica:

1. CREATE TABLE foo_history(
2. history_id serial not null,
3. date_created timestamp not null default now(),
4. date_deleted timestamp default null,
5. operacao varchar(20) not null,
6. usuario varchar(80) not null default current_user,
7. versao_atual varchar(80),
   
8. LIKE foo,
9. CONSTRAINT foo_history_pk PRIMARY KEY(history_id));

É mais ou menos isso. Deêm uma olhada na linha 8, a expressão LIKE foo. Ela diz ao PostgreSQL que ele deve procurar a estrutura da tabela foo, e copiar ela todinha embaixo. Legal não?

Imagine a tabela foo:

1. CREATE TABLE foo(
2. fid serial not null,
3. classe_vegetacao varchar(30) not null,
4. CONSTRAINT foo_pk PRIMARY KEY(fid));

Nossa tabela foo_history final seria:

1. CREATE TABLE foo_history(
2. history_id serial not null,
3. date_created timestamp not null default now(),
4. date_deleted timestamp default null,
5. operacao varchar(20) not null,
6. usuario varchar(80) not null default current_user,
7. versao_atual varchar(80),
   
8. fid integer,
9. classe_vegetacao varchar(30),
   
10. CONSTRAINT foo_history_pk PRIMARY KEY(history_id));

*Uma particularidade: qualquer campo do tipo SERIAL (como fid, na tabela foo) é realmente do tipo INTEGER. Quando declaramos SERIAL, o PostgreSQL entende que deve criar uma sequência específica para aquele campo e escolhe o valor padrão para aquele campo o próximo valor daquela sequência.

Bem, para começar precisamos de um código SQL que gere automaticamente este código acima. Ele precisa gerar a tabela sem que o usuário digite nada, apenas escolha a tabela que ele quer montar um registro histórico.

Nosso código também precisa montar automaticamente as regras que vão realizar a gestão desta tabelinha acima. Lembre-se que precisamos de uma regra para cada tipo de modificação na tabela, uma para INSERT, uma para UPDATE e uma para DELETE.

Cada uma tem uma particularidade, pois as alterações na tabela histórica não são as mesmas. A regra para delete, por exemplo, não deve criar novo registro na série histórica, apenas indicar que o registro foi deletado.

Não é muito complicado, mas também não é muito simples. Daqui uns dias falo deste códigozinho e dou umas dicas sobre ele.