Gerando seqüências flexíveis (ou parametrizáveis)

por Bruno Magalhães

Seqüências são essenciais a qualquer sistema de informação, seja ele embarcado, instalado ou hospedado, é assim que identificamos os diversos registros (ou entidades) que fazem parte dele, daí a sigla ID (abreviação do inglês Identifier). Claro que existem outras formas de identificar cada um destes registros, cada um com seus prós e contras, mas notavelmente os UUIDs (do inglês Universally Unique Identifier) que são um outro assunto completamente à parte.

Ao longo dos anos muito me questionei sobre as limitações dos servidores de bancos de dados em relação a geração nativa de seqüências, que se limitam a apenas uma única seqüência numérica por tabela!

Mas como assim, não é o suficiente, você me pergunta. Afinal a sigla ID já diz tudo e um único registro não pode ser (ou não deveria ser) identificado de duas (ou mais) formas distintas! Ou se quisermos compartilhar a mesma seqüência entre duas tabelas distintas, por exemplo clientes e fornecedores? Claro que poderíamos, e na maioria das vezes é o que acontece, criar 3 tabelas, sendo uma de atributos comuns, entre eles o ID, e outras duas, clientes e fornecedores, com atributos específicos e, por favor, com chave estrangeira!

Deste ponto em diante devemos então separar identificadores operacionais (públicos) e identificadores sistemáticos (privados), ou seja:

Identificadores operacionais

São aqueles que os usuários e administradores do sistema não só tem acesso, como também podem ser referenciados, alterados e até mesmo removidos completamente. Um bom exemplo é o número de série que geralmente vem impresso em qualquer equipamento eletrônico, e que geralmente segue um esquema específico do fabricante, como por exemplo IPN-0901- 1536479.

Identificadores sistêmicos

São aqueles utilizados pelo sistema de informação para identificar um registro específico, que por sua vez não podem ser alterados ou mesmo removidos, pois se isso acontecesse perderíamos toda e qualquer referência entre este registro e os outros registros armazenados no sistema. Estes identificadores são, ou deveriam ser, completamente escondidos do usuário final.

O problema está no fato de que na maioria dos sistemas atuais, principalmente na web, estes dois tipos de identificadores se confundem, e identificadores sistêmicos são utilizados como identificadores operacionais, afinal de contas quando é que o meu ID 435461906919 precisará ser alterado? Praticamente nunca!

Mas quando falamos em sistemas de médio e grande porte esta flexibilidade (ou pré-requisito de segurança, dependendo do ponto de vista) torna-se absolutamente crítica!

Solução

Então como implementar seqüências flexíveis em MySQL, e, na teoria, em qualquer outro banco de dados? Primeiro criamos a seguinte tabela de configurações, que será o centro nervoso de nosso gerador se seqüências:

SET NAMES utf8;
CREATE TABLE `tb_sequencias` (
  `sequencia_nome` varchar(40) NOT NULL default '',
  `sequencia_prefixo` varchar(20) NOT NULL default '',
  `sequencia_sufixo` varchar(20) NOT NULL default '',
  `sequencia_digito` bigint(20) unsigned NOT NULL default '0',
  `sequencia_valor` bigint(20) unsigned NOT NULL default '0',
  `sequencia_fator` bigint(20) unsigned NOT NULL default '0',
  PRIMARY KEY  (`sequencia_nome`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Note que utilizei a engine MyISAM especificamente por esta não respeitar transações, e independente da transação ser confirmada ou revertida, o incremento deverá acontecer!

Agora criamos a função, que pode também ser escrita em qualquer linguagem, mas que aqui preferi escrever em SQL mesmo para que pudéssemos utilizá-la de qualquer lugar, inclusive do terminal de comando:

SET NAMES utf8;
DELIMITER $$
CREATE FUNCTION `fn_sequencia` (`tp_sequencia_nome` varchar(40))
 RETURNS varchar(200) CHARSET utf8
 NOT DETERMINISTIC
 MODIFIES SQL DATA
 SQL SECURITY INVOKER
 BEGIN
   DECLARE tp_sequencia_prefixo VARCHAR(20) DEFAULT '';
   DECLARE tp_sequencia_sufixo VARCHAR(20) DEFAULT '';
   DECLARE tp_sequencia_valor BIGINT(20) DEFAULT 0;
   DECLARE tp_sequencia_digito BIGINT(20) DEFAULT 0;

   SELECT sequencia_prefixo,
          sequencia_sufixo,
          sequencia_valor + sequencia_fator AS sequencia_valor,
          sequencia_digito
   INTO   tp_sequencia_prefixo,
          tp_sequencia_sufixo,
          tp_sequencia_valor,
          tp_sequencia_digito
   FROM   tb_sequencias
   WHERE  sequencia_nome = tp_sequencia_nome
   LIMIT 1 FOR UPDATE;

   UPDATE tb_sequencias
   SET    sequencia_valor = tp_sequencia_valor
   WHERE  sequencia_nome = tp_sequencia_nome;

   IF tp_sequencia_digito > 0 THEN
     RETURN CONCAT(tp_sequencia_prefixo, LPAD(tp_sequencia_valor, tp_sequencia_digito, '0'), tp_sequencia_sufixo);
   ELSE
     RETURN CONCAT(tp_sequencia_prefixo, tp_sequencia_valor, tp_sequencia_sufixo);
   END IF;
END$$

Pronto, agora podemos gerar as mais diversas seqüências que quisermos, da seguinte forma:

INSERT INTO `tb_produtos` VALUES (fn_sequencia('sq_produto_id'), fn_sequencia('sq_produto_codigo'), 'Nome do produto');

Mas suponhamos que nosso cliente decidiu que todos os códigos de produtos deverão ser prefixados com o ano de sua fabricação, como o faríamos? Geralmente teríamos que alterar a função (ou o método) responsável por esta geração, ou teríamos que extrair esta informação da data do registro e alterar as telas de visualizações do sistema para utilizar-se desta informação. No nosso caso bastaria atualizar o registro da seqüência para:

REPLACE INTO `tb_sequencias` VALUES ('sq_produto_codigo', 'PR2010', '', '4', '1', '1');

Simplíssimo, não?

Conclusão

Depois de ter desenvolvido os primeiros rascunhos deste (sub)sistema fui pesquisar mais a fundo e descobri que o banco de dados ORACLE 9i utiliza por padrão um sistema praticamente idêntico ao que detalhei aqui, obviamente com algumas melhorias que só o desenvolvedor do próprio banco é capaz de fazer, melhorias como por exemplo o uso de tabelas do sistema (bloqueadas para acesso direto), funções (stored procedures) específicas para operação das configurações destas sequências, e quantos registros são pré-gerados e guardados na memória para uma rápida utilização. Mas o funcionamento é absolutamente idêntico!

Esta metodologia abre inúmeras possibilidades, por exemplo pode ser facilmente estendido para gerar seqüências pseudo-aleatórias, entre muitas outras.