Minha saga e dos meus colegas de projeto com o Oracle continua.
Estamos tentando configurar um framework web (o Rails)
para que ele interfira o mínimo possível na definição original das
tabelas do nosso banco de dados. Ela é uma base Oracle com convenções
próprias que, idealmente, não deveria ter seu esquema modificado de
forma a agradar nenhum framework. Não deveria.
Mas eis que o Rails não suporta tabelas com chaves primárias compostas
(e a extensão Composite Primary Keys, que resolveria o problema,
atualmente não está funcionando). Para quem não tem muita experiência
com banco de dados, vou ilustrar com um exemplo de uma tabela
que possui uma chave primária composta de 2 colunas (e não 1,
que seria o ideal para se trabalhar com o Rails).
Em uma instituição de ensino, por exemplo, poderíamos
ter alunos e cursos (disciplinas) vinculados entre si por meio
da seguinte tabela:
create table matricula (
cd_estudante int REFERENCES estudante (cd_estudante),
cd_curso int REFERENCES curso (cd_curso),
nota number,
CONSTRAINT matricula_pk PRIMARY KEY (cd_estudante, cd_curso)
);
Nossa chave primária é, portanto, composta. Para que o Rails consiga
interagir com essa tabela é necessário que ela tenha 1 única chave primária
(neste caso, uma chave artificial, que chamarei de cd_artificial).
Os campos cd_estudante e cd_curso podem continuar formando uma dupla
de campos únicos, com a adição de uma restrição de integridade. Nossa
tabela “Rails friendly” deveria ter, portanto, a seguinte definição:
create table matricula (
cd_artificial int,
cd_estudante int REFERENCES estudante (cd_estudante),
cd_curso int REFERENCES curso (cd_curso),
nota number,
CONSTRAINT matricula_pk UNIQUE (cd_estudante, cd_curso)
);
O problema do nosso projeto é que algumas das tabelas que precisam sofrer
essa metamorfose já possuem milhões de registros (ou seja, definir
uma nova tabela, migrar os dados da antiga para a nova e remover a antiga
custa muito tempo).
Nossa única regalia é poder parar o sistema para realizar as modificações.
O que segue abaixo, portanto, é um passo-a-passo de como
transformar aquela primeira definição de matricula na segunda, garantindo
que, após o procedimento:
- Todos os registros tenham um
cd_artificial populado corretamente
- Qualquer código-fonte existente que manipule a tabela continue funcionando,
pois não precisará saber da existência de
cd_artifical
- Novos registros possam ser inseridos tanto a partir da aplicação web, quanto
a partir do sql*plus, sem que um valor para o campo
cd_artificial precise ser
definido explicitamente.
Para ilustrar melhor o exemplo, apresento agora a definição de todas
as 3 tabelas do sistema fictício, e também os registros inseridos para possibilitar
o teste do procedimento.
create table estudante (
cd_estudante int primary key,
nm_estudante char(30)
);
create table curso (
cd_curso int primary key,
nm_curso char(30)
);
create table matricula (
cd_estudante int references estudante (cd_estudante),
cd_curso int references curso (cd_curso),
nota number,
constraint matricula_pk primary key (cd_estudante, cd_curso)
);
insert into estudante values (1, 'Caio');
insert into curso values (1, 'AWK');
insert into curso values (2, 'PHP');
insert into curso values (3, 'Python');
insert into curso values (4, 'Ruby');
insert into matricula values (1, 1, 5);
insert into matricula values (1, 2, 9);
Observe as colunas e os registros da tabela matricula, para poder
comparar com a versão final:
SQL> select * from matricula;
CD_ESTUDANTE CD_CURSO NOTA
------------ ---------- ----------
1 1 5
1 2 9
Lembre-se de que os passos a seguir se aplicam somente ao Oracle. Se você
estiver utilizando MySQL o procedimento é muito mais simples, e não envolve
a criação nem de uma seqüência e nem de um gatilho. Provavelmente no PostgreSQL
as coisas devem ser mais simples, mas não tenho o conhecimento necessário
para dizer com certeza.
Vamos aos passos.
Passo 1 - Remover a restrição de chave primária de cdestudante,cdcurso
Para que a nova chave primária possa ser definida, a antiga deve ser eliminada.
alter table matricula drop constraint matricula_pk;
Passo 2 - Definir a restrição unique para cdestudante,cdcurso
A nossa antiga chave primária garantia que um mesmo aluno não poderia ser vinculado
ao mesmo curso mais de uma vez. Como a chave foi eliminada, essa restrição de integridade
não existe mais. Precisamos defini-la novamente.
alter table matricula add constraint matricula_pk unique(cd_estudante,cd_curso);
Passo 3 - Adicionar a coluna cd_artificial na tabela
Agora podemos adicionar a coluna que se tornará nossa nova chave primária.
alter table matricula add cd_artificial numeric(10);
Note que, neste momento, a coluna cd_artificial não possui valores para nenhum
dos registros de matricula:
CD_ESTUDANTE CD_CURSO NOTA CD_ARTIFICIAL
------------ ---------- ---------- -------------
1 1 5
1 2 9
Passo 4 - Popular a coluna cd_artificial
Precisamos, portanto, popular a coluna recém-criada. Neste caso queremos atribuir valores
a partir de 1 até o número total de registros da tabela.
update matricula set cd_artificial = rownum;
Veja o resultado:
CD_ESTUDANTE CD_CURSO NOTA CD_ARTIFICIAL
------------ ---------- ---------- -------------
1 1 5 1
1 2 9 2
Passo 5 - Adicionar a restrição de chave primária à coluna cd_artificial
Agora que a nova coluna já possui dados, ela pode se tornar nossa chave primária.
alter table matricula add constraint matricula_pk primary key (cd_artificial);
Passo 6 - Definir a seqüência que populará o campo cd_artificial
Campos auto incrementais não existem nativamente no Oracle da forma como existem
no MySQL. Eles precisam ser definidos com um pouco mais de esforço: deve-se
criar uma seqüência e um gatilho.
A seqüência fica responsável por guardar, de forma transacional, um número atual,
e gerar um próximo número quando solicitada. Neste caso, como nossa tabela
já possui 2 registros, precisamos que a seqüência inicie em 3:
create sequence matricula_seq start with 3;
Passo 7 - Popular o campo cd_artificial automaticamente
O gatilho (trigger) será responsável por interceptar a inserção de um registro
na tabela matricula. Todo registro terá seu campo cd_artificial definido
como matricula_seq.nextval antes da inserção (before insert),
sem que seja preciso defini-lo explicitamente.
create or replace trigger matricula_pk_trig
before insert
on matricula
for each row
begin
select matricula_seq.nextval
into :new.cd_artificial
from dual;
end;
/ -- Esta contra-barra marca o fim da declaração da trigger.
Na verdade, é possível forçar um valor diferente de matricula_seq.nextval
para o novo registro, desde que esse novo valor não viole a restrição
de integridade da chave primária.
Para testar o gatilho, vamos inserir um novo registro que não especifica
cd_artificial:
SQL> insert into matricula (cd_estudante,cd_curso,nota) values (1,3,7);
SQL> select * from matricula;
CD_ESTUDANTE CD_CURSO NOTA CD_ARTIFICIAL
------------ ---------- ---------- -------------
1 1 5 1
1 2 9 2
1 3 7 3
Pronto! Agora o Rails será capaz de manipular essa tabela sem problemas.
