O caso da unicidade seletiva

Oracle é um grande banco de dados não só pela sua robustez e escalabilidade mas também pelos recursos de projeto físico que oferece.

Vejamos, por exemplo, o caso de certa aplicação que tudo o que precisava era de um repositório de dados para guardar os grupos e as tarefas associadas a cada grupo, bem como o status de cada projeto.

Após algum tempo de estudo das regras de negócio, o projetista de banco de dados propôs a seguinte tabela:

SQL> create table PROJECT
  2  (PROJECT_ID number primary key,
  3  TEAMID number,
  4  JOB varchar2(100),
  5  STATUS varchar2(20) check (status in ('ACTIVE', 'INACTIVE')));

A proposta cobria praticamente todas as regras de negócio, a saber:

- em um dado projeto há apenas um time trabalhando em um determinado job;

- um time pode trabalhar em diferentes projetos e jobs ao mesmo tempo

- projetos estao necessariamente em um de dois status: ativos or inativos

Porém , o que esta proposta ainda carecia é atender a uma outra regra de negócio:

- o job tem que ser único dentro do mesmo time para projetos ativos.

Em outras palavras: TEAMID e JOB tem que ser unique key quando STATUS=ACTIVE.

Como implementar esta regra de negócio?

Buenas, para ser justo é preciso dizer que certamente a regra de negócio poderia ser implementada na aplicação. Mas, neste caso, toda e qualquer aplicação teria que construir a mesma implementação de novo. Isto não é raro de acontecer. Casualmente, no momento em que escrevo este artigo trabalho em um projeto onde o mesmo banco de dados Oracle é acessado por aplicações Java, ASP e Oracle Forms!

Focando então no banco de dados de novo, temos a seguintes alternativas:

Database triggers

Database triggers são recursos disponíveis em Oracle desde há muito tempo. São ferramentas absolutamente úteis, desde que o projetista atente ao potencial problema de performance e ao infame problema das “mutating tables”.

O potencial problema de performance advém do fato de que Oracle triggers não são compilados. Isto significa, em especial, que todo e qualquer acesso ao banco de dados efetuado por um database trigger passa pela fase de “hard parse”. Dependendo dos requisitos de performance e escalabilidade da aplicação, isto pode ser um real fator inibidor de uso do recurso. Uma conhecida técnica para driblar este problema de performance é criar uma stored procedure com o código que se deseja rodar e colocar no corpo do trigger apenas a chamada à stored procedure. Como stored procedures são compiladas, a escalabilidade deste design é muito superior.

O problema de “mutanting table” é um velho conhecido de que tem alguma experiência com Oracle. Basicamente, se uma tabela está no meio de uma transação, não é possível codificar um database trigger que lê a mesma tabela durante a transação. Existem recursos de código para driblar esta restrição mas, francamente, são bastante deselegantes e adicionam indesejada complexibilidade à manutenção da aplicação.

Outra desvantagem deste design é que requer a construção de código para todas as operações DML (insert, delete, update).

Instead-of triggers

Apesar de que atualmente em IT basta existir para se ser declarado velho, este é um recurso relativamente novo em Oracle. Trata-se de uma ferramenta que permite ao projetista de banco de dados desenhar um mecanismo para capturar uma solicitação de um comando DML efetuada pela aplicação e definir que ações vão realmente acontecer no banco de dados.

Quando combinado com views é definitivamente um recurso que se deseja prestar atenção.

Por exemplo, no caso deste artigo, o seguinte design poderia ser implementado:

- uma view - PROJECT_VIEW - é criada com base na table PROJECT

SQL> create view project_view as select * from project;

- um ou mais instead-of triggers são associados à view. Estes triggers capturam os comandos DML. No caso de um insert, o trigger primeiro acessa a tabela PROJECT para verificar se o time já está associado ao job em um outro projeto ativo. Se não é o caso, então o trigger dispara um DML de insert na tabela PROJECT. Um exemplo de como este trigger poderia ser criado é fornecido abaixo:

create or replace trigger team_job_uniqueness
instead of insert on PROJECT_VIEW
for each row
declare
  v_count number;
begin
  if (:new.status = 'ACTIVE') then
   begin   
     select count(*)
     into v_count
     from project
     where teamid = :new.teamid
     and job = :new.job
     and status = 'ACTIVE';
     if (v_count > 0) then
       raise_application_error(-20001, 'Unicidade seletiva nao respeitada');
     end if;
   exception
     when others then
       raise_application_error(-20002, 'Problemas no acesso a tabela PROJETO');
   end;
  end if;
  begin
   insert into project(project_id, teamid, job, status) values (:new.project_id, :new.teamid, :new.job, :new.status);
  exception
   when others then
       raise_application_error(-20003, 'Problemas para criar novo PROJETO');
  end;
end;

- à aplicação são dados privilégios de acesso à view PROJECT_VIEW mas não à tabela PROJECT.

Este design evita o enervante problema das “mutanting tables” de uma maneira mais elegante mas ainda assim é bastante verborrágica, cheia de código para se manter.

Function-based indexes (FBIs)

A vida devia ser simples e fácil. É por isto que Oracle implementou - desde a versão 8i (8.1) - a noção de índices com base em expressões/funções criadas pelo projetista/desenvolvedor.

FBIs permitem, por exemplo, a criação de índices como o apresentado abaixo:

SQL> CREATE UNIQUE INDEX
  2  JOB_UNIQUE_IN_TEAMID ON PROJECT
  3  (CASE WHEN STATUS = 'ACTIVE' THEN TEAMID ELSE NULL END,
  4  CASE WHEN STATUS = 'ACTIVE' THEN JOB ELSE NULL END);

O resultado da existência de um índice assim criado em nosso estudo de caso é que

- apenas para projetos ativos serão criadas entradas no índice

- o fato de que o índice é UNIQUE garante que um time jamais trabalhará em um mesmo job ao mesmo tempo (para projetos ativos).

É simples de implementar e de manter, tem mínimo impacto em aplicações OLTP, é enxuto (pois existirão entradas neste índice apenas projetos ativos), é furiosamente rápido para recuperar a informação, não requer extra select para garantir a regra de negócio e é um mecanismo mantido e gerenciado pelo núcleo do banco de dados (já ví calejados projetistas de banco de dados chorando ao serem apresentados à esta técnica de design).

Conclusão

Não há nenhuma grande novidade a se descobrir aqui. O que vale é a velha regra de que para criar o projeto físico de um banco de dados a melhor opção é conhecer os recursos existente no banco de dados em questão. Eles variam imensamente de um bd para outro e entre versões do mesmo bd.

Claro que se portabilidade de bd é um requisito importante esta conclusão deve ser repensada. Agora... quantas vêzes realmente isto acontece? Já perdi o número de vezes em que ví projeto de desenvolvimento de aplicações VB em Windows se recusando a usar recursos específicos de Oracle devido a uma portabilidade que ninguém pediu. VB é proprietário, Windows é proprietário mas... o bd tinha que ser portável...

[Home] [IT] [Oracle/Db] [SD] [PM] [Lendas Corporativas] [Diversas] [The Secret of Life] [About IT talks]